136 lines
5.7 KiB
SQL
136 lines
5.7 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
-- request_integrations: one-row-per-kind -> many instances keyed by id.
|
|
ALTER TABLE public.request_integrations
|
|
ADD COLUMN IF NOT EXISTS id text,
|
|
ADD COLUMN IF NOT EXISTS name text NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS is_4k boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS is_default boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS is_default_4k boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS anime_enabled boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS anime_quality_profile_id integer,
|
|
ADD COLUMN IF NOT EXISTS anime_root_folder text NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS anime_tags integer[] NOT NULL DEFAULT '{}';
|
|
|
|
-- Backfill: the lone existing row per kind becomes that kind's HD default.
|
|
UPDATE public.request_integrations
|
|
SET id = gen_random_uuid()::text,
|
|
name = initcap(kind),
|
|
is_default = enabled
|
|
WHERE id IS NULL;
|
|
|
|
-- Swap the primary key from kind to id; keep kind as a plain column.
|
|
ALTER TABLE public.request_integrations
|
|
DROP CONSTRAINT request_integrations_pkey;
|
|
ALTER TABLE public.request_integrations
|
|
ALTER COLUMN id SET NOT NULL,
|
|
ADD PRIMARY KEY (id);
|
|
|
|
-- Quality-role invariants: at most one default / one 4K-default per kind.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_request_integrations_default_per_kind
|
|
ON public.request_integrations (kind) WHERE is_default;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_request_integrations_default4k_per_kind
|
|
ON public.request_integrations (kind) WHERE is_default_4k;
|
|
|
|
-- Targets: one request -> N fulfillment targets.
|
|
CREATE TABLE IF NOT EXISTS public.media_request_targets (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
request_id text NOT NULL REFERENCES public.media_requests(id) ON DELETE CASCADE,
|
|
integration_id text REFERENCES public.request_integrations(id) ON DELETE SET NULL,
|
|
integration_kind text NOT NULL DEFAULT '',
|
|
quality text NOT NULL,
|
|
is_anime boolean NOT NULL DEFAULT false,
|
|
external_id text NOT NULL DEFAULT '',
|
|
external_status text NOT NULL DEFAULT '',
|
|
status text NOT NULL DEFAULT 'queued',
|
|
last_error text NOT NULL DEFAULT '',
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT media_request_targets_quality_check CHECK (quality IN ('1080p', '2160p')),
|
|
CONSTRAINT media_request_targets_status_check
|
|
CHECK (status IN ('queued', 'downloading', 'completed', 'failed'))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_media_request_targets_request_quality
|
|
ON public.media_request_targets (request_id, quality);
|
|
CREATE INDEX IF NOT EXISTS idx_media_request_targets_request
|
|
ON public.media_request_targets (request_id);
|
|
|
|
-- Backfill targets from already-submitted requests (those with an external id).
|
|
INSERT INTO public.media_request_targets
|
|
(request_id, integration_id, integration_kind, quality, is_anime,
|
|
external_id, external_status, status, created_at, updated_at)
|
|
SELECT mr.id,
|
|
ri.id,
|
|
mr.integration_kind,
|
|
'1080p',
|
|
false,
|
|
mr.external_id,
|
|
mr.external_status,
|
|
CASE
|
|
WHEN mr.status = 'completed' THEN 'completed'
|
|
WHEN mr.status = 'downloading' THEN 'downloading'
|
|
WHEN mr.outcome = 'failed' THEN 'failed'
|
|
ELSE 'queued'
|
|
END,
|
|
mr.created_at,
|
|
mr.updated_at
|
|
FROM public.media_requests mr
|
|
LEFT JOIN public.request_integrations ri ON ri.kind = mr.integration_kind
|
|
WHERE mr.external_id <> '';
|
|
|
|
-- media_requests: add is_anime, move per-fulfillment columns out to targets.
|
|
ALTER TABLE public.media_requests
|
|
ADD COLUMN IF NOT EXISTS is_anime boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE public.media_requests
|
|
DROP COLUMN IF EXISTS integration_kind,
|
|
DROP COLUMN IF EXISTS external_id,
|
|
DROP COLUMN IF EXISTS external_status;
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
-- Restore per-fulfillment columns on media_requests.
|
|
ALTER TABLE public.media_requests
|
|
ADD COLUMN IF NOT EXISTS integration_kind text NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS external_id text NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS external_status text NOT NULL DEFAULT '';
|
|
|
|
-- Copy back the 1080p target's fulfillment fields (lossy: 4K/anime targets dropped).
|
|
UPDATE public.media_requests mr
|
|
SET integration_kind = t.integration_kind,
|
|
external_id = t.external_id,
|
|
external_status = t.external_status
|
|
FROM public.media_request_targets t
|
|
WHERE t.request_id = mr.id AND t.quality = '1080p';
|
|
|
|
ALTER TABLE public.media_requests DROP COLUMN IF EXISTS is_anime;
|
|
|
|
DROP TABLE IF EXISTS public.media_request_targets;
|
|
|
|
-- Collapse request_integrations back to kind-PK (lossy: keep one default per kind).
|
|
DELETE FROM public.request_integrations a
|
|
USING public.request_integrations b
|
|
WHERE a.kind = b.kind AND a.id <> b.id AND b.is_default AND NOT a.is_default;
|
|
-- If a kind has no default, keep an arbitrary row and drop the rest.
|
|
DELETE FROM public.request_integrations a
|
|
USING public.request_integrations b
|
|
WHERE a.kind = b.kind AND a.ctid < b.ctid;
|
|
|
|
DROP INDEX IF EXISTS idx_request_integrations_default_per_kind;
|
|
DROP INDEX IF EXISTS idx_request_integrations_default4k_per_kind;
|
|
|
|
ALTER TABLE public.request_integrations DROP CONSTRAINT request_integrations_pkey;
|
|
ALTER TABLE public.request_integrations ADD PRIMARY KEY (kind);
|
|
ALTER TABLE public.request_integrations
|
|
DROP COLUMN IF EXISTS id,
|
|
DROP COLUMN IF EXISTS name,
|
|
DROP COLUMN IF EXISTS is_4k,
|
|
DROP COLUMN IF EXISTS is_default,
|
|
DROP COLUMN IF EXISTS is_default_4k,
|
|
DROP COLUMN IF EXISTS anime_enabled,
|
|
DROP COLUMN IF EXISTS anime_quality_profile_id,
|
|
DROP COLUMN IF EXISTS anime_root_folder,
|
|
DROP COLUMN IF EXISTS anime_tags;
|
|
-- +goose StatementEnd
|