inventory/backend/api/init_database.php

<?php
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
    exit(0);
}

require_once '../config/database.php';
require_once '../models/User.php';

$database = new Database();
$conn = null;

try {
    $conn = $database->getConnection();
    
    // Check if database is already initialized
    $checkTables = $conn->query("SHOW TABLES LIKE 'users'");
    if ($checkTables->rowCount() > 0) {
        // Check if admin user exists
        $checkAdmin = $conn->prepare("SELECT COUNT(*) as count FROM users WHERE username = 'admin'");
        $checkAdmin->execute();
        $adminCount = $checkAdmin->fetch(PDO::FETCH_ASSOC)['count'];
        
        if ($adminCount > 0) {
            echo json_encode([
                "success" => true,
                "message" => "Database already initialized",
                "status" => "already_initialized"
            ]);
            exit;
        }
    }
    
    // Read and execute setup script
    $setupScript = file_get_contents('../setup_database.sql');
    
    // Split the script into individual statements
    $statements = preg_split('/;\s*\n/', $setupScript);
    
    $executedStatements = 0;
    $errors = [];
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (empty($statement) || strpos($statement, '--') === 0) {
            continue; // Skip empty lines and comments
        }
        
        try {
            $conn->exec($statement);
            $executedStatements++;
        } catch (PDOException $e) {
            // Log error but continue execution
            $errors[] = [
                'statement' => substr($statement, 0, 100) . '...',
                'error' => $e->getMessage()
            ];
        }
    }
    
    // Verify initialization
    $finalCheck = $conn->query("SELECT COUNT(*) as table_count FROM information_schema.tables WHERE table_schema = DATABASE()");
    $tableCount = $finalCheck->fetch(PDO::FETCH_ASSOC)['table_count'];
    
    // Check for admin user
    $adminCheck = $conn->prepare("SELECT COUNT(*) as admin_count FROM users WHERE username = 'admin'");
    $adminCheck->execute();
    $adminExists = $adminCheck->fetch(PDO::FETCH_ASSOC)['admin_count'] > 0;
    
    echo json_encode([
        "success" => true,
        "message" => "Database initialization completed",
        "status" => "initialized",
        "executed_statements" => $executedStatements,
        "table_count" => $tableCount,
        "admin_user_created" => $adminExists,
        "errors" => count($errors) > 0 ? $errors : null
    ]);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        "success" => false,
        "message" => "Database initialization failed",
        "error" => $e->getMessage()
    ]);
}
?>