66 lines
2.4 KiB
Python
66 lines
2.4 KiB
Python
|
"""unify Saved into a built-in Watch later playlist
|
||
|
|
|
||
|
|
Revision ID: 0012_watch_later
|
||
|
|
Revises: 0011_playlists
|
||
|
|
Create Date: 2026-06-15
|
||
|
|
|
||
|
|
Migrates the old per-video status='saved' into membership of a built-in 'watch_later'
|
||
|
|
playlist (one per user who had saved videos), then demotes those states to 'new'.
|
||
|
|
"""
|
||
|
|
from typing import Sequence, Union
|
||
|
|
|
||
|
|
from alembic import op
|
||
|
|
|
||
|
|
revision: str = "0012_watch_later"
|
||
|
|
down_revision: Union[str, None] = "0011_playlists"
|
||
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
||
|
|
depends_on: Union[str, Sequence[str], None] = None
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
# 1) A Watch later playlist for each user that has saved videos (and none yet).
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
INSERT INTO playlists (user_id, name, kind, source, dirty, position, created_at, updated_at)
|
||
|
|
SELECT DISTINCT vs.user_id, 'Watch later', 'watch_later', 'local', false, 0, now(), now()
|
||
|
|
FROM video_states vs
|
||
|
|
WHERE vs.status = 'saved'
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1 FROM playlists p
|
||
|
|
WHERE p.user_id = vs.user_id AND p.kind = 'watch_later'
|
||
|
|
)
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
# 2) Add each saved video as an item, ordered (newest-saved first → position 0).
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
INSERT INTO playlist_items (playlist_id, video_id, position, added_at)
|
||
|
|
SELECT p.id, vs.video_id,
|
||
|
|
row_number() OVER (
|
||
|
|
PARTITION BY vs.user_id
|
||
|
|
ORDER BY COALESCE(vs.watched_at, vs.updated_at) DESC
|
||
|
|
) - 1,
|
||
|
|
COALESCE(vs.watched_at, vs.updated_at, now())
|
||
|
|
FROM video_states vs
|
||
|
|
JOIN playlists p ON p.user_id = vs.user_id AND p.kind = 'watch_later'
|
||
|
|
WHERE vs.status = 'saved'
|
||
|
|
ON CONFLICT (playlist_id, video_id) DO NOTHING
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
# 3) Demote the migrated saved states to the default.
|
||
|
|
op.execute("UPDATE video_states SET status = 'new' WHERE status = 'saved'")
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
# Best-effort reverse: restore status='saved' for videos in watch_later lists, then drop them.
|
||
|
|
op.execute(
|
||
|
|
"""
|
||
|
|
UPDATE video_states vs SET status = 'saved'
|
||
|
|
FROM playlist_items pi
|
||
|
|
JOIN playlists p ON p.id = pi.playlist_id AND p.kind = 'watch_later'
|
||
|
|
WHERE pi.video_id = vs.video_id AND p.user_id = vs.user_id
|
||
|
|
AND vs.status = 'new'
|
||
|
|
"""
|
||
|
|
)
|
||
|
|
op.execute("DELETE FROM playlists WHERE kind = 'watch_later'")
|