namespace GoodBooks.PAY.PAYDAL.Query.Loyalty
{
    /// <summary>
    /// SQL for MLOYALTYEARNCFG — earn-rule configuration.
    /// Required indexes:
    ///   MLOYALTYEARNCFG: (LOYALTYPROGRAMID, STATUS, VALIDFROM, VALIDTO) — covers active-rule fetch
    ///   MLOYALTYEARNCFG: (TENANTID, STATUS) — covers admin list queries
    /// </summary>
    public static class LoyaltyEarnCfgQB
    {
        /// <summary>
        /// Returns all currently active earn rules for a loyalty program.
        /// "Active" means STATUS=1 AND VALIDFROM &lt;= today AND (VALIDTO IS NULL OR VALIDTO >= today).
        /// Ordered by RULETYPE DESC so more-specific rules (higher type value) come first,
        /// allowing BLL to short-circuit on first matching rule.
        /// </summary>
        public const string GET_EARN_CFGS_BY_PROGRAM = @"
            SELECT  EC.LOYALTYEARNCFGID  AS LoyaltyEarnCfgId,
                    EC.LOYALTYPROGRAMID  AS LoyaltyProgramId,
                    EC.RULENAME          AS RuleName,
                    EC.RULETYPE          AS RuleType,
                    EC.REFERENCEID       AS ReferenceId,
                    EC.EARNPOINTS        AS EarnPoints,
                    EC.VALIDFROM         AS ValidFrom,
                    EC.VALIDTO           AS ValidTo,
                    EC.REMARKS           AS Remarks,
                    EC.VERSION           AS Version,
                    EC.STATUS            AS Status,
                    EC.SORTORDER         AS SortOrder,
                    EC.CREATEDBYID       AS CreatedById,
                    EC.CREATEDON         AS CreatedOn,
                    EC.MODIFIEDBYID      AS ModifiedById,
                    EC.MODIFIEDON        AS ModifiedOn,
                    EC.SOURCETYPE        AS SourceType,
                    EC.TENANTID          AS TenantId
            FROM    MLOYALTYEARNCFG EC
            WHERE   EC.LOYALTYPROGRAMID = @LoyaltyProgramId
            AND     EC.STATUS           = 1
            AND     EC.VALIDFROM        <= GETDATE()
            AND     (EC.VALIDTO IS NULL OR EC.VALIDTO >= GETDATE())
            ORDER BY EC.RULETYPE DESC";

        /// <summary>
        /// Inserts a new earn rule configuration.
        /// LOYALTYEARNCFGID is assumed app-generated (non-IDENTITY).
        /// </summary>
        public const string INSERT_LOYALTY_EARN_CFG = @"
            INSERT INTO MLOYALTYEARNCFG
            (
                LOYALTYEARNCFGID,
                LOYALTYPROGRAMID,
                RULENAME,
                RULETYPE,
                REFERENCEID,
                EARNPOINTS,
                VALIDFROM,
                VALIDTO,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            )
            VALUES
            (
                @LoyaltyEarnCfgId,
                @LoyaltyProgramId,
                @RuleName,
                @RuleType,
                @ReferenceId,
                @EarnPoints,
                @ValidFrom,
                @ValidTo,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                GETUTCDATE(),
                @ModifiedById,
                GETUTCDATE(),
                @SourceType,
                @TenantId
            )";

        /// <summary>
        /// Updates an existing earn rule configuration.
        /// TENANTID guard prevents cross-tenant writes.
        /// </summary>
        public const string UPDATE_LOYALTY_EARN_CFG = @"
            UPDATE MLOYALTYEARNCFG
            SET    RULENAME      = @RuleName,
                   RULETYPE      = @RuleType,
                   REFERENCEID   = @ReferenceId,
                   EARNPOINTS    = @EarnPoints,
                   VALIDFROM     = @ValidFrom,
                   VALIDTO       = @ValidTo,
                   REMARKS       = @Remarks,
                   VERSION       = @Version,
                   STATUS        = @Status,
                   SORTORDER     = @SortOrder,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETUTCDATE(),
                   SOURCETYPE    = @SourceType
            WHERE  LOYALTYEARNCFGID = @LoyaltyEarnCfgId
            AND    TENANTID         = @TenantId";
    }
}