<?php
session_start();
error_reporting(0);
include('includes/config.php');

if (strlen($_SESSION['alogin']) == 0) {
    header('location:index.php');
    exit;
}

$bookname = $_GET['bookname'] ?? '';
$borrowerType = $_GET['borrowerType'] ?? '';
$from = !empty($_GET['from']) ? $_GET['from'] : date('Y-m-01');
$to = !empty($_GET['to']) ? $_GET['to'] : date('Y-m-d');
$status = $_GET['status'] ?? '';
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8" />
    <title>Issued Books Report | Library System</title>
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="icon" type="image/x-icon" href="../<?php echo $_SESSION['logo']; ?>">

    <!-- Bootstrap 4.6 -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- FontAwesome -->
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <!-- DataTables -->
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap4.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.bootstrap4.min.css" rel="stylesheet">

    <style>
        .square-card {
            width: 100%;
            background-color: #f8f9fa;
            border: 1px solid #dee2e6;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.05);
            border-radius: 5px;
        }

        .table-wrapper {
            overflow-x: auto;
            padding: 10px;
        }

        .header-line {
            color: #0d6efd;
            font-size: 20px;
        }

        .dataTables_filter label {
            color: #000 !important;
            font-weight: 500;
        }

        .dataTables_filter input {
            height: 32px;
            font-size: 0.95rem;
            padding: 4px 8px;
        }

        form.form-row .form-control {
            min-height: 38px;
        }
    </style>
</head>

<body>
    <div class="d-flex flex-column min-vh-100">
        <?php include('includes/header.php'); ?>

        <div class="main-content flex-grow-1">
            <div class="row pad-botm mb-4">
                <div class="col-md-12">
                    <h5 class="header-line" style="color:#0d6efd;">
                        <i class="fa fa-book-reader mr-2"></i> ISSUED BOOKS REPORT
                    </h5>
                </div>
            </div>

            <!-- Filter Form -->
            <form method="get" class="form-row align-items-center mb-4">
                <div class="col-md-2">
                    <input type="text" name="bookname" class="form-control" placeholder="Book Name"
                        value="<?= htmlentities($bookname) ?>">
                </div>
                <div class="col-md-2">
                    <select name="borrowerType" class="form-control">
                        <option value="">All Borrowers</option>
                        <option value="student" <?= ($borrowerType == 'student') ? 'selected' : '' ?>>Student</option>
                        <option value="staff" <?= ($borrowerType == 'staff') ? 'selected' : '' ?>>Staff</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <input type="date" name="from" class="form-control" value="<?= htmlentities($from) ?>">
                </div>
                <div class="col-md-2">
                    <input type="date" name="to" class="form-control" value="<?= htmlentities($to) ?>">
                </div>
                <div class="col-md-2">
                    <select name="status" class="form-control">
                        <option value="">All Status</option>
                        <option value="returned" <?= $status == 'returned' ? 'selected' : '' ?>>Returned</option>
                        <option value="notreturned" <?= $status == 'notreturned' ? 'selected' : '' ?>>Not Returned</option>
                    </select>
                </div>
                <div class="col-md-2 d-flex">
                    <button class="btn btn-primary mr-2" type="submit">
                        <i class="fa fa-filter"></i> Filter
                    </button>
                    <a href="report-issued-books.php" class="btn btn-secondary">
                        <i class="fa fa-rotate-left"></i> Reset
                    </a>
                </div>
            </form>

            <!-- Issued Books Table -->
            <div class="card square-card mb-5">
                <div class="card-header bg-secondary text-white">
                    <i class="fa fa-book mr-2"></i> Issued Books List
                </div>
                <div class="card-body table-wrapper">
                    <table id="issuedBooksTable" class="table table-bordered table-hover table-sm text-dark">
                        <thead class="table-theme-header">
                            <tr>
                                <th>#</th>
                                <th>Borrower</th>
                                <th>Type</th>
                                <th>Book</th>
                                <th>Book Copy No</th>
                                <th>ACC NO</th>
                                <th>Issued Date</th>
                                <th>Return Date</th>
                                <th>Fine (₹)</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $sql = "SELECT ib.id as rid, ib.BorrowerType, ib.BorrowerID, ib.BookId, ib.CopyId, ib.IssuesDate, ib.DueDate, ib.ReturnDate, ib.fine,
                                   b.BookName, bc.accession_no,
                                   bc.book_no,
                                   s.FullName as StudentName,
                                   st.FullName as StaffName
                            FROM tblissuedbookdetails ib
                            JOIN tblbooks b ON b.id = ib.BookId
                            LEFT JOIN tblbookcopies bc ON bc.id = ib.CopyId
                            LEFT JOIN tblstudents s ON LOWER(ib.BorrowerType)='student' AND ib.BorrowerID=s.StudentId
                            LEFT JOIN tblstaff st ON LOWER(ib.BorrowerType)='staff' AND ib.BorrowerID=st.StaffId
                            WHERE 1=1";

                            if ($borrowerType == 'student')
                                $sql .= " AND LOWER(ib.BorrowerType)='student'";
                            elseif ($borrowerType == 'staff')
                                $sql .= " AND LOWER(ib.BorrowerType)='staff'";

                            if ($bookname)
                                $sql .= " AND b.BookName LIKE :bookname";

                            // Date filter
                            $sql .= " AND DATE(ib.IssuesDate) BETWEEN :from AND :to";

                            if ($status == 'returned') {
                                $sql .= " AND ib.ReturnDate IS NOT NULL";
                            } elseif ($status == 'notreturned') {
                                $sql .= " AND ib.ReturnDate IS NULL";
                            }

                            $sql .= " ORDER BY ib.id DESC";

                            $query = $dbh->prepare($sql);

                            if ($bookname) {
                                $bk = "%$bookname%";
                                $query->bindParam(':bookname', $bk, PDO::PARAM_STR);
                            }
                            $query->bindParam(':from', $from, PDO::PARAM_STR);
                            $query->bindParam(':to', $to, PDO::PARAM_STR);

                            $query->execute();
                            $results = $query->fetchAll(PDO::FETCH_OBJ);
                            $cnt = 1;

                            foreach ($results as $row): ?>
                                <tr>
                                    <td><?= $cnt++; ?></td>
                                    <td><?= $row->BorrowerType == 'student' ? htmlentities($row->StudentName) : htmlentities($row->StaffName); ?>
                                    </td>
                                    <td>
                                        <?php if ($row->BorrowerType == 'student'): ?>
                                            <span class="badge bg-success" style="color:white;">Student</span>
                                        <?php else: ?>
                                            <span class="badge bg-primary" style="color:white;">Staff</span>
                                        <?php endif; ?>
                                    </td>
                                    <td><?= htmlentities($row->BookName); ?></td>
                                    <td><?= htmlentities($row->book_no ?? '-'); ?></td>
                                    <td><?= htmlentities($row->accession_no); ?></td>
                                    <td><?= date('d-m-Y h:i A', strtotime($row->IssuesDate)); ?></td>
                                    <td>
                                        <?= $row->ReturnDate ? date('d-m-Y h:i A', strtotime($row->ReturnDate)) : "<span class='text-danger'>Not Returned</span>"; ?>
                                    </td>
                                    <td><?= htmlentities($row->fine ?? 0); ?></td>
                                    <td>
                                        <?= $row->ReturnDate ? "<span class='text-success'>Returned</span>" : "<span class='text-warning'>Issued</span>"; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <?php include('includes/footer.php'); ?>
    </div>

    <!-- Scripts -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap4.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.bootstrap4.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>

    <script>
        $(document).ready(function () {
            $('#issuedBooksTable').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    { extend: 'print', className: 'btn btn-info', text: '<i class="fa fa-print"></i> Print' },
                    { extend: 'excelHtml5', className: 'btn btn-success', text: '<i class="fa fa-file-excel"></i> Export Excel' }
                ],
                language: { search: "🔍 Search issued books:" },
                pageLength: 10,
                lengthChange: true,
                ordering: true,
                responsive: true
            });
        });
    </script>
</body>

</html>