-- ============================================================
--  FLab 1 — Futures Paper Trading Schema
--  Key difference from Lab 4: `direction` column (LONG / SHORT)
--  P&L for SHORT = (entry_price - exit_price) / entry_price * 100
--  Run via install.php — do NOT add USE or CREATE DATABASE here.
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

CREATE TABLE IF NOT EXISTS experiments (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_key   VARCHAR(100) NOT NULL UNIQUE,
  name             VARCHAR(255) NOT NULL,
  started_at       DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  code_version     VARCHAR(128) NULL,
  gate_policy      VARCHAR(64) NOT NULL DEFAULT 'FLAB1_FUTURES',
  notes            TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS market_snapshots (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_id    BIGINT UNSIGNED NOT NULL,
  generated_ms     BIGINT NOT NULL,
  deep_generated_ms BIGINT NULL,
  regime           VARCHAR(32) NULL,
  breadth_up_pct   DOUBLE NULL,
  btc_change24h    DOUBLE NULL,
  eth_change24h    DOUBLE NULL,
  eligible_pairs   INT NULL,
  deep_count       INT NULL,
  snapshot_json    LONGTEXT NOT NULL,
  UNIQUE KEY uq_snapshot_time (experiment_id, generated_ms),
  KEY idx_snapshot_time (experiment_id, generated_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS open_trades (
  trade_id             VARCHAR(96) PRIMARY KEY,
  experiment_id        BIGINT UNSIGNED NOT NULL,
  direction            VARCHAR(5)  NOT NULL DEFAULT 'LONG',   -- LONG or SHORT
  symbol               VARCHAR(32) NOT NULL,
  coin                 VARCHAR(32) NOT NULL,
  opened_ms            BIGINT NOT NULL,
  opened_at            DATETIME(3) NOT NULL,
  entry_price          DOUBLE NOT NULL,
  entry_bid            DOUBLE NULL,
  entry_ask            DOUBLE NULL,
  quantity             DOUBLE NOT NULL,
  cost                 DOUBLE NOT NULL,
  entry_fee            DOUBLE NOT NULL,
  entry_spread_pct     DOUBLE NULL,
  base_stop_price      DOUBLE NOT NULL,
  dynamic_stop_price   DOUBLE NOT NULL,
  stop_stage           TINYINT NOT NULL DEFAULT 0,   -- 0=NONE 1=PROTECT 2=LOCK 3=TRACK
  current_bid          DOUBLE NOT NULL,
  peak_price           DOUBLE NOT NULL,              -- highest for LONG, lowest for SHORT
  peak_profit_pct      DOUBLE NOT NULL DEFAULT 0,
  mfe_pct              DOUBLE NOT NULL DEFAULT 0,
  mae_pct              DOUBLE NOT NULL DEFAULT 0,
  entry_score          DOUBLE NULL,
  entry_regime         VARCHAR(32) NULL,
  entry_volatility     VARCHAR(64) NULL,
  entry_breadth        DOUBLE NULL,
  entry_micro15        DOUBLE NULL,
  entry_micro30        DOUBLE NULL,
  entry_btc24h         DOUBLE NULL,
  entry_hour_utc       TINYINT NULL,
  entry_day_of_week    TINYINT NULL,
  entry_context_json   LONGTEXT NULL,
  entry_market_json    LONGTEXT NULL,
  KEY idx_open_sym   (experiment_id, symbol),
  KEY idx_open_dir   (experiment_id, direction),
  KEY idx_open_time  (experiment_id, opened_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS closed_trades (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  trade_id             VARCHAR(96) NOT NULL UNIQUE,
  experiment_id        BIGINT UNSIGNED NOT NULL,
  direction            VARCHAR(5)  NOT NULL DEFAULT 'LONG',
  symbol               VARCHAR(32) NOT NULL,
  coin                 VARCHAR(32) NOT NULL,
  opened_ms            BIGINT NOT NULL,
  closed_ms            BIGINT NOT NULL,
  holding_ms           BIGINT NOT NULL,
  entry_price          DOUBLE NOT NULL,
  exit_price           DOUBLE NOT NULL,
  quantity             DOUBLE NOT NULL,
  trade_cost           DOUBLE NOT NULL,
  entry_fee            DOUBLE NOT NULL,
  exit_fee             DOUBLE NOT NULL,
  total_fees           DOUBLE NOT NULL,
  entry_spread_pct     DOUBLE NULL,
  exit_spread_pct      DOUBLE NULL,
  gross_price_move_pct DOUBLE NOT NULL,   -- direction-adjusted price move
  net_profit           DOUBLE NOT NULL,
  profit_percent       DOUBLE NOT NULL,
  exit_reason          VARCHAR(64) NOT NULL,
  peak_profit_pct      DOUBLE NOT NULL,
  mfe_pct              DOUBLE NOT NULL,
  mae_pct              DOUBLE NOT NULL,
  max_stop_stage       TINYINT NOT NULL DEFAULT 0,
  entry_score          DOUBLE NULL,
  entry_regime         VARCHAR(32) NULL,
  entry_volatility     VARCHAR(64) NULL,
  entry_breadth        DOUBLE NULL,
  entry_micro15        DOUBLE NULL,
  entry_micro30        DOUBLE NULL,
  entry_btc24h         DOUBLE NULL,
  entry_hour_utc       TINYINT NULL,
  entry_day_of_week    TINYINT NULL,
  entry_context_json   LONGTEXT NULL,
  entry_market_json    LONGTEXT NULL,
  exit_context_json    LONGTEXT NULL,
  closed_at            DATETIME(3) NOT NULL,
  KEY idx_closed_dir    (experiment_id, direction, closed_ms),
  KEY idx_closed_sym    (experiment_id, symbol, closed_ms),
  KEY idx_closed_pnl    (experiment_id, net_profit),
  KEY idx_closed_reason (experiment_id, exit_reason),
  KEY idx_closed_hour   (experiment_id, entry_hour_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS worker_heartbeat (
  worker_name      VARCHAR(64) PRIMARY KEY,
  experiment_id    BIGINT UNSIGNED NULL,
  last_seen_ms     BIGINT NOT NULL,
  last_duration_ms BIGINT NULL,
  status           VARCHAR(32) NOT NULL,
  detail_json      LONGTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_errors (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_id  BIGINT UNSIGNED NULL,
  worker_name    VARCHAR(64) NULL,
  occurred_ms    BIGINT NOT NULL,
  message        TEXT NOT NULL,
  details_json   LONGTEXT NULL,
  KEY idx_error_time (occurred_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
