-- +goose Up
-- WS1 ontology staging and reference tables (AI Grounding Program). Like skills, sam_opportunities,
-- and opportunity_records these are GLOBAL PUBLIC reference data, NOT tenant data: the federal
-- vocabulary is identical for every org, so these tables carry NO organization_id and sit OUTSIDE Row
-- Level Security. The runtime app role gets SELECT only; cmd/ontology-sync writes as the migrator role.
-- ontology_terms is the raw pulled vocabulary with provenance; ontology_crosswalk links codes across
-- frameworks (NICE to DCWF, SOC to skill); mission_spine is the hand curated IC vocabulary, seeded here.

CREATE TABLE ontology_terms (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source_name      text NOT NULL,
    source_record_id text NOT NULL,
    term_kind        text NOT NULL CHECK (term_kind IN ('skill','labor_category','work_role','ksa','psc_code','mission_area')),
    term             text NOT NULL DEFAULT '',
    code             text NOT NULL DEFAULT '',
    category         text NOT NULL DEFAULT '',
    description      text NOT NULL DEFAULT '',
    raw_object_key   text NOT NULL DEFAULT '',
    raw_sha256       text NOT NULL DEFAULT '',
    fetched_at       timestamptz NOT NULL,
    synced_at        timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source_name, source_record_id)
);
CREATE INDEX idx_ontology_terms_kind ON ontology_terms (term_kind);
CREATE INDEX idx_ontology_terms_code ON ontology_terms (code);

CREATE TABLE ontology_crosswalk (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_system text NOT NULL,
    from_code   text NOT NULL,
    to_system   text NOT NULL,
    to_code     text NOT NULL,
    relation    text NOT NULL DEFAULT 'maps_to',
    source_name text NOT NULL DEFAULT '',
    synced_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (from_system, from_code, to_system, to_code)
);

CREATE TABLE mission_spine (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    version            text NOT NULL,
    term               text NOT NULL,
    canonical_definition text NOT NULL DEFAULT '',
    authority          text NOT NULL DEFAULT '',
    source_doc         text NOT NULL DEFAULT '',
    source_url         text NOT NULL DEFAULT '',
    effective_date     date,
    UNIQUE (version, term)
);

INSERT INTO mission_spine (version, term, canonical_definition, authority, source_doc) VALUES
 ('v1','SIGINT','Intelligence derived from signals, communications, electronic, and foreign instrumentation.','ODNI','Intelligence disciplines'),
 ('v1','GEOINT','Intelligence from the exploitation and analysis of imagery and geospatial information.','ODNI','Intelligence disciplines'),
 ('v1','HUMINT','Intelligence collected from human sources.','ODNI','Intelligence disciplines'),
 ('v1','MASINT','Measurement and signature intelligence.','ODNI','Intelligence disciplines'),
 ('v1','OSINT','Intelligence produced from publicly available information.','ODNI','IC OSINT Strategy'),
 ('v1','Satellite C2','Command and control of satellite systems.','JP 3-14','Space Operations'),
 ('v1','Cross Domain Solution','A controlled interface providing manual or automated access between security domains.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','COMSEC','Communications security: measures to deny unauthorized access to telecommunications.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','TEMPEST','Protection against compromising emanations.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','Electronic Warfare','Military action using electromagnetic and directed energy.','JP 3-13.1','Electronic Warfare');

GRANT SELECT ON ontology_terms TO missionmatch_app;
GRANT SELECT ON ontology_crosswalk TO missionmatch_app;
GRANT SELECT ON mission_spine TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS mission_spine;
DROP TABLE IF EXISTS ontology_crosswalk;
DROP TABLE IF EXISTS ontology_terms;