api/setup_db.php

<?php
/**
 * Database Setup Script for Docker Deployment
 * This script initializes the database with tables and default data.
 */

$host = getenv('DB_HOST') ?: "localhost";
$user = getenv('DB_USER') ?: "root";
$pass = getenv('DB_PASS') ?: "";
$db = getenv('DB_NAME') ?: "kiit_kaffe_db";

header('Content-Type: application/json');

try {
    // First, connect without database to create it
    $conn = new PDO("mysql:host=$host;charset=utf8mb4", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Create database if not exists
    $conn->exec("CREATE DATABASE IF NOT EXISTS `$db`");
    $conn->exec("USE `$db`");

    // Read and execute the SQL file using multiQuery for multiple statements
    $sqlFile = __DIR__ . '/../database.sql';
    if (file_exists($sqlFile)) {
        $sql = file_get_contents($sqlFile);
        // Split by semicolons and execute each statement
        $statements = array_filter(array_map('trim', explode(';', $sql)));
        foreach ($statements as $statement) {
            if (!empty($statement)) {
                $conn->exec($statement);
            }
        }
    }

    // Create default admin user if not exists
    $adminCheck = $conn->query("SELECT COUNT(*) FROM users WHERE email = 'admin@kiitkafe.in'")->fetchColumn();
    if ($adminCheck == 0) {
        $adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
        $stmt = $conn->prepare("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)");
        $stmt->execute(['Admin', 'admin@kiitkafe.in', $adminPassword, 'admin']);
    }

    echo json_encode([
        'status' => 'success',
        'message' => 'Database setup completed successfully',
        'database' => $db
    ]);

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'status' => 'error',
        'message' => 'Database setup failed: ' . $e->getMessage()
    ]);
}
?>