-- 032_wayleave_entity_rebuild.sql
-- Wayleave module realignment, migration 2 of 6
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 2)
--
-- Replaces wayleave.agreements / agreements_history with wayleave.wayleaves /
-- wayleaves_history, built from explicit DDL to the shape every other module has.
-- Requires 031 (module key rename + wayleave_status). Coverage tables follow in 033.
--
-- REBUILT, NOT MIGRATED. The module has never been used in production and its data
-- is test-only (Dave, 2026-08-10), so there is no data to carry across and no reason
-- to arrive at the canonical shape through a chain of column renames. Nothing here
-- preserves rows in these two tables and nothing is expected to.
--
-- THE MODULE IS DOWN FROM THE MOMENT THIS APPLIES until the rewritten endpoints
-- deploy. Every wayleave endpoint names tables that no longer answer to those names.
-- Deliberate — a missed reference fails loudly rather than reading stale data — but
-- it means 031-036 and the endpoint deploy want to happen in the same sitting.
--
-- WHAT CHANGES, AND WHY:
--
--   agreements            → wayleaves          entity noun aligned to the schema name,
--   agreement_id          → wayleave_id        matching projects.projects(project_id).
--   parent_agreement_id   → parent_wayleave_id Decision D7 — the rename is carried
--   agreement_name        → wayleave_name      through every table, view, FK column and
--   agreement_reference   → wayleave_reference meta row, because a half-rename reads
--   agreement_type        → wayleave_type      worse than either consistent option.
--   agreement_status_id   → wayleave_status_id
--   bd_manager            → wayleave_manager
--
-- bd_manager becomes wayleave_manager because the canonical pattern is
-- <entity>_manager and dashboardMineSql keys Mine scoping on it. The DISPLAY LABEL
-- stays "BD Manager" in the meta row (034), where an admin can change it: schema
-- follows the pattern, UI keeps the domain word.
--
-- wayleave_team, account_id, ecd_date and signed_date keep their names — already
-- correct or carrying no entity prefix, exactly as projects leaves its own.
--
-- COLUMNS GAINED: cover_image_url (D5), wired to image_upload.php in Phase 7.
-- COLUMN KEPT: is_deleted (D2). No other entity table has one. It stays because soft
-- delete preserves the audit trail, and the alignment is that every consumer filters
-- it — including a per-module predicate in dashboard_load.php, which is the one
-- module-walking consumer that would otherwise count deleted records.
--
-- geom IS CREATED AND DELIBERATELY UNUSED (D3, Dave 2026-08-10). Reserved for a
-- future summary geometry, so overview maps can show one shape per wayleave rather
-- than every member polygon. It does NOT displace wayleave_polygons as the canonical
-- coverage-geometry store (settled 2026-07-18: each polygon carries its own
-- attributes, which a single MultiPolygon cannot). Typed MultiPolygon/27700 to match
-- what a union of those polygons produces, so that work needs no type change later.
--
-- HISTORY GRAIN CHANGES. The old agreements_history was per-field
-- (history_field / history_old_value / history_new_value) — a wayleave-only shape
-- that migrations 017/018 could not be applied to and buildAuditLogQuery() cannot
-- read. The replacement is the full-row snapshot every other module uses. Rows are
-- not converted: the information for unchanged columns was never recorded.
--
-- VIEWS ARE DROPPED HERE AND RECREATED IN 036. All six depend on tables 032-034
-- rename or rebuild. Dropping them explicitly rather than relying on CASCADE keeps
-- the set auditable — if a seventh view exists on dev, the guard below catches it
-- rather than CASCADE silently taking it.

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

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

    IF to_regclass('wayleave.agreements') IS NULL THEN
        RAISE EXCEPTION 'wayleave.agreements is missing — 032 has already run, or the schema is not what this migration expects';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM wayleave.wayleave_status
                    WHERE wayleave_status_desc = 'Created') THEN
        RAISE EXCEPTION 'wayleave_status has no Created row — 031 did not seed it';
    END IF;

    -- Any view in the schema this migration does not know about. 036 recreates six
    -- by name; a seventh would be dropped here and never come back.
    SELECT string_agg(viewname, ', ') INTO v_extra
      FROM pg_views
     WHERE schemaname = 'wayleave'
       AND viewname NOT IN ('vw_agreement_polygons', 'vw_file_uploads',
                            'vw_uprns_from_boundary', 'vw_uprns_from_direct',
                            'vw_uprns_from_stocklists', 'vw_uprns_from_titles');

    IF v_extra IS NOT NULL THEN
        RAISE EXCEPTION 'unexpected view(s) in the wayleave schema: % — add them to 036 before running this', v_extra;
    END IF;
END $$;

-- ── 1. Drop the views (all recreated in 036) ────────────────────────────────

DROP VIEW IF EXISTS wayleave.vw_uprns_from_titles;
DROP VIEW IF EXISTS wayleave.vw_uprns_from_stocklists;
DROP VIEW IF EXISTS wayleave.vw_uprns_from_direct;
DROP VIEW IF EXISTS wayleave.vw_uprns_from_boundary;
DROP VIEW IF EXISTS wayleave.vw_file_uploads;
DROP VIEW IF EXISTS wayleave.vw_agreement_polygons;

-- ── 2. Drop every foreign key pointing at wayleave.agreements ───────────────
-- Catalog-driven rather than by name: the baseline dump is stale (it predates
-- migrations 009, 011, 018, 021 and 022) so constraint names cannot be trusted from
-- it. 033 re-adds these against the new table once the child columns are renamed.

DO $$
DECLARE
    r record;
    v_count integer := 0;
BEGIN
    FOR r IN
        SELECT conrelid::regclass AS child, conname
          FROM pg_constraint
         WHERE contype = 'f'
           AND confrelid = 'wayleave.agreements'::regclass
    LOOP
        EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I', r.child, r.conname);
        RAISE NOTICE 'dropped FK %.% -> wayleave.agreements', r.child, r.conname;
        v_count := v_count + 1;
    END LOOP;
    RAISE NOTICE '032: dropped % foreign key(s) referencing wayleave.agreements', v_count;
END $$;

-- ── 3. Drop the old entity, history and status tables ───────────────────────
-- Order matters: agreements carries the FK into agreement_status, so it goes first.
-- agreement_status is dropped here rather than in 031 because the views above and
-- the agreements table both depend on it, and both are only gone by this point.
-- Its rows were copied into wayleave.wayleave_status by 031.

DROP TABLE wayleave.agreements_history;
DROP TABLE wayleave.agreements;
DROP TABLE wayleave.agreement_status;

-- ── 4. wayleave.wayleaves ───────────────────────────────────────────────────
-- Explicit DDL with its own sequence and its own starting value. The opportunity
-- and projects tables are the reference for SHAPE, never the source of the objects.

CREATE SEQUENCE wayleave.wayleaves_wayleave_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleaves (
    wayleave_id         integer      NOT NULL DEFAULT nextval('wayleave.wayleaves_wayleave_id_seq'::regclass),
    parent_wayleave_id  integer,
    wayleave_name       varchar(250) NOT NULL,
    wayleave_status_id  integer,
    wayleave_manager    integer,
    geom                public.geometry(MultiPolygon, 27700),
    created_datetime    timestamp without time zone NOT NULL DEFAULT now(),
    created_user        integer      NOT NULL,
    modified_datetime   timestamp without time zone NOT NULL DEFAULT now(),
    modified_user       integer      NOT NULL,
    company_id          integer,
    account_id          integer,
    cover_image_url     varchar(100),
    is_deleted          boolean      NOT NULL DEFAULT false,
    -- Wayleave-specific statics. Seeded as field_type='static' meta rows by 015 and
    -- re-pointed by 034; storage stays code-defined here and in wayleave_save.php's
    -- whitelist, exactly as projects does it.
    wayleave_reference  varchar(100),
    wayleave_type       varchar(50),
    wayleave_team       varchar(100),
    ecd_date            date,
    signed_date         date,
    CONSTRAINT wayleaves_pk PRIMARY KEY (wayleave_id),
    CONSTRAINT wayleaves_parent_wayleave_id_fk FOREIGN KEY (parent_wayleave_id)
        REFERENCES wayleave.wayleaves(wayleave_id),
    CONSTRAINT wayleaves_status_fk FOREIGN KEY (wayleave_status_id)
        REFERENCES wayleave.wayleave_status(wayleave_status_id),
    CONSTRAINT wayleaves_manager_fk FOREIGN KEY (wayleave_manager)
        REFERENCES users.users(id),
    CONSTRAINT wayleaves_created_user_fk FOREIGN KEY (created_user)
        REFERENCES users.users(id),
    CONSTRAINT wayleaves_modified_user_fk FOREIGN KEY (modified_user)
        REFERENCES users.users(id)
);

ALTER SEQUENCE wayleave.wayleaves_wayleave_id_seq
    OWNED BY wayleave.wayleaves.wayleave_id;

CREATE INDEX wayleaves_geom_idx        ON wayleave.wayleaves USING gist (geom);
CREATE INDEX wayleaves_status_idx      ON wayleave.wayleaves (wayleave_status_id);
CREATE INDEX wayleaves_company_idx     ON wayleave.wayleaves (company_id);
CREATE INDEX wayleaves_account_idx     ON wayleave.wayleaves (account_id);
CREATE INDEX wayleaves_is_deleted_idx  ON wayleave.wayleaves (is_deleted);

COMMENT ON COLUMN wayleave.wayleaves.geom IS
    'Unused — no endpoint writes it. Reserved for a future summary geometry for overview maps (Dave, 2026-08-10). wayleave.wayleave_polygons remains the canonical coverage-geometry store; see docs/2026-08-10-wayleave-module-realignment.md.';

COMMENT ON COLUMN wayleave.wayleaves.is_deleted IS
    'Soft delete. Unique to this module among the entity tables — every consumer must filter it, including dashboard_load.php.';

COMMENT ON COLUMN wayleave.wayleaves.wayleave_manager IS
    'Was bd_manager. Renamed to the <entity>_manager pattern that dashboardMineSql keys on; the display label stays "BD Manager" in the field meta.';

-- The Created default, read from the catalog by statusDefaultId() to make that status
-- undeletable and undeactivatable. Set dynamically from the seeded row rather than
-- hardcoded: the id is allocated by 031 and differs between environments.
--
-- It must land as a PLAIN INTEGER LITERAL — statusDefaultId() matches /^\d+$/ and
-- treats anything else (an expression, a sub-select) as "no default", which would
-- silently lift the protection.
DO $$
DECLARE
    v_created integer;
BEGIN
    SELECT wayleave_status_id INTO v_created
      FROM wayleave.wayleave_status WHERE wayleave_status_desc = 'Created';

    EXECUTE format(
        'ALTER TABLE wayleave.wayleaves ALTER COLUMN wayleave_status_id SET DEFAULT %s',
        v_created
    );
    RAISE NOTICE '032: wayleave_status_id default set to % (Created)', v_created;
END $$;

COMMENT ON COLUMN wayleave.wayleaves.wayleave_status_id IS
    'Defaults to the Created status. statusDefaultId() reads this default to block deleting or deactivating that status — drop the default and the protection lifts.';

-- ── 5. wayleave.wayleaves_history ───────────────────────────────────────────
-- Full-row snapshot grain, matching projects.projects_history and
-- opportunity.opportunities_history. Written only by the trigger added in 035.
--
-- No foreign keys to users.users on the actor columns and none to wayleaves on
-- wayleave_id: history must survive the deletion of what it describes, and both
-- projects and opportunity leave it unconstrained for the same reason.

CREATE SEQUENCE wayleave.wayleaves_history_history_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleaves_history (
    history_id          bigint       NOT NULL DEFAULT nextval('wayleave.wayleaves_history_history_id_seq'::regclass),
    wayleave_id         integer      NOT NULL,
    parent_wayleave_id  integer,
    wayleave_name       varchar(250),
    wayleave_status_id  integer,
    wayleave_manager    integer,
    geom                public.geometry(MultiPolygon, 27700),
    created_datetime    timestamp without time zone,
    created_user        integer,
    modified_datetime   timestamp without time zone,
    modified_user       integer,
    company_id          integer,
    account_id          integer,
    cover_image_url     varchar(100),
    is_deleted          boolean,
    wayleave_reference  varchar(100),
    wayleave_type       varchar(50),
    wayleave_team       varchar(100),
    ecd_date            date,
    signed_date         date,
    history_action      varchar(10)  NOT NULL,
    history_datetime    timestamp without time zone NOT NULL DEFAULT now(),
    history_user        integer,
    CONSTRAINT wayleaves_history_pk PRIMARY KEY (history_id)
);

ALTER SEQUENCE wayleave.wayleaves_history_history_id_seq
    OWNED BY wayleave.wayleaves_history.history_id;

CREATE INDEX wayleaves_history_wayleave_id_idx ON wayleave.wayleaves_history (wayleave_id);
CREATE INDEX wayleaves_history_datetime_idx    ON wayleave.wayleaves_history (history_datetime);

-- ── 6. Modified-stamp trigger ───────────────────────────────────────────────
-- Mirrors projects.projects_update_modified_fields() and its opportunity twin:
-- stamps the modification time on every UPDATE whatever the write path. The user
-- half is commented out there too — modified_user is set by the endpoints, and the
-- history trigger in 035 falls back to it when the app.user_id GUC is absent.
--
-- 035's no-op suppression is written knowing this fires first, so an UPDATE that
-- touches only modified_datetime/modified_user writes no history row.

CREATE FUNCTION wayleave.wayleaves_update_modified_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.modified_datetime = now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_wayleaves_update_modified_fields
    BEFORE UPDATE ON wayleave.wayleaves
    FOR EACH ROW EXECUTE FUNCTION wayleave.wayleaves_update_modified_fields();

-- ── 7. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_cols integer;
BEGIN
    SELECT count(*) INTO v_cols
      FROM information_schema.columns
     WHERE table_schema = 'wayleave' AND table_name = 'wayleaves';

    IF v_cols <> 19 THEN
        RAISE EXCEPTION '032: wayleave.wayleaves has % columns, expected 19', v_cols;
    END IF;

    IF to_regclass('wayleave.agreements') IS NOT NULL
       OR to_regclass('wayleave.agreements_history') IS NOT NULL
       OR to_regclass('wayleave.agreement_status') IS NOT NULL THEN
        RAISE EXCEPTION '032: the old agreement tables are still present';
    END IF;

    RAISE NOTICE '032: wayleave.wayleaves and wayleaves_history created; coverage FKs re-added in 033';
END $$;
