-- =============================================
-- HRM 考勤管理补充 SQL 初始化脚本
-- 包含节假日配置、考勤统计汇总表、菜单权限
-- =============================================

-- 1. 节假日配置表
CREATE TABLE IF NOT EXISTS `hrm_attendance_holiday` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '节假日ID',
    `name` varchar(100) NOT NULL COMMENT '节假日名称',
    `date` date NOT NULL COMMENT '日期',
    `type` tinyint NOT NULL DEFAULT 1 COMMENT '类型：1-休息日，2-工作日',
    `is_legal` tinyint NOT NULL DEFAULT 0 COMMENT '是否法定节假日：0-否，1-是',
    `remark` varchar(500) DEFAULT NULL COMMENT '备注',
    `creator` varchar(64) DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_date` (`date`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM考勤节假日配置表';

-- 2. 考勤统计汇总表
CREATE TABLE IF NOT EXISTS `hrm_attendance_summary` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '汇总ID',
    `user_id` bigint NOT NULL COMMENT '员工ID',
    `dept_id` bigint NOT NULL COMMENT '部门ID',
    `year_month` varchar(7) NOT NULL COMMENT '年月，格式：yyyy-MM',
    `work_days` int NOT NULL DEFAULT 0 COMMENT '应出勤天数',
    `actual_days` int NOT NULL DEFAULT 0 COMMENT '实际出勤天数',
    `late_count` int NOT NULL DEFAULT 0 COMMENT '迟到次数',
    `early_count` int NOT NULL DEFAULT 0 COMMENT '早退次数',
    `absent_count` int NOT NULL DEFAULT 0 COMMENT '缺卡次数',
    `overtime_hours` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '加班时长（小时）',
    `leave_hours` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '请假时长（小时）',
    `remark` varchar(500) DEFAULT NULL COMMENT '备注',
    `creator` varchar(64) DEFAULT '' COMMENT '创建者',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater` varchar(64) DEFAULT '' COMMENT '更新者',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_year_month` (`user_id`, `year_month`, `deleted`),
    KEY `idx_dept_year_month` (`dept_id`, `year_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM考勤统计汇总表';

-- =============================================
-- 3. 菜单权限
-- =============================================

-- 3.1 考勤规则菜单
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (6013, '考勤规则', '', 2, 0, 6010, 'rule', 'ep:setting', 'hrm/attendance/rule/index', 'HrmAttendanceRule', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60131, '考勤规则查询', 'hrm:attendance:query', 3, 1, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60132, '考勤规则新增', 'hrm:attendance:create', 3, 2, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60133, '考勤规则修改', 'hrm:attendance:update', 3, 3, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60134, '考勤规则删除', 'hrm:attendance:delete', 3, 4, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60135, '考勤规则导出', 'hrm:attendance:export', 3, 5, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 3.2 节假日配置菜单
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (6014, '节假日配置', '', 2, 3, 6010, 'holiday', 'ep:calendar', 'hrm/attendance/holiday/index', 'HrmAttendanceHoliday', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60141, '节假日配置查询', 'hrm:attendance:query', 3, 1, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60142, '节假日配置新增', 'hrm:attendance:create', 3, 2, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60143, '节假日配置修改', 'hrm:attendance:update', 3, 3, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60144, '节假日配置删除', 'hrm:attendance:delete', 3, 4, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60145, '节假日配置导出', 'hrm:attendance:export', 3, 5, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 3.3 考勤统计汇总菜单
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (6015, '考勤统计', '', 2, 4, 6010, 'summary', 'ep:data-analysis', 'hrm/attendance/summary/index', 'HrmAttendanceSummary', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60151, '考勤统计查询', 'hrm:attendance:query', 3, 1, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60152, '考勤统计生成', 'hrm:attendance:calculate', 3, 2, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60153, '考勤统计导出', 'hrm:attendance:export', 3, 3, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- 3.4 打卡权限
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (60114, '打卡', 'hrm:attendance:clock', 3, 4, 6011, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 4. 角色菜单关联
-- =============================================

-- 给角色 ID=1（超级管理员）分配新增的菜单权限
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 6013, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60131, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60132, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60133, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60134, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60135, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 6014, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60141, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60142, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60143, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60144, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60145, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 6015, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60151, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60152, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60153, '1', NOW(), '1', NOW(), b'0');
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (1, 60114, '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 5. BPM 审批分类配置（可选）
-- =============================================

-- 补卡审批分类
INSERT INTO `bpm_category` (`id`, `name`, `code`, `description`, `status`, `sort`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES (200, '考勤异常审批', 'hrm_attendance_approve', 'HRM考勤异常审批分类', 0, 50, '1', NOW(), '1', NOW(), b'0');

-- =============================================
-- 执行完成提示
-- =============================================
SELECT 'HRM 考勤管理补充 SQL 初始化完成！' AS result;