CREATE TABLE IF NOT EXISTS practice_academy_games (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country_code VARCHAR(8) NOT NULL,
  title VARCHAR(120) NOT NULL DEFAULT 'Practice Academy',
  mode_key VARCHAR(40) NOT NULL DEFAULT 'practice_duel',

  enabled TINYINT(1) NOT NULL DEFAULT 1,
  max_games_per_user INT NOT NULL DEFAULT 50,

  winner_reward_amount DECIMAL(18,2) NOT NULL DEFAULT 1.00,
  loser_reward_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  draw_reward_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,

  reward_wallet_type ENUM('withdrawable_balance','play_balance','dragon_coins') NOT NULL DEFAULT 'withdrawable_balance',
  reward_currency_code VARCHAR(8) NOT NULL DEFAULT 'GMD',
  sponsor_source VARCHAR(80) NOT NULL DEFAULT 'benaplay_practice_sponsor',

  first_deposit_bonus_percent DECIMAL(8,2) NOT NULL DEFAULT 200.00,
  first_deposit_bonus_wallet_type ENUM('dragon_coins','play_balance') NOT NULL DEFAULT 'dragon_coins',

  min_real_moves_required INT NOT NULL DEFAULT 3,

  created_by_admin_id BIGINT UNSIGNED NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_practice_country_enabled (country_code, enabled),
  KEY idx_practice_mode (mode_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_practice_academy_progress (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  country_code VARCHAR(8) NOT NULL,

  active_practice_game_id BIGINT UNSIGNED NULL,

  games_completed INT NOT NULL DEFAULT 0,
  wins INT NOT NULL DEFAULT 0,
  losses INT NOT NULL DEFAULT 0,
  draws INT NOT NULL DEFAULT 0,

  total_reward_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,

  graduated TINYINT(1) NOT NULL DEFAULT 0,
  graduated_at DATETIME NULL,

  graduation_bonus_unlocked TINYINT(1) NOT NULL DEFAULT 0,
  graduation_bonus_claimed TINYINT(1) NOT NULL DEFAULT 0,
  graduation_bonus_claimed_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_practice_user_country (user_id, country_code),
  KEY idx_practice_user (user_id),
  KEY idx_practice_graduated (graduated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS practice_academy_rewards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  practice_game_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  country_code VARCHAR(8) NOT NULL,

  game_number INT NOT NULL,
  result ENUM('win','loss','draw') NOT NULL,

  reward_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  reward_wallet_type ENUM('withdrawable_balance','play_balance','dragon_coins') NOT NULL DEFAULT 'withdrawable_balance',
  reward_currency_code VARCHAR(8) NOT NULL DEFAULT 'GMD',

  sponsor_source VARCHAR(80) NOT NULL DEFAULT 'benaplay_practice_sponsor',
  wallet_txn_ref VARCHAR(120) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY uq_practice_reward_once (match_id, user_id),
  KEY idx_practice_reward_user (user_id, created_at),
  KEY idx_practice_reward_match (match_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS practice_sponsor_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  practice_game_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  country_code VARCHAR(8) NOT NULL,

  sponsor_source VARCHAR(80) NOT NULL,
  entry_side ENUM('debit','credit') NOT NULL DEFAULT 'debit',
  amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  currency_code VARCHAR(8) NOT NULL DEFAULT 'GMD',
  reason VARCHAR(160) NOT NULL DEFAULT 'Practice Academy winner reward',

  wallet_txn_ref VARCHAR(120) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  KEY idx_sponsor_country (country_code, created_at),
  KEY idx_sponsor_user (user_id, created_at),
  KEY idx_sponsor_match (match_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO practice_academy_games
(country_code, title, enabled, max_games_per_user, winner_reward_amount, loser_reward_amount, draw_reward_amount, reward_wallet_type, reward_currency_code, sponsor_source, first_deposit_bonus_percent, first_deposit_bonus_wallet_type, min_real_moves_required)
VALUES
('GM', 'Practice Academy', 1, 50, 1.00, 0.00, 0.00, 'withdrawable_balance', 'GMD', 'benaplay_practice_sponsor', 200.00, 'dragon_coins', 3)
ON DUPLICATE KEY UPDATE updated_at = NOW();
