64 lines
2.9 KiB
SQL
64 lines
2.9 KiB
SQL
ALTER TABLE project_episodes ADD COLUMN redraw_script text;
|
|
|
|
INSERT INTO project_episodes (
|
|
id, project_id, episode_no, name, cover_asset_id, source_video_asset_id,
|
|
subtitle_asset_id, audio_source, source_language, status, analysis_message,
|
|
redraw_script, created_at, updated_at
|
|
)
|
|
SELECT
|
|
gen_random_uuid(), project.id, 1, '第1集', project.cover_asset_id,
|
|
project.source_video_asset_id, project.subtitle_asset_id, project.audio_source,
|
|
project.source_language, project.redraw_status, project.analysis_message,
|
|
project.redraw_script, project.created_at, project.updated_at
|
|
FROM creative_projects project
|
|
WHERE project.project_type = 'video_redraw'
|
|
AND project.deleted_at IS NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM project_episodes episode
|
|
WHERE episode.project_id = project.id AND episode.deleted_at IS NULL
|
|
);
|
|
|
|
WITH first_episode AS (
|
|
SELECT DISTINCT ON (episode.project_id) episode.id, episode.project_id
|
|
FROM project_episodes episode
|
|
JOIN creative_projects project ON project.id = episode.project_id
|
|
WHERE project.project_type = 'video_redraw' AND episode.deleted_at IS NULL
|
|
ORDER BY episode.project_id, episode.episode_no, episode.created_at
|
|
)
|
|
UPDATE project_episodes episode
|
|
SET cover_asset_id = COALESCE(episode.cover_asset_id, project.cover_asset_id),
|
|
source_video_asset_id = COALESCE(episode.source_video_asset_id, project.source_video_asset_id),
|
|
subtitle_asset_id = COALESCE(episode.subtitle_asset_id, project.subtitle_asset_id),
|
|
audio_source = project.audio_source,
|
|
source_language = COALESCE(episode.source_language, project.source_language),
|
|
status = project.redraw_status,
|
|
analysis_message = COALESCE(episode.analysis_message, project.analysis_message),
|
|
redraw_script = COALESCE(episode.redraw_script, project.redraw_script)
|
|
FROM first_episode
|
|
JOIN creative_projects project ON project.id = first_episode.project_id
|
|
WHERE episode.id = first_episode.id;
|
|
|
|
WITH first_episode AS (
|
|
SELECT DISTINCT ON (episode.project_id) episode.id, episode.project_id
|
|
FROM project_episodes episode
|
|
JOIN creative_projects project ON project.id = episode.project_id
|
|
WHERE project.project_type = 'video_redraw' AND episode.deleted_at IS NULL
|
|
ORDER BY episode.project_id, episode.episode_no, episode.created_at
|
|
)
|
|
UPDATE episode_storyboards storyboard
|
|
SET episode_id = first_episode.id, project_id = NULL
|
|
FROM first_episode
|
|
WHERE storyboard.project_id = first_episode.project_id;
|
|
|
|
WITH first_episode AS (
|
|
SELECT DISTINCT ON (episode.project_id) episode.id, episode.project_id
|
|
FROM project_episodes episode
|
|
JOIN creative_projects project ON project.id = episode.project_id
|
|
WHERE project.project_type = 'video_redraw' AND episode.deleted_at IS NULL
|
|
ORDER BY episode.project_id, episode.episode_no, episode.created_at
|
|
)
|
|
UPDATE generation_tasks task
|
|
SET episode_id = first_episode.id
|
|
FROM first_episode
|
|
WHERE task.project_id = first_episode.project_id AND task.episode_id IS NULL;
|