<?php

namespace App\Services;

use App\Models\AttendanceLog;
use App\Models\AttendanceSetting;
use App\Models\Customer;
use App\Models\Employee;
use App\Models\Expense;
use App\Models\ExpenseArchiveItem;
use App\Models\MonthlySettlement;
use App\Models\Service;
use App\Models\ServiceSubscription;
use App\Models\StageAcademyActivation;
use App\Models\StageAcademyCourse;
use App\Models\SubscriptionPayment;
use App\Models\SubscriptionRenewal;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

class SystemAssistantService
{
    public function answer(string $question, User $user, array $history = [], array $sessionContext = []): array
    {
        $question = trim($question);
        $selection = $this->selectTool($question, $history, $sessionContext);

        if (! $selection) {
            $chatText = $this->generateGeneralChat($question, $history, $sessionContext)
                ?? $this->generateRuleBasedChat($question, $sessionContext);
            if (is_string($chatText) && trim($chatText) !== '') {
                return [
                    'request_type' => 'generalChat',
                    'tool' => 'generalChat',
                    'intent' => 'generalChat',
                    'status' => 'success',
                    'params' => null,
                    'text' => $chatText,
                    'summary' => 'general_chat',
                    'choices' => $this->generateSuggestions('help', []),
                    'context' => $sessionContext,
                ];
            }

            return [
                'request_type' => 'unknown',
                'tool' => 'unknown',
                'intent' => 'unknown',
                'status' => 'success',
                'params' => null,
                'text' => $this->helpText(),
                'summary' => 'help',
                'choices' => [],
                'context' => [],
            ];
        }

        $tool = $selection['tool'];
        $params = $selection['params'];
        $intent = $selection['intent'] ?? $tool;

        $result = match ($tool) {
            'findCustomer' => $this->toolFindCustomer($params),
            'customerSummary' => $this->toolCustomerSummary($params),
            'getInvoice' => $this->toolGetInvoice($params),
            'listInvoicesByCustomer' => $this->toolListInvoicesByCustomer($params),
            'getCustomerRenewals' => $this->toolGetCustomerRenewals($params),
            'salesReportMonthly' => $this->toolSalesReportMonthly($params),
            'salesReportYearly' => $this->toolSalesReportYearly($params),
            'salesReportRange' => $this->toolSalesReportRange($params),
            'topCustomers' => $this->toolTopCustomers($params),
            'topServicesSoldThisMonth' => $this->toolTopServicesSoldThisMonth(),
            'topStageAcademyCoursesSoldThisMonth' => $this->toolTopStageAcademyCoursesSoldThisMonth(),
            'employeesMonthlyReportThisMonth' => $this->toolEmployeesMonthlyReportThisMonth(),
            'newSubscriptions' => $this->toolNewSubscriptions($params),
            'lastInvoices' => $this->toolLastInvoices($params),
            'salesCompare' => $this->toolSalesCompare($params),
            'compareMonths' => $this->toolCompareMonths($params),
            'monthlyFullReport' => $this->toolMonthlyFullReport($params),
            'yearlyFullReport' => $this->toolYearlyFullReport($params),
            'serviceAnalysis' => $this->toolServiceAnalysis($params),
            'customerTotalPayments' => $this->toolCustomerTotalPayments($params),
            'help' => [
                'request_type' => 'help',
                'params' => $params,
                'text' => $this->helpText(),
                'summary' => 'help',
                'choices' => [],
            ],
            'fallback_error' => $this->toolFallbackError($params),
            default => [
                'request_type' => 'unknown',
                'params' => $params,
                'text' => $this->helpText(),
                'summary' => 'help',
                'choices' => [],
            ],
        };

        // Append metadata
        $result['tool'] = $tool;
        $result['intent'] = $intent;
        $result['status'] = 'success';

        // Extract context from result to update session
        $newContext = [];
        if (isset($params['customer_id'])) {
            $newContext['customer_id'] = $params['customer_id'];
        }
        if (isset($result['summary']) && preg_match('/customer_id=(\d+)/', $result['summary'], $m)) {
            $newContext['customer_id'] = (int) $m[1];
        }

        // Capture last invoice context
        if (isset($params['invoice'])) {
            $newContext['last_invoice_id'] = $params['invoice'];
        }

        // Capture date/period context
        if (isset($params['year'])) {
            $newContext['last_year'] = $params['year'];
        }
        if (isset($params['month'])) {
            $newContext['last_month'] = $params['month'];
        }
        if (isset($params['from'])) {
            $newContext['last_from'] = $params['from'];
        }
        if (isset($params['to'])) {
            $newContext['last_to'] = $params['to'];
        }

        // If the result has data and the user asked for analysis/comparison/details not covered by standard text,
        // we can use the LLM to generate a better response.
        // For now, let's always enhance sales reports if data is present.
        if (str_starts_with($tool, 'salesReport') && ! empty($result['data'])) {
            $enhancedText = $this->generateAnalysis($question, $result['data']);
            if ($enhancedText) {
                $result['text'] = $enhancedText;
            }
        }

        // Add smart suggestions
        $result['choices'] = array_merge($result['choices'] ?? [], $this->generateSuggestions($tool, $params));

        // Merge new context
        $result['context'] = array_merge($sessionContext, $newContext);

        return $result;
    }

    private function selectTool(string $question, array $history = [], array $sessionContext = []): ?array
    {
        // 1. Try Heuristics first for simple commands (fastest)
        // We only use heuristics if no session context is heavily implied or if it's a direct command
        if ($parsed = $this->selectToolHeuristic($question)) {
            return $parsed;
        }

        // 2. Try LLM with Schema Awareness and History
        if ($parsed = $this->selectToolViaOllama($question, $history, $sessionContext)) {
            return $parsed;
        }

        return null;
    }

    private function parseStructuredCommand(string $question): ?array
    {
        $question = trim($question);
        if (! preg_match('/^(findCustomer|customerSummary|getInvoice|listInvoicesByCustomer|getCustomerRenewals|salesReportMonthly|salesReportYearly|salesReportRange)\s+(.*)$/u', $question, $m)) {
            return null;
        }

        $tool = $m[1];
        $rest = trim($m[2]);

        return match ($tool) {
            'findCustomer' => $this->validateSelection($tool, ['query' => $rest]),
            'customerSummary' => $this->validateSelection($tool, ['customer_id' => $rest]),
            'getInvoice' => $this->validateSelection($tool, ['invoice' => $rest]),
            'getCustomerRenewals' => $this->validateSelection($tool, ['customer_id' => $rest]),
            'listInvoicesByCustomer' => $this->validateSelection($tool, $this->parseCustomerDateRange($rest)),
            'salesReportMonthly' => $this->validateSelection($tool, $this->parseYearMonth($rest)),
            'salesReportYearly' => $this->validateSelection($tool, ['year' => $rest]),
            'salesReportRange' => $this->validateSelection($tool, $this->parseFromTo($rest)),
            default => null,
        };
    }

    private function selectToolHeuristic(string $question): ?array
    {
        $q = Str::lower($question);
        $trimmed = trim($question);
        $now = now();

        // Explicit help command to avoid Ollama call
        if (preg_match('/^(help|commands|menu|مساعدة|اوامر|أوامر|قائمة)$/iu', $question)) {
            return $this->validateSelection('help', []);
        }

        if ($trimmed === __('system_assistant.chips.sales_month')) {
            return $this->validateSelection('salesReportMonthly', ['year' => $now->year, 'month' => $now->month]);
        }
        if ($trimmed === __('system_assistant.suggestions.sales_last_month')) {
            $prev = $now->copy()->subMonth();

            return $this->validateSelection('salesReportMonthly', ['year' => $prev->year, 'month' => $prev->month]);
        }
        if ($trimmed === __('system_assistant.suggestions.sales_this_year') || $trimmed === __('system_assistant.sales_report.yearly_sales', ['year' => $now->year])) {
            return $this->validateSelection('salesReportYearly', ['year' => $now->year]);
        }
        if (
            $trimmed === __('system_assistant.chips.top_customers')
            || $trimmed === __('system_assistant.suggestions.top_customers')
            || $trimmed === __('system_assistant.payloads.top_customers')
        ) {
            return $this->validateSelection('topCustomers', [
                'from' => $now->copy()->startOfMonth()->toDateString(),
                'to' => $now->copy()->endOfMonth()->toDateString(),
                'limit' => 5,
            ]);
        }
        if ($trimmed === __('system_assistant.chips.new_subscriptions')) {
            return $this->validateSelection('newSubscriptions', [
                'from' => $now->copy()->startOfMonth()->toDateString(),
                'to' => $now->copy()->endOfMonth()->toDateString(),
                'limit' => 10,
            ]);
        }
        if ($trimmed === __('system_assistant.chips.last_invoices')) {
            return $this->validateSelection('lastInvoices', ['limit' => 10]);
        }
        if ($trimmed === __('system_assistant.chips.sales_compare')) {
            return $this->validateSelection('salesCompare', ['mode' => 'this_vs_last_month']);
        }
        if ($trimmed === __('system_assistant.payloads.compare_last_year') || $trimmed === __('system_assistant.suggestions.compare_last_year')) {
            return $this->validateSelection('salesCompare', ['mode' => 'this_year_to_date_vs_last_year']);
        }
        if ($trimmed === __('system_assistant.payloads.service_analysis') || $trimmed === __('system_assistant.suggestions.service_analysis')) {
            return $this->validateSelection('serviceAnalysis', [
                'from' => $now->copy()->startOfMonth()->toDateString(),
                'to' => $now->copy()->endOfMonth()->toDateString(),
                'limit' => 5,
            ]);
        }
        if ($trimmed === __('system_assistant.chips.months_compare')) {
            return $this->validateSelection('compareMonths', ['months' => 6]);
        }
        if ($trimmed === __('system_assistant.chips.monthly_full_report')) {
            return $this->validateSelection('monthlyFullReport', ['year' => $now->year, 'month' => $now->month]);
        }
        if ($trimmed === __('system_assistant.chips.yearly_report')) {
            return $this->validateSelection('yearlyFullReport', ['year' => $now->year]);
        }
        if ($trimmed === __('system_assistant.chips.top_services_sold')) {
            return $this->validateSelection('topServicesSoldThisMonth', []);
        }
        if ($trimmed === __('system_assistant.chips.top_stage_academy_courses_sold')) {
            return $this->validateSelection('topStageAcademyCoursesSoldThisMonth', []);
        }
        if ($trimmed === __('system_assistant.chips.employees_monthly_report')) {
            return $this->validateSelection('employeesMonthlyReportThisMonth', []);
        }

        if (
            (Str::contains($q, ['مقارنة', 'compare']) && Str::contains($q, ['اشهر', 'الأشهر', 'months']))
            || preg_match('/\bمقارنة\s+بين\s+الأشهر\b/u', $question)
        ) {
            $months = 6;
            if (preg_match('/\b(\d{1,2})\s*(?:شهر|أشهر|اشهر|months?)\b/iu', $question, $m)) {
                $months = (int) $m[1];
            }

            return $this->validateSelection('compareMonths', ['months' => $months]);
        }
        if (Str::contains($q, ['تقرير شهري', 'monthly report'])) {
            if ($ym = $this->extractYearMonth($question)) {
                return $this->validateSelection('monthlyFullReport', $ym);
            }

            return $this->validateSelection('monthlyFullReport', ['year' => $now->year, 'month' => $now->month]);
        }
        if (Str::contains($q, ['تقرير سنوي', 'تقرير سنة', 'تقرير سنه', 'yearly report'])) {
            if (preg_match('/\b(20\d{2})\b/', $question, $m)) {
                return $this->validateSelection('yearlyFullReport', ['year' => (int) $m[1]]);
            }

            return $this->validateSelection('yearlyFullReport', ['year' => $now->year]);
        }

        if (preg_match('/cpp-\d+/i', $question, $m)) {
            return $this->validateSelection('getInvoice', ['invoice' => strtoupper($m[0])]);
        }

        // Specific check for "Invoices of Customer X" to avoid confusing customer ID with invoice ID
        if (Str::contains($q, ['فواتير', 'invoices']) && Str::contains($q, ['زبون', 'عميل', 'customer'])) {
            if (preg_match('/\b\d+\b/', $question, $m)) {
                return $this->validateSelection('listInvoicesByCustomer', ['customer_id' => $m[0]]);
            }
        }

        if (Str::contains($q, ['فاتورة', 'invoice'])) {
            if (preg_match('/\b\d+\b/', $question, $m)) {
                // If it looks like a customer query, skip this heuristic
                if (! Str::contains($q, ['زبون', 'عميل', 'customer'])) {
                    return $this->validateSelection('getInvoice', ['invoice' => $m[0]]);
                }
            }

            return $this->validateSelection('getInvoice', ['invoice' => $question]);
        }

        if (Str::contains($q, ['مبيعات', 'sales'])) {
            if ($range = $this->extractFromToDates($question)) {
                return $this->validateSelection('salesReportRange', $range);
            }

            if ($ym = $this->extractYearMonth($question)) {
                return $this->validateSelection('salesReportMonthly', $ym);
            }

            if (preg_match('/\b(20\d{2})\b/', $question, $m)) {
                return $this->validateSelection('salesReportYearly', ['year' => $m[1]]);
            }
        }

        if (Str::contains($q, ['تجديد', 'renewal', 'renewals'])) {
            if (preg_match('/\b\d+\b/', $question, $m)) {
                return $this->validateSelection('getCustomerRenewals', ['customer_id' => $m[0]]);
            }
        }

        if (Str::contains($q, ['مدفوع', 'payments', 'payment'])) {
            if (preg_match('/\b\d+\b/', $question, $m)) {
                return $this->validateSelection('customerTotalPayments', ['customer_id' => $m[0]]);
            }
        }

        if (Str::contains($q, ['ملخص', 'summary']) && preg_match('/\b\d+\b/', $question, $m)) {
            return $this->validateSelection('customerSummary', ['customer_id' => $m[0]]);
        }

        if (Str::contains($q, ['زبون', 'عميل', 'customer']) || preg_match('/\b\d{7,}\b/', $question)) {
            // Exclude analytical/ambiguous queries that should go to LLM
            if (Str::contains($q, ['أفضل', 'top', 'best', 'compare', 'مقارنة', 'analysis', 'تحليل', 'summary', 'ملخص', 'report', 'تقرير'])) {
                return null;
            }

            return $this->validateSelection('findCustomer', ['query' => $question]);
        }

        return null;
    }

    private function selectToolViaOllama(string $question, array $history = [], array $sessionContext = []): ?array
    {
        $baseUrl = $this->ollamaBaseUrl();
        if ($baseUrl === null) {
            return null;
        }

        $model = (string) env('OLLAMA_MODEL', 'mistral:7b-instruct');

        $messages = [
            [
                'role' => 'system',
                'content' => $this->ollamaSystemPrompt($sessionContext),
            ],
        ];

        // Add history (last 5 messages)
        // Filter out the last message if it matches the current question to avoid duplication
        $recentHistory = array_slice($history, -5);
        $lastMsg = end($recentHistory);
        if ($lastMsg && isset($lastMsg['content']) && trim($lastMsg['content']) === trim($question)) {
            array_pop($recentHistory);
        }

        foreach ($recentHistory as $msg) {
            $role = ($msg['role'] ?? '') === 'assistant' ? 'assistant' : 'user';
            $content = (string) ($msg['content'] ?? '');
            if ($content) {
                $messages[] = ['role' => $role, 'content' => $content];
            }
        }

        $messages[] = ['role' => 'user', 'content' => $question];

        $payload = [
            'model' => $model,
            'stream' => false,
            'messages' => $messages,
            'options' => [
                'temperature' => 0.1, // Lower temperature for more deterministic/strict JSON
            ],
        ];

        try {
            $res = Http::timeout(10)->retry(0, 0)->post($baseUrl.'/api/chat', $payload);
            if (! $res->ok()) {
                return $this->validateSelection('fallback_error', ['query' => $question]);
            }

            $json = $res->json();
            $content = data_get($json, 'message.content');
            if (! is_string($content) || trim($content) === '') {
                return $this->validateSelection('fallback_error', ['query' => $question]);
            }

            // Extract JSON from potential markdown blocks
            if (preg_match('/```json(.*?)```/s', $content, $m)) {
                $content = $m[1];
            } elseif (preg_match('/\{.*\}/s', $content, $m)) {
                $content = $m[0]; // Try to find JSON object if no code blocks
            }

            $decoded = json_decode($content, true);
            if (! is_array($decoded)) {
                return $this->validateSelection('fallback_error', ['query' => $question]);
            }

            // Handle new structure
            $tool = $decoded['tool'] ?? null;
            $params = $decoded['params'] ?? null;
            $intent = $decoded['intent'] ?? null;

            if (! is_string($tool) || ! is_array($params)) {
                return $this->validateSelection('fallback_error', ['query' => $question]);
            }

            $validated = $this->validateSelection($tool, $params, $intent);
            if (! $validated) {
                return $this->validateSelection('fallback_error', ['query' => $question]);
            }

            return $validated;
        } catch (\Throwable $e) {
            return $this->validateSelection('fallback_error', ['query' => $question]);
        }
    }

    private function generateAnalysis(string $question, array $data): ?string
    {
        $baseUrl = $this->ollamaBaseUrl();
        if ($baseUrl === null) {
            return null;
        }

        $model = (string) env('OLLAMA_MODEL', 'mistral:7b-instruct');

        // Summarize data to avoid token limits if too large
        $dataContext = json_encode($data);
        if (strlen($dataContext) > 8000) {
            $dataContext = substr($dataContext, 0, 8000).'... (truncated)';
        }

        $prompt = implode("\n", [
            'Act as a professional Data Analyst.',
            "Analyze the provided data to answer: '{$question}'.",
            'Provide a clear, analytical summary in ARABIC (unless user asked in English).',
            'Key Requirements:',
            '- Compare with previous period if available.',
            '- Calculate percentage changes (Growth/Decline).',
            '- Highlight top performers (customers/services).',
            '- Use Markdown tables or lists for clarity.',
            '- Be concise but insightful.',
            'Data:',
            $dataContext,
        ]);

        try {
            $res = Http::timeout(25)->post($baseUrl.'/api/chat', [
                'model' => $model,
                'stream' => false,
                'messages' => [
                    ['role' => 'user', 'content' => $prompt],
                ],
            ]);

            return data_get($res->json(), 'message.content');
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function generateRuleBasedChat(string $question, array $sessionContext = []): string
    {
        $trimmed = trim($question);
        if ($trimmed === '') {
            return $this->helpText();
        }

        if (preg_match('/^(السلام عليكم|هلا|أهلا|اهلا|مرحبا|مرحباً|صباح الخير|مساء الخير|hello|hi)\b/iu', $trimmed)) {
            return implode("\n", [
                'أهلاً بك. اكتب سؤالك بأي صيغة وسأحاول مساعدتك.',
                '',
                __('system_assistant.disclaimer'),
            ]);
        }

        if (preg_match('/\b(شكرا|شكراً|مشكور|مشكوره|thanks|thx)\b/iu', $trimmed)) {
            return 'العفو. اكتب ما تحتاجه بالتفصيل وسأساعدك.';
        }

        $q = Str::lower($trimmed);

        if (Str::contains($q, ['مبيعات', 'sales'])) {
            if ($this->extractFromToDates($trimmed) || $this->extractYearMonth($trimmed) || preg_match('/\b(20\d{2})\b/', $trimmed)) {
                return __('system_assistant.fallback_message');
            }

            return implode("\n", [
                'أكيد. حدّد الفترة المطلوبة حتى أطلع لك تقرير المبيعات.',
                'مثال: مبيعات 2026/04 أو من 2026-04-01 إلى 2026-04-30',
            ]);
        }

        if (Str::contains($q, ['زبون', 'عميل', 'customer'])) {
            if (! empty($sessionContext['customer_id']) && Str::contains($q, ['ملخص', 'تفاصيل', 'هذا', 'هاذا', 'نفسه'])) {
                return 'تقصد ملخص الزبون الحالي؟ اكتب: ملخص '.$sessionContext['customer_id'];
            }

            return 'اكتب اسم الزبون أو رقم الهاتف، أو اكتب: ملخص 12 لعرض ملخص زبون بالرقم.';
        }

        if (Str::contains($q, ['فاتورة', 'invoice', 'cpp-'])) {
            return 'اكتب رقم الفاتورة مثل: CPP-1001 أو اكتب رقمها فقط.';
        }

        return implode("\n\n", [
            __('system_assistant.fallback_message'),
            $this->helpText(),
        ]);
    }

    private function ollamaBaseUrl(): ?string
    {
        static $resolved = null;
        static $resolvedOnce = false;

        if ($resolvedOnce) {
            return $resolved;
        }

        $baseUrl = rtrim((string) env('OLLAMA_URL', ''), '/');
        if ($baseUrl === '') {
            $baseUrl = 'http://localhost:11434';
        }

        if (! $this->isAllowedInternalUrl($baseUrl)) {
            $resolvedOnce = true;
            $resolved = null;

            return null;
        }

        try {
            $res = Http::timeout(0.4)->retry(0, 0)->get($baseUrl.'/api/tags');
            if (! $res->ok()) {
                $resolvedOnce = true;
                $resolved = null;

                return null;
            }
        } catch (\Throwable $e) {
            $resolvedOnce = true;
            $resolved = null;

            return null;
        }

        $resolvedOnce = true;
        $resolved = $baseUrl;

        return $baseUrl;
    }

    private function generateGeneralChat(string $question, array $history = [], array $sessionContext = []): ?string
    {
        $baseUrl = $this->ollamaBaseUrl();
        if ($baseUrl === null) {
            return null;
        }

        $model = (string) env('OLLAMA_MODEL', 'mistral:7b-instruct');

        $messages = [
            [
                'role' => 'system',
                'content' => implode("\n", [
                    'أنت مساعد نظام داخل منصة إدارية.',
                    'المطلوب: الرد على أي سؤال عام أو نص بشكل مفيد وبالعربية (إلا إذا كان السؤال بالإنجليزية).',
                    'تنبيه مهم: لا تخترع أرقاماً أو بيانات من قاعدة البيانات.',
                    'إذا كان السؤال يطلب معلومات رقمية/تقارير من النظام (مبيعات/فواتير/زبائن/مصاريف/رواتب)، اطلب تحديد الفترة أو استخدم اقتراحات الأزرار.',
                    'حافظ على الرد قصير وواضح.',
                    ! empty($sessionContext) ? ('سياق الجلسة: '.json_encode($sessionContext)) : '',
                ]),
            ],
        ];

        $recentHistory = array_slice($history, -6);
        foreach ($recentHistory as $msg) {
            $role = ($msg['role'] ?? '') === 'assistant' ? 'assistant' : 'user';
            $content = (string) ($msg['content'] ?? '');
            if ($content !== '') {
                $messages[] = ['role' => $role, 'content' => $content];
            }
        }

        $messages[] = ['role' => 'user', 'content' => $question];

        try {
            $res = Http::timeout(20)->retry(0, 0)->post($baseUrl.'/api/chat', [
                'model' => $model,
                'stream' => false,
                'messages' => $messages,
                'options' => [
                    'temperature' => 0.5,
                ],
            ]);

            $text = data_get($res->json(), 'message.content');
            if (! is_string($text)) {
                return null;
            }

            return trim($text);
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function isAllowedInternalUrl(string $url): bool
    {
        $parts = parse_url($url);
        if (! is_array($parts)) {
            return false;
        }

        $scheme = strtolower((string) ($parts['scheme'] ?? ''));
        if (! in_array($scheme, ['http', 'https'], true)) {
            return false;
        }

        $host = (string) ($parts['host'] ?? '');
        if ($host === '') {
            return false;
        }

        if ($host === 'localhost') {
            return true;
        }

        if (! filter_var($host, FILTER_VALIDATE_IP)) {
            return false;
        }

        return $this->isPrivateIp($host);
    }

    // ... (isPrivateIp remains same, skipped for brevity in replace if not changing)
    // Actually I need to include it or use careful replace.
    // I will replace from selectToolViaOllama down to end of file, re-including helper methods.

    private function isPrivateIp(string $ip): bool
    {
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            if (str_starts_with($ip, '10.')) {
                return true;
            }
            if (str_starts_with($ip, '192.168.')) {
                return true;
            }
            if (str_starts_with($ip, '127.')) {
                return true;
            }
            if (preg_match('/^172\.(1[6-9]|2\d|3[0-1])\./', $ip)) {
                return true;
            }

            return false;
        }
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
            $normalized = strtolower($ip);
            if ($normalized === '::1') {
                return true;
            }
            if (str_starts_with($normalized, 'fc') || str_starts_with($normalized, 'fd')) {
                return true;
            }
            if (str_starts_with($normalized, 'fe80')) {
                return true;
            }

            return false;
        }

        return false;
    }

    private function toolFallbackError(array $params): array
    {
        return [
            'request_type' => 'error',
            'params' => $params,
            'text' => __('system_assistant.fallback_message'),
            'summary' => 'fallback_error',
            'choices' => $this->generateSuggestions('help', []),
        ];
    }

    private function validateSelection(string $tool, array $params, ?string $intent = null): ?array
    {
        $allow = [
            'findCustomer', 'customerSummary', 'getInvoice', 'listInvoicesByCustomer',
            'getCustomerRenewals', 'salesReportMonthly', 'salesReportYearly', 'salesReportRange',
            'topCustomers', 'topServicesSoldThisMonth', 'topStageAcademyCoursesSoldThisMonth', 'employeesMonthlyReportThisMonth',
            'newSubscriptions', 'lastInvoices', 'salesCompare', 'serviceAnalysis', 'customerTotalPayments',
            'compareMonths', 'monthlyFullReport', 'yearlyFullReport',
            'fallback_error', 'help',
        ];

        if (! in_array($tool, $allow, true)) {
            return null;
        }

        $normalized = match ($tool) {
            'findCustomer' => $this->validateFindCustomer($params),
            'customerSummary' => $this->validateCustomerId($params),
            'getInvoice' => $this->validateInvoice($params),
            'listInvoicesByCustomer' => $this->validateListInvoicesByCustomer($params),
            'getCustomerRenewals' => $this->validateCustomerId($params),
            'salesReportMonthly' => $this->validateSalesMonthly($params),
            'salesReportYearly' => $this->validateSalesYearly($params),
            'salesReportRange' => $this->validateSalesRange($params),
            'topCustomers' => $this->validateRangeWithLimit($params),
            'topServicesSoldThisMonth' => [],
            'topStageAcademyCoursesSoldThisMonth' => [],
            'employeesMonthlyReportThisMonth' => [],
            'newSubscriptions' => $this->validateRangeWithLimit($params),
            'lastInvoices' => $this->validateLimitOnly($params),
            'salesCompare' => $this->validateSalesCompare($params),
            'compareMonths' => $this->validateCompareMonths($params),
            'monthlyFullReport' => $this->validateSalesMonthly($params),
            'yearlyFullReport' => $this->validateSalesYearly($params),
            'serviceAnalysis' => $this->validateRangeWithLimit($params),
            'customerTotalPayments' => $this->validateCustomerPayments($params),
            'fallback_error' => ['query' => $params['query'] ?? ''],
            'help' => [],
            default => null,
        };

        if ($normalized === null) {
            return null;
        }

        return [
            'tool' => $tool,
            'intent' => $intent ?? $tool,
            'params' => $normalized,
        ];
    }

    // ... (validation methods remain same, need to include them or be careful)
    // I will use a smaller chunk for replacement to avoid re-pasting huge code.
    // I'll replace from `selectToolViaOllama` to `ollamaSystemPrompt`.

    // Wait, I need to insert `extractYearMonth`.

    private function extractYearMonth(string $text): ?array
    {
        if (preg_match('/(20\d{2})[\-\/](\d{1,2})/', $text, $m)) {
            return ['year' => (int) $m[1], 'month' => (int) $m[2]];
        }
        if (preg_match('/(\d{1,2})[\-\/](20\d{2})/', $text, $m)) {
            return ['year' => (int) $m[2], 'month' => (int) $m[1]];
        }

        return null;
    }

    private function validateFindCustomer(array $params): ?array
    {
        $query = $params['query'] ?? $params['name_or_phone'] ?? null;
        if (! is_string($query)) {
            return null;
        }
        $query = trim($query);
        if ($query === '' || mb_strlen($query) > 200) {
            return null;
        }

        return ['query' => $query];
    }

    private function validateCustomerId(array $params): ?array
    {
        $id = $params['customer_id'] ?? null;
        if (is_string($id) && ctype_digit($id)) {
            $id = (int) $id;
        }
        if (! is_int($id) || $id <= 0) {
            return null;
        }

        return ['customer_id' => $id];
    }

    private function validateInvoice(array $params): ?array
    {
        $invoice = $params['invoice'] ?? $params['invoice_no_or_id'] ?? $params['invoice_id'] ?? null;
        if (! is_string($invoice) && ! is_int($invoice)) {
            return null;
        }
        $invoice = is_int($invoice) ? (string) $invoice : trim($invoice);
        if ($invoice === '' || mb_strlen($invoice) > 50) {
            return null;
        }

        return ['invoice' => $invoice];
    }

    private function validateListInvoicesByCustomer(array $params): ?array
    {
        $base = $this->validateCustomerId($params);
        if (! $base) {
            return null;
        }

        $from = $params['from'] ?? null;
        $to = $params['to'] ?? null;

        if ($from !== null && ! is_string($from)) {
            return null;
        }
        if ($to !== null && ! is_string($to)) {
            return null;
        }

        $out = $base;
        if (is_string($from) && trim($from) !== '') {
            $out['from'] = Carbon::parse($from)->toDateString();
        }
        if (is_string($to) && trim($to) !== '') {
            $out['to'] = Carbon::parse($to)->toDateString();
        }

        return $out;
    }

    private function validateSalesMonthly(array $params): ?array
    {
        $year = $params['year'] ?? null;
        $month = $params['month'] ?? null;

        if (is_string($year) && ctype_digit($year)) {
            $year = (int) $year;
        }
        if (is_string($month) && ctype_digit($month)) {
            $month = (int) $month;
        }

        if (! is_int($year) || $year < 2000 || $year > 2100) {
            return null;
        }
        if (! is_int($month) || $month < 1 || $month > 12) {
            return null;
        }

        return ['year' => $year, 'month' => $month];
    }

    private function validateSalesYearly(array $params): ?array
    {
        $year = $params['year'] ?? null;
        if (is_string($year) && ctype_digit($year)) {
            $year = (int) $year;
        }
        if (! is_int($year) || $year < 2000 || $year > 2100) {
            return null;
        }

        return ['year' => $year];
    }

    private function validateSalesRange(array $params): ?array
    {
        $from = $params['from'] ?? null;
        $to = $params['to'] ?? null;
        if (! is_string($from) || ! is_string($to)) {
            return null;
        }
        $fromDate = Carbon::parse($from)->startOfDay();
        $toDate = Carbon::parse($to)->endOfDay();
        if ($toDate->lt($fromDate)) {
            return null;
        }

        return [
            'from' => $fromDate->toDateString(),
            'to' => $toDate->toDateString(),
        ];
    }

    private function validateLimitOnly(array $params): ?array
    {
        $limit = (int) ($params['limit'] ?? 10);
        if ($limit <= 0) {
            $limit = 10;
        }
        if ($limit > 50) {
            $limit = 50;
        }

        return ['limit' => $limit];
    }

    private function validateRangeWithLimit(array $params): ?array
    {
        $from = $params['from'] ?? null;
        $to = $params['to'] ?? null;
        if (! is_string($from) || ! is_string($to) || $from === '' || $to === '') {
            return null;
        }

        try {
            $fromDate = Carbon::parse($from)->toDateString();
            $toDate = Carbon::parse($to)->toDateString();
        } catch (\Throwable $e) {
            return null;
        }

        $limit = (int) ($params['limit'] ?? 5);
        if ($limit <= 0) {
            $limit = 5;
        }
        if ($limit > 50) {
            $limit = 50;
        }

        return [
            'from' => $fromDate,
            'to' => $toDate,
            'limit' => $limit,
        ];
    }

    private function validateSalesCompare(array $params): ?array
    {
        $mode = (string) ($params['mode'] ?? '');
        if (! in_array($mode, ['this_vs_last_month', 'this_year_to_date_vs_last_year'], true)) {
            $mode = 'this_vs_last_month';
        }

        return ['mode' => $mode];
    }

    private function validateCompareMonths(array $params): ?array
    {
        $months = (int) ($params['months'] ?? 6);
        if ($months < 3) {
            $months = 3;
        }
        if ($months > 24) {
            $months = 24;
        }

        return ['months' => $months];
    }

    private function validateCustomerPayments(array $params): ?array
    {
        $customerId = $params['customer_id'] ?? null;
        if (! is_string($customerId) && ! is_int($customerId)) {
            return null;
        }
        $customerId = (int) $customerId;
        if ($customerId <= 0) {
            return null;
        }

        $out = ['customer_id' => $customerId];
        if (isset($params['from']) && is_string($params['from']) && $params['from'] !== '') {
            try {
                $out['from'] = Carbon::parse($params['from'])->toDateString();
            } catch (\Throwable $e) {
            }
        }
        if (isset($params['to']) && is_string($params['to']) && $params['to'] !== '') {
            try {
                $out['to'] = Carbon::parse($params['to'])->toDateString();
            } catch (\Throwable $e) {
            }
        }

        return $out;
    }

    private function toolFindCustomer(array $params): array
    {
        $query = $params['query'];

        $customers = Customer::query()
            ->where(function ($q) use ($query) {
                $q->where('name', 'like', '%'.$query.'%')
                    ->orWhere('phone', 'like', '%'.$query.'%')
                    ->orWhere('whatsapp', 'like', '%'.$query.'%')
                    ->orWhere('business_name', 'like', '%'.$query.'%');
            })
            ->latest()
            ->limit(10)
            ->get(['id', 'name', 'phone', 'business_name', 'created_at']);

        if ($customers->isEmpty()) {
            return [
                'request_type' => 'findCustomer',
                'params' => $params,
                'text' => __('system_assistant.responses.no_results'),
                'summary' => 'no_results',
                'choices' => [],
            ];
        }

        if ($customers->count() === 1) {
            return $this->toolCustomerSummary(['customer_id' => $customers->first()->id]);
        }

        $choices = $customers->map(function ($c) {
            return [
                'label' => $c->name,
                'payload' => __('system_assistant.payloads.show_summary', ['id' => $c->id]),
                'meta' => $c->phone,
            ];
        })->toArray();

        return [
            'request_type' => 'findCustomer',
            'params' => $params,
            'text' => __('system_assistant.responses.found_customers', ['count' => $customers->count(), 'query' => $query]),
            'summary' => 'multiple_results',
            'choices' => $choices,
        ];
    }

    private function toolCustomerSummary(array $params): array
    {
        $customerId = $params['customer_id'];

        $customer = Customer::with([
            'serviceSubscriptions.service',
            'serviceSubscriptions.renewals',
            'serviceSubscriptions.payments',
        ])->find($customerId);

        if (! $customer) {
            return [
                'request_type' => 'customerSummary',
                'params' => $params,
                'text' => __('system_assistant.responses.customer_not_found', ['id' => $customerId]),
                'summary' => 'not_found',
                'choices' => [],
            ];
        }

        $subs = $customer->serviceSubscriptions;
        $subsCount = $subs->count();

        $subsNetTotal = $subs->sum(function (ServiceSubscription $s) {
            return max(0, (int) $s->price - (int) ($s->discount ?? 0));
        });

        $paidTotal = $subs->sum(function (ServiceSubscription $s) {
            return (int) $s->payments->sum('amount');
        });

        $remainingTotal = $subs->sum(function (ServiceSubscription $s) {
            $net = max(0, (int) $s->price - (int) ($s->discount ?? 0));
            $paid = (int) $s->payments->sum('amount');

            return max(0, $net - $paid);
        });

        $renewals = $subs->flatMap->renewals;
        $renewalsCount = $renewals->count();
        $renewalsTotal = (int) $renewals->sum('amount');

        $lastSub = $subs->sortByDesc('created_at')->first();
        $lastRenewal = $renewals->sortByDesc('renewal_date')->first();

        $lastInvoice = null;
        if ($lastRenewal && $lastSub) {
            $lastInvoice = $lastRenewal->created_at && $lastRenewal->created_at->gt($lastSub->created_at)
                ? ['type' => 'renewal', 'number' => $lastRenewal->invoice_number, 'date' => $lastRenewal->renewal_date]
                : ['type' => 'subscription', 'number' => $lastSub->invoice_number, 'date' => $lastSub->created_at];
        } elseif ($lastRenewal) {
            $lastInvoice = ['type' => 'renewal', 'number' => $lastRenewal->invoice_number, 'date' => $lastRenewal->renewal_date];
        } elseif ($lastSub) {
            $lastInvoice = ['type' => 'subscription', 'number' => $lastSub->invoice_number, 'date' => $lastSub->created_at];
        }

        $lines = [];
        $lines[] = '### 👤 '.__('system_assistant.suggestions.customer_summary');
        $lines[] = '| '.__('common.type').' | '.__('common.details').' |';
        $lines[] = '| :--- | :--- |';
        $lines[] = '| **'.__('subscriptions.customer_name')."** | {$customer->name} |";
        $lines[] = '| **'.__('subscriptions.phone')."** | {$customer->phone} |";
        if ($customer->whatsapp) {
            $lines[] = '| **'.__('subscriptions.whatsapp')."** | {$customer->whatsapp} |";
        }
        if ($customer->business_name) {
            $lines[] = '| **'.__('subscriptions.business_name')."** | {$customer->business_name} |";
        }
        $lines[] = '| **'.__('system_assistant.summary_labels.service_invoices')."** | {$subsCount} |";
        $lines[] = '| **'.__('system_assistant.summary_labels.renewals_count')."** | {$renewalsCount} |";
        $lines[] = '| **'.__('system_assistant.summary_labels.total').'** | '.number_format($subsNetTotal).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.paid').'** | '.number_format($paidTotal).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.remaining').'** | '.number_format($remainingTotal).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.renewals_total').'** | '.number_format($renewalsTotal).' |';

        if ($lastInvoice) {
            $date = $lastInvoice['date'] instanceof Carbon ? $lastInvoice['date']->format('Y-m-d') : (string) $lastInvoice['date'];
            $lines[] = '| **'.__('system_assistant.chips.last_invoices')."** | {$lastInvoice['number']} ({$date}) |";
        }

        $choices = [];
        if ($lastInvoice) {
            $choices[] = [
                'label' => __('system_assistant.suggestions.show_last_invoice', ['number' => $lastInvoice['number']]),
                'payload' => __('system_assistant.payloads.show_last_invoice', ['number' => $lastInvoice['number']]),
            ];
        }
        $choices[] = [
            'label' => __('system_assistant.suggestions.list_invoices'),
            'payload' => __('system_assistant.payloads.list_invoices', ['id' => $customer->id]),
        ];
        $choices[] = [
            'label' => __('system_assistant.suggestions.show_renewals'),
            'payload' => __('system_assistant.payloads.show_renewals', ['id' => $customer->id]),
        ];

        return [
            'request_type' => 'customerSummary',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "customer_id={$customer->id}",
            'choices' => $choices,
        ];
    }

    private function toolGetInvoice(array $params): array
    {
        $invoice = $params['invoice'];

        $sub = null;
        $renewal = null;

        if (ctype_digit($invoice)) {
            $sub = ServiceSubscription::with(['customer', 'service', 'items', 'payments'])->find((int) $invoice);
            if (! $sub) {
                $renewal = SubscriptionRenewal::with(['serviceSubscription.customer', 'serviceSubscription.service', 'creator'])->find((int) $invoice);
            }
        } else {
            $sub = ServiceSubscription::with(['customer', 'service', 'items', 'payments'])
                ->where('invoice_number', $invoice)
                ->first();

            if (! $sub) {
                $renewal = SubscriptionRenewal::with(['serviceSubscription.customer', 'serviceSubscription.service', 'creator'])
                    ->where('invoice_number', $invoice)
                    ->first();
            }
        }

        if (! $sub && ! $renewal) {
            return [
                'request_type' => 'getInvoice',
                'params' => $params,
                'text' => __('system_assistant.responses.invoice_not_found', ['invoice' => $invoice]),
                'summary' => 'not_found',
                'choices' => [],
            ];
        }

        if ($renewal) {
            $customer = $renewal->serviceSubscription?->customer;
            $service = $renewal->serviceSubscription?->service;

            $lines = [];
            $lines[] = '### 📄 '.__('system_assistant.suggestions.show_last_invoice', ['number' => $renewal->invoice_number]);

            $lines[] = '| '.__('system_assistant.table_headers.type').' | '.__('common.details').' |';
            $lines[] = '| :--- | :--- |';
            $lines[] = '| **'.__('system_assistant.table_headers.type').'** | '.__('system_assistant.invoice_types.renewal').' |';
            $lines[] = '| **'.__('system_assistant.table_headers.invoice_number')."** | {$renewal->invoice_number} |";
            $lines[] = '| **'.__('system_assistant.table_headers.date').'** | '.(is_object($renewal->renewal_date) ? $renewal->renewal_date->translatedFormat('Y-m-d') : (string) $renewal->renewal_date).' |';
            $lines[] = '| **'.__('system_assistant.table_headers.amount').'** | '.number_format($renewal->amount).' |';

            if ($customer) {
                $lines[] = '| **'.__('subscriptions.customer_name')."** | {$customer->name} |";
                $lines[] = '| **'.__('subscriptions.phone')."** | {$customer->phone} |";
            }
            if ($service) {
                $lines[] = '| **'.__('system_assistant.table_headers.service')."** | {$service->name} ({$service->type}) |";
            }
            if ($renewal->note) {
                $lines[] = '| **'.__('system_assistant.table_headers.note')."** | {$renewal->note} |";
            }
            if ($renewal->creator) {
                $lines[] = '| **'.__('system_assistant.table_headers.created_by')."** | {$renewal->creator->name} |";
            }

            return [
                'request_type' => 'getInvoice',
                'params' => $params,
                'text' => implode("\n", $lines),
                'summary' => "renewal_invoice={$renewal->invoice_number}, customer_id=".($customer ? $customer->id : ''),
                'choices' => $customer ? [
                    [
                        'label' => __('system_assistant.suggestions.customer_summary'),
                        'payload' => __('system_assistant.payloads.show_summary', ['id' => $customer->id]),
                    ],
                ] : [],
            ];
        }

        $customer = $sub->customer;
        $service = $sub->service;

        $net = max(0, (int) $sub->price - (int) ($sub->discount ?? 0));
        $paid = (int) $sub->payments->sum('amount');
        $remaining = max(0, $net - $paid);

        $lines = [];
        $lines[] = '### 📄 '.__('system_assistant.suggestions.show_last_invoice', ['number' => $sub->invoice_number]);

        $lines[] = '| '.__('system_assistant.table_headers.type').' | '.__('common.details').' |';
        $lines[] = '| :--- | :--- |';
        $lines[] = '| **'.__('system_assistant.table_headers.type').'** | '.__('system_assistant.invoice_types.service').' |';
        $lines[] = '| **'.__('system_assistant.table_headers.invoice_number')."** | {$sub->invoice_number} |";
        $lines[] = '| **'.__('system_assistant.table_headers.date').'** | '.($sub->created_at?->translatedFormat('Y-m-d h:i A') ?? '').' |';

        if ($customer) {
            $lines[] = '| **'.__('subscriptions.customer_name')."** | {$customer->name} |";
            $lines[] = '| **'.__('subscriptions.phone')."** | {$customer->phone} |";
        }
        if ($service) {
            $lines[] = '| **'.__('system_assistant.table_headers.service')."** | {$service->name} ({$service->type}) |";
        }

        $lines[] = '| **'.__('system_assistant.summary_labels.total').'** | '.number_format($sub->price).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.discount').'** | '.number_format((int) ($sub->discount ?? 0)).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.net').'** | '.number_format($net).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.paid').'** | '.number_format($paid).' |';
        $lines[] = '| **'.__('system_assistant.summary_labels.remaining').'** | '.number_format($remaining).' |';

        if ($sub->items && $sub->items->isNotEmpty()) {
            $lines[] = '';
            $lines[] = '#### '.__('system_assistant.invoice_details.items');
            $lines[] = '| # | '.__('subscriptions.service_details').' | '.__('subscriptions.quantity').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- | :--- |';

            $i = 1;
            foreach ($sub->items->take(10) as $item) {
                $lines[] = "| {$i} | {$item->service_detail} | {$item->quantity} | ".number_format($item->subtotal).' |';
                $i++;
            }
            if ($sub->items->count() > 10) {
                $lines[] = '| ... | ... | ... | ... |';
            }
        }

        if ($sub->payments && $sub->payments->isNotEmpty()) {
            $lines[] = '';
            $lines[] = '#### '.__('system_assistant.invoice_details.payments');
            $lines[] = '| # | '.__('subscriptions.payment_method').' | '.__('system_assistant.table_headers.date').' | '.__('subscriptions.price').' |';
            $lines[] = '| :--- | :--- | :--- | :--- |';

            $i = 1;
            foreach ($sub->payments->take(10) as $p) {
                $date = is_object($p->payment_date) ? $p->payment_date->translatedFormat('Y-m-d') : (string) $p->payment_date;
                $lines[] = "| {$i} | {$p->payment_method} | {$date} | ".number_format($p->amount).' |';
                $i++;
            }
            if ($sub->payments->count() > 10) {
                $lines[] = '| ... | ... | ... | ... |';
            }
        }

        $choices = [];
        if ($customer) {
            $choices[] = [
                'label' => __('system_assistant.suggestions.customer_summary'),
                'payload' => __('system_assistant.payloads.show_summary', ['id' => $customer->id]),
            ];
        }

        return [
            'request_type' => 'getInvoice',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "subscription_invoice={$sub->invoice_number}, customer_id=".($customer ? $customer->id : ''),
            'choices' => $choices,
        ];
    }

    private function toolListInvoicesByCustomer(array $params): array
    {
        $customerId = $params['customer_id'];
        $from = $params['from'] ?? null;
        $to = $params['to'] ?? null;

        $customer = Customer::find($customerId);
        if (! $customer) {
            return [
                'request_type' => 'listInvoicesByCustomer',
                'params' => $params,
                'text' => __('system_assistant.responses.customer_not_found', ['id' => $customerId]),
                'summary' => 'not_found',
                'choices' => [],
            ];
        }

        $subsQ = ServiceSubscription::query()
            ->with(['service'])
            ->where('customer_id', $customerId);

        if ($from) {
            $subsQ->whereDate('created_at', '>=', $from);
        }
        if ($to) {
            $subsQ->whereDate('created_at', '<=', $to);
        }

        $subs = $subsQ->latest()->limit(30)->get();

        $renewalsQ = SubscriptionRenewal::query()
            ->with(['serviceSubscription.service'])
            ->whereHas('serviceSubscription', fn ($q) => $q->where('customer_id', $customerId));

        if ($from) {
            $renewalsQ->whereDate('created_at', '>=', $from);
        }
        if ($to) {
            $renewalsQ->whereDate('created_at', '<=', $to);
        }

        $renewals = $renewalsQ->latest()->limit(30)->get();

        $lines = [];
        $lines[] = '### 🧾 '.__('system_assistant.suggestions.list_invoices');
        $lines[] = '**'.__('subscriptions.customer_name').":** {$customer->name} ({$customer->phone})";

        if ($from || $to) {
            $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $from ?? '...', 'to' => $to ?? '...']).'**';
        }
        $lines[] = '';

        if ($subs->isNotEmpty()) {
            $lines[] = '#### '.__('system_assistant.customer_report.service_invoices_count', ['count' => $subs->count()]);
            $lines[] = '| # | '.__('system_assistant.table_headers.invoice_number').' | '.__('system_assistant.table_headers.date').' | '.__('system_assistant.table_headers.service').' | '.__('system_assistant.table_headers.amount').' |';
            $lines[] = '| :--- | :--- | :--- | :--- | :--- |';

            $i = 1;
            foreach ($subs as $s) {
                $date = $s->created_at?->translatedFormat('Y-m-d');
                $lines[] = "| {$i} | {$s->invoice_number} | {$date} | ".($s->service?->name ?? '').' | '.number_format($s->price).' |';
                $i++;
            }
        } else {
            $lines[] = '#### '.__('system_assistant.customer_report.service_invoices_count', ['count' => 0]);
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        }

        $lines[] = '';

        if ($renewals->isNotEmpty()) {
            $lines[] = '#### '.__('system_assistant.customer_report.renewal_invoices_count', ['count' => $renewals->count()]);
            $lines[] = '| # | '.__('system_assistant.table_headers.invoice_number').' | '.__('system_assistant.table_headers.date').' | '.__('system_assistant.table_headers.service').' | '.__('system_assistant.table_headers.amount').' |';
            $lines[] = '| :--- | :--- | :--- | :--- | :--- |';

            $i = 1;
            foreach ($renewals as $r) {
                $date = is_object($r->renewal_date) ? $r->renewal_date->translatedFormat('Y-m-d') : (string) $r->renewal_date;
                $lines[] = "| {$i} | {$r->invoice_number} | {$date} | ".($r->serviceSubscription?->service?->name ?? '').' | '.number_format($r->amount).' |';
                $i++;
            }
        } else {
            $lines[] = '#### '.__('system_assistant.customer_report.renewal_invoices_count', ['count' => 0]);
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        }

        $choices = [];
        $first = $subs->first();
        if ($first) {
            $choices[] = [
                'label' => __('system_assistant.suggestions.open_latest_service_invoice', ['number' => $first->invoice_number]),
                'payload' => __('system_assistant.payloads.open_latest_service_invoice', ['number' => $first->invoice_number]),
            ];
        }
        $firstR = $renewals->first();
        if ($firstR) {
            $choices[] = [
                'label' => __('system_assistant.suggestions.open_latest_renewal_invoice', ['number' => $firstR->invoice_number]),
                'payload' => __('system_assistant.payloads.open_latest_renewal_invoice', ['number' => $firstR->invoice_number]),
            ];
        }

        return [
            'request_type' => 'listInvoicesByCustomer',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "customer_id={$customerId}",
            'choices' => $choices,
        ];
    }

    private function toolGetCustomerRenewals(array $params): array
    {
        $customerId = $params['customer_id'];

        $customer = Customer::find($customerId);
        if (! $customer) {
            return [
                'request_type' => 'getCustomerRenewals',
                'params' => $params,
                'text' => __('system_assistant.responses.customer_not_found', ['id' => $customerId]),
                'summary' => 'not_found',
                'choices' => [],
            ];
        }

        $renewals = SubscriptionRenewal::query()
            ->with(['serviceSubscription.service'])
            ->whereHas('serviceSubscription', fn ($q) => $q->where('customer_id', $customerId))
            ->latest()
            ->limit(50)
            ->get();

        if ($renewals->isEmpty()) {
            return [
                'request_type' => 'getCustomerRenewals',
                'params' => $params,
                'text' => __('system_assistant.responses.no_renewals', ['name' => $customer->name, 'phone' => $customer->phone]),
                'summary' => 'no_renewals',
                'choices' => [
                    [
                        'label' => __('system_assistant.suggestions.customer_summary'),
                        'payload' => __('system_assistant.payloads.show_summary', ['id' => $customer->id]),
                    ],
                ],
            ];
        }

        $total = (int) $renewals->sum('amount');
        $lines = [];
        $lines[] = '### 🔄 '.__('system_assistant.suggestions.show_renewals');
        $lines[] = '**'.__('subscriptions.customer_name').":** {$customer->name} ({$customer->phone})";
        $lines[] = '**'.__('system_assistant.customer_report.renewals_count', ['count' => $renewals->count()]).'**';
        $lines[] = '**'.__('system_assistant.customer_report.renewals_total_amount', ['amount' => number_format($total)]).'**';

        $lines[] = '';
        $lines[] = '| # | '.__('system_assistant.table_headers.invoice_number').' | '.__('system_assistant.table_headers.date').' | '.__('system_assistant.table_headers.service').' | '.__('system_assistant.table_headers.amount').' |';
        $lines[] = '| :--- | :--- | :--- | :--- | :--- |';

        $i = 1;
        foreach ($renewals as $r) {
            $date = is_object($r->renewal_date) ? $r->renewal_date->translatedFormat('Y-m-d') : (string) $r->renewal_date;
            $lines[] = "| {$i} | {$r->invoice_number} | {$date} | ".($r->serviceSubscription?->service?->name ?? '').' | '.number_format($r->amount).' |';
            $i++;
        }

        $choices = [
            [
                'label' => __('system_assistant.suggestions.customer_summary'),
                'payload' => __('system_assistant.payloads.show_summary', ['id' => $customer->id]),
            ],
            [
                'label' => __('system_assistant.suggestions.open_latest_renewal', ['number' => $renewals->first()->invoice_number]),
                'payload' => __('system_assistant.payloads.open_latest_renewal', ['number' => $renewals->first()->invoice_number]),
            ],
        ];

        return [
            'request_type' => 'getCustomerRenewals',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "customer_id={$customerId}",
            'choices' => $choices,
        ];
    }

    private function toolSalesReportMonthly(array $params): array
    {
        $year = (int) ($params['year'] ?? now()->year);
        $month = (int) ($params['month'] ?? now()->month);

        $start = Carbon::create($year, $month, 1)->startOfMonth();
        $end = $start->copy()->endOfMonth();

        // Current Month Data
        $subscriptions = ServiceSubscription::whereBetween('created_at', [$start, $end])->sum('price');
        $renewals = SubscriptionRenewal::whereBetween('renewal_date', [$start, $end])->sum('amount');
        $total = $subscriptions + $renewals;
        $countSub = ServiceSubscription::whereBetween('created_at', [$start, $end])->count();
        $countRen = SubscriptionRenewal::whereBetween('renewal_date', [$start, $end])->count();

        // Previous Month Data for Comparison
        $prevStart = $start->copy()->subMonth();
        $prevEnd = $prevStart->copy()->endOfMonth();
        $prevSub = ServiceSubscription::whereBetween('created_at', [$prevStart, $prevEnd])->sum('price');
        $prevRen = SubscriptionRenewal::whereBetween('renewal_date', [$prevStart, $prevEnd])->sum('amount');
        $prevTotal = $prevSub + $prevRen;

        // Analysis
        $growth = 0;
        if ($prevTotal > 0) {
            $growth = (($total - $prevTotal) / $prevTotal) * 100;
        } elseif ($total > 0) {
            $growth = 100; // From 0 to something is 100% growth effectively
        }

        $growthStr = number_format($growth, 1).'%';
        $trend = $growth > 0
            ? __('system_assistant.sales_report.trend_increase')
            : ($growth < 0 ? __('system_assistant.sales_report.trend_decrease') : __('system_assistant.sales_report.trend_stable'));

        // Top Customer (Simple Analysis)
        $topCustomer = ServiceSubscription::selectRaw('customer_id, sum(price) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('customer_id')
            ->orderByDesc('total')
            ->with('customer')
            ->first();

        $lines = [];
        $lines[] = '### 📊 '.__('system_assistant.sales_report.monthly_title', ['date' => $start->format('F Y')]);

        $lines[] = '| '.__('system_assistant.sales_report.metric').' | '.__('system_assistant.sales_report.value').' |';
        $lines[] = '| :--- | :--- |';
        $lines[] = '| **'.__('system_assistant.sales_report.total_revenue').'** | '.number_format($total).' |';
        $lines[] = '| **'.__('system_assistant.sales_report.comparison')." ({$prevStart->format('M')})** | {$trend} {$growthStr} |";
        $lines[] = '| **'.__('system_assistant.sales_report.new_subscriptions')."** | {$countSub} (".number_format($subscriptions).') |';
        $lines[] = '| **'.__('system_assistant.sales_report.renewals')."** | {$countRen} (".number_format($renewals).') |';

        if ($topCustomer && $topCustomer->customer) {
            $lines[] = '| **'.__('system_assistant.sales_report.top_customer')."** | {$topCustomer->customer->name} (".number_format($topCustomer->total).') |';
        }

        $analysisText = implode("\n", $lines);

        return [
            'request_type' => 'salesReportMonthly',
            'params' => $params,
            'text' => $analysisText,
            'summary' => "Total: {$total}",
            'data' => [
                'period' => $start->format('F Y'),
                'total_revenue' => $total,
                'prev_revenue' => $prevTotal,
                'growth_percentage' => $growth,
                'subscriptions_count' => $countSub,
                'renewals_count' => $countRen,
                'top_customer' => $topCustomer ? $topCustomer->customer->name : null,
            ],
        ];
    }

    private function toolSalesReportYearly(array $params): array
    {
        $year = $params['year'];
        $start = Carbon::create($year, 1, 1)->startOfYear();
        $end = $start->copy()->endOfYear();

        $report = $this->salesReportBetween($start, $end, "مبيعات سنة {$year}");

        $byMonth = ServiceSubscription::query()
            ->selectRaw('MONTH(created_at) as m, SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('m')
            ->orderBy('m')
            ->pluck('total', 'm')
            ->toArray();

        $renewalsByMonth = SubscriptionRenewal::query()
            ->selectRaw('MONTH(created_at) as m, SUM(amount) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('m')
            ->orderBy('m')
            ->pluck('total', 'm')
            ->toArray();

        $monthlyBreakdown = [];
        $lines = explode("\n", $report['text']);
        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.monthly_breakdown');
        $lines[] = '| '.__('system_assistant.sales_report.month').' | '.__('system_assistant.sales_report.total_sales').' |';
        $lines[] = '| :--- | :--- |';

        for ($m = 1; $m <= 12; $m++) {
            $subTotal = (int) ($byMonth[$m] ?? 0);
            $renTotal = (int) ($renewalsByMonth[$m] ?? 0);
            $sum = $subTotal + $renTotal;

            // Format month name (e.g., "01 - January")
            $monthName = Carbon::create($year, $m, 1)->translatedFormat('m - F');

            $lines[] = "| {$monthName} | ".number_format($sum).' |';
            $monthlyBreakdown[$m] = $sum;
        }

        $report['text'] = implode("\n", $lines);
        $report['data']['monthly_breakdown'] = $monthlyBreakdown;

        return $report;
    }

    private function toolSalesReportRange(array $params): array
    {
        $start = Carbon::parse($params['from'])->startOfDay();
        $end = Carbon::parse($params['to'])->endOfDay();

        return $this->salesReportBetween($start, $end, "مبيعات {$params['from']} → {$params['to']}");
    }

    private function salesReportBetween(Carbon $start, Carbon $end, string $title): array
    {
        $subsTotal = (int) ServiceSubscription::query()
            ->whereBetween('created_at', [$start, $end])
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->value('total') ?? 0;

        $subsCount = (int) ServiceSubscription::whereBetween('created_at', [$start, $end])->count();

        $renewalsTotal = (int) SubscriptionRenewal::query()
            ->whereBetween('created_at', [$start, $end])
            ->sum('amount');

        $renewalsCount = (int) SubscriptionRenewal::whereBetween('created_at', [$start, $end])->count();

        $grandTotal = $subsTotal + $renewalsTotal;
        $invoiceCount = $subsCount + $renewalsCount;

        $topCustomers = ServiceSubscription::query()
            ->select('customer_id')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('customer_id')
            ->orderByDesc('total')
            ->limit(5)
            ->get()
            ->map(function ($row) {
                $customer = Customer::find($row->customer_id);

                return [
                    'customer_id' => (int) $row->customer_id,
                    'name' => $customer?->name,
                    'phone' => $customer?->phone,
                    'total' => (int) $row->total,
                ];
            });

        $topServices = ServiceSubscription::query()
            ->select('service_id')
            ->selectRaw('COUNT(*) as cnt')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('service_id')
            ->orderByDesc('cnt')
            ->limit(5)
            ->get()
            ->map(function ($row) {
                $service = Service::find($row->service_id);

                return [
                    'service_id' => (int) $row->service_id,
                    'name' => $service?->name,
                    'type' => $service?->type,
                    'count' => (int) $row->cnt,
                    'total' => (int) $row->total,
                ];
            });

        $lines = [];
        $lines[] = "### 📊 {$title}";
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        $lines[] = '#### '.__('system_assistant.sales_report.summary');
        $lines[] = '| '.__('system_assistant.sales_report.metric').' | '.__('system_assistant.sales_report.value').' |';
        $lines[] = '| :--- | :--- |';
        $lines[] = '| **'.__('system_assistant.sales_report.total_sales').'** | '.number_format($grandTotal).' |';
        $lines[] = '| **'.__('system_assistant.sales_report.invoices_count')."** | {$invoiceCount} |";
        $lines[] = '| **'.__('system_assistant.sales_report.service_invoices')."** | {$subsCount} (".number_format($subsTotal).') |';
        $lines[] = '| **'.__('system_assistant.sales_report.renewal_invoices')."** | {$renewalsCount} (".number_format($renewalsTotal).') |';

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_customers');
        if ($topCustomers->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('subscriptions.customer_name').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- |';
            $i = 1;
            foreach ($topCustomers as $c) {
                $label = trim(($c['name'] ?? '').' '.(($c['phone'] ?? '') ? "({$c['phone']})" : ''));
                $label = $label !== '' ? $label : __('system_assistant.sales_report.customer_label', ['id' => $c['customer_id']]);
                $lines[] = "| {$i} | {$label} | ".number_format($c['total']).' |';
                $i++;
            }
        }

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_services');
        if ($topServices->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('pos.cards.services').' | '.__('subscriptions.quantity').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- | :--- |';
            $i = 1;
            foreach ($topServices as $s) {
                $label = ($s['name'] ?? '') !== '' ? "{$s['name']} ({$s['type']})" : __('system_assistant.sales_report.service_label', ['id' => $s['service_id']]);
                $lines[] = "| {$i} | {$label} | {$s['count']} | ".number_format($s['total']).' |';
                $i++;
            }
        }

        $data = [
            'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
            'totals' => [
                'sales_total' => $grandTotal,
                'invoices_count' => $invoiceCount,
                'services_total' => $subsTotal,
                'renewals_total' => $renewalsTotal,
            ],
            'top_customers' => $topCustomers->toArray(),
            'top_services' => $topServices->toArray(),
        ];

        return [
            'request_type' => 'salesReport',
            'params' => [
                'from' => $start->toDateString(),
                'to' => $end->toDateString(),
            ],
            'text' => implode("\n", $lines),
            'summary' => "total={$grandTotal}, invoices={$invoiceCount}",
            'data' => $data,
            'choices' => [],
        ];
    }

    private function toolTopCustomers(array $params): array
    {
        $start = Carbon::parse($params['from'])->startOfDay();
        $end = Carbon::parse($params['to'])->endOfDay();
        $limit = (int) ($params['limit'] ?? 5);

        $rows = ServiceSubscription::query()
            ->select('customer_id')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('customer_id')
            ->orderByDesc('total')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $customer = Customer::find($row->customer_id);

                return [
                    'customer_id' => (int) $row->customer_id,
                    'name' => $customer?->name,
                    'phone' => $customer?->phone,
                    'total' => (int) $row->total,
                ];
            });

        $lines = [];
        $lines[] = '### '.__('system_assistant.suggestions.top_customers');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        if ($rows->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('subscriptions.customer_name').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- |';
            $i = 1;
            foreach ($rows as $c) {
                $label = trim(($c['name'] ?? '').' '.(($c['phone'] ?? '') ? "({$c['phone']})" : ''));
                $label = $label !== '' ? $label : __('system_assistant.sales_report.customer_label', ['id' => $c['customer_id']]);
                $lines[] = "| {$i} | {$label} | ".number_format($c['total']).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'topCustomers',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "from={$start->toDateString()},to={$end->toDateString()}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'top_customers' => $rows->toArray(),
            ],
            'choices' => [],
        ];
    }

    private function toolTopServicesSoldThisMonth(): array
    {
        $start = now()->startOfMonth()->startOfDay();
        $end = now()->endOfMonth()->endOfDay();

        $rows = $this->topServicesBetween($start, $end, 10);

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.top_services_sold');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        if ($rows === []) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('system_assistant.table_headers.service').' | '.__('system_assistant.sales_report.invoices_count').' | '.__('system_assistant.table_headers.amount').' |';
            $lines[] = '| :--- | :--- | ---: | ---: |';
            $i = 1;
            foreach ($rows as $s) {
                $name = (string) ($s['name'] ?? '');
                $label = $name !== '' ? $name : __('system_assistant.sales_report.service_label', ['id' => $s['service_id'] ?? 0]);
                $lines[] = "| {$i} | {$label} | ".number_format((int) ($s['count'] ?? 0)).' | '.number_format((int) ($s['total'] ?? 0)).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'topServicesSoldThisMonth',
            'params' => [],
            'text' => implode("\n", $lines),
            'summary' => "from={$start->toDateString()},to={$end->toDateString()}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'top_services' => $rows,
            ],
            'choices' => [],
        ];
    }

    private function toolTopStageAcademyCoursesSoldThisMonth(): array
    {
        $start = now()->startOfMonth()->startOfDay();
        $end = now()->endOfMonth()->endOfDay();

        if (! Schema::hasTable('stage_academy_activations')) {
            return [
                'request_type' => 'topStageAcademyCoursesSoldThisMonth',
                'params' => [],
                'text' => '_'.__('system_assistant.responses.no_results').'_',
                'summary' => 'no_stage_academy_activations_table',
                'choices' => [],
            ];
        }

        $limit = 10;
        $nameColumn = app()->getLocale() === 'ar' ? 'name_ar' : 'name_en';

        $rows = StageAcademyActivation::query()
            ->select('course_id')
            ->selectRaw('COUNT(*) as cnt')
            ->selectRaw('COALESCE(SUM(price), 0) as total')
            ->whereBetween('created_at', [$start->copy()->utc(), $end->copy()->utc()])
            ->groupBy('course_id')
            ->orderByDesc('cnt')
            ->limit($limit)
            ->get()
            ->map(function ($row) use ($nameColumn) {
                $course = StageAcademyCourse::query()->find($row->course_id);
                $name = $course?->{$nameColumn} ?? null;

                return [
                    'course_id' => (int) $row->course_id,
                    'name' => $name,
                    'count' => (int) $row->cnt,
                    'total' => (int) $row->total,
                ];
            })
            ->toArray();

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.top_stage_academy_courses_sold');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        if ($rows === []) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | كورس | عدد التفعيلات | '.__('system_assistant.table_headers.amount').' |';
            $lines[] = '| :--- | :--- | ---: | ---: |';
            $i = 1;
            foreach ($rows as $c) {
                $name = (string) ($c['name'] ?? '');
                $label = $name !== '' ? $name : ('Course #'.((int) ($c['course_id'] ?? 0)));
                $lines[] = "| {$i} | {$label} | ".number_format((int) ($c['count'] ?? 0)).' | '.number_format((int) ($c['total'] ?? 0)).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'topStageAcademyCoursesSoldThisMonth',
            'params' => [],
            'text' => implode("\n", $lines),
            'summary' => "from={$start->toDateString()},to={$end->toDateString()}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'top_courses' => $rows,
            ],
            'choices' => [],
        ];
    }

    private function toolEmployeesMonthlyReportThisMonth(): array
    {
        $startLocal = now()->startOfMonth()->startOfDay();
        $endLocal = now()->endOfMonth()->endOfDay();
        $fromUtc = $startLocal->copy()->utc();
        $toUtc = $endLocal->copy()->utc();

        $employees = Employee::query()
            ->orderBy('full_name')
            ->get(['id', 'user_id', 'full_name', 'base_salary', 'is_active']);

        if ($employees->isEmpty()) {
            return [
                'request_type' => 'employeesMonthlyReportThisMonth',
                'params' => [],
                'text' => '_'.__('system_assistant.responses.no_results').'_',
                'summary' => 'no_employees',
                'choices' => [],
            ];
        }

        $employeesById = $employees->keyBy('id');
        $employeesByUserId = $employees
            ->filter(fn (Employee $e) => (int) ($e->user_id ?? 0) > 0)
            ->keyBy('user_id');

        $stats = [];
        foreach ($employees as $e) {
            $stats[$e->id] = [
                'employee_id' => $e->id,
                'employee_name' => (string) ($e->full_name ?? ''),
                'sales_count' => 0,
                'sales_total' => 0,
                'consultations_count' => 0,
                'consultations_total' => 0,
                'activations_count' => 0,
                'activations_total' => 0,
                'absences_count' => 0,
                'absences_total' => 0,
                'lateness_count' => 0,
                'lateness_total' => 0,
            ];
        }

        $subs = ServiceSubscription::query()
            ->whereBetween('created_at', [$fromUtc, $toUtc])
            ->get(['id', 'sales_employee_id', 'created_by', 'price', 'discount']);

        foreach ($subs as $sub) {
            $employeeId = (int) ($sub->sales_employee_id ?? 0);
            if ($employeeId <= 0) {
                $creatorId = (int) ($sub->created_by ?? 0);
                $employeeId = (int) ($employeesByUserId->get($creatorId)?->id ?? 0);
            }
            if ($employeeId <= 0 || ! isset($stats[$employeeId])) {
                continue;
            }

            $net = (int) max(0, ((int) ($sub->price ?? 0) - (int) ($sub->discount ?? 0)));
            $stats[$employeeId]['sales_count']++;
            $stats[$employeeId]['sales_total'] += $net;
        }

        $taskRows = DB::table('operations_tasks')
            ->join('operations_order_items', 'operations_order_items.id', '=', 'operations_tasks.order_item_id')
            ->join('operations_orders', 'operations_orders.id', '=', 'operations_order_items.order_id')
            ->join('service_subscriptions', 'service_subscriptions.id', '=', 'operations_orders.service_subscription_id')
            ->join('services', 'services.id', '=', 'service_subscriptions.service_id')
            ->where('operations_tasks.status', 'done')
            ->whereNotNull('operations_tasks.completed_by_user_id')
            ->where('services.type', 'one_time_service')
            ->whereRaw('COALESCE(operations_tasks.completed_at, operations_tasks.updated_at, operations_tasks.created_at) BETWEEN ? AND ?', [$fromUtc, $toUtc])
            ->get([
                'operations_tasks.completed_by_user_id as completed_by_user_id',
                'service_subscriptions.price as price',
                'service_subscriptions.discount as discount',
                'service_subscriptions.consultations_count as consultations_count',
            ]);

        foreach ($taskRows as $row) {
            $userId = (int) ($row->completed_by_user_id ?? 0);
            $employeeId = (int) ($employeesByUserId->get($userId)?->id ?? 0);
            if ($employeeId <= 0 || ! isset($stats[$employeeId])) {
                continue;
            }

            $net = (int) max(0, ((int) ($row->price ?? 0) - (int) ($row->discount ?? 0)));
            $maxCards = max(1, (int) ($row->consultations_count ?? 1));
            $perCard = intdiv($net, $maxCards);

            $stats[$employeeId]['consultations_count']++;
            $stats[$employeeId]['consultations_total'] += $perCard;
        }

        if (Schema::hasTable('stage_academy_activations')) {
            $acts = StageAcademyActivation::query()
                ->whereBetween('created_at', [$fromUtc, $toUtc])
                ->get(['id', 'created_by', 'price']);

            foreach ($acts as $a) {
                $userId = (int) ($a->created_by ?? 0);
                $employeeId = (int) ($employeesByUserId->get($userId)?->id ?? 0);
                if ($employeeId <= 0 || ! isset($stats[$employeeId])) {
                    continue;
                }

                $stats[$employeeId]['activations_count']++;
                $stats[$employeeId]['activations_total'] += (int) ($a->price ?? 0);
            }
        }

        $attendance = AttendanceLog::query()
            ->selectRaw('employee_id')
            ->selectRaw('COALESCE(SUM(CASE WHEN attendance_status = \'absent\' THEN 1 ELSE 0 END), 0) as absent_days_total')
            ->selectRaw('COALESCE(SUM(CASE WHEN attendance_status = \'late\' THEN late_minutes ELSE 0 END), 0) as late_minutes_total')
            ->selectRaw('COALESCE(SUM(CASE WHEN attendance_status = \'late\' THEN 1 ELSE 0 END), 0) as late_days_total')
            ->where('type', 'check_in')
            ->whereBetween('logged_at', [$fromUtc, $toUtc])
            ->groupBy('employee_id')
            ->get()
            ->keyBy('employee_id');

        $settings = Schema::hasTable('attendance_settings') ? AttendanceSetting::query()->first() : null;
        $absenceDeductionDays = max(0, min(30, (int) ($settings?->absence_deduction_days ?? 2)));
        $lateMinutePrice = max(0, (int) ($settings?->late_minute_price ?? 0));

        foreach ($stats as $employeeId => $row) {
            $employee = $employeesById->get($employeeId);
            if (! $employee) {
                continue;
            }

            $a = $attendance->get($employeeId);
            $absentDays = (int) ($a?->absent_days_total ?? 0);
            $lateMinutes = (int) ($a?->late_minutes_total ?? 0);
            $lateDays = (int) ($a?->late_days_total ?? 0);

            $baseSalary = (int) ($employee->base_salary ?? 0);
            $dailyRate = $baseSalary > 0 ? ($baseSalary / \App\Models\Employee::DEFAULT_SALARY_WORKING_DAYS_DIVISOR) : 0;
            $absenceAmount = (int) round($absentDays * $absenceDeductionDays * $dailyRate);

            $lateAmount = 0;
            if ($lateMinutes > 0) {
                if ($lateMinutePrice > 0) {
                    $lateAmount = $lateMinutes * $lateMinutePrice;
                } else {
                    $lateAmount = (int) round($lateMinutes * $employee->minuteRate(\App\Models\Employee::DEFAULT_SALARY_WORKING_DAYS_DIVISOR, 'Asia/Baghdad'));
                }
            }

            $stats[$employeeId]['absences_count'] = $absentDays;
            $stats[$employeeId]['absences_total'] = $absenceAmount;
            $stats[$employeeId]['lateness_count'] = $lateDays;
            $stats[$employeeId]['lateness_total'] = $lateAmount;
        }

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.employees_monthly_report');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $startLocal->format('Y-m-d'), 'to' => $endLocal->format('Y-m-d')]).'**';
        $lines[] = '';
        $lines[] = '| الموظف | عدد المبيعات | مجموع مبالغ المبيعات | عدد الاستشارات | مجموع مبالغ الاستشارات | عدد التفعيلات | مجموع مبالغ التفعيلات | الغيابات | مجموع مبالغ الغيابات | التأخيرات | مجموع مبالغ التأخيرات |';
        $lines[] = '| :--- | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: | ---: |';

        foreach ($employees as $e) {
            $r = $stats[$e->id] ?? null;
            if (! $r) {
                continue;
            }

            $lines[] = '| '.($r['employee_name'] ?: '—')
                .' | '.number_format((int) $r['sales_count'])
                .' | '.number_format((int) $r['sales_total'])
                .' | '.number_format((int) $r['consultations_count'])
                .' | '.number_format((int) $r['consultations_total'])
                .' | '.number_format((int) $r['activations_count'])
                .' | '.number_format((int) $r['activations_total'])
                .' | '.number_format((int) $r['absences_count'])
                .' | '.number_format((int) $r['absences_total'])
                .' | '.number_format((int) $r['lateness_count'])
                .' | '.number_format((int) $r['lateness_total'])
                .' |';
        }

        return [
            'request_type' => 'employeesMonthlyReportThisMonth',
            'params' => [],
            'text' => implode("\n", $lines),
            'summary' => "from={$startLocal->toDateString()},to={$endLocal->toDateString()}",
            'data' => [
                'period' => ['from' => $startLocal->toDateString(), 'to' => $endLocal->toDateString()],
                'rows' => array_values($stats),
            ],
            'choices' => [],
        ];
    }

    private function toolNewSubscriptions(array $params): array
    {
        $start = Carbon::parse($params['from'])->startOfDay();
        $end = Carbon::parse($params['to'])->endOfDay();
        $limit = (int) ($params['limit'] ?? 10);

        $base = ServiceSubscription::query()
            ->with(['customer', 'service'])
            ->whereBetween('created_at', [$start, $end]);

        $count = (int) (clone $base)->count();
        $total = (int) (clone $base)->selectRaw('SUM(GREATEST(price - discount, 0)) as total')->value('total') ?? 0;

        $items = (clone $base)
            ->orderByDesc('created_at')
            ->limit($limit)
            ->get();

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.new_subscriptions');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';
        $lines[] = __('system_assistant.sales_report.new_subscriptions', ['count' => $count, 'amount' => number_format($total)]);
        $lines[] = '';

        if ($items->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('system_assistant.table_headers.invoice_number').' | '.__('system_assistant.table_headers.date').' | '.__('subscriptions.customer_name').' | '.__('pos.cards.services').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- | :--- | :--- | :--- |';
            $i = 1;
            foreach ($items as $sub) {
                $date = $sub->created_at?->translatedFormat('Y-m-d') ?? '';
                $customer = $sub->customer?->name ?? __('system_assistant.sales_report.customer_label', ['id' => $sub->customer_id]);
                $service = $sub->service?->name ?? __('system_assistant.sales_report.service_label', ['id' => $sub->service_id]);
                $net = (int) max(0, ((int) $sub->price - (int) $sub->discount));
                $lines[] = "| {$i} | {$sub->invoice_number} | {$date} | {$customer} | {$service} | ".number_format($net).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'newSubscriptions',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "count={$count}, total={$total}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'count' => $count,
                'total' => $total,
            ],
            'choices' => [],
        ];
    }

    private function toolLastInvoices(array $params): array
    {
        $limit = (int) ($params['limit'] ?? 10);

        $subs = ServiceSubscription::query()
            ->with(['customer', 'service'])
            ->orderByDesc('created_at')
            ->limit($limit)
            ->get()
            ->map(function (ServiceSubscription $s) {
                $net = (int) max(0, ((int) $s->price - (int) $s->discount));

                return [
                    'type' => 'subscription',
                    'invoice_number' => (string) $s->invoice_number,
                    'date' => $s->created_at ? $s->created_at->copy() : null,
                    'customer' => $s->customer?->name,
                    'service' => $s->service?->name,
                    'amount' => $net,
                ];
            });

        $renewals = SubscriptionRenewal::query()
            ->with(['serviceSubscription.customer', 'serviceSubscription.service'])
            ->orderByDesc('renewal_date')
            ->limit($limit)
            ->get()
            ->map(function (SubscriptionRenewal $r) {
                $customer = $r->serviceSubscription?->customer?->name;
                $service = $r->serviceSubscription?->service?->name;
                $date = $r->renewal_date ? Carbon::parse($r->renewal_date) : null;

                return [
                    'type' => 'renewal',
                    'invoice_number' => (string) $r->invoice_number,
                    'date' => $date,
                    'customer' => $customer,
                    'service' => $service,
                    'amount' => (int) $r->amount,
                ];
            });

        $all = $subs->merge($renewals)
            ->sortByDesc(function ($row) {
                $d = $row['date'] ?? null;
                if ($d instanceof Carbon) {
                    return $d->timestamp;
                }

                return 0;
            })
            ->values()
            ->take($limit);

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.last_invoices');
        $lines[] = '';

        if ($all->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('system_assistant.table_headers.type').' | '.__('system_assistant.table_headers.invoice_number').' | '.__('system_assistant.table_headers.date').' | '.__('subscriptions.customer_name').' | '.__('pos.cards.services').' | '.__('system_assistant.table_headers.amount').' |';
            $lines[] = '| :--- | :--- | :--- | :--- | :--- | :--- | :--- |';
            $i = 1;
            foreach ($all as $row) {
                $typeLabel = ($row['type'] ?? '') === 'renewal' ? __('system_assistant.invoice_types.renewal') : __('system_assistant.invoice_types.service');
                $inv = $row['invoice_number'] ?? '';
                $date = ($row['date'] instanceof Carbon) ? $row['date']->translatedFormat('Y-m-d') : '';
                $customer = $row['customer'] ?? '';
                $service = $row['service'] ?? '';
                $amount = (int) ($row['amount'] ?? 0);
                $lines[] = "| {$i} | {$typeLabel} | {$inv} | {$date} | {$customer} | {$service} | ".number_format($amount).' |';
                $i++;
            }
        }

        $choices = [];
        foreach ($all->take(3) as $row) {
            $inv = trim((string) ($row['invoice_number'] ?? ''));
            if ($inv !== '') {
                $choices[] = [
                    'label' => __('system_assistant.payloads.show_last_invoice', ['number' => $inv]),
                    'payload' => __('system_assistant.payloads.show_last_invoice', ['number' => $inv]),
                ];
            }
        }

        return [
            'request_type' => 'lastInvoices',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "limit={$limit}",
            'data' => [
                'limit' => $limit,
                'items' => $all->toArray(),
            ],
            'choices' => $choices,
        ];
    }

    private function toolSalesCompare(array $params): array
    {
        $mode = (string) ($params['mode'] ?? 'this_vs_last_month');
        $now = now();

        if ($mode === 'this_year_to_date_vs_last_year') {
            $thisStart = $now->copy()->startOfYear();
            $thisEnd = $now->copy()->endOfDay();
            $lastStart = $thisStart->copy()->subYear();
            $lastEnd = $thisEnd->copy()->subYear();

            return $this->comparePeriods(
                $mode,
                ['from' => $thisStart, 'to' => $thisEnd, 'label' => $now->year.' YTD'],
                ['from' => $lastStart, 'to' => $lastEnd, 'label' => ($now->year - 1).' YTD'],
                __('system_assistant.suggestions.compare_last_year')
            );
        }

        $thisStart = $now->copy()->startOfMonth();
        $thisEnd = $now->copy()->endOfMonth();
        $lastStart = $thisStart->copy()->subMonth()->startOfMonth();
        $lastEnd = $lastStart->copy()->endOfMonth();

        return $this->comparePeriods(
            $mode,
            ['from' => $thisStart, 'to' => $thisEnd, 'label' => $thisStart->translatedFormat('Y-m')],
            ['from' => $lastStart, 'to' => $lastEnd, 'label' => $lastStart->translatedFormat('Y-m')],
            __('system_assistant.chips.sales_compare')
        );
    }

    private function comparePeriods(string $mode, array $a, array $b, string $title): array
    {
        $aData = $this->salesTotalsBetween($a['from'], $a['to']);
        $bData = $this->salesTotalsBetween($b['from'], $b['to']);

        $diff = $aData['grand_total'] - $bData['grand_total'];
        $pct = 0.0;
        if ($bData['grand_total'] > 0) {
            $pct = (($aData['grand_total'] - $bData['grand_total']) / $bData['grand_total']) * 100.0;
        } elseif ($aData['grand_total'] > 0) {
            $pct = 100.0;
        }

        $trend = $diff > 0 ? __('system_assistant.sales_report.trend_increase') : ($diff < 0 ? __('system_assistant.sales_report.trend_decrease') : __('system_assistant.sales_report.trend_stable'));

        $lines = [];
        $lines[] = "### {$title}";
        $lines[] = '';
        $lines[] = '| '.__('system_assistant.sales_report.metric').' | '.$b['label'].' | '.$a['label'].' |';
        $lines[] = '| :--- | :--- | :--- |';
        $lines[] = '| **'.__('system_assistant.sales_report.total_sales').'** | '.number_format($bData['grand_total']).' | '.number_format($aData['grand_total']).' |';
        $lines[] = '| **'.__('system_assistant.sales_report.invoices_count').'** | '.$bData['invoice_count'].' | '.$aData['invoice_count'].' |';
        $lines[] = '| **'.__('system_assistant.sales_report.service_invoices').'** | '.$bData['subs_count'].' ('.number_format($bData['subs_total']).') | '.$aData['subs_count'].' ('.number_format($aData['subs_total']).') |';
        $lines[] = '| **'.__('system_assistant.sales_report.renewal_invoices').'** | '.$bData['renewals_count'].' ('.number_format($bData['renewals_total']).') | '.$aData['renewals_count'].' ('.number_format($aData['renewals_total']).') |';
        $lines[] = '';
        $lines[] = __('system_assistant.sales_report.comparison', ['date' => $b['label'], 'trend' => $trend, 'growth' => number_format($pct, 1).'%']);

        return [
            'request_type' => 'salesCompare',
            'params' => ['mode' => $mode],
            'text' => implode("\n", $lines),
            'summary' => "diff={$diff}, pct={$pct}",
            'data' => [
                'a' => $aData,
                'b' => $bData,
                'diff' => $diff,
                'pct' => $pct,
            ],
            'choices' => [],
        ];
    }

    private function salesTotalsBetween(Carbon $start, Carbon $end): array
    {
        $subsTotal = (int) ServiceSubscription::query()
            ->whereBetween('created_at', [$start, $end])
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->value('total') ?? 0;
        $subsCount = (int) ServiceSubscription::whereBetween('created_at', [$start, $end])->count();

        $renewalsTotal = (int) SubscriptionRenewal::query()
            ->whereBetween('created_at', [$start, $end])
            ->sum('amount');
        $renewalsCount = (int) SubscriptionRenewal::whereBetween('created_at', [$start, $end])->count();

        return [
            'from' => $start->toDateString(),
            'to' => $end->toDateString(),
            'subs_total' => $subsTotal,
            'subs_count' => $subsCount,
            'renewals_total' => $renewalsTotal,
            'renewals_count' => $renewalsCount,
            'grand_total' => $subsTotal + $renewalsTotal,
            'invoice_count' => $subsCount + $renewalsCount,
        ];
    }

    private function expensesTotalsBetween(Carbon $start, Carbon $end): array
    {
        $from = $start->toDateString();
        $to = $end->toDateString();

        $currentTotal = (int) Expense::query()
            ->whereBetween('expense_date', [$from, $to])
            ->sum('total');

        $archivedTotal = (int) ExpenseArchiveItem::query()
            ->whereBetween('expense_date', [$from, $to])
            ->sum('total');

        $currentCount = (int) Expense::query()
            ->whereBetween('expense_date', [$from, $to])
            ->count();

        $archivedCount = (int) ExpenseArchiveItem::query()
            ->whereBetween('expense_date', [$from, $to])
            ->count();

        return [
            'from' => $from,
            'to' => $to,
            'current_total' => $currentTotal,
            'archived_total' => $archivedTotal,
            'total' => $currentTotal + $archivedTotal,
            'current_count' => $currentCount,
            'archived_count' => $archivedCount,
            'count' => $currentCount + $archivedCount,
        ];
    }

    private function salariesTotalsBetween(Carbon $start, Carbon $end): array
    {
        $from = $start->toDateString();
        $to = $end->toDateString();

        $query = MonthlySettlement::query()
            ->whereDate('date_from', '>=', $from)
            ->whereDate('date_to', '<=', $to);

        return [
            'from' => $from,
            'to' => $to,
            'total' => (int) (clone $query)->sum('total_salaries'),
            'count' => (int) (clone $query)->count(),
        ];
    }

    private function topCustomersBetween(Carbon $start, Carbon $end, int $limit = 5): array
    {
        return ServiceSubscription::query()
            ->select('customer_id')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('customer_id')
            ->orderByDesc('total')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $customer = Customer::find($row->customer_id);

                return [
                    'customer_id' => (int) $row->customer_id,
                    'name' => $customer?->name,
                    'phone' => $customer?->phone,
                    'total' => (int) $row->total,
                ];
            })
            ->toArray();
    }

    private function topServicesBetween(Carbon $start, Carbon $end, int $limit = 5): array
    {
        return ServiceSubscription::query()
            ->select('service_id')
            ->selectRaw('COUNT(*) as cnt')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('service_id')
            ->orderByDesc('cnt')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $service = Service::find($row->service_id);

                return [
                    'service_id' => (int) $row->service_id,
                    'name' => $service?->name,
                    'type' => $service?->type,
                    'count' => (int) $row->cnt,
                    'total' => (int) $row->total,
                ];
            })
            ->toArray();
    }

    private function topExpensesBetween(Carbon $start, Carbon $end, int $limit = 5): array
    {
        $from = $start->toDateString();
        $to = $end->toDateString();

        $current = Expense::query()
            ->whereBetween('expense_date', [$from, $to])
            ->orderByDesc('total')
            ->orderByDesc('expense_date')
            ->limit($limit)
            ->get(['id', 'name', 'total', 'expense_date', 'payment_method'])
            ->map(function ($e) {
                return [
                    'source' => 'current',
                    'name' => (string) ($e->name ?? ''),
                    'total' => (int) $e->total,
                    'date' => $e->expense_date ? Carbon::parse($e->expense_date)->toDateString() : null,
                    'payment_method' => $e->payment_method,
                ];
            });

        $archived = ExpenseArchiveItem::query()
            ->whereBetween('expense_date', [$from, $to])
            ->orderByDesc('total')
            ->orderByDesc('expense_date')
            ->limit($limit)
            ->get(['id', 'name', 'total', 'expense_date', 'payment_method'])
            ->map(function ($e) {
                return [
                    'source' => 'archived',
                    'name' => (string) ($e->name ?? ''),
                    'total' => (int) $e->total,
                    'date' => $e->expense_date ? Carbon::parse($e->expense_date)->toDateString() : null,
                    'payment_method' => $e->payment_method,
                ];
            });

        return $current
            ->merge($archived)
            ->sortByDesc('total')
            ->values()
            ->take($limit)
            ->toArray();
    }

    private function toolCompareMonths(array $params): array
    {
        $months = (int) ($params['months'] ?? 6);
        $months = max(3, min(24, $months));

        $now = now();
        $base = $now->copy()->startOfMonth();

        $rows = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $start = $base->copy()->subMonths($i)->startOfMonth();
            $end = $start->copy()->endOfMonth();

            $sales = $this->salesTotalsBetween($start, $end);
            $expenses = $this->expensesTotalsBetween($start, $end);
            $salaries = $this->salariesTotalsBetween($start, $end);

            $netBeforeSalaries = (int) $sales['grand_total'] - (int) $expenses['total'];
            $netAfterSalaries = $netBeforeSalaries - (int) $salaries['total'];

            $rows[] = [
                'month' => $start->translatedFormat('Y-m'),
                'sales_total' => (int) $sales['grand_total'],
                'expenses_total' => (int) $expenses['total'],
                'salaries_total' => (int) $salaries['total'],
                'net_before_salaries' => $netBeforeSalaries,
                'net_after_salaries' => $netAfterSalaries,
            ];
        }

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.months_compare');
        $lines[] = '';
        $lines[] = '| '.__('system_assistant.sales_report.month').' | '.__('system_assistant.sales_report.total_sales').' | المصاريف | الرواتب | صافي قبل الرواتب | صافي بعد الرواتب |';
        $lines[] = '| :--- | ---: | ---: | ---: | ---: | ---: |';

        foreach ($rows as $r) {
            $lines[] = '| '.$r['month'].' | '.number_format($r['sales_total']).' | '.number_format($r['expenses_total']).' | '.number_format($r['salaries_total']).' | '.number_format($r['net_before_salaries']).' | '.number_format($r['net_after_salaries']).' |';
        }

        return [
            'request_type' => 'compareMonths',
            'params' => ['months' => $months],
            'text' => implode("\n", $lines),
            'summary' => "months={$months}",
            'data' => [
                'months' => $months,
                'rows' => $rows,
            ],
            'choices' => [],
        ];
    }

    private function toolMonthlyFullReport(array $params): array
    {
        $year = (int) ($params['year'] ?? now()->year);
        $month = (int) ($params['month'] ?? now()->month);

        $start = Carbon::create($year, $month, 1)->startOfMonth();
        $end = $start->copy()->endOfMonth();

        $sales = $this->salesTotalsBetween($start, $end);
        $expenses = $this->expensesTotalsBetween($start, $end);
        $salaries = $this->salariesTotalsBetween($start, $end);

        $netBeforeSalaries = (int) $sales['grand_total'] - (int) $expenses['total'];
        $netAfterSalaries = $netBeforeSalaries - (int) $salaries['total'];

        $topCustomers = $this->topCustomersBetween($start, $end, 5);
        $topServices = $this->topServicesBetween($start, $end, 5);
        $topExpenses = $this->topExpensesBetween($start, $end, 5);

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.monthly_full_report').': '.$start->translatedFormat('F Y');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        $lines[] = '#### ملخص';
        $lines[] = '| '.__('system_assistant.sales_report.metric').' | '.__('system_assistant.sales_report.value').' |';
        $lines[] = '| :--- | ---: |';
        $lines[] = '| **'.__('system_assistant.sales_report.total_sales').'** | '.number_format((int) $sales['grand_total']).' |';
        $lines[] = '| **مصاريف** | '.number_format((int) $expenses['total']).' |';
        $lines[] = '| **رواتب (من التسويات المسجلة)** | '.number_format((int) $salaries['total']).' |';
        $lines[] = '| **صافي قبل الرواتب** | '.number_format($netBeforeSalaries).' |';
        $lines[] = '| **صافي بعد الرواتب** | '.number_format($netAfterSalaries).' |';
        $lines[] = '| **'.__('system_assistant.sales_report.invoices_count').'** | '.(int) $sales['invoice_count'].' |';
        $lines[] = '| **'.__('system_assistant.sales_report.service_invoices').'** | '.(int) $sales['subs_count'].' ('.number_format((int) $sales['subs_total']).') |';
        $lines[] = '| **'.__('system_assistant.sales_report.renewal_invoices').'** | '.(int) $sales['renewals_count'].' ('.number_format((int) $sales['renewals_total']).') |';
        $lines[] = '| **عدد المصاريف** | '.(int) $expenses['count'].' |';
        $lines[] = '| **عدد التسويات** | '.(int) $salaries['count'].' |';

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_customers');
        if (empty($topCustomers)) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('subscriptions.customer_name').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | ---: |';
            $i = 1;
            foreach ($topCustomers as $c) {
                $label = trim(($c['name'] ?? '').' '.(($c['phone'] ?? '') ? "({$c['phone']})" : ''));
                $label = $label !== '' ? $label : __('system_assistant.sales_report.customer_label', ['id' => $c['customer_id']]);
                $lines[] = "| {$i} | {$label} | ".number_format((int) ($c['total'] ?? 0)).' |';
                $i++;
            }
        }

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_services');
        if (empty($topServices)) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('pos.cards.services').' | '.__('subscriptions.quantity').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | ---: | ---: |';
            $i = 1;
            foreach ($topServices as $s) {
                $label = ($s['name'] ?? '') !== '' ? "{$s['name']} ({$s['type']})" : __('system_assistant.sales_report.service_label', ['id' => $s['service_id']]);
                $lines[] = "| {$i} | {$label} | ".(int) ($s['count'] ?? 0).' | '.number_format((int) ($s['total'] ?? 0)).' |';
                $i++;
            }
        }

        $lines[] = '';
        $lines[] = '#### المصاريف الأعلى';
        if (empty($topExpenses)) {
            $lines[] = '_لا توجد مصاريف ضمن هذه الفترة._';
        } else {
            $lines[] = '| # | الاسم | التاريخ | المبلغ | طريقة الدفع |';
            $lines[] = '| :--- | :--- | :--- | ---: | :--- |';
            $i = 1;
            foreach ($topExpenses as $e) {
                $name = (string) ($e['name'] ?? '');
                $date = (string) ($e['date'] ?? '');
                $total = (int) ($e['total'] ?? 0);
                $method = (string) ($e['payment_method'] ?? '');
                $lines[] = "| {$i} | {$name} | {$date} | ".number_format($total)." | {$method} |";
                $i++;
            }
        }

        return [
            'request_type' => 'monthlyFullReport',
            'params' => ['year' => $year, 'month' => $month],
            'text' => implode("\n", $lines),
            'summary' => "sales={$sales['grand_total']}, expenses={$expenses['total']}, salaries={$salaries['total']}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'sales' => $sales,
                'expenses' => $expenses,
                'salaries' => $salaries,
                'net_before_salaries' => $netBeforeSalaries,
                'net_after_salaries' => $netAfterSalaries,
            ],
            'choices' => [],
        ];
    }

    private function toolYearlyFullReport(array $params): array
    {
        $year = (int) ($params['year'] ?? now()->year);

        $yearStart = Carbon::create($year, 1, 1)->startOfYear();
        $yearEnd = $yearStart->copy()->endOfYear();

        $totals = [
            'sales_total' => 0,
            'expenses_total' => 0,
            'salaries_total' => 0,
            'net_before_salaries' => 0,
            'net_after_salaries' => 0,
        ];

        $rows = [];
        for ($m = 1; $m <= 12; $m++) {
            $start = Carbon::create($year, $m, 1)->startOfMonth();
            $end = $start->copy()->endOfMonth();

            $sales = $this->salesTotalsBetween($start, $end);
            $expenses = $this->expensesTotalsBetween($start, $end);
            $salaries = $this->salariesTotalsBetween($start, $end);

            $netBeforeSalaries = (int) $sales['grand_total'] - (int) $expenses['total'];
            $netAfterSalaries = $netBeforeSalaries - (int) $salaries['total'];

            $rows[] = [
                'month' => $start->translatedFormat('m - F'),
                'sales_total' => (int) $sales['grand_total'],
                'expenses_total' => (int) $expenses['total'],
                'salaries_total' => (int) $salaries['total'],
                'net_before_salaries' => $netBeforeSalaries,
                'net_after_salaries' => $netAfterSalaries,
            ];

            $totals['sales_total'] += (int) $sales['grand_total'];
            $totals['expenses_total'] += (int) $expenses['total'];
            $totals['salaries_total'] += (int) $salaries['total'];
            $totals['net_before_salaries'] += $netBeforeSalaries;
            $totals['net_after_salaries'] += $netAfterSalaries;
        }

        $topCustomers = $this->topCustomersBetween($yearStart, $yearEnd, 5);
        $topServices = $this->topServicesBetween($yearStart, $yearEnd, 5);

        $lines = [];
        $lines[] = '### '.__('system_assistant.chips.yearly_report').": {$year}";
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $yearStart->format('Y-m-d'), 'to' => $yearEnd->format('Y-m-d')]).'**';
        $lines[] = '';

        $lines[] = '#### ملخص سنوي';
        $lines[] = '| '.__('system_assistant.sales_report.metric').' | '.__('system_assistant.sales_report.value').' |';
        $lines[] = '| :--- | ---: |';
        $lines[] = '| **'.__('system_assistant.sales_report.total_sales').'** | '.number_format((int) $totals['sales_total']).' |';
        $lines[] = '| **مصاريف** | '.number_format((int) $totals['expenses_total']).' |';
        $lines[] = '| **رواتب (من التسويات المسجلة)** | '.number_format((int) $totals['salaries_total']).' |';
        $lines[] = '| **صافي قبل الرواتب** | '.number_format((int) $totals['net_before_salaries']).' |';
        $lines[] = '| **صافي بعد الرواتب** | '.number_format((int) $totals['net_after_salaries']).' |';

        $lines[] = '';
        $lines[] = '#### مقارنة الأشهر';
        $lines[] = '| '.__('system_assistant.sales_report.month').' | '.__('system_assistant.sales_report.total_sales').' | المصاريف | الرواتب | صافي قبل الرواتب | صافي بعد الرواتب |';
        $lines[] = '| :--- | ---: | ---: | ---: | ---: | ---: |';
        foreach ($rows as $r) {
            $lines[] = '| '.$r['month'].' | '.number_format($r['sales_total']).' | '.number_format($r['expenses_total']).' | '.number_format($r['salaries_total']).' | '.number_format($r['net_before_salaries']).' | '.number_format($r['net_after_salaries']).' |';
        }

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_customers');
        if (empty($topCustomers)) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('subscriptions.customer_name').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | ---: |';
            $i = 1;
            foreach ($topCustomers as $c) {
                $label = trim(($c['name'] ?? '').' '.(($c['phone'] ?? '') ? "({$c['phone']})" : ''));
                $label = $label !== '' ? $label : __('system_assistant.sales_report.customer_label', ['id' => $c['customer_id']]);
                $lines[] = "| {$i} | {$label} | ".number_format((int) ($c['total'] ?? 0)).' |';
                $i++;
            }
        }

        $lines[] = '';
        $lines[] = '#### '.__('system_assistant.sales_report.top_services');
        if (empty($topServices)) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('pos.cards.services').' | '.__('subscriptions.quantity').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | ---: | ---: |';
            $i = 1;
            foreach ($topServices as $s) {
                $label = ($s['name'] ?? '') !== '' ? "{$s['name']} ({$s['type']})" : __('system_assistant.sales_report.service_label', ['id' => $s['service_id']]);
                $lines[] = "| {$i} | {$label} | ".(int) ($s['count'] ?? 0).' | '.number_format((int) ($s['total'] ?? 0)).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'yearlyFullReport',
            'params' => ['year' => $year],
            'text' => implode("\n", $lines),
            'summary' => "year={$year}, sales={$totals['sales_total']}",
            'data' => [
                'period' => ['from' => $yearStart->toDateString(), 'to' => $yearEnd->toDateString()],
                'totals' => $totals,
                'rows' => $rows,
            ],
            'choices' => [],
        ];
    }

    private function toolServiceAnalysis(array $params): array
    {
        $start = Carbon::parse($params['from'])->startOfDay();
        $end = Carbon::parse($params['to'])->endOfDay();
        $limit = (int) ($params['limit'] ?? 5);

        $rows = ServiceSubscription::query()
            ->select('service_id')
            ->selectRaw('COUNT(*) as cnt')
            ->selectRaw('SUM(GREATEST(price - discount, 0)) as total')
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('service_id')
            ->orderByDesc('cnt')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $service = Service::find($row->service_id);

                return [
                    'service_id' => (int) $row->service_id,
                    'name' => $service?->name,
                    'type' => $service?->type,
                    'count' => (int) $row->cnt,
                    'total' => (int) $row->total,
                ];
            });

        $lines = [];
        $lines[] = '### '.__('system_assistant.suggestions.service_analysis');
        $lines[] = '**'.__('system_assistant.customer_report.period', ['from' => $start->format('Y-m-d'), 'to' => $end->format('Y-m-d')]).'**';
        $lines[] = '';

        if ($rows->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('pos.cards.services').' | '.__('subscriptions.quantity').' | '.__('subscriptions.total').' |';
            $lines[] = '| :--- | :--- | :--- | :--- |';
            $i = 1;
            foreach ($rows as $s) {
                $label = ($s['name'] ?? '') !== '' ? "{$s['name']} ({$s['type']})" : __('system_assistant.sales_report.service_label', ['id' => $s['service_id']]);
                $lines[] = "| {$i} | {$label} | {$s['count']} | ".number_format($s['total']).' |';
                $i++;
            }
        }

        return [
            'request_type' => 'serviceAnalysis',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "from={$start->toDateString()},to={$end->toDateString()}",
            'data' => [
                'period' => ['from' => $start->toDateString(), 'to' => $end->toDateString()],
                'top_services' => $rows->toArray(),
            ],
            'choices' => [],
        ];
    }

    private function toolCustomerTotalPayments(array $params): array
    {
        $customerId = (int) $params['customer_id'];
        $customer = Customer::find($customerId);
        if (! $customer) {
            return [
                'request_type' => 'customerTotalPayments',
                'params' => $params,
                'text' => __('system_assistant.responses.customer_not_found', ['id' => $customerId]),
                'summary' => 'customer_not_found',
                'choices' => [],
            ];
        }

        $query = SubscriptionPayment::query()
            ->whereHas('subscription', function ($q) use ($customerId) {
                $q->where('customer_id', $customerId);
            });

        if (! empty($params['from'])) {
            $query->whereDate('payment_date', '>=', $params['from']);
        }
        if (! empty($params['to'])) {
            $query->whereDate('payment_date', '<=', $params['to']);
        }

        $count = (int) (clone $query)->count();
        $total = (int) (clone $query)->sum('amount');
        $items = (clone $query)
            ->with(['subscription'])
            ->orderByDesc('payment_date')
            ->orderByDesc('id')
            ->limit(10)
            ->get();

        $lines = [];
        $lines[] = '### '.__('system_assistant.suggestions.total_payments');
        $lines[] = '| '.__('subscriptions.customer_name').' | '.__('subscriptions.total').' |';
        $lines[] = '| :--- | :--- |';
        $lines[] = "| **{$customer->name}** | ".number_format($total).' |';
        $lines[] = '';

        if ($items->isEmpty()) {
            $lines[] = '_'.__('system_assistant.responses.no_results').'_';
        } else {
            $lines[] = '| # | '.__('system_assistant.table_headers.date').' | '.__('system_assistant.table_headers.invoice_number').' | '.__('subscriptions.total').' | '.__('subscriptions.payment_method').' |';
            $lines[] = '| :--- | :--- | :--- | :--- | :--- |';
            $i = 1;
            foreach ($items as $p) {
                $date = $p->payment_date ? Carbon::parse($p->payment_date)->translatedFormat('Y-m-d') : '';
                $inv = $p->subscription?->invoice_number ?? '';
                $method = (string) ($p->payment_method ?? '');
                $lines[] = "| {$i} | {$date} | {$inv} | ".number_format((int) $p->amount)." | {$method} |";
                $i++;
            }
        }

        return [
            'request_type' => 'customerTotalPayments',
            'params' => $params,
            'text' => implode("\n", $lines),
            'summary' => "customer_id={$customerId}, total={$total}, count={$count}",
            'data' => [
                'customer_id' => $customerId,
                'total' => $total,
                'count' => $count,
            ],
            'choices' => [],
        ];
    }

    private function helpText(): string
    {
        return implode("\n", [
            'أستطيع جلب معلومات من قاعدة بيانات النظام فقط (قراءة فقط).',
            '',
            'جرّب أسئلة مثل:',
            '1) ابحث عن زبون: أحمد أو 0770xxxxxxx',
            '2) فاتورة: CPP-1001',
            '3) ملخص زبون: customerSummary 12',
            '4) تجديدات زبون: getCustomerRenewals 12',
            '5) مبيعات شهرية: مبيعات 2026/02',
            '6) مبيعات فترة: من 2026-02-01 إلى 2026-02-29',
            '7) مقارنة بين الأشهر: مقارنة بين الأشهر',
            '8) تقرير شهري شامل: تقرير شهري 2026/04',
            '9) تقرير سنوي: تقرير سنوي 2026',
        ]);
    }

    private function ollamaSystemPrompt(array $sessionContext = []): string
    {
        $today = now()->format('Y-m-d');

        $contextSection = '';
        if (! empty($sessionContext)) {
            $contextSection = "SESSION CONTEXT (Use these values for follow-up questions like 'he', 'it', 'the customer', 'compare'):\n".json_encode($sessionContext);
        }

        return implode("\n", [
            'Role: Expert Data Analyst & Intent Classifier.',
            'Task: Analyze the user question, identify intent, extract entities, and select the best tool.',
            "Current Date: {$today}",
            '',
            $contextSection,
            '',
            'Database Schema Context (Read-Only):',
            $this->schemaDescription(),
            '',
            'Tools (Strict Usage):',
            '- findCustomer: {"query": "string"} (Search by name/phone/business/email. Use this if unsure about ID)',
            '- customerSummary: {"customer_id": 123} (Get full profile, total spend, balance, last activity. Prefer ID from Context if available)',
            '- getInvoice: {"invoice": "CPP-1001" or "123"}',
            '- listInvoicesByCustomer: {"customer_id": 123, "from"?: "YYYY-MM-DD", "to"?: "YYYY-MM-DD"}',
            '- getCustomerRenewals: {"customer_id": 123}',
            '- salesReportMonthly: {"year": YYYY, "month": M} (Sales stats for specific month)',
            '- salesReportYearly: {"year": YYYY}',
            '- salesReportRange: {"from": "YYYY-MM-DD", "to": "YYYY-MM-DD"}',
            '- compareMonths: {"months": 6} (Compare last N months: sales/expenses/salaries/net)',
            '- monthlyFullReport: {"year": YYYY, "month": M} (Monthly report including sales + expenses + salaries + net)',
            '- yearlyFullReport: {"year": YYYY} (Yearly report including monthly breakdown)',
            '',
            'Instructions:',
            '1. Analyze the user question using the Schema Context & Conversation History.',
            '2. Extract entities (names, dates, IDs). Handle relative dates like "last month", "this year".',
            '3. RESOLVE CONTEXT: If the user refers to "he", "she", "it", "the customer", or implies a follow-up, use the ID from the SESSION CONTEXT.',
            '4. RESOLVE COMPARISONS: If the user asks to "compare" or "how about last month", use the `last_year`/`last_month` from SESSION CONTEXT to calculate the previous period.',
            '5. RESOLVE INVOICE DETAILS: If the user asks "show details" or "what is in it" after an invoice query, use `last_invoice_id` from SESSION CONTEXT.',
            '6. Return JSON ONLY in this format:',
            '   {"intent": "intent_name", "entities": {"key": "value"}, "tool": "toolName", "params": {...}}',
            '7. If multiple tools apply, pick the most specific one.',
            '8. Do NOT generate SQL. Do NOT explain. JSON ONLY.',
            '9. If the user speaks English, understand it but ensure the intent maps to these tools.',
        ]);
    }

    private function schemaDescription(): string
    {
        return implode("\n", [
            '- customers: id, name, phone, business_name, email, created_at',
            '- service_subscriptions: id, customer_id, service_id, price, discount, created_at (sales date), invoice_number',
            '- subscription_renewals: id, service_subscription_id, amount, renewal_date, created_at, invoice_number',
            '- payments: id, service_subscription_id, amount, payment_date, payment_method',
            '- services: id, name, type (monthly/yearly/one_time)',
            '- expenses: id, name, total, expense_date, payment_method',
            '- expense_archive_items: id, name, total, expense_date, payment_method',
            '- monthly_settlements: id, date_from, date_to, total_salaries, total_sales, total_expenses, settled_at',
        ]);
    }

    private function parseCustomerDateRange(string $rest): array
    {
        $parts = preg_split('/\s+/', trim($rest));
        $customerId = $parts[0] ?? '';
        $from = $parts[1] ?? null;
        $to = $parts[2] ?? null;
        $out = ['customer_id' => $customerId];
        if ($from) {
            $out['from'] = $from;
        }
        if ($to) {
            $out['to'] = $to;
        }

        return $out;
    }

    private function parseYearMonth(string $rest): array
    {
        $rest = trim($rest);
        if (preg_match('/(20\d{2})\D+(\d{1,2})/', $rest, $m)) {
            return ['year' => $m[1], 'month' => $m[2]];
        }
        $parts = preg_split('/\s+/', $rest);

        return ['year' => $parts[0] ?? '', 'month' => $parts[1] ?? ''];
    }

    private function parseFromTo(string $rest): array
    {
        $rest = trim($rest);
        if ($range = $this->extractFromToDates($rest)) {
            return $range;
        }
        $parts = preg_split('/\s+/', $rest);

        return ['from' => $parts[0] ?? '', 'to' => $parts[1] ?? ''];
    }

    private function extractFromToDates(string $text): ?array
    {
        if (preg_match('/(\d{4}-\d{2}-\d{2}).*?(\d{4}-\d{2}-\d{2})/u', $text, $m)) {
            return ['from' => $m[1], 'to' => $m[2]];
        }

        return null;
    }

    private function generateSuggestions(string $tool, array $params): array
    {
        $suggestions = [];

        if (in_array($tool, ['help', 'fallback_error', 'generalChat'], true)) {
            $suggestions[] = ['label' => __('system_assistant.chips.sales_month'), 'payload' => __('system_assistant.chips.sales_month')];
            $suggestions[] = ['label' => __('system_assistant.chips.top_customers'), 'payload' => __('system_assistant.chips.top_customers')];
            $suggestions[] = ['label' => __('system_assistant.chips.new_subscriptions'), 'payload' => __('system_assistant.chips.new_subscriptions')];
            $suggestions[] = ['label' => __('system_assistant.chips.last_invoices'), 'payload' => __('system_assistant.chips.last_invoices')];
            $suggestions[] = ['label' => __('system_assistant.chips.sales_compare'), 'payload' => __('system_assistant.chips.sales_compare')];
            $suggestions[] = ['label' => __('system_assistant.chips.months_compare'), 'payload' => __('system_assistant.chips.months_compare')];
            $suggestions[] = ['label' => __('system_assistant.chips.monthly_full_report'), 'payload' => __('system_assistant.chips.monthly_full_report')];
            $suggestions[] = ['label' => __('system_assistant.chips.yearly_report'), 'payload' => __('system_assistant.chips.yearly_report')];
            $suggestions[] = ['label' => __('system_assistant.chips.top_services_sold'), 'payload' => __('system_assistant.chips.top_services_sold')];
            $suggestions[] = ['label' => __('system_assistant.chips.top_stage_academy_courses_sold'), 'payload' => __('system_assistant.chips.top_stage_academy_courses_sold')];
            $suggestions[] = ['label' => __('system_assistant.chips.employees_monthly_report'), 'payload' => __('system_assistant.chips.employees_monthly_report')];
        } elseif ($tool === 'findCustomer') {
            // Suggest searching for invoices if a name was used?
            // Actually findCustomer returns choices, so the user picks one.
            // If they picked one, the next tool is usually customerSummary or something.
        } elseif ($tool === 'customerSummary' || $tool === 'getInvoice' || $tool === 'listInvoicesByCustomer') {
            $id = $params['customer_id'] ?? null;
            if ($id) {
                $suggestions[] = ['label' => __('system_assistant.suggestions.show_invoices'), 'payload' => __('system_assistant.payloads.show_invoices', ['id' => $id])];
                $suggestions[] = ['label' => __('system_assistant.suggestions.subscription_renewals'), 'payload' => __('system_assistant.payloads.show_renewals', ['id' => $id])];
                $suggestions[] = ['label' => __('system_assistant.suggestions.total_payments'), 'payload' => __('system_assistant.payloads.total_payments', ['id' => $id])];
            }
        } elseif (str_starts_with($tool, 'salesReport')) {
            $suggestions[] = ['label' => __('system_assistant.suggestions.sales_this_month'), 'payload' => __('system_assistant.chips.sales_month')];
            $suggestions[] = ['label' => __('system_assistant.suggestions.sales_last_month'), 'payload' => __('system_assistant.chips.sales_compare')];
            $suggestions[] = ['label' => __('system_assistant.suggestions.sales_this_year'), 'payload' => __('system_assistant.sales_report.yearly_sales', ['year' => now()->year])];
            $suggestions[] = ['label' => __('system_assistant.suggestions.top_customers'), 'payload' => __('system_assistant.payloads.top_customers')];
            $suggestions[] = ['label' => __('system_assistant.suggestions.compare_last_year'), 'payload' => __('system_assistant.payloads.compare_last_year')];
            $suggestions[] = ['label' => __('system_assistant.suggestions.service_analysis'), 'payload' => __('system_assistant.payloads.service_analysis')];
            $suggestions[] = ['label' => __('system_assistant.chips.monthly_full_report'), 'payload' => __('system_assistant.chips.monthly_full_report')];
            $suggestions[] = ['label' => __('system_assistant.chips.months_compare'), 'payload' => __('system_assistant.chips.months_compare')];
        }

        return $suggestions;
    }
}
