namespace GoodBooks.PAY.PAYDAL.Query.Cashback
{
    // INDEX NOTES:
    // TCASHBACKLEDGER: (PAYOUTSTATUS, SCHEDULEDPAYOUTON, TENANTID) — covers payout job polling
    // TCASHBACKLEDGER: (PAYORDERID, TENANTID) — covers per-order cashback lookup
    // TCASHBACKLEDGER: (CUSTOMERID, TENANTID) — covers customer cashback history

    /// <summary>SQL constants for TCASHBACKLEDGER.</summary>
    public static class CashbackLedgerQB
    {
        public const string GET_CASHBACK_LEDGER = @"
            SELECT  L.CASHBACKLEDGERID      AS CashbackLedgerId,
                    L.PAYORDERID            AS PayOrderId,
                    L.CASHBACKRULEID        AS CashbackRuleId,
                    L.CUSTOMERID            AS CustomerId,
                    L.CASHBACKAMT           AS CashbackAmt,
                    L.PAYOUTMODE            AS PayoutMode,
                    L.PAYOUTSTATUS          AS PayoutStatus,
                    L.SCHEDULEDPAYOUTON     AS ScheduledPayoutOn,
                    L.PAYOUTON              AS PayoutOn,
                    L.RETRYCOUNT            AS RetryCount,
                    L.FAILUREREASON         AS FailureReason,
                    L.VOUCHERID             AS VoucherId,
                    PO.PAYORDERNO           AS PayOrderNo,
                    L.VERSION               AS Version,
                    L.STATUS                AS Status,
                    L.SORTORDER             AS SortOrder,
                    L.CREATEDBYID           AS CreatedById,
                    L.CREATEDON             AS CreatedOn,
                    L.MODIFIEDBYID          AS ModifiedById,
                    L.MODIFIEDON            AS ModifiedOn,
                    L.SOURCETYPE            AS SourceType,
                    L.TENANTID              AS TenantId
            FROM    TCASHBACKLEDGER L
            LEFT JOIN TPAYORDER PO ON PO.PAYORDERID = L.PAYORDERID
            WHERE   L.CASHBACKLEDGERID = @CashbackLedgerId
            AND     L.TENANTID         = @TenantId";

        public const string GET_CASHBACK_LEDGER_LIST = @"
            SELECT  L.CASHBACKLEDGERID      AS CashbackLedgerId,
                    L.PAYORDERID            AS PayOrderId,
                    L.CASHBACKAMT           AS CashbackAmt,
                    L.PAYOUTMODE            AS PayoutMode,
                    L.PAYOUTSTATUS          AS PayoutStatus,
                    L.SCHEDULEDPAYOUTON     AS ScheduledPayoutOn,
                    L.PAYOUTON              AS PayoutOn,
                    PO.PAYORDERNO           AS PayOrderNo,
                    L.CREATEDON             AS CreatedOn,
                    L.TENANTID              AS TenantId
            FROM    TCASHBACKLEDGER L
            LEFT JOIN TPAYORDER PO ON PO.PAYORDERID = L.PAYORDERID
            WHERE   L.CUSTOMERID = @CustomerId
            AND     L.TENANTID   = @TenantId
            ORDER BY L.CREATEDON DESC
            OFFSET  @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>
        /// Unpaginated — one order's cashback rows are naturally few. Required index:
        /// (PAYORDERID, TENANTID), already noted above.
        /// </summary>
        public const string GET_CASHBACK_LEDGER_LIST_BY_ORDER = @"
            SELECT  L.CASHBACKLEDGERID      AS CashbackLedgerId,
                    L.PAYORDERID            AS PayOrderId,
                    L.CASHBACKAMT           AS CashbackAmt,
                    L.PAYOUTMODE            AS PayoutMode,
                    L.PAYOUTSTATUS          AS PayoutStatus,
                    L.SCHEDULEDPAYOUTON     AS ScheduledPayoutOn,
                    L.PAYOUTON              AS PayoutOn,
                    PO.PAYORDERNO           AS PayOrderNo,
                    L.CREATEDON             AS CreatedOn,
                    L.TENANTID              AS TenantId
            FROM    TCASHBACKLEDGER L
            LEFT JOIN TPAYORDER PO ON PO.PAYORDERID = L.PAYORDERID
            WHERE   L.PAYORDERID = @PayOrderId
            AND     L.TENANTID   = @TenantId
            ORDER BY L.CREATEDON DESC";

        /// <summary>
        /// Fetches the next batch of pending cashback rows ready for payout.
        /// TOP 50 cap prevents overwhelming downstream systems in one cycle.
        /// Includes only rows where SCHEDULEDPAYOUTON has passed.
        /// Required index: (PAYOUTSTATUS, SCHEDULEDPAYOUTON, TENANTID).
        /// </summary>
        public const string GET_PENDING_PAYOUTS = @"
            SELECT  TOP 50
                    L.CASHBACKLEDGERID      AS CashbackLedgerId,
                    L.PAYORDERID            AS PayOrderId,
                    L.CASHBACKRULEID        AS CashbackRuleId,
                    L.CUSTOMERID            AS CustomerId,
                    L.CASHBACKAMT           AS CashbackAmt,
                    L.PAYOUTMODE            AS PayoutMode,
                    L.RETRYCOUNT            AS RetryCount,
                    L.TENANTID              AS TenantId
            FROM    TCASHBACKLEDGER L
            WHERE   L.PAYOUTSTATUS      = 1          -- 1=Pending
            AND     L.SCHEDULEDPAYOUTON <= GETUTCDATE()
            ORDER BY L.SCHEDULEDPAYOUTON ASC";

        public const string SAVE_CASHBACK_LEDGER = @"
            INSERT INTO TCASHBACKLEDGER
            (
                CASHBACKLEDGERID, PAYORDERID, CASHBACKRULEID, CUSTOMERID, CASHBACKAMT,
                PAYOUTMODE, PAYOUTSTATUS, SCHEDULEDPAYOUTON, PAYOUTON, RETRYCOUNT,
                FAILUREREASON, VOUCHERID,
                VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                SOURCETYPE, TENANTID
            )
            VALUES
            (
                @CashbackLedgerId, @PayOrderId, @CashbackRuleId, @CustomerId, @CashbackAmt,
                @PayoutMode, @PayoutStatus, @ScheduledPayoutOn, @PayoutOn, @RetryCount,
                @FailureReason, @VoucherId,
                @Version, @Status, @SortOrder, @CreatedById, GETDATE(), @ModifiedById, GETDATE(),
                @SourceType, @TenantId
            )";

        /// <summary>
        /// Updates payout result after the job processes a row.
        /// On success: PAYOUTSTATUS=3, PAYOUTON=now.
        /// On failure: PAYOUTSTATUS=4, increment RETRYCOUNT, set FAILUREREASON.
        /// Caller determines status before calling.
        /// </summary>
        public const string UPDATE_PAYOUT_STATUS = @"
            UPDATE TCASHBACKLEDGER
            SET    PAYOUTSTATUS   = @PayoutStatus,
                   PAYOUTON       = @PayoutOn,
                   RETRYCOUNT     = @RetryCount,
                   FAILUREREASON  = @FailureReason,
                   VOUCHERID      = @VoucherId,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE()
            WHERE  CASHBACKLEDGERID = @CashbackLedgerId
            AND    TENANTID         = @TenantId";
    }
}