-- +goose Up
-- EEO / demographic data — PHYSICALLY + PRIVILEGE segregated.
--
-- This migration lives in db/migrations_eeo/ (NOT db/migrations/) so it is OUTSIDE sqlc's schema
-- path: no model for this table is ever generated into the shared dbgen package that matching code
-- imports [review EEO-1]. It is applied by goose as a SECOND migration set, by the migrator role.
--
-- Privilege boundary [review EEO-2/RLS-4]: ONLY missionmatch_compliance may touch this schema. The
-- matching runtime role (missionmatch_app) is granted NOTHING here — an accidental EEO query from
-- matching fails with "permission denied" at the DB. Append-only-ish [review EEO-4]: compliance may
-- SELECT/INSERT but not UPDATE/DELETE (corrections are new rows; collected_at orders history).
--
-- Purpose: PRODUCE aggregate adverse-impact (four-fifths / NYC LL144 / OFCCP) data ONLY — never a
-- per-candidate matching feature. The (organization_id, candidate_id) key joins to public.slate_members
-- for selection-rate reporting, run as missionmatch_compliance [review COMPL-5].

CREATE SCHEMA IF NOT EXISTS eeo;
GRANT USAGE ON SCHEMA eeo TO missionmatch_compliance;

CREATE TABLE eeo.candidate_demographics (
    id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    organization_id   uuid NOT NULL,
    candidate_id      uuid NOT NULL,            -- logical FK to public.candidates(id); deliberately NO
                                                -- cross-schema FK so the link is not discoverable from public
    gender            text,
    race_ethnicity    text,
    veteran_status    text,
    disability_status text,
    consent_given     boolean NOT NULL DEFAULT false,  -- voluntary self-ID consent
    collected_by      uuid,
    collected_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_eeo_org       ON eeo.candidate_demographics (organization_id);
CREATE INDEX idx_eeo_candidate ON eeo.candidate_demographics (organization_id, candidate_id);
ALTER TABLE eeo.candidate_demographics ENABLE ROW LEVEL SECURITY;
ALTER TABLE eeo.candidate_demographics FORCE  ROW LEVEL SECURITY;
CREATE POLICY eeo_tenant_isolation ON eeo.candidate_demographics
    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.candidate_demographics TO missionmatch_compliance;
REVOKE ALL ON eeo.candidate_demographics FROM PUBLIC;

-- +goose Down
DROP TABLE IF EXISTS eeo.candidate_demographics;
DROP SCHEMA IF EXISTS eeo;