Task S3T07: Create Country Snapshot/Seed Functions¶

-- ============================================================
-- Snapshot 1: Seed country_daily_stats from gk_moves
-- ============================================================
CREATE OR REPLACE FUNCTION stats.fn_snapshot_daily_country_stats(
  p_period daterange DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
  v_count BIGINT := 0;
BEGIN
  INSERT INTO stats.country_daily_stats (
    stats_date, country_code,
    moves_count, drops, grabs, comments, sees, archives, dips,
    unique_users, unique_gks, km_contributed
  )
  SELECT
    m.moved_on_datetime::date AS stats_date,
    m.country AS country_code,
    COUNT(*) AS moves_count,
    COUNT(*) FILTER (WHERE m.move_type = 0) AS drops,
    COUNT(*) FILTER (WHERE m.move_type = 1) AS grabs,
    COUNT(*) FILTER (WHERE m.move_type = 2) AS comments,
    COUNT(*) FILTER (WHERE m.move_type = 3) AS sees,
    COUNT(*) FILTER (WHERE m.move_type = 4) AS archives,
    COUNT(*) FILTER (WHERE m.move_type = 5) AS dips,
    COUNT(DISTINCT m.author) FILTER (WHERE m.author IS NOT NULL) AS unique_users,
    COUNT(DISTINCT m.geokret) AS unique_gks,
    COALESCE(SUM(m.km_distance), 0) AS km_contributed
  FROM geokrety.gk_moves m
  WHERE m.country IS NOT NULL
    AND (p_period IS NULL OR m.moved_on_datetime::date <@ p_period)
  GROUP BY m.moved_on_datetime::date, m.country
  ON CONFLICT (stats_date, country_code) DO UPDATE SET
    moves_count = EXCLUDED.moves_count,
    drops = EXCLUDED.drops,
    grabs = EXCLUDED.grabs,
    comments = EXCLUDED.comments,
    sees = EXCLUDED.sees,
    archives = EXCLUDED.archives,
    dips = EXCLUDED.dips,
    unique_users = EXCLUDED.unique_users,
    unique_gks = EXCLUDED.unique_gks,
    km_contributed = EXCLUDED.km_contributed;

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

COMMENT ON FUNCTION stats.fn_snapshot_daily_country_stats IS 'Seeds country_daily_stats from gk_moves. Idempotent via ON CONFLICT DO UPDATE. Optional p_period limits date range.';

-- ============================================================
-- Snapshot 2: Seed user_countries from gk_moves
-- ============================================================
CREATE OR REPLACE FUNCTION stats.fn_snapshot_user_country_stats(
  p_period daterange DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
  v_count BIGINT := 0;
BEGIN
  INSERT INTO stats.user_countries (
    user_id, country_code, move_count, first_visit, last_visit
  )
  SELECT
    m.author AS user_id,
    m.country AS country_code,
    COUNT(*) AS move_count,
    MIN(m.moved_on_datetime) AS first_visit,
    MAX(m.moved_on_datetime) AS last_visit
  FROM geokrety.gk_moves m
  WHERE m.country IS NOT NULL
    AND m.author IS NOT NULL
    AND (p_period IS NULL OR m.moved_on_datetime::date <@ p_period)
  GROUP BY m.author, m.country
  ON CONFLICT (user_id, country_code) DO UPDATE SET
    move_count = EXCLUDED.move_count,
    first_visit = LEAST(stats.user_countries.first_visit, EXCLUDED.first_visit),
    last_visit = GREATEST(stats.user_countries.last_visit, EXCLUDED.last_visit);

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

COMMENT ON FUNCTION stats.fn_snapshot_user_country_stats IS 'Seeds user_countries from gk_moves. Idempotent via ON CONFLICT DO UPDATE. Optional p_period limits date range.';

-- ============================================================
-- Snapshot 3: Seed gk_countries_visited from gk_moves
-- ============================================================
CREATE OR REPLACE FUNCTION stats.fn_snapshot_gk_country_stats(
  p_period daterange DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
  v_count BIGINT := 0;
BEGIN
  INSERT INTO stats.gk_countries_visited (
    geokrety_id, country_code, first_visited_at, first_move_id, move_count
  )
  SELECT
    sub.geokret AS geokrety_id,
    sub.country AS country_code,
    sub.first_visited_at,
    sub.first_move_id,
    sub.move_count
  FROM (
    SELECT
      m.geokret,
      m.country,
      MIN(m.moved_on_datetime) AS first_visited_at,
      (array_agg(m.id ORDER BY m.moved_on_datetime ASC, m.id ASC))[1] AS first_move_id,
      COUNT(*) AS move_count
    FROM geokrety.gk_moves m
    WHERE m.country IS NOT NULL
      AND (p_period IS NULL OR m.moved_on_datetime::date <@ p_period)
    GROUP BY m.geokret, m.country
  ) sub
  ON CONFLICT (geokrety_id, country_code) DO UPDATE SET
    move_count = EXCLUDED.move_count,
    first_visited_at = LEAST(stats.gk_countries_visited.first_visited_at, EXCLUDED.first_visited_at),
    first_move_id = CASE
      WHEN EXCLUDED.first_visited_at < stats.gk_countries_visited.first_visited_at THEN EXCLUDED.first_move_id
      ELSE stats.gk_countries_visited.first_move_id
    END;

  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

COMMENT ON FUNCTION stats.fn_snapshot_gk_country_stats IS 'Seeds gk_countries_visited from gk_moves. Idempotent via ON CONFLICT DO UPDATE. Optional p_period limits date range.';

-- Full snapshot of all country daily stats (all time)
SELECT stats.fn_snapshot_daily_country_stats(NULL);

-- Snapshot only for January 2025
SELECT stats.fn_snapshot_daily_country_stats('[2025-01-01, 2025-02-01)'::daterange);

-- Snapshot user country stats for all time
SELECT stats.fn_snapshot_user_country_stats(NULL);

-- Snapshot GK country stats for a specific quarter
SELECT stats.fn_snapshot_gk_country_stats('[2025-01-01, 2025-04-01)'::daterange);

-- Verify idempotency: run twice, check row counts match
SELECT stats.fn_snapshot_daily_country_stats('[2025-06-01, 2025-07-01)'::daterange) AS first_run;
SELECT stats.fn_snapshot_daily_country_stats('[2025-06-01, 2025-07-01)'::daterange) AS second_run;
-- Both return same count; ON CONFLICT DO UPDATE overwrites identical values