-- 全新部署时，执行这个脚本：
DROP DATABASE IF EXISTS `sense_adapter`;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sense_adapter;
USE `sense_adapter`;

-- DROP TABLE IF EXISTS `regulator`;
-- DROP TABLE IF EXISTS `sf_device`;
-- DROP TABLE IF EXISTS `sf_sense_capability`;
-- DROP TABLE IF EXISTS `sf_sense_task`;
-- DROP TABLE IF EXISTS `sense_data_sink_config`;

CREATE TABLE `regulator` (
  `id` bigint NOT NULL COMMENT '厂商id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '厂商昵称',
  `is_system_admin` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否是系统管理员',
  `user_type` varchar(255) COMMENT '用户类型, 0:管理员; 1:中信科CICIM, 2:Erission, 3:Huawei，4:ZTE',
  `telephone` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '厂商手机号',
  `avatar_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '头像地址',
  `authentication_status` int NULL COMMENT '认证状态，0： 未认证，1已认证',
  `authentication_time` datetime(6) DEFAULT NULL COMMENT '认证时间',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '登录密码',
  `password_modify_time` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '更新密码时间',
  `is_frozen` bit(1) NOT NULL DEFAULT b'0' COMMENT '用户是否被禁用，0：否，1：禁用',
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '用户是否被删除，0：否，1：删除',
  `create_at` datetime(6) DEFAULT NULL COMMENT '创建时间',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建用户',
  `update_at` datetime(6) DEFAULT NULL COMMENT '更新时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新用户',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='监管者表';

-- 创建默认的管理员和4个厂商的账户；
-- 管理员：15928589640  Cmii112233
-- CICTM 15928589601   c5Wxu)RrM
-- Ericsson 15928589602 eQKcu&8!
-- Huawei  15928589603  h0}oF!Mji
-- ZTE 15928589604 z6(Z*!StF
-- truncate table regulator;
INSERT INTO `regulator` (`id`, `name`, `is_system_admin`, `telephone`, `avatar_url`, `authentication_status`, `authentication_time`, `password`, `password_modify_time`, `is_frozen`, `is_del`, `create_at`, `create_by`, `update_at`, `update_by`, `user_type`) VALUES (1,'Admin',_binary '\0','6d46c798b1385c0294badf9903586923',NULL,0,NULL,'$2a$10$FYElzh8BsxQFr0Da.MMDuOpH.Ynm57QGBRsgRIzQowgZIdvXbAb3G','2024-09-23 18:40:49.045124',_binary '\0',_binary '\0','2024-09-23 18:40:47.183882','r_1',NULL,NULL,'0'),(552450964452278272,'CICTM',_binary '\0','ab674bbb9a1298f873464464d0108af1',NULL,0,NULL,'$2a$10$twsDhJrQ8yzt2pP9MvUmTe4YpCD7lJk/Thk1zdrGq9UIQMLWcldnq','2024-09-21 11:22:53.966212',_binary '\0',_binary '\0','2024-09-21 11:22:53.966212','r_2',NULL,NULL,'1'),(552451165640458240,'Ericsson',_binary '\0','74bd84f82b1a4067c0b1f941789f546a',NULL,0,NULL,'$2a$10$5mK/u4upnJLcxbGlLBoTs.Uc9myqREGK1ztRykceL1QtJoXxkj4Di','2024-09-21 11:23:41.935420',_binary '\0',_binary '\0','2024-09-21 11:23:41.935420','r_2',NULL,NULL,'2'),(552451398919258112,'Huawei',_binary '\0','df1eb215362d7dcda6d0341d360c2610',NULL,0,NULL,'$2a$10$lD9ruy3WlsHmGAda1.SL1ey17JrJ0/KFdD8WaSluL5zbswrS18dLC','2024-09-21 11:24:37.553081',_binary '\0',_binary '\0','2024-09-21 11:24:37.553081','r_2',NULL,NULL,'3'),(552451636522385408,'ZTE',_binary '\0','a91fd8159a002ee51ca59f02dcc92b1f',NULL,0,NULL,'$2a$10$xO/EDFvB4TlI2gjfjYP82O.HgAoDeZw3SWoeLiTGHRPXtGDK5rTv2','2024-09-21 11:25:34.202789',_binary '\0',_binary '\0','2024-09-21 11:25:34.202789','r_2',NULL,NULL,'4');

CREATE TABLE `sf_device` (
    `sf_dev_id` INT NOT NULL COMMENT 'SF ID',
    `sf_name` VARCHAR(255) COMMENT 'SF名称',
    `sf_vender_id` TINYINT COMMENT '厂商编号, 1: CICTM; 2: Ericsson; 3: Huawei; 4: ZTE',

    -- 核心网增加的字段：
    `sf_type` TINYINT COMMENT 'SF类型, 0: 无线SF, 1: SCF, 2: SG, 3, NEF',
    `scf_url` VARCHAR(4000) COMMENT 'SCF URL',
    `subscription_id` VARCHAR(255) COMMENT 'SCF分配, 感知能力订阅ID',
    `civic_address_list` MEDIUMTEXT COMMENT '感知业务开展范围JSON',
    `af_notify_url_head` VARCHAR(4000) COMMENT 'AF控制面接收SF通知用的URL的头(因为AF可能有多个网络，需要告知SCF/SG，用哪个URL给自己发消息)',
    `scf_auth_code` VARCHAR(4000) COMMENT '核心网鉴权用的base64字符串, 转换方式: appId:appPassword 转换成base64编码',

    `company_id` VARCHAR(255) COMMENT '所属公司/机构',
    `province_code` VARCHAR(255) COMMENT '省级行政区',
    `city_code` VARCHAR(255) COMMENT '地级行政区',
    `district_code` VARCHAR(255) COMMENT '区',
    `remarks` TEXT COMMENT '备注',
    `tcp_keep_alive_interval` INT COMMENT '控制面心跳时间间隔(秒)',

    `is_delete` BOOLEAN COMMENT '是否已经删除',

    `create_usr_id` VARCHAR(255) COMMENT '创建此SF的用户ID',
    `create_date` DATETIME COMMENT '创建此SF的日期',

    `upd_usr_id` VARCHAR(255) COMMENT '更新此表的用户ID',
    `upd_date` DATETIME COMMENT '更新此表的日期',
    PRIMARY KEY (`sf_dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SF设备信息表';

CREATE TABLE `sf_sense_task` (
    `sf_dev_id` INT NOT NULL COMMENT 'SF ID',
    `task_id` INT NOT NULL COMMENT '任务ID',
    `task_name` VARCHAR(255) COMMENT '任务名字',
    `json_string` MEDIUMTEXT COMMENT 'StartSenseTaskReqBean JSON字符串',
    `company_id` VARCHAR(255) COMMENT '所属公司/机构',
    `remarks` TEXT COMMENT '备注',
    `is_enable` BOOLEAN COMMENT '是否已经停止',

    `sf_resp_result_status` TINYINT COMMENT 'SF返回的执行状态, 0:success, 1:fail',
    `sf_resp_failure_reason` VARCHAR(255) COMMENT 'SF返回的失败原因',

    `is_delete` BOOLEAN COMMENT '是否已经删除',


    -- 核心网增加的字段：
    `data_plane_domain_name` VARCHAR(255) COMMENT '数据面上报IP的domain name，整网规划决定，用于SPF选择用于对接AF的本端接口，由1-20个字母、数字、下划线、横杠组成',
    `data_plane_ipv4_addr` VARCHAR(255) COMMENT '数据面上报IP，IPv4和IPv6只能且必须携带其中1个',
    `data_plane_ipv6_addr` VARCHAR(255) COMMENT '数据面上报IP，IPv4和IPv6只能且必须携带其中1个',
    `data_plane_port` INT COMMENT 'AF接收用户面数据的端口号',
    `civic_address_list` MEDIUMTEXT COMMENT '感知业务开展范围JSON',

    `create_usr_id` VARCHAR(255) COMMENT '创建此SF的用户ID',
    `create_date` DATETIME COMMENT '创建此SF的日期',

    `upd_usr_id` VARCHAR(255) COMMENT '更新此表的用户ID',
    `upd_date` DATETIME COMMENT '更新此表的日期',
    PRIMARY KEY (`sf_dev_id`, `task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SF感知任务信息表';

-- 下面数据库表，需要从 uav_industrial_portfolio 库中迁移过来.
CREATE TABLE `sense_data_sink_config` (
  `id` bigint 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 DEFAULT NULL COMMENT '分发者类型',
  `public_key` varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '公钥',
  `config` varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置',
  `is_delete` tinyint(1) DEFAULT NULL COMMENT '是否删除',
  `upd_usr_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新用户id',
  `upd_date` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_sense_code` (`code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='SF adapter的分发配置表';

CREATE TABLE `core5g_coop_uav_sub` (
  `subscription_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sub_dto_json` MEDIUMTEXT NOT NULL COMMENT '核心网订阅信息Core5gPosInfoSubscribeReqDTO的JSON字符串',
  `notif_uri` varchar(4096) NOT NULL COMMENT '核心网提供的用于接收无人机位置信息的URI',

  PRIMARY KEY (`subscription_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='核心网合作无人机轨迹订阅信息表';