using DTS.Common.Enums.Sensors;
using DTS.Common.Interface.Sensors;
using DTS.Common.Storage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;

namespace DTS.SensorDB
{
    /// <summary>
    /// represents a change to the offset tolerance in the db
    /// it implements ISensorChange so can be easily commited and read
    /// </summary>
    public class OffsetToleranceChange : IOffsetToleranceChange
    {
        public int SensorId { get; set; }
        public double LowMvValue
        {
            get => double.Parse(Value1, NumberStyles.Any, CultureInfo.InvariantCulture);
            set => Value1 = value.ToString(CultureInfo.InvariantCulture);
        }

        public double HighMvValue
        {
            get => double.Parse(Value2, NumberStyles.Any, CultureInfo.InvariantCulture);
            set => Value2 = value.ToString(CultureInfo.InvariantCulture);
        }

        public double LowEUValue
        {
            get => double.Parse(Value3, NumberStyles.Any, CultureInfo.InvariantCulture);
            set => Value3 = value.ToString(CultureInfo.InvariantCulture);
        }

        public double HighEUValue
        {
            get => double.Parse(Value4, NumberStyles.Any, CultureInfo.InvariantCulture);
            set => Value4 = value.ToString(CultureInfo.InvariantCulture);
        }

        public SensorChangeTypes ChangeType => SensorChangeTypes.OffsetTolerance;

        public string Value1 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);

        public string Value2 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);

        public string Value3 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);

        public string Value4 { get; private set; } = 0D.ToString(CultureInfo.InvariantCulture);

        public const int NEW_RECORD = -1;
        public int RecordId { get; set; } = NEW_RECORD;

        public DateTime TimeStamp { get; set; }

        public string UserName { get; set; }
        public OffsetToleranceChange() { }

        public OffsetToleranceChange(string value1, string value2, string value3, string value4)
        {
            Value1 = value1;
            Value2 = value2;
            Value3 = value3;
            Value4 = value4;
        }
    }
    /// <summary>
    /// this class simplifies sensor change histories and types in the db
    /// </summary>
    public abstract class SensorChangeTypeHelper
    {
        private static Dictionary<SensorChangeTypes, int> _mappingCache = null;
        private static object MyLock = new object();

        public static void Commit(ISensorData sensor, ISensorChange[] changes)
        {
            using (var sql = DbOperations.GetSQLCommand(true))
            {
                try
                {
                    sql.CommandType = CommandType.StoredProcedure;
                    sql.CommandText = "sp_SensorsChangeHistoryDelete";
                    sql.Parameters.Add(new SqlParameter("@RecordId", SqlDbType.Int) { Value = DBNull.Value });
                    sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = sensor.DatabaseId });
                    sql.ExecuteNonQuery();
                }
                finally
                {
                    sql.Connection.Dispose();
                }
            }

            if (changes.Any())
            {
                foreach (var change in changes)
                {
                    using (var sql = DbOperations.GetSQLCommand(true))
                    {
                        try
                        {
                            sql.CommandType = CommandType.StoredProcedure;
                            sql.CommandText = "sp_SensorsChangeHistoryInsert";

                            sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int)
                            { Value = sensor.DatabaseId });
                            sql.Parameters.Add(new SqlParameter("@ChangeType", SqlDbType.Int)
                            { Value = GetAllSensorChangeTypes()[change.ChangeType] });
                            sql.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.DateTime)
                            { Value = change.TimeStamp });
                            sql.Parameters.Add(new SqlParameter("@Username", SqlDbType.NVarChar)
                            { Value = change.UserName });
                            sql.Parameters.Add(new SqlParameter("@Value1", SqlDbType.NVarChar) { Value = change.Value1 });
                            sql.Parameters.Add(new SqlParameter("@Value2", SqlDbType.NVarChar) { Value = change.Value2 });
                            sql.Parameters.Add(new SqlParameter("@Value3", SqlDbType.NVarChar) { Value = change.Value3 });
                            sql.Parameters.Add(new SqlParameter("@Value4", SqlDbType.NVarChar) { Value = change.Value4 });

                            var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
                            { Direction = ParameterDirection.Output };
                            sql.Parameters.Add(errorNumber);

                            var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
                            { Direction = ParameterDirection.Output };
                            sql.Parameters.Add(errorMessage);

                            var new_id = new SqlParameter("@new_id", SqlDbType.Int)
                            { Direction = ParameterDirection.Output };
                            sql.Parameters.Add(new_id);

                            sql.ExecuteNonQuery();

                            if (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                            {
                                if (0 != Convert.ToInt32(errorNumber.Value))
                                {
                                    throw new Exception((string)errorMessage.Value);
                                }
                            }
                        }
                        finally
                        {
                            sql.Connection.Dispose();
                        }
                    }
                }
            }
        }
        /// <summary>
        /// writes a single change to the db
        /// </summary>
        /// <param name="change"></param>
        public static void Commit(ISensorChange change)
        {
            //can't commit when id < 0...
            if (change.SensorId < 0)
            {
                return;
            }
            using (var sql = DbOperations.GetSQLCommand(true))
            {
                try
                {
                    sql.CommandType = CommandType.StoredProcedure;
                    sql.CommandText = "sp_SensorsChangeHistoryInsert";
                    sql.Parameters.Add(new SqlParameter("@SensorId", SqlDbType.Int) { Value = change.SensorId });
                    sql.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar) { Value = change.UserName });
                    sql.Parameters.Add(new SqlParameter("@Timestamp", SqlDbType.DateTime) { Value = change.TimeStamp });
                    sql.Parameters.Add(new SqlParameter("@ChangeType", SqlDbType.Int) { Value = GetAllSensorChangeTypes()[SensorChangeTypes.OffsetTolerance] });
                    sql.Parameters.Add(new SqlParameter("@Value1", SqlDbType.NVarChar) { Value = change.Value1 });
                    sql.Parameters.Add(new SqlParameter("@Value2", SqlDbType.NVarChar) { Value = change.Value2 });
                    sql.Parameters.Add(new SqlParameter("@Value3", SqlDbType.NVarChar) { Value = change.Value3 });
                    sql.Parameters.Add(new SqlParameter("@Value4", SqlDbType.NVarChar) { Value = change.Value4 });

                    var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int)
                    { Direction = ParameterDirection.Output };
                    sql.Parameters.Add(errorNumber);

                    var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255)
                    { Direction = ParameterDirection.Output };
                    sql.Parameters.Add(errorMessage);

                    var newId = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                    sql.Parameters.Add(newId);

                    sql.ExecuteNonQuery();

                    if (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                    {
                        if (0 != Convert.ToInt32(errorNumber.Value))
                        {
                            throw new Exception((string)errorMessage.Value);
                        }
                    }
                }
                finally
                {
                    sql.Connection.Dispose();
                }
            }
        }
        /// <summary>
        /// returns all sensor change history for sensor
        /// </summary>
        /// <param name="sensor"></param>
        /// <returns></returns>
        public static ISensorChange[] GetAllSensorChanges(ISensorData sensor)
        {
            var list = new List<ISensorChange>();
            var lookup = SensorChangeTypeHelper.GetAllSensorChangeTypes();
            var intToType = new Dictionary<int, SensorChangeTypes>();
            using (var e = lookup.GetEnumerator())
            {
                while (e.MoveNext())
                {
                    intToType[e.Current.Value] = e.Current.Key;
                }
            }
            using (var sql = DbOperations.GetSQLCommand(true))
            {
                try
                {
                    sql.CommandType = CommandType.StoredProcedure;
                    sql.CommandText = "sp_SensorsChangeHistoryGet";
                    sql.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = sensor.DatabaseId });
                    sql.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar)
                    { Value = sensor.SerialNumber });

                    var reader = sql.ExecuteReader();
                    while (reader.Read())
                    {
                        var recordId = Convert.ToInt32(reader["RecordId"]);
                        var sensorId = Convert.ToInt32(reader["SensorId"]);
                        var ichangeType = Convert.ToInt32(reader["ChangeType"]);
                        var userName = (string)reader["UserName"];
                        var timeStamp = Convert.ToDateTime(reader["Timestamp"]);
                        var value1 = (string)reader["Value1"];
                        var value2 = (string)reader["Value2"];
                        var value3 = (string)reader["Value3"];
                        var value4 = (string)reader["Value4"];
                        if (!intToType.ContainsKey(ichangeType))
                        {
                            throw new NotImplementedException($"unknown change type: {ichangeType}");
                        }

                        switch (intToType[ichangeType])
                        {
                            case SensorChangeTypes.OffsetTolerance:
                                list.Add(new OffsetToleranceChange(value1, value2, value3, value4)
                                {
                                    RecordId = recordId,
                                    SensorId = sensorId,
                                    TimeStamp = timeStamp,
                                    UserName = userName
                                });
                                break;
                            default:
                                throw new NotImplementedException(
                                    $"Unknown change type: {intToType[ichangeType].ToString()}");
                        }
                    }
                }
                finally
                {
                    sql.Connection.Dispose();
                }
            }

            return list.ToArray();
        }
        /// <summary>
        /// returns a lookup table keyed by change type with corresponding change id in db
        /// this is designed to reduce db hits when there are many sensors to commit by allowing
        /// caching the sensor change type lookup
        /// </summary>
        /// <param name="UseCache"></param>
        /// <returns></returns>
        public static Dictionary<SensorChangeTypes, int> GetAllSensorChangeTypes(bool UseCache = true)
        {
            lock (MyLock)
            {
                if (!UseCache || null == _mappingCache)
                {
                    _mappingCache = new Dictionary<SensorChangeTypes, int>();
                    using (var cmd = DbOperations.GetSQLCommand(true))
                    {
                        try
                        {
                            cmd.CommandText = "sp_SensorsChangeTypesGet";
                            cmd.CommandType = CommandType.StoredProcedure;

                            var reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                var id = Convert.ToInt32(reader["Id"]);
                                var sType = Convert.ToString(reader["Name"]);
                                if (Enum.TryParse(sType, out SensorChangeTypes changeType))
                                {
                                    _mappingCache[changeType] = id;
                                }
                            }
                        }
                        finally
                        {
                            cmd.Connection.Dispose();
                        }
                    }
                }
                return _mappingCache;
            }
        }
    }
}