-- +goose Up
-- Teach the AI the federal opportunity landscape. The platform already ingests SAM.gov notices, contract
-- awards, and forecasts into opportunity_records, but nothing ever embedded them, so the inference layer
-- held the opportunities as rows it could aggregate and never as meaning it could retrieve. This adds the
-- same two retrieval arms the candidate corpus already has, so an opportunity can be found by meaning and
-- by words and the two fused with reciprocal rank fusion.
--
-- opportunity_records is GLOBAL PUBLIC reference data with no organization_id and no row level security,
-- exactly like the skills ontology, so this follows the skills pattern rather than the candidates pattern:
-- a vector(384) column pinned to the same MiniLM class dimension the rest of the platform uses, and a
-- PARTIAL HNSW index whose predicate matches the query predicate. The dimension is the one the boot vector
-- gate verifies, so an opportunity vector and a candidate vector are always comparable.
--
-- search_tsv is a GENERATED column, so the full text arm can never drift from the row: an ingest that
-- updates a title updates the index in the same write, with no backfill job to forget. The embedding is
-- necessarily a separate backfill (it requires a model call), which is why it is nullable and why every
-- read filters on IS NOT NULL.

ALTER TABLE opportunity_records
    ADD COLUMN embedding vector(384);

ALTER TABLE opportunity_records
    ADD COLUMN search_tsv tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(agency, '') || ' ' || coalesce(sub_agency, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(naics, '') || ' ' || coalesce(psc, '') || ' ' ||
                                          coalesce(set_aside, '') || ' ' || coalesce(contract_vehicle, '')), 'C') ||
        setweight(to_tsvector('english', coalesce(incumbent_name, '') || ' ' || coalesce(pop_state, '') || ' ' ||
                                          coalesce(pop_city, '')), 'D')
    ) STORED;

-- Partial HNSW so the index matches the "WHERE embedding IS NOT NULL" query predicate, the same shape and
-- build parameters as the skills and candidates indexes.
CREATE INDEX idx_opp_records_embedding_hnsw ON opportunity_records
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64)
    WHERE embedding IS NOT NULL;

CREATE INDEX idx_opp_records_search_tsv ON opportunity_records USING gin (search_tsv);

-- The app role reads the catalog (it already holds SELECT) and the backfill writes vectors as the migrator,
-- matching how skills embeddings are written. UPDATE is granted so an in application embed path can refresh
-- a vector without the migrator, the same grant skills carries.
GRANT UPDATE ON opportunity_records TO missionmatch_app;

-- +goose Down
-- Dropping the columns loses only derived data (a vector and a generated index), never an ingested record,
-- so this down is safe to run and does not need the refusal guard the row owning tables carry.
DROP INDEX IF EXISTS idx_opp_records_search_tsv;
DROP INDEX IF EXISTS idx_opp_records_embedding_hnsw;
ALTER TABLE opportunity_records DROP COLUMN IF EXISTS search_tsv;
ALTER TABLE opportunity_records DROP COLUMN IF EXISTS embedding;