-- name: UpsertSkill :one
-- COALESCE keeps an existing category or embedding when a caller upserts by name only (for example the
-- ingest and mission check paths that pass just a skill name), so a later name only upsert never nulls a
-- category that was set before and an embedding can still be backfilled once it is supplied.
INSERT INTO skills (name, category, embedding)
VALUES ($1,$2,$3)
ON CONFLICT (name) DO UPDATE SET
  category = COALESCE(EXCLUDED.category, skills.category),
  embedding = COALESCE(EXCLUDED.embedding, skills.embedding)
RETURNING *;

-- name: GetSkillByName :one
SELECT * FROM skills WHERE name = $1;

-- name: ListSkills :many
SELECT * FROM skills ORDER BY name LIMIT $1 OFFSET $2;

-- name: SearchSkillsSemantic :many
-- Cosine nearest-neighbor over the skills ontology (powers semantic skill adjacency).
SELECT id, name, (embedding <=> $1::vector)::float8 AS distance
FROM skills
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector, name, id  -- unique tiebreaker => deterministic [review SQLC-3]
LIMIT $2;