-- 062_project_soft_delete_map_views.sql
-- Project delete & restore, map-facing views
-- (docs/2026-08-28-project-delete-restore.md, Phase 3)
--
-- 061 filtered projects.vw_projects_list, which is what the project LIST reads. The MAP
-- reads different relations entirely, and they were missed because the set of relations
-- the map serves is DATA, not code: public.map_layers rows carry database_schema /
-- database_table and a url pointing at fn/map_get_v2.php?geotable=..., so no amount of
-- grepping the codebase finds them. Nothing in www/ names vw_projects_geom at all.
--
-- Found by Dave testing Phase 2, 2026-08-29: a deleted project was still returned by
-- fn/map_get_v2.php?geotable=projects.vw_projects_geom.
--
-- THE FOUR PROJECT LAYERS, and who fixes each:
--
--   18  Project Boundary            projects.vw_projects_geom            <- here
--   35  Project Neighbours          projects.project_nearest_neighbours  <- here
--   33  Projects (All)              projects.projects            GeoServer WMS, Dave
--   23  Parent/Child Project Links  projects.vw_parent_project_links     nothing needed
--
-- Layer 23 needs no change in either place: vw_parent_project_links is an INNER JOIN of
-- projects to projects on parent_project_id, and a deleted project has no parent and is
-- nobody's parent, so it can appear on neither side. That is the hierarchy-free delete
-- rule paying for itself.
--
-- Layer 33 reads the base table through GeoServer, so it cannot be fixed by a view and
-- is not fixed here; it needs is_deleted = false in the GeoServer layer's SQL view or a
-- CQL filter.
--
-- WHY IN THE VIEWS AND NOT IN map_get_v2.php. Neither view exposes is_deleted as a
-- column, so the endpoint could not filter them without selecting a column that is not
-- there. Filtering inside the view is also the more useful half: it is correct for every
-- consumer, including GeoServer, rather than only for requests that happen to arrive
-- through our endpoint. map_get_v2.php gets a predicate for projects.projects, which is
-- the one project relation it serves that does expose the column.
--
-- BOTH SIDES OF project_nearest_neighbours are filtered. It is a project cross-joined
-- laterally to the projects near it, so a deleted project can appear twice over: as the
-- project being asked about (bci), and as somebody else's neighbour (b). The second is
-- the one that would draw an archived project's geometry onto a live project's map. The
-- first would generate neighbour rows for a project nothing else can see, which
-- project_load.php would never ask for — it refuses deleted projects since Phase 2 — but
-- map layer 35 reads the view directly and is not going through that endpoint.
--
-- Both definitions are otherwise verbatim from the baseline schema; neither view has
-- been touched by any migration.

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

DO $guard$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = 'projects' AND table_name = 'projects'
                      AND column_name = 'is_deleted') THEN
        RAISE EXCEPTION '062: projects.projects has no is_deleted — apply 061 first';
    END IF;
    IF to_regclass('projects.vw_projects_geom') IS NULL THEN
        RAISE EXCEPTION '062: projects.vw_projects_geom is missing';
    END IF;
    IF to_regclass('projects.project_nearest_neighbours') IS NULL THEN
        RAISE EXCEPTION '062: projects.project_nearest_neighbours is missing';
    END IF;
    IF pg_get_viewdef('projects.vw_projects_geom'::regclass) LIKE '%is_deleted%' THEN
        RAISE EXCEPTION '062 has already run — vw_projects_geom filters is_deleted';
    END IF;
END $guard$;

-- ── 1. projects.vw_projects_geom ────────────────────────────────────────────

CREATE OR REPLACE VIEW projects.vw_projects_geom AS
 SELECT p.project_id,
    p.parent_project_id,
    p.project_name,
    p.project_status_id,
    p.project_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS project_manager_name,
    p.company_id,
    c.company_name,
    p.account_id,
    a.account_name,
    p.cover_image_url,
    p.geom,
        CASE
            WHEN ((p.project_status_id = 100) AND (NULLIF(vt.value, ''::text) IS NOT NULL)) THEN 'purple'::text
            WHEN (p.project_status_id = 100) THEN 'green'::text
            WHEN (p.project_status_id = 0) THEN 'black'::text
            WHEN (p.project_status_id = 5) THEN 'red'::text
            ELSE 'blue'::text
        END AS polygon_style
   FROM ((((projects.projects p
     LEFT JOIN users.users u ON ((p.project_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((p.account_id = a.account_id)))
     LEFT JOIN projects.project_field_values_text vt ON (((p.project_id = vt.project_id) AND (vt.field_id = 21))))
  WHERE (p.is_deleted = false);   -- 062

-- ── 2. projects.project_nearest_neighbours ──────────────────────────────────

CREATE OR REPLACE VIEW projects.project_nearest_neighbours AS
 SELECT bci.project_id,
    n.neighbour_id,
    n.project_name,
    n.geom,
    n.distance,
    n.project_status,
    n.account_id,
    n.account_name
   FROM (projects.projects bci
     CROSS JOIN LATERAL ( SELECT b.project_id AS neighbour_id,
            b.project_name,
            COALESCE(ps.project_status_desc, 'Status Not Set'::character varying) AS project_status,
            a.account_id,
            a.account_name,
            b.geom,
            round((public.st_distance(b.geom, bci.geom))::numeric, 2) AS distance
           FROM ((projects.projects b
             LEFT JOIN projects.project_status ps ON ((b.project_status_id = ps.project_status_id)))
             LEFT JOIN accounts.accounts a ON ((b.account_id = a.account_id)))
          -- 062: an archived project is not somebody's neighbour. Filtered inside the
          -- LATERAL so it cuts the candidate set before the distance test rather than
          -- after it.
          WHERE ((b.is_deleted = false) AND (b.project_id <> bci.project_id) AND (b.geom OPERATOR(public.&&) public.st_expand(bci.geom, (5000)::double precision)) AND (public.st_distance(b.geom, bci.geom) < (5000)::double precision))) n)
  WHERE (bci.is_deleted = false);   -- 062

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

DO $verify$
DECLARE
    v_geom  bigint;
    v_nb_a  bigint;
    v_nb_b  bigint;
BEGIN
    -- Both views still resolve.
    PERFORM 1 FROM projects.vw_projects_geom LIMIT 1;
    PERFORM 1 FROM projects.project_nearest_neighbours LIMIT 1;

    -- No deleted project appears in the boundary layer.
    SELECT count(*) INTO v_geom
      FROM projects.vw_projects_geom v
      JOIN projects.projects p ON p.project_id = v.project_id
     WHERE p.is_deleted;
    IF v_geom > 0 THEN
        RAISE EXCEPTION '062: vw_projects_geom still returns % deleted project(s)', v_geom;
    END IF;

    -- Nor on either side of the neighbours view.
    SELECT count(*) INTO v_nb_a
      FROM projects.project_nearest_neighbours n
      JOIN projects.projects p ON p.project_id = n.project_id
     WHERE p.is_deleted;
    SELECT count(*) INTO v_nb_b
      FROM projects.project_nearest_neighbours n
      JOIN projects.projects p ON p.project_id = n.neighbour_id
     WHERE p.is_deleted;
    IF v_nb_a > 0 OR v_nb_b > 0 THEN
        RAISE EXCEPTION '062: project_nearest_neighbours still returns deleted projects — % as subject, % as neighbour',
            v_nb_a, v_nb_b;
    END IF;

    -- Every LIVE project is still in the boundary layer: the filter removed archived
    -- projects and nothing else.
    SELECT count(*) INTO v_geom
      FROM projects.projects p
     WHERE p.is_deleted = false
       AND NOT EXISTS (SELECT 1 FROM projects.vw_projects_geom v
                        WHERE v.project_id = p.project_id);
    IF v_geom > 0 THEN
        RAISE EXCEPTION '062: % live project(s) dropped out of vw_projects_geom', v_geom;
    END IF;

    RAISE NOTICE '062: vw_projects_geom and project_nearest_neighbours now filter is_deleted';
END $verify$;
