<?php
session_start();
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
include "../include/config.php";
$userlogin = $_SESSION['email'];
$intake = $_SESSION['department'];

if (isset($_SESSION['email'])) {
    // 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'];
    $topAuthors = mysqli_query($conn, "SELECT author, COUNT(*) as count FROM catalogue GROUP BY author ORDER BY count DESC LIMIT 5");
    $editionStats = mysqli_query($conn, "SELECT edition, COUNT(*) as count FROM catalogue GROUP BY edition ORDER BY count DESC");
    $recentAdditions = mysqli_query($conn, "SELECT * FROM catalogue ORDER BY id DESC LIMIT 5");
    
    // 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'];
    
    // Most borrowed books
 
    
    // Recent issues
    $recentIssues = mysqli_query($conn, "
        SELECT bi.*, c.title, c.author 
        FROM book_issues bi 
        JOIN catalogue c ON bi.book_id = c.id 
        ORDER BY bi.issue_date DESC 
        LIMIT 5
    ");
    
    // Monthly borrowing trends (last 6 months)
    $monthlyTrends = mysqli_query($conn, "
        SELECT 
            DATE_FORMAT(issue_date, '%Y-%m') as month,
            COUNT(*) as issues_count,
            SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) as returned_count,
            SUM(CASE WHEN status = 'overdue' THEN 1 ELSE 0 END) as overdue_count
        FROM book_issues 
        WHERE issue_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
        GROUP BY DATE_FORMAT(issue_date, '%Y-%m')
        ORDER BY month DESC
    ");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Catalogue Analysis</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">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<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; }
h4 { color: var(--primary); margin-bottom: 20px; }
.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; }
.chart-container { position: relative; height: 300px; margin-bottom: 30px; }
.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); }
.btn-owing { 
    background: linear-gradient(135deg, #ff6b6b, #ff4757);
    border: none;
    color: white;
    transition: all 0.3s ease;
}
.btn-owing:hover {
    background: linear-gradient(135deg, #ff4757, #ff6b6b);
    transform: translateY(-2px);
    box-shadow: 0 4px 15px rgba(255, 107, 107, 0.3);
    color: white;
}
.action-buttons {
    display: flex;
    gap: 10px;
    justify-content: center;
    margin-bottom: 20px;
}
</style>
</head>
<body>
    <?php include "../include/nav.php"; ?>
    <div class="content">
        <div class="container">
            <h2 class="text-center mt-4">Library Analytics Dashboard</h2>
            
            <!-- Action Buttons -->
            <div class="action-buttons">
                <a href="owing.php" class="btn btn-owing">
                    <i class="fas fa-exclamation-triangle me-2"></i>View Students Owing Books
                </a>
                <a href="students_with_books.php" class="btn btn-primary">
                    <i class="fas fa-users me-2"></i>View All Issued Books
                </a>
            </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="stat-card">
                        <p>Average per Book</p>
                        <h3><?php echo $totalBooks > 0 ? round($totalCopies / $totalBooks, 1) : 0; ?></h3>
                        <p>Copies per Title</p>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="stat-card">
                        <p>Total Loans</p>
                        <h3><?php echo $totalIssued; ?></h3>
                        <p>All Time</p>
                    </div>
                </div>
            </div>
            
            <!-- Loan Statistics -->
            <div class="row mt-4">
                <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 returned-stat">
                        <p>Returned</p>
                        <h3><?php echo $returnedBooks; ?></h3>
                        <p>Books Returned</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 class="col-md-3">
                    <div class="loan-stat-card fines-stat">
                        <p>Total Fines</p>
                        <h3>$<?php echo number_format($totalFines, 2); ?></h3>
                        <p>Collected</p>
                    </div>
                </div>
            </div>
            
            <!-- Charts Row -->
            <div class="row mt-4">
                <div class="col-md-6">
                    <div class="card">
                        <h4>Top Authors</h4>
                        <div class="chart-container">
                            <canvas id="authorsChart"></canvas>
                        </div>
                    </div>
                </div>
                <div class="col-md-6">
                    <div class="card">
                        <h4>Most Borrowed Books</h4>
                        <div class="chart-container">
                            <canvas id="popularBooksChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
            
            <!-- Monthly Trends -->
            <div class="card mt-4">
                <h4>Borrowing Trends (Last 6 Months)</h4>
                <div class="chart-container">
                    <canvas id="monthlyTrendsChart"></canvas>
                </div>
            </div>
            
           
                
            <!-- Detailed Statistics -->
            <div class="card mt-4">
                <h4>Detailed Catalogue Statistics</h4>
                <div class="row">
                    <div class="col-md-6">
                        <h5>Books by Copies Available</h5>
                        <?php
                        $copyRanges = [
                            '1' => '1 copy',
                            '2-5' => '2-5 copies',
                            '6-10' => '6-10 copies',
                            '11+' => '11+ copies'
                        ];
                        
                        foreach ($copyRanges as $range => $label) {
                            if ($range == '1') {
                                $query = "SELECT COUNT(*) as count FROM catalogue WHERE copies = 1";
                            } elseif ($range == '2-5') {
                                $query = "SELECT COUNT(*) as count FROM catalogue WHERE copies BETWEEN 2 AND 5";
                            } elseif ($range == '6-10') {
                                $query = "SELECT COUNT(*) as count FROM catalogue WHERE copies BETWEEN 6 AND 10";
                            } else {
                                $query = "SELECT COUNT(*) as count FROM catalogue WHERE copies >= 11";
                            }
                            
                            $result = mysqli_fetch_assoc(mysqli_query($conn, $query));
                            $count = $result['count'];
                            $percentage = $totalBooks > 0 ? round(($count / $totalBooks) * 100, 1) : 0;
                        ?>
                        <div class="mb-2">
                            <div class="d-flex justify-content-between">
                                <span><?php echo $label; ?></span>
                                <span><?php echo $count; ?> (<?php echo $percentage; ?>%)</span>
                            </div>
                            <div class="progress">
                                <div class="progress-bar" role="progressbar" style="width: <?php echo $percentage; ?>%" 
                                     aria-valuenow="<?php echo $percentage; ?>" aria-valuemin="0" aria-valuemax="100"></div>
                            </div>
                        </div>
                        <?php } ?>
                    </div>
                    <div class="col-md-6">
                        <h5>Books Without Covers</h5>
                        <?php
                        $noCover = mysqli_fetch_assoc(mysqli_query($conn, 
                            "SELECT COUNT(*) as count FROM catalogue WHERE cover = '' OR cover IS NULL"));
                        $noCoverCount = $noCover['count'];
                        $withCoverCount = $totalBooks - $noCoverCount;
                        $noCoverPercentage = $totalBooks > 0 ? round(($noCoverCount / $totalBooks) * 100, 1) : 0;
                        $withCoverPercentage = $totalBooks > 0 ? round(($withCoverCount / $totalBooks) * 100, 1) : 0;
                        ?>
                        <div class="mb-2">
                            <div class="d-flex justify-content-between">
                                <span>With Covers</span>
                                <span><?php echo $withCoverCount; ?> (<?php echo $withCoverPercentage; ?>%)</span>
                            </div>
                            <div class="progress">
                                <div class="progress-bar bg-success" role="progressbar" style="width: <?php echo $withCoverPercentage; ?>%" 
                                     aria-valuenow="<?php echo $withCoverPercentage; ?>" aria-valuemin="0" aria-valuemax="100"></div>
                            </div>
                        </div>
                        <div class="mb-2">
                            <div class="d-flex justify-content-between">
                                <span>Without Covers</span>
                                <span><?php echo $noCoverCount; ?> (<?php echo $noCoverPercentage; ?>%)</span>
                            </div>
                            <div class="progress">
                                <div class="progress-bar bg-warning" role="progressbar" style="width: <?php echo $noCoverPercentage; ?>%" 
                                     aria-valuenow="<?php echo $noCoverPercentage; ?>" aria-valuemin="0" aria-valuemax="100"></div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>
    // Prepare data for authors chart
    const authorsData = {
        labels: [
            <?php 
            $authorLabels = [];
            $authorCounts = [];
            mysqli_data_seek($topAuthors, 0);
            while ($author = mysqli_fetch_assoc($topAuthors)) {
                $authorLabels[] = "'" . addslashes($author['author']) . "'";
                $authorCounts[] = $author['count'];
            }
            echo implode(', ', $authorLabels);
            ?>
        ],
        datasets: [{
            label: 'Number of Books',
            data: [<?php echo implode(', ', $authorCounts); ?>],
            backgroundColor: [
                'rgba(52, 163, 128, 0.7)',
                'rgba(35, 134, 101, 0.7)',
                'rgba(26, 101, 76, 0.7)',
                'rgba(20, 80, 60, 0.7)',
                'rgba(15, 60, 45, 0.7)'
            ],
            borderColor: [
                'rgba(52, 163, 128, 1)',
                'rgba(35, 134, 101, 1)',
                'rgba(26, 101, 76, 1)',
                'rgba(20, 80, 60, 1)',
                'rgba(15, 60, 45, 1)'
            ],
            borderWidth: 1
        }]
    };

    // Prepare data for popular books chart
    const popularBooksData = {
        labels: [
            <?php 
            $bookLabels = [];
            $bookBorrowCounts = [];
            while ($book = mysqli_fetch_assoc($popularBooks)) {
                $bookLabels[] = "'" . addslashes($book['title']) . "'";
                $bookBorrowCounts[] = $book['borrow_count'];
            }
            echo implode(', ', $bookLabels);
            ?>
        ],
        datasets: [{
            label: 'Times Borrowed',
            data: [<?php echo implode(', ', $bookBorrowCounts); ?>],
            backgroundColor: [
                'rgba(255, 107, 107, 0.7)',
                'rgba(29, 209, 161, 0.7)',
                'rgba(254, 202, 87, 0.7)',
                'rgba(84, 160, 255, 0.7)',
                'rgba(95, 39, 205, 0.7)'
            ],
            borderColor: [
                'rgba(255, 107, 107, 1)',
                'rgba(29, 209, 161, 1)',
                'rgba(254, 202, 87, 1)',
                'rgba(84, 160, 255, 1)',
                'rgba(95, 39, 205, 1)'
            ],
            borderWidth: 1
        }]
    };

    // Prepare data for monthly trends chart
    const monthlyTrendsData = {
        labels: [
            <?php 
            $monthLabels = [];
            $issueCounts = [];
            $returnCounts = [];
            $overdueCounts = [];
            
            mysqli_data_seek($monthlyTrends, 0);
            while ($trend = mysqli_fetch_assoc($monthlyTrends)) {
                $monthLabels[] = "'" . date('M Y', strtotime($trend['month'] . '-01')) . "'";
                $issueCounts[] = $trend['issues_count'];
                $returnCounts[] = $trend['returned_count'];
                $overdueCounts[] = $trend['overdue_count'];
            }
            echo implode(', ', $monthLabels);
            ?>
        ],
        datasets: [
            {
                label: 'Books Issued',
                data: [<?php echo implode(', ', $issueCounts); ?>],
                backgroundColor: 'rgba(52, 163, 128, 0.5)',
                borderColor: 'rgba(52, 163, 128, 1)',
                borderWidth: 2
            },
            {
                label: 'Books Returned',
                data: [<?php echo implode(', ', $returnCounts); ?>],
                backgroundColor: 'rgba(84, 160, 255, 0.5)',
                borderColor: 'rgba(84, 160, 255, 1)',
                borderWidth: 2
            },
            {
                label: 'Overdue Books',
                data: [<?php echo implode(', ', $overdueCounts); ?>],
                backgroundColor: 'rgba(255, 107, 107, 0.5)',
                borderColor: 'rgba(255, 107, 107, 1)',
                borderWidth: 2
            }
        ]
    };

    // Create charts when page loads
    document.addEventListener('DOMContentLoaded', function() {
        // Authors bar chart
        const authorsCtx = document.getElementById('authorsChart').getContext('2d');
        new Chart(authorsCtx, {
            type: 'bar',
            data: authorsData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            precision: 0
                        }
                    }
                }
            }
        });

        // Popular books chart
        const popularBooksCtx = document.getElementById('popularBooksChart').getContext('2d');
        new Chart(popularBooksCtx, {
            type: 'bar',
            data: popularBooksData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            precision: 0
                        }
                    }
                }
            }
        });

        // Monthly trends chart
        const monthlyTrendsCtx = document.getElementById('monthlyTrendsChart').getContext('2d');
        new Chart(monthlyTrendsCtx, {
            type: 'line',
            data: monthlyTrendsData,
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            precision: 0
                        }
                    }
                }
            }
        });
    });
    </script>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
<?php
} else {
    header("location:../index.php");
}