namespace GoodBooks.PAY.PAYDAL.Query.Loyalty
{
    /// <summary>
    /// SQL for MCUSTLOYALTYENROLLMENT.
    /// Required indexes:
    ///   MCUSTLOYALTYENROLLMENT: PK (ENROLLMENTID)
    ///   MCUSTLOYALTYENROLLMENT: (CUSTOMERID, LOYALTYPROGRAMID, TENANTID) — UK + covers idempotency check
    ///   MCUSTLOYALTYENROLLMENT: (TENANTID, STATUS) — covers tenant-level list queries
    /// </summary>
    public static class CustomerLoyaltyEnrollmentQB
    {
        /// <summary>
        /// Fetches a single enrollment by ID with program and tier names joined.
        /// TENANTID guard prevents cross-tenant reads.
        /// </summary>
        public const string GET_ENROLLMENT = @"
            SELECT  E.ENROLLMENTID       AS EnrollmentId,
                    E.CUSTOMERID         AS CustomerId,
                    E.LOYALTYPROGRAMID   AS LoyaltyProgramId,
                    E.LOYALTYTIERID      AS LoyaltyTierId,
                    E.ENROLLEDON         AS EnrolledOn,
                    E.MEMBERSHIPNO       AS MembershipNo,
                    E.CURRENTPOINTS      AS CurrentPoints,
                    E.LIFETIMEPOINTS     AS LifetimePoints,
                    E.LASTACTIVITYDATE   AS LastActivityDate,
                    E.REMARKS            AS Remarks,
                    E.VERSION            AS Version,
                    E.STATUS             AS Status,
                    E.SORTORDER          AS SortOrder,
                    E.CREATEDBYID        AS CreatedById,
                    E.CREATEDON          AS CreatedOn,
                    E.MODIFIEDBYID       AS ModifiedById,
                    E.MODIFIEDON         AS ModifiedOn,
                    E.SOURCETYPE         AS SourceType,
                    E.TENANTID           AS TenantId,
                    LP.PROGRAMCODE       AS ProgramCode,
                    LP.PROGRAMNAME       AS ProgramName,
                    LT.TIERNAME          AS TierName
            FROM    MCUSTLOYALTYENROLLMENT E
            LEFT JOIN MLOYALTYPROGRAM LP ON LP.LOYALTYPROGRAMID = E.LOYALTYPROGRAMID
            LEFT JOIN MLOYALTYTIER    LT ON LT.LOYALTYTIERID    = E.LOYALTYTIERID
            WHERE   E.ENROLLMENTID = @EnrollmentId
            AND     E.TENANTID     = @TenantId";

        /// <summary>
        /// Fetches a single enrollment for a customer+program combination.
        /// Used to retrieve the enrollment row before posting a ledger entry.
        /// Returns NULL when the customer is not enrolled.
        /// </summary>
        public const string GET_ENROLLMENT_BY_CUSTOMER_PROGRAM = @"
            SELECT  E.ENROLLMENTID       AS EnrollmentId,
                    E.CUSTOMERID         AS CustomerId,
                    E.LOYALTYPROGRAMID   AS LoyaltyProgramId,
                    E.LOYALTYTIERID      AS LoyaltyTierId,
                    E.ENROLLEDON         AS EnrolledOn,
                    E.MEMBERSHIPNO       AS MembershipNo,
                    E.CURRENTPOINTS      AS CurrentPoints,
                    E.LIFETIMEPOINTS     AS LifetimePoints,
                    E.LASTACTIVITYDATE   AS LastActivityDate,
                    E.REMARKS            AS Remarks,
                    E.VERSION            AS Version,
                    E.STATUS             AS Status,
                    E.SORTORDER          AS SortOrder,
                    E.CREATEDBYID        AS CreatedById,
                    E.CREATEDON          AS CreatedOn,
                    E.MODIFIEDBYID       AS ModifiedById,
                    E.MODIFIEDON         AS ModifiedOn,
                    E.SOURCETYPE         AS SourceType,
                    E.TENANTID           AS TenantId,
                    LP.PROGRAMCODE       AS ProgramCode,
                    LP.PROGRAMNAME       AS ProgramName,
                    LT.TIERNAME          AS TierName
            FROM    MCUSTLOYALTYENROLLMENT E
            LEFT JOIN MLOYALTYPROGRAM LP ON LP.LOYALTYPROGRAMID = E.LOYALTYPROGRAMID
            LEFT JOIN MLOYALTYTIER    LT ON LT.LOYALTYTIERID    = E.LOYALTYTIERID
            WHERE   E.CUSTOMERID       = @CustomerId
            AND     E.LOYALTYPROGRAMID = @LoyaltyProgramId
            AND     E.TENANTID         = @TenantId";

        /// <summary>
        /// Idempotency guard for EnrollCustomer: returns 1 if a record already exists, 0 otherwise.
        /// BLL aborts duplicate enrollment when count > 0.
        /// </summary>
        public const string COUNT_ENROLLMENT = @"
            SELECT COUNT(1)
            FROM   MCUSTLOYALTYENROLLMENT
            WHERE  CUSTOMERID       = @CustomerId
            AND    LOYALTYPROGRAMID = @LoyaltyProgramId
            AND    TENANTID         = @TenantId";

        /// <summary>
        /// Inserts a new customer enrollment.
        /// ENROLLMENTID is assumed app-generated (non-IDENTITY).
        /// CURRENTPOINTS and LIFETIMEPOINTS start at 0.
        /// </summary>
        public const string INSERT_ENROLLMENT = @"
            INSERT INTO MCUSTLOYALTYENROLLMENT
            (
                ENROLLMENTID,
                CUSTOMERID,
                LOYALTYPROGRAMID,
                LOYALTYTIERID,
                ENROLLEDON,
                MEMBERSHIPNO,
                CURRENTPOINTS,
                LIFETIMEPOINTS,
                LASTACTIVITYDATE,
                REMARKS,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE,
                TENANTID
            )
            VALUES
            (
                @EnrollmentId,
                @CustomerId,
                @LoyaltyProgramId,
                @LoyaltyTierId,
                @EnrolledOn,
                @MembershipNo,
                @CurrentPoints,
                @LifetimePoints,
                @LastActivityDate,
                @Remarks,
                @Version,
                @Status,
                @SortOrder,
                @CreatedById,
                GETUTCDATE(),
                @ModifiedById,
                GETUTCDATE(),
                @SourceType,
                @TenantId
            )";

        /// <summary>
        /// Updates the running point totals and last activity date on an enrollment.
        /// Called by the BLL after every ledger entry (earn, redeem, expire, adjust, reverse).
        /// TENANTID guard on all writes via ENROLLMENTID — not repeated here since ENROLLMENTID is PK.
        /// </summary>
        public const string UPDATE_ENROLLMENT_POINTS = @"
            UPDATE MCUSTLOYALTYENROLLMENT
            SET    CURRENTPOINTS    = @CurrentPoints,
                   LIFETIMEPOINTS   = @LifetimePoints,
                   LASTACTIVITYDATE = GETUTCDATE(),
                   MODIFIEDON       = GETUTCDATE()
            WHERE  ENROLLMENTID = @EnrollmentId";

        /// <summary>
        /// Updates the tier assignment after a tier-upgrade evaluation.
        /// Called by BLL only when GET_TIER_FOR_POINTS returns a different tier than the current one.
        /// </summary>
        public const string UPDATE_ENROLLMENT_TIER = @"
            UPDATE MCUSTLOYALTYENROLLMENT
            SET    LOYALTYTIERID = @LoyaltyTierId,
                   MODIFIEDON    = GETUTCDATE()
            WHERE  ENROLLMENTID  = @EnrollmentId";
    }
}