-- +goose Up
-- SAM.gov Contract Opportunities cache (screenMATCH requirement feed).
--
-- This is a GLOBAL PUBLIC reference catalog, NOT tenant data: federal opportunities published on
-- SAM.gov are public and identical for every org, keyed by SAM's own notice_id. It therefore has NO
-- organization_id and is deliberately OUTSIDE Row-Level Security (like audit_logs), every tenant
-- reads the same cached catalog. The runtime app role gets SELECT only; the daily sync writes as the
-- migrator role (see cmd/sam-sync). The frontend never calls SAM live, it always reads this store,
-- so the demo is fast and survives SAM downtime or quota exhaustion.

CREATE TABLE sam_opportunities (
    notice_id            text PRIMARY KEY,                  -- SAM noticeId (globally unique; upsert key)
    title                text NOT NULL DEFAULT '',
    solicitation_number  text NOT NULL DEFAULT '',
    department           text NOT NULL DEFAULT '',          -- first segment of fullParentPathName
    full_parent_path     text NOT NULL DEFAULT '',          -- full dotted agency hierarchy
    sub_agency           text NOT NULL DEFAULT '',          -- normalized (AIR FORCE / SPACE FORCE / 2nd segment)
    naics_code           text NOT NULL DEFAULT '',
    psc_code             text NOT NULL DEFAULT '',          -- classificationCode (PSC)
    set_aside_code       text NOT NULL DEFAULT '',          -- typeOfSetAside (e.g. 8A)
    set_aside            text NOT NULL DEFAULT '',          -- typeOfSetAsideDescription
    notice_type          text NOT NULL DEFAULT '',          -- type
    base_type            text NOT NULL DEFAULT '',          -- baseType
    posted_date          date,                              -- nullable
    response_deadline    timestamptz,                       -- nullable (carries a time)
    active               boolean NOT NULL DEFAULT true,     -- SAM "active" Yes/No -> bool
    pop_city             text NOT NULL DEFAULT '',          -- placeOfPerformance.city.name
    pop_state            text NOT NULL DEFAULT '',          -- placeOfPerformance.state.code
    pop_zip              text NOT NULL DEFAULT '',
    ui_link              text NOT NULL DEFAULT '',          -- public sam.gov notice link
    description_url      text NOT NULL DEFAULT '',          -- description (a URL; append api_key to fetch text)
    resource_links       jsonb NOT NULL DEFAULT '[]',       -- attachment download URLs
    poc                  jsonb NOT NULL DEFAULT '[]',       -- pointOfContact array
    raw                  jsonb NOT NULL,                    -- full SAM JSON for this notice
    source               text NOT NULL DEFAULT 'SAM.gov',
    synced_at            timestamptz NOT NULL DEFAULT now()
);

-- In-memory/SQL filter columns (the sync pulls by NAICS only; the app filters 8(a)/agency/state here).
CREATE INDEX idx_sam_opps_naics        ON sam_opportunities (naics_code);
CREATE INDEX idx_sam_opps_state        ON sam_opportunities (pop_state);
CREATE INDEX idx_sam_opps_setaside     ON sam_opportunities (set_aside_code);
CREATE INDEX idx_sam_opps_department   ON sam_opportunities (department);
CREATE INDEX idx_sam_opps_subagency    ON sam_opportunities (sub_agency);
CREATE INDEX idx_sam_opps_active_posted ON sam_opportunities (active, posted_date DESC);

-- Runtime app role: read-only (NOBYPASSRLS; no RLS on this table so it sees the whole public catalog).
GRANT SELECT ON sam_opportunities TO missionmatch_app;
-- The migrator owns the table and runs the sync writes; compliance never needs it.

-- +goose Down
DROP TABLE IF EXISTS sam_opportunities;