-- Replica of the production types and tables the tournament engine touches.
-- Enum values copied verbatim from the live PostgREST OpenAPI spec.
CREATE TYPE match_status AS ENUM ('waiting','ready','in_progress','paused','completed','aborted','abandoned');
CREATE TYPE match_result AS ENUM ('white_wins','black_wins','draw','white_timeout','black_timeout','white_resign','black_resign','white_abandon','black_abandon','stalemate','insufficient_material','threefold_repetition','fifty_moves','mutual_draw','aborted');
CREATE TYPE time_control AS ENUM ('bullet_1_0','bullet_1_1','bullet_2_1','blitz_3_0','blitz_3_2','blitz_5_0','blitz_5_3','rapid_10_0','rapid_10_5','rapid_15_10','rapid_30_0','classical_60_0','classical_90_30','custom');
CREATE TYPE tournament_format AS ENUM ('swiss','round_robin','single_elimination','double_elimination','swiss_to_bracket','arena','team_battle');

CREATE TABLE profiles (
  id uuid PRIMARY KEY,
  display_name text, username text, avatar_url text,
  elo_rapid int DEFAULT 1200, elo_blitz int DEFAULT 1200,
  elo_bullet int DEFAULT 1200, elo_classical int DEFAULT 1200,
  games_played int DEFAULT 0, total_games_played int DEFAULT 0,
  total_wins int DEFAULT 0, total_losses int DEFAULT 0, total_draws int DEFAULT 0,
  win_streak int DEFAULT 0, best_win_streak int DEFAULT 0,
  xp int DEFAULT 0, level int DEFAULT 1,
  is_banned bool DEFAULT false, ban_expires_at timestamptz, is_admin bool DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE el3ab_tournaments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  swiss_api_tournament_id uuid, org_id uuid,
  game_key text DEFAULT 'chess', name text NOT NULL, name_ar text,
  description text, format tournament_format DEFAULT 'swiss',
  time_control time_control DEFAULT 'rapid_10_0',
  rounds_total int, swiss_rounds int,
  min_players int DEFAULT 4, max_players int DEFAULT 64,
  bye_value numeric DEFAULT 1.0,
  registration_closes_at timestamptz, starts_at timestamptz NOT NULL,
  status text DEFAULT 'draft', current_round int DEFAULT 0,
  is_rated bool DEFAULT true, auto_start bool DEFAULT true,
  created_by uuid, tiebreak_rules jsonb DEFAULT '[]'::jsonb,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);

CREATE TABLE el3ab_tournament_rounds (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id uuid REFERENCES el3ab_tournaments(id),
  round_number int, status text,
  started_at timestamptz, completed_at timestamptz,
  pairings jsonb DEFAULT '[]'::jsonb, results jsonb DEFAULT '[]'::jsonb,
  created_at timestamptz DEFAULT now(), swiss_round_id uuid
);

CREATE TABLE tournament_registrations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tournament_id uuid REFERENCES el3ab_tournaments(id),
  player_id uuid, status text DEFAULT 'registered',
  seed int, final_standing int, is_bot bool DEFAULT false,
  registered_at timestamptz DEFAULT now(), withdrawn_at timestamptz
);

CREATE TABLE matches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  game_key text, white_player_id uuid, black_player_id uuid,
  match_type text, tournament_id uuid, tournament_round int, pairing_id uuid,
  status match_status DEFAULT 'waiting', result match_result,
  time_control time_control, initial_time_ms int, increment_ms int DEFAULT 0,
  white_time_remaining_ms int, black_time_remaining_ms int,
  starting_fen text, current_fen text, pgn text,
  moves jsonb DEFAULT '[]'::jsonb, move_count int DEFAULT 0,
  game_state jsonb DEFAULT '{}'::jsonb,
  white_rating_before int, black_rating_before int,
  white_rating_after int, black_rating_after int,
  rating_change_white int, rating_change_black int,
  bot_id text, is_rated bool DEFAULT false,
  started_at timestamptz, completed_at timestamptz,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now(),
  metadata jsonb DEFAULT '{}'::jsonb
);

CREATE TABLE rating_history (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id uuid, game_key text, time_control_type text,
  rating_before int, rating_after int, rating_change int,
  match_id uuid, opponent_id uuid, opponent_rating int,
  result text, k_factor int, created_at timestamptz DEFAULT now()
);

CREATE TABLE mp_log (
  id bigserial PRIMARY KEY, ts timestamptz DEFAULT now(),
  match_id uuid, game_key text, player_id uuid, event text, payload jsonb
);
