namespace GoodBooks.PAY.PAYDAL.Query.Cashback
{
    // INDEX NOTES:
    // MCASHBACKRULE: (TENANTID, ISACTIVE, VALIDFROM, VALIDTO) — covers active rule resolution
    // MCASHBACKRULE: (TENANTID, PAYGATEWAYID) — covers gateway-specific rule lookup

    /// <summary>SQL constants for MCASHBACKRULE.</summary>
    public static class CashbackRuleQB
    {
        public const string GET_CASHBACK_RULE = @"
            SELECT  R.CASHBACKRULEID        AS CashbackRuleId,
                    R.CASHBACKRULENAME      AS CashbackRuleName,
                    R.CASHBACKTYPE          AS CashbackType,
                    R.CASHBACKVALUE         AS CashbackValue,
                    R.MINAMOUNT             AS MinAmount,
                    R.MAXCASHBACK           AS MaxCashback,
                    R.PAYOUTMODE            AS PayoutMode,
                    R.PAYOUTDELAYDAYS       AS PayoutDelayDays,
                    R.PAYGATEWAYID          AS PayGatewayId,
                    R.ISACTIVE              AS IsActive,
                    R.VALIDFROM             AS ValidFrom,
                    R.VALIDTO               AS ValidTo,
                    R.DESCRIPTION           AS Description,
                    R.VERSION               AS Version,
                    R.STATUS                AS Status,
                    R.SORTORDER             AS SortOrder,
                    R.CREATEDBYID           AS CreatedById,
                    R.CREATEDON             AS CreatedOn,
                    R.MODIFIEDBYID          AS ModifiedById,
                    R.MODIFIEDON            AS ModifiedOn,
                    R.SOURCETYPE            AS SourceType,
                    R.TENANTID              AS TenantId
            FROM    MCASHBACKRULE R
            WHERE   R.CASHBACKRULEID = @CashbackRuleId
            AND     R.TENANTID       = @TenantId";

        public const string GET_CASHBACK_RULE_LIST = @"
            SELECT  R.CASHBACKRULEID        AS CashbackRuleId,
                    R.CASHBACKRULENAME      AS CashbackRuleName,
                    R.CASHBACKTYPE          AS CashbackType,
                    R.CASHBACKVALUE         AS CashbackValue,
                    R.MINAMOUNT             AS MinAmount,
                    R.MAXCASHBACK           AS MaxCashback,
                    R.PAYOUTMODE            AS PayoutMode,
                    R.ISACTIVE              AS IsActive,
                    R.VALIDFROM             AS ValidFrom,
                    R.VALIDTO               AS ValidTo,
                    R.TENANTID              AS TenantId
            FROM    MCASHBACKRULE R
            WHERE   R.TENANTID  = @TenantId
            AND     R.STATUS     = 1
            ORDER BY R.SORTORDER, R.CASHBACKRULENAME";

        /// <summary>
        /// Finds the best active cashback rule for an order amount and gateway.
        /// Priority: gateway-specific rule over all-gateway rule (PAYGATEWAYID=0).
        /// Only rules within their validity window are considered.
        /// </summary>
        public const string GET_ACTIVE_RULE_FOR_ORDER = @"
            SELECT  TOP 1
                    R.CASHBACKRULEID        AS CashbackRuleId,
                    R.CASHBACKTYPE          AS CashbackType,
                    R.CASHBACKVALUE         AS CashbackValue,
                    R.MAXCASHBACK           AS MaxCashback,
                    R.PAYOUTMODE            AS PayoutMode,
                    R.PAYOUTDELAYDAYS       AS PayoutDelayDays
            FROM    MCASHBACKRULE R
            WHERE   R.TENANTID    = @TenantId
            AND     R.ISACTIVE    = 1
            AND     R.STATUS      = 1
            AND     R.MINAMOUNT  <= @OrderAmt
            AND     (R.PAYGATEWAYID = @PayGatewayId OR R.PAYGATEWAYID = 0)
            AND     (R.VALIDFROM IS NULL OR R.VALIDFROM <= GETDATE())
            AND     (R.VALIDTO   IS NULL OR R.VALIDTO   >= GETDATE())
            ORDER BY R.PAYGATEWAYID DESC, R.SORTORDER"; // gateway-specific rule wins over all-gateway (PAYGATEWAYID=0)

        public const string SAVE_CASHBACK_RULE = @"
            INSERT INTO MCASHBACKRULE
            (
                CASHBACKRULEID, CASHBACKRULENAME, CASHBACKTYPE, CASHBACKVALUE,
                MINAMOUNT, MAXCASHBACK, PAYOUTMODE, PAYOUTDELAYDAYS, PAYGATEWAYID,
                ISACTIVE, VALIDFROM, VALIDTO, DESCRIPTION,
                VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                SOURCETYPE, TENANTID
            )
            VALUES
            (
                @CashbackRuleId, @CashbackRuleName, @CashbackType, @CashbackValue,
                @MinAmount, @MaxCashback, @PayoutMode, @PayoutDelayDays, @PayGatewayId,
                @IsActive, @ValidFrom, @ValidTo, @Description,
                @Version, @Status, @SortOrder, @CreatedById, GETDATE(), @ModifiedById, GETDATE(),
                @SourceType, @TenantId
            )";

        public const string UPDATE_CASHBACK_RULE = @"
            UPDATE MCASHBACKRULE
            SET    CASHBACKRULENAME  = @CashbackRuleName,
                   CASHBACKTYPE      = @CashbackType,
                   CASHBACKVALUE     = @CashbackValue,
                   MINAMOUNT         = @MinAmount,
                   MAXCASHBACK       = @MaxCashback,
                   PAYOUTMODE        = @PayoutMode,
                   PAYOUTDELAYDAYS   = @PayoutDelayDays,
                   PAYGATEWAYID      = @PayGatewayId,
                   ISACTIVE          = @IsActive,
                   VALIDFROM         = @ValidFrom,
                   VALIDTO           = @ValidTo,
                   DESCRIPTION       = @Description,
                   STATUS            = @Status,
                   SORTORDER         = @SortOrder,
                   MODIFIEDBYID      = @ModifiedById,
                   MODIFIEDON        = GETDATE()
            WHERE  CASHBACKRULEID    = @CashbackRuleId
            AND    TENANTID          = @TenantId";
    }
}