USE aihub;

CREATE TABLE IF NOT EXISTS mines_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  game_id INT NOT NULL,
  stake DECIMAL(15,2) NOT NULL,
  mine_count INT NOT NULL,
  grid_size INT NOT NULL DEFAULT 25,
  mine_positions JSON NOT NULL,
  revealed JSON NOT NULL,
  multiplier DECIMAL(10,4) NOT NULL DEFAULT 1,
  status ENUM('active','won','lost','cashed') NOT NULL DEFAULT 'active',
  payout DECIMAL(15,2) NOT NULL DEFAULT 0,
  fairness_nonce INT NULL,
  result_hash VARCHAR(64) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vip_tier_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tier_code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(60) NOT NULL,
  min_deposit DECIMAL(15,2) NOT NULL DEFAULT 0,
  cashback_pct DECIMAL(5,2) NOT NULL DEFAULT 0,
  bonus_multiplier DECIMAL(5,2) NOT NULL DEFAULT 1,
  perks_json JSON NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT IGNORE INTO vip_tier_config (tier_code, name, min_deposit, cashback_pct, bonus_multiplier, sort_order) VALUES
('bronze', 'Bronze', 0, 0.5, 1.0, 1),
('silver', 'Silver', 100000, 1.0, 1.05, 2),
('gold', 'Gold', 500000, 1.5, 1.10, 3),
('platinum', 'Platinum', 2000000, 2.5, 1.20, 4);

CREATE TABLE IF NOT EXISTS tournaments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  tournament_type ENUM('daily','weekly','monthly') NOT NULL,
  game_code VARCHAR(40) NULL,
  prize_pool DECIMAL(15,2) NOT NULL DEFAULT 0,
  entry_fee DECIMAL(15,2) NOT NULL DEFAULT 0,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  status ENUM('upcoming','active','completed','cancelled') NOT NULL DEFAULT 'upcoming',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status, ends_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tournament_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tournament_id INT NOT NULL,
  user_id INT NOT NULL,
  score DECIMAL(15,2) NOT NULL DEFAULT 0,
  rank_position INT NULL,
  prize_won DECIMAL(15,2) NOT NULL DEFAULT 0,
  joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tournament_user (tournament_id, user_id),
  FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leaderboard_snapshots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_type ENUM('daily','weekly','monthly','all_time') NOT NULL,
  metric ENUM('wagered','wins','profit','referrals') NOT NULL,
  user_id INT NOT NULL,
  metric_value DECIMAL(15,2) NOT NULL DEFAULT 0,
  rank_position INT NOT NULL DEFAULT 0,
  period_key VARCHAR(20) NOT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_lb (period_type, metric, user_id, period_key),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_rank (period_type, metric, period_key, rank_position)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS achievements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  title VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  icon VARCHAR(20) DEFAULT '🏆',
  reward_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  criteria_json JSON NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_achievements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  achievement_id INT NOT NULL,
  earned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_ach (user_id, achievement_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS rate_limit_buckets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bucket_key VARCHAR(120) NOT NULL,
  hit_count INT NOT NULL DEFAULT 1,
  window_start DATETIME NOT NULL,
  UNIQUE KEY uq_bucket (bucket_key, window_start)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS referral_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level_num INT NOT NULL UNIQUE,
  commission_pct DECIMAL(5,2) NOT NULL,
  description VARCHAR(100) NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO referral_levels (level_num, commission_pct, description) VALUES
(1, 5.00, 'Direct referral'),
(2, 2.00, 'Level 2 referral'),
(3, 1.00, 'Level 3 referral');

INSERT IGNORE INTO achievements (code, title, description, icon, reward_amount, criteria_json) VALUES
('first_bet', 'First Bet', 'Place your first casino bet', '🎲', 1000, '{"type":"bets","count":1}'),
('first_win', 'First Win', 'Win your first casino game', '🏆', 2000, '{"type":"wins","count":1}'),
('high_roller', 'High Roller', 'Wager 1,000,000 UGX total', '💎', 10000, '{"type":"wagered","amount":1000000}'),
('lucky_seven', 'Lucky Seven', 'Win 7 games in a row', '7️⃣', 5000, '{"type":"win_streak","count":7}'),
('referral_king', 'Referral King', 'Refer 10 friends', '👑', 15000, '{"type":"referrals","count":10}');

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('rate_limit_api_per_minute', '120'),
('rate_limit_login_per_minute', '10'),
('rate_limit_bet_per_minute', '60'),
('vip_platinum_deposit', '2000000'),
('vip_gold_deposit', '500000'),
('vip_silver_deposit', '100000'),
('tournament_daily_prize', '50000'),
('tournament_weekly_prize', '200000'),
('tournament_monthly_prize', '1000000');

INSERT IGNORE INTO games (game_code, name, game_type, icon, config_json, min_bet, max_bet, house_edge_pct, win_multiplier, sort_order) VALUES
('number_guess', 'Number Guess', 'guess', '🔢', '{"min":1,"max":100,"exactMultiplier":50,"closeMultiplier":5}', 500, 100000, 4, 5, 10),
('lucky_wheel', 'Lucky Wheel', 'wheel', '🎡', '{"sectors":[{"label":"2x","mult":2},{"label":"3x","mult":3},{"label":"5x","mult":5},{"label":"1.5x","mult":1.5},{"label":"0x","mult":0},{"label":"10x","mult":10}]}', 500, 100000, 4, 3, 11),
('mines', 'Mines', 'mines', '💣', '{"gridSize":25,"minMines":1,"maxMines":24}', 500, 100000, 3, 10, 12),
('plinko', 'Plinko', 'plinko', '📍', '{"rows":12,"risks":{"low":[1.2,1.5,2,3,5],"medium":[0.5,1,2,5,10],"high":[0.2,0.5,2,10,50]}}', 500, 100000, 3, 10, 13),
('keno', 'Keno', 'keno', '🎱', '{"poolSize":80,"drawCount":20,"maxPicks":10}', 500, 100000, 4, 50, 14),
('roulette_eu', 'European Roulette', 'roulette', '🎰', '{"variant":"eu","maxNumber":36}', 500, 100000, 2.7, 35, 15),
('roulette_us', 'American Roulette', 'roulette', '🎡', '{"variant":"us","maxNumber":37}', 500, 100000, 5.26, 35, 16),
('blackjack', 'Blackjack', 'blackjack', '🃏', '{"decks":6,"blackjackPays":1.5}', 500, 100000, 1, 2, 17),
('baccarat', 'Baccarat', 'baccarat', '🂡', '{"bankerCommission":0.05}', 500, 100000, 1.06, 2, 18),
('dragon_tiger', 'Dragon Tiger', 'dragon_tiger', '🐉', '{"suitedTieMult":12}', 500, 100000, 3, 2, 19),
('sic_bo', 'Sic Bo', 'sic_bo', '🎲', '{"dice":3}', 500, 100000, 3, 180, 20),
('casino_war', 'Casino War', 'casino_war', '⚔️', '{"warAllowed":true}', 500, 100000, 2, 2, 21),
('red_dog', 'Red Dog', 'red_dog', '🐕', '{}', 500, 100000, 3, 5, 22),
('video_poker', 'Video Poker', 'video_poker', '🎴', '{"variant":"jacks_or_better"}', 500, 100000, 2, 800, 23),
('three_card_poker', 'Three Card Poker', 'poker', '🂱', '{"pairPlusMult":40}', 500, 100000, 3, 5, 24),
('caribbean_stud', 'Caribbean Stud', 'poker', '🌴', '{"jackpotFee":0}', 500, 100000, 5, 100, 25),
('slot_classic', 'Classic Slots', 'slots', '🎰', '{"reels":3,"paylines":1,"theme":"classic"}', 500, 100000, 4, 100, 26),
('slot_fruit', 'Fruit Slots', 'slots', '🍒', '{"reels":3,"paylines":5,"theme":"fruit"}', 500, 100000, 4, 150, 27),
('slot_3reel', '3-Reel Slots', 'slots', '7️⃣', '{"reels":3,"paylines":9,"theme":"vegas"}', 500, 100000, 4, 200, 28),
('slot_5reel', '5-Reel Slots', 'slots', '💎', '{"reels":5,"paylines":20,"theme":"gems"}', 500, 100000, 4, 500, 29),
('slot_progressive', 'Progressive Jackpot', 'slots', '👑', '{"reels":5,"paylines":25,"progressive":true}', 500, 100000, 5, 1000, 30),
('slot_megaways', 'Megaways Slots', 'slots', '⚡', '{"reels":6,"paylines":117649,"megaways":true}', 500, 100000, 4, 5000, 31),
('daily_lottery', 'Daily Lottery', 'lottery', '📅', '{"ticketPrice":1000,"jackpot":100000}', 1000, 50000, 10, 100, 32),
('pick_3', 'Pick 3', 'lottery', '3️⃣', '{"digits":3,"exactMult":500}', 500, 50000, 10, 500, 33),
('pick_4', 'Pick 4', 'lottery', '4️⃣', '{"digits":4,"exactMult":5000}', 500, 50000, 10, 5000, 34),
('scratch_card', 'Scratch Cards', 'scratch', '🎫', '{"gridSize":9,"matchMult":10}', 500, 50000, 5, 50, 35);

INSERT IGNORE INTO tournaments (name, tournament_type, prize_pool, starts_at, ends_at, status) VALUES
('Daily Wager Race', 'daily', 50000, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'active'),
('Weekly High Roller', 'weekly', 200000, DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY), 'active'),
('Monthly Champion', 'monthly', 1000000, DATE_FORMAT(CURDATE(), '%Y-%m-01'), DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH), 'active');