sql/upgrade_260806/deviceservice.sql

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `deviceservice`.`device_base_info`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `project_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  `park_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车场ID',
  `local_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '线下设备主键ID',
  `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备编号',
  `no_code` int NOT NULL COMMENT '设备编号索引',
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名称',
  `parent_id` int NOT NULL COMMENT '父级设备ID，没有为0',
  `ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备IP',
  `port` int NULL DEFAULT NULL COMMENT '端口',
  `mac` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Mac地址',
  `net_mask` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '子网掩码',
  `gateway_ip` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备网关',
  `product` int NOT NULL COMMENT '设备产品线(0-车行;1-车位;2-人行)',
  `type` int NOT NULL COMMENT '设备类型',
  `model` int NOT NULL COMMENT '设备型号',
  `sn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备序列号',
  `cpuid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备cpuid',
  `certification_status` int NOT NULL COMMENT '设备状态(0-未认证;1-非本项目设备;2-非官方设备;3-密钥系统认证失败;4-认证成功;5- 无需认证)',
  `last_heartbeat_time` bigint NULL DEFAULT NULL COMMENT '最后心跳时间(13位毫秒时间戳)，在线/脱机判定依据；NULL=从未上报心跳',
  `mac_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '机号',
  `bluetooth` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '蓝牙',
  `device_level` int NOT NULL COMMENT '设备层级(主设备为1;二级子设备为2)',
  `parent_id_path` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父级设备导航ID，用逗号分隔',
  `need_upgrade` int NULL DEFAULT NULL COMMENT '设备是否需要升级',
  `deleted` int NOT NULL DEFAULT 0 COMMENT '是否删除(0-否;1-是)',
  `remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '修改人',
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `model_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '设备型号名称',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sn`(`sn` ASC) USING BTREE,
  INDEX `idx_parkid`(`park_id` ASC) USING BTREE,
  INDEX `idx_localid_parkid`(`local_id` ASC, `park_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备基础信息' ROW_FORMAT = Dynamic;

CREATE TABLE `deviceservice`.`device_camera_protocol_map`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '主键，雪花ID',
  `camera_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '相机型号 code，对应 device_camera_type 字典，如 R5L/C5',
  `protocol` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '协议 code，对应 device_camera_protocol 字典，如 VZICLOUD/XLT',
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '0禁用 1启用',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
  `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_camera_type`(`camera_type` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '相机型号→协议绑定关系' ROW_FORMAT = DYNAMIC;

CREATE TABLE `deviceservice`.`device_lane_relation`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `project_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  `park_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车场ID',
  `lane_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车道ID',
  `device_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备ID',
  `device_type` int NOT NULL COMMENT '设备类型',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_deviceid_parkid`(`device_id` ASC, `park_id` ASC) USING BTREE,
  INDEX `idx_laneid_parkid`(`lane_id` ASC, `park_id` ASC) USING BTREE,
  INDEX `idx_parkid`(`park_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备车道关系表' ROW_FORMAT = Dynamic;

CREATE TABLE `deviceservice`.`device_param`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `project_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  `park_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车场ID',
  `device_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备ID',
  `param_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '参数key',
  `param_value` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '参数value',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_deviceid_paramkey`(`device_id` ASC, `param_key` ASC) USING BTREE,
  INDEX `idx_parkid`(`park_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备参数配置' ROW_FORMAT = Dynamic;


INSERT INTO `test_deviceservice`.`device_camera_protocol_map` (`id`, `camera_type`, `protocol`, `status`, `remark`, `create_name`, `create_time`, `update_name`, `update_time`) VALUES ('100000000000000001', 'R5L', 'VZICLOUD', 1, '初始化', 'system', '2026-07-30 12:00:14', NULL, NULL);
INSERT INTO `test_deviceservice`.`device_camera_protocol_map` (`id`, `camera_type`, `protocol`, `status`, `remark`, `create_name`, `create_time`, `update_name`, `update_time`) VALUES ('100000000000000002', 'C5', 'VZICLOUD', 1, '初始化', 'system', '2026-07-30 12:00:14', NULL, NULL);
SET FOREIGN_KEY_CHECKS=1;