-- name: CreateRequirement :one
INSERT INTO requirements (
    organization_id, opportunity_id, title, customer_agency,
    required_level, required_poly, required_years,
    work_location, remote_allowed, key_personnel
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
RETURNING *;

-- name: GetRequirement :one
SELECT * FROM requirements WHERE id = $1 AND deleted_at IS NULL;

-- name: ListRequirements :many
-- id is a unique trailing sort key so the order is total: ties on created_at no longer leave the
-- LIMIT/OFFSET cut and the downstream Best Fit pick nondeterministic.
SELECT * FROM requirements
WHERE deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT $1 OFFSET $2;

-- name: ListOpenRequirements :many
-- Only requirements whose opportunity is open, plus standalone requirements (NULL opportunity_id),
-- mirroring the Command Center predicate (o.id IS NULL OR o.status='open'). The LEFT JOIN selects r.*
-- so the row type stays Requirement. Used by the Opportunity Fit Engine, which scores only OPEN ones.
SELECT r.* FROM requirements r
LEFT JOIN opportunities o
    ON o.organization_id = r.organization_id AND o.id = r.opportunity_id
WHERE r.deleted_at IS NULL
  AND (o.id IS NULL OR o.status = 'open')
ORDER BY r.created_at DESC, r.id DESC
LIMIT $1 OFFSET $2;

-- name: AddRequiredSkill :exec
INSERT INTO required_skills (organization_id, requirement_id, skill_id, mandatory)
VALUES ($1,$2,$3,$4)
ON CONFLICT (requirement_id, skill_id) DO UPDATE SET mandatory = EXCLUDED.mandatory;

-- name: ListRequiredSkills :many
SELECT s.id, s.name, rs.mandatory
FROM required_skills rs
JOIN skills s ON s.id = rs.skill_id
WHERE rs.requirement_id = $1
ORDER BY s.name;

-- name: AddRequiredCertification :exec
INSERT INTO required_certifications (organization_id, requirement_id, name)
VALUES ($1,$2,$3)
ON CONFLICT (requirement_id, name) DO NOTHING;

-- name: ListRequiredCertifications :many
SELECT name FROM required_certifications WHERE requirement_id = $1 ORDER BY name;