-- BEE Results V2: immutable terminal summaries and telemetry snapshots.
CREATE TABLE IF NOT EXISTS `bee_test_result_v2` (
  `test_id` BIGINT UNSIGNED NOT NULL,
  `initial_equity` DECIMAL(30,12) NOT NULL,
  `final_equity` DECIMAL(30,12) NOT NULL,
  `gross_pnl` DECIMAL(30,12) NOT NULL,
  `net_pnl` DECIMAL(30,12) NOT NULL,
  `return_percent` DECIMAL(20,8) NULL,
  `fees` DECIMAL(30,12) NOT NULL,
  `max_drawdown` DECIMAL(20,8) NULL,
  `peak_timestamp` BIGINT NULL,
  `trough_timestamp` BIGINT NULL,
  `closed_trades` INT UNSIGNED NOT NULL,
  `wins` INT UNSIGNED NOT NULL,
  `losses` INT UNSIGNED NOT NULL,
  `win_rate` DECIMAL(20,8) NOT NULL,
  `profit_factor` DECIMAL(30,12) NULL,
  `avg_win` DECIMAL(30,12) NOT NULL,
  `best_win` DECIMAL(30,12) NOT NULL,
  `worst_win` DECIMAL(30,12) NOT NULL,
  `avg_loss` DECIMAL(30,12) NOT NULL,
  `best_loss` DECIMAL(30,12) NOT NULL,
  `worst_loss` DECIMAL(30,12) NOT NULL,
  `max_consecutive_wins` INT UNSIGNED NOT NULL,
  `max_consecutive_losses` INT UNSIGNED NOT NULL,
  `orders_count` INT UNSIGNED NOT NULL,
  `transactions_count` INT UNSIGNED NOT NULL,
  `profit_hits` INT UNSIGNED NOT NULL,
  `stop_loss_hits` INT UNSIGNED NOT NULL,
  `phase_hits` JSON NOT NULL,
  `runtime_seconds` INT UNSIGNED NOT NULL,
  `termination_reason` VARCHAR(64) NOT NULL,
  `terminal_timestamp` BIGINT NOT NULL,
  `end_reached` TINYINT(1) NOT NULL,
  `data_completeness` VARCHAR(32) NOT NULL,
  `incomplete_reasons` JSON NOT NULL,
  `methodology_version` VARCHAR(16) NOT NULL,
  `terminal_position` JSON NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bee_test_equity_point_v2` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_id` BIGINT UNSIGNED NOT NULL,
  `timestamp` BIGINT NOT NULL,
  `wallet_balance` DECIMAL(30,12) NOT NULL,
  `unrealized_pnl` DECIMAL(30,12) NOT NULL,
  `equity` DECIMAL(30,12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bee_test_equity_point_v2` (`test_id`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bee_test_indicator_point_v2` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_id` BIGINT UNSIGNED NOT NULL,
  `series_id` VARCHAR(128) NOT NULL,
  `timestamp` BIGINT NOT NULL,
  `series_type` VARCHAR(64) NOT NULL,
  `parameters_hash` CHAR(64) NOT NULL,
  `strategy_revision_id` BIGINT UNSIGNED NOT NULL,
  `strategy_snapshot_hash` CHAR(64) NOT NULL,
  `phase` INT NULL,
  `purpose` VARCHAR(64) NOT NULL,
  `pane` VARCHAR(32) NOT NULL,
  `unit` VARCHAR(32) NOT NULL,
  `value` DECIMAL(30,12) NOT NULL,
  `price_context` JSON NOT NULL,
  `decision_context` JSON NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bee_test_indicator_point_v2` (`test_id`, `series_id`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
