USE aihub;

CREATE TABLE IF NOT EXISTS forex_bots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bot_code VARCHAR(40) NOT NULL UNIQUE,
  pair VARCHAR(20) NOT NULL,
  strategy_label VARCHAR(60) NOT NULL,
  lock_days INT NOT NULL,
  price DECIMAL(15,2) NOT NULL,
  total_income DECIMAL(15,2) NOT NULL,
  daily_income DECIMAL(15,2) GENERATED ALWAYS AS (total_income / lock_days) STORED,
  daily_return_pct DECIMAL(8,4) NULL,
  win_days_pct DECIMAL(5,2) DEFAULT 70.00,
  min_deposit_required DECIMAL(15,2) NOT NULL DEFAULT 0,
  subscription_plan_id INT NULL,
  image_url VARCHAR(255) NULL,
  stock INT NOT NULL DEFAULT 999,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_rentals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  bot_id INT NOT NULL,
  price_paid DECIMAL(15,2) NOT NULL,
  daily_income DECIMAL(15,2) NOT NULL,
  lock_days INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  last_payout_date DATE NULL,
  total_paid_out DECIMAL(15,2) NOT NULL DEFAULT 0,
  status ENUM('running','completed','cancelled') NOT NULL DEFAULT 'running',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (bot_id) REFERENCES forex_bots(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_trades_display (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rental_id INT NOT NULL,
  pair VARCHAR(20) NOT NULL,
  direction ENUM('buy','sell') NOT NULL,
  profit DECIMAL(15,2) NOT NULL,
  trade_at DATETIME NOT NULL,
  FOREIGN KEY (rental_id) REFERENCES forex_rentals(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO forex_bots (bot_code, pair, strategy_label, lock_days, price, total_income, min_deposit_required, status) VALUES
('FX-SCALP-1', 'EUR/USD', 'Starter Scalper', 30, 10000, 15000, 10000, 'active'),
('FX-SWING-1', 'GBP/USD', 'Gold Swing', 60, 50000, 80000, 100000, 'active'),
('FX-GRID-VIP', 'XAU/USD', 'VIP AI Grid', 90, 200000, 350000, 500000, 'active');