-- 028_opportunity_history_triggers.sql
-- Opportunity module alignment, migration 5 of 5
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- History capture for the opportunity module: one trigger on the entity table and one
-- on each of the five EAV value tables, following migrations 017 and 018 exactly.
--
-- Requires 026 (the history tables exist).
--
-- WHY TRIGGERS AND NOT PHP. Every write path is captured — the editor save, the create
-- endpoint, opportunity_manage.php, manual SQL — atomically with the statement that
-- caused it. opportunity_save.php therefore writes no history of its own, which is what
-- the other three modules do since 017/018.
--
-- 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 must degrade to the fallback, never abort the write. Note this is
-- why opportunity_create.php's second `new PDO(...)` has to go (Phase 6): a connection
-- that never set the GUC attributes every create to the fallback.
--
-- NO-OP SUPPRESSION on the entity table: an UPDATE that changes nothing except
-- modified_datetime / modified_user writes no history row. modified_* is excluded from
-- the comparison because update_modified_fields_trigger (migration 025) always bumps
-- modified_datetime first, so including it would make every update look like a change.
-- On the value tables the same rule applies to `value`.
--
-- INSERTS ARE CAPTURED TOO, so a new opportunity gets a history row from the moment it
-- exists. Historic rows have none — the audit panel is empty for them until they are
-- next edited, which is expected (nothing was recorded before this migration).

-- ── 1. Entity table ────────────────────────────────────────────────────────

CREATE FUNCTION public.fn_opportunities_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    opportunity.opportunities;
    v_user   integer;
BEGIN
    IF TG_OP = 'UPDATE'
       AND NEW.opportunity_id        IS NOT DISTINCT FROM OLD.opportunity_id
       AND NEW.parent_opportunity_id IS NOT DISTINCT FROM OLD.parent_opportunity_id
       AND NEW.opportunity_name      IS NOT DISTINCT FROM OLD.opportunity_name
       AND NEW.opportunity_status_id IS NOT DISTINCT FROM OLD.opportunity_status_id
       AND NEW.opportunity_manager   IS NOT DISTINCT FROM OLD.opportunity_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;

    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 opportunity.opportunities_history (
        opportunity_id,
        parent_opportunity_id,
        opportunity_name,
        opportunity_status_id,
        opportunity_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.opportunity_id,
        v_row.parent_opportunity_id,
        v_row.opportunity_name,
        v_row.opportunity_status_id,
        v_row.opportunity_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;
$$;

CREATE TRIGGER trg_opportunities_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunities
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunities_history();

-- ── 2. EAV value tables ────────────────────────────────────────────────────
--
-- One function per table rather than one shared function reading TG_ARGV, matching
-- 018. The row variable is declared as the table's own composite type, which a shared
-- function could not do.

-- text ----------------------------------------------------------------------
CREATE FUNCTION public.fn_opportunity_field_values_text_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  opportunity.opportunity_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 opportunity.opportunity_field_values_text_history
        (field_id, opportunity_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.opportunity_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_opportunity_field_values_text_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunity_field_values_text
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunity_field_values_text_history();

-- int -----------------------------------------------------------------------
CREATE FUNCTION public.fn_opportunity_field_values_int_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  opportunity.opportunity_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 opportunity.opportunity_field_values_int_history
        (field_id, opportunity_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.opportunity_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_opportunity_field_values_int_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunity_field_values_int
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunity_field_values_int_history();

-- numeric -------------------------------------------------------------------
CREATE FUNCTION public.fn_opportunity_field_values_numeric_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  opportunity.opportunity_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 opportunity.opportunity_field_values_numeric_history
        (field_id, opportunity_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.opportunity_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_opportunity_field_values_numeric_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunity_field_values_numeric
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunity_field_values_numeric_history();

-- date ----------------------------------------------------------------------
CREATE FUNCTION public.fn_opportunity_field_values_date_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  opportunity.opportunity_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 opportunity.opportunity_field_values_date_history
        (field_id, opportunity_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.opportunity_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_opportunity_field_values_date_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunity_field_values_date
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunity_field_values_date_history();

-- boolean -------------------------------------------------------------------
CREATE FUNCTION public.fn_opportunity_field_values_boolean_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_row  opportunity.opportunity_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 opportunity.opportunity_field_values_boolean_history
        (field_id, opportunity_id, value, record_datetime, record_user, history_action)
    VALUES
        (v_row.field_id, v_row.opportunity_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_opportunity_field_values_boolean_history
    AFTER INSERT OR DELETE OR UPDATE ON opportunity.opportunity_field_values_boolean
    FOR EACH ROW EXECUTE FUNCTION public.fn_opportunity_field_values_boolean_history();
