Task S3T06: Create Country History Trigger Function + Attach¶

CREATE OR REPLACE FUNCTION geokrety.fn_gk_moves_country_history()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    DELETE FROM stats.gk_country_history
    WHERE geokrety_id = OLD.geokret;

    INSERT INTO stats.gk_country_history (
      geokrety_id,
      country_code,
      arrived_at,
      departed_at,
      move_id
    )
    WITH ordered_moves AS (
      SELECT
        m.id,
        m.geokret,
        m.country,
        m.moved_on_datetime,
        LAG(m.country) OVER (
          PARTITION BY m.geokret
          ORDER BY m.moved_on_datetime, m.id
        ) AS prev_country
      FROM geokrety.gk_moves m
      WHERE m.geokret = OLD.geokret
        AND m.country IS NOT NULL
        AND m.move_type IN (0, 1, 3, 5)
    ),
    transitions AS (
      SELECT
        id,
        geokret,
        country,
        moved_on_datetime,
        LEAD(moved_on_datetime) OVER (ORDER BY moved_on_datetime, id) AS next_arrived_at
      FROM ordered_moves
      WHERE prev_country IS DISTINCT FROM country
    )
    SELECT
      geokret,
      country,
      moved_on_datetime,
      next_arrived_at,
      id
    FROM transitions;
  END IF;

  IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.geokret <> OLD.geokret) THEN
    DELETE FROM stats.gk_country_history
    WHERE geokrety_id = NEW.geokret;

    INSERT INTO stats.gk_country_history (
      geokrety_id,
      country_code,
      arrived_at,
      departed_at,
      move_id
    )
    WITH ordered_moves AS (
      SELECT
        m.id,
        m.geokret,
        m.country,
        m.moved_on_datetime,
        LAG(m.country) OVER (
          PARTITION BY m.geokret
          ORDER BY m.moved_on_datetime, m.id
        ) AS prev_country
      FROM geokrety.gk_moves m
      WHERE m.geokret = NEW.geokret
        AND m.country IS NOT NULL
        AND m.move_type IN (0, 1, 3, 5)
    ),
    transitions AS (
      SELECT
        id,
        geokret,
        country,
        moved_on_datetime,
        LEAD(moved_on_datetime) OVER (ORDER BY moved_on_datetime, id) AS next_arrived_at
      FROM ordered_moves
      WHERE prev_country IS DISTINCT FROM country
    )
    SELECT
      geokret,
      country,
      moved_on_datetime,
      next_arrived_at,
      id
    FROM transitions;
  END IF;

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

DROP TRIGGER IF EXISTS tr_gk_moves_after_country_history ON geokrety.gk_moves;
CREATE TRIGGER tr_gk_moves_after_country_history
  AFTER INSERT OR UPDATE OR DELETE ON geokrety.gk_moves
  FOR EACH ROW EXECUTE FUNCTION geokrety.fn_gk_moves_country_history();

-- Verify trigger exists
SELECT tgname FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'geokrety' AND c.relname = 'gk_moves'
  AND t.tgname = 'tr_gk_moves_after_country_history';

-- Verify function exists
SELECT proname FROM pg_proc
WHERE proname = 'fn_gk_moves_country_history'
  AND pronamespace = 'geokrety'::regnamespace;

-- Test scenario: GK travels PL → DE → CZ
-- After first move in PL: one open interval (PL, arrived, NULL)
-- After move in DE: PL closed, DE opens
-- After move in CZ: DE closed, CZ opens
SELECT country_code, arrived_at, departed_at
FROM stats.gk_country_history
WHERE geokrety_id = 1
ORDER BY arrived_at;