<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Log;
use App\Models\AuditLog;
use Carbon\Carbon;

class PerformanceOptimizationService
{
    /**
     * Optimize database queries and cache frequently accessed data
     */
    public static function optimizeDatabase()
    {
        try {
            $results = [];
            
            // 1. Analyze slow queries
            $results['slow_queries'] = self::analyzeSlowQueries();
            
            // 2. Optimize table statistics
            $results['table_optimization'] = self::optimizeTableStatistics();
            
            // 3. Clean up old audit logs
            $results['audit_cleanup'] = self::cleanupAuditLogs();
            
            // 4. Cache frequently accessed data
            $results['cache_warming'] = self::warmupCache();
            
            return [
                'success' => true,
                'results' => $results,
                'timestamp' => now()
            ];
            
        } catch (\Exception $e) {
            Log::error('Performance optimization failed', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            
            return [
                'success' => false,
                'error' => $e->getMessage(),
                'timestamp' => now()
            ];
        }
    }

    /**
     * Analyze and identify slow queries
     */
    private static function analyzeSlowQueries()
    {
        try {
            // Get table sizes to identify large tables
            $tableSizes = DB::select("
                SELECT 
                    table_name,
                    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
                    table_rows
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                ORDER BY (data_length + index_length) DESC
                LIMIT 10
            ");
            
            return [
                'large_tables' => $tableSizes,
                'recommendations' => self::getOptimizationRecommendations($tableSizes)
            ];
            
        } catch (\Exception $e) {
            return ['error' => $e->getMessage()];
        }
    }

    /**
     * Optimize table statistics for better query planning
     */
    private static function optimizeTableStatistics()
    {
        try {
            $tables = ['penduduk', 'berita', 'pengaduan', 'pelayanan_surat', 'audit_log'];
            $optimized = [];
            
            foreach ($tables as $table) {
                if (DB::getSchemaBuilder()->hasTable($table)) {
                    DB::statement("ANALYZE TABLE {$table}");
                    $optimized[] = $table;
                }
            }
            
            return [
                'optimized_tables' => $optimized,
                'count' => count($optimized)
            ];
            
        } catch (\Exception $e) {
            return ['error' => $e->getMessage()];
        }
    }

    /**
     * Clean up old audit logs to maintain performance
     */
    private static function cleanupAuditLogs($daysToKeep = 90)
    {
        try {
            $cutoffDate = Carbon::now()->subDays($daysToKeep);
            $deletedCount = AuditLog::where('created_at', '<', $cutoffDate)->delete();
            
            return [
                'deleted_records' => $deletedCount,
                'cutoff_date' => $cutoffDate->format('Y-m-d H:i:s')
            ];
            
        } catch (\Exception $e) {
            return ['error' => $e->getMessage()];
        }
    }

    /**
     * Warm up cache with frequently accessed data
     */
    private static function warmupCache()
    {
        try {
            $cached = [];
            
            // Cache dashboard statistics
            $dashboardStats = Cache::remember('dashboard_stats', 3600, function () {
                return [
                    'total_penduduk' => DB::table('penduduk')->count(),
                    'total_keluarga' => DB::table('keluarga')->count(),
                    'total_pengaduan' => DB::table('pengaduan')->count(),
                    'total_surat' => DB::table('pelayanan_surat')->count(),
                ];
            });
            $cached['dashboard_stats'] = 'cached';
            
            // Cache popular berita
            $popularBerita = Cache::remember('popular_berita', 1800, function () {
                return DB::table('berita')
                    ->where('status', 'published')
                    ->orderBy('views', 'desc')
                    ->limit(10)
                    ->get(['id', 'judul', 'slug', 'views']);
            });
            $cached['popular_berita'] = 'cached';
            
            // Cache system settings
            $settings = Cache::remember('system_settings', 7200, function () {
                return DB::table('settings')->pluck('value', 'key');
            });
            $cached['system_settings'] = 'cached';
            
            return [
                'cached_items' => $cached,
                'cache_duration' => '1-2 hours'
            ];
            
        } catch (\Exception $e) {
            return ['error' => $e->getMessage()];
        }
    }

    /**
     * Get optimization recommendations
     */
    private static function getOptimizationRecommendations($tableSizes)
    {
        $recommendations = [];
        
        foreach ($tableSizes as $table) {
            if ($table->size_mb > 50) {
                $recommendations[] = [
                    'table' => $table->table_name,
                    'issue' => 'Large table size',
                    'recommendation' => 'Consider archiving old data',
                    'size_mb' => $table->size_mb
                ];
            }
        }
        
        return $recommendations;
    }

    /**
     * Get database performance metrics
     */
    public static function getDatabaseMetrics()
    {
        try {
            // Get database size
            $dbSize = DB::select("
                SELECT 
                    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS db_size_mb
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
            ")[0]->db_size_mb;
            
            return [
                'database_size_mb' => $dbSize,
                'timestamp' => now()
            ];
            
        } catch (\Exception $e) {
            return ['error' => $e->getMessage()];
        }
    }
}