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

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

// Filters
$category = $_GET['category'] ?? '';
$rack = $_GET['rack'] ?? '';
$author = $_GET['author'] ?? '';
$department = $_GET['department'] ?? '';
$degree = $_GET['degree'] ?? '';
$book_status = $_GET['book_status'] ?? '';
?>
<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>Book Copies 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 -->
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.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">
  <!-- FontAwesome -->
  <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
  <!-- Select2 -->
  <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />

  <style>
    .square-card {
      background-color: #fff;
      border: 1px solid #dee2e6;
      border-radius: 6px;
      box-shadow: 0 4px 12px rgba(0, 0, 0, 0.05);
    }

    .header-line {
      font-size: 22px;
      font-weight: 600;
      color: #2258d5;
    }

    .main-content {
      margin-left: 180px;
      margin-top: 50px;
      padding: 20px;
    }

    .form-control option[value^="no"] {
      color: red;
      font-weight: bold;
    }

    .badge {
      font-size: 13px;
      padding: 6px 10px;
      border-radius: 6px;
    }

    .deleted-item {
      opacity: 0.6;
      /* faded effect */
      color: #6c757d !important;
      /* muted gray text */
    }

    .deleted-label {
      font-size: 12px;
      color: #dc3545;
      /* subtle red */
      font-style: italic;
      margin-left: 4px;
    }
  </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 mb-4">
        <div class="col-md-12 d-flex justify-content-between align-items-center">
          <h5 class="header-line" style="color:#0d6efd; font-size:20px;"><i class="fa fa-copy mr-2"></i> BOOK COPIES
            REPORT
          </h5>
        </div>
      </div>

      <!-- Filters -->
      <form method="get" class="row mb-3">
        <div class="col-md-2">
          <select name="category" id="category" class="form-control select2-ajax" data-placeholder="Search Category">
            <?php if ($category): ?>
              <?php
              $stmt = $dbh->prepare("SELECT CategoryName FROM tblcategory WHERE id=:id AND is_deleted=0");
              $stmt->bindParam(':id', $category, PDO::PARAM_INT);
              $stmt->execute();
              $cat = $stmt->fetch(PDO::FETCH_ASSOC);
              ?>
              <option value="<?= $category ?>" selected><?= htmlentities($cat['CategoryName'] ?? 'No Category') ?>
              </option>
            <?php endif; ?>
          </select>
        </div>

        <div class="col-md-2">
          <select name="rack" id="rack" class="form-control select2-ajax" data-placeholder="Search Rack">
            <?php if ($rack) {
              if ($rack === 'norack') {
                $rk = ['rack_no' => 'No Cupboard'];
              } else {
                $stmt = $dbh->prepare("SELECT rack_no FROM tbl_racks WHERE id = :id LIMIT 1");
                $stmt->bindParam(':id', $rack, PDO::PARAM_INT);
                $stmt->execute();
                $rk = $stmt->fetch(PDO::FETCH_ASSOC);
              }
              ?>
              <option value="<?= htmlentities($rk['rack_no']) ?>" selected>
                <?= htmlentities($rk['rack_no'] ?? 'No Cupboard') ?>
              </option>
              <?php
            }
            ?>
          </select>
        </div>

        <div class="col-md-2">
          <select name="author" id="author" class="form-control select2-ajax" data-placeholder="Search Author">
            <?php if ($author): ?>
              <?php
              $stmt = $dbh->prepare("SELECT AuthorName FROM tblauthors WHERE id=:id AND is_deleted=0");
              $stmt->bindParam(':id', $author, PDO::PARAM_INT);
              $stmt->execute();
              $auth = $stmt->fetch(PDO::FETCH_ASSOC);
              ?>
              <option value="<?= $author ?>" selected><?= htmlentities($auth['AuthorName'] ?? 'No Author') ?></option>
            <?php endif; ?>
          </select>
        </div>

        <div class="col-md-2">
          <select name="department" id="department" class="form-control select2-ajax"
            data-placeholder="Search Department">
            <?php if ($department): ?>
              <?php
              $stmt = $dbh->prepare("SELECT department_name FROM tbldepartment WHERE department_id=:id");
              $stmt->bindParam(':id', $department, PDO::PARAM_INT);
              $stmt->execute();
              $dept = $stmt->fetch(PDO::FETCH_ASSOC);
              ?>
              <option value="<?= $department ?>" selected><?= htmlentities($dept['department_name'] ?? 'No Department') ?>
              </option>
            <?php endif; ?>
          </select>
        </div>

        <div class="col-md-2">
          <select name="degree" id="degree" class="form-control select2-ajax" data-placeholder="Search Degree">
            <?php if ($degree): ?>
              <?php
              $stmt = $dbh->prepare("SELECT degree_name FROM tbldegree WHERE degree_id=:id");
              $stmt->bindParam(':id', $degree, PDO::PARAM_INT);
              $stmt->execute();
              $deg = $stmt->fetch(PDO::FETCH_ASSOC);
              ?>
              <option value="<?= $degree ?>" selected><?= htmlentities($deg['degree_name'] ?? 'No Degree') ?></option>
            <?php endif; ?>
          </select>
        </div>

        <div class="col-md-2">
          <select name="book_status" class="form-control select2">
            <option value="">All Status</option>
            <option value="available" <?= ($book_status == 'available') ? 'selected' : '' ?>>Available</option>
            <option value="issued" <?= ($book_status == 'issued') ? 'selected' : '' ?>>Issued</option>
            <option value="damaged" <?= ($book_status == 'damaged') ? 'selected' : '' ?>>Damaged</option>
            <option value="lost" <?= ($book_status == 'lost') ? 'selected' : '' ?>>Lost</option>
            <option value="NA" <?= ($book_status == 'NA') ? 'selected' : '' ?>>Condemned?NA</option>
          </select>
        </div>

        <div class="col-md-2 mt-2">
          <button class="btn btn-primary btn-block"><i class="fa fa-filter"></i> Apply</button>
        </div>
      </form>

      <!-- Table -->
      <div class="card square-card">
        <div class="card-body table-responsive">
          <table id="copiesTable" class="table table-bordered table-striped">
            <thead class="table-theme-header">
              <tr>
                <th>#</th>
                <th>Book Name</th>
                <th>Accession No</th>
                <th>Book No</th>
                <th>Status</th>
                <th>Category</th>
                <th>CUpboard</th>
                <th>Shelf</th>
                <th>Book No</th>
                <th>Authors</th>
                <th>Department</th>
                <th>Degree</th>
                <!-- <th>QRCode</th> -->
              </tr>
            </thead>
            <tbody>
              <?php
              $rackIds = [];

              if ($rack != '' && $rack != 'norack') {
                $stmt = $dbh->prepare("SELECT id FROM tbl_racks WHERE rack_no = :rk");
                $stmt->bindParam(':rk', $rack, PDO::PARAM_STR);
                $stmt->execute();
                $rackIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
              }

              $sql = "SELECT 
    c.id,
    c.book_no,
    c.book_status,
    c.accession_no,
    c.rack_id,
    c.cbrb_no,
    c.shift,
    c.isbn,
    b.BookName,
    b.AuthorIds,
    b.CatId,
    b.department_id,
    b.degree_id,
    cat.CategoryName,
    cat.is_deleted AS catdel,
    r.rack_no,
    r.shelf_no,
    r.floor,
    d.department_name,
    d.is_deleted AS deptdel,
    dg.degree_name,
    dg.is_deleted AS degdel
FROM tblbookcopies c
JOIN tblbooks b ON b.id = c.book_id
LEFT JOIN tblcategory cat ON cat.id = b.CatId
LEFT JOIN tbl_racks r ON r.id = c.rack_id
LEFT JOIN tbldepartment d ON d.department_id = b.department_id
LEFT JOIN tbldegree dg ON dg.degree_id = b.degree_id
WHERE 1=1";

              /* ---------------- APPLY FILTERS ---------------- */

              if ($category == 'nocat') {
                $sql .= " AND b.CatId IS NULL";
              } elseif ($category != '') {
                $sql .= " AND b.CatId = :cat";
              }

              if ($rack == 'norack') {
                $sql .= " AND c.rack_id IS NULL";
              } elseif ($rack != '') {
                if (!empty($rackIds)) {
                  $sql .= " AND c.rack_id IN (" . implode(',', $rackIds) . ")";
                } else {
                  $sql .= " AND 1=0"; // no results
                }
              }


              if ($author == 'noauthor') {
                $sql .= " AND (b.AuthorIds IS NULL OR b.AuthorIds = '')";
              } elseif ($author != '') {
                $sql .= " AND FIND_IN_SET(:author, b.AuthorIds)";
              }

              if ($department == 'nodept') {
                $sql .= " AND b.department_id IS NULL";
              } elseif ($department != '') {
                $sql .= " AND b.department_id = :dept";
              }

              if ($degree == 'nodegree') {
                $sql .= " AND b.degree_id IS NULL";
              } elseif ($degree != '') {
                $sql .= " AND b.degree_id = :degree";
              }

              if ($book_status != '') {
                $sql .= " AND c.book_status = :status";
              }

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

              /* ---------------- PREPARE QUERY ---------------- */

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

              /* ---------------- BIND PARAMETERS AFTER PREPARE ---------------- */

              if ($category != '' && $category != 'nocat')
                $query->bindParam(':cat', $category, PDO::PARAM_INT);

              // if ($rack != '' && $rack != 'norack')
              // $query->bindParam(':rack', $rack, PDO::PARAM_STR);
              
              if ($author != '' && $author != 'noauthor')
                $query->bindParam(':author', $author, PDO::PARAM_INT);

              if ($department != '' && $department != 'nodept')
                $query->bindParam(':dept', $department, PDO::PARAM_INT);

              if ($degree != '' && $degree != 'nodegree')
                $query->bindParam(':degree', $degree, PDO::PARAM_INT);

              if ($book_status != '')
                $query->bindParam(':status', $book_status, PDO::PARAM_STR);

              /* ---------------- EXECUTE & FETCH ---------------- */

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

              $cnt = 1;

              foreach ($results as $row):

                ?>
                <tr>
                  <td><?= $cnt++ ?></td>
                  <td><?= $row->BookName ? htmlentities($row->BookName) : "-";?></td>
                  <td><?= htmlentities($row->accession_no) ?></td>
                  <td><?= htmlentities($row->book_no) ?></td>
                  <td>
                    <?php
                    if ($row->book_status == 'available') {
                      echo "<span class='badge badge-success'>Available</span>";
                    } elseif ($row->book_status == 'issued') {
                      echo "<span class='badge badge-warning text-dark'>Issued</span>";
                    } elseif ($row->book_status == 'damaged') {
                      echo "<span class='badge badge-danger'>Damaged</span>";
                    } elseif ($row->book_status == 'lost') {
                      echo "<span class='badge badge-secondary'>Lost</span>";
                    } elseif ($row->book_status == 'NA') {
                      echo "<span class='badge badge-dark'>NA</span>";
                    } else {
                      echo "<span class='badge badge-light text-dark'>Unknown</span>";
                    }
                    ?>
                  </td>
                  <td>
                    <?php if ($row->catdel == 1): ?>
                      <span class="deleted-item"><?= htmlentities($row->CategoryName) ?></span>
                      <span class="deleted-label">(Deleted)</span>
                    <?php else: ?>
                      <?= $row->CategoryName ?? "<span class='text-muted'>No Category</span>" ?>
                    <?php endif; ?>
                  </td>


                  <td><?= $row->rack_no ?? "<span class='text-muted'>No Cupboard</span>" ?></td>
                  <td><?= $row->shelf_no ?? "<span class='text-muted'>No Shelf</span>" ?></td>
                  <td><?= $row->floor ?? "<span class='text-muted'>No Book No</span>" ?></td>
                  <td>
                    <?php
                    $authorsList = [];
                    if (!empty($row->AuthorIds)) {
                      $stmt = $dbh->query("SELECT AuthorName, is_deleted FROM tblauthors WHERE id IN (" . $row->AuthorIds . ")");
                      $authors = $stmt->fetchAll(PDO::FETCH_ASSOC);

                      foreach ($authors as $a) {
                        $name = htmlentities($a['AuthorName']);
                        if ($a['is_deleted'] == 1)
                          $authorsList[] = "<span class='deleted-item'>$name</span><span class='deleted-label'>(Deleted)</span>";
                        else
                          $authorsList[] = $name;
                      }
                    }
                    echo !empty($authorsList) ? implode(", ", $authorsList) : "<span class='text-muted'>No Author</span>";

                    ?>
                  </td>
                  <td>
                    <?php if ($row->deptdel == 1): ?>
                      <span class="deleted-item"><?= htmlentities($row->department_name) ?></span>
                      <span class="deleted-label">(Deleted)</span>
                    <?php else: ?>
                      <?= $row->department_name ?? "<span class='text-muted'>No Department</span>" ?>
                    <?php endif; ?>
                  </td>

                  <td>
                    <?php if ($row->degdel == 1): ?>
                      <span class="deleted-item"><?= htmlentities($row->degree_name) ?></span>
                      <span class="deleted-label">(Deleted)</span>
                    <?php else: ?>
                      <?= $row->degree_name ?? "<span class='text-muted'>No Degree</span>" ?>
                    <?php endif; ?>
                  </td>

                  <!-- <td>
                    <?php if ($row->QRCode): ?>
                      <img src="../<?= htmlentities($row->QRCode) ?>" width="40">
                    <?php else: ?>
                      <span class='text-muted'>No QR</span>
                    <?php endif; ?>
                  </td> -->
                </tr>
              <?php endforeach; ?>
            </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.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 src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>

  <script>
    $(document).ready(function () {
      // Initialize DataTable
      $('#copiesTable').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> Excel' }
        ],
        pageLength: 10,
        ordering: true
      });

      // Static Select2 for Book Status
      $('.select2').select2();

      // AJAX-based Select2 for dynamic dropdowns
      $('.select2-ajax').each(function () {
        let select = $(this);
        let type = select.attr('id');

        select.select2({
          placeholder: select.data('placeholder'),
          ajax: {
            url: 'get_options.php',
            dataType: 'json',
            delay: 250,
            data: function (params) {
              return {
                q: params.term,
                type: type
              };
            },
            processResults: function (data) {
              return { results: data };
            },
            cache: true
          },
          allowClear: true,
          templateResult: function (option) {
            if (!option.id) return option.text;
            const noValues = ['nocat', 'norack', 'noauthor', 'nodept', 'nodegree'];
            if (noValues.includes(option.id)) {
              return $('<span style="color:red; font-weight:bold;">' + option.text + '</span>');
            }
            return $('<span>' + option.text + '</span>');
          },
          templateSelection: function (option) {
            const noValues = ['nocat', 'norack', 'noauthor', 'nodept', 'nodegree'];
            if (option.id && noValues.includes(option.id)) {
              return $('<span style="color:red; font-weight:bold;">' + option.text + '</span>');
            }
            return option.text || option.text;
          }
        });
      });
    });
  </script>

</body>

</html>