using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Windows.Forms;

namespace AssistDB
{
    public partial class AssitDB : Form
    {
        List<Class1> jsonFile;
        private List<Column> cols;
        private String listView2Content;
        private List<String> listTable = new List<string>();
        
        public AssitDB(List<Class1> jsonFile)
        {
            this.jsonFile = jsonFile;
            InitializeComponent();
            allSchema();
            //allTable();

        }

        public void allSchema()
        {
            
            this.listView1.Items.Clear();
            this.jsonFile.ForEach(json => this.listView1.Items.Add(json.schema));
            this.jsonFile.ForEach(json => this.comboBox1.Items.Add(json.schema));
            this.jsonFile.ForEach(json => this.comboBox2.Items.Add(json.schema));
            
        }

        public void findByNameSchema(String search)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            this.listView1.Items.Clear();
            List<String> list = this.jsonFile.Select(name => name.schema).Where(schema => schema.Contains(search.ToUpper())).ToList();
            list.ForEach(name => this.listView1.Items.Add(name));
            Splash.CloseForm();
        }

        public void findByNameTable(String search)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (search.Length > 2)
            {
                this.listView2.Items.Clear();
                List<string> listResult = new List<string>();
                foreach (Class1 cl in this.jsonFile)
                {
                    foreach (Datum data in cl.data)
                    {
                        if (data.table.Contains(search.ToUpper()))
                        {
                            listResult.Add(data.table);
                            
                        }
                    }
                }

                listResult.Sort();
                listResult = listResult.Distinct().ToList();
                listResult.ForEach(table => this.listView2.Items.Add(table));

            }
            else
            {
                this.listView2.Clear();
                this.listView2.Items.Add("Veuillez renseigner une recherche plus grande");
            }
            Splash.CloseForm();
        }

        public void findByNameTableInSchema(String schema, String search)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (search.Length > 2)
            {
                this.listView2.Items.Clear();

                foreach (Class1 cl in this.jsonFile)
                {
                    if(cl.schema.Equals(schema))
                    {
                        foreach (Datum data in cl.data)
                        {
                            if (data.table.Contains(search.ToUpper()))
                            {
                                this.listView2.Items.Add(data.table);
                            }
                        }
                    }
                    
                }

            }
            else
            {
                this.listView2.Clear();
                this.listView2.Items.Add("Veuillez renseigner une recherche plus grande");
            }
            Splash.CloseForm();
        }

        private void searchBox_TextChanged(object sender, EventArgs e)
        {
            findByNameSchema(this.searchBox.Text);
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            if (this.listView1.SelectedItems.Count > 0)
            {
                findByNameTableInSchema(listView1.SelectedItems[0].Text, this.textBox1.Text);
            } 
            else
            {
                findByNameTable(this.textBox1.Text);
            }
                
        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (listView1.SelectedItems.Count > 0)
            {
                if (this.listView2.SelectedItems.Count > 0)
                {
                    this.listView2_SelectedIndexChanged(sender, e);
                }
                else
                {
                    
                    this.listView2Content = this.listView1.SelectedItems[0].Text;
                    this.listView2.Items.Clear();
                    Class1 schema = this.jsonFile.Where(json => json.schema.Equals(this.listView2Content)).First();
                    foreach (Datum data in schema.data)
                    {
                        this.listTable.Add(data.table);
                    }
                    this.listTable.ForEach(table => this.listView2.Items.Add(table));
                    
                } 
            }
            Splash.CloseForm();

        }

        private void listView2_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.listView2.SelectedItems.Count > 0)
            {

                HashSet<Class1> schemas = new HashSet<Class1>();
                Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
                foreach (Class1 schema in this.jsonFile)
                {
                    foreach (Datum table in schema.data)
                    {
                        
                        if (table.table.Equals(listView2.SelectedItems[0].Text)) {
                            if (this.listView1.SelectedItems.Count > 0)
                            {
                                if (schema.schema.Equals(listView1.SelectedItems[0].Text))
                                {
                                    this.listView3.Clear();
                                    this.richTextBox1.Clear();
                                    this.cols = new List<Column>();
                                    this.richTextBox1.SelectionFont = new Font("Consolas", 12);
                                    this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
                                    this.richTextBox1.AppendText(Environment.NewLine + "Table name    : " + table.table);
                                    this.richTextBox1.AppendText(Environment.NewLine + "Table texte   : " + table.table_text);
                                    this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
                                    this.listView3.Items.Add("Toutes les conlonnes");
                                    foreach (Column col in table.columns)
                                    {
                                        this.listView3.Items.Add(col.name);
                                        this.cols.Add(col);
                                        printCol(col);
                                    }
                                }
                            }
                            schemas.Add(schema);
                        }
                    }
                }
                this.listView1.Items.Clear();
                schemas.ToList().ForEach(json => this.listView1.Items.Add(json.schema));
                Splash.CloseForm();
            }
            else
            {
                this.listView3.Items.Clear();
                this.listView3.Items.Add("Veuillez selectionner un schéma");
            }
            Splash.CloseForm();
        }

        private void listView3_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.textBox2.Text.Equals(""))
            {
                this.richTextBox1.Clear();
                if (this.listView3.SelectedItems.Count > 0)
                {
                    if (listView3.SelectedItems[0].Text.Equals("Toutes les conlonnes"))
                    {
                        printAllcol(cols);
                    }
                    else
                    {
                        Column column = this.cols.Where(col => col.name.Equals(listView3.SelectedItems[0].Text)).First();
                        printCol(column);
                    }

                }
            } else
            {
                
                this.richTextBox1.Clear();
                if (this.listView3.SelectedItems.Count > 0)
                {
                    HashSet<Datum> tables = new HashSet<Datum>();
                    Column colres= null;
                    foreach (Class1 schema in this.jsonFile)
                    {
                        foreach (Datum table in schema.data)
                        {
                            foreach (Column col in table.columns)
                            {
                                if(col.name != null)
                                {
                                    if (col.name.Equals(this.listView3.SelectedItems[0].Text))
                                    {
                                        colres = col;
                                        tables.Add(table);
                                    }
                                }                                                                                           
                            }
                        }
                    }
                    printCol(colres);
                    this.listView2.Items.Clear();
                    tables.ToList().ForEach(json => this.listView2.Items.Add(json.table));
                }
                
            }
            Splash.CloseForm();
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            allSchema();
            this.searchBox.Text = "";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.listView2.Items.Clear();
            this.listView3.Items.Clear();
            this.richTextBox1.Clear();
            this.textBox1.Text = "";

        }

        private void printCol(Column col)
        {
            this.richTextBox1.SelectionFont = new Font("Consolas", 12);
            this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
            this.richTextBox1.AppendText(Environment.NewLine + "Colonne       : " + col.name);
            this.richTextBox1.AppendText(Environment.NewLine + "Data type     : " + col.data_type);
            this.richTextBox1.AppendText(Environment.NewLine + "Length        : " + col.length);
            this.richTextBox1.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
            this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
        }

        private void printAllcol(List<Column> cols)
        {
            cols.ForEach(col => printCol(col));
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.comboBox1.SelectedIndex > -1)
            {
                this.comboBox3.Items.Clear();
                this.listView5.Items.Clear();
                Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();

                foreach (Datum data in schema.data)
                {
                    this.comboBox3.Items.Add(data.table);
                }
                
            }
            Splash.CloseForm();
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.comboBox2.SelectedIndex > -1)
            {
                this.comboBox4.Items.Clear();
                this.listView5.Items.Clear();
                Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox2.SelectedItem)).First();

                foreach (Datum data in schema.data)
                {
                    this.comboBox4.Items.Add(data.table);
                }
               
            }
            Splash.CloseForm();
        }

        private void searchJoinTable()
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.comboBox3.SelectedIndex > -1 && this.comboBox4.SelectedIndex > -1)
            {
                
                this.listView5.Items.Clear();
                Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
                Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();

                Class1 schema2 = this.jsonFile.Where(json => json.schema.Equals(comboBox2.SelectedItem)).First();
                Datum table2 = schema2.data.Where(data => data.table.Equals(comboBox4.SelectedItem)).First();

                foreach(Column col in table.columns)
                {
                    foreach(Column col2 in table2.columns)
                    {
                        if(col.name.Contains(col2.name))
                        {
                            this.listView5.Items.Add(col.name);
                        }
                    }
                }
                
            }
            Splash.CloseForm();

        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {            
            searchJoinTable();          
        }

        private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
        {
            searchJoinTable();
        }

        private void listView5_SelectedIndexChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (this.listView5.SelectedItems.Count == 1)
            {
                this.richTextBox3.Clear();
                Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
                Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();

                foreach (Column col in table.columns)
                {
                    if (col.name.Equals(this.listView5.SelectedItems[0].Text))
                    {
                        this.richTextBox3.SelectionFont = new Font("Consolas", 12);
                        this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
                        this.richTextBox3.AppendText(Environment.NewLine + "Colonne       : " + col.name);
                        this.richTextBox3.AppendText(Environment.NewLine + "Data type     : " + col.data_type);
                        this.richTextBox3.AppendText(Environment.NewLine + "Length        : " + col.length);
                        this.richTextBox3.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
                        this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
                    }
                }
                this.richTextBox2.Clear();
                writeJoin(""); // JOIN
                writeJoin(" LEFT "); // LEFT JOIN
                writeJoin("RIGHT "); // RIGHT JOIN
                writeJoin("INNER "); // INNER JOIN
                writeJoin("OUTER "); // OUTER JOIN
            } else if (this.listView5.SelectedItems.Count > 1)
            {   
                this.richTextBox3.Clear();
                this.richTextBox3.SelectionFont = new Font("Consolas", 12);
                Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
                Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();
                
                for (int i=0; i< this.listView5.SelectedItems.Count; i++)
                {
                    foreach (Column col in table.columns)
                    {
                        if (col.name.Equals(this.listView5.SelectedItems[i].Text))
                        {
                            this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
                            this.richTextBox3.AppendText(Environment.NewLine + "Colonne       : " + col.name);
                            this.richTextBox3.AppendText(Environment.NewLine + "Data type     : " + col.data_type);
                            this.richTextBox3.AppendText(Environment.NewLine + "Length        : " + col.length);
                            this.richTextBox3.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
                            this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
                        }
                    }
                }
                this.richTextBox2.Clear();
                writeJoin(""); // JOIN
                writeJoin(" LEFT "); // LEFT JOIN
                writeJoin("RIGHT "); // RIGHT JOIN
                writeJoin("INNER "); // INNER JOIN
                writeJoin("OUTER "); // OUTER JOIN
            }
            Splash.CloseForm();
        }

        private void writeJoin(string join)
        {
            string schema1 = comboBox1.SelectedItem.ToString();
            string schema2 = comboBox2.SelectedItem.ToString();
            string table1 = comboBox3.SelectedItem.ToString();
            string table2 = comboBox4.SelectedItem.ToString();
            string select = "SELECT " + table1.ToLower() + ".*, " + table2.ToLower() + ".* ";
            string from = "FROM " +
               schema1 + "." + table1 + " AS " + table1.ToLower();
            string jointure = join + "JOIN " + schema2 + "." + table2 + " AS " + table2.ToLower() + " ";
            string on = "ON " + table1.ToLower() + "." + this.listView5.SelectedItems[0].Text + "=" + table2.ToLower() +
                "." + this.listView5.SelectedItems[0].Text;
            if(this.listView5.SelectedItems.Count > 1)
            {
                for (int i = 1; i < this.listView5.SelectedItems.Count; i++)
                {
                    on += "\nAND " + table1.ToLower() + "." + this.listView5.SelectedItems[i].Text + "=" + table2.ToLower() +
                "." + this.listView5.SelectedItems[i].Text + " ";
                }
            }
            
              

            this.richTextBox2.SelectionFont = new Font("Consolas", 14);
            if(join.Equals(""))
            {
                this.richTextBox2.AppendText(Environment.NewLine + "------------------------  JOIN  -------------------------");
            }
            else
            {
                this.richTextBox2.AppendText(Environment.NewLine + "---------------------  " + join + "JOIN  ----------------------");
            }

           this.richTextBox2.AppendText(Environment.NewLine + select);
           this.richTextBox2.AppendText(Environment.NewLine + from);
           this.richTextBox2.AppendText(Environment.NewLine + jointure);
           this.richTextBox2.AppendText(Environment.NewLine + on);
           this.richTextBox2.AppendText(Environment.NewLine + "---------------------------------------------------------");
            this.richTextBox2.AppendText(Environment.NewLine);
        }

        private void AssitDB_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
            if (textBox2.Text.Length > 2)
            {
                this.listView3.Items.Clear();
                List<string> listResult = new List<string>();
                List<string> listResultTable = new List<string>();
                foreach (Class1 cl in this.jsonFile)
                {
                    foreach (Datum data in cl.data)
                    {
                        foreach(Column col in data.columns)
                        {
                            if(col.name != null )
                            {
                                if (col.name.Contains(textBox2.Text.ToUpper()))
                                {
                                    listResult.Add(col.name);
                                }
                            }
                            
                        }

                    }
                }

                listResult.Sort();

                listResult = listResult.Distinct().ToList();

                listResult.ForEach(col => this.listView3.Items.Add(col));

            }
            else
            {
                this.listView3.Clear();
                this.listView3.Items.Add("Veuillez renseigner une recherche plus grande");
            }
            Splash.CloseForm();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.listView3.Items.Clear();
            this.textBox2.Text = "";
        }

    }
}