-- 035_wayleave_history_triggers.sql
-- Wayleave module realignment, migration 5 of 6
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 5)
--
-- History capture for the wayleave module: one trigger on the entity table and one on
-- each of the five EAV value tables, following migrations 017, 018 and 028 exactly.
-- Requires 034.
--
-- FIXES AN OMISSION IN 034. That migration added stocklist_id to wayleave.wayleaves to
-- complete the projects mirror, but not to wayleave.wayleaves_history — which 032 had
-- created without it. The entity trigger below inserts every entity column into the
-- history table, so without this the very first write to any wayleave would fail with
-- "column stocklist_id does not exist". 034 is applied and therefore immutable, so the
-- column is added here.
--
-- WHY TRIGGERS AND NOT PHP. This is the change that gives wayleave real history for the
-- first time. Until now wayleave_save.php wrote history itself, so ANY other write path
-- — polygon saves, coverage changes, land registry, map edits, manual SQL — left no
-- trace at all. From here every path is captured, atomically with the statement that
-- caused it, and wayleave_save.php's own history writes are removed in Phase 7.
--
-- ACTOR ATTRIBUTION: current_setting('app.user_id') — set per request by www/fn/db.php
-- — when it is a plain integer, else the row's modified_user / record_user, else NULL.
-- A malformed setting degrades to the fallback and never aborts the write. This is why
-- the wayleave endpoints' private `new PDO(...)` connections must go in Phase 7: a
-- connection that never set the GUC attributes every write to the fallback, which means
-- the last editor rather than the actual actor.
--
-- NO-OP SUPPRESSION on the entity table: an UPDATE changing nothing except
-- modified_datetime / modified_user writes no history row. Those two are excluded from
-- the comparison because trg_wayleaves_update_modified_fields (migration 032) always
-- bumps modified_datetime first, so including them would make every update look like a
-- change. On the value tables the same rule applies to `value`.
--
-- INSERTS ARE CAPTURED, so a wayleave gets a history row from the moment it exists.

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

DO $$
BEGIN
    IF to_regclass('wayleave.wayleaves_history') IS NULL THEN
        RAISE EXCEPTION 'wayleave.wayleaves_history is missing — apply 032/034 first';
    END IF;

    IF to_regclass('wayleave.wayleave_field_values_text') IS NULL THEN
        RAISE EXCEPTION 'the EAV tables are missing — apply 034 first';
    END IF;

    IF EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
                WHERE n.nspname = 'public' AND p.proname = 'fn_wayleaves_history') THEN
        RAISE EXCEPTION '035 has already run — public.fn_wayleaves_history() exists';
    END IF;
END $$;

-- ── 1. Complete the history table (034's omission) ──────────────────────────

ALTER TABLE wayleave.wayleaves_history ADD COLUMN IF NOT EXISTS stocklist_id integer;

-- ── 2. Entity table ────────────────────────────────────────────────────────

CREATE 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
       AND NEW.wayleave_reference IS NOT DISTINCT FROM OLD.wayleave_reference
       AND NEW.wayleave_type      IS NOT DISTINCT FROM OLD.wayleave_type
       AND NEW.wayleave_team      IS NOT DISTINCT FROM OLD.wayleave_team
       AND NEW.ecd_date           IS NOT DISTINCT FROM OLD.ecd_date
       AND NEW.signed_date        IS NOT DISTINCT FROM OLD.signed_date
    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, wayleave_reference, wayleave_type,
        wayleave_team, ecd_date, signed_date,
        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_row.wayleave_reference,
        v_row.wayleave_type, v_row.wayleave_team, v_row.ecd_date, v_row.signed_date,
        v_action, now(), v_user
    );

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

CREATE TRIGGER trg_wayleaves_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleaves
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleaves_history();

-- ── 3. EAV value tables ────────────────────────────────────────────────────
--
-- One function per table rather than one shared function reading TG_ARGV, matching 018
-- and 028. The row variable is declared as the table's own composite type, which a
-- shared function could not do. Written out rather than generated in a loop for the
-- same reason those two are: a function body inside a format() string means nested
-- dollar quoting, and that is not worth the saved lines.

-- text ----------------------------------------------------------------------
CREATE FUNCTION public.fn_wayleave_field_values_text_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  wayleave.wayleave_field_values_text;
    v_user integer;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.value IS NOT DISTINCT FROM OLD.value THEN
        RETURN NEW;
    END IF;

    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.record_user;
    END IF;

    INSERT INTO wayleave.wayleave_field_values_text_history
        (field_id, wayleave_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.wayleave_id, v_row.value, now(), v_user, TG_OP);

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

CREATE TRIGGER trg_wayleave_field_values_text_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleave_field_values_text
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleave_field_values_text_history();

-- int -----------------------------------------------------------------------
CREATE FUNCTION public.fn_wayleave_field_values_int_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  wayleave.wayleave_field_values_int;
    v_user integer;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.value IS NOT DISTINCT FROM OLD.value THEN
        RETURN NEW;
    END IF;

    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.record_user;
    END IF;

    INSERT INTO wayleave.wayleave_field_values_int_history
        (field_id, wayleave_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.wayleave_id, v_row.value, now(), v_user, TG_OP);

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

CREATE TRIGGER trg_wayleave_field_values_int_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleave_field_values_int
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleave_field_values_int_history();

-- numeric -------------------------------------------------------------------
CREATE FUNCTION public.fn_wayleave_field_values_numeric_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  wayleave.wayleave_field_values_numeric;
    v_user integer;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.value IS NOT DISTINCT FROM OLD.value THEN
        RETURN NEW;
    END IF;

    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.record_user;
    END IF;

    INSERT INTO wayleave.wayleave_field_values_numeric_history
        (field_id, wayleave_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.wayleave_id, v_row.value, now(), v_user, TG_OP);

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

CREATE TRIGGER trg_wayleave_field_values_numeric_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleave_field_values_numeric
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleave_field_values_numeric_history();

-- date ----------------------------------------------------------------------
CREATE FUNCTION public.fn_wayleave_field_values_date_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  wayleave.wayleave_field_values_date;
    v_user integer;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.value IS NOT DISTINCT FROM OLD.value THEN
        RETURN NEW;
    END IF;

    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.record_user;
    END IF;

    INSERT INTO wayleave.wayleave_field_values_date_history
        (field_id, wayleave_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.wayleave_id, v_row.value, now(), v_user, TG_OP);

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

CREATE TRIGGER trg_wayleave_field_values_date_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleave_field_values_date
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleave_field_values_date_history();

-- boolean -------------------------------------------------------------------
CREATE FUNCTION public.fn_wayleave_field_values_boolean_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  wayleave.wayleave_field_values_boolean;
    v_user integer;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.value IS NOT DISTINCT FROM OLD.value THEN
        RETURN NEW;
    END IF;

    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.record_user;
    END IF;

    INSERT INTO wayleave.wayleave_field_values_boolean_history
        (field_id, wayleave_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.wayleave_id, v_row.value, now(), v_user, TG_OP);

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

CREATE TRIGGER trg_wayleave_field_values_boolean_history
    AFTER INSERT OR DELETE OR UPDATE ON wayleave.wayleave_field_values_boolean
    FOR EACH ROW EXECUTE FUNCTION public.fn_wayleave_field_values_boolean_history();

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

DO $$
DECLARE
    v_trg     integer;
    v_missing text;
BEGIN
    SELECT count(*) INTO v_trg
      FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid
     WHERE NOT t.tgisinternal
       AND c.relnamespace = 'wayleave'::regnamespace
       AND t.tgname LIKE '%_history';

    IF v_trg <> 6 THEN
        RAISE EXCEPTION '035: % history trigger(s) present, expected 6', v_trg;
    END IF;

    -- The history table must carry every entity column, or the entity trigger fails on
    -- its first insert.
    --
    -- Asserted directly rather than by comparing column COUNTS. The first draft of this
    -- expected a difference of 3 (history_action, history_datetime, history_user) and
    -- forgot history_id, so it failed on a schema that was entirely correct — the
    -- second verification block in this migration set to do that. A derived count
    -- encodes an assumption that can be off by one; this names the missing column
    -- instead, and cannot be wrong about what it is checking.
    SELECT string_agg(c.column_name, ', ' ORDER BY c.ordinal_position) 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 '035: wayleaves_history is missing entity column(s): %', v_missing;
    END IF;

    RAISE NOTICE '035: six history triggers live; wayleave has database-written history for the first time';
END $$;
