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_fund_av` double(12, 4) NOT NULL COMMENT '投组原始净值，sum（个股原始净值*对应份额）',
  `rhp_fund_nav` double(12, 4) NOT NULL DEFAULT 0.0000 COMMENT '基金被动配息做配股',
  `rhp_nav` double(12, 4) NOT NULL COMMENT '复权净值',
  `rhp_asset_nav` double(12, 4) NOT NULL COMMENT '产品净值，投顾模式：fund_av',
  `rhp_div_forecast` double(12, 4) NOT NULL DEFAULT 0.0000 COMMENT '预配息金额',
  `rhp_div_acc` double(12, 4) NOT NULL COMMENT '累计配息金额，投顾：acc（port_div + fund_div）',
  `rhp_port_div` double(12, 4) NOT NULL COMMENT '主动配息',
  `rhp_cash` double(12, 4) NOT NULL DEFAULT 0.0000 COMMENT '现金（产品的现金账户）',
  `rhp_fund_div` double(12, 4) NOT NULL COMMENT '持有基金配息sum（个股每股配息*对应份额）',
  `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_asset_nav` + `rhp_div_acc`)) VIRTUAL COMMENT '产品累计净值 asset_nav+ acc_div' NOT NULL,
  PRIMARY KEY (`rhp_id`) USING BTREE,
  UNIQUE INDEX `rhp_date`(`rhp_date`, `rhp_risk`) USING BTREE,
  INDEX `rhp_risk`(`rhp_risk`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '持仓投组表' ROW_FORMAT = Dynamic;