-- =====================================================
-- HRM 考勤节假日配置：补齐「时间段」结构（start_date / end_date）
--
-- 背景：
--   2026-09-30 的提交 a797b580 将「节假日配置」由单日(date)改为时间段(startDate/endDate)，
--   DO/VO/Mapper 已按 start_date、end_date 查询，但数据库表结构没有同步。
--   2026-10-17 新增的 GET /hrm/attendance/record/my-status 第一次走到节假日查询，
--   报 Unknown column 'start_date' in 'field list'。
--
-- 内容：
--   ① hrm_attendance_holiday 新增 start_date、end_date
--   ② 存量数据按 date 回填（date 列保留兼容，保存时仍写入 start_date）
--   ③ 回填后收紧为 NOT NULL，避免后续写入半条时间段
--
-- 幂等：可重复执行（MySQL 不支持 ADD COLUMN IF NOT EXISTS，先查 information_schema）
-- 执行方式：
--   mysql -h<host> -P<port> -u<user> -p --default-character-set=utf8mb4 <db> < docs/sql/hrm_attendance_holiday_date_range_20261017.sql
-- =====================================================

SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- ① 新增 start_date
-- ------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'hrm_attendance_holiday'
                     AND column_name = 'start_date');
SET @ddl = IF(@col_exists = 0,
              'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `start_date` date DEFAULT NULL COMMENT ''开始日期'' AFTER `date`',
              'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- ② 新增 end_date
-- ------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                     AND table_name = 'hrm_attendance_holiday'
                     AND column_name = 'end_date');
SET @ddl = IF(@col_exists = 0,
              'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `end_date` date DEFAULT NULL COMMENT ''结束日期'' AFTER `start_date`',
              'SELECT 1');
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- ③ 存量数据回填：单日配置的起止日期都等于原 date
-- ------------------------------------------------------------
UPDATE `hrm_attendance_holiday` SET `start_date` = `date` WHERE `start_date` IS NULL;
UPDATE `hrm_attendance_holiday` SET `end_date` = `date` WHERE `end_date` IS NULL;

-- ------------------------------------------------------------
-- ④ 回填完成后收紧为 NOT NULL（幂等）
-- ------------------------------------------------------------
ALTER TABLE `hrm_attendance_holiday`
    MODIFY COLUMN `start_date` date NOT NULL COMMENT '开始日期',
    MODIFY COLUMN `end_date` date NOT NULL COMMENT '结束日期';

-- ------------------------------------------------------------
-- 校验
-- ------------------------------------------------------------
SELECT 'start_date' AS item, COUNT(*) AS cnt FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday' AND column_name = 'start_date'
UNION ALL
SELECT 'end_date', COUNT(*) FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday' AND column_name = 'end_date';