<?php

namespace App\Http\Controllers\Admin;

use App\Helpers\StorageHelper;
use App\Http\Controllers\Controller;
use App\Models\Message;
use App\Models\Plan;
use App\Models\SystemSetting;
use App\Support\SmtpConfig;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Str;
use Illuminate\View\View;

class SettingsController extends Controller
{   
    /**
     * Display the system settings page with all tab data pre-loaded.
     */
    public function index(): View
    {
        // Load plans for the "default plan" dropdown
        $plans = Plan::where('is_active', true)->orderBy('sort_order')->get(['id', 'name', 'slug']);

        return view('admin.settings', compact('plans'));
    }

    /**
     * Persist admin settings to the system_settings key-value store.
     *
     * Sensitive values (API keys, SMTP password) are encrypted at rest
     * and are never overwritten with blanks to prevent accidental clearing.
     */
    public function update(Request $request): RedirectResponse
    {
        $allowed = [
            // General tab
            'site_name', 'site_tagline', 'site_description',
            'default_language', 'timezone', 'default_currency',
            'support_email', 'support_phone',
            // Branding tab
            'primary_color', 'secondary_color', 'accent_color',
            'logo_light', 'logo_dark', 'favicon',
            // Email / SMTP tab
            'mail_driver', 'smtp_host', 'smtp_port', 'smtp_username', 'smtp_password',
            'smtp_encryption', 'smtp_from_address', 'smtp_from_name',
            'mail_from_name', 'mail_from_email',
            // Authentication tab
            'registration_enabled', 'social_login_enabled',
            'require_email_verification', 'default_plan', 'trial_days',
            // Security tab
            'max_login_attempts', 'lockout_duration', 'require_2fa_admins',
            'session_lifetime', 'password_min_length',
            'hcaptcha_site_key', 'hcaptcha_secret_key',
            // AI Configuration — the tab was removed (single source of truth
            // is now /admin/ai-providers). The active keys read by AIManager
            // are `ai_default_provider` / `ai_default_model` / `ai_monthly_budget`
            // and the per-provider api keys, all listed below in the
            // "Legacy / backward compat" block. The 4 legacy keys
            // (default_ai_provider, default_ai_model, ai_temperature,
            // ai_max_tokens) used to be written by the removed AI Config
            // tab but were never actually read anywhere — dropped from
            // the whitelist.
            // Integrations tab
            'google_client_id', 'google_client_secret',
            'microsoft_client_id', 'microsoft_client_secret',
            'github_client_id', 'github_client_secret',
            'facebook_client_id', 'facebook_client_secret',
            'stripe_key', 'stripe_secret', 'stripe_webhook_secret',
            'pusher_app_id', 'pusher_key', 'pusher_secret', 'pusher_cluster',
            'whatsapp_phone_number_id', 'whatsapp_access_token', 'whatsapp_verify_token',
            'twilio_sid', 'twilio_auth_token', 'twilio_phone_number',
            'telegram_bot_token',
            'slack_client_id', 'slack_client_secret', 'slack_signing_secret',
            'salesforce_client_id', 'salesforce_client_secret',
            'hubspot_client_id', 'hubspot_client_secret',
            // Integration enabled toggles
            'integration_google_enabled', 'integration_microsoft_enabled',
            'integration_github_enabled', 'integration_facebook_enabled',
            'integration_stripe_enabled',
            'integration_pusher_enabled', 'integration_whatsapp_enabled',
            'integration_twilio_enabled', 'integration_telegram_enabled',
            // The Slack / Salesforce / HubSpot toggles were missing from here,
            // which silently dropped their value on save — users saw the
            // toggle flip back off on every refresh even after hitting Save.
            'integration_slack_enabled', 'integration_salesforce_enabled',
            'integration_hubspot_enabled',
            // Legal / GDPR tab
            'cookie_consent_enabled', 'cookie_consent_message',
            'gdpr_data_export_enabled', 'gdpr_account_deletion_enabled',
            'terms_of_service_url', 'privacy_policy_url', 'refund_policy_url',
            'gdpr_cookie_enabled', 'gdpr_cookie_message',
            'gdpr_cookie_policy_url', 'gdpr_data_retention_days',
            // Maintenance tab
            'maintenance_mode', 'maintenance_message', 'maintenance_allowed_ips',
            // Frontend tab — `frontend_disabled` turns the marketing site off
            // entirely, sending homepage + legal pages straight to /login.
            // For private/internal MailTrixy deployments that don't run a
            // public SaaS landing page.
            'frontend_disabled',
            // SEO defaults — admin → CMS page. These were silently dropped
            // before because the whitelist never knew about them: form
            // posted, controller filtered them out via $request->only(),
            // user was redirected to /admin/settings with a "saved"
            // flash but nothing persisted ("redirected to Settings and
            // shows the message save, but when I come back, there is
            // still the default text" — the client's exact words).
            'seo_default_title', 'seo_default_description', 'seo_og_image',
            // Legacy / backward compat
            'app_url', 'date_format', 'meta_title', 'meta_description',
            'default_user_role', 'recaptcha_enabled', 'email_verification',
            'ai_default_provider', 'ai_default_model', 'ai_monthly_budget',
            'openai_api_key', 'anthropic_api_key', 'gemini_api_key', 'mistral_api_key',
            'pinecone_api_key', 'pinecone_host', 'pinecone_index',
            // Notification toggles
            'notify_new_user', 'notify_new_payment', 'notify_failed_payment',
            'notify_subscription_canceled', 'notify_trial_ending',
            'notify_support_ticket', 'notify_system_alert',
            'notify_ai_usage_high', 'notify_email_bounce', 'notify_quota_warning',
            'notify_payment_success', 'notify_payment_failed', 'notify_new_ticket',
            'notify_plan_change', 'notify_user_suspension', 'notify_ai_budget',
            'notify_high_bounce', 'notify_daily_summary',
            'slack_webhook_url',
            'slack_notify_new_user', 'slack_notify_payment', 'slack_notify_system_alert',
            'slack_notify_ticket', 'slack_notify_ai_usage',
            'slack_notify_payment_failed', 'slack_notify_new_ticket', 'slack_notify_ai_budget',
            'admin_notification_emails',
            // PWA tab
            'pwa_enabled', 'pwa_app_name', 'pwa_short_name', 'pwa_start_url',
            'pwa_theme_color', 'pwa_background_color',
            'pwa_icon', 'pwa_splash',
            // Custom Code tab — admin-injected CSS/JS that renders site-wide
            // into the layout's <head> and just before </body>. Output is
            // raw (not escaped) since the admin is the author by definition.
            // `head_code` and `footer_code` already existed in the frontend
            // layout; we extend with `custom_css` (a dedicated CSS textarea).
            'custom_css', 'head_code', 'footer_code',
            // Font picker — choice + weights (comma-separated). Loaded into
            // every layout's <head> via Google Fonts. Empty = use Outfit default.
            'font_family', 'font_weights',
            // Company billing / invoicing — the SELLER's details (us). Buyer
            // details snapshot onto each Payment row at checkout, not here.
            // tax_id is the local tax registration; vat is the EU VAT number.
            // Bank details are free-form text printed at the bottom of PDFs.
            'company_name', 'company_tax_id', 'company_vat_number',
            'company_address_line1', 'company_address_line2',
            'company_city', 'company_state', 'company_postal', 'company_country',
            'company_email', 'company_phone', 'company_website',
            'company_bank_details',
            // Tax/VAT system config — `tax_enabled` is the master switch;
            // `tax_default_rate` (%) is the fallback rate when a plan has
            // tax_rate=0. `tax_label` is the column label on invoices ("VAT",
            // "GST", "Sales Tax", etc.).
            'tax_enabled', 'tax_default_rate', 'tax_label',
            // External storage / CDN — see App\Helpers\StorageHelper for how
            // these flip the runtime filesystems disk config.
            's3_enabled', 's3_driver', 's3_key', 's3_secret', 's3_bucket',
            's3_region', 's3_endpoint', 's3_url', 's3_path_style',
        ];

        // Toggle fields normalized to 'true'/'false'
        $booleanKeys = [
            'registration_enabled', 'social_login_enabled', 'require_email_verification',
            'email_verification', 'recaptcha_enabled', 'require_2fa_admins',
            'maintenance_mode', 'frontend_disabled', 'cookie_consent_enabled',
            'gdpr_cookie_enabled', 'gdpr_data_export_enabled', 'gdpr_account_deletion_enabled',
            'tax_enabled', 's3_enabled', 's3_path_style',
            'integration_google_enabled', 'integration_microsoft_enabled',
            'integration_github_enabled', 'integration_facebook_enabled',
            'integration_stripe_enabled',
            'integration_pusher_enabled', 'integration_whatsapp_enabled',
            'integration_twilio_enabled', 'integration_telegram_enabled',
            'integration_slack_enabled', 'integration_salesforce_enabled',
            'integration_hubspot_enabled',
            'notify_new_user', 'notify_new_payment', 'notify_failed_payment',
            'notify_subscription_canceled', 'notify_trial_ending',
            'notify_support_ticket', 'notify_system_alert',
            'notify_ai_usage_high', 'notify_email_bounce', 'notify_quota_warning',
            'notify_payment_success', 'notify_payment_failed', 'notify_new_ticket',
            'notify_plan_change', 'notify_user_suspension', 'notify_ai_budget',
            'notify_high_bounce', 'notify_daily_summary',
            'slack_notify_new_user', 'slack_notify_payment', 'slack_notify_system_alert',
            'slack_notify_ticket', 'slack_notify_ai_usage',
            'slack_notify_payment_failed', 'slack_notify_new_ticket', 'slack_notify_ai_budget',
            'pwa_enabled',
        ];

        // Derive group from the request (tab context)
        $group = $request->input('_settings_group', 'general');

        // Handle file uploads (logos, favicon) — store to public disk, save path
        $fileFields = ['logo_light', 'logo_dark', 'favicon', 'pwa_icon', 'pwa_splash'];
        foreach ($fileFields as $field) {
            if ($request->hasFile($field)) {
                $folder = str_starts_with($field, 'pwa_') ? 'settings/pwa' : 'settings/branding';
                $path = $request->file($field)->store($folder, 'public');
                DB::table('system_settings')->updateOrInsert(
                    ['key' => $field],
                    ['value' => $path, 'group' => str_starts_with($field, 'pwa_') ? 'pwa' : 'branding', 'updated_at' => now()]
                );
            }
        }

        foreach ($request->only($allowed) as $key => $value) {
            // Skip file fields — already handled above
            if (in_array($key, $fileFields)) {
                continue;
            }
            // Skip empty values for encrypted keys to preserve existing secrets
            if (in_array($key, SystemSetting::ENCRYPTED_KEYS) && ($value === null || $value === '')) {
                continue;
            }

            if (in_array($key, $booleanKeys)) {
                $storeValue = $request->boolean($key) ? 'true' : 'false';
            } elseif (in_array($key, SystemSetting::ENCRYPTED_KEYS)) {
                $storeValue = encrypt($value);
            } else {
                $storeValue = $value ?? '';
            }

            DB::table('system_settings')->updateOrInsert(
                ['key' => $key],
                ['value' => $storeValue, 'group' => $group, 'updated_at' => now()]
            );
        }

        SystemSetting::clearCache();

        // Forms hosted on pages OTHER than /admin/settings can opt in to
        // a back() redirect by including a hidden `_redirect_back=1`
        // input — keeps the user on their original page after save so
        // they can immediately see the persisted values. Used by the
        // Global SEO Defaults form on /admin/cms.
        if ($request->boolean('_redirect_back')) {
            return back()->with('success', 'Settings saved successfully.');
        }

        return to_route('admin.settings', ['tab' => $group])->with('success', 'Settings saved successfully.');
    }

    /**
     * Send a test email to verify SMTP configuration.
     */
    public function testSmtp(Request $request): RedirectResponse
    {
        $request->validate([
            'test_email' => 'required|email',
        ]);

        // Pull saved SMTP settings
        $smtpHost = trim((string) SystemSetting::get('smtp_host'));
        $smtpPort = (int) SystemSetting::get('smtp_port', '587');
        $smtpUser = (string) SystemSetting::get('smtp_username');
        $smtpPass = (string) SystemSetting::get('smtp_password');
        $smtpEnc  = strtolower((string) SystemSetting::get('smtp_encryption', 'tls'));
        $fromAddr = SystemSetting::get('smtp_from_address', SystemSetting::get('mail_from_email', config('mail.from.address')));
        $fromName = SystemSetting::get('smtp_from_name', SystemSetting::get('mail_from_name', config('mail.from.name')));

        // ── Pre-flight checks ──────────────────────────────────────────
        if ($smtpHost === '') {
            return redirect()->back()->with('error', 'SMTP host is empty — fill in the SMTP form above and click Save before testing.');
        }
        if ($smtpPort < 1 || $smtpPort > 65535) {
            return redirect()->back()->with('error', "SMTP port {$smtpPort} is invalid. Common values: 587 (TLS/STARTTLS), 465 (SSL), 25 (unencrypted).");
        }
        if (!in_array($smtpEnc, ['tls', 'ssl', 'none', ''], true)) {
            return redirect()->back()->with('error', "SMTP encryption '{$smtpEnc}' is invalid. Use 'tls', 'ssl', or 'none'.");
        }
        if (empty($fromAddr)) {
            return redirect()->back()->with('error', 'From-address is empty — set "Mail From Email" in the form above and Save.');
        }

        // Port + encryption sanity check (warns about common misconfigs)
        $portWarning = null;
        if ($smtpPort === 465 && $smtpEnc !== 'ssl') {
            $portWarning = 'Port 465 normally pairs with SSL encryption (you have ' . ($smtpEnc ?: 'none') . '). Most providers reject this combo.';
        } elseif ($smtpPort === 587 && $smtpEnc !== 'tls') {
            $portWarning = 'Port 587 normally pairs with TLS/STARTTLS (you have ' . ($smtpEnc ?: 'none') . '). Most providers reject this combo.';
        } elseif ($smtpPort === 25 && in_array($smtpEnc, ['tls', 'ssl'], true)) {
            $portWarning = "Port 25 is typically unencrypted but you have '{$smtpEnc}' encryption. Many shared hosts also block port 25 entirely.";
        }

        // Reachability check — open a TCP socket to host:port before bothering with TLS handshake
        $errno = 0; $errstr = '';
        $sock = @fsockopen($smtpHost, $smtpPort, $errno, $errstr, 10);
        if (!$sock) {
            $hint = $errno === 0
                ? "DNS lookup failed for {$smtpHost}. Double-check the hostname has no typo."
                : "Could not connect to {$smtpHost}:{$smtpPort} — {$errstr} (errno {$errno}).";
            $extra = $smtpPort === 25 ? ' Port 25 is blocked by most shared hosting providers — try 587 (TLS) or 465 (SSL) instead.' : '';
            return redirect()->back()->with('error', "SMTP host unreachable. {$hint}{$extra}");
        }
        fclose($sock);

        // ── Apply config dynamically and attempt the actual send ──────
        config([
            'mail.default' => 'smtp',
            'mail.mailers.smtp' => SmtpConfig::build([
                'host' => $smtpHost,
                'port' => $smtpPort,
                'username' => $smtpUser,
                'password' => $smtpPass,
                'encryption' => $smtpEnc,
                'timeout' => 15,
                'local_domain' => parse_url(config('app.url'), PHP_URL_HOST),
            ]),
            'mail.from.address' => $fromAddr,
            'mail.from.name' => $fromName,
        ]);

        Mail::purge('smtp');

        try {
            Mail::raw(
                'This is a test email from ' . config('app.name') . ' to verify your SMTP configuration is working correctly.' .
                "\n\nSent at: " . now()->format('Y-m-d H:i:s T') .
                "\nHost: {$smtpHost}:{$smtpPort} ({$smtpEnc})" .
                "\nFrom: {$fromAddr}",
                function ($message) use ($request) {
                    $message->to($request->input('test_email'))
                        ->subject(config('app.name') . ' SMTP Test - ' . now()->format('Y-m-d H:i'));
                }
            );

            $msg = 'Test email sent successfully to ' . $request->input('test_email') . '. Check your inbox + spam folder.';
            if ($portWarning) {
                $msg .= ' (Note: ' . $portWarning . ')';
            }
            return redirect()->back()->with('success', $msg);
        } catch (\Throwable $e) {
            $raw = $e->getMessage();
            $hint = $this->diagnoseSmtpError($raw, $smtpHost, $smtpPort, $smtpEnc, $smtpUser);

            \Illuminate\Support\Facades\Log::warning('SMTP test failed', [
                'host'       => $smtpHost,
                'port'       => $smtpPort,
                'encryption' => $smtpEnc,
                'username'   => $smtpUser,
                'from'       => $fromAddr,
                'error'      => $raw,
            ]);

            $detail = "SMTP test failed: {$hint}";
            if ($portWarning) {
                $detail .= ' Also: ' . $portWarning;
            }
            $detail .= ' (Raw error: ' . \Illuminate\Support\Str::limit($raw, 200) . ')';

            return redirect()->back()->with('error', $detail);
        }
    }

    /**
     * Map raw SMTP transport errors to actionable hints. Keeps customers out
     * of the laravel.log when a setting is just slightly off.
     */
    private function diagnoseSmtpError(string $raw, string $host, int $port, string $enc, string $user): string
    {
        $r = strtolower($raw);

        // Authentication failures
        if (str_contains($r, 'authentication failed') || str_contains($r, 'auth failed') || str_contains($r, '535') || str_contains($r, 'invalid login')) {
            $isGmail = str_contains($host, 'gmail') || str_contains($host, 'google');
            if ($isGmail) {
                return "Gmail rejected the credentials. Gmail no longer accepts your regular password — you must generate an App Password at https://myaccount.google.com/apppasswords (2FA must be enabled first), then paste THAT 16-char password here.";
            }
            return "SMTP server rejected the username/password. Double-check both fields. Some providers (Outlook, Yahoo, iCloud) require an app-specific password rather than your account password.";
        }

        // TLS / SSL handshake errors
        if (str_contains($r, 'ssl') || str_contains($r, 'tls') || str_contains($r, 'stream_socket_enable_crypto') || str_contains($r, 'handshake')) {
            $other = $enc === 'tls' ? 'ssl' : 'tls';
            return "Encryption handshake failed. Your '{$enc}' setting probably doesn't match the server. Try '{$other}' instead, or check that the port matches (587=TLS, 465=SSL).";
        }

        // Connection / timeout
        if (str_contains($r, 'connection') && (str_contains($r, 'refused') || str_contains($r, 'timed out') || str_contains($r, 'reset'))) {
            return "Connection to {$host}:{$port} was refused or timed out. Likely causes: (1) wrong port, (2) firewall blocking outbound traffic on this port — many shared hosts block 25 — try 587 or 465.";
        }

        // From-address rejected
        if (str_contains($r, '550') || str_contains($r, 'sender') || str_contains($r, 'from address')) {
            return "Server rejected the From address. Most providers require it to match your authenticated user (e.g. From must equal {$user} or a verified alias).";
        }

        // Recipient rejected
        if (str_contains($r, '553') || str_contains($r, 'recipient') || str_contains($r, '554')) {
            return "Server rejected the recipient. Some providers (especially with SPF/DKIM enforcement) won't let you send to external addresses without proper DNS records.";
        }

        // DNS / hostname
        if (str_contains($r, 'name or service not known') || str_contains($r, 'getaddrinfo') || str_contains($r, 'could not resolve')) {
            return "DNS lookup for {$host} failed. The hostname is wrong or your server can't resolve it.";
        }

        return $raw;
    }

    /**
     * Toggle application maintenance mode on/off.
     *
     * Why this isn't simple:
     *
     * Laravel's `php artisan down --secret=XXX` writes a flag file that
     * blocks ALL requests with a 503, including the admin's own browser.
     * To bypass, the visitor has to hit `/XXX` once — that route sets a
     * signed `laravel_maintenance` cookie which whitelists their session
     * for 12 hours. Without that cookie, even the admin who flipped the
     * switch can't reach the site (they'd have to delete
     * storage/framework/down by FTP — bad).
     *
     * So when ENABLING:
     *   1. We generate the secret + call `down`
     *   2. Persist the secret to system_settings so the admin can fetch
     *      it later from this same admin route (admin routes are NOT
     *      exempted by the framework — but our admin group's auth check
     *      runs BEFORE the maintenance middleware in our middleware
     *      stack, so /admin/* still works for logged-in admins via the
     *      bypass cookie)
     *   3. Redirect the admin directly to the bypass URL — that single
     *      hop sets the cookie and bounces them back to /admin/dashboard
     *      so they're never locked out.
     */
    public function toggleMaintenance(Request $request): RedirectResponse
    {
        if (app()->isDownForMaintenance()) {
            Artisan::call('up');
            \App\Models\SystemSetting::set('maintenance_secret', '');
            \App\Models\SystemSetting::set('maintenance_started_at', '');
            return redirect()->back()->with('success', 'Maintenance mode disabled. Site is now live.');
        }

        $secret = Str::random(32);

        try {
            Artisan::call('down', [
                '--secret' => $secret,
                // Refresh framework cache so the down file is re-read
                '--render' => 'errors::503',
            ]);
        } catch (\Throwable $e) {
            // Older Laravel versions don't support --render; fall back.
            Artisan::call('down', ['--secret' => $secret]);
        }

        \App\Models\SystemSetting::set('maintenance_secret', $secret);
        \App\Models\SystemSetting::set('maintenance_started_at', now()->toIso8601String());

        $bypassUrl = rtrim(config('app.url'), '/') . '/' . $secret;

        // Previously we redirected the admin THROUGH the bypass URL
        // (settings → /secret → / → wherever) so Laravel's maintenance
        // middleware would set the bypass cookie automatically. Two
        // problems with that chain:
        //
        //   1. The flash data ('maintenance_bypass_url') was consumed
        //      by the redirect hops and the admin never saw the URL.
        //   2. On some hosts the cookie didn't stick — admin was
        //      locked out with no UI showing the secret. Only fix
        //      was deleting storage/framework/down by FTP.
        //
        // Now we redirect straight back to the maintenance settings
        // tab. The view at admin/settings.blade.php reads the stored
        // `maintenance_secret` from SystemSetting and renders the URL
        // in a prominent copy-able banner — so even if the admin
        // closes the tab and revisits later, the URL is still there.
        //
        // The admin still needs to OPEN the bypass URL once to get
        // the cookie. The banner has a "Copy" + "Open" button for
        // that. After clicking "Open", Laravel's own middleware sets
        // the cookie correctly via its native mechanism.
        return redirect()
            ->route('admin.settings', ['tab' => 'maintenance'])
            ->with([
                'success' => 'Maintenance mode enabled. Bookmark the bypass URL shown below — without it you cannot reach the admin panel from a new browser.',
                'maintenance_bypass_url' => $bypassUrl,
                'maintenance_secret'     => $secret,
            ]);
    }

    /**
     * AI usage statistics dashboard.
     */
    public function aiUsage()
    {
        $stats = DB::table('ai_usage_logs')
            ->selectRaw("COUNT(*) as total_requests, COALESCE(SUM(tokens_in + tokens_out), 0) as total_tokens, COALESCE(SUM(cost), 0) as total_cost")
            ->first();

        $byProvider = DB::table('ai_usage_logs')
            ->selectRaw("provider, model, COUNT(*) as requests, COALESCE(SUM(cost), 0) as cost, COALESCE(SUM(tokens_in + tokens_out), 0) as tokens")
            ->groupBy('provider', 'model')
            ->orderByDesc('requests')
            ->limit(10)
            ->get();

        return view('admin.ai-usage', compact('stats', 'byProvider'));
    }

    /**
     * Run a live S3/CDN connection test using either the saved settings
     * or values the admin just typed (overrides). Returns JSON so the
     * Storage tab can show inline feedback without a page reload.
     *
     * Why accept overrides instead of forcing save-first: admins need to
     * verify credentials BEFORE persisting them — typos in a saved S3
     * secret can break uploads for everyone.
     */
    public function testStorage(Request $request): JsonResponse
    {
        [$ok, $msg] = StorageHelper::testConnection([
            'key'        => $request->input('s3_key'),
            'secret'     => $request->input('s3_secret') ?: SystemSetting::get('s3_secret', ''),
            'bucket'     => $request->input('s3_bucket'),
            'region'     => $request->input('s3_region', 'us-east-1'),
            'endpoint'   => $request->input('s3_endpoint') ?: null,
            'path_style' => $request->boolean('s3_path_style'),
        ]);

        return response()->json(['ok' => $ok, 'message' => $msg]);
    }

    /**
     * Email deliverability statistics dashboard.
     */
    public function emailDeliverability()
    {
        $stats = DB::table('messages')
            ->where('direction', 'outbound')
            ->selectRaw("COUNT(*) as total, SUM(delivery_status = 'sent' OR delivery_status = 'delivered') as delivered, SUM(delivery_status = 'bounced') as bounced, SUM(delivery_status = 'failed') as failed")
            ->first();

        $recentBounces = Message::where('delivery_status', 'bounced')
            ->latest()
            ->limit(10)
            ->get();

        return view('admin.email-deliverability', compact('stats', 'recentBounces'));
    }
}
