-- name: InsertStep :one
INSERT INTO orchestrator_steps (
  id, run_id, phase, attempt, parent_step_id, depth,
  status, agent_kind_id, acp_session_id, prompt, stop_reason,
  gate_result, last_error, job_id
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
RETURNING id, run_id, phase, attempt, parent_step_id, depth,
          status, agent_kind_id, acp_session_id, prompt, stop_reason,
          gate_result, last_error, job_id, created_at, updated_at, ended_at;

-- name: GetStep :one
SELECT id, run_id, phase, attempt, parent_step_id, depth,
       status, agent_kind_id, acp_session_id, prompt, stop_reason,
       gate_result, last_error, job_id, created_at, updated_at, ended_at
FROM orchestrator_steps
WHERE id = $1;

-- name: GetStepBySession :one
SELECT id, run_id, phase, attempt, parent_step_id, depth,
       status, agent_kind_id, acp_session_id, prompt, stop_reason,
       gate_result, last_error, job_id, created_at, updated_at, ended_at
FROM orchestrator_steps
WHERE acp_session_id = $1
ORDER BY created_at DESC
LIMIT 1;

-- name: ListStepsByRun :many
SELECT id, run_id, phase, attempt, parent_step_id, depth,
       status, agent_kind_id, acp_session_id, prompt, stop_reason,
       gate_result, last_error, job_id, created_at, updated_at, ended_at
FROM orchestrator_steps
WHERE run_id = $1
ORDER BY created_at ASC;

-- name: UpdateStepStatus :one
UPDATE orchestrator_steps
SET status = $2,
    acp_session_id = $3,
    stop_reason = $4,
    gate_result = $5,
    last_error = $6,
    job_id = $7,
    ended_at = CASE WHEN $2 IN ('succeeded','failed','dead') THEN now() ELSE ended_at END,
    updated_at = now()
WHERE id = $1
RETURNING id, run_id, phase, attempt, parent_step_id, depth,
          status, agent_kind_id, acp_session_id, prompt, stop_reason,
          gate_result, last_error, job_id, created_at, updated_at, ended_at;

-- name: IncrementStepAttempt :one
UPDATE orchestrator_steps
SET attempt = attempt + 1,
    updated_at = now()
WHERE id = $1
RETURNING id, run_id, phase, attempt, parent_step_id, depth,
          status, agent_kind_id, acp_session_id, prompt, stop_reason,
          gate_result, last_error, job_id, created_at, updated_at, ended_at;

-- name: CountActiveChildSteps :one
SELECT COUNT(*) FROM orchestrator_steps
WHERE parent_step_id = $1
  AND status IN ('pending','spawning','running','awaiting_gate');

-- name: ResetStuckStepsOnRestart :many
-- 把活跃 run 下卡在 spawning/running/awaiting_gate 的 step 重置为 pending，便于
-- 重新入队恢复（镜像 acp ResetStuckSessionsOnRestart）。succeeded/canceled/failed
-- run 下的 step 不动。
UPDATE orchestrator_steps s
SET status = 'pending',
    updated_at = now()
FROM orchestrator_runs r
WHERE s.run_id = r.id
  AND r.status IN ('pending','running','paused')
  AND s.status IN ('spawning','running','awaiting_gate')
RETURNING s.id, s.run_id, s.phase, s.attempt, s.parent_step_id, s.depth,
          s.status, s.agent_kind_id, s.acp_session_id, s.prompt, s.stop_reason,
          s.gate_result, s.last_error, s.job_id, s.created_at, s.updated_at, s.ended_at;