CREATE database if NOT EXISTS `iailab_expert_tenant_zjgt` default character set utf8mb4 collate utf8mb4_general_ci;
USE `iailab_expert_tenant_zjgt`;

-- data
CREATE TABLE `t_channel_opcda_device` (
                                        `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
                                        `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务名',
                                        `host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'ip',
                                        `user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
                                        `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '密码',
                                        `prog_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备名',
                                        `cls_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备注册表ID',
                                        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                        `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                        PRIMARY KEY (`id`) USING BTREE,
                                        UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCDA表';

CREATE TABLE `t_channel_opcda_tag` (
                                     `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
                                     `server_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务id',
                                     `tag_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '测点名称',
                                     `data_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据类型',
                                     `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
                                     `item_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'itemId',
                                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                     PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='opcda测点表';

CREATE TABLE `t_channel_opcua_device` (
                                        `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                                        `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                                        `endpoint_url` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `security_policy` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `security_mode` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `connection_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `user_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `password` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `certificate_path` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
                                        `connect_inactivity_timeout` int DEFAULT NULL,
                                        `reconnect_interval` int DEFAULT NULL,
                                        `creator` bigint DEFAULT NULL,
                                        `create_time` datetime DEFAULT NULL,
                                        `updater` bigint DEFAULT NULL,
                                        `update_time` datetime DEFAULT NULL,
                                        PRIMARY KEY (`id`) USING BTREE,
                                        UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCUA表';

CREATE TABLE `t_channel_opcua_tag` (
                                     `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                     `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
                                     `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
                                     `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
                                     `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
                                     `address` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'opcda地址',
                                     `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
                                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                     PRIMARY KEY (`id`) USING BTREE,
                                     KEY `idx_device` (`device`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='opcda测点表';

CREATE TABLE `t_channel_modbus_device` (
                                         `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                         `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备名称',
                                         `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址',
                                         `port` smallint unsigned NOT NULL COMMENT '端口',
                                         `connect_inactivity_timeout` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '不活动超时(ms)',
                                         `reconnect_interval` smallint unsigned DEFAULT NULL COMMENT '重连超时(ms)',
                                         `attempts_before_timeout` smallint unsigned DEFAULT NULL COMMENT '重试次数',
                                         `wait_to_retry_milliseconds` smallint unsigned DEFAULT NULL COMMENT '重试间隔(ms)',
                                         `read_timeout` smallint unsigned DEFAULT NULL COMMENT '读超时(ms)',
                                         `write_timeout` smallint unsigned DEFAULT NULL COMMENT '写超时(ms)',
                                         `use_optimized_block_read` tinyint(1) DEFAULT NULL COMMENT '是否使用优化',
                                         `project_reference` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '系统内部用，默认空字符串',
                                         `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                         `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                         PRIMARY KEY (`id`) USING BTREE,
                                         UNIQUE KEY `uk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus表';

CREATE TABLE `t_channel_modbus_tag` (
                                      `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                      `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
                                      `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
                                      `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
                                      `format` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '大小端',
                                      `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
                                      `address` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Modbus地址',
                                      `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
                                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                      `tag_desc` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '描述',
                                      PRIMARY KEY (`id`) USING BTREE,
                                      KEY `idx_device` (`device`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus测点表';


CREATE TABLE `t_channel_kio_device` (
                                      `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                      `instance_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '实例名称',
                                      `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址',
                                      `port` smallint unsigned NOT NULL COMMENT '端口',
                                      `username` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
                                      `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
                                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                      PRIMARY KEY (`id`) USING BTREE,
                                      UNIQUE KEY `uk_instance_name` (`instance_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO实例表';


CREATE TABLE `t_channel_kio_tag` (
                                   `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                   `tag_name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
                                   `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
                                   `tag_id` int default NULL COMMENT '顺序号',
                                   `tag_desc` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL COMMENT '测点描述',
                                   `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
                                   `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
                                   `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
                                   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                   PRIMARY KEY (`id`) USING BTREE,
                                   KEY `idx_device` (`device`) USING BTREE,
                                   KEY `idx_tag_name` (`tag_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO测点表';

CREATE TABLE t_da_point(
                         `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                         `point_no` VARCHAR(36)   COMMENT '测点编码' ,
                         `point_name` VARCHAR(36)   COMMENT '测点名称' ,
                         `point_type` VARCHAR(10)   COMMENT '测点类型' ,
                         `data_type` VARCHAR(10)   COMMENT '数据类型' ,
                         `value_type` VARCHAR(10)   COMMENT '值类型' ,
                         `store_type` VARCHAR(10)   COMMENT '存储类型' ,
                         `unit` VARCHAR(36)   COMMENT '测量单位' ,
                         `unittransfactor` DECIMAL(24,6)   COMMENT '单位转换' ,
                         `default_value` DECIMAL(24,6)   COMMENT '默认值' ,
                         `max_value` DECIMAL(24,6)   COMMENT '最大值' ,
                         `min_value` DECIMAL(24,6)   COMMENT '最小值' ,
                         `offset_value` DECIMAL(24,6)   COMMENT '偏移量' ,
                         `minfreqid` VARCHAR(10)   COMMENT '采集频率' ,
                         `remark` VARCHAR(255)   COMMENT '备注' ,
                         `is_enable` tinyint(1)   COMMENT '是否启用' ,
                         `creator` VARCHAR(255)   COMMENT '创建人' ,
                         `create_time` DATETIME   COMMENT '创建时间' ,
                         `updater` VARCHAR(255)   COMMENT '修改人' ,
                         `update_time` DATETIME   COMMENT '修改时间' ,
                         PRIMARY KEY (id) USING BTREE,
                         UNIQUE KEY `uk_point_no` (`point_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测点表;';

CREATE TABLE t_da_measure_point(
                                 `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                 `point_id` VARCHAR(36)   COMMENT '测点ID' ,
                                 `source_type` VARCHAR(10)   COMMENT '数据源类型',
                                 `source_id` VARCHAR(36)   COMMENT '数据源ID',
                                 `tag_no` VARCHAR(64)   COMMENT '测点Tag',
                                 `dimension` INT   COMMENT '平滑尺度(min)' ,
                                 `value_type` VARCHAR(36)   COMMENT '值类型(数字量，模拟量)',
                                 PRIMARY KEY (id) USING BTREE,
                                 UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE,
                                 KEY `uk_tag_no` (`tag_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测量点表';

CREATE TABLE t_da_math_point(
                              `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                              `point_id` VARCHAR(36)   COMMENT '测点ID' ,
                              `expression` VARCHAR(2000)   COMMENT '计算公式' ,
                              PRIMARY KEY (id) USING BTREE,
                              UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计算点表';

CREATE TABLE `t_da_point_value` (
                                  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
                                  `point_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '测点编码',
                                  `data_time` datetime DEFAULT NULL COMMENT '数据时间',
                                  `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值',
                                  PRIMARY KEY (`id`) USING BTREE,
                                  KEY `idx_point_no` (`point_no`) USING BTREE,
                                  KEY `idx_data_time` (`data_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='点值表';

CREATE TABLE t_da_sequence_num(
                                `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                `code` VARCHAR(36)   COMMENT '编号',
                                `name` VARCHAR(36)   COMMENT '名称',
                                `sequence_num` int   COMMENT '序号' ,
                                `prefix` VARCHAR(10) COMMENT '前缀' ,
                                PRIMARY KEY (id) USING BTREE,
                                UNIQUE KEY `uk_code` (`code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '序号表';
INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('1', 'POINT_M', '测量点编码', 100000, 'M');
INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('2', 'POINT_C', '计算点编码', 100000, 'C');

CREATE TABLE `t_http_api`  (
                             `id` varchar(36) NOT NULL COMMENT 'ID',
                             `code` varchar(32) NOT NULL COMMENT '编码',
                             `name` varchar(32) NOT NULL COMMENT '名称',
                             `url` varchar(100) NOT NULL COMMENT 'URL',
                             `method` varchar(10) NULL DEFAULT NULL COMMENT '请求方法',
                             `param` varchar(200) NULL DEFAULT NULL COMMENT '参数',
                             `descp` varchar(100) NULL DEFAULT NULL COMMENT '描述',
                             `is_auth` tinyint(1) NOT NULL COMMENT '是否认证',
                             `auth_url` varchar(36) NULL DEFAULT NULL COMMENT '认证地址',
                             `creator` VARCHAR(64)   COMMENT '创建人' ,
                             `create_time` DATETIME   COMMENT '创建时间' ,
                             `updater` VARCHAR(64)   COMMENT '修改人' ,
                             `update_time` DATETIME   COMMENT '修改时间' ,
                             PRIMARY KEY (`ID`) USING BTREE,
                             UNIQUE KEY `uk_code` (`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Api接口' ROW_FORMAT = DYNAMIC;

CREATE TABLE `t_http_token` (
                              `id` varchar(36)  NOT NULL COMMENT 'ID',
                              `login_url` varchar(200) DEFAULT NULL COMMENT '登录地址',
                              `client_id` varchar(100) DEFAULT NULL COMMENT 'ClientId',
                              `client_secret` varchar(100) DEFAULT NULL COMMENT 'ClientSecret',
                              `username` varchar(50) DEFAULT NULL COMMENT '用户名',
                              `password` varchar(50) DEFAULT NULL COMMENT '密码',
                              `refresh_freq` varchar(10) DEFAULT NULL COMMENT '刷新频率',
                              `token` varchar(2000) DEFAULT NULL COMMENT 'token',
                              `expire_time` datetime DEFAULT NULL COMMENT '过期时间',
                              `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                              PRIMARY KEY (`id`) USING BTREE,
                              UNIQUE KEY `uk_login_url` (`login_url`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='接口Token';

CREATE TABLE `t_http_tag` (
                            `id` varchar(36) NOT NULL COMMENT 'ID',
                            `api_id` varchar(36) NOT NULL COMMENT '接口ID',
                            `tag_name` varchar(64) NOT NULL COMMENT '标签名称',
                            `data_type` varchar(16) NOT NULL COMMENT '数据类型',
                            `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
                            `tag_desc` varchar(64) DEFAULT NULL COMMENT '描述',
                            `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                            `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                            PRIMARY KEY (`id`) USING BTREE,
                            UNIQUE KEY `uk_api_tag` (`api_id`, `tag_name`) USING BTREE
) ENGINE=InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT='接口Tag表';

-- 指标管理------------
CREATE TABLE t_ind_data_set(
                                    `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                    `name` VARCHAR(30) NOT NULL   COMMENT '名称' ,
                                    `data_source` VARCHAR(64) NOT NULL   COMMENT '数据源ID',
                                    `query_sql` VARCHAR(200) NOT NULL   COMMENT '查询语句',
                                    `remark` VARCHAR(100)   COMMENT '备注',
                                    `sort` int   COMMENT '排序',
                                    `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                    `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                                    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                    PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集';

CREATE TABLE t_ind_data_set_field(
                               `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                               `data_set_id` VARCHAR(36) NOT NULL  COMMENT '数据集ID' ,
                               `field_code` VARCHAR(30)   COMMENT '英文名' ,
                               `field_name` VARCHAR(30)   COMMENT '中文名' ,
                               `field_type` VARCHAR(30)   COMMENT '数据类型' ,
                               `sort` int   COMMENT '排序',
                               PRIMARY KEY (id) USING BTREE,
                               INDEX `idx_data_set_id` (`data_set_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集字段';

CREATE TABLE t_ind_item_category(
                                `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                `label` VARCHAR(20)   COMMENT '标签' ,
                                `pid` VARCHAR(36)   COMMENT '父ID',
                                `sort` int   COMMENT '排序',
                                `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                                `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                                `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标分类';

CREATE TABLE t_ind_item(
                           `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                           `item_no` VARCHAR(36)   COMMENT '指标编码' ,
                           `item_name` VARCHAR(36)   COMMENT '指标名称' ,
                           `item_type` VARCHAR(20)   COMMENT '指标类型' ,
                           `item_category` VARCHAR(36)   COMMENT '指标分类' ,
                           `coefficient` decimal(10, 4) COMMENT '转换系数',
                           `precision` int COMMENT '指标精度',
                           `time_granularity` VARCHAR(10)   COMMENT '时间粒度' ,
                           `unit` VARCHAR(10)   COMMENT '数量单位' ,
                           `remark` VARCHAR(255)   COMMENT '备注' ,
                           `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0正常 1停用）',
                           `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                           `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                           `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                           `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                           PRIMARY KEY (id) USING BTREE,
                           UNIQUE INDEX `uk_item_no` (`item_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标项表';

CREATE TABLE t_ind_item_atom(
                              `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                              `item_id` VARCHAR(36)   COMMENT '指标ID' ,
                              `data_source` VARCHAR(64)   COMMENT '数据源',
                              `data_set` VARCHAR(64)   COMMENT '数据集',
                              `using_field` VARCHAR(64)   COMMENT '使用字段',
                              `stat_func` VARCHAR(64)   COMMENT '统计方式',
                              PRIMARY KEY (id) USING BTREE,
                              UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '原子指标';

CREATE TABLE t_ind_item_der(
                                `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                `item_id` VARCHAR(36)   COMMENT '指标ID' ,
                                `atom_item_id` VARCHAR(36)   COMMENT '原子指标ID' ,
                                `time_label` VARCHAR(20)   COMMENT '时间标识',
                                `time_limit` VARCHAR(20)   COMMENT '时间限定',
                                `time_start` DATETIME   COMMENT '开始时间',
                                `time_end` DATETIME   COMMENT '结束时间',
                                `dimension` VARCHAR(200)   COMMENT '分析维度',
                                PRIMARY KEY (id) USING BTREE,
                                UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '派生指标';

CREATE TABLE t_ind_item_cal(
                             `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                             `item_id` VARCHAR(36)   COMMENT '指标ID' ,
                             `expression` varchar(100)  DEFAULT NULL COMMENT '计算表达式',
                             PRIMARY KEY (id) USING BTREE,
                             UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '复合指标';


CREATE TABLE `t_ind_item_value` (
                                  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
                                  `item_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '指标编码',
                                  `data_time` varchar(20) DEFAULT NULL COMMENT '数据时间',
                                  `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值',
                                  PRIMARY KEY (`id`) USING BTREE,
                                  KEY `idx_item_no` (`item_no`) USING BTREE,
                                  KEY `idx_data_time` (`data_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='指标值表';


-- 计划数据------------
CREATE TABLE t_plan_data_set(
                               `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                               `name` VARCHAR(30) NOT NULL   COMMENT '名称' ,
                               `data_source` VARCHAR(64) NOT NULL   COMMENT '数据源ID',
                               `query_sql` VARCHAR(300) NOT NULL   COMMENT '查询语句',
                               `remark` VARCHAR(100)   COMMENT '备注',
                               `sort` int   COMMENT '排序',
                               `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                               `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                               `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                               `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                               PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据集';


CREATE TABLE t_plan_item_category(
                                    `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                    `label` VARCHAR(20)   COMMENT '标签' ,
                                    `pid` VARCHAR(36)   COMMENT '父ID',
                                    `sort` int   COMMENT '排序',
                                    `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                    `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                                    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                    PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据分类';

CREATE TABLE t_plan_item(
                           `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                           `item_no` VARCHAR(36)   COMMENT '指标编码' ,
                           `item_name` VARCHAR(36)   COMMENT '指标名称' ,
                           `item_category` VARCHAR(36)   COMMENT '指标分类',
                           `time_granularity` VARCHAR(10)   COMMENT '时间粒度',
                           `data_set` VARCHAR(64)   COMMENT '数据集',
                           `remark` VARCHAR(255)   COMMENT '备注' ,
                           `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0正常 1停用）',
                           `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
                           `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                           `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
                           `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                           PRIMARY KEY (id) USING BTREE,
                           UNIQUE INDEX `uk_item_no` (`item_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据项';

CREATE TABLE t_da_cumulate_point(
                                   `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                                   `point_id` VARCHAR(36)   COMMENT '测点ID' ,
                                   `moment_point` VARCHAR(36)   COMMENT '瞬时测点' ,
                                   `length` int COMMENT '累计长度',
                                   `divisor` int COMMENT '除数',
                                   PRIMARY KEY (id) USING BTREE,
                                   UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '累计点表';
INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('8', 'POINT_L', '累计点编码', 100001, 'L');
INSERT INTO `iailab_plat_system`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1673, 4, '累计点', 'CUMULATE', 'data_point_type', 0, '', '', '', '142', '2024-12-10 10:13:12', '142', '2024-12-10 10:13:12', b'0');

CREATE TABLE t_da_point_collect_status(
                           `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
                           `point_no` VARCHAR(36) NOT NULL   COMMENT '测点编码',
                           `collect_value` VARCHAR(24)   COMMENT '采集值',
                           `collect_quality` VARCHAR(5)   COMMENT '采集质量',
                           `collect_time` DATETIME   COMMENT '采集时间' ,
                           PRIMARY KEY (id) USING BTREE,
                           UNIQUE KEY `uk_point_no` (`point_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测点采集状态表';

CREATE TABLE `t_arc_data` (
                              `id` varchar(255) NOT NULL COMMENT 'id',
                              `arc_id` varchar(255) DEFAULT NULL COMMENT '归档id',
                              `arc_time` varchar(255) DEFAULT NULL COMMENT '归档时间',
                              `value` varchar(255) DEFAULT NULL COMMENT '归档时间',
                              `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                              PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='归档数据表';

CREATE TABLE `t_arc_setting` (
                                 `id` varchar(255) NOT NULL COMMENT 'id',
                                 `name` varchar(255) DEFAULT NULL COMMENT '名称',
                                 `type` varchar(255) DEFAULT NULL COMMENT '归档周期(shift、day、month、year)',
                                 `point` varchar(255) DEFAULT NULL COMMENT '归档点位',
                                 `calculate` varchar(255) DEFAULT NULL COMMENT '计算方法（none、sum、diff、avg）',
                                 `is_enable` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '是否启用',
                                 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                 `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='定时归档设置表';