<?php
declare(strict_types=1);

class ApiController
{
    /**
     * Endpoints that may be reached via GET (no body, no side effects).
     * Everything else MUST come in via POST with a valid CSRF token —
     * otherwise an `<img src="/api/...">` on a third-party page could
     * trigger state changes against a logged-in user's session.
     */
    private const READ_ONLY_ENDPOINTS = [
        'notifications/unread',
        'notifications/recent',
    ];

    public static function handle(string $endpoint): void
    {
        $data = [];
        $body = file_get_contents('php://input');
        if (!empty($body)) {
            $data = json_decode($body, true) ?? [];
        }
        $data = array_merge($data, $_POST);

        $method     = $_SERVER['REQUEST_METHOD'] ?? 'GET';
        $isReadOnly = in_array($endpoint, self::READ_ONLY_ENDPOINTS, true);

        if ($isReadOnly) {
            if ($method !== 'GET' && $method !== 'POST') {
                Helper::json(['error' => 'Method not allowed'], 405);
            }
        } else {
            if ($method !== 'POST') {
                Helper::json(['error' => 'Method not allowed'], 405);
            }
            $token = $data['csrf_token'] ?? ($_SERVER['HTTP_X_CSRF_TOKEN'] ?? '');
            if (!Auth::verifyCsrfToken($token)) {
                Helper::json(['error' => 'CSRF mismatch'], 403);
            }
        }

        match ($endpoint) {
            'task/update-status'       => self::updateTaskStatus($data),
            'task/update-dates'        => self::updateTaskDates($data),
            'task/update-position'     => self::updateTaskPosition($data),
            'task/add-dependency'      => self::addDependency($data),
            'task/remove-dependency'   => self::removeDependency($data),
            'task/clear-dependencies'  => self::clearDependencies($data),
            'task/clear-dates'         => self::clearDates($data),
            'notifications/unread'     => self::unreadCount(),
            'notifications/recent'     => self::recentNotifications(),
            'notifications/read'       => self::markRead($data),
            'notifications/read-all'   => self::markAllRead(),
            'notifications/delete'     => self::deleteNotif($data),
            'notifications/delete-all' => self::deleteAllNotifs(),
            'invitations/accept'       => self::acceptInvite($data),
            'invitations/decline'      => self::declineInvite($data),
            'comment/delete'           => self::deleteComment($data),
            'project/available-users'  => self::availableUsersForProject($data),
            'tasks/bulk-update'        => self::bulkUpdateTasks($data),
            'tasks/assignee-search'    => self::tasksAssigneeSearch($data),
            'tasks/toggle-assignee'    => self::tasksToggleAssignee($data),
            'admin/mail-queue/list'    => self::adminMailQueueList(),
            'admin/mail-queue/clear'   => self::adminMailQueueClear($data),
            'search'                   => self::search($data),
            default                    => Helper::json(['error' => 'Not found'], 404),
        };
    }

    private static function updateTaskStatus(array $data): void
    {
        Auth::requireLogin();
        $taskId   = (int)($data['task_id'] ?? 0);
        $statusId = (int)($data['status_id'] ?? 0);

        $db      = Database::getInstance();
        $task    = $db->fetch('SELECT * FROM tasks WHERE id = ?', [$taskId]);
        if (!$task) Helper::json(['error' => 'Not found'], 404);

        if (!Auth::canEditTask($task['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        $newStatus = $db->fetch('SELECT * FROM task_statuses WHERE id = ? AND project_id = ?', [$statusId, $task['project_id']]);
        if (!$newStatus) Helper::json(['error' => 'Invalid status for this project'], 400);

        $oldStatusId = (int)$task['status_id'];
        $db->execute(
            'UPDATE tasks SET status_id=?, updated_at=CURRENT_TIMESTAMP WHERE id=?',
            [$statusId, $taskId]
        );

        Helper::logActivity(Auth::id(), 'status_changed', $task['title'], $task['project_id'], $taskId);

        if ($oldStatusId !== $statusId && !empty($newStatus['is_done'])) {
            $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$task['project_id']]);
            $actor   = Auth::user();
            Mailer::notifyManagerTaskDone($task, $project, $actor);
            TaskController::checkAndNotifySuccessors($taskId, $project, $db);
        }

        Helper::json(['success' => true]);
    }

    private static function updateTaskPosition(array $data): void
    {
        Auth::requireLogin();
        $taskId   = (int)($data['task_id'] ?? 0);
        $statusId = (int)($data['status_id'] ?? 0);
        $position = (int)($data['position'] ?? 0);

        $db   = Database::getInstance();
        $task = $db->fetch('SELECT * FROM tasks WHERE id = ?', [$taskId]);
        if (!$task) Helper::json(['error' => 'Not found'], 404);

        if (!Auth::canEditTask($task['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        $newStatus = $db->fetch('SELECT * FROM task_statuses WHERE id = ? AND project_id = ?', [$statusId, $task['project_id']]);
        if (!$newStatus) Helper::json(['error' => 'Invalid status for this project'], 400);

        $oldStatusId = (int)$task['status_id'];
        $db->execute(
            'UPDATE tasks SET status_id=?, position=?, updated_at=CURRENT_TIMESTAMP WHERE id=?',
            [$statusId, $position, $taskId]
        );

        if ($oldStatusId !== $statusId && !empty($newStatus['is_done'])) {
            $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$task['project_id']]);
            $actor   = Auth::user();
            Mailer::notifyManagerTaskDone($task, $project, $actor);
            TaskController::checkAndNotifySuccessors($taskId, $project, $db);
        }

        Helper::json(['success' => true]);
    }

    private static function updateTaskDates(array $data): void
    {
        Auth::requireLogin();
        $taskId    = (int)($data['task_id'] ?? 0);
        $startDate = $data['start_date'] ?? null;
        $dueDate   = $data['due_date']   ?? null;

        if ($startDate && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) $startDate = null;
        if ($dueDate   && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $dueDate))   $dueDate   = null;

        $db   = Database::getInstance();
        $task = $db->fetch('SELECT * FROM tasks WHERE id = ?', [$taskId]);
        if (!$task) Helper::json(['error' => 'Not found'], 404);
        if (!Auth::canEditTask($task['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        $db->execute(
            'UPDATE tasks SET start_date=?, due_date=?, updated_at=CURRENT_TIMESTAMP WHERE id=?',
            [$startDate ?: null, $dueDate ?: null, $taskId]
        );
        Helper::logActivity(Auth::id(), 'activity_task_updated', $task['title'], $task['project_id'], $taskId);
        Helper::json(['success' => true]);
    }

    private static function unreadCount(): void
    {
        Auth::requireLogin();
        $count = Database::getInstance()->fetch(
            'SELECT COUNT(*) as c FROM notifications WHERE user_id = ? AND is_read = 0',
            [Auth::id()]
        )['c'] ?? 0;
        Helper::json(['count' => $count]);
    }

    private static function markRead(array $data): void
    {
        Auth::requireLogin();
        $id = (int)($data['id'] ?? 0);
        if ($id) {
            Database::getInstance()->execute(
                'UPDATE notifications SET is_read = 1 WHERE id = ? AND user_id = ?',
                [$id, Auth::id()]
            );
        }
        Helper::json(['success' => true]);
    }

    private static function markAllRead(): void
    {
        Auth::requireLogin();
        Database::getInstance()->execute(
            'UPDATE notifications SET is_read = 1 WHERE user_id = ?',
            [Auth::id()]
        );
        Helper::json(['success' => true]);
    }

    private static function deleteNotif(array $data): void
    {
        Auth::requireLogin();
        $id = (int)($data['id'] ?? 0);
        if ($id) {
            Database::getInstance()->execute(
                'DELETE FROM notifications WHERE id = ? AND user_id = ?',
                [$id, Auth::id()]
            );
        }
        Helper::json(['success' => true]);
    }

    private static function deleteAllNotifs(): void
    {
        Auth::requireLogin();
        Database::getInstance()->execute(
            'DELETE FROM notifications WHERE user_id = ?',
            [Auth::id()]
        );
        Helper::json(['success' => true]);
    }

    private static function recentNotifications(): void
    {
        Auth::requireLogin();
        $notifs = Database::getInstance()->fetchAll(
            'SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT 50',
            [Auth::id()]
        );
        Helper::json($notifs);
    }

    private static function acceptInvite(array $data): void
    {
        Auth::requireLogin();
        $db    = Database::getInstance();
        $invId = (int)($data['invitation_id'] ?? 0);
        $user  = Auth::user();

        $inv = $db->fetch(
            'SELECT i.*, g.name AS group_name FROM invitations i
             JOIN groups g ON g.id = i.group_id
             WHERE i.id = ? AND i.used_at IS NULL',
            [$invId]
        );

        if (!$inv || time() > $inv['expires_at']) {
            Helper::json(['error' => t('invite_not_found')], 404);
        }
        if (strtolower($user['email']) !== strtolower($inv['email'])) {
            Helper::json(['error' => t('invite_wrong_account')], 403);
        }

        $db->beginTransaction();
        try {
            $db->execute(
                'INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?,?,?)',
                [$inv['group_id'], $user['id'], $inv['group_role']]
            );
            $db->execute('UPDATE invitations SET used_at = CURRENT_TIMESTAMP WHERE id = ?', [$invId]);
            $db->execute(
                "DELETE FROM notifications WHERE user_id = ? AND related_type = 'invite' AND related_id = ?",
                [$user['id'], $invId]
            );
            $db->commit();
        } catch (\Throwable $e) {
            $db->rollBack();
            Helper::json(['error' => t('invite_join_error')], 500);
        }

        Helper::json(['success' => true, 'group_name' => $inv['group_name']]);
    }

    private static function declineInvite(array $data): void
    {
        Auth::requireLogin();
        $db    = Database::getInstance();
        $invId = (int)($data['invitation_id'] ?? 0);
        $user  = Auth::user();

        $inv = $db->fetch('SELECT * FROM invitations WHERE id = ? AND used_at IS NULL', [$invId]);

        if (!$inv || strtolower($user['email']) !== strtolower($inv['email'])) {
            Helper::json(['error' => t('invite_not_found')], 404);
        }

        $db->execute('DELETE FROM invitations WHERE id = ?', [$invId]);
        $db->execute(
            "DELETE FROM notifications WHERE user_id = ? AND related_type = 'invite' AND related_id = ?",
            [$user['id'], $invId]
        );

        Helper::json(['success' => true]);
    }

    private static function addDependency(array $data): void
    {
        Auth::requireLogin();
        $predId = (int)($data['predecessor_id'] ?? 0);
        $succId = (int)($data['successor_id']   ?? 0);
        if (!$predId || !$succId || $predId === $succId) Helper::json(['error' => 'Invalid'], 400);

        $db   = Database::getInstance();
        $pred = $db->fetch('SELECT project_id FROM tasks WHERE id = ?', [$predId]);
        $succ = $db->fetch('SELECT project_id FROM tasks WHERE id = ?', [$succId]);
        if (!$pred || !$succ) Helper::json(['error' => 'Not found'], 404);
        if ((int)$pred['project_id'] !== (int)$succ['project_id']) {
            Helper::json(['error' => 'Cross-project dependency not allowed'], 403);
        }
        if (!Auth::canEditTask((int)$pred['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        $db->execute(
            'INSERT OR IGNORE INTO task_dependencies (predecessor_id, successor_id) VALUES (?,?)',
            [$predId, $succId]
        );
        Helper::json(['success' => true]);
    }

    private static function removeDependency(array $data): void
    {
        Auth::requireLogin();
        $predId = (int)($data['predecessor_id'] ?? 0);
        $succId = (int)($data['successor_id']   ?? 0);
        if (!$predId || !$succId) Helper::json(['error' => 'Invalid'], 400);

        $db   = Database::getInstance();
        $task = $db->fetch('SELECT project_id FROM tasks WHERE id = ?', [$predId]);
        if (!$task || !Auth::canEditTask($task['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        $db->execute(
            'DELETE FROM task_dependencies WHERE predecessor_id = ? AND successor_id = ?',
            [$predId, $succId]
        );
        Helper::json(['success' => true]);
    }

    private static function clearDependencies(array $data): void
    {
        Auth::requireLogin();
        $projectId = (int)($data['project_id'] ?? 0);
        if (!$projectId) Helper::json(['error' => 'Invalid'], 400);
        if (!Auth::canEditTask($projectId)) Helper::json(['error' => 'Forbidden'], 403);

        $db = Database::getInstance();
        $db->execute(
            'DELETE FROM task_dependencies
             WHERE predecessor_id IN (SELECT id FROM tasks WHERE project_id = ?)
                OR successor_id   IN (SELECT id FROM tasks WHERE project_id = ?)',
            [$projectId, $projectId]
        );
        Helper::json(['success' => true]);
    }

    private static function clearDates(array $data): void
    {
        Auth::requireLogin();
        $projectId = (int)($data['project_id'] ?? 0);
        if (!$projectId) Helper::json(['error' => 'Invalid'], 400);
        if (!Auth::canEditTask($projectId)) Helper::json(['error' => 'Forbidden'], 403);

        Database::getInstance()->execute(
            'UPDATE tasks SET start_date = NULL, due_date = NULL, updated_at = CURRENT_TIMESTAMP
             WHERE project_id = ?',
            [$projectId]
        );
        Helper::json(['success' => true]);
    }

    /**
     * Server-side type-ahead for the "Add project member" form (security-audit #24).
     * Replaces the previous pre-load of the entire active-user directory into the
     * page. Caller must hold owner/manager on the project. Plugin-supplied
     * visibility filters (e.g. Provider Edition group isolation) are honoured and
     * the SQL fragment is validated against the same allowlist as the host query.
     */
    private static function availableUsersForProject(array $data): void
    {
        Auth::requireLogin();
        $projectId = (int)($data['project_id'] ?? 0);
        $q         = trim((string)($data['q'] ?? ''));

        if (!$projectId || !Auth::canEditProject($projectId)) {
            Helper::json(['error' => 'Forbidden'], 403);
        }
        if (mb_strlen($q) < 2) {
            Helper::json(['users' => []]);
        }

        $db = Database::getInstance();

        $userFilter = PluginManager::filter(
            'available_users_filter',
            ['extra_where' => '', 'extra_params' => []],
            ['is_admin' => Auth::isAdmin()]
        );
        $extraWhere = (string)($userFilter['extra_where'] ?? '');
        if ($extraWhere !== '' && !ProjectController::isSafeWhereFragment($extraWhere)) {
            error_log('[Plenvo] PluginManager filter "available_users_filter" returned an unsafe SQL fragment, ignoring: ' . $extraWhere);
            $extraWhere = '';
            $userFilter['extra_params'] = [];
        }
        $groupParams = $userFilter['extra_params'] ?? [];

        $memberIds      = array_column(
            $db->fetchAll('SELECT user_id FROM project_members WHERE project_id = ?', [$projectId]),
            'user_id'
        );
        $excludeClause  = '';
        $excludeParams  = [];
        if (!empty($memberIds)) {
            $placeholders  = implode(',', array_fill(0, count($memberIds), '?'));
            $excludeClause = " AND id NOT IN ($placeholders)";
            $excludeParams = $memberIds;
        }

        $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';

        $rows = $db->fetchAll(
            "SELECT id, username, email FROM users
             WHERE is_active = 1{$extraWhere}{$excludeClause}
               AND (username LIKE ? ESCAPE '\\' OR email LIKE ? ESCAPE '\\')
             ORDER BY username
             LIMIT 10",
            array_merge($groupParams, $excludeParams, [$like, $like])
        );

        Helper::json(['users' => $rows]);
    }

    /**
     * Bulk-update tasks within a single project. The list view selects checkboxes
     * and posts an action plus a value; we apply it inside one transaction with a
     * per-task `Auth::canEditTask` gate. Notifications are intentionally NOT fired
     * here in v1 — bulk operations are deliberate manager actions and the email
     * spam from N notifications outweighs the marginal value of "you were
     * reassigned" pings. Activity log entries are still written per task so the
     * project history reflects what happened.
     */
    private static function bulkUpdateTasks(array $data): void
    {
        Auth::requireLogin();
        $projectId = (int)($data['project_id'] ?? 0);
        $taskIds   = array_values(array_filter(array_map('intval', (array)($data['task_ids'] ?? []))));
        $action    = (string)($data['action'] ?? '');
        $value     = $data['value'] ?? null;

        if (!$projectId || empty($taskIds))         Helper::json(['error' => 'Invalid'], 400);
        if (!Auth::canEditTask($projectId))         Helper::json(['error' => 'Forbidden'], 403);

        $db = Database::getInstance();

        // Restrict to tasks that actually belong to the claimed project. Cross-project
        // ids are silently dropped; we never trust the client list as-is.
        $placeholders = implode(',', array_fill(0, count($taskIds), '?'));
        $rows = $db->fetchAll(
            "SELECT id, title FROM tasks WHERE project_id = ? AND id IN ($placeholders)",
            array_merge([$projectId], $taskIds)
        );
        if (empty($rows)) Helper::json(['error' => 'Not found'], 404);
        $allowedIds = array_column($rows, 'id');
        $titles     = array_column($rows, 'title', 'id');
        $ph         = implode(',', array_fill(0, count($allowedIds), '?'));

        $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$projectId]);

        $db->beginTransaction();
        try {
            switch ($action) {
                case 'status':
                    $statusId = (int)$value;
                    $status = $db->fetch('SELECT id FROM task_statuses WHERE id = ? AND project_id = ?', [$statusId, $projectId]);
                    if (!$status) throw new \RuntimeException('Invalid status for project');
                    $db->execute("UPDATE tasks SET status_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id IN ($ph)",
                        array_merge([$statusId], $allowedIds));
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'status_changed', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'priority':
                    $priority = (string)$value;
                    if (!in_array($priority, ['low','medium','high','urgent'], true)) throw new \RuntimeException('Invalid priority');
                    $db->execute("UPDATE tasks SET priority = ?, updated_at = CURRENT_TIMESTAMP WHERE id IN ($ph)",
                        array_merge([$priority], $allowedIds));
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'activity_task_updated', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'due_date':
                    $due = $value === null || $value === '' ? null : (string)$value;
                    if ($due !== null && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $due)) throw new \RuntimeException('Invalid date');
                    $db->execute("UPDATE tasks SET due_date = ?, updated_at = CURRENT_TIMESTAMP WHERE id IN ($ph)",
                        array_merge([$due], $allowedIds));
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'dates_updated', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'assign':
                    // value: ['users' => [int...], 'externals' => [int...]] — replaces the current set
                    $userIds = array_values(array_filter(array_map('intval', (array)(($value['users'] ?? [])))));
                    $extIds  = array_values(array_filter(array_map('intval', (array)(($value['externals'] ?? [])))));
                    // Wipe + reinsert. Cheaper than diff for the typical bulk case.
                    $db->execute("DELETE FROM task_assignees WHERE task_id IN ($ph)", $allowedIds);
                    if (!empty($userIds) || !empty($extIds)) {
                        $ins = $db->getPdo()->prepare('INSERT INTO task_assignees (task_id, user_id, external_id) VALUES (?, ?, ?)');
                        foreach ($allowedIds as $tid) {
                            foreach ($userIds as $uid) $ins->execute([$tid, $uid, null]);
                            foreach ($extIds  as $eid) $ins->execute([$tid, null, $eid]);
                        }
                    }
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'assignees_updated', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'label_add':
                    $labelId = (int)$value;
                    $label = $db->fetch('SELECT id FROM labels WHERE id = ? AND project_id = ?', [$labelId, $projectId]);
                    if (!$label) throw new \RuntimeException('Invalid label for project');
                    $ins = $db->getPdo()->prepare('INSERT OR IGNORE INTO task_labels (task_id, label_id) VALUES (?, ?)');
                    foreach ($allowedIds as $tid) $ins->execute([$tid, $labelId]);
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'activity_task_updated', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'label_remove':
                    $labelId = (int)$value;
                    $db->execute("DELETE FROM task_labels WHERE label_id = ? AND task_id IN ($ph)",
                        array_merge([$labelId], $allowedIds));
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'activity_task_updated', (string)$titles[$tid], $projectId, (int)$tid);
                    break;

                case 'delete':
                    foreach ($allowedIds as $tid) Helper::logActivity(Auth::id(), 'task_deleted', (string)$titles[$tid], $projectId);
                    $db->execute("DELETE FROM tasks WHERE id IN ($ph)", $allowedIds);
                    break;

                default:
                    throw new \RuntimeException('Unknown action');
            }
            $db->commit();
        } catch (\Throwable $e) {
            $db->rollBack();
            Helper::json(['error' => $e->getMessage()], 400);
        }

        Helper::json(['updated' => count($allowedIds), 'skipped' => count($taskIds) - count($allowedIds)]);
    }

    /**
     * Cross-project Cmd+K search. Returns up to 5 projects and 8 tasks the user
     * has access to. Permission filter: project_members JOIN ensures the result
     * is naturally scoped to projects where the user is a member or admin.
     */
    private static function search(array $data): void
    {
        Auth::requireLogin();
        $q = trim((string)($data['q'] ?? ''));
        if (mb_strlen($q) < 2) Helper::json(['results' => []]);

        $db   = Database::getInstance();
        $uid  = Auth::id();
        $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';

        $isAdmin = Auth::isAdmin();

        if ($isAdmin) {
            $projects = $db->fetchAll(
                "SELECT id, name, color FROM projects WHERE status != 'archived' AND name LIKE ? ESCAPE '\\' ORDER BY name LIMIT 5",
                [$like]
            );
            $tasks = $db->fetchAll(
                "SELECT t.id, t.title, p.id AS project_id, p.name AS project_name, p.color AS project_color
                 FROM tasks t
                 JOIN projects p ON p.id = t.project_id
                 WHERE p.status != 'archived' AND t.title LIKE ? ESCAPE '\\'
                 ORDER BY t.updated_at DESC LIMIT 8",
                [$like]
            );
        } else {
            $projects = $db->fetchAll(
                "SELECT p.id, p.name, p.color FROM projects p
                 JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = ?
                 WHERE p.status != 'archived' AND p.name LIKE ? ESCAPE '\\'
                 ORDER BY p.name LIMIT 5",
                [$uid, $like]
            );
            $tasks = $db->fetchAll(
                "SELECT t.id, t.title, p.id AS project_id, p.name AS project_name, p.color AS project_color
                 FROM tasks t
                 JOIN projects p ON p.id = t.project_id
                 JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = ?
                 WHERE p.status != 'archived' AND t.title LIKE ? ESCAPE '\\'
                 ORDER BY t.updated_at DESC LIMIT 8",
                [$uid, $like]
            );
        }

        // ── People sub-search (users + externals) with per-person counter
        // of "open tasks assigned to them in projects where I am owner/manager".
        // Counter = 0 entries are kept (consistency with members picker).
        // is_done filter via task_statuses; archived projects are excluded.
        $people = self::searchPeople($q, $like, $uid, $isAdmin, $db);

        Helper::json([
            'results' => [
                'projects' => $projects,
                'tasks'    => $tasks,
                'people'   => $people,
            ],
        ]);
    }

    private static function searchPeople(string $q, string $like, int $uid, bool $isAdmin, Database $db): array
    {
        // Counter mirrors the /my-tasks?assigned_to=…&status=open query so the
        // number on the cmd-k card equals what the user sees after clicking it.
        // Permission floor: project_members-membership OR direct assignment to
        // the searcher. Excludes archived projects and done statuses.
        $counterJoin = "FROM task_assignees ta
                        JOIN tasks         t  ON t.id = ta.task_id
                        JOIN projects      p  ON p.id = t.project_id
                        LEFT JOIN task_statuses ts ON ts.id = t.status_id
                        WHERE p.status != 'archived' AND COALESCE(ts.is_done, 0) = 0
                          AND (
                            EXISTS (SELECT 1 FROM project_members pm WHERE pm.project_id = t.project_id AND pm.user_id = ?)
                            OR EXISTS (SELECT 1 FROM task_assignees ta_me WHERE ta_me.task_id = t.id AND ta_me.user_id = ?)
                          )";

        // ── Users — apply the same plugin filter the members picker uses
        // (Provider Edition isolates by group; vanilla returns everyone).
        $userFilter = PluginManager::filter(
            'available_users_filter',
            ['extra_where' => '', 'extra_params' => []],
            ['is_admin' => $isAdmin]
        );
        $extraWhere = (string)($userFilter['extra_where'] ?? '');
        if ($extraWhere !== '' && !ProjectController::isSafeWhereFragment($extraWhere)) {
            error_log('[Plenvo] PluginManager filter "available_users_filter" returned an unsafe SQL fragment, ignoring: ' . $extraWhere);
            $extraWhere = '';
            $userFilter['extra_params'] = [];
        }
        $groupParams = $userFilter['extra_params'] ?? [];

        // The counterJoin contains two `?` placeholders for the searcher's id
        // (project_members membership AND direct-assignee permission floor).
        $counterParams = [$uid, $uid];

        $userSql = "SELECT u.id,
                           u.username,
                           u.email,
                           u.avatar,
                           (SELECT COUNT(*) $counterJoin AND ta.user_id = u.id) AS open_count
                    FROM users u
                    WHERE u.is_active = 1{$extraWhere}
                      AND (u.username LIKE ? ESCAPE '\\' OR u.email LIKE ? ESCAPE '\\')
                    ORDER BY u.username
                    LIMIT 6";
        $users = $db->fetchAll($userSql, array_merge($counterParams, $groupParams, [$like, $like]));

        // ── Externals — only those visible to the searching user (member of
        // the home project) AND home project not archived, mirroring the
        // /my-tasks assigned-to filter. The counter uses the same permission
        // floor as the user counter.
        $extSql = "SELECT e.id,
                          e.name,
                          e.email,
                          e.project_id,
                          (SELECT COUNT(*) $counterJoin AND ta.external_id = e.id) AS open_count
                   FROM project_externals e
                   JOIN projects p_e ON p_e.id = e.project_id
                   JOIN project_members pm_v ON pm_v.project_id = e.project_id AND pm_v.user_id = ?
                   WHERE p_e.status != 'archived'
                     AND (e.name LIKE ? ESCAPE '\\' OR COALESCE(e.email, '') LIKE ? ESCAPE '\\')
                   GROUP BY e.id
                   ORDER BY e.name
                   LIMIT 6";
        $externals = $db->fetchAll($extSql, array_merge($counterParams, [$uid, $like, $like]));

        // Merge into one "people" list, keep type info for the renderer.
        $people = [];
        foreach ($users as $u) {
            $people[] = [
                'kind'        => 'user',
                'id'          => (int)$u['id'],
                'name'        => (string)$u['username'],
                'subtitle'    => (string)($u['email'] ?? ''),
                'avatar'      => $u['avatar'] ?? null,
                'open_count'  => (int)$u['open_count'],
            ];
        }
        foreach ($externals as $e) {
            $people[] = [
                'kind'        => 'external',
                'id'          => (int)$e['id'],
                'name'        => (string)$e['name'],
                'subtitle'    => (string)($e['email'] ?? ''),
                'avatar'      => null,
                'open_count'  => (int)$e['open_count'],
            ];
        }
        return $people;
    }

    /**
     * Type-ahead for the "Assigned to" filter on /my-tasks. Returns up to
     * 6 users + 6 externals reachable by the searcher:
     *   - users: members of any project the searcher is also a member of,
     *     after the available_users_filter plugin hook (Provider Edition
     *     group isolation applies).
     *   - externals: only those whose home project is NOT archived.
     * Min query length 2 to keep dropdown responsive; an empty / short query
     * returns no rows so the client renders the static "Me / Anyone /
     * Unassigned" defaults.
     */
    private static function tasksAssigneeSearch(array $data): void
    {
        Auth::requireLogin();
        $q = trim((string)($data['q'] ?? ''));

        $db   = Database::getInstance();
        $uid  = Auth::id();

        // Optional project scope: when provided, narrow users and externals
        // to that single project so the inline assignee picker on the
        // project list view does not surface members the caller shares
        // another project with but who are not on the current task's
        // project — which would only result in a silent 403 on add. Caller
        // must have view access; admins can use any project.
        $scopedProjectId = isset($data['project_id']) ? (int)$data['project_id'] : 0;
        if ($scopedProjectId > 0 && !Auth::canViewProject($scopedProjectId)) {
            $scopedProjectId = 0;
        }

        // Empty query is only meaningful with a project scope, where it
        // returns the full member roster of that project so the inline
        // picker can show every member on first open without forcing the
        // user to type. Without scope an empty query would dump every
        // user matching the filters and is rejected.
        if ($q === '' && $scopedProjectId === 0) {
            Helper::json(['users' => [], 'externals' => []]);
        }
        if ($q !== '' && mb_strlen($q) < 2) {
            Helper::json(['users' => [], 'externals' => []]);
        }
        $like = $q === ''
            ? '%'
            : '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';

        // Project-scoped fast path — mirrors the query that TaskController::edit
        // uses to populate the form's assignee picker, so the inline list-view
        // picker shows the exact same set of members and externals. No
        // Provider Edition filter (membership is the access boundary), no
        // membership-overlap join, no is_active check (parity with the form).
        if ($scopedProjectId > 0) {
            $users = $db->fetchAll(
                "SELECT u.id, u.username, u.email, u.avatar
                   FROM project_members pm
                   JOIN users u ON u.id = pm.user_id
                  WHERE pm.project_id = ?
                    AND (u.username LIKE ? ESCAPE '\\' OR u.email LIKE ? ESCAPE '\\')
                  ORDER BY u.username
                  LIMIT 50",
                [$scopedProjectId, $like, $like]
            );
            $externals = $db->fetchAll(
                "SELECT e.id, e.name, e.email, p.name AS project_name
                   FROM project_externals e
                   JOIN projects p ON p.id = e.project_id
                  WHERE e.project_id = ?
                    AND (e.name LIKE ? ESCAPE '\\' OR COALESCE(e.email, '') LIKE ? ESCAPE '\\')
                  ORDER BY e.name
                  LIMIT 50",
                [$scopedProjectId, $like, $like]
            );
            Helper::json(['users' => $users, 'externals' => $externals]);
        }

        // Users dropdown: same plugin-filtered visibility as the project
        // members picker, narrowed to users that share at least one project
        // with the current user.
        $userFilter = PluginManager::filter(
            'available_users_filter',
            ['extra_where' => '', 'extra_params' => []],
            ['is_admin' => Auth::isAdmin()]
        );
        $extraWhere = (string)($userFilter['extra_where'] ?? '');
        if ($extraWhere !== '' && !ProjectController::isSafeWhereFragment($extraWhere)) {
            error_log('[Plenvo] PluginManager filter "available_users_filter" returned an unsafe SQL fragment, ignoring: ' . $extraWhere);
            $extraWhere = '';
            $userFilter['extra_params'] = [];
        }
        $groupParams = $userFilter['extra_params'] ?? [];

        // Admins typically have no project_members rows but see every user
        // by virtue of their global role — bypass the membership-overlap
        // requirement for them. The plugin filter (Provider Edition) still
        // applies even for admins, because tenant isolation is orthogonal to
        // the project-membership scope.
        $isAdmin = Auth::isAdmin();

        // Unscoped path (no project_id): kept compatible with /my-tasks and
        // any other caller that omits project_id. Returns 6 best matches for
        // a typeahead, requires the caller to share a project with the
        // candidate, and applies the Provider Edition tenant filter.
        if ($isAdmin) {
            $userSql = "SELECT u.id, u.username, u.email
                        FROM users u
                        WHERE u.is_active = 1{$extraWhere}
                          AND (u.username LIKE ? ESCAPE '\\' OR u.email LIKE ? ESCAPE '\\')
                        ORDER BY u.username
                        LIMIT 6";
            $users = $db->fetchAll($userSql, array_merge($groupParams, [$like, $like]));
        } else {
            $userSql = "SELECT u.id, u.username, u.email
                        FROM users u
                        WHERE u.is_active = 1{$extraWhere}
                          AND EXISTS (
                            SELECT 1 FROM project_members pm_t
                            JOIN project_members pm_me ON pm_me.project_id = pm_t.project_id AND pm_me.user_id = ?
                            WHERE pm_t.user_id = u.id
                          )
                          AND (u.username LIKE ? ESCAPE '\\' OR u.email LIKE ? ESCAPE '\\')
                        ORDER BY u.username
                        LIMIT 6";
            $users = $db->fetchAll($userSql, array_merge($groupParams, [$uid, $like, $like]));
        }

        if ($isAdmin) {
            $extSql = "SELECT e.id, e.name, e.email, p.name AS project_name
                       FROM project_externals e
                       JOIN projects p ON p.id = e.project_id
                       WHERE p.status != 'archived'
                         AND (e.name LIKE ? ESCAPE '\\' OR COALESCE(e.email, '') LIKE ? ESCAPE '\\')
                       ORDER BY e.name
                       LIMIT 6";
            $externals = $db->fetchAll($extSql, [$like, $like]);
        } else {
            $extSql = "SELECT e.id, e.name, e.email, p.name AS project_name
                       FROM project_externals e
                       JOIN projects p ON p.id = e.project_id
                       JOIN project_members pm ON pm.project_id = e.project_id AND pm.user_id = ?
                       WHERE p.status != 'archived'
                         AND (e.name LIKE ? ESCAPE '\\' OR COALESCE(e.email, '') LIKE ? ESCAPE '\\')
                       ORDER BY e.name
                       LIMIT 6";
            $externals = $db->fetchAll($extSql, [$uid, $like, $like]);
        }

        Helper::json(['users' => $users, 'externals' => $externals]);
    }

    /**
     * Add or remove a single assignee on a task. Used by the inline assignee
     * picker on the project list view; the full `update_assignees` form on
     * `/task/{id}/edit` still exists for the task detail page.
     *
     * Body: { task_id, action: "add"|"remove", user_id?, external_id? }
     * Returns the refreshed assignee list so the caller can re-render the cell.
     *
     * Adds are validated against `project_members` / `project_externals` so a
     * project member cannot smuggle a foreign user onto the task; the
     * `Auth::canEditTask` gate guards both branches. Removes are unconditional
     * for any caller that can edit the task.
     */
    private static function tasksToggleAssignee(array $data): void
    {
        Auth::requireLogin();
        $taskId   = (int)($data['task_id']     ?? 0);
        $userId   = (int)($data['user_id']     ?? 0);
        $extId    = (int)($data['external_id'] ?? 0);
        $action   = (string)($data['action']   ?? '');

        if (!$taskId || !in_array($action, ['add', 'remove'], true)) {
            Helper::json(['error' => 'Invalid'], 400);
        }
        if ($userId === 0 && $extId === 0) {
            Helper::json(['error' => 'Invalid'], 400);
        }

        $db   = Database::getInstance();
        $task = $db->fetch('SELECT id, title, project_id FROM tasks WHERE id = ?', [$taskId]);
        if (!$task) Helper::json(['error' => 'Not found'], 404);
        if (!Auth::canEditTask((int)$task['project_id'])) Helper::json(['error' => 'Forbidden'], 403);

        if ($action === 'add') {
            if ($userId > 0) {
                $member = $db->fetch(
                    'SELECT 1 FROM project_members WHERE project_id = ? AND user_id = ?',
                    [(int)$task['project_id'], $userId]
                );
                if (!$member) Helper::json(['error' => 'Not a project member'], 403);
                $exists = $db->fetch(
                    'SELECT 1 FROM task_assignees WHERE task_id = ? AND user_id = ?',
                    [$taskId, $userId]
                );
                if (!$exists) {
                    $db->execute(
                        'INSERT INTO task_assignees (task_id, user_id) VALUES (?, ?)',
                        [$taskId, $userId]
                    );
                    Helper::logActivity(Auth::id(), 'assignees_updated', $task['title'], (int)$task['project_id'], $taskId);
                    $project  = $db->fetch('SELECT * FROM projects WHERE id = ?', [(int)$task['project_id']]);
                    $assignee = $db->fetch('SELECT id, username, email FROM users WHERE id = ? AND is_active = 1', [$userId]);
                    if ($project && $assignee && $userId !== Auth::id()) {
                        Mailer::notifyAssignment($task, $project, $assignee);
                    }
                }
            } else {
                $valid = $db->fetch(
                    'SELECT 1 FROM project_externals WHERE id = ? AND project_id = ?',
                    [$extId, (int)$task['project_id']]
                );
                if (!$valid) Helper::json(['error' => 'Not a project external'], 403);
                $exists = $db->fetch(
                    'SELECT 1 FROM task_assignees WHERE task_id = ? AND external_id = ?',
                    [$taskId, $extId]
                );
                if (!$exists) {
                    $db->execute(
                        'INSERT INTO task_assignees (task_id, external_id) VALUES (?, ?)',
                        [$taskId, $extId]
                    );
                    Helper::logActivity(Auth::id(), 'assignees_updated', $task['title'], (int)$task['project_id'], $taskId);
                    $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [(int)$task['project_id']]);
                    $ext     = $db->fetch('SELECT * FROM project_externals WHERE id = ?', [$extId]);
                    if ($project && $ext) Mailer::notifyExternalAssignment($task, $project, $ext);
                }
            }
        } else {
            if ($userId > 0) {
                $db->execute(
                    'DELETE FROM task_assignees WHERE task_id = ? AND user_id = ?',
                    [$taskId, $userId]
                );
            } else {
                $db->execute(
                    'DELETE FROM task_assignees WHERE task_id = ? AND external_id = ?',
                    [$taskId, $extId]
                );
            }
            Helper::logActivity(Auth::id(), 'assignees_updated', $task['title'], (int)$task['project_id'], $taskId);
        }

        $rows = $db->fetchAll(
            'SELECT ta.user_id, ta.external_id, ta.external_name,
                    u.username, u.avatar,
                    e.name AS ext_name
               FROM task_assignees ta
               LEFT JOIN users u             ON u.id = ta.user_id
               LEFT JOIN project_externals e ON e.id = ta.external_id
              WHERE ta.task_id = ?
              ORDER BY u.username, e.name, ta.external_name',
            [$taskId]
        );
        $assignees = array_map(fn($r) => [
            'user_id'       => $r['user_id']     !== null ? (int)$r['user_id']     : null,
            'external_id'   => $r['external_id'] !== null ? (int)$r['external_id'] : null,
            'username'      => $r['username'] ?? null,
            'avatar'        => $r['avatar']   ?? null,
            'external_name' => $r['ext_name'] ?? $r['external_name'],
        ], $rows);

        Helper::json(['ok' => true, 'assignees' => $assignees]);
    }

    /** Admin-only: snapshot of the mail queue for the System-Settings popup. */
    private static function adminMailQueueList(): void
    {
        Auth::requireAdmin();
        Helper::json([
            'stats' => Mailer::queueStats(),
            'rows'  => Mailer::queueList(200),
        ]);
    }

    /**
     * Admin-only: delete rows from the mail queue.
     * Body: { mode: "all" | "pending" | "stuck" | "sent" }
     * Logs an audit-trail event with a recipient-fingerprint summary so
     * a clearance can later be reconstructed without retaining the
     * encrypted bodies. We capture the row ids and the masked recipient
     * addresses *before* the DELETE so the SecurityLog entry stays
     * meaningful even after the rows are gone.
     */
    private static function adminMailQueueClear(array $data): void
    {
        Auth::requireAdmin();
        $mode = (string)($data['mode'] ?? 'all');
        if (!in_array($mode, ['all', 'pending', 'stuck', 'sent'], true)) {
            Helper::json(['error' => 'Invalid mode'], 400);
        }

        $db = Database::getInstance();
        $about = match ($mode) {
            'pending' => 'sent_at IS NULL AND attempts <  5',
            'stuck'   => 'sent_at IS NULL AND attempts >= 5',
            'sent'    => 'sent_at IS NOT NULL',
            default   => '1=1',
        };
        $preview = $db->fetchAll('SELECT id, to_addrs, subject FROM mail_queue WHERE ' . $about . ' ORDER BY id LIMIT 50');
        $masked  = array_map(function ($r) {
            $to = json_decode((string)$r['to_addrs'], true);
            $first = is_array($to) ? ($to[0] ?? '') : (string)$to;
            return ['id' => (int)$r['id'], 'to' => Helper::maskEmail((string)$first)];
        }, $preview);

        $removed = Mailer::clearQueue($mode);
        SecurityLog::log('mail_queue_cleared', SecurityLog::SEV_WARN, [
            'mode'           => $mode,
            'removed'        => $removed,
            'sample_dropped' => $masked, // up to 50 ids + masked recipients
        ]);
        Helper::json(['ok' => true, 'removed' => $removed, 'stats' => Mailer::queueStats()]);
    }

    private static function deleteComment(array $data): void
    {
        Auth::requireLogin();
        $commentId = (int)($data['comment_id'] ?? 0);
        $db        = Database::getInstance();
        $comment   = $db->fetch('SELECT * FROM comments WHERE id = ?', [$commentId]);
        if (!$comment) Helper::json(['error' => 'Not found'], 404);

        if ($comment['user_id'] !== Auth::id()) {
            $task = $db->fetch('SELECT * FROM tasks WHERE id = ?', [$comment['task_id']]);
            if (!$task || !Auth::canEditProject($task['project_id'])) {
                Helper::json(['error' => 'Forbidden'], 403);
            }
        }

        $db->execute('DELETE FROM comments WHERE id = ?', [$commentId]);
        Helper::json(['success' => true]);
    }
}
