climperor/web/cloudflare/migrations/0001_init.sql

-- Climperor multi-user player data (D1 climperor-users)

CREATE TABLE IF NOT EXISTS users (
  account_id INTEGER PRIMARY KEY,
  steamid TEXT NOT NULL UNIQUE,
  personaname TEXT,
  avatar TEXT,
  public_share INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  last_login_at TEXT
);

CREATE TABLE IF NOT EXISTS player_profiles (
  account_id INTEGER PRIMARY KEY REFERENCES users(account_id),
  rank_tier INTEGER,
  leaderboard_rank INTEGER,
  availability_status TEXT,
  availability_note TEXT,
  availability_complete INTEGER NOT NULL DEFAULT 0,
  source TEXT,
  fetched_at TEXT,
  enriched_at TEXT,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS player_stats (
  account_id INTEGER NOT NULL REFERENCES users(account_id),
  scope TEXT NOT NULL, -- career | recent20 | recent180
  sample INTEGER NOT NULL DEFAULT 0,
  wins INTEGER NOT NULL DEFAULT 0,
  losses INTEGER NOT NULL DEFAULT 0,
  winrate REAL,
  kills INTEGER,
  deaths INTEGER,
  assists INTEGER,
  kda REAL,
  avg_kills REAL,
  avg_deaths REAL,
  avg_assists REAL,
  avg_gpm REAL,
  avg_xpm REAL,
  avg_hero_damage REAL,
  payload_json TEXT,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (account_id, scope)
);

CREATE TABLE IF NOT EXISTS player_heroes (
  account_id INTEGER NOT NULL REFERENCES users(account_id),
  hero_id INTEGER NOT NULL,
  hero_key TEXT,
  hero_name_loc TEXT,
  games INTEGER NOT NULL DEFAULT 0,
  wins INTEGER NOT NULL DEFAULT 0,
  winrate REAL,
  last_played INTEGER,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (account_id, hero_id)
);

CREATE TABLE IF NOT EXISTS player_matches (
  account_id INTEGER NOT NULL REFERENCES users(account_id),
  match_id INTEGER NOT NULL,
  start_time INTEGER,
  duration INTEGER,
  won INTEGER,
  hero_id INTEGER,
  hero_key TEXT,
  hero_name_loc TEXT,
  kills INTEGER,
  deaths INTEGER,
  assists INTEGER,
  kda REAL,
  gpm INTEGER,
  xpm INTEGER,
  hero_damage INTEGER,
  game_mode INTEGER,
  lobby_type INTEGER,
  r2_key TEXT,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (account_id, match_id)
);

CREATE INDEX IF NOT EXISTS idx_player_matches_start
  ON player_matches(account_id, start_time DESC);

CREATE TABLE IF NOT EXISTS player_peers (
  account_id INTEGER NOT NULL REFERENCES users(account_id),
  peer_account_id INTEGER NOT NULL,
  personaname TEXT,
  avatar TEXT,
  games INTEGER NOT NULL DEFAULT 0,
  wins INTEGER NOT NULL DEFAULT 0,
  winrate REAL,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (account_id, peer_account_id)
);

CREATE TABLE IF NOT EXISTS sync_jobs (
  id TEXT PRIMARY KEY,
  account_id INTEGER NOT NULL,
  kind TEXT NOT NULL, -- login_refresh | publish_match | backfill
  match_id INTEGER,
  status TEXT NOT NULL, -- queued | running | done | error
  attempts INTEGER NOT NULL DEFAULT 0,
  lease_until TEXT,
  error TEXT,
  next_retry_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sync_jobs_status
  ON sync_jobs(status, next_retry_at);