-- 033_wayleave_coverage_rename.sql
-- Wayleave module realignment, migration 3 of 6
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 3)
--
-- The eight coverage, journal and access tables: DROPPED and recreated from explicit
-- DDL under the wayleave noun, with wayleave_id as the foreign key into the table 032
-- built. Requires 032.
--
-- WHY DROP RATHER THAN RENAME. An earlier draft renamed these in place to preserve
-- their rows. That was incoherent: 032 rebuilt wayleave.wayleaves EMPTY, so every row
-- here is orphaned and has to go regardless — a rename preserved nothing while leaving
-- behind constraint, index and sequence names still saying "agreement", which would
-- then have needed a catalog sweep to fix. Recreating gives correct names for free and
-- explicit DDL that can be read.
--
-- The principle across 031-034: CONFIGURATION IS PRESERVED, RECORDS ARE REBUILT.
-- Statuses are copied (031) and field meta is kept (034) because both are admin's
-- work. The entity table, these coverage tables and the EAV values are all test
-- records, so all three are rebuilt. Test-only data confirmed by Dave, 2026-08-10.
--
-- agreement_polygon_uprns is worth a note while it is being recreated: despite the
-- name it has NO polygon_id, and none is added here. It links to the agreement, not to
-- the polygon that resolved the UPRN, so "which polygon covers this premise?" is not
-- answerable from it. Left as found — that is a data-model question for the
-- coverage-table item in docs/improvement-opportunities.md, not something to decide
-- inside a rebuild.
--
-- COLUMN DEFINITIONS COME FROM THE BASELINE DUMP, which is stale (it predates
-- migrations 006-030). No migration in that range touched these eight tables, so it
-- should be accurate — but "should be" is not good enough when the alternative is
-- silently dropping a column. Step 1 therefore FAILS if any table carries a column
-- this migration does not know about, rather than discarding it.

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

DO $$
DECLARE
    t text;
BEGIN
    IF to_regclass('wayleave.wayleaves') IS NULL THEN
        RAISE EXCEPTION 'wayleave.wayleaves is missing — apply 032 first';
    END IF;

    FOREACH t IN ARRAY ARRAY[
        'agreement_polygons', 'agreement_polygon_uprns', 'agreement_uprns',
        'agreement_stocklists', 'agreement_landregistry', 'agreement_releases',
        'agreement_journal', 'agreement_access_log'
    ] LOOP
        IF to_regclass('wayleave.' || t) IS NULL THEN
            RAISE EXCEPTION 'wayleave.% is missing — 033 has already run, or the schema is not what this migration expects', t;
        END IF;
    END LOOP;
END $$;

-- ── 1. Refuse to drop a column this migration does not know about ───────────

DO $$
DECLARE
    v_expected CONSTANT text[] := ARRAY[
        'agreement_polygons:id,agreement_id,geom,label,added_user,added_datetime,modified_user,modified_datetime,is_deleted',
        'agreement_polygon_uprns:id,agreement_id,uprn,is_assigned,is_approved,actioned_user_id,action_datetime,approved_user_id,approved_datetime',
        'agreement_uprns:id,agreement_id,uprn,added_user,added_datetime,is_deleted',
        'agreement_stocklists:id,agreement_id,stocklist_id,added_user,added_datetime,is_deleted',
        'agreement_landregistry:id,agreement_id,title_number,tenure,added_user,added_datetime,is_deleted',
        'agreement_releases:id,agreement_id,uprn,release_date,sales_ref,pic_ref,notes,released_user,released_datetime,created_datetime,created_user,modified_datetime,modified_user,is_deleted',
        'agreement_journal:id,agreement_id,user_id,log_datetime,log_text,is_system',
        'agreement_access_log:id,agreement_id,user_id,access_type,access_datetime'
    ];
    e        text;
    v_tbl    text;
    v_known  text[];
    v_actual text[];
    v_extra  text[];
BEGIN
    FOREACH e IN ARRAY v_expected LOOP
        v_tbl   := split_part(e, ':', 1);
        v_known := string_to_array(split_part(e, ':', 2), ',');

        SELECT array_agg(column_name::text ORDER BY ordinal_position) INTO v_actual
          FROM information_schema.columns
         WHERE table_schema = 'wayleave' AND table_name = v_tbl;

        SELECT array_agg(c) INTO v_extra
          FROM unnest(v_actual) AS c
         WHERE c <> ALL (v_known);

        IF v_extra IS NOT NULL THEN
            RAISE EXCEPTION 'wayleave.% has column(s) this migration does not recreate: %. Add them to 033 before running — dropping the table would discard them.',
                v_tbl, array_to_string(v_extra, ', ');
        END IF;
    END LOOP;

    RAISE NOTICE '033: all eight tables match the expected column sets';
END $$;

-- ── 2. Report what is discarded, then drop ──────────────────────────────────
-- Counts first so what went is on the record, not inferred later from an empty table.

DO $$
DECLARE
    r       record;
    v_total bigint := 0;
    v_n     bigint;
BEGIN
    FOR r IN SELECT unnest(ARRAY[
        'agreement_polygons', 'agreement_polygon_uprns', 'agreement_uprns',
        'agreement_stocklists', 'agreement_landregistry', 'agreement_releases',
        'agreement_journal', 'agreement_access_log'
    ]) AS t
    LOOP
        EXECUTE format('SELECT count(*) FROM wayleave.%I', r.t) INTO v_n;
        RAISE NOTICE '033: wayleave.% — % row(s) discarded', r.t, v_n;
        v_total := v_total + v_n;
    END LOOP;
    RAISE NOTICE '033: % coverage/journal/access row(s) discarded in total (orphaned by the 032 rebuild)', v_total;
END $$;

DROP TABLE wayleave.agreement_polygon_uprns;
DROP TABLE wayleave.agreement_polygons;
DROP TABLE wayleave.agreement_uprns;
DROP TABLE wayleave.agreement_stocklists;
DROP TABLE wayleave.agreement_landregistry;
DROP TABLE wayleave.agreement_releases;
DROP TABLE wayleave.agreement_journal;
DROP TABLE wayleave.agreement_access_log;

-- ── 3. Recreate under the wayleave noun ─────────────────────────────────────
-- Each with its own sequence, its own constraints named for the table, and
-- ON UPDATE RESTRICT on the parent link — matching how every projects and
-- opportunity child table is constrained. No ON DELETE: removing a wayleave that
-- still has coverage should be blocked, not silently cascade a legal document's
-- premise list away.

CREATE TABLE wayleave.wayleave_polygons (
    id                bigint       GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id       integer      NOT NULL,
    geom              public.geometry(Polygon, 27700) NOT NULL,
    label             varchar(250),
    added_user        integer      NOT NULL,
    added_datetime    timestamp without time zone NOT NULL DEFAULT now(),
    modified_user     integer,
    modified_datetime timestamp without time zone,
    is_deleted        boolean      NOT NULL DEFAULT false,
    CONSTRAINT wayleave_polygons_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_polygons_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_polygons_added_user_fk FOREIGN KEY (added_user)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_polygons_wayleave_idx ON wayleave.wayleave_polygons (wayleave_id);
CREATE INDEX wayleave_polygons_geom_idx     ON wayleave.wayleave_polygons USING gist (geom);

CREATE TABLE wayleave.wayleave_polygon_uprns (
    id                bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id       integer NOT NULL,
    uprn              bigint  NOT NULL,
    is_assigned       boolean NOT NULL DEFAULT true,
    is_approved       boolean NOT NULL DEFAULT false,
    actioned_user_id  integer NOT NULL,
    action_datetime   timestamp without time zone NOT NULL DEFAULT now(),
    approved_user_id  integer,
    approved_datetime timestamp without time zone,
    CONSTRAINT wayleave_polygon_uprns_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_polygon_uprns_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT
);
CREATE INDEX wayleave_polygon_uprns_wayleave_idx ON wayleave.wayleave_polygon_uprns (wayleave_id);
CREATE INDEX wayleave_polygon_uprns_uprn_idx     ON wayleave.wayleave_polygon_uprns (uprn);

COMMENT ON TABLE wayleave.wayleave_polygon_uprns IS
    'Polygon-resolved coverage UPRNs plus their approval state. Despite the name it carries no polygon_id — it links to the wayleave, not to the polygon that resolved the UPRN.';

CREATE TABLE wayleave.wayleave_uprns (
    id             bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id    integer NOT NULL,
    uprn           bigint  NOT NULL,
    added_user     integer NOT NULL,
    added_datetime timestamp without time zone NOT NULL DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    CONSTRAINT wayleave_uprns_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_uprns_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_uprns_added_user_fk FOREIGN KEY (added_user)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_uprns_wayleave_idx ON wayleave.wayleave_uprns (wayleave_id);
CREATE INDEX wayleave_uprns_uprn_idx     ON wayleave.wayleave_uprns (uprn);

CREATE TABLE wayleave.wayleave_stocklists (
    id             bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id    integer NOT NULL,
    stocklist_id   integer NOT NULL,
    added_user     integer NOT NULL,
    added_datetime timestamp without time zone NOT NULL DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    CONSTRAINT wayleave_stocklists_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_stocklists_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_stocklists_added_user_fk FOREIGN KEY (added_user)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_stocklists_wayleave_idx ON wayleave.wayleave_stocklists (wayleave_id);

CREATE TABLE wayleave.wayleave_landregistry (
    id             bigint      GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id    integer     NOT NULL,
    title_number   varchar(50) NOT NULL,
    tenure         varchar(20),
    added_user     integer     NOT NULL,
    added_datetime timestamp without time zone NOT NULL DEFAULT now(),
    is_deleted     boolean     NOT NULL DEFAULT false,
    CONSTRAINT wayleave_landregistry_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_landregistry_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_landregistry_added_user_fk FOREIGN KEY (added_user)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_landregistry_wayleave_idx ON wayleave.wayleave_landregistry (wayleave_id);
CREATE INDEX wayleave_landregistry_title_idx    ON wayleave.wayleave_landregistry (title_number);

CREATE TABLE wayleave.wayleave_releases (
    id                bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id       integer NOT NULL,
    uprn              bigint  NOT NULL,
    release_date      date,
    sales_ref         varchar(100),
    pic_ref           varchar(100),
    notes             text,
    released_user     integer,
    released_datetime timestamp without time zone,
    created_datetime  timestamp without time zone NOT NULL DEFAULT now(),
    created_user      integer NOT NULL,
    modified_datetime timestamp without time zone,
    modified_user     integer,
    is_deleted        boolean NOT NULL DEFAULT false,
    CONSTRAINT wayleave_releases_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_releases_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_releases_created_user_fk FOREIGN KEY (created_user)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_releases_wayleave_idx ON wayleave.wayleave_releases (wayleave_id);
CREATE INDEX wayleave_releases_uprn_idx     ON wayleave.wayleave_releases (uprn);

CREATE TABLE wayleave.wayleave_journal (
    id           bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id  integer NOT NULL,
    user_id      integer NOT NULL,
    log_datetime timestamp without time zone NOT NULL DEFAULT now(),
    log_text     text    NOT NULL,
    is_system    boolean NOT NULL DEFAULT false,
    CONSTRAINT wayleave_journal_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_journal_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_journal_user_fk FOREIGN KEY (user_id)
        REFERENCES users.users(id)
);
CREATE INDEX wayleave_journal_wayleave_idx ON wayleave.wayleave_journal (wayleave_id);

CREATE TABLE wayleave.wayleave_access_log (
    id              bigint  GENERATED BY DEFAULT AS IDENTITY,
    wayleave_id     integer NOT NULL,
    user_id         integer,
    access_type     varchar(50),
    access_datetime timestamp without time zone DEFAULT now(),
    CONSTRAINT wayleave_access_log_pk PRIMARY KEY (id),
    CONSTRAINT wayleave_access_log_wayleave_fk FOREIGN KEY (wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT
);
CREATE INDEX wayleave_access_log_wayleave_idx ON wayleave.wayleave_access_log (wayleave_id);
CREATE INDEX wayleave_access_log_datetime_idx ON wayleave.wayleave_access_log (access_datetime);

-- ── 4. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_fks   integer;
    v_tbls  integer;
    v_stale text;
BEGIN
    SELECT count(*) INTO v_fks
      FROM pg_constraint
     WHERE contype = 'f' AND confrelid = 'wayleave.wayleaves'::regclass;

    -- Eight child tables, plus wayleaves' own self-referencing parent link.
    IF v_fks <> 9 THEN
        RAISE EXCEPTION '033: % foreign key(s) reference wayleave.wayleaves, expected 9', v_fks;
    END IF;

    SELECT count(*) INTO v_tbls
      FROM information_schema.columns
     WHERE table_schema = 'wayleave'
       AND column_name  = 'wayleave_id'
       AND table_name IN ('wayleave_polygons', 'wayleave_polygon_uprns', 'wayleave_uprns',
                          'wayleave_stocklists', 'wayleave_landregistry', 'wayleave_releases',
                          'wayleave_journal', 'wayleave_access_log');

    IF v_tbls <> 8 THEN
        RAISE EXCEPTION '033: % of 8 recreated tables have a wayleave_id column', v_tbls;
    END IF;

    -- SCOPED TO THIS MIGRATION'S TABLES, deliberately. The ten EAV value and history
    -- tables still carry agreement_id at this point and that is correct — 034 rebuilds
    -- them. An earlier draft checked the whole schema here and failed on exactly that,
    -- costing a run and reporting nothing real. The schema-wide sweep belongs at the
    -- end of 034, once every table is at its final name.
    SELECT string_agg(table_name, ', ') INTO v_stale
      FROM information_schema.tables
     WHERE table_schema = 'wayleave' AND table_name LIKE 'agreement%'
       AND table_name NOT LIKE 'agreement_field%';

    IF v_stale IS NOT NULL THEN
        RAISE EXCEPTION '033: agreement-named table(s) remain outside the field meta: %', v_stale;
    END IF;

    RAISE NOTICE '033: eight tables rebuilt under the wayleave noun and linked to wayleave.wayleaves';
END $$;
