93 lines
3.3 KiB
SQL
93 lines
3.3 KiB
SQL
-- +goose Up
|
|
CREATE TABLE download (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
source_type TEXT NOT NULL, -- magnet | torrent | url
|
|
source_ref TEXT NOT NULL,
|
|
context TEXT NOT NULL DEFAULT '',
|
|
infohash TEXT,
|
|
idempotency_key TEXT,
|
|
state TEXT NOT NULL,
|
|
error_code TEXT,
|
|
error_msg TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_download_idempotency_key
|
|
ON download (idempotency_key)
|
|
WHERE idempotency_key IS NOT NULL;
|
|
|
|
CREATE INDEX idx_download_state ON download (state);
|
|
|
|
CREATE TABLE recognition (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
download_id INTEGER NOT NULL REFERENCES download (id) ON DELETE CASCADE,
|
|
attempt_no INTEGER NOT NULL DEFAULT 1,
|
|
is_current INTEGER NOT NULL DEFAULT 1, -- 0/1
|
|
media_type TEXT, -- movie | series
|
|
title TEXT,
|
|
original_title TEXT,
|
|
year INTEGER,
|
|
provider TEXT, -- tmdb | tvdb | none
|
|
provider_id TEXT,
|
|
confidence REAL,
|
|
reasons TEXT NOT NULL DEFAULT '[]', -- JSON: причины «не авто»
|
|
raw_llm TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_recognition_download ON recognition (download_id);
|
|
|
|
CREATE TABLE hint (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
download_id INTEGER NOT NULL REFERENCES download (id) ON DELETE CASCADE,
|
|
text TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_hint_download ON hint (download_id);
|
|
|
|
CREATE TABLE override (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
download_id INTEGER NOT NULL REFERENCES download (id) ON DELETE CASCADE,
|
|
field TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE (download_id, field)
|
|
);
|
|
|
|
CREATE TABLE metadata_candidate (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
recognition_id INTEGER NOT NULL REFERENCES recognition (id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL,
|
|
provider_id TEXT NOT NULL,
|
|
title TEXT,
|
|
year INTEGER,
|
|
chosen INTEGER NOT NULL DEFAULT 0, -- 0/1
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_candidate_recognition ON metadata_candidate (recognition_id);
|
|
|
|
CREATE TABLE file_link (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
download_id INTEGER NOT NULL REFERENCES download (id) ON DELETE CASCADE,
|
|
apply_batch_id TEXT NOT NULL,
|
|
src_path TEXT NOT NULL,
|
|
dst_path TEXT NOT NULL,
|
|
kind TEXT NOT NULL, -- video | subtitle | ...
|
|
status TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX idx_file_link_download ON file_link (download_id);
|
|
CREATE INDEX idx_file_link_batch ON file_link (apply_batch_id);
|
|
|
|
-- +goose Down
|
|
DROP TABLE file_link;
|
|
DROP TABLE metadata_candidate;
|
|
DROP TABLE override;
|
|
DROP TABLE hint;
|
|
DROP TABLE recognition;
|
|
DROP TABLE download;
|