<?php
// psa_stats.php – PSA Statistik & Auswertung (fix: bind_param-Referenzen & Monatsgrenzen)
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;
}

// (Optional für Debug in DEV)
// 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: korrektes 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_typ   = trim($_GET['typ'] ?? '');
$f_stat  = trim($_GET['status'] ?? '');
$typen   = ['Einsatzjacke','Einsatzhose','Einsatzhose AGT','Stiefel','Helm','Helmlampe'];
$stati   = ['Einsatzbereit','Nicht Einsatzbereit'];
$likeSearch = '%'.$search.'%';

// ---------- WHERE-Bausteine ----------
$where  = "psa.einheit = ?";
$types  = "s";
$params = [$einheit];

if ($search !== '') {
  $where .= " AND (psa.id_code LIKE ? OR psa.hersteller LIKE ? OR psa.modell LIKE ? OR psa.groesse LIKE ? OR psa.spindnr LIKE ?)";
  for ($i=0;$i<5;$i++){ $params[]=$likeSearch; $types.='s'; }
}
if ($f_typ !== '' && in_array($f_typ, $typen, true)) {
  $where .= " AND psa.typ = ?";
  $params[] = $f_typ; $types .= 's';
}
if ($f_stat !== '' && in_array($f_stat, $stati, true)) {
  $where .= " AND psa.status = ?";
  $params[] = $f_stat; $types .= 's';
}

// ---------- KPI: Gesamt, einsatzbereit, nicht einsatzbereit ----------
$sqlKpi = "
  SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN LOWER(psa.status)='einsatzbereit' THEN 1 ELSE 0 END) AS ready,
    SUM(CASE WHEN LOWER(psa.status)='nicht einsatzbereit' THEN 1 ELSE 0 END) AS notready
  FROM psa_ausruestung psa
  WHERE $where
";
$kpi = $conn->prepare($sqlKpi);
stmt_bind_params($kpi, $types, $params);
$kpi->execute();
$K = $kpi->get_result()->fetch_assoc() ?: ['total'=>0,'ready'=>0,'notready'=>0];

// ---------- Typ-Verteilung ----------
$sqlType = "
  SELECT psa.typ, COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where
  GROUP BY psa.typ
  ORDER BY cnt DESC
";
$st = $conn->prepare($sqlType);
stmt_bind_params($st, $types, $params);
$st->execute();
$resType = $st->get_result();
$typeLabels=[]; $typeCounts=[];
while($r=$resType->fetch_assoc()){ $typeLabels[]=$r['typ']; $typeCounts[]=(int)$r['cnt']; }

// ---------- Status-Verteilung ----------
$sqlStatus = "
  SELECT psa.status, COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where
  GROUP BY psa.status
";
$ss = $conn->prepare($sqlStatus);
stmt_bind_params($ss, $types, $params);
$ss->execute();
$resStatus = $ss->get_result();
$statLabels=[]; $statCounts=[];
while($r=$resStatus->fetch_assoc()){
  $lab = ($r['status']!=='') ? $r['status'] : '—';
  $statLabels[]=$lab; $statCounts[]=(int)$r['cnt'];
}

// ---------- Zuordnungsquote (über Spindnr -> Kamerad) ----------
$sqlAssign = "
  SELECT
    SUM(CASE WHEN k.id IS NOT NULL THEN 1 ELSE 0 END) AS assigned,
    SUM(CASE WHEN k.id IS NULL THEN 1 ELSE 0 END)     AS unassigned
  FROM psa_ausruestung psa
  LEFT JOIN kameraden k
    ON k.einheit = psa.einheit AND k.spindnr = psa.spindnr
  WHERE $where
";
$sa = $conn->prepare($sqlAssign);
stmt_bind_params($sa, $types, $params);
$sa->execute();
$A = $sa->get_result()->fetch_assoc() ?: ['assigned'=>0,'unassigned'=>0];

// ---------- Baujahre: Histogramm (letzte 12 Jahre + „älter“) ----------
$yearNow = (int)date('Y');
$yearMin = $yearNow - 11;
$sqlYear = "
  SELECT
    CASE
      WHEN psa.herstellungsjahr IS NULL OR psa.herstellungsjahr='' THEN 'unbekannt'
      WHEN CAST(psa.herstellungsjahr AS UNSIGNED) < $yearMin THEN '≤ ".($yearMin-1)."'
      ELSE psa.herstellungsjahr
    END AS jahr_grp,
    COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where
  GROUP BY jahr_grp
";
$sy = $conn->prepare($sqlYear);
stmt_bind_params($sy, $types, $params);
$sy->execute();
$resYear = $sy->get_result();
$yearMap = [];
while($r=$resYear->fetch_assoc()){ $yearMap[$r['jahr_grp']] = (int)$r['cnt']; }
$yearLabels = array_merge(['≤ '.($yearMin-1)], array_map(fn($y)=>(string)$y, range($yearMin,$yearNow)), ['unbekannt']);
$yearCounts = array_map(fn($lab)=>$yearMap[$lab] ?? 0, $yearLabels);

// ---------- Top Hersteller/Modelle ----------
$sqlTopBrand = "
  SELECT psa.hersteller, COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where
  GROUP BY psa.hersteller
  ORDER BY cnt DESC, psa.hersteller ASC
  LIMIT 10
";
$sb = $conn->prepare($sqlTopBrand);
stmt_bind_params($sb, $types, $params);
$sb->execute();
$topBrand = $sb->get_result();

$sqlTopModel = "
  SELECT psa.modell, COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where
  GROUP BY psa.modell
  ORDER BY cnt DESC, psa.modell ASC
  LIMIT 10
";
$sm = $conn->prepare($sqlTopModel);
stmt_bind_params($sm, $types, $params);
$sm->execute();
$topModel = $sm->get_result();

// ---------- Größe nach Typ (erste 60 Kombinationen) ----------
$sqlSize = "
  SELECT psa.typ, psa.groesse, COUNT(*) cnt
  FROM psa_ausruestung psa
  WHERE $where AND psa.groesse <> ''
  GROUP BY psa.typ, psa.groesse
  ORDER BY psa.typ ASC, cnt DESC, psa.groesse ASC
  LIMIT 60
";
$sz = $conn->prepare($sqlSize);
stmt_bind_params($sz, $types, $params);
$sz->execute();
$sizeRows = $sz->get_result();

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

$sqlHist = "
  SELECT DATE_FORMAT(datum, '%Y-%m') ym, COUNT(*) cnt
  FROM psa_waesche w
  JOIN psa_ausruestung psa ON psa.id = w.psa_id
  WHERE psa.einheit = ?
";
$typesHist = "s";
$paramsHist = [$einheit];

// Historie optional durch PSA-Filter einschränken
if ($f_typ !== '' && in_array($f_typ,$typen,true)) { $sqlHist .= " AND psa.typ = ?"; $typesHist.='s'; $paramsHist[]=$f_typ; }
if ($f_stat !== '' && in_array($f_stat,$stati,true)) { $sqlHist .= " AND psa.status = ?"; $typesHist.='s'; $paramsHist[]=$f_stat; }
if ($search !== '') {
  $sqlHist .= " AND (psa.id_code LIKE ? OR psa.hersteller LIKE ? OR psa.modell LIKE ? OR psa.groesse LIKE ? OR psa.spindnr LIKE ?)";
  for ($i=0;$i<5;$i++){ $paramsHist[]=$likeSearch; $typesHist.='s'; }
}

// Grenzen: [first_of_first_month, first_of_month_after_last]
$firstMonth = $months[0];
$lastMonthBoundary = (clone $months[count($months)-1])->modify('first day of next month');

$sqlHist .= " AND datum >= ? AND 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();
$resHist = $sh->get_result();
$histMap = [];
while($r=$resHist->fetch_assoc()){ $histMap[$r['ym']] = (int)$r['cnt']; }
$histCounts = [];
foreach($months as $m){
  $key = $m->format('Y-m');
  $histCounts[] = $histMap[$key] ?? 0;
}
?>
<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">PSA – 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: ID/Hersteller/Modell/Größe/Spind" value="<?= h($search) ?>">
      <select name="typ" class="form-select">
        <option value="">Alle Typen</option>
        <?php foreach($typen as $t): ?>
          <option value="<?= h($t) ?>" <?= $f_typ===$t?'selected':'' ?>><?= h($t) ?></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_stat===$s?'selected':'' ?>><?= h($s) ?></option>
        <?php endforeach; ?>
      </select>
      <button class="btn btn-primary">Anwenden</button>
      <?php if ($search!=='' || $f_typ!=='' || $f_stat!==''): ?>
        <a href="psa_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-md-3">
      <div class="kpi">
        <div class="sub">Gesamt</div>
        <h3><?= (int)$K['total'] ?></h3>
      </div>
    </div>
    <div class="col-md-3">
      <div class="kpi">
        <div class="sub">Einsatzbereit</div>
        <h3><?= (int)$K['ready'] ?></h3>
      </div>
    </div>
    <div class="col-md-3">
      <div class="kpi">
        <div class="sub">Nicht Einsatzbereit</div>
        <h3><?= (int)$K['notready'] ?></h3>
      </div>
    </div>
    <div class="col-md-3">
      <div class="kpi">
        <div class="sub">Zuordnungsquote</div>
        <?php
          $assigned = (int)$A['assigned'];
          $unassigned = (int)$A['unassigned'];
          $quote = ($assigned+$unassigned) ? round($assigned*100/($assigned+$unassigned)) : 0;
        ?>
        <h3><?= $quote ?>%</h3>
        <div class="small text-muted"><?= $assigned ?> zugeordnet / <?= $unassigned ?> frei</div>
      </div>
    </div>
  </div>

  <!-- Charts -->
  <div class="row g-4">
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Verteilung nach Typ</h5>
        <canvas id="chartTypes" 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>

  <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 Hersteller</h5>
        <div class="table-responsive">
          <table class="table table-tight align-middle mb-0">
            <thead class="table-light"><tr><th>Hersteller</th><th class="text-end">Anzahl</th></tr></thead>
            <tbody>
              <?php if ($topBrand->num_rows===0): ?>
                <tr><td colspan="2" class="text-muted">keine Daten</td></tr>
              <?php else: while($b=$topBrand->fetch_assoc()): ?>
                <tr><td><?= h($b['hersteller'] ?: '—') ?></td><td class="text-end"><?= (int)$b['cnt'] ?></td></tr>
              <?php endwhile; endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
    <div class="col-lg-6">
      <div class="card-soft p-3">
        <h5 class="mb-3">Top 10 Modelle</h5>
        <div class="table-responsive">
          <table class="table table-tight align-middle mb-0">
            <thead class="table-light"><tr><th>Modell</th><th class="text-end">Anzahl</th></tr></thead>
            <tbody>
              <?php if ($topModel->num_rows===0): ?>
                <tr><td colspan="2" class="text-muted">keine Daten</td></tr>
              <?php else: while($m=$topModel->fetch_assoc()): ?>
                <tr><td><?= h($m['modell'] ?: '—') ?></td><td class="text-end"><?= (int)$m['cnt'] ?></td></tr>
              <?php endwhile; endif; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>

  <!-- Größe nach Typ -->
  <div class="card-soft p-3 mt-4">
    <h5 class="mb-3">Größen-Übersicht nach Typ (Top-Kombinationen)</h5>
    <div class="table-responsive">
      <table class="table table-tight align-middle mb-0">
        <thead class="table-light">
          <tr><th>Typ</th><th>Größe</th><th class="text-end">Anzahl</th></tr>
        </thead>
        <tbody>
          <?php if ($sizeRows->num_rows===0): ?>
            <tr><td colspan="3" class="text-muted">keine Daten</td></tr>
          <?php else: while($s=$sizeRows->fetch_assoc()): ?>
            <tr>
              <td><?= h($s['typ']) ?></td>
              <td><span class="text-monospace"><?= h($s['groesse']) ?></span></td>
              <td class="text-end"><?= (int)$s['cnt'] ?></td>
            </tr>
          <?php endwhile; endif; ?>
        </tbody>
      </table>
    </div>
  </div>

  <!-- Verlauf Historie -->
  <div class="card-soft p-3 mt-4">
    <h5 class="mb-3">Wartung/Pflege – Einträge (letzte 12 Monate)</h5>
    <canvas id="chartHist" height="110"></canvas>
  </div>
</div>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.1/dist/chart.umd.min.js"></script>
<script>
const typeLabels  = <?= json_encode($typeLabels, JSON_UNESCAPED_UNICODE) ?>;
const typeCounts  = <?= json_encode($typeCounts, JSON_NUMERIC_CHECK) ?>;
const statLabels  = <?= json_encode($statLabels, JSON_UNESCAPED_UNICODE) ?>;
const statCounts  = <?= json_encode($statCounts, 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) ?>;

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

new Chart(document.getElementById('chartTypes'), {
  type: 'bar',
  data: { labels: typeLabels, datasets: [{ label: 'Stück', data: typeCounts }] },
  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('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('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(); ?>
