CREATE TABLE IF NOT EXISTS robo_base_datum
(
    rbd_id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rbd_datas              JSON                     DEFAULT NULL COMMENT '所有数据',
    v_rbd_ft_ticker        VARCHAR(255) GENERATED ALWAYS AS (rbd_datas ->> '$.ftTicker') COMMENT 'ft_ticker',
    v_rbd_bloomberg_ticker VARCHAR(255) GENERATED ALWAYS AS (rbd_datas ->> '$.bloombergTicker') COMMENT 'bloomberg_ticker',
    v_rbd_type             VARCHAR(255) GENERATED ALWAYS AS (rbd_datas ->> '$.type') COMMENT 'type',
    v_rbd_crncy            VARCHAR(255) GENERATED ALWAYS AS (rbd_datas ->> '$.currency') COMMENT 'crncy',
    v_rbd_risk             VARCHAR(255) GENERATED ALWAYS AS (rbd_datas ->> '$.risk') COMMENT 'risk',
    rbd_create_time        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rbd_update_time        DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rbd_id),
    UNIQUE KEY (v_rbd_ft_ticker),
    UNIQUE KEY (v_rbd_bloomberg_ticker),
    INDEX (v_rbd_crncy),
    INDEX (v_rbd_risk),
    INDEX (v_rbd_type)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT '基础资料信息表';


CREATE TABLE IF NOT EXISTS robo_fund_navs
(
    rfn_fund_id      BIGINT UNSIGNED NOT NULL COMMENT '基金ID',
    rfn_date         DATETIME        NOT NULL COMMENT '数据日期',
    rfn_av           DOUBLE          NOT NULL COMMENT '原始净值',
    rfn_div          DOUBLE          NOT NULL DEFAULT 0 COMMENT '原始分红',
    rfn_split        DOUBLE          NOT NULL DEFAULT 1 COMMENT '拆股',
    rfn_accrue_split DOUBLE          NOT NULL DEFAULT 1 COMMENT '累积拆股',
    rfn_av_p         DOUBLE          NOT NULL COMMENT '原始拆股净值',
    rfn_div_p        DOUBLE          NOT NULL DEFAULT 0 COMMENT '复权拆股分红',
    rfn_nav_cal      DOUBLE          NOT NULL COMMENT '复权净值',
    rfn_create_time  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rfn_update_time  DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rfn_fund_id, rfn_date),
    INDEX (rfn_date)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT '基金数据表';


CREATE TABLE IF NOT EXISTS `robo_exrate`
(
    `re_id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `re_ticker`      VARCHAR(255)    NOT NULL COMMENT '汇率ticker',
    `re_date`        DATETIME        NOT NULL COMMENT '日期',
    `re_close`       DOUBLE          NOT NULL COMMENT '收盘价',
    `re_create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `re_update_time` DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`re_id`),
    UNIQUE (`re_ticker`, `re_date`),
    INDEX (`re_date`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 0
  DEFAULT CHARSET = utf8mb4 COMMENT '汇率数据表';



CREATE TABLE IF NOT EXISTS `robo_index_datas`
(
    `rid_index_id`    BIGINT UNSIGNED NOT NULL COMMENT '指标id',
    `rid_date`        DATETIME        NOT NULL COMMENT '指标数据日期',
    `rid_high`        DOUBLE                   DEFAULT NULL COMMENT '最高价',
    `rid_open`        DOUBLE                   DEFAULT NULL COMMENT '最高价',
    `rid_low`         DOUBLE                   DEFAULT NULL COMMENT '最高价',
    `rid_close`       DOUBLE          NOT NULL COMMENT '收盘价',
    `rid_pe`          DOUBLE                   DEFAULT NULL COMMENT '市盈率',
    `rid_volume`      DOUBLE                   DEFAULT NULL COMMENT '成交量',
    `rid_create_time` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `rid_update_time` DATETIME                 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`rid_index_id`, `rid_date`),
    INDEX (`rid_date`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT '指标数据表';


