-- +goose Up
-- The acronym and term expansion layer for screenMATCH normalization: a tenant extendable table
-- mapping a GovCon or DoD term to its expansion, applied during requirement and resume skill
-- matching so JWICS matches Joint Worldwide Intelligence Communications System without waiting for
-- the ontology to hold both forms. Global seed rows carry organization_id NULL and belong to the
-- platform (migrator owned, tenants read them); tenant rows are RLS scoped writes.
--
-- Policies are per command so a tenant can never modify the global seed: SELECT sees global plus
-- own rows, INSERT and DELETE reach ONLY own rows. There is no UPDATE grant at all: a correction is
-- a delete plus an insert, which keeps the surface append and remove only.
CREATE TABLE term_expansions (
    id              uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid REFERENCES organizations(id),
    alias           text NOT NULL,
    expansion       text NOT NULL,
    created_by      uuid,
    created_at      timestamptz NOT NULL DEFAULT now(),
    CHECK (length(btrim(alias)) BETWEEN 1 AND 80),
    CHECK (length(btrim(expansion)) BETWEEN 2 AND 200)
);
CREATE UNIQUE INDEX uq_term_expansions_scope_alias_expansion
    ON term_expansions (COALESCE(organization_id, '00000000-0000-0000-0000-000000000000'::uuid),
                        lower(alias), lower(expansion));
CREATE INDEX idx_term_expansions_org ON term_expansions (organization_id, created_at DESC);

ALTER TABLE term_expansions ENABLE ROW LEVEL SECURITY;
ALTER TABLE term_expansions FORCE  ROW LEVEL SECURITY;
CREATE POLICY term_expansions_read ON term_expansions FOR SELECT TO missionmatch_app
    USING (organization_id IS NULL
           OR organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
CREATE POLICY term_expansions_insert ON term_expansions FOR INSERT TO missionmatch_app
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
CREATE POLICY term_expansions_delete ON term_expansions FOR DELETE TO missionmatch_app
    USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
GRANT SELECT, INSERT, DELETE ON term_expansions TO missionmatch_app;

-- The global GovCon and DoD seed. organization_id NULL, visible to every tenant, owned by the
-- migrator. Clearance style terms are included deliberately: the layer normalizes requirement and
-- resume TEXT wherever skills are compared, and these are the terms cleared staffing actually uses.
INSERT INTO term_expansions (alias, expansion) VALUES
    ('SIGINT',   'Signals Intelligence'),
    ('HUMINT',   'Human Intelligence'),
    ('GEOINT',   'Geospatial Intelligence'),
    ('MASINT',   'Measurement and Signature Intelligence'),
    ('OSINT',    'Open Source Intelligence'),
    ('ELINT',    'Electronic Intelligence'),
    ('ISR',      'Intelligence Surveillance and Reconnaissance'),
    ('RMF',      'Risk Management Framework'),
    ('ATO',      'Authority to Operate'),
    ('cATO',     'Continuous Authority to Operate'),
    ('IAT',      'Information Assurance Technical'),
    ('IAM',      'Information Assurance Management'),
    ('TS/SCI',   'Top Secret SCI'),
    ('TS SCI',   'Top Secret SCI'),
    ('CI poly',  'Counterintelligence Polygraph'),
    ('CI polygraph', 'Counterintelligence Polygraph'),
    ('FSP',      'Full Scope Polygraph'),
    ('FS poly',  'Full Scope Polygraph'),
    ('PMP',      'Project Management Professional'),
    ('JWICS',    'Joint Worldwide Intelligence Communications System'),
    ('SIPRNet',  'Secret Internet Protocol Router Network'),
    ('NIPRNet',  'Non-classified Internet Protocol Router Network'),
    ('COMSEC',   'Communications Security'),
    ('OPSEC',    'Operations Security'),
    ('INFOSEC',  'Information Security'),
    ('STIG',     'Security Technical Implementation Guide'),
    ('FedRAMP',  'Federal Risk and Authorization Management Program'),
    ('SDVOSB',   'Service Disabled Veteran Owned Small Business'),
    ('VOSB',     'Veteran Owned Small Business'),
    ('WOSB',     'Woman Owned Small Business'),
    ('8(a)',     'SBA 8a Business Development Program'),
    ('8a',       'SBA 8a Business Development Program'),
    ('HUBZone',  'Historically Underutilized Business Zone'),
    ('CONUS',    'Continental United States'),
    ('OCONUS',   'Outside the Continental United States'),
    ('COMINT',   'Communications Intelligence'),
    ('EW',       'Electronic Warfare'),
    ('C2',       'Command and Control'),
    ('C4ISR',    'Command Control Communications Computers Intelligence Surveillance and Reconnaissance'),
    ('CNO',      'Computer Network Operations'),
    ('DCO',      'Defensive Cyberspace Operations'),
    ('OCO',      'Offensive Cyberspace Operations'),
    ('SOC',      'Security Operations Center'),
    ('SIEM',     'Security Information and Event Management'),
    ('IdAM',     'Identity and Access Management'),
    ('ICAM',     'Identity Credential and Access Management'),
    ('PKI',      'Public Key Infrastructure'),
    ('ZTA',      'Zero Trust Architecture'),
    ('SAP',      'Special Access Program'),
    ('SCIF',     'Sensitive Compartmented Information Facility'),
    ('FSO',      'Facility Security Officer'),
    ('DISS',     'Defense Information System for Security'),
    ('NISPOM',   'National Industrial Security Program Operating Manual'),
    ('CMMC',     'Cybersecurity Maturity Model Certification'),
    ('CUI',      'Controlled Unclassified Information'),
    ('POAM',     'Plan of Action and Milestones'),
    ('eMASS',    'Enterprise Mission Assurance Support Service'),
    ('ACAS',     'Assured Compliance Assessment Solution'),
    ('HBSS',     'Host Based Security System'),
    ('TS',       'Top Secret');

-- +goose Down
DROP TABLE IF EXISTS term_expansions;