<?php

declare(strict_types=1);

namespace PpPhp\Core\Database\Schema;

use PpPhp\Core\Database\Dialect\DatabaseDialectInterface;

final class FieldDefinitionNormalizer
{
    public function normalize(array $rawFields, DatabaseDialectInterface $dialect): array
    {
        $ui = $dialect->uiConfig();
        $allowed = array_map('strtoupper', (array)($ui['field_types'] ?? []));
        $integerTypes = array_map('strtoupper', (array)($ui['integer_types'] ?? []));
        $sizedTypes = array_map('strtoupper', (array)($ui['sized_types'] ?? []));
        $enumTypes = array_map('strtoupper', (array)($ui['enum_types'] ?? []));
        $temporalTypes = array_map('strtoupper', (array)($ui['temporal_types'] ?? []));
        $normalized = [];
        $names = [];
        $autoCount = 0;

        foreach ($rawFields as $index => $raw) {
            if (!is_array($raw)) {
                continue;
            }
            $name = trim((string)($raw['name'] ?? $raw['COLUMN_NAME'] ?? ''));
            if ($name === '') {
                continue;
            }
            $this->assertIdentifier($name, 'Field');
            $key = strtolower($name);
            if (isset($names[$key])) {
                throw new \InvalidArgumentException("Duplicate field '{$name}' is not allowed.");
            }
            $names[$key] = true;

            $originalName = trim((string)($raw['original_name'] ?? $raw['ORIGINAL_NAME'] ?? $name));
            if ($originalName !== '') {
                $this->assertIdentifier($originalName, 'Original field');
            }

            $type = strtoupper(trim((string)($raw['type'] ?? $raw['DATA_TYPE'] ?? 'VARCHAR')));
            if (!in_array($type, $allowed, true)) {
                throw new \InvalidArgumentException("Field '{$name}' uses {$type}, which is not supported by {$dialect->label()}.");
            }

            $primary = !empty($raw['primary_key']) || (($raw['COLUMN_KEY'] ?? '') === 'PRI');
            $auto = !empty($raw['auto_increment']) || (($raw['EXTRA'] ?? '') === 'auto_increment');
            if ($auto) {
                $autoCount++;
                if ($autoCount > 1) {
                    throw new \InvalidArgumentException('Only one auto-generated identity field is allowed per table.');
                }
                $primary = true;
            }
            if ($primary) {
                $identityType = in_array('INT', $allowed, true) ? 'INT' : (in_array('INTEGER', $allowed, true) ? 'INTEGER' : ($integerTypes[0] ?? null));
                if ($identityType === null) {
                    throw new \InvalidArgumentException("{$dialect->label()} does not expose an integer type for primary keys.");
                }
                $type = $identityType;
            }

            $nullable = array_key_exists('nullable', $raw)
                ? (bool)$raw['nullable']
                : strtoupper((string)($raw['IS_NULLABLE'] ?? 'YES')) === 'YES';
            if ($primary) {
                $nullable = false;
            }

            $size = max(0, (int)($raw['size'] ?? $raw['CHARACTER_MAXIMUM_LENGTH'] ?? $raw['NUMERIC_PRECISION'] ?? 0));
            $scaleProvided = array_key_exists('scale', $raw) || array_key_exists('NUMERIC_SCALE', $raw);
            $scale = max(0, (int)($raw['scale'] ?? $raw['NUMERIC_SCALE'] ?? 0));
            if (in_array($type, $sizedTypes, true) && $size === 0) {
                $size = in_array($type, ['DECIMAL', 'NUMERIC'], true) ? 10 : 255;
            }
            if (in_array($type, ['DECIMAL', 'NUMERIC'], true) && !$scaleProvided) {
                $scale = 2;
            }

            $defaultMode = strtoupper(trim((string)($raw['default_mode'] ?? '')));
            $default = $raw['default'] ?? $raw['COLUMN_DEFAULT'] ?? null;
            $defaultExpression = $raw['default_expression'] ?? null;
            if ($defaultMode === '') {
                $defaultMode = $this->inferDefaultMode($default);
            }
            if (!in_array($defaultMode, ['NONE', 'NULL', 'VALUE', 'CURRENT_TIMESTAMP', 'EXPRESSION'], true)) {
                throw new \InvalidArgumentException("Default mode for '{$name}' is not supported.");
            }
            if ($primary || $auto) {
                $defaultMode = 'NONE';
                $default = null;
                $defaultExpression = null;
            }
            if ($defaultMode === 'NULL' && !$nullable) {
                throw new \InvalidArgumentException("Field '{$name}' must be nullable to use DEFAULT NULL.");
            }
            if ($defaultMode === 'CURRENT_TIMESTAMP' && !in_array($type, $temporalTypes, true)) {
                throw new \InvalidArgumentException("Field '{$name}' must use a date/time type for the current-time default.");
            }

            $enumValues = trim((string)($raw['enum_values'] ?? ''));
            if (in_array($type, $enumTypes, true) && $enumValues === '') {
                throw new \InvalidArgumentException("{$type} field '{$name}' requires comma-separated allowed values.");
            }

            $normalized[] = [
                'name' => $name,
                'original_name' => $originalName ?: $name,
                'type' => $type,
                'size' => $size,
                'scale' => $scale,
                'nullable' => $nullable,
                'default_mode' => $defaultMode,
                'default' => $default,
                'default_expression' => $defaultExpression,
                'auto_increment' => $auto,
                'primary_key' => $primary,
                'enum_values' => $enumValues,
                'ordinal' => $index,
            ];
        }

        if ($normalized === []) {
            throw new \InvalidArgumentException('At least one valid field is required.');
        }
        return $normalized;
    }

    public function assertTableName(string $name): string
    {
        $name = trim($name);
        $this->assertIdentifier($name, 'Table name');
        return $name;
    }

    private function assertIdentifier(string $value, string $label): void
    {
        if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $value)) {
            throw new \InvalidArgumentException($label . ' can contain letters, numbers, and underscores only, and must start with a letter or underscore.');
        }
    }

    private function inferDefaultMode(mixed $default): string
    {
        if ($default === null || $default === '') {
            return 'NONE';
        }
        $value = strtoupper(trim((string)$default));
        if ($value === 'NULL') {
            return 'NULL';
        }
        if (preg_match('/^(CURRENT_TIMESTAMP|NOW\(\)|GETDATE\(\)|SYSUTCDATETIME\(\))(\(\d+\))?$/i', $value)) {
            return 'CURRENT_TIMESTAMP';
        }
        if (preg_match('/^NEXTVAL\(/i', $value)) {
            return 'NONE';
        }
        return 'VALUE';
    }
}
