namespace ComplianceDAL.Query.GSTReturn;

public static class GSTReturnQB
{
    // ── TCOMPLIANCE_PERIOD ────────────────────────────────────────────────────

    public const string GET_COMPLIANCE_PERIOD = @"
        SELECT  p.PERIODID          PeriodId,
                p.FRAMEWORK         Framework,
                p.GSTIN             GSTIN,
                p.RETURNTYPE        ReturnType,
                p.PERIODYEAR        PeriodYear,
                p.PERIODMONTH       PeriodMonth,
                p.STATUS            Status,
                p.SUBMITTEDON       SubmittedOn,
                p.ACKNUMBER         AckNumber,
                p.FILEDON           FiledOn,
                p.CONFIRMATIONNUMBER ConfirmationNumber,
                p.CLIENTID          ClientId,
                p.DATABASENAME      DatabaseName,
                p.MODIFIEDBYID      ModifiedById,
                p.MODIFIEDON        ModifiedOn
        FROM    TCOMPLIANCE_PERIOD p
        WHERE   p.FRAMEWORK    = @Framework
        AND     p.GSTIN        = @GSTIN
        AND     p.RETURNTYPE   = @ReturnType
        AND     p.PERIODYEAR   = @PeriodYear
        AND     p.PERIODMONTH  = @PeriodMonth
        AND     p.CLIENTID     = @ClientId
        AND     p.DATABASENAME = @DatabaseName";

    public const string GET_PERIOD_LIST = @"
        SELECT  p.PERIODID          PeriodId,
                p.FRAMEWORK         Framework,
                p.GSTIN             GSTIN,
                p.RETURNTYPE        ReturnType,
                p.PERIODYEAR        PeriodYear,
                p.PERIODMONTH       PeriodMonth,
                p.STATUS            Status,
                p.SUBMITTEDON       SubmittedOn,
                p.ACKNUMBER         AckNumber,
                p.FILEDON           FiledOn,
                p.CONFIRMATIONNUMBER ConfirmationNumber
        FROM    TCOMPLIANCE_PERIOD p
        WHERE   p.CLIENTID     = @ClientId
        AND     p.DATABASENAME = @DatabaseName
        AND     (@Framework   = '' OR p.FRAMEWORK   = @Framework)
        AND     (@GSTIN       = '' OR p.GSTIN       = @GSTIN)
        AND     (@ReturnType  = '' OR p.RETURNTYPE  = @ReturnType)
        AND     (@PeriodYear  = 0  OR p.PERIODYEAR  = @PeriodYear)
        ORDER BY p.PERIODYEAR DESC, p.PERIODMONTH DESC, p.GSTIN, p.RETURNTYPE";

    // Upsert — create or update TCOMPLIANCE_PERIOD row
    // Requires unique index: UX_PERIOD_GSTIN_RETURN_PERIOD
    public const string UPSERT_COMPLIANCE_PERIOD = @"
        MERGE TCOMPLIANCE_PERIOD AS target
        USING (SELECT @Framework AS FRAMEWORK, @GSTIN AS GSTIN, @ReturnType AS RETURNTYPE,
                      @PeriodYear AS PERIODYEAR, @PeriodMonth AS PERIODMONTH) AS src
        ON  target.FRAMEWORK   = src.FRAMEWORK
        AND target.GSTIN       = src.GSTIN
        AND target.RETURNTYPE  = src.RETURNTYPE
        AND target.PERIODYEAR  = src.PERIODYEAR
        AND target.PERIODMONTH = src.PERIODMONTH
        AND target.CLIENTID     = @ClientId
        AND target.DATABASENAME = @DatabaseName
        WHEN MATCHED THEN UPDATE SET
            STATUS              = @Status,
            SUBMITTEDON         = COALESCE(@SubmittedOn, target.SUBMITTEDON),
            ACKNUMBER           = CASE WHEN @AckNumber <> '' THEN @AckNumber ELSE target.ACKNUMBER END,
            FILEDON             = COALESCE(@FiledOn, target.FILEDON),
            CONFIRMATIONNUMBER  = CASE WHEN @ConfirmationNumber <> '' THEN @ConfirmationNumber ELSE target.CONFIRMATIONNUMBER END,
            MODIFIEDBYID        = @ModifiedById,
            MODIFIEDON          = GETUTCDATE()
        WHEN NOT MATCHED THEN INSERT (
            FRAMEWORK, GSTIN, RETURNTYPE, PERIODYEAR, PERIODMONTH,
            STATUS, SUBMITTEDON, ACKNUMBER, FILEDON, CONFIRMATIONNUMBER,
            CLIENTID, DATABASENAME, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @Framework, @GSTIN, @ReturnType, @PeriodYear, @PeriodMonth,
            @Status, @SubmittedOn, @AckNumber, @FiledOn, @ConfirmationNumber,
            @ClientId, @DatabaseName, @ModifiedById, GETUTCDATE()
        )
        OUTPUT INSERTED.PERIODID;";

    public const string UPDATE_PERIOD_STATUS = @"
        UPDATE  TCOMPLIANCE_PERIOD
        SET     STATUS              = @Status,
                SUBMITTEDON         = CASE WHEN @Status = 'Submitted' THEN GETUTCDATE() ELSE SUBMITTEDON END,
                ACKNUMBER           = CASE WHEN @AckNumber <> '' THEN @AckNumber ELSE ACKNUMBER END,
                FILEDON             = CASE WHEN @Status = 'Filed' THEN GETUTCDATE() ELSE FILEDON END,
                CONFIRMATIONNUMBER  = CASE WHEN @ConfirmationNumber <> '' THEN @ConfirmationNumber ELSE CONFIRMATIONNUMBER END,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = GETUTCDATE()
        WHERE   FRAMEWORK    = @Framework
        AND     GSTIN        = @GSTIN
        AND     RETURNTYPE   = @ReturnType
        AND     PERIODYEAR   = @PeriodYear
        AND     PERIODMONTH  = @PeriodMonth
        AND     CLIENTID     = @ClientId
        AND     DATABASENAME = @DatabaseName";

    // ── GSTR-3B section 3.1 — computed from TGST_OUTWARD ─────────────────────
    // Section 3.1(a): taxable outward excl. zero-rated/nil/exempt
    // Section 3.1(b): zero-rated (exports)
    // Section 3.1(c): nil-rated and exempt
    public const string GET_GSTR3B_OUTWARD_SECTION = @"
        SELECT
            -- 3.1(a) taxable outward (B2B + B2CL + B2CS)
            SUM(CASE WHEN o.INVOICETYPE IN ('B2B','B2CL','B2CS') THEN o.TAXABLEVALUE ELSE 0 END) TaxableValueA,
            SUM(CASE WHEN o.INVOICETYPE IN ('B2B','B2CL','B2CS') THEN o.IGSTAMOUNT   ELSE 0 END) IGSTAmountA,
            SUM(CASE WHEN o.INVOICETYPE IN ('B2B','B2CL','B2CS') THEN o.CGSTAMOUNT   ELSE 0 END) CGSTAmountA,
            SUM(CASE WHEN o.INVOICETYPE IN ('B2B','B2CL','B2CS') THEN o.SGSTAMOUNT   ELSE 0 END) SGSTAmountA,
            SUM(CASE WHEN o.INVOICETYPE IN ('B2B','B2CL','B2CS') THEN o.CESSAMOUNT   ELSE 0 END) CessAmountA,
            -- 3.1(b) zero-rated exports
            SUM(CASE WHEN o.INVOICETYPE = 'EXP' THEN o.TAXABLEVALUE ELSE 0 END) TaxableValueB,
            SUM(CASE WHEN o.INVOICETYPE = 'EXP' THEN o.IGSTAMOUNT   ELSE 0 END) IGSTAmountB,
            SUM(CASE WHEN o.INVOICETYPE = 'EXP' THEN o.CESSAMOUNT   ELSE 0 END) CessAmountB,
            -- 3.1(c) nil-rated and exempt
            SUM(CASE WHEN o.INVOICETYPE = 'NIL' THEN o.TAXABLEVALUE ELSE 0 END) TaxableValueC,
            -- credit notes reduce outward liability
            SUM(CASE WHEN o.INVOICETYPE IN ('CDNR','CDNUR') THEN o.IGSTAMOUNT ELSE 0 END) CDNRIGSTAmount,
            SUM(CASE WHEN o.INVOICETYPE IN ('CDNR','CDNUR') THEN o.CGSTAMOUNT ELSE 0 END) CDNRCGSTAmount,
            SUM(CASE WHEN o.INVOICETYPE IN ('CDNR','CDNUR') THEN o.SGSTAMOUNT ELSE 0 END) CDNRSGSTAmount
        FROM    TGST_OUTWARD o
        WHERE   o.GSTIN        = @GSTIN
        AND     o.RETURNPERIOD = @ReturnPeriod
        AND     o.CLIENTID     = @ClientId
        AND     o.DATABASENAME = @DatabaseName";
}