<?php
/**
 * Database Configuration for Zen Zone Spa API
 * Optimized for cPanel MySQL (UNIX socket, MySQL 5.7.35)
 */

// Load environment configuration
require_once __DIR__ . '/env.php';

class DatabaseConfig {
    private static $connection = null;
    
    /**
     * Get database connection
     * Simple, bulletproof implementation for cPanel
     */
    public static function getConnection() {
        if (self::$connection === null) {
            try {
                $host = EnvLoader::get('DB_HOST', 'localhost');
                $dbname = EnvLoader::get('DB_NAME', 'zenzone_spa_db');
                $username = EnvLoader::get('DB_USERNAME', 'your_db_user');
                $password = EnvLoader::get('DB_PASSWORD', 'your_db_pass');
                
                // Simple DSN for cPanel (UNIX socket)
                $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
                
                // Minimal options - no SSL, no fancy stuff
                $options = [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ];
                
                // Create connection
                self::$connection = new PDO($dsn, $username, $password, $options);
                
                // Set charset (cPanel compatible)
                self::$connection->exec("SET NAMES utf8mb4");
                
            } catch (PDOException $e) {
                // Log error but don't expose details
                error_log("Database connection error: " . $e->getMessage());
                throw new Exception("Database connection failed. Please try again later.");
            }
        }
        
        return self::$connection;
    }
    
    /**
     * Test database connection
     */
    public static function testConnection() {
        try {
            $pdo = self::getConnection();
            $stmt = $pdo->query("SELECT 1");
            return $stmt !== false;
        } catch (Exception $e) {
            error_log("Database test failed: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Execute query with prepared statements
     */
    public static function executeQuery($sql, $params = []) {
        try {
            $pdo = self::getConnection();
            $stmt = $pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            error_log("Database query error: " . $e->getMessage());
            throw new Exception("Database query failed. Please try again.");
        }
    }
    
    /**
     * Begin transaction
     */
    public static function beginTransaction() {
        return self::getConnection()->beginTransaction();
    }
    
    /**
     * Commit transaction
     */
    public static function commit() {
        return self::getConnection()->commit();
    }
    
    /**
     * Rollback transaction
     */
    public static function rollback() {
        return self::getConnection()->rollBack();
    }
    
    /**
     * Get last insert ID
     */
    public static function lastInsertId() {
        return self::getConnection()->lastInsertId();
    }
    
    /**
     * Close database connection
     */
    public static function closeConnection() {
        self::$connection = null;
    }
    
    /**
     * Get database information
     */
    public static function getDatabaseInfo() {
        try {
            $pdo = self::getConnection();
            $version = $pdo->query("SELECT VERSION() as version")->fetch();
            $charset = $pdo->query("SELECT @@character_set_database as charset")->fetch();
            
            return [
                'mysql_version' => $version['version'],
                'charset' => $charset['charset'],
                'host' => EnvLoader::get('DB_HOST'),
                'database' => EnvLoader::get('DB_NAME')
            ];
        } catch (Exception $e) {
            return null;
        }
    }
}
