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

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

// Filters
$bookname = $_GET['bookname'] ?? '';
$borrowerType = $_GET['borrowerType'] ?? '';

// Default today's date if empty
$from = !empty($_GET['from']) ? $_GET['from'] : date('Y-m-01');
$to   = !empty($_GET['to'])   ? $_GET['to']   : date('Y-m-d');
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8" />
  <title>Not Returned 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 6 -->
  <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; margin-bottom: 15px; }
    .dataTables_filter label { color: #000 !important; font-weight: 500; }
    .dataTables_filter input { height: 32px; font-size: 0.95rem; padding: 4px 8px; }
  </style>
</head>
<body>

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

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

    <!-- Filter Form -->
    <form method="get" class="form-row mb-4">
      <div class="col-md-2 mb-2">
        <input type="text" name="bookname" class="form-control" placeholder="Book Name"
               value="<?= htmlentities($bookname) ?>">
      </div>
      <div class="col-md-2 mb-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 mb-2">
        <input type="date" name="from" class="form-control" value="<?= htmlentities($from) ?>">
      </div>
      <div class="col-md-2 mb-2">
        <input type="date" name="to" class="form-control" value="<?= htmlentities($to) ?>">
      </div>
      <div class="col-md-2 mb-2">
        <button class="btn btn-primary btn-block" type="submit">
          <i class="fa fa-filter mr-1"></i> Filter
        </button>
      </div>
    </form>

    <!-- Not Returned Books Table -->
    <div class="card square-card">
      <div class="card-header bg-secondary text-white">
        <i class="fa fa-book"></i> Not Returned Books
      </div>
      <div class="card-body table-wrapper">
        <table id="notReturnedBooks" 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 Name</th>
              <th>Book Copy No</th>
              <th>ISBN</th>
              <th>Issued Date</th>
              <th>Status</th>
            </tr>
          </thead>
          <tbody>
            <?php
            // SQL Query
            $sql = "SELECT i.id, i.BorrowerType, i.BorrowerID, i.BookId, i.CopyId, i.IssuesDate, i.fine,
                           b.BookName, b.ISBNNumber,
                           bc.book_no,
                           s.FullName as StudentName,
                           st.FullName as StaffName
                    FROM tblissuedbookdetails i
                    JOIN tblbooks b ON b.id = i.BookId
                    LEFT JOIN tblbookcopies bc ON bc.id = i.CopyId
                    LEFT JOIN tblstudents s ON LOWER(i.BorrowerType)='student' AND i.BorrowerID=s.StudentId
                    LEFT JOIN tblstaff st ON LOWER(i.BorrowerType)='staff' AND i.BorrowerID=st.StaffId
                    WHERE i.ReturnDate IS NULL";

            // Borrower type filter
            if($borrowerType=='student') $sql .= " AND LOWER(i.BorrowerType)='student'";
            elseif($borrowerType=='staff') $sql .= " AND LOWER(i.BorrowerType)='staff'";

            // Book name filter
            if($bookname) $sql .= " AND b.BookName LIKE :bookname";

            // Date range filter (full day)
            if($from) $sql .= " AND i.IssuesDate >= :from";
            if($to)   $sql .= " AND i.IssuesDate <= :to";

            $sql .= " ORDER BY i.IssuesDate DESC";

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

            // Bind parameters
            if($bookname){ $bk = "%$bookname%"; $query->bindParam(':bookname', $bk, PDO::PARAM_STR); }
            if($from) $query->bindValue(':from', $from . " 00:00:00", PDO::PARAM_STR);
            if($to)   $query->bindValue(':to',   $to   . " 23:59:59", PDO::PARAM_STR);

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

            // if($query->rowCount() > 0):
              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->ISBNNumber); ?></td>
                <td><?= date('d-m-Y h:i A', strtotime($row->IssuesDate)); ?></td>
                <td><span class="text-danger font-weight-bold">Not Returned</span></td>
              </tr>
            <?php
              endforeach;
            // else:
            //   echo "<tr><td colspan='8' class='text-center text-muted'>No records found.</td></tr>";
            // endif;
            ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

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

<!-- 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.print.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>

<script>
$(document).ready(function () {
  $('#notReturnedBooks').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 books:" },
    pageLength: 10,
    lengthChange: true,
    ordering: true,
    responsive: true
  });
});
</script>

</body>
</html>
