<?php
namespace Controllers\Admin;

use Core\Controller;

class LibraryController extends Controller {

    public function index(): void {
        $this->requireRole('super-admin', 'admin');
        $sid    = $this->schoolId();
        $page   = max(1, (int)$this->request->get('page', 1));
        $search = $this->request->get('search', '');
        $cat    = $this->request->get('category', '');

        $where  = ['b.school_id=?'];
        $params = [$sid];
        if ($search) { $where[] = '(b.title LIKE ? OR b.author LIKE ? OR b.isbn LIKE ?)'; $s = "%$search%"; $params[] = $s; $params[] = $s; $params[] = $s; }
        if ($cat)    { $where[] = 'b.category=?'; $params[] = $cat; }
        $w = implode(' AND ', $where);

        $books = $this->db->paginate(
            "SELECT b.*, (b.total_copies - IFNULL(borrowed.cnt,0)) AS available
             FROM library_books b
             LEFT JOIN (
                SELECT book_id, COUNT(*) AS cnt FROM library_borrowings
                WHERE status='borrowed' GROUP BY book_id
             ) borrowed ON borrowed.book_id=b.id
             WHERE $w ORDER BY b.title", $params, $page, 20
        );

        $categories = $this->db->fetchAll(
            "SELECT DISTINCT category FROM library_books WHERE school_id=? AND category IS NOT NULL ORDER BY category", [$sid]
        );

        $stats = $this->db->fetch(
            "SELECT COUNT(*) AS total_books,
                    SUM(total_copies) AS total_copies,
                    (SELECT COUNT(*) FROM library_borrowings lb JOIN library_books lb2 ON lb2.id=lb.book_id WHERE lb2.school_id=? AND lb.status='borrowed') AS borrowed,
                    (SELECT COUNT(*) FROM library_borrowings lb JOIN library_books lb2 ON lb2.id=lb.book_id WHERE lb2.school_id=? AND lb.status='borrowed' AND lb.due_date < CURDATE()) AS overdue
             FROM library_books WHERE school_id=?", [$sid, $sid, $sid]
        ) ?? [];

        $students = $this->db->fetchAll(
            "SELECT s.id, u.name, s.assessment_no, 'student' AS type FROM students s
             JOIN users u ON u.id=s.user_id
             WHERE s.school_id=? AND s.status='active' ORDER BY u.name", [$sid]
        );
        $teachers = $this->db->fetchAll(
            "SELECT t.id, u.name, IFNULL(t.tsc_no, t.employee_no) AS ref, 'teacher' AS type FROM teachers t
             JOIN users u ON u.id=t.user_id
             WHERE t.school_id=? AND u.is_active=1 ORDER BY u.name", [$sid]
        );

        $this->view('admin/library/index', compact('books','categories','stats','students','teachers','search','cat'));
    }

    public function storeBook(): void {
        $this->requireRole('super-admin', 'admin');
        $this->verifyCsrf();
        $data = $this->request->all();
        $this->db->insert('library_books', [
            'school_id'    => $this->schoolId(),
            'title'        => htmlspecialchars($data['title'], ENT_QUOTES),
            'author'       => htmlspecialchars($data['author'] ?? '', ENT_QUOTES) ?: null,
            'isbn'         => $data['isbn'] ?? null,
            'category'     => $data['category'] ?? null,
            'publisher'    => $data['publisher'] ?? null,
            'year'         => $data['year'] ?: null,
            'total_copies' => max(1, (int)($data['total_copies'] ?? 1)),
            'shelf'        => $data['shelf'] ?? null,
            'description'  => $data['description'] ?? null,
            'created_at'   => date('Y-m-d H:i:s'),
            'updated_at'   => date('Y-m-d H:i:s'),
        ]);
        $this->withSuccess('Book added to library.');
        $this->redirect('admin/library');
    }

    public function updateBook(string $id): void {
        $this->requireRole('super-admin', 'admin');
        $this->verifyCsrf();
        $data = $this->request->all();
        $this->db->update('library_books', [
            'title'        => htmlspecialchars($data['title'], ENT_QUOTES),
            'author'       => htmlspecialchars($data['author'] ?? '', ENT_QUOTES) ?: null,
            'isbn'         => $data['isbn'] ?? null,
            'category'     => $data['category'] ?? null,
            'publisher'    => $data['publisher'] ?? null,
            'year'         => $data['year'] ?: null,
            'total_copies' => max(1, (int)($data['total_copies'] ?? 1)),
            'shelf'        => $data['shelf'] ?? null,
            'description'  => $data['description'] ?? null,
            'updated_at'   => date('Y-m-d H:i:s'),
        ], 'id=? AND school_id=?', [(int)$id, $this->schoolId()]);
        $this->withSuccess('Book updated.');
        $this->redirect('admin/library');
    }

    public function deleteBook(string $id): void {
        $this->requireRole('super-admin', 'admin');
        $this->verifyCsrf();
        $this->db->query("DELETE FROM library_books WHERE id=? AND school_id=?", [(int)$id, $this->schoolId()]);
        $this->withSuccess('Book removed.');
        $this->redirect('admin/library');
    }

    public function borrowings(): void {
        $this->requireRole('super-admin', 'admin');
        $sid      = $this->schoolId();
        $page     = max(1, (int)$this->request->get('page', 1));
        $status   = $this->request->get('status', '');
        $search   = $this->request->get('search', '');
        $type     = $this->request->get('type', '');     // student | teacher
        $streamId = (int)$this->request->get('stream_id', 0);

        $where  = ['b.school_id=?'];
        $params = [$sid];
        if ($status)   { $where[] = 'lb.status=?';          $params[] = $status; }
        if ($type === 'student') { $where[] = 'lb.teacher_id IS NULL'; }
        if ($type === 'teacher') { $where[] = 'lb.teacher_id IS NOT NULL'; }
        if ($streamId) { $where[] = 's.stream_id=?';        $params[] = $streamId; }
        if ($search)   { $where[] = '(su.name LIKE ? OR tu.name LIKE ? OR s.assessment_no LIKE ? OR b.title LIKE ?)'; $sr = "%$search%"; $params[] = $sr; $params[] = $sr; $params[] = $sr; $params[] = $sr; }
        $w = implode(' AND ', $where);

        $sql = "SELECT lb.*,
                    b.title AS book_title, b.author,
                    COALESCE(su.name, tu.name) AS borrower_name,
                    COALESCE(s.assessment_no, t.tsc_no) AS borrower_ref,
                    CASE WHEN lb.teacher_id IS NOT NULL THEN 'Teacher' ELSE 'Student' END AS borrower_type,
                    DATEDIFF(CURDATE(), lb.due_date) AS days_overdue
             FROM library_borrowings lb
             JOIN library_books b ON b.id=lb.book_id
             LEFT JOIN students s ON s.id=lb.student_id
             LEFT JOIN users su ON su.id=s.user_id
             LEFT JOIN teachers t ON t.id=lb.teacher_id
             LEFT JOIN users tu ON tu.id=t.user_id
             WHERE $w ORDER BY lb.id DESC";

        // For print — fetch all without pagination
        $print = $this->request->get('print', '');
        if ($print) {
            $allRows  = $this->db->fetchAll($sql, $params);
            $school   = $this->db->fetch("SELECT * FROM schools WHERE id=?", [$sid]);
            $streams  = $this->db->fetchAll(
                "SELECT st.id, CONCAT(cl.name,' ',st.name) AS full_name FROM streams st
                 JOIN classes cl ON cl.id=st.class_id WHERE cl.school_id=? ORDER BY cl.level,st.name", [$sid]
            );
            $filters  = compact('status','search','type','streamId');
            $this->view('admin/library/print', compact('allRows','school','filters','streams'), null);
            return;
        }

        $borrowings = $this->db->paginate($sql, $params, $page, 20);

        $streams = $this->db->fetchAll(
            "SELECT st.id, CONCAT(cl.name,' ',st.name) AS full_name FROM streams st
             JOIN classes cl ON cl.id=st.class_id WHERE cl.school_id=? ORDER BY cl.level,st.name", [$sid]
        );

        $this->view('admin/library/borrowings', compact('borrowings','status','search','type','streamId','streams'));
    }

    public function borrow(): void {
        $this->requireRole('super-admin', 'admin');
        $this->verifyCsrf();
        $data      = $this->request->all();
        $sid       = $this->schoolId();
        $borrowerType = $data['borrower_type'] ?? 'student'; // student | teacher
        $borrowerId   = (int)($data['borrower_id'] ?? 0);

        // Resolve student_id and teacher_id
        $studentId = $borrowerType === 'student' ? $borrowerId : null;
        $teacherId = $borrowerType === 'teacher' ? $borrowerId : null;

        $book = $this->db->fetch("SELECT * FROM library_books WHERE id=? AND school_id=?", [(int)$data['book_id'], $sid]);
        if (!$book) { $this->withError('Book not found.'); $this->redirect('admin/library'); return; }

        $borrowed = $this->db->fetch(
            "SELECT COUNT(*) AS cnt FROM library_borrowings WHERE book_id=? AND status='borrowed'", [(int)$data['book_id']]
        )['cnt'] ?? 0;
        if ($borrowed >= $book['total_copies']) {
            $this->withError('No copies available for borrowing.');
            $this->redirect('admin/library'); return;
        }

        // Check not already borrowed by same person
        $checkCol = $borrowerType === 'teacher' ? 'teacher_id' : 'student_id';
        $existing = $this->db->fetch(
            "SELECT id FROM library_borrowings WHERE book_id=? AND {$checkCol}=? AND status='borrowed'",
            [(int)$data['book_id'], $borrowerId]
        );
        if ($existing) { $this->withError('This person already has this book borrowed.'); $this->redirect('admin/library'); return; }

        $dueDate = $data['due_date'] ?: date('Y-m-d', strtotime('+14 days'));
        $this->db->insert('library_borrowings', [
            'book_id'     => (int)$data['book_id'],
            'student_id'  => $studentId,
            'teacher_id'  => $teacherId,
            'borrow_date' => date('Y-m-d'),
            'due_date'    => $dueDate,
            'status'      => 'borrowed',
            'issued_by'   => $this->currentUser()['id'],
            'created_at'  => date('Y-m-d H:i:s'),
            'updated_at'  => date('Y-m-d H:i:s'),
        ]);
        $this->withSuccess("Book issued. Due: $dueDate");
        $this->redirect('admin/library/borrowings');
    }

    public function returnBook(string $id): void {
        $this->requireRole('super-admin', 'admin');
        $this->verifyCsrf();
        $data = $this->request->all();
        $fine = (float)($data['fine'] ?? 0);
        $this->db->update('library_borrowings', [
            'status'      => 'returned',
            'return_date' => date('Y-m-d'),
            'fine'        => $fine,
            'notes'       => $data['notes'] ?? null,
            'updated_at'  => date('Y-m-d H:i:s'),
        ], 'id=?', [(int)$id]);
        $this->withSuccess('Book returned' . ($fine > 0 ? ". Fine: KES $fine" : '.'));
        $this->redirect('admin/library/borrowings');
    }
}
