-- AEDT governance: model version registry, the org AEDT inventory, candidate notices, and the
-- retention sweep. All on the app pool under RLS (the eeo schema is reached only by the compliance
-- pool, never here).

-- name: ListModelVersions :many
SELECT version, weights, band_cuts, notes, effective_from, created_at
FROM aedt_model_versions
ORDER BY effective_from DESC, version;

-- name: ListAedtInventory :many
SELECT * FROM aedt_inventory ORDER BY created_at DESC, id;

-- name: UpsertAedtInventory :one
INSERT INTO aedt_inventory (organization_id, tool_key, display_name, purpose, model_version)
VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4)
ON CONFLICT (organization_id, tool_key) DO UPDATE
    SET display_name = EXCLUDED.display_name,
        purpose = EXCLUDED.purpose,
        model_version = EXCLUDED.model_version,
        updated_at = now()
RETURNING *;

-- name: InsertCandidateNotice :one
INSERT INTO aedt_candidate_notices (organization_id, candidate_id, notice_version, data_categories, retention_days, disclosed_by)
VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4, $5)
RETURNING *;

-- name: RetentionDueCandidates :many
-- Candidates whose most recent recorded decision is older than the retention window and whose PII is
-- not yet anonymized. The de identified slate_members outcomes are retained; only the candidate PII is
-- wiped (by AnonymizeCandidate). RLS scopes this to the caller's org.
SELECT c.id
FROM candidates c
WHERE c.anonymized_at IS NULL
  AND (
      SELECT max(sm.decided_at)
      FROM slate_members sm
      WHERE sm.candidate_id = c.id AND sm.decided_at IS NOT NULL
  ) < now() - make_interval(days => $1::int)
LIMIT 500;