USE aihub;

CREATE TABLE IF NOT EXISTS forex_instruments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(20) NOT NULL UNIQUE,
  display_name VARCHAR(60) NOT NULL,
  category ENUM('major','minor','exotic','commodity','index','crypto') NOT NULL DEFAULT 'major',
  base_currency VARCHAR(10) NOT NULL,
  quote_currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  pip_size DECIMAL(12,8) NOT NULL DEFAULT 0.0001,
  lot_step DECIMAL(8,4) NOT NULL DEFAULT 0.01,
  min_lot DECIMAL(8,4) NOT NULL DEFAULT 0.01,
  max_lot DECIMAL(8,2) NOT NULL DEFAULT 100,
  spread_pips DECIMAL(8,2) NOT NULL DEFAULT 1.5,
  leverage_max INT NOT NULL DEFAULT 100,
  margin_rate DECIMAL(8,4) NOT NULL DEFAULT 0.01,
  tick_size DECIMAL(12,8) NOT NULL DEFAULT 0.00001,
  seed_price DECIMAL(18,8) NOT NULL DEFAULT 1,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_quotes (
  instrument_id INT PRIMARY KEY,
  bid DECIMAL(18,8) NOT NULL,
  ask DECIMAL(18,8) NOT NULL,
  change_pct DECIMAL(8,4) NOT NULL DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_candles (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  instrument_id INT NOT NULL,
  timeframe ENUM('M1','M5','M15','M30','H1','H4','D1','W1') NOT NULL,
  open_time DATETIME NOT NULL,
  open_price DECIMAL(18,8) NOT NULL,
  high_price DECIMAL(18,8) NOT NULL,
  low_price DECIMAL(18,8) NOT NULL,
  close_price DECIMAL(18,8) NOT NULL,
  volume DECIMAL(18,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_candle (instrument_id, timeframe, open_time),
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id) ON DELETE CASCADE,
  INDEX idx_candle_lookup (instrument_id, timeframe, open_time)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  account_type ENUM('demo','live') NOT NULL DEFAULT 'demo',
  balance DECIMAL(18,2) NOT NULL DEFAULT 10000,
  equity DECIMAL(18,2) NOT NULL DEFAULT 10000,
  margin_used DECIMAL(18,2) NOT NULL DEFAULT 0,
  free_margin DECIMAL(18,2) NOT NULL DEFAULT 10000,
  margin_level DECIMAL(10,2) NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  leverage INT NOT NULL DEFAULT 100,
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_demo (user_id, account_type),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  instrument_id INT NOT NULL,
  side ENUM('buy','sell') NOT NULL,
  order_type ENUM('market','limit','stop') NOT NULL DEFAULT 'market',
  volume_lots DECIMAL(10,4) NOT NULL,
  price DECIMAL(18,8) NULL,
  stop_loss DECIMAL(18,8) NULL,
  take_profit DECIMAL(18,8) NULL,
  trailing_stop_pips DECIMAL(8,2) NULL,
  status ENUM('pending','filled','cancelled','rejected','expired') NOT NULL DEFAULT 'pending',
  filled_price DECIMAL(18,8) NULL,
  filled_at DATETIME NULL,
  expires_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES forex_accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id),
  INDEX idx_orders_account (account_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_positions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  instrument_id INT NOT NULL,
  order_id BIGINT NULL,
  side ENUM('buy','sell') NOT NULL,
  volume_lots DECIMAL(10,4) NOT NULL,
  open_price DECIMAL(18,8) NOT NULL,
  current_price DECIMAL(18,8) NOT NULL,
  stop_loss DECIMAL(18,8) NULL,
  take_profit DECIMAL(18,8) NULL,
  trailing_stop_pips DECIMAL(8,2) NULL,
  trailing_extreme DECIMAL(18,8) NULL,
  swap DECIMAL(18,2) NOT NULL DEFAULT 0,
  commission DECIMAL(18,2) NOT NULL DEFAULT 0,
  unrealized_pnl DECIMAL(18,2) NOT NULL DEFAULT 0,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  closed_at DATETIME NULL,
  FOREIGN KEY (account_id) REFERENCES forex_accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id),
  INDEX idx_pos_open (account_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_closed_trades (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  position_id BIGINT NOT NULL,
  instrument_id INT NOT NULL,
  side ENUM('buy','sell') NOT NULL,
  volume_lots DECIMAL(10,4) NOT NULL,
  open_price DECIMAL(18,8) NOT NULL,
  close_price DECIMAL(18,8) NOT NULL,
  gross_pnl DECIMAL(18,2) NOT NULL,
  net_pnl DECIMAL(18,2) NOT NULL,
  commission DECIMAL(18,2) NOT NULL DEFAULT 0,
  swap DECIMAL(18,2) NOT NULL DEFAULT 0,
  close_reason ENUM('manual','tp','sl','trailing','margin_call','admin') NOT NULL DEFAULT 'manual',
  opened_at DATETIME NOT NULL,
  closed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES forex_accounts(id) ON DELETE CASCADE,
  INDEX idx_closed_account (account_id, closed_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_account_ledger (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  entry_type VARCHAR(40) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  balance_after DECIMAL(18,2) NOT NULL,
  reference_id BIGINT NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (account_id) REFERENCES forex_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_watchlists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(60) NOT NULL DEFAULT 'Default',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_watchlist_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  watchlist_id INT NOT NULL,
  instrument_id INT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_wl_item (watchlist_id, instrument_id),
  FOREIGN KEY (watchlist_id) REFERENCES forex_watchlists(id) ON DELETE CASCADE,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_price_alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  instrument_id INT NOT NULL,
  condition_type ENUM('above','below') NOT NULL,
  target_price DECIMAL(18,8) NOT NULL,
  status ENUM('active','triggered','cancelled') NOT NULL DEFAULT 'active',
  triggered_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_news (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  summary TEXT NULL,
  source VARCHAR(80) NOT NULL DEFAULT 'Aihub FX',
  url VARCHAR(500) NULL,
  instrument_tags JSON NULL,
  published_at DATETIME NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_calendar_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_time DATETIME NOT NULL,
  country VARCHAR(60) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  title VARCHAR(200) NOT NULL,
  impact ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  forecast VARCHAR(40) NULL,
  previous VARCHAR(40) NULL,
  actual VARCHAR(40) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cal_time (event_time)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  title VARCHAR(80) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level_id INT NOT NULL,
  module_num INT NOT NULL,
  title VARCHAR(120) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  FOREIGN KEY (level_id) REFERENCES forex_academy_levels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_lessons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  lesson_num INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  content_type ENUM('video','pdf','text') NOT NULL DEFAULT 'text',
  content_url VARCHAR(500) NULL,
  content_html MEDIUMTEXT NULL,
  duration_minutes INT NOT NULL DEFAULT 5,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  FOREIGN KEY (module_id) REFERENCES forex_academy_modules(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_quizzes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module_id INT NOT NULL,
  title VARCHAR(120) NOT NULL,
  pass_score_pct INT NOT NULL DEFAULT 70,
  time_limit_min INT NULL,
  FOREIGN KEY (module_id) REFERENCES forex_academy_modules(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quiz_id INT NOT NULL,
  question_text VARCHAR(500) NOT NULL,
  choices_json JSON NOT NULL,
  correct_index INT NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (quiz_id) REFERENCES forex_academy_quizzes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_progress (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  lesson_id INT NOT NULL,
  status ENUM('not_started','in_progress','completed') NOT NULL DEFAULT 'not_started',
  progress_pct INT NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  UNIQUE KEY uq_progress (user_id, lesson_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (lesson_id) REFERENCES forex_academy_lessons(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_quiz_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  quiz_id INT NOT NULL,
  score_pct INT NOT NULL,
  passed TINYINT(1) NOT NULL DEFAULT 0,
  answers_json JSON NULL,
  attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (quiz_id) REFERENCES forex_academy_quizzes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  level_id INT NOT NULL,
  certificate_code VARCHAR(40) NOT NULL UNIQUE,
  issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (level_id) REFERENCES forex_academy_levels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_academy_bookmarks (
  user_id INT NOT NULL,
  lesson_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, lesson_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (lesson_id) REFERENCES forex_academy_lessons(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_signals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  instrument_id INT NOT NULL,
  direction ENUM('buy','sell') NOT NULL,
  entry_price DECIMAL(18,8) NOT NULL,
  stop_loss DECIMAL(18,8) NULL,
  take_profit DECIMAL(18,8) NULL,
  risk_rating ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  analyst_note TEXT NULL,
  status ENUM('active','closed','expired') NOT NULL DEFAULT 'active',
  result ENUM('pending','win','loss','expired') NOT NULL DEFAULT 'pending',
  published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  closed_at DATETIME NULL,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_community_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  instrument_id INT NULL,
  post_type ENUM('discussion','analysis','education') NOT NULL DEFAULT 'discussion',
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  likes_count INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (instrument_id) REFERENCES forex_instruments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_community_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES forex_community_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_community_likes (
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, user_id),
  FOREIGN KEY (post_id) REFERENCES forex_community_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS forex_trader_stats (
  user_id INT PRIMARY KEY,
  total_trades INT NOT NULL DEFAULT 0,
  winning_trades INT NOT NULL DEFAULT 0,
  total_pnl DECIMAL(18,2) NOT NULL DEFAULT 0,
  best_trade DECIMAL(18,2) NOT NULL DEFAULT 0,
  worst_trade DECIMAL(18,2) NOT NULL DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('forex_demo_start_balance', '10000'),
('forex_price_provider', 'simulated'),
('forex_commission_per_lot', '7'),
('forex_default_leverage', '100');

INSERT IGNORE INTO forex_instruments (symbol, display_name, category, base_currency, quote_currency, pip_size, seed_price, spread_pips, sort_order) VALUES
('EURUSD', 'EUR/USD', 'major', 'EUR', 'USD', 0.0001, 1.08500, 0.8, 1),
('GBPUSD', 'GBP/USD', 'major', 'GBP', 'USD', 0.0001, 1.26500, 1.0, 2),
('USDJPY', 'USD/JPY', 'major', 'USD', 'JPY', 0.01, 149.500, 0.9, 3),
('USDCHF', 'USD/CHF', 'major', 'USD', 'CHF', 0.0001, 0.88200, 1.2, 4),
('AUDUSD', 'AUD/USD', 'major', 'AUD', 'USD', 0.0001, 0.65800, 1.0, 5),
('USDCAD', 'USD/CAD', 'major', 'USD', 'CAD', 0.0001, 1.35800, 1.1, 6),
('NZDUSD', 'NZD/USD', 'major', 'NZD', 'USD', 0.0001, 0.61200, 1.2, 7),
('EURGBP', 'EUR/GBP', 'minor', 'EUR', 'GBP', 0.0001, 0.85800, 1.0, 10),
('EURJPY', 'EUR/JPY', 'minor', 'EUR', 'JPY', 0.01, 162.200, 1.2, 11),
('GBPJPY', 'GBP/JPY', 'minor', 'GBP', 'JPY', 0.01, 189.100, 1.5, 12),
('AUDJPY', 'AUD/JPY', 'minor', 'AUD', 'JPY', 0.01, 98.400, 1.3, 13),
('EURAUD', 'EUR/AUD', 'minor', 'EUR', 'AUD', 0.0001, 1.64800, 1.5, 14),
('USDTRY', 'USD/TRY', 'exotic', 'USD', 'TRY', 0.0001, 32.1500, 3.0, 20),
('USDZAR', 'USD/ZAR', 'exotic', 'USD', 'ZAR', 0.0001, 18.4500, 2.5, 21),
('USDMXN', 'USD/MXN', 'exotic', 'USD', 'MXN', 0.0001, 17.1200, 2.0, 22),
('XAUUSD', 'Gold', 'commodity', 'XAU', 'USD', 0.01, 2345.50, 2.5, 30),
('XAGUSD', 'Silver', 'commodity', 'XAG', 'USD', 0.001, 27.850, 2.0, 31),
('USOIL', 'WTI Oil', 'commodity', 'OIL', 'USD', 0.01, 78.50, 3.0, 32),
('NAS100', 'NASDAQ 100', 'index', 'NAS', 'USD', 0.1, 18250.0, 5.0, 40),
('SPX500', 'S&P 500', 'index', 'SPX', 'USD', 0.1, 5280.0, 4.0, 41),
('US30', 'Dow Jones', 'index', 'DOW', 'USD', 1.0, 39100.0, 6.0, 42),
('UK100', 'FTSE 100', 'index', 'FTSE', 'GBP', 0.1, 8120.0, 4.0, 43),
('BTCUSD', 'Bitcoin', 'crypto', 'BTC', 'USD', 0.01, 67500.0, 10.0, 50),
('ETHUSD', 'Ethereum', 'crypto', 'ETH', 'USD', 0.01, 3450.0, 5.0, 51);

INSERT IGNORE INTO forex_academy_levels (code, title, sort_order) VALUES
('beginner', 'Beginner', 1),
('intermediate', 'Intermediate', 2),
('advanced', 'Advanced', 3);

INSERT IGNORE INTO forex_academy_modules (level_id, module_num, title, description, sort_order) VALUES
(1, 1, 'Forex Foundations', 'What is Forex and how markets work', 1),
(1, 2, 'Market Participants', 'Brokers, liquidity, central banks', 2),
(1, 3, 'Trading Sessions', 'London, New York, Tokyo, Sydney', 3),
(2, 4, 'Chart Reading', 'Candlesticks, trends, support & resistance', 4),
(2, 5, 'Technical Analysis', 'MA, RSI, MACD, Bollinger Bands', 5),
(2, 6, 'Fundamental Analysis', 'Rates, inflation, employment, calendar', 6),
(3, 7, 'Smart Money Concepts', 'Order blocks, liquidity, fair value gaps', 7),
(3, 8, 'Risk Management', 'Position sizing, capital preservation', 8),
(3, 9, 'Trading Psychology', 'Emotions, discipline, journals', 9);

INSERT IGNORE INTO forex_academy_lessons (module_id, lesson_num, title, content_type, content_html, duration_minutes, sort_order) VALUES
(1, 1, 'What is Forex', 'text', '<p>Forex (foreign exchange) is the global marketplace for trading national currencies. With over $7 trillion daily volume, it is the largest financial market in the world.</p>', 8, 1),
(1, 2, 'Market Structure', 'text', '<p>The forex market is decentralized (OTC). Major tiers: interbank, institutional, retail brokers, and traders.</p>', 10, 2),
(1, 3, 'Currency Pairs', 'text', '<p>Pairs show how much quote currency is needed to buy one unit of base currency. EUR/USD = euros priced in US dollars.</p>', 8, 3),
(1, 4, 'Bid and Ask', 'text', '<p>Bid is the price buyers pay; ask is what sellers receive. The spread is the broker profit zone.</p>', 7, 4),
(1, 5, 'Pips and Lots', 'text', '<p>A pip is the smallest price move. Standard lot = 100,000 units. Mini = 10,000. Micro = 1,000.</p>', 10, 5),
(2, 1, 'Market Participants', 'text', '<p>Central banks, commercial banks, hedge funds, corporations, and retail traders all participate with different goals.</p>', 8, 1),
(2, 2, 'Brokers', 'text', '<p>Brokers provide access to liquidity. Choose regulated brokers with transparent spreads and execution.</p>', 8, 2),
(2, 3, 'Liquidity Providers', 'text', '<p>LPs supply the order book depth. ECN/STP models route orders to real market prices.</p>', 7, 3),
(2, 4, 'Central Banks', 'text', '<p>Central banks set interest rates and intervene in currency markets to stabilize economies.</p>', 9, 4),
(3, 1, 'Session Overview', 'text', '<p>Forex trades 24/5. Volatility shifts as major financial centers open and close.</p>', 6, 1),
(3, 2, 'London Session', 'text', '<p>08:00–17:00 GMT. Highest volume. EUR, GBP, CHF pairs most active.</p>', 7, 2),
(3, 3, 'New York Session', 'text', '<p>13:00–22:00 GMT. Overlaps London for peak liquidity. USD pairs dominate.</p>', 7, 3),
(3, 4, 'Tokyo Session', 'text', '<p>00:00–09:00 GMT. JPY pairs active. Often range-bound until London opens.</p>', 6, 4),
(3, 5, 'Sydney Session', 'text', '<p>22:00–07:00 GMT. Opens the week. AUD and NZD pairs lead.</p>', 6, 5),
(4, 1, 'Candlestick Patterns', 'text', '<p>Each candle shows open, high, low, close. Doji, hammer, engulfing patterns signal reversals or continuations.</p>', 12, 1),
(4, 2, 'Chart Reading', 'text', '<p>Read price action: higher highs = uptrend, lower lows = downtrend. Timeframes from M1 to monthly.</p>', 10, 2),
(4, 3, 'Trend Analysis', 'text', '<p>Trade with the trend. Use moving averages and trendlines to confirm direction.</p>', 10, 3),
(4, 4, 'Support and Resistance', 'text', '<p>Key levels where price historically reverses. Breakouts signal momentum continuation.</p>', 11, 4),
(5, 1, 'Technical Analysis Intro', 'text', '<p>TA uses price history and indicators to forecast future moves. Combine multiple tools for confluence.</p>', 8, 1),
(5, 2, 'Moving Averages', 'text', '<p>SMA and EMA smooth price. Golden cross (50/200) signals bullish trend; death cross bearish.</p>', 10, 2),
(5, 3, 'RSI', 'text', '<p>Relative Strength Index (0–100). Above 70 overbought, below 30 oversold.</p>', 9, 3),
(5, 4, 'MACD', 'text', '<p>Moving Average Convergence Divergence. Histogram crossovers signal momentum shifts.</p>', 10, 4),
(5, 5, 'Bollinger Bands', 'text', '<p>Volatility bands around a moving average. Squeeze precedes breakouts; touches signal mean reversion.</p>', 10, 5),
(6, 1, 'Fundamental Analysis', 'text', '<p>FA analyzes economic data, policy, and geopolitics to predict currency strength.</p>', 9, 1),
(6, 2, 'Interest Rates', 'text', '<p>Higher rates attract capital, strengthening currency. Watch central bank decisions.</p>', 10, 2),
(6, 3, 'Inflation', 'text', '<p>Rising inflation may force rate hikes. CPI and PPI reports move markets instantly.</p>', 9, 3),
(6, 4, 'Employment Reports', 'text', '<p>NFP (US jobs) is the most volatile release. Strong jobs = USD bullish typically.</p>', 10, 4),
(6, 5, 'Economic Calendars', 'text', '<p>Track high-impact events. Avoid trading during major releases or use wider stops.</p>', 8, 5),
(7, 1, 'Smart Money Concepts', 'text', '<p>SMC tracks institutional order flow: where banks accumulate and distribute positions.</p>', 12, 1),
(7, 2, 'Order Blocks', 'text', '<p>Last opposing candle before a strong move. Price often returns to these zones.</p>', 11, 2),
(7, 3, 'Liquidity Zones', 'text', '<p>Equal highs/lows attract stop hunts. Smart money sweeps liquidity before reversing.</p>', 11, 3),
(7, 4, 'Fair Value Gaps', 'text', '<p>Imbalance gaps from fast moves. Price tends to fill FVGs before continuing.</p>', 10, 4),
(8, 1, 'Risk Management', 'text', '<p>Never risk more than 1–2% per trade. Survival is the first rule of trading.</p>', 10, 1),
(8, 2, 'Position Sizing', 'text', '<p>Lot size = (Account × Risk%) / (Stop pips × pip value). Use the platform calculator.</p>', 12, 2),
(8, 3, 'Capital Preservation', 'text', '<p>Protect drawdown limits. Reduce size after losses. Compound only with consistent edge.</p>', 10, 3),
(9, 1, 'Trading Psychology', 'text', '<p>Fear and greed destroy accounts. Follow your plan regardless of recent outcomes.</p>', 10, 1),
(9, 2, 'Emotional Control', 'text', '<p>Revenge trading after losses is the #1 account killer. Step away after 2 consecutive losses.</p>', 9, 2),
(9, 3, 'Discipline', 'text', '<p>Execute your strategy mechanically. If you cannot follow rules, you are gambling.</p>', 9, 3),
(9, 4, 'Trading Journals', 'text', '<p>Log every trade: setup, emotion, result, lesson. Review weekly to improve edge.</p>', 10, 4);

INSERT IGNORE INTO forex_academy_quizzes (module_id, title, pass_score_pct) VALUES
(1, 'Module 1 Quiz', 70),
(3, 'Module 3 Quiz', 70),
(5, 'Technical Analysis Quiz', 70);

INSERT IGNORE INTO forex_academy_questions (quiz_id, question_text, choices_json, correct_index, sort_order) VALUES
(1, 'What does EUR/USD represent?', '["Euro priced in US dollars","US dollar priced in euros","Gold priced in euros","Euro vs British pound"]', 0, 1),
(1, 'What is a pip?', '["Smallest standard price move","Broker commission","Daily swap fee","Lot size unit"]', 0, 2),
(1, 'Standard lot size is:', '["100,000 units","10,000 units","1,000 units","1 unit"]', 0, 3),
(2, 'Which session has highest volume?', '["London","Sydney","Tokyo only","Weekend"]', 0, 1),
(2, 'USD pairs peak during overlap of:', '["London and New York","Tokyo and Sydney","Weekend gap","None"]', 0, 2),
(3, 'RSI above 70 typically indicates:', '["Overbought","Oversold","No signal","Breakout"]', 0, 1),
(3, 'MACD histogram crossing zero suggests:', '["Momentum shift","Spread widening","Market closed","Swap charge"]', 0, 2);

INSERT IGNORE INTO forex_news (title, summary, source, published_at) VALUES
('Fed holds rates steady, USD mixed', 'Federal Reserve kept policy unchanged; markets watch dot plot for 2026 guidance.', 'Aihub FX', NOW()),
('ECB signals cautious easing path', 'Euro volatility expected as Lagarde emphasizes data dependency.', 'Aihub FX', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
('Gold hits new high on safe-haven demand', 'XAU/USD extends rally amid geopolitical uncertainty.', 'Aihub FX', DATE_SUB(NOW(), INTERVAL 5 HOUR));

INSERT IGNORE INTO forex_calendar_events (event_time, country, currency, title, impact, forecast, previous) VALUES
(DATE_ADD(NOW(), INTERVAL 1 DAY), 'United States', 'USD', 'Non-Farm Payrolls', 'high', '180K', '175K'),
(DATE_ADD(NOW(), INTERVAL 2 DAY), 'Eurozone', 'EUR', 'ECB Rate Decision', 'high', '4.25%', '4.25%'),
(DATE_ADD(NOW(), INTERVAL 3 DAY), 'United Kingdom', 'GBP', 'GDP m/m', 'medium', '0.2%', '0.1%');