CREATE DATABASE IF NOT EXISTS betpa_game
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE betpa_game;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(26) NOT NULL UNIQUE,
  username VARCHAR(32) NOT NULL UNIQUE,
  phone VARCHAR(32) NULL,
  email VARCHAR(120) NULL,
  city VARCHAR(80) NOT NULL DEFAULT '',
  country VARCHAR(80) NOT NULL DEFAULT '',
  avatar_url VARCHAR(255) NULL,
  marketing_id VARCHAR(64) NOT NULL,
  device_hash CHAR(64) NOT NULL,
  total_points INT NOT NULL DEFAULT 0,
  wins INT NOT NULL DEFAULT 0,
  losses INT NOT NULL DEFAULT 0,
  current_streak INT NOT NULL DEFAULT 0,
  best_streak INT NOT NULL DEFAULT 0,
  rank_title VARCHAR(32) NOT NULL DEFAULT 'Rookie',
  online_status ENUM('offline','online','in_game') NOT NULL DEFAULT 'offline',
  last_login_date DATE NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_location_points (country, city, total_points DESC),
  INDEX idx_users_world_points (total_points DESC),
  INDEX idx_users_marketing (marketing_id),
  INDEX idx_users_phone (phone),
  INDEX idx_users_email (email)
);

CREATE TABLE user_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  device_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sessions_user (user_id),
  INDEX idx_sessions_expiry (expires_at)
);

CREATE TABLE match_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  device_hash CHAR(64) NOT NULL,
  city VARCHAR(80) NOT NULL,
  country VARCHAR(80) NOT NULL,
  rule_set ENUM('normal','joker') NOT NULL DEFAULT 'normal',
  status ENUM('waiting','matched','cancelled') NOT NULL DEFAULT 'waiting',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_queue_match (status, rule_set, country, created_at)
);

CREATE TABLE challenge_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  requester_user_id BIGINT UNSIGNED NOT NULL,
  requested_user_id BIGINT UNSIGNED NOT NULL,
  rule_set ENUM('normal','joker') NOT NULL DEFAULT 'normal',
  status ENUM('pending','accepted','ignored','cancelled','expired') NOT NULL DEFAULT 'pending',
  room_id BIGINT UNSIGNED NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (requested_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CHECK (requester_user_id <> requested_user_id),
  INDEX idx_challenge_inbox (requested_user_id, status, expires_at),
  INDEX idx_challenge_outbox (requester_user_id, status, expires_at)
);

CREATE TABLE game_rooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_code CHAR(12) NOT NULL UNIQUE,
  rule_set ENUM('normal','joker') NOT NULL DEFAULT 'normal',
  status ENUM('waiting','active','finished','abandoned') NOT NULL DEFAULT 'waiting',
  player_one_id BIGINT UNSIGNED NOT NULL,
  player_two_id BIGINT UNSIGNED NOT NULL,
  player_one_device_hash CHAR(64) NOT NULL,
  player_two_device_hash CHAR(64) NOT NULL,
  current_turn_user_id BIGINT UNSIGNED NULL,
  turn_deadline_at DATETIME NULL,
  winner_user_id BIGINT UNSIGNED NULL,
  game_state JSON NOT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (player_one_id) REFERENCES users(id),
  FOREIGN KEY (player_two_id) REFERENCES users(id),
  FOREIGN KEY (current_turn_user_id) REFERENCES users(id),
  FOREIGN KEY (winner_user_id) REFERENCES users(id),
  CHECK (player_one_id <> player_two_id),
  CHECK (player_one_device_hash <> player_two_device_hash),
  INDEX idx_rooms_players (player_one_id, player_two_id, created_at),
  INDEX idx_rooms_status_turn (status, turn_deadline_at)
);

CREATE TABLE game_actions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  action_type ENUM('play_card','draw','auto_play','quit','finish','cut') NOT NULL,
  payload JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES game_rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_actions_room (room_id, id)
);

CREATE TABLE point_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  reason ENUM('win_game','finish_game','quit_game','daily_login','win_streak_bonus','admin_adjust') NOT NULL,
  points INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (room_id) REFERENCES game_rooms(id) ON DELETE SET NULL,
  INDEX idx_points_user (user_id, created_at)
);

CREATE TABLE chat_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  message VARCHAR(280) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (room_id) REFERENCES game_rooms(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_chat_room (room_id, id),
  INDEX idx_chat_expiry (expires_at)
);

CREATE TABLE user_blocks (
  blocker_user_id BIGINT UNSIGNED NOT NULL,
  blocked_user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (blocker_user_id, blocked_user_id),
  FOREIGN KEY (blocker_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (blocked_user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE user_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reporter_user_id BIGINT UNSIGNED NOT NULL,
  reported_user_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  reason VARCHAR(80) NOT NULL,
  details VARCHAR(500) NULL,
  status ENUM('open','reviewed','dismissed','actioned') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reporter_user_id) REFERENCES users(id),
  FOREIGN KEY (reported_user_id) REFERENCES users(id),
  FOREIGN KEY (room_id) REFERENCES game_rooms(id) ON DELETE SET NULL,
  INDEX idx_reports_status (status, created_at)
);

CREATE TABLE suspicious_matches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_a_id BIGINT UNSIGNED NOT NULL,
  user_b_id BIGINT UNSIGNED NOT NULL,
  match_count_24h INT NOT NULL,
  quit_count_24h INT NOT NULL,
  note VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_suspicious_pair (user_a_id, user_b_id, created_at)
);

CREATE OR REPLACE VIEW leaderboard_world AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;

CREATE OR REPLACE VIEW leaderboard_country AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (PARTITION BY country ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;

CREATE OR REPLACE VIEW leaderboard_city AS
SELECT id, username, city, country, wins, total_points,
       DENSE_RANK() OVER (PARTITION BY country, city ORDER BY total_points DESC, wins DESC, created_at ASC) AS rank_no
FROM users;