<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use Illuminate\View\View;

class SystemController extends Controller
{
    /**
     * System health dashboard.
     */
    public function index(Request $request): View
    {
        // ------------------------------------------------------------------
        // Queue health
        // ------------------------------------------------------------------

        $pendingJobs = 0;
        $failedJobs = 0;

        try {
            $pendingJobs = DB::table('jobs')->count();
        } catch (\Exception $e) {
            // Jobs table might not exist
        }

        try {
            $failedJobs = DB::table('failed_jobs')->count();
        } catch (\Exception $e) {
            // Failed jobs table might not exist
        }

        // Queue depth by queue name
        $queueDepths = [];
        try {
            $queueDepths = DB::table('jobs')
                ->selectRaw('queue, COUNT(*) as count')
                ->groupBy('queue')
                ->pluck('count', 'queue')
                ->toArray();
        } catch (\Exception $e) {
            // Ignore
        }

        // Recent failed jobs
        $recentFailedJobs = [];
        try {
            $recentFailedJobs = DB::table('failed_jobs')
                ->orderByDesc('failed_at')
                ->limit(20)
                ->get(['id', 'queue', 'payload', 'exception', 'failed_at'])
                ->map(function ($job) {
                    // Extract job class name from payload
                    $payload = json_decode($job->payload, true);
                    $job->job_class = $payload['displayName'] ?? 'Unknown';
                    // SEC-007: Sanitize exception output — may contain connection strings or credentials
                    $job->exception_summary = $this->sanitizeLogContent(mb_substr($job->exception, 0, 300));
                    return $job;
                });
        } catch (\Exception $e) {
            // Ignore
        }

        // ------------------------------------------------------------------
        // Database health
        // ------------------------------------------------------------------

        $dbSize = null;
        try {
            $dbName = config('database.connections.' . config('database.default') . '.database');
            $result = DB::select("
                SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
                FROM information_schema.tables
                WHERE table_schema = ?
            ", [$dbName]);
            $dbSize = $result[0]->size_mb ?? null;
        } catch (\Exception $e) {
            // Not MySQL or no permission
        }

        // Table row counts for key tables
        $tableCounts = [];
        $keyTables = [
            'users', 'workspaces', 'contacts', 'conversations', 'messages',
            'campaigns', 'workflows', 'subscriptions', 'payments',
            'kb_documents', 'ai_usage_logs',
        ];
        foreach ($keyTables as $table) {
            try {
                $tableCounts[$table] = DB::table($table)->count();
            } catch (\Exception $e) {
                $tableCounts[$table] = 'N/A';
            }
        }

        // ------------------------------------------------------------------
        // Disk usage
        // ------------------------------------------------------------------

        $storagePath = storage_path();
        $diskFree = @disk_free_space($storagePath);
        $diskTotal = @disk_total_space($storagePath);
        $diskUsage = [
            'free_gb' => $diskFree !== false ? round($diskFree / 1073741824, 2) : null,
            'total_gb' => $diskTotal !== false ? round($diskTotal / 1073741824, 2) : null,
            'used_percent' => ($diskFree !== false && $diskTotal !== false && $diskTotal > 0)
                ? round((1 - $diskFree / $diskTotal) * 100, 1)
                : null,
        ];

        // Storage directory sizes
        $storageSizes = [];
        $storageDirs = ['app', 'logs', 'framework/cache', 'framework/sessions'];
        foreach ($storageDirs as $dir) {
            $fullPath = storage_path($dir);
            if (is_dir($fullPath)) {
                $storageSizes[$dir] = $this->getDirectorySizeMb($fullPath);
            }
        }

        // ------------------------------------------------------------------
        // Recent errors from log
        // ------------------------------------------------------------------

        $recentErrors = [];
        $logFile = storage_path('logs/laravel.log');
        if (file_exists($logFile)) {
            $logContent = $this->tailFile($logFile, 5000);

            // SEC-007: Sanitize log content before display to prevent secret leakage
            $logContent = $this->sanitizeLogContent($logContent);

            // Extract ERROR entries
            preg_match_all(
                '/\[\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}[^\]]*\]\s*(local|production)\.ERROR:.*/',
                $logContent,
                $matches
            );
            $recentErrors = array_slice(array_reverse($matches[0] ?? []), 0, 20);
            $recentErrors = array_map(fn ($e) => mb_substr($e, 0, 500), $recentErrors);
        }

        // ------------------------------------------------------------------
        // Application info
        // ------------------------------------------------------------------

        $appInfo = [
            'laravel_version' => app()->version(),
            'php_version' => PHP_VERSION,
            'environment' => app()->environment(),
            'debug_mode' => config('app.debug'),
            'cache_driver' => config('cache.default'),
            'queue_driver' => config('queue.default'),
            'session_driver' => config('session.driver'),
        ];

        return view('admin.system', compact(
            'pendingJobs',
            'failedJobs',
            'queueDepths',
            'recentFailedJobs',
            'dbSize',
            'tableCounts',
            'diskUsage',
            'storageSizes',
            'recentErrors',
            'appInfo',
        ));
    }

    /**
     * Calculate directory size in MB.
     */
    private function getDirectorySizeMb(string $path): float
    {
        $size = 0;

        try {
            $iterator = new \RecursiveIteratorIterator(
                new \RecursiveDirectoryIterator($path, \RecursiveDirectoryIterator::SKIP_DOTS),
                \RecursiveIteratorIterator::LEAVES_ONLY
            );

            foreach ($iterator as $file) {
                if ($file->isFile()) {
                    $size += $file->getSize();
                }
            }
        } catch (\Exception $e) {
            // Permission error or similar
        }

        return round($size / 1048576, 2); // Convert to MB
    }

    /**
     * SEC-007: Sanitize log content to redact secrets, tokens, passwords, and
     * database connection strings before displaying in the admin panel.
     */
    private function sanitizeLogContent(string $content): string
    {
        $patterns = [
            // Environment variable secrets (API_KEY=..., DB_PASSWORD=..., etc.)
            '/([A-Za-z0-9_]+_KEY|[A-Za-z0-9_]+_SECRET|[A-Za-z0-9_]+_PASSWORD|[A-Za-z0-9_]+_TOKEN)=([^\s]+)/i'
                => '$1=[REDACTED]',
            // Bearer tokens in log output
            '/Bearer\s+[A-Za-z0-9\-._~+\/]+=*/i'
                => 'Bearer [REDACTED]',
            // Quoted password assignments (password="...", password: "...")
            '/password["\']\s*[=:]\s*["\'][^"\']+["\']/i'
                => 'password=[REDACTED]',
            // Database connection URLs (mysql://user:pass@host/db)
            '/(?:mysql|pgsql|sqlite|redis|mongodb):\/\/[^\s]+/i'
                => '[DATABASE_URL_REDACTED]',
            // Stripe secret keys
            '/sk_(live|test)_[A-Za-z0-9]+/i'
                => 'sk_$1_[REDACTED]',
            // Generic long hex/base64 strings that look like API keys (32+ chars)
            '/(?<=["\':=\s])[A-Fa-f0-9]{40,}(?=["\'\s,;])/i'
                => '[POSSIBLE_SECRET_REDACTED]',
        ];

        foreach ($patterns as $pattern => $replacement) {
            $content = preg_replace($pattern, $replacement, $content);
        }

        return $content;
    }

    /**
     * Read the last N bytes from a file (tail).
     */
    private function tailFile(string $path, int $bytes = 5000): string
    {
        if (!file_exists($path)) {
            return '';
        }

        $fileSize = filesize($path);
        if ($fileSize <= $bytes) {
            return file_get_contents($path);
        }

        $handle = fopen($path, 'r');
        fseek($handle, -$bytes, SEEK_END);
        $content = fread($handle, $bytes);
        fclose($handle);

        return $content;
    }

    /**
     * One-shot cleanup for duplicate synced messages + duplicate "Unknown"
     * conversations. Streams plain-text output so the admin can watch
     * progress in the browser.
     *
     * Mounted at GET /admin/system/dedupe (admin-only, web auth).
     *
     * Query params:
     *   ?dry=1            preview only, no deletes
     *   ?workspace=2      scope to a single workspace
     *
     * Bug background:
     *   1. Duplicate MESSAGES — same email synced thousands of times
     *      because the RFC Message-ID header was missing (NULL bypasses
     *      MySQL's unique index since NULL != NULL on unique indexes).
     *   2. Duplicate CONVERSATIONS — "Unknown / Contact Mail" rows
     *      multiplied because findOrCreateConversation() skipped its
     *      subject-match check whenever the From address couldn't be
     *      parsed into a Contact row.
     *
     * The forward-fix lives in EmailSyncService::isAlreadySynced() and
     * the contact-less branch of findOrCreateConversation(). This route
     * is here to clean up the rows already in the DB from before the fix.
     */
    public function dedupe(Request $request): \Symfony\Component\HttpFoundation\StreamedResponse
    {
        $dryRun  = $request->boolean('dry');
        $scopeWs = (int) $request->query('workspace', 0) ?: null;

        // Same plain-text-stream pattern the legacy public/check.php used,
        // but routed through Laravel auth so only admins can hit it.
        return response()->stream(function () use ($dryRun, $scopeWs) {
            @set_time_limit(0);
            @ini_set('memory_limit', '512M');

            $emit = function (string $line) {
                echo $line . "\n";
                @ob_flush();
                @flush();
            };

            $emit($dryRun
                ? '═══ DRY RUN — nothing will be deleted ═══'
                : '═══ LIVE RUN — deleting duplicates ═══');
            $emit('');
            if ($scopeWs) {
                $emit("Scoped to workspace #{$scopeWs}");
                $emit('');
            }

            $affectedConversationIds = [];
            $totalMessagesDeleted    = 0;
            $totalConvsMerged        = 0;
            $totalConvsEmpty         = 0;

            // ────────────────────────────────────────────────────────
            // PHASE A — Merge duplicate "Unknown" conversations
            // ────────────────────────────────────────────────────────
            $emit('── Phase A: merging duplicate "Unknown" conversations ──');

            $convGroupsQuery = DB::table('conversations')
                ->selectRaw('workspace_id, email_account_id, subject, MIN(id) as keeper_id, COUNT(*) as cnt')
                ->whereNull('contact_id')
                ->whereNull('deleted_at')
                ->whereNotNull('subject')
                ->where('subject', '!=', '')
                ->groupBy('workspace_id', 'email_account_id', 'subject')
                ->havingRaw('COUNT(*) > 1');

            if ($scopeWs) {
                $convGroupsQuery->where('workspace_id', $scopeWs);
            }

            $convGroups = $convGroupsQuery->get();

            if ($convGroups->isEmpty()) {
                $emit('  No duplicate "Unknown" conversation groups found.');
            } else {
                foreach ($convGroups as $g) {
                    $keeperId = (int) $g->keeper_id;

                    $dupIds = DB::table('conversations')
                        ->where('workspace_id', $g->workspace_id)
                        ->where('email_account_id', $g->email_account_id)
                        ->where('subject', $g->subject)
                        ->whereNull('contact_id')
                        ->whereNull('deleted_at')
                        ->where('id', '!=', $keeperId)
                        ->pluck('id')
                        ->all();

                    if (empty($dupIds)) {
                        continue;
                    }

                    if (!$dryRun) {
                        DB::table('messages')
                            ->whereIn('conversation_id', $dupIds)
                            ->update(['conversation_id' => $keeperId]);

                        foreach (['conversation_viewers', 'audit_logs'] as $table) {
                            if (Schema::hasTable($table) && Schema::hasColumn($table, 'conversation_id')) {
                                DB::table($table)
                                    ->whereIn('conversation_id', $dupIds)
                                    ->update(['conversation_id' => $keeperId]);
                            }
                        }

                        DB::table('conversations')->whereIn('id', $dupIds)->delete();
                    }

                    $affectedConversationIds[] = $keeperId;
                    $totalConvsMerged += count($dupIds);

                    $verb = $dryRun ? 'would merge' : 'merged';
                    $shortSubject = mb_substr($g->subject, 0, 60);
                    $emit('  Subject "' . $shortSubject . '" — ' . $verb . ' ' . count($dupIds) . ' duplicate(s) into conv #' . $keeperId);
                }

                $verb = $dryRun ? 'would merge' : 'merged';
                $emit('  Total: ' . $convGroups->count() . ' group(s), ' . $verb . ' ' . $totalConvsMerged . ' conversation(s)');
            }
            $emit('');

            // ────────────────────────────────────────────────────────
            // PHASE B — Delete duplicate messages
            // ────────────────────────────────────────────────────────
            $emit('── Phase B: deleting duplicate messages ──');

            $deleteMessageDupes = function (
                string $label,
                array $groupKeys,
                ?\Closure $extraWhere
            ) use ($scopeWs, $dryRun, $emit, &$affectedConversationIds, &$totalMessagesDeleted) {
                $groupBy = implode(', ', $groupKeys);

                $q = DB::table('messages')
                    ->selectRaw($groupBy . ', MIN(id) as keeper_id, COUNT(*) as cnt')
                    ->groupByRaw($groupBy)
                    ->havingRaw('COUNT(*) > 1');

                if ($scopeWs) {
                    $q->where('workspace_id', $scopeWs);
                }
                if ($extraWhere) {
                    $extraWhere($q);
                }

                $groups = $q->get();
                if ($groups->isEmpty()) {
                    $emit('  [' . $label . '] no duplicate groups.');
                    return 0;
                }

                $deleted = 0;
                foreach ($groups as $group) {
                    $keeperId = (int) $group->keeper_id;

                    $dupQ = DB::table('messages')->where('id', '!=', $keeperId);
                    if ($scopeWs) {
                        $dupQ->where('workspace_id', $scopeWs);
                    }
                    foreach ($groupKeys as $key) {
                        $dupQ->where($key, $group->{$key});
                    }
                    if ($extraWhere) {
                        $extraWhere($dupQ);
                    }

                    $convIds = $dupQ->pluck('conversation_id')->all();
                    foreach ($convIds as $cid) {
                        if ($cid) {
                            $affectedConversationIds[] = (int) $cid;
                        }
                    }

                    $deleted += $dryRun ? $dupQ->count() : $dupQ->delete();
                }

                $verb = $dryRun ? 'would delete' : 'deleted';
                $emit('  [' . $label . '] ' . $groups->count() . ' group(s), ' . $verb . ' ' . $deleted . ' row(s)');

                $totalMessagesDeleted += $deleted;
                return $deleted;
            };

            // B1 — same RFC Message-ID
            $deleteMessageDupes(
                'same Message-ID header',
                ['workspace_id', 'message_id_header'],
                fn ($q) => $q->whereNotNull('message_id_header')->where('message_id_header', '!=', '')
            );

            // B2 — same channel id (Gmail / Outlook)
            $deleteMessageDupes(
                'same channel_message_id (Gmail/Outlook)',
                ['workspace_id', 'channel_message_id'],
                fn ($q) => $q->whereNotNull('channel_message_id')->where('channel_message_id', '!=', '')
            );

            // B3 — same imap_uid + folder per email account (needs conversation join)
            $emit('  [same imap_uid + folder per account]');

            $imapGroupsQ = DB::table('messages')
                ->join('conversations', 'conversations.id', '=', 'messages.conversation_id')
                ->whereNotNull('messages.imap_uid')
                ->whereNotNull('messages.imap_folder')
                ->whereNotNull('conversations.email_account_id')
                ->selectRaw('conversations.email_account_id, messages.imap_folder, messages.imap_uid, MIN(messages.id) as keeper_id, COUNT(*) as cnt')
                ->groupBy('conversations.email_account_id', 'messages.imap_folder', 'messages.imap_uid')
                ->havingRaw('COUNT(*) > 1');

            if ($scopeWs) {
                $imapGroupsQ->where('messages.workspace_id', $scopeWs);
            }

            $imapGroups  = $imapGroupsQ->get();
            $imapDeleted = 0;
            foreach ($imapGroups as $group) {
                $keeperId = (int) $group->keeper_id;

                $dupQ = DB::table('messages')
                    ->join('conversations', 'conversations.id', '=', 'messages.conversation_id')
                    ->where('conversations.email_account_id', $group->email_account_id)
                    ->where('messages.imap_folder', $group->imap_folder)
                    ->where('messages.imap_uid', $group->imap_uid)
                    ->where('messages.id', '!=', $keeperId);
                if ($scopeWs) {
                    $dupQ->where('messages.workspace_id', $scopeWs);
                }

                $ids     = $dupQ->pluck('messages.id')->all();
                $convIds = $dupQ->pluck('messages.conversation_id')->all();
                foreach ($convIds as $cid) {
                    if ($cid) {
                        $affectedConversationIds[] = (int) $cid;
                    }
                }

                if (!empty($ids)) {
                    $imapDeleted += $dryRun
                        ? count($ids)
                        : DB::table('messages')->whereIn('id', $ids)->delete();
                }
            }
            $verb = $dryRun ? 'would delete' : 'deleted';
            $emit('    ' . $imapGroups->count() . ' group(s), ' . $verb . ' ' . $imapDeleted . ' row(s)');
            $totalMessagesDeleted += $imapDeleted;

            // B4 — no identifier at all (worst case)
            $deleteMessageDupes(
                'no identifiers (same from + subject + sent_at within conversation)',
                ['conversation_id', 'from_email', 'subject', 'sent_at'],
                fn ($q) => $q
                    ->whereNull('message_id_header')
                    ->whereNull('channel_message_id')
                    ->whereNull('imap_uid')
            );

            $emit('');

            // ────────────────────────────────────────────────────────
            // PHASE C — Recalculate messages_count
            // ────────────────────────────────────────────────────────
            if (!$dryRun && !empty($affectedConversationIds)) {
                $emit('── Phase C: recalculating conversation message counts ──');
                $unique = array_unique($affectedConversationIds);
                foreach (array_chunk($unique, 500) as $chunk) {
                    DB::statement('
                        UPDATE conversations
                        SET messages_count = (
                            SELECT COUNT(*) FROM messages WHERE messages.conversation_id = conversations.id
                        )
                        WHERE id IN (' . implode(',', array_map('intval', $chunk)) . ')
                    ');
                }
                $emit('  Recalculated ' . count($unique) . ' conversation(s)');
                $emit('');
            }

            // ────────────────────────────────────────────────────────
            // PHASE D — Sweep empty conversations
            // ────────────────────────────────────────────────────────
            $emit('── Phase D: sweeping empty conversations ──');

            $emptyConvsSelect = DB::table('conversations')
                ->leftJoin('messages', 'messages.conversation_id', '=', 'conversations.id')
                ->whereNull('conversations.deleted_at')
                ->whereNull('messages.id')
                ->select('conversations.id');

            if ($scopeWs) {
                $emptyConvsSelect->where('conversations.workspace_id', $scopeWs);
            }

            $emptyIds = $emptyConvsSelect->pluck('conversations.id')->all();

            if (empty($emptyIds)) {
                $emit('  No empty conversations to sweep.');
            } else {
                $verb = $dryRun ? 'would delete' : 'deleted';
                if (!$dryRun) {
                    DB::table('conversations')->whereIn('id', $emptyIds)->update([
                        'deleted_at' => now(),
                        'updated_at' => now(),
                    ]);
                }
                $totalConvsEmpty = count($emptyIds);
                $emit('  ' . $verb . ' ' . $totalConvsEmpty . ' empty conversation(s)');
            }
            $emit('');

            // ────────────────────────────────────────────────────────
            // Summary
            // ────────────────────────────────────────────────────────
            $emit('═══ DONE ═══');
            $verb = $dryRun ? 'would' : 'did';
            $emit('  ' . $verb . ' merge:  ' . $totalConvsMerged . ' duplicate conversation(s)');
            $emit('  ' . $verb . ' delete: ' . $totalMessagesDeleted . ' duplicate message row(s)');
            $emit('  ' . $verb . ' sweep:  ' . $totalConvsEmpty . ' empty conversation(s)');
            $emit('');

            if ($dryRun) {
                $emit('Re-run WITHOUT ?dry=1 to actually clean up.');
            } else {
                $emit('Cleanup complete. You can leave this route in place — it requires admin auth.');
            }

            Log::info('Admin dedupe route ran', [
                'dry_run'           => $dryRun,
                'scope_workspace'   => $scopeWs,
                'conversations_merged' => $totalConvsMerged,
                'messages_deleted'  => $totalMessagesDeleted,
                'empty_swept'       => $totalConvsEmpty,
                'admin_user_id'     => auth()->id(),
            ]);
        }, 200, [
            'Content-Type'      => 'text/plain; charset=utf-8',
            'X-Accel-Buffering' => 'no',
            'Cache-Control'     => 'no-cache, no-store',
        ]);
    }
}
