-- name: CreateSlate :one
INSERT INTO slates (organization_id, requirement_id, name, status, created_by)
VALUES ($1,$2,$3,$4,$5)
RETURNING *;

-- name: GetSlate :one
SELECT * FROM slates WHERE id = $1;

-- name: ListSlatesForRequirement :many
SELECT * FROM slates WHERE requirement_id = $1 ORDER BY created_at DESC, id;

-- name: UpdateSlateStatus :one
UPDATE slates SET status = $2, updated_at = now() WHERE id = $1 RETURNING *;

-- name: AddSlateMember :one
INSERT INTO slate_members (
    organization_id, slate_id, candidate_id,
    total_score, band,
    clearance_score, skills_score, certs_score, customer_score, experience_score, location_score,
    explanation, trace, gap_summary, rank, model_version
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
RETURNING *;

-- name: ListSlateMembers :many
SELECT * FROM slate_members
WHERE slate_id = $1
ORDER BY rank NULLS LAST, total_score DESC, candidate_id;  -- PK tiebreaker => total order [review SQLC-5]

-- name: GetSlateMember :one
-- The frozen decision record for one member: the full per component trace, rationale, and gap summary
-- for the audit and candidate facing explanation.
SELECT * FROM slate_members WHERE slate_id = $1 AND candidate_id = $2;

-- name: SetSlateMemberDecision :one
-- Human-in-the-loop: a person approves/rejects each member, optionally with a note. Never auto-decided.
UPDATE slate_members
SET decision = $3, decided_by = $4, decision_note = $5, decided_at = now()
WHERE slate_id = $1 AND candidate_id = $2
RETURNING *;

-- name: ListSlateMembersWithNames :many
-- The slate roster joined to the candidate name for the slateMATCH detail and the branded export. Both
-- tables are RLS scoped to the caller's org, so the join cannot cross tenants.
SELECT sm.*, c.full_name
FROM slate_members sm
JOIN candidates c ON c.organization_id = sm.organization_id AND c.id = sm.candidate_id
WHERE sm.slate_id = $1
ORDER BY sm.rank NULLS LAST, sm.total_score DESC, sm.candidate_id;