<?php

/* ---------------------------------------------------------------- customers */

route('GET', ['admin', 'customers'], function () {
    Auth::require('support', 'admin');
    Response::json(DB::all('SELECT * FROM customers ORDER BY name'));
});

route('POST', ['admin', 'customers'], function () {
    Auth::require('admin');
    $b = Response::body();
    $name = trim((string) ($b['name'] ?? ''));
    if ($name === '') {
        throw new ApiError('יש להזין שם לקוח', 400);
    }
    $id = DB::insert('INSERT INTO customers (name, tax_id, phone, address) VALUES (?,?,?,?)',
        [$name, $b['tax_id'] ?? null, $b['phone'] ?? null, $b['address'] ?? null]);
    Response::json(['id' => $id], 201);
});

route('PATCH', ['admin', 'customers', '#'], function (int $id) {
    Auth::require('admin');
    $b = Response::body();
    DB::run('UPDATE customers SET name = ?, tax_id = ?, phone = ?, address = ?, is_active = ? WHERE id = ?',
        [$b['name'], $b['tax_id'] ?? null, $b['phone'] ?? null, $b['address'] ?? null,
         (int) (bool) ($b['is_active'] ?? 1), $id]);
    Response::json(['ok' => true]);
});

/* ------------------------------------------------------------ manufacturers */

route('GET', ['admin', 'manufacturers'], function () {
    Auth::require('admin');
    Response::json(DB::all('SELECT * FROM manufacturers ORDER BY name'));
});

route('POST', ['admin', 'manufacturers'], function () {
    Auth::require('admin');
    $name = trim((string) (Response::body()['name'] ?? ''));
    if ($name === '') {
        throw new ApiError('יש להזין שם יצרן', 400);
    }
    Response::json(['id' => DB::insert('INSERT INTO manufacturers (name) VALUES (?)', [$name])], 201);
});

route('PATCH', ['admin', 'manufacturers', '#'], function (int $id) {
    Auth::require('admin');
    $b = Response::body();
    DB::run('UPDATE manufacturers SET name = ?, is_active = ? WHERE id = ?',
        [$b['name'], (int) (bool) ($b['is_active'] ?? 1), $id]);
    Response::json(['ok' => true]);
});

/* ----------------------------------------------------------------- products */

route('GET', ['admin', 'products'], function () {
    Auth::require('admin');
    Response::json(DB::all(
        'SELECT p.*, m.name AS manufacturer_name FROM products p
         JOIN manufacturers m ON m.id = p.manufacturer_id ORDER BY m.name, p.name'
    ));
});

route('POST', ['admin', 'products'], function () {
    Auth::require('admin');
    $b = Response::body();
    if (empty($b['manufacturer_id']) || trim((string) ($b['name'] ?? '')) === '') {
        throw new ApiError('יש לבחור יצרן ולהזין שם מוצר', 400);
    }
    Response::json(['id' => DB::insert(
        'INSERT INTO products (manufacturer_id, name, model, catalog_number) VALUES (?,?,?,?)',
        [(int) $b['manufacturer_id'], trim($b['name']), $b['model'] ?? null, $b['catalog_number'] ?? null]
    )], 201);
});

route('PATCH', ['admin', 'products', '#'], function (int $id) {
    Auth::require('admin');
    $b = Response::body();
    DB::run('UPDATE products SET manufacturer_id = ?, name = ?, model = ?, catalog_number = ?, is_active = ? WHERE id = ?',
        [(int) $b['manufacturer_id'], $b['name'], $b['model'] ?? null, $b['catalog_number'] ?? null,
         (int) (bool) ($b['is_active'] ?? 1), $id]);
    Response::json(['ok' => true]);
});

/* ------------------------------------------------------- serials (units) */

route('GET', ['admin', 'units'], function () {
    Auth::require('admin');
    Response::json(DB::all(
        'SELECT pu.*, p.name AS product_name, c.name AS customer_name
         FROM product_units pu
         JOIN products p ON p.id = pu.product_id
         LEFT JOIN customers c ON c.id = pu.customer_id
         ORDER BY p.name, pu.serial_number LIMIT 2000'
    ));
});

route('POST', ['admin', 'units'], function () {
    Auth::require('admin');
    $b = Response::body();
    $serial = trim((string) ($b['serial_number'] ?? ''));
    if (empty($b['product_id']) || $serial === '') {
        throw new ApiError('יש לבחור מוצר ולהזין מספר סידורי', 400);
    }
    if (DB::value('SELECT id FROM product_units WHERE serial_number = ?', [$serial])) {
        throw new ApiError('מספר סידורי זה כבר קיים במערכת', 409);
    }
    Response::json(['id' => DB::insert(
        'INSERT INTO product_units (product_id, customer_id, serial_number, warranty_until, notes) VALUES (?,?,?,?,?)',
        [(int) $b['product_id'], !empty($b['customer_id']) ? (int) $b['customer_id'] : null,
         $serial, $b['warranty_until'] ?? null, $b['notes'] ?? null]
    )], 201);
});

route('PATCH', ['admin', 'units', '#'], function (int $id) {
    Auth::require('admin');
    $b = Response::body();
    DB::run('UPDATE product_units SET product_id = ?, customer_id = ?, serial_number = ?,
             warranty_until = ?, notes = ?, is_active = ? WHERE id = ?',
        [(int) $b['product_id'], !empty($b['customer_id']) ? (int) $b['customer_id'] : null,
         $b['serial_number'], $b['warranty_until'] ?? null, $b['notes'] ?? null,
         (int) (bool) ($b['is_active'] ?? 1), $id]);
    Response::json(['ok' => true]);
});

/* -------------------------------------------------------------- users */

route('GET', ['admin', 'users'], function () {
    Auth::require('admin');
    Response::json(DB::all(
        'SELECT u.id, u.full_name, u.email, u.phone, u.is_active, u.twofa_enabled,
                u.twofa_confirmed_at, u.last_login_at, r.code AS role, r.name_he AS role_name,
                c.name AS customer_name, u.customer_id
         FROM users u JOIN roles r ON r.id = u.role_id
         LEFT JOIN customers c ON c.id = u.customer_id
         ORDER BY r.code, u.full_name'
    ));
});

route('POST', ['admin', 'users'], function () {
    Auth::require('admin');
    $b     = Response::body();
    $email = trim(strtolower((string) ($b['email'] ?? '')));
    $pass  = (string) ($b['password'] ?? '');
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        throw new ApiError('כתובת דוא"ל אינה תקינה', 400);
    }
    if (mb_strlen($pass) < 10) {
        throw new ApiError('הסיסמה חייבת להכיל לפחות 10 תווים', 400);
    }
    $roleId = DB::value('SELECT id FROM roles WHERE code = ?', [$b['role'] ?? '']);
    if (!$roleId) {
        throw new ApiError('תפקיד לא חוקי', 400);
    }
    if ($b['role'] === 'customer' && empty($b['customer_id'])) {
        throw new ApiError('משתמש לקוח חייב להיות משויך לארגון', 400);
    }
    if (DB::value('SELECT id FROM users WHERE email = ?', [$email])) {
        throw new ApiError('כתובת הדוא"ל כבר רשומה במערכת', 409);
    }
    Response::json(['id' => DB::insert(
        'INSERT INTO users (customer_id, role_id, full_name, email, phone, password_hash, twofa_enabled, must_change_pass)
         VALUES (?,?,?,?,?,?,?,1)',
        [!empty($b['customer_id']) ? (int) $b['customer_id'] : null, (int) $roleId,
         trim((string) $b['full_name']), $email, $b['phone'] ?? null,
         password_hash($pass, PASSWORD_BCRYPT), (int) (bool) ($b['twofa_enabled'] ?? 0)]
    )], 201);
});

route('PATCH', ['admin', 'users', '#'], function (int $id) {
    $me = Auth::require('admin');
    $b  = Response::body();
    $roleId = DB::value('SELECT id FROM roles WHERE code = ?', [$b['role'] ?? '']);
    if (!$roleId) {
        throw new ApiError('תפקיד לא חוקי', 400);
    }
    if ((int) $id === (int) $me['id'] && empty($b['is_active'])) {
        throw new ApiError('לא ניתן להשבית את המשתמש שלך', 400);
    }
    DB::run('UPDATE users SET full_name = ?, phone = ?, role_id = ?, customer_id = ?, is_active = ?, twofa_enabled = ? WHERE id = ?',
        [trim((string) $b['full_name']), $b['phone'] ?? null, (int) $roleId,
         !empty($b['customer_id']) ? (int) $b['customer_id'] : null,
         (int) (bool) ($b['is_active'] ?? 1), (int) (bool) ($b['twofa_enabled'] ?? 0), $id]);

    if (!empty($b['password'])) {
        if (mb_strlen((string) $b['password']) < 10) {
            throw new ApiError('הסיסמה חייבת להכיל לפחות 10 תווים', 400);
        }
        DB::run('UPDATE users SET password_hash = ?, must_change_pass = 1 WHERE id = ?',
            [password_hash((string) $b['password'], PASSWORD_BCRYPT), $id]);
    }
    Response::json(['ok' => true]);
});

/**
 * DELETE /api/admin/users/{id}
 * A user who has never touched a ticket is removed outright. One with history
 * cannot simply vanish — tickets must keep an author — so their work is first
 * reassigned to another user, named in `reassign_to`.
 */
route('DELETE', ['admin', 'users', '#'], function (int $id) {
    $me = Auth::require('admin');
    if ($id === (int) $me['id']) {
        throw new ApiError('לא ניתן למחוק את המשתמש שאיתו אתם מחוברים', 400);
    }
    $target = DB::one('SELECT u.*, r.code AS role FROM users u JOIN roles r ON r.id = u.role_id WHERE u.id = ?', [$id]);
    if (!$target) {
        throw new ApiError('המשתמש לא נמצא', 404);
    }

    $counts = [
        'tickets'     => (int) DB::value('SELECT COUNT(*) FROM tickets WHERE created_by = ?', [$id]),
        'comments'    => (int) DB::value('SELECT COUNT(*) FROM ticket_comments WHERE user_id = ?', [$id]),
        'attachments' => (int) DB::value('SELECT COUNT(*) FROM ticket_attachments WHERE uploaded_by = ?', [$id]),
    ];
    $hasHistory = array_sum($counts) > 0;

    $b          = Response::body();
    $reassignTo = !empty($b['reassign_to']) ? (int) $b['reassign_to'] : null;

    if ($hasHistory && !$reassignTo) {
        throw new ApiError(
            'למשתמש זה יש היסטוריה במערכת (' . $counts['tickets'] . ' קריאות, '
            . $counts['comments'] . ' הודעות, ' . $counts['attachments'] . ' קבצים). '
            . 'יש לבחור משתמש שאליו יועברו הפריטים, או להשבית את המשתמש במקום למחוק.', 409);
    }

    $pdo = DB::conn();
    $pdo->beginTransaction();
    try {
        if ($hasHistory) {
            if (!DB::value('SELECT id FROM users WHERE id = ? AND is_active = 1', [$reassignTo])) {
                throw new ApiError('המשתמש שנבחר להעברה אינו קיים או אינו פעיל', 400);
            }
            DB::run('UPDATE tickets SET created_by = ? WHERE created_by = ?', [$reassignTo, $id]);
            DB::run('UPDATE ticket_comments SET user_id = ? WHERE user_id = ?', [$reassignTo, $id]);
            DB::run('UPDATE ticket_attachments SET uploaded_by = ? WHERE uploaded_by = ?', [$reassignTo, $id]);
            DB::run('UPDATE documents SET uploaded_by = ? WHERE uploaded_by = ?', [$reassignTo, $id]);
        }
        // These may be emptied rather than moved.
        DB::run('UPDATE tickets SET assigned_to = NULL WHERE assigned_to = ?', [$id]);
        DB::run('UPDATE tickets SET assigned_by = NULL WHERE assigned_by = ?', [$id]);
        DB::run('UPDATE tickets SET closed_by   = NULL WHERE closed_by   = ?', [$id]);
        DB::run('UPDATE ticket_tasks SET completed_by = NULL WHERE completed_by = ?', [$id]);
        DB::run('UPDATE ticket_history SET user_id = NULL WHERE user_id = ?', [$id]);
        DB::run('UPDATE procedure_templates SET created_by = NULL WHERE created_by = ?', [$id]);
        DB::run('DELETE FROM users WHERE id = ?', [$id]);   // sessions and recovery codes cascade
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }

    Response::json(['ok' => true, 'reassigned' => $hasHistory ? $counts : null]);
});

/** POST /api/admin/users/{id}/reset-2fa — clears the secret so the user re-enrolls. */
route('POST', ['admin', 'users', '#', 'reset-2fa'], function (int $id) {
    Auth::require('admin');
    DB::run('UPDATE users SET twofa_secret_enc = NULL, twofa_secret_iv = NULL, twofa_secret_tag = NULL,
             twofa_confirmed_at = NULL, twofa_last_counter = NULL WHERE id = ?', [$id]);
    DB::run('DELETE FROM user_recovery_codes WHERE user_id = ?', [$id]);
    DB::run('DELETE FROM user_sessions WHERE user_id = ?', [$id]);
    Response::json(['ok' => true]);
});

/* ------------------------------------------------- procedure templates */

route('GET', ['admin', 'templates'], function () {
    Auth::require('admin');
    $templates = DB::all('SELECT * FROM procedure_templates ORDER BY name');
    foreach ($templates as &$t) {
        $t['items'] = DB::all(
            'SELECT * FROM procedure_template_items WHERE template_id = ? ORDER BY sort_order, id', [$t['id']]
        );
    }
    Response::json($templates);
});

route('POST', ['admin', 'templates'], function () {
    Auth::require('admin');
    $b = Response::body();
    if (trim((string) ($b['name'] ?? '')) === '') {
        throw new ApiError('יש להזין שם נוהל', 400);
    }
    Response::json(['id' => DB::insert(
        'INSERT INTO procedure_templates (name, applies_to, is_default, created_by) VALUES (?,?,?,?)',
        [trim($b['name']), $b['applies_to'] ?? 'both', (int) (bool) ($b['is_default'] ?? 0), Auth::user()['id']]
    )], 201);
});

/**
 * PUT /api/admin/templates/{id} — replaces the whole item list.
 * Existing tickets keep their own snapshot, so this is safe to edit at any time.
 */
route('PUT', ['admin', 'templates', '#'], function (int $id) {
    Auth::require('admin');
    $b = Response::body();
    $pdo = DB::conn();
    $pdo->beginTransaction();
    try {
        DB::run('UPDATE procedure_templates SET name = ?, applies_to = ?, is_default = ?, is_active = ? WHERE id = ?',
            [trim((string) $b['name']), $b['applies_to'] ?? 'both',
             (int) (bool) ($b['is_default'] ?? 0), (int) (bool) ($b['is_active'] ?? 1), $id]);
        DB::run('DELETE FROM procedure_template_items WHERE template_id = ?', [$id]);
        foreach (($b['items'] ?? []) as $i => $item) {
            if (trim((string) ($item['label'] ?? '')) === '') {
                continue;
            }
            DB::run(
                'INSERT INTO procedure_template_items (template_id, sort_order, label, hint, input_type, options_json, is_required)
                 VALUES (?,?,?,?,?,?,?)',
                [$id, $i + 1, trim($item['label']), $item['hint'] ?? null,
                 $item['input_type'] ?? 'textarea',
                 !empty($item['options']) ? json_encode($item['options'], JSON_UNESCAPED_UNICODE) : null,
                 (int) (bool) ($item['is_required'] ?? 0)]
            );
        }
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
    Response::json(['ok' => true]);
});

/**
 * DELETE /api/admin/templates/{id}
 * Safe at any time: tickets hold their own snapshot of the checklist, so
 * deleting a template never touches work already done or in progress.
 */
route('DELETE', ['admin', 'templates', '#'], function (int $id) {
    Auth::require('admin');
    if (!DB::value('SELECT id FROM procedure_templates WHERE id = ?', [$id])) {
        throw new ApiError('הנוהל לא נמצא', 404);
    }
    $inUse = (int) DB::value('SELECT COUNT(DISTINCT ticket_id) FROM ticket_tasks WHERE template_id = ?', [$id]);
    DB::run('DELETE FROM procedure_templates WHERE id = ?', [$id]);   // items cascade
    Response::json(['ok' => true, 'tickets_kept' => $inUse]);
});

/* ------------------------------------------------------------ settings */

route('GET', ['admin', 'settings'], function () {
    Auth::require('admin');
    $rows = DB::all('SELECT setting_key, setting_value FROM settings ORDER BY setting_key');
    foreach ($rows as &$r) {
        if ($r['setting_key'] === 'smtp_pass') {
            $r['setting_value'] = $r['setting_value'] ? '********' : '';
        }
    }
    Response::json($rows);
});

route('PUT', ['admin', 'settings'], function () {
    Auth::require('admin');
    foreach (Response::body() as $key => $value) {
        if ($key === 'smtp_pass' && $value === '********') {
            continue;
        }
        DB::run('INSERT INTO settings (setting_key, setting_value) VALUES (?,?)
                 ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)', [$key, (string) $value]);
    }
    Response::json(['ok' => true]);
});

/**
 * POST /api/admin/settings/test-mail
 * Sends one message straight away, bypassing the queue, and returns the SMTP
 * error verbatim when it fails — the whole point is to see what went wrong.
 */
route('POST', ['admin', 'settings', 'test-mail'], function () {
    $u  = Auth::require('admin');
    $to = trim((string) (Response::body()['to'] ?? ''));
    if ($to === '') {
        $to = (string) Config::setting('support_email', '');
    }
    if (!filter_var($to, FILTER_VALIDATE_EMAIL)) {
        throw new ApiError('כתובת הדוא"ל לבדיקה אינה תקינה', 400);
    }

    $host = trim((string) Config::setting('smtp_host', ''));
    $body = Mailer::template('הודעת בדיקה ממערכת קריאות השירות', [
        'נשלח על ידי' => $u['full_name'],
        'זמן השליחה'  => date('d/m/Y H:i:s'),
        'שרת יוצא'    => $host !== '' ? $host . ':' . Config::setting('smtp_port', '587') : 'sendmail מקומי',
    ], 'אם הודעה זו התקבלה, שליחת הדואר מהמערכת תקינה.');

    $started = microtime(true);
    try {
        Mailer::send($to, 'בדיקת דואר — מערכת קריאות שירות', $body);
    } catch (Throwable $e) {
        // The SMTP reply is the useful part, so it is passed through as-is.
        throw new ApiError('השליחה נכשלה: ' . mb_substr($e->getMessage(), 0, 400), 502);
    }
    Response::json([
        'ok' => true,
        'to' => $to,
        'ms' => (int) ((microtime(true) - $started) * 1000),
    ]);
});

/** GET /api/admin/settings/mail-queue — the last few messages and their state. */
route('GET', ['admin', 'settings', 'mail-queue'], function () {
    Auth::require('admin');
    Response::json(DB::all(
        'SELECT id, to_email, subject, status, attempts, last_error, created_at, sent_at
         FROM email_queue ORDER BY id DESC LIMIT 10'
    ));
});

/** POST /api/admin/settings/retry-mail — put failed messages back in the queue. */
route('POST', ['admin', 'settings', 'retry-mail'], function () {
    Auth::require('admin');
    $n = DB::run('UPDATE email_queue SET status = "pending", attempts = 0, last_error = NULL WHERE status = "failed"');
    Response::json(['ok' => true, 'requeued' => $n]);
});

/* ------------------------------------------------------------- reports */

route('GET', ['admin', 'stats'], function () {
    Auth::require('support', 'admin');
    Response::json([
        'by_status'   => DB::all('SELECT status, COUNT(*) AS n FROM tickets GROUP BY status'),
        'by_severity' => DB::all('SELECT severity, COUNT(*) AS n FROM tickets
                                  WHERE status NOT IN ("closed","cancelled") AND severity IS NOT NULL
                                  GROUP BY severity'),
        'by_handler'  => DB::all('SELECT u.full_name, COUNT(*) AS n FROM tickets t
                                  JOIN users u ON u.id = t.assigned_to
                                  WHERE t.status NOT IN ("closed","cancelled")
                                  GROUP BY u.id ORDER BY n DESC'),
        'opened_30d'  => (int) DB::value('SELECT COUNT(*) FROM tickets WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)'),
        'closed_30d'  => (int) DB::value('SELECT COUNT(*) FROM tickets WHERE closed_at > DATE_SUB(NOW(), INTERVAL 30 DAY)'),
        'avg_hours_to_close' => (float) DB::value(
            'SELECT AVG(TIMESTAMPDIFF(HOUR, created_at, closed_at)) FROM tickets
             WHERE closed_at > DATE_SUB(NOW(), INTERVAL 90 DAY)'),
    ]);
});
