USE aihub;

CREATE TABLE IF NOT EXISTS matatu_player_stats (
  user_id INT PRIMARY KEY,
  games_played INT DEFAULT 0,
  wins INT DEFAULT 0,
  losses INT DEFAULT 0,
  tournaments_won INT DEFAULT 0,
  total_earnings DECIMAL(15,2) DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matatu_tournaments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  entry_fee DECIMAL(15,2) DEFAULT 0,
  prize_pool DECIMAL(15,2) DEFAULT 0,
  max_players INT DEFAULT 32,
  status ENUM('upcoming','active','finished','cancelled') DEFAULT 'upcoming',
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matatu_tournament_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tournament_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('registered','eliminated','winner') DEFAULT 'registered',
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mt_entry (tournament_id, user_id),
  FOREIGN KEY (tournament_id) REFERENCES matatu_tournaments(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matatu_match_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  match_id INT NOT NULL,
  user_id INT NULL,
  action VARCHAR(40) NOT NULL,
  card_code VARCHAR(4) NULL,
  meta_json JSON NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_matatu_log_match (match_id),
  FOREIGN KEY (match_id) REFERENCES matatu_matches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO matatu_tournaments (name, entry_fee, prize_pool, max_players, status, starts_at) VALUES
('Daily Matatu Cup', 5000, 50000, 32, 'upcoming', DATE_ADD(NOW(), INTERVAL 1 DAY)),
('Weekly Championship', 15000, 200000, 64, 'upcoming', DATE_ADD(NOW(), INTERVAL 7 DAY));