-- =============================================================================
-- 美国版 · 业务数据清理脚本（标签 / 打印日志 / 报表统计源数据 / 门店组织）
-- =============================================================================
-- 【警告】本脚本会永久删除下列范围内的数据，不可恢复！
--   ✓ 清空：打印日志、标签主数据、模板、产品、门店、Partner/Region、用户-门店绑定
--   ✗ 保留：user / role / userrole / userpost / menu / rolemenu（账号与权限菜单）
--
-- 说明：
--   - 「统计 / Label Report」数据来自 fl_label_print_task 聚合，无独立统计表
--   - 执行前请备份数据库；建议在测试环境先跑一遍
--   - MySQL 5.7+ / 8.0+
--
-- 用法（示例）：
--   mysql -h127.0.0.1 -P3306 -uroot -p your_database < clean-us-business-data.sql
-- 或配合同目录 PowerShell： .\run-clean-us-business-data.ps1
-- =============================================================================

SET NAMES utf8mb4;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;

-- 可选：指定库名（若已在 mysql 客户端 USE 过库，可注释掉下一行）
-- USE food_labeling_us;

START TRANSACTION;

-- ---------------------------------------------------------------------------
-- 0) 清理前行数（便于对比）
-- ---------------------------------------------------------------------------
SELECT '=== BEFORE ===' AS phase;
SELECT 'fl_label_print_data' AS tbl, COUNT(*) AS cnt FROM fl_label_print_data
UNION ALL SELECT 'fl_label_print_task', COUNT(*) FROM fl_label_print_task
UNION ALL SELECT 'fl_label', COUNT(*) FROM fl_label
UNION ALL SELECT 'fl_label_template', COUNT(*) FROM fl_label_template
UNION ALL SELECT 'fl_product', COUNT(*) FROM fl_product
UNION ALL SELECT 'location', COUNT(*) FROM location
UNION ALL SELECT 'userlocation', COUNT(*) FROM userlocation
UNION ALL SELECT 'fl_partner', COUNT(*) FROM fl_partner
UNION ALL SELECT 'fl_group', COUNT(*) FROM fl_group;

-- ---------------------------------------------------------------------------
-- 1) 打印日志（报表 / Print Log / Label Report 的数据源）
-- ---------------------------------------------------------------------------
DELETE FROM fl_label_print_data;
DELETE FROM fl_label_print_task;

-- ---------------------------------------------------------------------------
-- 2) 标签业务（分类、类型、模板、多选项、标签实例）
-- ---------------------------------------------------------------------------
DELETE FROM fl_label_product;
DELETE FROM fl_label;
DELETE FROM fl_label_template_element;
DELETE FROM fl_label_template_product_default;
DELETE FROM fl_label_template_location;
DELETE FROM fl_label_template;
DELETE FROM fl_label_type_location;
DELETE FROM fl_label_type;
DELETE FROM fl_label_category_location;
DELETE FROM fl_label_category;
DELETE FROM fl_label_multiple_option_location;
DELETE FROM fl_label_multiple_option;

-- ---------------------------------------------------------------------------
-- 3) 产品（Menu Management）
-- ---------------------------------------------------------------------------
DELETE FROM fl_location_product;
DELETE FROM fl_product_category_location;
DELETE FROM fl_product;
DELETE FROM fl_product_category;

-- ---------------------------------------------------------------------------
-- 4) 门店与支持信息
-- ---------------------------------------------------------------------------
DELETE FROM fl_location_support;
DELETE FROM userlocation;
DELETE FROM location;

-- ---------------------------------------------------------------------------
-- 5) 组织（Company / Region）— 先 Region 再 Partner（存在 FK_fl_group_partner）
-- ---------------------------------------------------------------------------
DELETE FROM fl_group;
DELETE FROM fl_partner;

-- ---------------------------------------------------------------------------
-- 6) 清理后行数
-- ---------------------------------------------------------------------------
SELECT '=== AFTER (expect 0) ===' AS phase;
SELECT 'fl_label_print_data' AS tbl, COUNT(*) AS cnt FROM fl_label_print_data
UNION ALL SELECT 'fl_label_print_task', COUNT(*) FROM fl_label_print_task
UNION ALL SELECT 'fl_label', COUNT(*) FROM fl_label
UNION ALL SELECT 'fl_label_template', COUNT(*) FROM fl_label_template
UNION ALL SELECT 'fl_product', COUNT(*) FROM fl_product
UNION ALL SELECT 'location', COUNT(*) FROM location
UNION ALL SELECT 'userlocation', COUNT(*) FROM userlocation
UNION ALL SELECT 'fl_partner', COUNT(*) FROM fl_partner
UNION ALL SELECT 'fl_group', COUNT(*) FROM fl_group;

COMMIT;

SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

SELECT '=== DONE ===' AS phase, NOW() AS finished_at;