-- 全部 hd_* 表结构（幂等）
-- 生成自 src/hdiv/data/schema.py

-- 分红送转全明细（plan.md §14/§30 的数据基座）
CREATE TABLE IF NOT EXISTS `hd_dividend` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`        VARCHAR(12)  NOT NULL,
  `end_date`      DATE         NOT NULL COMMENT '分红对应报告期',
  `ann_date`      DATE         NULL     COMMENT '预案公告日',
  `imp_ann_date`  DATE         NULL     COMMENT '实施公告日 —— PIT 关键字段',
  `div_proc`      VARCHAR(16)  NOT NULL COMMENT '预案/股东大会通过/实施/取消',
  `dedup_key`     VARCHAR(80)  NOT NULL COMMENT '去重键：NULL 不参与 MySQL 唯一约束，故显式构造',
  `stk_div`       DECIMAL(16,6) NULL    COMMENT '每股送转股',
  `stk_bo_rate`   DECIMAL(16,6) NULL    COMMENT '每股送股比例',
  `stk_co_rate`   DECIMAL(16,6) NULL    COMMENT '每股转增比例',
  `cash_div`      DECIMAL(16,6) NULL    COMMENT '每股税后现金分红（元）',
  `cash_div_tax`  DECIMAL(16,6) NULL    COMMENT '每股税前现金分红（元）—— 连续性判定口径',
  `record_date`   DATE         NULL     COMMENT '股权登记日',
  `ex_date`       DATE         NULL     COMMENT '除权除息日',
  `pay_date`      DATE         NULL     COMMENT '派息日',
  `div_listdate`  DATE         NULL     COMMENT '红股上市日',
  `base_share`    DECIMAL(24,4) NULL    COMMENT '基准股本（万股）',
  `source`        VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_div_key` (`dedup_key`),
  KEY `ix_hd_div_ex`  (`symbol`,`ex_date`),
  KEY `ix_hd_div_pit` (`symbol`,`imp_ann_date`),
  KEY `ix_hd_div_proc` (`div_proc`,`ex_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='分红送转全明细';

-- 扩展财务指标（G4；带 announce_date 以支持 PIT）
CREATE TABLE IF NOT EXISTS `hd_fina_indicator` (
  `id`                  BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`              VARCHAR(12)  NOT NULL,
  `end_date`            DATE         NOT NULL COMMENT '报告期',
  `ann_date`            DATE         NOT NULL COMMENT '公告日 —— PIT 关键字段',
  `eps`                 DECIMAL(16,6) NULL,
  `roe`                 DECIMAL(16,6) NULL COMMENT '净资产收益率 %',
  `roe_waa`             DECIMAL(16,6) NULL COMMENT '加权平均 ROE %',
  `roe_dt`              DECIMAL(16,6) NULL COMMENT '扣非 ROE %',
  `roa`                 DECIMAL(16,6) NULL,
  `roic`                DECIMAL(16,6) NULL COMMENT '投入资本回报率 %',
  `grossprofit_margin`  DECIMAL(16,6) NULL COMMENT '毛利率 %',
  `netprofit_margin`    DECIMAL(16,6) NULL COMMENT '净利率 %',
  `debt_to_assets`      DECIMAL(16,6) NULL COMMENT '资产负债率 %',
  `current_ratio`       DECIMAL(16,6) NULL,
  `quick_ratio`         DECIMAL(16,6) NULL,
  `ocf_to_or`           DECIMAL(16,6) NULL COMMENT '经营现金流/营收 %',
  `ocf_to_opincome`     DECIMAL(16,6) NULL COMMENT '经营现金流/经营利润 %',
  `ocf_to_profit`       DECIMAL(16,6) NULL COMMENT '经营现金流/净利润（盈利质量）',
  `netdebt`             DECIMAL(24,4) NULL,
  `interestdebt`        DECIMAL(24,4) NULL,
  `ebit`                DECIMAL(24,4) NULL,
  `ebitda`              DECIMAL(24,4) NULL,
  `interest_cover`      DECIMAL(16,6) NULL COMMENT '利息保障倍数',
  `fcff`                DECIMAL(24,4) NULL COMMENT '企业自由现金流',
  `fcfe`                DECIMAL(24,4) NULL COMMENT '股权自由现金流',
  `source`              VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`          DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_fina` (`symbol`,`end_date`,`ann_date`),
  KEY `ix_hd_fina_pit` (`symbol`,`ann_date`),
  KEY `ix_hd_fina_end` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='扩展财务指标（PIT）';

-- 现金流量表（G4；FCF 与分红覆盖的来源）
CREATE TABLE IF NOT EXISTS `hd_cashflow` (
  `id`                    BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`                VARCHAR(12)  NOT NULL,
  `end_date`              DATE         NOT NULL,
  `ann_date`              DATE         NOT NULL,
  `report_type`           VARCHAR(4)   NOT NULL DEFAULT '1'
                          COMMENT '1=合并报表 2=单季合并 6=母公司；实测全为 1',
  `n_cashflow_act`        DECIMAL(24,4) NULL COMMENT '经营活动现金流净额',
  `n_cashflow_inv_act`    DECIMAL(24,4) NULL COMMENT '投资活动现金流净额',
  `n_cash_flows_fnc_act`  DECIMAL(24,4) NULL COMMENT '筹资活动现金流净额',
  `c_pay_dist_dpcp_int_exp` DECIMAL(24,4) NULL COMMENT '分配股利/利润/偿付利息支付的现金',
  `free_cashflow`         DECIMAL(24,4) NULL COMMENT 'Tushare 口径自由现金流',
  `c_fr_sale_sg_ar`       DECIMAL(24,4) NULL COMMENT '销售商品收到的现金',
  `source`                VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`            DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_cf_key` (`symbol`,`end_date`,`ann_date`,`report_type`),
  KEY `ix_hd_cf_pit` (`symbol`,`ann_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='现金流量表';

-- 资产负债表（G4；负债率与净资产判定的来源）
CREATE TABLE IF NOT EXISTS `hd_balancesheet` (
  `id`                    BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`                VARCHAR(12)  NOT NULL,
  `end_date`              DATE         NOT NULL,
  `ann_date`              DATE         NOT NULL,
  `report_type`           VARCHAR(4)   NOT NULL DEFAULT '1'
                          COMMENT '1=合并报表 2=单季合并 6=母公司；实测全为 1',
  `total_assets`          DECIMAL(24,4) NULL,
  `total_liab`            DECIMAL(24,4) NULL,
  `total_hldr_eqy_exc_min_int` DECIMAL(24,4) NULL COMMENT '归母净资产',
  `total_cur_assets`      DECIMAL(24,4) NULL,
  `total_cur_liab`        DECIMAL(24,4) NULL,
  `money_cap`             DECIMAL(24,4) NULL COMMENT '货币资金',
  `goodwill`              DECIMAL(24,4) NULL COMMENT '商誉',
  `source`                VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`            DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_bs_key` (`symbol`,`end_date`,`ann_date`,`report_type`),
  KEY `ix_hd_bs_pit` (`symbol`,`ann_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='资产负债表';

-- 利润表（G4；分红支付率的分母来源）
CREATE TABLE IF NOT EXISTS `hd_income` (
  `id`                    BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`                VARCHAR(12)  NOT NULL,
  `end_date`              DATE         NOT NULL,
  `ann_date`              DATE         NOT NULL,
  `report_type`           VARCHAR(4)   NOT NULL DEFAULT '1'
                          COMMENT '1=合并报表 2=单季合并 6=母公司；实测全为 1',
  `total_revenue`         DECIMAL(24,4) NULL,
  `revenue`               DECIMAL(24,4) NULL,
  `operate_profit`        DECIMAL(24,4) NULL,
  `n_income`              DECIMAL(24,4) NULL COMMENT '净利润（含少数股东）',
  `n_income_attr_p`       DECIMAL(24,4) NULL COMMENT '归母净利润',
  `basic_eps`             DECIMAL(16,6) NULL,
  `source`                VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`            DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_inc_key` (`symbol`,`end_date`,`ann_date`,`report_type`),
  KEY `ix_hd_inc_pit` (`symbol`,`ann_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='利润表';

-- 基准指数行情（G5；plan.md §28.4 基准比较）
CREATE TABLE IF NOT EXISTS `hd_index_daily` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT,
  `index_code`  VARCHAR(16)  NOT NULL,
  `index_name`  VARCHAR(64)  NULL,
  `trade_date`  DATE         NOT NULL,
  `open`        DECIMAL(16,4) NULL,
  `high`        DECIMAL(16,4) NULL,
  `low`         DECIMAL(16,4) NULL,
  `close`       DECIMAL(16,4) NULL,
  `pre_close`   DECIMAL(16,4) NULL,
  `pct_chg`     DECIMAL(12,6) NULL,
  `vol`         DECIMAL(24,4) NULL,
  `amount`      DECIMAL(24,4) NULL,
  `source`      VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_idx` (`index_code`,`trade_date`),
  KEY `ix_hd_idx_date` (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='基准指数行情';

-- 停牌记录（G6；成交可行性约束）
CREATE TABLE IF NOT EXISTS `hd_suspend` (
  `id`            BIGINT      NOT NULL AUTO_INCREMENT,
  `symbol`        VARCHAR(12) NOT NULL,
  `trade_date`    DATE        NOT NULL,
  `suspend_type`  VARCHAR(4)  NOT NULL COMMENT 'S=停牌 R=复牌',
  `suspend_timing` VARCHAR(48) NULL,
  `source`        VARCHAR(16) NOT NULL DEFAULT 'tushare',
  `created_at`    DATETIME    NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_susp` (`symbol`,`trade_date`,`suspend_type`),
  KEY `ix_hd_susp_date` (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='停牌记录';

-- 涨跌停价（G6；plan.md §29 成交现实性）
CREATE TABLE IF NOT EXISTS `hd_limit` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT,
  `symbol`      VARCHAR(12)  NOT NULL,
  `trade_date`  DATE         NOT NULL,
  `up_limit`    DECIMAL(16,4) NULL,
  `down_limit`  DECIMAL(16,4) NULL,
  `source`      VARCHAR(16)  NOT NULL DEFAULT 'tushare',
  `created_at`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_limit` (`symbol`,`trade_date`),
  KEY `ix_hd_limit_date` (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='涨跌停价';

-- 本项目同步台账（含回补前后行数基线，供审计）
CREATE TABLE IF NOT EXISTS `hd_sync_log` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `job`             VARCHAR(64)  NOT NULL COMMENT '同步器名',
  `api`             VARCHAR(96)  NULL     COMMENT 'Tushare 接口名（交错模式为 a+b+c 形式）',
  `params_json`     TEXT         NULL,
  `started_at`      DATETIME     NOT NULL,
  `finished_at`     DATETIME     NULL,
  `status`          VARCHAR(16)  NOT NULL DEFAULT 'RUNNING' COMMENT 'RUNNING/OK/FAIL/SKIP',
  `rows_written`    BIGINT       NOT NULL DEFAULT 0,
  `rows_before`     BIGINT       NULL COMMENT '回补前基线行数',
  `rows_after`      BIGINT       NULL COMMENT '回补后行数（审计：只增不减）',
  `data_start`      DATE         NULL,
  `data_end`        DATE         NULL,
  `failure_reason`  TEXT         NULL,
  PRIMARY KEY (`id`),
  KEY `ix_hd_synclog_job` (`job`,`started_at`),
  KEY `ix_hd_synclog_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='同步台账';

-- 数据完整性问题清单（P0 验收依据）
CREATE TABLE IF NOT EXISTS `hd_data_audit` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `audit_id`      VARCHAR(32)  NOT NULL,
  `check_code`    VARCHAR(32)  NOT NULL COMMENT 'G1..G6 或自定义检查码',
  `check_name`    VARCHAR(128) NOT NULL,
  `severity`      VARCHAR(16)  NOT NULL COMMENT 'OK/WARN/FAIL',
  `table_name`    VARCHAR(64)  NULL,
  `metric_json`   TEXT         NULL,
  `message`       TEXT         NULL,
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_hd_audit_run` (`audit_id`),
  KEY `ix_hd_audit_code` (`check_code`,`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='数据审计结果';

-- 一次筛选运行（股票池快照头）
CREATE TABLE IF NOT EXISTS `hd_universe_run` (
  `run_id`        VARCHAR(32)  NOT NULL,
  `name`          VARCHAR(64)  NOT NULL,
  `asof_date`     DATE         NOT NULL,
  `mode`          VARCHAR(16)  NOT NULL COMMENT 'point_in_time/latest',
  `config_json`   MEDIUMTEXT   NOT NULL COMMENT '生效配置快照（含 override 后）',
  `config_hash`   VARCHAR(64)  NOT NULL,
  `data_version`  VARCHAR(64)  NULL,
  `code_version`  VARCHAR(32)  NULL,
  `member_count`  INT          NOT NULL DEFAULT 0,
  `candidate_count` INT        NOT NULL DEFAULT 0 COMMENT '进入筛选的候选总数',
  `stats_json`    TEXT         NULL COMMENT '逐滤网淘汰统计',
  `status`        VARCHAR(16)  NOT NULL DEFAULT 'OK' COMMENT 'OK/WARN/FAIL',
  `display_name`  VARCHAR(128) NULL     COMMENT '用户在界面上给这条筛选记录起的名字',
  `notes`         VARCHAR(500) NULL     COMMENT '用户备注',
  `archived_at`   DATETIME     NULL     COMMENT '归档时间；NULL=未归档',
  `deleted_at`    DATETIME     NULL     COMMENT '软删除时间；NULL=未删除（不做物理删除）',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_hd_uni_run_asof` (`asof_date`),
  KEY `ix_hd_uni_run_hash` (`config_hash`),
  KEY `ix_hd_uni_run_state` (`deleted_at`,`archived_at`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='股票池运行头';

-- 股票池成员 + 逐滤网命中留痕（可回答「为什么没选上」）
CREATE TABLE IF NOT EXISTS `hd_universe_member` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `symbol`          VARCHAR(12)  NOT NULL,
  `name`            VARCHAR(64)  NULL,
  `industry`        VARCHAR(64)  NULL,
  `passed`          TINYINT(1)   NOT NULL COMMENT '1=入选 0=被淘汰',
  `fail_stage`      VARCHAR(32)  NULL COMMENT '首个未通过的滤网',
  `fail_reason`     VARCHAR(255) NULL,
  `values_json`     TEXT         NULL COMMENT '各滤网实际取值快照',
  `filter_json`     TEXT         NULL COMMENT '各滤网通过位',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_uni_mem` (`run_id`,`symbol`),
  KEY `ix_hd_uni_mem_pass` (`run_id`,`passed`),
  KEY `ix_hd_uni_mem_sym` (`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='股票池成员留痕';

-- 决策时点因子值（长表；避免 1.9 亿行日频面板，见 plan §3.2）
CREATE TABLE IF NOT EXISTS `hd_factor_snapshot` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `snapshot_id`   VARCHAR(32)  NOT NULL COMMENT '一次快照（通常绑定 universe run 或 rebalance 日）',
  `asof_date`     DATE         NOT NULL,
  `symbol`        VARCHAR(12)  NOT NULL,
  `factor_code`   VARCHAR(48)  NOT NULL,
  `value`         DECIMAL(24,8) NULL,
  `source`        VARCHAR(32)  NULL COMMENT 'computed/dv_ttm/daily_basic 等口径来源',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_fac` (`snapshot_id`,`symbol`,`factor_code`),
  KEY `ix_hd_fac_asof` (`asof_date`,`factor_code`),
  KEY `ix_hd_fac_sym` (`symbol`,`factor_code`,`asof_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='决策时点因子快照';

-- 画像运行头
CREATE TABLE IF NOT EXISTS `hd_profile_run` (
  `run_id`        VARCHAR(32)  NOT NULL,
  `name`          VARCHAR(64)  NOT NULL,
  `asof_date`     DATE         NOT NULL,
  `universe_run_id` VARCHAR(32) NULL COMMENT '来源股票池 run',
  `config_json`   MEDIUMTEXT   NOT NULL,
  `config_hash`   VARCHAR(64)  NOT NULL,
  `data_version`  VARCHAR(64)  NULL,
  `symbol_count`  INT          NOT NULL DEFAULT 0,
  `status`        VARCHAR(16)  NOT NULL DEFAULT 'OK',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_hd_prof_run_asof` (`asof_date`),
  KEY `ix_hd_prof_run_hash` (`config_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='画像运行头';

-- 逐股逐指标历史分布统计（plan.md §9 明确要求 P10/P25/P50/P75/P90）
CREATE TABLE IF NOT EXISTS `hd_profile_stat` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `symbol`          VARCHAR(12)  NOT NULL,
  `metric_code`     VARCHAR(48)  NOT NULL,
  `window_years`    INT          NOT NULL DEFAULT 0 COMMENT '0=全历史；NULL 会绕过唯一约束故禁用',
  `n_obs`           INT          NOT NULL DEFAULT 0,
  `min_value`       DECIMAL(24,8) NULL,
  `max_value`       DECIMAL(24,8) NULL,
  `mean_value`      DECIMAL(24,8) NULL,
  `median_value`    DECIMAL(24,8) NULL,
  `std_value`       DECIMAL(24,8) NULL,
  `p10`             DECIMAL(24,8) NULL,
  `p25`             DECIMAL(24,8) NULL,
  `p50`             DECIMAL(24,8) NULL,
  `p75`             DECIMAL(24,8) NULL,
  `p90`             DECIMAL(24,8) NULL,
  `extra_percentiles_json` TEXT  NULL COMMENT '配置中超出默认 5 档的分位',
  `current_value`   DECIMAL(24,8) NULL COMMENT 'asof 当日取值',
  `current_percentile` DECIMAL(12,6) NULL COMMENT '当前值在历史分布中的分位 %',
  `status`          VARCHAR(16)  NOT NULL DEFAULT 'OK' COMMENT 'OK/INSUFFICIENT',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_prof_stat` (`run_id`,`symbol`,`metric_code`,`window_years`),
  KEY `ix_hd_prof_stat_sym` (`symbol`,`metric_code`),
  KEY `ix_hd_prof_stat_q` (`run_id`,`metric_code`,`current_percentile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='个股历史分布统计';

-- 画像时间序列（供 HTML 画图；仅筛选后股票，体积可控）
CREATE TABLE IF NOT EXISTS `hd_profile_series` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`      VARCHAR(32)  NOT NULL,
  `symbol`      VARCHAR(12)  NOT NULL,
  `metric_code` VARCHAR(48)  NOT NULL,
  `series_date` DATE         NOT NULL,
  `value`       DECIMAL(24,8) NULL,
  `created_at`  DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_prof_ser` (`run_id`,`symbol`,`metric_code`,`series_date`),
  KEY `ix_hd_prof_ser_sym` (`run_id`,`symbol`,`metric_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='个股画像序列';

-- 安全边际分项得分（plan.md §15）
CREATE TABLE IF NOT EXISTS `hd_profile_score` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`        VARCHAR(32)  NOT NULL,
  `symbol`        VARCHAR(12)  NOT NULL,
  `score_code`    VARCHAR(48)  NOT NULL COMMENT 'dividend_yield/valuation/financial_quality/balance_sheet/dividend_quality/composite',
  `score`         DECIMAL(12,6) NULL,
  `detail_json`   TEXT         NULL COMMENT '分项明细与所用锚点',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_prof_score` (`run_id`,`symbol`,`score_code`),
  KEY `ix_hd_prof_score_v` (`run_id`,`score_code`,`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='安全边际得分';

-- 策略身份与版本（plan.md §38/§39 生命周期）
CREATE TABLE IF NOT EXISTS `hd_strategy` (
  `strategy_id`   VARCHAR(32)  NOT NULL,
  `name`          VARCHAR(64)  NOT NULL,
  `version`       VARCHAR(16)  NOT NULL,
  `status`        VARCHAR(16)  NOT NULL DEFAULT 'DRAFT'
                  COMMENT 'DRAFT/RESEARCH/BACKTEST/VALIDATED/PAPER/LIVE/ARCHIVED',
  `description`   VARCHAR(500) NOT NULL DEFAULT '',
  `yaml_path`     VARCHAR(255) NULL,
  `config_json`   MEDIUMTEXT   NOT NULL,
  `config_hash`   VARCHAR(64)  NOT NULL,
  `parent_id`     VARCHAR(32)  NULL COMMENT '上一版本 strategy_id',
  `spec_type`     VARCHAR(16)  NOT NULL DEFAULT 'yaml',
  `created_at`    DATETIME     NOT NULL,
  `updated_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`strategy_id`,`version`),
  UNIQUE KEY `uq_hd_strat_hash` (`config_hash`),
  KEY `ix_hd_strat_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='策略版本';

-- 策略参数扁平化（path → value），便于跨版本 diff 与敏感性扫描
CREATE TABLE IF NOT EXISTS `hd_strategy_param` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `strategy_id`   VARCHAR(32)  NOT NULL,
  `version`       VARCHAR(16)  NOT NULL,
  `param_path`    VARCHAR(255) NOT NULL,
  `param_value`   VARCHAR(500) NULL,
  `value_type`    VARCHAR(16)  NULL COMMENT 'int/float/str/bool/null',
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_sparam` (`strategy_id`,`version`,`param_path`),
  KEY `ix_hd_sparam_path` (`param_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='策略参数扁平表';

-- 回测运行头（plan.md §47 原则 6：可复现四元组）
CREATE TABLE IF NOT EXISTS `hd_backtest_run` (
  `run_id`          VARCHAR(32)  NOT NULL,
  `strategy_id`     VARCHAR(32)  NOT NULL,
  `strategy_version` VARCHAR(16) NOT NULL,
  `mode`            VARCHAR(16)  NOT NULL DEFAULT 'single'
                    COMMENT 'single/in_sample/out_of_sample/walkforward_window',
  `wf_id`           VARCHAR(32)  NULL,
  `window_index`    INT          NULL,
  `start_date`      DATE         NOT NULL,
  `end_date`        DATE         NOT NULL,
  `initial_capital` DECIMAL(24,4) NOT NULL,
  `final_capital`   DECIMAL(24,4) NULL,
  `total_dividend_net` DECIMAL(24,4) NOT NULL DEFAULT 0 COMMENT '累计现金分红（税后）',
  `total_dividend_tax` DECIMAL(24,4) NOT NULL DEFAULT 0 COMMENT '累计代扣红利税',
  `total_fees`      DECIMAL(24,4) NOT NULL DEFAULT 0 COMMENT '累计交易费用',
  `cash_final`      DECIMAL(24,4) NULL COMMENT '期末现金（用于资金对账）',
  `strategy_config_json` MEDIUMTEXT NOT NULL,
  `config_hash`     VARCHAR(64)  NOT NULL,
  `backtest_config_json` TEXT     NULL,
  `cost_json`       TEXT         NULL,
  `data_version`    VARCHAR(64)  NULL,
  `code_version`    VARCHAR(32)  NULL,
  `seed`            INT          NULL,
  `status`          VARCHAR(16)  NOT NULL DEFAULT 'OK' COMMENT 'OK/FAIL/PARTIAL',
  `unimplemented_json` TEXT      NULL COMMENT '未建模部分显式声明（如涨跌停近似）',
  `display_name`    VARCHAR(128) NULL COMMENT '用户命名',
  `notes`           VARCHAR(500) NULL COMMENT '用户备注',
  `universe_run_id` VARCHAR(32)  NULL COMMENT '关联的股票池筛选记录（--universe-run 指定时写入）',
  `archived_at`     DATETIME     NULL COMMENT '归档时间；NULL=未归档',
  `deleted_at`      DATETIME     NULL COMMENT '软删除时间；NULL=未删除',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`run_id`),
  KEY `ix_hd_bt_strategy` (`strategy_id`,`strategy_version`),
  KEY `ix_hd_bt_wf` (`wf_id`,`window_index`),
  KEY `ix_hd_bt_period` (`start_date`,`end_date`),
  KEY `ix_hd_bt_universe` (`universe_run_id`),
  KEY `ix_hd_bt_state` (`deleted_at`,`archived_at`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='回测运行头';

-- 逐日净值曲线（含基准对齐，plan.md §28.4）
CREATE TABLE IF NOT EXISTS `hd_backtest_equity` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `trade_date`      DATE         NOT NULL,
  `nav`             DECIMAL(24,8) NOT NULL COMMENT '组合净值（初始=1）',
  `total_value`     DECIMAL(24,4) NOT NULL,
  `cash`            DECIMAL(24,4) NOT NULL,
  `position_value`  DECIMAL(24,4) NOT NULL,
  `daily_return`    DECIMAL(18,10) NULL,
  `cum_return`      DECIMAL(18,10) NULL,
  `drawdown`        DECIMAL(18,10) NULL COMMENT '相对历史高点回撤（负值）',
  `holding_count`   INT          NOT NULL DEFAULT 0,
  `benchmark_code`  VARCHAR(16)  NULL,
  `benchmark_close` DECIMAL(16,4) NULL,
  `benchmark_nav`   DECIMAL(24,8) NULL,
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_bt_eq` (`run_id`,`trade_date`),
  KEY `ix_hd_bt_eq_date` (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='回测净值曲线';

-- 逐日持仓快照
CREATE TABLE IF NOT EXISTS `hd_backtest_position` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`        VARCHAR(32)  NOT NULL,
  `trade_date`    DATE         NOT NULL,
  `symbol`        VARCHAR(12)  NOT NULL,
  `quantity`      DECIMAL(20,4) NOT NULL,
  `avg_cost`      DECIMAL(16,4) NULL,
  `close`         DECIMAL(16,4) NULL,
  `market_value`  DECIMAL(24,4) NULL,
  `weight`        DECIMAL(12,8) NULL,
  `unrealized_pnl` DECIMAL(24,4) NULL,
  `holding_days`  INT          NULL,
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_bt_pos` (`run_id`,`trade_date`,`symbol`),
  KEY `ix_hd_bt_pos_sym` (`run_id`,`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='逐日持仓';

-- 逐笔成交（plan.md §32；reason_json 为 AI 可解释性关键）
CREATE TABLE IF NOT EXISTS `hd_backtest_trade` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `trade_id`        VARCHAR(32)  NOT NULL,
  `symbol`          VARCHAR(12)  NOT NULL,
  `signal_date`     DATE         NOT NULL,
  `execution_date`  DATE         NOT NULL,
  `side`            VARCHAR(4)   NOT NULL COMMENT 'BUY/SELL',
  `price`           DECIMAL(16,4) NOT NULL,
  `quantity`        DECIMAL(20,4) NOT NULL,
  `amount`          DECIMAL(24,4) NOT NULL,
  `commission`      DECIMAL(16,4) NOT NULL DEFAULT 0,
  `stamp_tax`       DECIMAL(16,4) NOT NULL DEFAULT 0,
  `transfer_fee`    DECIMAL(16,4) NOT NULL DEFAULT 0,
  `slippage_cost`   DECIMAL(16,4) NOT NULL DEFAULT 0,
  `total_cost`      DECIMAL(16,4) NOT NULL DEFAULT 0,
  `realized_pnl`    DECIMAL(24,4) NULL,
  `holding_days`    INT          NULL,
  `reason_json`     TEXT         NULL COMMENT 'BUY/SELL 的完整理由快照',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_bt_trade` (`run_id`,`trade_id`),
  KEY `ix_hd_bt_trade_sym` (`run_id`,`symbol`,`execution_date`),
  KEY `ix_hd_bt_trade_date` (`execution_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='回测成交流水';

-- 逐信号（含 executed/skip_reason，用于区分「想买」与「买到了」）
CREATE TABLE IF NOT EXISTS `hd_backtest_signal` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `symbol`          VARCHAR(12)  NOT NULL,
  `signal_date`     DATE         NOT NULL,
  `signal_type`     VARCHAR(8)   NOT NULL COMMENT 'BUY/SELL/HOLD/WATCH',
  `yield_value`     DECIMAL(18,8) NULL COMMENT '触发时的股息率',
  `yield_percentile` DECIMAL(12,6) NULL COMMENT '触发时的历史分位 %',
  `target_weight`   DECIMAL(12,8) NULL,
  `price`           DECIMAL(16,4) NULL,
  `executed`        TINYINT(1)   NOT NULL DEFAULT 0,
  `skip_reason`     VARCHAR(64)  NULL COMMENT 'LIMIT_UP/LIMIT_DOWN/SUSPENDED/NO_CASH/MIN_COMMISSION',
  `reason_json`     TEXT         NULL,
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_hd_bt_sig` (`run_id`,`symbol`,`signal_date`),
  KEY `ix_hd_bt_sig_type` (`run_id`,`signal_type`,`executed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='回测信号流水';

-- 绩效指标长表（scope 区分样本内/外/窗口；plan.md §28）
CREATE TABLE IF NOT EXISTS `hd_backtest_metric` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `run_id`          VARCHAR(32)  NOT NULL,
  `scope`           VARCHAR(24)  NOT NULL COMMENT 'all/train/test/window_k/year_YYYY',
  `category`        VARCHAR(16)  NOT NULL COMMENT 'return/risk/trade/benchmark',
  `metric_code`     VARCHAR(48)  NOT NULL,
  `metric_value`    DECIMAL(24,8) NULL,
  `benchmark_code`  VARCHAR(16)  NOT NULL DEFAULT '' COMMENT '空串=策略自身；NULL 会绕过唯一约束故禁用',
  `unit`            VARCHAR(16)  NULL COMMENT 'ratio/pct/money/count/days',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_bt_metric` (`run_id`,`scope`,`metric_code`,`benchmark_code`),
  KEY `ix_hd_bt_metric_code` (`metric_code`),
  KEY `ix_hd_bt_metric_scope` (`run_id`,`scope`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='回测绩效指标';

-- Walk-forward 运行头（plan.md §23）
CREATE TABLE IF NOT EXISTS `hd_walkforward_run` (
  `wf_id`           VARCHAR(32)  NOT NULL,
  `strategy_id`     VARCHAR(32)  NOT NULL,
  `strategy_version` VARCHAR(16) NOT NULL,
  `config_json`     MEDIUMTEXT   NOT NULL,
  `config_hash`     VARCHAR(64)  NOT NULL,
  `scheme`          VARCHAR(16)  NOT NULL COMMENT 'rolling/expanding',
  `train_years`     INT          NOT NULL,
  `test_years`      INT          NOT NULL,
  `step_months`     INT          NOT NULL,
  `window_count`    INT          NOT NULL DEFAULT 0,
  `data_version`    VARCHAR(64)  NULL,
  `code_version`    VARCHAR(32)  NULL,
  `status`          VARCHAR(16)  NOT NULL DEFAULT 'OK',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`wf_id`),
  KEY `ix_hd_wf_strategy` (`strategy_id`,`strategy_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Walk-forward 运行头';

-- Walk-forward 每个窗口（train 定参 → test 冻结，plan.md §25）
CREATE TABLE IF NOT EXISTS `hd_walkforward_window` (
  `id`              BIGINT       NOT NULL AUTO_INCREMENT,
  `wf_id`           VARCHAR(32)  NOT NULL,
  `window_index`    INT          NOT NULL,
  `train_start`     DATE         NOT NULL,
  `train_end`       DATE         NOT NULL,
  `test_start`      DATE         NOT NULL,
  `test_end`        DATE         NOT NULL,
  `frozen_params_json` TEXT      NOT NULL COMMENT '由 train 段确定、在 test 段冻结的参数',
  `train_run_id`    VARCHAR(32)  NULL,
  `test_run_id`     VARCHAR(32)  NULL,
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_wf_win` (`wf_id`,`window_index`),
  KEY `ix_hd_wf_win_period` (`test_start`,`test_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Walk-forward 窗口';

-- 参数敏感性扫描（plan.md §27 过拟合控制）
CREATE TABLE IF NOT EXISTS `hd_sensitivity_run` (
  `sens_id`         VARCHAR(32)  NOT NULL,
  `base_strategy_id` VARCHAR(32) NOT NULL,
  `base_version`    VARCHAR(16)  NOT NULL,
  `sweep_json`      TEXT         NOT NULL COMMENT '扫描网格定义',
  `point_count`     INT          NOT NULL DEFAULT 0,
  `period_start`    DATE         NULL,
  `period_end`      DATE         NULL,
  `data_version`    VARCHAR(64)  NULL,
  `code_version`    VARCHAR(32)  NULL,
  `status`          VARCHAR(16)  NOT NULL DEFAULT 'OK',
  `created_at`      DATETIME     NOT NULL,
  PRIMARY KEY (`sens_id`),
  KEY `ix_hd_sens_strategy` (`base_strategy_id`,`base_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='敏感性扫描头';

-- 敏感性扫描的每个参数组合及其绩效
CREATE TABLE IF NOT EXISTS `hd_sensitivity_point` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `sens_id`       VARCHAR(32)  NOT NULL,
  `point_index`   INT          NOT NULL,
  `param_values_json` TEXT     NOT NULL,
  `param_key`     VARCHAR(128) NULL COMMENT '单参数扫描时的主键（便于画曲线）',
  `run_id`        VARCHAR(32)  NULL COMMENT '对应回测 run',
  `cagr`          DECIMAL(18,8) NULL,
  `total_return`  DECIMAL(18,8) NULL,
  `max_drawdown`  DECIMAL(18,8) NULL,
  `sharpe`        DECIMAL(18,8) NULL,
  `calmar`        DECIMAL(18,8) NULL,
  `sortino`       DECIMAL(18,8) NULL,
  `trade_count`   INT          NULL,
  `turnover`      DECIMAL(18,8) NULL,
  `extra_json`    TEXT         NULL,
  `created_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hd_sens_pt` (`sens_id`,`point_index`),
  KEY `ix_hd_sens_pt_key` (`sens_id`,`param_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='敏感性扫描点';

-- 报告元数据（含 SQL 溯源，保证页面数字可复现）
CREATE TABLE IF NOT EXISTS `hd_report` (
  `report_id`       VARCHAR(32)  NOT NULL,
  `report_type`     VARCHAR(24)  NOT NULL COMMENT 'audit/universe/profile/backtest/walkforward/sensitivity/strategy/index',
  `title`           VARCHAR(255) NOT NULL,
  `html_path`       VARCHAR(500) NOT NULL,
  `source_run_ids`  VARCHAR(500) NULL COMMENT '逗号分隔的来源 run_id',
  `context_json`    MEDIUMTEXT   NULL COMMENT '渲染上下文快照',
  `sql_provenance_json` MEDIUMTEXT NULL COMMENT '页面数据的来源 SQL（可复现性）',
  `data_version`    VARCHAR(64)  NULL,
  `generated_at`    DATETIME     NOT NULL,
  `file_bytes`      BIGINT       NULL,
  PRIMARY KEY (`report_id`),
  KEY `ix_hd_report_type` (`report_type`,`generated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='报告元数据';