sql/upgrade_260806/parkservice.sql

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `parkservice`.`park_base_info` ADD COLUMN `real_total_place` int NULL DEFAULT 0 COMMENT '实际对外总停车位' AFTER `open_tag`;

ALTER TABLE `parkservice`.`park_base_info` ADD COLUMN `real_longitude` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '实际经度' AFTER `real_total_place`;

ALTER TABLE `parkservice`.`park_base_info` ADD COLUMN `real_latitude` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '' COMMENT '实际纬度' AFTER `real_longitude`;

CREATE TABLE `parkservice`.`park_box`  (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '岗亭名称',
  `project_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '项目编号',
  `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '岗亭表\r\n' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_box_lane`  (
  `box_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `lane_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '本地上传通道id',
  `park_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车场id',
  INDEX `idx_box_id`(`box_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '岗亭车道表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_box_user`  (
  `box_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int NOT NULL,
  `username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '账号',
  INDEX `idx_box_id`(`box_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '岗亭人员表' ROW_FORMAT = DYNAMIC;

ALTER TABLE `parkservice`.`park_card_type` MODIFY COLUMN `code` int NOT NULL COMMENT '套餐类型编码;临停101开始；免费卡201开始；月卡301开始；集团通卡401开始；' AFTER `original_name`;

ALTER TABLE `parkservice`.`park_card_type` MODIFY COLUMN `category` int NOT NULL COMMENT '分类，如：1：临停； 2：免费卡；3：月卡；4：集团通卡；' AFTER `type`;

ALTER TABLE `parkservice`.`park_card_type` DROP COLUMN `custom_flag`;

CREATE TABLE `parkservice`.`park_free_open_reason`  (
  `id` bigint NOT NULL COMMENT '主键',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `reason_text` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '免费开闸原因',
  `priority` tinyint NOT NULL DEFAULT 50 COMMENT '排序优先级1-99，越大越靠前',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_park_free_reason`(`park_id` ASC, `priority` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场免费开闸原因' ROW_FORMAT = DYNAMIC;

ALTER TABLE `parkservice`.`park_lane_info` ADD COLUMN `device_sn` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '绑定设备SN' AFTER `third_lane_id`;

ALTER TABLE `parkservice`.`park_lane_info` ADD COLUMN `lane_status` tinyint NULL DEFAULT 1 COMMENT '车道状态 1-正常 2-禁用 3-常开 4-常闭' AFTER `device_sn`;

ALTER TABLE `parkservice`.`park_lane_info` ADD INDEX `idx_device_sn`(`device_sn` ASC) USING BTREE;

CREATE TABLE `parkservice`.`park_lane_kv_store`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `project_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '项目编号',
  `park_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `lane_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车道编号（业务编码）',
  `biz_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '业务类型：permission=通行权限, biz_param=车道业务参数',
  `payload` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'JSON 序列化后的载荷，与 Redis value 同形',
  `version` int NOT NULL DEFAULT 0 COMMENT '乐观锁版本号（每次更新+1）',
  `delete_flag` tinyint NOT NULL DEFAULT 0 COMMENT '0-正常 1-删除',
  `create_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_park_lane_biz`(`park_id` ASC, `lane_id` ASC, `biz_type` ASC) USING BTREE,
  INDEX `idx_project_park`(`project_no` ASC, `park_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 114 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车道级业务 KV 存储（payload 为 JSON）' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_lane_remaining_adjust_rule`  (
  `id` bigint NOT NULL COMMENT '主键',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `lane_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '通道ID',
  `adjust_type` tinyint NOT NULL DEFAULT 0 COMMENT '0不变 1增加 2减少',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_park_lane_remaining`(`park_id` ASC, `lane_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '通道剩余车位增减规则' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_manual_open_reason`  (
  `id` bigint NOT NULL COMMENT '主键',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `reason_text` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手动开闸原因',
  `priority` tinyint NOT NULL DEFAULT 50 COMMENT '排序优先级1-99，越大越靠前',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_park_manual_reason`(`park_id` ASC, `priority` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场手动开闸原因' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_package_runtime_config`  (
  `id` bigint NOT NULL COMMENT '主键',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `package_kind` tinyint NOT NULL COMMENT '1临停/计费套餐 2固定车/卡套餐',
  `package_ref_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '套餐标识(如卡类型编码字符串或计费套餐ID)',
  `count_remaining_space` tinyint NOT NULL DEFAULT 1 COMMENT '是否参与剩余车位计算 1是 0否',
  `access_mode` tinyint NOT NULL DEFAULT 2 COMMENT '1单进单出 2多进多出 3单进多出 4多进单出',
  `full_lot_action` tinyint NOT NULL DEFAULT 3 COMMENT '1自动开闸 2确认开闸 3禁止入场',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_park_pkg_runtime`(`park_id` ASC, `package_kind` ASC, `package_ref_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场套餐运行时策略' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_place_rel`  (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID，自增',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联车场ID',
  `place_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联车位ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场车位绑定表' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_privilege_plate_rule`  (
  `id` bigint NOT NULL COMMENT '主键',
  `park_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `vehicle_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'FIRE/CIVIL_AVIATION/CONSULATE/EMBASSY/MILITARY_POLICE',
  `regex_pattern` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车牌匹配正则',
  `enabled` tinyint NOT NULL DEFAULT 1 COMMENT '1启用 0禁用',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_park_privilege_type`(`park_id` ASC, `vehicle_type` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场特权车正则规则' ROW_FORMAT = DYNAMIC;

ALTER TABLE `parkservice`.`park_reserve` MODIFY COLUMN `effective_time` int NOT NULL COMMENT '预约前后有效时间(分钟)' AFTER `cancel_time`;

CREATE TABLE `parkservice`.`park_voice_scene`  (
  `id` bigint NOT NULL COMMENT '雪花ID',
  `park_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '车场ID',
  `sound_id` int NOT NULL COMMENT '场景编号',
  `sound_car_type` tinyint NOT NULL COMMENT '车辆类型 0-9',
  `scene_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `scene_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `enabled` tinyint NOT NULL DEFAULT 1 COMMENT '1启用 0车场不使用该场景',
  `default_voice` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `custom_voice` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line2` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line3` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line4` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `lcd_col1` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `lcd_col2` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `repeat_interval` int NOT NULL DEFAULT 0,
  `repeat_count` int NOT NULL DEFAULT 0,
  `voice_scene_type` int NOT NULL DEFAULT 0,
  `extra_properties` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '{}',
  `is_to_common_default` tinyint NOT NULL DEFAULT 0,
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_park_voice`(`park_id` ASC, `sound_car_type` ASC, `sound_id` ASC) USING BTREE,
  INDEX `idx_park_voice_park`(`park_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '车场语音场景' ROW_FORMAT = DYNAMIC;

CREATE TABLE `parkservice`.`park_voice_scene_template`  (
  `id` bigint NOT NULL COMMENT '雪花ID',
  `sound_id` int NOT NULL COMMENT '场景/语音编号，对应 Excel SoundId',
  `sound_car_type` tinyint NOT NULL COMMENT '车辆类型 0-9',
  `scene_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '内部编码，如 S2041',
  `scene_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '场景说明/名称（Remark）',
  `default_voice` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '默认语音 DefaultContent',
  `led_line1` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line2` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line3` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `led_line4` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `lcd_col1` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `lcd_col2` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `repeat_interval` int NOT NULL DEFAULT 0,
  `repeat_count` int NOT NULL DEFAULT 0,
  `voice_scene_type` int NOT NULL DEFAULT 0 COMMENT '对应 Excel Type',
  `extra_properties` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '{}' COMMENT 'JSON 扩展',
  `is_to_common_default` tinyint NOT NULL DEFAULT 0 COMMENT '是否转化为通用语音场景 0/1',
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_voice_tpl`(`sound_car_type` ASC, `sound_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '语音场景模板' ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS=1;