-- +goose Up
-- Solicitation extractions, brought under migration control.
--
-- This table already exists in the production database on M4 holding 2164 rows, but no migration in
-- either set creates it. It was applied to production by hand as raw DDL by the growth engine work,
-- which extracts labor categories, clearances, and skills out of SAM.gov solicitations. RUNBOOK_AIOE
-- recorded the debt and said to renumber the uncommitted copy to 0045 before it entered the repo; 0045
-- was taken in the meantime, so the number in that instruction is stale and this migration is 0060.
--
-- Leaving it uncommitted was not cosmetic. Under the database per tenant model a tenant database is
-- built by replaying the migration set, so a tenant provisioned anywhere other than the original M4
-- machine would come up WITHOUT this table and the growth engine would fail against it, silently and
-- only for the new tenant. Capturing it here is what makes a provisioned tenant reproducible.
--
-- The definition below is transcribed from the live table so the two match exactly, verified against
-- pg_dump of production rather than reconstructed from memory.
--
-- There is deliberately no organization_id and no row level security here, which is the one apparent
-- departure from the tenancy rule. It is correct: the parent table opportunity_records is likewise
-- global and unsecured because SAM.gov solicitations are PUBLIC federal data that is identical for
-- every tenant, not tenant owned records. An extraction is a derived fact about a public solicitation,
-- so it inherits the parent's scope. Adding RLS here would imply a per tenant ownership that does not
-- exist. If opportunity_records ever becomes tenant scoped, this table must change with it.
CREATE TABLE IF NOT EXISTS solicitation_extractions (
    id                       uuid        NOT NULL DEFAULT gen_random_uuid(),
    opportunity_record_id    uuid        NOT NULL,
    labor_category           text,
    required_clearance       text,
    required_skills          jsonb       NOT NULL DEFAULT '[]'::jsonb,
    required_certifications  jsonb       NOT NULL DEFAULT '[]'::jsonb,
    required_years           integer,
    confidence               numeric,
    model_version            text        NOT NULL,
    source_signal            text,
    extract_status           text        NOT NULL DEFAULT 'ok'::text,
    extracted_at             timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT solicitation_extractions_pkey PRIMARY KEY (id),
    -- One extraction per solicitation. This is what makes the extractor idempotent: a re run upserts on
    -- this key rather than accumulating a second opinion about the same solicitation.
    CONSTRAINT solicitation_extractions_uniq_record UNIQUE (opportunity_record_id),
    -- CASCADE, because an extraction is meaningless once the solicitation it describes is gone.
    CONSTRAINT solicitation_extractions_opportunity_record_id_fkey
        FOREIGN KEY (opportunity_record_id) REFERENCES opportunity_records (id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_solext_clearance    ON solicitation_extractions (required_clearance);
CREATE INDEX IF NOT EXISTS idx_solext_extracted_at ON solicitation_extractions (extracted_at);
CREATE INDEX IF NOT EXISTS idx_solext_labor        ON solicitation_extractions (labor_category);

-- Read only for the application and compliance roles, matching production exactly. The extractor runs
-- as the migrator role, so no write grant is issued to the application: the API reads extractions and
-- never authors them.
GRANT SELECT ON solicitation_extractions TO missionmatch_app;
GRANT SELECT ON solicitation_extractions TO missionmatch_compliance;

-- +goose Down
-- This down is deliberately asymmetric with the up, and refuses rather than dropping when the table
-- holds rows. On the production database this migration CREATES NOTHING: the table and its 2164 rows
-- were already there, applied by hand, and this migration only brings them under version control. A
-- plain DROP would therefore destroy data that the corresponding up never authored, which is the one
-- case where the usual symmetry of a migration pair is wrong. On a fresh database, where the up really
-- did create the table and it is still empty, the drop proceeds and up, down, up round trips cleanly.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
    IF to_regclass('public.solicitation_extractions') IS NULL THEN
        RETURN;
    END IF;
    EXECUTE 'SELECT count(*) FROM solicitation_extractions' INTO n;
    IF n > 0 THEN
        RAISE EXCEPTION
            'refusing to drop solicitation_extractions: % rows present. This migration did not create '
            'that data, it only placed an existing hand applied table under version control. Export or '
            'truncate deliberately first if the drop is really intended.', n;
    END IF;
    DROP TABLE solicitation_extractions;
END
$$;
-- +goose StatementEnd