-- ============================================================
--  TradingLab — Complete MySQL Schema
--  All tables: DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
--  This fixes the "Incorrect string value" error for
--  non-ASCII coin symbols (e.g. Chinese meme coin names).
--  Run via install.php — do NOT run USE or CREATE DATABASE here
--  so it works with any cPanel database name.
-- ============================================================

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,
  classifier_version VARCHAR(64) NOT NULL DEFAULT 'lab2_v1',
  gate_policy      VARCHAR(64) NOT NULL,
  notes            TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS strategy_state (
  experiment_id    BIGINT UNSIGNED NOT NULL,
  version          INT NOT NULL,
  name             VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  style            VARCHAR(64)  NOT NULL,
  profile_json     LONGTEXT     NOT NULL,
  allowed_regimes  VARCHAR(255) NULL,
  allowed_volatility VARCHAR(255) NULL,
  balance          DOUBLE NOT NULL DEFAULT 50000,
  completed        BIGINT NOT NULL DEFAULT 0,
  wins             BIGINT NOT NULL DEFAULT 0,
  losses           BIGINT NOT NULL DEFAULT 0,
  realized         DOUBLE NOT NULL DEFAULT 0,
  gross_wins       DOUBLE NOT NULL DEFAULT 0,
  gross_losses     DOUBLE NOT NULL DEFAULT 0,
  max_drawdown     DOUBLE NOT NULL DEFAULT 0,
  peak_equity      DOUBLE NOT NULL DEFAULT 50000,
  updated_ms       BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (experiment_id, version),
  KEY idx_state_realized (experiment_id, realized),
  KEY idx_state_style    (experiment_id, style)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS strategy_market_rules (
  experiment_id   BIGINT UNSIGNED NOT NULL,
  version         INT NOT NULL,
  dimension       VARCHAR(64)  NOT NULL,
  condition_name  VARCHAR(128) NOT NULL,
  allowed         TINYINT(1)   NOT NULL DEFAULT 1,
  rule_source     VARCHAR(64)  NOT NULL DEFAULT 'LAB1',
  rule_mode       VARCHAR(64)  NOT NULL DEFAULT 'PROFITABLE_AFTER_FEES',
  created_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (experiment_id, version, dimension, condition_name),
  KEY idx_rules_lookup (experiment_id, version, dimension, allowed)
) 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 opportunities (
  id                         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_id              BIGINT UNSIGNED NOT NULL,
  version                    INT NOT NULL,
  symbol                     VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  coin                       VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  first_seen_ms              BIGINT NOT NULL,
  last_seen_ms               BIGINT NOT NULL,
  confirmation_due_ms        BIGINT NULL,
  confirmation_checked_ms    BIGINT NULL,
  decision_ms                BIGINT NULL,
  trigger_price              DOUBLE NOT NULL,
  decision_mid               DOUBLE NULL,
  decision_bid               DOUBLE NULL,
  decision_ask               DOUBLE NULL,
  configured_confirmation_seconds INT NOT NULL DEFAULT 0,
  actual_confirmation_ms     BIGINT NULL,
  outcome                    VARCHAR(64)  NOT NULL DEFAULT 'PENDING',
  rejection_reason           VARCHAR(128) NULL,
  gate_allowed               TINYINT(1)   NULL,
  current_regime             VARCHAR(32)  NULL,
  current_volatility         VARCHAR(64)  NULL,
  allowed_regimes            VARCHAR(255) NULL,
  allowed_volatility         VARCHAR(255) NULL,
  evaluation_json            LONGTEXT NULL,
  market_json                LONGTEXT NULL,
  context_json               LONGTEXT NULL,
  market_tags_json           LONGTEXT NULL,
  profile_json               LONGTEXT NULL,
  created_at                 DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY idx_opp_version_time (experiment_id, version, first_seen_ms),
  KEY idx_opp_outcome      (experiment_id, outcome, decision_ms),
  KEY idx_opp_symbol       (experiment_id, symbol, first_seen_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pending_entries (
  experiment_id        BIGINT UNSIGNED NOT NULL,
  version              INT NOT NULL,
  symbol               VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  opportunity_id       BIGINT UNSIGNED NOT NULL,
  coin                 VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  first_seen_ms        BIGINT NOT NULL,
  last_seen_ms         BIGINT NOT NULL,
  confirmation_due_ms  BIGINT NOT NULL,
  trigger_price        DOUBLE NOT NULL,
  status               VARCHAR(32) NOT NULL,
  evaluation_json      LONGTEXT NULL,
  PRIMARY KEY (experiment_id, version, symbol),
  KEY idx_pending_due  (experiment_id, confirmation_due_ms, status)
) 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,
  opportunity_id             BIGINT UNSIGNED NULL,
  version                    INT NOT NULL,
  symbol                     VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  coin                       VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  opened_ms                  BIGINT NOT NULL,
  opened_at                  DATETIME(3) NOT NULL,
  trigger_price              DOUBLE NULL,
  decision_mid               DOUBLE NULL,
  entry_bid                  DOUBLE NULL,
  entry_ask                  DOUBLE NULL,
  entry_price                DOUBLE NOT NULL,
  quantity                   DOUBLE NOT NULL,
  cost                       DOUBLE NOT NULL,
  entry_fee                  DOUBLE NOT NULL,
  entry_spread_pct           DOUBLE NULL,
  entry_slippage_pct         DOUBLE NOT NULL DEFAULT 0,
  configured_confirmation_seconds INT NULL,
  actual_confirmation_ms     BIGINT NULL,
  risk_pct                   DOUBLE NOT NULL,
  base_stop_price            DOUBLE NOT NULL,
  dynamic_stop_price         DOUBLE NOT NULL,
  current_bid                DOUBLE NOT NULL,
  current_ask                DOUBLE NULL,
  highest_bid                DOUBLE NOT NULL,
  lowest_bid                 DOUBLE NOT NULL,
  peak_profit_pct            DOUBLE NOT NULL DEFAULT 0,
  mfe_pct                    DOUBLE NOT NULL DEFAULT 0,
  mae_pct                    DOUBLE NOT NULL DEFAULT 0,
  first_plus_05_ms           BIGINT NULL,
  first_minus_05_ms          BIGINT NULL,
  exit_mode                  VARCHAR(32) NOT NULL DEFAULT 'NORMAL',
  market_regime              VARCHAR(32) NULL,
  volatility_tag             VARCHAR(64) NULL,
  entry_hour_utc             TINYINT NULL,
  entry_day_of_week          TINYINT NULL,
  coin_sector                VARCHAR(32) NULL,
  entry_evaluation_json      LONGTEXT NULL,
  entry_market_json          LONGTEXT NULL,
  entry_context_json         LONGTEXT NULL,
  entry_tags_json            LONGTEXT NULL,
  last_evaluation_json       LONGTEXT NULL,
  last_market_json           LONGTEXT NULL,
  last_context_json          LONGTEXT NULL,
  profile_json               LONGTEXT NOT NULL,
  KEY idx_open_version (experiment_id, version),
  KEY idx_open_symbol  (experiment_id, symbol),
  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,
  opportunity_id             BIGINT UNSIGNED NULL,
  version                    INT NOT NULL,
  symbol                     VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  coin                       VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  opened_ms                  BIGINT NOT NULL,
  closed_ms                  BIGINT NOT NULL,
  holding_ms                 BIGINT NOT NULL,
  trigger_price              DOUBLE NULL,
  decision_mid               DOUBLE NULL,
  entry_bid                  DOUBLE NULL,
  entry_ask                  DOUBLE NULL,
  entry_price                DOUBLE NOT NULL,
  exit_trigger_price         DOUBLE NULL,
  exit_bid                   DOUBLE NULL,
  exit_ask                   DOUBLE 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,
  entry_slippage_pct         DOUBLE NULL,
  exit_slippage_pct          DOUBLE NULL,
  gross_price_move_pct       DOUBLE NOT NULL,
  net_profit                 DOUBLE NOT NULL,
  profit_percent             DOUBLE NOT NULL,
  exit_reason                VARCHAR(64) NOT NULL,
  risk_pct                   DOUBLE NOT NULL,
  lowest_bid                 DOUBLE NOT NULL,
  highest_bid                DOUBLE NOT NULL,
  mfe_pct                    DOUBLE NOT NULL,
  mae_pct                    DOUBLE NOT NULL,
  peak_profit_pct            DOUBLE NOT NULL,
  captured_peak_ratio        DOUBLE NULL,
  time_to_plus_05_ms         BIGINT NULL,
  time_to_minus_05_ms        BIGINT NULL,
  configured_confirmation_seconds INT NULL,
  actual_confirmation_ms     BIGINT NULL,
  entry_score                DOUBLE NULL,
  final_score                DOUBLE NULL,
  entry_style                VARCHAR(64) NULL,
  entry_setups               LONGTEXT NULL,
  entry_regime               VARCHAR(32) NULL,
  entry_volatility           VARCHAR(64) NULL,
  exit_regime                VARCHAR(32) NULL,
  exit_volatility            VARCHAR(64) NULL,
  entry_hour_utc             TINYINT NULL,
  entry_day_of_week          TINYINT NULL,
  coin_sector                VARCHAR(32) NULL,
  entry_evaluation_json      LONGTEXT NULL,
  exit_evaluation_json       LONGTEXT NULL,
  entry_market_json          LONGTEXT NULL,
  exit_market_json           LONGTEXT NULL,
  entry_context_json         LONGTEXT NULL,
  exit_context_json          LONGTEXT NULL,
  entry_tags_json            LONGTEXT NULL,
  exit_tags_json             LONGTEXT NULL,
  profile_json               LONGTEXT NOT NULL,
  closed_at                  DATETIME(3) NOT NULL,
  KEY idx_closed_version_time (experiment_id, version, closed_ms),
  KEY idx_closed_market       (experiment_id, entry_regime, entry_volatility),
  KEY idx_closed_symbol       (experiment_id, symbol, closed_ms),
  KEY idx_closed_pnl          (experiment_id, net_profit),
  KEY idx_closed_hour         (experiment_id, entry_hour_utc),
  KEY idx_closed_sector       (experiment_id, coin_sector)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trade_checkpoints (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  trade_id           VARCHAR(96) NOT NULL,
  experiment_id      BIGINT UNSIGNED NOT NULL,
  version            INT NOT NULL,
  symbol             VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  target_ms          BIGINT NOT NULL,
  captured_ms        BIGINT NOT NULL,
  actual_age_ms      BIGINT NOT NULL,
  lateness_ms        BIGINT NOT NULL,
  bid                DOUBLE NOT NULL,
  ask                DOUBLE NULL,
  gross_pct          DOUBLE NOT NULL,
  net_estimate       DOUBLE NOT NULL,
  mfe_pct            DOUBLE NOT NULL,
  mae_pct            DOUBLE NOT NULL,
  dynamic_stop_price DOUBLE NOT NULL,
  exit_mode          VARCHAR(32) NOT NULL,
  regime             VARCHAR(32) NULL,
  volatility_tag     VARCHAR(64) NULL,
  evaluation_json    LONGTEXT NULL,
  market_json        LONGTEXT NULL,
  context_json       LONGTEXT NULL,
  tags_json          LONGTEXT NULL,
  UNIQUE KEY uq_checkpoint       (trade_id, target_ms),
  KEY idx_checkpoint_version     (experiment_id, version, target_ms),
  KEY idx_checkpoint_time        (experiment_id, captured_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trade_events (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  trade_id       VARCHAR(96) NOT NULL,
  experiment_id  BIGINT UNSIGNED NOT NULL,
  version        INT NOT NULL,
  symbol         VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  event_type     VARCHAR(64) NOT NULL,
  event_ms       BIGINT NOT NULL,
  bid            DOUBLE NULL,
  ask            DOUBLE NULL,
  value1         DOUBLE NULL,
  value2         DOUBLE NULL,
  details_json   LONGTEXT NULL,
  KEY idx_event_trade (trade_id, event_ms),
  KEY idx_event_type  (experiment_id, event_type, event_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shadow_open_trades (
  shadow_id          VARCHAR(96) PRIMARY KEY,
  experiment_id      BIGINT UNSIGNED NOT NULL,
  opportunity_id     BIGINT UNSIGNED NOT NULL,
  version            INT NOT NULL,
  symbol             VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  coin               VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  opened_ms          BIGINT NOT NULL,
  entry_price        DOUBLE NOT NULL,
  quantity           DOUBLE NOT NULL,
  cost               DOUBLE NOT NULL,
  entry_fee          DOUBLE NOT NULL,
  risk_pct           DOUBLE NOT NULL,
  base_stop_price    DOUBLE NOT NULL,
  dynamic_stop_price DOUBLE NOT NULL,
  highest_bid        DOUBLE NOT NULL,
  lowest_bid         DOUBLE NOT NULL,
  mfe_pct            DOUBLE NOT NULL DEFAULT 0,
  mae_pct            DOUBLE NOT NULL DEFAULT 0,
  peak_profit_pct    DOUBLE NOT NULL DEFAULT 0,
  exit_mode          VARCHAR(32) NOT NULL DEFAULT 'NORMAL',
  entry_regime       VARCHAR(32) NULL,
  entry_volatility   VARCHAR(64) NULL,
  profile_json       LONGTEXT NOT NULL,
  KEY idx_shadow_version (experiment_id, version),
  KEY idx_shadow_symbol  (experiment_id, symbol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shadow_closed_trades (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shadow_id      VARCHAR(96) NOT NULL UNIQUE,
  experiment_id  BIGINT UNSIGNED NOT NULL,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  version        INT NOT NULL,
  symbol         VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  opened_ms      BIGINT NOT NULL,
  closed_ms      BIGINT NOT NULL,
  entry_price    DOUBLE NOT NULL,
  exit_price     DOUBLE NOT NULL,
  net_profit     DOUBLE NOT NULL,
  profit_percent DOUBLE NOT NULL,
  exit_reason    VARCHAR(64) NOT NULL,
  entry_regime   VARCHAR(32) NULL,
  entry_volatility VARCHAR(64) NULL,
  KEY idx_shadow_closed_version (experiment_id, version, closed_ms)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tracks signals that couldn't open because max_open was full (Section 18 of master plan)
CREATE TABLE IF NOT EXISTS missed_trades (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_id  BIGINT UNSIGNED NOT NULL,
  version        INT NOT NULL,
  symbol         VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  reason         VARCHAR(64) NOT NULL DEFAULT 'MAX_OPEN',
  signal_score   FLOAT NULL,
  tick_ms        BIGINT NOT NULL,
  regime         VARCHAR(32) NULL,
  volatility_tag VARCHAR(64) NULL,
  KEY idx_missed (experiment_id, version, tick_ms)
) 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;
