-- BotHub Module & Unlock Foundation
USE aihub;

CREATE TABLE IF NOT EXISTS modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  icon VARCHAR(20) DEFAULT '📦',
  description VARCHAR(255) NULL,
  status ENUM('enabled','disabled','maintenance') NOT NULL DEFAULT 'disabled',
  is_core TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  disabled_message VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  plan_code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  plan_type ENUM('general','sure_odds','casino','forex') NOT NULL DEFAULT 'general',
  price DECIMAL(15,2) NOT NULL,
  duration_days INT NOT NULL DEFAULT 30,
  perks_json JSON NULL,
  module_id INT NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscription_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  payment_note VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  source VARCHAR(40) DEFAULT 'balance',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE RESTRICT,
  INDEX idx_user_active (user_id, status, ends_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS unlock_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  rule_type ENUM('deposit_total','subscription','kyc','manual','survey_points') NOT NULL,
  threshold_value DECIMAL(15,2) NULL,
  plan_id INT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_unlocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  module_id INT NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'manual',
  expires_at DATETIME NULL,
  granted_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  UNIQUE KEY uq_user_module (user_id, module_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_stats (
  user_id INT PRIMARY KEY,
  lifetime_deposit DECIMAL(15,2) NOT NULL DEFAULT 0,
  lifetime_wagered DECIMAL(15,2) NOT NULL DEFAULT 0,
  survey_points INT NOT NULL DEFAULT 0,
  surveys_completed INT NOT NULL DEFAULT 0,
  vip_tier VARCHAR(20) NOT NULL DEFAULT 'bronze',
  daily_login_streak INT NOT NULL DEFAULT 0,
  last_login_claim DATE NULL,
  self_excluded_until DATETIME NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS module_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  admin_id INT NOT NULL,
  old_status VARCHAR(20) NULL,
  new_status VARCHAR(20) NOT NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_id INT NOT NULL,
  action VARCHAR(80) NOT NULL,
  detail TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO modules (slug, name, icon, description, status, is_core, sort_order) VALUES
('machines', 'AI Machines', '🖥', 'Rent AI machines for daily income', 'enabled', 1, 1),
('casino', 'Casino', '🎰', 'Play casino games with your wallet', 'disabled', 0, 2),
('forex', 'Forex Bots', '📈', 'Rent trading bots for daily returns', 'disabled', 0, 3),
('sure_odds', 'Sure Odds', '⚽', 'Daily sure match tips and odds', 'enabled', 1, 4),
('socialmedis', 'Social Media Hub', '📱', 'Earn by sharing content on social platforms', 'enabled', 0, 5);

INSERT IGNORE INTO subscription_plans (plan_code, name, plan_type, price, duration_days, perks_json, status) VALUES
('sure_odds_weekly', 'Sure Odds Weekly', 'sure_odds', 12000, 7, '{}', 'active'),
('sure_odds_monthly', 'Sure Odds Monthly', 'sure_odds', 40000, 30, '{}', 'active');

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('casino_enabled', '1'),
('casino_global_house_edge_pct', '4'),
('casino_global_daily_loss_limit', '50000'),
('casino_global_max_bet', '100000'),
('referral_deposit_pct', '5'),
('referral_purchase_pct', '3'),
('survey_unlock_threshold', '10'),
('vip_silver_deposit', '100000'),
('vip_gold_deposit', '500000'),
('vip_platinum_deposit', '2000000');