-- name: ListSlateDecisionEvidenceForOrg :many
-- The WS8 in tenant teaching read. It returns ONLY the contents free craft evidence the producer folds
-- into scrub.TeachingSignal counts: the reviewer decision on each slate member, the stored sub scores that
-- reveal a clearance gap (clearance_score) or an experience shortfall (experience_score), the frozen gap
-- summary, and the matched skills evidence (per skill similarity and whether the match was exact or a
-- semantic substitution) from the candidate's persisted evaluation. It carries NO name, NO email, NO free
-- text resume, only the decision label, the scores, and the jsonb match evidence the engine itself froze.
--
-- Both slate_members and candidate_evaluations are RLS scoped, and the producer runs this inside
-- RunInTenantTx on the app pool, so the join is scoped to the caller's org by Postgres Row Level Security
-- and can never read another tenant's decisions. candidate_evaluations is LEFT joined on the pair
-- (organization_id, candidate_id, requirement_id): a slate member that was never screened through
-- screenMATCH has no stored evaluation, so the matched skills evidence is then NULL and the producer folds
-- only what slate_members alone supports (the clearance and experience patterns), never fabricating a
-- skill substitution it cannot see.
SELECT
    sm.candidate_id,
    sm.decision,
    sm.clearance_score,
    sm.experience_score,
    sm.gap_summary,
    s.requirement_id,
    r.required_level,
    ce.matched_skills
FROM slate_members sm
JOIN slates s
    ON s.organization_id = sm.organization_id
   AND s.id = sm.slate_id
JOIN requirements r
    ON r.organization_id = s.organization_id
   AND r.id = s.requirement_id
LEFT JOIN candidate_evaluations ce
    ON ce.organization_id = sm.organization_id
   AND ce.candidate_id = sm.candidate_id
   AND ce.requirement_id = s.requirement_id
ORDER BY sm.slate_id, sm.candidate_id;

-- name: UpsertTeachingSignalAggregate :exec
-- Persist one folded per tenant craft aggregate into the global teaching_signal_aggregates table. This is
-- a GLOBAL migrator owned table (app has SELECT only), so the producer runs this through the migrator pool
-- seam, never the app role. The unique key is (coalesce(tenant_id, the nil uuid), pattern, band, outcome,
-- model_version, window_start), so a rerun over the same window for the same tenant refreshes the counts in
-- place rather than inserting a duplicate, and a forked engine model version lands as a distinct row rather
-- than silently overwriting a prior version's aggregate. The live in tenant rows always carry tenant_id set
-- and dp_applied false; the flag on cross tenant path writes tenant_id NULL, dp_applied true rows later.
INSERT INTO teaching_signal_aggregates (
    tenant_id, dp_applied, pattern, band, outcome, model_version,
    observations, successes, window_start, window_end
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT (
    coalesce(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid),
    pattern, band, outcome, model_version, window_start
) DO UPDATE SET
    observations = EXCLUDED.observations,
    successes    = EXCLUDED.successes,
    window_end   = EXCLUDED.window_end,
    dp_applied   = EXCLUDED.dp_applied;

-- name: ListTeachingAggregatesForTenant :many
-- The cross cutting GOVERNANCE CONFIDENCE the proposal generator reads: this tenant's own folded craft counts
-- for the live in tenant rows (dp_applied false). The generator uses these to confirm a craft pattern is
-- reliably approved at a band before it ever proposes a curated edge, so the specific recurring pair (read
-- from the tenant's own evaluations) is only proposed when the contents free aggregate also supports it.
-- These rows carry NO PII and no candidate id, only the counts. Migrator pool, the global table; the tenant
-- is passed explicitly because this read is governance confidence, not the RLS scoped evaluation scan.
SELECT id, tenant_id, dp_applied, pattern, band, outcome, model_version, observations, successes,
       window_start, window_end, created_at
  FROM teaching_signal_aggregates
 WHERE tenant_id = $1
   AND dp_applied = false
   AND model_version = $2;

-- name: GetLatestDpBudgetLedger :one
-- The hard spend gate read. It returns the most recent ledger row for one (model_version, tenant_id) budget
-- key, which carries the running accumulated RDP after every release composed so far. The gate adds the
-- candidate release's per order RDP to this accumulation, reconverts to epsilon at the configured delta, and
-- refuses the release (writing nothing) if it would exceed the budget. coalesce maps a NULL tenant_id (the
-- cross tenant cohort) onto the fixed nil uuid so the cross tenant releases share one budget key space, the
-- same convention the aggregates table uses. Ordered released_at then id descending so the newest composed
-- accumulation is row one; zero rows means this is the first release on a fresh budget. dp_budget_ledger is a
-- global migrator owned table the app role can only SELECT, so this read runs through the migrator pool.
SELECT id, model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta,
       contributing_tenant_count, released_at
  FROM dp_budget_ledger
 WHERE model_version = $1
   AND coalesce(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid)
       = coalesce(sqlc.narg('tenant_id')::uuid, '00000000-0000-0000-0000-000000000000'::uuid)
 ORDER BY released_at DESC, id DESC
 LIMIT 1;

-- name: InsertDpBudgetLedger :one
-- Record one differential privacy release in the append only budget ledger. The gate inserts this ONLY after
-- it has confirmed the release stays within budget, so a persisted row is by construction an in budget,
-- already audited release. rdp_orders is the jsonb map of Renyi order to the per order accumulated RDP at
-- this release; accumulated_rdp is the running RDP over the worst case (max over the grid converted) used for
-- the human readable summary; epsilon_at_delta is the converted epsilon (the min over orders) at the target
-- delta; delta is that target; contributing_tenant_count is the number of live non test distinct
-- organizations in the cohort, sourced from the production flag, never a literal. The spent epsilon columns
-- are numeric so the audit record is bit stable while the grid math ran in float64. dp_budget_ledger is a
-- global migrator owned table the app role can only SELECT, so this write runs through the migrator pool.
INSERT INTO dp_budget_ledger (
    model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta,
    contributing_tenant_count
) VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id, model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta,
          contributing_tenant_count, released_at;

-- name: ResolveSkillIDByName :one
-- Resolve a skill name to its global skills.id, lower cased and trimmed, so a matched_skills entry that
-- carries a candidate or required skill NAME but no id can still be turned into the id the proposal and the
-- staged edge need. The skills table is global reference data the app role may SELECT; the proposal generator
-- runs this through the same RLS scoped app tx that read the org's evaluations (the read is the only app
-- pool step, the proposal WRITE goes through the migrator pool).
SELECT id FROM skills WHERE lower(name) = lower(trim($1));

-- name: GetActiveProposalForPair :one
-- Idempotency guard for the proposal generator: returns the existing proposal for a (from_skill_id,
-- to_skill_id, relation) triple that is still pending or already approved, so the generator never creates a
-- duplicate suggestion for a pair a human is already deciding or has already decided. A rejected proposal is
-- intentionally NOT matched here, so a human who rejected a pair can see it proposed again if the evidence
-- recurs (the human stays in control either way). Migrator pool, the global table.
SELECT id, kind, status, from_skill_id, to_skill_id, relation, proposed_strength
  FROM curation_proposals
 WHERE from_skill_id = $1
   AND to_skill_id = $2
   AND relation = $3
   AND status IN ('pending', 'approved')
 LIMIT 1;

-- name: InsertCurationProposal :one
-- Create a pending curation proposal. Never auto applied and never auto rejected: status defaults to pending,
-- approver and approved_at stay NULL until a human acts. Migrator pool only, because curation_proposals is a
-- global migrator owned table the app role can only SELECT.
INSERT INTO curation_proposals (
    kind, from_skill_id, to_skill_id, relation, proposed_strength,
    evidence_observations, evidence_successes, model_version
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
RETURNING id, kind, from_skill_id, to_skill_id, relation, proposed_strength,
          evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at;

-- name: ListPendingProposals :many
-- The /compliance surface of proposals awaiting the first human step. Migrator pool, the global table, gated
-- behind the compliance or operator role at the handler. Ordered oldest first so the queue is a stable list.
SELECT id, kind, from_skill_id, to_skill_id, relation, proposed_strength,
       evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at
  FROM curation_proposals
 WHERE status = 'pending'
 ORDER BY created_at, id;

-- name: GetProposal :one
-- Read one proposal by id for the approve and verify handlers. Migrator pool, the global table.
SELECT id, kind, from_skill_id, to_skill_id, relation, proposed_strength,
       evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at
  FROM curation_proposals
 WHERE id = $1;

-- name: ApproveProposalTx :one
-- The first of two human steps. Set status to approved and record the approver and approved_at, but ONLY when
-- the proposal is still pending, so a double submit or a concurrent approval cannot move an already decided
-- proposal. Returns the row only on a real transition; zero rows means it was not pending. Migrator pool.
UPDATE curation_proposals
   SET status = 'approved', approver = $2, approved_at = now()
 WHERE id = $1
   AND status = 'pending'
RETURNING id, kind, from_skill_id, to_skill_id, relation, proposed_strength,
          evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at;

-- name: StageSubstitutesForEdge :one
-- Insert the staged, INERT skill_relationships edge that an approval creates. verified_at is left NULL, so the
-- resolver (which loads only verified_at IS NOT NULL edges) ignores it and no score changes. A SECOND distinct
-- human action later sets verified_at to activate it. On a conflicting (from, to, relation) that already
-- exists, do nothing and return the existing row, so an approval is idempotent against a manually seeded edge.
-- Migrator pool only (skill_relationships is global, the app role has SELECT only).
INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at)
VALUES ($1, $2, 'substitutes_for', $3, 'ws8_curation', NULL)
ON CONFLICT (from_skill_id, to_skill_id, relation) DO UPDATE SET source = skill_relationships.source
RETURNING id, from_skill_id, to_skill_id, relation, strength, verified_at;

-- name: VerifyStagedEdge :one
-- The second of two human steps. Activate a staged edge by setting verified_at and recording the verifier,
-- ONLY when it is still unverified, so a double submit cannot re activate or change the verifier of an already
-- live edge. The resolver reads verified_at IS NOT NULL edges, so this single write is what makes the edge
-- live across tenants. The engine math is byte identical: only the resolver reference set grew. Migrator pool.
UPDATE skill_relationships
   SET verified_at = now(), verifier = $2
 WHERE id = $1
   AND verified_at IS NULL
RETURNING id, from_skill_id, to_skill_id, relation, strength, verified_at, verifier;