<?php

namespace FluentSupport\App\Modules\MCP\Tools;

use FluentSupport\App\Models\Agent;
use FluentSupport\App\Models\MailBox;
use FluentSupport\App\Models\Product;
use FluentSupport\App\Models\Ticket;
use FluentSupport\App\Models\TicketTag;
use FluentSupport\App\Modules\MCP\Helpers\MCPHelper;
use FluentSupport\App\Modules\MCP\Support\PermissionGate;
use FluentSupport\App\Modules\MCP\Support\TicketAccessGuard;
use FluentSupport\App\Modules\PermissionManager;
use FluentSupport\App\Services\Helper;
use FluentSupport\App\Services\TicketHelper;
use FluentSupport\App\Services\Tickets\AgentTicketAccess;
use FluentSupport\App\Services\Tickets\TicketService;

class ManagementTools
{
    const CACHE_PREFIX  = 'fsmcp_ctx_';
    const CACHE_TTL     = 60;

    /** Bumped to invalidate every agent's cached context; it is part of the cache key. */
    const CACHE_VERSION_OPTION = '_fluent_support_mcp_ctx_version';
    const MAX_AGENTS    = 50;
    const MAX_BULK      = 50;
    const MAX_TAG_NAMES = 20;

    public static function getSupportContext($params)
    {
        $currentAgent = MCPHelper::resolveAgent();
        if (!$currentAgent) {
            return MCPHelper::error('unauthorized', __('No agent found for current user', 'fluent-support'));
        }

        $cacheKey = self::CACHE_PREFIX . $currentAgent->id . '_' . (int) get_option(self::CACHE_VERSION_OPTION, 0);
        $cached   = get_transient($cacheKey);
        if ($cached !== false) {
            return $cached;
        }

        $result = self::buildContext($currentAgent);
        set_transient($cacheKey, $result, self::CACHE_TTL);

        return $result;
    }

    private static function buildContext($currentAgent)
    {
        $canSeeSensitive = PermissionManager::userCan('fst_sensitive_data');
        $access          = new AgentTicketAccess();

        $agents = Agent::select(['id', 'first_name', 'last_name', 'email', 'status'])
            ->where('person_type', 'agent')
            ->where('first_name', '!=', '')
            ->whereNotNull('first_name')
            ->orderBy('first_name', 'ASC')
            ->get()
            ->map(function ($agent) use ($canSeeSensitive) {
                $entry = [
                    'id'     => $agent->id,
                    'name'   => MCPHelper::personName($agent),
                    'status' => $agent->status,
                ];
                if ($canSeeSensitive) {
                    $entry['email'] = $agent->email;
                }
                return $entry;
            })->toArray();

        $products = Product::select(['id', 'title', 'description'])
            ->orderBy('title', 'ASC')
            ->get()
            ->map(function ($p) {
                return ['id' => $p->id, 'title' => $p->title, 'description' => $p->description ?: ''];
            })->toArray();

        $signature = \FluentSupport\App\Services\Tickets\ResponseService::getAppendedSignature($currentAgent);

        $mailboxes = MailBox::getAccessibleBoxes($canSeeSensitive)
            ->map(function ($mb) use ($canSeeSensitive, $currentAgent, $signature) {
                $entry = ['id' => $mb->id, 'name' => $mb->name];
                if ($canSeeSensitive) {
                    $entry['email'] = $mb->email;
                }
                // What the customer receives around the agent's text, so replies
                // don't repeat the template's greeting, sign-off or signature.
                $entry['emails'] = [
                    'reply'    => self::describeAgentEmail($mb, 'ticket_replied_by_agent_email_to_customer', $currentAgent, $signature, true),
                    'outreach' => self::describeAgentEmail($mb, 'ticket_created_by_agent_on_behalf_email_to_customer', $currentAgent, $signature, true),
                ];
                return $entry;
            })->toArray();

        $tags = TicketTag::select(['id', 'title'])
            ->orderBy('title', 'ASC')
            ->get()
            ->toArray();

        $statusCounts = $access->applyMailboxRestrictionScope(Ticket::selectRaw('status, COUNT(*) as cnt'))
            ->groupBy('status')
            ->get()
            ->pluck('cnt', 'status');

        $stats = [
            'total'      => (int) $statusCounts->sum(),
            'new'        => (int) ($statusCounts['new'] ?? 0),
            'active'     => (int) ($statusCounts['active'] ?? 0),
            'closed'     => (int) ($statusCounts['closed'] ?? 0),
            'unassigned' => $access->applyMailboxRestrictionScope(Ticket::whereNull('agent_id'))
                ->whereNotIn('status', ['closed'])
                ->count(),
        ];

        $ticketFields = ['id', 'title', 'status', 'priority', 'customer_id', 'agent_id', 'waiting_since', 'created_at', 'response_count', 'last_agent_response', 'last_customer_response'];
        $eagerLoad    = [
            'customer' => function ($q) { $q->select(['id', 'first_name', 'last_name', 'email']); },
        ];

        $slaSettings        = PermissionGate::getSlaSettings();
        $firstResponseHours = $slaSettings['first_response_hours'];
        $resolutionHours    = $slaSettings['resolution_hours'];

        $now                 = strtotime(current_time('mysql'));
        $firstResponseCutoff = date('Y-m-d H:i:s', $now - ($firstResponseHours * 3600));
        $resolutionCutoff    = date('Y-m-d H:i:s', $now - ($resolutionHours * 3600));

        $myQueueBase = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->where('agent_id', $currentAgent->id)
            ->whereNotIn('status', ['closed']);

        // Split by whose turn it is instead of ranking both populations by raw waiting_since:
        // an agent-last ticket idle for 12 days and a customer-last ticket waiting 12 days both
        // have an old waiting_since, but only the latter needs a reply. Mixing them let stale
        // agent-last tickets crowd the actionable ones out of the capped list.
        $awaitingYourReplyQuery = (clone $myQueueBase)->waitingOnly();
        $awaitingYourReplyTotal = (clone $awaitingYourReplyQuery)->count();
        $awaitingYourReply      = $awaitingYourReplyQuery
            ->orderBy('waiting_since', 'ASC')
            ->limit(10)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        $awaitingCustomerQuery = (clone $myQueueBase)->where(function ($q) {
            $q->whereColumn('last_customer_response', '<', 'last_agent_response');
        });
        $awaitingCustomerTotal = (clone $awaitingCustomerQuery)->count();
        $awaitingCustomer      = $awaitingCustomerQuery
            ->orderBy('waiting_since', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        $myQueue = [
            'awaiting_your_reply'           => $awaitingYourReply,
            'awaiting_your_reply_total'     => $awaitingYourReplyTotal,
            'awaiting_your_reply_truncated' => $awaitingYourReplyTotal > count($awaitingYourReply),
            'awaiting_customer'             => $awaitingCustomer,
            'awaiting_customer_total'       => $awaitingCustomerTotal,
            'awaiting_customer_truncated'   => $awaitingCustomerTotal > count($awaitingCustomer),
        ];

        $unassigned = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->whereNull('agent_id')
            ->whereNotIn('status', ['closed'])
            ->orderBy('created_at', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        // Only tickets waiting on an agent: a ticket we answered months ago has the
        // oldest waiting_since but needs nothing from us.
        $longestWaiting = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->whereNotIn('status', ['closed'])
            ->whereNotNull('waiting_since')
            ->waitingOnly()
            ->orderBy('waiting_since', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        $critical = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->where('priority', 'critical')
            ->whereNotIn('status', ['closed'])
            ->orderBy('created_at', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        $slaFirstResponse = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->whereNotIn('status', ['closed'])
            ->where('response_count', 0)
            ->where('created_at', '<=', $firstResponseCutoff)
            ->orderBy('created_at', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        // Past the resolution target and still waiting on an agent. Tickets waiting on the
        // customer are excluded: nothing we can do moves them.
        $slaResolution = static::scopedTicketQuery($ticketFields, $eagerLoad)
            ->whereNotIn('status', ['closed'])
            ->where('created_at', '<=', $resolutionCutoff)
            ->waitingOnly()
            ->orderBy('created_at', 'ASC')
            ->limit(5)
            ->get()
            ->map(function ($t) use ($now) { return self::formatContextTicket($t, $now); })
            ->toArray();

        $guidelines = apply_filters('fluent_support/mcp_ai_guidelines', PermissionGate::getAiGuidelines());
        if (!$guidelines) {
            // Tone and conduct only: the triage order is in the server instructions.
            $guidelines = 'Be professional and empathetic. Address customers by name. '
                . 'Read the whole conversation before replying. '
                . 'Use internal notes to record decisions and escalations.';
        }

        $openCount = $stats['new'] + $stats['active'];

        return MCPHelper::envelope(
            "Support context: {$openCount} open ticket(s), {$stats['unassigned']} unassigned",
            [
                'you'      => array_merge([
                    'agent_id'  => $currentAgent->id,
                    'name'      => MCPHelper::personName($currentAgent),
                    'email'     => $currentAgent->email,
                ], self::replyMode(), [
                    // Added only where an inbox email template uses {{agent.signature}};
                    // each mailbox's emails say whether theirs does.
                    'signature' => $signature !== '' ? [
                        'enabled' => true,
                        'text'    => mb_substr(MCPHelper::htmlToMarkdown($signature), 0, self::SIGNATURE_MAX_CHARS),
                    ] : [
                        'enabled' => false,
                        'note'    => 'You have no signature set.',
                    ],
                ]),
                'my_queue' => $myQueue,
                'needs_attention' => [
                    'unassigned'      => $unassigned,
                    'longest_waiting' => $longestWaiting,
                    'critical'        => $critical,
                    'sla_breach'      => [
                        'no_first_response'  => $slaFirstResponse,
                        'overdue_resolution' => $slaResolution,
                        'thresholds'         => [
                            'first_response' => $firstResponseHours . 'h',
                            'resolution'     => $resolutionHours . 'h',
                        ],
                    ],
                ],
                'agents'     => $agents,
                'products'   => $products,
                'mailboxes'  => $mailboxes,
                'tags'       => $tags,
                'stats'      => $stats,
                'priorities' => ['normal', 'medium', 'critical'],
                'statuses'   => ['new', 'active', 'closed'],
                'uploads'    => self::uploadLimits(),
                // Every date the MCP returns is in this zone, shown by its offset.
                'site_timezone' => wp_timezone_string(),
                'guidelines' => $guidelines,
            ]
        );
    }

    /**
     * How this agent's replies go out, as reply-to-ticket applies it. Only an agent
     * who cannot send directly gets a sentence explaining why.
     *
     * @return array
     */
    private static function replyMode()
    {
        if (PermissionManager::userCan(\FluentSupport\App\Modules\MCP\Support\AbilityGuard::MANAGE_TICKET_CAPS)) {
            return ['replies' => 'send'];
        }

        if (PermissionManager::currentUserCan('fst_draft_reply')) {
            return [
                'replies'      => 'draft_only',
                'replies_note' => 'You only have the Draft Reply permission: reply-to-ticket saves your replies as drafts for another agent to approve.',
            ];
        }

        return [
            'replies'      => 'none',
            'replies_note' => 'You can read tickets but not reply to them.',
        ];
    }

    /** How much of an inbox email template the MCP shows, in characters of Markdown. */
    const TEMPLATE_MAX_CHARS = 2000;

    /** How much of the agent's signature the MCP shows, in characters of Markdown. */
    const SIGNATURE_MAX_CHARS = 1000;

    /**
     * The email the customer gets when the agent writes from this mailbox: the
     * template itself, with [YOUR MESSAGE] where the agent's text goes and
     * [YOUR SIGNATURE] where their signature is added. The agent reads the template
     * and decides what to leave out; nothing here guesses at greetings or sign-offs.
     *
     * adds_signature is true only when the template has {{agent.signature}} and the
     * agent has a signature; then [YOUR SIGNATURE] marks it.
     *
     * @param MailBox     $mailbox
     * @param string      $emailKey
     * @param Agent       $agent
     * @param string|null $signature The agent's signature HTML. Pass it when describing several mailboxes for one agent.
     * @param bool        $compact   For get-support-context: leave out the signature text (it is under
     *                               you.signature) and the shared note (it is in the server instructions).
     * @return array
     */
    public static function describeAgentEmail($mailbox, $emailKey, $agent, $signature = null, $compact = false)
    {
        if ($signature === null) {
            $signature = \FluentSupport\App\Services\Tickets\ResponseService::getAppendedSignature($agent);
        }
        $hasSignature = (string) $signature !== '';

        $settings = (new \FluentSupport\App\Services\EmailNotification\Settings())->getBoxEmailSettings($mailbox, $emailKey);
        if (!$settings) {
            return ['sent' => false, 'note' => 'This inbox does not send this email.'];
        }

        $body = (string) ($settings['email_body'] ?? '');

        // The outreach email carries the agent's text as {{ticket.content}}: for an
        // agent-initiated ticket that is the agent's first message.
        $contentPattern = $emailKey === 'ticket_created_by_agent_on_behalf_email_to_customer'
            ? '/{{\s*(response\.(full_)?content|ticket\.content)\s*}}/'
            : '/{{\s*response\.(full_)?content\s*}}/';
        $signaturePattern = '/{{\s*agent\.signature\s*}}/';

        $includesMessage = (bool) preg_match($contentPattern, $body);
        $hasSignatureSlot = (bool) preg_match($signaturePattern, $body);
        $addsSignature = $hasSignature && $hasSignatureSlot;

        // The signature placeholder renders empty for an agent without one.
        $shown = preg_replace($contentPattern, '[YOUR MESSAGE]', $body);
        $shown = preg_replace($signaturePattern, $hasSignature ? '[YOUR SIGNATURE]' : '', $shown);
        $template = MCPHelper::htmlToMarkdown($shown);
        $truncated = mb_strlen($template) > self::TEMPLATE_MAX_CHARS;
        if ($truncated) {
            $template = mb_substr($template, 0, self::TEMPLATE_MAX_CHARS) . '...';
        }

        // From the agent's side a signature is either added automatically or not;
        // why not (no slot in the template, or no signature set) doesn't change what to write.
        if (!$includesMessage) {
            $note = 'This email does not include your message: it tells the customer to read it in the portal.';
        } elseif ($compact) {
            // The shared rule is in the server instructions; adds_signature carries the rest.
            $note = '';
        } else {
            $note = 'The customer gets this template with [YOUR MESSAGE] replaced by your text, so leave out any greeting or sign-off it already has. '
                . ($addsSignature
                    ? 'Your signature, shown in signature, is added automatically at [YOUR SIGNATURE], so do not sign your message. '
                    : 'No signature is added automatically. ')
                . 'The [...] markers are for reading the template only; never type them in a reply.';
        }

        $description = [
            'sent'           => ($settings['status'] ?? 'no') === 'yes',
            'includes_reply' => $includesMessage,
            'adds_signature' => $addsSignature,
            'subject'        => (string) ($settings['email_subject'] ?? ''),
            'template'       => $template,
            'from_name'      => $mailbox->getFromName($agent),
        ];

        if ($truncated) {
            $description['template_truncated'] = true;
        }

        if ($addsSignature && !$compact) {
            $description['signature'] = mb_substr(MCPHelper::htmlToMarkdown($signature), 0, self::SIGNATURE_MAX_CHARS);
        }

        if ($note !== '') {
            $description['note'] = $note;
        }

        return $description;
    }

    /**
     * The limits AttachmentStager applies to files sent with a reply or note.
     *
     * @return array
     */
    private static function uploadLimits()
    {
        $settings = (new \FluentSupport\App\Services\EmailNotification\Settings())->globalBusinessSettings();

        return [
            'max_files'        => \FluentSupport\App\Modules\MCP\Support\AttachmentStager::maxFiles(),
            'max_file_size_mb'   => floatval($settings['max_file_size'] ?? 0) ?: null,
            'allowed_extensions' => \FluentSupport\App\Modules\MCP\Support\AttachmentStager::allowedExtensions(),
            'note'               => 'Per reply or note: up to max_files files, each up to max_file_size_mb as a file (base64 text is about a third larger).',
        ];
    }

    public static function invalidateSupportContextCache()
    {
        update_option(self::CACHE_VERSION_OPTION, (int) get_option(self::CACHE_VERSION_OPTION, 0) + 1, false);
    }

    /**
     * Build a ticket query scoped to the current agent's visibility, mirroring listTickets().
     *
     * Applies the same `fluent_support/tickets_query_by_permission_ref` permission scope used by
     * TicketTools::listTickets so the "needs attention" lists never expose tickets — or the
     * customer names/emails they serialize — outside the agent's visibility scope. The
     * restricted-mailbox filter alone is not enough: an agent scoped to only their own tickets
     * must not enumerate other customers' tickets across the rest of the instance.
     */
    private static function scopedTicketQuery($ticketFields, $eagerLoad)
    {
        $query = Ticket::select($ticketFields)->with($eagerLoad);

        do_action_ref_array('fluent_support/tickets_query_by_permission_ref', [&$query]);

        return (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
    }

    private static function formatContextTicket($ticket, int $now)
    {
        $item = [
            'id'             => $ticket->id,
            'title'          => $ticket->title,
            'status'         => $ticket->status ?: 'new',
            'priority'       => MCPHelper::normalizePriority($ticket->priority),
            'response_count' => (int) $ticket->response_count,
            'waiting_on'     => MCPHelper::waitingOn($ticket),
        ];

        if ($ticket->relationLoaded('customer') && $ticket->customer) {
            $item['customer'] = MCPHelper::formatPersonSummary($ticket->customer);
        }

        if ($ticket->waiting_since) {
            $wait = max(0, $now - strtotime($ticket->waiting_since));
            $item['waiting'] = self::formatDuration($wait);
        }

        return $item;
    }

    public static function tagTicket($params)
    {
        $agent = MCPHelper::resolveAgent();
        if (!$agent) {
            return MCPHelper::error('unauthorized', __('No agent found for current user', 'fluent-support'));
        }

        $ticketId = (int) ($params['ticket_id'] ?? 0);
        if (!$ticketId) {
            return MCPHelper::error('invalid_param', __('ticket_id is required', 'fluent-support'), ['fields' => ['ticket_id']]);
        }

        $ticket = Ticket::find($ticketId);
        if (!$ticket) {
            return MCPHelper::error('not_found', __('Ticket not found', 'fluent-support'), ['next_step' => 'Use list-tickets to find valid ticket IDs']);
        }

        if ($err = TicketAccessGuard::assert($ticket)) {
            return $err;
        }

        $addTagIds    = array_map('intval', (array) ($params['add_tag_ids'] ?? []));
        $removeTagIds = array_map('intval', (array) ($params['remove_tag_ids'] ?? []));

        $addTagNames = array_values(array_unique(array_filter(array_map('sanitize_text_field', (array) ($params['add_tag_names'] ?? [])), function ($name) {
            return $name !== '';
        })));

        if (count($addTagNames) > self::MAX_TAG_NAMES) {
            return MCPHelper::error('invalid_param', sprintf(__('add_tag_names may not exceed %d items per request. Split into multiple calls.', 'fluent-support'), self::MAX_TAG_NAMES), ['fields' => ['add_tag_names'], 'limit' => self::MAX_TAG_NAMES]);
        }

        // Names only pick existing tags: the tag list is configuration that people
        // manage in the admin. Any unknown name stops the call before anything changes.
        $resolved = self::resolveTagNames($addTagNames);
        if ($resolved['unknown']) {
            return MCPHelper::error(
                'unknown_tag',
                /* translators: %s is a comma-separated list of tag names */
                sprintf(__('No tag is called: %s', 'fluent-support'), implode(', ', $resolved['unknown'])),
                [
                    'fields'         => ['add_tag_names'],
                    'available_tags' => $resolved['available'],
                    'next_step'      => 'Use one of these tags, or ask a person to create a new tag in the admin.',
                ]
            );
        }
        foreach ($resolved['ids'] as $tagId) {
            $addTagIds[] = $tagId;
        }

        // Only real ticket tags: fs_taggables also holds agent groups and other types.
        if ($addTagIds) {
            $addTagIds = TicketTag::whereIn('id', array_unique($addTagIds))->pluck('id')->toArray();
        }

        if ($addTagIds) {
            $ticket->applyTags($addTagIds);
        }

        if ($removeTagIds) {
            $ticket->detachTags($removeTagIds);
        }

        $ticket->load('tags');

        /* translators: %d is the ticket ID */
        $summary = sprintf(__('Tags updated on ticket #%d', 'fluent-support'), $ticketId);

        $data = ['tags' => $ticket->tags->map(function ($tag) {
            return ['id' => $tag->id, 'title' => $tag->title];
        })->toArray()];

        return MCPHelper::envelope($summary, $data);
    }

    /**
     * Existing tags for tag-ticket's add_tag_names, matched by title ignoring case
     * and surrounding spaces. Nothing is created.
     *
     * @param string[] $names
     * @return array{ids: int[], unknown: string[], available: string[]}
     */
    private static function resolveTagNames(array $names)
    {
        if (!$names) {
            return ['ids' => [], 'unknown' => [], 'available' => []];
        }

        $tags = TicketTag::select(['id', 'title'])->orderBy('title', 'ASC')->get();
        $byKey = [];
        foreach ($tags as $tag) {
            $key = mb_strtolower(trim((string) $tag->title));
            if (!isset($byKey[$key])) {
                $byKey[$key] = (int) $tag->id;
            }
        }

        $ids = [];
        $unknown = [];
        foreach ($names as $name) {
            $key = mb_strtolower(trim($name));
            if (isset($byKey[$key])) {
                $ids[] = $byKey[$key];
            } else {
                $unknown[] = $name;
            }
        }

        return [
            'ids'       => array_values(array_unique($ids)),
            'unknown'   => $unknown,
            'available' => $unknown ? array_slice($tags->pluck('title')->all(), 0, 200) : [],
        ];
    }

    public static function getSupportInsights($params)
    {
        $agent = MCPHelper::resolveAgent();
        if (!$agent) {
            return MCPHelper::error('unauthorized', __('No agent found for current user', 'fluent-support'));
        }

        $periodMap = ['24h' => 1, '7d' => 7, '30d' => 30, '90d' => 90];
        $period    = sanitize_text_field($params['period'] ?? '7d');
        if (!isset($periodMap[$period])) {
            $period = '7d';
        }

        // Ticket dates are stored in site time (current_time('mysql')), so the
        // cut-off must be site time too; a UTC cut-off shifts the whole window.
        $nowTs   = time();
        $sinceTs = $nowTs - $periodMap[$period] * DAY_IN_SECONDS;
        $since   = wp_date('Y-m-d H:i:s', $sinceTs);

        $scoped = function () {
            $query = Ticket::query();
            do_action_ref_array('fluent_support/tickets_query_by_permission_ref', [&$query]);
            return (new AgentTicketAccess())->applyMailboxRestrictionScope($query);
        };

        $periodTicketsQuery  = $scoped()->where('created_at', '>=', $since);
        $closedInPeriodQuery = $scoped()->where('resolved_at', '>=', $since);

        // Response times — aggregates in SQL; bounded sample only for median.
        $rtBase   = $scoped()->where('first_response_time', '>', 0)->where('created_at', '>=', $since);
        $rtAgg    = (clone $rtBase)->selectRaw('COUNT(*) as cnt, AVG(first_response_time) as avg_val, MAX(first_response_time) as max_val, MIN(first_response_time) as min_val')->first();
        $rtSample = ($rtAgg && (int) $rtAgg->cnt > 0)
            ? (clone $rtBase)->orderByDesc('id')->limit(500)->pluck('first_response_time')->toArray()
            : [];

        // Resolution times — same pattern.
        $ctBase   = (clone $closedInPeriodQuery)->where('total_close_time', '>', 0);
        $ctAgg    = (clone $ctBase)->selectRaw('COUNT(*) as cnt, AVG(total_close_time) as avg_val, MAX(total_close_time) as max_val, MIN(total_close_time) as min_val')->first();
        $ctSample = ($ctAgg && (int) $ctAgg->cnt > 0)
            ? (clone $ctBase)->orderByDesc('id')->limit(500)->pluck('total_close_time')->toArray()
            : [];

        // Waiting, split by whose turn it is. waiting_on=agent tickets genuinely need
        // a reply; waiting_on=customer tickets only reflect how long the customer
        // has been idle since our last reply. A single blended average over both populations
        // measures mostly customer silence and hides how many tickets actually need a reply.
        $waitOnAgentBase    = $scoped()->whereNotIn('status', ['closed'])->whereNotNull('waiting_since')->waitingOnly();
        $waitOnCustomerBase = $scoped()->whereNotIn('status', ['closed'])->whereNotNull('waiting_since')->where(function ($q) {
            $q->whereColumn('last_customer_response', '<', 'last_agent_response');
        });
        $waitingOnAgent    = self::computeWaitStats($waitOnAgentBase);
        $waitingOnCustomer = self::computeWaitStats($waitOnCustomerBase);

        $allAgents = Agent::where('person_type', 'agent')
            ->whereNotNull('first_name')
            ->where('first_name', '!=', '')
            ->select(['id', 'first_name', 'last_name'])
            ->get()
            ->keyBy('id');

        $agentWorkload = $scoped()->whereNotIn('status', ['closed'])
            ->whereNotNull('agent_id')
            ->selectRaw('agent_id, COUNT(*) as ticket_count')
            ->groupBy('agent_id')
            ->get()
            ->keyBy('agent_id');

        $agentClosedInPeriod = $scoped()->where('resolved_at', '>=', $since)
            ->whereNotNull('agent_id')
            ->selectRaw('agent_id, COUNT(*) as closed_count')
            ->groupBy('agent_id')
            ->get()
            ->keyBy('agent_id');

        $agentResponseTimes = $scoped()->where('first_response_time', '>', 0)
            ->where('created_at', '>=', $since)
            ->whereNotNull('agent_id')
            ->selectRaw('agent_id, AVG(first_response_time) as avg_response_time')
            ->groupBy('agent_id')
            ->get()
            ->keyBy('agent_id');

        $agentResolutionTimes = $scoped()->where('resolved_at', '>=', $since)
            ->where('total_close_time', '>', 0)
            ->whereNotNull('agent_id')
            ->selectRaw('agent_id, AVG(total_close_time) as avg_close_time')
            ->groupBy('agent_id')
            ->get()
            ->keyBy('agent_id');

        $agentIds = $allAgents->keys()
            ->merge($agentWorkload->keys())
            ->merge($agentClosedInPeriod->keys())
            ->unique();

        $performance = [];
        foreach ($agentIds as $agentId) {
            $agentModel = $allAgents->get($agentId);
            if (!$agentModel) {
                continue;
            }

            $entry = [
                'agent_id'         => $agentId,
                'agent_name'       => MCPHelper::personName($agentModel),
                'open_tickets'     => (int) ($agentWorkload->get($agentId)->ticket_count ?? 0),
                'closed_in_period' => (int) ($agentClosedInPeriod->get($agentId)->closed_count ?? 0),
            ];

            $avgResponse = $agentResponseTimes->get($agentId);
            $entry['avg_first_response'] = $avgResponse
                ? self::formatDuration((float) $avgResponse->avg_response_time)
                : null;

            $avgResolution = $agentResolutionTimes->get($agentId);
            $entry['avg_resolution'] = $avgResolution
                ? self::formatDuration((float) $avgResolution->avg_close_time)
                : null;

            $performance[] = $entry;
        }

        usort($performance, fn($a, $b) => $b['open_tickets'] - $a['open_tickets']);

        if (!empty($params['agent_id'])) {
            $onlyAgent   = (int) $params['agent_id'];
            $performance = array_values(array_filter($performance, fn($row) => (int) $row['agent_id'] === $onlyAgent));
        }

        $agentTotal      = count($performance);
        $truncatedAgents = $agentTotal > self::MAX_AGENTS;
        if ($truncatedAgents) {
            $performance = array_slice($performance, 0, self::MAX_AGENTS);
        }

        $created = (clone $periodTicketsQuery)->count();
        $closed  = (clone $closedInPeriodQuery)->count();

        return MCPHelper::envelope(
            "Support insights for {$period}: {$created} created, {$closed} closed",
            [
                'period' => $period,
                'from'   => wp_date('c', $sinceTs),
                'to'     => wp_date('c', $nowTs),
                'volume' => ['created' => $created, 'closed' => $closed],
                'first_response_time' => self::computeTimeStatsFromAgg($rtAgg, $rtSample),
                'resolution_time'     => self::computeTimeStatsFromAgg($ctAgg, $ctSample),
                'waiting'             => [
                    'waiting_on_agent'    => $waitingOnAgent,
                    'waiting_on_customer' => $waitingOnCustomer,
                ],
                'agent_performance'          => $performance,
                'agent_performance_total'     => $agentTotal,
                'agent_performance_truncated' => $truncatedAgents,
            ]
        );
    }

    private static function computeTimeStatsFromAgg($agg, array $sample)
    {
        if (!$agg || (int) $agg->cnt === 0) {
            return ['count' => 0, 'average' => null, 'median' => null, 'max' => null, 'min' => null];
        }

        return [
            'count'   => (int) $agg->cnt,
            'average' => self::formatDuration((float) $agg->avg_val),
            'median'  => self::formatDuration(self::median($sample)),
            'max'     => self::formatDuration((float) $agg->max_val),
            'min'     => self::formatDuration((float) $agg->min_val),
        ];
    }

    /**
     * Aggregate + bounded-sample-for-median pattern for a waiting_since-based population,
     * mirroring computeTimeStatsFromAgg() but for the TIMESTAMPDIFF-against-now duration used
     * by the two waiting populations (waiting_on_agent / waiting_on_customer) instead of a
     * stored duration column.
     */
    private static function computeWaitStats($baseQuery)
    {
        // waiting_since is stored in site time, so measure against site time, not UTC.
        $now = current_time('mysql');
        $agg = (clone $baseQuery)
            ->selectRaw('COUNT(*) as cnt, AVG(TIMESTAMPDIFF(SECOND, waiting_since, ?)) as avg_val, MAX(TIMESTAMPDIFF(SECOND, waiting_since, ?)) as max_val', [$now, $now])
            ->first();
        $sample = ($agg && (int) $agg->cnt > 0)
            ? (clone $baseQuery)->selectRaw('TIMESTAMPDIFF(SECOND, waiting_since, ?) as wait_sec', [$now])->orderByDesc('id')->limit(500)->pluck('wait_sec')->toArray()
            : [];

        return [
            'count'   => $agg ? (int) $agg->cnt : 0,
            'average' => self::formatDuration($agg ? (float) $agg->avg_val : 0),
            'max'     => self::formatDuration($agg ? (float) $agg->max_val : 0),
            'median'  => self::formatDuration(self::median($sample)),
        ];
    }

    private static function median(array $values)
    {
        if (empty($values)) {
            return 0;
        }
        sort($values);
        $count = count($values);
        $mid   = (int) floor($count / 2);
        return ($count % 2 === 0)
            ? ($values[$mid - 1] + $values[$mid]) / 2
            : $values[$mid];
    }

    private static function formatDuration($seconds)
    {
        return MCPHelper::formatDuration($seconds);
    }

    public static function bulkAction($params)
    {
        $agent = MCPHelper::resolveAgent();
        if (!$agent) {
            return MCPHelper::error('unauthorized', __('No agent found for current user', 'fluent-support'));
        }

        $ticketIds = array_map('intval', (array) ($params['ticket_ids'] ?? []));
        $action    = sanitize_text_field($params['action'] ?? '');

        if (empty($ticketIds) || !$action) {
            return MCPHelper::error('invalid_param', __('ticket_ids and action are required', 'fluent-support'), ['fields' => ['ticket_ids', 'action']]);
        }

        if (count($ticketIds) > self::MAX_BULK) {
            return MCPHelper::error('invalid_param', sprintf(__('ticket_ids may not exceed %d items per request. Split into multiple calls.', 'fluent-support'), self::MAX_BULK), ['fields' => ['ticket_ids'], 'limit' => self::MAX_BULK]);
        }

        $allowedActions = ['close', 'assign', 'tag', 'reply'];
        if (!in_array($action, $allowedActions)) {
            return MCPHelper::error('invalid_param', sprintf(__('action must be one of: %s', 'fluent-support'), implode(', ', $allowedActions)), ['fields' => ['action']]);
        }

        $assignTarget = null;
        if ($action === 'assign') {
            // Null ticket: each ticket's mailbox restriction is checked per-item
            // in the loop below, since the batch can span multiple mailboxes.
            $assignTarget = MCPHelper::resolveAssignmentTarget($params['agent_id'] ?? 0, null, 'agent_id');
            if (is_wp_error($assignTarget)) {
                return $assignTarget;
            }
        }

        $bulkTagIds = [];
        if ($action === 'tag') {
            $tagIds = array_map('intval', (array) ($params['tag_ids'] ?? []));
            if (empty($tagIds)) {
                return MCPHelper::error('invalid_param', __('tag_ids is required for the tag action', 'fluent-support'), ['fields' => ['tag_ids']]);
            }
            // Only real ticket tags: fs_taggables also holds agent groups and other types.
            $bulkTagIds = TicketTag::whereIn('id', $tagIds)->pluck('id')->toArray();
            if (!$bulkTagIds) {
                return MCPHelper::error('invalid_param', __('None of the tag_ids is a ticket tag', 'fluent-support'), ['fields' => ['tag_ids'], 'next_step' => 'Use get-support-context to see available tags and their IDs']);
            }
        }

        if ($action === 'reply' && empty($params['content']) && empty($params['saved_reply_id'])) {
            return MCPHelper::error('invalid_param', __('content or saved_reply_id is required for the reply action', 'fluent-support'), ['fields' => ['content', 'saved_reply_id']]);
        }

        $tickets       = Ticket::whereIn('id', $ticketIds)->get();
        $ticketService = new TicketService();
        $results       = [];
        $foundIds      = $tickets->pluck('id')->toArray();

        foreach ($ticketIds as $tid) {
            if (!in_array($tid, $foundIds)) {
                $results[] = ['id' => $tid, 'status' => 'not_found'];
            }
        }

        foreach ($tickets as $ticket) {
            if (TicketAccessGuard::assert($ticket)) {
                $results[] = ['id' => $ticket->id, 'status' => 'forbidden'];
                continue;
            }

            // Isolate each ticket: a failure on one (e.g. inside close(),
            // onAgentChange(), or applyTags()) must not abort the whole batch.
            try {
                switch ($action) {
                    case 'close':
                        if ($ticket->status === 'closed') {
                            $results[] = ['id' => $ticket->id, 'status' => 'already_closed'];
                        } else {
                            // close() takes 'yes'/'no'; only 'no' sends the closing email.
                            $ticketService->close($ticket, $agent, '', !empty($params['silent']) ? 'yes' : 'no');
                            $results[] = ['id' => $ticket->id, 'status' => 'closed'];
                        }
                        break;

                    case 'assign':
                        if (TicketAccessGuard::assertAssignableAgent($ticket, $assignTarget)) {
                            $results[] = ['id' => $ticket->id, 'status' => 'mailbox_restricted'];
                            break;
                        }
                        MCPHelper::applyAgentAssignment($ticket, $assignTarget, $agent);
                        $results[] = ['id' => $ticket->id, 'status' => 'assigned'];
                        break;

                    case 'tag':
                        $ticket->applyTags($bulkTagIds);
                        $results[] = ['id' => $ticket->id, 'status' => 'tagged'];
                        break;

                    case 'reply':
                        $results[] = self::bulkReply($ticket, $params);
                        break;
                }
            } catch (\Exception $e) {
                $results[] = ['id' => $ticket->id, 'status' => 'error', 'message' => $e->getMessage()];
            }
        }

        $processed = count(array_filter($results, function ($r) {
            return in_array($r['status'], ['closed', 'already_closed', 'assigned', 'tagged', 'replied', 'drafted', 'duplicate'], true);
        }));

        $total     = count($ticketIds);
        $verbMap   = ['close' => 'closed', 'assign' => 'assigned', 'tag' => 'tagged', 'reply' => 'replied to'];
        $verb      = $verbMap[$action] ?? $action;

        return MCPHelper::envelope(
            "{$processed} of {$total} ticket(s) {$verb}",
            ['results' => $results],
            ['processed' => $processed, 'total' => $total]
        );
    }

    /**
     * One ticket of a bulk reply, through the same path as reply-to-ticket
     * (access, reopen, auto-assign, drafts, duplicate guard).
     */
    private static function bulkReply(Ticket $ticket, $params)
    {
        $replyParams = array_intersect_key($params, array_flip(['content', 'content_format', 'saved_reply_id', 'close_ticket', 'draft']));
        $replyParams['ticket_id'] = $ticket->id;

        $result = ResponseTools::replyToTicket($replyParams);

        if (is_wp_error($result)) {
            $error = json_decode($result->get_error_message(), true);
            return [
                'id'      => $ticket->id,
                'status'  => $error['error']['code'] ?? 'error',
                'message' => $error['error']['message'] ?? $result->get_error_message(),
            ];
        }

        $data = $result['data'];
        if (!empty($data['duplicate'])) {
            $status = 'duplicate';
        } elseif (!empty($data['draft'])) {
            $status = 'drafted';
        } else {
            $status = 'replied';
        }

        return array_filter([
            'id'            => $ticket->id,
            'status'        => $status,
            'response_id'   => $data['response']['id'] ?? null,
            'ticket_status' => $data['ticket_status'] ?? null,
            'reopened'      => !empty($data['reopened']) ?: null,
        ], function ($value) {
            return $value !== null;
        });
    }

    public static function getMentions($params)
    {
        $agent = MCPHelper::resolveAgent();
        if (!$agent) {
            return MCPHelper::error('unauthorized', __('No agent found for current user', 'fluent-support'));
        }

        $notificationSettings = new \FluentSupport\App\Services\Notifications\NotificationSettings();
        if (!$notificationSettings->canUseNotificationTables(true)) {
            return MCPHelper::error('not_available', __('Internal notifications are not enabled. Enable them in Fluent Support Settings > Notifications.', 'fluent-support'), [
                'next_step' => 'Enable internal notifications in Fluent Support admin settings',
            ]);
        }

        ['page' => $page, 'per_page' => $perPage] = MCPHelper::pagination($params, 15);

        $status = sanitize_text_field($params['status'] ?? 'all');
        if (!in_array($status, ['all', 'unread', 'read'], true)) {
            $status = 'all';
        }

        $filters = [
            'category' => \FluentSupport\App\Services\Notifications\NotificationCategory::MENTIONS,
        ];

        if ($status !== 'all') {
            $filters['status'] = $status;
        }

        if (!empty($params['ticket_id'])) {
            $filters['ticket_id'] = (int) $params['ticket_id'];
        }

        $queryService = new \FluentSupport\App\Services\Notifications\NotificationQueryService();
        $query        = $queryService->getNotificationsForPerson($agent->id, $filters);

        if (!$query) {
            return MCPHelper::error('not_available', __('Internal notifications are not enabled.', 'fluent-support'));
        }

        $paginated = $query->paginate($perPage, ['*'], 'page', $page);

        $unreadCount = (new \FluentSupport\App\Services\Notifications\NotificationQueryService())
            ->getUnreadCount($agent->id, ['category' => \FluentSupport\App\Services\Notifications\NotificationCategory::MENTIONS]);

        $mentions = [];
        foreach ($paginated->items() as $notification) {
            $payload = $notification->getPayloadAttribute($notification->getRawOriginal('payload') ?? null);
            if (!is_array($payload)) {
                $payload = [];
            }

            $readStatus = null;
            if ($notification->relationLoaded('recipients') && $notification->recipients->isNotEmpty()) {
                $recipient  = $notification->recipients->first();
                $readStatus = (bool) $recipient->is_read;
            }

            $actor      = $notification->relationLoaded('actor') ? $notification->actor : null;
            $ticket     = $notification->relationLoaded('ticket') ? $notification->ticket : null;
            $actorName  = $actor ? MCPHelper::personName($actor) : (__('Someone', 'fluent-support'));
            $ticketTitle = $ticket ? $ticket->title : ($payload['ticket_title'] ?? null);

            $summary = $ticketTitle
                ? sprintf(__('%1$s mentioned you in "%2$s"', 'fluent-support'), $actorName, $ticketTitle)
                : sprintf(__('%1$s mentioned you', 'fluent-support'), $actorName);

            $mention = [
                'id'              => $notification->id,
                'summary'         => $summary,
                'is_read'         => $readStatus,
                'ticket_id'       => $notification->ticket_id,
                'ticket_title'    => $ticketTitle,
                'conversation_id' => $notification->conversation_id,
                'mentioned_by'    => $actor ? MCPHelper::formatPersonSummary($actor) : null,
                'content_preview' => isset($payload['content_preview']) ? sanitize_text_field($payload['content_preview']) : null,
                'created_at'      => MCPHelper::toIso8601($notification->created_at),
            ];

            $mentions[] = $mention;
        }

        $total = $paginated->total();

        return MCPHelper::envelope(
            sprintf(
                _n('Found %d mention', 'Found %d mentions', $total, 'fluent-support'),
                $total
            ) . ($unreadCount ? " ({$unreadCount} unread)" : ''),
            ['mentions' => $mentions, 'unread_count' => $unreadCount],
            MCPHelper::pagingMeta($paginated)
        );
    }

}