-- +goose Up
-- USASpending federal awards cache: incumbent and competitor intelligence plus Galapagos own past
-- performance. Like sam_opportunities this is a GLOBAL PUBLIC catalog (federal award data is public),
-- so it has no organization_id and sits OUTSIDE Row Level Security. The runtime app role reads only;
-- the daily sync writes as the migrator role. The frontend reads this cache, never USASpending live.

CREATE TABLE usaspending_awards (
    generated_internal_id text PRIMARY KEY,             -- USASpending unique award id (upsert key)
    award_id              text NOT NULL DEFAULT '',     -- PIID
    recipient_name        text NOT NULL DEFAULT '',
    recipient_uei         text NOT NULL DEFAULT '',
    recipient_id          text NOT NULL DEFAULT '',
    award_amount          numeric(16,2),                -- nullable
    awarding_agency       text NOT NULL DEFAULT '',
    awarding_sub_agency   text NOT NULL DEFAULT '',
    pop_start_date        date,
    pop_end_date          date,                         -- current period of performance end (recompete signal)
    naics_code            text NOT NULL DEFAULT '',
    naics_description     text NOT NULL DEFAULT '',
    description           text NOT NULL DEFAULT '',
    is_galapagos          boolean NOT NULL DEFAULT false, -- true when this is Galapagos own award history
    raw                   jsonb NOT NULL,
    source                text NOT NULL DEFAULT 'USASpending',
    synced_at             timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_awards_naics      ON usaspending_awards (naics_code, award_amount DESC NULLS LAST);
CREATE INDEX idx_awards_galapagos  ON usaspending_awards (is_galapagos) WHERE is_galapagos;
CREATE INDEX idx_awards_recipient  ON usaspending_awards (recipient_uei);

GRANT SELECT ON usaspending_awards TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS usaspending_awards;