<?php
// geraete_stats.php – Statistik & Auswertung für Geräte
ob_start();
session_start();

require_once __DIR__ . '/includes/header.php';
require_once __DIR__ . '/includes/nav.php';
require_once __DIR__ . '/includes/db.php';

if (!isset($_SESSION['user_id'])) { header("Location: login.php"); exit; }
$einheit = $_SESSION['einheit'] ?? '';
if ($einheit === '') {
  echo "<div class='container my-5'><div class='alert alert-warning'>⚠️ Keine Einheit in der Session gefunden. Bitte erneut einloggen.</div></div>";
  include 'includes/footer.php'; exit;
}

// (Debug)
// ini_set('display_errors',1); ini_set('display_startup_errors',1); error_reporting(E_ALL);

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
/** Helper für dynamisches bind_param mit Referenzen */
function stmt_bind_params(mysqli_stmt $stmt, string $types, array &$params): void {
  $bind = [$types];
  foreach ($params as $i => $v) { $bind[] = &$params[$i]; }
  call_user_func_array([$stmt, 'bind_param'], $bind);
}

/* -------------------- Filter -------------------- */
$search    = trim($_GET['search'] ?? '');
$f_fzg     = trim($_GET['fahrzeug'] ?? '');
$f_kat     = trim($_GET['kat'] ?? '');
$f_status  = trim($_GET['status'] ?? '');
$like      = '%'.$search.'%';

// Dropdowns
$fahrzeuge = [];
$stmtF = $conn->prepare("SELECT barcode_fzg, fahrzeugbezeichnung FROM ffw_fahrzeuge WHERE einheit=? ORDER BY fahrzeugbezeichnung");
$stmtF->bind_param('s', $einheit);
$stmtF->execute();
$resF = $stmtF->get_result();
while($r=$resF->fetch_assoc()){ $fahrzeuge[]=$r; }

$kategorien = [];
$stmtK = $conn->prepare("SELECT DISTINCT kat FROM ffw_geraete WHERE einheit=? AND kat<>'' ORDER BY kat");
$stmtK->bind_param('s', $einheit);
$stmtK->execute();
$resK = $stmtK->get_result();
while($r=$resK->fetch_assoc()){ $kategorien[]=$r['kat']; }

$stati = [];
$stmtS = $conn->prepare("SELECT DISTINCT status FROM ffw_geraete WHERE einheit=? AND status<>'' ORDER BY status");
$stmtS->bind_param('s', $einheit);
$stmtS->execute();
$resS = $stmtS->get_result();
while($r=$resS->fetch_assoc()){ $stati[]=$r['status']; }

/* -------------------- WHERE dynamisch -------------------- */
$where  = "g.einheit = ?";
$types  = "s";
$params = [$einheit];

if ($search !== '') {
  $where .= " AND (g.bezeichnung LIKE ? OR g.barcode LIKE ? OR g.kat LIKE ? OR g.gr LIKE ?)";
  for($i=0;$i<4;$i++){ $params[] = $like; $types .= 's'; }
}
if ($f_fzg !== '') {
  $where .= " AND g.fahrzeug = ?";
  $params[] = $f_fzg; $types .= 's';
}
if ($f_kat !== '' && in_array($f_kat, $kategorien, true)) {
  $where .= " AND g.kat = ?";
  $params[] = $f_kat; $types .= 's';
}
if ($f_status !== '' && in_array($f_status, $stati, true)) {
  $where .= " AND g.status = ?";
  $params[] = $f_status; $types .= 's';
}

/* -------------------- KPIs + Due Buckets --------------------
   Due-Logik: nächste Prüfung = lpruefung + 1 Jahr (wie in deiner Übersicht)
---------------------------------------------------------------- */
$sqlBase = "
  SELECT g.id, g.status, g.kat, g.gr, g.fahrzeug, g.baujahr, g.lpruefung
  FROM ffw_geraete g
  WHERE $where
";
$sb = $conn->prepare($sqlBase);
stmt_bind_params($sb, $types, $params);
$sb->execute();
$rows = $sb->get_result()->fetch_all(MYSQLI_ASSOC);

$now = time();
$total = 0; $cntFrei = 0;
$due_lt7 = 0; $due_lt30 = 0; $due_ok = 0; $due_over = 0; $due_none = 0;

foreach($rows as $r){
  $total++;
  $st = mb_strtolower((string)($r['status'] ?? ''));
  if ($st === 'frei') $cntFrei++;

  $lts = is_numeric($r['lpruefung'] ?? null) ? (int)$r['lpruefung'] : 0;
  $naechste = $lts > 0 ? strtotime('+1 year', $lts) : 0;
  if ($naechste === 0) { $due_none++; continue; }

  $diffDays = (int)floor(($naechste - $now)/86400);
  if ($diffDays < 0) $due_over++;
  elseif ($diffDays < 7) $due_lt7++;
  elseif ($diffDays < 30) $due_lt30++;
  else $due_ok++;
}

/* -------------------- Verteilung: Kategorie -------------------- */
$sqlKat = "
  SELECT g.kat, COUNT(*) cnt
  FROM ffw_geraete g
  WHERE $where
  GROUP BY g.kat
  ORDER BY cnt DESC, g.kat ASC
";
$sk = $conn->prepare($sqlKat);
stmt_bind_params($sk, $types, $params);
$sk->execute();
$rKat = $sk->get_result();
$katLabels=[]; $katCounts=[];
while($x=$rKat->fetch_assoc()){ $katLabels[] = $x['kat'] ?: '—'; $katCounts[] = (int)$x['cnt']; }

/* -------------------- Verteilung: Status -------------------- */
$sqlStat = "
  SELECT g.status, COUNT(*) cnt
  FROM ffw_geraete g
  WHERE $where
  GROUP BY g.status
  ORDER BY cnt DESC
";
$ss = $conn->prepare($sqlStat);
stmt_bind_params($ss, $types, $params);
$ss->execute();
$rStat = $ss->get_result();
$statLabels=[]; $statCounts=[];
while($x=$rStat->fetch_assoc()){ $statLabels[] = $x['status'] ?: '—'; $statCounts[] = (int)$x['cnt']; }

/* -------------------- Top Fahrzeuge -------------------- */
$sqlFzg = "
  SELECT g.fahrzeug, f.fahrzeugbezeichnung, COUNT(*) cnt
  FROM ffw_geraete g
  LEFT JOIN ffw_fahrzeuge f ON f.barcode_fzg = g.fahrzeug AND f.einheit = g.einheit
  WHERE $where
  GROUP BY g.fahrzeug, f.fahrzeugbezeichnung
  ORDER BY cnt DESC, f.fahrzeugbezeichnung ASC
  LIMIT 10
";
$sf = $conn->prepare($sqlFzg);
stmt_bind_params($sf, $types, $params);
$sf->execute();
$rFzg = $sf->get_result();
$fzgLabels=[]; $fzgCounts=[];
while($x=$rFzg->fetch_assoc()){
  $label = $x['fahrzeugbezeichnung'] ?: ($x['fahrzeug'] ?: '—');
  $fzgLabels[] = $label; $fzgCounts[] = (int)$x['cnt'];
}

/* -------------------- Top Geräteraum -------------------- */
$sqlGr = "
  SELECT g.gr, COUNT(*) cnt
  FROM ffw_geraete g
  WHERE $where AND g.gr <> ''
  GROUP BY g.gr
  ORDER BY cnt DESC, g.gr ASC
  LIMIT 10
";
$sgr = $conn->prepare($sqlGr);
stmt_bind_params($sgr, $types, $params);
$sgr->execute();
$rGr = $sgr->get_result();
$grRows = $rGr->fetch_all(MYSQLI_ASSOC);

/* -------------------- Baujahr Histogramm --------------------
   baujahr kann als Timestamp (versehentlich) oder 4-stellig vorliegen.
---------------------------------------------------------------- */
$yearNow = (int)date('Y');
$yearMin = $yearNow - 11;
$yearMap = [];

foreach($rows as $r){
  $bj = $r['baujahr'];
  $label = 'unbekannt';
  if (is_numeric($bj)) {
    $num = (int)$bj;
    if ($num > 100000) { // Timestamp-artig
      $y = (int)date('Y', $num);
      $label = ($y < $yearMin) ? '≤ '.($yearMin-1) : (string)$y;
    } elseif ($num >= 1900 && $num <= $yearNow) {
      $label = ($num < $yearMin) ? '≤ '.($yearMin-1) : (string)$num;
    }
  }
  $yearMap[$label] = ($yearMap[$label] ?? 0) + 1;
}
$yearLabels = array_merge(['≤ '.($yearMin-1)], array_map(fn($y)=>(string)$y, range($yearMin,$yearNow)), ['unbekannt']);
$yearCounts = array_map(fn($lab)=>$yearMap[$lab] ?? 0, $yearLabels);

/* -------------------- Verlauf Wartung (geraete_wartung) 12 Monate -------------------- */
$start = (new DateTime('first day of -11 months'))->setTime(0,0,0);
$months = [];
for($i=0;$i<12;$i++){ $months[] = (clone $start)->modify("+$i months"); }
$histLabels = array_map(fn($d)=>$d->format('m.Y'), $months);

$sqlHist = "
  SELECT DATE_FORMAT(w.datum, '%Y-%m') ym, COUNT(*) cnt
  FROM geraete_wartung w
  JOIN ffw_geraete g ON g.barcode = w.barcode
  WHERE g.einheit = ?
";
$typesHist = "s";
$paramsHist = [$einheit];

if ($f_fzg !== '') { $sqlHist .= " AND g.fahrzeug = ?"; $typesHist.='s'; $paramsHist[]=$f_fzg; }
if ($f_kat !== '' && in_array($f_kat,$kategorien,true)) { $sqlHist .= " AND g.kat = ?"; $typesHist.='s'; $paramsHist[]=$f_kat; }
if ($f_status !== '' && in_array($f_status,$stati,true)) { $sqlHist .= " AND g.status = ?"; $typesHist.='s'; $paramsHist[]=$f_status; }
if ($search !== '') {
  $sqlHist .= " AND (g.bezeichnung LIKE ? OR g.barcode LIKE ? OR g.kat LIKE ? OR g.gr LIKE ?)";
  for($i=0;$i<4;$i++){ $paramsHist[]=$like; $typesHist.='s'; }
}
$firstMonth = $months[0];
$lastMonthBoundary = (clone $months[count($months)-1])->modify('first day of next month');
$sqlHist .= " AND w.datum >= ? AND w.datum < ? GROUP BY ym";
$paramsHist[] = $firstMonth->format('Y-m-01');       $typesHist .= 's';
$paramsHist[] = $lastMonthBoundary->format('Y-m-01');$typesHist .= 's';

$sh = $conn->prepare($sqlHist);
stmt_bind_params($sh, $typesHist, $paramsHist);
$sh->execute();
$rHist = $sh->get_result();
$histMap = [];
while($x=$rHist->fetch_assoc()){ $histMap[$x['ym']] = (int)$x['cnt']; }
$histCounts = [];
foreach($months as $m){
  $key = $m->format('Y-m');
  $histCounts[] = $histMap[$key] ?? 0;
}

/* -------------------- UI -------------------- */
?>
<style>
  .kpi { border:1px solid #eee; border-radius:12px; padding:16px; background:#fff; }
  .kpi h3 { margin:0; font-size:1.4rem; }
  .kpi .sub { color:#6c757d; font-size:.9rem; }
  .card-soft { border:1px solid #eee; border-radius:12px; background:#fff; }
  .table-tight td, .table-tight th { padding:.5rem .6rem; }
</style>

<div class="container my-5">
  <div class="d-flex justify-content-between align-items-end flex-wrap gap-2 mb-3">
    <div>
      <h2 class="mb-1">Geräte – Statistik & Auswertung</h2>
      <div class="text-muted small">Einheit: <span class="badge text-bg-secondary"><?= h($einheit) ?></span></div>
    </div>
    <form method="get" class="d-flex flex-wrap gap-2">
      <input type="text" name="search" class="form-control" placeholder="Suche: Bezeichnung / Barcode / Kategorie / Raum" value="<?= h($search) ?>">
      <select name="fahrzeug" class="form-select">
        <option value="">Alle Fahrzeuge</option>
        <?php foreach($fahrzeuge as $fz): ?>
          <option value="<?= h($fz['barcode_fzg']) ?>" <?= $f_fzg===$fz['barcode_fzg']?'selected':'' ?>>
            <?= h($fz['fahrzeugbezeichnung']) ?>
          </option>
        <?php endforeach; ?>
      </select>
      <select name="kat" class="form-select">
        <option value="">Alle Kategorien</option>
        <?php foreach($kategorien as $k): ?>
          <option value="<?= h($k) ?>" <?= $f_kat===$k?'selected':'' ?>><?= h($k) ?></option>
        <?php endforeach; ?>
      </select>
      <select name="status" class="form-select">
        <option value="">Alle Stati</option>
        <?php foreach($stati as $s): ?>
          <option value="<?= h($s) ?>" <?= $f_status===$s?'selected':'' ?>><?= h($s) ?></option>
        <?php endforeach; ?>
      </select>
      <button class="btn btn-primary">Anwenden</button>
      <?php if ($search!=='' || $f_fzg!=='' || $f_kat!=='' || $f_status!==''): ?>
        <a href="geraete_stats.php" class="btn btn-outline-secondary">Zurücksetzen</a>
      <?php endif; ?>
    </form>
  </div>

  <!-- KPI Cards -->
  <div class="row g-3 mb-4">
    <div class="col-6 col-md-3">
      <div class="kpi">
        <div class="sub">Gesamt</div>
        <h3><?= (int)$total ?></h3>
      </div>
    </div>
    <div class="col-6 col-md-3">
      <div class="kpi">
        <div class="sub">Status „frei“</div>
        <h3><?= (int)$cntFrei ?></h3>
      </div>
    </div>
    <div class="col-6 col-md-3">
      <div class="kpi">
        <div class="sub">Fällig < 30 Tage</div>
        <h3><?= (int)$due_lt30 + (int)$due_lt7 /* lt7 ist Teilmenge von lt30? hier zählen wir getrennt in Chart */ ?></h3>
      </div>
    </div>
    <div class="col-6 col-md-3">
      <div class="kpi">
        <div class="sub">Fällig < 7 Tage</div>
        <h3><?= (int)$due_lt7 ?></h3>
      </div>
    </div>
  </div>

  <!-- Charts 1 -->
  <div class="row g-4">
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Verteilung nach Kategorie</h5>
        <canvas id="chartKat" height="180"></canvas>
      </div>
    </div>
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Status-Verteilung</h5>
        <canvas id="chartStatus" height="180"></canvas>
      </div>
    </div>
  </div>

  <!-- Charts 2 -->
  <div class="row g-4 mt-1">
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Fälligkeit (Buckets)</h5>
        <canvas id="chartDue" height="180"></canvas>
      </div>
    </div>
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Top 10 Fahrzeuge (Anzahl Geräte)</h5>
        <canvas id="chartFzg" height="180"></canvas>
      </div>
    </div>
  </div>

  <!-- Baujahr -->
  <div class="row g-4 mt-1">
    <div class="col-lg-12">
      <div class="card-soft p-3">
        <h5 class="mb-3">Baujahr-Verteilung</h5>
        <canvas id="chartYears" height="120"></canvas>
      </div>
    </div>
  </div>

  <!-- Tabellen -->
  <div class="row g-4 mt-1">
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Top 10 Geräteraum</h5>
        <div class="table-responsive">
          <table class="table table-tight align-middle mb-0">
            <thead class="table-light"><tr><th>Geräteraum</th><th class="text-end">Anzahl</th></tr></thead>
            <tbody>
              <?php if (empty($grRows)): ?>
                <tr><td colspan="2" class="text-muted">keine Daten</td></tr>
              <?php else: foreach($grRows as $gr): ?>
                <tr><td><?= h($gr['gr'] ?: '—') ?></td><td class="text-end"><?= (int)$gr['cnt'] ?></td></tr>
              <?php endforeach; endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>

    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Wartung/Pflege – Einträge (letzte 12 Monate)</h5>
        <canvas id="chartHist" height="150"></canvas>
      </div>
    </div>
  </div>
</div>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
<script>
const katLabels  = <?= json_encode($katLabels, JSON_UNESCAPED_UNICODE) ?>;
const katCounts  = <?= json_encode($katCounts, JSON_NUMERIC_CHECK) ?>;
const statLabels = <?= json_encode($statLabels, JSON_UNESCAPED_UNICODE) ?>;
const statCounts = <?= json_encode($statCounts, JSON_NUMERIC_CHECK) ?>;
const fzgLabels  = <?= json_encode($fzgLabels, JSON_UNESCAPED_UNICODE) ?>;
const fzgCounts  = <?= json_encode($fzgCounts, JSON_NUMERIC_CHECK) ?>;
const yearLabels = <?= json_encode($yearLabels, JSON_UNESCAPED_UNICODE) ?>;
const yearCounts = <?= json_encode($yearCounts, JSON_NUMERIC_CHECK) ?>;
const histLabels = <?= json_encode($histLabels, JSON_UNESCAPED_UNICODE) ?>;
const histCounts = <?= json_encode($histCounts, JSON_NUMERIC_CHECK) ?>;
const dueBuckets = {
  labels: ['Überfällig', '< 7 Tage', '< 30 Tage', 'OK (≥30)', 'Kein Datum'],
  data: [<?= (int)$due_over ?>, <?= (int)$due_lt7 ?>, <?= (int)$due_lt30 ?>, <?= (int)$due_ok ?>, <?= (int)$due_none ?>]
};

Chart.defaults.font.family = "'Segoe UI', system-ui, -apple-system, Roboto, Arial";

new Chart(document.getElementById('chartKat'), {
  type: 'bar',
  data: { labels: katLabels, datasets: [{ label: 'Anzahl', data: katCounts }] },
  options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
});

new Chart(document.getElementById('chartStatus'), {
  type: 'doughnut',
  data: { labels: statLabels, datasets: [{ data: statCounts }] },
  options: { responsive:true, plugins:{ legend:{ position:'bottom' } } }
});

new Chart(document.getElementById('chartFzg'), {
  type: 'bar',
  data: { labels: fzgLabels, datasets: [{ label: 'Geräte', data: fzgCounts }] },
  options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
});

new Chart(document.getElementById('chartYears'), {
  type: 'bar',
  data: { labels: yearLabels, datasets: [{ label:'Anzahl', data: yearCounts }] },
  options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
});

new Chart(document.getElementById('chartDue'), {
  type: 'bar',
  data: { labels: dueBuckets.labels, datasets: [{ label:'Anzahl', data: dueBuckets.data }] },
  options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
});

new Chart(document.getElementById('chartHist'), {
  type: 'line',
  data: { labels: histLabels, datasets: [{ label:'Einträge', data: histCounts, tension:.3, fill:false }] },
  options: { responsive:true, plugins:{ legend:{ display:false } }, scales:{ y:{ beginAtZero:true, ticks:{ precision:0 } } } }
});
</script>

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