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

// ── Shared SELECT fragment ────────────────────────────────────────────────

const SELECT_BORROW = `
  SELECT
    br.Id               AS id,
    br.BorrowerName     AS borrowerName,
    br.BorrowerEmail    AS borrowerEmail,
    br.BorrowDate       AS borrowDate,
    br.DueDate          AS dueDate,
    br.ReturnDate       AS returnDate,
    br.Status           AS status,
    br.Notes            AS notes,
    br.BookId           AS bookId,
    b.Title             AS bookTitle,
    b.ISBN              AS bookISBN,
    CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName
  FROM BorrowRecords br
  JOIN Books   b ON b.Id   = br.BookId
  JOIN Authors a ON a.Id   = b.AuthorId
`

function mapRecord(r) {
  const now      = new Date()
  const dueDate  = new Date(r.dueDate)
  const isOverdue = r.status === 'Active' && dueDate < now
  const daysOverdue = isOverdue
    ? Math.floor((now - dueDate) / (1000 * 60 * 60 * 24))
    : 0

  return {
    ...r,
    isOverdue,
    daysOverdue,
  }
}

// ── GET /api/borrowrecords ────────────────────────────────────────────────

router.get('/', async (req, res, next) => {
  try {
    const { borrowerName, borrowerEmail, bookId, status, borrowDateFrom, borrowDateTo, isOverdue } = req.query

    let sql    = SELECT_BORROW + ` WHERE 1=1`
    const params = []

    if (borrowerName) {
      sql += ` AND br.BorrowerName LIKE ?`
      params.push(`%${borrowerName}%`)
    }

    if (borrowerEmail) {
      sql += ` AND br.BorrowerEmail LIKE ?`
      params.push(`%${borrowerEmail}%`)
    }

    if (bookId) {
      sql += ` AND br.BookId = ?`
      params.push(bookId)
    }

    if (status) {
      sql += ` AND br.Status = ?`
      params.push(status)
    }

    if (borrowDateFrom) {
      sql += ` AND br.BorrowDate >= ?`
      params.push(new Date(borrowDateFrom))
    }

    if (borrowDateTo) {
      sql += ` AND br.BorrowDate <= ?`
      params.push(new Date(borrowDateTo))
    }

    if (isOverdue === 'true') {
      sql += ` AND br.Status = 'Active' AND br.DueDate < NOW()`
    }

    sql += ` ORDER BY br.BorrowDate DESC`

    const [rows] = await pool.execute(sql, params)
    res.json(rows.map(mapRecord))
  } catch (err) { next(err) }
})

// ── GET /api/borrowrecords/overdue-report ─────────────────────────────────

router.get('/overdue-report', async (req, res, next) => {
  try {
    // Fetch all active records past their due date
    const [rows] = await pool.execute(`
      SELECT
        br.Id               AS borrowRecordId,
        br.BorrowerName     AS borrowerName,
        br.BorrowerEmail    AS borrowerEmail,
        br.BookId           AS bookId,
        b.Title             AS bookTitle,
        b.Genre             AS genre,
        CONCAT(a.FirstName, ' ', a.LastName) AS authorFullName,
        br.BorrowDate       AS borrowDate,
        br.DueDate          AS dueDate,
        DATEDIFF(NOW(), br.DueDate) AS daysOverdue
      FROM BorrowRecords br
      JOIN Books   b ON b.Id = br.BookId
      JOIN Authors a ON a.Id = b.AuthorId
      WHERE br.Status = 'Active'
        AND br.DueDate < NOW()
      ORDER BY br.DueDate ASC
    `)

    // Auto-update those records to Overdue status in the database
    if (rows.length > 0) {
      const ids = rows.map(r => r.borrowRecordId)
      const placeholders = ids.map(() => '?').join(',')
      await pool.execute(
        `UPDATE BorrowRecords SET Status = 'Overdue' WHERE Id IN (${placeholders})`,
        ids
      )
    }

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

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

// ── GET /api/borrowrecords/:id ────────────────────────────────────────────

router.get('/:id', async (req, res, next) => {
  try {
    const [rows] = await pool.execute(
      SELECT_BORROW + ` WHERE br.Id = ?`,
      [req.params.id]
    )

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

    res.json(mapRecord(rows[0]))
  } catch (err) { next(err) }
})

// ── POST /api/borrowrecords ───────────────────────────────────────────────

router.post('/', async (req, res, next) => {
  try {
    const { borrowerName, borrowerEmail, bookId, borrowDate, dueDate, notes } = req.body

    // Validate required fields
    if (!borrowerName?.trim())  return res.status(400).json({ message: 'borrowerName is required.' })
    if (!borrowerEmail?.trim()) return res.status(400).json({ message: 'borrowerEmail is required.' })
    if (!bookId)                return res.status(400).json({ message: 'bookId is required.' })
    if (!dueDate)               return res.status(400).json({ message: 'dueDate is required.' })

    const borrow = new Date(borrowDate || Date.now())
    const due    = new Date(dueDate)

    if (due <= borrow)
      return res.status(400).json({ message: 'dueDate must be after borrowDate.' })

    // Validate book exists and has available copies
    const [books] = await pool.execute(
      `SELECT Id, Title, AvailableCopies FROM Books WHERE Id = ?`,
      [bookId]
    )

    if (books.length === 0)
      return res.status(400).json({ message: `Book with ID ${bookId} does not exist.` })

    if (books[0].AvailableCopies <= 0)
      return res.status(409).json({ message: `No available copies of '${books[0].Title}' at the moment.` })

    // Decrement available copies
    await pool.execute(
      `UPDATE Books SET AvailableCopies = AvailableCopies - 1 WHERE Id = ?`,
      [bookId]
    )

    // Insert the borrow record
    const [result] = await pool.execute(`
      INSERT INTO BorrowRecords
        (BorrowerName, BorrowerEmail, BookId, BorrowDate, DueDate, Status, Notes)
      VALUES (?, ?, ?, ?, ?, 'Active', ?)
    `, [
      borrowerName.trim(),
      borrowerEmail.trim(),
      parseInt(bookId),
      borrow,
      due,
      notes || null,
    ])

    const [rows] = await pool.execute(
      SELECT_BORROW + ` WHERE br.Id = ?`,
      [result.insertId]
    )

    res.status(201).json(mapRecord(rows[0]))
  } catch (err) { next(err) }
})

// ── PUT /api/borrowrecords/:id ────────────────────────────────────────────

router.put('/:id', async (req, res, next) => {
  try {
    const { borrowerName, borrowerEmail, dueDate, returnDate, status, notes } = req.body

    // Validate required fields
    if (!borrowerName?.trim())  return res.status(400).json({ message: 'borrowerName is required.' })
    if (!borrowerEmail?.trim()) return res.status(400).json({ message: 'borrowerEmail is required.' })
    if (!dueDate)               return res.status(400).json({ message: 'dueDate is required.' })
    if (!status)                return res.status(400).json({ message: 'status is required.' })

    // Fetch existing record
    const [existing] = await pool.execute(`
      SELECT br.Id, br.Status, br.BookId, b.AvailableCopies, b.TotalCopies
      FROM BorrowRecords br
      JOIN Books b ON b.Id = br.BookId
      WHERE br.Id = ?
    `, [req.params.id])

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

    const prev    = existing[0]
    const wasReturned = prev.Status === 'Returned'
    const nowReturned = status === 'Returned'

    // Handle available copies adjustments
    if (!wasReturned && nowReturned) {
      // Book is being returned — increment available copies
      await pool.execute(
        `UPDATE Books SET AvailableCopies = AvailableCopies + 1 WHERE Id = ?`,
        [prev.BookId]
      )
    } else if (wasReturned && !nowReturned) {
      // Record is being reopened — decrement available copies again
      if (prev.AvailableCopies <= 0)
        return res.status(409).json({ message: 'Cannot reopen borrow record: no available copies.' })
      await pool.execute(
        `UPDATE Books SET AvailableCopies = AvailableCopies - 1 WHERE Id = ?`,
        [prev.BookId]
      )
    }

    // If marking as Returned with no return date provided, default to now
    const resolvedReturnDate = nowReturned
      ? (returnDate ? new Date(returnDate) : new Date())
      : (returnDate ? new Date(returnDate) : null)

    await pool.execute(`
      UPDATE BorrowRecords
      SET BorrowerName = ?, BorrowerEmail = ?, DueDate = ?,
          ReturnDate = ?, Status = ?, Notes = ?
      WHERE Id = ?
    `, [
      borrowerName.trim(),
      borrowerEmail.trim(),
      new Date(dueDate),
      resolvedReturnDate,
      status,
      notes || null,
      req.params.id,
    ])

    const [rows] = await pool.execute(
      SELECT_BORROW + ` WHERE br.Id = ?`,
      [req.params.id]
    )

    res.json(mapRecord(rows[0]))
  } catch (err) { next(err) }
})

// ── DELETE /api/borrowrecords/:id ─────────────────────────────────────────

router.delete('/:id', async (req, res, next) => {
  try {
    const [existing] = await pool.execute(
      `SELECT Id, Status FROM BorrowRecords WHERE Id = ?`,
      [req.params.id]
    )

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

    if (['Active', 'Overdue'].includes(existing[0].Status))
      return res.status(409).json({
        message: 'Cannot delete an active or overdue borrow record. Mark it as returned first.'
      })

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

module.exports = router