CREATE DATABASE IF NOT EXISTS OpenLibraryData;
USE OpenLibraryData;

CREATE TABLE IF NOT EXISTS Authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    birthdate DATE,
    deathdate DATE,
    bio TEXT
);

CREATE TABLE IF NOT EXISTS Works (
    work_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    genre VARCHAR(100),
    description TEXT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

CREATE TABLE IF NOT EXISTS Editions (
    edition_id INT AUTO_INCREMENT PRIMARY KEY,
    work_id INT,
    isbn VARCHAR(20),
    publish_date DATE,
    publisher VARCHAR(255),
    cover_image VARCHAR(255),
    FOREIGN KEY (work_id) REFERENCES Works(work_id)
);

CREATE TABLE IF NOT EXISTS Types (
    type_id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS Ratings (
    rating_id INT AUTO_INCREMENT PRIMARY KEY,
    work_id INT,
    rating FLOAT,
    number_of_ratings INT,
    FOREIGN KEY (work_id) REFERENCES Works(work_id)
);
