-- HRM 加班申请：协同办公审批 + 薪酬核算数据源

CREATE TABLE IF NOT EXISTS `hrm_overtime_application` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '申请ID',
    `no` varchar(32) NOT NULL COMMENT '申请单号',
    `user_id` bigint NOT NULL COMMENT '系统用户ID',
    `employee_id` bigint NOT NULL COMMENT '员工ID',
    `dept_id` bigint NOT NULL COMMENT '部门ID',
    `overtime_date` date NOT NULL COMMENT '加班日期',
    `start_time` time NOT NULL COMMENT '开始时间',
    `end_time` time NOT NULL COMMENT '结束时间',
    `duration` decimal(6,1) NOT NULL COMMENT '扣除用餐后的加班时长（0.5小时取整）',
    `overtime_type` tinyint NOT NULL COMMENT '1-工作日，2-休息日，3-法定节假日',
    `cross_day` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否跨天',
    `meal_deduction` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否扣除用餐时间',
    `meal_deduction_hours` decimal(4,1) NOT NULL DEFAULT 0 COMMENT '用餐扣除小时数',
    `reason` varchar(500) NOT NULL COMMENT '加班原因',
    `related_type` tinyint DEFAULT NULL COMMENT '关联类型：1-项目，2-工单',
    `related_code` varchar(100) DEFAULT NULL COMMENT '项目/工单编号',
    `work_content` varchar(1000) DEFAULT NULL COMMENT '工作内容简述',
    `status` tinyint NOT NULL DEFAULT 0 COMMENT '0-草稿，10-审批中，20-通过，30-驳回，40-撤销',
    `process_instance_id` varchar(64) DEFAULT NULL COMMENT '协同办公流程实例ID',
    `approve_time` datetime 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_hrm_overtime_no` (`no`),
    KEY `idx_hrm_overtime_user_date_status` (`user_id`, `overtime_date`, `status`),
    KEY `idx_hrm_overtime_process_instance` (`process_instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM加班申请表';

-- 协同办公流程分类已在 bpm/manager/category 中维护，分类编码为 overtime_approve。
-- 仍需在该分类下建立并发布流程定义 key=hrm_overtime。

-- 人事审批菜单及按钮权限
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`)
SELECT 6007, '加班申请', '', 2, 4, 6002, 'overtime', 'ep:timer', 'hrm/overtime/index', 'HrmOvertimeApplication', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 6007);

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`)
SELECT 60071, '加班申请查询', 'hrm:overtime:query', 3, 1, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60071);
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`)
SELECT 60072, '加班申请新增', 'hrm:overtime:create', 3, 2, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60072);
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`)
SELECT 60073, '加班申请修改', 'hrm:overtime:update', 3, 3, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60073);
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`)
SELECT 60074, '加班申请删除', 'hrm:overtime:delete', 3, 4, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60074);
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`)
SELECT 60075, '加班申请提交', 'hrm:overtime:submit', 3, 5, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60075);
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`)
SELECT 60076, '加班申请撤销', 'hrm:overtime:cancel', 3, 6, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60076);
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`)
SELECT 60077, '加班申请管理', 'hrm:overtime:manage', 3, 7, 6007, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 60077);

INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
SELECT 1, menu_id, '1', NOW(), '1', NOW(), b'0' FROM (
    SELECT 6007 menu_id UNION ALL SELECT 60071 UNION ALL SELECT 60072 UNION ALL
    SELECT 60073 UNION ALL SELECT 60074 UNION ALL SELECT 60075 UNION ALL SELECT 60076 UNION ALL SELECT 60077
) ids WHERE NOT EXISTS (SELECT 1 FROM `system_role_menu` rm WHERE rm.`role_id` = 1 AND rm.`menu_id` = ids.menu_id);

SELECT 'HRM 加班申请初始化完成；请在协同办公发布 hrm_overtime 流程' AS result;