<?php
include 'db.php';

// Filter verarbeiten
$filter_kunde = $_GET['kunde'] ?? '';
$filter_projekt = $_GET['projekt'] ?? '';
$filter_status = $_GET['status'] ?? '';
$filter_suche = $_GET['suche'] ?? '';

// Kunden und Projekte laden
$kunden = $conn->query("SELECT id, firmenname FROM kunden ORDER BY firmenname");
$projekte = $conn->query("SELECT id, name, kunden_id FROM projekte ORDER BY name");

// SQL-Query vorbereiten
$sql = "
  SELECT l.*, a.titel AS auftragstitel, p.name AS projektname, k.firmenname
  FROM lizenzen l
  JOIN auftraege a ON l.auftrag_id = a.id
  JOIN projekte p ON a.projekt_id = p.id
  JOIN kunden k ON p.kunden_id = k.id
  WHERE 1
";
$params = [];

if ($filter_kunde) {
    $sql .= " AND k.id = ?";
    $params[] = $filter_kunde;
}
if ($filter_projekt) {
    $sql .= " AND p.id = ?";
    $params[] = $filter_projekt;
}
if ($filter_status) {
    $sql .= " AND l.status = ?";
    $params[] = $filter_status;
}
if ($filter_suche) {
    $sql .= " AND l.lizenzname LIKE ?";
    $params[] = "%$filter_suche%";
}
$sql .= " ORDER BY l.enddatum DESC";

$stmt = $conn->prepare($sql);
if ($params) {
    $types = str_repeat('s', count($params));
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
?>

<!DOCTYPE html>
<html lang="de">
<head>
  <meta charset="UTF-8">
  <title>Lizenzen</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    body { background: #eef1f4; }
    .glass {
      background: rgba(255,255,255,0.95);
      padding: 20px;
      border-radius: 16px;
      box-shadow: 0 4px 12px rgba(0,0,0,0.05);
    }
    table td, table th { vertical-align: middle; }
  </style>
</head>
<body>
<div class="container py-4">
  <div class="glass">
    <h3 class="mb-4">🎫 Lizenzübersicht</h3>

    <form class="row g-3 mb-4">
      <div class="col-md-3">
        <label class="form-label">Kunde</label>
        <select name="kunde" class="form-select" onchange="this.form.submit()">
          <option value="">Alle</option>
          <?php while($k = $kunden->fetch_assoc()): ?>
            <option value="<?= $k['id'] ?>" <?= ($filter_kunde == $k['id']) ? 'selected' : '' ?>><?= htmlspecialchars($k['firmenname']) ?></option>
          <?php endwhile; ?>
        </select>
      </div>
      <div class="col-md-3">
        <label class="form-label">Projekt</label>
        <select name="projekt" class="form-select" onchange="this.form.submit()">
          <option value="">Alle</option>
          <?php
          $projekte->data_seek(0);
          while($p = $projekte->fetch_assoc()):
            if ($filter_kunde && $p['kunden_id'] != $filter_kunde) continue;
          ?>
            <option value="<?= $p['id'] ?>" <?= ($filter_projekt == $p['id']) ? 'selected' : '' ?>><?= htmlspecialchars($p['name']) ?></option>
          <?php endwhile; ?>
        </select>
      </div>
      <div class="col-md-2">
        <label class="form-label">Status</label>
        <select name="status" class="form-select" onchange="this.form.submit()">
          <option value="">Alle</option>
          <option value="aktiv" <?= $filter_status == 'aktiv' ? 'selected' : '' ?>>aktiv</option>
          <option value="inaktiv" <?= $filter_status == 'inaktiv' ? 'selected' : '' ?>>inaktiv</option>
          <option value="abgelaufen" <?= $filter_status == 'abgelaufen' ? 'selected' : '' ?>>abgelaufen</option>
        </select>
      </div>
      <div class="col-md-4">
        <label class="form-label">Suche</label>
        <input type="text" name="suche" class="form-control" value="<?= htmlspecialchars($filter_suche) ?>" placeholder="Lizenzname" onkeydown="if(event.key==='Enter'){this.form.submit();}">
      </div>
    </form>

    <div class="table-responsive">
      <table class="table table-bordered table-hover">
        <thead class="table-light">
          <tr>
            <th>Lizenzname</th>
            <th>Kunde</th>
            <th>Projekt</th>
            <th>Auftrag</th>
            <th>Zeitraum</th>
            <th>Status</th>
            <th>Wert (€)</th>
          </tr>
        </thead>
        <tbody>
        <?php while($row = $result->fetch_assoc()): ?>
          <tr>
            <td><?= htmlspecialchars($row['lizenzname']) ?></td>
            <td><?= htmlspecialchars($row['firmenname']) ?></td>
            <td><?= htmlspecialchars($row['projektname']) ?></td>
            <td><?= htmlspecialchars($row['auftragstitel']) ?></td>
            <td><?= date('d.m.Y', strtotime($row['startdatum'])) ?> – <?= date('d.m.Y', strtotime($row['enddatum'])) ?></td>
            <td><?= ucfirst($row['status']) ?></td>
            <td><?= number_format($row['wert'], 2, ',', '.') ?></td>
          </tr>
        <?php endwhile; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
</body>
</html>
