using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Wordprocessing;
using Fast.Shared.Logic.FileService;
using Fast.Shared.Logic.Package;
using static Fast.Shared.Models.Enums;

namespace Fast.Logic.CrudeOil;

public partial class InvoiceDoc(IFileService fileService, string filesFolderName, string templatesFolderName, string signaturesFolderName, MyDbContext db)
    : DocBaseOpenXml(fileService, filesFolderName, templatesFolderName, signaturesFolderName, string.Empty, true)
{
    private const int ColIndexReceiptMeter = 0;
    private const int ColIndexDeliveryMeter = 1;
    private const int ColIndexInternalContract = 2;
    private const int ColIndexExternalContract = 3;
    private const int ColIndexBasePrice = 4;
    private const int ColIndexRollPrice = 5;
    private const int ColIndexPriceAdj1 = 6;
    private const int ColIndexPriceAdj2 = 7;
    private const int ColIndexPipePercentage = 8;
    private const int ColIndexPipeRate = 9;
    private const int ColIndexPipeDeduct = 10;
    private const int ColIndexNetPrice = 11;
    private const int ColIndexVolume = 12;
    private const int ColIndexGrossAmount = 13;
    private const int ColIndexBankRate = 14;
    private const int ColIndexBankAmount = 15;
    private const int ColIndexFees = 16;
    private const int ColIndexInvoiceAmount = 17;

    protected override string TemplateFileName => "CrudeInvoiceTemplate.docx";

    public async Task<(string FileNameOriginal, string FileNameOnDisk)> GenerateAsync(string invoiceNum)
    {
        var invoice = (
            from q in db.InvoiceCrudes
                .Include(x => x.InvoiceCrudeLines)
                    .ThenInclude(x => x.Deal)
                .Include(x => x.CounterpartyState)
                .Include(x => x.InternalEntityState)
                .Include(x => x.Counterparty)
                .Include(x => x.InternalEntity)
                .Include(x => x.InvoiceType)
            where q.InvoiceNum == invoiceNum
            select q
        ).AsNoTracking().First();

        var internalEntityName = invoice.InternalEntity.Name;
        var counterpartyName = invoice.Counterparty.Name;
        var internalInvoiceAddress = invoice.InternalEntityAddressLine1 + " " + invoice.InternalEntityAddressLine2;
        var internalCityStateZip = GetCityStateZipStr(invoice.InternalEntityCity, invoice.InternalEntityState?.Name, invoice.InternalEntityZip);
        var counterpartyAddress = invoice.CounterpartyAddressLine1 + " " + invoice.CounterpartyAddressLine2;
        var counterpartyCityStateZip = GetCityStateZipStr(invoice.CounterpartyCity, invoice.CounterpartyState?.Name, invoice.CounterpartyZip);
        var customerNum = invoice.Counterparty.InternalCustomerNum;

        SetText("Version", invoice.InvoiceType.Name ?? "");
        SetText("InternalAddress", internalInvoiceAddress ?? "");
        SetText("InternalCityStateZip", internalCityStateZip ?? "");
        SetText("InternalContactName", invoice.ContactName ?? "");
        SetText("InternalContactPhone", invoice.ContactPhone ?? "");
        SetText("InternalContactEmail", invoice.ContactEmail ?? "");
        SetText("InternalBank", invoice.PaymentBankName ?? "");
        SetText("InternalBankNum", invoice.PaymentAccountNum ?? "");
        SetText("InternalBankAccount", invoice.PaymentAccountName ?? "");
        SetText("InternalBankCompany", invoice.InternalEntity.Name ?? "");
        SetText("CounterpartyName", counterpartyName ?? "");
        SetText("CounterpartyAddress", counterpartyAddress ?? "");
        SetText("CounterpartyCityStateZip", counterpartyCityStateZip ?? "");
        SetText("InternalNumber", invoice.InvoiceNum ?? "");
        SetText("InternalInvoiceDate", invoice.InvoiceDate.ToString("MM/dd/yyyy"));
        SetText("InternalDueDate", invoice.DueDate?.ToString("MM/dd/yyyy") ?? "");
        SetText("InternalProductionMonth", invoice.Month.ToString("MM/yyyy"));
        SetText("InternalEntityShort", invoice.InternalEntity.ShortName ?? "");
        SetText("CounterpartyShort", invoice.Counterparty.ShortName ?? "");
        SetText("AccountingContactEmail", invoice.AccountingContactEmail ?? "");
        SetText("AccountingContactName", invoice.AccountingContactName ?? "");
        SetText("AccountingContactPhone", invoice.AccountingContactPhone ?? "");

        await SetLinesAsync(invoice);

        var fileNameOriginal = GetNewFileName(invoice.Month, invoice.CounterpartyId, invoice.InvoiceNum!);
        var existingFileNameOnDisk = db.InvoiceCrudes.First(x => x.InvoiceNum == invoiceNum).FileNameOnDisk;
        var newFileNameOnDisk = Guid.NewGuid().ToString("N") + ".pdf";
        var fileNameOnDisk = existingFileNameOnDisk ?? newFileNameOnDisk;

        wordprocessingDocument.PackageProperties.Title = fileNameOriginal;
        string baseCrudePath = Path.Join(filesFolderName, "Crude");

        using var ms = SaveToMemoryStream();
        await PdfConverter.ConvertDocxAsync(ms, fileService, baseCrudePath, fileNameOnDisk, useSharding: true);

        return (fileNameOriginal, fileNameOnDisk);
    }

    protected override HashSet<string> GetDocKeys()
    {
        HashSet<string> keys = new(StringComparer.OrdinalIgnoreCase)
        {
            "Version",
            "InternalAddress",
            "InternalCityStateZip",
            "InternalContactName",
            "InternalContactEmail",
            "InternalContactPhone",
            "InternalBank",
            "InternalBankNum",
            "InternalBankCompany",
            "InternalBankAccount",
            "AccountingContactEmail",
            "AccountingContactName",
            "AccountingContactPhone",
            "CounterpartyName",
            "CounterpartyAddress",
            "CounterpartyCityStateZip",
            "InternalNumber",
            "InternalInvoiceDate",
            "InternalDueDate",
            "InternalProductionMonth",
            "InternalEntityShort",
            "CounterpartyShort",
        };

        return keys;
    }

    private async Task SetLinesAsync(InvoiceCrude invoice)
    {
        var allLines = invoice.InvoiceCrudeLines.OrderBy(x => x.LineNum).ToList();
        var lineGroups = GetInvoiceLineGroups(allLines);
        var lineSupportData = await GetLineSupportDataAsync();

        var table = doc.MainDocumentPart?.Document?.Body?
            .Descendants<Table>()
            .LastOrDefault()
            ?? throw new InvalidOperationException("No table found in document");

        foreach (var group in lineGroups)
        {
            AddGroupLines(group, table, lineSupportData);
            AddGroupSubtotalLine(group, table);
        }

        AddTaxLine(invoice, table);
        AddGrandTotalLine(invoice, table);
    }

    private class InvoiceLineGroup
    {
        public List<InvoiceCrudeLine> InvoiceLines { get; } = new();
    }

    private static List<InvoiceLineGroup> GetInvoiceLineGroups(List<InvoiceCrudeLine> invoiceLines)
    {
        List<InvoiceLineGroup> allInvoiceLineGroups = new();
        InvoiceLineGroup? currentGroup = null;
        string? previousDealNum = null;
        foreach (var line in invoiceLines)
        {
            var currentDealNum = line.Deal?.TicketNum;
            var hasDealNum = !string.IsNullOrWhiteSpace(currentDealNum);
            var dealNumChanged = currentDealNum != previousDealNum;

            if (!hasDealNum || dealNumChanged)
            {
                //create a new group
                currentGroup = new InvoiceLineGroup();
                allInvoiceLineGroups.Add(currentGroup);
                previousDealNum = currentDealNum;
            }
            //add line to current group
            currentGroup!.InvoiceLines.Add(line);
        }

        return allInvoiceLineGroups;
    }

    private class LineRequiredData
    {
        public List<MeterInfo> Meters { get; set; } = new();
        public Dictionary<int, string> PipelinesDict { get; set; } = new();
        public Dictionary<int, string> MeterNameDict { get; set; } = new();
    }

    private static async Task<LineRequiredData> GetLineSupportDataAsync()
    {
        using var db = Main.CreateContext();
        var data = new LineRequiredData();

        data.Meters = await DataHelper.GetMetersByProductAsync(Enums.ProductCategory.CrudeOil);
        data.PipelinesDict = await (from q in db.Pipelines select new { q.Id, q.PipeShort }).AsNoTracking().ToDictionaryAsync(x => x.Id, x => x.PipeShort);
        data.MeterNameDict = data.Meters.Select(x => new { x.MeterId, x.MeterName }).Distinct().ToDictionary(x => x.MeterId, x => x.MeterName);

        return data;
    }

    private static void AddGroupLines(InvoiceLineGroup group, Table table, LineRequiredData data)
    {
        foreach (var line in group.InvoiceLines)
        {
            //the template file table should already have a blank row for the first line
            //so we only add rows for subsequent lines
            if (line.LineNum != 1)
                AddBlankRow(table);

            var deliveryMeterName = "";
            if (line.DeliveryMeterId.HasValue && data.MeterNameDict.TryGetValue(line.DeliveryMeterId.Value, out var dName))
                deliveryMeterName = dName;
            else if (line.MeterCustomText != null)
                deliveryMeterName = line.MeterCustomText;

            var receiptMeterName = "";
            if (line.ReceiptMeterId.HasValue && data.MeterNameDict.TryGetValue(line.ReceiptMeterId.Value, out var rName))
                receiptMeterName = rName;

            SetTableColText(table, ColIndexReceiptMeter, receiptMeterName);
            SetTableColText(table, ColIndexDeliveryMeter, deliveryMeterName);
            SetTableColText(table, ColIndexInternalContract, line.InternalContractNum ?? "");
            SetTableColText(table, ColIndexExternalContract, line.CounterpartyContractNum ?? "");
            SetTableColText(table, ColIndexBasePrice, FormatNum(line.BasePrice, NumFormat.c5));
            SetTableColText(table, ColIndexRollPrice, FormatNum(line.RollPrice, NumFormat.c5));
            SetTableColText(table, ColIndexPriceAdj1, FormatNum(line.PriceAdj1, NumFormat.c5));
            SetTableColText(table, ColIndexPriceAdj2, FormatNum(line.PriceAdj2, NumFormat.c5));
            SetTableColText(table, ColIndexPipePercentage, FormatNum(line.PipelinePercentage, NumFormat.p5));
            SetTableColText(table, ColIndexPipeRate, FormatNum(line.PipelineRate, NumFormat.c5));
            SetTableColText(table, ColIndexPipeDeduct, FormatNum(line.PipelineDeduct, NumFormat.c5));
            SetTableColText(table, ColIndexNetPrice, FormatNum(line.NetPrice, NumFormat.c5));
            SetTableColText(table, ColIndexVolume, FormatNum(line.Quantity, NumFormat.n2));
            SetTableColText(table, ColIndexGrossAmount, FormatNum(line.GrossAmount, NumFormat.c2));
            SetTableColText(table, ColIndexBankRate, FormatNum(line.QbRate, NumFormat.c5));
            SetTableColText(table, ColIndexBankAmount, FormatNum(line.QbAmount, NumFormat.c2));
            SetTableColText(table, ColIndexFees, FormatNum(line.ActualFee, NumFormat.c2));
            SetTableColText(table, ColIndexInvoiceAmount, FormatNum(line.InvoiceAmount, NumFormat.c2));
        }
    }

    private static void AddGroupSubtotalLine(InvoiceLineGroup group, Table table)
    {
        if (group.InvoiceLines.Count <= 1)
        {
            //even if there is no subtotal being added, we still want a blank line for separation
            AddBlankRow(table);
            return;
        }

        //this is for the subtotal row
        AddBlankRow(table);

        var dealNum = group.InvoiceLines.First().Deal?.TicketNum ?? "";
        var totalVolume = group.InvoiceLines.Sum(x => x.Quantity);
        var totalAmount = group.InvoiceLines.Sum(x => x.InvoiceAmount);

        SetTableColText(table, ColIndexPipePercentage, $"SUBTOTAL FOR {dealNum}");
        SetTableColText(table, ColIndexVolume, FormatNum(totalVolume, NumFormat.n2));
        SetTableColText(table, ColIndexInvoiceAmount, FormatNum(totalAmount, NumFormat.c2));

        //this is for the blank row after the subtotal
        AddBlankRow(table);
    }

    private static void AddTaxLine(InvoiceCrude invoice, Table table)
    {
        if (invoice.SalesTaxAmount.GetValueOrDefault() != 0)
        {
            AddBlankRow(table);
            SetTableColText(table, ColIndexPipePercentage, "TAX");
            SetTableColText(table, ColIndexInvoiceAmount, FormatNum(invoice.SalesTaxAmount, NumFormat.c2));
        }
    }

    private static void AddGrandTotalLine(InvoiceCrude invoice, Table table)
    {
        AddBlankRow(table);
        SetTableColText(table, ColIndexPipePercentage, "TOTAL AMOUNT DUE", true);
        SetTableColText(table, ColIndexVolume, FormatNum(invoice.TotalQuantity, NumFormat.n2), true);
        SetTableColText(table, ColIndexInvoiceAmount, FormatNum(invoice.GrandTotal, NumFormat.c2), true);
    }

    private static void AddBlankRow(Table lineTable)
    {
        // clones the last row of the table and replaces any existing data with blanks
        var lastRow = lineTable.Elements<TableRow>().LastOrDefault()
            ?? throw new InvalidOperationException("No rows found in table to clone");

        var newRow = (TableRow)lastRow.CloneNode(true);
        var cells = newRow.Elements<TableCell>().ToList();

        SetTableCellText(cells[ColIndexReceiptMeter], "");
        SetTableCellText(cells[ColIndexDeliveryMeter], "");
        SetTableCellText(cells[ColIndexInternalContract], "");
        SetTableCellText(cells[ColIndexExternalContract], "");
        SetTableCellText(cells[ColIndexBasePrice], "");
        SetTableCellText(cells[ColIndexRollPrice], "");
        SetTableCellText(cells[ColIndexPriceAdj1], "");
        SetTableCellText(cells[ColIndexPriceAdj2], "");
        SetTableCellText(cells[ColIndexPipePercentage], "");
        SetTableCellText(cells[ColIndexPipeRate], "");
        SetTableCellText(cells[ColIndexPipeDeduct], "");
        SetTableCellText(cells[ColIndexNetPrice], "");
        SetTableCellText(cells[ColIndexVolume], "");
        SetTableCellText(cells[ColIndexGrossAmount], "");
        SetTableCellText(cells[ColIndexBankRate], "");
        SetTableCellText(cells[ColIndexBankAmount], "");
        SetTableCellText(cells[ColIndexFees], "");
        SetTableCellText(cells[ColIndexInvoiceAmount], "");

        // insert the new row after the last row
        lineTable.InsertAfter(newRow, lastRow);
    }

    private static void SetTableColText(Table table, int colIndex, string text, bool bold = false)
    {
        var lastRow = table.Elements<TableRow>().LastOrDefault()
            ?? throw new InvalidOperationException("No rows found in table");

        var cells = lastRow.Elements<TableCell>().ToList();

        if (colIndex >= cells.Count || colIndex < 0)
        {
            throw new ArgumentOutOfRangeException(nameof(colIndex),
                $"Column index {colIndex} is out of range. Table has {cells.Count} columns.");
        }

        var cell = cells[colIndex];
        SetTableCellText(cell, text, bold);
    }

    private static string GetCityStateZipStr(string? city, string? state, string? zip)
    {
        city = string.IsNullOrWhiteSpace(city) ? "" : city;
        state = string.IsNullOrWhiteSpace(state) ? "" : state;
        zip = string.IsNullOrWhiteSpace(zip) ? "" : " " + zip;
        var comma = city == "" ? "" : ", ";
        var cityStateZip = $"{city}{comma}{state}{zip}";
        return cityStateZip;
    }

    private string GetNewFileName(DateOnly month, int counterpartyId, string invoiceNum)
    {
        var monthStr = month.ToString("yyyy-MM");
        string counterparty = db.Counterparties.Where(x => x.Id == counterpartyId).Select(x => x.Name).First();
        counterparty = RegexAlphaNumeric.Replace(counterparty, "");
        string newFileName = $"{monthStr}-{counterparty}-{invoiceNum}.pdf";
        return newFileName;
    }

    private static readonly Regex RegexAlphaNumeric = new(@"[^0-9a-zA-Z]+", RegexOptions.Compiled);
}
