-- 038_wayleave_statics_to_projects_set.sql
-- Wayleave module realignment — correction to 034
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 4)
--
-- 034 seeded ELEVEN static fields. Six mirror migration 012's projects set; the other
-- five — wayleave_reference, wayleave_type, wayleave_team, ecd_date, signed_date —
-- have no equivalent column on projects.projects and should never have been static
-- (Dave, 2026-08-11, on testing item 10).
--
-- WHY IT MATTERS RATHER THAN BEING COSMETIC. A static field is code-defined: a real
-- column on the entity table, whitelisted in the save endpoint, refused by
-- admin_save.php for edit or delete, and seeded only by migration. Making these five
-- static hard-coded five business decisions into the schema — that a wayleave has a
-- reference, a type, a team and exactly two dates — for every client, permanently, and
-- put them beyond admin's reach. As dynamic fields they are admin's to define, rename,
-- reorder, retire or replace. That is the whole point of the meta system, and
-- "mirror projects" is what makes the boundary obvious: if projects.projects has no
-- such column, it is not static.
--
-- The five columns therefore go from wayleave.wayleaves too. A static field's
-- field_form_id IS its column; with the meta rows gone the columns have nothing
-- reading or writing them, and their values will live in the EAV tables instead.
--
-- NO DYNAMIC FIELDS ARE CREATED HERE. Creating them is an admin task, not a
-- migration's (Dave, 2026-08-10). Admin recreates whichever of the five this client
-- wants, in Module Management, after this applies.
--
-- wayleave.teams IS DROPPED WITH THEM (Dave, 2026-08-11). wayleave_team was the only
-- field pointing at it, and the decision is that it SHOULD be orphaned rather than
-- kept as reference data. The argument is the same one that makes the five fields
-- dynamic, applied to a table:
--
--   Client A is a wayleave team of three people. Client B is three teams of ten.
--   Only B needs the concept at all. A schema table forces it on both — A carries a
--   teams table it will never populate and a Team field it does not want. A dynamic
--   dropdown exists only where it is configured, and costs the other client nothing.
--
-- The general rule this settles, worth stating because it will come up again: a
-- lookup table in the schema is only justified when the values are GeoLynx-controlled
-- or consumed outside the form (statuses are, which is why wayleave_status stays).
-- Client-configurable option lists belong to a dynamic field's own options.
--
-- get_update_form.php's 'wayleave_team' entry in $optionsSources goes with it.
--
-- Order matters: dropdown options reference the field row, the history trigger and the
-- list view reference the columns. Options -> fields -> trigger -> view -> columns.

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

DO $$
DECLARE
    v_n integer;
BEGIN
    SELECT count(*) INTO v_n FROM wayleave.wayleave_fields WHERE field_type = 'static';
    IF v_n <> 11 THEN
        RAISE EXCEPTION '038 expects 034''s eleven static fields, found % — has this already run?', v_n;
    END IF;

    IF to_regclass('wayleave.vw_wayleave_list') IS NULL THEN
        RAISE EXCEPTION 'wayleave.vw_wayleave_list is missing — apply 036 first';
    END IF;
END $$;

-- ── 1. Remove the five meta rows and their options ──────────────────────────

DELETE FROM wayleave.wayleave_field_dropdown_options
 WHERE field_id IN (SELECT field_id FROM wayleave.wayleave_fields
                     WHERE field_form_id IN ('wayleave_reference', 'wayleave_type',
                                             'wayleave_team', 'ecd_date', 'signed_date'));

DELETE FROM wayleave.wayleave_fields
 WHERE field_form_id IN ('wayleave_reference', 'wayleave_type',
                         'wayleave_team', 'ecd_date', 'signed_date');

-- Renumber the survivors 1-6 so the form has no gaps, and move the 6-column spacer
-- onto the last one, matching 012 and 027.
UPDATE wayleave.wayleave_fields SET field_display_order = 1, field_spacer_after = NULL WHERE field_form_id = 'wayleave_name';
UPDATE wayleave.wayleave_fields SET field_display_order = 2, field_spacer_after = NULL WHERE field_form_id = 'account_id';
UPDATE wayleave.wayleave_fields SET field_display_order = 3, field_spacer_after = NULL WHERE field_form_id = 'wayleave_manager';
UPDATE wayleave.wayleave_fields SET field_display_order = 4, field_spacer_after = NULL WHERE field_form_id = 'company_id';
UPDATE wayleave.wayleave_fields SET field_display_order = 5, field_spacer_after = NULL WHERE field_form_id = 'parent_wayleave_id';
UPDATE wayleave.wayleave_fields SET field_display_order = 6, field_spacer_after = 6    WHERE field_form_id = 'wayleave_status_id';

-- ── 2. Rebuild the history trigger without the five columns ─────────────────
-- CREATE OR REPLACE, so the trigger binding created by 035 is untouched.

CREATE OR REPLACE FUNCTION public.fn_wayleaves_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    wayleave.wayleaves;
    v_user   integer;
BEGIN
    IF TG_OP = 'UPDATE'
       AND NEW.wayleave_id        IS NOT DISTINCT FROM OLD.wayleave_id
       AND NEW.parent_wayleave_id IS NOT DISTINCT FROM OLD.parent_wayleave_id
       AND NEW.wayleave_name      IS NOT DISTINCT FROM OLD.wayleave_name
       AND NEW.wayleave_status_id IS NOT DISTINCT FROM OLD.wayleave_status_id
       AND NEW.wayleave_manager   IS NOT DISTINCT FROM OLD.wayleave_manager
       AND NEW.geom               IS NOT DISTINCT FROM OLD.geom
       AND NEW.created_datetime   IS NOT DISTINCT FROM OLD.created_datetime
       AND NEW.created_user       IS NOT DISTINCT FROM OLD.created_user
       AND NEW.company_id         IS NOT DISTINCT FROM OLD.company_id
       AND NEW.account_id         IS NOT DISTINCT FROM OLD.account_id
       AND NEW.stocklist_id       IS NOT DISTINCT FROM OLD.stocklist_id
       AND NEW.cover_image_url    IS NOT DISTINCT FROM OLD.cover_image_url
       AND NEW.is_deleted         IS NOT DISTINCT FROM OLD.is_deleted
    THEN
        RETURN NEW;
    END IF;

    v_action := TG_OP;

    IF TG_OP = 'DELETE' THEN
        v_row := OLD;
    ELSE
        v_row := NEW;
    END IF;

    IF current_setting('app.user_id', true) ~ '^\d+$' THEN
        v_user := current_setting('app.user_id', true)::integer;
    ELSE
        v_user := v_row.modified_user;
    END IF;

    INSERT INTO wayleave.wayleaves_history (
        wayleave_id, parent_wayleave_id, wayleave_name, wayleave_status_id,
        wayleave_manager, geom, created_datetime, created_user,
        modified_datetime, modified_user, company_id, account_id, stocklist_id,
        cover_image_url, is_deleted,
        history_action, history_datetime, history_user
    ) VALUES (
        v_row.wayleave_id, v_row.parent_wayleave_id, v_row.wayleave_name,
        v_row.wayleave_status_id, v_row.wayleave_manager, v_row.geom,
        v_row.created_datetime, v_row.created_user, v_row.modified_datetime,
        v_row.modified_user, v_row.company_id, v_row.account_id, v_row.stocklist_id,
        v_row.cover_image_url, v_row.is_deleted,
        v_action, now(), v_user
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

-- ── 3. Rebuild the list view without the five columns ───────────────────────
-- DROP then CREATE: CREATE OR REPLACE VIEW cannot remove columns.

DROP VIEW wayleave.vw_wayleave_list;

CREATE VIEW wayleave.vw_wayleave_list AS
 WITH all_uprns AS (
         SELECT wayleave_polygon_uprns.wayleave_id, wayleave_polygon_uprns.uprn
           FROM wayleave.wayleave_polygon_uprns
          WHERE wayleave_polygon_uprns.is_assigned = true
            AND wayleave_polygon_uprns.is_approved = true
        UNION
         SELECT wayleave_uprns.wayleave_id, wayleave_uprns.uprn
           FROM wayleave.wayleave_uprns
          WHERE wayleave_uprns.is_deleted = false
        UNION
         SELECT ws.wayleave_id, sp.uprn
           FROM wayleave.wayleave_stocklists ws
             JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ws.stocklist_id
          WHERE ws.is_deleted = false
        ), prem AS (
         SELECT all_uprns.wayleave_id, count(DISTINCT all_uprns.uprn) AS premise_count
           FROM all_uprns
          GROUP BY all_uprns.wayleave_id
        ), poly AS (
         SELECT wayleave_polygons.wayleave_id, count(*) AS polygon_count
           FROM wayleave.wayleave_polygons
          WHERE wayleave_polygons.is_deleted = false
          GROUP BY wayleave_polygons.wayleave_id
        )
 SELECT w.wayleave_id,
    w.parent_wayleave_id,
    pw.wayleave_name AS parent_wayleave_name,
    w.wayleave_name,
    w.wayleave_status_id,
    st.wayleave_status_desc,
    st.wayleave_status_style,
    w.wayleave_manager,
    pm.username AS wayleave_manager_name,
    w.created_datetime,
    to_char(w.created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS created_date,
    w.created_user,
    uc.username AS created_username,
    to_char(w.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    w.modified_user,
    um.username AS modified_username,
    w.company_id,
    c.company_name,
    w.account_id,
    ac.account_name,
    w.stocklist_id,
    w.cover_image_url,
    COALESCE(prem.premise_count, 0::bigint) AS premise_count,
    COALESCE(poly.polygon_count, 0::bigint) AS polygon_count
   FROM wayleave.wayleaves w
     LEFT JOIN wayleave.wayleaves pw ON w.parent_wayleave_id = pw.wayleave_id
     LEFT JOIN wayleave.wayleave_status st ON st.wayleave_status_id = w.wayleave_status_id
     LEFT JOIN users.users pm ON pm.id = w.wayleave_manager
     LEFT JOIN users.users uc ON uc.id = w.created_user
     LEFT JOIN users.users um ON um.id = w.modified_user
     LEFT JOIN users.companies c ON c.company_id = w.company_id
     LEFT JOIN accounts.accounts ac ON ac.account_id = w.account_id
     LEFT JOIN prem ON prem.wayleave_id = w.wayleave_id
     LEFT JOIN poly ON poly.wayleave_id = w.wayleave_id
  WHERE w.is_deleted = false;

-- ── 4. Drop the columns ─────────────────────────────────────────────────────

ALTER TABLE wayleave.wayleaves
    DROP COLUMN wayleave_reference,
    DROP COLUMN wayleave_type,
    DROP COLUMN wayleave_team,
    DROP COLUMN ecd_date,
    DROP COLUMN signed_date;

ALTER TABLE wayleave.wayleaves_history
    DROP COLUMN wayleave_reference,
    DROP COLUMN wayleave_type,
    DROP COLUMN wayleave_team,
    DROP COLUMN ecd_date,
    DROP COLUMN signed_date;

-- ── 4b. Drop the orphaned teams lookup ──────────────────────────────────────
-- Nothing references it once wayleave_team's meta row is gone. Reported before the
-- drop so the configured team names are in the migration output if anyone wants them
-- back as dropdown options on the dynamic field.

DO $$
DECLARE
    v_teams text;
BEGIN
    SELECT string_agg(team_name, ', ' ORDER BY display_order, team_name) INTO v_teams
      FROM wayleave.teams;
    RAISE NOTICE '038: dropping wayleave.teams — configured values were: %',
        COALESCE(v_teams, '(none)');
END $$;

DROP TABLE wayleave.teams;

-- ── 5. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_static  text;
    v_left    text;
    v_missing text;
BEGIN
    -- Exactly migration 012's six, no more and no fewer.
    SELECT string_agg(field_form_id, ', ' ORDER BY field_display_order) INTO v_static
      FROM wayleave.wayleave_fields WHERE field_type = 'static';

    IF v_static <> 'wayleave_name, account_id, wayleave_manager, company_id, parent_wayleave_id, wayleave_status_id' THEN
        RAISE EXCEPTION '038: static set is now "%", expected the projects six', v_static;
    END IF;

    SELECT string_agg(column_name, ', ') INTO v_left
      FROM information_schema.columns
     WHERE table_schema = 'wayleave' AND table_name IN ('wayleaves', 'wayleaves_history')
       AND column_name IN ('wayleave_reference', 'wayleave_type', 'wayleave_team',
                           'ecd_date', 'signed_date');

    IF v_left IS NOT NULL THEN
        RAISE EXCEPTION '038: column(s) not dropped: %', v_left;
    END IF;

    -- History must still carry every entity column, or the trigger fails on first write.
    SELECT string_agg(c.column_name, ', ') INTO v_missing
      FROM information_schema.columns c
     WHERE c.table_schema = 'wayleave' AND c.table_name = 'wayleaves'
       AND NOT EXISTS (SELECT 1 FROM information_schema.columns h
                        WHERE h.table_schema = 'wayleave' AND h.table_name = 'wayleaves_history'
                          AND h.column_name = c.column_name);

    IF v_missing IS NOT NULL THEN
        RAISE EXCEPTION '038: wayleaves_history is missing entity column(s): %', v_missing;
    END IF;

    PERFORM 1 FROM wayleave.vw_wayleave_list LIMIT 1;

    RAISE NOTICE '038: static set reduced to the projects six; the other five are now admin-created dynamic fields';
END $$;
