using DataHelper = Fast.Logic.DataHelper;

namespace Fast.Web.Logic
{
    public class PlantStatementImportHelper
    {
        private readonly MyDbContext db;
        readonly string SheetName;

        public PlantStatementImportHelper(MyDbContext context, string sheetName)
        {
            db = context;
            SheetName = sheetName;
        }

        private readonly HashSet<string> SkipColumns = new();
        private readonly StringBuilder NotesBuilder = new();

        public string? Notes
        {
            get
            {
                string? notes = null;
                if (NotesBuilder.Length > 0)
                {
                    NotesBuilder.Insert(0, "File uploaded successfully but with the following notes:\r\n\r\n");
                    notes = NotesBuilder.ToString();
                }

                return notes;
            }
        }

        private readonly HashSet<string> RequiredHeaders = new() { "PROD_M", "STMT_D", "PLANT_N", "METER_N", "PRODUCER_N" };

        public void ValidateRequiredFields(List<string> headers)
        {
            HashSet<string> headersHash = headers.Select(x => x.Trim().ToUpper()).ToHashSet();

            foreach (string requiredHeader in RequiredHeaders)
            {
                if (!headersHash.Contains(requiredHeader))
                    throw new Exception("Required column header {" + requiredHeader + "} not found in sheet {" + SheetName + "}");
            }
        }

        public void SetItemValue(PlantStatement item, string headerName, string? fileValue, int row, int col)
        {
            try
            {
                headerName = headerName.Trim().ToUpper();

                if (!string.IsNullOrWhiteSpace(headerName))
                {
                    if (RequiredHeaders.Contains(headerName) && string.IsNullOrWhiteSpace(fileValue))
                        throw new Exception("Missing required value");

                    var val = fileValue?.Trim();

                    switch (headerName)
                    {
                        case "PROD_M":
                            item.ProductionMonth = GetMonth(val) ?? throw new Exception("Production month is required");
                            break;
                        case "STMT_D":
                            item.StatementDate = GetDate(val) ?? throw new Exception("Statement date is required");
                            break;
                        case "PLANT_N":
                            item.PlantId = GetPlantId(val) ?? throw new Exception("Plant name is required");
                            break;
                        case "PRODUCER_N":
                            item.ProducerId = GetProducerId(val) ?? throw new Exception("Producer name is required");
                            break;
                        case "METER_N":
                            item.MeterId = GetMeterId(val).GetValueOrDefault();
                            break;
                        case "PROC_OR_INLET":
                            item.StatementDescriptorId = GetStatementDescriptorId(val);
                            break;
                        case "PAY_TYPE":
                            item.PayoutTypeId = GetPayoutTypeId(val);
                            break;
                        case "WH_V_MCF":
                            item.WellheadVolumeMcf = GetInt(val);
                            break;
                        case "WH_V_MMBTU":
                            item.WellheadVolumeMmbtu = GetInt(val);
                            break;
                        case "PR_V_MCF":
                            item.ProcessedVolumeMcf = GetInt(val);
                            break;
                        case "PR_V_MMBTU":
                            item.ProcessedVolumeMmbtu = GetInt(val);
                            break;
                        case "SHRINK_MCF":
                            item.ShrinkMcf = GetInt(val);
                            break;
                        case "SHRINK_MMBTU":
                            item.ShrinkMmbtu = GetInt(val);
                            break;
                        case "PLANT_F_MCF":
                            item.PlantFuelMcf = GetInt(val);
                            break;
                        case "PLANT_F_MMBTU":
                            item.PlantFuelMmbtu = GetInt(val);
                            break;
                        case "FLARE_MCF":
                            item.FlareMcf = GetInt(val);
                            break;
                        case "FLARE_MMBTU":
                            item.FlareMmbtu = GetInt(val);
                            break;
                        case "BYPASS_MCF":
                            item.BypassMcf = GetInt(val);
                            break;
                        case "BYPASS_MMBTU":
                            item.BypassMmbtu = GetInt(val);
                            break;
                        case "PLANT_G_L_MCF":
                            item.PlantGainLossMcf = GetInt(val);
                            break;
                        case "PLANT_G_L_MMBTU":
                            item.PlantGainLossMmbtu = GetInt(val);
                            break;
                        case "ELECTION_MCF":
                            item.ElectionMcf = GetInt(val);
                            break;
                        case "ELECTION_MMBTU":
                            item.ElectionMmbtu = GetInt(val);
                            break;
                        case "PIPE_G_L_MCF":
                            item.PipelineGainLossMcf = GetInt(val);
                            break;
                        case "PIPE_G_L_MMBTU":
                            item.PipelineGainLossMmbtu = GetInt(val);
                            break;
                        case "I_K_PVR_MCF":
                            item.InKindPvrMcf = GetInt(val);
                            break;
                        case "I_K_PTR_MMBTU":
                            item.InKindPtrMmbtu = GetInt(val);
                            break;
                        case "REC_CO2":
                            item.RecoveredCarbonDioxide = GetInt(val);
                            break;
                        case "REC_ETHANE":
                            item.RecoveredEthane = GetInt(val);
                            break;
                        case "REC_PROP":
                            item.RecoveredPropane = GetInt(val);
                            break;
                        case "REC_ISO_BUT":
                            item.RecoveredIsoButane = GetInt(val);
                            break;
                        case "REC_N_BUT":
                            item.RecoveredNormalButane = GetInt(val);
                            break;
                        case "REC_NAT_GAS":
                            item.RecoveredNaturalGasoline = GetInt(val);
                            break;
                        case "REC_SCRUBBER":
                            item.RecoveredScrubber = GetInt(val);
                            break;
                        case "ALLOC_CO2":
                            item.AllocatedCarbonDioxide = GetInt(val);
                            break;
                        case "ALLOC_ETHANE":
                            item.AllocatedEthane = GetInt(val);
                            break;
                        case "ALLOC_PROP":
                            item.AllocatedPropane = GetInt(val);
                            break;
                        case "ALLOC_ISO_BUT":
                            item.AllocatedIsoButane = GetInt(val);
                            break;
                        case "ALLOC_N_BUT":
                            item.AllocatedNormalButane = GetInt(val);
                            break;
                        case "ALLOC_NAT_GAS":
                            item.AllocatedNaturalGasoline = GetInt(val);
                            break;
                        case "ALLOC_SCRUBBER":
                            item.AllocatedScrubber = GetInt(val);
                            break;
                        case "THEO_CO2":
                            item.TheoreticalCarbonDioxide = GetInt(val);
                            break;
                        case "THEO_ETHANE":
                            item.TheoreticalEthane = GetInt(val);
                            break;
                        case "THEO_PROPANE":
                            item.TheoreticalPropane = GetInt(val);
                            break;
                        case "THEO_ISO_BUT":
                            item.TheoreticalIsoButane = GetInt(val);
                            break;
                        case "THEO_N_BUT":
                            item.TheoreticalNormalButane = GetInt(val);
                            break;
                        case "THEO_ISO_PENTANE":
                            item.TheoreticalIsoPentane = GetInt(val);
                            break;
                        case "THEO_N_PENTANE":
                            item.TheoreticalNormalPentane = GetInt(val);
                            break;
                        case "THEO_NAT_GAS":
                            item.TheoreticalNaturalGasoline = GetInt(val);
                            break;
                        case "PTR_CO_P":
                            item.PtrCashoutPrice = GetDecimal(val);
                            break;
                        case "PTR_TRAN":
                            item.PtrTrans = GetDecimal(val);
                            break;
                        case "PTR_F_PER":
                            item.PtrFuelPercent = GetDecimal(val);
                            break;
                        case "WH_PTR_P":
                            item.WellheadPtrPrice = GetDecimal(val);
                            break;
                        case "LIQ_T_PER":
                            item.LiquidsTakePercent = GetDecimal(val);
                            break;
                        case "TAXES":
                            item.Taxes = GetDecimal(val);
                            break;
                        case "FRAC_FEE":
                            item.FractionationFee = GetDecimal(val);
                            break;
                        case "PLT_PROC_FEE":
                            item.PlantProcessingFee = GetDecimal(val);
                            break;
                        case "CO2_FEE":
                            item.CarbonDioxideFee = GetDecimal(val);
                            break;
                        case "COMP_FEE":
                            item.CompressionFee = GetDecimal(val);
                            break;
                        case "ELEC_FEE":
                            item.ElectricityFee = GetDecimal(val);
                            break;
                        case "STAB_FEE":
                            item.StabilizationFee = GetDecimal(val);
                            break;
                        case "MISC_FEE":
                            item.MiscellaneousFees = GetDecimal(val);
                            break;
                        case "LIQ_L_FEE":
                            item.MiscellaneousFees = GetDecimal(val);
                            break;
                        case "GPM_CO2":
                            item.GpmCarbonDioxide = GetDecimal(val);
                            break;
                        case "GPM_ETH":
                            item.GpmEthane = GetDecimal(val);
                            break;
                        case "GPM_PROP":
                            item.GpmPropane = GetDecimal(val);
                            break;
                        case "GPM_ISO_BUT":
                            item.GpmIsoButane = GetDecimal(val);
                            break;
                        case "GPM_N_BUT":
                            item.GpmNormalButane = GetDecimal(val);
                            break;
                        case "GPM_ISO_PENT":
                            item.GpmIsoPentane = GetDecimal(val);
                            break;
                        case "GPM_N_PENT":
                            item.GpmNormalPentane = GetDecimal(val);
                            break;
                        case "GPM_HEX_PLUS":
                            item.GpmHexanePlus = GetDecimal(val);
                            break;
                        case "MOLE_N2":
                            item.MolePercentNitrogen = GetDecimal(val);
                            break;
                        case "MOLE_CO2":
                            item.MolePercentCarbonDioxide = GetDecimal(val);
                            break;
                        case "MOLE_METH":
                            item.MolePercentMethane = GetDecimal(val);
                            break;
                        case "MOLE_ETH":
                            item.MolePercentEthane = GetDecimal(val);
                            break;
                        case "MOLE_PROP":
                            item.MolePercentPropane = GetDecimal(val);
                            break;
                        case "MOLE_ISO_BUT":
                            item.MolePercentIsoButane = GetDecimal(val);
                            break;
                        case "MOLE_N_BUT":
                            item.MolePercentNormalButane = GetDecimal(val);
                            break;
                        case "MOLE_ISO_PENT":
                            item.MolePercentIsoPentane = GetDecimal(val);
                            break;
                        case "MOLE_N_PENT":
                            item.MolePercentNormalPentane = GetDecimal(val);
                            break;
                        case "MOLE_HEX":
                            item.MolePercentHexane = GetDecimal(val);
                            break;
                        case "MOLE_HEPT":
                            item.MolePercentHeptane = GetDecimal(val);
                            break;
                        default:
                            if (SkipColumns.Contains(headerName))
                            {
                                //do nothing, unrecognized header already added
                            }
                            else
                            {
                                SkipColumns.Add(headerName);
                                var message = GetNote("Unrecognized column header skipped", headerName, fileValue ?? "", row, col);
                                NotesBuilder.Append(message);
                            }

                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                string originalMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
                var newMessage = GetNote(originalMessage, headerName, fileValue ?? "", row, col);
                throw new Exception(newMessage);
            }
        }

        private string GetNote(string note, string headerName, string fileValue, int row, int col)
        {
            var newNote = "";
            newNote += "Sheet {" + SheetName + "} \u2022 ";
            newNote += "Cell {" + OpenXmlHelper.GetColumnName(col) + row + "} \u2022 ";
            newNote += "Header {" + headerName + "} \u2022 ";
            newNote += "Value {" + fileValue + "}\r\n";
            newNote += note + "\r\n\r\n";
            return newNote;
        }

        private static DateOnly? GetDate(string? value)
        {
            DateOnly? newValue = null;
            if (!string.IsNullOrWhiteSpace(value))
            {
                if (double.TryParse(value, out double oaDate))
                {
                    newValue = DateOnly.FromDateTime(DateTime.FromOADate(oaDate));
                }
                else if (DateTime.TryParse(value, out DateTime dt))
                {
                    newValue = DateOnly.FromDateTime(dt);
                }
            }

            return newValue;
        }

        private static DateOnly? GetMonth(string? value)
        {
            DateOnly? newValue = null;
            if (!string.IsNullOrWhiteSpace(value))
            {
                DateTime dt;
                if (double.TryParse(value, out double oaDate))
                {
                    dt = DateTime.FromOADate(oaDate);
                    newValue = new DateOnly(dt.Year, dt.Month, 1);
                }
                else if (DateTime.TryParse(value, out dt))
                {
                    newValue = new DateOnly(dt.Year, dt.Month, 1);
                }
            }

            return newValue;
        }

        private static int? GetInt(string? value)
        {
            int? newValue = null;
            if (!string.IsNullOrWhiteSpace(value) && double.TryParse(value, out double dVal))
            {
                newValue = (int)dVal;
            }

            return newValue;
        }

        private static decimal? GetDecimal(string? value)
        {
            decimal? newValue = null;
            if (!string.IsNullOrWhiteSpace(value) && decimal.TryParse(value, out decimal dVal))
            {
                newValue = dVal;
            }

            return newValue;
        }

        Dictionary<string, int>? plantNames = null;

        private int? GetPlantId(string? name)
        {
            int? newValue = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                string nameTrimmed = name.Trim();
                plantNames ??= (
                    from q in db.Plants
                    select new { q.Id, q.Name }
                ).ToDictionary(x => x.Name, x => x.Id, StringComparer.OrdinalIgnoreCase);

                if (plantNames.ContainsKey(nameTrimmed))
                    newValue = plantNames[nameTrimmed];
                else
                    throw new Exception("Unrecognized value");
            }

            return newValue;
        }

        Dictionary<string, int>? meterNames = null;

        private int? GetMeterId(string? name)
        {
            int? newValue = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                string nameTrimmed = name.Trim();
                if (meterNames == null)
                {
                    meterNames = DataHelper.GetMetersByProductAsync(Enums.ProductCategory.NaturalGasAndLng).Result
                        .Where(x => !x.MeterName.Contains("{Inactive}") && !x.MeterName.Contains("{ PTR}") && x.MeterName != "PTR")
                        .ToDictionary(x => x.MeterName, x => x.MeterId, StringComparer.OrdinalIgnoreCase);
                }

                if (meterNames.ContainsKey(nameTrimmed))
                    newValue = meterNames[nameTrimmed];
                else
                    throw new Exception("Unrecognized value");
            }

            return newValue;
        }

        ILookup<string, int>? producerNames = null;

        private int? GetProducerId(string? name)
        {
            int? newValue = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                string nameTrimmed = name.Trim();
                if (producerNames == null)
                {
                    producerNames = DataHelper.GetProducersAsync(true).Result
                        .ToLookup(x => x.Name, x => x.Id, StringComparer.OrdinalIgnoreCase);
                }

                if (producerNames.Contains(nameTrimmed))
                    newValue = producerNames[nameTrimmed].First();
                else
                    throw new Exception("Unrecognized value");
            }

            return newValue;
        }

        Dictionary<string, int>? statementDescriptors = null;

        private int? GetStatementDescriptorId(string? name)
        {
            int? newValue = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                string nameTrimmed = name.Trim();
                if (statementDescriptors == null)
                {
                    statementDescriptors = (
                        from q in db.PlantStatementDescriptors
                        select new { q.Id, q.Name }
                    ).ToDictionary(x => x.Name, x => x.Id, StringComparer.OrdinalIgnoreCase);
                }

                if (statementDescriptors.ContainsKey(nameTrimmed))
                    newValue = statementDescriptors[nameTrimmed];
                else
                    throw new Exception("Unrecognized value");
            }

            return newValue;
        }

        Dictionary<string, int>? payoutTypes = null;

        private int? GetPayoutTypeId(string? name)
        {
            int? newValue = null;

            if (!string.IsNullOrWhiteSpace(name))
            {
                string nameTrimmed = name.Trim();
                if (payoutTypes == null)
                {
                    payoutTypes = (
                        from q in db.PayoutTypes
                        select new { q.Id, q.Name }
                    ).ToDictionary(x => x.Name, x => x.Id, StringComparer.OrdinalIgnoreCase);
                }

                if (payoutTypes.ContainsKey(nameTrimmed))
                    newValue = payoutTypes[nameTrimmed];
                else
                    throw new Exception("Unrecognized value");
            }

            return newValue;
        }
    }
}
