612 lines
19 KiB
PHP
612 lines
19 KiB
PHP
<?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;
|
|
}
|
|
}
|