# 无人机信息表
drop table if exists uav;
CREATE TABLE `uav`
(
    `id`          int                                                           NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `object_id`    varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci  NOT NULL COMMENT '目标唯一编号',
    `longitude`   DECIMAL(18, 15)                                                 NOT NULL COMMENT '目标经度(使用WGS84坐标系)',
    `latitude`    DECIMAL(18, 15)                                                 NOT NULL COMMENT '目标纬度(使用WGS84坐标系)',
    `altitude`    DECIMAL(8, 3)                                                 NOT NULL COMMENT '目标海拔高度(海拔高度（GPS/北斗），单位：米)',
    `height`      DECIMAL(8, 3) COMMENT '距地高度(距地高度（相对基站安装的地面高度），单位：米)',
    `speed`       DECIMAL(8, 3)                                                 NOT NULL COMMENT '目标速度(单位（m/s）)',
    `time`        timestamp COMMENT '探测时间戳(UTC)(单位：毫秒)',
    `location`    varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '发现地点',
    `uav_Model`    varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '无人机型号',
    `uavSN`       varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci          DEFAULT '' COMMENT '无人机飞控序列号',
    `channel`     varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '频点',
    `device_type`  int                                                           not null COMMENT '侦测设备类型',
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb3
  ROW_FORMAT = DYNAMIC COMMENT ='无人机信息表';

# 无人机设备类型表
drop table if exists uav_device_type;
CREATE TABLE `uav_device_type`
(
    `id`               int primary key                                              NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `device_type`      int                                                          NOT NULL COMMENT '设备类型',
    `device_name`      varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '设备名称',
    `device_type_abbr` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '' COMMENT '设备缩写'
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb3
  ROW_FORMAT = DYNAMIC COMMENT ='无人机设备类型表';


INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (0, '5G-A基站', '5ga');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (1, '雷达设备', 'radar');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (2, '频谱设备', 'spec');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (3, '光电设备', 'oe');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (4, '反制设备', 'cm');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (5, '诱骗设备', 'dec');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (6, '干扰设备', 'ifr');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (7, '指挥车', 'cv');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (8, '察打一体', 'isrs');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (9, 'AOA', 'aoa');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (10, 'TDOA', 'tdoa');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (11, '协议破解', 'dcd');
INSERT INTO ldb_airport.uav_device_type (device_type, device_name, device_type_abbr) VALUES (102, 'Remote ID设备', 'rid');