SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value LONGTEXT NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(32) PRIMARY KEY,
  phone VARCHAR(40) NOT NULL UNIQUE,
  email VARCHAR(190) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  country_code VARCHAR(10) NOT NULL DEFAULT 'BD',
  country_name VARCHAR(80) NOT NULL DEFAULT 'Bangladesh',
  dial_code VARCHAR(10) NOT NULL DEFAULT '+880',
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  bonus DECIMAL(14,2) NOT NULL DEFAULT 0,
  safe_income DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  avatar VARCHAR(20) DEFAULT '',
  invite_code VARCHAR(60) DEFAULT '',
  referral_code VARCHAR(60) DEFAULT '',
  created_at DATETIME NOT NULL,
  last_login DATETIME DEFAULT NULL,
  INDEX idx_phone(phone),
  INDEX idx_country(country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
  id VARCHAR(80) PRIMARY KEY,
  user_id VARCHAR(32) NOT NULL,
  type VARCHAR(70) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'success',
  note TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_tx_user(user_id),
  INDEX idx_tx_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_methods (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  country_code VARCHAR(10) NOT NULL,
  method_key VARCHAR(60) NOT NULL,
  label VARCHAR(120) NOT NULL,
  provider VARCHAR(120) DEFAULT '',
  wallet_number VARCHAR(190) NOT NULL,
  instructions TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX idx_pm_country(country_code),
  INDEX idx_pm_active(active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deposit_requests (
  id VARCHAR(80) PRIMARY KEY,
  user_id VARCHAR(32) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  country_code VARCHAR(10) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  method_id INT UNSIGNED NOT NULL,
  method_key VARCHAR(60) NOT NULL,
  method_label VARCHAR(120) NOT NULL,
  wallet_number VARCHAR(190) NOT NULL,
  trxid VARCHAR(160) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL,
  reviewed_at DATETIME NULL,
  INDEX idx_dep_user(user_id),
  INDEX idx_dep_status(status),
  INDEX idx_dep_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS withdraw_requests (
  id VARCHAR(80) PRIMARY KEY,
  user_id VARCHAR(32) NOT NULL,
  country_code VARCHAR(10) NOT NULL,
  method_key VARCHAR(60) NOT NULL,
  method_label VARCHAR(120) NOT NULL,
  full_name VARCHAR(190) NOT NULL,
  account_number VARCHAR(190) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  fee DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL,
  reviewed_at DATETIME NULL,
  INDEX idx_w_user(user_id),
  INDEX idx_w_status(status),
  INDEX idx_w_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS games (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  icon VARCHAR(20) DEFAULT '🎮',
  provider VARCHAR(100) DEFAULT '',
  category VARCHAR(60) NOT NULL DEFAULT 'slots',
  image_url VARCHAR(500) DEFAULT '',
  game_url VARCHAR(500) DEFAULT '',
  is_hot TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX idx_games_cat(category),
  INDEX idx_games_active(active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
