using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;

namespace insertyaka
{
    class Program
    {
        static void Main(string[] args)
        {
            //Creation d'un fichier sql
            StreamWriter sw = new StreamWriter(@"D:\Jennifer\cSharp\yaka\insert_yaka.sql");
            String prop_sql= "";
            String cat_sql="";
            String desc_sql= "";
            String art_sql= "";
            String art_prop_sql= "";
            String pack_sql= "";
            String pack_prop_sql= "";
            String comp_sql= "";

            //Chargement de la page XML
            XmlDocument doc = new XmlDocument();
            doc.Load(@"D:\Jennifer\cSharp\yaka\insertyaka\yakadb.xml");

            /***** TABLE PROPERTY ****/
            XmlNodeList list = doc.DocumentElement.SelectNodes("//property/name/text()");

            /*HashSet permet d'avoir uniquement les différents*/
            HashSet<string> uniqueprops = new HashSet<string>();
            /*Dictionary est un tableau associatif*/
            Dictionary<string, int> dico = new Dictionary<string, int>();

            /*Pour tous les nodes de property, je les ajoutes dans le hashset et c'est le hashset qui élimine les doublons*/
            foreach (XmlNode p in list)
            {
                //Console.WriteLine("Ajoute valeur de propriété : " + p.Value);
                uniqueprops.Add(p.Value);
            }

            /*Je peux numéroter arbitrairement les property et je les mets dans un dico pour pouvoir retrouver l'association*/
            int i = 0;
            foreach (string item in uniqueprops)
            {
                Console.WriteLine("Valeur dans le hashset : " + item);
                dico.Add(item, ++i);
            }

            foreach (string item in dico.Keys)
            {
                prop_sql += "INSERT INTO property(prop_id,prop_name) VALUES(" + dico[item] + ",'" + item + "');\n";
            }


            //***** TABLE CATEGORY *****//
            XmlNode categories = doc.DocumentElement.SelectSingleNode("categories"); //Single car il n'y a qu'une balise
            XmlNodeList liste_categories = categories.SelectNodes("category");

            foreach (XmlNode categorie in liste_categories)
            {
                String cat_id = categorie.SelectSingleNode("@id").Value;
                Console.WriteLine("id de la categorie:" + cat_id);
                String cat_name = categorie.SelectSingleNode("text()").Value;
                Console.WriteLine("nom de la categorie: " + cat_name);
                String cat_parent;

                if (categorie.SelectSingleNode("@parent") == null)
                {
                    cat_parent = "null";
                }
                else
                {
                    cat_parent = categorie.SelectSingleNode("@parent").Value;
                }
                Console.WriteLine("Clé etrangère de la catégorie: " + cat_parent);

                cat_sql += "INSERT INTO category(cat_id,cat_name,cat_parent,cat_deleted) VALUES (" + cat_id + ",'" + cat_name + "'," + cat_parent + ",0);\n";
            }
            XmlNode articles = doc.DocumentElement.SelectSingleNode("articles");



            /***** TABLE DE DESCRIPTION *****/
            XmlNodeList types = articles.SelectNodes("type");

            foreach (XmlNode type in types)
            {
                String desc_id = type.SelectSingleNode("@id").Value;
                Console.WriteLine("Id de la description : " + desc_id);

                String desc_name = type.SelectSingleNode("name/text()").Value;
                Console.WriteLine("Nom de la description : " + desc_name);

                String desc_description = type.SelectSingleNode("description/text()").Value;
                Console.WriteLine("Description : " + desc_description);

                String desc_shortdesc = type.SelectSingleNode("short-desc/text()").Value;
                Console.WriteLine("Raccourci description :" + desc_shortdesc);

                String desc_image = type.SelectSingleNode("image/text()").Value;
                Console.WriteLine("image de la description : " + desc_image);

                String desc_thumbnail = type.SelectSingleNode("thumb/text()").Value;
                Console.WriteLine("vignette : " + desc_thumbnail);

                String desc_categorie_fk = type.SelectSingleNode("category_ref/@id").Value;
                Console.WriteLine("Clé etrangère categorie : " + desc_categorie_fk);

                desc_sql += "INSERT INTO description(desc_id,desc_name,desc_description,desc_shortdesc,desc_image,desc_thumbnail,desc_category_fk,desc_deleted) VALUES (" + desc_id + ",'" + desc_name.Replace("'", "''") + "','" + desc_description.Replace("'", "''") + "','" + desc_shortdesc.Replace("'", "''") + "','" + desc_image + "','" + desc_thumbnail + "'," + desc_categorie_fk + ",0);\n";


                /***** TABLE ARTICLE *****/
                XmlNode sub_articles = type.SelectSingleNode("articles");

                XmlNode compose = type.SelectSingleNode("composition");

                if (sub_articles != null)
                {
                    XmlNodeList article = sub_articles.SelectNodes("article");

                    foreach (XmlNode art_detail in article)
                    {
                        String art_id = art_detail.SelectSingleNode("@id").Value;
                        Console.WriteLine("id de l'article :" + art_id);

                        String art_price = art_detail.SelectSingleNode("price/text()").Value;
                        int prix = (int)(float.Parse(art_price));
                        Console.WriteLine("Prix de l'article :" + prix);

                        art_sql += "INSERT INTO article(art_id,art_price,art_description_fk,art_promotion_fk,art_deleted) VALUES (" + art_id + ",'" + prix + "'," + desc_id + ",null,0);\n";


                        /***** TABLE ARTICLE HAS PROPERTIES *****/
                        XmlNode properties = art_detail.SelectSingleNode("properties");

                        if (properties != null)
                        {
                            XmlNodeList property = properties.SelectNodes("property");

                            foreach (XmlNode prop in property)
                            {
                                String prop_name = prop.SelectSingleNode("name/text()").Value;
                                Console.WriteLine("Nom de la propriété: " + prop_name);

                                String ap_value = prop.SelectSingleNode("value/text()").Value;
                                Console.WriteLine("Valeur de la propriété de l'article: " + ap_value);

                                art_prop_sql += "INSERT INTO articleHasProperties(ap_article_fk,ap_property_fk,ap_value) VALUES (" + art_id + "," + dico[prop_name] + ",'" + ap_value + "');\n";
                            }
                        }
                    }
                }

                /***** TABLE PACK + COMPOSE *****/
                if (compose != null)
                {
                    String pack_id = compose.SelectSingleNode("@id").Value;
                    Console.WriteLine("Id du pack : " + pack_id);

                    XmlNodeList reference_liste = compose.SelectNodes("article_ref");

                    pack_sql += "INSERT INTO pack(pack_id,pack_description_fk,pack_promotion_fk,pack_deleted) VALUES (" + pack_id + "," + desc_id + ",null,0);\n";

                    foreach (XmlNode reference in reference_liste)
                    {
                        String comp_article_fk = reference.SelectSingleNode("@id").Value;
                        Console.WriteLine("Clé etrangère de l'article:" + comp_article_fk);

                        String comp_quantity = reference.SelectSingleNode("@quantity").Value;
                        Console.WriteLine("Quantité : " + comp_quantity);

                        comp_sql += "INSERT INTO compose(comp_pack_fk,comp_article_fk,comp_quantity) VALUES ("+pack_id + "," + comp_article_fk + "," + comp_quantity +");\n";


                        /***** TABLE PACK HAS PROPERTIES *****/
                        XmlNode properties = compose.SelectSingleNode("properties");

                        if (properties != null)
                        {
                            XmlNodeList property = properties.SelectNodes("property");

                            foreach (XmlNode prop in property)
                            {
                                String prop_name = prop.SelectSingleNode("name/text()").Value;
                                Console.WriteLine("Nom de la propriété: " + prop_name);

                                String pp_value = prop.SelectSingleNode("value/text()").Value;
                                Console.WriteLine("Valeur de la propriété de l'article: " + pp_value);

                                pack_prop_sql += "INSERT INTO packHasProperties(pp_pack_fk,pp_property_fk,pp_value) VALUES (" + pack_id + "," + dico[prop_name] + ",'" + pp_value.Replace("'", "''") + "');\n";
                            }
                        }
                    }
                }
            }

            String sql = "";

            //Pour qu'il ne tient pas compte de l'auto-incrémentation
            sql += "SET IDENTITY_INSERT property ON\n";
                sql += prop_sql;
            sql += "SET IDENTITY_INSERT property OFF\n";

            sql += "SET IDENTITY_INSERT category ON\n";
            sql += cat_sql;
            sql += "SET IDENTITY_INSERT category OFF\n";

            sql += "SET IDENTITY_INSERT description ON\n";
            sql += desc_sql;
            sql += "SET IDENTITY_INSERT description OFF\n";

            sql += "SET IDENTITY_INSERT article ON\n";
            sql += art_sql;
            sql += "SET IDENTITY_INSERT article OFF\n";

            sql += "SET IDENTITY_INSERT pack ON\n";
            sql += pack_sql;
            sql += "SET IDENTITY_INSERT pack OFF\n";

            sql += comp_sql;

            sql += art_prop_sql;

            sql += pack_prop_sql;

            sw.WriteLine(sql);
            sw.Close();

        Console.ReadKey(false);
        }
    }
}