USE aihub;

CREATE TABLE IF NOT EXISTS games (
  id INT AUTO_INCREMENT PRIMARY KEY,
  game_code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  game_type VARCHAR(30) NOT NULL,
  module_id INT NULL,
  icon VARCHAR(20) DEFAULT '🎲',
  min_bet DECIMAL(15,2) NOT NULL DEFAULT 500,
  max_bet DECIMAL(15,2) NOT NULL DEFAULT 100000,
  house_edge_pct DECIMAL(5,2) NOT NULL DEFAULT 4.00,
  win_multiplier DECIMAL(8,4) NOT NULL DEFAULT 1.9600,
  win_probability_pct DECIMAL(5,2) NULL,
  max_payout_per_round DECIMAL(15,2) NOT NULL DEFAULT 500000,
  daily_loss_limit_per_user DECIMAL(15,2) NOT NULL DEFAULT 50000,
  config_json JSON NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_rounds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  game_id INT NOT NULL,
  stake DECIMAL(15,2) NOT NULL,
  payout DECIMAL(15,2) NOT NULL DEFAULT 0,
  result ENUM('win','lose','push') NOT NULL,
  pick_side VARCHAR(40) NULL,
  outcome_data JSON NULL,
  margin_snapshot JSON NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE RESTRICT,
  INDEX idx_user_game (user_id, game_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_economics_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  game_id INT NOT NULL,
  admin_id INT NOT NULL,
  old_config JSON NULL,
  new_config JSON NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS game_providers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider_slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  api_key VARCHAR(255) NULL,
  webhook_secret VARCHAR(255) NULL,
  config_json JSON NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'inactive',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS jackpot_pool (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pool_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
  contribution_pct DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO jackpot_pool (id, pool_amount) VALUES (1, 0);

INSERT IGNORE INTO games (game_code, name, game_type, icon, config_json, sort_order) VALUES
('coin_flip', 'Coin Flip', 'binary', '🪙', '{"sides":["heads","tails"]}', 1),
('color_pick', 'Color Pick', 'binary', '🎨', '{"sides":["red","green","blue"],"multipliers":{"red":1.96,"green":1.96,"blue":2.8}}', 2),
('dice', 'Dice Roll', 'binary', '🎲', '{"threshold":50,"sides":["under","over"]}', 3),
('odd_even', 'Odd or Even', 'binary', '🔢', '{"max":6,"sides":["odd","even"]}', 4),
('crash', 'Crash', 'crash', '🚀', '{"max_multiplier":10,"house_edge_pct":3}', 5);