namespace ComplianceDAL.Query.GSTInward;

public static class GSTInwardQB
{
    public const string GET_INWARD = @"
        SELECT  i.INWARDID          InwardId,
                i.SUPPLIERGSTIN     SupplierGSTIN,
                i.SUPPLIERNAME      SupplierName,
                i.OURGSTIN          OurGSTIN,
                i.RETURNPERIOD      ReturnPeriod,
                i.INVOICENUMBER     InvoiceNumber,
                i.INVOICEDATE       InvoiceDate,
                i.INVOICEVALUE      InvoiceValue,
                i.TAXABLEVALUE      TaxableValue,
                i.IGSTAMOUNT        IGSTAmount,
                i.CGSTAMOUNT        CGSTAmount,
                i.SGSTAMOUNT        SGSTAmount,
                i.CESSAMOUNT        CessAmount,
                i.ITCELIGIBLE       ITCEligible,
                i.ACCEPTANCESTATUS  AcceptanceStatus,
                i.SOURCE            Source,
                i.SOURCEDOCUMENTID  SourceDocumentId,
                i.MATCHEDDOCUMENTID MatchedDocumentId,
                i.MATCHEDENTITYTYPE MatchedEntityType,
                i.CLIENTID          ClientId,
                i.DATABASENAME      DatabaseName,
                i.CREATEDBYID       CreatedById,
                i.CREATEDON         CreatedOn,
                i.MODIFIEDBYID      ModifiedById,
                i.MODIFIEDON        ModifiedOn
        FROM    TGST_INWARD i
        WHERE   i.INWARDID     = @InwardId
        AND     i.CLIENTID     = @ClientId
        AND     i.DATABASENAME = @DatabaseName";

    // Filtered list — index: IX_INWARD_OURGSTIN_PERIOD
    public const string GET_INWARD_LIST = @"
        SELECT  i.INWARDID          InwardId,
                i.SUPPLIERGSTIN     SupplierGSTIN,
                i.SUPPLIERNAME      SupplierName,
                i.OURGSTIN          OurGSTIN,
                i.RETURNPERIOD      ReturnPeriod,
                i.INVOICENUMBER     InvoiceNumber,
                i.INVOICEDATE       InvoiceDate,
                i.INVOICEVALUE      InvoiceValue,
                i.TAXABLEVALUE      TaxableValue,
                i.IGSTAMOUNT        IGSTAmount,
                i.CGSTAMOUNT        CGSTAmount,
                i.SGSTAMOUNT        SGSTAmount,
                i.CESSAMOUNT        CessAmount,
                i.ITCELIGIBLE       ITCEligible,
                i.ACCEPTANCESTATUS  AcceptanceStatus,
                i.SOURCE            Source,
                i.SOURCEDOCUMENTID  SourceDocumentId,
                i.MATCHEDDOCUMENTID MatchedDocumentId
        FROM    TGST_INWARD i
        WHERE   i.CLIENTID         = @ClientId
        AND     i.DATABASENAME     = @DatabaseName
        AND     (@OurGSTIN         = '' OR i.OURGSTIN         = @OurGSTIN)
        AND     (@ReturnPeriod     = '' OR i.RETURNPERIOD     = @ReturnPeriod)
        AND     (@SupplierGSTIN    = '' OR i.SUPPLIERGSTIN    = @SupplierGSTIN)
        AND     (@Source           = '' OR i.SOURCE           = @Source)
        AND     (@AcceptanceStatus = '' OR i.ACCEPTANCESTATUS = @AcceptanceStatus)
        ORDER BY i.SUPPLIERGSTIN, i.INVOICEDATE, i.INVOICENUMBER
        OFFSET ((@Page - 1) * @PageSize) ROWS FETCH NEXT @PageSize ROWS ONLY";

    // Upsert — portal sync: update if already exists for same supplier+invoice+period+source
    // Requires unique index: UX_INWARD_SUPPLIER_INVOICE
    public const string UPSERT_INWARD = @"
        MERGE TGST_INWARD AS target
        USING (SELECT @SupplierGSTIN AS SUPPLIERGSTIN, @InvoiceNumber AS INVOICENUMBER,
                      @OurGSTIN AS OURGSTIN, @ReturnPeriod AS RETURNPERIOD, @Source AS SOURCE) AS src
        ON  target.SUPPLIERGSTIN = src.SUPPLIERGSTIN
        AND target.INVOICENUMBER = src.INVOICENUMBER
        AND target.OURGSTIN      = src.OURGSTIN
        AND target.RETURNPERIOD  = src.RETURNPERIOD
        AND target.SOURCE        = src.SOURCE
        AND target.CLIENTID      = @ClientId
        AND target.DATABASENAME  = @DatabaseName
        WHEN MATCHED THEN UPDATE SET
            SUPPLIERNAME     = @SupplierName,
            INVOICEDATE      = @InvoiceDate,
            INVOICEVALUE     = @InvoiceValue,
            TAXABLEVALUE     = @TaxableValue,
            IGSTAMOUNT       = @IGSTAmount,
            CGSTAMOUNT       = @CGSTAmount,
            SGSTAMOUNT       = @SGSTAmount,
            CESSAMOUNT       = @CessAmount,
            ITCELIGIBLE      = @ITCEligible,
            SOURCEDOCUMENTID = @SourceDocumentId,
            MODIFIEDBYID     = @CreatedById,
            MODIFIEDON       = GETUTCDATE()
        WHEN NOT MATCHED THEN INSERT (
            SUPPLIERGSTIN, SUPPLIERNAME, OURGSTIN, RETURNPERIOD,
            INVOICENUMBER, INVOICEDATE, INVOICEVALUE,
            TAXABLEVALUE, IGSTAMOUNT, CGSTAMOUNT, SGSTAMOUNT, CESSAMOUNT,
            ITCELIGIBLE, ACCEPTANCESTATUS, SOURCE, SOURCEDOCUMENTID,
            CLIENTID, DATABASENAME, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @SupplierGSTIN, @SupplierName, @OurGSTIN, @ReturnPeriod,
            @InvoiceNumber, @InvoiceDate, @InvoiceValue,
            @TaxableValue, @IGSTAmount, @CGSTAmount, @SGSTAmount, @CessAmount,
            @ITCEligible, 'Pending', @Source, @SourceDocumentId,
            @ClientId, @DatabaseName, @CreatedById, GETUTCDATE(), @CreatedById, GETUTCDATE()
        )
        OUTPUT INSERTED.INWARDID;";

    public const string UPDATE_MATCH = @"
        UPDATE  TGST_INWARD
        SET     MATCHEDDOCUMENTID   = @MatchedDocumentId,
                MATCHEDENTITYTYPE   = @MatchedEntityType,
                ACCEPTANCESTATUS    = @AcceptanceStatus,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = GETUTCDATE()
        WHERE   INWARDID       = @InwardId
        AND     CLIENTID       = @ClientId
        AND     DATABASENAME   = @DatabaseName";

    public const string DELETE_BY_PERIOD = @"
        DELETE FROM TGST_INWARD
        WHERE   OURGSTIN     = @OurGSTIN
        AND     RETURNPERIOD = @ReturnPeriod
        AND     SOURCE       = @Source
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName
        AND     ACCEPTANCESTATUS = 'Pending'";

    // Reconciliation data: TGST_INWARD records with ITC eligibility summary
    public const string GET_ITC_SUMMARY = @"
        SELECT  i.OURGSTIN          OurGSTIN,
                i.RETURNPERIOD      ReturnPeriod,
                i.SOURCE            Source,
                COUNT(*)            TotalInvoices,
                SUM(i.TAXABLEVALUE) TotalTaxableValue,
                SUM(i.IGSTAMOUNT)   TotalIGST,
                SUM(i.CGSTAMOUNT)   TotalCGST,
                SUM(i.SGSTAMOUNT)   TotalSGST,
                SUM(i.CESSAMOUNT)   TotalCess,
                SUM(CASE WHEN i.ITCELIGIBLE = 1 THEN i.IGSTAMOUNT ELSE 0 END) EligibleIGST,
                SUM(CASE WHEN i.ITCELIGIBLE = 1 THEN i.CGSTAMOUNT ELSE 0 END) EligibleCGST,
                SUM(CASE WHEN i.ITCELIGIBLE = 1 THEN i.SGSTAMOUNT ELSE 0 END) EligibleSGST,
                SUM(CASE WHEN i.MATCHEDDOCUMENTID IS NOT NULL THEN 1 ELSE 0 END) Matched,
                SUM(CASE WHEN i.MATCHEDDOCUMENTID IS NULL     THEN 1 ELSE 0 END) Unmatched
        FROM    TGST_INWARD i
        WHERE   i.OURGSTIN     = @OurGSTIN
        AND     i.RETURNPERIOD = @ReturnPeriod
        AND     i.CLIENTID     = @ClientId
        AND     i.DATABASENAME = @DatabaseName
        GROUP BY i.OURGSTIN, i.RETURNPERIOD, i.SOURCE";
}