<?php
/**
 * Standalone migration for ai_settings + ai_settings_log tables.
 * Visit: http://localhost/orchview2/migrate_settings.php
 * DELETE this file after running.
 */

function loadEnvVars(string $envFile): array {
    $vars = [];
    if (!file_exists($envFile)) return $vars;
    foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#') continue;
        if (strpos($line, '=') === false) continue;
        [$key, $val] = explode('=', $line, 2);
        $key = trim($key);
        $val = trim($val);
        if ((($val[0] ?? '') === '"' || ($val[0] ?? '') === "'") && $val[0] === substr($val, -1)) {
            $val = substr($val, 1, -1);
        }
        $vars[$key] = $val;
    }
    return $vars;
}

function e(?string $s): string { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

$root = dirname(__DIR__);
$env  = loadEnvVars($root . '/.env');
if (empty($env)) $env = loadEnvVars($root . '/.env.example');

$host = $env['DB_HOST'] ?? 'localhost';
$db   = $env['DB_NAME'] ?? 'orchview2';
$user = $env['DB_USER'] ?? 'root';
$pass = $env['DB_PASSWORD'] ?? '';

$output = [];
try {
    $pdo = new PDO("mysql:host={$host};dbname={$db};charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    // ── ai_settings ──────────────────────────────────────────────────
    $tables = $pdo->query("SHOW TABLES LIKE 'ai_settings'")->fetchAll();
    if (empty($tables)) {
        $pdo->exec("
            CREATE TABLE ai_settings (
                setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
                setting_value TEXT NULL,
                description VARCHAR(255) NULL,
                category VARCHAR(50) NULL,
                updated_at DATETIME NULL,
                updated_by INT UNSIGNED NULL,
                INDEX idx_as_cat (category)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
        $output[] = "✓ Created ai_settings table";
    } else {
        $output[] = "- ai_settings already exists";
    }

    // ── ai_settings_log ──────────────────────────────────────────────
    $tables = $pdo->query("SHOW TABLES LIKE 'ai_settings_log'")->fetchAll();
    if (empty($tables)) {
        $pdo->exec("
            CREATE TABLE ai_settings_log (
                log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                setting_key VARCHAR(100) NOT NULL,
                old_value TEXT NULL,
                new_value TEXT NULL,
                changed_at DATETIME NOT NULL,
                changed_by INT UNSIGNED NULL,
                INDEX idx_asl_key (setting_key)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
        $output[] = "✓ Created ai_settings_log table";
    } else {
        $output[] = "- ai_settings_log already exists";
    }

} catch (\Throwable $e) {
    $output[] = "ERROR: " . $e->getMessage();
    if (!empty($env['APP_DEBUG']) && $env['APP_DEBUG'] === 'true') {
        $output[] = $e->getTraceAsString();
    }
}

header('Content-Type: text/plain; charset=utf-8');
echo "Settings Migration\n==================\n\n";
foreach ($output as $line) echo $line . "\n";
echo "\nDone. Delete this file (migrate_settings.php) after running.\n";