using System.Globalization;
using System.Runtime.CompilerServices;
using ComplianceDAL.DTO.CanonicalInvoice;
using ComplianceDAL.Query.CanonicalBuilder;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;

namespace ComplianceDAL.CustomCode.CanonicalBuilder;

public class CanonicalBuilderDAL : ICanonicalBuilderDAL
{
    private readonly IQueryExecutor _queryExecutor;
    private readonly ILogger<CanonicalBuilderDAL> _logger;

    // MBIZTRANSACTIONCLASSID -> IRP DocDtls.Typ. Only used by the batch path (GET_CANONICAL_INVOICES_BATCH),
    // which still returns the raw class id — the single-document header/detail queries now resolve this
    // to the IRP code directly in SQL (DocDtlsTyp), same as GB4's EInvoiceQueryBuilder.
    private static readonly Dictionary<int, string> BizClassToDocType = new()
    {
        [-1799999904] = "INV", // Sales Invoice
        [-1799999911] = "INV", // Miscellaneous Delivery Challan
        [-1399999924] = "INV", // Job Work Bill Passing
        [-1399999949] = "DBN", // Debit Note
        [-1399999948] = "CRN", // Credit Note
        [-1799999922] = "DBN", // Purchase Return
        [-1399999855] = "DBN", // Sub-Contract Bill Return
        [-1399999919] = "CRN", // Job Work Bill Return
        [-1799999903] = "CRN", // Sales Return
    };

    public CanonicalBuilderDAL(IQueryExecutor queryExecutor, ILogger<CanonicalBuilderDAL> logger)
    {
        _queryExecutor = queryExecutor;
        _logger        = logger;
    }

    public async Task<CanonicalInvoiceDTO?> GetCanonicalInvoice(int documentId, LoginDTO login, CancellationToken ct)
    {
        try
        {
            var headerRow = await _queryExecutor.QuerySingleAsync<dynamic>(
                login,
                CanonicalBuilderQB.GET_CANONICAL_INVOICE_HEADER,
                new { DocumentId = documentId, ClientId = login.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            if (headerRow is null) return null;

            var detailRows = await _queryExecutor.QueryAsync<dynamic>(
                login,
                CanonicalBuilderQB.GET_CANONICAL_INVOICE_DETAIL,
                new { DocumentId = documentId },
                cancellationToken: ct).ConfigureAwait(false);

            return MapToCanonicalInvoice(documentId, headerRow, detailRows);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Canonical builder DAL: GetCanonicalInvoice failed for DocumentId {DocumentId}", documentId);
            throw;
        }
    }

    public IAsyncEnumerable<CanonicalInvoiceDTO> GetCanonicalInvoicesBatch(
        DateTime fromDate, DateTime toDate, int ouId, int companyId, LoginDTO login, CancellationToken ct)
        => StreamCanonicalBatchAsync(CanonicalBuilderQB.GET_CANONICAL_INVOICES_BATCH, fromDate, toDate, ouId, companyId, login, ct);

    public IAsyncEnumerable<CanonicalInvoiceDTO> GetCanonicalPurchaseDocumentsBatch(
        DateTime fromDate, DateTime toDate, int ouId, int companyId, LoginDTO login, CancellationToken ct)
        => StreamCanonicalBatchAsync(CanonicalBuilderQB.GET_CANONICAL_PURCHASE_DOCUMENTS_BATCH, fromDate, toDate, ouId, companyId, login, ct);

    // Shared by both batch queries — the query already ORDERs BY DocumentId, so a document's rows are
    // yielded as soon as the next DocumentId is seen, rather than materializing the entire date-range
    // result set into a Dictionary before yielding the first invoice (the previous implementation did
    // this and was IAsyncEnumerable in signature only). Both queries produce the same output column
    // shape (SupplierGSTIN/ReceiverGSTIN etc. already resolved to the correct role by each query), so
    // MapBatchGroupToCanonicalInvoice needs no changes to serve either one.
    //
    // yield return is not permitted inside a try block that has a catch clause, so the enumerator is
    // driven manually here (same pattern as GSTCanonicalBuilderBLL.DriveBatch): MoveNextAsync (where
    // the query stream can throw, e.g. a SQL error) stays inside try/catch, and yield return stays
    // outside it in the enclosing try/finally.
    private async IAsyncEnumerable<CanonicalInvoiceDTO> StreamCanonicalBatchAsync(
        string sql, DateTime fromDate, DateTime toDate, int ouId, int companyId, LoginDTO login,
        [EnumeratorCancellation] CancellationToken ct)
    {
        int? currentDocId = null;
        List<dynamic> currentRows = [];

        var enumerator = _queryExecutor.QueryStreamAsync<dynamic>(
            login, sql,
            new { FromDate = fromDate, ToDate = toDate, OUId = ouId, CompanyId = companyId },
            ct).GetAsyncEnumerator(ct);

        try
        {
            while (true)
            {
                dynamic row;
                try
                {
                    if (!await enumerator.MoveNextAsync().ConfigureAwait(false))
                        break;
                    row = enumerator.Current;
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "Canonical builder DAL: StreamCanonicalBatchAsync failed for OuId {OuId}, FromDate {FromDate}, ToDate {ToDate}", ouId, fromDate, toDate);
                    throw;
                }

                int docId = (int)row.DocumentId;

                if (currentDocId.HasValue && docId != currentDocId.Value)
                {
                    var invoice = MapCompletedGroup(currentRows, currentDocId.Value);
                    if (invoice is not null) yield return invoice;
                    currentRows = [];
                }

                currentDocId = docId;
                currentRows.Add(row);
            }
        }
        finally
        {
            await enumerator.DisposeAsync().ConfigureAwait(false);
        }

        if (currentRows.Count > 0)
        {
            var invoice = MapCompletedGroup(currentRows, currentDocId!.Value);
            if (invoice is not null) yield return invoice;
        }
    }

    // yield return is not permitted inside a try block that has a catch clause, so per-document
    // mapping errors are caught here (a non-iterator method) rather than around the yield itself.
    private CanonicalInvoiceDTO? MapCompletedGroup(List<dynamic> rows, int documentId)
    {
        try
        {
            return MapBatchGroupToCanonicalInvoice(rows);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Canonical builder DAL: batch mapping failed for DocumentId {DocumentId}", documentId);
            throw;
        }
    }

    private CanonicalInvoiceDTO? MapToCanonicalInvoice(int documentId, dynamic headerRow, IEnumerable<dynamic> detailRows)
    {
        try
        {
            return MapRowsToCanonicalInvoice(documentId, headerRow, detailRows);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Canonical builder DAL: MapToCanonicalInvoice failed");
            throw;
        }
    }

    private static string MapDocumentType(int bizTransactionClassId)
        => BizClassToDocType.TryGetValue(bizTransactionClassId, out var docType) ? docType : "INV";

    // MGSTCATEGORY does not exist — GB5 uses the same mgcm table as GB4. Only the batch path
    // (GET_CANONICAL_INVOICES_BATCH) still needs this; the single-document header query resolves
    // TranDtlsSupTyp directly in SQL via the same CASE c.GCMCODE mapping GB4 uses.
    private static string MapSupplyType(string? gcmCode) => gcmCode switch
    {
        "1" or "3" or "4" or "10" or "11" or "20" or "21" or "25" or "28" or "29" => "B2B",
        "2"  => "B2C",
        "13" => "SEZWP",
        "14" or "18" or "26" => "SEZWOP",
        "5" or "19" or "27" => "EXPWP",
        "6" => "EXPWOP",
        "15" => "DEXP",
        _ => "*"
    };

    // Header/detail column names match GB4's EInvoiceQueryBuilder.GET_EINVOICE_HEADER/GET_EINVOICE_DETAIL
    // exactly (DocDtlsTyp, TranDtlsSupTyp, SellerDtls*, BuyerDtls*, ShipDtls*, ValDtls*, etc.) — see
    // CanonicalBuilderQB for the SQL. DocumentId/DocumentDate aren't selected as raw columns by that
    // query (GB4 never did either), so DocumentId comes from the call parameter and DocumentDate is
    // parsed back out of DocDtlsDt (CONVERT(VARCHAR, ..., 103) = "dd/MM/yyyy").
    private static CanonicalInvoiceDTO MapRowsToCanonicalInvoice(int documentId, dynamic h, IEnumerable<dynamic> rows)
    {
        var invoice = new CanonicalInvoiceDTO
        {
            DocumentId        = documentId,
            DocumentNumber    = (string)h.DocDtlsNo,
            DocumentDate      = DateTime.ParseExact((string)h.DocDtlsDt, "dd/MM/yyyy", CultureInfo.InvariantCulture),
            DocumentType      = (string)h.DocDtlsTyp,
            SupplyType        = (string)h.TranDtlsSupTyp,
            IsReverseCharge   = (string)h.TranDtlsRegRev == "Y",
            POS               = (string)h.BuyerDtlsPos,
            DiscountAmount    = (decimal)h.ValDtlsDiscAmt,
            OtherChargesAmount = (decimal)h.ValDtlsOtherCharge,
            RoundOffAmount    = (decimal)h.ValDtlsRndOffAmt,
            TotalTaxableValue = (decimal)h.ValDtlsAssVal,
            TotalIGSTAmount   = (decimal)h.ValDtlsIgstVal,
            TotalCGSTAmount   = (decimal)h.ValDtlsCgstVal,
            TotalSGSTAmount   = (decimal)h.ValDtlsSgstVal,
            TotalCessAmount   = (decimal)h.ValDtlsCesVal,
            TotalInvoiceValue = (decimal)h.ValDtlsTotInvVal,
            Supplier = new CanonicalPartyDTO
            {
                GSTIN     = (string)h.SellerDtlsGstin,
                LegalName = (string)h.SellerDtlsLglNm,
                TradeName = (string)h.SellerDtlsTrdNm,
                Address1  = (string)h.SellerDtlsAddr1,
                Address2  = (string)h.SellerDtlsAddr2,
                City      = (string)h.SellerDtlsLoc,
                Pincode   = (string)h.SellerDtlsPin,
                StateCode = (string)h.SellerDtlsState,
                Phone     = (string)h.SellerDtlsPh,
                Email     = (string)h.SellerDtlsEm
            },
            Receiver = new CanonicalPartyDTO
            {
                GSTIN     = (string)h.BuyerDtlsGstin,
                LegalName = (string)h.BuyerDtlsLglNm,
                TradeName = (string)h.BuyerDtlsTrdNm,
                Address1  = (string)h.BuyerDtlsAddr1,
                Address2  = (string)h.BuyerDtlsAddr2,
                City      = (string)h.BuyerDtlsLoc,
                Pincode   = (string)h.BuyerDtlsPin,
                StateCode = (string)h.BuyerDtlsState,
                Phone     = (string)h.BuyerDtlsPh,
                Email     = (string)h.BuyerDtlsEm
            }
        };

        // ShipDtlsGstin is '*' when the ship-to address is the same as bill-to (or absent) — same
        // sentinel GB4's EInvoiceDAL checks (`if (EInvoiceFlatDTOs[0].ShipDtlsGstin != "*")`).
        if ((string)h.ShipDtlsGstin != "*")
        {
            invoice.ShipTo = new CanonicalPartyDTO
            {
                GSTIN     = (string)h.ShipDtlsGstin,
                LegalName = (string)h.ShipDtlsLglNm,
                TradeName = (string)h.ShipDtlsTrdNm,
                Address1  = (string)h.ShipDtlsAddr1,
                Address2  = (string)h.ShipDtlsAddr2,
                City      = (string)h.ShipDtlsLoc,
                Pincode   = (string)h.ShipDtlsPin,
                StateCode = (string)h.ShipDtlsStcd
            };
        }

        foreach (var row in rows)
        {
            decimal gstRate  = (decimal)row.GstRt;
            decimal igstAmt  = (decimal)row.IgstAmt;
            decimal cgstAmt  = (decimal)row.CgstAmt;
            decimal sgstAmt  = (decimal)row.SgstAmt;

            var line = new CanonicalLineDTO
            {
                LineNumber   = int.Parse((string)row.SlNo, CultureInfo.InvariantCulture),
                ItemCode     = (string)row.Barcde,
                ItemName     = (string)row.PrdDesc,
                HSNCode      = (string)row.HsnCd,
                UOM          = (string)row.Unit,
                Quantity     = (decimal)row.Qty,
                Rate         = (decimal)row.UnitPrice,
                Value        = (decimal)row.TotAmt,
                Discount     = (decimal)row.Discount,
                OtherCharges = (decimal)row.OthChrg,
                TaxableValue = (decimal)row.AssAmt,
                // GstRt on TMMCHARGESPIVOT/detailcharge is a single combined rate (GB4's ItemList.GstRt
                // is likewise one field) — split it back to IGST vs CGST+SGST based on which amount is set.
                IGSTRate     = igstAmt > 0 ? gstRate : 0m,
                IGSTAmount   = igstAmt,
                CGSTRate     = igstAmt > 0 ? 0m : gstRate / 2,
                CGSTAmount   = cgstAmt,
                SGSTRate     = igstAmt > 0 ? 0m : gstRate / 2,
                SGSTAmount   = sgstAmt,
                CessRate     = (decimal)row.CesRt,
                CessAmount   = (decimal)row.CesAmt,
                ItemNature   = (string)row.IsServc == "Y" ? "Service" : "Goods"
            };

            invoice.Lines.Add(line);
        }

        return invoice;
    }

    // Batch rows come from the single flattened query (GET_CANONICAL_INVOICES_BATCH) — header fields
    // repeat on every row of the group, so they're read once from the first row.
    private static CanonicalInvoiceDTO? MapBatchGroupToCanonicalInvoice(List<dynamic> rows)
    {
        if (rows.Count == 0) return null;

        var first = rows[0];
        var invoice = new CanonicalInvoiceDTO
        {
            DocumentId     = (int)first.DocumentId,
            DocumentNumber = (string)first.DocumentNumber,
            DocumentDate   = (DateTime)first.DocumentDate,
            DocumentType   = MapDocumentType((int)first.BizTransactionClassId),
            SupplyType     = MapSupplyType((string?)first.GSTCategoryCode),
            Supplier = new CanonicalPartyDTO
            {
                GSTIN = (string)first.SupplierGSTIN
            },
            Receiver = new CanonicalPartyDTO
            {
                GSTIN     = (string)first.ReceiverGSTIN,
                LegalName = (string)first.ReceiverLegalName,
                Pincode   = (string)first.ReceiverPincode,
                StateCode = (string)first.ReceiverStateCode
            }
        };

        foreach (var row in rows)
        {
            var line = new CanonicalLineDTO
            {
                LineNumber   = (int)row.LineNumber,
                ItemId       = (int)row.ItemId,
                HSNCode      = (string)row.HSNCode,
                UOM          = (string)row.UOM,
                Quantity     = (decimal)row.Quantity,
                TaxableValue = (decimal)row.TaxableValue,
                IGSTRate     = (decimal)row.IGSTRate,
                IGSTAmount   = (decimal)row.IGSTAmount,
                CGSTRate     = (decimal)row.CGSTRate,
                CGSTAmount   = (decimal)row.CGSTAmount,
                SGSTRate     = (decimal)row.SGSTRate,
                SGSTAmount   = (decimal)row.SGSTAmount,
                CessRate     = (decimal)row.CessRate,
                CessAmount   = (decimal)row.CessAmount
            };

            invoice.Lines.Add(line);
            invoice.TotalTaxableValue += line.TaxableValue;
            invoice.TotalIGSTAmount  += line.IGSTAmount;
            invoice.TotalCGSTAmount  += line.CGSTAmount;
            invoice.TotalSGSTAmount  += line.SGSTAmount;
            invoice.TotalCessAmount  += line.CessAmount;
        }

        invoice.TotalInvoiceValue = invoice.TotalTaxableValue + invoice.TotalIGSTAmount
            + invoice.TotalCGSTAmount + invoice.TotalSGSTAmount + invoice.TotalCessAmount;

        return invoice;
    }
}