79 lines
2.7 KiB
SQL
79 lines
2.7 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
ALTER TABLE watch_together_rooms
|
|
ADD COLUMN phase TEXT NOT NULL DEFAULT 'lobby',
|
|
ADD COLUMN selection_mode TEXT NOT NULL DEFAULT 'host_pick',
|
|
ADD COLUMN selection_revision BIGINT NOT NULL DEFAULT 0,
|
|
ADD COLUMN selected_content_id TEXT,
|
|
ADD COLUMN selected_file_id INTEGER,
|
|
ADD COLUMN selected_library_id INTEGER;
|
|
|
|
UPDATE watch_together_rooms
|
|
SET selected_content_id = content_id,
|
|
selected_file_id = file_id,
|
|
selected_library_id = library_id,
|
|
phase = CASE
|
|
WHEN content_id IS NOT NULL AND BTRIM(content_id) <> '' THEN 'playing'
|
|
ELSE 'lobby'
|
|
END,
|
|
selection_mode = 'host_pick',
|
|
selection_revision = CASE
|
|
WHEN content_id IS NOT NULL AND BTRIM(content_id) <> '' THEN 1
|
|
ELSE 0
|
|
END;
|
|
|
|
ALTER TABLE watch_together_rooms
|
|
DROP CONSTRAINT IF EXISTS watch_together_rooms_status_check,
|
|
DROP COLUMN IF EXISTS status,
|
|
DROP COLUMN IF EXISTS content_id,
|
|
DROP COLUMN IF EXISTS file_id,
|
|
DROP COLUMN IF EXISTS library_id;
|
|
|
|
ALTER TABLE watch_together_rooms
|
|
ADD CONSTRAINT watch_together_rooms_phase_check
|
|
CHECK (phase IN ('lobby', 'playing', 'ended')),
|
|
ADD CONSTRAINT watch_together_rooms_selection_mode_check
|
|
CHECK (selection_mode IN ('host_pick', 'vote'));
|
|
|
|
DROP INDEX IF EXISTS idx_watch_together_rooms_status_created_at;
|
|
CREATE INDEX idx_watch_together_rooms_phase_created_at
|
|
ON watch_together_rooms (phase, created_at DESC);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
ALTER TABLE watch_together_rooms
|
|
ADD COLUMN content_id TEXT,
|
|
ADD COLUMN file_id INTEGER,
|
|
ADD COLUMN library_id INTEGER,
|
|
ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
|
|
|
|
UPDATE watch_together_rooms
|
|
SET content_id = COALESCE(selected_content_id, ''),
|
|
file_id = selected_file_id,
|
|
library_id = selected_library_id,
|
|
status = CASE
|
|
WHEN phase = 'ended' THEN 'closed'
|
|
ELSE 'active'
|
|
END;
|
|
|
|
ALTER TABLE watch_together_rooms
|
|
ALTER COLUMN content_id SET NOT NULL,
|
|
DROP CONSTRAINT IF EXISTS watch_together_rooms_phase_check,
|
|
DROP CONSTRAINT IF EXISTS watch_together_rooms_selection_mode_check,
|
|
DROP COLUMN IF EXISTS phase,
|
|
DROP COLUMN IF EXISTS selection_mode,
|
|
DROP COLUMN IF EXISTS selection_revision,
|
|
DROP COLUMN IF EXISTS selected_content_id,
|
|
DROP COLUMN IF EXISTS selected_file_id,
|
|
DROP COLUMN IF EXISTS selected_library_id;
|
|
|
|
ALTER TABLE watch_together_rooms
|
|
ADD CONSTRAINT watch_together_rooms_status_check
|
|
CHECK (status IN ('active', 'closed'));
|
|
|
|
DROP INDEX IF EXISTS idx_watch_together_rooms_phase_created_at;
|
|
CREATE INDEX idx_watch_together_rooms_status_created_at
|
|
ON watch_together_rooms (status, created_at DESC);
|
|
-- +goose StatementEnd
|