USE aihub;

CREATE TABLE IF NOT EXISTS matatu_tournament_brackets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tournament_id INT NOT NULL,
  round_num INT NOT NULL DEFAULT 1,
  slot_num INT NOT NULL DEFAULT 1,
  player1_user_id INT NULL,
  player2_user_id INT NULL,
  player2_is_bot TINYINT(1) NOT NULL DEFAULT 0,
  bot_name VARCHAR(40) NULL,
  winner_user_id INT NULL,
  table_id INT NULL,
  match_id INT NULL,
  status ENUM('pending','ready','active','finished','bye') NOT NULL DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  UNIQUE KEY uq_mt_bracket (tournament_id, round_num, slot_num),
  FOREIGN KEY (tournament_id) REFERENCES matatu_tournaments(id) ON DELETE CASCADE,
  FOREIGN KEY (player1_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (player2_user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_mt_bracket_status (tournament_id, status)
) ENGINE=InnoDB;

ALTER TABLE matatu_tables ADD COLUMN tournament_bracket_id INT NULL;