using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Globals.Score;
using System.Configuration;


namespace DataLayer
{
    public class DataAccess
    {

        private DbDataAdapter dgAdapter;
        private DbDataAdapter dsAdapter;
        private DbDataAdapter dpAdapter;

        private DbDataAdapter gdgAdapter;
        private DbDataAdapter gdsAdapter;

        private DbConnection connection;
        private DbConnection global;

        public DataSet DsGameScores { get; set; }

        public void Load()
        {

            DbSqliteConnection("System.Data.SQLite", GetConnectionStringByProvider("System.Data.SQLite"));
            connection.Open();

            DsGameScores = new DataSet();

            DbCommand cmdGames = connection.CreateCommand();
            cmdGames.CommandText = "select * from games";
            cmdGames.CommandType = CommandType.Text;
            dgAdapter.SelectCommand = cmdGames;
            dgAdapter.Fill(DsGameScores, "games");

            DbCommand cmdPlayers = connection.CreateCommand();
            cmdPlayers.CommandText = "select * from players";
            cmdPlayers.CommandType = CommandType.Text;
            dpAdapter.SelectCommand = cmdPlayers;
            dpAdapter.Fill(DsGameScores, "players");

            DbCommand cmdScores = connection.CreateCommand();
            cmdScores.CommandText = "select * from scores order by score desc";
            cmdScores.CommandType = CommandType.Text;
            dsAdapter.SelectCommand = cmdScores;
            dsAdapter.Fill(DsGameScores, "scores");

            connection.Close();
        }

        public void globalAccess()
        {
            DbSqliteConnection("MySql.Data.MySqlClient", GetConnectionStringByProvider("MySql.Data.MySqlClient"));
            global.Open();

            DbCommand cmdGames = global.CreateCommand();
            cmdGames.CommandText = "select * from games";
            cmdGames.CommandType = CommandType.Text;
            gdgAdapter.SelectCommand = cmdGames;
            gdgAdapter.Fill(DsGameScores, "games");

            DbCommand cmdScores = global.CreateCommand();
            cmdScores.CommandText = "select * from scores order by score desc";
            cmdScores.CommandType = CommandType.Text;
            gdsAdapter.SelectCommand = cmdScores;
            gdsAdapter.Fill(DsGameScores, "scores");

            global.Close();
        }

        public DataAccess()
        {
            globalAccess();
        }

        static string GetConnectionStringByProvider(string providerName)
        {

            string returnValue = null;

            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;

            if (settings != null)
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.ProviderName.ToLower() == providerName.ToLower())
                    {
                        returnValue = cs.ConnectionString;
                        break;
                    }
                }
            }
            return returnValue;
        }

        private void DbSqliteConnection(string providerName, string connectionString)
        {
            if (connectionString != null)
            {
                if (providerName == "System.Data.SQLite")
                {
                    try
                    {
                        DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                        connection = factory.CreateConnection();
                        connection.ConnectionString = connectionString;
                        this.dpAdapter = factory.CreateDataAdapter();
                        this.dgAdapter = factory.CreateDataAdapter();
                        this.dsAdapter = factory.CreateDataAdapter();
                    }
                    catch (Exception ex)
                    {
                        if (connection != null)
                        {
                            connection = null;
                        }
                        Console.WriteLine(ex.Message);
                    }
                }

                if (providerName == "Mysql.Data.MySqlClient")
                {
                    try
                    {
                        DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
                        global = factory.CreateConnection();
                        global.ConnectionString = connectionString;
                        gdgAdapter = factory.CreateDataAdapter();
                        gdsAdapter = factory.CreateDataAdapter();

                    }
                    catch (Exception ex)
                    {
                        if (global != null)
                        {
                            global = null;
                        }
                        Console.WriteLine(ex.Message);
                    }
                }

            }

        }

        public void Update(DataSet ds)
        {

        }

        public List<ScoreItem> GetTop10Scores(string gameName)
        {
            return null;
        }

        public bool KnownPlayer(string name)
        {
            try {
                bool known = false;

                foreach (DataRow row in DsGameScores.Tables["players"].Rows)
                {
                    if ((row["name"]).ToString().ToLower() == name.ToLower()) known = true;
                }
                return known;
            }

            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public bool KnownGame(string game)
        {
            try
            {
                bool known = false;

                foreach (DataRow row in DsGameScores.Tables["games"].Rows)
                {
                    if ((row["name"]).ToString().ToLower() == game.ToLower()) known = true;
                }
                return known;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
    }
}