<?php
session_start();
require_once('inc/config.inc.php');
require_once('inc/functions.inc.php');
require_once __DIR__ . '/inc/vacation_absence.inc.php';

$user = check_user();
if (!is_admin_user()) {
    die('Zugriff verweigert. Nur Chefs dürfen die Abwesenheitsübersicht sehen.');
}

$schemaWarning = '';
try {
    vacationAbsenceEnsureSchema($pdo);
} catch (Throwable $e) {
    $schemaWarning = 'Das Abwesenheitsschema konnte nicht automatisch aktualisiert werden: ' . $e->getMessage();
}

include 'header.php';

// Jahr für Auswertung
$year = date('Y');
$yearStart = $year . '-01-01';
$nextYearStart = (string)((int)$year + 1) . '-01-01';

$reasonOptions = vacationAbsenceReasonOptions();
$reasonKeys = array_keys($reasonOptions);

function vacationOverviewStatusLabel(?string $status): string
{
    $status = trim((string)$status);
    if ($status === '' || $status === 'beantragt') {
        return 'Beantragt';
    }
    if ($status === 'genehmigt') {
        return 'Genehmigt';
    }
    if ($status === 'abgelehnt') {
        return 'Abgelehnt';
    }
    return ucfirst($status);
}

// Lade alle Mitarbeiter
$stmt = $pdo->prepare("SELECT id, vorname, nachname, email, urlaubstage FROM users ORDER BY nachname, vorname");
$stmt->execute();
$users = $stmt->fetchAll();

$statsStmt = $pdo->prepare("
        SELECT
            user_id,
            COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'urlaub' AND LOWER(TRIM(COALESCE(status, ''))) = 'genehmigt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS urlaub_used_days,
            COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS total_absence_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(status, ''))) = 'beantragt' OR TRIM(COALESCE(status, '')) = '' THEN 1 ELSE 0 END), 0) AS pending_count,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'urlaub' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS urlaub_total_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'krankheit_mit_atest' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS krankheit_mit_atest_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'krankheit_ohne_atest' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS krankheit_ohne_atest_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'berufsschule' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS berufsschule_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'weiterbildung' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS weiterbildung_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'persoenliche_gruende' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS persoenliche_gruende_days,
        COALESCE(SUM(CASE WHEN LOWER(TRIM(COALESCE(absence_reason, 'urlaub'))) = 'sonstiges' AND LOWER(TRIM(COALESCE(status, ''))) != 'abgelehnt' THEN COALESCE(days, 0) ELSE 0 END), 0) AS sonstiges_days
    FROM vacations
    WHERE start_date >= :year_start
      AND start_date < :next_year_start
    GROUP BY user_id
");
$statsStmt->execute([
    'year_start' => $yearStart,
    'next_year_start' => $nextYearStart,
]);
$statsRows = $statsStmt->fetchAll(PDO::FETCH_ASSOC);
$statsByUser = [];
foreach ($statsRows as $row) {
    $statsByUser[(int)$row['user_id']] = $row;
}

$upcomingStmt = $pdo->prepare("
    SELECT
        v.user_id,
        v.start_date,
        v.end_date,
        v.days,
        v.status,
        v.absence_reason,
        u.vorname,
        u.nachname
    FROM vacations v
    JOIN users u ON u.id = v.user_id
    WHERE v.end_date >= CURDATE()
      AND LOWER(TRIM(COALESCE(v.status, ''))) != 'abgelehnt'
    ORDER BY v.start_date ASC, v.end_date ASC
");
$upcomingStmt->execute();
$upcomingRows = $upcomingStmt->fetchAll(PDO::FETCH_ASSOC);
$upcomingByUser = [];
foreach ($upcomingRows as $row) {
    $uid = (int)$row['user_id'];
    if (!isset($upcomingByUser[$uid])) {
        $upcomingByUser[$uid] = [];
    }
    if (count($upcomingByUser[$uid]) < 5) {
        $upcomingByUser[$uid][] = $row;
    }
}

function vacationOverviewReasonValue(array $statsByUser, int $userId, string $key): int
{
    $map = [
        'urlaub' => 'urlaub_total_days',
        'krankheit_mit_atest' => 'krankheit_mit_atest_days',
        'krankheit_ohne_atest' => 'krankheit_ohne_atest_days',
        'berufsschule' => 'berufsschule_days',
        'weiterbildung' => 'weiterbildung_days',
        'persoenliche_gruende' => 'persoenliche_gruende_days',
        'sonstiges' => 'sonstiges_days',
    ];

    $field = $map[$key] ?? null;
    if ($field === null || !isset($statsByUser[$userId])) {
        return 0;
    }

    return (int)($statsByUser[$userId][$field] ?? 0);
}

?>

<div class="container">
    <h2>Abwesenheitsübersicht (<?php echo $year; ?>)</h2>

    <?php if (!empty($schemaWarning)): ?>
    <div class="alert alert-warning"><?php echo htmlspecialchars($schemaWarning); ?></div>
    <?php endif; ?>

    <div class="alert alert-info">
        <strong>Hinweis:</strong> Nur der Grund <em>Urlaub</em> zählt auf den Urlaubsanspruch. Alle anderen Abwesenheitsgründe werden hier zusätzlich pro Jahr ausgewertet.
    </div>

    <div class="table-responsive">
    <table class="table table-striped table-bordered">
        <thead>
            <tr>
                <th>Mitarbeiter</th>
                <th>Email</th>
                <th>Anspruch</th>
                <th>Urlaub genutzt (<?php echo $year; ?>)</th>
                <th>Verbleibend</th>
                <th>Alle Abwesenheiten</th>
                <th>Offene Anträge</th>
                <?php foreach ($reasonKeys as $reasonKey): ?>
                    <th><?php echo htmlspecialchars(vacationAbsenceReasonLabel($reasonKey)); ?></th>
                <?php endforeach; ?>
                <th>Bevorstehende Einträge</th>
            </tr>
        </thead>
        <tbody>
        <?php foreach ($users as $u):
            $uid = $u['id'];

            $entitlement = isset($u['urlaubstage']) ? (int)$u['urlaubstage'] : 0;
            $used = (int)($statsByUser[$uid]['urlaub_used_days'] ?? 0);
            $remaining = $entitlement - $used;
            $totalAbsences = (int)($statsByUser[$uid]['total_absence_days'] ?? 0);
            $pending = (int)($statsByUser[$uid]['pending_count'] ?? 0);
            $upcoming = $upcomingByUser[$uid] ?? [];
        ?>
            <tr>
                <td><?php echo htmlspecialchars($u['vorname'] . ' ' . $u['nachname']); ?></td>
                <td><?php echo htmlspecialchars($u['email']); ?></td>
                <td><?php echo $entitlement; ?></td>
                <td><?php echo $used; ?></td>
                <td><?php echo $remaining; ?></td>
                <td><?php echo $totalAbsences; ?></td>
                <td><?php echo $pending; ?></td>
                <?php foreach ($reasonKeys as $reasonKey): ?>
                    <td><?php echo vacationOverviewReasonValue($statsByUser, (int)$uid, $reasonKey); ?></td>
                <?php endforeach; ?>
                <td>
                    <?php if (count($upcoming) == 0) { echo '-'; } else {
                        foreach ($upcoming as $up) {
                            $reasonLabel = vacationAbsenceReasonLabel($up['absence_reason'] ?? 'urlaub');
                            $statusLabel = vacationOverviewStatusLabel($up['status'] ?? null);
                            echo htmlspecialchars($up['start_date'] . ' → ' . $up['end_date'] . ' (' . $up['days'] . 'd) ' . ' [' . $reasonLabel . ', ' . $statusLabel . ']');
                            echo '<br>';
                        }
                    } ?>
                </td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    </div>

    <p>Hinweis: Der Urlaubsanspruch wird weiterhin aus <strong>users.urlaubstage</strong> gelesen. Die zusätzlichen Spalten zeigen die Abwesenheiten je Grund pro Jahr.</p>

</div>

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