using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using _01.EF_Mappings;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;

namespace _04.Import_Rivers_From_XML
{
    public class Import_Rivers_From_XML
    {
        static void Main(string[] args)
        {
              XmlDocument xmlDoc = new XmlDocument();
              xmlDoc.Load("rivers.xml");
              string xPath = "/rivers/river";
              
             // var riversDB = new River();
              XmlNodeList riverList = xmlDoc.SelectNodes(xPath);

              var contextRivers = new GeographyEntities();
                  foreach (XmlNode river in riverList)
                  {
                      //mandatory
                      string name = river.FirstChild.InnerText;
                      int length = Int32.Parse(river["length"].InnerText);
                      string outflow = river["outflow"].InnerText;
                      int? drainageArea = null;
                      int? averageDischarge = null;
                      string countryRiver = null;

                      //optional
            
                      if (river["drainage-area"] != null)
                      {
                          drainageArea = Int32.Parse(river["drainage-area"].InnerText);
                          
                      }
                      
            
                      if (river["average-discharge"] != null)
                      {
                          averageDischarge = Int32.Parse(river["average-discharge"].InnerText);
                          
                      }
                     
                      
                      var riversDB = new River()
                      {
                          RiverName = name,
                          Length = length,
                          Outflow = outflow,
                          DrainageArea = drainageArea,
                          AverageDischarge = averageDischarge,
    
                      };

                      var context = new GeographyEntities();
                        
                      context.Rivers.Add(riversDB);
                      context.SaveChanges();
                      Console.WriteLine("Rivers added!");

                  //Error inserting..................................
                  foreach (XmlNode countryName in river["countries"])
                  {
                      countryRiver = countryName.InnerText;
                      var addCountry = contextRivers.Countries
                      .FirstOrDefault(c => c.CountryName == countryRiver);
                      riversDB.Countries.Add(addCountry);
                 
                      context.Rivers.Add(riversDB);
                  }
                      
                  
                  }

                 
            
            var rivers = contextRivers.Rivers
                .OrderByDescending(r => r.Length)
                .Select(r => new
                {
                    riverName = r.RiverName,
                    countries = r.Countries
                        .OrderBy(c => c.CountryName)
                        .Select(c => new
                        {
                            c.CountryName

                        })

                }).ToList();
            foreach (var item in rivers)
            {
                Console.WriteLine(item.riverName);
                foreach (var country in item.countries)
                {
                    Console.WriteLine(country);
                }
            }
            
        }
    }
}