DROP TABLE IF EXISTS robo_benchmark;
CREATE TABLE IF NOT EXISTS robo_benchmark
(
    rb_id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rb_module      VARCHAR(255)    NOT NULL COMMENT '模块',
    rb_date        DATETIME        NOT NULL COMMENT '日期',
    rb_risk        VARCHAR(255)    NOT NULL COMMENT '风险等级',
    rb_nav         DOUBLE(16, 4)   NOT NULL COMMENT '资产值',
    rb_remarks     JSON                     DEFAULT NULL COMMENT '其他信息',
    rb_create_time DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rb_update_time DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rb_id),
    UNIQUE INDEX (rb_module, rb_date, rb_risk),
    INDEX (rb_date, rb_risk),
    INDEX (rb_risk)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT 'BENCHMARK数据表';

ALTER TABLE robo_benchmark ADD COLUMN v_rb_re TINYINT GENERATED ALWAYS AS (IF(rb_remarks->>'$.re' = 'true', 1, 0)) COMMENT '是否再分配' AFTER rb_remarks;
ALTER TABLE robo_benchmark ADD INDEX v_rb_re(`v_rb_re`);

ALTER TABLE robo_benchmark DROP INDEX v_rb_re;
ALTER TABLE robo_benchmark DROP COLUMN v_rb_re;


DROP TABLE IF EXISTS robo_data_logger;
CREATE TABLE IF NOT EXISTS robo_data_logger
(
    rdl_id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rdl_date        DATETIME        NOT NULL COMMENT '日期',
    rdl_risk        VARCHAR(255)    NOT NULL COMMENT '风险等级',
    rdl_type        VARCHAR(255)    NOT NULL COMMENT '数据类别',
    rdl_datas       JSON            NOT NULL COMMENT '日志数据',
    rdl_create_time DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rdl_update_time DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rdl_id),
    UNIQUE INDEX (rdl_date, rdl_risk, rdl_type),
    INDEX (rdl_risk, rdl_type),
    INDEX (rdl_type)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT '数据日志表';