namespace GoodBooks.PAY.PAYDAL.Query.Loyalty
{
    /// <summary>
    /// APPEND-ONLY LEDGER — This file must contain only INSERT and SELECT constants.
    /// No UPDATE or DELETE SQL is permitted. Compensate with new negative-points INSERT rows.
    /// Violation breaks the audit trail and loyalty balance integrity.
    ///
    /// Required indexes:
    ///   TLOYALTYLEDGER: (ENROLLMENTID, TENANTID, EXPIRYDATE) — covers available-points SUM
    ///   TLOYALTYLEDGER: (ENROLLMENTID, CREATEDON DESC)       — covers paged ledger history
    ///   TLOYALTYLEDGER: (TENANTID, ENTRYTYPE, EXPIRYDATE)    — covers expiry-job polling
    /// </summary>
    public static class LoyaltyLedgerQB
    {
        /// <summary>
        /// Inserts a single ledger entry.
        /// LOYALTYLEDGERID is an app-generated INT PK — the caller must supply it
        /// (e.g. from an AutoNumber service). Include it in the INSERT column list.
        /// CREATEDON is set server-side to GETUTCDATE() to eliminate clock-skew risk.
        /// </summary>
        public const string INSERT_LEDGER_ENTRY = @"
            INSERT INTO TLOYALTYLEDGER
            (
                LOYALTYLEDGERID,
                ENROLLMENTID,
                LOYALTYPROGRAMID,
                CUSTOMERID,
                ENTRYTYPE,
                POINTS,
                REFERENCEORDERID,
                EARNCFGID,
                EXPIRYDATE,
                REMARKS,
                CREATEDBYID,
                CREATEDON,
                TENANTID
            )
            VALUES
            (
                @LoyaltyLedgerId,
                @EnrollmentId,
                @LoyaltyProgramId,
                @CustomerId,
                @EntryType,
                @Points,
                @ReferenceOrderId,
                @EarnCfgId,
                @ExpiryDate,
                @Remarks,
                @CreatedById,
                GETUTCDATE(),
                @TenantId
            )";

        /// <summary>
        /// Returns the live balance of non-expired points for an enrollment.
        /// COALESCE ensures 0 is returned when no rows match (new enrollment).
        /// SUM includes both positive (earn) and negative (redeem/expire/reverse) rows.
        /// Only rows where EXPIRYDATE IS NULL or EXPIRYDATE > GETDATE() are included.
        /// </summary>
        public const string GET_AVAILABLE_POINTS = @"
            SELECT COALESCE(SUM(POINTS), 0) AS AvailablePoints
            FROM   TLOYALTYLEDGER
            WHERE  ENROLLMENTID = @EnrollmentId
            AND    TENANTID     = @TenantId
            AND    (EXPIRYDATE IS NULL OR EXPIRYDATE > GETDATE())";

        /// <summary>
        /// Paged ledger history for an enrollment, newest entries first.
        /// JOINs TPAYORDER to surface the PayOrderNo for display.
        /// OFFSET/FETCH paging — caller supplies @Offset and @PageSize.
        /// </summary>
        public const string GET_LEDGER_PAGED = @"
            SELECT  L.LOYALTYLEDGERID    AS LoyaltyLedgerId,
                    L.ENROLLMENTID       AS EnrollmentId,
                    L.LOYALTYPROGRAMID   AS LoyaltyProgramId,
                    L.CUSTOMERID         AS CustomerId,
                    L.ENTRYTYPE          AS EntryType,
                    L.POINTS             AS Points,
                    L.REFERENCEORDERID   AS ReferenceOrderId,
                    L.EARNCFGID          AS EarnCfgId,
                    L.EXPIRYDATE         AS ExpiryDate,
                    L.REMARKS            AS Remarks,
                    L.CREATEDBYID        AS CreatedById,
                    L.CREATEDON          AS CreatedOn,
                    L.TENANTID           AS TenantId,
                    PO.PAYORDERNO        AS PayOrderNo
            FROM    TLOYALTYLEDGER L
            LEFT JOIN TPAYORDER PO ON PO.PAYORDERID = L.REFERENCEORDERID
            WHERE   L.ENROLLMENTID = @EnrollmentId
            AND     L.TENANTID     = @TenantId
            ORDER BY L.CREATEDON DESC
            OFFSET  @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";

        /// <summary>
        /// COUNT companion for GET_LEDGER_PAGED — used by the DAL to return total records.
        /// </summary>
        public const string GET_LEDGER_COUNT = @"
            SELECT COUNT(1)
            FROM   TLOYALTYLEDGER
            WHERE  ENROLLMENTID = @EnrollmentId
            AND    TENANTID     = @TenantId";

        /// <summary>
        /// Identifies enrollments that have points expiring today (for the daily expiry job).
        /// Sums only Earn-type rows (ENTRYTYPE=1) scheduled to expire today so the job
        /// knows how many compensating negative rows to insert.
        /// Groups by ENROLLMENTID so one job iteration processes one enrollment at a time.
        /// </summary>
        public const string GET_EXPIRING_POINTS = @"
            SELECT  ENROLLMENTID                     AS EnrollmentId,
                    SUM(POINTS)                      AS PointsExpiring
            FROM    TLOYALTYLEDGER
            WHERE   CAST(EXPIRYDATE AS DATE) = CAST(GETDATE() AS DATE)
            AND     ENTRYTYPE                = 1
            AND     TENANTID                 = @TenantId
            GROUP BY ENROLLMENTID";
    }
}