using Dapper;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.Storage;

namespace DataAccessLibrary.DataAccess
{
    public static class SqliteDataAccess
    {
        public async static void InitializeDatabase()
        {
            await ApplicationData.Current.LocalFolder.CreateFileAsync("Golf.db", CreationCollisionOption.OpenIfExists);

            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Golf.db");

            string speler = "CREATE TABLE IF NOT EXISTS Speler (Id INTEGER PRIMARY KEY, Voornaam NVARCHAR, " +
                "Achternaam NVARCHAR, Handicap REAL, Telefoon NVARCHAR NULL, Email NVARCHAR NULL, " +
                "ClubId INTEGER, TeamId INTEGER, PuntenVoor INTEGER, PuntenTegen INTEGER)";

            string coach = "CREATE TABLE IF NOT EXISTS Coach (Id INTEGER PRIMARY KEY, Voornaam  NVARCHAR, Achternaam NVARCHAR, Telefoon NVARCHAR)";

            string golfClub = "CREATE TABLE IF NOT EXISTS Golfclub (Id INTEGER PRIMARY KEY, Naam NVARCHAR)";

            string klasse = "CREATE TABLE IF NOT EXISTS Klasse(Klasse_Id INTEGER PRIMARY KEY, Klasse_Naam  NVARCHAR, Klasse_Omschrijving NVARCHAR)";

            string wedstrijd = "CREATE TABLE IF NOT EXISTS Wedstrijd (Id INTEGER PRIMARY KEY, Datum NVARCHAR CURRENT_DATE, " +
                "AwaySpeler INTEGER, HomePunten INTEGER, AwayPunten INTEGER)";

            string team = "CREATE TABLE IF NOT EXISTS Team (Id INTEGER PRIMARY KEY, KlasseId INTEGER, Speler1Id INTEGER, " +
                "Speler2Id INTEGER, Speler3Id INTEGER, Speler4Id INTEGER, Speler5Id INTEGER, Speler6Id INTEGER, Naam NVARCHAR, " +
                "CoachId INTEGER, ClubId INTEGER, PuntenVoor INTEGER, PuntenTegen INTEGER)";

            using (SqliteConnection db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Open();

                SqliteCommand createSpeler = new SqliteCommand(speler, db);
                SqliteCommand createCoach = new SqliteCommand(coach, db);
                SqliteCommand createGolfclub = new SqliteCommand(golfClub, db);
                SqliteCommand createWedstrijd = new SqliteCommand(wedstrijd, db);
                SqliteCommand createTeam = new SqliteCommand(team, db);
                SqliteCommand createKlasse = new SqliteCommand(klasse, db);

                createSpeler.ExecuteReader();
                createCoach.ExecuteReader();
                createGolfclub.ExecuteReader();
                createWedstrijd.ExecuteReader();
                createTeam.ExecuteReader();
                createKlasse.ExecuteReader();

                db.Close();
            }
        }

        public static List<T> LoadData<T>(string sqlStatement, Dictionary<String, object> parameters)
        {
            DynamicParameters p = parameters.ToDynamicParameters();
            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Golf.db");

            using (IDbConnection db = new SqliteConnection($"Filename={dbpath}"))
            {
                var rows = db.Query<T>(sqlStatement, p);
                return rows.ToList();
            }

        }
        public static void SaveData(string sqlStatement, Dictionary<string, object> parameters)
        {
            DynamicParameters p = parameters.ToDynamicParameters();
            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Golf.db");

            using (IDbConnection db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Execute(sqlStatement, p);
            }
        }

        public static void DeleteData(string sqlStatement, Dictionary<string, object> parameters)
        {
            DynamicParameters p = parameters.ToDynamicParameters();
            string dbpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Golf.db");

            using (IDbConnection db = new SqliteConnection($"Filename={dbpath}"))
            {
                db.Execute(sqlStatement, p);
            }
        }
        private static DynamicParameters ToDynamicParameters(this Dictionary<string, object> p)
        {
            DynamicParameters output = new DynamicParameters();

            p.ToList().ForEach(x => output.Add(x.Key, x.Value));

            return output;
        }

    }
}
