-- Author: fruit.com.cn
-- 将已有零绑定转码任务按分集视频 URL 回填（幂等，仅处理未完成任务）

BEGIN;

UPDATE transcode_task t
SET drama_id = e.drama_id,
    episode_id = e.id
FROM drama_episode e
WHERE t.deleted = 0
  AND e.deleted = 0
  AND t.status IN ('QUEUED', 'RUNNING')
  AND t.episode_id = 0
  AND e.drama_id > 0
  AND split_part(e.video_url, '?', 1) = split_part(t.source_url, '?', 1);

UPDATE drama_episode e
SET video_asset_id = t.media_asset_id
FROM transcode_task t
WHERE t.deleted = 0
  AND t.episode_id = e.id
  AND t.media_asset_id > 0
  AND e.video_asset_id = 0
  AND split_part(e.video_url, '?', 1) = split_part(t.source_url, '?', 1);

COMMIT;