aiot-platform-cloud/sql/mysql/project/04-index-audit.sql

-- =============================================
-- 租户-项目两级架构 - 唯一索引审计脚本
-- 检查各业务表现有唯一键，评估是否需要将 project_id 纳入唯一约束
-- 执行顺序：在 03-alter-business-tables.sql 之后执行
-- =============================================

-- -----------------------------------------------
-- 查看各表当前的索引情况（审计用，执行前先 SHOW INDEX 确认）
-- -----------------------------------------------
SHOW INDEX FROM iot_device;
SHOW INDEX FROM ops_order;
SHOW INDEX FROM ops_order_event;
SHOW INDEX FROM ops_order_dispatch;
SHOW INDEX FROM ops_order_queue;
SHOW INDEX FROM ops_bus_area;
SHOW INDEX FROM ops_area_device_relation;
SHOW INDEX FROM ops_order_security_ext;
SHOW INDEX FROM ops_area_security_user;
SHOW INDEX FROM ops_order_clean_ext;
SHOW INDEX FROM ops_cleaner_status;
SHOW INDEX FROM ops_cleaner_performance_monthly;
SHOW INDEX FROM ops_device_trajectory;
SHOW INDEX FROM ops_inspection_record;
SHOW INDEX FROM ops_inspection_record_item;

-- -----------------------------------------------
-- 唯一键改造建议（根据实际业务语义决定是否执行）
-- 说明：若某张表的唯一键仅含 tenant_id，则需要将 project_id 也纳入，
--       以确保在同一租户下不同项目之间的数据互不干扰。
-- -----------------------------------------------

-- ops_bus_area：区域编码在项目内唯一（原唯一键可能仅含 tenant_id + code）
-- 示例：DROP INDEX uk_tenant_code ON ops_bus_area;
-- 示例：ALTER TABLE ops_bus_area ADD UNIQUE KEY uk_project_code (tenant_id, project_id, code, deleted);

-- ops_area_device_relation：设备-区域关联在项目内唯一
-- 示例：DROP INDEX uk_area_device ON ops_area_device_relation;
-- 示例：ALTER TABLE ops_area_device_relation ADD UNIQUE KEY uk_project_area_device (project_id, area_id, device_id, deleted);

-- ops_area_security_user：安保用户-区域关联在项目内唯一
-- 示例：DROP INDEX uk_area_user ON ops_area_security_user;
-- 示例：ALTER TABLE ops_area_security_user ADD UNIQUE KEY uk_project_area_user (project_id, area_id, user_id, deleted);

-- ops_cleaner_status：保洁员状态在项目内唯一（每个项目内每个用户仅一条状态记录）
-- 示例：DROP INDEX uk_tenant_user ON ops_cleaner_status;
-- 示例：ALTER TABLE ops_cleaner_status ADD UNIQUE KEY uk_project_user (project_id, user_id, deleted);

-- ops_cleaner_performance_monthly：月度绩效在项目内唯一（project + user + year_month）
-- 示例：DROP INDEX uk_tenant_user_month ON ops_cleaner_performance_monthly;
-- 示例：ALTER TABLE ops_cleaner_performance_monthly ADD UNIQUE KEY uk_project_user_month (project_id, user_id, stat_year, stat_month, deleted);

-- iot_device：设备标识符在项目内唯一（device_key / product_key + device_name）
-- 示例：根据实际唯一键定义决定是否改造。

-- -----------------------------------------------
-- 验证：各表 project_id 回填情况
-- -----------------------------------------------
SELECT 'iot_device'                    AS tbl, COUNT(*) AS total, SUM(project_id IS NULL) AS null_count FROM iot_device
UNION ALL
SELECT 'ops_order',                    COUNT(*), SUM(project_id IS NULL) FROM ops_order
UNION ALL
SELECT 'ops_order_event',              COUNT(*), SUM(project_id IS NULL) FROM ops_order_event
UNION ALL
SELECT 'ops_order_dispatch',           COUNT(*), SUM(project_id IS NULL) FROM ops_order_dispatch
UNION ALL
SELECT 'ops_order_queue',              COUNT(*), SUM(project_id IS NULL) FROM ops_order_queue
UNION ALL
SELECT 'ops_bus_area',                 COUNT(*), SUM(project_id IS NULL) FROM ops_bus_area
UNION ALL
SELECT 'ops_area_device_relation',     COUNT(*), SUM(project_id IS NULL) FROM ops_area_device_relation
UNION ALL
SELECT 'ops_order_security_ext',       COUNT(*), SUM(project_id IS NULL) FROM ops_order_security_ext
UNION ALL
SELECT 'ops_area_security_user',       COUNT(*), SUM(project_id IS NULL) FROM ops_area_security_user
UNION ALL
SELECT 'ops_order_clean_ext',          COUNT(*), SUM(project_id IS NULL) FROM ops_order_clean_ext
UNION ALL
SELECT 'ops_cleaner_status',           COUNT(*), SUM(project_id IS NULL) FROM ops_cleaner_status
UNION ALL
SELECT 'ops_cleaner_performance_monthly', COUNT(*), SUM(project_id IS NULL) FROM ops_cleaner_performance_monthly
UNION ALL
SELECT 'ops_device_trajectory',        COUNT(*), SUM(project_id IS NULL) FROM ops_device_trajectory
UNION ALL
SELECT 'ops_inspection_record',        COUNT(*), SUM(project_id IS NULL) FROM ops_inspection_record
UNION ALL
SELECT 'ops_inspection_record_item',   COUNT(*), SUM(project_id IS NULL) FROM ops_inspection_record_item;