namespace GB5Shared.ActionProcessor
{
    /// <summary>
    /// SQL constants for TACTIONOUTBOX.
    /// Required index: TACTIONOUTBOX (SENDSTATUS, CREATEDON, TENANTID)
    /// SENDSTATUS: 0=Pending, 1=Sent, 2=Failed
    /// </summary>
    public static class ActionOutboxQB
    {
        // =====================================================
        // Insert new outbox row (SENDSTATUS=0 Pending).
        // OUTBOXID is IDENTITY — DB generates it.
        // =====================================================
        public const string INSERT_OUTBOX = @"
INSERT INTO TACTIONOUTBOX
(
    ACTIONRUNID, DESTINATIONTOPIC, PAYLOAD,
    SENDSTATUS, ATTEMPTS,
    VERSION, STATUS, SORTORDER,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
    SOURCETYPE, TENANTID, CORRELATIONKEY
)
VALUES
(
    @ActionRunId, @DestinationTopic, @Payload,
    0, 0,
    0, 1, 9999,
    @CreatedById, GETDATE(), @CreatedById, GETDATE(),
    5, @TenantId, @CorrelationKey
)
";

        // =====================================================
        // Load batch of pending rows for dispatcher
        // =====================================================
        public const string GET_PENDING_BATCH = @"
SELECT TOP (@BatchSize)
    OUTBOXID          AS OutboxId,
    ACTIONRUNID       AS ActionRunId,
    DESTINATIONTOPIC  AS DestinationTopic,
    PAYLOAD           AS Payload,
    SENDSTATUS        AS SendStatus,
    ATTEMPTS          AS Attempts,
    TENANTID          AS TenantId,
    CREATEDON         AS CreatedOn,
    CORRELATIONKEY    AS CorrelationKey
FROM TACTIONOUTBOX
WHERE SENDSTATUS = 0
  AND TENANTID   = @TenantId
ORDER BY CREATEDON ASC
";

        // =====================================================
        // Mark sent (SENDSTATUS=1)
        // =====================================================
        public const string UPDATE_SENT = @"
UPDATE TACTIONOUTBOX
SET
    SENDSTATUS   = 1,
    MODIFIEDON   = GETDATE(),
    MODIFIEDBYID = @ModifiedById
WHERE OUTBOXID = @OutboxId
  AND TENANTID = @TenantId
";

        // =====================================================
        // Increment attempts and mark failed (SENDSTATUS=2)
        // =====================================================
        public const string UPDATE_ATTEMPTS_FAILED = @"
UPDATE TACTIONOUTBOX
SET
    ATTEMPTS     = ATTEMPTS + 1,
    SENDSTATUS   = 2,
    MODIFIEDON   = GETDATE(),
    MODIFIEDBYID = @ModifiedById
WHERE OUTBOXID = @OutboxId
  AND TENANTID = @TenantId
";

        // =====================================================
        // Reset back to Pending after transient failure (for retry)
        // =====================================================
        public const string UPDATE_ATTEMPTS_RETRY = @"
UPDATE TACTIONOUTBOX
SET
    ATTEMPTS     = ATTEMPTS + 1,
    MODIFIEDON   = GETDATE(),
    MODIFIEDBYID = @ModifiedById
WHERE OUTBOXID = @OutboxId
  AND TENANTID = @TenantId
";
    }
}