const express = require('express')
const router  = express.Router()
const pool    = require('../config/db')

// ── GET /api/books ────────────────────────────────────────────────────────

router.get('/', async (req, res, next) => {
  try {
    const { title, genre, authorId, isAvailable, publishedYearFrom, publishedYearTo, priceMax } = req.query

    let sql = `
      SELECT
        b.Id               AS id,
        b.Title            AS title,
        b.ISBN             AS isbn,
        b.Genre            AS genre,
        b.PublishedYear    AS publishedYear,
        b.Price            AS price,
        b.TotalCopies      AS totalCopies,
        b.AvailableCopies  AS availableCopies,
        b.Description      AS description,
        b.CreatedAt        AS createdAt,
        b.AuthorId         AS authorId,
        CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName
      FROM Books b
      JOIN Authors a ON a.Id = b.AuthorId
      WHERE 1=1
    `
    const params = []

    if (title) {
      sql += ` AND b.Title LIKE ?`
      params.push(`%${title}%`)
    }

    if (genre) {
      sql += ` AND b.Genre LIKE ?`
      params.push(`%${genre}%`)
    }

    if (authorId) {
      sql += ` AND b.AuthorId = ?`
      params.push(authorId)
    }

    if (isAvailable === 'true') {
      sql += ` AND b.AvailableCopies > 0`
    } else if (isAvailable === 'false') {
      sql += ` AND b.AvailableCopies = 0`
    }

    if (publishedYearFrom) {
      sql += ` AND b.PublishedYear >= ?`
      params.push(parseInt(publishedYearFrom))
    }

    if (publishedYearTo) {
      sql += ` AND b.PublishedYear <= ?`
      params.push(parseInt(publishedYearTo))
    }

    if (priceMax) {
      sql += ` AND b.Price <= ?`
      params.push(parseFloat(priceMax))
    }

    sql += ` ORDER BY b.Title`

    const [rows] = await pool.execute(sql, params)

    const books = rows.map(b => ({
      ...b,
      price:      Number(b.price),
      isAvailable: b.availableCopies > 0,
    }))

    res.json(books)
  } catch (err) { next(err) }
})

// ── GET /api/books/:id ────────────────────────────────────────────────────

router.get('/:id', async (req, res, next) => {
  try {
    const [rows] = await pool.execute(`
      SELECT
        b.Id               AS id,
        b.Title            AS title,
        b.ISBN             AS isbn,
        b.Genre            AS genre,
        b.PublishedYear    AS publishedYear,
        b.Price            AS price,
        b.TotalCopies      AS totalCopies,
        b.AvailableCopies  AS availableCopies,
        b.Description      AS description,
        b.CreatedAt        AS createdAt,
        b.AuthorId         AS authorId,
        CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName
      FROM Books b
      JOIN Authors a ON a.Id = b.AuthorId
      WHERE b.Id = ?
    `, [req.params.id])

    if (rows.length === 0)
      return res.status(404).json({ message: `Book with ID ${req.params.id} not found.` })

    const b = rows[0]
    res.json({ ...b, price: Number(b.price), isAvailable: b.availableCopies > 0 })
  } catch (err) { next(err) }
})

// ── POST /api/books ───────────────────────────────────────────────────────

router.post('/', async (req, res, next) => {
  try {
    const { title, isbn, genre, publishedYear, price, totalCopies, description, authorId } = req.body

    // Validate required fields
    if (!title?.trim())  return res.status(400).json({ message: 'title is required.' })
    if (!isbn?.trim())   return res.status(400).json({ message: 'isbn is required.' })
    if (!authorId)       return res.status(400).json({ message: 'authorId is required.' })

    // Validate author exists
    const [author] = await pool.execute(`SELECT Id FROM Authors WHERE Id = ?`, [authorId])
    if (author.length === 0)
      return res.status(400).json({ message: `Author with ID ${authorId} does not exist.` })

    // Validate ISBN uniqueness
    const [isbnCheck] = await pool.execute(`SELECT Id FROM Books WHERE ISBN = ?`, [isbn.trim()])
    if (isbnCheck.length > 0)
      return res.status(409).json({ message: `A book with ISBN '${isbn}' already exists.` })

    const copies = parseInt(totalCopies) || 1

    const [result] = await pool.execute(`
      INSERT INTO Books
        (Title, ISBN, Genre, PublishedYear, Price, TotalCopies, AvailableCopies, Description, AuthorId, CreatedAt)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
    `, [
      title.trim(),
      isbn.trim(),
      genre || null,
      parseInt(publishedYear),
      parseFloat(price),
      copies,
      copies,   // AvailableCopies starts equal to TotalCopies
      description || null,
      parseInt(authorId),
    ])

    const [rows] = await pool.execute(`
      SELECT
        b.Id AS id, b.Title AS title, b.ISBN AS isbn, b.Genre AS genre,
        b.PublishedYear AS publishedYear, b.Price AS price,
        b.TotalCopies AS totalCopies, b.AvailableCopies AS availableCopies,
        b.Description AS description, b.CreatedAt AS createdAt,
        b.AuthorId AS authorId,
        CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName
      FROM Books b
      JOIN Authors a ON a.Id = b.AuthorId
      WHERE b.Id = ?
    `, [result.insertId])

    const b = rows[0]
    res.status(201).json({ ...b, price: Number(b.price), isAvailable: b.availableCopies > 0 })
  } catch (err) { next(err) }
})

// ── PUT /api/books/:id ────────────────────────────────────────────────────

router.put('/:id', async (req, res, next) => {
  try {
    const { title, isbn, genre, publishedYear, price, totalCopies, availableCopies, description, authorId } = req.body

    // Validate required fields
    if (!title?.trim())  return res.status(400).json({ message: 'title is required.' })
    if (!isbn?.trim())   return res.status(400).json({ message: 'isbn is required.' })
    if (!authorId)       return res.status(400).json({ message: 'authorId is required.' })

    // Validate book exists
    const [exists] = await pool.execute(`SELECT Id FROM Books WHERE Id = ?`, [req.params.id])
    if (exists.length === 0)
      return res.status(404).json({ message: `Book with ID ${req.params.id} not found.` })

    // Validate author exists
    const [author] = await pool.execute(`SELECT Id FROM Authors WHERE Id = ?`, [authorId])
    if (author.length === 0)
      return res.status(400).json({ message: `Author with ID ${authorId} does not exist.` })

    // Validate ISBN uniqueness excluding this book
    const [isbnCheck] = await pool.execute(
      `SELECT Id FROM Books WHERE ISBN = ? AND Id != ?`,
      [isbn.trim(), req.params.id]
    )
    if (isbnCheck.length > 0)
      return res.status(409).json({ message: `Another book with ISBN '${isbn}' already exists.` })

    // Validate available copies does not exceed total copies
    if (parseInt(availableCopies) > parseInt(totalCopies))
      return res.status(400).json({ message: 'Available copies cannot exceed total copies.' })

    await pool.execute(`
      UPDATE Books
      SET Title = ?, ISBN = ?, Genre = ?, PublishedYear = ?, Price = ?,
          TotalCopies = ?, AvailableCopies = ?, Description = ?, AuthorId = ?
      WHERE Id = ?
    `, [
      title.trim(),
      isbn.trim(),
      genre || null,
      parseInt(publishedYear),
      parseFloat(price),
      parseInt(totalCopies),
      parseInt(availableCopies),
      description || null,
      parseInt(authorId),
      req.params.id,
    ])

    const [rows] = await pool.execute(`
      SELECT
        b.Id AS id, b.Title AS title, b.ISBN AS isbn, b.Genre AS genre,
        b.PublishedYear AS publishedYear, b.Price AS price,
        b.TotalCopies AS totalCopies, b.AvailableCopies AS availableCopies,
        b.Description AS description, b.CreatedAt AS createdAt,
        b.AuthorId AS authorId,
        CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName
      FROM Books b
      JOIN Authors a ON a.Id = b.AuthorId
      WHERE b.Id = ?
    `, [req.params.id])

    const b = rows[0]
    res.json({ ...b, price: Number(b.price), isAvailable: b.availableCopies > 0 })
  } catch (err) { next(err) }
})

// ── DELETE /api/books/:id ─────────────────────────────────────────────────

router.delete('/:id', async (req, res, next) => {
  try {
    const [exists] = await pool.execute(`SELECT Id, Title FROM Books WHERE Id = ?`, [req.params.id])
    if (exists.length === 0)
      return res.status(404).json({ message: `Book with ID ${req.params.id} not found.` })

    // Block deletion if active or overdue borrows exist
    const [activeBorrows] = await pool.execute(`
      SELECT COUNT(*) AS count FROM BorrowRecords
      WHERE BookId = ? AND Status IN ('Active', 'Overdue')
    `, [req.params.id])

    if (Number(activeBorrows[0].count) > 0)
      return res.status(409).json({
        message: `Cannot delete '${exists[0].Title}' because it has ${activeBorrows[0].count} active/overdue borrow record(s).`
      })

    await pool.execute(`DELETE FROM Books WHERE Id = ?`, [req.params.id])
    res.status(204).send()
  } catch (err) { next(err) }
})

module.exports = router