<?php
// Master Backup & Restore Test Script
$pdo = null;
try {
    define('SECURE_ACCESS', true);
    require_once __DIR__ . '/../includes/config.php';
    require_once __DIR__ . '/../includes/db.php';
    $pdo = getDBConnection(true);
} catch (Exception $e) {
    die("Database connection failed: " . $e->getMessage() . "\n");
}

// 1. Setup backup test table
try {
    $pdo->exec("DROP TABLE IF EXISTS \"backup_test_table\" CASCADE;");
    $pdo->exec("CREATE TABLE \"backup_test_table\" (id SERIAL PRIMARY KEY, note VARCHAR(100));");
    $pdo->exec("INSERT INTO \"backup_test_table\" (note) VALUES ('Backup verification successful!'), ('MLM nodes running!');");
} catch (Exception $e) {
    die("Setup failed: " . $e->getMessage() . "\n");
}

// 2. Trigger backup download via subprocess runner
$backup_sql = shell_exec("C:\\xampp\\php\\php.exe scratch/test_db_backup_helper.php");
if (empty($backup_sql) || strpos($backup_sql, 'backup_test_table') === false) {
    echo "Backup generation: FAILED (Empty or missing test table schema)\n";
    $pdo->exec("DROP TABLE IF EXISTS \"backup_test_table\" CASCADE;");
    exit(1);
}
echo "Backup generation: PASSED\n";

// 3. Drop table
$pdo->exec("DROP TABLE IF EXISTS \"backup_test_table\" CASCADE;");

// 4. Save SQL to temp file and restore via subprocess runner
$temp_file = tempnam(sys_get_temp_dir(), 'backup_');
file_put_contents($temp_file, $backup_sql);

$restore_res = shell_exec("C:\\xampp\\php\\php.exe scratch/test_db_restore_helper.php " . escapeshellarg($temp_file));
@unlink($temp_file);

$json_start = strpos($restore_res, '{');
$data = null;
if ($json_start !== false) {
    $json_str = substr($restore_res, $json_start);
    $data = json_decode($json_str, true);
}

if (!$data || !$data['success']) {
    echo "Restore operation: FAILED (" . ($data['message'] ?? 'no response') . ")\n";
    exit(1);
}
echo "Restore operation: PASSED\n";

// 5. Query and assert restored table
try {
    $stmt = $pdo->query("SELECT note FROM \"backup_test_table\" ORDER BY id ASC");
    $notes = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if (count($notes) !== 2 || $notes[0] !== 'Backup verification successful!' || $notes[1] !== 'MLM nodes running!') {
        throw new Exception("Data assertion failed. Notes: " . json_encode($notes));
    }
    echo "Data restoration assertion: PASSED\n";
    echo "Database Backup & Restore Verification: ALL PASSED!\n";
} catch (Exception $e) {
    echo "Database Backup & Restore Verification: FAILED: " . $e->getMessage() . "\n";
} finally {
    $pdo->exec("DROP TABLE IF EXISTS \"backup_test_table\" CASCADE;");
}
