-- 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
SELECT * FROM requirements
WHERE deleted_at IS NULL
ORDER BY created_at 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;