SELECT ca.id, ca.artifact_key, ca.content
FROM context_artifacts ca
WHERE ca.project_id = :project_id
  AND ca.artifact_type = 'spec_acceptance_criterion'
  AND ca.is_active = true
  AND ca.id NOT IN (
    SELECT cad.depends_on_id
    FROM context_artifact_dependencies cad
    WHERE cad.dependency_type = 'tests'
  )
ORDER BY ca.created_at;

SELECT ca.id, ca.artifact_key, ca.metadata->>'path' AS path, ca.metadata->>'method' AS method
FROM context_artifacts ca
WHERE ca.project_id = :project_id
  AND ca.artifact_type = 'spec_endpoint'
  AND ca.is_active = true
  AND (ca.metadata->>'auth_required')::boolean = true
  AND ca.id NOT IN (
    SELECT cad.artifact_id
    FROM context_artifact_dependencies cad
    JOIN context_artifacts guard_ca ON guard_ca.id = cad.depends_on_id
    WHERE cad.dependency_type = 'guards'
      AND guard_ca.artifact_type = 'spec_guard'
  )
ORDER BY ca.artifact_key;

WITH RECURSIVE journey AS (
  SELECT ca.id, ca.artifact_key, ca.content, cad.dependency_type, cad.metadata->>'condition' AS branch_condition, 1 AS step_order
  FROM context_artifacts ca
  LEFT JOIN context_artifact_dependencies cad ON cad.artifact_id = ca.id
  WHERE ca.project_id = :project_id
    AND ca.artifact_type = 'spec_flow_step'
    AND ca.id = :entry_step_id

  UNION ALL

  SELECT next_ca.id, next_ca.artifact_key, next_ca.content, next_edge.dependency_type, next_edge.metadata->>'condition', j.step_order + 1
  FROM context_artifact_dependencies next_edge
  JOIN context_artifacts next_ca ON next_ca.id = next_edge.artifact_id
  JOIN journey j ON next_edge.depends_on_id = j.id
  WHERE next_edge.dependency_type IN ('flow_next', 'flow_branch')
    AND j.step_order < 50  -- cycle guard
)
SELECT * FROM journey ORDER BY step_order;

WITH RECURSIVE topo AS (
  SELECT ca.id, ca.artifact_key, ca.artifact_type, 0 AS depth
  FROM context_artifacts ca
  WHERE ca.project_id = :project_id
    AND ca.artifact_type LIKE 'impl_%'
    AND ca.is_active = true
    AND ca.id NOT IN (
      SELECT DISTINCT cad.artifact_id
      FROM context_artifact_dependencies cad
      WHERE cad.dependency_type = 'requires'
    )

  UNION ALL

  SELECT ca.id, ca.artifact_key, ca.artifact_type, t.depth + 1
  FROM context_artifact_dependencies cad
  JOIN context_artifacts ca ON ca.id = cad.artifact_id
  JOIN topo t ON t.id = cad.depends_on_id
  WHERE cad.dependency_type = 'requires'
    AND t.depth < 20
)
SELECT DISTINCT id, artifact_key, artifact_type, MAX(depth) AS batch
FROM topo
GROUP BY id, artifact_key, artifact_type
ORDER BY batch, artifact_key;

SELECT ca.id, ca.artifact_key, ca.artifact_type, ca.metadata->>'file_path' AS file_path
FROM context_artifacts ca
WHERE ca.project_id = :project_id
  AND ca.artifact_type LIKE 'code_%'
  AND ca.is_active = true
  AND ca.id NOT IN (
    SELECT cad.artifact_id
    FROM context_artifact_dependencies cad
    WHERE cad.dependency_type IN ('implements', 'exposes', 'persists', 'satisfies')
  )
ORDER BY ca.artifact_type, ca.artifact_key;

SELECT
  crit.id AS criterion_id,
  crit.artifact_key AS criterion_key,
  crit.content AS criterion_text,
  tc.id AS test_case_id,
  tc.artifact_key AS test_key,
  tc.metadata->>'status' AS test_status
FROM context_artifacts crit
JOIN context_artifact_dependencies cad ON cad.depends_on_id = crit.id AND cad.dependency_type = 'tests'
JOIN context_artifacts tc ON tc.id = cad.artifact_id AND tc.artifact_type = 'test_case'
WHERE crit.project_id = :project_id
  AND crit.artifact_type = 'spec_acceptance_criterion'
  AND crit.is_active = true
ORDER BY crit.artifact_key;