<?php
/**
 * Execute a .sql file against the ERP database (reads .env for credentials).
 * Usage: php scripts/run_sql_file.php path/to/file.sql
 */

declare(strict_types=1);

if ($argc < 2) {
    fwrite(STDERR, "Usage: php scripts/run_sql_file.php <sql-file>\n");
    exit(1);
}

$file = $argv[1];
if (! is_file($file)) {
    fwrite(STDERR, "File not found: {$file}\n");
    exit(1);
}

$host = '127.0.0.1';
$user = 'root';
$pass = '';
$db   = 'erp_foundation';
$port = 3306;

$envFile = dirname(__DIR__) . DIRECTORY_SEPARATOR . '.env';
if (is_file($envFile)) {
    foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#' || strpos($line, '=') === false) {
            continue;
        }
        list($k, $v) = array_map('trim', explode('=', $line, 2));
        $v = trim($v, " \t\"'");
        if ($k === 'database.default.hostname') {
            $host = $v;
        } elseif ($k === 'database.default.username') {
            $user = $v;
        } elseif ($k === 'database.default.password') {
            $pass = $v;
        } elseif ($k === 'database.default.database') {
            $db = $v;
        } elseif ($k === 'database.default.port') {
            $port = (int) $v;
        }
    }
}

$sql = file_get_contents($file);
if ($sql === false) {
    fwrite(STDERR, "Could not read {$file}\n");
    exit(1);
}

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$m = new mysqli($host, $user, $pass, $db, $port);
$m->set_charset('utf8mb4');

echo "Running {$file} on {$db}...\n";

if ($m->multi_query($sql)) {
    $index = 0;
    do {
        $index++;
        if ($result = $m->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo json_encode($row, JSON_UNESCAPED_UNICODE) . "\n";
            }
            $result->free();
        }
        if ($m->errno) {
            fwrite(STDERR, "Error after statement #{$index}: [{$m->errno}] {$m->error}\n");
            exit(1);
        }
    } while ($m->more_results() && $m->next_result());
} else {
    fwrite(STDERR, "multi_query failed: [{$m->errno}] {$m->error}\n");
    exit(1);
}

if ($m->errno) {
    fwrite(STDERR, "Final error: [{$m->errno}] {$m->error}\n");
    exit(1);
}

echo "Done.\n";
