Global Leaderboard with Supabase

-- Create the leaderboard table
CREATE TABLE leaderboard (
  id           uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  player_id    text         NOT NULL,  -- persistent device UUID
  initials     text         NOT NULL,  -- 1–3 chars, player-chosen
  score        integer      NOT NULL,  -- primary ranking metric
  stage        integer      DEFAULT 1, -- tiebreaker: level reached
  city         text,
  country      text,
  country_code text,                  -- ISO 3166-1 alpha-2 (e.g. 'AR')
  created_at   timestamptz  DEFAULT now()
);

-- Enable Row Level Security
ALTER TABLE leaderboard ENABLE ROW LEVEL SECURITY;

-- Allow anyone to read scores
CREATE POLICY "public read" ON leaderboard
  FOR SELECT USING (true);

-- Allow anyone to submit a score
CREATE POLICY "public insert" ON leaderboard
  FOR INSERT WITH CHECK (true);

-- Useful queries for monitoring
SELECT * FROM leaderboard
ORDER BY score DESC, stage DESC, created_at ASC
LIMIT 100;

// ── Config — replace with your project values ─────────────────
const SUPABASE_URL = 'https://YOUR_PROJECT.supabase.co'
const SUPABASE_KEY = 'your_anon_key_here'
const TABLE        = 'leaderboard'

// ── Player Identity ───────────────────────────────────────────
// Generates a UUID once per device and stores it in localStorage.
// This gives each player a persistent identity across sessions
// without requiring login or any account system.
function getPlayerId() {
  let id = localStorage.getItem('kp_player_id')
  if (!id) {
    id = crypto.randomUUID()
    localStorage.setItem('kp_player_id', id)
  }
  return id
}

// ── Geolocation ───────────────────────────────────────────────
// Looks up city/country from IP address. Result cached in
// localStorage so we only make the API call once per device.
async function getGeo() {
  const cached = localStorage.getItem('kp_geo')
  if (cached) return JSON.parse(cached)
  try {
    const res  = await fetch('https://ipapi.co/json/')
    const data = await res.json()
    const geo  = { city: data.city, country: data.country_name, code: data.country_code }
    localStorage.setItem('kp_geo', JSON.stringify(geo))
    return geo
  } catch { return { city: '', country: '', code: '' } }
}

// ── Submit Score ──────────────────────────────────────────────
export async function submitScore({ initials, score, stage = 1 }) {
  const [player_id, geo] = await Promise.all([getPlayerId(), getGeo()])
  const body = JSON.stringify({
    player_id, initials, score, stage,
    city: geo.city, country: geo.country, country_code: geo.code
  })
  await fetch(`${SUPABASE_URL}/rest/v1/${TABLE}`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'apikey': SUPABASE_KEY,
      'Authorization': `Bearer ${SUPABASE_KEY}`,
      'Prefer': 'return=minimal'
    },
    body
  })
}

// ── Fetch Top Scores ──────────────────────────────────────────
export async function getTopScores(limit = 10) {
  const url = `${SUPABASE_URL}/rest/v1/${TABLE}` +
    `?select=*&order=score.desc,stage.desc,created_at.asc&limit=${limit}`
  const res = await fetch(url, {
    headers: { 'apikey': SUPABASE_KEY, 'Authorization': `Bearer ${SUPABASE_KEY}` }
  })
  return res.json()
}

https://YOUR_PROJECT.supabase.co/rest/v1/leaderboard?select=id&limit=1&apikey=YOUR_ANON_KEY

Monitor type:    HTTP(S)
Interval:        Every 5 minutes
Alert on down:   Yes (email)

import { getTopScores } from './LeaderboardManager.js'

export async function renderLeaderboard(containerEl) {
  const scores = await getTopScores(10)
  const rows = scores.map((entry, i) => {
    // Country code → flag emoji (e.g. 'AR' → 🇦🇷)
    const flag = entry.country_code
      ? [...entry.country_code.toUpperCase()]
          .map(c => String.fromCodePoint(0x1F1E6 + c.charCodeAt(0) - 65))
          .join('')
      : '🌐'
    return `<tr class="${i === 0 ? 'top' : ''}">
      <td>${i + 1}</td>
      <td>${entry.initials}</td>
      <td>${entry.score.toLocaleString()}</td>
      <td>${flag} ${entry.city || entry.country || '—'}</td>
    </tr>`
  }).join('')

  containerEl.innerHTML = `
    <table class="lb-table">
      <thead><tr><th>#</th><th>NAME</th><th>SCORE</th><th>LOCATION</th></tr></thead>
      <tbody>${rows}</tbody>
    </table>
  `
}

SELECT
  initials,
  score,
  stage,
  city,
  country_code,
  created_at,
  -- Compute rank position including ties
  RANK() OVER (
    ORDER BY score DESC, stage DESC, created_at ASC
  ) AS rank
FROM leaderboard
LIMIT 100;