-- =============================================
-- 业务数据清理存储过程
-- 说明：

--   1. 仅清理【业务单据/运行数据】，保留【基础数据/配置/主数据】
--   2. 保留范围：BPM 配置、BI 大屏、GoView 大屏、字典/菜单/角色/用户、
--      物料/分类/单位/仓库/库位/车间/工作站/设备/工具/工序/工艺/质检方案/缺陷/指标、
--      编码规则、条码配置、安灯配置、班组排班、BOM/SOP/SIP、
--      CRM 客户/联系人/商机状态/合同配置/客户限制/公海配置、
--      ERP 供应商/结算账户、SRM 供应商主表/分类/招投标配置、
--      HRM 员工档案及考勤/薪资/社保/税率配置、AI 模型/工具/角色/知识库/工作流、
--      IM 敏感词/表情包、通知公告内容、系统权限用户体系等
--   3. 若存储过程已存在，请先执行 DROP PROCEDURE sp_clean_business_data; 再执行本脚本
-- =============================================

CREATE DEFINER=`root`@`%` PROCEDURE `sp_clean_business_data`()
BEGIN
    SET FOREIGN_KEY_CHECKS = 0;

    -- ============================================================
    -- 1. 系统运行数据（日志 / Token / 验证码 / 站内信 / 社交绑定）
    -- ============================================================
    TRUNCATE TABLE system_login_log;
    TRUNCATE TABLE system_operate_log;
    TRUNCATE TABLE system_notify_message;
    TRUNCATE TABLE system_oauth2_access_token;
    TRUNCATE TABLE system_oauth2_refresh_token;
    TRUNCATE TABLE system_oauth2_code;
    TRUNCATE TABLE system_oauth2_approve;
    TRUNCATE TABLE system_sms_code;
    TRUNCATE TABLE system_sms_log;
    TRUNCATE TABLE system_mail_log;
    TRUNCATE TABLE system_social_user;
    TRUNCATE TABLE system_social_user_bind;
    TRUNCATE TABLE infra_api_access_log;
    TRUNCATE TABLE infra_api_error_log;
    TRUNCATE TABLE infra_job_log;

    -- ============================================================
    -- 2. 文件与附件
    -- ============================================================
    TRUNCATE TABLE system_storage_attachment;
    TRUNCATE TABLE system_storage_blob;
    TRUNCATE TABLE storage_attachment;
    TRUNCATE TABLE storage_blob;
    TRUNCATE TABLE infra_file;
    TRUNCATE TABLE infra_file_content;

    -- ============================================================
    -- 3. BPM 流程实例与审批历史
    --    保留：流程定义/表单/分类/表达式/监听器/用户组/业务附件配置
    -- ============================================================
    TRUNCATE TABLE bpm_oa_leave;
    TRUNCATE TABLE bpm_process_instance_copy;
    TRUNCATE TABLE act_hi_procinst;
    TRUNCATE TABLE act_hi_taskinst;
    TRUNCATE TABLE act_hi_actinst;
    TRUNCATE TABLE act_hi_varinst;
    TRUNCATE TABLE act_hi_identitylink;
    TRUNCATE TABLE act_hi_comment;
    TRUNCATE TABLE act_hi_detail;
    TRUNCATE TABLE act_hi_attachment;
    TRUNCATE TABLE act_hi_entitylink;
    TRUNCATE TABLE act_hi_tsk_log;
    TRUNCATE TABLE act_ru_task;
    TRUNCATE TABLE act_ru_execution;
    TRUNCATE TABLE act_ru_actinst;
    TRUNCATE TABLE act_ru_variable;
    TRUNCATE TABLE act_ru_identitylink;
    TRUNCATE TABLE act_ru_job;
    TRUNCATE TABLE act_ru_timer_job;
    TRUNCATE TABLE act_ru_deadletter_job;
    TRUNCATE TABLE act_ru_suspended_job;
    TRUNCATE TABLE act_ru_external_job;
    TRUNCATE TABLE act_ru_history_job;
    TRUNCATE TABLE act_ru_event_subscr;
    TRUNCATE TABLE act_ru_entitylink;
    TRUNCATE TABLE act_evt_log;

    -- ============================================================
    -- 4. MES 生产模块 — 业务单据
    -- ============================================================
    TRUNCATE TABLE mes_pro_work_order_param_record_detail;
    TRUNCATE TABLE mes_pro_work_order_param_record;
    TRUNCATE TABLE mes_pro_work_order_process;
    TRUNCATE TABLE mes_pro_work_order_bom;
    TRUNCATE TABLE mes_pro_work_order;
    TRUNCATE TABLE mes_pro_task_issue;
    TRUNCATE TABLE mes_pro_task;
    TRUNCATE TABLE mes_pro_feedback;
    TRUNCATE TABLE mes_pro_mps_merge_item;
    TRUNCATE TABLE mes_pro_mps;
    TRUNCATE TABLE mes_pro_card_process;
    TRUNCATE TABLE mes_pro_card;
    TRUNCATE TABLE mes_pro_work_record_log;
    TRUNCATE TABLE mes_pro_work_record;
    TRUNCATE TABLE mes_pro_andon_record;

    -- ============================================================
    -- 5. MES 仓库模块 — 业务单据
    -- ============================================================
    TRUNCATE TABLE mes_wm_transaction;
    TRUNCATE TABLE mes_wm_stock_reserve;
    TRUNCATE TABLE mes_wm_material_stock;
    TRUNCATE TABLE mes_wm_sn;
    TRUNCATE TABLE mes_wm_batch;
    TRUNCATE TABLE mes_wm_barcode;
    TRUNCATE TABLE mes_wm_stock_taking_task_result;
    TRUNCATE TABLE mes_wm_stock_taking_task_line;
    TRUNCATE TABLE mes_wm_stock_taking_task;
    TRUNCATE TABLE mes_wm_stock_taking_plan_param;
    TRUNCATE TABLE mes_wm_stock_taking_plan;
    TRUNCATE TABLE mes_wm_package_line;
    TRUNCATE TABLE mes_wm_package;
    TRUNCATE TABLE mes_wm_item_receipt_detail;
    TRUNCATE TABLE mes_wm_item_receipt_line;
    TRUNCATE TABLE mes_wm_item_receipt;
    TRUNCATE TABLE mes_wm_item_consume_detail;
    TRUNCATE TABLE mes_wm_item_consume_line;
    TRUNCATE TABLE mes_wm_item_consume;
    TRUNCATE TABLE mes_wm_product_produce_detail;
    TRUNCATE TABLE mes_wm_product_produce_line;
    TRUNCATE TABLE mes_wm_product_produce;
    TRUNCATE TABLE mes_wm_product_issue_detail;
    TRUNCATE TABLE mes_wm_product_issue_line;
    TRUNCATE TABLE mes_wm_product_issue;
    TRUNCATE TABLE mes_wm_product_receipt_detail;
    TRUNCATE TABLE mes_wm_product_receipt_line;
    TRUNCATE TABLE mes_wm_product_receipt;
    TRUNCATE TABLE mes_wm_product_sales_detail;
    TRUNCATE TABLE mes_wm_product_sales_line;
    TRUNCATE TABLE mes_wm_product_sales;
    TRUNCATE TABLE mes_wm_misc_receipt_detail;
    TRUNCATE TABLE mes_wm_misc_receipt_line;
    TRUNCATE TABLE mes_wm_misc_receipt;
    TRUNCATE TABLE mes_wm_misc_issue_detail;
    TRUNCATE TABLE mes_wm_misc_issue_line;
    TRUNCATE TABLE mes_wm_misc_issue;
    TRUNCATE TABLE mes_wm_outsource_receipt_detail;
    TRUNCATE TABLE mes_wm_outsource_receipt_line;
    TRUNCATE TABLE mes_wm_outsource_receipt;
    TRUNCATE TABLE mes_wm_outsource_issue_detail;
    TRUNCATE TABLE mes_wm_outsource_issue_line;
    TRUNCATE TABLE mes_wm_outsource_issue;
    TRUNCATE TABLE mes_wm_return_vendor_detail;
    TRUNCATE TABLE mes_wm_return_vendor_line;
    TRUNCATE TABLE mes_wm_return_vendor;
    TRUNCATE TABLE mes_wm_return_issue_detail;
    TRUNCATE TABLE mes_wm_return_issue_line;
    TRUNCATE TABLE mes_wm_return_issue;
    TRUNCATE TABLE mes_wm_return_sales_detail;
    TRUNCATE TABLE mes_wm_return_sales_line;
    TRUNCATE TABLE mes_wm_return_sales;
    TRUNCATE TABLE mes_wm_sales_notice_line;
    TRUNCATE TABLE mes_wm_sales_notice;
    TRUNCATE TABLE mes_wm_transfer_detail;
    TRUNCATE TABLE mes_wm_transfer_line;
    TRUNCATE TABLE mes_wm_transfer;
    TRUNCATE TABLE mes_wm_arrival_notice_line;
    TRUNCATE TABLE mes_wm_arrival_notice;

    -- ============================================================
    -- 6. MES 质检模块 — 业务单据
    -- ============================================================
    TRUNCATE TABLE mes_qc_indicator_result_detail;
    TRUNCATE TABLE mes_qc_indicator_result;
    TRUNCATE TABLE mes_qc_defect_record;
    TRUNCATE TABLE mes_qc_ncr;
    TRUNCATE TABLE mes_qc_iqc_line;
    TRUNCATE TABLE mes_qc_iqc;
    TRUNCATE TABLE mes_qc_ipqc_line;
    TRUNCATE TABLE mes_qc_ipqc;
    TRUNCATE TABLE mes_qc_oqc_line;
    TRUNCATE TABLE mes_qc_oqc;
    TRUNCATE TABLE mes_qc_rqc_line;
    TRUNCATE TABLE mes_qc_rqc;

    -- ============================================================
    -- 7. MES 设备模块 — 点检/保养/维修/计量检测记录
    -- ============================================================
    TRUNCATE TABLE mes_dv_check_record_line;
    TRUNCATE TABLE mes_dv_check_record;
    TRUNCATE TABLE mes_dv_mainten_record_line;
    TRUNCATE TABLE mes_dv_mainten_record;
    TRUNCATE TABLE mes_dv_repair_line;
    TRUNCATE TABLE mes_dv_repair;
    TRUNCATE TABLE mes_dv_metering_check;

    -- ============================================================
    -- 8. MES 工艺/设计模块 — 业务单据（编码生成记录为流水数据）
    -- ============================================================
    TRUNCATE TABLE mes_pd_archive;
    TRUNCATE TABLE mes_pd_document_audit;
    TRUNCATE TABLE mes_pd_document;
    TRUNCATE TABLE mes_pd_project;
    TRUNCATE TABLE mes_md_auto_code_record;

    -- ============================================================
    -- 9. CRM 模块 — 业务单据
    --    保留：crm_customer / crm_contact / crm_business_status(_type)
    --          / crm_contract_config / crm_customer_limit_config / crm_customer_pool_config
    -- ============================================================
    TRUNCATE TABLE crm_receivable_plan;
    TRUNCATE TABLE crm_receivable;
    TRUNCATE TABLE crm_contract_product;
    TRUNCATE TABLE crm_contract;
    TRUNCATE TABLE crm_sale_quotation_product;
    TRUNCATE TABLE crm_sale_quotation;
    TRUNCATE TABLE crm_business_product;
    TRUNCATE TABLE crm_contact_business;
    TRUNCATE TABLE crm_business;
    TRUNCATE TABLE crm_clue;
    TRUNCATE TABLE crm_follow_up_record;
    TRUNCATE TABLE crm_invoice;
    TRUNCATE TABLE crm_sales_target;
    TRUNCATE TABLE crm_permission;

    -- ============================================================
    -- 10. 售后模块 — 业务单据
    -- ============================================================
    TRUNCATE TABLE after_sale_return_item;
    TRUNCATE TABLE after_sale_return;
    TRUNCATE TABLE after_sale_ticket_item;
    TRUNCATE TABLE after_sale_ticket;
    TRUNCATE TABLE after_sale_repair;
    TRUNCATE TABLE after_sale_permission;

    -- ============================================================
    -- 11. ERP 模块 — 业务单据
    --    保留：erp_supplier / erp_account
    -- ============================================================
    TRUNCATE TABLE erp_finance_payment_item;
    TRUNCATE TABLE erp_finance_payment;
    TRUNCATE TABLE erp_finance_receipt_item;
    TRUNCATE TABLE erp_finance_receipt;
    TRUNCATE TABLE erp_purchase_order_items;
    TRUNCATE TABLE erp_purchase_order;
    TRUNCATE TABLE erp_purchase_request_items;
    TRUNCATE TABLE erp_purchase_request;
    TRUNCATE TABLE erp_sale_order_items;
    TRUNCATE TABLE erp_sale_order;
    TRUNCATE TABLE erp_purchase_invoice;

    -- ============================================================
    -- 12. SRM 模块 — 业务单据
    --    保留：srm_supplier / srm_supplier_contact / srm_supplier_category
    --          / srm_tender_config
    -- ============================================================
    TRUNCATE TABLE srm_bid_award;
    TRUNCATE TABLE srm_bid_evaluation;
    TRUNCATE TABLE srm_bid_open;
    TRUNCATE TABLE srm_tender_bid;
    TRUNCATE TABLE srm_tender_material;
    TRUNCATE TABLE srm_tender_project;
    TRUNCATE TABLE srm_delivery_plan;
    TRUNCATE TABLE srm_purchase_collaboration;
    TRUNCATE TABLE srm_supplier_quote;
    TRUNCATE TABLE srm_supplier_evaluation;
    TRUNCATE TABLE srm_supplier_certificate;
    TRUNCATE TABLE srm_supplier_apply;

    -- ============================================================
    -- 13. HRM 模块 — 业务单据
    --    保留：hrm_employee 及档案子表（教育/工作经历/紧急联系人/
    --          社保公积金/薪酬档案/合同）、考勤节假日/规则、请假类型配置、
    --          薪酬结构、社保公积金方案、个税税率配置
    -- ============================================================
    TRUNCATE TABLE hrm_salary_payment_detail;
    TRUNCATE TABLE hrm_salary_payment;
    TRUNCATE TABLE hrm_salary_calculation;
    TRUNCATE TABLE hrm_attendance_summary;
    TRUNCATE TABLE hrm_attendance_record;
    TRUNCATE TABLE hrm_attendance_exception;
    TRUNCATE TABLE hrm_leave_application;
    TRUNCATE TABLE hrm_resignation_application;
    TRUNCATE TABLE hrm_transfer_application;

    -- ============================================================
    -- 14. AI 模块 — 对话与生成内容
    --    保留：ai_api_key / ai_model / ai_tool / ai_chat_role
    --          / ai_knowledge(_document/_segment) / ai_workflow
    -- ============================================================
    TRUNCATE TABLE ai_chat_message;
    TRUNCATE TABLE ai_chat_conversation;
    TRUNCATE TABLE ai_image;
    TRUNCATE TABLE ai_mind_map;
    TRUNCATE TABLE ai_music;
    TRUNCATE TABLE ai_write;

    -- ============================================================
    -- 15. IM 模块 — 会话与消息
    --    保留：im_sensitive_word / im_face_pack / im_face_pack_item
    -- ============================================================
    TRUNCATE TABLE im_group_message_receipt;
    TRUNCATE TABLE im_group_message;
    TRUNCATE TABLE im_private_message;
    TRUNCATE TABLE im_message_file;
    TRUNCATE TABLE im_group_request;
    TRUNCATE TABLE im_group_member;
    TRUNCATE TABLE im_group;
    TRUNCATE TABLE im_friend_request;
    TRUNCATE TABLE im_friend;
    TRUNCATE TABLE im_conversation_read;
    TRUNCATE TABLE im_conversation;
    TRUNCATE TABLE im_channel_message;
    TRUNCATE TABLE im_channel_material;
    TRUNCATE TABLE im_channel;
    TRUNCATE TABLE im_rtc_participant;
    TRUNCATE TABLE im_rtc_call;
    TRUNCATE TABLE im_user_online;
    TRUNCATE TABLE im_face_user_item;

    -- ============================================================
    -- 16. OA 已读/指定人关联（保留通知公告内容 oa_notice / system_notice）
    -- ============================================================
    TRUNCATE TABLE oa_notice_read;
    TRUNCATE TABLE oa_notice_user;

    -- ============================================================
    -- 17. 示例数据
    -- ============================================================
    TRUNCATE TABLE yudao_demo01_contact;
    TRUNCATE TABLE yudao_demo02_category;
    TRUNCATE TABLE yudao_demo03_course;
    TRUNCATE TABLE yudao_demo03_grade;
    TRUNCATE TABLE yudao_demo03_student;

    SET FOREIGN_KEY_CHECKS = 1;

    SELECT '业务数据清理完成' AS result;
END