<?php

declare(strict_types=1);

namespace PpPhp\Core\Projects;

use PpPhp\Core\DatabaseConnection;
use PpPhp\Core\Database\Dialect\DatabaseDialectRegistry;
use PpPhp\Core\Database\Schema\TableDesignerService;

/** Captures and applies a data-free, dialect-neutral schema blueprint. */
final class ProjectSchemaBlueprintService
{
    public function capture(string $projectDir, array $connection): array
    {
        $statePath = rtrim($projectDir, '/') . '/config/tables.json';
        $state = is_file($statePath) ? json_decode((string)file_get_contents($statePath), true) : [];
        $selected = array_values(array_unique(array_filter(array_map('strval', (array)($state['selectedTables'] ?? [])))));
        if ($selected === []) return ['version' => 1, 'source_type' => DatabaseConnection::normalize($connection)['type'], 'tables' => [], 'relations' => []];
        $config = DatabaseConnection::normalize($connection); $pdo = DatabaseConnection::connect($config, $projectDir);
        $designer = new TableDesignerService($pdo, DatabaseDialectRegistry::forConfig($config), $config);
        $objects = []; foreach ($designer->listObjects() as $object) $objects[(string)$object['name']] = strtoupper((string)$object['type']);
        $tables = [];
        foreach ($selected as $name) {
            if (($objects[$name] ?? 'TABLE') !== 'TABLE') continue;
            try {
                $details = $designer->describe($name); $fields = [];
                foreach ((array)($details['columns'] ?? []) as $column) $fields[] = self::portableField($column);
                if ($fields !== []) $tables[] = ['name' => $name, 'fields' => $fields];
            } catch (\Throwable) {}
        }
        $relations = array_values(array_filter((array)($state['relations'] ?? []), static fn(array $r): bool => isset($objects[(string)($r['master_table'] ?? '')], $objects[(string)($r['detail_table'] ?? '')])));
        return ['version' => 1, 'source_type' => $config['type'], 'tables' => $tables, 'relations' => $relations];
    }

    public function apply(string $projectDir, array $connection, array $blueprint): array
    {
        $config = DatabaseConnection::normalize($connection); $pdo = DatabaseConnection::connect($config, $projectDir);
        $dialect = DatabaseDialectRegistry::forConfig($config); $designer = new TableDesignerService($pdo, $dialect, $config);
        $existing = []; foreach ($designer->listObjects() as $object) $existing[strtolower((string)$object['name'])] = true;
        $created = []; $skipped = [];
        try {
            foreach ((array)($blueprint['tables'] ?? []) as $table) {
                $name = (string)($table['name'] ?? ''); if ($name === '') continue;
                if (isset($existing[strtolower($name)])) { $skipped[] = $name; continue; }
                $fields = array_map(fn(array $field): array => self::forDialect($field, $dialect->uiConfig()), (array)($table['fields'] ?? []));
                $designer->create($name, $fields); $created[] = $name; $existing[strtolower($name)] = true;
            }
        } catch (\Throwable $e) {
            foreach (array_reverse($created) as $createdTable) { try { $designer->drop($createdTable, 'TABLE'); } catch (\Throwable) {} }
            throw new \RuntimeException('Schema application failed and newly-created tables were rolled back: ' . $e->getMessage(), 0, $e);
        }
        $relations = 0;
        foreach ((array)($blueprint['relations'] ?? []) as $relation) {
            if (!isset($existing[strtolower((string)($relation['master_table'] ?? ''))], $existing[strtolower((string)($relation['detail_table'] ?? ''))])) continue;
            try { $designer->addForeignKey($relation); $relations++; } catch (\Throwable) {}
        }
        return ['created' => $created, 'skipped' => $skipped, 'relations' => $relations];
    }

    public static function portableField(array $column): array
    {
        return [
            'name' => (string)($column['COLUMN_NAME'] ?? $column['name'] ?? ''),
            'type' => strtoupper((string)($column['type'] ?? $column['DATA_TYPE'] ?? 'VARCHAR')),
            'size' => (int)($column['size'] ?? $column['CHARACTER_MAXIMUM_LENGTH'] ?? $column['NUMERIC_PRECISION'] ?? 0),
            'scale' => (int)($column['scale'] ?? $column['NUMERIC_SCALE'] ?? 0), 'nullable' => !empty($column['nullable']),
            'default_mode' => (string)($column['default_mode'] ?? 'NONE'), 'default' => $column['default'] ?? null,
            'default_expression' => $column['default_expression'] ?? null, 'primary_key' => !empty($column['primary_key']),
            'auto_increment' => !empty($column['auto_increment']), 'enum_values' => (string)($column['enum_values'] ?? ''),
        ];
    }

    public static function forDialect(array $field, array $ui): array
    {
        $allowed = array_map('strtoupper', (array)($ui['field_types'] ?? [])); $type = strtoupper((string)($field['type'] ?? 'TEXT'));
        if (!in_array($type, $allowed, true)) {
            $type = match (true) {
                (bool)preg_match('/INT|SERIAL/', $type) => in_array('INTEGER', $allowed, true) ? 'INTEGER' : 'INT',
                (bool)preg_match('/BOOL|BIT/', $type) => in_array('BOOLEAN', $allowed, true) ? 'BOOLEAN' : 'TINYINT',
                (bool)preg_match('/DECIMAL|NUMERIC|MONEY|NUMBER/', $type) => in_array('DECIMAL', $allowed, true) ? 'DECIMAL' : 'NUMERIC',
                (bool)preg_match('/REAL|FLOAT|DOUBLE/', $type) => in_array('REAL', $allowed, true) ? 'REAL' : 'FLOAT',
                (bool)preg_match('/DATE|TIME/', $type) => in_array('DATETIME', $allowed, true) ? 'DATETIME' : (in_array('TIMESTAMP', $allowed, true) ? 'TIMESTAMP' : 'TEXT'),
                (bool)preg_match('/BLOB|BINARY|BYTEA|IMAGE/', $type) => in_array('BLOB', $allowed, true) ? 'BLOB' : (in_array('BYTEA', $allowed, true) ? 'BYTEA' : 'TEXT'),
                (bool)preg_match('/JSON/', $type) && in_array('JSON', $allowed, true) => 'JSON',
                default => in_array('VARCHAR', $allowed, true) ? 'VARCHAR' : 'TEXT',
            };
        }
        $field['type'] = $type;
        // A primary key is NOT NULL by definition, but only an AUTO-INCREMENT key
        // must be an integer — a VARCHAR/UUID primary key is valid on every engine
        // and must survive the translation (forcing it to INT corrupts the data).
        if (!empty($field['primary_key'])) {
            $field['nullable'] = false;
            if (!empty($field['auto_increment']) && !preg_match('/INT/', $type)) {
                $field['type'] = in_array('INTEGER', $allowed, true) ? 'INTEGER' : 'INT';
            }
        }
        return $field;
    }
}
