namespace GB5Shared.ActionProcessor
{
    /// <summary>
    /// SQL constants for TEVENTACTIONRUN.
    /// Required index: TEVENTACTIONRUN (TENANTID, RUNSTATUS, CREATEDON)
    /// </summary>
    public static class EventActionRunQB
    {
        // =====================================================
        // Insert new TEVENTACTIONRUN row (RUNSTATUS=0 Pending).
        // ACTIONRUNID is IDENTITY — DB generates it; OUTPUT returns it.
        // PLAYGROUNDRUNID is NULL for normal runs; set for playground-triggered runs.
        // =====================================================
        public const string INSERT_RUN = @"
INSERT INTO TEVENTACTIONRUN
(
    ACTIONID, JOBEXECUTIONID, EVENTTYPEID,
    RUNSTATUS, ATTEMPTS, PAYLOAD, CORRELATIONKEY,
    VERSION, STATUS, SORTORDER,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
    SOURCETYPE, TENANTID, PLAYGROUNDRUNID
)
OUTPUT INSERTED.ACTIONRUNID
VALUES
(
    @ActionId, @JobExecutionId, @EventTypeId,
    0, 0, @Payload, @CorrelationKey,
    0, 1, 9999,
    @CreatedById, GETDATE(), @CreatedById, GETDATE(),
    5, @TenantId, @PlaygroundRunId
)
";

        // =====================================================
        // Deduplication check: returns 1 if a run already exists for this
        // (ActionId, CorrelationKey) pair — prevents dual-publish from creating
        // two TEVENTACTIONRUN rows for the same logical event.
        // (Direct Dapr publish + outbox relay both carry the same CorrelationKey.)
        // =====================================================
        public const string EXISTS_BY_CORRELATION_KEY = @"
SELECT CASE WHEN EXISTS (
    SELECT 1
    FROM   TEVENTACTIONRUN
    WHERE  ACTIONID        = @ActionId
      AND  CORRELATIONKEY  = @CorrelationKey
      AND  TENANTID        = @TenantId
) THEN 1 ELSE 0 END
";

        // =====================================================
        // Update RUNSTATUS (0=Pending,1=InProgress,2=Completed,3=Failed)
        // =====================================================
        public const string UPDATE_RUNSTATUS = @"
UPDATE TEVENTACTIONRUN
SET
    RUNSTATUS    = @RunStatus,
    MODIFIEDON   = GETDATE(),
    MODIFIEDBYID = @ModifiedById
WHERE ACTIONRUNID = @ActionRunId
  AND TENANTID   = @TenantId
";

        // =====================================================
        // Update result after handler completes
        // =====================================================
        public const string UPDATE_RESULT = @"
UPDATE TEVENTACTIONRUN
SET
    RUNSTATUS    = @RunStatus,
    ATTEMPTS     = @Attempts,
    ERRORMESSAGE = @ErrorMessage,
    STARTEDON    = @StartedOn,
    COMPLETEDON  = @CompletedOn,
    RESULT       = @Result,
    MODIFIEDON   = GETDATE(),
    MODIFIEDBYID = @ModifiedById
WHERE ACTIONRUNID = @ActionRunId
  AND TENANTID   = @TenantId
";

        // =====================================================
        // Progress rollup for one scheduler execution — used to detect
        // when every fanned-out action for a JobExecutionId has reached
        // a terminal state (2=Completed or 3=Failed), so the caller can
        // finalize the parent TJOBEXECUTION row.
        // =====================================================
        public const string GET_JOB_EXECUTION_PROGRESS = @"
SELECT
    COUNT(*)                                                          AS TotalActions,
    ISNULL(SUM(CASE WHEN RUNSTATUS IN (2, 3) THEN 1 ELSE 0 END), 0)   AS CompletedActions,
    ISNULL(SUM(CASE WHEN RUNSTATUS = 3 THEN 1 ELSE 0 END), 0)         AS FailedActions,
    MAX(CASE WHEN RUNSTATUS = 3 THEN ERRORMESSAGE END)                AS LastError
FROM TEVENTACTIONRUN
WHERE JOBEXECUTIONID = @JobExecutionId
  AND TENANTID        = @TenantId
";
    }
}