-- name: UpsertRequirementPhasePointer :one
INSERT INTO requirement_phase_pointers (requirement_id, phase, approved_artifact_id, approved_by, approved_at, updated_at)
VALUES ($1, $2, $3, $4, now(), now())
ON CONFLICT (requirement_id, phase) DO UPDATE
  SET approved_artifact_id = EXCLUDED.approved_artifact_id,
      approved_by = EXCLUDED.approved_by,
      approved_at = EXCLUDED.approved_at,
      updated_at = now()
RETURNING requirement_id, phase, approved_artifact_id, approved_by, approved_at, updated_at;

-- name: GetRequirementPhasePointer :one
SELECT requirement_id, phase, approved_artifact_id, approved_by, approved_at, updated_at
FROM requirement_phase_pointers
WHERE requirement_id = $1 AND phase = $2;

-- name: ListRequirementPhasePointers :many
SELECT requirement_id, phase, approved_artifact_id, approved_by, approved_at, updated_at
FROM requirement_phase_pointers
WHERE requirement_id = $1
ORDER BY phase ASC;
