Task S4T08: Waypoint Resolution & Cache Visit Trigger¶

<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class CreateWaypointCacheTrigger extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<'SQL'
CREATE OR REPLACE FUNCTION geokrety.fn_gk_moves_waypoint_cache()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
DECLARE
  v_waypoint_id  BIGINT;
  v_waypoint_code TEXT;
BEGIN
  IF TG_OP IN ('INSERT', 'UPDATE') AND NEW.move_type <> 2 AND NEW.waypoint IS NOT NULL THEN
    v_waypoint_code := UPPER(NEW.waypoint);

    INSERT INTO stats.waypoints (waypoint_code, source, lat, lon, country, first_seen_at)
    VALUES (
      v_waypoint_code,
      'UK',
      NEW.lat,
      NEW.lon,
      CASE
        WHEN NEW.country IS NULL OR BTRIM(NEW.country) = '' THEN NULL
        ELSE UPPER(NEW.country)
      END,
      NEW.moved_on_datetime
    )
    ON CONFLICT (waypoint_code) DO UPDATE SET
      lat = COALESCE(stats.waypoints.lat, EXCLUDED.lat),
      lon = COALESCE(stats.waypoints.lon, EXCLUDED.lon),
      country = COALESCE(stats.waypoints.country, EXCLUDED.country),
      first_seen_at = LEAST(stats.waypoints.first_seen_at, EXCLUDED.first_seen_at)
    RETURNING id INTO v_waypoint_id;

    IF v_waypoint_id IS NULL THEN
      SELECT id INTO v_waypoint_id
      FROM stats.waypoints
      WHERE waypoint_code = v_waypoint_code;
    END IF;

    INSERT INTO stats.gk_cache_visits (gk_id, waypoint_id, visit_count, first_visited_at, last_visited_at)
    SELECT
      NEW.geokret_id,
      v_waypoint_id,
      COUNT(*)::BIGINT,
      MIN(m.moved_on_datetime),
      MAX(m.moved_on_datetime)
    FROM geokrety.gk_moves m
    WHERE m.geokret_id = NEW.geokret_id
      AND m.waypoint IS NOT NULL
      AND UPPER(m.waypoint) = v_waypoint_code
      AND m.move_type <> 2
    GROUP BY NEW.geokret_id
    ON CONFLICT (gk_id, waypoint_id) DO UPDATE SET
      visit_count = EXCLUDED.visit_count,
      first_visited_at = EXCLUDED.first_visited_at,
      last_visited_at = EXCLUDED.last_visited_at;

    IF NEW.author IS NOT NULL THEN
      INSERT INTO stats.user_cache_visits (user_id, waypoint_id, visit_count, first_visited_at, last_visited_at)
      SELECT
        NEW.author,
        v_waypoint_id,
        COUNT(*)::BIGINT,
        MIN(m.moved_on_datetime),
        MAX(m.moved_on_datetime)
      FROM geokrety.gk_moves m
      WHERE m.author = NEW.author
        AND m.waypoint IS NOT NULL
        AND UPPER(m.waypoint) = v_waypoint_code
        AND m.move_type <> 2
      GROUP BY NEW.author
      ON CONFLICT (user_id, waypoint_id) DO UPDATE SET
        visit_count = EXCLUDED.visit_count,
        first_visited_at = EXCLUDED.first_visited_at,
        last_visited_at = EXCLUDED.last_visited_at;
    END IF;
  END IF;

  IF TG_OP IN ('DELETE', 'UPDATE') AND OLD.move_type <> 2 AND OLD.waypoint IS NOT NULL THEN
    v_waypoint_code := UPPER(OLD.waypoint);

    SELECT id INTO v_waypoint_id
    FROM stats.waypoints
    WHERE waypoint_code = v_waypoint_code;

    IF v_waypoint_id IS NOT NULL THEN
      INSERT INTO stats.gk_cache_visits (gk_id, waypoint_id, visit_count, first_visited_at, last_visited_at)
      SELECT
        OLD.geokret_id,
        v_waypoint_id,
        COUNT(*)::BIGINT,
        MIN(m.moved_on_datetime),
        MAX(m.moved_on_datetime)
      FROM geokrety.gk_moves m
      WHERE m.geokret_id = OLD.geokret_id
        AND m.waypoint IS NOT NULL
        AND UPPER(m.waypoint) = v_waypoint_code
        AND m.move_type <> 2
      GROUP BY OLD.geokret_id
      ON CONFLICT (gk_id, waypoint_id) DO UPDATE SET
        visit_count = EXCLUDED.visit_count,
        first_visited_at = EXCLUDED.first_visited_at,
        last_visited_at = EXCLUDED.last_visited_at;

      DELETE FROM stats.gk_cache_visits
      WHERE gk_id = OLD.geokret_id
        AND waypoint_id = v_waypoint_id
        AND NOT EXISTS (
          SELECT 1
          FROM geokrety.gk_moves m
          WHERE m.geokret_id = OLD.geokret_id
            AND m.waypoint IS NOT NULL
            AND UPPER(m.waypoint) = v_waypoint_code
            AND m.move_type <> 2
        );

      IF OLD.author IS NOT NULL THEN
        INSERT INTO stats.user_cache_visits (user_id, waypoint_id, visit_count, first_visited_at, last_visited_at)
        SELECT
          OLD.author,
          v_waypoint_id,
          COUNT(*)::BIGINT,
          MIN(m.moved_on_datetime),
          MAX(m.moved_on_datetime)
        FROM geokrety.gk_moves m
        WHERE m.author = OLD.author
          AND m.waypoint IS NOT NULL
          AND UPPER(m.waypoint) = v_waypoint_code
          AND m.move_type <> 2
        GROUP BY OLD.author
        ON CONFLICT (user_id, waypoint_id) DO UPDATE SET
          visit_count = EXCLUDED.visit_count,
          first_visited_at = EXCLUDED.first_visited_at,
          last_visited_at = EXCLUDED.last_visited_at;

        DELETE FROM stats.user_cache_visits
        WHERE user_id = OLD.author
          AND waypoint_id = v_waypoint_id
          AND NOT EXISTS (
            SELECT 1
            FROM geokrety.gk_moves m
            WHERE m.author = OLD.author
              AND m.waypoint IS NOT NULL
              AND UPPER(m.waypoint) = v_waypoint_code
              AND m.move_type <> 2
          );
      END IF;
    END IF;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER tr_gk_moves_after_waypoint_visits
  AFTER INSERT OR UPDATE OR DELETE ON geokrety.gk_moves
  FOR EACH ROW EXECUTE FUNCTION geokrety.fn_gk_moves_waypoint_cache();
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<'SQL'
DROP TRIGGER IF EXISTS tr_gk_moves_after_waypoint_visits ON geokrety.gk_moves;
DROP FUNCTION IF EXISTS geokrety.fn_gk_moves_waypoint_cache();
SQL
        );
    }
}