CREATE TABLE IF NOT EXISTS robo_mpt_portfolios
(
    rmp_id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rmp_date        DATETIME        NOT NULL COMMENT '日期',
    rmp_risk        TINYINT         NOT NULL COMMENT '风险等级',
    rmp_type        VARCHAR(255)    NOT NULL COMMENT '投组类型',
    rmp_rolve       TINYINT         NOT NULL COMMENT '求解方式',
    rmp_portfolio   JSON                     DEFAULT NULL COMMENT '投组权重',
    rmp_cvar        DOUBLE                   DEFAULT NULL COMMENT '投组cvar',
    rmp_create_time DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rmp_update_time DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rmp_id),
    UNIQUE INDEX (rmp_date, rmp_risk, rmp_type),
    INDEX (rmp_risk),
    INDEX (rmp_type)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT '最优投组表';


CREATE TABLE IF NOT EXISTS robo_hold_portfolios
(
      `rhp_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rhp_date` datetime NOT NULL COMMENT '日期',
  `rhp_risk` tinyint(4) NOT NULL COMMENT '风险等级',
  `rhp_rrs_id` bigint(20) UNSIGNED NULL DEFAULT NULL COMMENT '调仓信号id',
  `rhp_rebalance` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否调仓',
  `rhp_portfolios` json NOT NULL COMMENT '投组信息',
  `rhp_nav` double(12, 4) NOT NULL COMMENT '基金投组净值',
  `rhp_asset_nav` double(12, 4) NOT NULL COMMENT '产品净值',
  `rhp_div` double(12, 4) NOT NULL COMMENT '配息滚动金额',
  `rhp_div_acc` double(12, 4) NOT NULL COMMENT '累计配息金额',
  `rhp_fund_div` double(12, 4) NOT NULL COMMENT '基金配息金额',
  `rhp_create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rhp_update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `v_nav_div_acc` double(12, 4) GENERATED ALWAYS AS (((`rhp_div_acc` + `rhp_nav`) + `rhp_fund_div`)) VIRTUAL COMMENT '配息金额+净值+当日基金配息' NOT NULL,
    PRIMARY KEY (rhp_id),
    UNIQUE INDEX (rhp_date, rhp_risk),
    INDEX (rhp_risk)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT '持仓投组表';