USE aihub;

CREATE TABLE IF NOT EXISTS matatu_tables (
  id INT AUTO_INCREMENT PRIMARY KEY,
  host_user_id INT NOT NULL,
  stake DECIMAL(15,2) NOT NULL,
  status ENUM('waiting','playing','finished','cancelled') NOT NULL DEFAULT 'waiting',
  is_private TINYINT(1) NOT NULL DEFAULT 0,
  invite_code VARCHAR(8) NULL,
  player2_user_id INT NULL,
  player2_is_bot TINYINT(1) NOT NULL DEFAULT 0,
  bot_name VARCHAR(40) NULL,
  match_id INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (host_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_status (status),
  INDEX idx_invite (invite_code)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matatu_matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  table_id INT NOT NULL,
  draw_pile_json JSON NOT NULL,
  discard_json JSON NOT NULL,
  current_turn_user_id INT NOT NULL,
  chosen_suit VARCHAR(10) NULL,
  pending_draw INT NOT NULL DEFAULT 0,
  phase ENUM('playing','awaiting_suit','ended') NOT NULL DEFAULT 'playing',
  winner_user_id INT NULL,
  matatu_called_user_id INT NULL,
  last_action_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  FOREIGN KEY (table_id) REFERENCES matatu_tables(id) ON DELETE CASCADE,
  INDEX idx_table (table_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matatu_hands (
  match_id INT NOT NULL,
  user_id INT NOT NULL,
  cards_json JSON NOT NULL,
  PRIMARY KEY (match_id, user_id),
  FOREIGN KEY (match_id) REFERENCES matatu_matches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

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

INSERT IGNORE INTO games (game_code, name, game_type, icon, config_json, sort_order, min_bet, max_bet) VALUES
('matatu_champion', 'Matatu Champion', 'matatu', 'MC', '{"players":2}', 6, 1000, 500000);

INSERT INTO settings (setting_key, setting_value) VALUES
('matatu_rake_pct', '5'),
('matatu_bot_wait_seconds', '8'),
('matatu_turn_timeout_seconds', '60')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);