<?php
/**
 * DATABASE PERFORMANCE OPTIMIZATION IMPLEMENTATION
 * 
 * Key improvements for handling large datasets:
 * 1. Proper pagination everywhere
 * 2. Efficient queries with LIMIT
 * 3. Proper indexing usage
 * 4. Caching for expensive operations
 */

// Add these optimized methods to Database class

class DatabaseOptimizations {
    
    /**
     * OPTIMIZED: Get live draw results with proper pagination and indexing
     */
    public function getLiveDrawResultsOptimized($limit = 20, $offset = 0, $marketCode = null) {
        try {
            $sql = "SELECT * FROM live_draw_results";
            $params = [];
            
            if ($marketCode) {
                $sql .= " WHERE market = ?";
                $params[] = $marketCode;
            }
            
            // Use indexed columns for ORDER BY
            $sql .= " ORDER BY draw_date DESC, id DESC LIMIT ? OFFSET ?";
            $params[] = $limit;
            $params[] = $offset;
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error in optimized live draw results: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * OPTIMIZED: History with better pagination and search
     */
    public function getHistoryResultsOptimized($marketCode, $limit = 35, $offset = 0, $searchNumber = '') {
        try {
            $sql = "SELECT * FROM live_draw_results WHERE market = ?";
            $params = [$marketCode];
            
            if (!empty($searchNumber)) {
                // Use indexed column for better performance
                $sql .= " AND result_4d LIKE ?";
                $params[] = $searchNumber . '%'; // Prefix search is faster than %search%
            }
            
            $sql .= " ORDER BY draw_date DESC, id DESC LIMIT ? OFFSET ?";
            $params[] = $limit;
            $params[] = $offset;
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error in optimized history: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * OPTIMIZED: Buku Mimpi with better search performance
     */
    public function getBukuMimpiOptimized($type, $limit = 20, $offset = 0, $searchTerm = '') {
        try {
            $table = "buku_mimpi_" . $type;
            $sql = "SELECT * FROM {$table} WHERE is_active = 1";
            $params = [];
            
            if (!empty($searchTerm)) {
                // Prefix search on indexed columns
                $sql .= " AND (angka LIKE ? OR judul_mimpi LIKE ?)";
                $params[] = $searchTerm . '%';
                $params[] = $searchTerm . '%';
            }
            
            $sql .= " ORDER BY angka ASC LIMIT ? OFFSET ?";
            $params[] = $limit;
            $params[] = $offset;
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error in optimized buku mimpi: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * OPTIMIZED: Dashboard stats with efficient queries
     */
    public function getDashboardStatsOptimized() {
        try {
            $stats = [];
            
            // Use efficient counting with indexes
            $queries = [
                'total_markets' => "SELECT COUNT(*) FROM market_schedules WHERE is_active = 1",
                'total_results' => "SELECT COUNT(*) FROM live_draw_results",
                'today_results' => "SELECT COUNT(*) FROM live_draw_results WHERE DATE(draw_date) = CURDATE()",
                'total_tickets' => "SELECT COUNT(*) FROM support_tickets",
                'active_jackpots' => "SELECT COUNT(*) FROM bukti_jackpot WHERE is_active = 1"
            ];
            
            foreach ($queries as $key => $query) {
                $stmt = $this->pdo->prepare($query);
                $stmt->execute();
                $stats[$key] = $stmt->fetchColumn();
            }
            
            return $stats;
        } catch (PDOException $e) {
            error_log("Error in dashboard stats: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * CACHING: Simple file-based cache for expensive queries
     */
    public function getCachedData($cacheKey, $callback, $ttl = 300) {
        $cacheFile = '../cache/' . md5($cacheKey) . '.cache';
        
        // Create cache directory if not exists
        if (!is_dir('../cache')) {
            mkdir('../cache', 0755, true);
        }
        
        // Check if cache exists and is valid
        if (file_exists($cacheFile) && (time() - filemtime($cacheFile)) < $ttl) {
            return unserialize(file_get_contents($cacheFile));
        }
        
        // Generate new data
        $data = $callback();
        
        // Save to cache
        file_put_contents($cacheFile, serialize($data));
        
        return $data;
    }
    
    /**
     * BATCH OPERATIONS: Insert multiple records efficiently
     */
    public function insertLiveDrawResultsBatch($records) {
        try {
            $sql = "INSERT INTO live_draw_results 
                    (market_code, market_name, result_4d, result_3d, result_2d, 
                     draw_date, draw_time, as_digit, kop_digit, kep_digit, ekor_digit) 
                    VALUES ";
            
            $values = [];
            $params = [];
            
            foreach ($records as $record) {
                $values[] = "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $params = array_merge($params, [
                    $record['market_code'], $record['market_name'], $record['result_4d'],
                    $record['result_3d'], $record['result_2d'], $record['draw_date'],
                    $record['draw_time'], $record['as_digit'], $record['kop_digit'],
                    $record['kep_digit'], $record['ekor_digit']
                ]);
            }
            
            $sql .= implode(', ', $values);
            $sql .= " ON DUPLICATE KEY UPDATE 
                      result_4d = VALUES(result_4d),
                      result_3d = VALUES(result_3d),
                      result_2d = VALUES(result_2d),
                      updated_at = CURRENT_TIMESTAMP";
            
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($params);
        } catch (PDOException $e) {
            error_log("Error in batch insert: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * DATABASE MAINTENANCE: Clean old data
     */
    public function cleanupOldData($days = 365) {
        try {
            // Keep only last X days of data
            $cutoffDate = date('Y-m-d', strtotime("-{$days} days"));
            
            $queries = [
                "DELETE FROM admin_activity_log WHERE created_at < ?",
                "DELETE FROM support_tickets WHERE created_at < ? AND status = 'closed'",
                // Add more cleanup queries as needed
            ];
            
            foreach ($queries as $query) {
                $stmt = $this->pdo->prepare($query);
                $stmt->execute([$cutoffDate]);
            }
            
            return true;
        } catch (PDOException $e) {
            error_log("Error in cleanup: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * PERFORMANCE MONITORING: Check slow queries
     */
    public function getPerformanceStats() {
        try {
            $stats = [];
            
            // Database size
            $stmt = $this->pdo->prepare("
                SELECT 
                    table_name,
                    table_rows,
                    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                ORDER BY (data_length + index_length) DESC
            ");
            $stmt->execute();
            $stats['table_sizes'] = $stmt->fetchAll();
            
            // Index usage
            $stmt = $this->pdo->prepare("
                SELECT 
                    table_name,
                    index_name,
                    cardinality
                FROM information_schema.statistics 
                WHERE table_schema = DATABASE()
                ORDER BY table_name, cardinality DESC
            ");
            $stmt->execute();
            $stats['index_info'] = $stmt->fetchAll();
            
            return $stats;
        } catch (PDOException $e) {
            error_log("Error getting performance stats: " . $e->getMessage());
            return [];
        }
    }
}

// USAGE EXAMPLES:

/*
// In your pages, replace old queries with optimized versions:

// OLD (loads all data):
$results = $db->getLiveDrawResults();

// NEW (paginated):
$page = $_GET['page'] ?? 1;
$limit = 20;
$offset = ($page - 1) * $limit;
$results = $db->getLiveDrawResultsOptimized($limit, $offset);

// OLD (inefficient search):
$items = $db->getBukuMimpi($type, $search);

// NEW (optimized search):
$items = $db->getBukuMimpiOptimized($type, $limit, $offset, $search);

// Caching example:
$expensiveData = $db->getCachedData('market_stats', function() use ($db) {
    return $db->getDashboardStatsOptimized();
}, 300); // Cache for 5 minutes
*/

?>