using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ServiceShared.Database.Controllers
{
    public class Traces
    {
        private DbContext _DbContext;

        /// <summary>
        /// Constructor Traces database controller
        /// </summary>
        /// <param name="dbContext">DbContext</param>
        public Traces(DbContext dbContext)
        {
            this._DbContext = dbContext;
        }

        /// <summary>
        /// Creates Traces log in the database
        /// </summary>
        /// <param name="results">Traces object</param>
        /// <returns>returns true if success</returns>
        public bool Create(Models.Database.Traces traces)
        {
            bool result = false;

            try
            {
                if (traces != null &&
                   !string.IsNullOrEmpty(traces.PGS) &&
                   traces.PGS.Length == 128 &&
                   !string.IsNullOrEmpty(traces.UDID) &&
                   (traces.UDID.Length >= 10 && traces.UDID.Length <= 64) &&
                   !string.IsNullOrEmpty(traces.Message))
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"insert into traces (pgs, 
                                                                 udid, 
                                                                 message, 
                                                                 trace_type)

                                                        values (@pgs, 
                                                                @udid, 
                                                                @message,  
                                                                @trace_type)";

                        cmd.Parameters.Add(new MySqlParameter("pgs", traces.PGS));
                        cmd.Parameters.Add(new MySqlParameter("udid", traces.UDID));
                        cmd.Parameters.Add(new MySqlParameter("message", traces.Message));
                        cmd.Parameters.Add(new MySqlParameter("trace_type", traces.TraceType.ToString()));

                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + traces.PGS +
                                                    ", @udid = " + traces.UDID +
                                                    ", @message = " + traces.Message +
                                                    ", @trace_type = " + traces.TraceType.ToString(),
                                                    Log.Types.TRACE);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "Create(Models.Traces)");
            }

            return result;
        }

        /// <summary>
        /// Creates Traces log in the database
        /// </summary>
        /// <param name="udid">udif of device</param>
        /// <returns>returns true if success</returns>
        public bool DeleteDevice(string udid)
        {
            bool result = false;

            try
            {
                if (!string.IsNullOrEmpty(udid) && udid.Length >= 10 && udid.Length <= 64)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"delete from traces where udid = @udid";
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));
                        cmd.ExecuteNonQuery();
                        result = true;
                        client.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "DeleteDevice(string)");
            }

            return result;
        }

        /// <summary>
        /// Returns Traces by pgs and udid from the database
        /// </summary>
        /// <param name="pgs">pgs</param>
        /// <param name="udid">udid</param>
        /// <returns>returns traces object from the database</returns>
        public List<Models.Database.Traces> GetTraces(string pgs, string udid)
        {
            List<Models.Database.Traces> result = null;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"select
                                                t.id,
                                                t.pgs,
                                                t.udid,
                                                t.message,
                                                t.trace_type,
                                                t.created
                                            from
                                                traces t
                                            where
                                                t.pgs = @pgs and
                                                t.udid = @udid
                                            order by
                                                t.created desc";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
                        cmd.Parameters.Add(new MySqlParameter("udid", udid));

                        MySqlDataReader reader = cmd.ExecuteReader();

                        if (reader != null && reader.HasRows)
                        {
                            result = new List<Models.Database.Traces>();

                            while (reader.Read())
                            {
                                Models.Database.Traces dbTraces = this.ReadTraces(reader);

                                if (dbTraces != null)
                                {
                                    result.Add(dbTraces);
                                }
                            }
                        }

                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs +
                                                    ", @udid = " + udid, Log.Types.TRACE);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(string, string)");
            }

            return result;
        }

        /// <summary>
        /// Returns Traces by pgs from the database
        /// </summary>
        /// <param name="pgs">pgs</param>
        /// <returns>returns traces object from the database</returns>
        public List<Models.Database.Traces> GetTraces(string pgs)
        {
            List<Models.Database.Traces> result = null;

            try
            {
                if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
                {
                    using (MySqlConnection client = this._DbContext.CreateConnection())
                    {
                        client.Open();
                        MySqlCommand cmd = client.CreateCommand();
                        cmd.CommandText = @"select
                                                t.id,
                                                t.pgs,
                                                t.udid,
                                                t.message,
                                                t.trace_type,
                                                t.created
                                            from
                                                traces t
                                            where
                                                t.pgs = @pgs
                                            order by
                                                t.created desc";

                        cmd.Parameters.Add(new MySqlParameter("pgs", pgs));

                        MySqlDataReader reader = cmd.ExecuteReader();

                        if (reader != null && reader.HasRows)
                        {
                            result = new List<Models.Database.Traces>();

                            while (reader.Read())
                            {
                                Models.Database.Traces dbTraces = this.ReadTraces(reader);

                                if (dbTraces != null)
                                {
                                    result.Add(dbTraces);
                                }
                            }
                        }

                        client.Close();

                        Log.Write(cmd.CommandText + ", @pgs = " + pgs, Log.Types.TRACE);
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(string)");
            }

            return result;
        }

        /// <summary>
        /// Returns Traces from the database for date range
        /// </summary>
        /// <returns>returns traces object from the database</returns>
        public List<Models.Database.Traces> GetTraces(DateTime from, DateTime? to = null)
        {
            List<Models.Database.Traces> result = null;

            try
            {
                using (MySqlConnection client = this._DbContext.CreateConnection())
                {
                    client.Open();
                    MySqlCommand cmd = client.CreateCommand();
                    cmd.CommandText = @"select
                                                t.id,
                                                t.pgs,
                                                t.udid,
                                                t.message,
                                                t.trace_type,
                                                t.created
                                        from
                                                traces t
                                        where
                                                t.created >= @from " + (to.HasValue ? " and t.created <= @to" : "") + 
                                        " order by " +
                                        "       t.created desc";

                    cmd.Parameters.Add(new MySqlParameter("from", from.ToString("yyyy-MM-dd HH:mm:ii")));

                    if(to.HasValue)
                    {
                        cmd.Parameters.Add(new MySqlParameter("to", to.Value.ToString("yyyy-MM-dd HH:mm:ii")));
                    }

                    MySqlDataReader reader = cmd.ExecuteReader();

                    if (reader != null && reader.HasRows)
                    {
                        result = new List<Models.Database.Traces>();

                        while (reader.Read())
                        {
                            Models.Database.Traces dbTraces = this.ReadTraces(reader);

                            if (dbTraces != null)
                            {
                                result.Add(dbTraces);
                            }
                        }
                    }

                    client.Close();

                    Log.Write(cmd.CommandText + ", @from = " + from.ToString("yyyy-MM-dd HH:mm:ii") +
                                                (to.HasValue ? " @to = " + to.Value.ToString("yyyy-MM-dd HH:mm:ii") : ""), Log.Types.LOG);
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(DateTime, DateTime)");
            }

            return result;
        }

        /// <summary>
        /// Reads traces from mysql data reader
        /// </summary>
        /// <param name="reader">MySqlDataReader with current connection context</param>
        /// <returns>Returns Traces object</returns>
        private Models.Database.Traces ReadTraces(MySqlDataReader reader)
        {
            Models.Database.Traces result = null;

            try
            {
                if (reader["pgs"] != null && reader["pgs"] != DBNull.Value)
                {
                    result = new Models.Database.Traces();
                    result.Id = (long)reader["id"];
                    result.PGS = (string)reader["pgs"];
                    result.UDID = (string)reader["udid"];
                    result.Message = (string)reader["message"];

                    string trace_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);
                    Log.Types traceType = Log.Types.TRACE;

                    if (!string.IsNullOrEmpty(trace_type) && Enum.TryParse(trace_type, out traceType) && traceType != Log.Types.TRACE)
                    {
                        result.TraceType = traceType;
                    }

                    result.Created = (DateTime)reader["created"];
                }
            }
            catch (Exception ex)
            {
                Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "ReadTraces(MySqlDataReader)");
            }

            return result;
        }
    }
}