-- 一次性迁移：先暂停图片写入/清理任务，执行本文件，再部署新后端并恢复服务。
-- 已确认历史仅 Android 和微信小程序；未来来源不能再按昵称推断。
-- DDL 会隐式提交，执行前备份；勿在旧后端仍写入时迁移后直接恢复旧服务。
ALTER TABLE tpl_app_user_devices
  ADD COLUMN client_source VARCHAR(16) NOT NULL DEFAULT 'unknown'
    COMMENT '来源: android/wechat/ios/web/unknown' AFTER device_id;
ALTER TABLE tpl_app_processed_images
  ADD COLUMN client_source VARCHAR(16) NOT NULL DEFAULT 'unknown'
    COMMENT '来源: android/wechat/ios/web/unknown' AFTER id,
  ADD KEY idx_source_category_updated (client_source, category, updated_at),
  ADD KEY idx_source_category_region_updated (client_source, category, region, updated_at);

-- 显式保留 updated_at，不能让迁移刷新历史活跃时间/排行榜时间窗。
UPDATE tpl_app_user_devices
SET client_source = 'android', updated_at = updated_at
WHERE client_source = 'unknown';
UPDATE tpl_app_processed_images
SET client_source = CASE WHEN LEFT(nickname, 8) = 'android_' THEN 'android' ELSE 'wechat' END,
    updated_at = updated_at
WHERE client_source = 'unknown';

SELECT client_source, COUNT(*) AS records FROM tpl_app_processed_images GROUP BY client_source;
SELECT client_source, COUNT(*) AS devices FROM tpl_app_user_devices GROUP BY client_source;