namespace GoodBooks.PAY.PAYDAL.Query.Loyalty
{
    /// <summary>
    /// SQL for MLOYALTYPROGRAM. Always filter by TENANTID.
    /// Required indexes:
    ///   MLOYALTYPROGRAM: PK (LOYALTYPROGRAMID)
    ///   MLOYALTYPROGRAM: (TENANTID, STATUS) — covers list queries
    ///   MLOYALTYTIER:    (LOYALTYPROGRAMID) — covers child fetches and bulk delete on update
    /// </summary>
    public static class LoyaltyProgramQB
    {
        /// <summary>
        /// Fetches a single loyalty program header by ID and tenant.
        /// Child arrays (tiers, earn configs) are fetched separately by the DAL.
        /// CreatedByName and ModifiedByName are joined from MUSERS.
        /// </summary>
        public const string GET_LOYALTY_PROGRAM = @"
            SELECT  LP.LOYALTYPROGRAMID    AS LoyaltyProgramId,
                    LP.PROGRAMCODE         AS ProgramCode,
                    LP.PROGRAMNAME         AS ProgramName,
                    LP.PROGRAMTYPE         AS ProgramType,
                    LP.POINTSNAME          AS PointsName,
                    LP.POINTSEXPIREDAYS    AS PointsExpireDays,
                    LP.MINREDEEMPOINTS     AS MinRedeemPoints,
                    LP.POINTSTOCURRENCY    AS PointsToCurrency,
                    LP.CURRENCYTOPOINTS    AS CurrencyToPoints,
                    LP.ALLOWTIERING        AS AllowTiering,
                    LP.REMARKS             AS Remarks,
                    LP.VERSION             AS Version,
                    LP.STATUS              AS Status,
                    LP.SORTORDER           AS SortOrder,
                    LP.CREATEDBYID         AS CreatedById,
                    UC.USERNAME            AS CreatedByName,
                    LP.CREATEDON           AS CreatedOn,
                    LP.MODIFIEDBYID        AS ModifiedById,
                    UM.USERNAME            AS ModifiedByName,
                    LP.MODIFIEDON          AS ModifiedOn,
                    LP.SOURCETYPE          AS SourceType,
                    LP.TENANTID            AS TenantId
            FROM    MLOYALTYPROGRAM LP
            LEFT JOIN MUSERS UC ON UC.USERID = LP.CREATEDBYID
            LEFT JOIN MUSERS UM ON UM.USERID = LP.MODIFIEDBYID
            WHERE   LP.LOYALTYPROGRAMID = @LoyaltyProgramId
            AND     LP.TENANTID         = @TenantId";

        /// <summary>
        /// Paged list of loyalty programs for a tenant.
        /// Filtered to active records (STATUS=1) by default; admin views may pass STATUS=NULL.
        /// OFFSET/FETCH paging — caller supplies @Offset and @PageSize.
        /// Wrap in QueryPagedAsync which builds the COUNT(*) automatically.
        /// </summary>
        public const string GET_LOYALTY_PROGRAM_LIST = @"
            SELECT  LP.LOYALTYPROGRAMID    AS LoyaltyProgramId,
                    LP.PROGRAMCODE         AS ProgramCode,
                    LP.PROGRAMNAME         AS ProgramName,
                    LP.PROGRAMTYPE         AS ProgramType,
                    LP.POINTSNAME          AS PointsName,
                    LP.POINTSEXPIREDAYS    AS PointsExpireDays,
                    LP.MINREDEEMPOINTS     AS MinRedeemPoints,
                    LP.POINTSTOCURRENCY    AS PointsToCurrency,
                    LP.CURRENCYTOPOINTS    AS CurrencyToPoints,
                    LP.ALLOWTIERING        AS AllowTiering,
                    LP.REMARKS             AS Remarks,
                    LP.VERSION             AS Version,
                    LP.STATUS              AS Status,
                    LP.SORTORDER           AS SortOrder,
                    LP.CREATEDBYID         AS CreatedById,
                    LP.CREATEDON           AS CreatedOn,
                    LP.MODIFIEDBYID        AS ModifiedById,
                    LP.MODIFIEDON          AS ModifiedOn,
                    LP.SOURCETYPE          AS SourceType,
                    LP.TENANTID            AS TenantId
            FROM    MLOYALTYPROGRAM LP
            WHERE   LP.TENANTID = @TenantId
            AND     LP.STATUS   = 1
            ORDER BY LP.SORTORDER, LP.PROGRAMNAME
            OFFSET  @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>
        /// Inserts a new loyalty program header.
        /// LOYALTYPROGRAMID is assumed to be app-generated (not IDENTITY).
        /// Child tiers and earn configs are inserted separately inside the same transaction.
        /// </summary>
        public const string INSERT_LOYALTY_PROGRAM = @"
            INSERT INTO MLOYALTYPROGRAM
            (
                LOYALTYPROGRAMID,
                PROGRAMCODE,
                PROGRAMNAME,
                PROGRAMTYPE,
                POINTSNAME,
                POINTSEXPIREDAYS,
                MINREDEEMPOINTS,
                POINTSTOCURRENCY,
                CURRENCYTOPOINTS,
                ALLOWTIERING,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            )
            VALUES
            (
                @LoyaltyProgramId,
                @ProgramCode,
                @ProgramName,
                @ProgramType,
                @PointsName,
                @PointsExpireDays,
                @MinRedeemPoints,
                @PointsToCurrency,
                @CurrencyToPoints,
                @AllowTiering,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                GETUTCDATE(),
                @ModifiedById,
                GETUTCDATE(),
                @SourceType,
                @TenantId
            )";

        /// <summary>
        /// Updates the loyalty program header.
        /// TENANTID guard prevents cross-tenant writes.
        /// After UPDATE the caller should DELETE all tiers (DELETE_LOYALTY_TIER_BY_PROGRAM)
        /// and re-insert them to handle tier reordering.
        /// </summary>
        public const string UPDATE_LOYALTY_PROGRAM = @"
            UPDATE MLOYALTYPROGRAM
            SET    PROGRAMCODE      = @ProgramCode,
                   PROGRAMNAME      = @ProgramName,
                   PROGRAMTYPE      = @ProgramType,
                   POINTSNAME       = @PointsName,
                   POINTSEXPIREDAYS = @PointsExpireDays,
                   MINREDEEMPOINTS  = @MinRedeemPoints,
                   POINTSTOCURRENCY = @PointsToCurrency,
                   CURRENCYTOPOINTS = @CurrencyToPoints,
                   ALLOWTIERING     = @AllowTiering,
                   REMARKS          = @Remarks,
                   VERSION          = @Version,
                   STATUS           = @Status,
                   SORTORDER        = @SortOrder,
                   MODIFIEDBYID     = @ModifiedById,
                   MODIFIEDON       = GETUTCDATE(),
                   SOURCETYPE       = @SourceType
            WHERE  LOYALTYPROGRAMID = @LoyaltyProgramId
            AND    TENANTID         = @TenantId";

        /// <summary>
        /// Deletes all tier rows for a program before re-inserting on update.
        /// Safe to call inside the same transaction as INSERT_LOYALTY_TIER.
        /// </summary>
        public const string DELETE_LOYALTY_TIER_BY_PROGRAM = @"
            DELETE FROM MLOYALTYTIER
            WHERE  LOYALTYPROGRAMID = @LoyaltyProgramId";
    }
}