ylrz_saas_his_scrm/sql/cleanup_orphaned_tables.sql

-- ============================================================================
-- ylrz_saas 数据库清理 SQL
-- 生成时间: 2026-05-21
-- 分析依据: 交叉比对 mapper XML 引用表(713张) vs 数据库实际表(865张)
-- 孤儿表数: 288张 (在数据库中但无任何代码引用)
-- 
-- 分类说明:
--   类别A: 明显垃圾表 (copy/bak/test后缀) - 13张
--   类别B: 系统框架表 (Quartz/Liquibase/CodeGen) - 17张 [KEEP - 不删]
--   类别C: 分表/分区表 (fs_user_operation_log_*, qw_msg_*) - 20张 [需DBA确认]
--   类别D: 孤儿业务表 (无Mapper引用) - 238张
-- 
-- 当前文件包含: 类别A (13张) + 类别D (238张) = 251张
-- 安全删除，这些表在代码中无任何引用
-- ============================================================================

-- ==============================
-- 批次1: 类别A - 明显垃圾表 (13张)
-- 这些是明显的 copy/bak/test 临时表
-- ==============================

DROP TABLE IF EXISTS `ab_test`;
DROP TABLE IF EXISTS `company_menu_copy8.28`;
DROP TABLE IF EXISTS `company_tag_copy1`;
DROP TABLE IF EXISTS `fs_course_watch_log_bak`;
DROP TABLE IF EXISTS `fs_user_course_period_copy1`;
DROP TABLE IF EXISTS `fs_user_course_period_days_copy1`;
DROP TABLE IF EXISTS `fs_user_information_collection_copy1`;
DROP TABLE IF EXISTS `qw_company_copy1`;
DROP TABLE IF EXISTS `qw_push_count_copy1`;
DROP TABLE IF EXISTS `qw_restriction_push_record_copy1`;
DROP TABLE IF EXISTS `qw_session_copy1`;
DROP TABLE IF EXISTS `sys_dict_type_copy1`;
DROP TABLE IF EXISTS `temp_danmu`;

-- ==============================
-- 批次2: 类别D - 孤儿业务表 (238张)
-- 按前缀分组，这些表在代码中无任何 MyBatis Mapper 引用
-- ==============================

-- account_* (1)
DROP TABLE IF EXISTS `account_balance_log`;

-- ad_* (1)
DROP TABLE IF EXISTS `ad_html_url`;

-- adv_* (17) - 旧广告投放系统
DROP TABLE IF EXISTS `adv_advertiser`;
DROP TABLE IF EXISTS `adv_api_call_log`;
DROP TABLE IF EXISTS `adv_callback_account`;
DROP TABLE IF EXISTS `adv_callback_log`;
DROP TABLE IF EXISTS `adv_channel`;
DROP TABLE IF EXISTS `adv_conversion_log`;
DROP TABLE IF EXISTS `adv_conversion_target`;
DROP TABLE IF EXISTS `adv_domain_url`;
DROP TABLE IF EXISTS `adv_event_type`;
DROP TABLE IF EXISTS `adv_landing_page_template`;
DROP TABLE IF EXISTS `adv_lead`;
DROP TABLE IF EXISTS `adv_mini_config`;
DROP TABLE IF EXISTS `adv_project`;
DROP TABLE IF EXISTS `adv_promotion_account`;
DROP TABLE IF EXISTS `adv_site`;
DROP TABLE IF EXISTS `adv_site_statistics`;
DROP TABLE IF EXISTS `adv_tracking_link`;

-- agent_* (14) - 旧代理系统
DROP TABLE IF EXISTS `agent`;
DROP TABLE IF EXISTS `agent_commission_ratio`;
DROP TABLE IF EXISTS `agent_commission_record`;
DROP TABLE IF EXISTS `agent_income_summary`;
DROP TABLE IF EXISTS `agent_level_config`;
DROP TABLE IF EXISTS `agent_level_log`;
DROP TABLE IF EXISTS `agent_prepaid_balance`;
DROP TABLE IF EXISTS `agent_prepaid_recharge`;
DROP TABLE IF EXISTS `agent_promote_record`;
DROP TABLE IF EXISTS `agent_quota_log`;
DROP TABLE IF EXISTS `agent_resource_auth`;
DROP TABLE IF EXISTS `agent_settlement`;
DROP TABLE IF EXISTS `agent_subsidy_record`;
DROP TABLE IF EXISTS `agent_user`;

-- ai_* (5) - 旧AI系统
DROP TABLE IF EXISTS `ai_chat_log`;
DROP TABLE IF EXISTS `ai_chat_tag_extract_log`;
DROP TABLE IF EXISTS `ai_prompt`;
DROP TABLE IF EXISTS `ai_sensitive_word`;
DROP TABLE IF EXISTS `ai_transfer_rule`;

-- aiob_* (3) - 百度AI外呼
DROP TABLE IF EXISTS `aiob_baidu_call_api`;
DROP TABLE IF EXISTS `aiob_baidu_encryption`;
DROP TABLE IF EXISTS `aiob_baidu_task`;

-- balance_* (1)
DROP TABLE IF EXISTS `balance_rollback_error`;

-- billing_* (1)
DROP TABLE IF EXISTS `billing_statement_item`;

-- chronic_* (1)
DROP TABLE IF EXISTS `chronic_follow_plan`;

-- cid_* (2)
DROP TABLE IF EXISTS `cid_ipad_return_address`;
DROP TABLE IF EXISTS `cid_ipad_return_message`;

-- commission_* (2)
DROP TABLE IF EXISTS `commission_config`;
DROP TABLE IF EXISTS `commission_settle_rule`;

-- company_* (16) - 旧公司模块表
DROP TABLE IF EXISTS `company_bill`;
DROP TABLE IF EXISTS `company_course_redpacket`;
DROP TABLE IF EXISTS `company_external_api_call_log`;
DROP TABLE IF EXISTS `company_external_api_config`;
DROP TABLE IF EXISTS `company_extract`;
DROP TABLE IF EXISTS `company_fs_user`;
DROP TABLE IF EXISTS `company_link`;
DROP TABLE IF EXISTS `company_lobster_tag_user_rel`;
DROP TABLE IF EXISTS `company_recharge_order`;
DROP TABLE IF EXISTS `company_red_package_logs`;
DROP TABLE IF EXISTS `company_red_packet_balance_deduction_record`;
DROP TABLE IF EXISTS `company_tag_group`;
DROP TABLE IF EXISTS `company_traffic_record`;
DROP TABLE IF EXISTS `company_traffic_record_log`;
DROP TABLE IF EXISTS `company_voice_api_tiantian`;
DROP TABLE IF EXISTS `company_voice_robotic_call_log`;

-- crm_* (1)
DROP TABLE IF EXISTS `crm_customer_call_log`;

-- customer_* (5) - 旧客户管理
DROP TABLE IF EXISTS `customer_auto_tag`;
DROP TABLE IF EXISTS `customer_deposit`;
DROP TABLE IF EXISTS `customer_lifecycle`;
DROP TABLE IF EXISTS `customer_portrait`;
DROP TABLE IF EXISTS `customer_tier`;

-- dashboard_* (1)
DROP TABLE IF EXISTS `dashboard_snapshot`;

-- date_* (1)
DROP TABLE IF EXISTS `date_comparison_config`;

-- device_* (2)
DROP TABLE IF EXISTS `device`;
DROP TABLE IF EXISTS `device_type`;

-- distribution_* (1)
DROP TABLE IF EXISTS `distribution_order_split`;

-- fastgpt_* (4)
DROP TABLE IF EXISTS `fastgpt_chat_msg_log`;
DROP TABLE IF EXISTS `fastgpt_countent`;
DROP TABLE IF EXISTS `fastgpt_role_type`;
DROP TABLE IF EXISTS `fastgpt_tag_content`;

-- financial_* (1)
DROP TABLE IF EXISTS `financial_ledger`;

-- fs_* (52) - 旧FS平台业务表
DROP TABLE IF EXISTS `fs_course_answer_logs_1`;
DROP TABLE IF EXISTS `fs_course_order`;
DROP TABLE IF EXISTS `fs_course_red_packet_log_1`;
DROP TABLE IF EXISTS `fs_course_red_packet_retry`;
DROP TABLE IF EXISTS `fs_course_watch_log_1`;
DROP TABLE IF EXISTS `fs_delivery_notice`;
DROP TABLE IF EXISTS `fs_delivery_notice_logs`;
DROP TABLE IF EXISTS `fs_jst_aftersale_push`;
DROP TABLE IF EXISTS `fs_jst_cod_push`;
DROP TABLE IF EXISTS `fs_knowledge`;
DROP TABLE IF EXISTS `fs_knowledge_cate`;
DROP TABLE IF EXISTS `fs_mini_program_agreement`;
DROP TABLE IF EXISTS `fs_miniprogram_sub_notify_task`;
DROP TABLE IF EXISTS `fs_project_address_config`;
DROP TABLE IF EXISTS `fs_promotional_active_resource`;
DROP TABLE IF EXISTS `fs_sales_user_period_relation`;
DROP TABLE IF EXISTS `fs_statistics_index_overview`;
DROP TABLE IF EXISTS `fs_store_canvas`;
DROP TABLE IF EXISTS `fs_store_cart`;
DROP TABLE IF EXISTS `fs_store_coupon`;
DROP TABLE IF EXISTS `fs_store_coupon_issue`;
DROP TABLE IF EXISTS `fs_store_coupon_issue_user`;
DROP TABLE IF EXISTS `fs_store_express`;
DROP TABLE IF EXISTS `fs_store_hospital580_prescription_answer_scrm`;
DROP TABLE IF EXISTS `fs_store_hospital580_prescription_chat_scrm`;
DROP TABLE IF EXISTS `fs_store_hospital580_prescription_medicine_scrm`;
DROP TABLE IF EXISTS `fs_store_hospital580_prescription_scrm`;
DROP TABLE IF EXISTS `fs_store_hospital580_product_push_scrm`;
DROP TABLE IF EXISTS `fs_store_hospital580_scrm`;
DROP TABLE IF EXISTS `fs_store_level_scrm`;
DROP TABLE IF EXISTS `fs_store_menu`;
DROP TABLE IF EXISTS `fs_store_order_audit_log_scrm`;
DROP TABLE IF EXISTS `fs_store_order_bill_log_scrm`;
DROP TABLE IF EXISTS `fs_store_order_status`;
DROP TABLE IF EXISTS `fs_store_product_attr_item_scrm`;
DROP TABLE IF EXISTS `fs_store_product_details`;
DROP TABLE IF EXISTS `fs_store_product_relation`;
DROP TABLE IF EXISTS `fs_store_product_rule`;
DROP TABLE IF EXISTS `fs_tag_update_queue`;
DROP TABLE IF EXISTS `fs_user_app_version`;
DROP TABLE IF EXISTS `fs_user_black`;
DROP TABLE IF EXISTS `fs_user_company_user_transfer_task`;
DROP TABLE IF EXISTS `fs_user_company_user_transfer_task_detail`;
DROP TABLE IF EXISTS `fs_user_course_live`;
DROP TABLE IF EXISTS `fs_user_course_period_company`;
DROP TABLE IF EXISTS `fs_user_course_video_task`;
DROP TABLE IF EXISTS `fs_user_pop`;
DROP TABLE IF EXISTS `fs_user_talent_wallet`;
DROP TABLE IF EXISTS `fs_user_tui_money_rank`;
DROP TABLE IF EXISTS `fs_watch_device_info`;
DROP TABLE IF EXISTS `fs_watch_user`;
DROP TABLE IF EXISTS `fs_wx_express_task`;

-- health_* (1)
DROP TABLE IF EXISTS `health_check_record`;

-- inventory_* (1)
DROP TABLE IF EXISTS `inventory_check`;

-- invoice_* (1)
DROP TABLE IF EXISTS `invoice`;

-- kb_* (2)
DROP TABLE IF EXISTS `kb_document`;
DROP TABLE IF EXISTS `kb_knowledge_base`;

-- live_* (26) - 旧直播系统
DROP TABLE IF EXISTS `live_answer_red_packet`;
DROP TABLE IF EXISTS `live_answer_red_packet_live`;
DROP TABLE IF EXISTS `live_config`;
DROP TABLE IF EXISTS `live_goods_order`;
DROP TABLE IF EXISTS `live_goods_order_items`;
DROP TABLE IF EXISTS `live_order_status`;
DROP TABLE IF EXISTS `live_question`;
DROP TABLE IF EXISTS `live_question_answer_red_packet`;
DROP TABLE IF EXISTS `live_question_bank`;
DROP TABLE IF EXISTS `live_question_live`;
DROP TABLE IF EXISTS `live_question_user_log`;
DROP TABLE IF EXISTS `live_reward_compensation`;
DROP TABLE IF EXISTS `live_reward_record_wx`;
DROP TABLE IF EXISTS `live_room_online_trend`;
DROP TABLE IF EXISTS `live_student_data`;
DROP TABLE IF EXISTS `live_user`;
DROP TABLE IF EXISTS `live_user_gift`;
DROP TABLE IF EXISTS `live_user_video_duration`;
DROP TABLE IF EXISTS `live_user_watch_duration`;
DROP TABLE IF EXISTS `live_watch_score_rule`;
DROP TABLE IF EXISTS `live_watch_config`;

-- lobster_* (13) - 旧Lobster AI工作流
DROP TABLE IF EXISTS `lobster_api_registry`;
DROP TABLE IF EXISTS `lobster_channel_type_registry`;
DROP TABLE IF EXISTS `lobster_dialogue_state`;
DROP TABLE IF EXISTS `lobster_event_node_audit`;
DROP TABLE IF EXISTS `lobster_evolution_log`;
DROP TABLE IF EXISTS `lobster_evolution_suggestion`;
DROP TABLE IF EXISTS `lobster_heartbeat_registry`;
DROP TABLE IF EXISTS `lobster_learning_corpus`;
DROP TABLE IF EXISTS `lobster_message_delivery_log`;
DROP TABLE IF EXISTS `lobster_system_prompt`;
DROP TABLE IF EXISTS `lobster_unified_contact`;
DROP TABLE IF EXISTS `lobster_user_node_optimization`;
DROP TABLE IF EXISTS `lobster_workflow_node`;

-- market_* (1)
DROP TABLE IF EXISTS `market_push`;

-- marketing_* (1)
DROP TABLE IF EXISTS `marketing_trigger`;

-- medical_* (2)
DROP TABLE IF EXISTS `medical_insurance_config`;
DROP TABLE IF EXISTS `medical_insurance_settlement`;

-- medicine_* (1)
DROP TABLE IF EXISTS `medicine_remind`;

-- notify_* (2)
DROP TABLE IF EXISTS `notify_record`;
DROP TABLE IF EXISTS `notify_template`;

-- pharmacist_* (1)
DROP TABLE IF EXISTS `pharmacist`;

-- platform_* (3)
DROP TABLE IF EXISTS `platform_course`;
DROP TABLE IF EXISTS `platform_live`;
DROP TABLE IF EXISTS `platform_product`;

-- prescription_* (1)
DROP TABLE IF EXISTS `prescription`;

-- procurement_* (1)
DROP TABLE IF EXISTS `procurement_order_settle`;

-- promotion_* (1)
DROP TABLE IF EXISTS `promotion`;

-- proxy_* (3) - 注意: proxy/proxy_oper_log/proxy_withdraw等在用,勿删!
-- 只删除无mapper引用的:
DROP TABLE IF EXISTS `proxy_balance_log`;
DROP TABLE IF EXISTS `proxy_menu`;
DROP TABLE IF EXISTS `proxy_role_menu`;

-- purchase_* (2)
DROP TABLE IF EXISTS `purchase_order`;
DROP TABLE IF EXISTS `purchase_order_item`;

-- qw_* (17) - 旧企微功能表
DROP TABLE IF EXISTS `qw_assign_rule`;
DROP TABLE IF EXISTS `qw_assign_rule_user`;
DROP TABLE IF EXISTS `qw_config`;
DROP TABLE IF EXISTS `qw_contact_way_welcome`;
DROP TABLE IF EXISTS `qw_customer_link`;
DROP TABLE IF EXISTS `qw_customer_link_channel`;
DROP TABLE IF EXISTS `qw_customer_link_user`;
DROP TABLE IF EXISTS `qw_external_ai_analyze_session`;
DROP TABLE IF EXISTS `qw_group_actual`;
DROP TABLE IF EXISTS `qw_group_live_code`;
DROP TABLE IF EXISTS `qw_schedule`;
DROP TABLE IF EXISTS `qw_sop_clickhouse`;
DROP TABLE IF EXISTS `qw_sop_logs_clickhouse`;
DROP TABLE IF EXISTS `qw_sop_service_provider`;
DROP TABLE IF EXISTS `qw_sop_temp_qw_sop_temp`;
DROP TABLE IF EXISTS `qw_user_del_loss_log`;
DROP TABLE IF EXISTS `qw_work_short_link`;

-- reconciliation_* (1)
DROP TABLE IF EXISTS `reconciliation_report`;

-- red_* (1)
DROP TABLE IF EXISTS `red_packet_log`;

-- resource_* (2)
DROP TABLE IF EXISTS `resource_auth`;
DROP TABLE IF EXISTS `resource_management`;

-- saas_* (2)
DROP TABLE IF EXISTS `saas_tenant`;
DROP TABLE IF EXISTS `saas_tenant_config`;

-- sales_* (3)
DROP TABLE IF EXISTS `sales_performance_detail`;
DROP TABLE IF EXISTS `sales_performance_pharmacist`;
DROP TABLE IF EXISTS `sales_performance_store`;

-- settlement_* (2)
DROP TABLE IF EXISTS `settlement_batch`;
DROP TABLE IF EXISTS `settlement_ratio`;

-- shipment_* (1)
DROP TABLE IF EXISTS `shipment`;

-- stock_* (1)
DROP TABLE IF EXISTS `stock_transfer`;

-- store_* (1)
DROP TABLE IF EXISTS `store_company_code`;

-- supplier_* (1)
DROP TABLE IF EXISTS `supplier`;

-- sys_* (2) - 自定义系统配置(非若依框架)
DROP TABLE IF EXISTS `sys_dept_config`;
DROP TABLE IF EXISTS `sys_dept_config_log`;

-- tenant_* (12) - 旧租户配置
DROP TABLE IF EXISTS `tenant_custom_field`;
DROP TABLE IF EXISTS `tenant_custom_field_index`;
DROP TABLE IF EXISTS `tenant_fee_config`;
DROP TABLE IF EXISTS `tenant_fee_consumption`;
DROP TABLE IF EXISTS `tenant_package`;
DROP TABLE IF EXISTS `tenant_package_order`;
DROP TABLE IF EXISTS `tenant_prepaid_balance`;
DROP TABLE IF EXISTS `tenant_product_import`;
DROP TABLE IF EXISTS `tenant_recharge_order`;
DROP TABLE IF EXISTS `tenant_resource`;
DROP TABLE IF EXISTS `tenant_service_config`;
DROP TABLE IF EXISTS `tenant_wallet_txn`;

-- wechat_* (1)
DROP TABLE IF EXISTS `wechat_customer_inherit_rule`;

-- withdraw_* (1)
DROP TABLE IF EXISTS `withdraw_apply`;

-- wx_* (1)
DROP TABLE IF EXISTS `wx_msg_log`;

-- ============================================================================
-- 总计: 251 张表 
-- ============================================================================
-- 
-- 未包含在本文中的需要保留的表:
-- 1. 类别B (17张): qrtz_*(Quartz), databasechangelog*(Liquibase), gen_table*(生成器), sys_job/sys_job_log
-- 2. 类别C (20张): fs_user_operation_log_0~9, qw_msg_0~9 - 可能为MySQL分区表,需DBA另行确认
-- 3. Mapper引用表 (713张): 所有在 MyBatis Mapper XML 中有 SQL 引用的表
-- 4. 动态SQL引用表: qw_sop, qw_sop_temp, crm_customer, ad_account, fs_store_product, live_video 等
-- ============================================================================