using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using Npgsql;
using System.Data;
using Project3Tools;



namespace Project3
{
    class Program
    {
        static void Main(string[] args)
        {
            //Databases
            //
            //Wijk:
            //  naam Varchar (PK)
            //
            //Straatnaam:
            //  id SERIAL PK
            //  straatnaam varchar (FK van
            //
            //
            //
            //
            //
            //
            //

            Project3Tools.GeneralParser P2 = new Project3Tools.GeneralParser();
            GeneralParser parser = new GeneralParser();


            //Scholen table
            //0. Brin nummer (PK) varchar         2/2
            //1. Instellingsnaam (varchar)        3/
            //2. Straatnaam (integer)             4/          (NEEDS CONVERSION)
            //3. Postcode (varchar)               6/

            List<List<String>> School1 = parser.ParseCSV
            (
                @"D:\AlleCsv\scholen\school05_alleBasis.csv",
                ';',
                (int line, int column, string value) =>
                    {
                    if (column == 2 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
                    },
                (string value) => value + " "
            );
            List<List<String>> School2 = parser.ParseCSV
            (
                @"D:\AlleCsv\scholen\school02_Middelbaar.csv",
                ';',
                (int line, int column, string value) =>
                {
                    if (column == 2 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
                },
                (string value) => value + " "
            );
            List<List<String>> School3 = parser.ParseCSV
            (
                @"D:\AlleCsv\scholen\school03_Mbo.csv",
                ';',
                (int line, int column, string value) =>
                {
                    if (column == 6 || column == 7 || column == 9 || column == 10) { return true; } else { return false; }
                },
                (string value) => value + " "
            );
            List<List<String>> School4 = parser.ParseCSV
            (
                @"D:\AlleCsv\scholen\school04_HboWo.csv",
                ';',
                (int line, int column, string value) =>
                {
                    if (column == 5 || column == 6 || column == 8 || column == 9) { return true; } else { return false; }
                },
                (string value) => value + " "
            );


            SQLConnectionTool N1 = new SQLConnectionTool("Host = localhost; Username = postgres; Password = test; Database = Test");
            //string[] typenames = { "varchar(150)", "varchar(100)", "varchar(100)", "varchar(100)" };
            //N1.createTable("Scholen", School1[0].ToList(), typenames.ToList(), true, 1, "Niveau");
            //N1.insertIntoTable("Scholen", School1[0].ToList(), School1, "Niveau", "'Basisschool'");
            //N1.insertIntoTable("Scholen", School1[0].ToList(), School2, "Niveau", "'Middelbare School'");
            //N1.insertIntoTable("Scholen", School1[0].ToList(), School3, "Niveau", "'MBO'");
            //N1.insertIntoTable("Scholen", School1[0].ToList(), School4, "Niveau", "'HBO'");
            //Console.Read();
        }
    }

    //Created by Allon
    public class GeneralParser
    {
        public List<List<string>> ParseCSV
        (
            string location,
            char seperator,
            Func<int, int, string, bool> filter,
            Func<string, string> action
        )
        {
            List<List<string>> parsed = new List<List<string>>();
            try
            {
                using (StreamReader reader = new StreamReader(location))
                {
                    string line;
                    /* Lees elke regel van het csv bestand */
                    int lineCounter = 0;
                    while ((line = reader.ReadLine()) != null)
                    {
                        lineCounter += 1;
                        /* Splits de regel op in kolommen en loop door elke kolom heen */
                        string[] columns = line.Split(seperator);
                        int columnCounter = 0;
                        List<string> rowList = new List<string>();
                        foreach (string column in columns)
                        {
                            columnCounter += 1;
                            /* Filter */
                            if (filter(lineCounter, columnCounter, column))
                            {
                                /* Voer een actie uit op de waarde en voeg het toe aan de rijlijst */
                                rowList.Add(action(column));
                            }
                        }
                        parsed.Add(rowList);
                    }
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("Something went wrong while reading the csv file: " + e);
            }
            return parsed;
        }

        public List<List<string>> parseList
        (
            List<List<string>> list,
            Func<List<List<string>>, List<string>, bool> filter,
            Func<List<List<string>>, List<string>, List<string>> action
        )
        {
            List<List<string>> parsed = new List<List<string>>();
            foreach (List<string> sublist in list)
            {
                if (filter(list, sublist))
                    parsed.Add(action(list, sublist));
            }
            return parsed;
        }

        public List<string> parseList
        (
            List<string> list,
            Func<List<string>, string, bool> filter,
            Func<List<string>, string, string> action
        )
        {
            List<string> parsed = new List<string>();
            foreach (string value in list)
            {
                if (filter(list, value))
                    parsed.Add(action(list, value));
            }
            return parsed;
        }

        public List<string> compareLists(List<string> list1, List<string> list2)
        {
            List<string> compared = new List<string>();
            IEnumerable<string> intersection = list1.Intersect(list2);
            foreach (string value in intersection)
            {
                compared.Add(value);
            }
            return compared;
        }

        public List<List<string>> compareLists(List<List<string>> list1, List<List<string>> list2)
        {
            List<List<string>> compared = new List<List<string>>();
            parseList(list1, (listOne, value) => true, (listOne, value) =>
            {
                parseList(list2, (listTwo, value2) => true, (listTwo, value2) =>
                {
                    return compareLists(value, value2);
                });
                return value;
            });
            return compared;
        }

        public List<string> ListTo1d(List<List<string>> list, Func<int, string, bool> filter)
        {
            List<string> result = new List<string>();
            /* Loop door de eerste laag */
            foreach (List<string> sublist in list)
            {
                int counter = 0;
                /* Loop door de tweede laag */
                foreach (string value in sublist)
                {
                    counter += 1;
                    /* Filter */
                    if (filter(counter, value))
                    {
                        result.Add(value);
                    }
                }
            }
            return result;
        }
    }

    //Created by Tim
    public class SQLConnectionTool
    {
        public SQLConnectionTool(string connectionstring)
        {
            CON = new NpgsqlConnection(connectionstring);
        }
        NpgsqlConnection CON;

        //Voorbeeld input:
        //tablename: "Mijntable"
        //names = {"naam", "leeftijd"}
        //typenames = {"Varchar(30)", "integer"}
        //droptable = true
        //PrimaryKey = null

        //Voorbeeld output:
        //DROP TABLE Mijntable;
        //CREATE TABLE if not exists Mijntable
        //(
        //  id SERIAL PRIMARY KEY,
        //  naam varchar(30),
        //  leeftijd integer
        //);
        public void createTable(string tablename, List<string> names, List<string> typenames, bool dropTable, int PrimaryKey, string extra)
        {
            if (names.Count != typenames.Count)
            {
                //MessageBox.Show("Please make sure that the amount of names equals the amount of type names");
                throw new Exception("YA DUN! Names: " + names.Count + " typenames: " + typenames.Count);
            }
            if (PrimaryKey >= names.Count && PrimaryKey != 0)
            {
                //MessageBox.Show("Please make sure that the primary key is smaller than the amount of data types");
                throw new Exception("YA DUN!");
            }


            NpgsqlCommand COM = new NpgsqlCommand();
            COM.Connection = CON;
            CON.Open();
            if (dropTable)
            {
                COM.CommandText = "DROP TABLE if exists " + tablename + ";";
                Console.Write(COM.CommandText + "\n");
                COM.ExecuteNonQuery();
            }
            COM.CommandText = "CREATE TABLE if not exists " + tablename + "\n ( \n ";
            if (PrimaryKey == 0)
            {
                COM.CommandText = COM.CommandText + "id SERIAL PRIMARY KEY, \n ";
            }
            for (int i = 0; i < typenames.Count; i++)
            {
                COM.CommandText = COM.CommandText + names[i].Replace(" ", "").Replace("-", "_") + " " + typenames[i];
                if (i + 1 == PrimaryKey)
                {
                    COM.CommandText = COM.CommandText + " PRIMARY KEY";
                }
                if ((i != typenames.Count && extra != "") || (i != typenames.Count - 1 && extra == ""))
                {
                    COM.CommandText = COM.CommandText + ", ";
                }
                COM.CommandText = COM.CommandText + "\n ";
            }
            if (extra != "")
            {
                COM.CommandText = COM.CommandText + extra + " varchar (100)";
            }
            COM.CommandText = COM.CommandText + "\n );\n";
            Console.Write(COM.CommandText);
            Console.Read();
            COM.ExecuteNonQuery();
            CON.Close();
        }

        //Voorbeeld input:
        //tablename = "Mijntable"
        //names = {"naam", "leeftijd"}
        //typenames = {"Varchar(30)", "integer"}
        //Values = {"Eddy", "42"},{"Fred", "28"}, {"Jos", "6"}, {"Loubna", "6"}, {"Rini", "6"}}
        //
        //Voorbeeld output:
        //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Eddy', 42);
        //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Fred', 28);
        //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Jos', 6);
        //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Loubna', 6);
        //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Rini', 6);

        public void insertIntoTable(string tablename, List<string> names, List<List<string>> Values, string extraName, string extraVal)
        {
            if (names.Count != Values[0].Count)
            {
                //MessageBox.Show("Please make sure that the amount of typenames, names and values are equal");
                throw new Exception("YA DUN! Names: " + names.Count + " values[0]: " + Values[0].Count);
            }

            NpgsqlCommand COM = new NpgsqlCommand();
            COM.Connection = CON;
            CON.Open();

            for (int i = 1; i < Values.Count; i++)
            {
                COM.CommandText = "INSERT INTO " + tablename + "(";
                for (int j = 0; j < Values[i].Count; j++)
                {
                    COM.CommandText = COM.CommandText + names[j].Replace(" ", "");
                    if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
                    {
                        COM.CommandText = COM.CommandText + ", ";
                    }
                }
                if (extraName != "")
                {
                    COM.CommandText = COM.CommandText + extraName;
                }
                COM.CommandText = COM.CommandText + ") VALUES (";
                for (int j = 0; j < Values[i].Count; j++)
                {
                    COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
                    if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
                    {
                        COM.CommandText = COM.CommandText + ", ";
                    }
                }
                if (extraName != "")
                {
                    COM.CommandText = COM.CommandText + extraVal;
                }
                COM.CommandText = COM.CommandText + "); \n";
                Console.Write(COM.CommandText);
                try
                {
                    COM.ExecuteNonQuery();
                }
                catch
                {
                    COM.CommandText = "";
                }
            }
            CON.Close();
        }


        public void joinTables(List<List<string>> table1, List<List<string>> table2, string tablename1, string tablename2, string newtable, int joinRowTable1, int joinRowTable2, List<int> selectRowsTable1, List<int> selectRowsTable2)
        {
            NpgsqlCommand COM = new NpgsqlCommand();
            CON.Open();
            COM.CommandText = "SELECT ";
            foreach (int i in selectRowsTable1)
            {
                COM.CommandText = COM.CommandText + tablename1 + "." + table1[0][selectRowsTable1[i]] + ", ";
            }
            int j = 0;
            foreach (int i in selectRowsTable2)
            {
                COM.CommandText = COM.CommandText + tablename2 + "." + table2[0][selectRowsTable2[i]];
                if (j != selectRowsTable2.Count - 1)
                {
                    COM.CommandText = COM.CommandText + ", ";
                }
                j++;
            }
            COM.CommandText = COM.CommandText + "\n";
            COM.CommandText = COM.CommandText + "INTO " + newtable + "\n";
            COM.CommandText = COM.CommandText + "FROM " + tablename1 + "\n";
            COM.CommandText = COM.CommandText + "INNER JOIN " + tablename2 + " ON " + tablename1 + "." + table1[0][joinRowTable1] + " = " + tablename2 + "." + table2[0][joinRowTable2] + ";";
            Console.WriteLine(COM.CommandText);
            Console.Read();
            COM.ExecuteNonQuery();
            CON.Close();
        }

        public void readTable(string tablename, List<int> rows)
        {
            DataSet DS = new DataSet();
            DataTable DT = new DataTable();

            CON.Open();
            NpgsqlDataAdapter DA = new NpgsqlDataAdapter(("SELECT * FROM " + tablename), CON);
            DA.Fill(DS);
            DT = DS.Tables[0];
            foreach (DataRow ROW in DT.Rows)
            {
                for (int i = 0; i < rows.Count; i++)
                {
                    Console.Write(ROW[rows[i]].ToString() + " ");
                }
                Console.Write("\n");
            }
            CON.Close();
        }

        public int straatnaamtoint(string straatnaam)
        {

            return 0;
        }
    }
}