shanxia2.0/backend/scripts/migrate_20260820_duty_shift_dict_time.sql

-- backend/scripts/migrate_20260820_duty_shift_dict_time.sql
-- 值班排班：字典类型/工作安排 + slot 时段与 work_plans
-- 可重复执行（字典 NOT EXISTS；ALTER 检查 information_schema）

SET NAMES utf8mb4;

-- 1) 字典类型（tenant shanxia2.0）
INSERT INTO `sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'DUTYDICTTYPE00000000000000000001',
  '值班类型',
  'duty_shift_type',
  'shanxia2.0',
  90,
  '值班排班班次类型',
  '',
  '',
  NOW(),
  '',
  NOW(),
  0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_type`
  WHERE `dict_type` = 'duty_shift_type'
    AND `tenant_no` = 'shanxia2.0'
    AND `del_flag` = 0
);

INSERT INTO `sys_dict_type` (
  `id`, `dict_name`, `dict_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'DUTYDICTTYPE00000000000000000002',
  '值班工作安排',
  'duty_work_plan',
  'shanxia2.0',
  91,
  '值班排班工作安排',
  '',
  '',
  NOW(),
  '',
  NOW(),
  0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_type`
  WHERE `dict_type` = 'duty_work_plan'
    AND `tenant_no` = 'shanxia2.0'
    AND `del_flag` = 0
);

-- 2) 字典数据预置
INSERT INTO `sys_dict_data` (
  `id`, `dict_label`, `dict_value`, `tag_type`, `dict_type`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT * FROM (
  SELECT 'DUTYDICTDATA0000000000000000001' AS id, '早班' AS dict_label, 'morning' AS dict_value, '' AS tag_type, 'duty_shift_type' AS dict_type, 'shanxia2.0' AS tenant_no, 1 AS order_num, NULL AS remark, '' AS create_by, '' AS create_dept_id, NOW() AS create_time, '' AS update_by, NOW() AS update_time, 0 AS del_flag
  UNION ALL SELECT 'DUTYDICTDATA0000000000000000002', '晚班', 'evening', '', 'duty_shift_type', 'shanxia2.0', 2, NULL, '', '', NOW(), '', NOW(), 0
  UNION ALL SELECT 'DUTYDICTDATA0000000000000000003', '值班', 'duty', '', 'duty_shift_type', 'shanxia2.0', 3, NULL, '', '', NOW(), '', NOW(), 0
  UNION ALL SELECT 'DUTYDICTDATA0000000000000000004', '配酸', 'peisuan', '', 'duty_work_plan', 'shanxia2.0', 1, NULL, '', '', NOW(), '', NOW(), 0
  UNION ALL SELECT 'DUTYDICTDATA0000000000000000005', '装卸', 'zhuangxie', '', 'duty_work_plan', 'shanxia2.0', 2, NULL, '', '', NOW(), '', NOW(), 0
) AS t
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict_data` d
  WHERE d.`tenant_no` = t.`tenant_no`
    AND d.`dict_type` = t.`dict_type`
    AND d.`dict_value` = t.`dict_value`
    AND d.`del_flag` = 0
);

-- 3) duty_slot 扩字段（列已存在则跳过）
SET @db = DATABASE();

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'duty_slot' AND COLUMN_NAME = 'work_plans'
);
SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE `duty_slot` ADD COLUMN `work_plans` varchar(500) DEFAULT NULL COMMENT ''工作安排dict_value逗号分隔'' AFTER `capacity`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'duty_slot' AND COLUMN_NAME = 'start_time'
);
SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE `duty_slot` ADD COLUMN `start_time` time DEFAULT NULL COMMENT ''值班开始时间'' AFTER `work_plans`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'duty_slot' AND COLUMN_NAME = 'end_time'
);
SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE `duty_slot` ADD COLUMN `end_time` time DEFAULT NULL COMMENT ''值班结束时间'' AFTER `start_time`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'duty_slot' AND COLUMN_NAME = 'cross_day'
);
SET @sql = IF(
  @col_exists = 0,
  'ALTER TABLE `duty_slot` ADD COLUMN `cross_day` smallint(6) NOT NULL DEFAULT 0 COMMENT ''结束是否次日0/1'' AFTER `end_time`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4) 回填时段（仅 start_time 为空的历史行）
UPDATE `duty_slot`
SET `start_time` = '08:00:00', `end_time` = '18:00:00', `cross_day` = 0
WHERE `del_flag` = 0 AND `shift_type` = 'morning' AND `start_time` IS NULL;

UPDATE `duty_slot`
SET `start_time` = '18:00:00', `end_time` = '07:00:00', `cross_day` = 1
WHERE `del_flag` = 0 AND `shift_type` = 'evening' AND `start_time` IS NULL;

-- 5) 回填工作安排（整段匹配）；其它 remark 保留为备注
UPDATE `duty_slot`
SET `work_plans` = 'peisuan', `remark` = NULL
WHERE `del_flag` = 0
  AND TRIM(`remark`) = '配酸'
  AND (`work_plans` IS NULL OR `work_plans` = '');

UPDATE `duty_slot`
SET `work_plans` = 'zhuangxie', `remark` = NULL
WHERE `del_flag` = 0
  AND TRIM(`remark`) = '装卸'
  AND (`work_plans` IS NULL OR `work_plans` = '');

UPDATE `duty_slot`
SET `work_plans` = 'peisuan,zhuangxie', `remark` = NULL
WHERE `del_flag` = 0
  AND TRIM(`remark`) IN ('配酸,装卸', '配酸，装卸', '装卸,配酸', '装卸，配酸')
  AND (`work_plans` IS NULL OR `work_plans` = '');