<?php
session_start();
require_once '../config/database.php';

// Auth: allow admin, teacher, student to view
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['user_role'], ['admin','teacher','student'])) {
    header('Location: ../login.php');
    exit();
}

$success_message = '';
$error_message = '';

// Inputs
$q = trim($_GET['q'] ?? '');
$category = trim($_GET['category'] ?? '');
$availability = trim($_GET['availability'] ?? ''); // available|all
$export = trim($_GET['export'] ?? ''); // csv

// Load categories
$categories = [];
try {
    $stmt = $pdo->query("SELECT DISTINCT category FROM library_books WHERE status='active' AND category IS NOT NULL AND category<>'' ORDER BY category");
    $categories = $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (Exception $e) {}

// Summary stats
$stats = [
    'total_books' => 0,
    'total_copies' => 0,
    'available_copies' => 0,
    'issued_books' => 0,
    'overdue_books' => 0,
];
try {
    $stmt = $pdo->query("SELECT 
        COUNT(*) as total_books,
        COALESCE(SUM(total_copies),0) as total_copies,
        COALESCE(SUM(available_copies),0) as available_copies
      FROM library_books WHERE status='active'");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        $stats['total_books'] = (int)$row['total_books'];
        $stats['total_copies'] = (int)$row['total_copies'];
        $stats['available_copies'] = (int)$row['available_copies'];
    }
    // issued & overdue
    $stmt = $pdo->query("SELECT 
        SUM(CASE WHEN status='issued' THEN 1 ELSE 0 END) as issued_books,
        SUM(CASE WHEN status='overdue' THEN 1 ELSE 0 END) as overdue_books
      FROM library_issues");
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        $stats['issued_books'] = (int)$row['issued_books'];
        $stats['overdue_books'] = (int)$row['overdue_books'];
    }
} catch (Exception $e) {}

// Build query
$where = ["status='active'"];
$params = [];
if ($q !== '') {
    $where[] = "(title LIKE ? OR author LIKE ? OR isbn LIKE ?)";
    $like = "%$q%";
    $params[] = $like; $params[] = $like; $params[] = $like;
}
if ($category !== '') { $where[] = "category = ?"; $params[] = $category; }
if ($availability === 'available') { $where[] = "available_copies > 0"; }
$whereSql = implode(' AND ', $where);

$sql = "SELECT id, title, author, isbn, category, publisher, publication_year, total_copies, available_copies, location
        FROM library_books WHERE $whereSql ORDER BY title";

$books = [];
try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $books = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    $error_message = 'তথ্য লোড করতে সমস্যা হয়েছে: ' . $e->getMessage();
}

// CSV export
if ($export === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=library_books_' . date('Ymd_His') . '.csv');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Title','Author','ISBN','Category','Publisher','Year','Total Copies','Available Copies','Location']);
    foreach ($books as $b) {
        fputcsv($out, [
            $b['title'], $b['author'], $b['isbn'], $b['category'], $b['publisher'], $b['publication_year'],
            $b['total_copies'], $b['available_copies'], $b['location']
        ]);
    }
    fclose($out);
    exit;
}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>লাইব্রেরি - বইয়ের তালিকা</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="../assets/css/style.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-primary">
    <div class="container-fluid">
        <a class="navbar-brand" href="../index.php"><i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম</a>
        <div class="navbar-nav ms-auto">
            <?php if (in_array($_SESSION['user_role'], ['admin','teacher'])): ?>
                <a class="nav-link" href="manage.php"><i class="fas fa-tools"></i> ম্যানেজ</a>
            <?php endif; ?>
            <a class="nav-link" href="../index.php"><i class="fas fa-home"></i> হোম</a>
            <a class="nav-link" href="../logout.php"><i class="fas fa-sign-out-alt"></i> লগআউট</a>
        </div>
    </div>
</nav>

<div class="container-fluid mt-4">
    <div class="d-flex justify-content-between align-items-center mb-3">
        <h2><i class="fas fa-book"></i> লাইব্রেরি বইয়ের তালিকা</h2>
        <div>
            <a class="btn btn-success me-2" href="?<?= http_build_query(array_merge($_GET, ['export'=>'csv'])) ?>"><i class="fas fa-file-csv"></i> CSV</a>
            <button onclick="window.print()" class="btn btn-primary"><i class="fas fa-print"></i> প্রিন্ট</button>
        </div>
    </div>

    <!-- Stats -->
    <div class="row mb-4">
        <div class="col-md-2">
            <div class="card bg-primary text-white"><div class="card-body text-center"><div class="fw-bold">মোট বই</div><div class="fs-4"><?= (int)$stats['total_books'] ?></div></div></div>
        </div>
        <div class="col-md-2">
            <div class="card bg-info text-white"><div class="card-body text-center"><div class="fw-bold">মোট কপি</div><div class="fs-4"><?= (int)$stats['total_copies'] ?></div></div></div>
        </div>
        <div class="col-md-2">
            <div class="card bg-success text-white"><div class="card-body text-center"><div class="fw-bold">উপলব্ধ</div><div class="fs-4"><?= (int)$stats['available_copies'] ?></div></div></div>
        </div>
        <div class="col-md-3">
            <div class="card bg-warning text-white"><div class="card-body text-center"><div class="fw-bold">ইস্যুকৃত</div><div class="fs-4"><?= (int)$stats['issued_books'] ?></div></div></div>
        </div>
        <div class="col-md-3">
            <div class="card bg-danger text-white"><div class="card-body text-center"><div class="fw-bold">মেয়াদোত্তীর্ণ</div><div class="fs-4"><?= (int)$stats['overdue_books'] ?></div></div></div>
        </div>
    </div>

    <?php if ($error_message): ?>
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
            <i class="fas fa-exclamation-triangle"></i> <?= htmlspecialchars($error_message) ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    <?php endif; ?>

    <!-- Filters -->
    <form method="GET" class="card p-3 mb-3">
        <div class="row g-3 align-items-end">
            <div class="col-md-4">
                <label class="form-label">সার্চ (নাম/লেখক/ISBN)</label>
                <input type="text" class="form-control" name="q" value="<?= htmlspecialchars($q) ?>" placeholder="উদাহরণ: Physics / Zafar Iqbal / 978...">
            </div>
            <div class="col-md-3">
                <label class="form-label">বিভাগ</label>
                <select class="form-select" name="category">
                    <option value="">সব বিভাগ</option>
                    <?php foreach ($categories as $cat): ?>
                        <option value="<?= htmlspecialchars($cat) ?>" <?= $category===$cat?'selected':''; ?>><?= htmlspecialchars($cat) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3">
                <label class="form-label">অবস্থা</label>
                <select class="form-select" name="availability">
                    <option value="all" <?= $availability==='all' || $availability===''? 'selected':''; ?>>সব</option>
                    <option value="available" <?= $availability==='available'? 'selected':''; ?>>শুধু উপলব্ধ</option>
                </select>
            </div>
            <div class="col-md-2">
                <button type="submit" class="btn btn-primary w-100"><i class="fas fa-search"></i> খুঁজুন</button>
            </div>
        </div>
    </form>

    <!-- Books Table -->
    <div class="card">
        <div class="card-header">
            <h5 class="mb-0"><i class="fas fa-list"></i> বইয়ের তালিকা</h5>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-striped">
                    <thead>
                        <tr>
                            <th>নাম</th>
                            <th>লেখক</th>
                            <th>ISBN</th>
                            <th>বিভাগ</th>
                            <th>প্রকাশক</th>
                            <th>বছর</th>
                            <th class="text-end">উপলব্ধ/মোট</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($books)): ?>
                            <tr><td colspan="7" class="text-center text-muted">কোন তথ্য পাওয়া যায়নি</td></tr>
                        <?php else: ?>
                            <?php foreach ($books as $b): ?>
                                <?php 
                                    $avail = (int)$b['available_copies'];
                                    $total = (int)$b['total_copies'];
                                    $badge = $avail>0 ? 'bg-success' : 'bg-danger';
                                ?>
                                <tr>
                                    <td><strong><?= htmlspecialchars($b['title']) ?></strong></td>
                                    <td><?= htmlspecialchars($b['author']) ?></td>
                                    <td><?= htmlspecialchars($b['isbn']) ?></td>
                                    <td><?= htmlspecialchars($b['category']) ?></td>
                                    <td><?= htmlspecialchars($b['publisher']) ?></td>
                                    <td><?= htmlspecialchars((string)$b['publication_year']) ?></td>
                                    <td class="text-end"><span class="badge <?= $badge ?>"><?= $avail ?></span> / <?= $total ?></td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>