inventory/backend/config/database.php

<?php
class Database {
    private $host;
    private $db_name;
    private $username;
    private $password;
    public $conn;

    public function __construct() {
        // In Docker, environment variables are passed directly
        // For local development, try to load .env.local file
        if (getenv('DB_HOST') === false) {
            $envFile = __DIR__ . '/../.env.local';
            if (file_exists($envFile)) {
                $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
                foreach ($lines as $line) {
                    if (strpos($line, '=') !== false) {
                        list($key, $value) = explode('=', $line, 2);
                        putenv("$key=$value");
                    }
                }
            }
        
            // Get database configuration from environment variables
            $this->host = getenv('DB_HOST');
            $this->db_name = getenv('DB_NAME');
            $this->username = getenv('DB_USER');
            $this->password = getenv('DB_PASS');
        }
        
        // Debug: Log environment variables (remove in production)
        error_log("Database config - Host: " . ($this->host ?: 'NOT SET') . 
                 ", DB: " . ($this->db_name ?: 'NOT SET') . 
                 ", User: " . ($this->username ?: 'NOT SET'));
    }

    public function getConnection() {
        $this->conn = null;

        // Check if we're in Docker environment or if host is a container name
        $isDockerEnvironment = ($this->host === 'mariadb' || $this->host === 'mysql' || 
                               strpos($this->host, 'inventory-') === 0 || 
                               !file_exists('/var/run/mysqld/mysqld.sock'));
        
        // In Docker environment, always use TCP connection
        if ($isDockerEnvironment) {
            error_log("Docker environment detected, using TCP connection to host: " . $this->host);
        } else {
            // Check for local MySQL socket first in non-Docker environments
            $localSocketPath = '/var/run/mysqld/mysqld.sock';
            $localSocketExists = file_exists($localSocketPath);
            
            // Try local database connection first if socket exists
            if ($localSocketExists) {
                try {
                    $dsn = "mysql:unix_socket=" . $localSocketPath . ";dbname=" . $this->db_name . ";charset=utf8mb4";
                    error_log("Attempting local database connection via socket: $dsn with user " . $this->username);
                    $this->conn = new PDO($dsn, $this->username, $this->password);
                    $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $this->conn->exec("set names utf8");
                    error_log("Local database connection successful via socket");
                    return $this->conn;
                } catch(PDOException $exception) {
                    error_log("Local database connection failed: " . $exception->getMessage());
                    // Fall through to external connection
                }
            }
        }

        // Try external database connection
        if (empty($this->host) || empty($this->db_name) || empty($this->username)) {
            error_log("Database configuration missing: Host=" . ($this->host ?: 'MISSING') . 
                     ", DB=" . ($this->db_name ?: 'MISSING') . 
                     ", User=" . ($this->username ?: 'MISSING'));
            throw new Exception("Database configuration is incomplete. Please check environment variables.");
        }

        try {
            $dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $connectionType = $isDockerEnvironment ? "external (Docker environment)" : "external";
            error_log("Attempting $connectionType database connection: $dsn with user " . $this->username);
            $this->conn = new PDO($dsn, $this->username, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->exec("set names utf8");
            error_log("$connectionType database connection successful");
        } catch(PDOException $exception) {
            error_log("External database connection error: " . $exception->getMessage());
            error_log("Connection details - Host: " . $this->host . ", DB: " . $this->db_name . ", User: " . $this->username);
            throw new Exception("Database connection failed: " . $exception->getMessage());
        }

        return $this->conn;
    }
}
?>