namespace GoodBooks.PAY.PAYDAL.Query.Loyalty
{
    /// <summary>
    /// SQL for MLOYALTYTIER — child table of MLOYALTYPROGRAM.
    /// Required indexes:
    ///   MLOYALTYTIER: (LOYALTYPROGRAMID, SLNO) — covers ordered child fetch and tier-upgrade lookup
    /// </summary>
    public static class LoyaltyTierQB
    {
        /// <summary>
        /// Returns all tiers for a program in display order (SLNO ASC).
        /// Used when loading a program for editing or when building the enrollment response.
        /// No TENANTID filter on this table — access is always via LOYALTYPROGRAMID which is
        /// already scoped to a tenant at the program level.
        /// </summary>
        public const string GET_TIERS_BY_PROGRAM = @"
            SELECT  T.LOYALTYTIERID     AS LoyaltyTierId,
                    T.LOYALTYPROGRAMID  AS LoyaltyProgramId,
                    T.SLNO              AS SlNo,
                    T.TIERCODE          AS TierCode,
                    T.TIERNAME          AS TierName,
                    T.MINPOINTS         AS MinPoints,
                    T.MAXPOINTS         AS MaxPoints,
                    T.EARNMULTIPLIER    AS EarnMultiplier,
                    T.REMARKS           AS Remarks
            FROM    MLOYALTYTIER T
            WHERE   T.LOYALTYPROGRAMID = @LoyaltyProgramId
            ORDER BY T.SLNO ASC";

        /// <summary>
        /// Inserts a single tier row.
        /// Called in a loop (BLL responsibility) after DELETE_LOYALTY_TIER_BY_PROGRAM on update,
        /// or after INSERT_LOYALTY_PROGRAM on create. Must run in the same transaction.
        /// </summary>
        public const string INSERT_LOYALTY_TIER = @"
            INSERT INTO MLOYALTYTIER
            (
                LOYALTYTIERID,
                LOYALTYPROGRAMID,
                SLNO,
                TIERCODE,
                TIERNAME,
                MINPOINTS,
                MAXPOINTS,
                EARNMULTIPLIER,
                REMARKS
            )
            VALUES
            (
                @LoyaltyTierId,
                @LoyaltyProgramId,
                @SlNo,
                @TierCode,
                @TierName,
                @MinPoints,
                @MaxPoints,
                @EarnMultiplier,
                @Remarks
            )";

        /// <summary>
        /// Returns the highest tier that a customer qualifies for based on their lifetime points.
        /// Used in tier-upgrade logic after a new earn entry is posted.
        /// Returns TOP 1 row ordered by MINPOINTS DESC so the highest matching tier wins.
        /// </summary>
        public const string GET_TIER_FOR_POINTS = @"
            SELECT  TOP 1
                    T.LOYALTYTIERID     AS LoyaltyTierId,
                    T.LOYALTYPROGRAMID  AS LoyaltyProgramId,
                    T.SLNO              AS SlNo,
                    T.TIERCODE          AS TierCode,
                    T.TIERNAME          AS TierName,
                    T.MINPOINTS         AS MinPoints,
                    T.MAXPOINTS         AS MaxPoints,
                    T.EARNMULTIPLIER    AS EarnMultiplier,
                    T.REMARKS           AS Remarks
            FROM    MLOYALTYTIER T
            WHERE   T.LOYALTYPROGRAMID = @LoyaltyProgramId
            AND     T.MINPOINTS        <= @LifetimePoints
            ORDER BY T.MINPOINTS DESC";
    }
}