{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: ['Southern Gothic', 'Bildungsroman']
  }

{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: ['Southern Gothic', 'Bildungsroman']
  }

{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Southern Gothic'
  },
  {
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Bildungsroman'
  }

{
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Southern Gothic'
  },
  {
    bookId: 2,
    title: 'To Kill a Mockingbird',
    author: 'Harper Lee',
    synopsis: 'Set in small-town Alabama, the novel is a bildungsroman, or coming-of-age story, and chronicles the childhood of Scout and Jem Finch as their father Atticus defends a Black man falsely accused of rape.',
    datePublished: '1960-07-11',
    rating: '9.4',
    coverImage: null,
    genres: 'Bildungsroman'
  }

const getAllBooks = async () => {
    const result = await db.select({
        bookId: Books.bookId,
        title: Books.title,
        author: Books.author,
        synopsis: Books.synopsis,
        datePublished: Books.datePublished,
        rating: Books.rating,
        coverImage: Books.coverImage,
        genres: Genres.genre,
      })
      .from(Books)
      .innerJoin(BookGenres, eq(Books.bookId, BookGenres.bookId))
      .innerJoin(Genres, eq(BookGenres.genreId, Genres.genreId));

    console.log(result);

    pool.end();
}

const getAllBooks = async () => {
    const result = await db.select({
        bookId: Books.bookId,
        title: Books.title,
        author: Books.author,
        synopsis: Books.synopsis,
        datePublished: Books.datePublished,
        rating: Books.rating,
        coverImage: Books.coverImage,
        genres: Genres.genre,
      })
      .from(Books)
      .innerJoin(BookGenres, eq(Books.bookId, BookGenres.bookId))
      .innerJoin(Genres, eq(BookGenres.genreId, Genres.genreId));

    console.log(result);

    pool.end();
}

CREATE TABLE books (
    book_id         SERIAL PRIMARY KEY,
    title             VARCHAR(100) UNIQUE NOT NULL,
    author             VARCHAR(50) NOT NULL,
    synopsis         TEXT NOT NULL,
    date_published     DATE DEFAULT CURRENT_DATE NOT NULL,
    rating             DECIMAL(3, 1) CHECK(rating >= 0.0 AND rating <= 10.0) DEFAULT 0.0 NOT NULL,
    cover_image     VARCHAR(255) -- will need a default book cover
);
        
CREATE TABLE book_stories (
    book_id     INT     REFERENCES books(book_id) ON DELETE CASCADE,
    story_id     SERIAL     PRIMARY KEY,
    story         TEXT     NOT NULL
);

CREATE TABLE genres (
    genre_id     SERIAL PRIMARY KEY,
    genre         VARCHAR(40) UNIQUE NOT NULL
);


CREATE TABLE book_genres (
    book_id     INT REFERENCES books(book_id) ON DELETE CASCADE,
    genre_id     INT REFERENCES genres(genre_id) ON DELETE CASCADE,
    PRIMARY KEY (genre_id, book_id)
);

CREATE TABLE books (
    book_id         SERIAL PRIMARY KEY,
    title             VARCHAR(100) UNIQUE NOT NULL,
    author             VARCHAR(50) NOT NULL,
    synopsis         TEXT NOT NULL,
    date_published     DATE DEFAULT CURRENT_DATE NOT NULL,
    rating             DECIMAL(3, 1) CHECK(rating >= 0.0 AND rating <= 10.0) DEFAULT 0.0 NOT NULL,
    cover_image     VARCHAR(255) -- will need a default book cover
);
        
CREATE TABLE book_stories (
    book_id     INT     REFERENCES books(book_id) ON DELETE CASCADE,
    story_id     SERIAL     PRIMARY KEY,
    story         TEXT     NOT NULL
);

CREATE TABLE genres (
    genre_id     SERIAL PRIMARY KEY,
    genre         VARCHAR(40) UNIQUE NOT NULL
);


CREATE TABLE book_genres (
    book_id     INT REFERENCES books(book_id) ON DELETE CASCADE,
    genre_id     INT REFERENCES genres(genre_id) ON DELETE CASCADE,
    PRIMARY KEY (genre_id, book_id)
);