<?php

/**
 * GET /api/tickets/export
 * Builds a styled workbook of the tickets the caller may see, honouring the
 * same filters as the list, plus an optional date range. Two sheets: the
 * tickets themselves, and a summary of counts.
 */
route('GET', ['tickets', 'export'], function () {
    $u      = Auth::require();
    $params = [];
    $where  = [Tickets::scope($u, $params)];
    $applied = [];

    if (!empty($_GET['status'])) {
        $where[]   = 't.status = ?';
        $params[]  = $_GET['status'];
        $applied[] = 'סטטוס: ' . (Tickets::STATUS_LABELS[$_GET['status']] ?? $_GET['status']);
    }
    if (!empty($_GET['type'])) {
        $where[]   = 't.type = ?';
        $params[]  = $_GET['type'];
        $applied[] = 'סוג: ' . (Tickets::TYPE_LABELS[$_GET['type']] ?? $_GET['type']);
    }
    if (!empty($_GET['severity'])) {
        $where[]   = 't.severity = ?';
        $params[]  = $_GET['severity'];
        $applied[] = 'חומרה: ' . (Tickets::SEVERITY_LABELS[$_GET['severity']] ?? $_GET['severity']);
    }
    if (!empty($_GET['customer_id']) && $u['role'] !== 'customer') {
        $where[]   = 't.customer_id = ?';
        $params[]  = (int) $_GET['customer_id'];
        $applied[] = 'לקוח: ' . DB::value('SELECT name FROM customers WHERE id = ?', [(int) $_GET['customer_id']]);
    }
    if (!empty($_GET['assigned_to'])) {
        $where[]   = 't.assigned_to = ?';
        $params[]  = (int) $_GET['assigned_to'];
        $applied[] = 'מטפל: ' . DB::value('SELECT full_name FROM users WHERE id = ?', [(int) $_GET['assigned_to']]);
    }
    if (!empty($_GET['from'])) {
        $where[]   = 't.created_at >= ?';
        $params[]  = $_GET['from'] . ' 00:00:00';
        $applied[] = 'מתאריך ' . $_GET['from'];
    }
    if (!empty($_GET['to'])) {
        $where[]   = 't.created_at <= ?';
        $params[]  = $_GET['to'] . ' 23:59:59';
        $applied[] = 'עד תאריך ' . $_GET['to'];
    }
    if (!empty($_GET['q'])) {
        $where[]  = '(t.ticket_number LIKE ? OR t.description LIKE ? OR t.serial_snapshot LIKE ? OR t.location LIKE ?)';
        $like     = '%' . $_GET['q'] . '%';
        array_push($params, $like, $like, $like, $like);
        $applied[] = 'חיפוש: ' . $_GET['q'];
    }
    if (isset($_GET['open']) && $_GET['open'] === '1') {
        $where[]   = 't.status NOT IN ("closed", "cancelled")';
        $applied[] = 'פתוחות בלבד';
    }

    $rows = DB::all(
        'SELECT t.*, c.name AS customer_name, p.name AS product_name, m.name AS manufacturer_name,
                creator.full_name AS created_by_name, handler.full_name AS assigned_to_name,
                closer.full_name AS closed_by_name
         FROM tickets t
         JOIN customers c           ON c.id = t.customer_id
         LEFT JOIN products p       ON p.id = t.product_id
         LEFT JOIN manufacturers m  ON m.id = t.manufacturer_id
         JOIN users creator         ON creator.id = t.created_by
         LEFT JOIN users handler    ON handler.id = t.assigned_to
         LEFT JOIN users closer     ON closer.id = t.closed_by
         WHERE ' . implode(' AND ', $where) . '
         ORDER BY t.created_at DESC
         LIMIT 20000',
        $params
    );

    $sevTerm = $u['role'] === 'customer' ? 'תיעדוף' : 'חומרה';
    $headers = [
        'מספר קריאה', 'סוג', 'סטטוס', $sevTerm, 'לקוח', 'יחידה', 'יצרן', 'מוצר', 'מספר סידורי',
        'מיקום', 'איש קשר', 'טלפון', 'תיאור', 'פותח הקריאה', 'מטפל',
        'נפתחה', 'שויכה', 'הסתיים הטיפול', 'נסגרה', 'שעות עד סגירה',
        'סיכום הטיפול', 'סיכום הסגירה',
    ];
    $widths = [13, 12, 18, 10, 20, 14, 14, 20, 16, 16, 14, 13, 46, 16, 16, 17, 17, 17, 17, 11, 40, 40];

    $sheet = [];
    $sheet[] = ['height' => 30, 'cells' => array_merge(
        [['v' => 'קריאות שירות — Smart Shooter', 's' => Xlsx::S_TITLE]],
        array_fill(0, count($headers) - 1, ['v' => '', 's' => Xlsx::S_TITLE])
    )];
    $subtitle = 'הופק ' . date('d/m/Y H:i') . ' · ' . count($rows) . ' קריאות'
              . ($applied ? ' · ' . implode(' · ', $applied) : ' · כל הקריאות');
    $sheet[] = ['height' => 18, 'cells' => [['v' => $subtitle, 's' => Xlsx::S_SUBTITLE]]];
    $sheet[] = ['cells' => []];

    $headerCells = [];
    foreach ($headers as $h) {
        $headerCells[] = ['v' => $h, 's' => Xlsx::S_HEADER];
    }
    $sheet[] = ['height' => 28, 'cells' => $headerCells];

    foreach ($rows as $i => $t) {
        $alt  = $i % 2 === 1;
        $txt  = $alt ? Xlsx::S_TEXT_ALT : Xlsx::S_TEXT;
        $date = $alt ? Xlsx::S_DATE_ALT : Xlsx::S_DATE;
        $num  = $alt ? Xlsx::S_NUM_ALT  : Xlsx::S_NUM;

        $hours = ($t['closed_at'] && $t['created_at'])
            ? round((strtotime($t['closed_at']) - strtotime($t['created_at'])) / 3600, 1) : null;

        $d = fn($v) => ($x = Xlsx::dateValue($v)) === null ? ['v' => '', 's' => $date] : ['v' => $x, 't' => 'd', 's' => $date];

        $sheet[] = ['cells' => [
            ['v' => $t['ticket_number'], 's' => $num],
            ['v' => Tickets::TYPE_LABELS[$t['type']] ?? $t['type'], 's' => $txt],
            ['v' => Tickets::STATUS_LABELS[$t['status']] ?? $t['status'], 's' => $txt],
            ['v' => $t['severity'] ? (Tickets::SEVERITY_LABELS[$t['severity']] ?? '') : '', 's' => $txt],
            ['v' => $t['customer_name'], 's' => $txt],
            ['v' => $t['unit_name'], 's' => $txt],
            ['v' => $t['manufacturer_name'], 's' => $txt],
            ['v' => $t['product_name'], 's' => $txt],
            ['v' => $t['serial_snapshot'], 's' => $txt],
            ['v' => $t['location'], 's' => $txt],
            ['v' => $t['contact_name'], 's' => $txt],
            ['v' => $t['contact_phone'], 's' => $txt],
            ['v' => $t['description'], 's' => $txt],
            ['v' => $t['created_by_name'], 's' => $txt],
            ['v' => $t['assigned_to_name'], 's' => $txt],
            $d($t['created_at']), $d($t['assigned_at']), $d($t['handler_done_at']), $d($t['closed_at']),
            $hours === null ? ['v' => '', 's' => $num] : ['v' => $hours, 't' => 'n', 's' => $num],
            ['v' => $t['handler_summary'], 's' => $txt],
            ['v' => $t['close_summary'], 's' => $txt],
        ]];
    }

    $xlsx = new Xlsx();
    $xlsx->addSheet('קריאות', $sheet, $widths, 4,
        'A4:' . Xlsx::col(count($headers) - 1) . (4 + count($rows)));

    // ---- summary sheet
    $byStatus   = [];
    $bySeverity = [];
    $byHandler  = [];
    $openCount  = 0;
    foreach ($rows as $t) {
        $byStatus[$t['status']] = ($byStatus[$t['status']] ?? 0) + 1;
        if ($t['severity']) {
            $bySeverity[$t['severity']] = ($bySeverity[$t['severity']] ?? 0) + 1;
        }
        if ($t['assigned_to_name']) {
            $byHandler[$t['assigned_to_name']] = ($byHandler[$t['assigned_to_name']] ?? 0) + 1;
        }
        if (!in_array($t['status'], ['closed', 'cancelled'], true)) {
            $openCount++;
        }
    }

    $sum = [];
    $sum[] = ['height' => 30, 'cells' => [['v' => 'סיכום', 's' => Xlsx::S_TITLE], ['v' => '', 's' => Xlsx::S_TITLE], ['v' => '', 's' => Xlsx::S_TITLE]]];
    $sum[] = ['cells' => [['v' => $subtitle, 's' => Xlsx::S_SUBTITLE]]];
    $sum[] = ['cells' => []];
    $sum[] = ['height' => 26, 'cells' => [['v' => 'סך הכל קריאות', 's' => Xlsx::S_LABEL], ['v' => count($rows), 't' => 'n', 's' => Xlsx::S_BIG]]];
    $sum[] = ['height' => 26, 'cells' => [['v' => 'פתוחות', 's' => Xlsx::S_LABEL], ['v' => $openCount, 't' => 'n', 's' => Xlsx::S_BIG]]];
    $sum[] = ['cells' => []];

    $block = function (string $title, array $data, array $labels) use (&$sum) {
        $sum[] = ['height' => 22, 'cells' => [['v' => $title, 's' => Xlsx::S_HEADER], ['v' => 'כמות', 's' => Xlsx::S_HEADER]]];
        $i = 0;
        foreach ($data as $key => $count) {
            $alt = $i++ % 2 === 1;
            $sum[] = ['cells' => [
                ['v' => $labels[$key] ?? $key, 's' => $alt ? Xlsx::S_TEXT_ALT : Xlsx::S_TEXT],
                ['v' => $count, 't' => 'n', 's' => $alt ? Xlsx::S_NUM_ALT : Xlsx::S_NUM],
            ]];
        }
        $sum[] = ['cells' => []];
    };
    $block('לפי סטטוס', $byStatus, Tickets::STATUS_LABELS);
    $block('לפי ' . $sevTerm, $bySeverity, Tickets::SEVERITY_LABELS);
    if ($byHandler) {
        arsort($byHandler);
        $block('לפי מטפל', $byHandler, []);
    }
    $xlsx->addSheet('סיכום', $sum, [30, 14]);

    $file = sys_get_temp_dir() . '/tickets-' . bin2hex(random_bytes(6)) . '.xlsx';
    $xlsx->save($file);

    $name = 'tickets-' . date('Y-m-d') . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $name . '"');
    header('Content-Length: ' . filesize($file));
    header('Cache-Control: no-store');
    readfile($file);
    @unlink($file);
    exit;
});

