-- Create Authors table with additional columns
CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(255) NOT NULL,
    country_of_origin VARCHAR(255),
    date_of_birth DATE
    -- Add more columns as needed
);

-- Create Publishers table
CREATE TABLE Publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(255) NOT NULL
);

-- Create Books table
CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    book_title VARCHAR(255) NOT NULL,
    author_id INT,
    publisher_id INT,
    publication_year INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id),
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
);

-- Create Genres table
CREATE TABLE Genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(255) NOT NULL
);

-- Create BookGenres table to associate books with genres (many-to-many relationship)
CREATE TABLE BookGenres (
    book_id INT,
    genre_id INT,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (genre_id) REFERENCES Genres(genre_id),
    PRIMARY KEY (book_id, genre_id)
);


INSERT INTO Authors (author_name, country_of_origin, date_of_birth) VALUES
    ('Jane Austen', 'United Kingdom', '1775-12-16'),
    ('George Orwell', 'United Kingdom', '1903-06-25'),
    ('J.K. Rowling', 'United Kingdom', '1965-07-31'),
    ('Leo Tolstoy', 'Russia', '1828-09-09'),
    ('Agatha Christie', 'United Kingdom', '1890-09-15'),
    ('Fyodor Dostoevsky', 'Russia', '1821-11-11'),
    ('Mark Twain', 'United States', '1835-11-30'),
    ('Charles Dickens', 'United Kingdom', '1812-02-07'),
    ('Gabriel García Márquez', 'Colombia', '1927-03-06'),
    ('Hermann Hesse', 'Germany', '1877-07-02'),
    ('Harper Lee', 'United States', '1926-04-28'),
    ('Mikhail Bulgakov', 'Russia', '1891-05-15'),
    ('J.R.R. Tolkien', 'United Kingdom', '1892-01-03'),
    ('Virginia Woolf', 'United Kingdom', '1882-01-25'),
    ('Ernest Hemingway', 'United States', '1899-07-21'),
    ('Toni Morrison', 'United States', '1931-02-18'),
    ('H.P. Lovecraft', 'United States', '1890-08-20'),
    ('Albert Camus', 'France', '1913-11-07'),
    ('Kurt Vonnegut', 'United States', '1922-11-11'),
    ('Ayn Rand', 'United States', '1905-02-02');


INSERT INTO Genres (genre_name) VALUES
    ('Fiction'),
    ('Dystopian'),
    ('Fantasy'),
    ('Historical Fiction'),
    ('Mystery'),
    ('Psychological Thriller'),
    ('Adventure'),
    ('Social Novel'),
    ('Magical Realism'),
    ('Philosophical Fiction');

INSERT INTO Publishers (publisher_name) VALUES
    ('HarperCollins'),
    ('Secker & Warburg'),
    ('Bloomsbury'),
    ('Moscow News'),
    ('Dodd, Mead and Company'),
    ('The Russian Messenger'),
    ('Charles L. Webster and Company'),
    ('Chapman & Hall'),
    ('Penguin Random House'),
    ('S. Fischer Verlag'),
    ('J. B. Lippincott & Co.'),
    ('Harper & Brothers'),
    ('George Allen & Unwin'),
    ('Hogarth Press'),
    ('Scribner'),
    ('Alfred A. Knopf'),
    ('Arkham House'),
    ('Gallimard'),
    ('Delacorte Press'),
    ('Random House');

INSERT INTO Books (book_title, author_id, publisher_id, publication_year) VALUES
    ('Pride and Prejudice', 1, 1, 1813),
    ('1984', 2, 2, 1949),
    ('Harry Potter and the Sorcerer''s Stone', 3, 3, 1997),
    ('War and Peace', 4, 4, 1869),
    ('Murder on the Orient Express', 5, 5, 1934),
    ('Crime and Punishment', 6, 4, 1866),
    ('Adventures of Huckleberry Finn', 7, 6, 1884),
    ('Great Expectations', 8, 1, 1861),
    ('One Hundred Years of Solitude', 9, 7, 1967),
    ('Siddhartha', 10, 8, 1922),
    ('To Kill a Mockingbird', 11, 9, 1960),
    ('The Master and Margarita', 12, 10, 1967),
    ('The Hobbit', 13, 11, 1937),
    ('Mrs. Dalloway', 14, 1, 1925),
    ('The Old Man and the Sea', 15, 12, 1952),
    ('Beloved', 16, 13, 1987),
    ('The Call of Cthulhu', 17, 14, 1926),
    ('The Stranger', 18, 15, 1942),
    ('Slaughterhouse-Five', 19, 16, 1969),
    ('Atlas Shrugged', 20, 17, 1957);