Task S2T12: Create Daily Activity Seed Function¶

CREATE OR REPLACE FUNCTION stats.fn_seed_daily_activity(
  p_period tstzrange DEFAULT NULL
) RETURNS BIGINT LANGUAGE plpgsql AS $$
DECLARE
  v_rows BIGINT := 0;
BEGIN

  -- ============================================================
  -- Seed daily_activity from gk_moves
  -- ============================================================
  INSERT INTO stats.daily_activity (
    activity_date,
    total_moves,
    drops,
    grabs,
    comments,
    sees,
    archives,
    dips,
    km_contributed
  )
  SELECT
    moved_on_datetime::date         AS activity_date,
    COUNT(*)                        AS total_moves,
    COUNT(*) FILTER (WHERE move_type = 0) AS drops,
    COUNT(*) FILTER (WHERE move_type = 1) AS grabs,
    COUNT(*) FILTER (WHERE move_type = 2) AS comments,
    COUNT(*) FILTER (WHERE move_type = 3) AS sees,
    COUNT(*) FILTER (WHERE move_type = 4) AS archives,
    COUNT(*) FILTER (WHERE move_type = 5) AS dips,
    COALESCE(SUM(km_distance), 0)::NUMERIC(15,3) AS km_contributed
  FROM geokrety.gk_moves
  WHERE (p_period IS NULL OR moved_on_datetime <@ p_period)
  GROUP BY moved_on_datetime::date
  ON CONFLICT (activity_date) DO UPDATE SET
    total_moves    = EXCLUDED.total_moves,
    drops          = EXCLUDED.drops,
    grabs          = EXCLUDED.grabs,
    comments       = EXCLUDED.comments,
    sees           = EXCLUDED.sees,
    archives       = EXCLUDED.archives,
    dips           = EXCLUDED.dips,
    km_contributed = EXCLUDED.km_contributed;

  GET DIAGNOSTICS v_rows = ROW_COUNT;

  -- ============================================================
  -- Seed daily_activity from gk_geokrety (gk_created)
  -- ============================================================
  INSERT INTO stats.daily_activity (activity_date, gk_created)
  SELECT
    created_on_datetime::date AS activity_date,
    COUNT(*) AS gk_created
  FROM geokrety.gk_geokrety
  WHERE (p_period IS NULL OR created_on_datetime <@ p_period)
  GROUP BY created_on_datetime::date
  ON CONFLICT (activity_date) DO UPDATE SET
    gk_created = EXCLUDED.gk_created;

  -- ============================================================
  -- Seed daily_activity from gk_pictures
  -- ============================================================
  INSERT INTO stats.daily_activity (
    activity_date,
    pictures_uploaded_total,
    pictures_uploaded_avatar,
    pictures_uploaded_move,
    pictures_uploaded_user
  )
  SELECT
    created_on_datetime::date AS activity_date,
    COUNT(*) AS pictures_uploaded_total,
    COUNT(*) FILTER (WHERE type = 0) AS pictures_uploaded_avatar,
    COUNT(*) FILTER (WHERE type = 1) AS pictures_uploaded_move,
    COUNT(*) FILTER (WHERE type = 2) AS pictures_uploaded_user
  FROM geokrety.gk_pictures
  WHERE (p_period IS NULL OR created_on_datetime <@ p_period)
  GROUP BY created_on_datetime::date
  ON CONFLICT (activity_date) DO UPDATE SET
    pictures_uploaded_total  = EXCLUDED.pictures_uploaded_total,
    pictures_uploaded_avatar = EXCLUDED.pictures_uploaded_avatar,
    pictures_uploaded_move   = EXCLUDED.pictures_uploaded_move,
    pictures_uploaded_user   = EXCLUDED.pictures_uploaded_user;

  -- ============================================================
  -- Seed daily_activity from gk_users (users_registered)
  -- ============================================================
  INSERT INTO stats.daily_activity (activity_date, users_registered)
  SELECT
    joined_on_datetime::date AS activity_date,
    COUNT(*) AS users_registered
  FROM geokrety.gk_users
  WHERE (p_period IS NULL OR joined_on_datetime <@ p_period)
  GROUP BY joined_on_datetime::date
  ON CONFLICT (activity_date) DO UPDATE SET
    users_registered = EXCLUDED.users_registered;

  -- ============================================================
  -- Seed daily_active_users from gk_moves (unique authors per day)
  -- ============================================================
  INSERT INTO stats.daily_active_users (activity_date, user_id)
  SELECT DISTINCT
    moved_on_datetime::date AS activity_date,
    author
  FROM geokrety.gk_moves
  WHERE author IS NOT NULL
    AND (p_period IS NULL OR moved_on_datetime <@ p_period)
  ON CONFLICT (activity_date, user_id) DO NOTHING;

  RAISE NOTICE 'Daily activity seed completed: % rows in daily_activity affected', v_rows;
  RETURN v_rows;
END;
$$;

COMMENT ON FUNCTION stats.fn_seed_daily_activity(tstzrange) IS 'Idempotent backfill of daily_activity and daily_active_users from source tables; p_period limits to a date range; pass NULL to seed all history';

-- Full historical backfill (run once during Sprint 6)
SELECT stats.fn_seed_daily_activity(NULL);

-- Seed only January 2026
SELECT stats.fn_seed_daily_activity('[2026-01-01, 2026-02-01)'::tstzrange);

-- Refresh last 7 days (incremental refresh pattern)
SELECT stats.fn_seed_daily_activity(
  tstzrange(
    (CURRENT_DATE - INTERVAL '7 days')::timestamptz,
    CURRENT_TIMESTAMP
  )
);

-- Verify seed outputs match source table
SELECT
  da.activity_date,
  da.total_moves AS seeded_total,
  src.move_count AS source_total
FROM stats.daily_activity da
JOIN (
  SELECT moved_on_datetime::date AS day, COUNT(*) AS move_count
  FROM geokrety.gk_moves GROUP BY 1
) src ON src.day = da.activity_date
WHERE da.total_moves != src.move_count
LIMIT 10;
-- Zero rows means seed is accurate