-- Create Authors table
CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(255) NOT NULL,
    country_of_origin VARCHAR(255),
    date_of_birth DATE
	/*,FOREIGN KEY (author_id) REFERENCES Books(author_id)*/
);


CREATE TABLE Genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(255) NOT NULL UNIQUE
	/*,FOREIGN KEY (genres) REFERENCES Books(genre_name)*/
);


CREATE TABLE Publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(255) NOT NULL
	/*,FOREIGN KEY (publisher_id) REFERENCES Books(publisher_id)*/
);

-- Create Books table with the genres column and foreign key reference
CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    book_title VARCHAR(255) NOT NULL,
    author_id INT,
    publisher_id INT,
    publication_year INT,
    genres VARCHAR(255)
);

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'),
	('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');


-- Inserting values into the Books table with a single genre
INSERT INTO Books (book_title, author_id, publisher_id, publication_year, genres) VALUES
    ('Pride and Prejudice', 1, 1, 1813, 'Fiction'),
    ('1984', 2, 2, 1949, 'Dystopian'),
    ('Harry Potter and the Sorcerer''s Stone', 3, 3, 1997, 'Fantasy'),
    ('War and Peace', 4, 4, 1869, 'Historical Fiction'),
    ('Murder on the Orient Express', 5, 5, 1934, 'Crimi'),
	('The Murder of Roger Ackroyd', 5, 5, 1926, 'Crimi'),
    ('And Then There Were None', 5, 5, 1939, 'Crimi'),
    ('The ABC Murders', 5, 5, 1936, 'Crimi'),
    ('Death on the Nile', 5, 5, 1937, 'Crimi'),
    ('The Pale Horse', 5, 5, 1961, 'Crimi'),
	('The Hobbit', 13, 11, 1937, 'Fantasy'),
    ('Harry Potter and the Sorcerer''s Stone', 3, 3, 1997, 'Fantasy'),
	('Brave New World', NULL, 19, 1932, 'Dystopian'),
    ('1984', NULL, 2, 1949, 'Dystopian');



INSERT INTO Genres (genre_name) VALUES
    ('Fiction'),
    ('Dystopian'),
    ('Fantasy'),
    ('Historical Fiction'),
    ('Mystery'),
	('Crimi');


INSERT INTO Publishers (publisher_name) VALUES
    ('HarperCollins'),
    ('Secker & Warburg'),
    ('Bloomsbury'),
    ('Moscow News'),
    ('Dodd, Mead and Company');
/*
ALTER TABLE books
ADD constraint fk
FOREIGN KEY (author_id) REFERENCES Authors(author_id);

ALTER TABLE books
ADD constraint fl
FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id);

ALTER TABLE books
ADD constraint fk
fk	FOREIGN KEY (genres) REFERENCES Genres(genre_name);
*/