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

// ── GET /api/authors ──────────────────────────────────────────────────────

router.get('/', async (req, res, next) => {
  try {
    const { name, nationality } = req.query

    let sql = `
      SELECT
        a.Id           AS id,
        a.FirstName    AS firstName,
        a.LastName     AS lastName,
        a.Biography    AS biography,
        a.Nationality  AS nationality,
        a.CreatedAt    AS createdAt,
        COUNT(b.Id)    AS bookCount
      FROM Authors a
      LEFT JOIN Books b ON b.AuthorId = a.Id
    `
    const params = []

    if (name) {
      sql += ` WHERE (a.FirstName LIKE ? OR a.LastName LIKE ?)`
      params.push(`%${name}%`, `%${name}%`)
    }

    if (nationality) {
      sql += name ? ` AND a.Nationality LIKE ?` : ` WHERE a.Nationality LIKE ?`
      params.push(`%${nationality}%`)
    }

    sql += ` GROUP BY a.Id ORDER BY a.LastName, a.FirstName`

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

    const authors = rows.map(r => ({
      ...r,
      fullName:  `${r.firstName} ${r.lastName}`,
      bookCount: Number(r.bookCount),
    }))

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

// ── GET /api/authors/top-borrowed ─────────────────────────────────────────

router.get('/top-borrowed', async (req, res, next) => {
  try {
    const topN = Math.min(Math.max(parseInt(req.query.topN) || 5, 1), 100)

    const sql = `
      SELECT
        a.Id                        AS authorId,
        CONCAT(a.FirstName, ' ', a.LastName) AS fullName,
        a.Nationality               AS nationality,
        COUNT(br.Id)                AS totalBorrows,
        COUNT(DISTINCT b.Id)        AS uniqueBooks,
        MAX(br.BorrowDate)          AS lastBorrowDate
      FROM Authors a
      JOIN Books b        ON b.AuthorId  = a.Id
      JOIN BorrowRecords br ON br.BookId = b.Id
      GROUP BY a.Id
      ORDER BY totalBorrows DESC
      LIMIT ?
    `

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

    const result = rows.map(r => ({
      ...r,
      totalBorrows: Number(r.totalBorrows),
      uniqueBooks:  Number(r.uniqueBooks),
    }))

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

// ── GET /api/authors/:id ──────────────────────────────────────────────────

router.get('/:id', async (req, res, next) => {
  try {
    const [rows] = await pool.execute(`
      SELECT
        a.Id           AS id,
        a.FirstName    AS firstName,
        a.LastName     AS lastName,
        a.Biography    AS biography,
        a.Nationality  AS nationality,
        a.CreatedAt    AS createdAt,
        COUNT(b.Id)    AS bookCount
      FROM Authors a
      LEFT JOIN Books b ON b.AuthorId = a.Id
      WHERE a.Id = ?
      GROUP BY a.Id
    `, [req.params.id])

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

    const a = rows[0]
    res.json({ ...a, fullName: `${a.firstName} ${a.lastName}`, bookCount: Number(a.bookCount) })
  } catch (err) { next(err) }
})

// ── POST /api/authors ─────────────────────────────────────────────────────

router.post('/', async (req, res, next) => {
  try {
    const { firstName, lastName, biography, nationality } = req.body

    if (!firstName?.trim() || !lastName?.trim())
      return res.status(400).json({ message: 'firstName and lastName are required.' })

    const [result] = await pool.execute(`
      INSERT INTO Authors (FirstName, LastName, Biography, Nationality, CreatedAt)
      VALUES (?, ?, ?, ?, NOW())
    `, [firstName.trim(), lastName.trim(), biography || null, nationality || null])

    const [rows] = await pool.execute(`
      SELECT
        a.Id AS id, a.FirstName AS firstName, a.LastName AS lastName,
        a.Biography AS biography, a.Nationality AS nationality, a.CreatedAt AS createdAt,
        0 AS bookCount
      FROM Authors a WHERE a.Id = ?
    `, [result.insertId])

    const a = rows[0]
    res.status(201).json({ ...a, fullName: `${a.firstName} ${a.lastName}`, bookCount: 0 })
  } catch (err) { next(err) }
})

// ── PUT /api/authors/:id ──────────────────────────────────────────────────

router.put('/:id', async (req, res, next) => {
  try {
    const { firstName, lastName, biography, nationality } = req.body

    if (!firstName?.trim() || !lastName?.trim())
      return res.status(400).json({ message: 'firstName and lastName are required.' })

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

    await pool.execute(`
      UPDATE Authors
      SET FirstName = ?, LastName = ?, Biography = ?, Nationality = ?
      WHERE Id = ?
    `, [firstName.trim(), lastName.trim(), biography || null, nationality || null, req.params.id])

    const [rows] = await pool.execute(`
      SELECT
        a.Id AS id, a.FirstName AS firstName, a.LastName AS lastName,
        a.Biography AS biography, a.Nationality AS nationality, a.CreatedAt AS createdAt,
        COUNT(b.Id) AS bookCount
      FROM Authors a
      LEFT JOIN Books b ON b.AuthorId = a.Id
      WHERE a.Id = ?
      GROUP BY a.Id
    `, [req.params.id])

    const a = rows[0]
    res.json({ ...a, fullName: `${a.firstName} ${a.lastName}`, bookCount: Number(a.bookCount) })
  } catch (err) { next(err) }
})

// ── DELETE /api/authors/:id ───────────────────────────────────────────────

router.delete('/:id', async (req, res, next) => {
  try {
    const [exists] = await pool.execute(`
      SELECT a.Id, a.FirstName, a.LastName, COUNT(b.Id) AS bookCount
      FROM Authors a
      LEFT JOIN Books b ON b.AuthorId = a.Id
      WHERE a.Id = ?
      GROUP BY a.Id
    `, [req.params.id])

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

    if (Number(exists[0].bookCount) > 0)
      return res.status(409).json({
        message: `Cannot delete author '${exists[0].FirstName} ${exists[0].LastName}' because they have ${exists[0].bookCount} book(s) in the system. Remove those books first.`
      })

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

module.exports = router