144 lines
8.1 KiB
SQL
144 lines
8.1 KiB
SQL
-- 动态时间窗预约改版：多窗口模型（规则 → 多个开放窗口(完整datetime,支持跨天) → 窗口内 日期×餐段 明细）。
|
||
-- 旧模型（相对日期类型 startTimeType/endTimeType + hard_appt_time 映射表）作废：
|
||
-- 存量旧规则软删保留（del_flag='1'），hard_appt_time 表 DROP，hard_appt 旧列 DROP。
|
||
-- 订单 hard_order.appt_id 语义变为"窗口ID"（指向新表 hard_appt_window）；旧订单指向软删规则行，展示有判空保护。
|
||
-- 幂等迁移脚本：可反复执行。每条 DDL 先查 information_schema，已存在/已删除则跳过（SELECT 1 占位）。
|
||
-- 执行顺序：本脚本 → 重启 hardware 服务 → App 发版 → 管理端前端。
|
||
-- 权限复用 hard_hardAppt_*，无新增菜单。
|
||
|
||
-- 【可选诊断】改造前现状。
|
||
SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' ORDER BY ORDINAL_POSITION;
|
||
|
||
-- 一、hard_appt 旧列删除（title/hour_type/sort/start_time/start_time_type/end_time/end_time_type/reserve_mode，各自独立判断）。
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `title`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'title');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `hour_type`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'hour_type');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `sort`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'sort');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `start_time`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'start_time');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `start_time_type`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'start_time_type');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `end_time`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'end_time');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `end_time_type`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'end_time_type');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt` DROP COLUMN `reserve_mode`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' AND COLUMN_NAME = 'reserve_mode');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
-- 二、存量旧规则软删（新模型与旧模型语义不同，作废；旧订单展示字段有判空保护）。
|
||
|
||
UPDATE `pjcloudx_hardware`.`hard_appt` SET del_flag = '1' WHERE del_flag = '0';
|
||
|
||
-- 三、旧映射表 DROP（表已不存在则跳过）。
|
||
|
||
SET @ddl = (SELECT IF(COUNT(*) > 0,
|
||
'DROP TABLE `pjcloudx_hardware`.`hard_appt_time`',
|
||
'SELECT 1')
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt_time');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
-- 四、新表：开放窗口 + 窗口内可预约明细（日期×餐段）。
|
||
|
||
CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_appt_window` (
|
||
`id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '编号',
|
||
`appt_id` bigint(19) NOT NULL COMMENT '预约规则ID(hard_appt.id)',
|
||
`mch_id` bigint(19) NOT NULL COMMENT '商户ID',
|
||
`start_time` datetime NOT NULL COMMENT '开始预约时间(支持跨天窗口)',
|
||
`end_time` datetime NOT NULL COMMENT '结束预约时间(必须晚于开始)',
|
||
`auto_refund_time` datetime DEFAULT NULL COMMENT '免审退款申请截止时间(闭区间)，空=开放预约结束时间；须在(start_time,end_time]范围内',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标记,0正常,1删除',
|
||
`tenant_id` bigint(19) DEFAULT NULL COMMENT '租户ID',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_appt_window_appt` (`tenant_id`, `appt_id`, `del_flag`),
|
||
KEY `idx_appt_window_mch` (`tenant_id`, `mch_id`, `del_flag`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '动态时间窗预约开放窗口';
|
||
|
||
-- 四点一、已按旧版建表的库补列（列已存在则跳过）。
|
||
SET @ddl = (SELECT IF(COUNT(*) = 0,
|
||
'ALTER TABLE `pjcloudx_hardware`.`hard_appt_window` ADD COLUMN `auto_refund_time` datetime DEFAULT NULL COMMENT ''免审退款申请截止时间(闭区间)，空=开放预约结束时间；须在(start_time,end_time]范围内'' AFTER `end_time`',
|
||
'SELECT 1')
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt_window' AND COLUMN_NAME = 'auto_refund_time');
|
||
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
CREATE TABLE IF NOT EXISTS `pjcloudx_hardware`.`hard_appt_item` (
|
||
`id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '编号',
|
||
`window_id` bigint(19) NOT NULL COMMENT '预约窗口ID(hard_appt_window.id)',
|
||
`mch_id` bigint(19) NOT NULL COMMENT '商户ID',
|
||
`target_date` date NOT NULL COMMENT '目标用餐日期',
|
||
`hour_type` varchar(32) NOT NULL COMMENT '餐段字典值(daily_menu)',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
|
||
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
|
||
`del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标记,0正常,1删除',
|
||
`tenant_id` bigint(19) DEFAULT NULL COMMENT '租户ID',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_appt_item_window` (`tenant_id`, `window_id`, `del_flag`),
|
||
KEY `idx_appt_item_mch_date` (`tenant_id`, `mch_id`, `target_date`)
|
||
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '动态时间窗可预约明细(日期×餐段)';
|
||
|
||
-- 五、【验收】
|
||
-- 1) hard_appt 仅剩规则头字段 + 审计列：
|
||
SELECT COLUMN_NAME FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt' ORDER BY ORDINAL_POSITION;
|
||
-- 2) 旧规则全部软删（期望 0）：
|
||
SELECT COUNT(*) AS active_old_rules FROM `pjcloudx_hardware`.`hard_appt` WHERE del_flag = '0';
|
||
-- 3) 旧映射表已不存在（期望 0）：
|
||
SELECT COUNT(*) AS legacy_time_table FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt_time';
|
||
-- 4) 新表就位：
|
||
SELECT TABLE_NAME FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME IN ('hard_appt_window', 'hard_appt_item');
|
||
-- 5) 窗口表含免审退款截止列（期望 1）：
|
||
SELECT COUNT(*) AS window_has_auto_refund_time FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'pjcloudx_hardware' AND TABLE_NAME = 'hard_appt_window' AND COLUMN_NAME = 'auto_refund_time';
|