<?php
/**
 * Bericht-Baukasten: frei konfigurierbare Auswertungen über Tickets.
 * Alle SQL-Bausteine stammen aus festen Listen (kein Nutzereingabe-SQL), Werte laufen über Parameter.
 */

/** Gruppierungen: key => [Bezeichnung, SQL-Ausdruck-Generator, JOIN, Anzeige-Spalte]. */
function report_dimensions(PDO $pdo): array
{
    $sqlite = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
    $bucket = fn(string $fmtSqlite, string $fmtMysql) => $sqlite ? "strftime('{$fmtSqlite}', t.created_at)" : "DATE_FORMAT(t.created_at, '{$fmtMysql}')";
    return [
        'department' => ['Abteilung',   'COALESCE(d.name, \'–\')',  'LEFT JOIN departments d ON d.id = t.department_id'],
        'help_topic' => ['Hilfethema',  'COALESCE(h.name, \'ohne Thema\')', 'LEFT JOIN help_topics h ON h.id = t.help_topic_id'],
        'agent'      => ['Mitarbeiter', 'COALESCE(s.name, \'nicht zugewiesen\')', 'LEFT JOIN staff s ON s.id = t.assigned_staff_id'],
        'team'       => ['Team',        'COALESCE(tm.name, \'ohne Team\')', 'LEFT JOIN teams tm ON tm.id = t.team_id'],
        'priority'   => ['Priorität',   'p.name',                   'JOIN priorities p ON p.id = t.priority_id'],
        'status'     => ['Status',      't.status',                 ''],
        'resolution_code' => ['Abschlussgrund', "CASE t.resolution_code WHEN 'fixed' THEN 'Fehler behoben' WHEN 'guidance' THEN 'Anleitung oder Beratung' WHEN 'duplicate' THEN 'Doppelt / zusammengeführt' WHEN 'not_reproducible' THEN 'Nicht reproduzierbar' WHEN 'no_action' THEN 'Keine Maßnahme erforderlich' WHEN 'spam' THEN 'Spam oder Sperre' WHEN 'automated' THEN 'Automatisch geschlossen' WHEN 'other' THEN 'Sonstiges' ELSE 'Nicht erfasst' END", ''],
        'source'     => ['Quelle',      't.source',                 ''],
        'day'        => ['Tag',         $bucket('%Y-%m-%d', '%Y-%m-%d'), ''],
        'week'       => ['Woche',       $bucket('%Y-W%W', '%x-W%v'), ''],
        'month'      => ['Monat',       $bucket('%Y-%m', '%Y-%m'),  ''],
    ];
}

/** Kennzahlen: key => [Bezeichnung, SQL-Ausdruck, Einheit]. */
function report_metrics(PDO $pdo): array
{
    $sqlite = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
    $hours = fn(string $from, string $to) => $sqlite
        ? "(julianday({$to}) - julianday({$from})) * 24"
        : "TIMESTAMPDIFF(MINUTE, {$from}, {$to}) / 60";
    return [
        'total'        => ['Tickets gesamt',        'COUNT(*)', ''],
        'open'         => ['Offen',                 "SUM(CASE WHEN t.status NOT IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
        'resolved'     => ['Gelöst/Geschlossen',    "SUM(CASE WHEN t.status IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
        'overdue'      => ['Überfällig',            "SUM(CASE WHEN t.is_overdue = 1 AND t.status NOT IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
        'avg_response' => ['Ø Erste Antwort',       'AVG(CASE WHEN t.first_responded_at IS NOT NULL THEN ' . $hours('t.created_at', 't.first_responded_at') . ' END)', 'Std'],
        'avg_resolve'  => ['Ø Lösungszeit',         'AVG(CASE WHEN t.resolved_at IS NOT NULL THEN ' . $hours('t.created_at', 't.resolved_at') . ' END)', 'Std'],
        'sla_response' => ['SLA Erste Antwort',     'AVG(CASE WHEN t.first_responded_at IS NOT NULL AND t.first_response_due_at IS NOT NULL THEN (CASE WHEN t.first_responded_at <= t.first_response_due_at THEN 100.0 ELSE 0.0 END) END)', '%'],
        'sla_resolve'  => ['SLA Lösung',            'AVG(CASE WHEN t.resolved_at IS NOT NULL AND t.resolution_due_at IS NOT NULL THEN (CASE WHEN t.resolved_at <= t.resolution_due_at THEN 100.0 ELSE 0.0 END) END)', '%'],
    ];
}

/** Zulässige Filter: key => SQL-Spalte. */
function report_filter_columns(): array
{
    return ['departmentId' => 't.department_id', 'priorityId' => 't.priority_id', 'agentId' => 't.assigned_staff_id',
            'helpTopicId' => 't.help_topic_id', 'status' => 't.status', 'source' => 't.source'];
}

/** Bereinigt eine Berichtskonfiguration auf erlaubte Werte (mit sinnvollen Standards). */
function report_clean_config(PDO $pdo, array $in): array
{
    $dims = report_dimensions($pdo); $metrics = report_metrics($pdo);
    $dimension = isset($dims[$in['dimension'] ?? '']) ? $in['dimension'] : 'department';
    $chosen = array_values(array_filter((array) ($in['metrics'] ?? []), fn($m) => is_string($m) && isset($metrics[$m])));
    $valid = fn($d) => is_string($d) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $d) && strtotime($d) !== false;
    $to = $valid($in['to'] ?? null) ? $in['to'] : date('Y-m-d');
    $from = $valid($in['from'] ?? null) ? $in['from'] : date('Y-m-d', strtotime('-30 days'));
    if ($from > $to) [$from, $to] = [$to, $from];
    $filters = [];
    foreach (array_keys(report_filter_columns()) as $k) {
        if (isset($in['filters'][$k]) && is_scalar($in['filters'][$k]) && trim((string) $in['filters'][$k]) !== '') $filters[$k] = trim((string) $in['filters'][$k]);
    }
    $sort = $in['sort'] ?? 'label';
    $sort = ($sort === 'label' || isset($metrics[$sort])) ? $sort : 'label';
    return [
        'dimension' => $dimension, 'metrics' => $chosen ?: ['total', 'open', 'resolved'],
        'from' => $from, 'to' => $to, 'filters' => $filters, 'sort' => $sort, 'desc' => !empty($in['desc']),
    ];
}

/**
 * Führt einen Bericht aus. $deptScope = erlaubte Abteilungs-IDs des Mitarbeiters (null = alle).
 *
 * @return array{columns: array<int,array{key:string,label:string,unit:string}>, rows: array<int,array>, totals: array}
 */
function report_run(PDO $pdo, array $config, ?array $deptScope): array
{
    $config = report_clean_config($pdo, $config);
    $dims = report_dimensions($pdo); $metrics = report_metrics($pdo);
    [$dimLabel, $dimExpr, $dimJoin] = $dims[$config['dimension']];

    $select = ["{$dimExpr} AS label"];
    foreach ($config['metrics'] as $m) $select[] = $metrics[$m][1] . " AS m_{$m}";
    $where = ['t.created_at >= ?', 't.created_at < ?'];
    $params = [$config['from'] . ' 00:00:00', date('Y-m-d', strtotime($config['to'] . ' +1 day')) . ' 00:00:00'];
    foreach ($config['filters'] as $k => $v) {
        $where[] = report_filter_columns()[$k] . ' = ?';
        $params[] = $v;
    }
    [$scopeSql, $scopeParams] = dept_filter_sql($deptScope, 't.department_id');
    $joins = [$dimJoin];
    $sql = 'SELECT ' . implode(', ', $select) . ' FROM tickets t ' . implode(' ', array_filter($joins))
         . ' WHERE ' . implode(' AND ', $where) . $scopeSql . ' AND t.is_spam = 0 GROUP BY label';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([...$params, ...$scopeParams]);
    $rows = $stmt->fetchAll();

    foreach ($rows as &$r) {
        foreach ($config['metrics'] as $m) {
            $v = $r["m_{$m}"];
            $r[$m] = $v === null ? null : ($metrics[$m][2] === '' ? (int) $v : round((float) $v, 1));
            unset($r["m_{$m}"]);
        }
    }
    unset($r);
    $key = $config['sort'];
    usort($rows, function ($a, $b) use ($key) {
        $c = $key === 'label' ? strnatcasecmp((string) $a['label'], (string) $b['label']) : (($a[$key] ?? -1) <=> ($b[$key] ?? -1));
        return $c;
    });
    if ($config['desc']) $rows = array_reverse($rows);

    $totals = [];
    foreach ($config['metrics'] as $m) {
        $vals = array_filter(array_column($rows, $m), fn($v) => $v !== null);
        if ($metrics[$m][2] === '') $totals[$m] = array_sum($vals);
        else $totals[$m] = $vals ? round(array_sum($vals) / count($vals), 1) : null; // Durchschnitt der Zeilen
    }
    $columns = [['key' => 'label', 'label' => $dimLabel, 'unit' => '']];
    foreach ($config['metrics'] as $m) $columns[] = ['key' => $m, 'label' => $metrics[$m][0], 'unit' => $metrics[$m][2]];
    return ['config' => $config, 'columns' => $columns, 'rows' => $rows, 'totals' => $totals];
}

/** Bericht als CSV (Semikolon-getrennt mit UTF-8-BOM für Excel). */
function report_to_csv(array $report): string
{
    $out = fopen('php://temp', 'r+');
    fwrite($out, "\xEF\xBB\xBF");
    fputcsv($out, array_map(fn($c) => $c['label'] . ($c['unit'] ? " ({$c['unit']})" : ''), $report['columns']), ';');
    foreach ($report['rows'] as $row) {
        $line = [];
        foreach ($report['columns'] as $c) {
            $v = $row[$c['key']] ?? '';
            // Schutz vor Formel-Injektion in Tabellenkalkulationen
            if ($c['key'] === 'label' && is_string($v) && preg_match('/^[=+\-@]/', $v)) $v = "'" . $v;
            $line[] = is_float($v) ? str_replace('.', ',', (string) $v) : $v;
        }
        fputcsv($out, $line, ';');
    }
    $tot = ['Gesamt'];
    foreach (array_slice($report['columns'], 1) as $c) $tot[] = $report['totals'][$c['key']] ?? '';
    fputcsv($out, $tot, ';');
    rewind($out);
    return (string) stream_get_contents($out);
}