DROP TABLE IF EXISTS authorships;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS publishers;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS booktypes;

CREATE TABLE booktypes (
   book_type_id INTEGER,
   book_type VARCHAR(25),
   PRIMARY KEY (book_type_id)
);

CREATE TABLE categories (
   category_id INTEGER,
   category VARCHAR(25),
   PRIMARY KEY (category_id)
);

CREATE TABLE publishers (
   publisher_id INTEGER,
   publisher VARCHAR(30),
   speciality VARCHAR(50),
   country VARCHAR(25),
   PRIMARY KEY (publisher_id)
);

CREATE TABLE books (
   book_id INTEGER,
   title VARCHAR(50),
   publisher_id INTEGER,
   published_year INTEGER,
   purchase_price DECIMAL(6,2),
   category_id INTEGER,
   purchase_date DATE,
   pages INTEGER,
   book_type_id INTEGER,
   PRIMARY KEY (book_id),
   FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id),
   FOREIGN KEY (category_id) REFERENCES categories (category_id),
   FOREIGN KEY (book_type_id) REFERENCES booktypes (book_type_id)
);

CREATE TABLE authors (
   author_id INTEGER,
   first_name VARCHAR(25),
   last_name VARCHAR(25),
   pseudonym VARCHAR(25),
   PRIMARY KEY (author_id)
);

CREATE TABLE authorships (
   author_id INTEGER,
   book_id INTEGER,
   PRIMARY KEY (author_id, book_id),
   FOREIGN KEY (author_id) REFERENCES authors (author_id),
   FOREIGN KEY (book_id) REFERENCES books (book_id)
);