<?php
require_once __DIR__ . '/../lib/core.php';
dc_require_login();
dc_require_csrf();

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = isset($_POST['action']) ? $_POST['action'] : '';
    $id = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    if ($action === 'delete' && $id > 0) {
        $stmt = dc_db()->prepare('SELECT qr_image_path FROM door_cert_doors WHERE id=?');
        $stmt->execute(array($id));
        $door = $stmt->fetch();
        if ($door) {
            $stmt = dc_db()->prepare('DELETE FROM door_cert_download_logs WHERE door_id=?');
            $stmt->execute(array($id));
            $stmt = dc_db()->prepare('DELETE FROM door_cert_doors WHERE id=?');
            $stmt->execute(array($id));
            $qrPath = !empty($door['qr_image_path']) ? realpath(__DIR__ . '/../' . $door['qr_image_path']) : false;
            $qrDir = realpath(__DIR__ . '/../storage/qrcodes');
            if ($qrPath && $qrDir && strpos($qrPath, $qrDir) === 0 && is_file($qrPath)) {
                unlink($qrPath);
            }
            dc_flash('success', 'Door deleted.');
        }
        dc_redirect('admin/doors.php');
    }
}

$projects = dc_db()->query('SELECT id, project_number, client_name FROM door_cert_projects ORDER BY client_name, project_number')->fetchAll();
$categories = dc_db()->query('SELECT id, code, name FROM door_cert_categories ORDER BY code')->fetchAll();
$standards = dc_db()->query('SELECT name FROM door_cert_standards WHERE is_active=1 ORDER BY name')->fetchAll();

$filters = array(
    'q' => trim(isset($_GET['q']) ? $_GET['q'] : ''),
    'project_id' => isset($_GET['project_id']) ? (int)$_GET['project_id'] : 0,
    'category_id' => isset($_GET['category_id']) ? (int)$_GET['category_id'] : 0,
    'status' => trim(isset($_GET['status']) ? $_GET['status'] : ''),
    'po_number' => trim(isset($_GET['po_number']) ? $_GET['po_number'] : ''),
    'standard' => trim(isset($_GET['standard']) ? $_GET['standard'] : ''),
);
$page = max(1, isset($_GET['page']) ? (int)$_GET['page'] : 1);
$perPage = 20;
$offset = ($page - 1) * $perPage;

$where = array();
$params = array();
if ($filters['q'] !== '') {
    $like = '%' . $filters['q'] . '%';
    $where[] = '(d.door_id LIKE ? OR d.door_number LIKE ? OR d.po_number LIKE ? OR d.area_location LIKE ? OR d.fire_rating LIKE ? OR d.standards LIKE ? OR p.project_number LIKE ? OR p.client_name LIKE ? OR p.site_location LIKE ? OR c.code LIKE ? OR c.name LIKE ?)';
    for ($i = 0; $i < 11; $i++) {
        $params[] = $like;
    }
}
if ($filters['project_id'] > 0) {
    $where[] = 'p.id = ?';
    $params[] = $filters['project_id'];
}
if ($filters['category_id'] > 0) {
    $where[] = 'c.id = ?';
    $params[] = $filters['category_id'];
}
if ($filters['po_number'] !== '') {
    $where[] = 'd.po_number LIKE ?';
    $params[] = '%' . $filters['po_number'] . '%';
}
if ($filters['standard'] !== '') {
    $where[] = 'd.standards LIKE ?';
    $params[] = '%' . $filters['standard'] . '%';
}
if ($filters['status'] === 'active') {
    $where[] = "(d.status = 'active' AND (d.valid_to IS NULL OR d.valid_to = '0000-00-00' OR d.valid_to >= CURDATE()))";
} elseif ($filters['status'] === 'expired') {
    $where[] = "(d.status = 'expired' OR (d.valid_to IS NOT NULL AND d.valid_to <> '0000-00-00' AND d.valid_to < CURDATE()))";
} elseif ($filters['status'] === 'suspended') {
    $where[] = "d.status = 'suspended'";
}

$fromSql = 'FROM door_cert_doors d
    JOIN door_cert_categories c ON c.id = d.category_id
    JOIN door_cert_projects p ON p.id = d.project_id';
$whereSql = $where ? ' WHERE ' . implode(' AND ', $where) : '';

$countStmt = dc_db()->prepare('SELECT COUNT(*) ' . $fromSql . $whereSql);
$countStmt->execute($params);
$totalDoors = (int)$countStmt->fetchColumn();
$totalPages = max(1, (int)ceil($totalDoors / $perPage));
if ($page > $totalPages) {
    $page = $totalPages;
    $offset = ($page - 1) * $perPage;
}

$sql = 'SELECT d.*, c.code category_code, c.name category_name, p.project_number, p.client_name
    ' . $fromSql . $whereSql . '
    ORDER BY d.created_at DESC
    LIMIT ' . (int)$perPage . ' OFFSET ' . (int)$offset;
$stmt = dc_db()->prepare($sql);
$stmt->execute($params);
$doors = $stmt->fetchAll();

function dc_doors_url($overrides) {
    $query = array_merge($_GET, $overrides);
    foreach ($query as $key => $value) {
        if ($value === '' || $value === 0 || $value === '0' || $value === null) {
            unset($query[$key]);
        }
    }
    return 'doors.php' . ($query ? '?' . http_build_query($query) : '');
}

$firstItem = $totalDoors > 0 ? $offset + 1 : 0;
$lastItem = min($offset + count($doors), $totalDoors);

dc_admin_header('Doors');
?>
<div class="dc-card">
  <div class="dc-header">
    <h2>Door Records</h2>
    <a class="dc-btn" href="door-form.php">Add Door</a>
  </div>
  <form method="get" class="dc-filter-form">
    <div class="dc-filter-grid">
      <div><label>Search</label><input name="q" value="<?php echo dc_h($filters['q']); ?>" placeholder="Door ID, door no, PO, client, site, area"></div>
      <div>
        <label>Client</label>
        <select name="project_id">
          <option value="0">All clients</option>
          <?php foreach ($projects as $project): ?>
            <option value="<?php echo (int)$project['id']; ?>" <?php echo $filters['project_id'] === (int)$project['id'] ? 'selected' : ''; ?>><?php echo dc_h($project['client_name'] . ' - ' . $project['project_number']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <label>Category</label>
        <select name="category_id">
          <option value="0">All categories</option>
          <?php foreach ($categories as $category): ?>
            <option value="<?php echo (int)$category['id']; ?>" <?php echo $filters['category_id'] === (int)$category['id'] ? 'selected' : ''; ?>><?php echo dc_h($category['code'] . ' - ' . $category['name']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
      <div>
        <label>Status</label>
        <select name="status">
          <option value="">All statuses</option>
          <option value="active" <?php echo $filters['status'] === 'active' ? 'selected' : ''; ?>>Active</option>
          <option value="expired" <?php echo $filters['status'] === 'expired' ? 'selected' : ''; ?>>Expired</option>
          <option value="suspended" <?php echo $filters['status'] === 'suspended' ? 'selected' : ''; ?>>Suspended</option>
        </select>
      </div>
      <div><label>PO Number</label><input name="po_number" value="<?php echo dc_h($filters['po_number']); ?>"></div>
      <div>
        <label>Standard</label>
        <select name="standard">
          <option value="">All standards</option>
          <?php foreach ($standards as $standard): ?>
            <option value="<?php echo dc_h($standard['name']); ?>" <?php echo $filters['standard'] === $standard['name'] ? 'selected' : ''; ?>><?php echo dc_h($standard['name']); ?></option>
          <?php endforeach; ?>
        </select>
      </div>
    </div>
    <div class="dc-actions">
      <button type="submit">Apply Filters</button>
      <a class="dc-btn light" href="doors.php">Clear</a>
    </div>
  </form>
</div>

<div class="dc-card">
  <div class="dc-list-summary">
    <strong><?php echo (int)$totalDoors; ?></strong> doors
    <?php if ($totalDoors > 0): ?>, showing <?php echo (int)$firstItem; ?>-<?php echo (int)$lastItem; ?><?php endif; ?>
  </div>
  <table>
    <tr><th>Door ID</th><th>Door No.</th><th>PO No.</th><th>Client</th><th>Category</th><th>Standards</th><th>Valid To</th><th>Status</th><th></th></tr>
    <?php if (!$doors): ?>
      <tr><td colspan="9">No door records found.</td></tr>
    <?php endif; ?>
    <?php foreach ($doors as $door): ?>
      <?php $status = dc_status_for_door($door); ?>
      <tr>
        <td><?php echo dc_h($door['door_id']); ?></td>
        <td><?php echo dc_h($door['door_number']); ?></td>
        <td><?php echo dc_h($door['po_number']); ?></td>
        <td><?php echo dc_h($door['client_name']); ?></td>
        <td><?php echo dc_h($door['category_code']); ?></td>
        <td><?php echo dc_h(dc_display_standards($door['standards'])); ?></td>
        <td><?php echo dc_h(dc_format_date($door['valid_to'])); ?></td>
        <td><span class="dc-status <?php echo dc_h($status); ?>"><?php echo dc_h($status); ?></span></td>
        <td class="dc-row-actions">
          <a href="door-form.php?id=<?php echo (int)$door['id']; ?>">Edit</a>
          <a href="label.php?id=<?php echo (int)$door['id']; ?>">Label</a>
          <a target="_blank" href="../download.php?type=certificate&id=<?php echo rawurlencode($door['door_id']); ?>&key=<?php echo rawurlencode($door['qr_token']); ?>">Certificate</a>
          <a target="_blank" href="../verify.php?id=<?php echo rawurlencode($door['door_id']); ?>&key=<?php echo rawurlencode($door['qr_token']); ?>">Verify</a>
          <form method="post" class="dc-inline-form" onsubmit="return confirm('Delete this door record?');">
            <?php echo dc_csrf_field(); ?>
            <input type="hidden" name="action" value="delete">
            <input type="hidden" name="id" value="<?php echo (int)$door['id']; ?>">
            <button type="submit" class="dc-link-button danger">Delete</button>
          </form>
        </td>
      </tr>
    <?php endforeach; ?>
  </table>
  <?php if ($totalPages > 1): ?>
    <div class="dc-pagination">
      <?php if ($page > 1): ?><a class="dc-btn light" href="<?php echo dc_h(dc_doors_url(array('page' => $page - 1))); ?>">Previous</a><?php endif; ?>
      <span>Page <?php echo (int)$page; ?> of <?php echo (int)$totalPages; ?></span>
      <?php if ($page < $totalPages): ?><a class="dc-btn light" href="<?php echo dc_h(dc_doors_url(array('page' => $page + 1))); ?>">Next</a><?php endif; ?>
    </div>
  <?php endif; ?>
</div>
<?php dc_footer(); ?>
