CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id VARCHAR(64) NOT NULL UNIQUE,
  username VARCHAR(64),
  first_name VARCHAR(64),
  photo_url VARCHAR(255),
  coins INT DEFAULT 0,
  xp INT DEFAULT 0,
  wins INT DEFAULT 0,
  losses INT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rooms (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_code VARCHAR(8) NOT NULL UNIQUE,
  host_id INT NOT NULL,
  status ENUM('waiting','playing','finished') DEFAULT 'waiting',
  max_players INT DEFAULT 4,
  current_turn_index INT DEFAULT 0,
  winner_id INT DEFAULT NULL,
  started_at DATETIME DEFAULT NULL,
  finished_at DATETIME DEFAULT NULL,
  FOREIGN KEY (host_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (winner_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS room_players (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  user_id INT NOT NULL,
  player_order INT NOT NULL,
  position INT DEFAULT 1,
  connected TINYINT(1) DEFAULT 1,
  ready TINYINT(1) DEFAULT 0,
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS moves (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  turn_no INT NOT NULL,
  user_id INT NOT NULL,
  dice INT NOT NULL,
  from_position INT NOT NULL,
  to_position INT NOT NULL,
  event_type ENUM('normal','snake','ladder','win') NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS snakes (
  head INT PRIMARY KEY,
  tail INT NOT NULL
);

CREATE TABLE IF NOT EXISTS ladders (
  bottom INT PRIMARY KEY,
  top INT NOT NULL
);

CREATE TABLE IF NOT EXISTS chat_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  user_id INT NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS game_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  event VARCHAR(128) NOT NULL,
  json_data JSON,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
);