-- name: CreateRequirement :one
INSERT INTO requirements (id, project_id, number, title, description, owner_id, workspace_id)
VALUES (
  $1, $2,
  (SELECT COALESCE(MAX(number), 0) + 1 FROM requirements WHERE project_id = $2),
  $3, $4, $5, $6
)
RETURNING id, project_id, number, title, description, phase, status, owner_id,
          closed_at, created_at, updated_at, workspace_id;

-- name: GetRequirementByNumber :one
SELECT id, project_id, number, title, description, phase, status, owner_id,
       closed_at, created_at, updated_at, workspace_id
FROM requirements
WHERE project_id = $1 AND number = $2;

-- name: GetRequirementByID :one
SELECT id, project_id, number, title, description, phase, status, owner_id,
       closed_at, created_at, updated_at, workspace_id
FROM requirements WHERE id = $1;

-- name: ListRequirements :many
SELECT id, project_id, number, title, description, phase, status, owner_id,
       closed_at, created_at, updated_at, workspace_id
FROM requirements
WHERE project_id = $1
  AND (sqlc.narg('phase')::text  IS NULL OR phase  = sqlc.narg('phase'))
  AND (sqlc.narg('status')::text IS NULL OR status = sqlc.narg('status'))
ORDER BY number DESC;

-- name: UpdateRequirement :one
UPDATE requirements
SET title        = $2,
    description  = $3,
    owner_id     = $4,
    workspace_id = $5,
    updated_at   = now()
WHERE id = $1
RETURNING id, project_id, number, title, description, phase, status, owner_id,
          closed_at, created_at, updated_at, workspace_id;

-- name: UpdateRequirementPhase :one
UPDATE requirements
SET phase = $2, updated_at = now()
WHERE id = $1
RETURNING id, project_id, number, title, description, phase, status, owner_id,
          closed_at, created_at, updated_at, workspace_id;

-- name: CloseRequirement :exec
UPDATE requirements
SET status = 'closed', closed_at = now(), updated_at = now()
WHERE id = $1 AND status = 'open';

-- name: ReopenRequirement :exec
UPDATE requirements
SET status = 'open', closed_at = NULL, updated_at = now()
WHERE id = $1 AND status = 'closed';