70 lines
3.0 KiB
SQL
70 lines
3.0 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
CREATE TABLE public.history_import_sources (
|
|
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name text NOT NULL,
|
|
source_type text NOT NULL,
|
|
base_url text NOT NULL,
|
|
system_id text,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
sort_order integer NOT NULL DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT history_import_sources_source_type_check CHECK (source_type IN ('emby'))
|
|
);
|
|
|
|
CREATE INDEX idx_history_import_sources_enabled_sort
|
|
ON public.history_import_sources (enabled, sort_order, id);
|
|
|
|
CREATE TABLE public.history_import_runs (
|
|
id text PRIMARY KEY,
|
|
user_id integer NOT NULL,
|
|
profile_id text NOT NULL,
|
|
source_type text NOT NULL,
|
|
connection_mode text NOT NULL,
|
|
status text NOT NULL,
|
|
fetched integer NOT NULL DEFAULT 0,
|
|
matched integer NOT NULL DEFAULT 0,
|
|
unmatched integer NOT NULL DEFAULT 0,
|
|
progress_updated integer NOT NULL DEFAULT 0,
|
|
history_created integer NOT NULL DEFAULT 0,
|
|
skipped integer NOT NULL DEFAULT 0,
|
|
warnings jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
unmatched_samples jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
error_message text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
started_at timestamp with time zone,
|
|
completed_at timestamp with time zone,
|
|
CONSTRAINT history_import_runs_source_type_check CHECK (source_type IN ('emby')),
|
|
CONSTRAINT history_import_runs_connection_mode_check CHECK (connection_mode IN ('connect', 'predefined', 'custom')),
|
|
CONSTRAINT history_import_runs_status_check CHECK (status IN ('queued', 'running', 'completed', 'failed')),
|
|
CONSTRAINT history_import_runs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE,
|
|
CONSTRAINT history_import_runs_profile_fkey FOREIGN KEY (user_id, profile_id) REFERENCES public.user_profiles(user_id, id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_history_import_runs_user_created
|
|
ON public.history_import_runs (user_id, created_at DESC);
|
|
|
|
CREATE INDEX idx_history_import_runs_profile_created
|
|
ON public.history_import_runs (profile_id, created_at DESC);
|
|
|
|
CREATE TABLE public.history_import_connect_sessions (
|
|
id text PRIMARY KEY,
|
|
user_id integer NOT NULL,
|
|
connect_user_id text NOT NULL,
|
|
connect_access_token text NOT NULL,
|
|
servers_json jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
consumed_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT history_import_connect_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_history_import_connect_sessions_user_expires
|
|
ON public.history_import_connect_sessions (user_id, expires_at DESC);
|
|
|
|
CREATE INDEX idx_history_import_connect_sessions_expiry
|
|
ON public.history_import_connect_sessions (expires_at);
|
|
-- +goose StatementEnd
|