<?php
/**
 * Optimized Database Class with Connection Pooling for Production
 * This replaces multiple new Database() calls with a singleton pattern
 */

class DatabaseOptimized {
    private static $instance = null;
    private static $pdo = null;
    
    private $host = 'localhost';
    private $dbname = 'AUTOPREDIKSI'; 
    private $username = 'root';
    private $password = '';

    // Prevent direct instantiation
    private function __construct() {
        if (self::$pdo === null) {
            try {
                // Check if MySQL PDO driver is available
                if (!extension_loaded('pdo_mysql')) {
                    throw new Exception("PDO MySQL extension not loaded. Please enable pdo_mysql in php.ini");
                }

                self::$pdo = new PDO(
                    "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4",
                    $this->username,
                    $this->password,
                    [
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                        PDO::ATTR_EMULATE_PREPARES => false,
                        PDO::ATTR_PERSISTENT => true, // Use persistent connections
                        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, // Buffer queries for better memory usage
                        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci" // Set charset
                    ]
                );
                
                // Set SQL mode for better performance
                self::$pdo->exec("SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
                
            } catch (Exception $e) {
                error_log("Database connection error: " . $e->getMessage());
                throw $e;
            }
        }
    }

    // Get singleton instance
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    // Get PDO connection
    public function getConnection() {
        return self::$pdo;
    }

    // Prevent cloning
    private function __clone() {}

    // Prevent unserialization
    private function __wakeup() {}

    /**
     * Optimized method to get live draw results with proper indexing
     */
    public function getLiveDrawResultsOptimized($site, $limit = 20, $offset = 0, $marketCode = null) {
        try {
            $tableName = "live_draw_results_" . $site;
            
            $sql = "SELECT market, draw_date, draw_time, result, created_at FROM {$tableName}";
            $params = [];
            
            if ($marketCode) {
                $sql .= " WHERE market = ?";
                $params[] = $marketCode;
            }
            
            // Use indexed columns for ORDER BY (we created idx_draw_date and idx_draw_time)
            $sql .= " ORDER BY draw_date DESC, draw_time DESC LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
            
            $stmt = self::$pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            error_log("Error getting live draw results: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Optimized method to get market schedules with proper indexing
     */
    public function getMarketSchedulesOptimized($site, $activeOnly = true) {
        try {
            $tableName = "market_schedules_" . $site;
            
            $sql = "SELECT market_name, market_code, open_time, close_time, logo_path, official_site FROM {$tableName}";
            $params = [];
            
            if ($activeOnly) {
                $sql .= " WHERE is_active = ?"; // Uses idx_active index
                $params[] = 1;
            }
            
            $sql .= " ORDER BY open_time"; // Uses idx_open_time index
            
            $stmt = self::$pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            error_log("Error getting market schedules: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Optimized method to get buku mimpi with search
     */
    public function getBukuMimpiOptimized($site, $search = '', $limit = 20, $offset = 0) {
        try {
            $tableName = "buku_mimpi_" . $site;
            
            // Only select needed columns
            $sql = "SELECT id, angka_2d, deskripsi, image_url FROM {$tableName} WHERE is_active = ?";
            $params = [1];
            
            if (!empty($search)) {
                $sql .= " AND (angka_2d LIKE ? OR deskripsi LIKE ?)";
                $searchTerm = "%{$search}%";
                $params[] = $searchTerm;
                $params[] = $searchTerm;
            }
            
            $sql .= " ORDER BY angka_2d ASC LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
            
            $stmt = self::$pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            error_log("Error getting buku mimpi: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Optimized method to get support tickets with indexing
     */
    public function getSupportTicketsOptimized($site, $status = null, $limit = 20, $offset = 0) {
        try {
            $tableName = "support_tickets_" . $site;
            
            $sql = "SELECT id, ticket_number, username, whatsapp, subject, status, created_at FROM {$tableName}";
            $params = [];
            
            if ($status) {
                $sql .= " WHERE status = ?"; // Uses idx_status index
                $params[] = $status;
            }
            
            $sql .= " ORDER BY created_at DESC LIMIT ? OFFSET ?"; // Uses idx_created_at index
            $params[] = (int)$limit;
            $params[] = (int)$offset;
            
            $stmt = self::$pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            error_log("Error getting support tickets: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get connection info for monitoring
     */
    public function getConnectionInfo() {
        try {
            $stmt = self::$pdo->query("SELECT CONNECTION_ID() as connection_id, NOW() as current_time");
            return $stmt->fetch();
        } catch (PDOException $e) {
            return ['error' => $e->getMessage()];
        }
    }
}

/**
 * Backward compatibility function
 * Replace all "new Database()" calls with this
 */
function getOptimizedDB() {
    return DatabaseOptimized::getInstance();
}

/**
 * Quick method to get optimized database connection
 */
function getDB() {
    return DatabaseOptimized::getInstance()->getConnection();
}
?>