using CashLineERP.Application.Tax;
using CashLineERP.Domain.Finance;
using CashLineERP.Domain.Organization;
using CashLineERP.Domain.Parties;
using CashLineERP.Domain.Procurement;
using CashLineERP.Domain.Sales;
using CashLineERP.SharedKernel.Tax;
using CashLineERP.SharedKernel.Workflow;
using Microsoft.EntityFrameworkCore;
using System.Text;
using System.Text.Json;

namespace CashLineERP.Infrastructure.Persistence.Repositories;

public sealed class StatutoryTaxRepository : IStatutoryTaxRepository
{
    private static readonly JsonSerializerOptions JsonOptions = new() { WriteIndented = true };
    private readonly CashLineErpDbContext _dbContext;

    public StatutoryTaxRepository(CashLineErpDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public DocumentTaxSummaryView? GetAccountsPayableBillTaxSummary(long accountsPayableBillId)
    {
        var bill = _dbContext.AccountsPayableBills
            .AsNoTracking()
            .Include(existingBill => existingBill.Lines)
            .SingleOrDefault(existingBill => existingBill.Id == accountsPayableBillId);

        return bill is null
            ? null
            : BuildAccountsPayableBillSummary(bill);
    }

    public DocumentTaxSummaryView? GetAccountsReceivableInvoiceTaxSummary(long accountsReceivableInvoiceId)
    {
        var invoice = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Include(existingInvoice => existingInvoice.Lines)
            .SingleOrDefault(existingInvoice => existingInvoice.Id == accountsReceivableInvoiceId);

        return invoice is null
            ? null
            : BuildAccountsReceivableInvoiceSummary(invoice);
    }

    public DocumentVatBreakdownExportView? GetAccountsPayableBillVatBreakdownExport(long accountsPayableBillId)
    {
        var bill = _dbContext.AccountsPayableBills
            .AsNoTracking()
            .Include(existingBill => existingBill.Lines)
            .SingleOrDefault(existingBill => existingBill.Id == accountsPayableBillId);

        if (bill is null)
        {
            return null;
        }

        var summary = BuildAccountsPayableBillSummary(bill);
        return BuildVatBreakdownExport(
            summary,
            BuildSafeFileName($"ap-bill-{bill.BillNumber}-vat-breakdown.csv"));
    }

    public DocumentVatBreakdownExportView? GetAccountsReceivableInvoiceVatBreakdownExport(long accountsReceivableInvoiceId)
    {
        var invoice = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Include(existingInvoice => existingInvoice.Lines)
            .SingleOrDefault(existingInvoice => existingInvoice.Id == accountsReceivableInvoiceId);

        if (invoice is null)
        {
            return null;
        }

        var summary = BuildAccountsReceivableInvoiceSummary(invoice);
        return BuildVatBreakdownExport(
            summary,
            BuildSafeFileName($"ar-invoice-{invoice.InvoiceNumber}-vat-breakdown.csv"));
    }

    public DocumentWithholdingSummaryView? GetAccountsPayableBillWithholdingSummary(long accountsPayableBillId)
    {
        var bill = _dbContext.AccountsPayableBills
            .AsNoTracking()
            .Include(existingBill => existingBill.Lines)
            .SingleOrDefault(existingBill => existingBill.Id == accountsPayableBillId);

        return bill is null
            ? null
            : BuildAccountsPayableBillWithholdingSummary(bill);
    }

    public DocumentWithholdingSummaryView? GetAccountsReceivableInvoiceWithholdingSummary(long accountsReceivableInvoiceId)
    {
        var invoice = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Include(existingInvoice => existingInvoice.Lines)
            .SingleOrDefault(existingInvoice => existingInvoice.Id == accountsReceivableInvoiceId);

        return invoice is null
            ? null
            : BuildAccountsReceivableInvoiceWithholdingSummary(invoice);
    }

    public DocumentWithholdingExportView? GetAccountsPayableBillWithholdingExport(long accountsPayableBillId)
    {
        var bill = _dbContext.AccountsPayableBills
            .AsNoTracking()
            .Include(existingBill => existingBill.Lines)
            .SingleOrDefault(existingBill => existingBill.Id == accountsPayableBillId);

        if (bill is null)
        {
            return null;
        }

        var summary = BuildAccountsPayableBillWithholdingSummary(bill);
        return BuildWithholdingExport(
            summary,
            BuildSafeFileName($"ap-bill-{bill.BillNumber}-withholding.csv"));
    }

    public DocumentWithholdingExportView? GetAccountsReceivableInvoiceWithholdingExport(long accountsReceivableInvoiceId)
    {
        var invoice = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Include(existingInvoice => existingInvoice.Lines)
            .SingleOrDefault(existingInvoice => existingInvoice.Id == accountsReceivableInvoiceId);

        if (invoice is null)
        {
            return null;
        }

        var summary = BuildAccountsReceivableInvoiceWithholdingSummary(invoice);
        return BuildWithholdingExport(
            summary,
            BuildSafeFileName($"ar-invoice-{invoice.InvoiceNumber}-withholding.csv"));
    }

    public StatutoryEvidenceExportView? GetAccountsPayableBillStatutoryEvidenceExport(long accountsPayableBillId)
    {
        var bill = _dbContext.AccountsPayableBills
            .AsNoTracking()
            .Include(existingBill => existingBill.Lines)
            .SingleOrDefault(existingBill => existingBill.Id == accountsPayableBillId);

        if (bill is null)
        {
            return null;
        }

        var summary = BuildAccountsPayableBillSummary(bill);
        var branch = ResolveAccountsPayableBranch(bill);
        var supplier = _dbContext.BusinessParties
            .AsNoTracking()
            .SingleOrDefault(existingParty => existingParty.Id == bill.SupplierId);
        var goodsReceipt = bill.GoodsReceiptId.HasValue
            ? _dbContext.GoodsReceipts.AsNoTracking().SingleOrDefault(entry => entry.Id == bill.GoodsReceiptId.Value)
            : null;
        var purchaseOrder = bill.PurchaseOrderId.HasValue
            ? _dbContext.PurchaseOrders.AsNoTracking().SingleOrDefault(entry => entry.Id == bill.PurchaseOrderId.Value)
            : null;
        var evidencePayload = new
        {
            document = new
            {
                summary.DocumentType,
                summary.DocumentId,
                summary.DocumentNumber,
                summary.DocumentDate,
                summary.TaxDate,
                summary.CurrencyCode,
                summary.SubtotalAmount,
                summary.TaxAmount,
                summary.TotalAmount
            },
            financeReview = new
            {
                reviewPerspective = "PURCHASE",
                isStatutoryReady = summary.IsStatutoryReady,
                validationMessages = summary.ValidationMessages,
                generatedAtUtc = DateTime.UtcNow
            },
            branch = branch is null
                ? null
                : new
                {
                    branch.BranchCode,
                    branch.BranchName,
                    branch.TaxBranchCode,
                    branch.CountryCode,
                    branch.BranchType,
                    branch.IsActive
                },
            counterparty = supplier is null
                ? null
                : new
                {
                    supplier.PartyCode,
                    supplier.PartyName,
                    supplier.PartyType,
                    supplier.TaxRegistrationNumber,
                    supplier.TaxBranchCode,
                    supplier.CountryCode,
                    supplier.AddressLine1,
                    supplier.Email,
                    supplier.MobileNumber,
                    supplier.IsActive
                },
            supportingReferences = new
            {
                bill.PurchaseOrderId,
                purchaseOrderNumber = purchaseOrder?.PurchaseOrderNumber,
                bill.GoodsReceiptId,
                goodsReceiptNumber = goodsReceipt?.GoodsReceiptNumber,
                goodsReceiptReceivedAtUtc = goodsReceipt?.ReceivedAtUtc
            },
            payablesControl = new
            {
                workflowStatusId = bill.StatusId,
                bill.ThreeWayMatchStatus,
                bill.MatchVarianceAmount,
                bill.MatchNotes,
                bill.MatchedAtUtc,
                bill.MatchedByUserId,
                bill.ApprovedAtUtc,
                bill.ApprovedByUserId,
                bill.PostedAtUtc,
                bill.PostedByUserId,
                bill.GeneralLedgerBatchId
            },
            vatBreakdown = summary.Breakdowns
        };

        return BuildStatutoryEvidenceExport(
            summary,
            counterparty: supplier,
            branch: branch,
            fileName: BuildSafeFileName($"ap-bill-{bill.BillNumber}-statutory-evidence.json"),
            evidencePayload);
    }

    public StatutoryEvidenceExportView? GetAccountsReceivableInvoiceStatutoryEvidenceExport(long accountsReceivableInvoiceId)
    {
        var invoice = _dbContext.AccountsReceivableInvoices
            .AsNoTracking()
            .Include(existingInvoice => existingInvoice.Lines)
            .SingleOrDefault(existingInvoice => existingInvoice.Id == accountsReceivableInvoiceId);

        if (invoice is null)
        {
            return null;
        }

        var summary = BuildAccountsReceivableInvoiceSummary(invoice);
        var branch = ResolveAccountsReceivableBranch(invoice);
        var customer = _dbContext.BusinessParties
            .AsNoTracking()
            .SingleOrDefault(existingParty => existingParty.Id == invoice.CustomerId);
        var etaPayload = EtaPayloadBuilder.Build(invoice, customer, branch);
        var evidencePayload = new
        {
            document = new
            {
                summary.DocumentType,
                summary.DocumentId,
                summary.DocumentNumber,
                invoice.InvoiceType,
                summary.DocumentDate,
                summary.TaxDate,
                summary.CurrencyCode,
                summary.SubtotalAmount,
                summary.TaxAmount,
                summary.TotalAmount
            },
            financeReview = new
            {
                reviewPerspective = "SALES",
                isStatutoryReady = summary.IsStatutoryReady,
                validationMessages = summary.ValidationMessages,
                generatedAtUtc = DateTime.UtcNow
            },
            branch = branch is null
                ? null
                : new
                {
                    branch.BranchCode,
                    branch.BranchName,
                    branch.TaxBranchCode,
                    branch.CountryCode,
                    branch.BranchType,
                    branch.IsActive
                },
            counterparty = customer is null
                ? null
                : new
                {
                    customer.PartyCode,
                    customer.PartyName,
                    customer.PartyType,
                    customer.TaxRegistrationNumber,
                    customer.TaxBranchCode,
                    customer.CountryCode,
                    customer.AddressLine1,
                    customer.Email,
                    customer.MobileNumber,
                    customer.IsActive
                },
            etaReadiness = new
            {
                normalizedDocumentType = etaPayload.NormalizedDocumentType,
                etaPayload.IsStatutoryReady,
                etaPayload.ValidationMessages,
                invoice.EtaStatus,
                invoice.EtaUuid,
                invoice.GeneralLedgerBatchId,
                invoice.SalesOrderId,
                invoice.DispatchOrderId
            },
            etaPayloadPreview = ParseJson(etaPayload.PayloadText),
            vatBreakdown = summary.Breakdowns
        };

        return BuildStatutoryEvidenceExport(
            summary,
            counterparty: customer,
            branch: branch,
            fileName: BuildSafeFileName($"ar-invoice-{invoice.InvoiceNumber}-statutory-evidence.json"),
            evidencePayload);
    }

    private DocumentTaxSummaryView BuildAccountsPayableBillSummary(AccountsPayableBill bill) =>
        BuildSummary(
            documentType: "AP_BILL",
            documentId: bill.Id,
            documentNumber: bill.BillNumber,
            documentDate: bill.BillDate,
            taxDate: bill.BillDate,
            currencyCode: bill.CurrencyCode,
            subtotalAmount: bill.SubtotalAmount,
            taxAmount: bill.TaxAmount,
            totalAmount: bill.TotalAmount,
            lineTaxes: bill.Lines.Select(line => (line.TaxCode, line.LineNetAmount, line.LineTaxAmount)).ToArray(),
            additionalValidations:
            [
                bill.Lines.Count == 0 ? "AP bill must contain at least one bill line." : null,
                bill.GoodsReceiptId is null ? "AP bill should retain linked goods receipt evidence for statutory audit traceability." : null,
                bill.StatusId == WorkflowStatusSeedIds.Draft ? "AP bill is still draft and not yet in a controlled posting state." : null
            ]);

    private DocumentTaxSummaryView BuildAccountsReceivableInvoiceSummary(AccountsReceivableInvoice invoice) =>
        BuildSummary(
            documentType: "AR_INVOICE",
            documentId: invoice.Id,
            documentNumber: invoice.InvoiceNumber,
            documentDate: invoice.InvoiceDate,
            taxDate: invoice.TaxDate,
            currencyCode: invoice.CurrencyCode,
            subtotalAmount: invoice.SubtotalAmount,
            taxAmount: invoice.TaxAmount,
            totalAmount: invoice.TotalAmount,
            lineTaxes: invoice.Lines.Select(line => (line.TaxCode, line.LineNetAmount, line.LineTaxAmount)).ToArray(),
            additionalValidations:
            [
                invoice.Lines.Count == 0 ? "AR invoice must contain at least one invoice line." : null,
                string.Equals(invoice.InvoiceType, "TAX", StringComparison.OrdinalIgnoreCase) && invoice.TaxDate is null
                    ? "Tax invoice requires tax date before statutory submission."
                    : null,
                invoice.StatusId != WorkflowStatusSeedIds.Posted
                    ? "AR invoice must be posted before statutory submission or ETA transmission."
                    : null
            ]);

    private DocumentWithholdingSummaryView BuildAccountsPayableBillWithholdingSummary(AccountsPayableBill bill) =>
        BuildWithholdingSummary(
            documentType: "AP_BILL",
            documentId: bill.Id,
            documentNumber: bill.BillNumber,
            documentDate: bill.BillDate,
            taxDate: bill.BillDate,
            currencyCode: bill.CurrencyCode,
            subtotalAmount: bill.SubtotalAmount,
            documentTotalAmount: bill.TotalAmount,
            lineWithholdings: bill.Lines.Select(line => (line.TaxCode, line.LineNetAmount)).ToArray(),
            additionalValidations:
            [
                bill.Lines.Count == 0 ? "AP bill must contain at least one bill line." : null,
                bill.Lines.All(line => !StatutoryTaxPolicy.IsWithholdingRecognized(line.TaxCode))
                    ? "AP bill does not contain withholding-coded lines."
                    : null
            ]);

    private DocumentWithholdingSummaryView BuildAccountsReceivableInvoiceWithholdingSummary(AccountsReceivableInvoice invoice) =>
        BuildWithholdingSummary(
            documentType: "AR_INVOICE",
            documentId: invoice.Id,
            documentNumber: invoice.InvoiceNumber,
            documentDate: invoice.InvoiceDate,
            taxDate: invoice.TaxDate,
            currencyCode: invoice.CurrencyCode,
            subtotalAmount: invoice.SubtotalAmount,
            documentTotalAmount: invoice.TotalAmount,
            lineWithholdings: invoice.Lines.Select(line => (line.TaxCode, line.LineNetAmount)).ToArray(),
            additionalValidations:
            [
                invoice.Lines.Count == 0 ? "AR invoice must contain at least one invoice line." : null,
                invoice.Lines.All(line => !StatutoryTaxPolicy.IsWithholdingRecognized(line.TaxCode))
                    ? "AR invoice does not contain withholding-coded lines."
                    : null
            ]);

    private static DocumentTaxSummaryView BuildSummary(
        string documentType,
        long documentId,
        string documentNumber,
        DateOnly documentDate,
        DateOnly? taxDate,
        string currencyCode,
        decimal subtotalAmount,
        decimal taxAmount,
        decimal totalAmount,
        IReadOnlyCollection<(string? TaxCode, decimal TaxableAmount, decimal TaxAmount)> lineTaxes,
        IReadOnlyCollection<string?> additionalValidations)
    {
        var validations = new List<string>();

        foreach (var line in lineTaxes)
        {
            if (string.IsNullOrWhiteSpace(line.TaxCode))
            {
                validations.Add("All statutory lines must carry a tax code.");
                continue;
            }

            if (!StatutoryTaxPolicy.IsRecognized(line.TaxCode))
            {
                validations.Add($"Tax code '{line.TaxCode}' is not recognized by the current statutory policy baseline.");
            }
        }

        validations.AddRange(additionalValidations.Where(message => !string.IsNullOrWhiteSpace(message))!);

        var breakdowns = lineTaxes
            .GroupBy(line => StatutoryTaxPolicy.NormalizeTaxCode(line.TaxCode))
            .OrderBy(group => group.Key)
            .Select(group =>
            {
                var normalizedCode = string.IsNullOrWhiteSpace(group.Key) ? "UNSPECIFIED" : group.Key;
                var taxableBase = group.Sum(line => line.TaxableAmount);
                var groupTaxAmount = group.Sum(line => line.TaxAmount);

                return new DocumentTaxBreakdownView(
                    normalizedCode,
                    StatutoryTaxPolicy.ResolveCategory(normalizedCode),
                    StatutoryTaxPolicy.ResolveRate(normalizedCode),
                    taxableBase,
                    groupTaxAmount,
                    taxableBase + groupTaxAmount,
                    group.Count());
            })
            .ToArray();

        return new DocumentTaxSummaryView(
            documentType,
            documentId,
            documentNumber,
            documentDate,
            taxDate,
            currencyCode,
            subtotalAmount,
            taxAmount,
            totalAmount,
            validations.Count == 0,
            validations.Distinct(StringComparer.OrdinalIgnoreCase).ToArray(),
            breakdowns);
    }

    private static DocumentVatBreakdownExportView BuildVatBreakdownExport(DocumentTaxSummaryView summary, string fileName)
    {
        var exportedAtUtc = DateTime.UtcNow;
        var csv = new StringBuilder();
        csv.AppendLine("DocumentType,DocumentNumber,DocumentDate,TaxDate,CurrencyCode,TaxCode,TaxCategory,TaxRate,TaxableAmount,TaxAmount,TotalAmount,LineCount");

        foreach (var breakdown in summary.Breakdowns)
        {
            csv.AppendLine(string.Join(",",
                EscapeCsv(summary.DocumentType),
                EscapeCsv(summary.DocumentNumber),
                EscapeCsv(summary.DocumentDate.ToString("yyyy-MM-dd")),
                EscapeCsv(summary.TaxDate?.ToString("yyyy-MM-dd") ?? string.Empty),
                EscapeCsv(summary.CurrencyCode),
                EscapeCsv(breakdown.TaxCode),
                EscapeCsv(breakdown.TaxCategory),
                breakdown.TaxRate.ToString("0.####"),
                breakdown.TaxableAmount.ToString("0.##"),
                breakdown.TaxAmount.ToString("0.##"),
                breakdown.TotalAmount.ToString("0.##"),
                breakdown.LineCount.ToString()));
        }

        return new DocumentVatBreakdownExportView(
            summary.DocumentType,
            summary.DocumentId,
            summary.DocumentNumber,
            summary.DocumentDate,
            summary.TaxDate,
            summary.CurrencyCode,
            exportedAtUtc,
            fileName,
            "text/csv",
            csv.ToString(),
            summary.Breakdowns);
    }

    private static DocumentWithholdingSummaryView BuildWithholdingSummary(
        string documentType,
        long documentId,
        string documentNumber,
        DateOnly documentDate,
        DateOnly? taxDate,
        string currencyCode,
        decimal subtotalAmount,
        decimal documentTotalAmount,
        IReadOnlyCollection<(string? TaxCode, decimal BaseAmount)> lineWithholdings,
        IReadOnlyCollection<string?> additionalValidations)
    {
        var validations = new List<string>();

        foreach (var line in lineWithholdings)
        {
            if (string.IsNullOrWhiteSpace(line.TaxCode))
            {
                continue;
            }

            var normalizedCode = StatutoryTaxPolicy.NormalizeTaxCode(line.TaxCode);
            if (normalizedCode.StartsWith("WHT", StringComparison.OrdinalIgnoreCase) &&
                !StatutoryTaxPolicy.IsWithholdingRecognized(normalizedCode))
            {
                validations.Add($"Withholding code '{line.TaxCode}' is not recognized by the current statutory withholding baseline.");
            }
        }

        validations.AddRange(additionalValidations.Where(message => !string.IsNullOrWhiteSpace(message))!);

        var breakdowns = lineWithholdings
            .Where(line => StatutoryTaxPolicy.IsWithholdingRecognized(line.TaxCode))
            .GroupBy(line => StatutoryTaxPolicy.NormalizeTaxCode(line.TaxCode))
            .OrderBy(group => group.Key)
            .Select(group =>
            {
                var normalizedCode = group.Key;
                var baseAmount = group.Sum(line => line.BaseAmount);
                var withholdingAmount = group.Sum(line => StatutoryTaxPolicy.CalculateWithholding(line.BaseAmount, normalizedCode));

                return new DocumentWithholdingBreakdownView(
                    normalizedCode,
                    StatutoryTaxPolicy.ResolveWithholdingCategory(normalizedCode),
                    StatutoryTaxPolicy.ResolveWithholdingRate(normalizedCode),
                    baseAmount,
                    withholdingAmount,
                    group.Count());
            })
            .ToArray();

        var totalWithholdingAmount = breakdowns.Sum(entry => entry.WithholdingAmount);
        return new DocumentWithholdingSummaryView(
            documentType,
            documentId,
            documentNumber,
            documentDate,
            taxDate,
            currencyCode,
            subtotalAmount,
            documentTotalAmount,
            totalWithholdingAmount,
            documentTotalAmount - totalWithholdingAmount,
            validations.Count == 0,
            validations.Distinct(StringComparer.OrdinalIgnoreCase).ToArray(),
            breakdowns);
    }

    private static DocumentWithholdingExportView BuildWithholdingExport(DocumentWithholdingSummaryView summary, string fileName)
    {
        var exportedAtUtc = DateTime.UtcNow;
        var csv = new StringBuilder();
        csv.AppendLine("DocumentType,DocumentNumber,DocumentDate,TaxDate,CurrencyCode,DocumentTotalAmount,TotalWithholdingAmount,NetSettlementAmount,WithholdingCode,WithholdingCategory,WithholdingRate,BaseAmount,WithholdingAmount,LineCount");

        foreach (var breakdown in summary.Breakdowns)
        {
            csv.AppendLine(string.Join(",",
                EscapeCsv(summary.DocumentType),
                EscapeCsv(summary.DocumentNumber),
                EscapeCsv(summary.DocumentDate.ToString("yyyy-MM-dd")),
                EscapeCsv(summary.TaxDate?.ToString("yyyy-MM-dd") ?? string.Empty),
                EscapeCsv(summary.CurrencyCode),
                summary.DocumentTotalAmount.ToString("0.##"),
                summary.TotalWithholdingAmount.ToString("0.##"),
                summary.NetSettlementAmount.ToString("0.##"),
                EscapeCsv(breakdown.WithholdingCode),
                EscapeCsv(breakdown.WithholdingCategory),
                breakdown.WithholdingRate.ToString("0.####"),
                breakdown.BaseAmount.ToString("0.##"),
                breakdown.WithholdingAmount.ToString("0.##"),
                breakdown.LineCount.ToString()));
        }

        return new DocumentWithholdingExportView(
            summary.DocumentType,
            summary.DocumentId,
            summary.DocumentNumber,
            summary.DocumentDate,
            summary.TaxDate,
            summary.CurrencyCode,
            summary.SubtotalAmount,
            summary.DocumentTotalAmount,
            summary.TotalWithholdingAmount,
            summary.NetSettlementAmount,
            exportedAtUtc,
            fileName,
            "text/csv",
            csv.ToString(),
            summary.Breakdowns);
    }

    private static StatutoryEvidenceExportView BuildStatutoryEvidenceExport(
        DocumentTaxSummaryView summary,
        BusinessParty? counterparty,
        Branch? branch,
        string fileName,
        object evidencePayload)
    {
        var exportedAtUtc = DateTime.UtcNow;
        return new StatutoryEvidenceExportView(
            summary.DocumentType,
            summary.DocumentId,
            summary.DocumentNumber,
            summary.DocumentDate,
            summary.TaxDate,
            summary.CurrencyCode,
            counterparty?.PartyCode ?? "UNKNOWN",
            counterparty?.PartyName ?? "Unknown counterparty",
            counterparty?.PartyType ?? "UNKNOWN",
            counterparty?.TaxRegistrationNumber,
            branch?.BranchCode,
            branch?.BranchName,
            branch?.TaxBranchCode,
            summary.IsStatutoryReady,
            exportedAtUtc,
            fileName,
            "application/json",
            JsonSerializer.Serialize(evidencePayload, JsonOptions),
            summary.ValidationMessages);
    }

    private Branch? ResolveAccountsPayableBranch(AccountsPayableBill bill)
    {
        long? branchId = bill.GoodsReceiptId.HasValue
            ? _dbContext.GoodsReceipts
                .AsNoTracking()
                .Where(entry => entry.Id == bill.GoodsReceiptId.Value)
                .Select(entry => (long?)entry.BranchId)
                .SingleOrDefault()
            : null;

        branchId ??= bill.PurchaseOrderId.HasValue
            ? _dbContext.PurchaseOrders
                .AsNoTracking()
                .Where(entry => entry.Id == bill.PurchaseOrderId.Value)
                .Select(entry => (long?)entry.BranchId)
                .SingleOrDefault()
            : null;

        return branchId.HasValue
            ? _dbContext.Branches.AsNoTracking().SingleOrDefault(entry => entry.Id == branchId.Value)
            : null;
    }

    private Branch? ResolveAccountsReceivableBranch(AccountsReceivableInvoice invoice)
    {
        long? branchId = invoice.DispatchOrderId.HasValue
            ? _dbContext.DispatchOrders
                .AsNoTracking()
                .Where(entry => entry.Id == invoice.DispatchOrderId.Value)
                .Select(entry => (long?)entry.BranchId)
                .SingleOrDefault()
            : null;

        branchId ??= invoice.SalesOrderId.HasValue
            ? _dbContext.SalesOrders
                .AsNoTracking()
                .Where(entry => entry.Id == invoice.SalesOrderId.Value)
                .Select(entry => (long?)entry.BranchId)
                .SingleOrDefault()
            : null;

        return branchId.HasValue
            ? _dbContext.Branches.AsNoTracking().SingleOrDefault(entry => entry.Id == branchId.Value)
            : null;
    }

    private static JsonElement ParseJson(string payloadText)
    {
        using var document = JsonDocument.Parse(payloadText);
        return document.RootElement.Clone();
    }

    private static string EscapeCsv(string value)
    {
        var escaped = value.Replace("\"", "\"\"");
        return $"\"{escaped}\"";
    }

    private static string BuildSafeFileName(string rawName) =>
        rawName.Replace(" ", "-", StringComparison.Ordinal).ToLowerInvariant();
}
