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

using Npgsql;
using NpgsqlTypes;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;



namespace Project3
{
    class Program
    {
        static void Main(string[] args)
        {
            GeneralParser parser = new GeneralParser();
            List<List<String>> School1 = parser.ParseCSV
            (
                @"D:\AlleCsv\scholen\school01_Basis.csv",
                ';',
                (int line, int column, string value) => { if (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 == 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 == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
                },
                (string value) => value + " "
            );

            SQLConnectionTool.CON = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
            string[] typenames = { "varchar(50)", "varchar(100)", "varchar(100)", "varchar(100)" };
            SQLConnectionTool.createTable("TestScholen", School1[0].ToList(), typenames.ToList(), true, 0, "schoolType");
            SQLConnectionTool.insertIntoTable("TestScholen", School1[0].ToList(), typenames.ToList(), School1, "schoolType", "'Basisschool'");
            SQLConnectionTool.insertIntoTable("TestScholen", School1[0].ToList(), typenames.ToList(), School2, "schoolType", "'Middelbare School'");
            SQLConnectionTool.insertIntoTable("TestScholen", School1[0].ToList(), typenames.ToList(), School3, "schoolType", "'MBO'");
            //SQLConnectionTool.insertIntoTable("TestScholen", School1[0].ToList(), typenames.ToList(), School4, "schoolType", "'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 static 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 static void createTable(string tablename, List<string> names, List<string> typenames, bool dropTable, int PrimaryKey, string extra)
        {
            //Zorgt ervoor dat de names en typenames dezelfde grootte hebben.
            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!");
            }

            //Maakt een SQLcommand aan en opent de verbinding.
            NpgsqlCommand COM = new NpgsqlCommand();
            COM.Connection = CON;
            CON.Open();
            //Verwijderd het tabel om alle vorige waarden te verwijderen.
            if (dropTable)
            {
                COM.CommandText = "DROP TABLE if exists " + tablename + ";";
                Console.Write(COM.CommandText + "\n");
                COM.ExecuteNonQuery();
            }
            //Maakt het tabel aan.
            COM.CommandText = "CREATE TABLE if not exists " + tablename + "\n ( \n ";
            //Als er geen primary key gegeven is, dan wordt een autoincrement ID aangemaakt.
            if (PrimaryKey == 0)
            {
                COM.CommandText = COM.CommandText + "id SERIAL PRIMARY KEY, \n ";
            }
            //Loopt door alle datatypes heen.
            for (int i = 0; i < typenames.Count; i++)
            {
                COM.CommandText = COM.CommandText + names[i].Replace(" ","").Replace("-","_") + " " + typenames[i];
                //Als de Primary key niet null is, wordt een primary key aangemaakt.
                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";
            Console.Write(COM.CommandText);
            Console.Read();
            COM.ExecuteNonQuery();
            //Sluit de verbinding
            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 static void insertIntoTable(string tablename, List<string> names, List<string> typenames, List<List<string>> Values, string extraName, string extraVal)
        {
            if (names.Count != typenames.Count || 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 + " typenames: " + typenames.Count + " values[0]: " + Values[0].Count);
            }

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

            for (int i = 1; i < Values.Count; i++)
            {
                if (Values[i][3].ToUpper().Contains("ROTTER"))
                {
                    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++)
                    {
                        if (typenames[j].ToLower().Contains("varchar"))
                        {
                            COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
                        }
                        else
                        {
                            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);
                    //Console.Read();
                    try
                    {
                        COM.ExecuteNonQuery();
                    }
                    catch
                    {
                        COM.CommandText = "";
                    }
                }
            }
            CON.Close();
        }

        public static 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();
        }
    }
}