-- ####### 升级部署执行 SQL 文件，迭代更新
-- 2022-07-07

DROP TABLE IF EXISTS `relation_user_scene`;

ALTER TABLE model_vehicle ADD COLUMN model_label varchar(100) DEFAULT NULL COMMENT '模型标识' AFTER description;

ALTER TABLE model_vehicle_template ADD COLUMN model_label varchar(100) DEFAULT NULL COMMENT '模型标识' AFTER description;

UPDATE model_vehicle_template SET model_label = CONCAT(vehicle_type,'_',vehicle_model,'_',vehicle_colour);

UPDATE model_vehicle v SET v.model_label = (SELECT m.model_label FROM model_vehicle_template m WHERE v.vehicle_type = m.vehicle_type);

UPDATE model_vehicle SET vehicle_type = CONCAT(vehicle_type,',',vehicle_model,',',vehicle_colour);

ALTER TABLE algorithm ADD COLUMN git_version varchar(128) DEFAULT NULL COMMENT 'git算法版本号' AFTER docker_image;


DROP TABLE IF EXISTS `simulation_automatic_project`;
CREATE TABLE `simulation_automatic_project` (
`id`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id' ,
`project_date`  int(10) NULL DEFAULT NULL COMMENT '项目日期' ,
`project_num`  int(10) NULL DEFAULT NULL COMMENT '项目序号' ,
`project_id`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目id' ,
`project_name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目名称' ,
`project_describe`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目描述' ,
`algorithm`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '算法 id' ,
`algorithm_type`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '算法类型' ,
`vehicle`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车辆模型 id' ,
`scene`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '场景测试包 id' ,
`operation_cycle`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '运行周期' ,
`parallelism`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '并行度' ,
`rule_view`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规则查看' ,
`max_simulation_time`  bigint(15) NULL DEFAULT NULL COMMENT '最大仿真时间，单位秒' ,
`is_choice_gpu`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否选择gpu(0:是，1：否)' ,
`automatic_run_times`  bigint(15) NULL DEFAULT NULL COMMENT '自动运行次数' ,
`last_run_time`  datetime NULL DEFAULT NULL COMMENT '最近运行时间' ,
`automatic_run_state`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '自动运行状态(0：开启，1：关闭)' ,
`create_time`  datetime NULL DEFAULT NULL ,
`create_user_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' ,
`modify_time`  datetime NULL DEFAULT NULL ,
`modify_user_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`is_deleted`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='自动运行项目表';

DROP TABLE IF EXISTS `simulation_automatic_subproject`;
CREATE TABLE `simulation_automatic_subproject` (
`id`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id' ,
`parent_id`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主项目表id' ,
`parent_project_id`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主项目表项目id' ,
`project_date`  int(10) NULL DEFAULT NULL COMMENT '项目日期' ,
`project_num`  int(10) NULL DEFAULT NULL COMMENT '项目序号' ,
`project_id`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目id' ,
`project_name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '项目名称' ,
`now_run_state`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '当前运行状态(10：未执行，20：执行中，30：执行完成，40：已中止)' ,
`evaluation_level`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '评测等级(G,A,M,P)' ,
`start_time`  datetime NULL DEFAULT NULL COMMENT '开始时间' ,
`finish_time`  datetime NULL DEFAULT NULL COMMENT '完成时间' ,
`algorithm_score`  double NULL DEFAULT NULL COMMENT '算法得分' ,
`task_number`  int(11) NULL DEFAULT NULL COMMENT '任务数量' ,
`task_completed`  int(11) NULL DEFAULT NULL COMMENT '\'Aborted\', \'PendingAnalysis\', \'Terminated\'任务个数' ,
`create_time`  datetime NULL DEFAULT NULL ,
`create_user_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' ,
`modify_time`  datetime NULL DEFAULT NULL ,
`modify_user_id`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`is_deleted`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='自动运行子项目子表';

ALTER TABLE system_menu ADD COLUMN icon varchar(50) DEFAULT NULL COMMENT '图标' AFTER router;

DROP TABLE IF EXISTS `system_parameter`;
CREATE TABLE `system_parameter` (
`id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '参数id' ,
`user_id`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '账户id' ,
`num_create_user`  int(32) NULL DEFAULT NULL COMMENT '可创建子账户数量' ,
`num_create_scene_package`  int(32) NULL DEFAULT NULL COMMENT '最多可创建场景测试包数量' ,
`num_scene_per_package`  int(32) NULL DEFAULT NULL COMMENT '场景测试包最大场景数量' ,
`create_time`  datetime NULL DEFAULT NULL ,
`modify_time`  datetime NULL DEFAULT NULL ,
`create_user_id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`modify_user_id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`is_deleted`  varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;


ALTER TABLE system_role ADD COLUMN sort int(11) DEFAULT NULL COMMENT '排序号' AFTER visible;

INSERT INTO `system_user` (`id`, `username`, `password`, `nickname`, `photo`, `phone`, `company`, `role_code`, `use_type`, `visible`, `openid`, `create_time`, `modify_time`, `create_user_id`, `modify_user_id`, `is_deleted`) VALUES ('510f29c5af754429a7a2a02f8275e506', 'sysadmin', 'e10adc3949ba59abbe56e057f20f883e', '超级管理员', NULL, '12345678912', '国汽', '0', '1', '1', NULL, '2022-5-16 10:19:46', '2022-6-13 10:01:42', '510f29c5af754429a7a2a02f8275e506', '510f29c5af754429a7a2a02f8275e506', '0');
INSERT INTO `system_user` (`id`, `username`, `password`, `nickname`, `photo`, `phone`, `company`, `role_code`, `use_type`, `visible`, `openid`, `create_time`, `modify_time`, `create_user_id`, `modify_user_id`, `is_deleted`) VALUES ('545d4914865d4a3cb6398fc25ffbc1b5', 'admin', 'e10adc3949ba59abbe56e057f20f883e', '子管理员', NULL, '090-00000000', '国汽', '1', '1', '1', '', '2022-5-10 10:36:11', '2022-7-6 15:11:40', '510f29c5af754429a7a2a02f8275e506', '545d4914865d4a3cb6398fc25ffbc1b5', '0');
INSERT INTO `system_user` (`id`, `username`, `password`, `nickname`, `photo`, `phone`, `company`, `role_code`, `use_type`, `visible`, `openid`, `create_time`, `modify_time`, `create_user_id`, `modify_user_id`, `is_deleted`) VALUES ('aaaaaaaaaabbbbbbbbbbcccccccc1001', '1001', 'e10adc3949ba59abbe56e057f20f883e', '索为普通用户1', NULL, '112', '国汽', '2', '1', '1', '', '2022-6-15 16:05:53', '2022-6-22 17:15:14', '510f29c5af754429a7a2a02f8275e506', 'aaaaaaaaaabbbbbbbbbbcccccccc1001', '0');


