-- yt - A fully featured command line YouTube client
--
-- Copyright (C) 2024 Benedikt Peetz <benedikt.peetz@b-peetz.de>
-- SPDX-License-Identifier: GPL-3.0-or-later
--
-- This file is part of Yt.
--
-- You should have received a copy of the License along with this program.
-- If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>.

-- All tables should be declared STRICT, as I actually like to have types checking (and a
-- db that doesn't lie to me).

-- Keep this table in sync with the `Video` structure
CREATE TABLE IF NOT EXISTS videos (
    cache_path                  TEXT UNIQUE                    CHECK (CASE WHEN cache_path IS NOT NULL THEN
                                                                            status == 2
                                                                      ELSE
                                                                            1
                                                                      END),
    description                 TEXT,
    duration                    REAL,
    extractor_hash              TEXT UNIQUE NOT NULL PRIMARY KEY,
    last_status_change          INTEGER     NOT NULL,
    parent_subscription_name    TEXT,
    priority                    INTEGER     NOT NULL DEFAULT 0,
    publish_date                INTEGER,
    status                      INTEGER     NOT NULL DEFAULT 0 CHECK (status IN (0, 1, 2, 3, 4, 5) AND
                                                                      CASE WHEN status == 2 THEN
                                                                           cache_path IS NOT NULL
                                                                      ELSE
                                                                           1
                                                                      END AND
                                                                      CASE WHEN status != 2 THEN
                                                                           cache_path IS NULL
                                                                      ELSE
                                                                           1
                                                                      END),
    status_change               INTEGER     NOT NULL DEFAULT 0 CHECK (status_change IN (0, 1)),
    thumbnail_url               TEXT,
    title                       TEXT        NOT NULL,
    url                         TEXT UNIQUE NOT NULL
) STRICT;

-- Store additional metadata for the videos marked to be watched
CREATE TABLE IF NOT EXISTS video_options (
    extractor_hash              TEXT UNIQUE NOT NULL PRIMARY KEY,
    subtitle_langs              TEXT        NOT NULL,
    playback_speed              REAL        NOT NULL,
    FOREIGN KEY(extractor_hash) REFERENCES videos (extractor_hash)
) STRICT;

-- Store subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
    name              TEXT UNIQUE NOT NULL PRIMARY KEY,
    url               TEXT        NOT NULL
) STRICT;