create database if not exists uav_industrial_portfolio DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
use uav_industrial_portfolio;
ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` ADD COLUMN `bts_community_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '物理小区名称' AFTER `radar_id`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` ADD COLUMN `bts_community_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '物理小区编号' AFTER `bts_community_name`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` ADD COLUMN `bts_info_dip_angle` decimal(5, 1) NULL DEFAULT 19.0 COMMENT '下傾角（°）（上傾用負數角度表示）' AFTER `bts_sector_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` ADD COLUMN `bts_info_vertical_sector_angle` decimal(5, 1) NULL DEFAULT 25.0 COMMENT '垂直扇形角度' AFTER `bts_info_dip_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` ADD COLUMN `altitude` decimal(5, 1) NULL DEFAULT NULL COMMENT '地面海拔高度' AFTER `bts_info_vertical_sector_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_record_info` ADD COLUMN `bts_community_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '物理小区名称' AFTER `radar_id`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_record_info` ADD COLUMN `bts_community_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '物理小区编号' AFTER `bts_community_name`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_record_info` ADD COLUMN `bts_info_dip_angle` decimal(5, 1) NULL DEFAULT 19.0 COMMENT '下傾角（°）（上傾用負數角度表示）' AFTER `bts_sector_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_record_info` ADD COLUMN `bts_info_vertical_sector_angle` decimal(5, 1) NULL DEFAULT 25.0 COMMENT '垂直扇形角度' AFTER `bts_info_dip_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_record_info` ADD COLUMN `altitude` decimal(5, 1) NULL DEFAULT NULL COMMENT '地面海拔高度' AFTER `bts_info_vertical_sector_angle`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` DROP INDEX `idx_bts_statistic`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` ADD COLUMN `bts_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '基站编码' AFTER `bts_id`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` ADD COLUMN `dimension` int NULL DEFAULT 0 COMMENT '维度（0:全部 1:年  2:月 3:周  4:日 ）' AFTER `bts_code`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` ADD COLUMN `dimension_value` int NULL DEFAULT NULL COMMENT '年：2024，月202401，日20240101' AFTER `dimension`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` MODIFY COLUMN `bts_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '基站id';

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` MODIFY COLUMN `update_at` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';

ALTER TABLE `uav_industrial_portfolio`.`civil_military_statisic` ADD INDEX `idx_bts_statistic_dim`(`bts_code`, `dimension`, `dimension_value`) USING BTREE;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_detection_configure_info` ADD COLUMN `suppression_num` int NULL DEFAULT NULL COMMENT '虚警的抑制点数' AFTER `keep_duration`;
ALTER TABLE `uav_industrial_portfolio`.`civil_military_track` ADD INDEX `idx_track_create_at`(`create_at`) USING BTREE;
ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` ADD COLUMN `area_type` int NULL DEFAULT NULL COMMENT '告警的区域类型，0：规划区域告警，1：基站探测区域告警' AFTER `aerocraft_id`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` ADD INDEX `idx_aerocraft`(`company_id`, `aerocraft_type`, `aerocraft_id`, `area_type`) USING BTREE;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` 
ADD INDEX `idx_company_id_status`(`company_id`, `status`) USING BTREE;
ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` 
ADD INDEX `idx_status_update_at`(`status`, `update_at`);
alter table civil_military_bts_info
    add origin varchar(50) null comment '数据来源';

-- auto-generated definition
create table traffic_flight_info
(
    id            bigint auto_increment
        primary key                         comment '主键 ID',
    flight_no     varchar(50)      not null comment '航班号',
    takeoff_time  time             not null comment '起飞时间',
    landing_time  time             not null comment '降落时间',
    runway_no     varchar(50)      not null comment '跑道编号',
    aircraft_type varchar(50)      null comment '机型',
    org_id        bigint           null comment '所属组织',
    create_id     bigint           null comment '创建人',
    create_time   datetime         null comment '创建时间',
    modify_id     bigint           null comment '更新人',
    modify_time   datetime         null comment '更新时间',
    deleted       bit default b'0' not null comment '删除标记'
)
    comment '航班信息-智慧交通中应用';

-- auto-generated definition
create table traffic_runway_info
(
    id               bigint unsigned auto_increment
        primary key                        comment '主键 ID',
    runway_no        varchar(50)      null comment '跑道编号',
    runway_name      varchar(50)      null comment '跑道名称',
    left_top_lng     decimal(20, 7)   null comment '左上经度',
    left_top_lat     decimal(20, 7)   null comment '左上纬度',
    left_bottom_lng  decimal(20, 7)   null comment '左下经度',
    left_bottom_lat  decimal(20, 7)   null comment '左下纬度',
    right_top_lng    decimal(20, 7)   null comment '右上经度',
    right_top_lat    int              null comment '右上纬度',
    right_bottom_lng decimal(20, 7)   null comment '右下经度',
    right_bottom_lat decimal(20, 7)   null comment '右下纬度',
    org_id           bigint           null comment '所属组织',
    create_id        bigint           null comment '创建人',
    create_time      datetime         null comment '创建时间',
    modify_id        bigint           null comment '更新人',
    modify_time      datetime         null comment '更新时间',
    deleted          bit default b'0' null comment '删除标记'
)
    comment '跑道信息-智慧交通';



create table traffic_airport_warn_info
(
    id           bigint            not null     comment '主键 ID',
    warn_time    datetime          null comment '告警时间',
    runway_no    int               null comment '跑道编号',
    warn_info    varchar(100)      null comment '告警详情',
    flight_about varchar(100)      null comment '影响航班',
    invader_lng  decimal(20, 7)    null comment '入侵物经度',
    invader_lat  decimal(20, 7)    null comment '入侵物纬度',
    handle_state int(4)            null comment '处理状态,0:未处理，1:已处理',
    handle_info  varchar(255)      null comment '处理意见',
    handle_id    bigint            null comment '处理人',
    handle_time  datetime          null comment '处理时间',
    org_id       bigint            null comment '所属组织',
    create_id    bigint            null comment '创建人',
    create_time  datetime          null comment '创建时间',
    modify_id    bigint            null comment '更新人',
    modify_time  datetime          null comment '更新时间',
    deleted      bit default false null comment '删除标记',
    constraint traffic_airport_warn_info_pk
        primary key (id)
)
    comment '智慧机场-探测事件';
create index idx_traffic_flight_info_takeoff_time_index
    on traffic_flight_info (takeoff_time);


create index idx_traffic_airport_warn_info_org_id_index
    on traffic_airport_warn_info (org_id);

create index idx_traffic_airport_warn_info_warn_time_index
    on traffic_airport_warn_info (warn_time);

alter table traffic_airport_warn_info
    add bts_code varchar(255) not null comment '基站编号' after warn_time;

create index idx_traffic_airport_warn_info_bts_code_index
    on traffic_airport_warn_info (bts_code);

create index idx_traffic_airport_warn_info_runway_no_index
    on traffic_airport_warn_info (runway_no);

alter table traffic_flight_info
    modify takeoff_time timestamp not null default CURRENT_TIMESTAMP comment '起飞时间';

alter table traffic_flight_info
    modify landing_time timestamp not null default CURRENT_TIMESTAMP comment '降落时间';

alter table traffic_flight_info
    modify create_time timestamp null default CURRENT_TIMESTAMP comment '创建时间';

alter table traffic_flight_info
    modify modify_time timestamp null default CURRENT_TIMESTAMP comment '修改时间';

alter table traffic_airport_warn_info
    modify runway_no varchar(255) null comment '跑道编号';
ALTER TABLE `uav_industrial_portfolio`.`civil_military_bts_info` 
MODIFY COLUMN `create_by` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人名称';
alter table traffic_airport_warn_info
    add invader_id varchar(255) null comment '入侵物编号' after runway_no;
alter table
  traffic_runway_info
modify
  right_top_lat decimal(20, 7) null comment '右上纬度';
ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` 
ADD COLUMN `target_type` VARCHAR(32) NULL DEFAULT NULL COMMENT  '探测到的目标类型' AFTER `radar_id`,
ADD COLUMN `is_del` BIT(1) NULL DEFAULT b'0' COMMENT  '是否删除，0：未删除，1：删除' AFTER `update_by`;

ALTER TABLE `uav_industrial_portfolio`.`civil_military_detection_configure_info` 
ADD COLUMN `configs` LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL COMMENT  '个人所有配置' AFTER `ref_wgs_lng`;

CREATE TABLE IF NOT EXISTS `uav_industrial_portfolio`.`sense_data_sink_config` (
  `id` BIGINT(20) NOT NULL COMMENT  '主键id',
  `code` VARCHAR(64) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NOT NULL COMMENT  '分发者编码',
  `name` VARCHAR(64) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NOT NULL COMMENT  '分发者名称',
  `type` INT(11) NULL DEFAULT NULL COMMENT  '分发者类型',
  `public_key` VARCHAR(4096) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NULL DEFAULT NULL COMMENT  '公钥',
  `config` VARCHAR(4096) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NULL DEFAULT NULL COMMENT  '配置',
  `is_delete` TINYINT(1) NULL DEFAULT NULL COMMENT  '是否删除',
  `upd_usr_id` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NULL DEFAULT NULL COMMENT  '更新用户id',
  `upd_date` TIMESTAMP NULL DEFAULT NULL COMMENT  '更新时间',
  PRIMARY KEY USING BTREE (`id`),
  INDEX `idx_sense_code` USING BTREE (`code`) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin
ROW_FORMAT = DYNAMIC
COMMENT='SF adapter的分发配置表'
;
ALTER TABLE `civil_military_alarm` 
DROP INDEX `idx_company_id_status` ,
ADD INDEX `idx_company_id_status_del` USING BTREE (`company_id`, `status`, `is_del`);

ALTER TABLE `uav_industrial_portfolio`.`civil_military_alarm` 
DROP INDEX `idx_company_id_status_del` ,
ADD INDEX `idx_company_id_status_del_time` USING BTREE (`company_id`, `status`, `is_del`, `begin_time`) VISIBLE;
;

ALTER TABLE uav_industrial_portfolio.photoelectric_alarm
  CHANGE camera_id camera_id VARCHAR(64) NULL COMMENT '摄像头ID';