namespace AutomationDAL.Query.Run;

public static class RunQB
{
    // Enqueued as 'Queued' with no STARTEDON — an agent transitions it to 'Running' (and sets
    // STARTEDON) only when it actually claims the row via CLAIM_NEXT_JOB.
    public const string INSERT_EXECUTION = @"
        INSERT INTO AUTO.TRUNEXECUTION
               (SCRIPTID, VERSIONID, TARGETID, TENANTID, STATUS, TRIGGEREDBY, PARAMETERSJSON,
                RETRYCOUNT, CORRELATIONID, CREATEDBYID, CREATEDON)
        VALUES (@ScriptId, @VersionId, @TargetId, @TenantId, 'Queued', @TriggeredBy, @ParametersJson,
                0, NEWID(), @CreatedById, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    // Atomic claim-and-dequeue, mirroring JobEngine's TJOBQUEUE pattern (UPDATE...OUTPUT...WITH
    // (ROWLOCK, READPAST)) — reshaped as a CTE since this claim needs a join (matching the agent's
    // supported runtimes) and an ORDER BY (oldest-queued-first), neither of which a single
    // UPDATE...FROM statement can express together with TOP(1) in SQL Server.
    // Covering index required: IX_TRUNEXECUTION_STATUS_QUEUE (STATUS, TENANTID, CREATEDON)
    public const string CLAIM_NEXT_JOB = @"
        ;WITH candidate AS (
            SELECT TOP (1) r.RUNID
            FROM   AUTO.TRUNEXECUTION r WITH (ROWLOCK, READPAST)
            JOIN   AUTO.TSCRIPT s ON s.SCRIPTID = r.SCRIPTID
            WHERE  r.STATUS   = 'Queued'
            AND    r.TENANTID = @TenantId
            AND    ',' + @SupportedRuntimes + ',' LIKE '%,' + s.RUNTIMETYPE + ',%'
            AND    (r.TARGETID IS NULL OR (
                        SELECT COUNT(*) FROM AUTO.TRUNEXECUTION r2
                        WHERE  r2.TARGETID = r.TARGETID AND r2.STATUS = 'Running'
                    ) < (SELECT ISNULL(CONCURRENCYLIMIT, 1) FROM AUTO.TSCRIPTTARGET WHERE TARGETID = r.TARGETID))
            ORDER BY r.CREATEDON ASC
        )
        UPDATE AUTO.TRUNEXECUTION
        SET    STATUS = 'Running', AGENTID = @AgentId, STARTEDON = GETUTCDATE()
        OUTPUT INSERTED.RUNID
        FROM   AUTO.TRUNEXECUTION t
        JOIN   candidate c ON c.RUNID = t.RUNID;";

    // Composite payload for the run CLAIM_NEXT_JOB just claimed — enough for the agent to start
    // the container without a second round-trip.
    public const string GET_JOB_DETAIL = @"
        SELECT r.RUNID, r.CORRELATIONID, r.SCRIPTID, r.VERSIONID, r.TENANTID, r.TRIGGEREDBY,
               s.RUNTIMETYPE, v.IMAGETAG, r.TARGETID, t.TARGETURL, t.CREDENTIALVAULTREF, r.PARAMETERSJSON
        FROM   AUTO.TRUNEXECUTION r
        JOIN   AUTO.TSCRIPT s ON s.SCRIPTID = r.SCRIPTID
        JOIN   AUTO.TSCRIPTVERSION v ON v.VERSIONID = r.VERSIONID
        LEFT JOIN AUTO.TSCRIPTTARGET t ON t.TARGETID = r.TARGETID
        WHERE  r.RUNID = @RunId";

    public const string INSERT_ARTIFACT = @"
        INSERT INTO AUTO.TRUNARTIFACT (RUNID, ARTIFACTTYPE, BLOBURI, CREATEDON)
        VALUES (@RunId, @ArtifactType, @BlobUri, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS BIGINT);";

    public const string GET_ARTIFACTS_BY_RUN = @"
        SELECT ARTIFACTID, RUNID, ARTIFACTTYPE, BLOBURI, CREATEDON
        FROM   AUTO.TRUNARTIFACT
        WHERE  RUNID = @RunId
        ORDER BY ARTIFACTID";

    public const string COMPLETE_EXECUTION = @"
        UPDATE AUTO.TRUNEXECUTION
        SET    STATUS       = @Status,
               COMPLETEDON  = GETUTCDATE(),
               SUMMARY      = @Summary,
               ERRORMESSAGE = @ErrorMessage
        WHERE  RUNID    = @RunId
        AND    TENANTID = @TenantId
        AND    STATUS   = 'Running'";

    // Covering index required: IX_TRUNEXECUTION_SCRIPTID (SCRIPTID, TENANTID, STATUS)
    public const string GET_BY_ID = @"
        SELECT RUNID, SCRIPTID, VERSIONID, TARGETID, AGENTID, TENANTID, STATUS, TRIGGEREDBY, PARAMETERSJSON,
               STARTEDON, COMPLETEDON, SUMMARY, ERRORMESSAGE, RETRYCOUNT, CORRELATIONID,
               CREATEDBYID, CREATEDON
        FROM   AUTO.TRUNEXECUTION
        WHERE  RUNID    = @RunId
        AND    TENANTID = @TenantId";

    // Used by scheduled-dispatch dependency chaining (TSCHEDULE.DEPENDSONSCRIPTID) — the
    // dependency is satisfied only if its most recent run succeeded.
    public const string GET_LATEST_STATUS_FOR_SCRIPT = @"
        SELECT TOP (1) STATUS
        FROM   AUTO.TRUNEXECUTION
        WHERE  SCRIPTID = @ScriptId
        AND    TENANTID = @TenantId
        ORDER BY RUNID DESC";

    public const string GET_HISTORY = @"
        SELECT r.RUNID, r.SCRIPTID, s.SCRIPTNAME, r.VERSIONID, r.STATUS, r.TRIGGEREDBY,
               r.STARTEDON, r.COMPLETEDON
        FROM   AUTO.TRUNEXECUTION r
        JOIN   AUTO.TSCRIPT s ON s.SCRIPTID = r.SCRIPTID
        WHERE  r.TENANTID = @TenantId
        AND    (@ScriptId IS NULL OR r.SCRIPTID = @ScriptId)
        AND    (@Status   IS NULL OR r.STATUS   = @Status)
        ORDER BY r.RUNID DESC";

    public const string INSERT_LOG = @"
        INSERT INTO AUTO.TRUNLOG (RUNID, LOGLEVEL, MESSAGE, LOGGEDON)
        VALUES (@RunId, @LogLevel, @Message, GETUTCDATE());";

    public const string GET_LOGS_BY_RUN = @"
        SELECT LOGID, RUNID, LOGLEVEL, MESSAGE, LOGGEDON
        FROM   AUTO.TRUNLOG
        WHERE  RUNID = @RunId
        ORDER BY LOGID";
}