<?php

namespace Tempo\Purchasing\Delivery;

use App\Shared\Context\CompanyContext;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Schema\Blueprint;
use Tempo\Purchasing\SgfCandidate\Schema as RequisitionSchema;

/** Schema preparation is deliberately independent of route/code activation and grants. */
final class Installation
{
    public const TABLES = ['cb_centres', 'cb_origins', 'cb_budgets', 'cb_budget_entries', 'cb_budget_consumptions', 'cb_budget_reinforcements', 'cb_audit'];

    public static function target(ConnectionInterface $db): string
    {
        if ($db->getDriverName() !== 'pgsql' || $db->selectOne('select current_database() as name')->name !== $db->getDatabaseName()) {
            throw new \RuntimeException('Destino PostgreSQL não conferido.');
        }

        $server = $db->selectOne('select inet_server_addr()::text as address, inet_server_port() as port');

        return hash('sha256', json_encode([$db->getDriverName(), $db->getConfig('host'), (string) $db->getConfig('port'), $db->getDatabaseName(), $server->address, $server->port], JSON_THROW_ON_ERROR));
    }

    public static function migrationHash(): string
    {
        return hash('sha256', file_get_contents(__DIR__.'/BudgetSchema.php').file_get_contents(__DIR__.'/BudgetUiSchema.php').file_get_contents(__DIR__.'/budget-contract.json').file_get_contents(dirname(__DIR__).'/internal-requisitions/Schema.php'));
    }

    public static function signature(ConnectionInterface $db, array $tables): string
    {
        sort($tables);
        $bindings = implode(',', array_fill(0, count($tables), '?'));

        return hash('sha256', json_encode([
            $db->select("select table_name,column_name,data_type,character_maximum_length,is_nullable,ordinal_position from information_schema.columns where table_schema='public' and table_name in ($bindings) order by table_name,ordinal_position", $tables),
            $db->select("select c.relname,k.conname,pg_get_constraintdef(k.oid) as definition from pg_constraint k join pg_class c on c.oid=k.conrelid join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and c.relname in ($bindings) order by c.relname,k.conname", $tables),
            $db->select("select c.relname,t.tgname,pg_get_triggerdef(t.oid) as definition from pg_trigger t join pg_class c on c.oid=t.tgrelid join pg_namespace n on n.oid=c.relnamespace where n.nspname='public' and c.relname in ($bindings) and not t.tgisinternal order by c.relname,t.tgname", $tables),
        ], JSON_THROW_ON_ERROR));
    }

    public static function tables(): array
    {
        return [...self::TABLES, ...BudgetUiSchema::TABLES, 'permission_grants', 'ir_requests', 'ir_steps', 'ir_events', 'ir_commands', 'ir_policies', 'ir_policy_versions'];
    }

    public static function migrate(ConnectionInterface $db, string $expectedTarget, string $backupHash, string $operator): array
    {
        if (! hash_equals(self::target($db), $expectedTarget) || ! preg_match('/^[a-f0-9]{64}$/D', $backupHash) || trim($operator) === '') {
            throw new \RuntimeException('Destino, backup ou operador não conferidos.');
        }

        return $db->transaction(function () use ($db, $backupHash, $operator) {
            $db->select("select pg_advisory_xact_lock(hashtext('tempo.internal-requisitions.install.v1'))");
            $schema = $db->getSchemaBuilder();
            if ($schema->hasTable('sgc_installations')) {
                $old = $db->table('sgc_installations')->where('module', 'internal-requisitions')->first();
                if (! $old || $old->revision !== 2 || ! hash_equals($old->migration_sha256, self::migrationHash()) || ! hash_equals($old->schema_signature, self::signature($db, self::tables()))) {
                    throw new \RuntimeException('Instalação/schema divergente; reconciliar sem sobrescrever.');
                }

                return ['prepared' => true, 'replayed' => true, 'activated' => false];
            }
            foreach (['users', 'tenants', 'companies', 'units', 'company_memberships', 'permission_grants', 'identity_audit'] as $table) {
                if (! $schema->hasTable($table)) {
                    throw new \RuntimeException('Fundação de identidade ausente: '.$table);
                }
            }
            $budgetTables = array_filter(self::TABLES, fn ($table) => $schema->hasTable($table));
            if ($budgetTables !== [] && count($budgetTables) !== count(self::TABLES)) {
                throw new \RuntimeException('Schema orçamental parcial; não completar às cegas.');
            }
            if ($budgetTables === []) {
                BudgetSchema::create($db);
            }
            $contract = json_decode(file_get_contents(__DIR__.'/budget-contract.json'), true, 16, JSON_THROW_ON_ERROR);
            if (! hash_equals($contract['schema_signature'], self::signature($db, self::TABLES))) {
                throw new \RuntimeException('Schema orçamental incompatível com o contrato aceite.');
            }
            $constraint = $db->selectOne("select pg_get_constraintdef(oid) as definition from pg_constraint where conrelid='permission_grants'::regclass and conname='permission_grants_scope_check'");
            if (! $constraint || ! preg_match('/^CHECK\s*\((.*)\)$/s', $constraint->definition, $parts)) {
                throw new \RuntimeException('Catálogo de âmbito desconhecido.');
            }
            $budgetRights = array_values(array_filter(AccessOverlay::RIGHTS, fn ($right) => str_starts_with($right, 'purchasing_budget.')));
            if (array_filter($budgetRights, fn ($right) => ! str_contains($constraint->definition, "'".$right."'")) !== []) {
                $literals = implode(',', array_map(fn ($right) => $db->getPdo()->quote($right).'::text', $budgetRights));
                $db->statement('ALTER TABLE permission_grants DROP CONSTRAINT permission_grants_scope_check');
                $db->statement("ALTER TABLE permission_grants ADD CONSTRAINT permission_grants_scope_check CHECK (({$parts[1]}) OR (unit_id IS NOT NULL AND permission::text IN ($literals)))");
            }
            RequisitionSchema::install($db);
            BudgetUiSchema::create($db);
            $schema->create('sgc_installations', function (Blueprint $table) {
                $table->string('module')->primary();
                $table->integer('revision');
                $table->string('schema_signature', 64);
                $table->string('migration_sha256', 64);
                $table->string('backup_sha256', 64);
                $table->string('operator');
                $table->string('database_operator');
                $table->timestampTz('prepared_at');
            });
            $db->table('sgc_installations')->insert(['module' => 'internal-requisitions', 'revision' => 2, 'schema_signature' => self::signature($db, self::tables()), 'migration_sha256' => self::migrationHash(), 'backup_sha256' => $backupHash, 'operator' => $operator, 'database_operator' => $db->selectOne('select current_user as name')->name, 'prepared_at' => now()]);
            $db->unprepared("CREATE FUNCTION sgc_installations_immutable() RETURNS trigger LANGUAGE plpgsql AS \$\$ BEGIN RAISE EXCEPTION 'Preserve installation history'; END; \$\$; CREATE TRIGGER sgc_installations_immutable BEFORE UPDATE OR DELETE ON sgc_installations FOR EACH ROW EXECUTE FUNCTION sgc_installations_immutable();");

            return ['prepared' => true, 'replayed' => false, 'activated' => false];
        });
    }

    public static function assertBudgetReady(ConnectionInterface $db): void
    {
        abort_unless(config('purchasing-operational.enabled') === true, 403);
        abort_unless(hash_equals((string) config('purchasing-operational.target', ''), self::target($db)), 403, 'Destino operacional divergente.');
        $context = app(CompanyContext::class);
        $scope = $context->identifiers() + ['unit_id' => $context->unitId()];
        abort_unless($scope['unit_id'] !== null && in_array(implode(':', $scope), config('purchasing-operational.scopes', []), true), 403, 'Opção interna não configurada para esta unidade.');
        abort_unless($db->getSchemaBuilder()->hasTable('sgc_installations'), 503, 'Schema operacional não preparado.');
        $row = $db->table('sgc_installations')->where('module', 'internal-requisitions')->first();
        $signatureMatches=$row && hash_equals($row->schema_signature, self::signature($db, self::tables()));
        if(!$signatureMatches && class_exists(\Tempo\Purchasing\SgfSourcing\Schema::class)){
            $signatureMatches=\Tempo\Purchasing\SgfSourcing\Schema::compatible($db);
        }
        abort_unless($row && $row->revision === 2 && hash_equals($row->migration_sha256, self::migrationHash()) && $signatureMatches, 503, 'Schema operacional não preparado.');
    }
}