<?php

/**
 * RecordCopyPaste - Export/Import relational database records via JSON
 *
 * Handles copying records with their relations across databases with same structure.
 * Supports identifier-based duplicate detection, ID mapping, and flexible overrides.
 *
 * Export uses JSON template syntax directly in SQL.
 *
 * @author enured
 * @version 1
 */

namespace IMATHUZH\Qfq\Core\Helper;

use Exception;
use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Report\Link;
use IMATHUZH\Qfq\Core\Store\Session;
use IMATHUZH\Qfq\Core\Store\Store;

class CopyPaste {

    private $db;
    private array $idMapping = [];
    private ?Store $store;
    private string|int|array|null|false $dbIndexData;
    private string|int|array|null|false $dbIndexQfq;
    private mixed $dbArray;
    private mixed $content;

    /**
     * @throws \CodeException
     * @throws \UserFormException
     * @throws \DbException
     * @throws \UserReportException
     */
    public function __construct($content, $db = null, $store = null, $phpUnit = false) {
        if (defined('PHPUNIT_QFQ')) {
            $phpUnit = true;
        }

        if ($store === null) {
            $store = Store::getInstance('', $phpUnit);
        }

        $this->content = trim($content);
        $this->store = $store;

        if ($db === null) {
            $this->dbIndexData = $this->store->getVar(SYSTEM_DB_INDEX_DATA, STORE_SYSTEM);
            $this->dbIndexQfq = $this->store->getVar(SYSTEM_DB_INDEX_QFQ, STORE_SYSTEM);

            $this->dbArray[$this->dbIndexData] = new Database($this->dbIndexData);

            if ($this->dbIndexData != $this->dbIndexQfq) {
                $this->dbArray[$this->dbIndexQfq] = new Database($this->dbIndexQfq);
            }

            $db = $this->dbArray[$this->dbIndexData];
        }

        $this->db = $db;
    }

    /**
     * Handle Export: Parse JSON template or rule names, fetch data, generate complete JSON
     *
     * @return string JSON string for clipboard
     * @throws \UserReportException
     * @throws \Exception
     */
    public function handleExport(): string {
        // Auto-detect: JSON template or rule names
        if (str_starts_with($this->content, '{')) {
            // It's a JSON template
            $template = json_decode($this->content, true);

            if (json_last_error() !== JSON_ERROR_NONE) {
                throw new \UserReportException("Invalid JSON template: " . json_last_error_msg());
            }
        } else {
            // It's comma-separated rule names
            $ruleNames = array_map('trim', explode(',', $this->content));
            $template = $this->loadRules($ruleNames);
        }

        if (!isset($template['element']) || !is_array($template['element'])) {
            throw new \Exception("Invalid template: missing element array");
        }

        // Build result with version and timestamp
        $result = [
            'version' => '1.0',
            'exportedAt' => date('c'),
            'element' => []
        ];

        // Process each root element
        foreach ($template['element'] as $elementTemplate) {
            $processedElements = $this->processElementTemplate($elementTemplate, null);
            $result['element'] = array_merge($result['element'], $processedElements);
        }

        return json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
    }

    /**
     * Handle Import: Parse JSON + overrides, check duplicates, insert/update
     *
     * @param string|null $importOverrides JSON format: {"element":[{"table":"Person","data":[{"id":600}]}]}
     *                                      Optional fields: relation, identifier, condition, element
     * @return array Result with status and created/updated IDs
     * @throws Exception
     */
    public function handleImport(?string $importOverrides = null): array {
        $data = json_decode($this->content, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception("Invalid JSON: " . json_last_error_msg());
        }

        if (!isset($data['element'])) {
            throw new Exception("Invalid JSON structure: missing element array");
        }

        // Parse import overrides
        $overrides = [];
        if ($importOverrides) {
            $overrides = $this->parseImportOverrides($importOverrides);
        }

        // Reset ID mapping for this import
        $this->idMapping = [];

        // Transactions are handled by sql() internally - no manual transaction needed
        try {
            $results = [
                'success' => true,
                'imported' => [],
                'idMapping' => []
            ];

            // Process all root elements recursively
            foreach ($data['element'] as $element) {
                $importResult = $this->importElementRecursive($element, $overrides);
                $results['imported'][] = $importResult;
            }

            $results['idMapping'] = $this->idMapping;

            return $results;

        } catch (Exception $e) {
            // sql() handles rollback internally
            throw $e;
        }
    }

    /**
     * Load and merge multiple rule definitions from CopyPasteRule table
     *
     * @param array $ruleNames Array of rule names
     * @return array Merged template with all elements
     * @throws Exception
     */
    private function loadRules(array $ruleNames): array {
        if (empty($ruleNames)) {
            throw new Exception("No rule names provided");
        }

        $mergedTemplate = [
            'element' => []
        ];

        foreach ($ruleNames as $ruleName) {
            if (empty(trim($ruleName))) continue;

            $query = "SELECT definition FROM CopyPasteRule WHERE name = ? LIMIT 1";
            $result = $this->db->sql($query, ROW_EXPECT_GE_1, [$ruleName]);

            if (empty($result)) {
                throw new Exception("Rule not found: {$ruleName}");
            }

            $row = $result[0];

            // Parse rule definition
            $ruleDefinition = $row['definition'];

            $ruleTemplate = json_decode($ruleDefinition, true);

            if (json_last_error() !== JSON_ERROR_NONE) {
                throw new Exception("Invalid JSON in rule '{$ruleName}': " . json_last_error_msg());
            }

            // Merge elements
            if (isset($ruleTemplate['element']) && is_array($ruleTemplate['element'])) {
                $mergedTemplate['element'] = array_merge(
                    $mergedTemplate['element'],
                    $ruleTemplate['element']
                );
            }
        }

        return $mergedTemplate;
    }

    /**
     * Process element template: fetch data and process child elements (BATCH-OPTIMIZED)
     *
     * @param array $template Element template with table, condition, etc.
     * @param array|null $parentData Parent record data for relation mapping
     * @return array Array of processed elements (can be multiple if condition matches multiple records)
     */
    private function processElementTemplate(array $template, ?array $parentData): array {
        $table = $template['table'] ?? null;
        $dataTemplate = $template['data'] ?? null;
        $condition = $template['condition'] ?? null;
        $relation = $template['relation'] ?? null;
        $identifier = $template['identifier'] ?? null;
        $childTemplates = $template['element'] ?? [];

        if (!$table) {
            throw new Exception("Element template missing table");
        }

        // Fetch records for this level
        $records = $this->fetchRecordsForTemplate($table, $dataTemplate, $condition, $relation, $parentData);

        if (empty($records)) {
            return [];
        }

        // Process child elements in BATCHES
        $childElementsByTemplate = [];

        if (!empty($childTemplates)) {
            // Group child templates by (table, relation, condition) for batching
            foreach ($childTemplates as $childIndex => $childTemplate) {
                $batchKey = $this->getBatchKey($childTemplate);

                // Fetch all children for this template in ONE batch query
                $allChildRecords = $this->fetchChildrenBatch($childTemplate, $records);

                // Group children by parent ID
                $childrenByParent = $this->groupChildrenByParent($allChildRecords, $childTemplate['relation'] ?? null);

                $childElementsByTemplate[$childIndex] = $childrenByParent;
            }
        }

        // Build elements with their children
        $elements = [];

        foreach ($records as $record) {
            $element = [
                'table' => $table,
                'data' => $record,
                'relation' => $relation,
                'identifier' => $identifier,
                'condition' => $condition,
                'element' => []
            ];

            // Assign pre-fetched children to this parent
            foreach ($childTemplates as $childIndex => $childTemplate) {
                $childRelation = $childTemplate['relation'] ?? null;

                // Try ALL relation keys - we don't know which one was used for grouping
                $childRecords = [];
                if ($childRelation) {
                    foreach ($childRelation as $childCol => $parentRef) {
                        [$refTable, $refCol] = explode('.', $parentRef, 2);

                        // Try to find children using this parent column
                        if (isset($record[$refCol])) {
                            $lookupKey = $record[$refCol];
                            if (isset($childElementsByTemplate[$childIndex][$lookupKey])) {
                                $childRecords = array_merge($childRecords, $childElementsByTemplate[$childIndex][$lookupKey]);
                            }
                        }
                    }

                    // Remove duplicates (same child might be found via multiple relations)
                    $childRecords = array_values(array_unique($childRecords, SORT_REGULAR));
                }

                if (!empty($childRecords)) {
                    // Recursively process these children's children
                    foreach ($childRecords as $childRecord) {
                        $processedChildren = $this->processChildElementsRecursive(
                            $childTemplate,
                            $childRecord
                        );
                        $element['element'] = array_merge($element['element'], $processedChildren);
                    }
                }
            }

            $elements[] = $element;
        }

        // If multiple records with same template, group data into array
        if (count($elements) > 1 && $this->shouldGroupData($elements)) {
            // Collect all unique child elements from all parents
            $allChildElements = [];
            $seenChildKeys = [];

            foreach ($elements as $element) {
                foreach ($element['element'] as $childElement) {
                    // Create unique key for deduplication (table + data identifier)
                    $childTable = $childElement['table'];
                    $childIdentifier = $childElement['identifier'] ?? null;

                    // Use identifier fields or all data for uniqueness check
                    if ($childIdentifier && $childIdentifier !== '') {
                        if ($childIdentifier === '*') {
                            // Use all non-id fields
                            $keyData = $childElement['data'];
                            unset($keyData['id'], $keyData['created'], $keyData['modified']);
                        } else {
                            // Use specified identifier fields
                            $identifierFields = explode('-', $childIdentifier);
                            $keyData = [];
                            foreach ($identifierFields as $field) {
                                $keyData[$field] = $childElement['data'][$field] ?? null;
                            }
                        }
                    } else {
                        // No identifier - use id or all data
                        $keyData = ['id' => $childElement['data']['id'] ?? json_encode($childElement['data'])];
                    }

                    $uniqueKey = $childTable . ':' . json_encode($keyData);

                    // Only add if not seen before
                    if (!isset($seenChildKeys[$uniqueKey])) {
                        $allChildElements[] = $childElement;
                        $seenChildKeys[$uniqueKey] = true;
                    }
                }
            }

            // Group child elements if they share same table/relation/identifier/condition
            $allChildElements = $this->groupChildElements($allChildElements);

            $grouped = [
                'table' => $table,
                'data' => array_map(fn($e) => $e['data'], $elements),
                'relation' => $relation,
                'identifier' => $identifier,
                'condition' => $condition,
                'element' => $allChildElements
            ];
            return [$grouped];
        }

        // Single parent or ungroupable parents - group children for each
        foreach ($elements as &$element) {
            if (!empty($element['element'])) {
                $element['element'] = $this->groupChildElements($element['element']);
            }
        }

        return $elements;
    }

    /**
     * Fetch records for a template
     */
    private function fetchRecordsForTemplate(string $table, ?array $dataTemplate, ?string $condition, ?array $relation, ?array $parentData): array {
        $whereConditions = [];
        $params = [];

        // Handle relation (FK condition)
        if ($relation && $parentData) {
            foreach ($relation as $childCol => $parentRef) {
                [$parentTable, $parentCol] = explode('.', $parentRef, 2);

                // Determine which parent value to use based on relation type:
                // 1. If childCol is 'id' (reverse relation) → use parentData[parentCol]
                // 2. If parentTable == child table (self-ref) → use parentData[parentCol]
                // 3. If parentData has childCol (cross-table hierarchy) → use parentData[childCol]
                // 4. Otherwise (normal relation) → use parentData[parentCol]

                $parentValue = null;

                if ($childCol === 'id') {
                    // Reverse relation: use parentData[parentCol]
                    $parentValue = $parentData[$parentCol] ?? null;
                }
                elseif ($parentTable === $table) {
                    // Self-referencing: use parentData[parentCol] (usually 'id')
                    $parentValue = $parentData[$parentCol] ?? null;
                }
                elseif (isset($parentData[$childCol]) && $parentData[$childCol] !== null && $parentData[$childCol] !== 0 && $parentData[$childCol] !== '0') {
                    // Cross-table hierarchy: parent has same FK as child
                    // Example: FormElement->FormElement where both have formId
                    $parentValue = $parentData[$childCol];
                }
                else {
                    // Normal relation: use parentData[parentCol]
                    $parentValue = $parentData[$parentCol] ?? null;
                }

                if ($parentValue !== null) {
                    $whereConditions[] = $this->escapeIdentifier($childCol) . " = ?";
                    $params[] = $parentValue;
                }
            }
        }

        // Handle IDs from data template
        if ($dataTemplate) {
            // Check if it's a single record (associative array) or array of records (sequential array)
            if (!isset($dataTemplate[0])) {
                // Single record - wrap it in array
                $dataTemplate = [$dataTemplate];
            }

            if (isset($dataTemplate[0]['id'])) {
                $ids = array_map(fn($d) => $d['id'], $dataTemplate);
                $ids = array_filter($ids); // Remove nulls

                if (!empty($ids)) {
                    $placeholders = implode(',', array_fill(0, count($ids), '?'));
                    $whereConditions[] = $this->escapeIdentifier('id') . " IN ({$placeholders})";
                    foreach ($ids as $id) {
                        $params[] = $id;
                    }
                }
            }
        }

        // Handle condition SQL string
        if ($condition) {
            $whereConditions[] = "({$condition})";
        }

        $query = "SELECT * FROM " . $this->escapeIdentifier($table);
        if (!empty($whereConditions)) {
            $query .= " WHERE " . implode(' AND ', $whereConditions);
        }

        $result = $this->db->sql($query, ROW_REGULAR, $params);

        return $result;
    }

    /**
     * Fetch children for multiple parents in ONE batch query
     */
    private function fetchChildrenBatch(array $childTemplate, array $parentRecords): array {
        if (empty($parentRecords)) {
            return [];
        }

        $table = $childTemplate['table'] ?? null;
        $condition = $childTemplate['condition'] ?? null;
        $relation = $childTemplate['relation'] ?? null;

        if (!$table || !$relation) {
            return [];
        }

        // Collect parent values for each relation
        $whereConditions = [];
        $params = [];

        foreach ($relation as $childCol => $parentRef) {
            [$refTable, $refCol] = explode('.', $parentRef, 2);

            // Collect values from parent records
            // Strategy depends on relation type:
            // 1. Reverse relation: child.id references parent.fkCol (e.g., {"id":"RoleAssign.grId"})
            //    → Use parent[refCol] to find child by its id
            // 2. Self-referencing within same table: (e.g., {"feIdContainer":"FormElement.id"})
            //    → Use parent[refCol] (id) to link parent.id → child.fkCol
            // 3. Cross-table hierarchy: parent & child share FK to common ancestor (e.g., {"formId":"Form.id"})
            //    → Use parent[childCol] to maintain same hierarchy level
            // 4. Normal relation: child.fkCol references parent.id
            //    → Use parent[refCol] (usually 'id')
            $relationIds = [];
            foreach ($parentRecords as $parent) {
                $value = null;

                // If childCol is 'id', it's a reverse relation → use refCol
                if ($childCol === 'id') {
                    if (isset($parent[$refCol]) && $parent[$refCol] !== null && $parent[$refCol] !== 0 && $parent[$refCol] !== '0') {
                        $value = $parent[$refCol];
                    }
                }
                // If refTable == parent table, it's self-referencing within same table → use refCol (id)
                elseif ($refTable === $table) {
                    if (isset($parent[$refCol]) && $parent[$refCol] !== null && $parent[$refCol] !== 0 && $parent[$refCol] !== '0') {
                        $value = $parent[$refCol];
                    }
                }
                // Check if parent has the child's FK column (cross-table hierarchy)
                elseif (isset($parent[$childCol]) && $parent[$childCol] !== null && $parent[$childCol] !== 0 && $parent[$childCol] !== '0') {
                    $value = $parent[$childCol];
                }
                // Fallback: use referenced column (normal relation)
                elseif (isset($parent[$refCol]) && $parent[$refCol] !== null && $parent[$refCol] !== 0 && $parent[$refCol] !== '0') {
                    $value = $parent[$refCol];
                }

                if ($value !== null) {
                    $relationIds[] = $value;
                }
            }

            $relationIds = array_unique($relationIds);

            if (!empty($relationIds)) {
                $placeholders = implode(',', array_fill(0, count($relationIds), '?'));
                $whereConditions[] = $this->escapeIdentifier($childCol) . " IN ({$placeholders})";
                foreach ($relationIds as $id) {
                    $params[] = $id;
                }
            }
        }

        if (empty($whereConditions)) {
            return [];
        }

        // Combine multiple relation conditions with AND (all must match)
        // Multiple relations mean the child must satisfy ALL foreign key constraints
        // Example: FormElement with formId=1126 AND feIdContainer=19483
        $relationCondition = '(' . implode(' AND ', $whereConditions) . ')';

        // Additional condition from template
        $finalConditions = [$relationCondition];
        if ($condition) {
            $finalConditions[] = "({$condition})";
        }

        $query = "SELECT * FROM " . $this->escapeIdentifier($table);
        $query .= " WHERE " . implode(' AND ', $finalConditions);

        $result = $this->db->sql($query, ROW_REGULAR, $params);

        return $result;
    }

    /**
     * Group child records by parent ID
     *
     * For relations with multiple FKs, groups by the FK column that has varying values
     * (i.e., different parents), not just the first FK with non-zero values.
     */
    private function groupChildrenByParent(array $childRecords, ?array $relation): array {
        if (empty($childRecords) || !$relation) {
            return [];
        }

        $grouped = [];

        // Try each FK column and choose the one with most unique values
        $bestColumn = null;
        $maxUniqueValues = 0;

        foreach ($relation as $childCol => $parentRef) {
            [$refTable, $refCol] = explode('.', $parentRef, 2);

            // Count unique non-zero values for this FK column
            $uniqueValues = [];
            foreach ($childRecords as $record) {
                $value = $record[$childCol] ?? null;
                if ($value !== null && $value !== 0 && $value !== '0') {
                    $uniqueValues[$value] = true;
                }
            }

            $uniqueCount = count($uniqueValues);

            // NEU:
            // - Spalte mit mehr unterschiedlichen Werten gewinnt
            // - bei Gleichstand gewinnt die SPÄTER definierte Relation
            if ($uniqueCount > $maxUniqueValues || ($uniqueCount === $maxUniqueValues && $uniqueCount > 0)) {
                $maxUniqueValues = $uniqueCount;
                $bestColumn = $childCol;
            }
        }

        // If no suitable column found, return empty
        if ($bestColumn === null) {
            return [];
        }

        // Group by the best FK column
        foreach ($childRecords as $record) {
            $parentId = $record[$bestColumn] ?? null;
            if ($parentId !== null && $parentId !== 0 && $parentId !== '0') {
                if (!isset($grouped[$parentId])) {
                    $grouped[$parentId] = [];
                }
                $grouped[$parentId][] = $record;
            }
        }

        return $grouped;
    }


    /**
     * Process child elements recursively (for nested children)
     */
    private function processChildElementsRecursive(array $template, array $recordData): array {
        $childTemplates = $template['element'] ?? [];

        $element = [
            'table' => $template['table'],
            'data' => $recordData,
            'relation' => $template['relation'] ?? null,
            'identifier' => $template['identifier'] ?? null,
            'condition' => $template['condition'] ?? null,
            'element' => []
        ];

        // Process nested children (recursive)
        if (!empty($childTemplates)) {
            foreach ($childTemplates as $childTemplate) {
                $processedChildren = $this->processElementTemplate($childTemplate, $recordData);
                $element['element'] = array_merge($element['element'], $processedChildren);
            }

            // NOTE: No grouping here!
            // Grouping happens in processElementTemplate after all siblings are collected
        }

        return [$element];
    }

    /**
     * Get batch key for grouping child templates
     */
    private function getBatchKey(array $template): string {
        return json_encode([
            'table' => $template['table'] ?? null,
            'relation' => $template['relation'] ?? null,
            'condition' => $template['condition'] ?? null
        ]);
    }

    /**
     * Extract parent column name from child relation
     * Relation format: { "childCol": "ParentTable.parentCol" }
     *
     * For multiple relations, returns the first column that exists in the record.
     * If no match, returns 'id' as default.
     *
     * @param array|null $relation Relation mapping
     * @param array|null $record Optional parent record to check which column exists
     * @return string Parent column name
     */
    private function getParentColumnFromRelation(?array $relation, ?array $record = null): string {
        if (!$relation) {
            return 'id';
        }

        // If we have a record, find the first matching parent column that exists
        if ($record !== null) {
            foreach ($relation as $childCol => $parentRef) {
                $parts = explode('.', $parentRef, 2);
                $parentCol = $parts[1] ?? 'id';

                // Check if this column exists in the record
                if (isset($record[$parentCol])) {
                    return $parentCol;
                }
            }
        }

        // No record provided or no match found - return first parent column
        foreach ($relation as $childCol => $parentRef) {
            $parts = explode('.', $parentRef, 2);
            return $parts[1] ?? 'id';
        }

        return 'id';
    }

    /**
     * Determine if multiple elements should be grouped with array data
     */
    private function shouldGroupData(array $elements): bool {
        if (count($elements) <= 1) {
            return false;
        }

        // NEU: wenn irgendein Element schon Kinder hat, NICHT mehr gruppieren
        foreach ($elements as $element) {
            if (!empty($element['element'])) {
                return false;
            }
        }

        // Bisherige Logik
        $first = $elements[0];
        $firstRelation   = json_encode($first['relation']);
        $firstIdentifier = $first['identifier'];
        $firstCondition  = $first['condition'];

        foreach ($elements as $element) {
            if (json_encode($element['relation']) !== $firstRelation ||
                $element['identifier'] !== $firstIdentifier ||
                $element['condition']  !== $firstCondition) {
                return false;
            }
        }

        return true;
    }

    /**
     * Group child elements that have the same table, relation, identifier, and condition
     * into single elements with data arrays
     *
     * @param array $childElements Array of child elements
     * @return array Grouped child elements
     */
    private function groupChildElements(array $childElements): array {
        if (empty($childElements)) {
            return [];
        }

        // Group by table + relation + identifier + condition
        $groups = [];

        foreach ($childElements as $childElement) {
            $groupKey = json_encode([
                'table'      => $childElement['table'],
                'relation'   => $childElement['relation'] ?? null,
                'identifier' => $childElement['identifier'] ?? null,
                'condition'  => $childElement['condition'] ?? null
            ]);

            if (!isset($groups[$groupKey])) {
                $groups[$groupKey] = [
                    'template'      => $childElement,
                    'data'          => [],
                    'childElements' => []
                ];
            }

            // Sammle die Daten – kann ein einzelner Record oder ein Array von Records sein
            $groups[$groupKey]['data'][] = $childElement['data'];

            // Collect grandchildren (nested elements) for deduplication
            if (!empty($childElement['element'])) {
                foreach ($childElement['element'] as $grandchild) {
                    $groups[$groupKey]['childElements'][] = $grandchild;
                }
            }
        }

        // Build result: single elements with data arrays (if multiple) or single data object
        $result = [];

        foreach ($groups as $group) {
            $template      = $group['template'];
            $dataRecords   = $group['data'];
            $grandchildren = $group['childElements'];

            // --- NEU: verschachtelte Daten flach machen ---
            // dataRecords kann z.B. so aussehen:
            // [ [ row1, row2 ], [ row3, row4 ] ]
            // Wir wollen: [ row1, row2, row3, row4 ]
            $flatDataRecords = [];
            foreach ($dataRecords as $dataRecord) {
                // Wenn es ein Array von Records ist (0-basierter Index mit Arrays)
                if (is_array($dataRecord) && isset($dataRecord[0]) && is_array($dataRecord[0])) {
                    foreach ($dataRecord as $nestedRecord) {
                        $flatDataRecords[] = $nestedRecord;
                    }
                } else {
                    // einzelner Record
                    $flatDataRecords[] = $dataRecord;
                }
            }

            // If only one data record, keep as single object
            // If multiple, use array
            $template['data'] = count($flatDataRecords) === 1
                ? $flatDataRecords[0]
                : $flatDataRecords;

            // Deduplicate and group grandchildren
            if (!empty($grandchildren)) {
                // Deduplicate grandchildren based on identifier
                $uniqueGrandchildren = [];
                $seenKeys            = [];

                foreach ($grandchildren as $grandchild) {
                    $childTable      = $grandchild['table'];
                    $childIdentifier = $grandchild['identifier'] ?? null;

                    // Build unique key
                    if ($childIdentifier && $childIdentifier !== '') {
                        if ($childIdentifier === '*') {
                            $keyData = $grandchild['data'];
                            unset($keyData['id'], $keyData['created'], $keyData['modified']);
                        } else {
                            $identifierFields = explode('-', $childIdentifier);
                            $keyData          = [];
                            foreach ($identifierFields as $field) {
                                $keyData[$field] = $grandchild['data'][$field] ?? null;
                            }
                        }
                    } else {
                        $keyData = ['id' => $grandchild['data']['id'] ?? json_encode($grandchild['data'])];
                    }

                    $uniqueKey = $childTable . ':' . json_encode($keyData);

                    if (!isset($seenKeys[$uniqueKey])) {
                        $uniqueGrandchildren[]    = $grandchild;
                        $seenKeys[$uniqueKey] = true;
                    }
                }

                // Recursively group grandchildren
                $template['element'] = $this->groupChildElements($uniqueGrandchildren);
            }

            $result[] = $template;
        }

        return $result;
    }


    /**
     * Parse import overrides from JSON structure
     *
     * New format: {"element":[{"table":"Person","data":[{"id":600}]}]}
     * Optional fields: relation, identifier, condition, element
     *
     * Converts nested element structure into flat table => data mapping.
     * All data records for the same table are merged into single override set.
     * Applied to ALL records of that table during import.
     *
     * @param string $definition JSON string with element structure
     * @return array Flat structure: ['TableName' => ['column' => 'value', ...], ...]
     * @throws Exception
     */
    private function parseImportOverrides(string $definition): array {
        $overrideData = json_decode($definition, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception("Invalid override JSON: " . json_last_error_msg());
        }

        if (!isset($overrideData['element'])) {
            throw new Exception("Invalid override structure: missing element array");
        }

        // Convert nested element structure to flat table => data mapping
        return $this->flattenOverrideElements($overrideData['element']);
    }

    /**
     * Flatten override elements recursively into table => data mapping
     *
     * Processes nested elements and merges all data for each table.
     * Ignores optional fields (relation, identifier, condition) as they're not needed for overrides.
     *
     * @param array $elements Array of element objects
     * @return array Flat structure: ['TableName' => ['column' => 'value', ...], ...]
     */
    private function flattenOverrideElements(array $elements): array {
        $result = [];

        foreach ($elements as $element) {
            $table = $element['table'] ?? null;

            if (!$table) {
                continue;
            }

            $data = $element['data'] ?? [];

            // Handle both single object and array of data
            if (isset($data[0]) && is_array($data[0])) {
                // Array of data records - merge all into one override set
                $mergedData = [];
                foreach ($data as $dataRecord) {
                    $mergedData = array_merge($mergedData, $dataRecord);
                }
                $data = $mergedData;
            }

            // Initialize table entry if not exists
            if (!isset($result[$table])) {
                $result[$table] = [];
            }

            // Merge data with existing overrides for this table
            $result[$table] = array_merge($result[$table], $data);

            // Process nested elements recursively
            if (isset($element['element']) && is_array($element['element'])) {
                $childOverrides = $this->flattenOverrideElements($element['element']);

                // Merge child overrides
                foreach ($childOverrides as $childTable => $childData) {
                    if (!isset($result[$childTable])) {
                        $result[$childTable] = [];
                    }
                    $result[$childTable] = array_merge($result[$childTable], $childData);
                }
            }
        }

        return $result;
    }

    /**
     * Import element recursively (ADAPTIVE DEPTH)
     *
     * Import order depends on relation type:
     *
     * For NORMAL relations (child->parent, e.g. FormElement.formId -> Form.id):
     * 1. Map parent relations
     * 2. Insert parent record FIRST
     * 3. Import children (with mapped parent ID)
     *
     * For CHILD relations (parent->child, e.g. Address.id <- AddressAssign.adrId):
     * 1. Import children FIRST (depth-first)
     * 2. Map child relations
     * 3. Insert parent record
     */
    private function importElementRecursive(array $element, array $overrides): array {
        $table      = $element['table'];
        $dataInput  = $element['data'];
        $identifier = $element['identifier'] ?? null;
        $relation   = $element['relation'] ?? [];

        $result = [
            'table' => $table,
            'items' => [],
        ];

        // Handle data as array or single object
        if (isset($dataInput[0]) && is_array($dataInput[0])) {
            $dataArray = $dataInput;
        } else {
            $dataArray = [$dataInput];
        }

        // Check if this element has child relations (parent->child FKs)
        // Only check first record to determine strategy
        $hasChildRelations = false;
        if (!empty($dataArray)) {
            $firstData = $dataArray[0];

            // Apply overrides to get complete data
            if (isset($overrides[$table])) {
                $firstData = array_merge($firstData, $overrides[$table]);
            }

            $hasChildRelations = $this->hasChildRelations($firstData, $element['element'] ?? []);
        }

        // Collect child results (they are imported only once for array data)
        $childResults = [];

        if ($hasChildRelations) {
            // -------------------------------------------------------------
            // CHILD-FIRST STRATEGY (parent has FKs to children)
            // -------------------------------------------------------------
            $childrenImported = false;

            foreach ($dataArray as $dataIndex => $data) {
                // Save OLD ID from export data BEFORE applying overrides
                $exportId = $data['id'] ?? null;

                // Apply overrides
                if (isset($overrides[$table])) {
                    $data = array_merge($data, $overrides[$table]);
                }

                // Use export ID for mapping (not potentially modified ID from overrides)
                $oldId = $exportId;

                // Map PARENT relation keys (from parent's idMapping)
                $data = $this->mapRelationKeys($data, $relation);

                $itemResult = [
                    'action' => null,
                    'oldId'  => $oldId,
                    'newId'  => null,
                ];

                // DEPTH-FIRST: Import children first (for child relations)
                // Import children only ONCE for array data (not for every parent)
                if (!$childrenImported && !empty($element['element'])) {
                    foreach ($element['element'] as $childElement) {
                        $childResult    = $this->importElementRecursive($childElement, $overrides);
                        $childResults[] = $childResult;
                    }
                    $childrenImported = true;
                }

                // Map child relation keys (FKs from parent to children)
                $data = $this->mapChildRelationKeys($data, $element['element'] ?? []);

                // Remove id from data
                unset($data['id']);

                // Insert parent AFTER children
                if ($identifier !== null && $identifier !== '') {
                    $existingId = $this->findByIdentifier($table, $data, $identifier, $relation);

                    if ($existingId) {
                        $itemResult['action'] = 'existing';
                        $itemResult['newId']  = $existingId;
                    } else {
                        $newId                = $this->insertRecord($table, $data);
                        $itemResult['action'] = 'inserted';
                        $itemResult['newId']  = $newId;
                    }
                } else {
                    $newId                = $this->insertRecord($table, $data);
                    $itemResult['action'] = 'inserted';
                    $itemResult['newId']  = $newId;
                }

                // Map old ID to new ID
                if ($oldId && !isset($this->idMapping[$table][$oldId])) {
                    $this->idMapping[$table][$oldId] = $itemResult['newId'];
                }

                $result['items'][] = $itemResult;
            }

        } else {
            // -------------------------------------------------------------
            // PARENT-FIRST STRATEGY (child has FKs to parent)
            //   -> erst alle Eltern importieren (inkl. idMapping),
            //      dann Kinder.
            // -------------------------------------------------------------

            // 1) Alle Eltern importieren
            foreach ($dataArray as $dataIndex => $data) {
                // Save OLD ID from export data BEFORE applying overrides
                $exportId = $data['id'] ?? null;

                // Apply overrides
                if (isset($overrides[$table])) {
                    $data = array_merge($data, $overrides[$table]);
                }

                // Use export ID for mapping (not potentially modified ID from overrides)
                $oldId = $exportId;

                // Map PARENT relation keys (from parent's idMapping)
                $data = $this->mapRelationKeys($data, $relation);

                // Remove id from data
                unset($data['id']);

                $itemResult = [
                    'action' => null,
                    'oldId'  => $oldId,
                    'newId'  => null,
                ];

                // Insert parent FIRST
                if ($identifier !== null && $identifier !== '') {
                    $existingId = $this->findByIdentifier($table, $data, $identifier, $relation);

                    if ($existingId) {
                        $itemResult['action'] = 'existing';
                        $itemResult['newId']  = $existingId;
                    } else {
                        $newId                = $this->insertRecord($table, $data);
                        $itemResult['action'] = 'inserted';
                        $itemResult['newId']  = $newId;
                    }
                } else {
                    $newId                = $this->insertRecord($table, $data);
                    $itemResult['action'] = 'inserted';
                    $itemResult['newId']  = $newId;
                }

                // Map old ID to new ID (jetzt existiert der Parent sicher)
                if ($oldId) {
                    $this->idMapping[$table][$oldId] = $itemResult['newId'];
                }

                $result['items'][] = $itemResult;
            }

            // 2) Kinder importieren – jetzt sind alle Parent-IDs gemappt
            if (!empty($element['element'])) {
                foreach ($element['element'] as $childElement) {
                    $childResult    = $this->importElementRecursive($childElement, $overrides);
                    $childResults[] = $childResult;
                }
            }
        }

        // Add child results (imported only once for array data)
        if (!empty($childResults)) {
            $result['children'] = $childResults;
        }

        return $result;
    }

    /**
     * Find existing record by identifier columns
     */
    private function findByIdentifier(string $table, array $data, string $identifier, array $relation = []): ?int {
        $tableName = $this->escapeIdentifier($table);

        // Determine which columns to use for identification
        $identifierColumns = [];
        if ($identifier === '*') {
            // Use all columns EXCEPT: id, created, modified, and relation FK columns
            $excludeColumns = ['id', 'created', 'modified'];

            // Add relation FK columns to exclude
            foreach ($relation as $fkColumn => $parentRef) {
                $excludeColumns[] = $fkColumn;
            }

            $identifierColumns = array_diff(array_keys($data), $excludeColumns);
        } else {
            // Use specified columns (separated by -)
            $identifierColumns = array_map('trim', explode('-', $identifier));
        }

        if (empty($identifierColumns)) {
            return null;
        }

        // Build WHERE clause
        $conditions = [];
        $params = [];

        foreach ($identifierColumns as $column) {
            if (!array_key_exists($column, $data)) {
                continue;
            }

            $escapedColumn = $this->escapeIdentifier($column);
            $value = $data[$column];

            if ($value === null) {
                $conditions[] = "{$escapedColumn} IS NULL";
            } else {
                $conditions[] = "{$escapedColumn} = ?";
                $params[] = $value;
            }
        }

        if (empty($conditions)) {
            return null;
        }

        $query = "SELECT id FROM {$tableName} WHERE " . implode(' AND ', $conditions) . " LIMIT 1";

        $result = $this->db->sql($query, ROW_REGULAR, $params);

        if (empty($result)) {
            return null;
        }

        return $result[0]['id'] ?? null;
    }

    /**
     * Insert record into database
     */
    private function insertRecord(string $table, array $data): int {
        if (empty($data)) {
            throw new Exception("Cannot insert empty data into {$table}");
        }

        $tableName = $this->escapeIdentifier($table);
        $columns = array_keys($data);
        $values = array_values($data);

        $columnsList = implode(', ', array_map(fn($c) => $this->escapeIdentifier($c), $columns));
        $placeholders = implode(', ', array_fill(0, count($values), '?'));

        $query = "INSERT INTO {$tableName} ({$columnsList}) VALUES ({$placeholders})";

        // sql() returns the insert ID directly for INSERT statements
        $insertId = $this->db->sql($query, ROW_REGULAR, $values);

        if (!$insertId) {
            throw new Exception("Insert failed for {$table}: could not retrieve insert ID");
        }

        return $insertId;
    }

    /**
     * Map relation keys from old IDs to new IDs (PARENT relations)
     * Relation format: { "pId": "Person.id" }
     */
    private function mapRelationKeys(array $data, array $relation): array {
        $mapped = $data;

        foreach ($relation as $column => $reference) {
            // Parse reference: "Person.id"
            [$referencesTable, $referencesColumn] = explode('.', $reference, 2);
            $oldValue = $data[$column] ?? null;

            if ($oldValue !== null && isset($this->idMapping[$referencesTable][$oldValue])) {
                $mapped[$column] = $this->idMapping[$referencesTable][$oldValue];
            }
        }

        return $mapped;
    }

    /**
     * Map child relation keys (FKs pointing to child records)
     *
     * Example: AddressAssign has "adrId" pointing to Address (which is a child element)
     * Child relation format: { "id": "AddressAssign.adrId" } means Address.id maps to AddressAssign.adrId
     *
     * @param array $data Current record data
     * @param array $childElements Child element definitions
     * @return array Data with child FK columns mapped to new IDs
     */
    private function mapChildRelationKeys(array $data, array $childElements): array {
        $mapped = $data;

        foreach ($childElements as $childElement) {
            $childTable = $childElement['table'];
            $childRelation = $childElement['relation'] ?? [];

            // Child relation format: { "childCol": "ParentTable.parentCol" }
            // We need to reverse this: find parentCol and map it using childTable's idMapping
            foreach ($childRelation as $childCol => $parentRef) {
                // Parse: "AddressAssign.adrId"
                [$parentTable, $parentCol] = explode('.', $parentRef, 2);

                // If parentCol exists in current data, map it using child's idMapping
                if (isset($mapped[$parentCol])) {
                    $oldValue = $mapped[$parentCol];

                    if (isset($this->idMapping[$childTable][$oldValue])) {
                        $mapped[$parentCol] = $this->idMapping[$childTable][$oldValue];
                    }
                }
            }
        }

        return $mapped;
    }

    /**
     * Check if element has child relations (parent->child FK)
     *
     * Child relations occur when the PARENT has a FK field (other than 'id')
     * that points to a CHILD record.
     *
     * Example of CHILD relation:
     *   Address has field "adrId" that points to AddressAssign.id
     *   Relation: {"id": "Address.adrId"}
     *   → Address must be inserted AFTER AddressAssign (depth-first)
     *
     * Example of NORMAL relation (NOT child relation):
     *   FormElement has field "formId" that points to Form.id
     *   Relation: {"formId": "Form.id"}
     *   → Form must be inserted BEFORE FormElement (parent-first)
     *
     * @param array $data Parent record data
     * @param array $childElements Child element definitions
     * @return bool True if parent has FK fields pointing to children
     */
    private function hasChildRelations(array $data, array $childElements): bool {
        foreach ($childElements as $childElement) {
            $childRelation = $childElement['relation'] ?? [];

            foreach ($childRelation as $childCol => $parentRef) {
                // Parse: "ParentTable.parentCol"
                [$parentTable, $parentCol] = explode('.', $parentRef, 2);

                // Child relation: Parent has a FK field (NOT 'id') pointing to child
                // Normal relation: Child has FK field pointing to parent's 'id'
                if ($parentCol !== 'id' && isset($data[$parentCol])) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Escape table/column identifier
     */
    private function escapeIdentifier(string $identifier): string {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }
}