inventory/backend/init-database.php

#!/usr/bin/env php
<?php

/**
 * Database Initialization Script
 * 
 * This script automatically creates the database and tables if they don't exist.
 * It runs when the container starts and checks if the database is properly set up.
 */

// Load environment variables
function loadEnv($file) {
    if (!file_exists($file)) {
        echo "Environment file not found: $file\n";
        return false;
    }
    
    $lines = file($file, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '#') === 0) continue;
        if (strpos($line, '=') === false) continue;
        
        list($key, $value) = explode('=', $line, 2);
        $key = trim($key);
        $value = trim($value);
        
        if (!array_key_exists($key, $_SERVER) && !array_key_exists($key, $_ENV)) {
            putenv(sprintf('%s=%s', $key, $value));
            $_ENV[$key] = $value;
            $_SERVER[$key] = $value;
        }
    }
    return true;
}

// Load environment from .env file
loadEnv('.env');
// Also try to load from .env.with-services for docker-compose-with-services
loadEnv('.env.with-services');

// Get database configuration
$dbHost = getenv('DB_HOST') ?: 'localhost';
$dbPort = getenv('DB_PORT') ?: '3306';
$dbName = getenv('DB_NAME') ?: 'inventory_db';
$dbUser = getenv('DB_USER') ?: 'root';
$dbPass = getenv('DB_PASS') ?: '';

echo "Database Initialization Script\n";
echo "==============================\n";
echo "Host: $dbHost:$dbPort\n";
echo "Database: $dbName\n";
echo "User: $dbUser\n\n";

try {
    // First connect without database to create it if needed
    $dsn = "mysql:host=$dbHost;port=$dbPort;charset=utf8mb4";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
    
    $pdo = new PDO($dsn, $dbUser, $dbPass, $options);
    echo "Connected to MySQL server successfully.\n";
    
    // Create database if it doesn't exist
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbName` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "Database '$dbName' created or already exists.\n";
    
    // Connect to the specific database
    $pdo->exec("USE `$dbName`");
    
    // Check if tables exist by checking if users table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'users'");
    $tablesExist = $stmt->rowCount() > 0;
    
    if (!$tablesExist) {
        echo "Tables do not exist. Creating database schema...\n";
        
        // Read and execute the complete setup script
        $setupFile = __DIR__ . '/setup_database.sql';
        if (file_exists($setupFile)) {
            $sql = file_get_contents($setupFile);
            $statements = array_filter(array_map('trim', explode(';', $sql)));
            
            foreach ($statements as $statement) {
                if (!empty($statement)) {
                    try {
                        $pdo->exec($statement);
                    } catch (PDOException $e) {
                        echo "Error executing statement: " . $statement . "\n";
                        echo "Error: " . $e->getMessage() . "\n";
                    }
                }
            }
            echo "Database schema created successfully.\n";
        } else {
            echo "Setup script not found: $setupFile\n";
            exit(1);
        }
        
        // Create default user if the script exists
        $defaultUserFile = __DIR__ . '/create_default_user.sql';
        if (file_exists($defaultUserFile)) {
            $sql = file_get_contents($defaultUserFile);
            $statements = array_filter(array_map('trim', explode(';', $sql)));
            
            foreach ($statements as $statement) {
                if (!empty($statement)) {
                    try {
                        $pdo->exec($statement);
                    } catch (PDOException $e) {
                        echo "Error creating default user: " . $e->getMessage() . "\n";
                    }
                }
            }
            echo "Default user created.\n";
        }
        
    } else {
        echo "Database tables already exist. Skipping initialization.\n";
    }
    
    // Verify database setup by checking key tables
    $requiredTables = ['users', 'items', 'clients', 'projects', 'accounting_entries'];
    $missingTables = [];
    
    foreach ($requiredTables as $table) {
        $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
        if ($stmt->rowCount() == 0) {
            $missingTables[] = $table;
        }
    }
    
    if (!empty($missingTables)) {
        echo "Warning: Missing tables: " . implode(', ', $missingTables) . "\n";
        exit(1);
    }
    
    echo "Database initialization completed successfully!\n";
    echo "Required tables are present and ready for use.\n";
    
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage() . "\n";
    echo "Please check your database configuration.\n";
    exit(1);
}