
-- LMAX FinHoldings schema
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  phone VARCHAR(50) DEFAULT NULL,
  country VARCHAR(80) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  referral_code VARCHAR(16) UNIQUE,
  referred_by INT DEFAULT NULL,
  balance DECIMAL(18,2) DEFAULT 0.00,
  total_deposited DECIMAL(18,2) DEFAULT 0.00,
  total_profit DECIMAL(18,2) DEFAULT 0.00,
  total_referral DECIMAL(18,2) DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(120) UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('super','manager') DEFAULT 'super',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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 admin user: admin / admin123 (CHANGE AFTER INSTALL!)
INSERT INTO admins (username, email, password_hash, role) VALUES
('admin','admin@example.com', '$2y$10$FQ0Wz8Jk4wW2F0u4v5AXaO2jG6E6mOZq4s0vSXQKkq5rJ3Bqf3K/e', 'super')
ON DUPLICATE KEY UPDATE email=VALUES(email);

-- 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`);

INSERT INTO email_templates (slug,subject,body,enabled) VALUES
('welcome','Welcome to {{site_name}}','<p>Hello {{name}},</p><p>Welcome to {{site_name}}. We're glad you're here.</p>',1),
('deposit_received','Deposit Received','<p>We received your deposit of <b>${{amount}}</b>. It is pending review.</p>',1),
('deposit_approved','Deposit Approved','<p>Your deposit of <b>${{amount}}</b> is approved and added to your balance.</p>',1),
('withdrawal_requested','Withdrawal Requested','<p>We received your withdrawal request of <b>${{amount}}</b>.</p>',1),
('withdrawal_approved','Withdrawal Approved','<p>Your withdrawal of <b>${{amount}}</b> has been processed.</p>',1),
('reset_password','Reset your password','<p>Use the link to reset your password: {{link}}</p>',1)
ON DUPLICATE KEY UPDATE subject=VALUES(subject);
