

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  description TEXT,
  min_amount DECIMAL(18,2) DEFAULT 10.00,
  max_amount DECIMAL(18,2) DEFAULT 100000.00,
  roi_percent DECIMAL(6,2) NOT NULL, -- total ROI over the full duration
  duration_hours INT NOT NULL DEFAULT 24,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS investments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_time TIMESTAMP NOT NULL,
  status ENUM('pending','active','completed','cancelled') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deposits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency VARCHAR(16) DEFAULT 'USDT',
  address_used VARCHAR(191) DEFAULT NULL,
  proof_path VARCHAR(191) DEFAULT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  address VARCHAR(191) NOT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  processed_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS referrals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT NOT NULL,
  referred_id INT NOT NULL,
  bonus_amount DECIMAL(18,2) DEFAULT 0.00,
  status ENUM('pending','paid') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS email_templates (
  slug VARCHAR(40) PRIMARY KEY,
  subject VARCHAR(191) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  enabled TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(60) PRIMARY KEY,
  `value` TEXT
);

-- Default settings & email templates
INSERT INTO settings (`key`,`value`) VALUES
('site_name','LMAX FinHoldings'),
('site_tagline','Smarter crypto investing — demo project'),
('site_email','support@yourdomain.com'),
('site_phone','+000 000 0000'),
('wallet_usdt_trc20','TXXXX_DEMO_WALLET'),
('wallet_btc','bc1q-demo-wallet'),
('turnstile_site_key',''),
('turnstile_secret_key','')
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);


