-- OnboardX 入职业务数据重置脚本
--
-- 保留：
--   1. ob_variable_definition  合同变量字典
--   2. ob_material_type        上传资料类型
--
-- 清除：公司、部门、岗位、合同模板、入职模板、任务、员工档案及全部流程明细。
-- 不处理：sys_file 表记录和阿里云 OSS 对象，避免误删系统其他模块共用文件。
--
-- 重要：请确认已经连接 OnboardX 数据库后再执行。

USE `OnboardX`;

DROP PROCEDURE IF EXISTS `ob_reset_business_data`;

DELIMITER $$

CREATE PROCEDURE `ob_reset_business_data`()
BEGIN
    DECLARE old_foreign_key_checks INT DEFAULT 1;
    DECLARE variable_definition_count_before BIGINT DEFAULT 0;
    DECLARE variable_definition_count_after BIGINT DEFAULT 0;
    DECLARE material_type_count_before BIGINT DEFAULT 0;
    DECLARE material_type_count_after BIGINT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET FOREIGN_KEY_CHECKS = old_foreign_key_checks;
        RESIGNAL;
    END;

    IF LOWER(DATABASE()) <> 'onboardx' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '当前数据库不是 OnboardX，已拒绝执行入职业务数据清理';
    END IF;

    SELECT COUNT(*)
    INTO variable_definition_count_before
    FROM `ob_variable_definition`;

    SELECT COUNT(*)
    INTO material_type_count_before
    FROM `ob_material_type`;

    SET old_foreign_key_checks = @@FOREIGN_KEY_CHECKS;
    SET FOREIGN_KEY_CHECKS = 0;

    START TRANSACTION;

    -- 一、删除任务运行数据及快照。
    -- 兼容尚未执行员工人事管理迁移的旧数据库。
    IF EXISTS (
        SELECT 1
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'ob_employee_attachment'
    ) THEN
        DELETE FROM ob_employee_attachment;
    END IF;
    IF EXISTS (
        SELECT 1
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'ob_employee_change_record'
    ) THEN
        DELETE FROM ob_employee_change_record;
    END IF;
    -- 兼容尚未执行员工合同快照迁移的旧数据库。
    IF EXISTS (
        SELECT 1
        FROM `information_schema`.`TABLES`
        WHERE `TABLE_SCHEMA` = DATABASE()
          AND `TABLE_NAME` = 'ob_employee_contract_snapshot'
    ) THEN
        SET @delete_employee_contract_snapshot_sql = 'DELETE FROM `ob_employee_contract_snapshot`';
        PREPARE delete_employee_contract_snapshot_statement FROM @delete_employee_contract_snapshot_sql;
        EXECUTE delete_employee_contract_snapshot_statement;
        DEALLOCATE PREPARE delete_employee_contract_snapshot_statement;
    END IF;
    DELETE FROM `ob_task_operation_log`;
    DELETE FROM `ob_task_audit`;
    DELETE FROM `ob_task_material`;
    DELETE FROM `ob_task_contract_variable`;
    DELETE FROM `ob_task_contract`;
    DELETE FROM `ob_task_form_value`;
    DELETE FROM `ob_task_material_requirement`;
    DELETE FROM `ob_task_form_requirement`;
    DELETE FROM `ob_task_employee_info`;
    DELETE FROM `ob_task_link`;
    DELETE FROM `ob_onboarding_task`;
    DELETE FROM `ob_employee`;

    -- 二、删除入职模板及其关联配置。
    DELETE FROM `ob_onboarding_template_material`;
    DELETE FROM `ob_onboarding_template_form_field`;
    DELETE FROM `ob_onboarding_template_contract`;
    DELETE FROM `ob_onboarding_template_position`;
    DELETE FROM `ob_onboarding_template`;

    -- 三、删除合同模板实例数据。
    -- ob_template_variable 是具体 DOCX 版本识别出的变量，不是全局合同变量字典。
    DELETE FROM `ob_contract_template_position`;
    DELETE FROM `ob_template_variable`;
    DELETE FROM `ob_contract_template_version`;
    DELETE FROM `ob_contract_template`;

    -- 四、删除旧版公司岗位配置和动态字段实例。
    DELETE FROM `ob_contract_scope`;
    DELETE FROM `ob_form_requirement`;
    DELETE FROM `ob_material_requirement`;
    DELETE FROM `ob_form_field`;

    -- 五、最后删除组织基础数据。
    DELETE FROM `ob_position`;
    DELETE FROM `ob_department`;
    DELETE FROM `ob_company`;

    -- 防止后续维护脚本时误把两张保留表加入删除范围。
    SELECT COUNT(*)
    INTO variable_definition_count_after
    FROM `ob_variable_definition`;

    SELECT COUNT(*)
    INTO material_type_count_after
    FROM `ob_material_type`;

    IF variable_definition_count_before <> variable_definition_count_after THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '合同变量字典数量发生变化，事务已回滚';
    END IF;

    IF material_type_count_before <> material_type_count_after THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '上传资料类型数量发生变化，事务已回滚';
    END IF;

    COMMIT;
    SET FOREIGN_KEY_CHECKS = old_foreign_key_checks;
END$$

DELIMITER ;

CALL `ob_reset_business_data`();
DROP PROCEDURE IF EXISTS `ob_reset_business_data`;

-- 执行结果校验：以下两张保留表应仍有数据。
SELECT 'ob_variable_definition' AS `PreservedTable`, COUNT(*) AS `RemainingRows`
FROM `ob_variable_definition`
UNION ALL
SELECT 'ob_material_type', COUNT(*)
FROM `ob_material_type`;

-- 其余 ob_ 业务表的 TABLE_ROWS 应为 0（InnoDB 下该值为估算值，仅供快速查看）。
SELECT `TABLE_NAME`, `TABLE_ROWS`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = DATABASE()
  AND `TABLE_NAME` LIKE 'ob\_%'
ORDER BY `TABLE_NAME`;