CREATE TABLE IF NOT EXISTS servers (
  server_id VARCHAR(64) NOT NULL,
  steam_id VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT '',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  port INT UNSIGNED NOT NULL DEFAULT 0,
  query_port INT UNSIGNED NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'unknown',
  players INT UNSIGNED NOT NULL DEFAULT 0,
  max_players INT UNSIGNED NOT NULL DEFAULT 0,
  map VARCHAR(120) NOT NULL DEFAULT '',
  region VARCHAR(64) NOT NULL DEFAULT '',
  mode VARCHAR(64) NOT NULL DEFAULT '',
  perspective VARCHAR(64) NOT NULL DEFAULT '',
  difficulty VARCHAR(64) NOT NULL DEFAULT '',
  ping INT NULL,
  last_seen DATETIME NULL,
  monitored_since DATETIME NULL,
  plugin_framework VARCHAR(64) NOT NULL DEFAULT '',
  observed_timeline_json JSON NULL,
  data_json JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (server_id),
  KEY idx_servers_status_players (status, players),
  KEY idx_servers_region (region),
  KEY idx_servers_last_seen (last_seen),
  KEY idx_servers_steam_id (steam_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS server_snapshot (
  server_id VARCHAR(64) NOT NULL,
  steam_id VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT '',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  port INT UNSIGNED NOT NULL DEFAULT 0,
  query_port INT UNSIGNED NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'unknown',
  players INT UNSIGNED NOT NULL DEFAULT 0,
  max_players INT UNSIGNED NOT NULL DEFAULT 0,
  map VARCHAR(120) NOT NULL DEFAULT '',
  region VARCHAR(64) NOT NULL DEFAULT '',
  mode VARCHAR(64) NOT NULL DEFAULT '',
  perspective VARCHAR(64) NOT NULL DEFAULT '',
  difficulty VARCHAR(64) NOT NULL DEFAULT '',
  ping INT NULL,
  last_seen DATETIME NULL,
  monitored_since DATETIME NULL,
  plugin_framework VARCHAR(64) NOT NULL DEFAULT '',
  observed_timeline_json JSON NULL,
  data_json JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (server_id),
  KEY idx_server_snapshot_status_players (status, players),
  KEY idx_server_snapshot_region (region),
  KEY idx_server_snapshot_last_seen (last_seen),
  KEY idx_server_snapshot_steam_id (steam_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sites (
  slug VARCHAR(120) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  tagline VARCHAR(255) NOT NULL DEFAULT '',
  theme VARCHAR(64) NOT NULL DEFAULT '',
  short_info MEDIUMTEXT NULL,
  gameplay_info MEDIUMTEXT NULL,
  owner_discord_id VARCHAR(32) NOT NULL DEFAULT '',
  data_json JSON NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (slug),
  KEY idx_sites_owner (owner_discord_id),
  KEY idx_sites_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_servers (
  site_slug VARCHAR(120) NOT NULL,
  server_id VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (site_slug, server_id),
  KEY idx_site_servers_server (server_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  discord_id VARCHAR(32) NOT NULL,
  username VARCHAR(120) NOT NULL DEFAULT '',
  global_name VARCHAR(120) NOT NULL DEFAULT '',
  email VARCHAR(255) NOT NULL DEFAULT '',
  role VARCHAR(32) NOT NULL DEFAULT 'member',
  status VARCHAR(32) NOT NULL DEFAULT 'active',
  last_login_ip VARCHAR(80) NOT NULL DEFAULT '',
  last_user_agent VARCHAR(255) NOT NULL DEFAULT '',
  session_token VARCHAR(255) NOT NULL DEFAULT '',
  data_json JSON NOT NULL,
  created_at DATETIME NULL,
  last_login_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (discord_id),
  KEY idx_users_role_status (role, status),
  KEY idx_users_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_config (
  config_key VARCHAR(64) NOT NULL,
  value_json JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS geo_cache (
  ip VARCHAR(64) NOT NULL,
  country_code VARCHAR(8) NOT NULL DEFAULT '',
  region VARCHAR(32) NOT NULL DEFAULT '',
  updated_at DATETIME NULL,
  data_json JSON NOT NULL,
  PRIMARY KEY (ip),
  KEY idx_geo_cache_country (country_code),
  KEY idx_geo_cache_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS storage_migrations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  migration_name VARCHAR(128) NOT NULL,
  summary_json JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_storage_migrations_name (migration_name),
  KEY idx_storage_migrations_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;