/**
 * Library Module Controller
 */
const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

const staffOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'librarian', 'teacher'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Restricted access' });
  }
  next();
};

// ============ BOOKS CATALOG ============

/**
 * @openapi
 * /api/library/books:
 *   get:
 *     summary: List library books
 *     description: |
 *       Returns all non-deleted books ordered by title.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Book list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Add a book
 *     description: |
 *       Creates a new book record. Restricted to library staff
 *       (`school_admin`, `systems_admin`, `principal`, `librarian`,
 *       `teacher`).
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - title
 *             properties:
 *               isbn: { type: string }
 *               title: { type: string }
 *               author: { type: string }
 *               category: { type: string }
 *               publisher: { type: string }
 *               year_published: { type: integer }
 *               total_copies: { type: integer }
 *               shelf_location: { type: string }
 *     responses:
 *       200:
 *         description: Created book
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.get('/books', auth, (req, res) => {
  const books = db.prepare(`
    SELECT *
    FROM library_books
    WHERE is_deleted = 0
    ORDER BY title
  `).all();
  res.json(books);
});

router.post('/books', auth, staffOnly, (req, res) => {
  const { isbn, title, author, category, publisher, year_published, total_copies, shelf_location } = req.body;
  const uid = uuidv4();
  
  try {
    const result = db.prepare(`
      INSERT INTO library_books (uid, isbn, title, author, category, publisher, year_published, total_copies, available_copies, shelf_location)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, isbn, title, author, category, publisher, year_published, total_copies, total_copies, shelf_location);
    res.json({ success: true, id: result.lastInsertRowid, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/library/books/bulk:
 *   post:
 *     summary: Bulk import books
 *     description: |
 *       Inserts many books in a single transaction and reports
 *       per-row success or failure. Restricted to library staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - books
 *             properties:
 *               books:
 *                 type: array
 *                 items:
 *                   type: object
 *                   required:
 *                     - title
 *                   properties:
 *                     isbn: { type: string }
 *                     title: { type: string }
 *                     author: { type: string }
 *                     category: { type: string }
 *                     publisher: { type: string }
 *                     year_published: { type: integer }
 *                     total_copies: { type: integer }
 *                     shelf_location: { type: string }
 *     responses:
 *       200:
 *         description: Per-row outcomes
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: integer }
 *                 failed: { type: integer }
 *                 errors:
 *                   type: array
 *                   items: { type: object }
 *       400:
 *         description: Books array missing
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       500:
 *         description: Transaction error
 *         content:
 *           application/json:
 *             schema: { type: object }
 */
router.post('/books/bulk', auth, staffOnly, (req, res) => {
  const { books } = req.body;
  if (!Array.isArray(books)) return res.status(400).json({ error: 'Books array is required' });

  const insert = db.prepare(`
    INSERT INTO library_books (uid, isbn, title, author, category, publisher, year_published, total_copies, available_copies, shelf_location)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const results = { success: 0, failed: 0, errors: [] };

  const performBulk = db.transaction((booksToInsert) => {
    for (const book of booksToInsert) {
      try {
        const { isbn, title, author, category, publisher, year_published, total_copies, shelf_location } = book;
        if (!title) throw new Error('Title is required');
        const uid = uuidv4();
        insert.run(uid, isbn || null, title, author || null, category || null, publisher || null, year_published || null, total_copies || 1, total_copies || 1, shelf_location || null);
        results.success++;
      } catch (err) {
        results.failed++;
        results.errors.push({ title: book.title || 'Unknown', error: err.message });
      }
    }
  });

  try {
    performBulk(books);
    res.json(results);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/library/books/{id}:
 *   put:
 *     summary: Update a book
 *     description: |
 *       Updates book fields and rebalances `available_copies` based
 *       on the change to `total_copies`. Restricted to library
 *       staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               title: { type: string }
 *               author: { type: string }
 *               isbn: { type: string }
 *               category: { type: string }
 *               publisher: { type: string }
 *               year_published: { type: integer }
 *               total_copies: { type: integer }
 *               shelf_location: { type: string }
 *     responses:
 *       200:
 *         description: Updated
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 *       404:
 *         description: Book not found
 *         content:
 *           application/json:
 *             schema: { type: object }
 *   delete:
 *     summary: Soft-delete a book
 *     description: |
 *       Marks the book as deleted (`is_deleted = 1`). Restricted to
 *       library staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.put('/books/:id', auth, staffOnly, (req, res) => {
  const { id } = req.params;
  const { title, author, isbn, category, publisher, year_published, total_copies, shelf_location } = req.body;

  const current = db.prepare('SELECT total_copies, available_copies FROM library_books WHERE id = ?').get(id);
  if (!current) return res.status(404).json({ error: 'Book not found' });

  const oldTotal = current.total_copies;
  const newTotal = total_copies !== undefined ? total_copies : oldTotal;
  const delta = newTotal - oldTotal;

  db.prepare(`
    UPDATE library_books
    SET
      title = COALESCE(?, title),
      author = COALESCE(?, author),
      isbn = COALESCE(?, isbn),
      category = COALESCE(?, category),
      publisher = COALESCE(?, publisher),
      year_published = COALESCE(?, year_published),
      total_copies = ?,
      shelf_location = COALESCE(?, shelf_location),
      available_copies = available_copies + ?,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(title, author, isbn, category, publisher, year_published, newTotal, shelf_location, delta, id);

  db.prepare(`UPDATE library_books SET available_copies = MAX(0, available_copies) WHERE id = ?`).run(id);
  res.json({ success: true });
});

router.delete('/books/:id', auth, staffOnly, (req, res) => {
  db.prepare('UPDATE library_books SET is_deleted = 1 WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

// ============ ISSUES (BORROWING) ============

/**
 * @openapi
 * /api/library/issues:
 *   get:
 *     summary: List library issues
 *     description: |
 *       Returns borrowing records (most recent first). Students
 *       and parents see only their own issues; staff see all.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Issue list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Issue a book
 *     description: |
 *       Records a new borrowing. Decrements `available_copies` on
 *       the book and auto-fulfills any pending reservations for the
 *       same user and book. Restricted to library staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - book_id
 *               - user_id
 *               - due_date
 *             properties:
 *               book_id: { type: integer }
 *               user_id: { type: integer }
 *               due_date: { type: string, format: date }
 *     responses:
 *       200:
 *         description: Issued
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Book not found or no copies available
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.get('/issues', auth, (req, res) => {
  let query = `
    SELECT li.*, lb.title as book_title, lb.author as book_author, u.first_name, u.last_name,
      (SELECT first_name || ' ' || last_name FROM users WHERE id = li.issued_by) as issuer_name
    FROM library_issues li
    JOIN library_books lb ON li.book_id = lb.id
    JOIN users u ON li.user_id = u.id
    WHERE li.is_deleted = 0
  `;
  const params = [];

  // Students/Parents only see their own
  if (['student', 'parent'].includes(req.user.role)) {
    query += ' AND li.user_id = ?';
    params.push(req.user.id);
  }

  query += ' ORDER BY li.issue_date DESC';
  
  const issues = db.prepare(query).all(...params);
  res.json(issues);
});

router.post('/issues', auth, staffOnly, (req, res) => {
  const { book_id, user_id, due_date } = req.body;
  const uid = uuidv4();

  const performIssue = db.transaction(() => {
    const book = db.prepare('SELECT available_copies FROM library_books WHERE id = ? AND is_deleted = 0').get(book_id);
    if (!book) throw new Error('Book not found');
    if (book.available_copies <= 0) throw new Error('No copies available');

    const result = db.prepare(`
      INSERT INTO library_issues (uid, book_id, user_id, issue_date, due_date, status, issued_by)
      VALUES (?, ?, ?, date(\'now\'), ?, 'issued', ?)
    `).run(uid, book_id, user_id, due_date, req.user.id);

    db.prepare('UPDATE library_books SET available_copies = available_copies - 1 WHERE id = ?').run(book_id);
    
    // Auto-fulfill any pending reservations for this user and book
    db.prepare(`
      UPDATE library_reservations SET status = 'fulfilled' 
      WHERE user_id = ? AND book_id = ? AND status = 'pending'
    `).run(user_id, book_id);

    return result.lastInsertRowid;
  });

  try {
    const id = performIssue();
    res.json({ success: true, id, uid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/library/issues/{id}/return:
 *   put:
 *     summary: Return a borrowed book
 *     description: |
 *       Marks the issue as `returned` (or `lost` when `is_lost` is
 *       set or `fine_reason` is `Lost`). Optionally records a fine.
 *       Restricted to library staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               condition_notes: { type: string }
 *               generate_fine: { type: boolean }
 *               fine_amount: { type: number }
 *               fine_reason: { type: string }
 *               is_lost: { type: boolean }
 *     responses:
 *       200:
 *         description: Returned
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Issue missing, already returned, or already lost
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.put('/issues/:id/return', auth, staffOnly, (req, res) => {
  const { id } = req.params;
  const { condition_notes, generate_fine, fine_amount, fine_reason, is_lost } = req.body;

  const performReturn = db.transaction(() => {
    const issue = db.prepare('SELECT * FROM library_issues WHERE id = ?').get(id);
    if (!issue) throw new Error('Issue record not found');
    if (issue.status === 'returned') throw new Error('Book already returned');
    if (issue.status === 'lost') throw new Error('Book is already marked as lost');

    const markAsLost = is_lost || (generate_fine && fine_reason === 'Lost');

    if (markAsLost) {
      db.prepare(`
        UPDATE library_issues 
        SET return_date = date('now'), status = 'lost', updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `).run(id);

      // Decrement total copies because the copy is permanently lost
      db.prepare('UPDATE library_books SET total_copies = MAX(0, total_copies - 1) WHERE id = ?').run(issue.book_id);
    } else {
      db.prepare(`
        UPDATE library_issues 
        SET return_date = date('now'), status = 'returned', updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `).run(id);

      db.prepare('UPDATE library_books SET available_copies = available_copies + 1 WHERE id = ?').run(issue.book_id);
    }

    if (generate_fine && fine_amount > 0) {
      db.prepare(`
        INSERT INTO library_fines (uid, issue_id, user_id, amount, reason, status)
        VALUES (?, ?, ?, ?, ?, 'unpaid')
      `).run(uuidv4(), id, issue.user_id, fine_amount, fine_reason || condition_notes);
    }
  });

  try {
    performReturn();
    res.json({ success: true });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// ============ RESERVATIONS (BOOKINGS) ============

/**
 * @openapi
 * /api/library/reservations:
 *   get:
 *     summary: List reservations
 *     description: |
 *       Returns pending, fulfilled, and cancelled reservations.
 *       Students and parents see only their own; staff see all.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Reservation list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Reserve a book
 *     description: |
 *       Creates a pending reservation for the caller. Rejects if a
 *       pending reservation already exists for the same book.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - book_id
 *             properties:
 *               book_id: { type: integer }
 *               notes: { type: string }
 *     responses:
 *       200:
 *         description: Reserved
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Duplicate pending reservation
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/reservations', auth, (req, res) => {
  let query = `
    SELECT lr.*, lb.title as book_title, lb.author as book_author, u.first_name, u.last_name
    FROM library_reservations lr
    JOIN library_books lb ON lr.book_id = lb.id
    JOIN users u ON lr.user_id = u.id
    WHERE lr.is_deleted = 0
  `;
  const params = [];

  if (['student', 'parent'].includes(req.user.role)) {
    query += ' AND lr.user_id = ?';
    params.push(req.user.id);
  }

  query += ' ORDER BY lr.reservation_date DESC';
  const reservations = db.prepare(query).all(...params);
  res.json(reservations);
});

router.post('/reservations', auth, (req, res) => {
  const { book_id, notes } = req.body;
  const uid = uuidv4();
  const userId = req.user.id;

  try {
    // Check if already reserved
    const existing = db.prepare('SELECT id FROM library_reservations WHERE user_id = ? AND book_id = ? AND status = "pending"').get(userId, book_id);
    if (existing) throw new Error('You already have a pending reservation for this book');

    const result = db.prepare(`
      INSERT INTO library_reservations (uid, book_id, user_id, notes)
      VALUES (?, ?, ?, ?)
    `).run(uid, book_id, userId, notes);
    
    res.json({ success: true, id: result.lastInsertRowid });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

/**
 * @openapi
 * /api/library/reservations/{id}/cancel:
 *   put:
 *     summary: Cancel a reservation
 *     description: |
 *       Marks the caller's pending reservation as `cancelled`. The
 *       reservation must belong to the caller.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Cancelled
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.put('/reservations/:id/cancel', auth, (req, res) => {
  db.prepare('UPDATE library_reservations SET status = "cancelled" WHERE id = ? AND user_id = ?').run(req.params.id, req.user.id);
  res.json({ success: true });
});

// ============ FINES ============

/**
 * @openapi
 * /api/library/fines:
 *   get:
 *     summary: List library fines
 *     description: |
 *       Returns fines. Students and parents see only their own;
 *       staff see all.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Fine list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/fines', auth, (req, res) => {
  let query = `
    SELECT lf.*, lb.title as book_title, u.first_name, u.last_name
    FROM library_fines lf
    JOIN library_issues li ON lf.issue_id = li.id
    JOIN library_books lb ON li.book_id = lb.id
    JOIN users u ON lf.user_id = u.id
    WHERE lf.is_deleted = 0
  `;
  const params = [];

  if (['student', 'parent'].includes(req.user.role)) {
    query += ' AND lf.user_id = ?';
    params.push(req.user.id);
  }

  query += ' ORDER BY lf.created_at DESC';
  const fines = db.prepare(query).all(...params);
  res.json(fines);
});

/**
 * @openapi
 * /api/library/fines/{id}/pay:
 *   post:
 *     summary: Mark fine as paid
 *     description: |
 *       Sets the fine's status to `paid`. Restricted to library
 *       staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Paid
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.post('/fines/:id/pay', auth, staffOnly, (req, res) => {
  db.prepare('UPDATE library_fines SET status = "paid", updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

/**
 * @openapi
 * /api/library/fines/{id}/waive:
 *   post:
 *     summary: Waive a fine
 *     description: |
 *       Sets the fine's status to `waived`. Restricted to library
 *       staff.
 *     tags:
 *       - Library
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Waived
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Staff only }
 */
router.post('/fines/:id/waive', auth, staffOnly, (req, res) => {
  db.prepare('UPDATE library_fines SET status = "waived", updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

module.exports = router;
