// Copyright © Selmo Technology GmbH. All Rights Reserved

using Microsoft.EntityFrameworkCore;
using SelmoStudio.Hmi.Plugin.Services;
using System.Data;
using System.Text;

namespace SelmoStudio.Hmi.Server.Db.Context
{
    public class ProcessValueDbContext : DbContext, IProcessValueDbContext
    {
        public ProcessValueDbContext(DbContextOptions<ProcessValueDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }

        #region Create + Insert Process Values

        public void ExecuteCreateHistoryTable(int groupId, List<Guid> tableColumns)
        {
            var tableName = SqlProcessValueHistoryQueryHelper.CreateTableNameForHour(DateTime.Now, groupId);

            var cleanColumns = CreateColumsForCreateQuery(tableColumns, true);

            var query = $"CREATE TABLE {tableName}({cleanColumns});";

            try
            {
                Database.ExecuteSqlRaw($"DROP TABLE IF EXISTS {tableName};");
                Database.ExecuteSqlRaw(query);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion Create + Insert Process Values

        #region Select Process Values

        public List<(DateTimeOffset timestamp, float[] values)> GetProcessValues(int groupId, List<Guid> tableColumns)
        {
            var tableName = SqlProcessValueHistoryQueryHelper.CreateTableNameForHour(DateTime.Now, groupId);
            var columnNames = CreateColumnNames(tableColumns);

            var entries = new List<(DateTimeOffset timestamp, float[] values)>();

            using (var cmd = Database.GetDbConnection().CreateCommand())
            {
                bool wasOpen = cmd.Connection.State == ConnectionState.Open;
                if (!wasOpen) cmd.Connection.Open();
                try
                {
                    cmd.CommandText = $"SELECT * FROM {tableName};";
                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        var timestampAsText = reader[TimestampColumnName].ToString();
                        var entry = new List<float>();
                        foreach (var col in columnNames)
                        {
                            var valueAsText = reader[col].ToString();
                            entry.Add(float.Parse(valueAsText));
                        }

                        (DateTimeOffset timestamp, float[] values) valueToadd = (DateTimeOffset.Parse(timestampAsText), entry.ToArray());
                        entries.Add(valueToadd);
                    }
                }
                finally
                {
                    if (!wasOpen) cmd.Connection.Close();
                }
            }

            return entries;
        }

        private List<string> CreateColumnNames(List<Guid> tableColumns)
        {
            List<string> names = new();
            foreach (var col in tableColumns)
            {
                names.Add(ConvertToColumnName(col));
            }
            return names;
        }

        #endregion Select Process Values

        private const string PrefixColumn = "C_";

        private string ConvertToColumnName(Guid guid)
        {
            var cleanName = guid.ToString().Replace("-", string.Empty);
            return $"{PrefixColumn}{cleanName}";
        }

        private const string TimestampColumnName = "timestamp";

        private string CreateColumsForCreateQuery(List<Guid> tableColumns, bool includingPk)
        {
            StringBuilder columns = new();

            if (includingPk)
            {
                columns.Append("id INTEGER PRIMARY KEY AUTOINCREMENT,");
            }

            columns.Append($"{TimestampColumnName} DATETIME DEFAULT CURRENT_TIMESTAMP,");

            foreach (var column in tableColumns)
            {
                columns.Append($"{ConvertToColumnName(column)} REAL,");
            }
            var cleanColumns = columns.ToString().TrimEnd(',');
            return cleanColumns;
        }

        private string CreateColumsForInsertQuery(List<Guid> tableColumns)
        {
            StringBuilder columns = new();

            foreach (var column in tableColumns)
            {
                columns.Append($"{ConvertToColumnName(column)},");
            }
            var cleanColumns = columns.ToString().TrimEnd(',');
            return cleanColumns;
        }

        //private string CreateTableNameForDay(DateTime dateTime)
        //{
        //    var tableName = $"ProcessValues{dateTime.ToString("yyMMdd")}";

        //    return tableName;
        //}

        public void ExecuteInsertTestData(List<Guid> tableColumns)
        {
            // TODO !!! call ExecuteInsertProcessValues() to PREVENT SQL INJECTION

            var tableName = "";// CreateTableNameForDay(DateTime.Now);

            var cleanColumnsWithourPk = CreateColumsForInsertQuery(tableColumns);

            var insertData = SimulatedValueHelper.CreateRandomValuesCommaSeperated(tableColumns.Count, ',', -12345, 12345);

            var query = $"INSERT INTO {tableName}({cleanColumnsWithourPk})VALUES({insertData});";

            try
            {
                Database.ExecuteSqlRaw(query);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void ExecuteInsertData(int groupId, List<Guid> tableColumns, string insertData)
        {
            // TODO !!! call ExecuteInsertProcessValues() to PREVENT SQL INJECTION

            var tableName = SqlProcessValueHistoryQueryHelper.CreateTableNameForHour(DateTime.Now, groupId);

            var cleanColumnsWithourPk = CreateColumsForInsertQuery(tableColumns);

            var query = $"INSERT INTO {tableName}({cleanColumnsWithourPk})VALUES({insertData});";

            Database.ExecuteSqlRaw(query);
        }
    }
}