using Dm.util;
using SqlSugar;
using SqlSugarORM;
using System.Reflection;
using Universal;

namespace DB_Proxima;

public class RemoteToLocal
{

    private SqlSugarCustom? _orm;

    public bool Initialize(string dbName, string password, string host, string username)
    {
        if (this._orm is not null)
            return false;
        _orm = new SqlSugarCustom();
        _orm.CreateDataBase(dbName);
        _orm.Initialize(null);
        string dbString = $"Database={dbName};Password={password};Host={host};Username={username};Persist Security Info=True";
        return _orm.Open(dbString, DbType.PostgreSQL, true);
    }

    public bool CreateTablePM(IDictionary<string, object> data)
    {
        if (this._orm is null)
            return false;

        Dictionary<string, object> ValueSensor = [];
        Dictionary<string, object> StatusSensor = [];
        Dictionary<string, object> leakCheck = [];
        Dictionary<string, object> recipe = [];
        Dictionary<string, object> aoValue = [];

        Dictionary<string, object> ffu = [];
        Dictionary<string, object> mfc = [];
        Dictionary<string, object> gaslineHeater = [];
        Dictionary<string, object> bufferFoup = [];
        Dictionary<string, object> avValue = [];



        foreach (var item in data)
        {
            if (item.Value is not IDictionary<string, object> values)
            {
                switch (item.Key)
                {
                    case string s when s.EndsWith("Enable"):
                        continue;
                    case string s when s.StartsWith("LeakCheck"):
                        leakCheck.Add(item.Key, item.Value);
                        continue;
                    default:
                        recipe.Add(item.Key, item.Value);
                        continue;
                }
            }

            switch (item.Key)
            {
                case "APC":
                case "APCVATGV":
                case "BoatElevatorServo":
                case "BoatRotationServo":
                case "BufferServo":
                case "Shutter":
                    CreateTable(item.Key, values);
                    continue;

                case string s when s.startsWith("Trig"):
                    if (item.Value is IDictionary<string, object> value)
                        aoValue.Add(item.Key, value["AOValue"]);
                    continue;

                case string s when s.startsWith("FS"):
                case string s1 when s1.startsWith("PG"):
                case string s2 when s2.startsWith("PS"):
                case string s3 when s3.startsWith("VG"):
                    if (item.Value is IDictionary<string, object> vss)
                        ValueSensor.Add(item.Key, vss["Value"]);
                    continue;

                case string s when s.StartsWith("Sensor"):
                    if (item.Value is IDictionary<string, object> status)
                        StatusSensor.Add(item.Key, status["Value"]);
                    continue;
                case string s when s.StartsWith("MFC"):
                    if (item.Value is IDictionary<string, object> mfcs)
                        mfcs.Foreach(t => mfc.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                case string s when s.StartsWith("FFU"):
                    if (item.Value is IDictionary<string, object> ffus)
                        ffus.Foreach(t => ffu.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                case string s when s.StartsWith("GaselineHeater"):
                    if (item.Value is IDictionary<string, object> gaslines)
                        gaslines.Foreach(t => gaslineHeater.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                case string s when s.StartsWith("Valve"):
                    if (item.Value is IDictionary<string, object> valves)
                        valves.Foreach(t => avValue.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                default:
                    continue;
            }
        }
        CreateTable("MFC", mfc);
        CreateTable("FFU", ffu);
        CreateTable("Valve", avValue);
        CreateTable("GaselineHeater", gaslineHeater);
        CreateTable("ValueSensor", ValueSensor);
        CreateTable("StatusSensor", StatusSensor);
        CreateTable("LeakCheck", leakCheck);
        CreateTable("AoValue", aoValue);
        CreateTable("Recipe", recipe);



        return true;
    }

    public void CreateTableSystem(Dictionary<string, object> data)
    {
        if (_orm is null)
            return;

        Dictionary<string, object> systemCollection = [];
        Dictionary<string, object> alarmCollection = [];

        _orm.CreateTable<Heater>("Heater");
        _orm.CreateTable<Stocker>("Stocker");
        _orm.CreateTable<LoadPort>("LoadPort");
        _orm.CreateTable<FIMS>("FIMS");

        foreach (var item in data)
        {
            if (item.Value is not IDictionary<string, object> values)
                continue;

            switch (item.Key)
            {
                case "Boat":
                case "CarrierRobot":
                case "Scheduler":
                case "WaferRobot":
                    CreateTable(item.Key, values);
                    continue;

                case "System":
                    if (values is not IDictionary<string, object> systems)
                        continue;
                    foreach (var system in systems)
                    {
                        switch (system.Key)
                        {
                            case string s when s.StartsWith("Heater"):
                                continue;
                            case string s when s.StartsWith("AlarmSignalHeater"):
                                alarmCollection.Add(system.Key, ((IDictionary<string, object>)system.Value)["Value"] ??= false);
                                continue;
                            default:
                                systemCollection.Add(system.Key, system.Value);
                                break;
                        }
                    }
                    continue;
                default:
                    break;
            }

            CreateTable("System", systemCollection);
            CreateTable("AlarmSignalHeater", alarmCollection);
        }
    }

    public bool InsertDataPM(IDictionary<string, object> data, Guid guid, DateTime time)
    {
        if (this._orm is null)
            return false;

        Dictionary<string, object> ValueSensor = [];
        Dictionary<string, object> StatusSensor = [];
        Dictionary<string, object> leakCheck = [];
        Dictionary<string, object> recipe = [];
        Dictionary<string, object> aoValue = [];

        Dictionary<string, object> mfc = [];
        Dictionary<string, object> ffu = [];
        Dictionary<string, object> gaslineHeater = [];
        Dictionary<string, object> bufferFoup = [];
        Dictionary<string, object> avValue = [];
        foreach (var item in data)
        {
            if (item.Value is not IDictionary<string, object> values)
            {
                switch (item.Key)
                {
                    case string s when s.EndsWith("Enable"):
                        continue;
                    case string s when s.StartsWith("LeakCheck"):
                        leakCheck.Add(item.Key, item.Value);
                        continue;
                    default:
                        recipe.Add(item.Key, item.Value);
                        continue;
                }
            }

            switch (item.Key)
            {
                case "APC":
                case "APCVATGV":
                case "BoatElevatorServo":
                case "BoatRotationServo":
                case "BufferServo":
                case "Shutter":
                    InsertData(item.Key, time, guid, values);
                    continue;

                case string s when s.startsWith("Trig"):
                    if (item.Value is IDictionary<string, object> value)
                        aoValue.Add(item.Key, value["AOValue"]);
                    continue;

                case string s when s.startsWith("FS"):
                case string s1 when s1.startsWith("PG"):
                case string s2 when s2.startsWith("PS"):
                case string s3 when s3.startsWith("VG"):
                    if (item.Value is IDictionary<string, object> vss)
                        ValueSensor.Add(item.Key, vss["Value"]);
                    continue;

                //case string s when s.StartsWith("ValveAV"):
                //    _orm.Insert<AvValue>("AvValue", CreateData<AvValue>(guid, time, item));
                //    continue;

                case string s when s.StartsWith("Sensor"):
                    if (item.Value is IDictionary<string, object> status)
                        StatusSensor.Add(item.Key, status["Value"]);
                    continue;

                //case string s when s.StartsWith("FFU"):
                //    _orm.Insert<FFU>("FFU", CreateData<FFU>(guid, time, item));
                //    continue;

                //case string s when s.StartsWith("GaslineHeater"):
                //    _orm.Insert<GaslineHeater>("GaslineHeater", CreateData<GaslineHeater>(guid, time, item));
                //    continue;

                case string s when s.StartsWith("MFC"):
                    if (item.Value is IDictionary<string, object> mfcs)
                        mfcs.Foreach(t => mfc.Add($"{item.Key}_{t.Key}", t.Value));
                    //_orm.Insert<MFC>("MFC", CreateData<MFC>(guid, time, item));
                    continue;

                case string s when s.StartsWith("FFU"):
                    if (item.Value is IDictionary<string, object> ffus)
                        ffus.Foreach(t => ffu.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                case string s when s.StartsWith("GaselineHeater"):
                    if (item.Value is IDictionary<string, object> gaslines)
                        gaslines.Foreach(t => gaslineHeater.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;
                case string s when s.StartsWith("Valve"):
                    if (item.Value is IDictionary<string, object> valves)
                        valves.Foreach(t => avValue.Add($"{item.Key}_{t.Key}", t.Value));
                    continue;

                default:
                    continue;
            }
        }

        InsertData("StatusSensor", time, guid, StatusSensor);
        InsertData("Recipe", time, guid, recipe);
        InsertData("LeakCheck", time, guid, leakCheck);
        InsertData("AoValue", time, guid, aoValue);
        InsertData("MFC", time, guid, mfc);
        InsertData("FFU", time, guid, ffu);
        InsertData("Valve", time, guid, avValue);
        InsertData("GaselineHeater", time, guid, gaslineHeater);

        return true;
    }

    public void InsertDataSystem(Dictionary<string, object> data, Guid guid, DateTime time)
    {
        if (_orm is null)
            return;

        Dictionary<string, object> systemCollection = [];
        Dictionary<string, object> alarmCollection = [];

        foreach (var item in data)
        {
            if (item.Value is not IDictionary<string, object> values)
                continue;

            switch (item.Key)
            {
                case "Boat":
                case "CarrierRobot":
                case "Scheduler":
                case "WaferRobot":
                    InsertData(item.Key, time, guid, values);
                    continue;

                case "System":
                    if (values is not IDictionary<string, object> systems)
                        continue;
                    foreach (var system in systems)
                    {
                        switch (system.Key)
                        {
                            case string s when s.StartsWith("Heater"):
                                _orm.Insert("Heater", CreateData<Heater>(guid, time, item));
                                continue;
                            case string s when s.StartsWith("AlarmSignalHeater"):
                                alarmCollection.Add(system.Key, ((IDictionary<string, object>)system.Value)["Value"] ??= false);
                                continue;
                            default:
                                systemCollection.Add(system.Key, system.Value);
                                break;
                        }
                    }
                    continue;
                case string s when s.StartsWith("Stocker"):
                    _orm.Insert("Stocker", CreateData<Stocker>(guid, time, item));
                    continue;
                case string s when s.StartsWith("LP"):
                    continue;
                case string s when s.StartsWith("FIMS"):
                    continue;

                default:
                    break;
            }
        }

        InsertData("System", time, guid, systemCollection);
        InsertData("AlarmSignalHeater", time, guid, alarmCollection);
    }


    private static T? CreateData<T>(Guid guid, DateTime time, KeyValuePair<string, object> input) where T : class, new()
    {
        if (input.Value is not IDictionary<string, object> values)
            return null;

        T source = new();
        if (source is not IBasicInfo basicInfo)
            return null;

        basicInfo.UID = guid;
        basicInfo.Time = time;
        basicInfo.Name = input.Key;

        DicToClass(ref source, values);
        return source;
    }

    private static void DicToClass<T>(ref T input, IDictionary<string, object> source)
    {
        if (input is null)
            return;

        foreach (PropertyInfo property in input.GetType().GetProperties())
        {
            if (!source.TryGetValue(property.Name, out object? value))
                continue;
            property.SetValue(input, value);
        }
    }

    private void CreateTable(string tableName, IDictionary<string, object> source)
    {
        if (_orm is null || _orm.Client is null || source is null)
            return;

        DynamicProperyBuilder builder = _orm.Client.DynamicBuilder().CreateClass(tableName, new SugarTable());
        builder.CreateProperty("UID", typeof(Guid), new SugarColumn() { });
        builder.CreateProperty("time", typeof(DateTime), new SugarColumn() { });
        source.Foreach(t => builder.CreateProperty(t.Key.Replace('.', '_'), t.Value?.GetType(), new SugarColumn() { IsNullable = true }));

        _orm.Client.CodeFirst.InitTables(builder.BuilderType());
    }

    public void CreateTableRaw(string tableName, IDictionary<string, object> source)
    {
        if (_orm is null || _orm.Client is null || source is null)
            return;

        DynamicProperyBuilder builder = _orm.Client.DynamicBuilder().CreateClass(tableName, new SugarTable());
        //builder.CreateProperty("UID", typeof(Guid), new SugarColumn() { });
        //builder.CreateProperty("time", typeof(DateTime), new SugarColumn() { });
        source.Foreach(t => builder.CreateProperty(t.Key.Replace('.', '_'), t.Value?.GetType(), new SugarColumn() { IsNullable = true }));

        _orm.Client.CodeFirst.InitTables(builder.BuilderType());
    }

    public void CreateTableRaw(string tableName, IList<DbColumnInfo> source)
    {
        if (_orm is null || _orm.Client is null || source is null)
            return;

        DynamicProperyBuilder builder = _orm.Client.DynamicBuilder().CreateClass(tableName, new SugarTable());

        foreach (var t in source)
        {


            Type type = t.DataType switch
            {
                "float4" => typeof(float),
                "float8"=>typeof(double),
                "bool" => typeof(bool),
                "int4" => typeof(int),
                "int8" => typeof(long),
                "text" => typeof(char[]),
                "varchar"=> typeof(char[]),
                "timestamp" => typeof(DateTime),
                "time" => typeof(TimeSpan),
                _ => typeof(object),
            };


            if (t.DataType == "text"||t.DataType== "varchar")
                builder.CreateProperty(t.DbColumnName.Replace('.', '_'), type, new SugarColumn() { IsNullable = true, Length = 1000 });
            else
                builder.CreateProperty(t.DbColumnName.Replace('.', '_'), type, new SugarColumn() { IsNullable = true });
            //$"\"{t.DbColumnName.Replace('.', '_')}\""
        }

        //source.Foreach(t => builder.CreateProperty(t.DbColumnName.Replace('.', '_'), t.PropertyType, new SugarColumn() { IsNullable = true }));

        _orm.Client.CodeFirst.InitTables(builder.BuilderType());
    }

    private int InsertData(string tableName, DateTime dateTime, Guid guid, IDictionary<string, object> source)
    {
        if (_orm is null || _orm.Client is null)
            return 0;

        Dictionary<string, object> column = new() { ["UID"] = guid, ["time"] = dateTime, };
        source.Foreach(t => column.Add(t.Key, t.Value));

        return _orm.Client.Insertable(column).AS(tableName).ExecuteCommand();
    }

    public int InsertDataRaw(string tableName, IDictionary<string, object> source)
    {
        if (_orm is null || _orm.Client is null)
            return 0;
        _orm.Client.CurrentConnectionConfig.MoreSettings ??= new();
        _orm.Client.CurrentConnectionConfig.MoreSettings.IsCorrectErrorSqlParameterName = false;

        Dictionary<string, object> column = [];
        //source.Foreach(t => column.Add(t.Key.Replace('.', '_'), t.Value));
        foreach (var t in source)
        {
            if (t.Value is null)
            {
                continue;
            }
            if (t.Key.contains("-"))
                _orm.Client.CurrentConnectionConfig.MoreSettings.IsCorrectErrorSqlParameterName = true;
            column.Add(t.Key.Replace('.', '_'), t.Value);

        }


        return _orm.Client.Insertable(column).AS(tableName).ExecuteCommand();
    }
}