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.Threading;
using System.Xml.Linq;
using System.Net;

namespace P3Parser
{
    class Program
    {
        static void Main(string[] args)
        {
            doStuffs();
        }

        public static void doStuffs()
        {
            parseMisdaden();
        }

        public static void parseMisdaden()
        {
            timsParser tp1 = new timsParser();
            int[] i1 = { 0,5,8,10,9,11,14,19,22};
            List<List<string>> misdaden1 = timsParser.parseCSV(@"D:\AlleCsv\misdaden\Straatroof-2011.csv", i1, ';');
            string[] misdaadnamen = { "casenummer", "dag", "begindatum", "begintijd", "einddatum", "eindtijd", "plaats", "straat", "postcode"};
            List<int> removeIndex = new List<int>();
            //Throw out the empty strings.
            for (int i = 0; i < misdaden1.Count; i++)
            {
                for (int j = 0; j < misdaden1[0].Count; j++)
                {
                    Console.ForegroundColor = ConsoleColor.White;
                    Console.Write('"' + misdaden1[i][j] + '"' + "  ");
                    if (misdaden1[i][j].Replace(" ", "") == "")
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.Write("REMOVED");
                        removeIndex.Add(i);
                    }
                }
                Console.WriteLine();
            }
            for (int i = 0; i < removeIndex.Count; i++)
            {
                misdaden1.RemoveAt(removeIndex[i]-i);
            }
            Console.WriteLine();
            for (int i = 0; i < misdaden1.Count; i++)
            {
                for (int j = 0; j < misdaden1[0].Count; j++)
                {
                    Console.ForegroundColor = ConsoleColor.White;
                    Console.Write('"' + misdaden1[i][j] + '"' + "  ");
                    if (misdaden1[i][j].Replace(" ", "") == "")
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine("REMOVED");
                        Console.ForegroundColor = ConsoleColor.White;
                        removeIndex.Add(i);
                    }
                }
                Console.WriteLine(" " + i.ToString());
            }
            /*
            List<List<string>> misdaden2 = new List<List<string>>();
            for (int i = 0; i < misdaden1.Count; i++)
            {
                latlng p;
                List<string> l = new List<string>();
                p = timsParser.addressToLatLng(misdaden1[i][8]);
                if (p.x != 0 && p.y != 0)
                {
                    l.AddRange(misdaden1[i]);
                    l.Add(p.x.ToString());
                    l.Add(p.y.ToString());
                    misdaden2.Add(l);
                }
                else
                {
                    p = timsParser.addressToLatLng(misdaden1[i][6] + " " + misdaden1[i][7]);
                    if (p.x != 0 && p.y != 0)
                    {
                        l.AddRange(misdaden1[i]);
                        l.Add(p.x.ToString());
                        l.Add(p.y.ToString());
                        misdaden2.Add(l);
                    }
                    else
                    {
                        Console.WriteLine();
                    }
                }
            } */
            for (int i = 0; i < misdaden1.Count; i++)
            {
                for (int j = 0; j < misdaden1[0].Count; j++)
                {
                    if (misdaden1[i][j].Contains("'"))
                    {
                        misdaden1[i][j] = misdaden1[i][j].Replace("'", "");
                    }
                    Console.Write(misdaden1[i][j] + ", ");
                }
                Console.WriteLine();
            }
            SQLconnector.InsertIntoTable("misdaden", misdaadnamen.ToList(), misdaden1);
            Console.Read();
        }
    }

    class timsParser
    {
        public timsParser()
        {

        }

        public static List<List<string>> parseCSV(string filelocation, int[] rows, char seperator)
        {
            List<List<string>> endresult = new List<List<string>>();
            using (StreamReader R = new StreamReader(filelocation))
            {
                int i = 0;
                string line;
                while (true)
                {
                    i++;
                    line = R.ReadLine();
                    try
                    {
                        Thread.Sleep(1);
                        List<string> s = line.Split(seperator).ToList();
                        List<string> s2 = new List<string>();
                        if (line.Replace(" ", "") != "")
                        {
                            for (int j = 0; j < s.Count; j++)
                            {
                                if (rows.Contains(j))
                                {
                                    s2.Add(s[j]);
                                }
                            }
                            if (s2.Count == rows.Length)
                            {
                                endresult.Add(s2);
                            }
                        }
                    }
                    catch
                    {
                        return endresult;
                    }
                }
            }
        }

        public static latlng addressToLatLng(string address)
        {
            int attempt = 0;
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine("INPUT: " + address);
            string requestUri = string.Format("http://maps.googleapis.com/maps/api/geocode/xml?address={0}&sensor=false", Uri.EscapeDataString(address));
            tryagain:
            Thread.Sleep(2000);
            try
            {
                WebRequest request = WebRequest.Create(requestUri);
                WebResponse response = request.GetResponse();
                XDocument xdoc = XDocument.Load(response.GetResponseStream());
                XElement result = xdoc.Element("GeocodeResponse").Element("result");
                XElement locationElement = result.Element("geometry").Element("location");
                XElement lat = locationElement.Element("lat");
                XElement lng = locationElement.Element("lng");

                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine("SUCCES!");

                Console.WriteLine("LAT: " + lat.Value.ToString());
                Console.WriteLine("LNG: " + lng.Value.ToString());
                Console.ForegroundColor = ConsoleColor.White;
                return (new latlng(double.Parse(lat.Value, System.Globalization.CultureInfo.InvariantCulture.NumberFormat), double.Parse(lng.Value, System.Globalization.CultureInfo.InvariantCulture.NumberFormat)));
            }
            catch  (Exception e)
            {
                if (attempt < 4)
                {
                    attempt++;
                    goto tryagain;
                }
                else
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine("Didn't manage to get the latitude and longitude");
                    Console.WriteLine(e);
                    Console.ForegroundColor = ConsoleColor.White;
                    return new latlng(0, 0);
                }
            }
        }

    }

    public class SQLconnector
    {
        public static NpgsqlConnection Con = new NpgsqlConnection("Host=localhost;Username=postgres;Password=Test;Database=Project3Solo");

        public static void InsertIntoTable(string tablename, List<string> Names, List<List<string>> Values)
        {
            NpgsqlCommand COM = new NpgsqlCommand();
            Con.Open();
            COM.Connection = Con;
            for (int i = 0; i < Values.Count; i++)
            {
                COM.CommandText = "INSERT INTO " + tablename + "(";
                for (int j = 0; j < Names.Count; j++)
                {
                    COM.CommandText = COM.CommandText + Names[j];
                    if (j != Names.Count-1)
                    {
                        COM.CommandText = COM.CommandText + ",";
                    }
                }
                COM.CommandText = COM.CommandText + ") VALUES (";
                for (int j = 0; j < Values[i].Count; j++)
                {
                    COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
                    if (j != Values[0].Count-1)
                    {
                        COM.CommandText = COM.CommandText + ",";
                    }
                }
                COM.CommandText = COM.CommandText + ");";
                Thread.Sleep(10);
                Console.WriteLine(COM.CommandText);
                try
                {
                    COM.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
            }
            Con.Close();
        }

    }

    public class latlng
    {
        public double x;
        public double y;
        public latlng(double x, double y)
        {
            this.x = x;
            this.y = y;
        }
    }
}