create database if not exists uav_mission DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
use uav_mission;
# 为照片表添加媒体信息字段
alter table
  picture_general
add
  media_type varchar(20) default null null comment '媒体类型 null = picture， picture|video';
# 根 据 后 缀 设 置 media_type 字 段
UPDATE
  picture_general
SET
  media_type = CASE
    WHEN LOWER(file_name) LIKE '%.jpg' THEN 'picture'
    WHEN LOWER(file_name) LIKE '%.png' THEN 'picture'
    WHEN LOWER(file_name) LIKE '%.mp4' THEN 'video'
    WHEN LOWER(file_name) LIKE '%.avi' THEN 'video'
    ELSE null
  END
where
  deleted = 0;
UPDATE
  picture_general
SET
  media_type = CASE
    WHEN LOWER(file_name) LIKE '%.jpg' THEN 'picture'
    WHEN LOWER(file_name) LIKE '%.png' THEN 'picture'
    WHEN LOWER(file_name) LIKE '%.mp4' THEN 'video'
    WHEN LOWER(file_name) LIKE '%.avi' THEN 'video'
    ELSE null
  END
where
  deleted = 0;
alter table
  alert_event
add
  dealt_date datetime null comment '告警处理时间，处理后更新';
  
  
CREATE TABLE `story_info` (
  `id` bigint NOT NULL COMMENT '故事ID',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '故事名称',
  `state` int DEFAULT NULL COMMENT '故事状态（0:ready,1:running,2:suspend,3:stop）',
  `remarks` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '故事备注',
  `type` int DEFAULT NULL COMMENT '故事类型',
  `org_id` bigint DEFAULT NULL COMMENT '公司ID',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '创建者名称',
  `create_id` bigint DEFAULT NULL COMMENT '创建者ID',
  `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '修改者名称',
  `modify_id` bigint DEFAULT NULL COMMENT '修改者ID',
  `modify_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `deleted` int DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE `story_object_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '故事对象ID',
  `uav_case_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '无人机编码',
  `uav_type` int DEFAULT NULL COMMENT '无人机类型(0:真机,1:模拟机)',
  `seq_num` int DEFAULT NULL COMMENT '对象顺序号',
  `line_case_id` bigint DEFAULT NULL COMMENT '航线实例ID',
  `line_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '航线名称',
  `line_track_id` bigint DEFAULT NULL COMMENT '航迹实例ID',
  `track_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '航迹名称',
  `project_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '素材仓库的projectId',
  `bucket_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '素材仓库的bucketName',
  `object_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '素材仓库的objectName',
  `object_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '素材仓库的objectPath',
  `object_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '素材仓库的视频ID',
  `story_id` bigint DEFAULT NULL COMMENT '关联的故事ID',
  `ai_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'AI处理相关信息',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '创建者名称',
  `create_id` bigint DEFAULT NULL COMMENT '创建者ID',
  `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '修改者名称',
  `modify_id` bigint DEFAULT NULL COMMENT '修改者ID',
  `modify_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `deleted` int DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;