<?php

namespace App\Services\Imports;

use App\Models\Customer;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use RuntimeException;

class AdminZoneDnsSqlImporter
{
    private ?int $canonicalServerId = null;

    public function import(
        string $path,
        bool $preview = false,
        bool $importCustomers = true,
        bool $importDns = true,
    ): array {
        if (! is_file($path)) {
            throw new RuntimeException("SQL file not found: {$path}");
        }

        $this->assertRequiredSchema();

        $tables = $this->parseSqlDump($path, [
            'users',
            'dns_manager_domain',
            'dns_domain_user',
            'dns_manager_ns',
        ]);

        $summary = [
            'source' => [
                'users' => count($tables['users']),
                'dns_manager_domain' => count($tables['dns_manager_domain']),
                'dns_domain_user' => count($tables['dns_domain_user']),
                'dns_manager_ns' => count($tables['dns_manager_ns']),
            ],
            'customers' => [
                'created' => 0,
                'updated' => 0,
                'restored' => 0,
                'skipped' => 0,
            ],
            'dns_domains' => [
                'created' => 0,
                'updated' => 0,
                'skipped' => 0,
            ],
            'dns_records' => [
                'created' => 0,
                'updated' => 0,
                'skipped' => 0,
            ],
            'dns_links' => [
                'created' => 0,
                'updated' => 0,
                'skipped' => 0,
            ],
        ];

        if (! $importCustomers && ! $importDns) {
            return $summary;
        }

        $callback = function () use ($tables, &$summary, $preview, $importCustomers, $importDns) {
            $customerIdMap = [];
            $domainIdMap = [];

            if ($importCustomers) {
                $customerIdMap = $this->importCustomers($tables['users'], $summary, $preview);
            }

            if ($importDns) {
                $domainIdMap = $this->importDnsDomains($tables['dns_manager_domain'], $summary, $preview);
                $this->importDnsRecords($tables['dns_manager_ns'], $domainIdMap, $summary, $preview);
                $this->importDnsCustomerLinks($tables['dns_domain_user'], $customerIdMap, $domainIdMap, $summary, $preview);
            }
        };

        if ($preview) {
            $callback();
            return $summary;
        }

        DB::transaction($callback);

        return $summary;
    }

    private function assertRequiredSchema(): void
    {
        foreach (['username', 'email_verified_at', 'type'] as $column) {
            if (! Schema::hasColumn('customers', $column)) {
                throw new RuntimeException(
                    "Missing customers.{$column}. Run migrations before importing admin_zonedns data."
                );
            }
        }
    }

    private function importCustomers(array $rows, array &$summary, bool $preview): array
    {
        $idMap = [];

        foreach ($rows as $row) {
            $sourceId = (int) ($row['id'] ?? 0);
            if ($sourceId <= 0) {
                $summary['customers']['skipped']++;
                continue;
            }

            $match = $this->findCustomerMatch($row);
            $payload = $this->buildCustomerPayload($row);

            if (! $match) {
                $existingById = Customer::withTrashed()->find($sourceId);

                if ($preview) {
                    $summary['customers']['created']++;
                    $idMap[$sourceId] = $existingById?->id ?? $sourceId;
                    continue;
                }

                if (! $existingById) {
                    $payload['id'] = $sourceId;
                }

                $payload['uuid'] = (string) Str::uuid();

                $customerId = DB::table('customers')->insertGetId($payload);
                $summary['customers']['created']++;
                $idMap[$sourceId] = $customerId;
                continue;
            }

            $idMap[$sourceId] = $match->id;

            if ($preview) {
                $summary['customers']['updated']++;
                continue;
            }

            $restored = method_exists($match, 'trashed') && $match->trashed();
            if ($restored) {
                $match->restore();
                $summary['customers']['restored']++;
            }

            DB::table('customers')
                ->where('id', $match->id)
                ->update($payload);

            $summary['customers']['updated']++;
        }

        return $idMap;
    }

    private function importDnsDomains(array $rows, array &$summary, bool $preview): array
    {
        $idMap = [];

        foreach ($rows as $row) {
            $sourceId = (int) ($row['id'] ?? 0);
            if ($sourceId <= 0) {
                $summary['dns_domains']['skipped']++;
                continue;
            }

            $domainName = trim((string) ($row['domain_name'] ?? ''));
            if ($domainName === '') {
                $summary['dns_domains']['skipped']++;
                continue;
            }

            $existing = DB::table('dns_manager_domain')->where('id', $sourceId)->first();
            if (! $existing) {
                $existing = DB::table('dns_manager_domain')->where('domain_name', $domainName)->first();
            }

            $targetId = $existing?->id ?? $sourceId;
            $idMap[$sourceId] = (int) $targetId;

            $payload = [
                'domain_name' => $domainName,
                'dns_manager_server_id' => $this->resolveDnsServerId((int) ($row['dns_manager_server_id'] ?? 0)),
                'is_created' => (int) ($row['is_created'] ?? 0),
                'plesk_id' => $this->nullableInt($row['plesk_id'] ?? null),
                'created_at' => $this->normalizeDateTime($row['created_at'] ?? null) ?? now(),
                'updated_at' => $this->normalizeDateTime($row['updated_at'] ?? null) ?? now(),
            ];

            if ($preview) {
                $summary['dns_domains'][$existing ? 'updated' : 'created']++;
                continue;
            }

            if ($existing) {
                DB::table('dns_manager_domain')->where('id', $existing->id)->update($payload);
                $summary['dns_domains']['updated']++;
                continue;
            }

            $payload['id'] = $sourceId;
            DB::table('dns_manager_domain')->insert($payload);
            $summary['dns_domains']['created']++;
        }

        return $idMap;
    }

    private function importDnsRecords(array $rows, array $domainIdMap, array &$summary, bool $preview): void
    {
        foreach ($rows as $row) {
            $sourceId = (int) ($row['id'] ?? 0);
            $sourceDomainId = (int) ($row['dns_manager_domain_id'] ?? 0);
            $targetDomainId = $domainIdMap[$sourceDomainId] ?? null;

            if ($sourceId <= 0 || ! $targetDomainId) {
                $summary['dns_records']['skipped']++;
                continue;
            }

            $existing = DB::table('dns_manager_ns')->where('id', $sourceId)->first();
            $payload = [
                'dns_manager_domain_id' => $targetDomainId,
                'name' => (string) ($row['name'] ?? ''),
                'type' => (string) ($row['type'] ?? 'A'),
                'priority' => $this->nullableString($row['priority'] ?? null, '0'),
                'weight' => $this->nullableString($row['weight'] ?? null, '0'),
                'port' => $this->nullableString($row['port'] ?? null, '0'),
                'service_name' => $this->nullableString($row['service_name'] ?? null),
                'protocol' => $this->nullableString($row['protocol'] ?? null),
                'value' => $this->nullableString($row['value'] ?? null),
                'plesk_id' => $this->nullableInt($row['plesk_id'] ?? null),
                'ttl' => (int) ($row['ttl'] ?? 300),
                'created_at' => $this->normalizeDateTime($row['created_at'] ?? null) ?? now(),
                'updated_at' => $this->normalizeDateTime($row['updated_at'] ?? null) ?? now(),
            ];

            if (Schema::hasColumn('dns_manager_ns', 'type_valid')) {
                $payload['type_valid'] = $existing?->type_valid ?? (string) ($row['type'] ?? 'A');
            }

            if ($preview) {
                $summary['dns_records'][$existing ? 'updated' : 'created']++;
                continue;
            }

            if ($existing) {
                DB::table('dns_manager_ns')->where('id', $sourceId)->update($payload);
                $summary['dns_records']['updated']++;
                continue;
            }

            $payload['id'] = $sourceId;
            DB::table('dns_manager_ns')->insert($payload);
            $summary['dns_records']['created']++;
        }
    }

    private function importDnsCustomerLinks(
        array $rows,
        array $customerIdMap,
        array $domainIdMap,
        array &$summary,
        bool $preview,
    ): void {
        foreach ($rows as $row) {
            $domainId = $domainIdMap[(int) ($row['domain_id'] ?? 0)] ?? null;
            $customerId = $customerIdMap[(int) ($row['user_id'] ?? 0)] ?? null;

            if (! $domainId || ! $customerId) {
                $summary['dns_links']['skipped']++;
                continue;
            }

            $existing = DB::table('dns_domain_user')
                ->where('domain_id', $domainId)
                ->where('user_id', $customerId)
                ->first();

            $payload = [
                'domain_id' => $domainId,
                'user_id' => $customerId,
                'created_at' => $this->normalizeDateTime($row['created_at'] ?? null) ?? now(),
                'updated_at' => $this->normalizeDateTime($row['updated_at'] ?? null) ?? now(),
            ];

            if ($preview) {
                $summary['dns_links'][$existing ? 'updated' : 'created']++;
                continue;
            }

            if ($existing) {
                DB::table('dns_domain_user')
                    ->where('id', $existing->id)
                    ->update(['updated_at' => $payload['updated_at']]);
                $summary['dns_links']['updated']++;
                continue;
            }

            DB::table('dns_domain_user')->insert($payload);
            $summary['dns_links']['created']++;
        }
    }

    private function findCustomerMatch(array $row): ?Customer
    {
        $email = $this->nullableString($row['email'] ?? null);
        if ($email) {
            $match = Customer::withTrashed()->where('email', $email)->first();
            if ($match) {
                return $match;
            }
        }

        $username = $this->nullableString($row['username'] ?? null);
        if ($username && Schema::hasColumn('customers', 'username')) {
            return Customer::withTrashed()->where('username', $username)->first();
        }

        return null;
    }

    private function buildCustomerPayload(array $row): array
    {
        $email = $this->nullableString($row['email'] ?? null);

        return [
            'fullname' => $this->nullableString($row['name'] ?? null) ?? $this->nullableString($row['username'] ?? null) ?? $email,
            'username' => $this->nullableString($row['username'] ?? null),
            'email' => $email,
            'email_verified_at' => $this->normalizeDateTime($row['email_verified_at'] ?? null),
            'password' => $this->nullableString($row['password'] ?? null),
            'type' => $this->nullableString($row['type'] ?? null, 'guest'),
            'remember_token' => $this->nullableString($row['remember_token'] ?? null),
            'status' => $this->mapCustomerStatus($row['status'] ?? null),
            'created_at' => $this->normalizeDateTime($row['created_at'] ?? null) ?? now(),
            'updated_at' => $this->normalizeDateTime($row['updated_at'] ?? null) ?? now(),
        ];
    }

    private function mapCustomerStatus(mixed $status): int
    {
        return match (strtolower(trim((string) $status))) {
            'verified', 'active', 'activated', '1' => 1,
            'unverified', 'inactive', 'deactivated', '0', '2' => 2,
            default => 2,
        };
    }

    private function resolveDnsServerId(int $sourceServerId): int
    {
        if ($sourceServerId > 0 && DB::table('dns_manager_servers')->where('id', $sourceServerId)->exists()) {
            return $sourceServerId;
        }

        if ($this->canonicalServerId !== null) {
            return $this->canonicalServerId;
        }

        $server = DB::table('dns_manager_servers')
            ->select('id')
            ->orderByRaw('CASE WHEN id = 1 THEN 0 ELSE 1 END')
            ->orderBy('id')
            ->first();

        if (! $server) {
            throw new RuntimeException('No dns_manager_servers available to map imported domains.');
        }

        return $this->canonicalServerId = (int) $server->id;
    }

    private function parseSqlDump(string $path, array $tables): array
    {
        $result = [];
        foreach ($tables as $table) {
            $result[$table] = [];
        }

        $handle = fopen($path, 'rb');
        if (! $handle) {
            throw new RuntimeException("Unable to open SQL file: {$path}");
        }

        $activeTable = null;
        $activeColumns = [];
        $buffer = '';
        $state = ['in_string' => false, 'escaped' => false];

        while (($line = fgets($handle)) !== false) {
            if ($activeTable === null) {
                $trimmed = trim($line);
                if (
                    preg_match('/^INSERT INTO `([^`]+)` \((.+)\) VALUES$/', $trimmed, $matches)
                    && in_array($matches[1], $tables, true)
                ) {
                    $activeTable = $matches[1];
                    $activeColumns = $this->parseColumns($matches[2]);
                    $buffer = '';
                    $state = ['in_string' => false, 'escaped' => false];
                }

                continue;
            }

            $buffer .= $line;

            if (! $this->lineEndsStatement($line, $state)) {
                continue;
            }

            $valueBlock = preg_replace('/;\s*$/', '', trim($buffer));
            $result[$activeTable] = array_merge(
                $result[$activeTable],
                $this->parseValueBlock($valueBlock, $activeColumns)
            );

            $activeTable = null;
            $activeColumns = [];
            $buffer = '';
            $state = ['in_string' => false, 'escaped' => false];
        }

        fclose($handle);

        return $result;
    }

    private function parseColumns(string $columnSegment): array
    {
        preg_match_all('/`([^`]+)`/', $columnSegment, $matches);
        return $matches[1] ?? [];
    }

    private function lineEndsStatement(string $line, array &$state): bool
    {
        $length = strlen($line);

        for ($i = 0; $i < $length; $i++) {
            $char = $line[$i];

            if ($state['in_string']) {
                if ($state['escaped']) {
                    $state['escaped'] = false;
                    continue;
                }

                if ($char === '\\') {
                    $state['escaped'] = true;
                    continue;
                }

                if ($char === "'") {
                    $state['in_string'] = false;
                }

                continue;
            }

            if ($char === "'") {
                $state['in_string'] = true;
                continue;
            }

            if ($char === ';') {
                return true;
            }
        }

        return false;
    }

    private function parseValueBlock(string $valueBlock, array $columns): array
    {
        $rows = [];
        $fields = [];
        $field = '';
        $depth = 0;
        $inString = false;
        $escaped = false;
        $length = strlen($valueBlock);

        for ($i = 0; $i < $length; $i++) {
            $char = $valueBlock[$i];

            if ($inString) {
                $field .= $char;

                if ($escaped) {
                    $escaped = false;
                    continue;
                }

                if ($char === '\\') {
                    $escaped = true;
                    continue;
                }

                if ($char === "'") {
                    $inString = false;
                }

                continue;
            }

            if ($char === "'") {
                $inString = true;
                $field .= $char;
                continue;
            }

            if ($char === '(') {
                if ($depth > 0) {
                    $field .= $char;
                }
                $depth++;
                continue;
            }

            if ($char === ')') {
                if ($depth > 1) {
                    $field .= $char;
                    $depth--;
                    continue;
                }

                $fields[] = $this->normalizeSqlValue($field);
                $rows[] = array_combine($columns, $fields);
                $fields = [];
                $field = '';
                $depth = 0;
                continue;
            }

            if ($char === ',' && $depth === 1) {
                $fields[] = $this->normalizeSqlValue($field);
                $field = '';
                continue;
            }

            if ($depth >= 1) {
                $field .= $char;
            }
        }

        return $rows;
    }

    private function normalizeSqlValue(string $value): mixed
    {
        $value = trim($value);

        if (strcasecmp($value, 'NULL') === 0) {
            return null;
        }

        if ($value === '') {
            return '';
        }

        if ($value[0] === "'" && substr($value, -1) === "'") {
            return stripcslashes(substr($value, 1, -1));
        }

        if (is_numeric($value)) {
            return str_contains($value, '.') ? (float) $value : (int) $value;
        }

        return $value;
    }

    private function normalizeDateTime(mixed $value): ?string
    {
        $value = $this->nullableString($value);
        if (! $value || $value === '0000-00-00 00:00:00') {
            return null;
        }

        return $value;
    }

    private function nullableString(mixed $value, ?string $default = null): ?string
    {
        if ($value === null) {
            return $default;
        }

        $value = trim((string) $value);

        if ($value === '') {
            return $default;
        }

        return $value;
    }

    private function nullableInt(mixed $value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }

        return (int) $value;
    }
}