93 lines
3.5 KiB
SQL
93 lines
3.5 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_idx ON users (lower(email));
|
|
|
|
CREATE TABLE IF NOT EXISTS galleries (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
title TEXT NOT NULL,
|
|
client_name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
password_hash TEXT,
|
|
downloads_enabled INTEGER NOT NULL DEFAULT 1,
|
|
favorites_enabled INTEGER NOT NULL DEFAULT 1,
|
|
download_all_enabled INTEGER NOT NULL DEFAULT 1,
|
|
watermark_enabled INTEGER NOT NULL DEFAULT 0,
|
|
expires_at TEXT,
|
|
cover_media_id TEXT,
|
|
theme_config TEXT NOT NULL DEFAULT '{}',
|
|
branding_config TEXT NOT NULL DEFAULT '{}',
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
published_at TEXT,
|
|
CONSTRAINT galleries_status_check CHECK (status IN ('draft', 'published', 'archived'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS galleries_user_id_created_at_idx ON galleries (user_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS media (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
gallery_id TEXT NOT NULL REFERENCES galleries(id) ON DELETE CASCADE,
|
|
original_filename TEXT NOT NULL,
|
|
mime_type TEXT NOT NULL,
|
|
file_size INTEGER NOT NULL DEFAULT 0,
|
|
storage_key TEXT NOT NULL,
|
|
external_url TEXT,
|
|
thumbnail_key TEXT,
|
|
preview_key TEXT,
|
|
processing_status TEXT NOT NULL DEFAULT 'UPLOADING',
|
|
processing_error TEXT,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
duration_seconds REAL,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT media_processing_status_check CHECK (processing_status IN ('UPLOADING', 'PROCESSING', 'READY', 'FAILED'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS media_gallery_sort_order_idx ON media (gallery_id, sort_order, id);
|
|
|
|
CREATE TABLE IF NOT EXISTS favorites (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
gallery_id TEXT NOT NULL REFERENCES galleries(id) ON DELETE CASCADE,
|
|
media_id TEXT NOT NULL REFERENCES media(id) ON DELETE CASCADE,
|
|
visitor_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (gallery_id, media_id, visitor_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS downloads (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
gallery_id TEXT NOT NULL REFERENCES galleries(id) ON DELETE CASCADE,
|
|
media_id TEXT REFERENCES media(id) ON DELETE SET NULL,
|
|
visitor_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS downloads_gallery_created_at_idx ON downloads (gallery_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS download_jobs (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
gallery_id TEXT NOT NULL REFERENCES galleries(id) ON DELETE CASCADE,
|
|
visitor_id TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'QUEUED',
|
|
storage_key TEXT,
|
|
error TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TEXT,
|
|
CONSTRAINT download_jobs_status_check CHECK (status IN ('QUEUED', 'PROCESSING', 'READY', 'FAILED'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS download_jobs_gallery_visitor_idx ON download_jobs (gallery_id, visitor_id, created_at DESC);
|