schema.sql

DROP TABLE IF EXISTS m2m_books_authors CASCADE;
DROP TABLE IF EXISTS subscriptions CASCADE;
DROP TABLE IF EXISTS m2m_books_genres CASCADE;
DROP TABLE IF EXISTS genres CASCADE;
DROP TABLE IF EXISTS books1 CASCADE;
DROP TABLE IF EXISTS books CASCADE;
DROP TABLE IF EXISTS subscribers CASCADE;
DROP TABLE IF EXISTS authors CASCADE;

CREATE TABLE IF NOT EXISTS books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    "year" INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    age_restrictions BOOLEAN
);

CREATE INDEX IF NOT EXISTS idx_books_title ON books USING BTREE (title);

CREATE TABLE IF NOT EXISTS books1 (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    "year" INTEGER NOT NULL,
    quantity INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_books1_title ON books1 USING HASH (title);

CREATE TABLE IF NOT EXISTS genres (
    id SERIAL PRIMARY KEY,
    "name" VARCHAR(255) NOT NULL UNIQUE,
    description VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS m2m_books_genres (
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (genre_id) REFERENCES genres(id)
);

CREATE TABLE IF NOT EXISTS authors (
    id SERIAL PRIMARY KEY,
    name_authors VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS subscribers (
    id SERIAL PRIMARY KEY,
    name_subscribers VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id SERIAL PRIMARY KEY,
    subscriber_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    start_date_sub DATE NOT NULL,
    finish_date_sub DATE,
    is_active BOOLEAN NOT NULL,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

CREATE TABLE IF NOT EXISTS m2m_books_authors (
    id SERIAL PRIMARY KEY,
    book_id INTEGER NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (author_id) REFERENCES authors(id)
);