using System;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using classes;
using Model;
using MySqlConnector;
/// <summary>
/// Graph generating service. This is linked to the format on the Frontend dont not change these unless the formats have been changed on the frontend
/// </summary>
namespace Services
{
    public static class GraphService
    {
        public static List<Graph> LoadGraphSettings(MySqlConnection con)
        {
            MySqlDataReader reader = null;
            List<Graph> graphs = new List<Graph> { };
            string statement = "";

            try
            {
                statement = "SELECT * FROM graph WHERE blnActive = 1";
                using var cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = statement;
                cmd.ExecuteNonQuery();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        graphs.Add(new Graph
                        {
                            GraphType = reader["strGraphType"].ToString(),
                            GraphTitle = reader["strGraphTitle"].ToString(),
                            GraphQuery = reader["strGraphData"].ToString(),
                            GraphX = reader["strXColumn"].ToString(),
                            GraphY = reader["strYColumn"].ToString()
                        });
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return graphs;
        }
        //Generating piechart
        public static List<Object> GetPieChart(MySqlConnection con, string title, string startDate = null, string endDate = null)
        {
            string statement = "db::PieChart";
            MySqlDataReader reader = null;
            string graphTitle = $"{Helper.CleanInput(title)}";
            List<Object> pieChartObject = new List<Object> { };
            string query = "";
            Dictionary<string, string> dict = new Dictionary<string, string>();
            Exception ex = null;

            try
            {
                using var cmd = new MySqlCommand();
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {

                    if (Helper.IsValidDate(startDate) && Helper.IsValidDate(endDate))
                    {
                        cmd.Connection = con;
                        statement = $"SELECT * FROM graph WHERE strGraphTitle = '{graphTitle}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                query = reader.GetString(3);
                            }
                            query = query.Replace("{startDate}", startDate);
                            query = query.Replace("{endDate}", endDate);
                            reader.Close();

                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        ex = new InvalidDataException("Invalid Dates supplied");
                    }

                }

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    int columnCount = reader.FieldCount;
                    while (reader.Read())
                    {
                        for (int i = 0; i < columnCount; i++)
                        {
                            dict.Add(reader.GetName(i), reader.GetValue(i).ToString());
                        }
                        var dbRow = new ExpandoObject() as IDictionary<string, Object>;
                        foreach (var entry in dict)
                        {
                            dbRow.Add(entry.Key, entry.Value);
                        }
                        dict.Clear();
                        pieChartObject.Add(dbRow);
                        dbRow = null;
                    }
                }
            }
            catch
            {
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
            return pieChartObject;

        }

        //populate the infrigements table
        public static List<object> GetTable(MySqlConnection con, string graphTitle, string startDate = null, string endDate = null)
        {
            string statement = "db::TablePayments ";
            List<InfridgmentRow> rows = new List<InfridgmentRow> { };
            MySqlDataReader reader = null;
            string query = "";
            List<object> list = new List<object> { };
            Dictionary<string, string> dict = new Dictionary<string, string>();


            try
            {
                using var cmd = new MySqlCommand();

                if ((!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)))
                {

                    if (Helper.IsValidDate(startDate) && Helper.IsValidDate(endDate))
                    {
                        cmd.Connection = con;
                        statement = $"SELECT * FROM graph WHERE strGraphTitle = '{graphTitle}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                query = reader.GetString(3);
                            }
                            query = query.Replace("{startDate}", startDate);
                            query = query.Replace("{endDate}", endDate);
                            reader.Close();

                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                        }

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            int columnCount = reader.FieldCount;

                            while (reader.Read())
                            {
                                for (int i = 0; i < columnCount; i++)
                                {
                                    dict.Add(reader.GetName(i), reader.GetValue(i).ToString());
                                }
                                var dbRow = new ExpandoObject() as IDictionary<string, Object>;
                                foreach (var entry in dict)
                                {
                                    dbRow.Add(entry.Key, entry.Value);
                                }
                                dict.Clear();
                                list.Add(dbRow);
                                dbRow = null;

                            }

                        }
                    }
                }
                return list;
            }
            catch (Exception)
            {
                throw new Exception($"Error occurred while generating {graphTitle}");
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }

        public static Object BarGraphFamily(MySqlConnection con, Graph graph, string startDate = null, string endDate = null)
        {
            string statement = "db::BarGraphFamily";
            MySqlDataReader reader = null;
            string graphTitle = $"{Helper.CleanInput(graph.GraphTitle)}";
            List<object> results = new List<object>();
            Exception functionErrors = null;
            string query = "";
            Dictionary<string, string> dict = new Dictionary<string, string>();

            try
            {
                using var cmd = new MySqlCommand();

                if ((!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)))
                {

                    if (Helper.IsValidDate(startDate) && Helper.IsValidDate(endDate))
                    {
                        cmd.Connection = con;
                        statement = $"SELECT * FROM graph WHERE strGraphTitle = '{graphTitle}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                query = reader.GetString(3);
                            }
                            query = query.Replace("{startDate}", startDate);
                            query = query.Replace("{endDate}", endDate);
                            reader.Close();

                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                        }

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int columnCount = reader.FieldCount;
                                for (int i = 0; i < columnCount; i++)
                                {
                                    dict.Add(reader.GetName(i), reader.GetValue(i).ToString());
                                }
                                var dbRow = new ExpandoObject() as IDictionary<string, Object>;
                                foreach (var entry in dict)
                                {
                                    dbRow.Add(entry.Key, entry.Value);
                                }
                                dict.Clear();
                                results.Add(dbRow);
                                dbRow = null;

                            }
                        }
                    }
                    else
                    {
                        functionErrors = new Exception("Invalid dates entered");
                    }
                }

                return results;

            }
            catch (Exception ex)
            {
                if (functionErrors != null) throw functionErrors;
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }

        }

        //Computes infringements on payment status
        public static Object TotalCounter(MySqlConnection con, string title, string startDate = null, string endDate = null)
        {
            string statement = "";
            Exception ex = null;
            MySqlDataReader reader = null;
            int infridgementCount = 0;
            string graphTitle = Helper.CleanInput(title);
            Dictionary<string, string> dict = new Dictionary<string, string>();

            string query = "";

            try
            {
                using var cmd = new MySqlCommand();
                if ((!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)))
                {
                    startDate = Helper.CleanInput(startDate);
                    endDate = Helper.CleanInput(endDate);

                    if (Helper.IsValidDate(startDate) && Helper.IsValidDate(endDate))
                    {
                        cmd.Connection = con;
                        statement = $"SELECT * FROM graph WHERE strGraphTitle = '{graphTitle}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                query = reader.GetString(3);
                            }
                            query = query.Replace("{startDate}", startDate);
                            query = query.Replace("{endDate}", endDate);
                            reader.Close();

                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        ex = new Exception("Invalid dates entered");
                    }

                }

                reader = cmd.ExecuteReader();
                int columnCount = reader.FieldCount;

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        infridgementCount = reader.GetInt32(0);
                        if (columnCount > 1)
                        {
                            dict.Add(reader.GetValue(1).ToString(), reader.GetValue(0).ToString()); //key value pair for the results
                        }
                    }
                    if (dict.Count > 0) //query was a group by count
                    {
                        return dict;
                    }
                }

            }
            catch (Exception exception)
            {
                if (ex != null) throw ex;
                throw (exception);
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return infridgementCount;
        }

        public static List<NedbankTransaction> GetNedBankTable(MySqlConnection con)
        {
            string statement = "";
            MySqlDataReader reader = null;

            List<NedbankTransaction> transactions = new List<NedbankTransaction> { };
            try
            {

                statement = "SELECT transaction.TransactionID, transaction.refPaymentProviderID, transaction.strAmount, transaction.strRecieptNumber, transaction.strStatus, transaction.dtPaid,  transaction.dtLastEdit FROM transaction";
                using var cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = statement;
                cmd.ExecuteNonQuery();

                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        transactions.Add(new NedbankTransaction
                        {
                            TransactionID = Convert.ToInt32(reader["TransactionID"]),
                            iVeri_TX_ID = reader["refPaymentProviderID"].ToString(),
                            Amount = Convert.ToInt32(reader["strAmount"]),
                            Receipt_Number = reader["strRecieptNumber"].ToString(),
                            Status = reader["strStatus"].ToString(),
                            Date_Received = reader["dtPaid"].ToString(),
                            Date_Response = reader["dtLastEdit"].ToString(),
                            Number_of_Infringements = Convert.ToInt32(reader["TransactionID"])

                        });

                    }
                    reader.Close();
                    foreach (var transaction in transactions)
                    {
                        statement = $"SELECT COUNT(InfringementID) from infringement WHERE refTrasactionID = {transaction.TransactionID}";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                transaction.Number_of_Infringements = reader.GetInt32(0);
                            }
                            reader.Close();
                        }
                    }
                    return transactions;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null) reader.Close();
            }

            return transactions;
        }

    }
}