<?php
ob_start();
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
include "../include/config.php";

if (isset($_SESSION['email'])) {
    // Get filter parameters
    $filter = isset($_GET['filter']) ? $_GET['filter'] : 'all';
    $search = isset($_GET['search']) ? mysqli_real_escape_string($conn, $_GET['search']) : '';
    
    // Base query for students with overdue books or fines
    $base_query = "
        SELECT 
            s.studentid,
            s.studentname as student_name,
            s.email as student_email,
            s.mobile as student_phone,
            COUNT(bi.id) as books_owed,
            SUM(bi.fine_amount) as total_fines,
            MAX(bi.due_date) as latest_due_date
        FROM students s
        JOIN book_issues bi ON s.studentid = bi.studentid
        WHERE (bi.status = 'overdue' OR bi.status = 'issued' OR bi.fine_amount > 0)
    ";
    
    // Add filters based on selection
    if ($filter === 'overdue') {
        $base_query .= " AND bi.status = 'overdue'";
    } elseif ($filter === 'fines') {
        $base_query .= " AND bi.fine_amount > 0";
    } elseif ($filter === 'issued') {
        $base_query .= " AND bi.status = 'issued'";
    }
    
    // Add search filter if provided
    if (!empty($search)) {
        $base_query .= " AND (s.studentname LIKE '%$search%' OR s.studentid LIKE '%$search%' OR s.email LIKE '%$search%')";
    }
    
    $base_query .= " GROUP BY s.studentid ORDER BY total_fines DESC, books_owed DESC";
    
    $owing_students = mysqli_query($conn, $base_query);
    
    // Fetch detailed book information for each student
    $student_details = [];
    if (mysqli_num_rows($owing_students) > 0) {
        mysqli_data_seek($owing_students, 0);
        while ($student = mysqli_fetch_assoc($owing_students)) {
            $student_id = $student['studentid'];
            $books_query = mysqli_query($conn, "
                SELECT 
                    bi.*,
                    c.title,
                    c.author,
                    c.isbn,
                    DATEDIFF(CURDATE(), bi.due_date) as days_overdue
                FROM book_issues bi
                JOIN catalogue c ON bi.book_id = c.id
                WHERE bi.studentid = '$student_id' 
                AND (bi.status = 'overdue' OR bi.status = 'issued' OR bi.fine_amount > 0)
                ORDER BY bi.due_date ASC
            ");
            
            $student_books = [];
            while ($book = mysqli_fetch_assoc($books_query)) {
                $student_books[] = $book;
            }
            
            $student_details[] = [
                'student_info' => $student,
                'books' => $student_books
            ];
        }
    }
    
    // Fetch catalogue statistics
    $totalBooks = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as count FROM catalogue"))['count'];
    $totalCopies = mysqli_fetch_assoc(mysqli_query($conn, "SELECT SUM(copies) as total FROM catalogue"))['total'];
    
    // Fetch book issuance statistics
    $totalIssued = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as count FROM book_issues"))['count'];
    $currentlyIssued = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as count FROM book_issues WHERE status = 'issued'"))['count'];
    $returnedBooks = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as count FROM book_issues WHERE status = 'returned'"))['count'];
    $overdueBooks = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as count FROM book_issues WHERE status = 'overdue'"))['count'];
    $totalFines = mysqli_fetch_assoc(mysqli_query($conn, "SELECT SUM(fine_amount) as total FROM book_issues WHERE fine_amount > 0"))['total'];
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Students Owing Books</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Roboto:wght@400;500;700&display=swap" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
body { font-family: 'Roboto', sans-serif; background: #f5f7fa; }
.card { border-radius: 20px; box-shadow: 0 8px 25px rgba(0,0,0,0.1); padding: 30px; margin-top: 30px; margin-bottom: 30px; }
h2 { color: var(--primary); margin-bottom: 20px; font-weight: 700; }
.owing-card { background: linear-gradient(135deg, #ff6b6b, #ff4757); color: white; border-radius: 15px; padding: 20px; margin-bottom: 20px; }
.student-card { border-left: 5px solid #ff4757; margin-bottom: 20px; }
.overdue-badge { background-color: #dc3545; }
.fine-badge { background-color: #ffc107; color: #000; }
.stat-card { text-align: center; padding: 20px; background: linear-gradient(135deg, var(--primary), #34a380); color: white; border-radius: 15px; margin-bottom: 20px; }
.stat-card h3 { font-size: 2.5rem; margin: 10px 0; }
.stat-card p { margin: 0; font-size: 1.1rem; }
.loan-stat-card { text-align: center; padding: 20px; border-radius: 15px; margin-bottom: 20px; color: white; }
.issued-stat { background: linear-gradient(135deg, #ff6b6b, #ff8e8e); }
.returned-stat { background: linear-gradient(135deg, #1dd1a1, #48dbfb); }
.overdue-stat { background: linear-gradient(135deg, #feca57, #ff9ff3); }
.fines-stat { background: linear-gradient(135deg, #54a0ff, #5f27cd); }
.filter-section { background: #f8f9fa; padding: 20px; border-radius: 10px; margin-bottom: 20px; }
.table-responsive { max-height: 600px; overflow-y: auto; }
.status-badge { font-size: 0.85rem; }
</style>
</head>
<body>
    <?php include "../include/nav.php"; ?>
    <div class="content">
        <div class="container">
            <h2 class="text-center mt-4">Students Owing Books - Library Analytics</h2>
            
            <!-- Statistics Card -->
            <div class="row mt-4">
                <div class="col-md-12">
                    <div class="owing-card text-center">
                        <h3><?php echo count($student_details); ?> Students with Outstanding Books</h3>
                        
                    </div>
                </div>
            </div>

            <!-- Filter Section -->
            <div class="card mt-4">
                <h4><i class="fas fa-filter me-2"></i>Filter Students</h4>
                <div class="row">
                    <div class="col-md-8">
                        <form method="GET" class="row g-3">
                            <div class="col-md-4">
                                <label class="form-label">Filter by Status</label>
                                <select name="filter" class="form-select">
                                    <option value="all" <?php echo $filter === '' ? 'selected' : ''; ?>>All Owing Students</option>
                                    <option value="overdue" <?php echo $filter === 'overdue' ? 'selected' : ''; ?>>Overdue Books Only</option>
                                    <option value="fines" <?php echo $filter === 'fines' ? 'selected' : ''; ?>>With Fines Only</option>
                                    <option value="issued" <?php echo $filter === 'issued' ? 'selected' : ''; ?>>Currently Issued Only</option>
                                </select>
                            </div>
                            <div class="col-md-5">
                                <label class="form-label">Search Students</label>
                                <input type="text" name="search" class="form-control" placeholder="Search by name, ID, or email" value="<?php echo htmlspecialchars($search); ?>">
                            </div>
                            <div class="col-md-3">
                                <label class="form-label">&nbsp;</label>
                                <div class="d-grid gap-2">
                                    <button type="submit" class="btn btn-primary">Apply Filters</button>
                                    <a href="owing.php" class="btn btn-secondary">Reset</a>
                                </div>
                            </div>
                        </form>
                    </div>
                    
                </div>
            </div>
            
            <!-- Statistics Cards -->
            <div class="row mt-4">
                <div class="col-md-3">
                    <div class="stat-card">
                        <p>Total Books</p>
                        <h3><?php echo $totalBooks; ?></h3>
                        <p>Unique Titles</p>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="stat-card">
                        <p>Total Copies</p>
                        <h3><?php echo $totalCopies; ?></h3>
                        <p>Available</p>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="loan-stat-card issued-stat">
                        <p>Currently Issued</p>
                        <h3><?php echo $currentlyIssued; ?></h3>
                        <p>Books on Loan</p>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="loan-stat-card overdue-stat">
                        <p>Overdue</p>
                        <h3><?php echo $overdueBooks; ?></h3>
                        <p>Books Overdue</p>
                    </div>
                </div>
            </div>
            
            <!-- Students Table -->
            <div class="card mt-4">
                <h4><i class="fas fa-users me-2"></i>Students with Outstanding Obligations</h4>
                
                <?php if(count($student_details) > 0): ?>
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead class="table-dark">
                            <tr>
                                <th>Student ID</th>
                                <th>Name</th>
                                <th>Contact</th>
                                <th>Books Owed</th>
                                <th>Status</th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($student_details as $student_data): 
                                $student = $student_data['student_info'];
                                $has_overdue = false;
                                $has_fines = false;
                                
                                foreach ($student_data['books'] as $book) {
                                    if ($book['status'] === 'overdue') $has_overdue = true;
                                    if ($book['fine_amount'] > 0) $has_fines = true;
                                }
                            ?>
                            <tr>
                                <td><strong><?php echo $student['studentid']; ?></strong></td>
                                <td>
                                    <strong><?php echo $student['student_name']; ?></strong>
                                    <br><small class="text-muted"><?php echo $student['student_email']; ?></small>
                                </td>
                                <td><?php echo $student['student_phone']; ?></td>
                                <td>
                                    <span class="badge bg-primary"><?php echo $student['books_owed']; ?> books</span>
                                </td>
                              
                                <td>
                                    <?php if ($has_overdue): ?>
                                        <span class="badge bg-danger status-badge">Overdue</span>
                                    <?php endif; ?>
                                    <?php if ($has_fines): ?>
                                        <span class="badge bg-warning status-badge">Fines Due</span>
                                    <?php endif; ?>
                                    <?php if (!$has_overdue && !$has_fines): ?>
                                        <span class="badge bg-info status-badge">Issued</span>
                                    <?php endif; ?>
                                </td>
                                <td>
                                    <button class="btn btn-sm btn-info" data-bs-toggle="modal" data-bs-target="#studentModal<?php echo $student['studentid']; ?>">
                                        <i class="fas fa-eye me-1"></i>View Details
                                    </button>
                                  
                                </td>
                            </tr>
                            
                            <!-- Student Details Modal -->
                            <div class="modal fade" id="studentModal<?php echo $student['studentid']; ?>" tabindex="-1">
                                <div class="modal-dialog modal-lg">
                                    <div class="modal-content">
                                        <div class="modal-header">
                                            <h5 class="modal-title">Student Details: <?php echo $student['student_name']; ?></h5>
                                            <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                                        </div>
                                        <div class="modal-body">
                                            <div class="row">
                                                <div class="col-md-6">
                                                    <h6>Student Information</h6>
                                                    <p><strong>ID:</strong> <?php echo $student['studentid']; ?></p>
                                                    <p><strong>Name:</strong> <?php echo $student['student_name']; ?></p>
                                                    <p><strong>Email:</strong> <?php echo $student['student_email']; ?></p>
                                                    <p><strong>Phone:</strong> <?php echo $student['student_phone']; ?></p>
                                                </div>
                                                <div class="col-md-6">
                                                    <h6>Summary</h6>
                                                    <p><strong>Total Books Owed:</strong> <?php echo $student['books_owed']; ?></p>

                                                    <p><strong>Latest Due Date:</strong> <?php echo date('M j, Y', strtotime($student['latest_due_date'])); ?></p>
                                                </div>
                                            </div>
                                            <hr>
                                            <h6>Books Details</h6>
                                            <div class="table-responsive">
                                                <table class="table table-sm">
                                                    <thead>
                                                        <tr>
                                                            <th>Book Title</th>
                                                            <th>Author</th>
                                                            <th>Due Date</th>
                                                            <th>Days Overdue</th>
                                                            <th>Status</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody>
                                                        <?php foreach ($student_data['books'] as $book): 
                                                            $due_date = strtotime($book['due_date']);
                                                            $today = strtotime(date('Y-m-d'));
                                                            $is_overdue = $due_date < $today;
                                                        ?>
                                                        <tr>
                                                            <td><?php echo $book['title']; ?></td>
                                                            <td><?php echo $book['author']; ?></td>
                                                            <td><?php echo date('M j, Y', $due_date); ?></td>
                                                            <td>
                                                                <?php if ($is_overdue): ?>
                                                                    <span class="text-danger"><?php echo $book['days_overdue']; ?> days</span>
                                                                <?php else: ?>
                                                                    <span class="text-success">Not due yet</span>
                                                                <?php endif; ?>
                                                            </td>
                                                            
                                                            <td>
                                                                <span class="badge bg-<?php 
                                                                    if ($book['status'] == 'issued') echo 'warning';
                                                                    elseif ($book['status'] == 'returned') echo 'success';
                                                                    else echo 'danger';
                                                                ?>">
                                                                    <?php echo ucfirst($book['status']); ?>
                                                                </span>
                                                            </td>
                                                        </tr>
                                                        <?php endforeach; ?>
                                                    </tbody>
                                                </table>
                                            </div>
                                        </div>
                                       
                                    </div>
                                </div>
                            </div>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                <?php else: ?>
                <div class="alert alert-success text-center">
                    <i class="fas fa-check-circle me-2"></i>No students found with the current filters.
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>

</body>
</html>
<?php
} else {
    header("location:../index.php");
}
?>