-- 018_eav_history_triggers.sql
-- Entity history capture via DB triggers, phase 3
-- (docs/2026-07-19-entity-history-triggers.md).
--
-- History triggers for the 15 dynamic-field (EAV) value tables — projects /
-- accounts / stocklists × text / int / numeric / date / boolean — replacing the
-- change-gated history inserts in the three *_save.php endpoints (removed in
-- the companion PHP change). One function per table, following the
-- fn_projects_history() pattern from migration 017.
--
-- History tables keep their value-log shape: value_id self-generates (existing
-- nextval defaults), record_datetime is the moment of capture (now(), matching
-- what the PHP inserts wrote), record_user is the actor — app.user_id GUC when
-- it is a plain integer, else the source row's record_user. history_action is
-- added so DELETEs are distinguishable; it keeps its DEFAULT so any straggling
-- legacy insert that does not name the column still succeeds (existing rows
-- were all change-gated UPDATE writes, so the backfilled default is accurate).
--
-- No-op suppression: an UPDATE that leaves value unchanged inserts no history
-- row (the PHP endpoints only wrote on change; triggers keep that behaviour).

-- ── projects ──────────────────────────────────────────────────────────────

ALTER TABLE projects.project_field_values_text_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_project_field_values_text_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  projects.project_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 projects.project_field_values_text_history
        (field_id, project_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.project_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_project_field_values_text_history
    AFTER INSERT OR DELETE OR UPDATE ON projects.project_field_values_text
    FOR EACH ROW EXECUTE FUNCTION public.fn_project_field_values_text_history();

ALTER TABLE projects.project_field_values_int_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_project_field_values_int_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  projects.project_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 projects.project_field_values_int_history
        (field_id, project_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.project_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_project_field_values_int_history
    AFTER INSERT OR DELETE OR UPDATE ON projects.project_field_values_int
    FOR EACH ROW EXECUTE FUNCTION public.fn_project_field_values_int_history();

ALTER TABLE projects.project_field_values_numeric_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_project_field_values_numeric_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  projects.project_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 projects.project_field_values_numeric_history
        (field_id, project_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.project_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_project_field_values_numeric_history
    AFTER INSERT OR DELETE OR UPDATE ON projects.project_field_values_numeric
    FOR EACH ROW EXECUTE FUNCTION public.fn_project_field_values_numeric_history();

ALTER TABLE projects.project_field_values_date_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_project_field_values_date_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  projects.project_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 projects.project_field_values_date_history
        (field_id, project_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.project_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_project_field_values_date_history
    AFTER INSERT OR DELETE OR UPDATE ON projects.project_field_values_date
    FOR EACH ROW EXECUTE FUNCTION public.fn_project_field_values_date_history();

ALTER TABLE projects.project_field_values_boolean_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_project_field_values_boolean_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  projects.project_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 projects.project_field_values_boolean_history
        (field_id, project_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.project_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_project_field_values_boolean_history
    AFTER INSERT OR DELETE OR UPDATE ON projects.project_field_values_boolean
    FOR EACH ROW EXECUTE FUNCTION public.fn_project_field_values_boolean_history();

-- ── accounts ──────────────────────────────────────────────────────────────

ALTER TABLE accounts.account_field_values_text_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_account_field_values_text_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  accounts.account_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 accounts.account_field_values_text_history
        (field_id, account_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.account_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_account_field_values_text_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.account_field_values_text
    FOR EACH ROW EXECUTE FUNCTION public.fn_account_field_values_text_history();

ALTER TABLE accounts.account_field_values_int_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_account_field_values_int_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  accounts.account_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 accounts.account_field_values_int_history
        (field_id, account_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.account_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_account_field_values_int_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.account_field_values_int
    FOR EACH ROW EXECUTE FUNCTION public.fn_account_field_values_int_history();

ALTER TABLE accounts.account_field_values_numeric_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_account_field_values_numeric_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  accounts.account_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 accounts.account_field_values_numeric_history
        (field_id, account_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.account_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_account_field_values_numeric_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.account_field_values_numeric
    FOR EACH ROW EXECUTE FUNCTION public.fn_account_field_values_numeric_history();

ALTER TABLE accounts.account_field_values_date_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_account_field_values_date_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  accounts.account_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 accounts.account_field_values_date_history
        (field_id, account_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.account_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_account_field_values_date_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.account_field_values_date
    FOR EACH ROW EXECUTE FUNCTION public.fn_account_field_values_date_history();

ALTER TABLE accounts.account_field_values_boolean_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_account_field_values_boolean_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  accounts.account_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 accounts.account_field_values_boolean_history
        (field_id, account_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.account_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_account_field_values_boolean_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.account_field_values_boolean
    FOR EACH ROW EXECUTE FUNCTION public.fn_account_field_values_boolean_history();

-- ── stocklists ──────────────────────────────────────────────────────────────

ALTER TABLE stocklists.stocklist_field_values_text_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_stocklist_field_values_text_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  stocklists.stocklist_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 stocklists.stocklist_field_values_text_history
        (field_id, stocklist_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.stocklist_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_stocklist_field_values_text_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklist_field_values_text
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklist_field_values_text_history();

ALTER TABLE stocklists.stocklist_field_values_int_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_stocklist_field_values_int_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  stocklists.stocklist_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 stocklists.stocklist_field_values_int_history
        (field_id, stocklist_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.stocklist_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_stocklist_field_values_int_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklist_field_values_int
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklist_field_values_int_history();

ALTER TABLE stocklists.stocklist_field_values_numeric_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_stocklist_field_values_numeric_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  stocklists.stocklist_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 stocklists.stocklist_field_values_numeric_history
        (field_id, stocklist_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.stocklist_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_stocklist_field_values_numeric_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklist_field_values_numeric
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklist_field_values_numeric_history();

ALTER TABLE stocklists.stocklist_field_values_date_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_stocklist_field_values_date_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  stocklists.stocklist_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 stocklists.stocklist_field_values_date_history
        (field_id, stocklist_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.stocklist_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_stocklist_field_values_date_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklist_field_values_date
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklist_field_values_date_history();

ALTER TABLE stocklists.stocklist_field_values_boolean_history
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE';

CREATE FUNCTION public.fn_stocklist_field_values_boolean_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  stocklists.stocklist_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 stocklists.stocklist_field_values_boolean_history
        (field_id, stocklist_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.stocklist_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_stocklist_field_values_boolean_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklist_field_values_boolean
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklist_field_values_boolean_history();
