-- +goose Up
-- Fixes a conflict introduced by 0048.
--
-- application_stage_events is append only, enforced by the shared forbid_mutation() trigger. But unlike
-- audit_logs, which nothing references, this table is the target of an ON DELETE CASCADE from
-- applications. forbid_mutation() refuses DELETE unconditionally and does not care which role is asking,
-- so the cascade is refused too and an application can never be deleted by anyone.
--
-- That blocks three things the product genuinely needs: tenant offboarding, the privacy erasure path
-- that already exists for candidates, and removing test data.
--
-- The fix keeps the property that actually matters. The APPLICATION role can never rewrite recruiting
-- history, which is what makes every derived metric auditable. The MIGRATOR role, which already holds
-- BYPASSRLS and owns schema and lifecycle operations, may perform maintenance. The check is on
-- current_user rather than a session flag, so the application role cannot grant itself the exemption by
-- setting a GUC. It is also belt and braces: UPDATE and DELETE are revoked from the application role at
-- the grant level as well, so the trigger is the second of two independent barriers.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_forbid_event_mutation() RETURNS trigger AS $$
BEGIN
    IF current_user = 'missionmatch_migrator' THEN
        -- BEFORE DELETE must return OLD to permit the row deletion, BEFORE UPDATE must return NEW.
        -- A statement level TRUNCATE trigger ignores the return value entirely.
        IF TG_OP = 'DELETE' THEN
            RETURN OLD;
        END IF;
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'append-only table: % not permitted for role %', TG_OP, current_user;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

DROP TRIGGER IF EXISTS application_stage_events_append_only ON application_stage_events;
DROP TRIGGER IF EXISTS application_stage_events_no_truncate ON application_stage_events;

CREATE TRIGGER application_stage_events_append_only
    BEFORE UPDATE OR DELETE ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION rm_forbid_event_mutation();
CREATE TRIGGER application_stage_events_no_truncate
    BEFORE TRUNCATE ON application_stage_events
    FOR EACH STATEMENT EXECUTE FUNCTION rm_forbid_event_mutation();

-- +goose Down
DROP TRIGGER IF EXISTS application_stage_events_append_only ON application_stage_events;
DROP TRIGGER IF EXISTS application_stage_events_no_truncate ON application_stage_events;

CREATE TRIGGER application_stage_events_append_only
    BEFORE UPDATE OR DELETE ON application_stage_events
    FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE TRIGGER application_stage_events_no_truncate
    BEFORE TRUNCATE ON application_stage_events
    FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation();

DROP FUNCTION IF EXISTS rm_forbid_event_mutation();