using DataservCycle;
using DataservEngine3;
using DataservEngine3.Components.Communications;
using DataservEngine3.Components.Tags;
using DataservEngine3.DataservData;
using DataservPlugin;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Xml;

[PluginData(
Name = "Carrier-MXF-MES",
Author = "Tim Dekker",
Description =
    "Carrier MX uses Database Stored Procedures for Interaction - See Documentation @ `H:\\Appliance\\Customer\\Carrier\\Carrier Mexico Sanata Catarina\\Dataserv Integration`."
)]
public class CarrierMesPlantF : AbstractPlugin {

    public override void OnEngineLoaded() {
        LoadEnvironmentItems();
    }
    private static int _carrierMesProductionMode;
    private static DataTable resultsTable;
    private static string mesConnectionString = "database=ATOM_CMXF_MES;server=ATOM-MES;Pwd=Ta8k7E&q_V7j;User ID=bumpTestAC;";
    private bool _debug;
    private const string DefaultDataCatalog = "ALL";
    private int _assetId = 15;
    private static string _carrierMesAppConfigPath = @"c:\siq\App.Config.XML";
    private Dictionary<string, string> outputStepLookup = new Dictionary<string, string>
    {
        {
            "FinalFinalEvacTime_AC1", "1.02"
        },
        {
            "FinalFinalEvacLevelHighSide_AC1", "1.02"
        },
        {
            "FinalVacCheckTime_AC1", "1.03"
        },
        {
            "FinalVacCheckLevelHighSide_AC1", "1.03"
        },
        {
            "FinalRejectEvacTime_AC1", "1.04"
        },
        {
            "FinalRejectEvacLevelHighSide_AC1", "1.04"
        },
        {
            "FinalFill1Time_AC1", "1.12"
        },
        {
            "FinalFill1Quantity_AC1", "1.12"
        },
        {
            "FinalFill1FlowRate_AC1", "1.12"
        },
        {
            "FinalFill2Type_AC1", "1.12"
        },
        {
            "FinalFill1Pressure_AC1", "1.12"
        },
        {
            "FinalFill2Quantity_AC1", "1.12"
        },
        {
            "FinalFill2FlowRate_AC1", "1.12"
        },
        {
            "FinalFill2Time_AC1", "1.12"
        },
        {
            "FinalReclaimTime_AC1", "1.15"
        },
        {
            "FinalHoseEvacTime_AC1", "1.16"
        },

    };
    
    

    private void LoadEnvironmentItems()
    {
         if (Context.DataservEnvironmentItems["CarrierMES_ProductionMode"] != null)
        {
            var envProdMode = 0;
            if (int.TryParse(
                Context
                   .DataservEnvironmentItems["CarrierMES_ProductionMode"]
                   .Value
                   .ToString()
                   .Trim(),
                out envProdMode
                ))
            {
                _carrierMesProductionMode = envProdMode;
            }
            //update with live changes
            var envProdModeTag = Context.DataservTaglist.Tag("TAGGROUP::Dataserv Environment Items::TAG::CarrierMES_ProductionMode");

            if (envProdModeTag != null && int.TryParse(envProdModeTag.Value.ToString(), out envProdMode))
            {
                _carrierMesProductionMode = envProdMode;
            }
        }
        mesConnectionString = LoadEnvironmentItem("CarrierMES_ConnectionString");

        try
        {
            if (!File.Exists(_carrierMesAppConfigPath))
            {
                AddLogItem($"File not found: {_carrierMesAppConfigPath}");
                AddNotificationPrompt($"File not found: {_carrierMesAppConfigPath}");
                return;
            }

            var xmlDoc = new XmlDocument();
            xmlDoc.Load(_carrierMesAppConfigPath);// Load the XML from the file

            // Select the AssetId node
            var assetIdNode = xmlDoc.SelectSingleNode("/AppConfig/AssetId");
            if (assetIdNode != null)
            {
                _assetId = int.Parse(assetIdNode.InnerText);
                AddLogItem($"AssetId: {_assetId}");
            }
            else
            {
                AddLogItem("AssetId node not found.");
                AddNotificationPrompt("AssetId node not found.");
            }
        }
        catch (Exception ex)
        {
            AddLogItem(ex, LogLevel.Fatal);
        }
    }

    private string LoadEnvironmentItem(string itemName)
    {
        var value = string.Empty;
        if (Context.DataservEnvironmentItems[itemName] != null)
        {
            if (Context
               .DataservEnvironmentItems[itemName]
               .Value
               .ToString()
               .Trim() !=
                string.Empty)
            {
                value = Context
                       .DataservEnvironmentItems[itemName]
                       .Value
                       .ToString()
                       .Trim();
            }
            //update with live changes
            var envConStringTag = Context.DataservTaglist.Tag($"TAGGROUP::Dataserv Environment Items::TAG::{itemName}");
            if (envConStringTag != null && envConStringTag.Value.ToString() != string.Empty)
            {
                value = envConStringTag.Value.ToString();
            }
        }

        return value;
    }
    
    private void LogAndNotify(string message)
    {
        AddLogItem(message);
        AddNotificationPrompt(message);
    }
    
    public override bool OnScanItemsValidated(ScanItemCollection scanItems)
    {
        resultsTable = null;
        LoadEnvironmentItems();
        
         if (_carrierMesProductionMode == 0)
        {
            scanItems["Model"].Value = scanItems["Serial Number"].Value;
            AddLogItem($"Model set to {scanItems["Model"].Value} for bypass.");
            return true;
        }
        //Validate barcode
        var serial = scanItems["Serial"];
        if (!ValidateBarcode(serial.Value))
        {
            LogAndNotify($"Cycle will exit, as barcode is not valid to run.");
            return false;
        }

        //Validate Cascade
        if (!ValidateCascade(serial.Value))
        {
            LogAndNotify($"Cycle will exit, as cascade reported unit is not valid to run.");
            return false;
        }

        //Validate operator
        var operatorScan = scanItems["Operator"].Value;
        if (!ValidateUser(operatorScan))
        {
            LogAndNotify($"Cycle will exit, as user is not valid to run.");
            return false;
        }


        resultsTable = ExecuteReadRecipe(serial.Value, DefaultDataCatalog);

        return true;
    }

    private bool ValidateCascade(string scannedBarcode)
    {
        var suppliedBarcode = new SqlParameter("@Barcode", SqlDbType.VarChar) {Value = scannedBarcode};
        var suppliedAssetId = new SqlParameter("@AssetId", SqlDbType.Int) {Value = _assetId};
        var results = ExecuteStoredProcedureToTable("[TestEquipment].[ValidateCascadeEffect]", suppliedBarcode, suppliedAssetId);

        var isCascadeValid = results.Rows[0][0].ToString() == "1";
        //log for failure
        if (!isCascadeValid)
        {
            AddLogItem($"MES returned cascade validation is invalid for this barcode: {scannedBarcode}");
            return false;
        }

        return true;
    }

    private bool ValidateBarcode(string scannedBarcode)
    {
        var suppliedBarcode = new SqlParameter("@Barcode", SqlDbType.VarChar) {Value = scannedBarcode};
        var suppliedAssetId = new SqlParameter("@AssetId", SqlDbType.Int) {Value = _assetId};

        var results = ExecuteStoredProcedureToTable("[TestEquipment].[ValidateBarcode]", suppliedBarcode, suppliedAssetId);

        var isBarcodeValid = results.Rows[0][0].ToString() == "1";
        //log for failure
        if (!isBarcodeValid)
        {
            AddLogItem($"MES returned running is NOT allowed for this barcode: {scannedBarcode}");
            return false;
        }

        return true;

    }

    private bool ValidateUser(string userCredential)
    {
        var suppliedCredential = new SqlParameter("@UserCredential", SqlDbType.VarChar) {Value = userCredential};

        var results = ExecuteStoredProcedureToTable("[TestEquipment].[ValidateUser]", suppliedCredential);

        var isUserAllowed = results.Rows[0][0].ToString() == "1";
        //log for failure
        if (!isUserAllowed)
        {
            AddLogItem($"MES returned running is NOT allowed for this user: {userCredential}");
            return false;
        }
        return true;
    }

    public override bool OnRecipeRead(
        ref Recipe recipe,
        Circuit circuit,
        ScanItemCollection scanItems,
        ref string response,
        ref Request request
    )
    {
        try
        {
             if (_carrierMesProductionMode == 0)
        {
            return true;
        }
        
        var useRef     = true;
            
            if (resultsTable != null && resultsTable.Rows.Count > 0)
            {
                if (_debug)
                {
                    AddLogItem("Got recipe MES data", resultsTable.ToCsv(), "", LogLevel.Debug);
                }
            
                var setRefType = false;
                var setRefSize = false;
                
                
                foreach (DataRow row in resultsTable.Rows)
                {
                    var name = row["Name"] as string;
                    var value = row["Value"] as string;
                    var infoSource = row["InfoSource"] as string;
                    //air cooled
                    //RefrigerantType, circuit1_ref_lbs, circuit2_ref_lbs
                    if (name == "RefrigerantType")
                    {
                        var refr = float.Parse(value);
                        SetRecipeItem(ref recipe, "PresetFillType", refr);
                        setRefType = true;
                    }

                    if (name == "circuit1_ref_lbs")
                    {
                        SetRecipeItem(ref recipe, "PresetFill2Quantity", value);
                        setRefSize = true;
                    }
                    
                     if (name == "REFRIGERANT")
                    {
                        if(value=="0") useRef = false;
                    }
                    
                     if (name == "REFRIGERANT_LBS")
                    {
                        if (!setRefSize)
                    {
                        SetRecipeItem(ref recipe, "PresetFill2Quantity", value);
                        setRefSize = true;
                    }
                   }

                    //water cooled
                    //REFRIG_TYPE_19MV, REFRIG_WGHT_19MV
                     if (name == "REFRIG_TYPE")
                    {
                        var match   = System.Text.RegularExpressions.Regex.Match(value, @"(\d{3})");
                        string refr = match.Success ? match.Groups[1].Value : null;
                        SetRecipeItem(ref recipe, "PresetFillType", float.Parse(refr));
                        setRefType = true;
                    }
                    if (name == "REFRIG_TYPE_19MV")
                    {
                        var match   = System.Text.RegularExpressions.Regex.Match(value, @"(\d{3})");
                        string refr = match.Success ? match.Groups[1].Value : null;
                        SetRecipeItem(ref recipe, "PresetFillType", float.Parse(refr));
                        setRefType = true;
                    }
                    
                       if (name == "REFRIG_19MV")
                    {
                        var xx = bool.Parse(value);
                        if(!xx) useRef = false;
                    }
                    
                    //assume either split or single fill
                    if (name == "REFRIG_WGHT_19MV")
                    {
                        if(!setRefType){
                        SetRecipeItem(ref recipe, "PresetFill2Quantity", value);
                        setRefSize = true;
                        }
                    }

                }
            
            if(!useRef) { SetRecipeItem(ref recipe, "PresetFill2Quantity", 0); setRefSize = true;}
            
                if (!setRefType)
                {
                    LogAndNotify("Ref type was not set based on the returned data: " + resultsTable.ToCsv());
                }
                if (!setRefSize)
                {
                    LogAndNotify("Ref size was not set based on the returned data: " + resultsTable.ToCsv());
                }

                if (setRefType && setRefSize)
                {
                    string result = PromptForUserInput("Introduzca la cantidad de aceite deseada","Cantidad");
                        float circuit2Oil = float.Parse(result);
                        if (circuit2Oil >= 0) 
                            SetRecipeItem(ref recipe, "PresetFill1Quantity", circuit2Oil);
                        else 
                            return false;
                    return true;
                }
                else
                {
                    return false;
                }
            }

        }
        catch (Exception ex)
        {
            AddLogItem(ex);
        }
        return false;
    }
    
    


    private DataTable ExecuteReadRecipe(
        string barcode,
        string dataCatalog = null
    )
    {
        var sqlParameters = new List<SqlParameter>();

        var barcodeParameter = new SqlParameter("@Barcode", SqlDbType.VarChar) {Value = barcode};
        sqlParameters.Add(barcodeParameter);

        if (dataCatalog != null)
        {
            var dataCatalogParameter = new SqlParameter("@DataCatalog", SqlDbType.VarChar) {Value = dataCatalog};
            sqlParameters.Add(dataCatalogParameter);
        }

        return ExecuteStoredProcedureToTable("[TestEquipment].[GetUnitInfo]", sqlParameters.ToArray());
    }

    public override void OnCircuitComplete(Circuit circuit)
    {
         if (_carrierMesProductionMode == 0)
        {
            return;
        }
        //insert result data
        var barCode = GetFieldMappingFromCircuit(circuit, "Serial");
        if (barCode == null)
        {
            AddLogItem("Critical error, unable to find barcode field mapping!");
            AddNotificationPrompt("Critical error, unable to find barcode field mapping!");
        }
        var opid = GetFieldMappingFromCircuit(circuit, "Operator");
        if (opid == null)
        {
            AddLogItem("Critical error, unable to find user credential field mapping!");
            AddNotificationPrompt("Critical error, unable to find user credential field mapping!");
        }
        var completionCode = GetFieldMappingFromCircuit(circuit, "FinalDataCompletionCode_AC1");
        if (completionCode == null)
        {
            AddLogItem("Critical error, unable to find completionCode field mapping!");
            AddNotificationPrompt("Critical error, unable to find completionCode field mapping!");
        }

        //custom XML for carrier
        var innerXml = "";
        foreach (var fieldMapping in circuit.OutputMappings[0].FieldMappings.Cast<FieldMapping>())
        {
            var destinationName = fieldMapping.Destination.Name;
            var step = outputStepLookup.TryGetValue(destinationName, out var value) ? value : "-1";
            innerXml +=
            $"<DetailInfo>\n<Name>{destinationName}</Name>\n<Value>{Stringify(fieldMapping.Source)}</Value>\n <Step>{step}</Step>\n</DetailInfo>\n";
        }
        var xmlResults = $"<QGResults>\n <TestDetails>\n{innerXml}</TestDetails>\n</QGResults>";
        AddLogItem("History: (assetId): " + _assetId);
        AddLogItem(Stringify(barCode.Source));
        AddLogItem(Stringify(opid.Source));
        AddLogItem(Stringify(completionCode.Source));
        AddLogItem(xmlResults);
        AddLogItem(circuit.CircuitStartTime.ToString());
        //submit the results
        var result = ExecuteSubmitHistory(
        Stringify(barCode.Source),
        _assetId,
        Stringify(opid.Source),
        Stringify(completionCode.Source),
        xmlResults,
        circuit.CircuitStartTime.ToString()
        );


        if (!result)
        {
            AddLogItem("CRITICAL: History failed to submit to MES");
            AddNotificationPrompt("CRITICAL: History failed to submit to MES");
        }
    }

    private string Stringify(string raw)
    {
        var parsed = Context.RegularExpression.Parse(raw);
        switch (parsed)
        {
            case ScanItem scanItem:
                return scanItem.Value;
            case Tag tag:
                return tag.Value.ToString();
            default:
                return parsed.ToString();
        }
    }

    private FieldMapping GetFieldMappingFromCircuit(Circuit circuit, string destinationFieldName)
    {
        return circuit
              .OutputMappings[0]
              .FieldMappings
              .Cast<FieldMapping>()
              .FirstOrDefault(
               f => f.Destination.Name == destinationFieldName
               );
    }

    private bool ExecuteSubmitHistory(
        string barcode,
        int assetId,
        string userCredential,
        string resultCode,
        string xmlResults,
        string date
    )
    {
        var sqlParameters = new List<SqlParameter>();

        var barcodeParameter = new SqlParameter("@Barcode", SqlDbType.VarChar) {Value = barcode};
        sqlParameters.Add(barcodeParameter);

        var assetParameter = new SqlParameter("@AssetId", SqlDbType.Int) {Value = assetId};
        sqlParameters.Add(assetParameter);

        var credentialParameter = new SqlParameter("@UserCredential", SqlDbType.VarChar) {Value = userCredential};
        sqlParameters.Add(credentialParameter);

        var resultParameter = new SqlParameter("@ResultCode", SqlDbType.VarChar) {Value = resultCode};
        sqlParameters.Add(resultParameter);

        var xmlResultDetails = new SqlParameter("@XMLResultDetails", SqlDbType.Xml) {Value = xmlResults};
        sqlParameters.Add(xmlResultDetails);

        var dateParameter = new SqlParameter("@TestDate", SqlDbType.DateTime) {Value = date};
        sqlParameters.Add(dateParameter);


        var results = ExecuteStoredProcedureToTable("[TestEquipment].[InsertTestResult]", sqlParameters.ToArray());
        AddLogItem(results.ToCsv());
        var isResultsValid = results.Rows[0][0].ToString() == "1";
        if (isResultsValid)
        {
            return true;
        }

        AddLogItem("CRITICAL: History failed to submit to MES");
        return false;
    }

    private SqlParameterCollection ExecuteStoredProcedure(
        string storedProcedureName,
        params SqlParameter[] spParams
    )
    {
        try
        {
            //query the database
            using (var cn = new SqlConnection(mesConnectionString))
            {
                AddLogItem($"CarrierMX_MES - Connected");
                using (var cmd = new SqlCommand(mesConnectionString))
                {
                    AddLogItem($"CarrierMX_MES - Created Command");
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = cn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedureName;
                        cmd.Parameters.AddRange(spParams);
                        cn.Open();
                        var res = cmd.ExecuteNonQuery();
                        //res not checked, but possibly needs to be
                        try
                        {
                            if (_debug)
                                AddLogItem(
                                $"Carrier-MX-MES - SQL Result '{storedProcedureName}' with Input: '{string.Join(";", spParams.Select(parm => $"{parm.ParameterName} = {parm.Value.ToString()}"))}' - Result '{string.Join(";", (from SqlParameter resparm in cmd.Parameters select resparm).ToArray().Select(parm => $"{parm.ParameterName} = {parm.Value.ToString()}"))}'"
                                );
                        }
                        catch (Exception ex)
                        {
                            //we just want to log each SQL result for trouble shooting.
                        }
                        return cmd.Parameters;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            AddLogItem($"CarrierMX_MES - Issue Executing StoredProcedure", ex);
        }
        return null;
    }

    private DataTable ExecuteStoredProcedureToTable(
        string storedProcedureName,
        params SqlParameter[] spParams
    )
    {
        try
        {
            //query the database
            using (var cn = new SqlConnection(mesConnectionString))
            {
                AddLogItem($"CarrierMX_MES - Connected");
                using (var cmd = new SqlCommand())
                {
                    AddLogItem($"CarrierMX_MES - Created Command");
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = cn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedureName;
                        cmd.Parameters.AddRange(spParams);
                        cn.Open();

                        var table = new DataTable();
                        table.Load(cmd.ExecuteReader());

                        if (table.Rows.Count == 0)
                        {
                            AddLogItem($"CarrierFMX_MES - No Results");
                            return null;
                        }
                        //res not checked, but possibly needs to be

                        return table;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            AddLogItem($"CarrierMX_MES - Issue Executing StoredProcedure", ex);
        }
        return null;
    }
}

public static class ExtensionMethods {
    public static string ToCsv(this DataTable table)
    {
        var csv = new StringBuilder();

        // Add column headers
        for (var i = 0; i < table.Columns.Count; i++)
        {
            csv.Append(table.Columns[i].ColumnName);
            if (i < table.Columns.Count - 1)
                csv.Append(",");
        }
        csv.AppendLine();

        // Add rows
        foreach (DataRow row in table.Rows)
        {
            for (var i = 0; i < table.Columns.Count; i++)
            {
                // Handle special characters like commas
                var value = row[i]?.ToString()?.Replace("\"", "\"\"") ?? string.Empty;
                if (value.Contains(",") || value.Contains("\""))
                {
                    csv.Append($"\"{value}\"");
                }
                else
                {
                    csv.Append(value);
                }

                if (i < table.Columns.Count - 1)
                    csv.Append(",");
            }
            csv.AppendLine();
        }

        return csv.ToString();
    }
}