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 < 5) { return true; } else { return false; }
                },
                (string value) => value + " "
            );


            for (int i = 0; i < School1.Count(); i++)
            {
                for (int j = 0; j < School1[0].Count(); j++)
                {
                    Console.Write(School1[i][j]);
                }
                Console.WriteLine();
            }
            SQLConnectionThing.editData();
            SQLConnectionThing.readData();
            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 SQLConnectionThing
    {



        public static DataTable GrabData(string statement)
        {
            DataTable DT = new DataTable();

            NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
            NpgsqlCommand Comm = new NpgsqlCommand(statement, Con);
            NpgsqlDataAdapter ada = new NpgsqlDataAdapter(Comm);
            Con.Open();
            var cmd = new NpgsqlCommand(statement, Con);
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DT.Rows.Add(reader.GetString(0));
            }
            Con.Close();
            return DT;
        }

        //public static void createTable()
        //
        //public static void insertIntoTable()
        //
        //public static void readFromTable()
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //
        //

        public static void readData()
        {
            DataSet DS = new DataSet();
            DataTable DT = new DataTable();
            NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");

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

            Con.Close();

        }

        public static void editData()
        {
            NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=test;Database=Test");
            NpgsqlCommand Com = new NpgsqlCommand();
            Com.Connection = Con;
            Con.Open();
            Com.CommandText = "DROP TABLE lolz";
            Com.ExecuteNonQuery();
            Com.CommandText = @"CREATE TABLE if not exists lolz
(
    id SERIAL,
    name varchar(30)
); ";
            Com.ExecuteNonQuery();
            Random rnd = new Random();
            for (int i = 0; i < 100; i++)
            {
                int a = rnd.Next(1, 6);
                switch (a)
                {
                    case 1:
                        Com.CommandText = "INSERT INTO lolz(name) VALUES ('FRED');";
                        break;
                    case 2:
                        Com.CommandText = "INSERT INTO lolz(name) VALUES ('EDDY');";
                        break;
                    case 3:
                        Com.CommandText = "INSERT INTO lolz(name) VALUES ('HERBERT');";
                        break;
                    case 4:
                        Com.CommandText = "INSERT INTO lolz(name) VALUES ('HENDRIK');";
                        break;
                    case 5:
                        Com.CommandText = "INSERT INTO lolz(name) VALUES ('BOB');";
                        break;

                }
                Com.ExecuteNonQuery();
            }

            Con.Close();
        }
    }
}