projects-jenz/discord_verificiation/playtime-frontend/lib/queries.ts

import { query } from '@/lib/db';

// map_end_dt was dropped from the schema — it was redundant with (and
// occasionally got out of sync with) the next row's map_start_dt, since a
// map's end is definitionally when the next one begins. This subquery
// derives the same information: NULL for the currently-running map (no
// later row exists yet), otherwise the next map's start time.
const NEXT_MAP_START = (alias: string) =>
  `(SELECT MIN(m2.map_start_dt) FROM playtime_display_map_history m2 WHERE m2.map_start_dt > ${alias}.map_start_dt)`;

// Tickrate changes sometimes require a very quick map restart, which
// produces a second map_history row for the same map seconds later. Treat
// anything under 2 minutes as such an artifact rather than a real play —
// applied everywhere a map_history row is being counted/listed as an
// actual occurrence. The currently-running map (no next row yet) is always
// included since we can't know its eventual duration yet.
const REAL_MAP_PERIOD = `(
  ${NEXT_MAP_START('m')} IS NULL
  OR TIMESTAMPDIFF(MINUTE, m.map_start_dt, ${NEXT_MAP_START('m')}) >= 2
)`;

export interface PlayerRecord {
  steamid: string;
  current_name: string;
  current_country: string | null;
  player_tier: number;
  first_seen: string;
  last_seen: string;
}

export interface NameHistoryEntry {
  player_name: string;
  first_seen: string;
  last_seen: string;
}

export interface SessionEntry {
  session_id: number;
  session_start_dt: string;
  session_end_dt: string | null;
  session_active_minutes: number | null;
  maps_played: string | null; // comma-separated map names overlapping this session
}

export async function getPlayer(steamid: string): Promise<PlayerRecord | null> {
  const rows = await query<PlayerRecord>(
    `SELECT steamid, current_name, current_country, player_tier, first_seen, last_seen
     FROM playtime_display_players
     WHERE steamid = ?`,
    [steamid],
  );
  return rows[0] ?? null;
}

export async function getNameHistory(steamid: string): Promise<NameHistoryEntry[]> {
  return query<NameHistoryEntry>(
    `SELECT player_name, first_seen, last_seen
     FROM playtime_display_name_history
     WHERE steamid = ?
     ORDER BY last_seen DESC`,
    [steamid],
  );
}

export async function getSessions(steamid: string, limit = 100): Promise<SessionEntry[]> {
  // The maps_played subquery finds every map_history period that overlaps
  // this session's [start, end] window (same overlap logic used elsewhere
  // in the app), and concatenates the names. A session can span more than
  // one map if the player stayed connected through a map change.
  return query<SessionEntry>(
    `SELECT
       s.session_id,
       s.session_start_dt,
       s.session_end_dt,
       s.session_active_minutes,
       (
         SELECT GROUP_CONCAT(DISTINCT m.map_name ORDER BY m.map_start_dt SEPARATOR ', ')
         FROM playtime_display_map_history m
         WHERE m.map_start_dt < COALESCE(s.session_end_dt, NOW())
           AND COALESCE(${NEXT_MAP_START('m')}, NOW()) > s.session_start_dt
           AND ${REAL_MAP_PERIOD}
       ) AS maps_played
     FROM playtime_display_sessions s
     WHERE s.steamid = ?
     ORDER BY s.session_start_dt DESC
     LIMIT ?`,
    [steamid, limit],
  );
}

export interface MapPopulationPoint {
  map_id: number;
  map_name: string;
  map_start_dt: string;
  map_end_dt: string | null;
  total_players: number;
}

export async function getMapPopulationSeries(limit = 40, before?: string): Promise<MapPopulationPoint[]> {
  // Total DISTINCT players who were connected at any point during each map's
  // full session (not just at its start) — one data point per map. Fetched
  // most-recent-first (for the LIMIT), then the caller should reverse it
  // back to chronological order for display.
  // `before` (a DB-formatted datetime string) lets the caller page further
  // back into history than the initial batch, for pan-to-load-more charts.
  const rows = await query<MapPopulationPoint>(
    `SELECT m.map_id, m.map_name, m.map_start_dt, ${NEXT_MAP_START('m')} AS map_end_dt,
       (SELECT COUNT(DISTINCT s.steamid) FROM playtime_display_sessions s
        WHERE s.session_start_dt < COALESCE(${NEXT_MAP_START('m')}, NOW())
          AND (s.session_end_dt IS NULL OR s.session_end_dt > m.map_start_dt)
       ) AS total_players
     FROM playtime_display_map_history m
     WHERE ${REAL_MAP_PERIOD}
       ${before ? 'AND m.map_start_dt < ?' : ''}
     ORDER BY m.map_start_dt DESC
     LIMIT ?`,
    before ? [before, limit] : [limit],
  );
  return rows.reverse();
}

export interface CountrySummary {
  current_country: string | null; // null represents the "Unknown" bucket
  player_count: number;
  total_minutes: number;
  avg_minutes: number;
}

export async function getCountriesSummary(): Promise<CountrySummary[]> {
  // "Unknown" covers both NULL and '' — the actual sentinel written by the
  // plugin when GeoipCode2 can't resolve a country (private/local IP, GeoIP
  // miss, etc.) is an empty string, not SQL NULL, since the upsert always
  // writes some value rather than leaving the column untouched. NULL is
  // also handled in case any row ever ends up there some other way (e.g.
  // the column's own DEFAULT).
  const UNKNOWN = `(p.current_country IS NULL OR p.current_country = '')`;
  const KNOWN = `(p.current_country IS NOT NULL AND p.current_country != '')`;
  const rows = await query<CountrySummary>(
    `SELECT * FROM (
       SELECT p.current_country, COUNT(*) AS player_count,
         SUM(pt.total_minutes) AS total_minutes,
         AVG(pt.total_minutes) AS avg_minutes
       FROM playtime_display_players p
       JOIN (
         SELECT p2.steamid,
           (SELECT COALESCE(s.session_end_playtime_minutes, s.session_start_playtime_minutes)
            FROM playtime_display_sessions s
            WHERE s.steamid = p2.steamid
            ORDER BY s.session_id DESC
            LIMIT 1) AS total_minutes
         FROM playtime_display_players p2
       ) pt ON pt.steamid = p.steamid
       WHERE ${KNOWN}
       GROUP BY p.current_country

       UNION ALL

       SELECT NULL AS current_country, COUNT(*) AS player_count,
         SUM(pt.total_minutes) AS total_minutes,
         AVG(pt.total_minutes) AS avg_minutes
       FROM playtime_display_players p
       JOIN (
         SELECT p2.steamid,
           (SELECT COALESCE(s.session_end_playtime_minutes, s.session_start_playtime_minutes)
            FROM playtime_display_sessions s
            WHERE s.steamid = p2.steamid
            ORDER BY s.session_id DESC
            LIMIT 1) AS total_minutes
         FROM playtime_display_players p2
       ) pt ON pt.steamid = p.steamid
       WHERE ${UNKNOWN}
     ) t
     ORDER BY player_count DESC, current_country IS NULL, current_country ASC`,
  );
  // Skip the Unknown bucket entirely if it's empty, rather than showing a
  // dead "0 players" row.
  return rows.filter((r) => r.current_country !== null || r.player_count > 0);
}

export interface CountryPlayer {
  steamid: string;
  current_name: string;
  last_seen: string;
  total_minutes: number | null;
}

export async function getPlayersByCountry(
  countryCode: string | null,
  sort: 'playtime' | 'recent',
): Promise<CountryPlayer[]> {
  const orderBy = sort === 'playtime' ? 'total_minutes DESC' : 'p.last_seen DESC';
  return query<CountryPlayer>(
    `SELECT p.steamid, p.current_name, p.last_seen,
       (SELECT COALESCE(session_end_playtime_minutes, session_start_playtime_minutes)
        FROM playtime_display_sessions s
        WHERE s.steamid = p.steamid
        ORDER BY s.session_id DESC
        LIMIT 1
       ) AS total_minutes
     FROM playtime_display_players p
     WHERE ${countryCode === null ? "(p.current_country IS NULL OR p.current_country = '')" : 'p.current_country = ?'}
     ORDER BY ${orderBy}
     LIMIT 200`,
    countryCode === null ? [] : [countryCode],
  );
}

export interface MapSummary {
  map_name: string;
  times_played: number;
  last_played: string;
  total_minutes: number;
}

export interface MapPeriod {
  map_id: number;
  map_name: string;
  map_start_dt: string;
  map_end_dt: string | null;
  player_count: number;
}

export interface MapPresentPlayer {
  steamid: string;
  current_name: string;
  current_country: string | null;
  session_start_dt: string;
  session_end_dt: string | null;
}

export interface MapVoteRow {
  vote_id: number;
  vote_time: string;
  result: string | null;
  steamid: string;
  current_name: string;
  current_country: string | null;
  vote_choice: string;
  vote_weight: number;
}

export async function searchMapNames(q: string, limit = 50, offset = 0): Promise<MapSummary[]> {
  const like = `%${q}%`;
  return query<MapSummary>(
    `SELECT m.map_name, COUNT(*) AS times_played, MAX(m.map_start_dt) AS last_played,
       SUM(TIMESTAMPDIFF(MINUTE, m.map_start_dt, COALESCE(${NEXT_MAP_START('m')}, NOW()))) AS total_minutes
     FROM playtime_display_map_history m
     WHERE m.map_name LIKE ?
       AND ${REAL_MAP_PERIOD}
     GROUP BY m.map_name
     ORDER BY last_played DESC
     LIMIT ? OFFSET ?`,
    [like, limit, offset],
  );
}

export async function getMapPeriods(mapName: string): Promise<MapPeriod[]> {
  return query<MapPeriod>(
    `SELECT m.map_id, m.map_name, m.map_start_dt, ${NEXT_MAP_START('m')} AS map_end_dt,
       (SELECT COUNT(DISTINCT s.steamid) FROM playtime_display_sessions s
        WHERE s.session_start_dt < COALESCE(${NEXT_MAP_START('m')}, NOW())
          AND (s.session_end_dt IS NULL OR s.session_end_dt > m.map_start_dt)
       ) AS player_count
     FROM playtime_display_map_history m
     WHERE m.map_name = ?
       AND ${REAL_MAP_PERIOD}
     ORDER BY m.map_start_dt DESC`,
    [mapName],
  );
}

export async function getMapPeriod(mapId: number): Promise<MapPeriod | null> {
  const rows = await query<MapPeriod>(
    `SELECT m.map_id, m.map_name, m.map_start_dt, ${NEXT_MAP_START('m')} AS map_end_dt,
       (SELECT COUNT(DISTINCT s.steamid) FROM playtime_display_sessions s
        WHERE s.session_start_dt < COALESCE(${NEXT_MAP_START('m')}, NOW())
          AND (s.session_end_dt IS NULL OR s.session_end_dt > m.map_start_dt)
       ) AS player_count
     FROM playtime_display_map_history m
     WHERE m.map_id = ?`,
    [mapId],
  );
  return rows[0] ?? null;
}

export async function getPlayersPresentDuringMap(mapId: number): Promise<MapPresentPlayer[]> {
  return query<MapPresentPlayer>(
    `SELECT s.steamid, COALESCE(p.current_name, s.player_name) AS current_name,
            p.current_country, s.session_start_dt, s.session_end_dt
     FROM playtime_display_sessions s
     LEFT JOIN playtime_display_players p ON p.steamid = s.steamid
     JOIN playtime_display_map_history m ON m.map_id = ?
     WHERE s.session_start_dt < COALESCE(${NEXT_MAP_START('m')}, NOW())
       AND (s.session_end_dt IS NULL OR s.session_end_dt > m.map_start_dt)
     ORDER BY s.session_start_dt`,
    [mapId],
  );
}

export async function getMapVotes(mapId: number): Promise<MapVoteRow[]> {
  return query<MapVoteRow>(
    `SELECT e.vote_id, e.vote_time, e.result,
            c.steamid, COALESCE(p.current_name, c.steamid) AS current_name, p.current_country,
            c.vote_choice, c.vote_weight
     FROM playtime_display_map_vote_events e
     JOIN playtime_display_map_vote_choices c ON c.vote_id = e.vote_id
     LEFT JOIN playtime_display_players p ON p.steamid = c.steamid
     WHERE e.map_id = ?
     ORDER BY e.vote_time, c.vote_weight DESC`,
    [mapId],
  );
}

export async function getAdjacentMaps(
  mapStartDt: string,
): Promise<{ prev: MapPeriod | null; next: MapPeriod | null }> {
  const [prevRows, nextRows] = await Promise.all([
    query<MapPeriod>(
      `SELECT m.map_id, m.map_name, m.map_start_dt, ${NEXT_MAP_START('m')} AS map_end_dt
       FROM playtime_display_map_history m
       WHERE m.map_start_dt < ?
         AND ${REAL_MAP_PERIOD}
       ORDER BY m.map_start_dt DESC
       LIMIT 1`,
      [mapStartDt],
    ),
    query<MapPeriod>(
      `SELECT m.map_id, m.map_name, m.map_start_dt, ${NEXT_MAP_START('m')} AS map_end_dt
       FROM playtime_display_map_history m
       WHERE m.map_start_dt > ?
         AND ${REAL_MAP_PERIOD}
       ORDER BY m.map_start_dt ASC
       LIMIT 1`,
      [mapStartDt],
    ),
  ]);
  return { prev: prevRows[0] ?? null, next: nextRows[0] ?? null };
}

export interface CurrentPlayer {
  steamid: string;
  current_name: string;
  current_country: string | null;
}

/**
 * "Currently online" defined explicitly: a session whose last heartbeat
 * (session_end_dt) was within the last N minutes. This runs NOW() inside
 * MySQL itself rather than passing a JS-computed timestamp, so it's immune
 * to any Node/MySQL timezone mismatch — same check the admin already
 * validated by hand (27 vs a real count of 29).
 *
 * This is deliberately separate from the historical bucket-overlap queries
 * used elsewhere (population-over-time chart, getPlayersAtTime): those have
 * no freshness concept at all, which is correct for a genuinely historical
 * point in time, but wrong for "right now" — a session whose heartbeat
 * stopped an hour ago (player disconnected without a clean close) would
 * still satisfy a historical overlap check forever, silently inflating
 * "currently online" counts.
 */
export async function getCurrentPlayers(freshnessMinutes = 3): Promise<CurrentPlayer[]> {
  // Capped at 64 — the game's hard player limit. In principle this query
  // should never find more than that many truly-simultaneous players, but
  // rapid reconnects within the freshness window could in theory produce
  // more distinct steamids than were ever actually online at once. Ordering
  // by most-recent heartbeat and capping keeps the freshest 64, discarding
  // anything older if that edge case ever occurs.
  //
  // LEFT JOIN (not INNER) + COALESCE fallback to the session's own stored
  // player_name: an INNER JOIN here was silently dropping any session whose
  // matching players row wasn't found, undercounting "currently online"
  // for reasons unrelated to whether the player is actually connected.
  return query<CurrentPlayer>(
    `SELECT s.steamid, COALESCE(MAX(p.current_name), MAX(s.player_name)) AS current_name,
       MAX(p.current_country) AS current_country
     FROM playtime_display_sessions s
     LEFT JOIN playtime_display_players p ON p.steamid = s.steamid
     WHERE s.session_end_dt > NOW() - INTERVAL ? MINUTE
     GROUP BY s.steamid
     ORDER BY MAX(s.session_end_dt) DESC
     LIMIT 64`,
    [freshnessMinutes],
  );
}

export interface PlayerAtTime {
  steamid: string;
  current_name: string;
  current_country: string | null;
}

export async function getPlayersAtTime(atIso: string): Promise<PlayerAtTime[]> {
  return query<PlayerAtTime>(
    `SELECT s.steamid, COALESCE(MAX(p.current_name), MAX(s.player_name)) AS current_name,
       MAX(p.current_country) AS current_country
     FROM playtime_display_sessions s
     LEFT JOIN playtime_display_players p ON p.steamid = s.steamid
     WHERE s.session_start_dt <= ?
       AND (s.session_end_dt IS NULL OR s.session_end_dt >= ?)
     GROUP BY s.steamid`,
    [atIso, atIso],
  );
}

/**
 * A player's current total playtime is just the counter snapshot on their
 * most recent session row — session_end_playtime_minutes if that session
 * has had at least one heartbeat/close, otherwise session_start_playtime_minutes
 * (covers the brief window right after connect, before the first update).
 * No need to touch `player_time` at all — this value already reflects it.
 */
export async function getTotalPlaytimeMinutes(steamid: string): Promise<number | null> {
  const rows = await query<{ minutes: number | null }>(
    `SELECT COALESCE(session_end_playtime_minutes, session_start_playtime_minutes) AS minutes
     FROM playtime_display_sessions
     WHERE steamid = ?
     ORDER BY session_id DESC
     LIMIT 1`,
    [steamid],
  );
  return rows[0]?.minutes ?? null;
}