<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use InvalidArgumentException;
use PDO;
use Throwable;

final class StudioEntityService
{
    private const FIELD_TYPES = [
        'string', 'text', 'integer', 'bigint', 'decimal', 'boolean',
        'date', 'time', 'datetime', 'email', 'phone', 'url',
        'enum', 'json', 'address', 'gps', 'image', 'pdf', 'file', 'signature'
    ];

    /** @return array<string,mixed>|null */
    public function module(int $moduleId): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, code, name, version, author, description, icon, category, status, definition_json, created_at, updated_at
             FROM axiom_studio_modules WHERE id = :id AND archived_at IS NULL LIMIT 1"
        );
        $stmt->execute(['id' => $moduleId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /** @return list<array<string,mixed>> */
    public function entities(int $moduleId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, module_id, code, name, table_name, description, position, created_at, updated_at
             FROM axiom_studio_entities
             WHERE module_id = :module_id AND archived_at IS NULL
             ORDER BY position ASC, name ASC"
        );
        $stmt->execute(['module_id' => $moduleId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @return array<string,mixed>|null */
    public function entity(int $entityId): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, module_id, code, name, table_name, description, position, created_at, updated_at
             FROM axiom_studio_entities WHERE id = :id AND archived_at IS NULL LIMIT 1"
        );
        $stmt->execute(['id' => $entityId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /** @return list<array<string,mixed>> */
    public function fields(int $entityId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT id, entity_id, code, label, field_type, length_value, precision_value, scale_value,
                    is_required, is_unique, is_indexed, default_value, help_text, options_json, position
             FROM axiom_studio_fields
             WHERE entity_id = :entity_id AND archived_at IS NULL
             ORDER BY position ASC, id ASC"
        );
        $stmt->execute(['entity_id' => $entityId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @return list<array<string,mixed>> */
    public function relations(int $moduleId): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT r.id, r.module_id, r.source_entity_id, r.target_entity_id, r.relation_type,
                    r.source_field, r.target_field, r.on_delete, r.label,
                    se.name AS source_name, te.name AS target_name
             FROM axiom_studio_relations r
             INNER JOIN axiom_studio_entities se ON se.id = r.source_entity_id
             INNER JOIN axiom_studio_entities te ON te.id = r.target_entity_id
             WHERE r.module_id = :module_id AND r.archived_at IS NULL
             ORDER BY r.id ASC"
        );
        $stmt->execute(['module_id' => $moduleId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /** @param array<string,mixed> $input */
    public function createEntity(int $moduleId, array $input, ?int $userId): int
    {
        if (!$this->module($moduleId)) {
            throw new InvalidArgumentException('Le module AXIOM demandé est introuvable.');
        }
        $name = trim((string)($input['name'] ?? ''));
        if ($name === '') {
            throw new InvalidArgumentException('Le nom de l’entité est obligatoire.');
        }
        $code = $this->normalizeIdentifier((string)($input['code'] ?? $name), 'Le code de l’entité');
        $tableName = $this->normalizeIdentifier((string)($input['table_name'] ?? $code), 'Le nom de table');
        $description = trim((string)($input['description'] ?? ''));

        $pdo = Database::connection();
        $pdo->beginTransaction();
        try {
            $positionStmt = $pdo->prepare("SELECT COALESCE(MAX(position), 0) + 10 FROM axiom_studio_entities WHERE module_id = :module_id");
            $positionStmt->execute(['module_id' => $moduleId]);
            $position = (int)$positionStmt->fetchColumn();

            $stmt = $pdo->prepare(
                "INSERT INTO axiom_studio_entities
                 (module_id, code, name, table_name, description, position, created_by, updated_by)
                 VALUES (:module_id, :code, :name, :table_name, :description, :position, :created_by, :updated_by)"
            );
            $stmt->execute([
                'module_id' => $moduleId,
                'code' => $code,
                'name' => $name,
                'table_name' => $tableName,
                'description' => $description,
                'position' => $position,
                'created_by' => $userId,
                'updated_by' => $userId,
            ]);
            $entityId = (int)$pdo->lastInsertId();
            $this->recordHistory('entity', $entityId, 'created', null, ['code' => $code, 'name' => $name], $userId);
            $this->synchronizeModuleDefinition($moduleId);
            $pdo->commit();
            return $entityId;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    /** @param array<string,mixed> $input */
    public function createField(int $entityId, array $input, ?int $userId): int
    {
        $entity = $this->entity($entityId);
        if (!$entity) {
            throw new InvalidArgumentException('L’entité demandée est introuvable.');
        }
        $label = trim((string)($input['label'] ?? ''));
        if ($label === '') {
            throw new InvalidArgumentException('Le libellé du champ est obligatoire.');
        }
        $code = $this->normalizeIdentifier((string)($input['code'] ?? $label), 'Le code du champ');
        $type = strtolower(trim((string)($input['field_type'] ?? 'string')));
        if (!in_array($type, self::FIELD_TYPES, true)) {
            throw new InvalidArgumentException('Le type de champ sélectionné n’est pas pris en charge.');
        }
        $length = $this->nullablePositiveInt($input['length_value'] ?? null, 65535);
        $precision = $this->nullablePositiveInt($input['precision_value'] ?? null, 65);
        $scale = $this->nullablePositiveInt($input['scale_value'] ?? null, 30);
        if ($precision !== null && $scale !== null && $scale > $precision) {
            throw new InvalidArgumentException('L’échelle décimale ne peut pas dépasser la précision.');
        }
        $options = $this->normalizeOptions((string)($input['options'] ?? ''));

        $pdo = Database::connection();
        $pdo->beginTransaction();
        try {
            $positionStmt = $pdo->prepare("SELECT COALESCE(MAX(position), 0) + 10 FROM axiom_studio_fields WHERE entity_id = :entity_id");
            $positionStmt->execute(['entity_id' => $entityId]);
            $position = (int)$positionStmt->fetchColumn();
            $stmt = $pdo->prepare(
                "INSERT INTO axiom_studio_fields
                 (entity_id, code, label, field_type, length_value, precision_value, scale_value,
                  is_required, is_unique, is_indexed, default_value, help_text, options_json, position, created_by, updated_by)
                 VALUES
                 (:entity_id, :code, :label, :field_type, :length_value, :precision_value, :scale_value,
                  :is_required, :is_unique, :is_indexed, :default_value, :help_text, :options_json, :position, :created_by, :updated_by)"
            );
            $payload = [
                'entity_id' => $entityId,
                'code' => $code,
                'label' => $label,
                'field_type' => $type,
                'length_value' => $length,
                'precision_value' => $precision,
                'scale_value' => $scale,
                'is_required' => !empty($input['is_required']) ? 1 : 0,
                'is_unique' => !empty($input['is_unique']) ? 1 : 0,
                'is_indexed' => !empty($input['is_indexed']) ? 1 : 0,
                'default_value' => trim((string)($input['default_value'] ?? '')) ?: null,
                'help_text' => trim((string)($input['help_text'] ?? '')) ?: null,
                'options_json' => $options,
                'position' => $position,
                'created_by' => $userId,
                'updated_by' => $userId,
            ];
            $stmt->execute($payload);
            $fieldId = (int)$pdo->lastInsertId();
            $this->recordHistory('field', $fieldId, 'created', null, $payload, $userId);
            $this->synchronizeModuleDefinition((int)$entity['module_id']);
            $pdo->commit();
            return $fieldId;
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    /** @param array<string,mixed> $input */
    public function createRelation(int $moduleId, array $input, ?int $userId): int
    {
        $sourceId = (int)($input['source_entity_id'] ?? 0);
        $targetId = (int)($input['target_entity_id'] ?? 0);
        $source = $this->entity($sourceId);
        $target = $this->entity($targetId);
        if (!$source || !$target || (int)$source['module_id'] !== $moduleId || (int)$target['module_id'] !== $moduleId) {
            throw new InvalidArgumentException('Les deux entités doivent appartenir au module sélectionné.');
        }
        $type = (string)($input['relation_type'] ?? 'many_to_one');
        if (!in_array($type, ['one_to_one', 'one_to_many', 'many_to_one', 'many_to_many'], true)) {
            throw new InvalidArgumentException('Le type de relation est invalide.');
        }
        $sourceField = $this->normalizeIdentifier((string)($input['source_field'] ?? ($target['code'] . '_id')), 'Le champ source');
        $targetField = $this->normalizeIdentifier((string)($input['target_field'] ?? 'id'), 'Le champ cible');
        $onDelete = strtoupper((string)($input['on_delete'] ?? 'RESTRICT'));
        if (!in_array($onDelete, ['RESTRICT', 'CASCADE', 'SET NULL', 'NO ACTION'], true)) {
            $onDelete = 'RESTRICT';
        }
        $stmt = Database::connection()->prepare(
            "INSERT INTO axiom_studio_relations
             (module_id, source_entity_id, target_entity_id, relation_type, source_field, target_field, on_delete, label, created_by, updated_by)
             VALUES (:module_id, :source_entity_id, :target_entity_id, :relation_type, :source_field, :target_field, :on_delete, :label, :created_by, :updated_by)"
        );
        $payload = [
            'module_id' => $moduleId,
            'source_entity_id' => $sourceId,
            'target_entity_id' => $targetId,
            'relation_type' => $type,
            'source_field' => $sourceField,
            'target_field' => $targetField,
            'on_delete' => $onDelete,
            'label' => trim((string)($input['label'] ?? '')) ?: null,
            'created_by' => $userId,
            'updated_by' => $userId,
        ];
        $stmt->execute($payload);
        $id = (int)Database::connection()->lastInsertId();
        $this->recordHistory('relation', $id, 'created', null, $payload, $userId);
        $this->synchronizeModuleDefinition($moduleId);
        return $id;
    }

    public function archive(string $type, int $id, ?int $userId): void
    {
        $map = [
            'entity' => ['table' => 'axiom_studio_entities', 'module' => true],
            'field' => ['table' => 'axiom_studio_fields', 'module' => false],
            'relation' => ['table' => 'axiom_studio_relations', 'module' => true],
        ];
        if (!isset($map[$type])) {
            throw new InvalidArgumentException('Type d’objet Studio invalide.');
        }
        $table = $map[$type]['table'];
        $moduleId = 0;
        if ($type === 'field') {
            $stmt = Database::connection()->prepare("SELECT e.module_id FROM axiom_studio_fields f INNER JOIN axiom_studio_entities e ON e.id=f.entity_id WHERE f.id=:id");
        } else {
            $stmt = Database::connection()->prepare("SELECT module_id FROM {$table} WHERE id=:id");
        }
        $stmt->execute(['id' => $id]);
        $moduleId = (int)$stmt->fetchColumn();
        $update = Database::connection()->prepare("UPDATE {$table} SET archived_at = CURRENT_TIMESTAMP, updated_by = :user_id WHERE id = :id");
        $update->execute(['user_id' => $userId, 'id' => $id]);
        $this->recordHistory($type, $id, 'archived', null, ['archived' => true], $userId);
        if ($moduleId > 0) {
            $this->synchronizeModuleDefinition($moduleId);
        }
    }

    /** @return array{sql:string,json:string,form:list<array<string,mixed>>} */
    public function preview(int $moduleId): array
    {
        $module = $this->module($moduleId);
        if (!$module) {
            throw new InvalidArgumentException('Module introuvable.');
        }
        $entities = $this->entities($moduleId);
        $relations = $this->relations($moduleId);
        $definition = json_decode((string)$module['definition_json'], true);
        if (!is_array($definition)) {
            $definition = [];
        }
        $sql = [];
        $form = [];
        foreach ($entities as $entity) {
            $fields = $this->fields((int)$entity['id']);
            $columns = ["  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT"];
            $indexes = [];
            foreach ($fields as $field) {
                $columns[] = '  `' . $field['code'] . '` ' . $this->sqlType($field)
                    . ((int)$field['is_required'] === 1 ? ' NOT NULL' : ' NULL')
                    . ($field['default_value'] !== null ? ' DEFAULT ' . $this->sqlDefault((string)$field['default_value'], (string)$field['field_type']) : '');
                if ((int)$field['is_unique'] === 1) {
                    $indexes[] = '  UNIQUE KEY `uq_' . $entity['table_name'] . '_' . $field['code'] . '` (`' . $field['code'] . '`)';
                } elseif ((int)$field['is_indexed'] === 1) {
                    $indexes[] = '  KEY `idx_' . $entity['table_name'] . '_' . $field['code'] . '` (`' . $field['code'] . '`)';
                }
            }
            $columns[] = "  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP";
            $columns[] = "  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
            $columns[] = "  PRIMARY KEY (`id`)";
            $all = array_merge($columns, $indexes);
            $sql[] = "CREATE TABLE `{$entity['table_name']}` (\n" . implode(",\n", $all) . "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
            $form[] = ['entity' => $entity, 'fields' => $fields];
        }
        foreach ($relations as $relation) {
            if ($relation['relation_type'] === 'many_to_many') {
                $sql[] = '-- Relation N:N ' . $relation['source_name'] . ' ↔ ' . $relation['target_name'] . ' : table de jointure générée lors du packaging.';
            } else {
                $sql[] = '-- Relation ' . $relation['relation_type'] . ' : ' . $relation['source_name'] . '.' . $relation['source_field'] . ' → ' . $relation['target_name'] . '.' . $relation['target_field'];
            }
        }
        return [
            'sql' => implode("\n\n", $sql) ?: '-- Ajoutez une entité et des champs pour générer l’aperçu SQL.',
            'json' => json_encode($definition, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) ?: '{}',
            'form' => $form,
        ];
    }

    /** @return list<string> */
    public function fieldTypes(): array
    {
        return self::FIELD_TYPES;
    }

    private function synchronizeModuleDefinition(int $moduleId): void
    {
        $module = $this->module($moduleId);
        if (!$module) {
            return;
        }
        $definition = json_decode((string)$module['definition_json'], true);
        if (!is_array($definition)) {
            $definition = [];
        }
        $definition['schema'] = 'axiom.module/1.1';
        $definition['entities'] = [];
        foreach ($this->entities($moduleId) as $entity) {
            $entityDefinition = [
                'code' => $entity['code'],
                'name' => $entity['name'],
                'table' => $entity['table_name'],
                'description' => $entity['description'],
                'fields' => [],
            ];
            foreach ($this->fields((int)$entity['id']) as $field) {
                $entityDefinition['fields'][] = [
                    'code' => $field['code'],
                    'label' => $field['label'],
                    'type' => $field['field_type'],
                    'length' => $field['length_value'] !== null ? (int)$field['length_value'] : null,
                    'precision' => $field['precision_value'] !== null ? (int)$field['precision_value'] : null,
                    'scale' => $field['scale_value'] !== null ? (int)$field['scale_value'] : null,
                    'required' => (bool)$field['is_required'],
                    'unique' => (bool)$field['is_unique'],
                    'indexed' => (bool)$field['is_indexed'],
                    'default' => $field['default_value'],
                    'help' => $field['help_text'],
                    'options' => $field['options_json'] ? json_decode((string)$field['options_json'], true) : null,
                ];
            }
            $definition['entities'][] = $entityDefinition;
        }
        $definition['relations'] = array_map(static function (array $relation): array {
            return [
                'source' => $relation['source_name'],
                'target' => $relation['target_name'],
                'type' => $relation['relation_type'],
                'source_field' => $relation['source_field'],
                'target_field' => $relation['target_field'],
                'on_delete' => $relation['on_delete'],
            ];
        }, $this->relations($moduleId));
        $json = json_encode($definition, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
        $stmt = Database::connection()->prepare("UPDATE axiom_studio_modules SET definition_json=:json, updated_at=CURRENT_TIMESTAMP WHERE id=:id");
        $stmt->execute(['json' => $json ?: '{}', 'id' => $moduleId]);
    }

    /** @param array<string,mixed>|null $before @param array<string,mixed>|null $after */
    private function recordHistory(string $objectType, int $objectId, string $action, ?array $before, ?array $after, ?int $userId): void
    {
        $stmt = Database::connection()->prepare(
            "INSERT INTO axiom_studio_history (object_type, object_id, action_name, before_json, after_json, user_id)
             VALUES (:object_type, :object_id, :action_name, :before_json, :after_json, :user_id)"
        );
        $stmt->execute([
            'object_type' => $objectType,
            'object_id' => $objectId,
            'action_name' => $action,
            'before_json' => $before ? json_encode($before, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
            'after_json' => $after ? json_encode($after, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
            'user_id' => $userId,
        ]);
    }

    private function normalizeIdentifier(string $value, string $label): string
    {
        $value = trim($value);
        if (function_exists('iconv')) {
            $converted = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
            if ($converted !== false) {
                $value = $converted;
            }
        }
        $value = strtolower($value);
        $value = preg_replace('/[^a-z0-9_]+/', '_', $value) ?? '';
        $value = trim($value, '_');
        if ($value === '' || !preg_match('/^[a-z][a-z0-9_]{1,62}$/', $value)) {
            throw new InvalidArgumentException($label . ' doit commencer par une lettre et contenir 2 à 63 caractères (lettres, chiffres, underscore).');
        }
        $reserved = ['select','from','where','table','group','order','index','key','primary','constraint','user','system','database','schema','insert','update','delete','create','alter','drop'];
        if (in_array($value, $reserved, true)) {
            throw new InvalidArgumentException($label . ' utilise un mot réservé SQL.');
        }
        return $value;
    }

    private function nullablePositiveInt(mixed $value, int $max): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }
        $int = filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1, 'max_range' => $max]]);
        if ($int === false) {
            throw new InvalidArgumentException('Une valeur numérique est hors limites.');
        }
        return (int)$int;
    }

    private function normalizeOptions(string $raw): ?string
    {
        $values = array_values(array_filter(array_map('trim', preg_split('/[\r\n,;]+/', $raw) ?: []), static fn(string $v): bool => $v !== ''));
        return $values ? json_encode($values, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null;
    }

    /** @param array<string,mixed> $field */
    private function sqlType(array $field): string
    {
        return match ((string)$field['field_type']) {
            'string', 'email', 'phone', 'url', 'address', 'image', 'pdf', 'file', 'signature' => 'VARCHAR(' . ((int)($field['length_value'] ?: 255)) . ')',
            'text' => 'TEXT',
            'integer' => 'INT',
            'bigint' => 'BIGINT',
            'decimal' => 'DECIMAL(' . ((int)($field['precision_value'] ?: 12)) . ',' . ((int)($field['scale_value'] ?: 2)) . ')',
            'boolean' => 'TINYINT(1)',
            'date' => 'DATE',
            'time' => 'TIME',
            'datetime' => 'DATETIME',
            'json', 'gps' => 'JSON',
            'enum' => 'VARCHAR(190)',
            default => 'VARCHAR(255)',
        };
    }

    private function sqlDefault(string $value, string $type): string
    {
        if (strtoupper($value) === 'CURRENT_TIMESTAMP' && $type === 'datetime') {
            return 'CURRENT_TIMESTAMP';
        }
        if (in_array($type, ['integer','bigint','decimal','boolean'], true) && is_numeric($value)) {
            return $value;
        }
        return "'" . str_replace("'", "''", $value) . "'";
    }
}
