-- 029_opportunity_link_unique.sql
-- Opportunity module alignment — corrective migration
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- Restores the UNIQUE (opportunity_id, project_id) constraint that
-- prospector.opportunity_project_link carried as opportunity_project_link_unique_1 and
-- that migration 025 failed to recreate.
--
-- WHY IT MATTERS. opportunity_manage.php's add path is an upsert:
--
--     INSERT INTO ... ON CONFLICT (project_id, opportunity_id) DO UPDATE SET ...
--
-- Without a matching unique constraint PostgreSQL rejects that statement outright —
-- "there is no unique or exclusion constraint matching the ON CONFLICT specification" —
-- so adding a project to an opportunity would fail every time. It is also what makes
-- re-adding a previously removed project reuse its row (flipping is_deleted back to
-- false) instead of inserting a duplicate, which is the behaviour the module relies on:
-- the link row is the record that a project was once in this opportunity.
--
-- HOW IT WAS MISSED. 025's DDL was transcribed from the baseline dump's CREATE TABLE
-- block, which lists columns only — constraints appear in a separate ALTER TABLE section
-- hundreds of lines later. The Phase 0 catalog query that would have caught it covered
-- the projects schema, not prospector. No data problem results: the constraint existed
-- on the source table, so no duplicate pair can have been copied.
--
-- Safe to apply with the module live; it takes a brief ACCESS EXCLUSIVE lock on a
-- 387-row table.

DO $$
DECLARE
    v_dupes bigint;
BEGIN
    SELECT count(*) INTO v_dupes
      FROM (SELECT opportunity_id, project_id
              FROM opportunity.opportunity_project_link
             GROUP BY opportunity_id, project_id
            HAVING count(*) > 1) d;

    IF v_dupes > 0 THEN
        RAISE EXCEPTION 'opportunity_project_link has % duplicated (opportunity_id, project_id) pair(s) — resolve before adding the constraint', v_dupes;
    END IF;
END $$;

ALTER TABLE opportunity.opportunity_project_link
    ADD CONSTRAINT opportunity_project_link_opportunity_id_project_id_key
    UNIQUE (opportunity_id, project_id);
