-- Database schema for PerfectExchange Demo (updated)
CREATE DATABASE IF NOT EXISTS infioxml_perfectexdb;
USE infioxml_perfectexdb;

-- users table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(120) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_pro TINYINT(1) DEFAULT 0,
  balance DECIMAL(18,8) DEFAULT 0.0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- followers mapping: follower_id follows pro_id, with configurable copy percentage
CREATE TABLE followers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pro_id INT NOT NULL,
  follower_id INT NOT NULL,
  copy_pct DECIMAL(5,2) DEFAULT 1.00, -- percent of pro trade to replicate (e.g., 1.00 = 1%)
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_follow (pro_id, follower_id),
  FOREIGN KEY (pro_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- investment plans
CREATE TABLE plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  duration_days INT NOT NULL,
  rate_percent DECIMAL(8,4) NOT NULL,
  min_amount DECIMAL(18,8) DEFAULT 1.0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- user investments
CREATE TABLE investments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  plan_id INT NOT NULL,
  amount DECIMAL(18,8) NOT NULL,
  start_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('active','completed','cancelled') DEFAULT 'active',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- trades table (pro trader actions)
CREATE TABLE trades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pro_id INT NOT NULL,
  symbol VARCHAR(50) NOT NULL,
  side ENUM('buy','sell') NOT NULL,
  quantity DECIMAL(32,16) NOT NULL,
  price DECIMAL(32,16) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pro_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- replicated trades (what followers receive)
CREATE TABLE replicated_trades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  trade_id INT NOT NULL,
  follower_id INT NOT NULL,
  pro_id INT NOT NULL,
  symbol VARCHAR(50) NOT NULL,
  side ENUM('buy','sell') NOT NULL,
  quantity DECIMAL(32,16) NOT NULL,
  price DECIMAL(32,16) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (trade_id) REFERENCES trades(id) ON DELETE CASCADE,
  FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
