-- 040_wayleave_coverage_unique_constraints.sql
-- Restores five UNIQUE constraints migration 033 dropped.
--
-- 033 replaced the eight coverage tables with explicit DDL and transcribed the
-- primary keys and foreign keys but not the UNIQUE constraints. Four endpoints upsert
-- against them with ON CONFLICT, which needs a matching unique index — without one
-- Postgres raises 42P10, "there is no unique or exclusion constraint matching the ON
-- CONFLICT specification". Drawing a polygon fails with exactly that.
--
-- This is the same fault as migration 029 in the opportunity work, and 033's own
-- header warns about it. The lesson that keeps not landing: when recreating a table,
-- transcribe the CONSTRAINT list from the catalog, not the CREATE TABLE block — a
-- dump lists columns there and constraints somewhere else entirely.
--
--   wayleave_polygon_uprns  (wayleave_id, uprn)          wayleave_coverage_polygon_save.php:98
--   wayleave_uprns          (wayleave_id, uprn)          wayleave_coverage_uprn_save.php:34
--   wayleave_stocklists     (wayleave_id, stocklist_id)  wayleave_coverage_stocklist_save.php:33
--   wayleave_landregistry   (wayleave_id, title_number)  wayleave_landreg_save.php:32
--   wayleave_releases       (wayleave_id, uprn)          no upsert today, but the original
--                                                        carried it and it stops duplicates
--
-- NOT RESTORED, deliberately. Three other UNIQUE constraints existed on the old
-- tables and are not being recreated, because the modules this one is being aligned
-- to do not have them: agreement_status_description_key (projects/accounts/stocklists
-- status tables have a PK only), and the section / sub-category name constraints
-- (opportunity's 026 DDL gives those tables a PK only). agreement_fields'
-- field_form_id uniqueness IS preserved — 034 created it as a unique INDEX instead,
-- which serves the same purpose.

-- ── 0. Guards ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    t text;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'wayleave_polygon_uprns', 'wayleave_uprns', 'wayleave_stocklists',
        'wayleave_landregistry', 'wayleave_releases'
    ] LOOP
        IF to_regclass('wayleave.' || t) IS NULL THEN
            RAISE EXCEPTION 'wayleave.% is missing — apply 033 first', t;
        END IF;
    END LOOP;
END $$;

-- ── 1. Report any duplicates that would block the constraints ───────────────
-- The tables were truncated by 033, so anything here is test data created since.
-- Reported rather than deleted: removing rows is not this migration's call.

DO $$
DECLARE
    r       record;
    v_total bigint := 0;
BEGIN
    FOR r IN
        SELECT 'wayleave_polygon_uprns' AS t, 'wayleave_id, uprn' AS cols
        UNION ALL SELECT 'wayleave_uprns',        'wayleave_id, uprn'
        UNION ALL SELECT 'wayleave_stocklists',   'wayleave_id, stocklist_id'
        UNION ALL SELECT 'wayleave_landregistry', 'wayleave_id, title_number'
        UNION ALL SELECT 'wayleave_releases',     'wayleave_id, uprn'
    LOOP
        EXECUTE format(
            'SELECT count(*) FROM (SELECT %s FROM wayleave.%I GROUP BY %s HAVING count(*) > 1) d',
            r.cols, r.t, r.cols
        ) INTO v_total;

        IF v_total > 0 THEN
            RAISE EXCEPTION 'wayleave.% has % duplicate (%) group(s) — resolve them before this can run',
                r.t, v_total, r.cols;
        END IF;
    END LOOP;

    RAISE NOTICE '040: no duplicates; constraints can be added';
END $$;

-- ── 2. Add the constraints ──────────────────────────────────────────────────
-- Named for their tables, following the convention 033 used for the rest.

ALTER TABLE wayleave.wayleave_polygon_uprns
    ADD CONSTRAINT wayleave_polygon_uprns_wayleave_id_uprn_key UNIQUE (wayleave_id, uprn);

ALTER TABLE wayleave.wayleave_uprns
    ADD CONSTRAINT wayleave_uprns_wayleave_id_uprn_key UNIQUE (wayleave_id, uprn);

ALTER TABLE wayleave.wayleave_stocklists
    ADD CONSTRAINT wayleave_stocklists_wayleave_id_stocklist_id_key UNIQUE (wayleave_id, stocklist_id);

ALTER TABLE wayleave.wayleave_landregistry
    ADD CONSTRAINT wayleave_landregistry_wayleave_id_title_number_key UNIQUE (wayleave_id, title_number);

ALTER TABLE wayleave.wayleave_releases
    ADD CONSTRAINT wayleave_releases_wayleave_id_uprn_key UNIQUE (wayleave_id, uprn);

-- ── 3. Verify ───────────────────────────────────────────────────────────────
-- Asserts the constraint exists AND covers the exact column pair each ON CONFLICT
-- names — a UNIQUE on the right table but the wrong columns still raises 42P10.

DO $$
DECLARE
    r       record;
    v_found integer;
BEGIN
    FOR r IN
        SELECT 'wayleave_polygon_uprns' AS t, ARRAY['wayleave_id','uprn']         AS cols
        UNION ALL SELECT 'wayleave_uprns',        ARRAY['wayleave_id','uprn']
        UNION ALL SELECT 'wayleave_stocklists',   ARRAY['wayleave_id','stocklist_id']
        UNION ALL SELECT 'wayleave_landregistry', ARRAY['wayleave_id','title_number']
        UNION ALL SELECT 'wayleave_releases',     ARRAY['wayleave_id','uprn']
    LOOP
        SELECT count(*) INTO v_found
          FROM pg_constraint c
         WHERE c.conrelid = ('wayleave.' || r.t)::regclass
           AND c.contype  = 'u'
           AND (SELECT array_agg(a.attname::text ORDER BY a.attname)
                  FROM unnest(c.conkey) k
                  JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k)
               = (SELECT array_agg(x ORDER BY x) FROM unnest(r.cols) x);

        IF v_found = 0 THEN
            RAISE EXCEPTION '040: wayleave.% has no UNIQUE on (%)', r.t, array_to_string(r.cols, ', ');
        END IF;
    END LOOP;

    RAISE NOTICE '040: five UNIQUE constraints restored; the coverage upserts can run';
END $$;
