-- 渠道统计按 tag_id 取去重 external_userid；组合索引避免回表扫描标签关系数据。
-- 既有库切换统计前应执行一次：php think qywx:backfill-customer-tags --all --fast
SET @idx_tag_ext_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'zyt_qywx_external_contact_tag'
      AND INDEX_NAME = 'idx_tag_ext'
);

SET @add_idx_tag_ext_sql := IF(
    @idx_tag_ext_exists = 0,
    'ALTER TABLE `zyt_qywx_external_contact_tag` ADD INDEX `idx_tag_ext` (`tag_id`, `external_userid`)',
    'SELECT 1'
);

PREPARE add_idx_tag_ext_stmt FROM @add_idx_tag_ext_sql;
EXECUTE add_idx_tag_ext_stmt;
DEALLOCATE PREPARE add_idx_tag_ext_stmt;

-- 渠道选项按标签读取最新名称，覆盖 tag_id + update_time 可避免重复扫描关系表。
SET @idx_tag_update_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'zyt_qywx_external_contact_tag'
      AND INDEX_NAME = 'idx_tag_update'
);

SET @add_idx_tag_update_sql := IF(
    @idx_tag_update_exists = 0,
    'ALTER TABLE `zyt_qywx_external_contact_tag` ADD INDEX `idx_tag_update` (`tag_id`, `update_time`)',
    'SELECT 1'
);

PREPARE add_idx_tag_update_stmt FROM @add_idx_tag_update_sql;
EXECUTE add_idx_tag_update_stmt;
DEALLOCATE PREPARE add_idx_tag_update_stmt;