-- +goose Up
-- federal_mission_tags tags global federal records (opportunity_records, usaspending_awards) with one or
-- more mission areas, each carrying the mechanism and the evidence phrase so the inference is traceable.
-- Global reference data: migrator owned writes, app SELECT only, outside RLS, no PII and no eeo reach.
CREATE TABLE federal_mission_tags (
    record_table  text NOT NULL CHECK (record_table IN ('opportunity_records','usaspending_awards')),
    record_id     text NOT NULL,
    mission_code  text NOT NULL REFERENCES mission_areas(code),
    mechanism     text NOT NULL,
    evidence      text NOT NULL DEFAULT '',
    confidence    numeric(3,2) NOT NULL DEFAULT 1.0,
    classified_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (record_table, record_id, mission_code)
);
CREATE INDEX idx_federal_mission_tags_code ON federal_mission_tags (mission_code, record_table);
GRANT SELECT ON federal_mission_tags TO missionmatch_app;

-- award_incumbent_roles holds the incumbent labor mix mined from award and solicitation text, with the
-- evidence phrase. Counts come only from genuine text matches, never fabricated.
CREATE TABLE award_incumbent_roles (
    record_table   text NOT NULL CHECK (record_table IN ('opportunity_records','usaspending_awards','sam_opportunities')),
    record_id      text NOT NULL,
    labor_category text NOT NULL,
    role_count     int  NOT NULL DEFAULT 0,
    evidence       text NOT NULL DEFAULT '',
    classified_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (record_table, record_id, labor_category)
);
GRANT SELECT ON award_incumbent_roles TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS award_incumbent_roles;
DROP TABLE IF EXISTS federal_mission_tags;