-- +goose Up
-- M7 bid outcomes ledger. One append friendly row per bid: what the bid was for (an opportunity, a
-- requirement, or a slate, each nullable because not every bid links all three), when the proposal was
-- submitted, the win or loss result once the award decision lands, who won on a loss, and the debrief
-- notes. It ships DORMANT: the recording path is flag gated off by default (MM_BID_OUTCOMES_ENABLED) so
-- the table accrues real win and loss labels the day it lands while no learned model consumes it yet. The
-- single advisory read over it (bid_win_rate_by_segment in the closed AI Registry) returns a market base
-- rate, never a candidate score; the frozen matching engine stays the only scorer. This is data plumbing,
-- so there is NO model_version bump and NO engine change.
--
-- Tenant isolation is the platform standard, defense in depth: RLS on organization_id (ENABLE plus FORCE,
-- USING plus WITH CHECK) PLUS composite foreign keys that carry organization_id, so a child row can never
-- reference a parent in another tenant (FK validation bypasses RLS, so WITH CHECK alone is necessary but
-- not sufficient). Every reference target here already carries a composite UNIQUE (organization_id, id):
-- opportunities (uq_opportunities_org_id), requirements (uq_requirements_org_id), and slates
-- (uq_slates_org_id), all in 0002_matching.sql, so all three links are composite FKs ON DELETE SET NULL.
-- The app role records and updates an outcome and never deletes one, so the grant is SELECT, INSERT,
-- UPDATE with no DELETE.

CREATE TABLE bid_outcomes (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES organizations(id),
    opportunity_id  uuid,                          -- the opportunity the bid was for; null when not linked
    requirement_id  uuid,                          -- the requirement the bid was for; null when not linked
    slate_id        uuid,                          -- the slate the bid was staffed from; null when not linked
    submitted_at    timestamptz,                   -- when the proposal was submitted; null until submitted
    result          text NOT NULL DEFAULT 'pending'
                      CHECK (result IN ('win', 'loss', 'no_bid', 'pending')),
    award_date      date,                          -- when the award decision landed; null until known
    awardee_uei     text NOT NULL DEFAULT '',      -- who won, recorded on a loss
    notes           text NOT NULL DEFAULT '',      -- debrief notes
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    -- tenant composite key so this row can be a composite FK target if a later child ever needs one
    CONSTRAINT uq_bid_outcomes_org_id UNIQUE (organization_id, id),
    -- the linked opportunity is in the same org; a hard delete of the opportunity nulls the link rather
    -- than orphaning or blocking the outcome record, so the win or loss label survives the parent delete
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL,
    -- the linked requirement is in the same org; a hard delete nulls the link, keeping the label
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL,
    -- the linked slate is in the same org; a hard delete nulls the link, keeping the label
    FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_bid_outcomes_org        ON bid_outcomes (organization_id, created_at DESC);
CREATE INDEX idx_bid_outcomes_org_result ON bid_outcomes (organization_id, result);

ALTER TABLE bid_outcomes ENABLE ROW LEVEL SECURITY;
ALTER TABLE bid_outcomes FORCE  ROW LEVEL SECURITY;
CREATE POLICY bid_outcomes_isolation ON bid_outcomes
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- The app role records an outcome (INSERT), updates the result and award once known (UPDATE), and reads it
-- (SELECT for the advisory base rate). It never deletes an outcome: a win or loss label is a permanent fact.
GRANT SELECT, INSERT, UPDATE ON bid_outcomes TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS bid_outcomes;