-- ════════════════════════════════════════════════════════
-- VaultFX — MySQL Schema
-- ════════════════════════════════════════════════════════

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  display_id VARCHAR(20) UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  mobile VARCHAR(20) UNIQUE,
  password_hash VARCHAR(255),
  password_changed_at DATETIME DEFAULT NULL,
  otp VARCHAR(10),
  otp_expiry DATETIME,
  account_status ENUM('pending_approval','active','suspended','rejected','inactive') DEFAULT 'pending_approval',
  kyc_status ENUM('pending','submitted','verified','rejected') DEFAULT 'pending',
  referral_code VARCHAR(20) UNIQUE,
  referred_by VARCHAR(20),
  approved_by INT,
  approved_at DATETIME,
  rejection_reason TEXT,
  approval_sms_sent TINYINT(1) DEFAULT 0,
  approval_email_sent TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wallets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNIQUE,
  balance_usd DECIMAL(15,2) DEFAULT 0.00,
  invested_usd DECIMAL(15,2) DEFAULT 0.00,
  total_pnl DECIMAL(15,2) DEFAULT 0.00,
  total_limit DECIMAL(15,2) DEFAULT 0.00,
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS transactions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  type ENUM('deposit','withdrawal','refund','bonus','system_credit','system_debit'),
  method ENUM('upi_manual','razorpay','usdt','system','payout'),
  amount_inr DECIMAL(15,2) DEFAULT 0,
  amount_usd DECIMAL(15,2) DEFAULT 0,
  utr VARCHAR(100),
  proof_url VARCHAR(500),
  razorpay_order_id VARCHAR(100),
  razorpay_payment_id VARCHAR(100),
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  admin_note TEXT,
  approved_by INT,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS bank_accounts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  account_type ENUM('bank','upi'),
  upi_id VARCHAR(100),
  account_number VARCHAR(30),
  ifsc_code VARCHAR(20),
  bank_name VARCHAR(100),
  branch_name VARCHAR(100),
  account_holder VARCHAR(100),
  is_primary TINYINT(1) DEFAULT 0,
  is_verified TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS currencies (
  id INT PRIMARY KEY AUTO_INCREMENT,
  currency_name VARCHAR(30) NOT NULL,
  display_name VARCHAR(80) DEFAULT NULL,
  currency_image VARCHAR(500),
  market_type ENUM('forex','crypto','commodity'),
  base_price DECIMAL(20,8) DEFAULT 0,
  display_price DECIMAL(20,8) DEFAULT 0,
  up_percent DECIMAL(10,4) DEFAULT 0,
  down_percent DECIMAL(10,4) DEFAULT 0,
  spread DECIMAL(10,5) DEFAULT 0.0002,
  pip_value DECIMAL(10,5) DEFAULT 0.0001,
  lot_size DECIMAL(15,2) DEFAULT 100000,
  min_trade DECIMAL(10,4) DEFAULT 0.01,
  max_trade DECIMAL(10,2) DEFAULT 100,
  market_open TINYINT(1) DEFAULT 1,
  is_active TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS market_control (
  id INT PRIMARY KEY AUTO_INCREMENT,
  currency_id INT,
  control_mode ENUM('auto','manual','fixed','reverse') DEFAULT 'auto',
  price_multiplier DECIMAL(5,2) DEFAULT 1.00,
  force_direction ENUM('up','down','neutral') DEFAULT 'neutral',
  force_percent DECIMAL(5,2) DEFAULT 0,
  fixed_price DECIMAL(20,8) DEFAULT NULL,
  volatility_factor DECIMAL(5,2) DEFAULT 1.00,
  win_rate_target DECIMAL(5,2) DEFAULT 50.00,
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (currency_id) REFERENCES currencies(id)
);

CREATE TABLE IF NOT EXISTS orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  currency_id INT,
  currency_pair VARCHAR(30),
  market_type ENUM('forex','crypto','commodity'),
  order_type ENUM('buy','sell'),
  lot_size DECIMAL(10,4),
  entry_price DECIMAL(20,8),
  current_price DECIMAL(20,8),
  stop_loss DECIMAL(20,8),
  take_profit DECIMAL(20,8),
  platform_price DECIMAL(20,8),
  margin_used DECIMAL(15,2),
  leverage INT DEFAULT 100,
  pnl DECIMAL(15,2) DEFAULT 0.00,
  pnl_percent DECIMAL(10,4) DEFAULT 0,
  status ENUM('open','closed','cancelled') DEFAULT 'open',
  close_reason ENUM('manual','stop_loss','take_profit','admin','auto','liquidation') DEFAULT 'manual',
  admin_forced TINYINT(1) DEFAULT 0,
  closed_price DECIMAL(20,8),
  closed_at DATETIME,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (currency_id) REFERENCES currencies(id)
);

CREATE TABLE IF NOT EXISTS user_trade_control (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNIQUE,
  control_mode ENUM('normal','force_win','force_loss','auto') DEFAULT 'auto',
  win_rate_override DECIMAL(5,2) DEFAULT NULL,
  max_win_per_trade DECIMAL(15,2) DEFAULT NULL,
  max_loss_per_trade DECIMAL(15,2) DEFAULT NULL,
  daily_loss_limit DECIMAL(15,2) DEFAULT NULL,
  market_direction ENUM('neutral','up','down') DEFAULT 'neutral',
  market_percent DECIMAL(8,4) DEFAULT 0,
  market_ramp_per_min DECIMAL(8,4) DEFAULT 0.2,
  max_leverage INT DEFAULT NULL,
  is_active TINYINT(1) DEFAULT 1,
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS portfolio (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_id INT,
  holding_type ENUM('position','holding','bulk'),
  entry_price DECIMAL(20,8),
  quantity DECIMAL(20,8),
  current_value DECIMAL(15,2),
  pnl DECIMAL(15,2) DEFAULT 0,
  status ENUM('active','closed') DEFAULT 'active',
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE IF NOT EXISTS watchlists (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  currency_id INT,
  list_name VARCHAR(50) DEFAULT 'Default',
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (currency_id) REFERENCES currencies(id)
);

CREATE TABLE IF NOT EXISTS kyc_documents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  doc_type ENUM('aadhaar_front','aadhaar_back','pan','passport','selfie'),
  file_url VARCHAR(500),
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  rejection_reason TEXT,
  reviewed_by INT,
  reviewed_at DATETIME,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE,
  full_name VARCHAR(100),
  email VARCHAR(100) DEFAULT NULL,
  mobile VARCHAR(20) DEFAULT NULL,
  password_hash VARCHAR(255),
  otp VARCHAR(10) DEFAULT NULL,
  otp_expiry DATETIME DEFAULT NULL,
  role ENUM('superadmin','manager','support') DEFAULT 'support',
  permissions JSON,
  last_login DATETIME,
  last_login_ip VARCHAR(45) DEFAULT NULL,
  last_login_device VARCHAR(120) DEFAULT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS referrals (
  id INT PRIMARY KEY AUTO_INCREMENT,
  referrer_id INT,
  referred_id INT,
  bonus_amount_usd DECIMAL(10,2) DEFAULT 0,
  status ENUM('pending','credited','expired') DEFAULT 'pending',
  credited_at DATETIME,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (referrer_id) REFERENCES users(id),
  FOREIGN KEY (referred_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS rewards (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  reward_type ENUM('referral','milestone','bonus','competition'),
  amount_usd DECIMAL(10,2),
  description TEXT,
  status ENUM('pending','credited') DEFAULT 'pending',
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  title VARCHAR(200),
  message TEXT,
  type ENUM('info','success','warning','alert'),
  is_read TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  message TEXT,
  type ENUM('info','success','warning','alert') DEFAULT 'info',
  link VARCHAR(255) DEFAULT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS price_history (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  currency_id INT,
  price DECIMAL(20,8),
  platform_price DECIMAL(20,8),
  recorded_at DATETIME DEFAULT NOW(),
  INDEX idx_currency_time (currency_id, recorded_at),
  FOREIGN KEY (currency_id) REFERENCES currencies(id)
);

CREATE TABLE IF NOT EXISTS market_discovery_cache (
  id INT PRIMARY KEY AUTO_INCREMENT,
  analysis_json LONGTEXT,
  generated_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS payment_qr_codes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(100) DEFAULT NULL,
  qr_url VARCHAR(500) NOT NULL,
  is_primary TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS platform_settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  setting_key VARCHAR(100) UNIQUE,
  setting_value TEXT,
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE IF NOT EXISTS user_sessions (
  id VARCHAR(36) PRIMARY KEY,
  user_id INT NOT NULL,
  device_label VARCHAR(120) DEFAULT NULL,
  user_agent VARCHAR(512) DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  last_active_at DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME DEFAULT NULL,
  INDEX idx_user_sessions_user (user_id),
  INDEX idx_user_sessions_active (user_id, revoked_at),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS account_change_requests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  request_type ENUM('profile','bank_add','bank_update','bank_delete') NOT NULL,
  payload JSON NOT NULL,
  bank_account_id INT DEFAULT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  admin_note TEXT,
  reviewed_by INT DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_change_req_user (user_id),
  INDEX idx_change_req_status (status),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS login_alert_tokens (
  id INT PRIMARY KEY AUTO_INCREMENT,
  account_type ENUM('user','admin') NOT NULL,
  account_id INT NOT NULL,
  session_id VARCHAR(36) DEFAULT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_alert_token (token),
  INDEX idx_login_alert_account (account_type, account_id)
);

SET FOREIGN_KEY_CHECKS = 1;