namespace FrameworkDAL.Query.DirectAction
{
    // INDEX required: IX_TDIRECTACTIONTOKEN_TENANT_CREATEDON on (TENANTID, CREATEDON DESC)
    public static class DirectActionTokenAdminQB
    {
        // =====================================================
        // List tokens for a tenant — status label computed in SQL.
        // TokenHash is masked: first 8 chars + '...' (never return full hash via API).
        // =====================================================
        public const string GET_TOKEN_LIST = @"
            SELECT
                LEFT(TOKENHASH, 8) + '...'          AS TokenHashMasked,
                ACTIONCODE                           AS ActionCode,
                CAST(CONTEXTID AS NVARCHAR(50))      AS ContextId,
                ASSIGNEEUSERID                       AS AssigneeUserId,
                TENANTID                             AS TenantId,
                CASE
                    WHEN STATUS = 1 THEN 'USED'
                    WHEN STATUS = 2 THEN 'REVOKED'
                    WHEN EXPIRESAT < GETUTCDATE() THEN 'EXPIRED'
                    ELSE 'UNUSED'
                END                                  AS Status,
                EXPIRESAT                            AS ExpiresAt,
                USEDAT                               AS UsedAt,
                REMARKS                              AS Remarks,
                CREATEDON                            AS CreatedOn
            FROM TDIRECTACTIONTOKEN
            WHERE TENANTID = @TenantId
              AND (@ActionCode IS NULL OR ACTIONCODE = @ActionCode)
              AND (
                    @Status IS NULL
                    OR (
                        @Status = 'USED'    AND STATUS = 1
                    ) OR (
                        @Status = 'REVOKED' AND STATUS = 2
                    ) OR (
                        @Status = 'EXPIRED' AND STATUS = 0 AND EXPIRESAT < GETUTCDATE()
                    ) OR (
                        @Status = 'UNUSED'  AND STATUS = 0 AND EXPIRESAT >= GETUTCDATE()
                    )
                  )
              AND (@FromDate IS NULL OR CREATEDON >= @FromDate)
              AND (@ToDate   IS NULL OR CREATEDON <  DATEADD(DAY, 1, @ToDate))
            ORDER BY CREATEDON DESC";
    }
}