-- Крок 1: Оголошуємо ENUM-тип до будь-яких таблиць
CREATE TYPE file_format_enum AS ENUM ('mp3', 'ogg', 'wav', 'm4b', 'aac', 'flac');

-- authors: незалежна сутність, PK — UUID
-- Відповідає 3НФ: всі атрибути залежать лише від id
CREATE TABLE authors (
    PRIMARY KEY (id),
    id          UUID            NOT NULL,  -- сурогатний ключ
    first_name  VARCHAR(64)     NOT NULL,  -- ім'я обов'язкове
    last_name   VARCHAR(64)     NOT NULL,  -- прізвище обов'язкове
    bio         TEXT,                      -- NULL: біографія необов'язкова
    image_path  VARCHAR(2048)              -- NULL: фото необов'язкове
);

-- genres: статичний довідник жанрів
-- UNIQUE (name): назва жанру унікальна в системі
CREATE TABLE genres (
    PRIMARY KEY (id),
    id          UUID         NOT NULL,
    name        VARCHAR(64)  NOT NULL,
                CONSTRAINT genres_name_key
                    UNIQUE (name),          -- унікальна назва
    description TEXT                        -- NULL: опис необов'язковий
);

-- users: основна сутність; username унікальний та не порожній
CREATE TABLE users (
    PRIMARY KEY (id),
    id            UUID          NOT NULL,
    username      VARCHAR(64)   NOT NULL,
                  CONSTRAINT users_username_key
                      UNIQUE (username),    -- логін унікальний
                  CONSTRAINT users_username_not_empty_check
                       CHECK (length(trim(username)) > 0),  -- не порожній рядок
    password_hash VARCHAR(128)  NOT NULL,   -- зберігаємо лише хеш, ніколи пароль
    email         VARCHAR(376),             -- NULL: email необов'язковий
    avatar_path   VARCHAR(2048)             -- NULL
);

CREATE INDEX users_email_idx ON users(email);  -- прискорення пошуку за email

-- audiobooks: залежить від authors та genres (FK)
-- CHECK: duration > 0 і release_year у допустимому діапазоні
CREATE TABLE audiobooks (
    PRIMARY KEY (id),
    id               UUID         NOT NULL,

    author_id        UUID         NOT NULL,
                      CONSTRAINT audiobooks_author_id_authors_id_fkey
                     FOREIGN KEY (author_id)
                      REFERENCES authors(id)
                       ON DELETE CASCADE,   -- видалення автора → видалення книг

    genre_id         UUID         NOT NULL,
                      CONSTRAINT audiobooks_genre_id_genres_id_fkey
                     FOREIGN KEY (genre_id)
                      REFERENCES genres(id)
                       ON DELETE CASCADE,

    title            VARCHAR(255) NOT NULL,
    duration         INTEGER      NOT NULL,
                     CONSTRAINT audiobooks_duration_positive_check
                          CHECK (duration > 0),  -- тривалість > 0 секунд
    release_year     INTEGER      NOT NULL,
                     CONSTRAINT audiobooks_release_year_check
                          CHECK (release_year >= 1900
                             AND release_year <= EXTRACT(YEAR FROM CURRENT_DATE) + 1),
    description      TEXT,
    cover_image_path VARCHAR(2048)
);

-- Індекси за FK-полями: прискорюють JOIN та WHERE
CREATE INDEX audiobooks_author_id_idx ON audiobooks(author_id);
CREATE INDEX audiobooks_genre_id_idx  ON audiobooks(genre_id);

-- collections: слабка сутність, залежить від users
CREATE TABLE collections (
    PRIMARY KEY (id),
    id         UUID         NOT NULL,
    user_id    UUID,                        -- NULL допускається (проєктне рішення)
                CONSTRAINT collections_user_id_users_id_fkey
               FOREIGN KEY (user_id)
                REFERENCES users(id)
                 ON DELETE CASCADE,
    name       VARCHAR(128) NOT NULL,
               CONSTRAINT collections_name_not_empty_check
                    CHECK (length(trim(name)) > 0),
    created_at TIMESTAMP
);

-- audiobook_collection: junction-таблиця M:N
-- Складений PK гарантує: книга не може бути в колекції двічі
CREATE TABLE audiobook_collection (
    PRIMARY KEY (collection_id, audiobook_id),
    collection_id UUID NOT NULL,
                   CONSTRAINT audiobook_collection_collection_id_fkey
                  FOREIGN KEY (collection_id)
                   REFERENCES collections(id) ON DELETE CASCADE,
    audiobook_id  UUID NOT NULL,
                   CONSTRAINT audiobook_collection_audiobook_id_fkey
                  FOREIGN KEY (audiobook_id)
                   REFERENCES audiobooks(id) ON DELETE CASCADE
);

-- audiobook_files: слабка сутність, залежить від audiobooks
CREATE TABLE audiobook_files (
    PRIMARY KEY (id),
    id           UUID             NOT NULL,
    audiobook_id UUID             NOT NULL,
                  CONSTRAINT audiobook_files_audiobook_id_fkey
                 FOREIGN KEY (audiobook_id)
                  REFERENCES audiobooks(id) ON DELETE CASCADE,
    file_path    VARCHAR(2048)    NOT NULL,
                 CONSTRAINT audiobook_files_file_path_not_empty_check
                      CHECK (length(trim(file_path)) > 0),
    format       file_format_enum NOT NULL,  -- тільки значення з ENUM
    size         INTEGER,
                 CONSTRAINT audiobook_files_size_positive_check
                      CHECK (size IS NULL OR size > 0)   -- NULL або > 0
);

CREATE INDEX audiobook_files_audiobook_id_idx ON audiobook_files(audiobook_id);

-- listening_progresses: зв'язок з атрибутами (User ↔ Audiobook)
CREATE TABLE listening_progresses (
    PRIMARY KEY (id),
    id           UUID      NOT NULL,
    user_id      UUID,
                  CONSTRAINT listening_progresses_user_id_fkey
                 FOREIGN KEY (user_id)
                  REFERENCES users(id) ON DELETE CASCADE,
    audiobook_id UUID      NOT NULL,
                  CONSTRAINT listening_progresses_audiobook_id_fkey
                 FOREIGN KEY (audiobook_id)
                  REFERENCES audiobooks(id) ON DELETE CASCADE,
    position     INTEGER   NOT NULL,
                 CONSTRAINT listening_progresses_position_positive_check
                      CHECK (position > 0),          -- позиція у секундах > 0
    last_listened TIMESTAMP                          -- NULL: ще не слухали
);

CREATE INDEX listening_progresses_user_id_idx      ON listening_progresses(user_id);
CREATE INDEX listening_progresses_audiobook_id_idx ON listening_progresses(audiobook_id);