-- name: CreateDependency :one
INSERT INTO issue_dependencies (id, project_id, blocked_id, blocker_id, created_by)
VALUES ($1, $2, $3, $4, $5)
RETURNING id, project_id, blocked_id, blocker_id, created_by, created_at;

-- name: DeleteDependency :execrows
DELETE FROM issue_dependencies
WHERE project_id = $1 AND blocked_id = $2 AND blocker_id = $3;

-- name: ListBlockers :many
-- Issues that block $1 (the dependencies of blocked issue $1).
SELECT i.id, i.project_id, i.requirement_id, i.number, i.title, i.description, i.status,
       i.assignee_id, i.created_by, i.closed_at, i.created_at, i.updated_at, i.workspace_id, i.parent_id, i.priority
FROM issue_dependencies d
JOIN issues i ON i.id = d.blocker_id
WHERE d.blocked_id = $1
ORDER BY i.number ASC;

-- name: ListBlocked :many
-- Issues blocked by $1 (issues that depend on blocker $1).
SELECT i.id, i.project_id, i.requirement_id, i.number, i.title, i.description, i.status,
       i.assignee_id, i.created_by, i.closed_at, i.created_at, i.updated_at, i.workspace_id, i.parent_id, i.priority
FROM issue_dependencies d
JOIN issues i ON i.id = d.blocked_id
WHERE d.blocker_id = $1
ORDER BY i.number ASC;

-- name: ListSubtasks :many
SELECT id, project_id, requirement_id, number, title, description, status,
       assignee_id, created_by, closed_at, created_at, updated_at, workspace_id, parent_id, priority
FROM issues
WHERE parent_id = $1
ORDER BY priority DESC, number ASC;

-- name: ListBlockerIDs :many
-- Direct blocker issue ids of $1 (one hop along blocked-by edges). Used by the
-- service-layer iterative reachability cycle check in AddDependency.
SELECT blocker_id FROM issue_dependencies WHERE blocked_id = $1;

-- name: ListReadyLeafSubtasks :many
-- Ready leaf subtasks for a project: open subtasks (parent_id NOT NULL) whose
-- every blocker is closed, with no active (starting/running) acp_session, and
-- no open child issue (leaf). Ordered by priority DESC, number ASC. LIMIT $2.
SELECT i.id, i.project_id, i.requirement_id, i.number, i.title, i.description, i.status,
       i.assignee_id, i.created_by, i.closed_at, i.created_at, i.updated_at, i.workspace_id, i.parent_id, i.priority
FROM issues i
WHERE i.project_id = $1
  AND i.status = 'open'
  AND i.parent_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM issue_dependencies d
    JOIN issues b ON b.id = d.blocker_id
    WHERE d.blocked_id = i.id AND b.status <> 'closed'
  )
  AND NOT EXISTS (
    SELECT 1 FROM acp_sessions s
    WHERE s.issue_id = i.id AND s.status IN ('starting', 'running')
  )
  AND NOT EXISTS (
    SELECT 1 FROM issues c
    WHERE c.parent_id = i.id AND c.status = 'open'
  )
ORDER BY i.priority DESC, i.number ASC
LIMIT $2;

-- name: ListSchedulableProjects :many
-- Distinct project ids that currently have at least one ready leaf subtask
-- (open subtask whose blockers are all closed and no active session). Cheap
-- pre-filter so the scheduler only iterates projects with pending work.
SELECT DISTINCT i.project_id
FROM issues i
WHERE i.status = 'open'
  AND i.parent_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM issue_dependencies d
    JOIN issues b ON b.id = d.blocker_id
    WHERE d.blocked_id = i.id AND b.status <> 'closed'
  )
  AND NOT EXISTS (
    SELECT 1 FROM acp_sessions s
    WHERE s.issue_id = i.id AND s.status IN ('starting', 'running')
  )
  AND NOT EXISTS (
    SELECT 1 FROM issues c
    WHERE c.parent_id = i.id AND c.status = 'open'
  );

-- name: CountActiveSessionsForProject :one
-- Active (starting/running) acp_sessions in a project. Used by the scheduler's
-- soft per-project concurrency pre-check.
SELECT COUNT(*) FROM acp_sessions
WHERE project_id = $1 AND status IN ('starting', 'running');