<?php
include '../includes/db.php';
include '../includes/header.php';
include '../includes/nav.php';
session_start();

if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit;
}

$einheit = $_SESSION['einheit'] ?? '';
$monat = $_GET['monat'] ?? date('m');
$jahr = $_GET['jahr'] ?? date('Y');
$startdatum = "$jahr-$monat-01";
$enddatum = date("Y-m-t", strtotime($startdatum));

// Daten abfragen
$sql = "
SELECT u.id, u.vorname, u.nachname,
       COUNT(z.id) AS eintraege,
       ROUND(SUM(TIMESTAMPDIFF(SECOND, z.startzeit, z.endzeit))/3600, 2) AS gesamtstunden,
       ROUND(AVG(TIMESTAMPDIFF(SECOND, z.startzeit, z.endzeit))/3600, 2) AS durchschnitt,
       MIN(z.startzeit) AS erster,
       MAX(z.endzeit) AS letzter
FROM users u
LEFT JOIN zeiterfassung z ON z.kamerad_id = u.id AND z.endzeit IS NOT NULL AND z.startzeit BETWEEN ? AND ?
WHERE u.einheit = ?
GROUP BY u.id
ORDER BY gesamtstunden DESC
";
$stmt = $conn->prepare($sql);
$stmt->bind_param("sss", $startdatum, $enddatum, $einheit);
$stmt->execute();
$ergebnis = $stmt->get_result();

$labels = [];
$gesamtstunden = [];
$daten = [];

while ($row = $ergebnis->fetch_assoc()) {
    $labels[] = $row['vorname'] . ' ' . $row['nachname'];
    $gesamtstunden[] = $row['gesamtstunden'];
    $daten[] = $row;
}

// CSV Export
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="zeiterfassung_'.$monat.'_'.$jahr.'.csv"');
    $output = fopen("php://output", "w");
    fputcsv($output, ['Name', 'Gesamtstunden', 'Einträge', 'Ø Stunden', 'Erster Eintrag', 'Letzter Eintrag']);
    foreach ($daten as $d) {
        fputcsv($output, [
            $d['vorname'].' '.$d['nachname'],
            $d['gesamtstunden'],
            $d['eintraege'],
            $d['durchschnitt'],
            $d['erster'],
            $d['letzter']
        ]);
    }
    fclose($output);
    exit;
}
?>

<div class="container my-5">
  <h2 class="mb-4">📊 Zeiterfassung-Statistik – <?= strftime('%B %Y', strtotime($startdatum)) ?></h2>

  <form method="get" class="row g-3 mb-4">
    <div class="col-auto">
      <label for="monat" class="form-label">Monat</label>
      <select name="monat" id="monat" class="form-select">
        <?php for ($m = 1; $m <= 12; $m++): ?>
          <option value="<?= str_pad($m, 2, '0', STR_PAD_LEFT) ?>" <?= $monat == str_pad($m, 2, '0', STR_PAD_LEFT) ? 'selected' : '' ?>>
            <?= strftime('%B', mktime(0, 0, 0, $m, 1)) ?>
          </option>
        <?php endfor; ?>
      </select>
    </div>
    <div class="col-auto">
      <label for="jahr" class="form-label">Jahr</label>
      <select name="jahr" id="jahr" class="form-select">
        <?php foreach ([2025, 2024, 2023, 2022] as $y): ?>
          <option value="<?= $y ?>" <?= $jahr == $y ? 'selected' : '' ?>><?= $y ?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="col-auto align-self-end">
      <button class="btn btn-primary">Filtern</button>
    </div>
    <div class="col-auto align-self-end">
      <a href="?export=csv&monat=<?= $monat ?>&jahr=<?= $jahr ?>" class="btn btn-success">📥 CSV Export</a>
    </div>
  </form>

  <div class="table-responsive mb-5">
    <table class="table table-bordered table-striped">
      <thead class="table-dark">
        <tr>
          <th>Name</th>
          <th>Gesamtstunden</th>
          <th>Einträge</th>
          <th>Ø Stunden</th>
          <th>Erster Eintrag</th>
          <th>Letzter Eintrag</th>
          <th>Aktion</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($daten as $d): ?>
          <tr>
            <td><?= htmlspecialchars($d['vorname'] . ' ' . $d['nachname']) ?></td>
            <td><?= number_format($d['gesamtstunden'], 2, ',', '.') ?></td>
            <td><?= $d['eintraege'] ?></td>
            <td><?= number_format($d['durchschnitt'], 2, ',', '.') ?></td>
            <td><?= $d['erster'] ? date('d.m.Y H:i', strtotime($d['erster'])) : '-' ?></td>
            <td><?= $d['letzter'] ? date('d.m.Y H:i', strtotime($d['letzter'])) : '-' ?></td>
            <td>
              <a href="zeiterfassung_details.php?id=<?= $d['id'] ?>&monat=<?= $monat ?>&jahr=<?= $jahr ?>" class="btn btn-sm btn-outline-secondary">Details</a>
            </td>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>

  <canvas id="gesamtstundenChart" height="100"></canvas>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const ctx = document.getElementById('gesamtstundenChart').getContext('2d');
new Chart(ctx, {
  type: 'bar',
  data: {
    labels: <?= json_encode($labels) ?>,
    datasets: [{
      label: 'Gesamtstunden',
      data: <?= json_encode($gesamtstunden) ?>,
      backgroundColor: 'rgba(0,123,255,0.6)',
      borderColor: 'rgba(0,123,255,1)',
      borderWidth: 1
    }]
  },
  options: {
    indexAxis: 'y',
    scales: {
      x: {
        beginAtZero: true,
        title: { display: true, text: 'Stunden' }
      },
      y: {
        title: { display: true, text: 'Kamerad' }
      }
    }
  }
});
</script>

<?php include 'includes/footer.php'; ?>
