---

CREATE TABLE genres (
    id   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name TEXT    NOT NULL UNIQUE
);

-- Связующая таблица many-to-many: книги <-> жанры
CREATE TABLE book_genres (
    book_id  INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,

    PRIMARY KEY (book_id, genre_id),

    FOREIGN KEY (book_id)  REFERENCES books(id)  ON DELETE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genres(id) ON DELETE CASCADE
);

-- Таблица издательств
CREATE TABLE publishers (
    id      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name    TEXT    NOT NULL,
    city    TEXT,
    founded INTEGER
);

-- Связь one-to-many: у книги одно издательство, у издательства много книг
-- Добавляем колонку в books через ALTER
ALTER TABLE books ADD COLUMN publisher_id INTEGER
    REFERENCES publishers(id) ON DELETE SET NULL;