-- Author: fruit.com.cn
-- K110 校准互动计数字段。
-- 历史压测数据把部分计数直接改成 100000 量级，与真实行数不符；
-- 这里按实际有效互动行重算剧集点赞数与剧收藏数，可重复执行。
BEGIN;

-- 剧集点赞数 = 该集未删除的 EPISODE 点赞行数
UPDATE drama_episode e
SET like_count = sub.cnt
FROM (
    SELECT e2.id, COUNT(l.id) AS cnt
    FROM drama_episode e2
    LEFT JOIN content_like l
           ON l.target_type = 'EPISODE' AND l.target_id = e2.id AND l.deleted = 0
    GROUP BY e2.id
) sub
WHERE e.id = sub.id AND e.like_count IS DISTINCT FROM sub.cnt;

-- 剧收藏数 = 该剧未删除的收藏行数
UPDATE drama d
SET favorite_count = sub.cnt
FROM (
    SELECT d2.id, COUNT(f.id) AS cnt
    FROM drama d2
    LEFT JOIN user_favorite f ON f.drama_id = d2.id AND f.deleted = 0
    GROUP BY d2.id
) sub
WHERE d.id = sub.id AND d.favorite_count IS DISTINCT FROM sub.cnt;

COMMIT;