-- name: CreateOpportunity :one
INSERT INTO opportunities (organization_id, name, solicitation_number, customer_agency, due_date, status)
VALUES ($1,$2,$3,$4,$5,$6)
RETURNING *;

-- name: GetOpportunity :one
SELECT * FROM opportunities WHERE id = $1;

-- name: ListOpportunities :many
SELECT * FROM opportunities ORDER BY created_at DESC, id DESC LIMIT $1 OFFSET $2;

-- name: ListRequirementsForOpportunity :many
SELECT * FROM requirements WHERE opportunity_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC, id DESC;

-- name: ProposalReadiness :many
-- Band/decision rollup across every slate member under one opportunity's requirements, the source
-- for GET /proposal-readiness [review COMPL-2]. RLS scopes all three tables to the caller's org.
SELECT sm.band, sm.decision, COUNT(*) AS n
FROM requirements r
JOIN slates s        ON s.requirement_id = r.id
JOIN slate_members sm ON sm.slate_id = s.id
WHERE r.opportunity_id = $1
GROUP BY sm.band, sm.decision
ORDER BY sm.band, sm.decision;