using ByteArrayStyle;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Xylem.Common.Hardware.WaterMeter.Genesis.Registers;
using Xylem.Common.Logic.ProductionOrderCore.Csd;
using Xylem.Common.Logic.ProductionOrderCore.Vako;
using Xylem.Common.Logic.ServiceCore;
using Xylem.Common.Logic.SoftwareAccessHelper;

namespace Xylem.Common.Logic.ProductionOrderCore.OrderData
{
    public static class OrderProgrammingParameters
    {
        //
        public static List<ProgrammingParameters> GetProgrammingParameters(String pcbID, String connectionString)
        {
            var ret = new List<ProgrammingParameters>();
            var orderNr = default(int);

            using (var dataAccess = new SqlDataAccess(connectionString))
            {
                //var sb = new StringBuilder();
                //sb.AppendLine(" SELECT distinct[MapPcbIdToSerialNumber_SerialNumber],  mapOrder.CordonelPressureTest_FertigungsAuftragsNr  ");
                //sb.AppendLine($" FROM  Auftrag.dbo.Cordonel_PressureTest mapOrder ");
                //sb.AppendLine($"     left outer join [Auftrag].[dbo].[MapPcbIdToSerialNumber] map  on mapOrder.CordonelPressureTest_PcbId = map.MapPcbIdToSerialNumber_PcbId ");
                //sb.AppendLine($"     left outer join Auftrag.dbo.AuftragPositionSerienNr mapSrn on map.[MapPcbIdToSerialNumber_SerialNumber] = mapSrn.SerienNr and mapSrn.Pruefgangnr = 0 ");
                //sb.AppendLine($"     left outer join Auftrag.dbo.AlleAuftragPositionen auftraginfo on auftraginfo.AuftragNr = mapSrn.AuftragNr and auftraginfo.PositionNr = mapSrn.PositionNr ");
                //sb.AppendLine($" where mapOrder.CordonelPressureTest_PcbId = '{pcbID}'");

                var mapData = dataAccess.ExecuteQuery($@"
                    SELECT DISTINCT 
	                       map.MapPcbIdToSerialNumber_SerialNumber
	                     , mapOrder.CordonelPressureTest_FertigungsAuftragsNr
                      FROM Cordonel_PressureTest AS mapOrder 
                      LEFT OUTER JOIN MapPcbIdToSerialNumber AS map 
	                    ON mapOrder.CordonelPressureTest_PcbId = map.MapPcbIdToSerialNumber_PcbId 
                      LEFT OUTER JOIN AuftragPositionSerienNr AS mapSrn 
	                    ON map.MapPcbIdToSerialNumber_SerialNumber = mapSrn.SerienNr 
                       AND mapSrn.Pruefgangnr = 0 
                      LEFT OUTER JOIN AlleAuftragPositionen AS auftraginfo 
	                    ON auftraginfo.AuftragNr = mapSrn.AuftragNr 
                       AND auftraginfo.PositionNr = mapSrn.PositionNr 
                     WHERE mapOrder.CordonelPressureTest_PcbId = '{pcbID}'
                       AND mapOrder.CordonelPressureTest_IsDeleted = 0
                       AND map.MapPcbIdToSerialNumber_Deleted = 0");

                if (mapData.Rows.Count != 1)
                {
                    throw new ApplicationException("No base data found");
                }

                orderNr = (Int32)mapData.Rows[0]["CordonelPressureTest_FertigungsAuftragsNr"];

            }

            var radioParams = OrderRadioParameter.GetRadioParams(pcbID, connectionString);
            //NA Cordonels do not have a radio address
            if (radioParams.RadioAddress != 0)
            {

                ret.Add(new ProgrammingParameters("SENSUSRADIO_RadioAddress", BitConverter.GetBytes(radioParams.RadioAddress).Reverse().ToArray()));

                if (radioParams.FrqOffset.HasValue)
                {
                    ret.Add(new ProgrammingParameters("SENSUSRADIO_FrequencyOffset", BitConverter.GetBytes(radioParams.FrqOffset.Value).Reverse().ToArray()));
                }


                ret.Add(new ProgrammingParameters("SENSUSRADIO_EncryptionKey", radioParams.EncryptionKey.ToArray()));



                if (radioParams.PowerLevel > 0 && radioParams.PowerLevelOption > 0 && radioParams.ImpedanceCodeNew > 0 && radioParams.ImpedanceCodeOption > 0)
                {
                    ret.Add(new ProgrammingParameters("SENSUSRADIO_PowerLevel", BitConverter.GetBytes(radioParams.PowerLevel).Reverse().ToArray()));
                    ret.Add(new ProgrammingParameters("SENSUSRADIO_PowerLevelOption", BitConverter.GetBytes(radioParams.PowerLevelOption).Reverse().ToArray()));
                    ret.Add(new ProgrammingParameters("SENSUSRADIO_ImpedanceCodeNew", BitConverter.GetBytes(radioParams.ImpedanceCodeNew).Reverse().ToArray()));
                    ret.Add(new ProgrammingParameters("SENSUSRADIO_ImpedanceCodeOption", BitConverter.GetBytes(radioParams.ImpedanceCodeOption).Reverse().ToArray()));
                }
            }
            var rawVako = VakoDbAdapter.GetRawVako(connectionString, orderNr);
            var rawMapping = VakoDbAdapter.GetVakoMapping(connectionString, rawVako.Type);
            var vakoData = VakoParser.Parse(rawVako, rawMapping);

            var csdIdent = vakoData.KeyValues.FirstOrDefault(a => a.Name == "C_GEN_CSD")?.Value;

            if (String.IsNullOrEmpty(csdIdent))
            {
                throw new ApplicationException("CSD Could not befound");
            }
            if (orderNr == 3204790)
            {
                csdIdent = "773328";
            }

            using (var dataAccess = new SqlDataAccess(connectionString))
            {
                var sb = new StringBuilder();
                sb.AppendLine($" SELECT ");
                sb.AppendLine($" RegisterName	,");
                sb.AppendLine($" RegisterValue ");
                sb.AppendLine($"  from [Auftrag].[dbo].[Cordonel_ProgrammingMapper] ");


                sb.AppendLine($" where 1=0 ");


                foreach (var item in vakoData.KeyValues)
                {
                    sb.AppendLine($"  or  (KEYNAME = '{item.Name}' and KeyValue = '{item.Charcode.Replace("\'", "\'\'")}') ");
                }
                var mapData = dataAccess.ExecuteQuery(sb.ToString());

                foreach (DataRow row in mapData.Rows)
                {
                    ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), (Byte[])row["RegisterValue"]));
                }
            }

            var meterSize = BitConverter.ToUInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_MeterSize").RegisterValue.Reverse().ToArray(), 0);
            var displayUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_DisplayUnits").RegisterValue.Reverse().ToArray(), 0);
            var flowUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "METROLOGYASST_FlowUnits").RegisterValue.Reverse().ToArray(), 0);




            using (var dataAccess = new SqlDataAccess(connectionString))
            {
                var sb = new StringBuilder();
                sb.AppendLine($" SELECT                                                                                         ");
                sb.AppendLine($"     CordonelDnDisplayunitDisplaypow10_RegisterIdent as RegisterName,                           ");
                sb.AppendLine($" 	 [CordonelDnDisplayunitDisplaypow10_DisplayPow10] as RegisterValue       ");
                sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnDisplayunitDisplaypow10]                                       ");
                sb.AppendLine($"     where [CordonelDnDisplayunitDisplaypow10_MeterSize] = {meterSize}                          ");
                sb.AppendLine($"    and [CordonelDnDisplayunitDisplaypow10_DisplayUnit] = {displayUnits}                        ");
                sb.AppendLine($" union                                                                                          ");
                sb.AppendLine($" SELECT                                                                                         ");
                sb.AppendLine($"   CordonelDnFlowUnitFlowPoint_RegisterIdent as RegisterName,                                   ");
                sb.AppendLine($"      CAST([CordonelDnFlowUnitFlowPoint_FlowPoint] AS varbinary)  as RegisterValue              ");
                sb.AppendLine($"   FROM [Auftrag].[dbo].[CordonelDnFlowUnitFlowPoint]                                           ");
                sb.AppendLine($"    where[CordonelDnFlowUnitFlowPoint_FlowUnit] = {flowUnits}                                   ");
                sb.AppendLine($"    and[CordonelDnFlowUnitFlowPoint_MeterSize] = {meterSize}                                    ");

                var mapData = dataAccess.ExecuteQuery(sb.ToString());

                foreach (DataRow row in mapData.Rows)
                {
                    ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), RegisterConverter.ValueToByteArray(Convert.ToInt32(row["RegisterValue"]))));
                }
            }
            var ident = "GNS";
            if (vakoData.KeyValues.Any(k => k.Key == "Produktgruppe") && vakoData.KeyValues.First(k => k.Key == "Produktgruppe").Charcode == "U")
            {
                ident += vakoData.KeyValues.First(k => k.Key == "Nennweite").Charcode;
            }

            if (!string.IsNullOrWhiteSpace(csdIdent))
            {
                var csdTokens = csdIdent.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                if (csdTokens.Length >= 2)
                {
                    var csdNr = csdTokens[1];

                    if (!string.IsNullOrWhiteSpace(csdNr))
                    {
                        csdIdent = csdNr;
                    }
                }
            }

            var retStr = LocalWebRequest.GetRequest($"http://10.49.40.25/csdcore/csdservice/getstampings?CsdIdent={csdIdent}&GroupId=1&ProductIdent={ident}&ProductDn={rawVako.Dn}&ShowOnlyValidStampings=0", 80000);
            var retListRealCsd = JsonConvert.DeserializeObject<List<CsdParameterResult>>(retStr);
            if (retListRealCsd != null && retListRealCsd.Any())
            {

                foreach (var csdItemToConvert in retListRealCsd)
                {

                    ret.Add(new ProgrammingParameters(csdItemToConvert.Name, ByteStyler.Parse(csdItemToConvert.ValueText, false), ProgrammingSource.Csd));

                }
            }




            if (orderNr != 3249279)
            {


                if (radioParams.RadioAddress != 0)
                {
                    if (vakoData.KeyValues.Any(f => f.Name.Contains("C_GEN_FUNKSCHLUESSEL")))
                    {
                        var KeyInfo = vakoData.KeyValues.FirstOrDefault(f => f.Name.Contains("C_GEN_FUNKSCHLUESSEL"));
                        var keyList = ret.FindAll(f => f.RegisterName == "SENSUSRADIO_EncryptionKey");
                        var heightstKey = keyList.OrderByDescending(o => o.Source).First();  //desc

                        var defEncryptionKey = new List<Byte>();
                        defEncryptionKey.AddRange(new Byte[] { 0x0E, 0x9F, 0xAC, 0xC6 }.ToArray());
                        defEncryptionKey.AddRange(new Byte[] { 0xA0, 0xE1, 0xEF, 0x1C }.ToArray());
                        defEncryptionKey.AddRange(new Byte[] { 0x3B, 0x27, 0xA4, 0x9B }.ToArray());
                        defEncryptionKey.AddRange(new Byte[] { 0xAC, 0x21, 0x65, 0x35 }.ToArray());


                        switch (KeyInfo.Charcode)
                        {
                            //1   Standard
                            case "1":

                                if (ByteStyler.ToString(heightstKey.RegisterValue) != ByteStyler.ToString(defEncryptionKey.ToArray()))
                                {
                                    throw new ApplicationException("Order placed with default encryption, but the datasource has a diffrent key");
                                }

                                break;
                            //2   Zufallszahl
                            case "2":
                                //notdef

                                if (ByteStyler.ToString(heightstKey.RegisterValue) == ByteStyler.ToString(defEncryptionKey.ToArray()))
                                {
                                    throw new ApplicationException("Order placed with radom encryption, but the datasource has a default key");
                                }
                                //not csd
                                else if (heightstKey.Source == ProgrammingSource.Csd)
                                {
                                    throw new ApplicationException("Order placed with radom encryption, but the datasource has a customer key");
                                }
                                break;
                            //3   nach CSD
                            case "3":
                                if (heightstKey.Source != ProgrammingSource.Csd)
                                {
                                    throw new ApplicationException("Order placed with Customer encryption but the datasource has no Customer key");
                                }
                                break;
                            default:
                                throw new ApplicationException("No Vako Key for Radiokey found, unkown parameter");
                        }
                    }
                    else
                    {
                        throw new ApplicationException("No Vako Key for Radiokey found");
                    }

                }
            }

            StringBuilder csv = new StringBuilder();
            foreach (var item in ret)
            {
                csv.AppendLine($"{item.RegisterName}={ByteStyler.ToString(item.RegisterValue)} (From{item.Source})");
            }

            return ret;

        }

        public static List<ProgrammingParameters> GetProgrammingParametersFixNa(String connectionString)
        {
            var ret = new List<ProgrammingParameters>();

            Int32 orderNr = 3215223;

            var rawVako = VakoDbAdapter.GetRawVako(connectionString, orderNr);
            var rawMapping = VakoDbAdapter.GetVakoMapping(connectionString, rawVako.Type);
            var vakoData = VakoParser.Parse(rawVako, rawMapping);

            var csdIdent = vakoData.KeyValues.FirstOrDefault(a => a.Name == "C_GEN_CSD")?.Value;


            using (var dataAccess = new SqlDataAccess(connectionString))
            {
                var sb = new StringBuilder();
                sb.AppendLine($" SELECT ");
                sb.AppendLine($" RegisterName	,");
                sb.AppendLine($" RegisterValue ");
                sb.AppendLine($"  from [Auftrag].[dbo].[Cordonel_ProgrammingMapper] ");


                sb.AppendLine($" where 1=0 ");


                foreach (var item in vakoData.KeyValues)
                {
                    sb.AppendLine($"  or  (KEYNAME = '{item.Name}' and KeyValue = '{item.Charcode.Replace("\'", "\'\'")}') ");
                }
                var mapData = dataAccess.ExecuteQuery(sb.ToString());

                foreach (DataRow row in mapData.Rows)
                {
                    ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), (Byte[])row["RegisterValue"]));
                }
            }

            var meterSize = BitConverter.ToUInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_MeterSize").RegisterValue.Reverse().ToArray(), 0);
            var displayUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "GENESISFLOW_DisplayUnits").RegisterValue.Reverse().ToArray(), 0);
            var flowUnits = BitConverter.ToInt32(ret.Find(a => a.RegisterName == "METROLOGYASST_FlowUnits").RegisterValue.Reverse().ToArray(), 0);




            using (var dataAccess = new SqlDataAccess(connectionString))
            {
                var sb = new StringBuilder();
                sb.AppendLine($" SELECT                                                                                         ");
                sb.AppendLine($"     CordonelDnDisplayunitDisplaypow10_RegisterIdent as RegisterName,                           ");
                sb.AppendLine($" 	 [CordonelDnDisplayunitDisplaypow10_DisplayPow10] as RegisterValue       ");
                sb.AppendLine($" FROM [Auftrag].[dbo].[CordonelDnDisplayunitDisplaypow10]                                       ");
                sb.AppendLine($"     where [CordonelDnDisplayunitDisplaypow10_MeterSize] = {meterSize}                          ");
                sb.AppendLine($"    and [CordonelDnDisplayunitDisplaypow10_DisplayUnit] = {displayUnits}                        ");
                sb.AppendLine($" union                                                                                          ");
                sb.AppendLine($" SELECT                                                                                         ");
                sb.AppendLine($"   CordonelDnFlowUnitFlowPoint_RegisterIdent as RegisterName,                                   ");
                sb.AppendLine($"      CAST([CordonelDnFlowUnitFlowPoint_FlowPoint] AS varbinary)  as RegisterValue              ");
                sb.AppendLine($"   FROM [Auftrag].[dbo].[CordonelDnFlowUnitFlowPoint]                                           ");
                sb.AppendLine($"    where[CordonelDnFlowUnitFlowPoint_FlowUnit] = {flowUnits}                                   ");
                sb.AppendLine($"    and[CordonelDnFlowUnitFlowPoint_MeterSize] = {meterSize}                                    ");

                var mapData = dataAccess.ExecuteQuery(sb.ToString());

                foreach (DataRow row in mapData.Rows)
                {
                    ret.Add(new ProgrammingParameters(row["RegisterName"].ToString(), RegisterConverter.ValueToByteArray(Convert.ToInt32(row["RegisterValue"]))));
                }
            }
            var ident = "GNS";
            if (vakoData.KeyValues.Any(k => k.Key == "Produktgruppe") && vakoData.KeyValues.First(k => k.Key == "Produktgruppe").Charcode == "U")
            {
                ident += vakoData.KeyValues.First(k => k.Key == "Nennweite").Charcode;
            }



            var retStr = LocalWebRequest.GetRequest($"http://sla12iis01/csdcore/csdservice/getstampings?CsdIdent={csdIdent}&GroupId=1&ProductIdent={ident}&ProductDn={rawVako.Dn}&ShowOnlyValidStampings=0", 8000);
            var retListRealCsd = JsonConvert.DeserializeObject<List<CsdParameterResult>>(retStr);
            if (retListRealCsd != null && retListRealCsd.Any())
            {

                foreach (var csdItemToConvert in retListRealCsd)
                {
                    if (csdItemToConvert.ValueText.Contains("-"))
                    {
                        ret.Add(new ProgrammingParameters(csdItemToConvert.Name, ByteStyler.Parse(csdItemToConvert.ValueText, false), ProgrammingSource.Csd));
                    }
                }
            }


            StringBuilder csv = new StringBuilder();
            foreach (var item in ret)
            {
                csv.AppendLine($"{item.RegisterName}={ByteStyler.ToString(item.RegisterValue)} (From{item.Source})");
            }

            return ret;

        }


    }
}