<?php
/**
 * Database Connection Configuration
 * MoMo Plugin Platform - South Sudan
 * 
 * Simplified and bulletproof database connection
 */

// Prevent multiple inclusions
if (defined('DB_CONNECTED')) {
    return;
}
define('DB_CONNECTED', true);

// Environment Configuration
$environment = $_ENV['APP_ENV'] ?? 'production'; // Change to 'development' for debugging

// Database Configuration
$db_config = [
    'host' => 'localhost',
    'username' => 'homecar1_root',
    'password' => '2021wemadeit',
    'database' => 'homecar1_connectpay',
    'port' => 3306,
    'charset' => 'utf8mb4'
];

// Initialize connection variable
$conn = null;

try {
    // Create connection
    $conn = new mysqli(
        $db_config['host'],
        $db_config['username'],
        $db_config['password'],
        $db_config['database'],
        $db_config['port']
    );

    // Check connection
    if ($conn->connect_error) {
        throw new Exception("Connection failed: " . $conn->connect_error);
    }

    // Set charset
    if (!$conn->set_charset($db_config['charset'])) {
        throw new Exception("Error setting charset: " . $conn->error);
    }

    // Set timezone to UTC
    $conn->query("SET time_zone = '+00:00'");

    // Connection success
    if ($environment === 'development') {
        error_log("Database connected successfully to {$db_config['database']}");
    }

} catch (Exception $e) {
    // Log the error
    error_log("Database Connection Error: " . $e->getMessage());
    
    if ($environment === 'development') {
        // Show detailed error in development
        die('
        <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 50px auto; padding: 20px; border: 1px solid #f44336; border-radius: 5px; background: #ffebee;">
            <h2 style="color: #d32f2f;">Database Connection Error</h2>
            <p><strong>Host:</strong> ' . $db_config['host'] . ':' . $db_config['port'] . '</p>
            <p><strong>Database:</strong> ' . $db_config['database'] . '</p>
            <p><strong>Username:</strong> ' . $db_config['username'] . '</p>
            <p><strong>Error:</strong> ' . htmlspecialchars($e->getMessage()) . '</p>
            <hr>
            <h3>Quick Fixes:</h3>
            <ol>
                <li>Check if MySQL is running: <code>sudo systemctl status mysql</code></li>
                <li>Test connection: <code>mysql -u ' . $db_config['username'] . ' -p ' . $db_config['database'] . '</code></li>
                <li>Verify database exists and user has permissions</li>
                <li>Check cPanel/hosting control panel database settings</li>
            </ol>
        </div>
        ');
    } else {
        // Show generic error in production
        die('
        <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 50px auto; padding: 20px; border: 1px solid #ddd; border-radius: 5px;">
            <h2 style="color: #d32f2f;">System Temporarily Unavailable</h2>
            <p>We are experiencing technical difficulties. Please try again later.</p>
            <p>If the problem persists, please contact support.</p>
            <hr>
            <small>Error ID: ' . uniqid() . ' | Time: ' . date('Y-m-d H:i:s') . '</small>
        </div>
        ');
    }
}

// Test the connection with a simple query
try {
    if ($conn) {
        $test_result = $conn->query("SELECT 1 as test");
        if (!$test_result) {
            throw new Exception("Database test query failed: " . $conn->error);
        }
        
        if ($environment === 'development') {
            error_log("Database test query successful");
        }
    }
} catch (Exception $e) {
    error_log("Database test failed: " . $e->getMessage());
    if ($environment === 'development') {
        die('Database connection test failed: ' . htmlspecialchars($e->getMessage()));
    }
}

/**
 * Essential Helper Functions
 * FIXED: Check if functions exist before declaring them
 */

/**
 * Safely escape string for database queries
 */
if (!function_exists('db_escape')) {
    function db_escape($string) {
        global $conn;
        if (!$conn) return false;
        return $conn->real_escape_string($string);
    }
}

/**
 * Execute a prepared statement safely
 */
if (!function_exists('db_execute')) {
    function db_execute($query, $params = [], $types = '') {
        global $conn;
        
        if (!$conn) {
            error_log("No database connection available for query: " . $query);
            return false;
        }
        
        try {
            $stmt = $conn->prepare($query);
            
            if (!$stmt) {
                throw new Exception("Prepare failed: " . $conn->error);
            }
            
            if (!empty($params)) {
                if (empty($types)) {
                    // Auto-detect parameter types
                    $types = '';
                    foreach ($params as $param) {
                        if (is_int($param)) {
                            $types .= 'i';
                        } elseif (is_float($param)) {
                            $types .= 'd';
                        } else {
                            $types .= 's';
                        }
                    }
                }
                
                $stmt->bind_param($types, ...$params);
            }
            
            $result = $stmt->execute();
            
            if (!$result) {
                throw new Exception("Execute failed: " . $stmt->error);
            }
            
            // Return result for SELECT queries
            if (stripos(trim($query), 'SELECT') === 0) {
                return $stmt->get_result();
            }
            
            // Return affected rows for other queries
            return $stmt->affected_rows;
            
        } catch (Exception $e) {
            error_log("Database Query Error: " . $e->getMessage() . " | Query: " . $query);
            return false;
        }
    }
}

/**
 * Get last insert ID
 */
if (!function_exists('db_insert_id')) {
    function db_insert_id() {
        global $conn;
        return $conn ? $conn->insert_id : 0;
    }
}

/**
 * Check if database connection is alive
 */
if (!function_exists('db_ping')) {
    function db_ping() {
        global $conn;
        return $conn ? $conn->ping() : false;
    }
}

/**
 * Close database connection
 */
if (!function_exists('db_close')) {
    function db_close() {
        global $conn;
        if ($conn) {
            $conn->close();
            $conn = null;
        }
    }
}

// Register shutdown function to close connection (only if not already registered)
if (!function_exists('db_shutdown_registered')) {
    function db_shutdown_registered() {
        return true;
    }
    register_shutdown_function('db_close');
}

// Final validation
if (!$conn instanceof mysqli) {
    error_log("Database connection validation failed - connection is not a mysqli instance");
    if ($environment === 'development') {
        die('Database connection not established properly. Check error log for details.');
    }
    die('System temporarily unavailable.');
}

// Success confirmation for development
if ($environment === 'development') {
    // Uncomment for debugging:
    // echo "<!-- Database connected successfully to {$db_config['database']} -->\n";
}

?>