<?php
declare(strict_types=1);

/**
 * osTicket-Import (Kern): liest einen SQL-Dump (phpMyAdmin oder mysqldump, auch mit anderem Tabellenpräfix als "ost_")
 * und übernimmt Kunden, Abteilungen, Hilfethemen, Tickets, Nachrichten und Anhänge in DeskHub.
 *
 * Ablauf (jeweils in kleinen Schritten, damit auch Dumps mit mehreren hundert MB im Browser laufen):
 *   1. osticket_analyze_step(): liest die Datei blockweise, merkt sich die nötigen Daten (Zustandsordner) und schreibt
 *      Nachrichtentexte zeilenweise in entries.jsonl.
 *   2. osticket_import_step(): pro Durchgang (users, structure, tickets, threads, attachments) werden Datensätze ab
 *      einem Offset übernommen. Jeder Schritt meldet next_offset, damit der Aufrufer nichts überspringt.
 */

const OSTICKET_ATTACHMENT_MAX_BYTES = 50 * 1024 * 1024;

/** Tabellen, die gelesen werden (Schlüssel = Name ohne Präfix) mit den Spalten von osTicket 1.18 für Dumps ohne Spaltenliste. */
function osticket_tables(): array
{
    return [
        'user' => ['id', 'org_id', 'default_email_id', 'status', 'name', 'created', 'updated'],
        'user_email' => ['id', 'user_id', 'flags', 'address'],
        'ticket' => ['ticket_id', 'ticket_pid', 'number', 'user_id', 'user_email_id', 'status_id', 'dept_id', 'sla_id', 'topic_id', 'staff_id', 'team_id', 'email_id', 'lock_id', 'flags', 'sort', 'ip_address', 'source', 'source_extra', 'isoverdue', 'isanswered', 'duedate', 'est_duedate', 'reopened', 'closed', 'lastupdate', 'created', 'updated'],
        'ticket__cdata' => ['ticket_id', 'subject', 'priority'],
        'ticket_priority' => ['priority_id', 'priority', 'priority_desc', 'priority_color', 'priority_urgency', 'ispublic'],
        'ticket_status' => ['id', 'name', 'state', 'mode', 'flags', 'sort', 'properties', 'created', 'updated'],
        'thread' => ['id', 'object_id', 'object_type', 'extra', 'lastresponse', 'lastmessage', 'created'],
        'thread_collaborator' => ['id', 'flags', 'thread_id', 'user_id', 'role', 'created', 'updated'],
        'thread_entry' => ['id', 'pid', 'thread_id', 'staff_id', 'user_id', 'type', 'flags', 'poster', 'editor', 'editor_type', 'source', 'title', 'body', 'format', 'ip_address', 'extra', 'recipients', 'created', 'updated'],
        'department' => ['id', 'pid', 'tpl_id', 'sla_id', 'schedule_id', 'email_id', 'autoresp_email_id', 'manager_id', 'flags', 'name', 'signature', 'ispublic', 'group_membership', 'ticket_auto_response', 'message_auto_response', 'path', 'updated', 'created'],
        'help_topic' => ['topic_id', 'topic_pid', 'ispublic', 'isactive', 'ticket_auto_response', 'noautoresp', 'flags', 'status_id', 'priority_id', 'dept_id', 'staff_id', 'team_id', 'sla_id', 'page_id', 'sequence_id', 'sort', 'topic', 'number_format', 'notes', 'created', 'updated'],
        'staff' => ['staff_id', 'dept_id', 'role_id', 'username', 'firstname', 'lastname', 'passwd', 'backend', 'email'],
        'attachment' => ['id', 'object_id', 'type', 'file_id', 'name', 'inline', 'lang'],
        'file' => ['id', 'ft', 'bk', 'type', 'size', 'key', 'signature', 'name', 'attrs', 'created'],
        'file_chunk' => ['file_id', 'chunk_id', 'filedata'],
    ];
}

/** Tabellenname mit beliebigem Präfix -> Schlüssel aus osticket_tables() oder null. */
function osticket_table_key(string $table): ?string
{
    static $keys = null;
    $keys ??= (function () { $k = array_keys(osticket_tables()); usort($k, fn($a, $b) => strlen($b) <=> strlen($a)); return $k; })();
    foreach ($keys as $key) {
        if ($table === $key) return $key;
        if (str_ends_with($table, '_' . $key) || str_ends_with($table, $key) && strlen($table) > strlen($key) && $table[strlen($table) - strlen($key) - 1] === '_') return $key;
    }
    return null;
}

/** Inhalt zwischen Anführungszeichen (MySQL-Maskierung) in den echten Text umwandeln. */
function osticket_unescape(string $s): string
{
    if (!str_contains($s, '\\') && !str_contains($s, "''")) return $s;
    return (string) preg_replace_callback('/\\\\(.)|\'\'/s', function (array $m): string {
        if ($m[0] === "''") return "'";
        return match ($m[1]) { '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t", 'b' => "\x08", 'Z' => "\x1a", '%' => '\\%', '_' => '\\_', default => $m[1] };
    }, $s);
}

/**
 * Zerlegt "(a,'b'),(c,NULL)" in Wertelisten. Strings werden entmaskiert, NULL wird null, alles andere (Zahlen, 0x…) bleibt Text.
 * Funktioniert für eine Zeile pro Datensatz (phpMyAdmin) genauso wie für mehrere Datensätze in einer Zeile (mysqldump).
 *
 * @return Generator<int, array<int, ?string>>
 */
function osticket_rows(string $s): Generator
{
    $n = strlen($s);
    $i = 0;
    while ($i < $n) {
        $i = strpos($s, '(', $i);
        if ($i === false) return;
        $i++;
        $vals = [];
        $closed = false;
        while ($i < $n) {
            while ($i < $n && ($s[$i] === ' ' || $s[$i] === "\t")) $i++;
            if ($i >= $n) break;
            if ($s[$i] === "'") {
                $j = $i + 1;
                $buf = '';
                while (true) {
                    $k = $j + strcspn($s, "'\\", $j);
                    if ($k >= $n) { $buf .= substr($s, $j); $j = $n; break; }
                    $buf .= substr($s, $j, $k - $j);
                    if ($s[$k] === '\\') { $buf .= substr($s, $k, 2); $j = $k + 2; continue; }
                    if ($k + 1 < $n && $s[$k + 1] === "'") { $buf .= "''"; $j = $k + 2; continue; }
                    $j = $k + 1;
                    break;
                }
                $vals[] = osticket_unescape($buf);
                $i = $j;
            } else {
                $k = $i + strcspn($s, ',)', $i);
                $tok = trim(substr($s, $i, $k - $i));
                $vals[] = strcasecmp($tok, 'NULL') === 0 ? null : $tok;
                $i = $k;
            }
            while ($i < $n && ($s[$i] === ' ' || $s[$i] === "\t")) $i++;
            if ($i < $n && $s[$i] === ',') { $i++; continue; }
            if ($i < $n && $s[$i] === ')') { $i++; $closed = true; break; }
        }
        if ($closed) yield $vals;
    }
}

/** "INSERT INTO `t` (`a`,`b`) VALUES (..." -> [Tabelle, Spalten|null, Rest nach VALUES] oder null. */
function osticket_parse_header(string $line): ?array
{
    if (!preg_match('/^INSERT\s+(?:IGNORE\s+)?INTO\s+`?([A-Za-z0-9_]+)`?\s*(?:\(([^)]*)\))?\s*VALUES\s*(.*)$/s', $line, $m)) return null;
    $cols = isset($m[2]) && $m[2] !== '' ? array_map(fn($c) => trim($c, " `\t"), explode(',', $m[2])) : null;
    return [$m[1], $cols, $m[3]];
}

// ─── Zustand ─────────────────────────────────────────────────────────────────

function osticket_state_dir(): string
{
    $dir = sys_get_temp_dir() . '/deskhub_import_' . preg_replace('/[^a-zA-Z0-9]/', '', session_id() ?: 'cli');
    if (!is_dir($dir)) @mkdir($dir, 0700, true);
    return $dir;
}

function osticket_state_empty(): array
{
    return ['users' => [], 'emails' => [], 'tickets' => [], 'cdata' => [], 'thread_ticket' => [], 'ticket_thread' => [], 'collab' => [], 'first_resp' => [],
        'departments' => [], 'topics' => [], 'staff' => [], 'prios' => [], 'statuses' => [], 'att' => [], 'files' => [], 'inline_att' => 0,
        'entry_count' => 0, 'chunk_start' => null, 'ctx' => null, 'done' => false, 'maps' => null];
}

function osticket_state_load(string $dir): array
{
    $f = $dir . '/state.json';
    $st = is_file($f) ? json_decode((string) file_get_contents($f), true) : null;
    return is_array($st) ? $st + osticket_state_empty() : osticket_state_empty();
}

function osticket_state_save(string $dir, array $st): void
{
    file_put_contents($dir . '/state.json', json_encode($st, JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE), LOCK_EX);
}

function osticket_state_clear(string $dir): void
{
    foreach (glob($dir . '/*') ?: [] as $f) @unlink($f);
    @rmdir($dir);
}

// ─── Analyse ─────────────────────────────────────────────────────────────────

/** Liest einen Block der Datei ab $offset. Gibt ['offset' => neuer Offset, 'done' => bool, 'counts' => [...]] zurück. */
function osticket_analyze_step(string $sqlPath, string $dir, int $offset, int $maxBytes = 4 * 1024 * 1024): array
{
    if ($offset === 0) { foreach (['state.json', 'entries.jsonl', 'entry_map.jsonl'] as $f) @unlink($dir . '/' . $f); }
    $st = osticket_state_load($dir);
    $fh = fopen($sqlPath, 'rb');
    if (!$fh) throw new RuntimeException('Die SQL-Datei konnte nicht geöffnet werden.');
    fseek($fh, $offset);
    $entries = fopen($dir . '/entries.jsonl', 'ab');
    $tables = osticket_tables();
    $read = 0;
    $ctx = $st['ctx'];   // ['key' => Tabellenschlüssel|null, 'cols' => Spalten]

    while (!feof($fh) && $read < $maxBytes) {
        $lineStart = $offset + $read;
        $line = fgets($fh, 64 * 1024 * 1024);
        if ($line === false) break;
        $read += strlen($line);

        if ($line[0] === 'I' && str_starts_with($line, 'INSERT')) {
            $h = osticket_parse_header(rtrim($line));
            $ctx = null;
            if (!$h) continue;
            $key = osticket_table_key($h[0]);
            if ($key === null) continue;
            if ($key === 'file_chunk') { $st['chunk_start'] ??= $lineStart; continue; }
            $ctx = ['key' => $key, 'cols' => $h[1] ?? $tables[$key]];
            $rest = $h[2];
            if ($rest !== '') osticket_consume($st, $ctx, $rest, $entries);
            if (str_ends_with(rtrim($line), ';')) $ctx = null;
            continue;
        }
        if ($ctx === null) continue;
        if ($line[0] !== '(') { $ctx = null; continue; }
        osticket_consume($st, $ctx, $line, $entries);
        if (substr(rtrim($line), -1) === ';') $ctx = null;
    }
    $newOffset = $offset + $read;
    $done = feof($fh) || $line === false;
    fclose($fh);
    fclose($entries);
    $st['ctx'] = $done ? null : $ctx;
    if ($done) $st['done'] = true;
    osticket_state_save($dir, $st);
    return ['offset' => $newOffset, 'done' => $done, 'counts' => osticket_counts($st)];
}

/** Verarbeitet die Datensätze einer Zeile für die aktuelle Tabelle. */
function osticket_consume(array &$st, array $ctx, string $text, $entriesFh): void
{
    $key = $ctx['key'];
    $cols = $ctx['cols'];
    $nc = count($cols);
    foreach (osticket_rows($text) as $vals) {
        if (count($vals) !== $nc) continue;
        $r = array_combine($cols, $vals);
        switch ($key) {
            case 'user':
                $st['users'][$r['id']] = ['n' => (string) ($r['name'] ?? ''), 'd' => (int) ($r['default_email_id'] ?? 0)];
                break;
            case 'user_email':
                $st['emails'][$r['id']] = [(int) $r['user_id'], (string) $r['address']];
                break;
            case 'ticket':
                $st['tickets'][$r['ticket_id']] = ['n' => (string) $r['number'], 'u' => (int) $r['user_id'], 's' => (int) $r['status_id'], 'd' => (int) $r['dept_id'], 't' => (int) $r['topic_id'],
                    'a' => (int) $r['staff_id'], 'src' => (string) ($r['source'] ?? ''), 'c' => $r['created'], 'cl' => $r['closed'] ?? null, 'lu' => $r['lastupdate'] ?? null,
                    'od' => (int) ($r['isoverdue'] ?? 0), 'ip' => (string) ($r['ip_address'] ?? '')];
                break;
            case 'ticket__cdata':
                $st['cdata'][$r['ticket_id']] = [(string) ($r['subject'] ?? ''), (int) ($r['priority'] ?? 0)];
                break;
            case 'thread':
                if (($r['object_type'] ?? '') === 'T') { $st['thread_ticket'][$r['id']] = (int) $r['object_id']; $st['ticket_thread'][$r['object_id']] = (int) $r['id']; }
                break;
            case 'thread_collaborator':
                if ((int) $r['user_id'] > 0) $st['collab'][$r['thread_id']][] = (int) $r['user_id'];
                break;
            case 'thread_entry':
                $st['entry_count']++;
                if (($r['type'] ?? '') === 'R') {
                    $tid = (string) $r['thread_id'];
                    if (!isset($st['first_resp'][$tid]) || strcmp((string) $r['created'], $st['first_resp'][$tid]) < 0) $st['first_resp'][$tid] = (string) $r['created'];
                }
                fwrite($entriesFh, json_encode(['id' => (int) $r['id'], 'th' => (int) $r['thread_id'], 'st' => (int) $r['staff_id'], 'us' => (int) $r['user_id'], 'ty' => (string) $r['type'],
                    'po' => (string) ($r['poster'] ?? ''), 'b' => (string) ($r['body'] ?? ''), 'f' => (string) ($r['format'] ?? 'html'), 'c' => $r['created']], JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE) . "\n");
                break;
            case 'department':
                $st['departments'][$r['id']] = (string) $r['name'];
                break;
            case 'help_topic':
                $st['topics'][$r['topic_id']] = ['n' => (string) $r['topic'], 'd' => (int) $r['dept_id'], 'p' => (int) ($r['priority_id'] ?? 0), 'on' => (int) ($r['isactive'] ?? 1), 'pub' => (int) ($r['ispublic'] ?? 1)];
                break;
            case 'staff':
                $st['staff'][$r['staff_id']] = ['n' => trim(($r['firstname'] ?? '') . ' ' . ($r['lastname'] ?? '')), 'e' => strtolower((string) ($r['email'] ?? ''))];
                break;
            case 'ticket_priority':
                $st['prios'][$r['priority_id']] = ['n' => (string) ($r['priority'] ?? ''), 'd' => (string) ($r['priority_desc'] ?? ''), 'u' => (int) ($r['priority_urgency'] ?? 0)];
                break;
            case 'ticket_status':
                $st['statuses'][$r['id']] = ['n' => (string) $r['name'], 's' => (string) ($r['state'] ?? '')];
                break;
            case 'attachment':
                if (($r['type'] ?? '') === 'H') {
                    if ((int) ($r['inline'] ?? 0) === 1) $st['inline_att']++;
                    else $st['att'][$r['object_id']][] = [(int) $r['file_id'], $r['name'] === null ? null : (string) $r['name']];
                }
                break;
            case 'file':
                $st['files'][$r['id']] = ['n' => (string) $r['name'], 't' => (string) ($r['type'] ?? ''), 's' => (int) ($r['size'] ?? 0)];
                break;
        }
    }
}

/** E-Mail-Adresse und Name je osTicket-Kunde. */
function osticket_contacts(array $st): array
{
    $byUser = [];
    foreach ($st['emails'] as $id => [$uid, $addr]) $byUser[$uid][(int) $id] = $addr;
    $out = [];
    foreach ($st['users'] as $uid => $u) {
        $addr = $byUser[(int) $uid][$u['d']] ?? (isset($byUser[(int) $uid]) ? reset($byUser[(int) $uid]) : '');
        $out[(int) $uid] = ['name' => $u['n'], 'email' => trim((string) $addr)];
    }
    // Adressen ohne passenden Kundeneintrag trotzdem verfügbar machen
    foreach ($byUser as $uid => $list) if (!isset($out[$uid])) $out[$uid] = ['name' => '', 'email' => trim((string) reset($list))];
    return $out;
}

/** Status eines osTicket-Tickets -> DeskHub-Status oder null (gelöschte Tickets werden nicht importiert). */
function osticket_status(array $st, int $statusId): ?string
{
    $s = $st['statuses'][$statusId] ?? null;
    if ($s) {
        $state = strtolower($s['s']);
        if ($state === 'deleted') return null;
        if ($state === 'open') return 'open';
        if ($state === 'archived') return 'closed';
        if ($state === 'closed') return preg_match('/gelöst|geloest|resolved/i', $s['n']) ? 'resolved' : 'closed';
    }
    return [1 => 'open', 2 => 'resolved', 3 => 'closed', 4 => 'closed'][$statusId] ?? ($statusId === 5 ? null : 'closed');
}

function osticket_counts(array $st): array
{
    $tickets = 0;
    foreach ($st['tickets'] as $t) if (osticket_status($st, $t['s']) !== null) $tickets++;
    $attFiles = [];
    foreach ($st['att'] as $list) foreach ($list as [$fid]) $attFiles[$fid] = $st['files'][$fid]['s'] ?? 0;
    return ['users' => count(array_filter(osticket_contacts($st), fn($c) => $c['email'] !== '')), 'departments' => count($st['departments']), 'topics' => count($st['topics']),
        'tickets' => $tickets, 'threads' => (int) $st['entry_count'], 'attachments' => count($attFiles), 'attachment_bytes' => array_sum($attFiles)];
}