-- +goose Up
-- Repairs every composite foreign key in the schema that declared a bare ON DELETE SET NULL.
--
-- A bare SET NULL on a multi column foreign key nulls EVERY column in the key, not just the one that
-- stopped being true. Because every tenant scoped key in this schema leads with organization_id, and
-- organization_id is NOT NULL on every table, each of these constraints attempts to write NULL into a
-- NOT NULL column and aborts. The delete does not partially apply, it fails outright.
--
-- The observable effect is that the referenced rows can never be deleted at all. Verified directly
-- against the live schema before writing this migration:
--
--   DELETE FROM clients WHERE id = ...
--   ERROR: null value in column "organization_id" of relation "requisitions" violates not-null constraint
--   CONTEXT: UPDATE ONLY "public"."requisitions" SET "organization_id" = NULL, "client_id" = NULL ...
--
-- Twelve of the twenty four reference users, so a recruiter who has ever created a client, an interview,
-- an offer, a placement, a task, an evaluation or a recommendation cannot be removed from the system.
-- That is the entire offboarding path. Others block deleting an opportunity, a slate, an offer, and a
-- candidate through ai_memory, which is the privacy erasure path.
--
-- This is the same defect class as migration 0050: a constraint written to protect history that instead
-- made a legitimate lifecycle operation impossible. The fix restores the DDL's original intent rather
-- than changing it. ON DELETE SET NULL (column) confines the write to the column that actually became
-- invalid, leaving organization_id and any other key column untouched. Supported since Postgres 15; this
-- deployment runs 16.14.
--
-- No data is read or written. Each statement replaces a constraint definition, and the referential rule
-- is unchanged for every row that already satisfies it.

-- Keys referencing users. Twelve constraints, the offboarding path.
ALTER TABLE aedt_candidate_notices DROP CONSTRAINT fk_notice_disclosed_by;
ALTER TABLE aedt_candidate_notices ADD CONSTRAINT fk_notice_disclosed_by
    FOREIGN KEY (organization_id, disclosed_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (disclosed_by);

ALTER TABLE application_stage_events DROP CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey;
ALTER TABLE application_stage_events ADD CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey
    FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (actor_user_id);

ALTER TABLE applications DROP CONSTRAINT applications_organization_id_created_by_fkey;
ALTER TABLE applications ADD CONSTRAINT applications_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE applications DROP CONSTRAINT applications_organization_id_owner_user_id_fkey;
ALTER TABLE applications ADD CONSTRAINT applications_organization_id_owner_user_id_fkey
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (owner_user_id);

ALTER TABLE candidate_evaluations DROP CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey;
ALTER TABLE candidate_evaluations ADD CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey
    FOREIGN KEY (organization_id, evaluated_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (evaluated_by);

ALTER TABLE clients DROP CONSTRAINT clients_organization_id_created_by_fkey;
ALTER TABLE clients ADD CONSTRAINT clients_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE clients DROP CONSTRAINT clients_owner_fk;
ALTER TABLE clients ADD CONSTRAINT clients_owner_fk
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (owner_user_id);

ALTER TABLE interviews DROP CONSTRAINT interviews_organization_id_created_by_fkey;
ALTER TABLE interviews ADD CONSTRAINT interviews_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE offers DROP CONSTRAINT offers_organization_id_created_by_fkey;
ALTER TABLE offers ADD CONSTRAINT offers_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE placements DROP CONSTRAINT placements_organization_id_created_by_fkey;
ALTER TABLE placements ADD CONSTRAINT placements_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE recommendation_runs DROP CONSTRAINT recommendation_runs_organization_id_created_by_fkey;
ALTER TABLE recommendation_runs ADD CONSTRAINT recommendation_runs_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE staffing_recommendations DROP CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey;
ALTER TABLE staffing_recommendations ADD CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey
    FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (decided_by);

ALTER TABLE tasks DROP CONSTRAINT tasks_organization_id_created_by_fkey;
ALTER TABLE tasks ADD CONSTRAINT tasks_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_created_by_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (created_by);

ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_owner_user_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_owner_user_id_fkey
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (owner_user_id);

-- The privacy erasure path. A candidate cannot be deleted while ai_memory references them.
ALTER TABLE ai_memory DROP CONSTRAINT fk_ai_memory_candidate;
ALTER TABLE ai_memory ADD CONSTRAINT fk_ai_memory_candidate
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id)
    ON DELETE SET NULL (candidate_id);

-- Capture side keys. These block deleting an opportunity, a requirement or a slate.
ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id)
    ON DELETE SET NULL (opportunity_id);

ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id)
    ON DELETE SET NULL (requirement_id);

ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_slate_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_slate_id_fkey
    FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id)
    ON DELETE SET NULL (slate_id);

ALTER TABLE requirements DROP CONSTRAINT requirements_organization_id_opportunity_id_fkey;
ALTER TABLE requirements ADD CONSTRAINT requirements_organization_id_opportunity_id_fkey
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id)
    ON DELETE SET NULL (opportunity_id);

ALTER TABLE placements DROP CONSTRAINT placements_organization_id_offer_id_fkey;
ALTER TABLE placements ADD CONSTRAINT placements_organization_id_offer_id_fkey
    FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id)
    ON DELETE SET NULL (offer_id);

-- Recruiting side keys on requisitions. The client key is the one whose failure was reproduced above.
ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_client_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_client_id_fkey
    FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id)
    ON DELETE SET NULL (client_id);

ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_requirement_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_requirement_id_fkey
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id)
    ON DELETE SET NULL (requirement_id);

-- +goose Down
-- Restores the original definitions. This reinstates the defect, which is what a down migration is for:
-- it returns the schema to the state the previous migration left it in, not to a better one.
ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_requirement_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_requirement_id_fkey
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL;
ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_client_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_client_id_fkey
    FOREIGN KEY (organization_id, client_id) REFERENCES clients (organization_id, id) ON DELETE SET NULL;
ALTER TABLE placements DROP CONSTRAINT placements_organization_id_offer_id_fkey;
ALTER TABLE placements ADD CONSTRAINT placements_organization_id_offer_id_fkey
    FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id) ON DELETE SET NULL;
ALTER TABLE requirements DROP CONSTRAINT requirements_organization_id_opportunity_id_fkey;
ALTER TABLE requirements ADD CONSTRAINT requirements_organization_id_opportunity_id_fkey
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL;
ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_slate_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_slate_id_fkey
    FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) ON DELETE SET NULL;
ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_requirement_id_fkey
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL;
ALTER TABLE bid_outcomes DROP CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey;
ALTER TABLE bid_outcomes ADD CONSTRAINT bid_outcomes_organization_id_opportunity_id_fkey
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL;
ALTER TABLE ai_memory DROP CONSTRAINT fk_ai_memory_candidate;
ALTER TABLE ai_memory ADD CONSTRAINT fk_ai_memory_candidate
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE SET NULL;
ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_owner_user_id_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_owner_user_id_fkey
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE requisitions DROP CONSTRAINT requisitions_organization_id_created_by_fkey;
ALTER TABLE requisitions ADD CONSTRAINT requisitions_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE tasks DROP CONSTRAINT tasks_organization_id_created_by_fkey;
ALTER TABLE tasks ADD CONSTRAINT tasks_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE staffing_recommendations DROP CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey;
ALTER TABLE staffing_recommendations ADD CONSTRAINT staffing_recommendations_organization_id_decided_by_fkey
    FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE recommendation_runs DROP CONSTRAINT recommendation_runs_organization_id_created_by_fkey;
ALTER TABLE recommendation_runs ADD CONSTRAINT recommendation_runs_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE placements DROP CONSTRAINT placements_organization_id_created_by_fkey;
ALTER TABLE placements ADD CONSTRAINT placements_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE offers DROP CONSTRAINT offers_organization_id_created_by_fkey;
ALTER TABLE offers ADD CONSTRAINT offers_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE interviews DROP CONSTRAINT interviews_organization_id_created_by_fkey;
ALTER TABLE interviews ADD CONSTRAINT interviews_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE clients DROP CONSTRAINT clients_owner_fk;
ALTER TABLE clients ADD CONSTRAINT clients_owner_fk
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE clients DROP CONSTRAINT clients_organization_id_created_by_fkey;
ALTER TABLE clients ADD CONSTRAINT clients_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE candidate_evaluations DROP CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey;
ALTER TABLE candidate_evaluations ADD CONSTRAINT candidate_evaluations_organization_id_evaluated_by_fkey
    FOREIGN KEY (organization_id, evaluated_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE applications DROP CONSTRAINT applications_organization_id_owner_user_id_fkey;
ALTER TABLE applications ADD CONSTRAINT applications_organization_id_owner_user_id_fkey
    FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE applications DROP CONSTRAINT applications_organization_id_created_by_fkey;
ALTER TABLE applications ADD CONSTRAINT applications_organization_id_created_by_fkey
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE application_stage_events DROP CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey;
ALTER TABLE application_stage_events ADD CONSTRAINT application_stage_events_organization_id_actor_user_id_fkey
    FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL;
ALTER TABLE aedt_candidate_notices DROP CONSTRAINT fk_notice_disclosed_by;
ALTER TABLE aedt_candidate_notices ADD CONSTRAINT fk_notice_disclosed_by
    FOREIGN KEY (organization_id, disclosed_by) REFERENCES users (organization_id, id) ON DELETE SET NULL;