server.js

// Import modules
import express from "express";
import bodyParser from "body-parser";
import pg from "pg";
import axios from "axios";
import dotenv from "dotenv";

dotenv.config();

const app = express();
const port = 3000;

// Connect to PostgreSQL
const db = new pg.Client({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});
db.connect();

// Middleware
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static("public"));
app.set("view engine", "ejs");

// Fetch cover from Open Library
async function fetchCover(title, author = "") {
  try {
    const response = await axios.get("https://openlibrary.org/search.json", {
      params: { title, author, limit: 1 },
    });
    if (response.data.docs.length > 0 && response.data.docs[0].cover_i) {
      return `https://covers.openlibrary.org/b/id/${response.data.docs[0].cover_i}-M.jpg`;
    }
    return null;
  } catch (err) {
    console.log(err);
    return null;
  }
}

// Update existing books without cover_url
async function updateExistingCovers() {
  try {
    const result = await db.query("SELECT * FROM books WHERE cover_url IS NULL OR cover_url=''");
    for (let book of result.rows) {
      const cover = await fetchCover(book.title, book.author);
      if (cover) {
        await db.query("UPDATE books SET cover_url=$1 WHERE id=$2", [cover, book.id]);
        console.log(`Updated cover for: ${book.title}`);
      }
    }
  } catch (err) {
    console.log(err);
  }
}
updateExistingCovers();

// Routes

// Home page with optional sort
app.get("/", async (req, res) => {
  try {
    let query = "SELECT * FROM books";
    if (req.query.sort === "rating") query += " ORDER BY rating DESC";
    else if (req.query.sort === "read_date") query += " ORDER BY read_date DESC";
    else query += " ORDER BY id ASC";

    const result = await db.query(query);
    res.render("index", { listTitle: "Today", listItems: result.rows });
  } catch (err) {
    console.log(err);
  }
});

// Add book
app.post("/add", async (req, res) => {
  const { title, author, rating, review, read_date } = req.body;
  try {
    const cover = await fetchCover(title, author);
    await db.query(
      "INSERT INTO books (title, author, rating, review, read_date, cover_url) VALUES ($1,$2,$3,$4,$5,$6)",
      [title, author, rating, review, read_date, cover]
    );
    res.redirect("/");
  } catch (err) {
    console.log(err);
  }
});

// Delete book
app.post("/delete/:id", async (req, res) => {
  const { id } = req.params;
  try {
    await db.query("DELETE FROM books WHERE id=$1", [id]);
    res.redirect("/");
  } catch (err) {
    console.log(err);
  }
});

// Edit book page
app.get("/edit/:id", async (req, res) => {
  const { id } = req.params;
  try {
    const result = await db.query("SELECT * FROM books WHERE id=$1", [id]);
    res.render("edit", { book: result.rows[0] });
  } catch (err) {
    console.log(err);
  }
});

// Update book
app.post("/edit/:id", async (req, res) => {
  const { id } = req.params;
  const { title, author, rating, review, read_date } = req.body;
  try {
    const cover = await fetchCover(title, author); // Update cover automatically
    await db.query(
      "UPDATE books SET title=$1, author=$2, rating=$3, review=$4, read_date=$5, cover_url=$6 WHERE id=$7",
      [title, author, rating, review, read_date, cover, id]
    );
    res.redirect("/");
  } catch (err) {
    console.log(err);
  }
});

// Start server
app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});