<?php

namespace App\Http\Controllers\Developer;

use App\Http\Controllers\Controller;
use App\Models\Tenant;
use App\Models\User;
use App\Models\Subscription;
use App\Models\ChatSession;
use App\Models\Plan;
use App\Services\TenantSyncService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Config;
use Carbon\Carbon;
use Illuminate\Support\Facades\File;

class DashboardController extends Controller
{
    protected $syncService;

    public function __construct(TenantSyncService $syncService)
    {
        $this->syncService = $syncService;
    }

    public function serverConfig()
    {
        $envContent = File::get(base_path('.env'));
        $serverInfo = [
            'php_version' => PHP_VERSION,
            'laravel_version' => app()->version(),
            'server_software' => $_SERVER['SERVER_SOFTWARE'] ?? 'Unknown',
            'os' => PHP_OS,
            'database_driver' => config('database.default'),
            'cache_driver' => config('cache.default'),
            'session_driver' => config('session.driver'),
            'memory_limit' => ini_get('memory_limit'),
            'upload_max_filesize' => ini_get('upload_max_filesize'),
            'post_max_size' => ini_get('post_max_size'),
            'max_execution_time' => ini_get('max_execution_time'),
        ];

        return view('developer.server.index', compact('envContent', 'serverInfo'));
    }

    public function updateEnv(Request $request)
    {
        $request->validate(['env_content' => 'required']);

        try {
            File::put(base_path('.env'), $request->env_content);
            Artisan::call('config:clear');
            return response()->json(['success' => true, 'message' => '.env file updated and config cache cleared.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function clearAllCaches()
    {
        try {
            Artisan::call('cache:clear');
            Artisan::call('config:clear');
            Artisan::call('route:clear');
            Artisan::call('view:clear');
            return response()->json(['success' => true, 'message' => 'All caches cleared successfully.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function runArtisanCommand(Request $request)
    {
        $request->validate(['command' => 'required|string']);
        $command = $request->command;

        // Whitelist safe commands
        $allowedCommands = [
            'config:cache',
            'config:clear',
            'route:cache',
            'route:clear',
            'view:cache',
            'view:clear',
            'optimize',
            'optimize:clear'
        ];

        if (!in_array($command, $allowedCommands)) {
            return response()->json(['success' => false, 'message' => 'Command not allowed.'], 403);
        }

        try {
            Artisan::call($command);
            return response()->json([
                'success' => true, 
                'message' => "Command 'php artisan {$command}' executed successfully."
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function index(Request $request)
    {
        try {
            $selectedTenantId = $request->input('tenant_id');
            
            // Get tenant statistics
            $tenantStats = $this->getTenantStatistics();
            
            // Get recent tenant activities
            $recentActivities = $this->getRecentTenantActivities();
            
            // Get application logs
            $applicationLogs = $this->getApplicationLogs();
            
            // Get database information
            $databaseInfo = $this->getDatabaseInfo();
            
            // Add active queries for dashboard overview
            $query = DB::connection('mysql')->table('information_schema.processlist')
                ->select(['ID', 'USER', 'HOST', 'DB', 'COMMAND', 'TIME', 'STATE', 'INFO'])
                ->where('COMMAND', '!=', 'Sleep');

            if ($selectedTenantId && $selectedTenantId !== 'system') {
                $tenant = Tenant::find($selectedTenantId);
                if ($tenant) {
                    $query->where(function($q) use ($tenant) {
                        $q->where('DB', $tenant->database_name)
                          ->orWhere('INFO', 'LIKE', "%{$tenant->database_name}%");
                    });
                }
            }

            $activeQueries = $query->orderBy('TIME', 'DESC')
                ->limit(5)
                ->get();
            
            return view('developer.dashboard.index', compact(
                'tenantStats',
                'recentActivities',
                'applicationLogs',
                'databaseInfo',
                'activeQueries',
                'selectedTenantId'
            ));
        } catch (\Exception $e) {
            // Log the error and return a simple response
            \Log::error('Developer dashboard error: ' . $e->getMessage());
            
            return view('developer.dashboard.index', [
                'tenantStats' => [
                    'total' => 0,
                    'active' => 0,
                    'trialing' => 0,
                    'expired' => 0,
                    'new_this_month' => 0,
                    'with_subscriptions' => 0,
                    'total_users' => 0,
                    'active_sessions' => 0,
                ],
                'recentActivities' => collect([]),
                'applicationLogs' => collect([]),
                'activeQueries' => collect([]),
                'databaseInfo' => [
                    'size' => 0,
                    'tables' => [],
                    'connection' => [
                        'driver' => 'mysql',
                        'database' => 'grocery_central',
                        'host' => '127.0.0.1',
                        'port' => '3306',
                        'charset' => 'utf8mb4',
                        'collation' => 'utf8mb4_unicode_ci',
                    ],
                    'total_tables' => 0,
                    'error' => $e->getMessage()
                ]
            ]);
        }
    }
    
    public function tenants()
    {
        $tenants = Tenant::with(['subscription.plan', 'users'])
            ->orderBy('created_at', 'desc')
            ->paginate(20);
            
        $plans = Plan::where('is_active', true)->get();
            
        return view('developer.tenants.index', compact('tenants', 'plans'));
    }

    public function assignSubscription(Request $request)
    {
        $request->validate([
            'tenant_id' => 'required|exists:tenants,id',
            'plan_id' => 'required|exists:plans,id',
        ]);

        $tenant = Tenant::findOrFail($request->tenant_id);
        $plan = Plan::findOrFail($request->plan_id);

        // Delete existing subscription if any
        Subscription::where('tenant_id', $tenant->id)->delete();

        // Create new subscription
        $subscription = Subscription::create([
            'tenant_id' => $tenant->id,
            'plan_id' => $plan->id,
            'status' => 'active',
            'starts_at' => now(),
            'ends_at' => now()->addDays($plan->interval === 'monthly' ? 30 : 365),
            'payment_gateway' => 'manual',
            'payment_status' => 'paid',
            'verified_at' => now(),
            'amount' => $plan->price,
            'currency' => 'BDT',
        ]);

        // Update tenant status
        $tenant->update(['status' => 'active']);

        // Update Tenant Category from Plan and sync default categories
        if ($plan->tenant_category_id) {
            $tenant->update(['tenant_category_id' => $plan->tenant_category_id]);
            
            // Sync default categories to tenant database
            if ($plan->category && $plan->category->default_categories) {
                $this->syncService->syncCategoriesToTenant($tenant, $plan->category->default_categories);
            }
        }

        return back()->with('success', "Subscription '{$plan->name}' assigned to '{$tenant->name}' successfully.");
    }
    
    public function tenantActivity($id)
    {
        $tenant = Tenant::with(['subscription.plan', 'users'])
            ->findOrFail($id);
            
        // Get tenant activities
        $activities = $this->getTenantActivities($id);
        
        return view('developer.tenants.activity', compact('tenant', 'activities'));
    }
    
    public function tenantApplications($id)
    {
        $tenant = Tenant::findOrFail($id);
        
        // Get tenant's application logs
        $logs = $this->getTenantApplicationLogs($id);
        
        // Get tenant's database information
        $databaseInfo = $this->getDatabaseInfo($tenant->database_name);
        
        return view('developer.tenants.applications', compact('tenant', 'logs', 'databaseInfo'));
    }
    
    public function database()
    {
        $databaseInfo = $this->getDatabaseInfo();
        $tenants = Tenant::select('id', 'name', 'database_name')->orderBy('name')->get();
        
        return view('developer.database.index', compact('databaseInfo', 'tenants'));
    }

    public function getTableData(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $page = $request->input('page', 1);
            $limit = $request->input('limit', 10);
            
            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
            }

            $connection = $this->getDatabaseConnection($tenantId);
            
            $data = DB::connection($connection)->table($tableName)
                ->paginate($limit, ['*'], 'page', $page);
            
            return response()->json([
                'success' => true,
                'data' => $data->items(),
                'total' => $data->total(),
                'current_page' => $data->currentPage(),
                'last_page' => $data->lastPage()
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function addTableRow(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $rowData = $request->input('data');
            
            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
            }

            $connection = $this->getDatabaseConnection($tenantId);
            
            DB::connection($connection)->table($tableName)->insert($rowData);
            
            return response()->json(['success' => true, 'message' => 'Row added successfully.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function updateTableRow(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $where = $request->input('where'); // {id: 5} or similar
            $data = $request->input('data');
            
            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
            }

            $connection = $this->getDatabaseConnection($tenantId);
            
            $query = DB::connection($connection)->table($tableName);
            foreach ($where as $key => $value) {
                $query->where($key, $value);
            }
            
            $query->update($data);
            
            return response()->json(['success' => true, 'message' => 'Row updated successfully.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function deleteTableRow(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $where = $request->input('where');
            
            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
            }

            $connection = $this->getDatabaseConnection($tenantId);
            
            $query = DB::connection($connection)->table($tableName);
            foreach ($where as $key => $value) {
                $query->where($key, $value);
            }
            
            $query->delete();
            
            return response()->json(['success' => true, 'message' => 'Row deleted successfully.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    private function getDatabaseConnection($tenantId)
    {
        if ($tenantId && $tenantId !== 'all') {
            $tenant = Tenant::findOrFail($tenantId);
            Config::set('database.connections.tenant.database', $tenant->database_name);
            DB::purge('tenant');
            return 'tenant';
        }
        return 'mysql';
    }

    public function getTables(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $connection = 'mysql';
            $database = config('database.connections.mysql.database');

            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                Config::set('database.connections.tenant.database', $tenant->database_name);
                DB::purge('tenant');
                $connection = 'tenant';
                $database = $tenant->database_name;
            }

            $tables = DB::connection($connection)->select("SHOW TABLE STATUS FROM `{$database}`");
            
            return response()->json([
                'success' => true,
                'tables' => collect($tables)->map(function($table) {
                    $table = (object)$table;
                    return [
                        'name' => $table->Name,
                        'rows' => $table->Rows,
                        'size' => round(($table->Data_length + $table->Index_length) / 1024 / 1024, 2),
                        'engine' => $table->Engine,
                        'collation' => $table->Collation,
                        'comment' => $table->Comment
                    ];
                })
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function getTableColumns(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $connection = 'mysql';

            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                Config::set('database.connections.tenant.database', $tenant->database_name);
                DB::purge('tenant');
                $connection = 'tenant';
            }

            $columns = DB::connection($connection)->select("SHOW FULL COLUMNS FROM `{$tableName}`");
            
            return response()->json([
                'success' => true,
                'columns' => collect($columns)->map(function($column) {
                    $column = (object)$column;
                    return [
                        'field' => $column->Field,
                        'type' => $column->Type,
                        'null' => $column->Null,
                        'key' => $column->Key,
                        'default' => $column->Default,
                        'extra' => $column->Extra,
                        'comment' => $column->Comment
                    ];
                })
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function updateTableColumn(Request $request)
    {
        $request->validate([
            'table' => 'required',
            'old_name' => 'required',
            'new_name' => 'required',
            'type' => 'required',
        ]);

        try {
            $tenantId = $request->input('tenant_id');
            $tableName = $request->input('table');
            $oldName = $request->input('old_name');
            $newName = $request->input('new_name');
            $type = $request->input('type');
            $isNullable = $request->input('is_nullable') ? 'NULL' : 'NOT NULL';
            
            $connection = 'mysql';

            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                Config::set('database.connections.tenant.database', $tenant->database_name);
                DB::purge('tenant');
                $connection = 'tenant';
            }

            // MySQL specific ALTER TABLE syntax
            $sql = "ALTER TABLE `{$tableName}` CHANGE `{$oldName}` `{$newName}` {$type} {$isNullable}";
            DB::connection($connection)->statement($sql);

            return response()->json(['success' => true, 'message' => "Column '{$oldName}' updated successfully."]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function logs(Request $request)
    {
        $limit = $request->query('view') === 'all' ? null : 100;
        $logs = $this->getApplicationLogs($limit);
        $viewAll = $request->query('view') === 'all';
        
        return view('developer.logs.index', compact('logs', 'viewAll'));
    }

    public function downloadLogs(Request $request)
    {
        $logFile = storage_path('logs/laravel.log');
        
        if (File::exists($logFile)) {
            return response()->download($logFile);
        }
        
        return back()->with('error', 'Log file not found.');
    }

    public function clearLogs(Request $request)
    {
        $logFile = storage_path('logs/laravel.log');
        
        try {
            if (File::exists($logFile)) {
                File::put($logFile, '');
                return back()->with('success', 'Application logs cleared successfully.');
            }
            return back()->with('error', 'Log file not found.');
        } catch (\Exception $e) {
            return back()->with('error', 'Failed to clear logs: ' . $e->getMessage());
        }
    }

    public function monitoring()
    {
        $tenants = Tenant::orderBy('name')->get();
        return view('developer.monitoring.index', compact('tenants'));
    }

    public function getMonitoringData(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $userId = $request->input('user_id');
            $data = [];

            if ($tenantId && $tenantId !== 'system') {
                $tenant = Tenant::findOrFail($tenantId);
                
                // ... (rest of basic info)
                $data['tenant'] = [
                    'id' => $tenant->id,
                    'name' => $tenant->name,
                    'domain' => $tenant->domain,
                    'status' => $tenant->status,
                    'database' => $tenant->database_name,
                ];

                // ... (database stats)
                if ($this->databaseExists($tenant->database_name)) {
                    $databaseInfo = $this->getDatabaseInfo($tenant->database_name);
                    $data['database'] = [
                        'size' => $databaseInfo['size'],
                        'total_tables' => $databaseInfo['total_tables'],
                    ];
                } else {
                    $data['database'] = ['error' => 'Database not found'];
                }

                // 3. User Stats
                $userQuery = User::where('tenant_id', $tenant->id);
                $data['users'] = [
                    'total' => (clone $userQuery)->count(),
                    'active_last_24h' => (clone $userQuery)
                        ->where('updated_at', '>=', now()->subDay())
                        ->count(),
                    'list' => (clone $userQuery)->select('id', 'name', 'email')->get(),
                ];

                if ($userId && $userId !== 'all') {
                    $selectedUser = User::find($userId);
                    if ($selectedUser) {
                        $data['selected_user_info'] = [
                            'id' => $selectedUser->id,
                            'name' => $selectedUser->name,
                            'email' => $selectedUser->email,
                            'last_active' => $selectedUser->updated_at->diffForHumans(),
                        ];
                    }
                }

                // 4. Logs (Recent 10)
                $logQuery = $this->getTenantApplicationLogs($tenant->id);
                $data['logs'] = $logQuery->take(10);

                // 5. Active Queries
                $activeQueries = DB::connection('mysql')->table('information_schema.processlist')
                    ->select(['ID', 'USER', 'HOST', 'DB', 'COMMAND', 'TIME', 'STATE', 'INFO'])
                    ->where(function($q) use ($tenant) {
                        $q->where('DB', $tenant->database_name)
                          ->orWhere('INFO', 'LIKE', "%{$tenant->database_name}%");
                    })
                    ->where('COMMAND', '!=', 'Sleep')
                    ->orderBy('TIME', 'DESC')
                    ->limit(10)
                    ->get();
                $data['active_queries'] = $activeQueries;

            } else {
                // System wide monitoring
                $data['system'] = [
                    'php_version' => PHP_VERSION,
                    'laravel_version' => app()->version(),
                    'server_time' => now()->toDateTimeString(),
                    'memory_usage' => round(memory_get_usage(true) / 1024 / 1024, 2) . ' MB',
                ];

                $data['tenant_stats'] = $this->getTenantStatistics();
                $data['logs'] = $this->getApplicationLogs()->take(10);
                
                // 5. Active Queries (System Wide)
                $activeQueries = DB::connection('mysql')->table('information_schema.processlist')
                    ->select(['ID', 'USER', 'HOST', 'DB', 'COMMAND', 'TIME', 'STATE', 'INFO'])
                    ->where('COMMAND', '!=', 'Sleep')
                    ->limit(10)
                    ->get();
                $data['active_queries'] = $activeQueries;
            }

            return response()->json([
                'success' => true,
                'data' => $data
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    private function databaseExists($databaseName)
    {
        try {
            $result = DB::connection('mysql')->select("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?", [$databaseName]);
            return !empty($result);
        } catch (\Exception $e) {
            return false;
        }
    }

    public function clearCache(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            
            if ($tenantId === 'all') {
                $tenants = Tenant::all();
                $count = 0;
                foreach ($tenants as $tenant) {
                    if ($this->databaseExists($tenant->database_name)) {
                        $this->switchTenantDatabase($tenant->id);
                        Artisan::call('cache:clear');
                        $count++;
                    }
                }
                $message = "Cache cleared for {$count} accessible tenant databases.";
            } elseif ($tenantId) {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                $this->switchTenantDatabase($tenantId);
                Artisan::call('cache:clear');
                $message = 'Cache cleared for selected tenant successfully';
            } else {
                Artisan::call('cache:clear');
                Artisan::call('config:clear');
                Artisan::call('route:clear');
                Artisan::call('view:clear');
                $message = 'System cache cleared successfully';
            }
            
            return response()->json(['success' => true, 'message' => $message]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
    
    public function optimizeDatabase(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            
            if ($tenantId === 'all') {
                $tenants = Tenant::all();
                $count = 0;
                foreach ($tenants as $tenant) {
                    if ($this->databaseExists($tenant->database_name)) {
                        $this->optimizeTargetDatabase($tenant->database_name);
                        $count++;
                    }
                }
                $message = "{$count} accessible tenant databases optimized successfully";
            } elseif ($tenantId) {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                $this->optimizeTargetDatabase($tenant->database_name);
                $message = "Database for '{$tenant->name}' optimized successfully";
            } else {
                $this->optimizeTargetDatabase(DB::getDatabaseName());
                $message = 'Main database optimized successfully';
            }
            
            return response()->json(['success' => true, 'message' => $message]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
    
    private function optimizeTargetDatabase($databaseName)
    {
        Config::set('database.connections.tenant.database', $databaseName);
        DB::purge('tenant');
        
        $tables = DB::connection('tenant')->select("SHOW TABLES");
        foreach ($tables as $table) {
            $tableName = array_values((array)$table)[0];
            DB::connection('tenant')->statement("OPTIMIZE TABLE `{$tableName}`");
        }
    }

    private function switchTenantDatabase($tenantId)
    {
        $tenant = Tenant::findOrFail($tenantId);
        Config::set('database.connections.tenant.database', $tenant->database_name);
        DB::purge('tenant');
        DB::reconnect('tenant');
        DB::setDefaultConnection('tenant');
    }
    
    public function analyzeQueries(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $targetDatabase = null;

            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                $targetDatabase = $tenant->database_name;
            } else {
                $targetDatabase = config('database.connections.mysql.database');
            }

            // Always use main connection to query information_schema to avoid "Unknown database" connection errors
            $query = DB::connection('mysql')->table('information_schema.processlist')
                ->select(['ID', 'USER', 'HOST', 'DB', 'COMMAND', 'TIME', 'STATE', 'INFO'])
                ->where('COMMAND', '!=', 'Sleep');
            
            if ($targetDatabase) {
                $query->where('DB', $targetDatabase);
            }

            $queries = $query->orderBy('TIME', 'DESC')
                ->limit(10)
                ->get();
            
            $indexStatsQuery = DB::connection('mysql')->table('information_schema.statistics')
                ->select([
                    'TABLE_NAME', 
                    'INDEX_NAME', 
                    'COLUMN_NAME', 
                    'NON_UNIQUE', 
                    'CARDINALITY'
                ]);

            if ($targetDatabase) {
                $indexStatsQuery->where('TABLE_SCHEMA', $targetDatabase);
            }

            $indexStats = $indexStatsQuery->orderBy('CARDINALITY', 'DESC')
                ->limit(10)
                ->get();

            return response()->json([
                'success' => true, 
                'active_queries' => $queries,
                'index_stats' => $indexStats,
                'message' => 'Query analysis completed successfully.'
            ]);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
    
    public function exportSchema(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            $connection = 'mysql';
            $database = config('database.connections.mysql.database');

            if ($tenantId) {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                Config::set('database.connections.tenant.database', $tenant->database_name);
                DB::purge('tenant');
                $connection = 'tenant';
                $database = $tenant->database_name;
            }

            $tables = DB::connection($connection)->select("SHOW TABLES FROM `{$database}`");
            $output = "-- Database Schema Export for {$database}\n";
            $output .= "-- Generated on " . now()->toDateTimeString() . "\n\n";
            
            foreach ($tables as $table) {
                $tableName = array_values((array)$table)[0];
                $createTable = DB::connection($connection)->select("SHOW CREATE TABLE `{$tableName}`");
                $createSql = ((array)$createTable[0])['Create Table'];
                $output .= "{$createSql};\n\n";
            }
            
            return response($output)
                ->header('Content-Type', 'text/plain')
                ->header('Content-Disposition', 'attachment; filename="' . $database . '_schema_' . now()->format('Y-m-d') . '.sql"');
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    public function uploadDatabase(Request $request)
    {
        $request->validate([
            'database_file' => 'required|file',
            'tenant_id' => 'nullable'
        ]);

        try {
            $tenantId = $request->input('tenant_id');
            $database = config('database.connections.mysql.database');
            $tenant = null;

            if ($tenantId && $tenantId !== 'all') {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist. Please create it first."], 404);
                }
                $database = $tenant->database_name;
                $tenant->update(['status' => 'maintenance']);
            }

            $file = $request->file('database_file');
            $path = $file->getRealPath();

            $username = config('database.connections.mysql.username');
            $password = config('database.connections.mysql.password');
            $host = config('database.connections.mysql.host');

            // Construct mysql import command
            $command = sprintf(
                'mysql --user=%s --password=%s --host=%s %s < %s',
                escapeshellarg($username),
                escapeshellarg($password),
                escapeshellarg($host),
                escapeshellarg($database),
                escapeshellarg($path)
            );

            exec($command, $output, $returnVar);

            if ($tenant) {
                $tenant->update(['status' => 'active']);
            }

            if ($returnVar !== 0) {
                // Fallback to manual execution if mysql command fails
                return $this->manualUpload($path, $database, $tenantId ? 'tenant' : 'mysql');
            }

            return response()->json(['success' => true, 'message' => 'Database uploaded and restored successfully.']);
        } catch (\Exception $e) {
            if (isset($tenant) && $tenant) {
                $tenant->update(['status' => 'active']);
            }
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    private function manualUpload($path, $database, $connection = 'mysql')
    {
        try {
            if ($connection === 'tenant') {
                Config::set('database.connections.tenant.database', $database);
                DB::purge('tenant');
            }

            $sql = file_get_contents($path);
            DB::connection($connection)->unprepared($sql);

            return response()->json(['success' => true, 'message' => 'Database uploaded and restored manually.']);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => 'Manual restoration failed: ' . $e->getMessage()], 500);
        }
    }

    public function backupDatabase(Request $request)
    {
        try {
            $tenantId = $request->input('tenant_id');
            
            if ($tenantId === 'all') {
                return $this->backupAllTenants();
            }

            $database = config('database.connections.mysql.database');
            $tenant = null;

            if ($tenantId) {
                $tenant = Tenant::findOrFail($tenantId);
                if (!$this->databaseExists($tenant->database_name)) {
                    return response()->json(['success' => false, 'message' => "Database '{$tenant->database_name}' does not exist."], 404);
                }
                $database = $tenant->database_name;
                
                // Put tenant in maintenance mode
                $tenant->update(['status' => 'maintenance']);
            }

            $filename = "backup-{$database}-" . now()->format('Y-m-d-H-i-s') . ".sql";
            $storagePath = storage_path('app/backups');
            
            if (!file_exists($storagePath)) {
                mkdir($storagePath, 0755, true);
            }
            
            $path = $storagePath . '/' . $filename;
            $success = $this->runMysqlDump($database, $path);
            
            $response = null;
            if (!$success) {
                $response = $this->manualBackup($path, $database, $tenantId ? 'tenant' : 'mysql');
            } else {
                $response = response()->download($path)->deleteFileAfterSend(true);
            }

            // Restore tenant status after backup
            if ($tenant) {
                $tenant->update(['status' => 'active']);
            }
            
            return $response;
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    private function backupAllTenants()
    {
        $tenants = Tenant::all();
        
        // Put all tenants in maintenance mode
        foreach ($tenants as $tenant) {
            if (!$this->databaseExists($tenant->database_name)) {
                continue;
            }
            $tenant->update(['status' => 'maintenance']);
        }

        $timestamp = time();
        $tempPath = storage_path('app/backups/all_tenants_' . $timestamp);
        
        if (!file_exists($tempPath)) {
            mkdir($tempPath, 0755, true);
        }
        
        $files = [];
        try {
            foreach ($tenants as $tenant) {
                if (!$this->databaseExists($tenant->database_name)) {
                    continue;
                }
                $filename = "tenant-{$tenant->id}-{$tenant->database_name}.sql";
                $path = $tempPath . '/' . $filename;
                if ($this->runMysqlDump($tenant->database_name, $path)) {
                    $files[] = $path;
                }
            }
            
            if (empty($files)) {
                return response()->json(['success' => false, 'message' => 'No tenant databases could be backed up.'], 500);
            }

            $zipPath = storage_path('app/backups/all_tenants_backup_' . now()->format('Y-m-d') . '.zip');
            $zip = new \ZipArchive();
            
            if ($zip->open($zipPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === TRUE) {
                foreach ($files as $file) {
                    $zip->addFile($file, basename($file));
                }
                $zip->close();
                
                // Cleanup individual SQL files
                foreach ($files as $file) { @unlink($file); }
                @rmdir($tempPath);
                
                return response()->download($zipPath)->deleteFileAfterSend(true);
            }
        } finally {
            // Always restore tenant status
            foreach ($tenants as $tenant) {
                $tenant->update(['status' => 'active']);
            }
        }
        
        return response()->json(['success' => false, 'message' => 'Failed to create zip archive.'], 500);
    }

    private function runMysqlDump($database, $path)
    {
        $username = config('database.connections.mysql.username');
        $password = config('database.connections.mysql.password');
        $host = config('database.connections.mysql.host');
        
        $command = sprintf(
            'mysqldump --user=%s --password=%s --host=%s %s > %s',
            escapeshellarg($username),
            escapeshellarg($password),
            escapeshellarg($host),
            escapeshellarg($database),
            escapeshellarg($path)
        );
        
        exec($command, $output, $returnVar);
        return $returnVar === 0;
    }

    private function manualBackup($path, $database, $connection = 'mysql')
    {
        if ($connection === 'tenant') {
            Config::set('database.connections.tenant.database', $database);
            DB::purge('tenant');
        }

        $tables = DB::connection($connection)->select('SHOW TABLES');
        $sql = "-- Manual Backup for {$database}\n\n";
        
        foreach ($tables as $table) {
            $tableName = array_values((array)$table)[0];
            $createTable = DB::connection($connection)->select("SHOW CREATE TABLE `{$tableName}`");
            $sql .= ((array)$createTable[0])['Create Table'] . ";\n\n";
            
            $rows = DB::connection($connection)->table($tableName)->get();
            foreach ($rows as $row) {
                $rowArray = (array)$row;
                $keys = array_keys($rowArray);
                $values = array_map(function($value) use ($connection) {
                    if (is_null($value)) return 'NULL';
                    return DB::connection($connection)->getPdo()->quote($value);
                }, array_values($rowArray));
                
                $sql .= "INSERT INTO `{$tableName}` (`" . implode('`, `', $keys) . "`) VALUES (" . implode(', ', $values) . ");\n";
            }
            $sql .= "\n";
        }
        
        file_put_contents($path, $sql);
        return response()->download($path)->deleteFileAfterSend(true);
    }
    
    private function getTenantStatistics()
    {
        return [
            'total' => Tenant::count(),
            'active' => Tenant::where('status', 'active')->count(),
            'trialing' => Tenant::where('status', 'trialing')->count(),
            'expired' => Tenant::where('status', 'expired')->count(),
            'new_this_month' => Tenant::where('created_at', '>=', Carbon::now()->startOfMonth())->count(),
            'with_subscriptions' => Tenant::whereHas('subscription')->count(),
            'total_users' => User::count(),
            'active_sessions' => ChatSession::where('status', 'active')->count(),
            'list' => Tenant::select('id', 'name')->get(),
        ];
    }
    
    private function getRecentTenantActivities()
    {
        return collect([
            [
                'type' => 'tenant_created',
                'message' => 'New tenant registered',
                'tenant_name' => 'Demo Grocery Store',
                'timestamp' => Carbon::now()->subMinutes(15),
                'icon' => 'user-plus',
                'color' => 'green'
            ],
            [
                'type' => 'subscription_created',
                'message' => 'Subscription activated',
                'tenant_name' => 'Tech Company',
                'timestamp' => Carbon::now()->subHours(2),
                'icon' => 'credit-card',
                'color' => 'blue'
            ],
            [
                'type' => 'chat_started',
                'message' => 'Chat session initiated',
                'tenant_name' => 'Service Provider',
                'timestamp' => Carbon::now()->subHours(4),
                'icon' => 'message-circle',
                'color' => 'purple'
            ],
            [
                'type' => 'user_registered',
                'message' => 'New user added',
                'tenant_name' => 'Retail Store',
                'timestamp' => Carbon::now()->subHours(6),
                'icon' => 'user',
                'color' => 'indigo'
            ],
        ])->sortByDesc('timestamp')->take(10);
    }
    
    private function getTenantActivities($tenantId)
    {
        $tenant = Tenant::find($tenantId);
        
        $activities = collect();
        
        // Get subscription activities
        $subscriptions = Subscription::where('tenant_id', $tenantId)
            ->orderBy('created_at', 'desc')
            ->take(10)
            ->get();
            
        foreach ($subscriptions as $subscription) {
            $activities->push([
                'type' => 'subscription',
                'message' => "Subscription {$subscription->status}",
                'details' => "Plan: " . ($subscription->plan->name ?? 'Unknown'),
                'timestamp' => $subscription->created_at,
                'icon' => 'credit-card',
                'color' => $subscription->status === 'active' ? 'green' : 'gray'
            ]);
        }
        
        // Get chat activities
        $chatSessions = ChatSession::where('tenant_id', $tenantId)
            ->orderBy('created_at', 'desc')
            ->take(10)
            ->get();
            
        foreach ($chatSessions as $session) {
            $activities->push([
                'type' => 'chat',
                'message' => "Chat session {$session->status}",
                'details' => "Messages: " . $session->messages()->count(),
                'timestamp' => $session->created_at,
                'icon' => 'message-circle',
                'color' => $session->status === 'active' ? 'blue' : 'gray'
            ]);
        }
        
        // Get user activities
        $users = User::where('tenant_id', $tenantId)
            ->orderBy('created_at', 'desc')
            ->take(10)
            ->get();
            
        foreach ($users as $user) {
            $activities->push([
                'type' => 'user',
                'message' => 'User account created',
                'details' => "Email: " . $user->email,
                'timestamp' => $user->created_at,
                'icon' => 'user',
                'color' => 'indigo'
            ]);
        }
        
        return $activities->sortByDesc('timestamp');
    }
    
    private function getApplicationLogs($limit = 100)
    {
        // Get Laravel logs
        $logFile = storage_path('logs/laravel.log');
        $logs = [];
        
        if (File::exists($logFile)) {
            // Read the last 2MB of the log file for "all" or 512KB for default to be efficient
            $fileSize = File::size($logFile);
            $maxRead = $limit ? 512 * 1024 : 5 * 1024 * 1024; // 512KB or 5MB
            $start = max(0, $fileSize - $maxRead);
            
            $handle = fopen($logFile, 'r');
            if ($start > 0) {
                fseek($handle, $start);
                // Skip the first potentially incomplete line
                fgets($handle);
            }
            
            $content = '';
            while (!feof($handle)) {
                $line = fgets($handle);
                if ($line !== false) {
                    $content .= $line;
                }
            }
            fclose($handle);

            $lines = explode("\n", $content);
            $currentLog = null;
            $parsedLogs = [];

            foreach ($lines as $line) {
                // Laravel log format: [YYYY-MM-DD HH:MM:SS] env.LEVEL: message
                if (preg_match('/^\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\]\s+(\w+)\.(\w+):\s+(.*)/', $line, $matches)) {
                    if ($currentLog) {
                        $parsedLogs[] = $currentLog;
                    }
                    $currentLog = [
                        'timestamp' => Carbon::parse($matches[1]),
                        'env' => $matches[2],
                        'level' => strtolower($matches[3]),
                        'message' => $matches[4],
                        'context' => '',
                        'raw' => $line
                    ];
                } elseif ($currentLog && trim($line)) {
                    $currentLog['message'] .= "\n" . $line;
                    $currentLog['raw'] .= "\n" . $line;
                }
            }

            if ($currentLog) {
                $parsedLogs[] = $currentLog;
            }

            $logs = array_reverse($parsedLogs);
        }
        
        if ($limit) {
            return collect($logs)->take($limit);
        }
        
        return collect($logs);
    }
    
    private function getTenantApplicationLogs($tenantId)
    {
        $tenant = Tenant::find($tenantId);
        
        // Get tenant-specific logs (you might want to implement a custom logging system)
        $logs = collect([
            [
                'timestamp' => Carbon::now()->subMinutes(30),
                'level' => 'info',
                'message' => "User {$tenant->name} logged in",
                'context' => 'authentication'
            ],
            [
                'timestamp' => Carbon::now()->subHours(1),
                'level' => 'info',
                'message' => "Subscription created for {$tenant->name}",
                'context' => 'billing'
            ],
            [
                'timestamp' => Carbon::now()->subHours(2),
                'level' => 'info',
                'message' => "Chat session started for {$tenant->name}",
                'context' => 'chat'
            ],
            [
                'timestamp' => Carbon::now()->subHours(3),
                'level' => 'info',
                'message' => "New user added to {$tenant->name}",
                'context' => 'user_management'
            ],
        ])->sortByDesc('timestamp');
        
        return $logs;
    }
    
    private function getDatabaseInfo($databaseName = null)
    {
        $connection = 'mysql';
        if ($databaseName) {
            if (!$this->databaseExists($databaseName)) {
                return [
                    'size' => 0,
                    'total_tables' => 0,
                    'tables' => [],
                    'error' => "Database '{$databaseName}' does not exist."
                ];
            }
            Config::set('database.connections.tenant.database', $databaseName);
            DB::purge('tenant');
            $connection = 'tenant';
        } else {
            $databaseName = config('database.connections.mysql.database');
        }

        try {
            $tables = DB::connection($connection)->select('SHOW TABLE STATUS');
            $totalSize = 0;
            $tableDetails = [];

            foreach ($tables as $table) {
                $table = (object)$table;
                $size = ($table->Data_length + $table->Index_length) / 1024 / 1024; // MB
                $totalSize += $size;
                $tableDetails[] = [
                    'name' => $table->Name,
                    'rows' => $table->Rows,
                    'size' => round($size, 2),
                    'engine' => $table->Engine
                ];
            }

            return [
                'size' => round($totalSize, 2),
                'total_tables' => count($tables),
                'tables' => array_slice($tableDetails, 0, 10), // Top 10 tables
                'connection' => config("database.connections.{$connection}")
            ];
        } catch (\Exception $e) {
            return [
                'size' => 0,
                'total_tables' => 0,
                'tables' => [],
                'error' => $e->getMessage()
            ];
        }
    }
}
