-- ============================================================
-- Схема базы данных для Домашней библиотеки
-- PostgreSQL 14+
-- ============================================================

-- Авторы
CREATE TABLE IF NOT EXISTS authors (
    id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL UNIQUE,           -- "Пелевин,Виктор,Олегович"
    last_name TEXT NOT NULL,                  -- "Пелевин"
    first_name TEXT,                          -- "Виктор"
    middle_name TEXT,                         -- "Олегович"
    name_hash TEXT UNIQUE,                    -- MD5 от full_name (lowercase)
    first_letter TEXT NOT NULL,               -- первая буква фамилии (lowercase)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Серии
CREATE TABLE IF NOT EXISTS series (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,                -- "Плоский мир"
    name_hash TEXT UNIQUE,                    -- MD5 от name (lowercase)
    first_letter TEXT,                        -- первая буква названия
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Жанры (справочник из genres.xml + кастомные из inpx)
CREATE TABLE IF NOT EXISTS genres (
    id SERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,                -- "sf_history", "popadanec"
    title_ru TEXT,                            -- "Альтернативная история"
    title_en TEXT,                            -- "Alternative history"
    parent_code TEXT,                         -- "sf" — родительский жанр
    root_code TEXT,                           -- "sf" — корневой жанр
    is_root BOOLEAN DEFAULT FALSE,            -- TRUE для корневых
    is_custom BOOLEAN DEFAULT FALSE,          -- TRUE если добавлен из inpx
    genre_alt TEXT[],                         -- альтернативные коды
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Книги
CREATE TABLE IF NOT EXISTS books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    title_hash TEXT,                          -- MD5 от title (lowercase)
    internal_id INTEGER,                      -- ID книги в источнике (LIBID)
    internal_id2 INTEGER,                     -- Дублирующий ID
    file_size INTEGER,
    format TEXT DEFAULT 'fb2',
    added_date DATE,                          -- Дата добавления в источник
    language TEXT DEFAULT 'ru',
    source TEXT NOT NULL,                     -- 'lib.rus.ec', 'flibusta' и т.д.
    series_id INTEGER REFERENCES series(id) ON DELETE SET NULL,
    series_number NUMERIC(10,2),              -- Номер в серии
    fts tsvector GENERATED ALWAYS AS (
        to_tsvector('russian', coalesce(title,''))
    ) STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Связь книг и авторов (многие-ко-многим)
CREATE TABLE IF NOT EXISTS book_authors (
    book_id INTEGER REFERENCES books(id) ON DELETE CASCADE,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE,
    position INTEGER,                         -- Порядок автора (1 = главный)
    PRIMARY KEY (book_id, author_id)
);

-- Связь книг и жанров (многие-ко-многим)
CREATE TABLE IF NOT EXISTS book_genres (
    book_id INTEGER REFERENCES books(id) ON DELETE CASCADE,
    genre_id INTEGER REFERENCES genres(id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, genre_id)
);