-- 049_wayleave_polygons_multipolygon.sql
-- Drop the geometry typmod from the wayleave geometry columns, and store valid on write.
--
-- 033 declared wayleave_polygons.geom as geometry(Polygon, 27700) and 032 declared
-- wayleaves.geom as geometry(MultiPolygon, 27700). projects.projects.geom is a bare
-- public.geometry - no type, no SRID - and that is deliberate, not an oversight (Dave,
-- 2026-08-14). Wayleave constrained both axes, and both bit:
--
-- THE TYPE. Drawing a boundary whose outline crosses itself - one stray click - stores
-- fine, because the type is Polygon and the geometry is a (invalid) Polygon. It is the
-- READ that changes it: map_get_v2.php serves every layer through ST_MakeValid, which
-- resolves a self-intersecting polygon into a MULTIPOLYGON of its untangled parts. So the
-- feature the map hands back is a MultiPolygon and the next save of it is rejected:
--
--   ERROR: Geometry type (MultiPolygon) does not match column type (Polygon)
--
-- The polygon is then uneditable and undeletable through the UI, with an error pointing at
-- the save rather than at the shape. A multi-part boundary - an estate in two blocks - was
-- refused outright for the same reason.
--
-- THE SRID. 27700 is British National Grid. Pinning it in the column type puts a single
-- country's projection into the schema, which is the wrong place for it: the transform
-- belongs in the endpoints, where it can differ per deployment, not in a DDL every client
-- shares. This is the half that would have been expensive to discover later.
--
-- Cutouts were never affected either way. A donut is one OGC Polygon with an interior
-- ring, so it fitted the old type as well as the new; what stops it is the drawing tool,
-- which produces a single exterior ring. Recorded in docs/improvement-opportunities.md.
--
-- STILL STORED VALID, which is the part that is not about types. The coverage views
-- resolve premises with ST_Within against these polygons, and ST_Within on an invalid
-- geometry gives unreliable answers rather than an error. Validity is now resolved on
-- write instead of on every read. ST_CollectionExtract(..., 3) is there because
-- ST_MakeValid can return a GeometryCollection when a shape degenerates to a polygon plus
-- a dangling edge; taking type 3 keeps the polygonal part and drops the thread.

-- ── 1. Guard ────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_dependents text;
BEGIN
    IF to_regclass('wayleave.wayleave_polygons') IS NULL THEN
        RAISE EXCEPTION '049: wayleave.wayleave_polygons not found';
    END IF;
    IF to_regclass('wayleave.vw_wayleave_pending') IS NULL THEN
        RAISE EXCEPTION '049: apply 048 first';
    END IF;

    -- Name every view depending on one of these geom columns BEFORE touching anything.
    -- ALTER COLUMN TYPE reports dependents one at a time, so the first attempt at this
    -- migration failed, was corrected, and would have failed again on the next one. This
    -- lists them all at once, and only for the geom column - refobjsubid is what keeps
    -- vw_wayleave_list, which reads these tables but not their geometry, out of it.
    SELECT string_agg(DISTINCT dv.relname, ', ') INTO v_dependents
      FROM pg_depend d
      JOIN pg_rewrite r ON r.oid = d.objid
      JOIN pg_class  dv ON dv.oid = r.ev_class
     WHERE d.classid    = 'pg_rewrite'::regclass
       AND d.refclassid = 'pg_class'::regclass
       AND d.refobjid IN ('wayleave.wayleave_polygons'::regclass,
                          'wayleave.wayleaves'::regclass,
                          'wayleave.wayleaves_history'::regclass)
       AND d.refobjsubid = (SELECT attnum FROM pg_attribute
                             WHERE attrelid = d.refobjid AND attname = 'geom')
       AND dv.relkind = 'v'
       AND dv.relname NOT IN ('vw_wayleave_pending', 'vw_uprns_from_boundary',
                              'vw_wayleave_polygons', 'vw_parent_wayleave_links');
    IF v_dependents IS NOT NULL THEN
        RAISE EXCEPTION '049: view(s) depend on a geom column this migration does not recreate: % - add them to the drop and create lists', v_dependents;
    END IF;
END $$;

-- ── 2. Views off, columns changed, views back ───────────────────────────────
-- ALTER COLUMN TYPE is blocked by ANY view referencing the column, so all four dependents
-- come down first. The first attempt at this migration dropped only the two that read the
-- geometry for coverage and was rejected by the two that merely carry it:
--
--   vw_wayleave_polygons     - passes geom straight through as a map layer.
--   vw_parent_wayleave_links - draws the parent/child link line from st_centroid(w.geom),
--                              which is why altering wayleaves.geom needs it gone too.
--
-- Each is recreated verbatim: vw_uprns_from_boundary and vw_wayleave_pending from 048,
-- the other two from 036. None of their definitions change here.

DROP VIEW wayleave.vw_wayleave_pending;
DROP VIEW wayleave.vw_uprns_from_boundary;
DROP VIEW wayleave.vw_wayleave_polygons;
DROP VIEW wayleave.vw_parent_wayleave_links;

-- The USING clause repairs what the old type let in: self-intersecting outlines become
-- valid multi-part geometries here rather than staying broken and being patched on every
-- read. Nothing is reprojected - the stored SRID is kept, only the constraint goes.
ALTER TABLE wayleave.wayleave_polygons
    ALTER COLUMN geom TYPE public.geometry
    USING public.ST_CollectionExtract(public.ST_MakeValid(geom), 3);

ALTER TABLE wayleave.wayleaves
    ALTER COLUMN geom TYPE public.geometry;

ALTER TABLE wayleave.wayleaves_history
    ALTER COLUMN geom TYPE public.geometry;

COMMENT ON COLUMN wayleave.wayleave_polygons.geom IS
    'Untyped public.geometry, matching projects.projects.geom. A typmod of Polygon made any self-intersecting boundary uneditable, because map_get_v2.php reads through ST_MakeValid and that returns a MultiPolygon; pinning SRID 27700 put one country projection into the schema. Endpoints transform; the column does not constrain. Stored valid on write so ST_Within in the coverage views has a sound geometry to test against.';

COMMENT ON COLUMN wayleave.wayleaves.geom IS
    'Union of the wayleave polygons, maintained by wayleave_coverage_polygon_save.php. 032 described this column as unused and unwritten, which was wrong at the time it was written. Untyped, as projects.projects.geom is.';

CREATE VIEW wayleave.vw_wayleave_polygons AS
 SELECT wayleave_polygons.id,
    wayleave_polygons.wayleave_id,
    wayleave_polygons.label,
    wayleave_polygons.geom
   FROM wayleave.wayleave_polygons
  WHERE wayleave_polygons.is_deleted = false;

CREATE VIEW wayleave.vw_parent_wayleave_links AS
 SELECT w.wayleave_id,
    w.wayleave_name,
    w.parent_wayleave_id,
    pw.wayleave_name AS parent_wayleave_name,
    public.st_makeline(public.st_centroid(w.geom), public.st_centroid(pw.geom)) AS geom,
    round((public.st_distance(public.st_centroid(w.geom), public.st_centroid(pw.geom)))::numeric, 0) AS distance
   FROM wayleave.wayleaves w
     JOIN wayleave.wayleaves pw ON w.parent_wayleave_id = pw.wayleave_id;

CREATE VIEW wayleave.vw_uprns_from_boundary AS
 SELECT p.wayleave_id,
    abp.uprn,
    p.id::text AS source_ref,
    'polygon'::text AS src,
    COALESCE(c.state, 'pending'::character varying) AS state,
    true AS in_source,
    COALESCE(c.state, 'pending'::character varying)::text AS map_state,
    c.decided_user,
    c.decided_datetime,
    u.username AS decided_username,
    abp.address AS address_full,
    abp.geom
   FROM wayleave.wayleave_polygons p
     JOIN basedata.abp abp ON public.st_within(abp.geom, p.geom)
     LEFT JOIN wayleave.wayleave_coverage c
            ON c.wayleave_id      = p.wayleave_id
           AND c.uprn             = abp.uprn
           AND c.source           = 'polygon'
           AND c.source_ref::text = p.id::text
           AND c.state           <> 'removed'
     LEFT JOIN users.users u ON u.id = c.decided_user
  WHERE p.is_deleted = false
UNION ALL
 SELECT c.wayleave_id,
    c.uprn,
    c.source_ref::text,
    'polygon'::text,
    c.state,
    false,
    CASE WHEN c.state = 'approved' THEN 'removing' ELSE c.state::text END,
    c.decided_user,
    c.decided_datetime,
    u.username,
    abp.address,
    abp.geom
   FROM wayleave.wayleave_coverage c
     LEFT JOIN users.users u ON u.id = c.decided_user
     LEFT JOIN basedata.abp abp ON abp.uprn = c.uprn
  WHERE c.source = 'polygon' AND c.state <> 'removed'
    AND NOT EXISTS (
        SELECT 1
          FROM wayleave.wayleave_polygons p2
          JOIN basedata.abp a2 ON public.st_within(a2.geom, p2.geom)
         WHERE p2.is_deleted   = false
           AND p2.wayleave_id  = c.wayleave_id
           AND p2.id::text     = c.source_ref::text
           AND a2.uprn         = c.uprn);

CREATE VIEW wayleave.vw_wayleave_pending AS
 WITH all_src AS (
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_boundary
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_direct
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_stocklists
         UNION ALL
         SELECT wayleave_id, uprn, src, source_ref, map_state FROM wayleave.vw_uprns_from_titles
        ), marked AS (
         SELECT a.wayleave_id, a.uprn, a.src, a.source_ref, a.map_state,
                EXISTS (SELECT 1 FROM wayleave.wayleave_coverage c
                         WHERE c.wayleave_id = a.wayleave_id
                           AND c.uprn        = a.uprn
                           AND c.state       = 'approved') AS covered_any,
                EXISTS (SELECT 1 FROM wayleave.wayleave_coverage c
                         WHERE c.wayleave_id = a.wayleave_id
                           AND c.uprn        = a.uprn
                           AND c.state       = 'approved'
                           AND NOT (c.source = a.src AND c.source_ref = a.source_ref)) AS covered_other
           FROM all_src a
          WHERE a.map_state IN ('pending', 'removing')
        )
 SELECT m.wayleave_id,
    m.src,
    m.source_ref,
    count(*) FILTER (WHERE m.map_state = 'pending')                          AS adding_gross,
    count(*) FILTER (WHERE m.map_state = 'pending'  AND NOT m.covered_any)   AS adding_net,
    count(*) FILTER (WHERE m.map_state = 'removing')                         AS removing_gross,
    count(*) FILTER (WHERE m.map_state = 'removing' AND NOT m.covered_other) AS removing_net
   FROM marked m
  GROUP BY m.wayleave_id, m.src, m.source_ref;

COMMENT ON VIEW wayleave.vw_wayleave_pending IS
    'One row per outstanding source change. Pending is derived, not stored: only a direct UPRN writes a pending row, so counting wayleave_coverage would miss every polygon, stocklist and title change. Filter by wayleave_id - since 048 that predicate reaches the base tables.';

-- ── 3. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_bad text;
BEGIN
    -- format_type prints a constrained column as geometry(Type,SRID) and an unconstrained
    -- one as plain geometry, so this asserts the absence of BOTH restrictions at once.
    SELECT string_agg(c.relname || '.' || a.attname || ' is ' || format_type(a.atttypid, a.atttypmod), ', ')
      INTO v_bad
      FROM pg_attribute a
      JOIN pg_class c ON c.oid = a.attrelid
     WHERE a.attrelid IN ('wayleave.wayleave_polygons'::regclass,
                          'wayleave.wayleaves'::regclass,
                          'wayleave.wayleaves_history'::regclass)
       AND a.attname = 'geom'
       AND format_type(a.atttypid, a.atttypmod) <> 'geometry';
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '049: typmod still present on %', v_bad;
    END IF;

    -- Nothing was mangled on the way through: every stored polygon must still be present
    -- and valid, or CollectionExtract dropped something it should not have.
    SELECT string_agg(id::text, ', ') INTO v_bad
      FROM wayleave.wayleave_polygons
     WHERE geom IS NULL OR public.ST_IsEmpty(geom) OR NOT public.ST_IsValid(geom);
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '049: polygon(s) emptied or invalid after conversion: %', v_bad;
    END IF;

    -- All four dependents back, not just the two the first attempt knew about. A missing
    -- one here is silent until a map layer renders empty or the hierarchy panel breaks.
    SELECT string_agg(v, ', ') INTO v_bad
      FROM unnest(ARRAY['vw_uprns_from_boundary', 'vw_wayleave_pending',
                        'vw_wayleave_polygons', 'vw_parent_wayleave_links']) AS v
     WHERE to_regclass('wayleave.' || v) IS NULL;
    IF v_bad IS NOT NULL THEN
        RAISE EXCEPTION '049: view(s) not recreated: %', v_bad;
    END IF;

    PERFORM 1 FROM wayleave.vw_uprns_from_boundary   LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_pending      LIMIT 1;
    PERFORM 1 FROM wayleave.vw_wayleave_polygons     LIMIT 1;
    PERFORM 1 FROM wayleave.vw_parent_wayleave_links LIMIT 1;
END $$;
