-- 017_entity_history_triggers.sql
-- Entity history capture via DB triggers, phase 2
-- (docs/2026-07-19-entity-history-triggers.md).
--
-- Upgrades accounts.accounts_history / stocklists.stocklists_history to the
-- projects_history shape (history_id / history_action / history_datetime /
-- history_user), then puts history triggers on all three entity tables so every
-- write path — editor saves, map/geometry saves, create endpoints, manual SQL —
-- is captured atomically with the triggering statement.
--
-- projects.projects already has trg_projects_history (live since the baseline,
-- executing public.fn_projects_history()); its function is REPLACEd here to add
-- no-op suppression and GUC actor attribution. The trigger itself is untouched.
-- Accounts/stocklists get new functions and triggers following the same pattern.
--
-- 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, else
-- NULL. A malformed setting must degrade to the fallback, never abort the write.
--
-- No-op suppression: an UPDATE that changes nothing except modified_datetime /
-- modified_user (e.g. journal-note saves) inserts no history row.
--
-- The companion PHP change (same change set) removes the application-side
-- history inserts from project_save.php / account_save.php / stocklist_save.php.
-- Apply this migration before exercising the app on the updated code: the old
-- account/stocklist PHP wrote history positionally (insert … select *) and
-- breaks against the upgraded tables, while the new code writes no history
-- until the triggers exist.

-- ── 1. accounts.accounts_history: shape upgrade ─────────────────────────────

-- Stray identity default left over from the table's creation: history rows must
-- carry the source row's account_id, never generate their own.
ALTER TABLE accounts.accounts_history ALTER COLUMN account_id DROP DEFAULT;
DROP SEQUENCE accounts.accounts_history_account_id_seq;

ALTER TABLE accounts.accounts_history
    ADD COLUMN history_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE',
    ADD COLUMN history_datetime timestamp without time zone NOT NULL DEFAULT now(),
    ADD COLUMN history_user integer;

-- Backfill: the old insert…select * snapshots copied modified_* verbatim, so
-- they are the best available proxies for when/who.
UPDATE accounts.accounts_history
SET history_datetime = coalesce(modified_datetime, history_datetime),
    history_user     = modified_user;

-- Existing rows were all UPDATE snapshots; from here the trigger supplies the
-- action explicitly (matches projects_history: NOT NULL, no default).
ALTER TABLE accounts.accounts_history ALTER COLUMN history_action DROP DEFAULT;

-- ── 2. stocklists.stocklists_history: identical upgrade ─────────────────────

ALTER TABLE stocklists.stocklists_history ALTER COLUMN stocklist_id DROP DEFAULT;
DROP SEQUENCE stocklists.stocklists_history_stocklist_id_seq;

ALTER TABLE stocklists.stocklists_history
    ADD COLUMN history_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ADD COLUMN history_action character varying(10) NOT NULL DEFAULT 'UPDATE',
    ADD COLUMN history_datetime timestamp without time zone NOT NULL DEFAULT now(),
    ADD COLUMN history_user integer;

UPDATE stocklists.stocklists_history
SET history_datetime = coalesce(modified_datetime, history_datetime),
    history_user     = modified_user;

ALTER TABLE stocklists.stocklists_history ALTER COLUMN history_action DROP DEFAULT;

-- ── 3. projects: replace fn_projects_history() in place ─────────────────────
-- Existing body (baseline) has no no-op suppression and attributes only from
-- modified_user. trg_projects_history keeps pointing here.

CREATE OR REPLACE FUNCTION public.fn_projects_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    projects.projects;
    v_user   integer;
BEGIN
    -- No-op suppression: nothing meaningful changed (modified_* always bumps
    -- via update_modified_fields_trigger, so it is excluded from the compare)
    IF TG_OP = 'UPDATE'
       AND NEW.project_id        IS NOT DISTINCT FROM OLD.project_id
       AND NEW.parent_project_id IS NOT DISTINCT FROM OLD.parent_project_id
       AND NEW.project_name      IS NOT DISTINCT FROM OLD.project_name
       AND NEW.project_status_id IS NOT DISTINCT FROM OLD.project_status_id
       AND NEW.project_manager   IS NOT DISTINCT FROM OLD.project_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
    THEN
        RETURN NEW;
    END IF;

    v_action := TG_OP;

    -- For DELETE, capture the old row; otherwise capture the new row
    IF TG_OP = 'DELETE' THEN
        v_row := OLD;
    ELSE
        v_row := NEW;
    END IF;

    -- Actor: app.user_id GUC (set per request by www/fn/db.php), falling back
    -- to the row's modified_user; malformed values degrade, never abort
    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 projects.projects_history (
        project_id,
        parent_project_id,
        project_name,
        project_status_id,
        project_manager,
        geom,
        created_datetime,
        created_user,
        modified_datetime,
        modified_user,
        company_id,
        account_id,
        stocklist_id,
        cover_image_url,
        history_action,
        history_datetime,
        history_user
    ) VALUES (
        v_row.project_id,
        v_row.parent_project_id,
        v_row.project_name,
        v_row.project_status_id,
        v_row.project_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_action,
        now(),
        v_user
    );

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

-- ── 4. accounts: history function + trigger ─────────────────────────────────

CREATE FUNCTION public.fn_accounts_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    accounts.accounts;
    v_user   integer;
BEGIN
    IF TG_OP = 'UPDATE'
       AND NEW.account_id        IS NOT DISTINCT FROM OLD.account_id
       AND NEW.parent_account_id IS NOT DISTINCT FROM OLD.parent_account_id
       AND NEW.account_name      IS NOT DISTINCT FROM OLD.account_name
       AND NEW.account_status_id IS NOT DISTINCT FROM OLD.account_status_id
       AND NEW.account_manager   IS NOT DISTINCT FROM OLD.account_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
    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 accounts.accounts_history (
        account_id,
        parent_account_id,
        account_name,
        account_status_id,
        account_manager,
        geom,
        created_datetime,
        created_user,
        modified_datetime,
        modified_user,
        company_id,
        history_action,
        history_datetime,
        history_user
    ) VALUES (
        v_row.account_id,
        v_row.parent_account_id,
        v_row.account_name,
        v_row.account_status_id,
        v_row.account_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_action,
        now(),
        v_user
    );

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

CREATE TRIGGER trg_accounts_history
    AFTER INSERT OR DELETE OR UPDATE ON accounts.accounts
    FOR EACH ROW EXECUTE FUNCTION public.fn_accounts_history();

-- ── 5. stocklists: history function + trigger ───────────────────────────────

CREATE FUNCTION public.fn_stocklists_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    stocklists.stocklists;
    v_user   integer;
BEGIN
    IF TG_OP = 'UPDATE'
       AND NEW.stocklist_id        IS NOT DISTINCT FROM OLD.stocklist_id
       AND NEW.parent_stocklist_id IS NOT DISTINCT FROM OLD.parent_stocklist_id
       AND NEW.stocklist_name      IS NOT DISTINCT FROM OLD.stocklist_name
       AND NEW.stocklist_status_id IS NOT DISTINCT FROM OLD.stocklist_status_id
       AND NEW.stocklist_manager   IS NOT DISTINCT FROM OLD.stocklist_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
    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 stocklists.stocklists_history (
        stocklist_id,
        parent_stocklist_id,
        stocklist_name,
        stocklist_status_id,
        stocklist_manager,
        geom,
        created_datetime,
        created_user,
        modified_datetime,
        modified_user,
        company_id,
        account_id,
        history_action,
        history_datetime,
        history_user
    ) VALUES (
        v_row.stocklist_id,
        v_row.parent_stocklist_id,
        v_row.stocklist_name,
        v_row.stocklist_status_id,
        v_row.stocklist_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_action,
        now(),
        v_user
    );

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

CREATE TRIGGER trg_stocklists_history
    AFTER INSERT OR DELETE OR UPDATE ON stocklists.stocklists
    FOR EACH ROW EXECUTE FUNCTION public.fn_stocklists_history();
