CREATE TABLE IF NOT EXISTS server_state (
  server_id VARCHAR(64) NOT NULL,
  site_slug VARCHAR(120) NOT NULL DEFAULT '',
  steam_id VARCHAR(64) NOT NULL DEFAULT '',
  status VARCHAR(32) NOT NULL DEFAULT 'unknown',
  framework VARCHAR(64) NOT NULL DEFAULT '',
  plugin_version VARCHAR(64) NOT NULL DEFAULT '',
  server_name VARCHAR(255) NOT NULL DEFAULT '',
  map VARCHAR(120) NOT NULL DEFAULT '',
  players INT UNSIGNED NOT NULL DEFAULT 0,
  max_players INT UNSIGNED NOT NULL DEFAULT 0,
  modules_json JSON NULL,
  reconnect_errors_json JSON NULL,
  state_json JSON NOT NULL,
  last_heartbeat_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (server_id),
  KEY idx_server_state_site (site_slug),
  KEY idx_server_state_status (status),
  KEY idx_server_state_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS server_players (
  server_id VARCHAR(64) NOT NULL,
  steam_id VARCHAR(32) NOT NULL,
  player_name VARCHAR(120) NOT NULL DEFAULT '',
  ping INT NULL,
  player_group VARCHAR(120) NOT NULL DEFAULT '',
  payload_json JSON NOT NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (server_id, steam_id),
  KEY idx_server_players_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
  event_id VARCHAR(64) NOT NULL,
  site_slug VARCHAR(120) NOT NULL DEFAULT '',
  server_id VARCHAR(64) NOT NULL DEFAULT '',
  source VARCHAR(32) NOT NULL DEFAULT '',
  event_type VARCHAR(64) NOT NULL DEFAULT '',
  actor VARCHAR(120) NOT NULL DEFAULT '',
  message TEXT NULL,
  payload_json JSON NOT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (event_id),
  KEY idx_events_site (site_slug),
  KEY idx_events_server (server_id),
  KEY idx_events_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commands (
  command_id VARCHAR(64) NOT NULL,
  site_slug VARCHAR(120) NOT NULL DEFAULT '',
  server_id VARCHAR(64) NOT NULL DEFAULT '',
  command_name VARCHAR(64) NOT NULL DEFAULT '',
  args VARCHAR(240) NOT NULL DEFAULT '',
  source VARCHAR(32) NOT NULL DEFAULT '',
  requested_by VARCHAR(120) NOT NULL DEFAULT '',
  requested_by_discord_id VARCHAR(40) NOT NULL DEFAULT '',
  status VARCHAR(32) NOT NULL DEFAULT 'queued',
  result_text TEXT NULL,
  error_text TEXT NULL,
  created_at DATETIME NULL,
  claimed_at DATETIME NULL,
  lease_until DATETIME NULL,
  completed_at DATETIME NULL,
  data_json JSON NOT NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (command_id),
  KEY idx_commands_server_status (server_id, status),
  KEY idx_commands_site_created (site_slug, created_at),
  KEY idx_commands_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS discord_delivery_state (
  delivery_key VARCHAR(255) NOT NULL,
  delivery_type VARCHAR(64) NOT NULL DEFAULT 'status_embed',
  site_slug VARCHAR(120) NOT NULL DEFAULT '',
  guild_id VARCHAR(32) NOT NULL DEFAULT '',
  channel_id VARCHAR(32) NOT NULL DEFAULT '',
  message_id VARCHAR(32) NOT NULL DEFAULT '',
  state_json JSON NOT NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (delivery_key),
  KEY idx_delivery_site (site_slug),
  KEY idx_delivery_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;