-- +goose Up
-- Opportunity Intelligence unified record cache (Workstream A). One normalized shape over every federal
-- opportunity feed (active SAM solicitations, contract awards, and agency forecasts), so the readiness
-- and Command Center layers read a single provenance tagged catalog instead of one table per feed. Like
-- sam_opportunities and usaspending_awards this is GLOBAL PUBLIC reference data, NOT tenant data:
-- federal opportunities are identical for every org, so the table carries NO organization_id and sits
-- OUTSIDE Row Level Security. The runtime app role gets SELECT only; the ingest sync (cmd/opp-sync)
-- writes as the migrator role. Each row keeps its source provenance and a pointer to the retained raw
-- payload in object storage. Dollar figures are nullable and only set from a real source field, never
-- fabricated; forecast rows carry is_planning true so the UI can label them as planning data.

CREATE TABLE opportunity_records (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source_name          text NOT NULL,                       -- provenance label, for example SAM.gov
    source_record_id     text NOT NULL,                       -- the source's own id (upsert key with source_name)
    dedupe_key           text NOT NULL DEFAULT '',            -- normalized cross source key (solicitation or award id)
    record_kind          text NOT NULL CHECK (record_kind IN ('active_solicitation', 'contract_award', 'forecast')),
    title                text NOT NULL DEFAULT '',
    agency               text NOT NULL DEFAULT '',
    sub_agency           text NOT NULL DEFAULT '',
    naics                text NOT NULL DEFAULT '',
    psc                  text NOT NULL DEFAULT '',
    pop_city             text NOT NULL DEFAULT '',            -- place of performance city
    pop_state            text NOT NULL DEFAULT '',            -- place of performance state code
    pop_country          text NOT NULL DEFAULT '',
    pop_zip              text NOT NULL DEFAULT '',
    set_aside            text NOT NULL DEFAULT '',
    set_aside_code       text NOT NULL DEFAULT '',
    contract_vehicle     text NOT NULL DEFAULT '',
    estimated_value      numeric(16,2),                       -- nullable; only set from a real source figure
    value_source         text NOT NULL DEFAULT '',            -- how the dollar figure is known (the source field)
    incumbent_name       text NOT NULL DEFAULT '',            -- award and forecast incumbent context
    incumbent_uei        text NOT NULL DEFAULT '',
    posted_date          date,
    response_deadline    timestamptz,
    award_date           date,
    estimated_award_date date,
    pop_start_date       date,
    pop_end_date         date,
    ui_link              text NOT NULL DEFAULT '',
    is_planning          boolean NOT NULL DEFAULT false,      -- true for forecasts: planning data subject to change
    raw_object_key       text NOT NULL DEFAULT '',            -- object storage key for the retained raw payload
    raw_sha256           text NOT NULL DEFAULT '',            -- integrity stamp of the retained payload
    fetched_at           timestamptz NOT NULL,                -- when the source was fetched (the record provenance time)
    synced_at            timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source_name, source_record_id)
);

CREATE INDEX idx_opp_records_kind   ON opportunity_records (record_kind);
CREATE INDEX idx_opp_records_naics  ON opportunity_records (naics);
CREATE INDEX idx_opp_records_agency ON opportunity_records (agency);
CREATE INDEX idx_opp_records_state  ON opportunity_records (pop_state);
CREATE INDEX idx_opp_records_dedupe ON opportunity_records (dedupe_key);
CREATE INDEX idx_opp_records_posted ON opportunity_records (posted_date DESC NULLS LAST);

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

-- +goose Down
DROP TABLE IF EXISTS opportunity_records;