-- ============================================================
-- 业务数据清理存储过程（2026-10-23 版）
--
-- 清理范围：基础数据、采购、销售、设备、仓储、工艺设计、产品设计、
--           生产、质量、协同审核记录、能耗、售后、系统日志/Token/附件
-- 保留：系统用户/角色/菜单/字典、基础设施配置、编码规则、
--       流程配置（分类/表单/分组/监听器/表达式/定义扩展/业务附件/免审批配置）、
--       流程定义与引擎元数据（act_re_*/act_ge_*/act_id_*/flw_event_*）
-- 注意：TRUNCATE 不可回滚，执行前务必备份
--
-- 用法：
--   CALL sp_clean_business_data_check();  -- 预检：只统计各表行数，不删数据
--   CALL sp_clean_business_data();        -- 执行：真正清空
--
-- 优化点：
--   1. 目标表集中在临时表维护，新增/改名时只改清单，避免大段重复 SQL
--   2. 自动跳过不存在的表（兼容不同环境的库结构漂移）
--   3. 预检模式输出每个分区待清理行数，便于执行前评估
--   4. 统一关闭/恢复 FOREIGN_KEY_CHECKS，杜绝外键阻塞
-- ============================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_clean_business_data_core$$
DROP PROCEDURE IF EXISTS sp_clean_business_data$$
DROP PROCEDURE IF EXISTS sp_clean_business_data_check$$

-- ------------------------------------------------------------
-- 核心过程：p_dry_run = 0 执行；1 仅预检
-- ------------------------------------------------------------
CREATE PROCEDURE sp_clean_business_data_core(IN p_dry_run TINYINT)
BEGIN
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_section VARCHAR(64);
    DECLARE v_table VARCHAR(64);
    DECLARE v_status VARCHAR(16);
    DECLARE v_exists INT DEFAULT 0;
    DECLARE v_rows BIGINT DEFAULT NULL;

    DECLARE cur CURSOR FOR
        SELECT section, table_name FROM tmp_clean_targets ORDER BY section, table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 中途出错时兜底：恢复外键检查并清理临时表
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET FOREIGN_KEY_CHECKS = 1;
        DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
        DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;
        RESIGNAL;
    END;

    DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
    DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;

    CREATE TEMPORARY TABLE tmp_clean_targets (
        table_name VARCHAR(64) NOT NULL PRIMARY KEY,
        section VARCHAR(64) NOT NULL DEFAULT ''
    ) ENGINE = MEMORY;

    CREATE TEMPORARY TABLE tmp_clean_result (
        section VARCHAR(64) NOT NULL,
        table_name VARCHAR(64) NOT NULL,
        status VARCHAR(16) NOT NULL,
        rows_before BIGINT NULL
    ) ENGINE = MEMORY;

    INSERT INTO tmp_clean_targets (table_name) VALUES
    -- 01 系统日志 / Token / 消息记录
    ('system_login_log'),
    ('system_operate_log'),
    ('system_notify_message'),
    ('system_oauth2_access_token'),
    ('system_oauth2_refresh_token'),
    ('system_oauth2_code'),
    ('system_oauth2_approve'),
    ('system_sms_code'),
    ('system_sms_log'),
    ('system_mail_log'),
    ('system_social_user'),
    ('system_social_user_bind'),
    ('infra_api_access_log'),
    ('infra_api_error_log'),
    ('infra_job_log'),
    -- 02 系统附件
    ('system_storage_attachment'),
    ('system_storage_blob'),
    ('storage_attachment'),
    ('storage_blob'),
    ('infra_file'),
    ('infra_file_content'),
    -- 03 基础数据 - MDM 主数据
    ('mdm_item'),
    ('mdm_item_sku'),
    ('mdm_item_model'),
    ('mdm_item_batch_config'),
    ('mdm_item_migration_map'),
    ('mdm_item_category'),
    ('mdm_brand'),
    ('mdm_unit_measure'),
    ('mdm_warehouse'),
    -- 04 基础数据 - MES 物料 / BOM
    ('mes_md_item'),
    ('mes_md_item_type'),
    ('mes_md_item_batch_config'),
    ('mes_md_unit_measure'),
    ('mes_md_vendor'),
    ('mes_md_product_bom'),
    ('mes_md_product_sop'),
    ('mes_md_product_sip'),
    ('mes_md_auto_code_record'),
    -- 05 基础数据 - MES 车间 / 产线 / 班组排班
    ('mes_md_workshop'),
    ('mes_md_workstation'),
    ('mes_md_workstation_machine'),
    ('mes_md_workstation_tool'),
    ('mes_md_workstation_worker'),
    ('mes_pro_line'),
    ('mes_cal_holiday'),
    ('mes_cal_team'),
    ('mes_cal_team_member'),
    ('mes_cal_team_shift'),
    ('mes_cal_plan'),
    ('mes_cal_plan_shift'),
    ('mes_cal_plan_team'),
    -- 06 基础数据 - MES 设备 / 工具台账
    ('mes_dv_machinery'),
    ('mes_dv_machinery_type'),
    ('mes_dv_subject'),
    ('mes_dv_check_plan'),
    ('mes_dv_check_plan_machinery'),
    ('mes_dv_check_plan_subject'),
    ('mes_dv_metering'),
    ('mes_tm_tool'),
    ('mes_tm_tool_type'),
    -- 07 基础数据 - MES 工艺 / 参数配置
    ('mes_pro_route'),
    ('mes_pro_route_process'),
    ('mes_pro_route_product'),
    ('mes_pro_route_product_bom'),
    ('mes_pro_process'),
    ('mes_pro_process_content'),
    ('mes_pro_process_param'),
    ('mes_pd_process_param'),
    ('mes_pd_process_param_template'),
    ('mes_pd_process_param_template_detail'),
    ('mes_pro_andon_config'),
    -- 08 基础数据 - MES 质检基础（方案 / 缺陷 / 指标）
    ('mes_qc_template'),
    ('mes_qc_template_indicator'),
    ('mes_qc_template_item'),
    ('mes_qc_defect'),
    ('mes_qc_indicator'),
    -- 09 基础数据 - MES 仓库档案
    ('mes_wm_warehouse'),
    ('mes_wm_warehouse_area'),
    ('mes_wm_warehouse_location'),
    ('mes_wm_barcode_config'),
    -- 10 采购 - ERP
    ('erp_purchase_request'),
    ('erp_purchase_request_items'),
    ('erp_purchase_order'),
    ('erp_purchase_order_items'),
    ('erp_purchase_invoice'),
    ('erp_finance_payment'),
    ('erp_finance_payment_item'),
    -- 11 采购 - SRM
    ('srm_tender_project'),
    ('srm_tender_material'),
    ('srm_tender_bid'),
    ('srm_bid_open'),
    ('srm_bid_evaluation'),
    ('srm_bid_award'),
    ('srm_supplier_apply'),
    ('srm_supplier_quote'),
    ('srm_supplier_evaluation'),
    ('srm_supplier_certificate'),
    ('srm_delivery_plan'),
    ('srm_purchase_collaboration'),
    -- 12 销售 - ERP
    ('erp_sale_order'),
    ('erp_sale_order_items'),
    ('erp_finance_receipt'),
    ('erp_finance_receipt_item'),
    -- 13 销售 - CRM（保留客户/联系人）
    ('crm_clue'),
    ('crm_business'),
    ('crm_business_product'),
    ('crm_contact_business'),
    ('crm_follow_up_record'),
    ('crm_sale_quotation'),
    ('crm_sale_quotation_product'),
    ('crm_contract'),
    ('crm_contract_product'),
    ('crm_receivable'),
    ('crm_receivable_plan'),
    ('crm_invoice'),
    ('crm_sales_target'),
    ('crm_permission'),
    -- 14 设备 - 点检 / 保养 / 维修 / 计量记录
    ('mes_dv_check_record'),
    ('mes_dv_check_record_line'),
    ('mes_dv_mainten_record'),
    ('mes_dv_mainten_record_line'),
    ('mes_dv_repair'),
    ('mes_dv_repair_line'),
    ('mes_dv_metering_check'),
    -- 15 仓储 - 业务单据
    ('mes_wm_transaction'),
    ('mes_wm_stock_reserve'),
    ('mes_wm_material_stock'),
    ('mes_wm_sn'),
    ('mes_wm_batch'),
    ('mes_wm_barcode'),
    ('mes_wm_stock_taking_task_result'),
    ('mes_wm_stock_taking_task_line'),
    ('mes_wm_stock_taking_task'),
    ('mes_wm_stock_taking_plan_param'),
    ('mes_wm_stock_taking_plan'),
    ('mes_wm_package_line'),
    ('mes_wm_package'),
    ('mes_wm_item_receipt_detail'),
    ('mes_wm_item_receipt_line'),
    ('mes_wm_item_receipt'),
    ('mes_wm_item_consume_detail'),
    ('mes_wm_item_consume_line'),
    ('mes_wm_item_consume'),
    ('mes_wm_product_produce_detail'),
    ('mes_wm_product_produce_line'),
    ('mes_wm_product_produce'),
    ('mes_wm_product_issue_detail'),
    ('mes_wm_product_issue_line'),
    ('mes_wm_product_issue'),
    ('mes_wm_product_receipt_detail'),
    ('mes_wm_product_receipt_line'),
    ('mes_wm_product_receipt'),
    ('mes_wm_product_sales_detail'),
    ('mes_wm_product_sales_line'),
    ('mes_wm_product_sales'),
    ('mes_wm_misc_receipt_detail'),
    ('mes_wm_misc_receipt_line'),
    ('mes_wm_misc_receipt'),
    ('mes_wm_misc_issue_detail'),
    ('mes_wm_misc_issue_line'),
    ('mes_wm_misc_issue'),
    ('mes_wm_outsource_receipt_detail'),
    ('mes_wm_outsource_receipt_line'),
    ('mes_wm_outsource_receipt'),
    ('mes_wm_outsource_issue_detail'),
    ('mes_wm_outsource_issue_line'),
    ('mes_wm_outsource_issue'),
    ('mes_wm_return_vendor_detail'),
    ('mes_wm_return_vendor_line'),
    ('mes_wm_return_vendor'),
    ('mes_wm_return_issue_detail'),
    ('mes_wm_return_issue_line'),
    ('mes_wm_return_issue'),
    ('mes_wm_return_sales_detail'),
    ('mes_wm_return_sales_line'),
    ('mes_wm_return_sales'),
    ('mes_wm_sales_notice_line'),
    ('mes_wm_sales_notice'),
    ('mes_wm_transfer_detail'),
    ('mes_wm_transfer_line'),
    ('mes_wm_transfer'),
    ('mes_wm_arrival_notice_line'),
    ('mes_wm_arrival_notice'),
    -- 16 工艺设计 / 产品设计 - 业务单据
    ('mes_pd_project'),
    ('mes_pd_document'),
    ('mes_pd_document_audit'),
    ('mes_pd_archive'),
    -- 17 生产 - 业务单据
    ('mes_pro_work_order_param_record_detail'),
    ('mes_pro_work_order_param_record'),
    ('mes_pro_work_order_process'),
    ('mes_pro_work_order_bom'),
    ('mes_pro_work_order'),
    ('mes_pro_task_issue'),
    ('mes_pro_task_assignee'),
    ('mes_pro_task_pause_record'),
    ('mes_pro_task'),
    ('mes_pro_feedback'),
    ('mes_pro_mps_merge_item'),
    ('mes_pro_mps'),
    ('mes_pro_card_process'),
    ('mes_pro_card'),
    ('mes_pro_work_record_log'),
    ('mes_pro_work_record'),
    ('mes_pro_andon_record'),
    ('mes_pro_batch_worker'),
    ('mes_pro_batch'),
    ('mes_pro_pallet'),
    ('mes_pro_bag_code_replace'),
    ('mes_pro_bag_code'),
    -- 18 质量 - 检验单据 / 报告实例
    ('mes_qc_indicator_result_detail'),
    ('mes_qc_indicator_result'),
    ('mes_qc_defect_record'),
    ('mes_qc_ncr'),
    ('mes_qc_iqc_line'),
    ('mes_qc_iqc'),
    ('mes_qc_ipqc_line'),
    ('mes_qc_ipqc'),
    ('mes_qc_oqc_line'),
    ('mes_qc_oqc'),
    ('mes_qc_rqc_line'),
    ('mes_qc_rqc'),
    ('qc_report_instance'),
    ('qc_report_render_record'),
    -- 19 协同 - 审核记录与运行态（流程配置/定义保留）
    ('bpm_oa_leave'),
    ('bpm_process_instance_copy'),
    ('act_hi_procinst'),
    ('act_hi_taskinst'),
    ('act_hi_actinst'),
    ('act_hi_varinst'),
    ('act_hi_identitylink'),
    ('act_hi_comment'),
    ('act_hi_detail'),
    ('act_hi_attachment'),
    ('act_hi_entitylink'),
    ('act_hi_tsk_log'),
    ('act_ru_task'),
    ('act_ru_execution'),
    ('act_ru_actinst'),
    ('act_ru_variable'),
    ('act_ru_identitylink'),
    ('act_ru_job'),
    ('act_ru_timer_job'),
    ('act_ru_deadletter_job'),
    ('act_ru_suspended_job'),
    ('act_ru_external_job'),
    ('act_ru_history_job'),
    ('act_ru_event_subscr'),
    ('act_ru_entitylink'),
    ('act_evt_log'),
    ('flw_ru_batch'),
    ('flw_ru_batch_part'),
    -- 20 能耗 - 采集记录与告警日志（告警规则保留）
    ('mes_energy_record'),
    ('mes_energy_alert_log'),
    -- 21 售后
    ('after_sale_return_item'),
    ('after_sale_return'),
    ('after_sale_ticket_item'),
    ('after_sale_ticket'),
    ('after_sale_repair'),
    ('after_sale_permission'),
    ('after_sale_problem'),
    ('after_sale_visit'),
    -- 22 智能体导入会话（临时数据）
    ('system_agent_import_session'),
    ('agent_import_command'),
    ('agent_import_command_row'),
    ('mdm_agent_import_command'),
    ('mdm_agent_import_command_row');

    -- 补充分区名称（顺序敏感：先匹配特殊表，再按前缀兜底）
    UPDATE tmp_clean_targets SET section = CASE
        WHEN table_name LIKE 'agent_import%' OR table_name LIKE 'mdm_agent_import%'
             OR table_name = 'system_agent_import_session'
            THEN '22 智能体导入会话'
        WHEN table_name IN ('system_storage_attachment','system_storage_blob','storage_attachment',
                            'storage_blob','infra_file','infra_file_content')
            THEN '02 系统附件'
        WHEN table_name LIKE 'system_%'
             OR table_name IN ('infra_api_access_log','infra_api_error_log','infra_job_log')
            THEN '01 系统日志与令牌'
        WHEN table_name LIKE 'mdm_%'
            THEN '03 基础数据-MDM'
        WHEN table_name IN ('mes_md_workshop','mes_md_workstation','mes_md_workstation_machine',
                            'mes_md_workstation_tool','mes_md_workstation_worker','mes_pro_line')
             OR table_name LIKE 'mes_cal_%'
            THEN '05 基础数据-MES车间班组'
        WHEN table_name LIKE 'mes_md_%'
            THEN '04 基础数据-MES物料BOM'
        WHEN table_name IN ('mes_dv_check_record','mes_dv_check_record_line','mes_dv_mainten_record',
                            'mes_dv_mainten_record_line','mes_dv_repair','mes_dv_repair_line','mes_dv_metering_check')
            THEN '14 设备-业务记录'
        WHEN table_name LIKE 'mes_dv_%' OR table_name LIKE 'mes_tm_%'
            THEN '06 基础数据-MES设备工具台账'
        WHEN table_name LIKE 'mes_pro_route%' OR table_name IN ('mes_pro_process','mes_pro_process_content',
                            'mes_pro_process_param','mes_pd_process_param','mes_pd_process_param_template',
                            'mes_pd_process_param_template_detail','mes_pro_andon_config')
            THEN '07 基础数据-MES工艺参数'
        WHEN table_name IN ('mes_qc_template','mes_qc_template_indicator','mes_qc_template_item',
                            'mes_qc_defect','mes_qc_indicator')
            THEN '08 基础数据-MES质检基础'
        WHEN table_name IN ('mes_wm_warehouse','mes_wm_warehouse_area','mes_wm_warehouse_location','mes_wm_barcode_config')
            THEN '09 基础数据-MES仓库档案'
        WHEN table_name LIKE 'erp_purchase%' OR table_name LIKE 'erp_finance_payment%'
            THEN '10 采购-ERP'
        WHEN table_name LIKE 'srm_%'
            THEN '11 采购-SRM'
        WHEN table_name LIKE 'erp_sale%' OR table_name LIKE 'erp_finance_receipt%'
            THEN '12 销售-ERP'
        WHEN table_name LIKE 'crm_%'
            THEN '13 销售-CRM'
        WHEN table_name LIKE 'mes_wm_%'
            THEN '15 仓储-业务单据'
        WHEN table_name LIKE 'mes_pd_%'
            THEN '16 工艺设计产品设计'
        WHEN table_name LIKE 'mes_pro_%'
            THEN '17 生产'
        WHEN table_name LIKE 'mes_qc_%' OR table_name LIKE 'qc_report_%'
            THEN '18 质量'
        WHEN table_name LIKE 'act_%' OR table_name LIKE 'flw_%' OR table_name LIKE 'bpm_%'
            THEN '19 协同-审核记录'
        WHEN table_name LIKE 'mes_energy%'
            THEN '20 能耗'
        WHEN table_name LIKE 'after_sale%'
            THEN '21 售后'
        ELSE '99 未分类'
    END;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_section, v_table;
        IF v_done = 1 THEN
            LEAVE read_loop;
        END IF;

        SELECT COUNT(*) INTO v_exists
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = v_table AND TABLE_TYPE = 'BASE TABLE';

        IF v_exists = 0 THEN
            SET v_status = 'SKIP';
            SET v_rows = NULL;
        ELSEIF p_dry_run = 1 THEN
            SET v_status = 'DRY-RUN';
            SET @row_count = 0;
            SET @sql = CONCAT('SELECT COUNT(*) INTO @row_count FROM `', v_table, '`');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET v_rows = @row_count;
        ELSE
            SET v_status = 'TRUNCATED';
            SET v_rows = NULL;
            SET @sql = CONCAT('TRUNCATE TABLE `', v_table, '`');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        INSERT INTO tmp_clean_result (section, table_name, status, rows_before)
        VALUES (v_section, v_table, v_status, v_rows);
    END LOOP;
    CLOSE cur;

    SET FOREIGN_KEY_CHECKS = 1;

    -- 分区汇总
    SELECT section AS section_name,
           SUM(status = 'TRUNCATED') AS truncated_tables,
           SUM(status = 'DRY-RUN')   AS planned_tables,
           SUM(status = 'SKIP')      AS skipped_tables,
           IFNULL(SUM(rows_before), 0) AS rows_before
    FROM tmp_clean_result
    GROUP BY section
    ORDER BY section;

    -- 不存在而被跳过的表
    SELECT section AS section_name, table_name
    FROM tmp_clean_result
    WHERE status = 'SKIP'
    ORDER BY section, table_name;

    SELECT IF(p_dry_run = 1, '预检完成：未删除任何数据', '业务数据清理完成') AS result;

    DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
    DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;
END$$

-- ------------------------------------------------------------
-- 对外入口
-- ------------------------------------------------------------
CREATE PROCEDURE sp_clean_business_data()
BEGIN
    CALL sp_clean_business_data_core(0);
END$$

CREATE PROCEDURE sp_clean_business_data_check()
BEGIN
    CALL sp_clean_business_data_core(1);
END$$

DELIMITER ;

-- ============================================================
-- 保留清单（不会被清理）
-- 【系统】system_users/system_dept/system_post/system_role/system_role_menu/
--         system_menu/system_dict_type/system_dict_data/system_tenant*/
--         system_user_post/system_user_role/system_oauth2_client/
--         system_notice/system_notify_template/system_mail_*/system_sms_template/
--         system_social_client/system_company_config
-- 【基础设施】infra_config/infra_job/infra_codegen_*/infra_data_source_config/infra_file_config
-- 【编码规则】mes_md_auto_code_rule/mes_md_auto_code_part
-- 【流程配置】bpm_category/bpm_form/bpm_user_group/bpm_process_listener/
--            bpm_process_expression/bpm_process_definition_info/
--            bpm_business_attach/bpm_process_auto_approve_config
-- 【流程定义】act_re_deployment/act_re_model/act_re_procdef/act_ge_bytearray/
--            act_ge_property/act_id_*/act_procdef_info/flw_event_*/flw_channel_definition
-- 【主数据】crm_customer/crm_contact/erp_supplier/erp_account/
--          srm_supplier/srm_supplier_contact/srm_supplier_category/srm_tender_config
-- 【质量平台配置】qc_report_template/qc_report_template_version/qc_report_rule/
--                qc_report_data_source
-- 【能耗规则】mes_energy_alert_rule
-- 【业务未涉及模块】HRM/AI/IM/OA 公告数据
-- ============================================================

-- 执行：
-- CALL sp_clean_business_data_check();
-- CALL sp_clean_business_data();