-- +goose Up
-- Continuous adverse impact (four fifths / NYC LL144 / OFCCP) time series.
--
-- This migration lives in db/migrations_eeo/ (NOT db/migrations/), the SECOND migration set applied by
-- the migrator role, so no sqlc model for this table is ever generated into the shared dbgen package the
-- matching and AI code import. It extends the segregated eeo schema with an APPEND ONLY snapshot table.
--
-- Purpose: today the platform computes a single point in time adverse impact report. This table records
-- that report on a schedule so a best in world AEDT tool can PROVE on every release that it is not
-- drifting into disparate impact. Each row is one demographic GROUP bucket's selection rate and four
-- fifths impact ratio at one moment, exactly as the existing AdverseImpact report already labels it.
--
-- Privilege boundary, mirrored from 0001: ONLY missionmatch_compliance may touch the eeo schema. The
-- matching runtime role (missionmatch_app) is granted NOTHING here; an accidental query from matching or
-- the AI path fails with permission denied at the database. RLS is keyed to app.current_org_id exactly
-- like eeo.candidate_demographics, so a snapshot read or write is scoped to the caller's org.
--
-- This is GROUP LEVEL AGGREGATE data only. It is never per person, it is never joined to candidates, and
-- it is NEVER an input to matching. Append only: the compliance role may SELECT and INSERT, never UPDATE
-- or DELETE (a correction is a new snapshot; snapshot_at orders the series).

CREATE TABLE eeo.fairness_snapshots (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id  uuid NOT NULL,
    snapshot_at      timestamptz NOT NULL DEFAULT now(),
    decision_context text NOT NULL DEFAULT '',   -- the selection event the rates are over, mirrors AdverseImpact
    group_label      text NOT NULL,              -- the demographic group bucket as the Report already labels it
    selected         int NOT NULL,               -- the count selected behind the rate
    total            int NOT NULL,               -- the count considered behind the rate
    selection_rate   numeric(7,6) NOT NULL,      -- selected over total
    impact_ratio     numeric(7,6),               -- this group's rate over the highest group's rate; the reference passes at 1.0, null only for an n/a row (unknown group or a cut with fewer than two known groups)
    below_threshold  boolean NOT NULL DEFAULT false, -- impact_ratio < 0.80
    model_version    text NOT NULL DEFAULT ''     -- the aedt_model_versions version in force at snapshot time
);
CREATE INDEX idx_eeo_fairness_org_time ON eeo.fairness_snapshots (organization_id, snapshot_at DESC);

ALTER TABLE eeo.fairness_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE eeo.fairness_snapshots FORCE  ROW LEVEL SECURITY;
CREATE POLICY eeo_fairness_tenant_isolation ON eeo.fairness_snapshots
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- Compliance role ONLY; append only (no UPDATE/DELETE). App role intentionally absent.
GRANT SELECT, INSERT ON eeo.fairness_snapshots TO missionmatch_compliance;
REVOKE ALL ON eeo.fairness_snapshots FROM PUBLIC;

-- +goose Down
DROP TABLE IF EXISTS eeo.fairness_snapshots;