-- External candidate sourcing. The import command (cmd/import-candidates) writes as the migrator role
-- with an explicit organization_id; the delete path runs as the app role inside a tenant tx (RLS).

-- name: UpsertExternalCandidate :one
INSERT INTO candidates (
    organization_id, full_name, email, headline,
    clearance_level, clearance_poly, clearance_status,
    years_experience, location, remote, relocatable, available, source, external_id
) VALUES (
    $1, $2, $3, $4,
    'unknown', 'none', 'unknown',
    $5, $6, $7, false, true, 'external', $8
)
ON CONFLICT (organization_id, external_id) WHERE external_id IS NOT NULL DO UPDATE SET
    full_name        = EXCLUDED.full_name,
    email            = EXCLUDED.email,
    headline         = EXCLUDED.headline,
    years_experience = EXCLUDED.years_experience,
    location         = EXCLUDED.location,
    remote           = EXCLUDED.remote,
    updated_at       = now()
RETURNING id;

-- name: InsertSourcingLog :exec
INSERT INTO sourcing_log (organization_id, candidate_id, external_id, source, provider, query, legal_basis)
VALUES ($1, $2, $3, $4, $5, $6, $7);

-- name: ListExternalCandidates :many
-- id is the unique trailing sort key so created_at ties leave a total order and the LIMIT cut is stable.
SELECT id, full_name, email, headline, location, years_experience, available, external_id, created_at
FROM candidates
WHERE source = 'external' AND deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT $1;

-- name: ListSourcingLog :many
-- id is the unique trailing sort key so collected_at ties leave a total order and the LIMIT cut is stable.
SELECT id, candidate_id, external_id, source, provider, query, legal_basis, collected_at
FROM sourcing_log
ORDER BY collected_at DESC, id DESC
LIMIT $1;

-- name: DeleteExternalCandidate :execrows
DELETE FROM candidates WHERE id = $1 AND source = 'external';

-- name: DetachSourcingLog :exec
UPDATE sourcing_log SET candidate_id = NULL WHERE candidate_id = $1;

-- name: DeleteSlateMembersForCandidate :exec
-- Erasure must also remove slate placements of the sourced person; slate_members has no ON DELETE rule
-- so the candidate delete would otherwise fail. RLS scopes this to the caller's org.
DELETE FROM slate_members WHERE candidate_id = $1;