namespace ComplianceDAL.Query.GSTOutward;

public static class GSTOutwardQB
{
    public const string GET_GSTOUTWARD = @"
        SELECT  o.OUTWARDID             OutwardId,
                o.ARTIFACTID            ArtifactId,
                o.GSTIN                 GSTIN,
                o.RETURNPERIOD          ReturnPeriod,
                o.SOURCEDOCUMENTID      SourceDocumentId,
                o.INVOICENUMBER         InvoiceNumber,
                o.INVOICEDATE           InvoiceDate,
                o.INVOICEVALUE          InvoiceValue,
                o.RECEIVERGSTIN         ReceiverGSTIN,
                o.RECEIVERNAME          ReceiverName,
                o.POS                   POS,
                o.SUPPLYTYPE            SupplyType,
                o.INVOICETYPE           InvoiceType,
                o.HSNCODE               HSNCode,
                o.UOM                   UOM,
                o.QUANTITY              Quantity,
                o.TAXABLEVALUE          TaxableValue,
                o.IGSTRATE              IGSTRate,
                o.IGSTAMOUNT            IGSTAmount,
                o.CGSTRATE              CGSTRate,
                o.CGSTAMOUNT            CGSTAmount,
                o.SGSTRATE              SGSTRate,
                o.SGSTAMOUNT            SGSTAmount,
                o.CESSRATE              CessRate,
                o.CESSAMOUNT            CessAmount,
                o.FILINGSTATUS          FilingStatus,
                o.ACKREFERENCENUMBER    AckReferenceNumber,
                o.ORIGINALOUTWARDID     OriginalOutwardId,
                o.CLIENTID              ClientId,
                o.DATABASENAME          DatabaseName,
                o.CREATEDBYID           CreatedById,
                o.CREATEDON             CreatedOn,
                o.MODIFIEDBYID          ModifiedById,
                o.MODIFIEDON            ModifiedOn
        FROM    TGST_OUTWARD o
        WHERE   o.OUTWARDID    = @OutwardId
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName";

    // Filtered list — supports GSTIN, period, invoice type, filing status filters
    // Indexes: IX_OUTWARD_GSTIN_PERIOD, IX_OUTWARD_INVOICETYPE
    public const string GET_GSTOUTWARD_LIST = @"
        SELECT  o.OUTWARDID             OutwardId,
                o.GSTIN                 GSTIN,
                o.RETURNPERIOD          ReturnPeriod,
                o.SOURCEDOCUMENTID      SourceDocumentId,
                o.INVOICENUMBER         InvoiceNumber,
                o.INVOICEDATE           InvoiceDate,
                o.INVOICEVALUE          InvoiceValue,
                o.RECEIVERGSTIN         ReceiverGSTIN,
                o.RECEIVERNAME          ReceiverName,
                o.INVOICETYPE           InvoiceType,
                o.TAXABLEVALUE          TaxableValue,
                o.IGSTAMOUNT            IGSTAmount,
                o.CGSTAMOUNT            CGSTAmount,
                o.SGSTAMOUNT            SGSTAmount,
                o.CESSAMOUNT            CessAmount,
                o.FILINGSTATUS          FilingStatus,
                o.ACKREFERENCENUMBER    AckReferenceNumber
        FROM    TGST_OUTWARD o
        WHERE   o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        AND     (@GSTIN        = '' OR o.GSTIN        = @GSTIN)
        AND     (@ReturnPeriod = '' OR o.RETURNPERIOD = @ReturnPeriod)
        AND     (@InvoiceType  = '' OR o.INVOICETYPE  = @InvoiceType)
        AND     (@FilingStatus = -1  OR o.FILINGSTATUS = @FilingStatus)
        ORDER BY o.INVOICEDATE DESC, o.INVOICENUMBER
        OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_GSTOUTWARD_LIST_COUNT = @"
        SELECT  COUNT(*)
        FROM    TGST_OUTWARD o
        WHERE   o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        AND     (@GSTIN        = '' OR o.GSTIN        = @GSTIN)
        AND     (@ReturnPeriod = '' OR o.RETURNPERIOD = @ReturnPeriod)
        AND     (@InvoiceType  = '' OR o.INVOICETYPE  = @InvoiceType)
        AND     (@FilingStatus = -1  OR o.FILINGSTATUS = @FilingStatus)";

    public const string SAVE_GSTOUTWARD = @"
        INSERT INTO TGST_OUTWARD (
            ARTIFACTID, GSTIN, RETURNPERIOD, SOURCEDOCUMENTID,
            INVOICENUMBER, INVOICEDATE, INVOICEVALUE,
            RECEIVERGSTIN, RECEIVERNAME, POS, SUPPLYTYPE, INVOICETYPE,
            HSNCODE, UOM, QUANTITY,
            TAXABLEVALUE, IGSTRATE, IGSTAMOUNT, CGSTRATE, CGSTAMOUNT,
            SGSTRATE, SGSTAMOUNT, CESSRATE, CESSAMOUNT,
            FILINGSTATUS, ACKREFERENCENUMBER, ORIGINALOUTWARDID,
            CLIENTID, DATABASENAME, CREATEDBYID, CREATEDON
        )
        OUTPUT INSERTED.OUTWARDID
        VALUES (
            @ArtifactId, @GSTIN, @ReturnPeriod, @SourceDocumentId,
            @InvoiceNumber, @InvoiceDate, @InvoiceValue,
            @ReceiverGSTIN, @ReceiverName, @POS, @SupplyType, @InvoiceType,
            @HSNCode, @UOM, @Quantity,
            @TaxableValue, @IGSTRate, @IGSTAmount, @CGSTRate, @CGSTAmount,
            @SGSTRate, @SGSTAmount, @CessRate, @CessAmount,
            0, '', @OriginalOutwardId,
            @ClientId, @DatabaseName, @CreatedById, GETUTCDATE()
        )";

    // Upsert for SyncGSTOutward — MERGE on (SourceDocumentId, GSTIN, ReturnPeriod, tenant) while
    // FilingStatus < 2 (port of the Postgres partial-unique-index upsert; UX_OUTWARD_SOURCE_PERIOD
    // is itself a filtered index in the SQL Server migration, same predicate).
    public const string UPSERT_GSTOUTWARD = @"
        MERGE TGST_OUTWARD AS target
        USING (SELECT @SourceDocumentId AS SOURCEDOCUMENTID, @GSTIN AS GSTIN, @ReturnPeriod AS RETURNPERIOD) AS src
        ON  target.SOURCEDOCUMENTID = src.SOURCEDOCUMENTID
        AND target.GSTIN            = src.GSTIN
        AND target.RETURNPERIOD     = src.RETURNPERIOD
        AND target.CLIENTID         = @ClientId
        AND target.DATABASENAME     = @DatabaseName
        AND target.FILINGSTATUS     < 2
        WHEN MATCHED THEN UPDATE SET
            ARTIFACTID      = @ArtifactId,
            INVOICENUMBER   = @InvoiceNumber,
            INVOICEDATE     = @InvoiceDate,
            INVOICEVALUE    = @InvoiceValue,
            RECEIVERGSTIN   = @ReceiverGSTIN,
            RECEIVERNAME    = @ReceiverName,
            POS             = @POS,
            SUPPLYTYPE      = @SupplyType,
            INVOICETYPE     = @InvoiceType,
            HSNCODE         = @HSNCode,
            UOM             = @UOM,
            QUANTITY        = @Quantity,
            TAXABLEVALUE    = @TaxableValue,
            IGSTRATE        = @IGSTRate,
            IGSTAMOUNT      = @IGSTAmount,
            CGSTRATE        = @CGSTRate,
            CGSTAMOUNT      = @CGSTAmount,
            SGSTRATE        = @SGSTRate,
            SGSTAMOUNT      = @SGSTAmount,
            CESSRATE        = @CessRate,
            CESSAMOUNT      = @CessAmount,
            MODIFIEDBYID    = @CreatedById,
            MODIFIEDON      = GETUTCDATE()
        WHEN NOT MATCHED THEN INSERT (
            ARTIFACTID, GSTIN, RETURNPERIOD, SOURCEDOCUMENTID,
            INVOICENUMBER, INVOICEDATE, INVOICEVALUE,
            RECEIVERGSTIN, RECEIVERNAME, POS, SUPPLYTYPE, INVOICETYPE,
            HSNCODE, UOM, QUANTITY,
            TAXABLEVALUE, IGSTRATE, IGSTAMOUNT, CGSTRATE, CGSTAMOUNT,
            SGSTRATE, SGSTAMOUNT, CESSRATE, CESSAMOUNT,
            FILINGSTATUS, ACKREFERENCENUMBER, ORIGINALOUTWARDID,
            CLIENTID, DATABASENAME, CREATEDBYID, CREATEDON
        ) VALUES (
            @ArtifactId, @GSTIN, @ReturnPeriod, @SourceDocumentId,
            @InvoiceNumber, @InvoiceDate, @InvoiceValue,
            @ReceiverGSTIN, @ReceiverName, @POS, @SupplyType, @InvoiceType,
            @HSNCode, @UOM, @Quantity,
            @TaxableValue, @IGSTRate, @IGSTAmount, @CGSTRate, @CGSTAmount,
            @SGSTRate, @SGSTAmount, @CessRate, @CessAmount,
            0, '', @OriginalOutwardId,
            @ClientId, @DatabaseName, @CreatedById, GETUTCDATE()
        )
        OUTPUT INSERTED.OUTWARDID;";

    public const string UPDATE_FILING_STATUS = @"
        UPDATE  TGST_OUTWARD
        SET     FILINGSTATUS       = @FilingStatus,
                ACKREFERENCENUMBER = @AckReferenceNumber,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = GETUTCDATE()
        WHERE   GSTIN        = @GSTIN
        AND     RETURNPERIOD = @ReturnPeriod
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName
        AND     FILINGSTATUS < @FilingStatus";

    public const string DELETE_OUTWARD_BY_PERIOD = @"
        DELETE FROM TGST_OUTWARD
        WHERE   GSTIN        = @GSTIN
        AND     RETURNPERIOD = @ReturnPeriod
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName
        AND     FILINGSTATUS = 0";

    // ── GSTR-1 section queries ────────────────────────────────────────────────

    // B2B: all B2B invoices for period — BLL groups by ReceiverGSTIN
    public const string GET_GSTR1_B2B_INVOICES = @"
        SELECT  o.RECEIVERGSTIN     ReceiverGSTIN,
                o.RECEIVERNAME      ReceiverName,
                o.INVOICENUMBER     InvoiceNumber,
                o.INVOICEDATE       InvoiceDate,
                o.INVOICEVALUE      InvoiceValue,
                o.POS               POS,
                o.TAXABLEVALUE      TaxableValue,
                o.IGSTRATE          IGSTRate,
                o.IGSTAMOUNT        IGSTAmount,
                o.CGSTRATE          CGSTRate,
                o.CGSTAMOUNT        CGSTAmount,
                o.SGSTRATE          SGSTRate,
                o.SGSTAMOUNT        SGSTAmount,
                o.CESSRATE          CessRate,
                o.CESSAMOUNT        CessAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  = 'B2B'
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        ORDER BY o.RECEIVERGSTIN, o.INVOICEDATE, o.INVOICENUMBER";

    // B2CL: large B2C invoices (>2.5L or interstate) — BLL groups by POS
    public const string GET_GSTR1_B2CL_INVOICES = @"
        SELECT  o.POS               POS,
                o.INVOICENUMBER     InvoiceNumber,
                o.INVOICEDATE       InvoiceDate,
                o.INVOICEVALUE      InvoiceValue,
                o.TAXABLEVALUE      TaxableValue,
                o.IGSTRATE          IGSTRate,
                o.IGSTAMOUNT        IGSTAmount,
                o.CGSTRATE          CGSTRate,
                o.CGSTAMOUNT        CGSTAmount,
                o.SGSTRATE          SGSTRate,
                o.SGSTAMOUNT        SGSTAmount,
                o.CESSRATE          CessRate,
                o.CESSAMOUNT        CessAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  = 'B2CL'
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        ORDER BY o.POS, o.INVOICEDATE, o.INVOICENUMBER";

    // B2CS: aggregated by POS + SupplyType + rates
    public const string GET_GSTR1_B2CS_AGGREGATE = @"
        SELECT  o.POS               POS,
                o.SUPPLYTYPE        SupplyType,
                o.IGSTRATE          IGSTRate,
                o.CGSTRATE          CGSTRate,
                o.SGSTRATE          SGSTRate,
                SUM(o.TAXABLEVALUE) TaxableValue,
                SUM(o.IGSTAMOUNT)   IGSTAmount,
                SUM(o.CGSTAMOUNT)   CGSTAmount,
                SUM(o.SGSTAMOUNT)   SGSTAmount,
                SUM(o.CESSAMOUNT)   CessAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  = 'B2CS'
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        GROUP BY o.POS, o.SUPPLYTYPE, o.IGSTRATE, o.CGSTRATE, o.SGSTRATE
        ORDER BY o.POS, o.SUPPLYTYPE";

    // EXP: export invoices — BLL groups by ExportType
    public const string GET_GSTR1_EXP_INVOICES = @"
        SELECT  o.SUPPLYTYPE        SupplyType,
                o.INVOICENUMBER     InvoiceNumber,
                o.INVOICEDATE       InvoiceDate,
                o.INVOICEVALUE      InvoiceValue,
                o.TAXABLEVALUE      TaxableValue,
                o.IGSTRATE          IGSTRate,
                o.IGSTAMOUNT        IGSTAmount,
                o.CESSRATE          CessRate,
                o.CESSAMOUNT        CessAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  = 'EXP'
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        ORDER BY o.SUPPLYTYPE, o.INVOICEDATE, o.INVOICENUMBER";

    // CDNR: credit/debit notes — BLL groups by ReceiverGSTIN
    public const string GET_GSTR1_CDNR_NOTES = @"
        SELECT  o.RECEIVERGSTIN     ReceiverGSTIN,
                o.INVOICENUMBER     NoteNumber,
                o.INVOICEDATE       NoteDate,
                o.INVOICEVALUE      NoteValue,
                o.INVOICETYPE       NoteType,
                o.TAXABLEVALUE      TaxableValue,
                o.IGSTAMOUNT        IGSTAmount,
                o.CGSTAMOUNT        CGSTAmount,
                o.SGSTAMOUNT        SGSTAmount,
                o.CESSAMOUNT        CessAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  IN ('CDNR', 'CDNUR')
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName
        ORDER BY o.RECEIVERGSTIN, o.INVOICEDATE";

    // NIL: nil-rated summary aggregated across types
    public const string GET_GSTR1_NIL_SUMMARY = @"
        SELECT  SUM(CASE WHEN o.SUPPLYTYPE = 'NIL'    THEN o.TAXABLEVALUE ELSE 0 END) NilSupplies,
                SUM(CASE WHEN o.SUPPLYTYPE = 'EXEMPT'  THEN o.TAXABLEVALUE ELSE 0 END) ExemptSupplies,
                SUM(CASE WHEN o.SUPPLYTYPE = 'NONGST'  THEN o.TAXABLEVALUE ELSE 0 END) NonGSTSupplies
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE  = 'NIL'
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName";

    // Period summary — totals across all invoice types
    public const string GET_OUTWARD_SUMMARY = @"
        SELECT  COUNT(*)                    TotalInvoices,
                SUM(o.TAXABLEVALUE)         TotalTaxableValue,
                SUM(o.IGSTAMOUNT)           TotalIGST,
                SUM(o.CGSTAMOUNT)           TotalCGST,
                SUM(o.SGSTAMOUNT)           TotalSGST,
                SUM(o.CESSAMOUNT)           TotalCess,
                SUM(o.INVOICEVALUE)         TotalInvoiceValue
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.INVOICETYPE NOT IN ('NIL', 'CDNR', 'CDNUR')
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName";
}