CREATE DATABASE IF NOT EXISTS forex_platform
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE forex_platform;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin', 'client') NOT NULL DEFAULT 'client',
  client_id VARCHAR(20) NULL,
  token VARCHAR(120) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS clients (
  id VARCHAR(20) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL,
  phone VARCHAR(60) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'Review',
  kyc VARCHAR(40) NOT NULL DEFAULT 'Requested',
  equity DECIMAL(14, 2) NOT NULL DEFAULT 0,
  balance DECIMAL(14, 2) NOT NULL DEFAULT 0,
  risk VARCHAR(40) NOT NULL DEFAULT 'Medium',
  manager VARCHAR(120) NOT NULL DEFAULT 'Client Desk',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS positions (
  id VARCHAR(30) PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  pair VARCHAR(30) NOT NULL,
  side VARCHAR(10) NOT NULL,
  lots DECIMAL(10, 2) NOT NULL,
  entry DECIMAL(14, 5) NOT NULL,
  current DECIMAL(14, 5) NOT NULL,
  pnl DECIMAL(14, 2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX positions_client_id_index (client_id)
);

CREATE TABLE IF NOT EXISTS deposits (
  id VARCHAR(30) PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  amount DECIMAL(14, 2) NOT NULL,
  method VARCHAR(80) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'Pending',
  created_at DATE NOT NULL,
  INDEX deposits_client_id_index (client_id)
);

CREATE TABLE IF NOT EXISTS tickets (
  id VARCHAR(30) PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'Medium',
  status VARCHAR(40) NOT NULL DEFAULT 'Open',
  created_at DATE NOT NULL,
  INDEX tickets_client_id_index (client_id)
);

CREATE TABLE IF NOT EXISTS market_pairs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(30) NOT NULL UNIQUE,
  bid DECIMAL(14, 5) NOT NULL,
  ask DECIMAL(14, 5) NOT NULL,
  change_percent DECIMAL(8, 2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS trading_assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(30) NOT NULL UNIQUE,
  tv_symbol VARCHAR(80) NOT NULL,
  asset_group VARCHAR(40) NOT NULL,
  bid DECIMAL(18, 5) NOT NULL,
  ask DECIMAL(18, 5) NOT NULL,
  change_percent DECIMAL(8, 2) NOT NULL DEFAULT 0,
  spread VARCHAR(40) NOT NULL,
  pip DECIMAL(18, 5) NOT NULL,
  margin DECIMAL(14, 2) NOT NULL,
  swap_value DECIMAL(14, 2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trade_orders (
  id VARCHAR(30) PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  symbol VARCHAR(30) NOT NULL,
  side VARCHAR(10) NOT NULL,
  order_type VARCHAR(30) NOT NULL DEFAULT 'Market',
  volume DECIMAL(10, 2) NOT NULL,
  leverage VARCHAR(20) NOT NULL,
  price DECIMAL(18, 5) NOT NULL,
  stop_loss DECIMAL(18, 5) NULL,
  take_profit DECIMAL(18, 5) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'Filled',
  created_at DATETIME NOT NULL,
  INDEX trade_orders_client_id_index (client_id)
);

CREATE TABLE IF NOT EXISTS news (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  impact VARCHAR(40) NOT NULL,
  event_time VARCHAR(20) NOT NULL
);