-- Таблица авторов
CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    middle_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255)
);

-- Таблица жанров
CREATE TABLE genres (
    id BIGSERIAL PRIMARY KEY,
    genre VARCHAR(255) NOT NULL,
    section VARCHAR(255),
    subsection VARCHAR(255)
);

-- Таблица серий
CREATE TABLE series (
    id BIGSERIAL PRIMARY KEY,
    series VARCHAR(255) NOT NULL,
    search_series VARCHAR(255)
);

-- Таблица книг
CREATE TABLE books (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    file_name VARCHAR(255),
    path VARCHAR(255),
    file_size BIGINT,
    time TIMESTAMP,
    lang VARCHAR(3),
    doc_date VARCHAR(50),
    format VARCHAR(10),
    cat_type INTEGER NOT NULL DEFAULT 0,
    register_date TIMESTAMP,
    series_id BIGINT REFERENCES series(id)
);

-- Связующая таблица книг и авторов
CREATE TABLE book_author (
    id BIGSERIAL PRIMARY KEY,
    book_id BIGINT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    author_id BIGINT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    UNIQUE(book_id, author_id)
);

-- Связующая таблица книг и жанров
CREATE TABLE book_genre (
    id BIGSERIAL PRIMARY KEY,
    book_id BIGINT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    genre_id BIGINT NOT NULL REFERENCES genres(id) ON DELETE CASCADE,
    UNIQUE(book_id, genre_id)
);

-- Таблица пользователей
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(128) NOT NULL,
    email VARCHAR(254),
    first_name VARCHAR(30),
    last_name VARCHAR(150),
    is_active BOOLEAN DEFAULT true,
    is_staff BOOLEAN DEFAULT false,
    is_superuser BOOLEAN DEFAULT false,
    last_login TIMESTAMP,
    date_joined TIMESTAMP NOT NULL
);

-- Таблица групп
CREATE TABLE groups (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) UNIQUE NOT NULL
);

-- Связующая таблица пользователей и групп
CREATE TABLE user_groups (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    UNIQUE(user_id, group_id)
);

-- Создание индексов для улучшения производительности
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_series_id ON books(series_id);
CREATE INDEX idx_authors_full_name ON authors(full_name);
CREATE INDEX idx_genres_genre ON genres(genre);
CREATE INDEX idx_series_series ON series(series);
CREATE INDEX idx_book_author_book_id ON book_author(book_id);
CREATE INDEX idx_book_author_author_id ON book_author(author_id);
CREATE INDEX idx_book_genre_book_id ON book_genre(book_id);
CREATE INDEX idx_book_genre_genre_id ON book_genre(genre_id);