<?php

use Illuminate\Contracts\Console\Kernel;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Laravel\Fortify\Features;
use Symfony\Component\Process\Process;

require dirname(__DIR__).'/vendor/autoload.php';
$root = dirname(__DIR__);
if (! in_array('--apply-authorized', $argv, true)) {
    throw new LogicException('Explicit preparation flag required.');
}
$s = json_decode(file_get_contents('C:/laragon/data/sgf-tempo/secrets/payables-demo-compras.json'), true, 32, JSON_THROW_ON_ERROR);
if (($s['APP_ENV'] ?? '') !== 'payable-testing' || ($s['DB_HOST'] ?? '') !== '127.0.0.1' || realpath($s['HOST_ROOT'] ?? '') !== realpath($root) || ! preg_match('/^compras_receipts_test_([a-f0-9]{12})$/D', $s['DB_DATABASE'] ?? '', $m) || $s['DB_USERNAME'] !== 'payable_qa_'.$m[1]) {
    throw new LogicException('Wrong pilot destination.');
}
$app = require $root.'/bootstrap/app.php';
$app->make(Kernel::class)->bootstrap();
$owner = DB::connection('pgsql')->getConfig();
unset($owner['name']);
if ($owner['host'] !== '127.0.0.1') {
    throw new LogicException('Local server required.');
}
$qa = [...$owner, 'url' => null, 'database' => $s['DB_DATABASE'], 'username' => $s['DB_USERNAME'], 'password' => $s['DB_PASSWORD']];
config(['database.connections.receipt_install' => $qa, 'database.connections.receipt_owner' => [...$owner, 'url' => null, 'database' => 'postgres']]);
DB::setDefaultConnection('receipt_install');
$db = DB::connection();
$admin = DB::connection('receipt_owner');
$identity = $db->selectOne('select current_database() as db, current_user as role');
if ($identity->db !== $s['DB_DATABASE'] || $identity->role !== $s['DB_USERNAME']) {
    throw new LogicException('Identity mismatch.');
}
$fingerprint = function ($connection, $tables) {
    $out = [];
    foreach ($tables as $table) {
        $rows = $connection->select('select row_to_json(t)::text as row from "'.str_replace('"', '""', $table).'" t order by row_to_json(t)::text');
        $out[$table] = hash('sha256', json_encode(array_column($rows, 'row'), JSON_THROW_ON_ERROR));
    }

    return $out;
};
$pg = function ($binary, $args, $connection) {
    $p = new Process(['C:/Program Files/PostgreSQL/17/bin/'.$binary.'.exe', ...$args], null, ['PGHOST' => $connection['host'], 'PGPORT' => (string) $connection['port'], 'PGUSER' => $connection['username'], 'PGPASSWORD' => $connection['password']], timeout: 120);
    $p->run();
    if (! $p->isSuccessful()) {
        throw new RuntimeException('Backup/restore failed.');
    }
};

$tables = array_column($db->select("select tablename from pg_tables where schemaname='public' order by tablename"), 'tablename');
$migration = 'database/migrations/2026_11_03_000004_add_administrative_geography_references.php';
$authorization = json_decode(file_get_contents($root.'/docs/proofs/administrative-geography-installation-authorization.json'), true, 32, JSON_THROW_ON_ERROR);
if ($authorization['scope'] !== 'install_two_nullable_geography_columns_and_enable_selection'
    || $authorization['environment'] !== 'http://127.0.0.1:8005'
    || $authorization['proposal_sha256'] !== hash_file('sha256', $root.'/docs/proposta-instalacao-geografia.md')
    || $authorization['migration_sha256'] !== hash_file('sha256', $root.'/'.$migration)
    || file_exists($root.'/docs/proofs/administrative-geography-installation.json')
    || Features::enabled(Features::twoFactorAuthentication())
    || filter_var($s['SGC_TWO_FACTOR_ENABLED'] ?? false, FILTER_VALIDATE_BOOLEAN)
    || filter_var($s['SGC_GEOGRAPHY_ENABLED'] ?? false, FILTER_VALIDATE_BOOLEAN)) {
    throw new LogicException('Authorization or pilot state does not match empty geography installation.');
}
foreach (['tenants', 'suppliers'] as $table) {
    if (Schema::connection('receipt_install')->hasColumn($table, 'geography_reference')) {
        throw new LogicException('Geography schema already present; inspect proof instead of repeating.');
    }
}
$oldColumns = [];
foreach (['tenants', 'suppliers'] as $table) {
    $oldColumns[$table] = Schema::connection('receipt_install')->getColumnListing($table);
}
$projected = function ($connection) use ($oldColumns) {
    $out = [];
    foreach ($oldColumns as $table => $columns) {
        $selected = implode(',', array_map(fn ($c) => '"'.str_replace('"', '""', $c).'"', $columns));
        $rows = $connection->select('select row_to_json(t)::text as row from (select '.$selected.' from "'.$table.'") t order by row_to_json(t)::text');
        $out[$table] = hash('sha256', json_encode(array_column($rows, 'row'), JSON_THROW_ON_ERROR));
    }

    return $out;
};
$checkEmpty = function ($connection, $name) use ($tables, $oldColumns) {
    foreach ($oldColumns as $table => $columns) {
        if (Schema::connection($name)->getColumnListing($table) !== [...$columns, 'geography_reference']
            || $connection->table($table)->whereNotNull('geography_reference')->exists()) {
            throw new LogicException('Unexpected geography structure or nonempty references.');
        }
    }
    if (array_column($connection->select("select tablename from pg_tables where schemaname='public' order by tablename"), 'tablename') !== $tables) {
        throw new LogicException('Unexpected table created.');
    }
};
$restore = 'compras_pilot_restore_'.bin2hex(random_bytes(6));
$created = false;
try {
    $db->beginTransaction();
    $db->statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    $baseline = $fingerprint($db, $tables);
    $beforeRows = $projected($db);
    $migrationCount = $db->table('migrations')->count();
    $directory = 'C:/laragon/data/sgf-tempo/backups/compras-geography-installation-'.gmdate('Ymd-His').'-'.bin2hex(random_bytes(3));
    if (! mkdir($directory, 0700, true)) {
        throw new RuntimeException('Backup unavailable.');
    }
    $dump = $directory.'/before.dump';
    $snapshot = $db->selectOne('select pg_export_snapshot() as snapshot')->snapshot;
    $pg('pg_dump', ['--format=custom', '--snapshot='.$snapshot, '--file='.$dump, '--dbname='.$qa['database']], $qa);
    $db->commit();
    $admin->statement('CREATE DATABASE "'.$restore.'"');
    $created = true;
    $pg('pg_restore', ['--no-owner', '--no-privileges', '--exit-on-error', '--dbname='.$restore, $dump], $owner);
    config(['database.connections.receipt_restore' => [...$owner, 'url' => null, 'database' => $restore]]);
    $restored = DB::connection('receipt_restore');
    if ($fingerprint($restored, $tables) !== $baseline) {
        throw new LogicException('Restored backup differs.');
    }
    if (Artisan::call('migrate', ['--database' => 'receipt_restore', '--path' => $migration, '--force' => true]) !== 0) {
        throw new RuntimeException('Disposable migration failed.');
    }
    $protected = array_values(array_diff($tables, ['tenants', 'suppliers', 'migrations']));
    $checkEmpty($restored, 'receipt_restore');
    if ($fingerprint($restored, $protected) !== array_intersect_key($baseline, array_flip($protected)) || $projected($restored) !== $beforeRows || $restored->table('migrations')->count() !== $migrationCount + 1) {
        throw new LogicException('Unexpected dry-run changes.');
    }
    $db->transaction(function () use ($db, $tables, $baseline, $fingerprint, $migration, $protected, $projected, $beforeRows, $migrationCount, $checkEmpty) {
        $db->statement("SET LOCAL lock_timeout = '5s'");
        foreach ($tables as $table) {
            $db->statement('LOCK TABLE "'.str_replace('"', '""', $table).'" IN SHARE MODE');
        }
        if ($fingerprint($db, $tables) !== $baseline) {
            throw new LogicException('Pilot changed after backup; abort.');
        }
        if (Artisan::call('migrate', ['--database' => 'receipt_install', '--path' => $migration, '--force' => true]) !== 0) {
            throw new RuntimeException('Pilot migration failed.');
        }
        $checkEmpty($db, 'receipt_install');
        if ($fingerprint($db, $protected) !== array_intersect_key($baseline, array_flip($protected)) || $projected($db) !== $beforeRows || $db->table('migrations')->count() !== $migrationCount + 1) {
            throw new LogicException('Unexpected pilot changes.');
        }
    });
    $result = ['checked_at' => gmdate('c'), 'result' => 'passed', 'environment' => 'http://127.0.0.1:8005', 'scope' => $authorization['scope'], 'migration' => $migration, 'migration_sha256' => hash_file('sha256', $root.'/'.$migration), 'backup' => $dump, 'backup_sha256' => hash_file('sha256', $dump), 'restored_tables_checked' => count($tables), 'protected_tables_checked' => count($protected), 'existing_company_and_supplier_columns_preserved' => true, 'new_references_all_null' => true, 'pilot_migration_applied' => true, 'feature_activated' => false, 'mfa_enabled' => false, 'passwords_or_grants_changed' => false, 'restore_database_removed' => false];
} finally {
    if ($db->transactionLevel() > 0) {
        $db->rollBack();
    }
    DB::purge('receipt_restore');
    if ($created) {
        $admin->statement('DROP DATABASE "'.$restore.'" WITH (FORCE)');
    }
}
$result['restore_database_removed'] = true;
file_put_contents($root.'/docs/proofs/administrative-geography-installation.json', json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES).PHP_EOL);
echo 'Two empty geography columns installed; all prior values preserved; activation remains separate.'.PHP_EOL;