-- Copy Trading System Database Schema Additions
-- This file extends the existing database with copy trading functionality

-- Copy Traders Table
CREATE TABLE IF NOT EXISTS `copy_traders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT 'Linked to register table',
  `trader_name` varchar(100) NOT NULL,
  `profile_image` varchar(255) DEFAULT 'default_trader.jpg',
  `roi_percentage` decimal(10, 2) DEFAULT 0.00,
  `total_pnl` decimal(15, 2) DEFAULT 0.00,
  `win_rate` decimal(5, 2) DEFAULT 0.00,
  `followers_count` int DEFAULT 0,
  `risk_level` enum('Low', 'Medium', 'High') DEFAULT 'Medium',
  `aum` decimal(15, 2) DEFAULT 0.00 COMMENT 'Assets Under Management',
  `strategy_description` text,
  `trading_pairs` varchar(500) COMMENT 'Comma-separated trading pairs',
  `status` enum('Active', 'Inactive', 'Pending', 'Suspended') DEFAULT 'Pending',
  `featured` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  KEY `roi_percentage` (`roi_percentage`),
  KEY `followers_count` (`followers_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Copied Trades Table
CREATE TABLE IF NOT EXISTS `copied_trades` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT 'User who is copying',
  `trader_id` int NOT NULL COMMENT 'Trader being copied',
  `investment_amount` decimal(15, 2) NOT NULL,
  `leverage` decimal(5, 2) DEFAULT 1.00,
  `risk_level` enum('Low', 'Medium', 'High') DEFAULT 'Medium',
  `stop_loss` decimal(10, 2) DEFAULT NULL,
  `take_profit` decimal(10, 2) DEFAULT NULL,
  `trailing_stop` decimal(10, 2) DEFAULT NULL,
  `copy_mode` enum('Fixed', 'Proportional') DEFAULT 'Proportional',
  `current_profit` decimal(15, 2) DEFAULT 0.00,
  `status` enum('Active', 'Paused', 'Stopped', 'Completed') DEFAULT 'Active',
  `started_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `stopped_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `trader_id` (`trader_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `register`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`trader_id`) REFERENCES `copy_traders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Trader Followers Table
CREATE TABLE IF NOT EXISTS `trader_followers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trader_id` int NOT NULL,
  `user_id` int NOT NULL,
  `followed_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `unfollowed_at` timestamp NULL DEFAULT NULL,
  `status` enum('Active', 'Unfollowed') DEFAULT 'Active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_follow` (`trader_id`, `user_id`, `status`),
  KEY `trader_id` (`trader_id`),
  KEY `user_id` (`user_id`),
  FOREIGN KEY (`trader_id`) REFERENCES `copy_traders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `register`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Trader Statistics Table
CREATE TABLE IF NOT EXISTS `trader_statistics` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trader_id` int NOT NULL,
  `period` enum('Daily', 'Weekly', 'Monthly', 'Yearly') NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `total_trades` int DEFAULT 0,
  `winning_trades` int DEFAULT 0,
  `losing_trades` int DEFAULT 0,
  `roi` decimal(10, 2) DEFAULT 0.00,
  `pnl` decimal(15, 2) DEFAULT 0.00,
  `max_drawdown` decimal(10, 2) DEFAULT 0.00,
  `sharpe_ratio` decimal(10, 2) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `trader_id` (`trader_id`),
  KEY `period` (`period`),
  KEY `period_start` (`period_start`),
  FOREIGN KEY (`trader_id`) REFERENCES `copy_traders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Login Verification Table
CREATE TABLE IF NOT EXISTS `login_verification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `otp_code` varchar(10) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `verified` tinyint(1) DEFAULT 0,
  `attempts` int DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `otp_code` (`otp_code`),
  KEY `expires_at` (`expires_at`),
  FOREIGN KEY (`user_id`) REFERENCES `register`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Email Verification Table
CREATE TABLE IF NOT EXISTS `email_verification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `verification_token` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` enum('Registration', 'PasswordReset', 'EmailChange') DEFAULT 'Registration',
  `expires_at` timestamp NOT NULL,
  `verified` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `verification_token` (`verification_token`),
  KEY `email` (`email`),
  FOREIGN KEY (`user_id`) REFERENCES `register`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Notifications Table
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('Info', 'Success', 'Warning', 'Error', 'Trade', 'System') DEFAULT 'Info',
  `link` varchar(255) DEFAULT NULL,
  `read` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `read` (`read`),
  KEY `type` (`type`),
  FOREIGN KEY (`user_id`) REFERENCES `register`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Platform Settings Table
CREATE TABLE IF NOT EXISTS `platform_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL UNIQUE,
  `setting_value` text NOT NULL,
  `setting_type` enum('string', 'number', 'boolean', 'json') DEFAULT 'string',
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Trading Signals Table
CREATE TABLE IF NOT EXISTS `trading_signals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trader_id` int NOT NULL,
  `symbol` varchar(20) NOT NULL,
  `signal_type` enum('Buy', 'Sell') NOT NULL,
  `entry_price` decimal(15, 8) NOT NULL,
  `stop_loss` decimal(15, 8) DEFAULT NULL,
  `take_profit` decimal(15, 8) DEFAULT NULL,
  `leverage` decimal(5, 2) DEFAULT 1.00,
  `status` enum('Open', 'Closed', 'Cancelled') DEFAULT 'Open',
  `profit_loss` decimal(15, 2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `closed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `trader_id` (`trader_id`),
  KEY `symbol` (`symbol`),
  KEY `status` (`status`),
  FOREIGN KEY (`trader_id`) REFERENCES `copy_traders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Trader Analytics Table
CREATE TABLE IF NOT EXISTS `trader_analytics` (
  `id` int NOT NULL AUTO_INCREMENT,
  `trader_id` int NOT NULL,
  `date` date NOT NULL,
  `total_trades` int DEFAULT 0,
  `winning_trades` int DEFAULT 0,
  `losing_trades` int DEFAULT 0,
  `profit` decimal(15, 2) DEFAULT 0.00,
  `loss` decimal(15, 2) DEFAULT 0.00,
  `net_pnl` decimal(15, 2) DEFAULT 0.00,
  `roi` decimal(10, 2) DEFAULT 0.00,
  `followers_gained` int DEFAULT 0,
  `followers_lost` int DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_date` (`trader_id`, `date`),
  KEY `trader_id` (`trader_id`),
  KEY `date` (`date`),
  FOREIGN KEY (`trader_id`) REFERENCES `copy_traders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert default platform settings
INSERT INTO `platform_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('primary_color', '#f97316', 'string', 'Primary brand color (orange)'),
('secondary_color', '#eab308', 'string', 'Secondary brand color (gold)'),
('dark_theme', 'true', 'boolean', 'Enable dark theme by default'),
('glassmorphism_enabled', 'true', 'boolean', 'Enable glassmorphism effects'),
('otp_expiry_minutes', '10', 'number', 'OTP code expiry time in minutes'),
('max_otp_attempts', '3', 'number', 'Maximum OTP verification attempts'),
('min_copy_investment', '100', 'number', 'Minimum copy trading investment amount'),
('max_leverage', '100', 'number', 'Maximum leverage allowed'),
('email_verification_required', 'true', 'boolean', 'Require email verification for registration'),
('login_verification_required', 'true', 'boolean', 'Require OTP verification on login');