<?php

/**
 * SyncByRule - Export/Import relational database records via JSON with deployment tracking
 *
 * Handles copying records with their relations across databases with same structure.
 * Supports identifier-based duplicate detection, ID mapping, flexible overrides, deployment tracking,
 * data type validation, and checksum verification.
 *
 * Export uses JSON template syntax directly in SQL.
 *
 * @author enured
 * @version 1.1
 */

namespace IMATHUZH\Qfq\Core\Helper;

use Exception;
use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Evaluate;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;

/**
 * Class SyncByRule
 *
 * @package IMATHUZH\Qfq\Core\Helper
 */
class SyncByRule {

    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;
    private ?int $ruleId = null;
    private bool $enableDeployTracking = false;

    /**
     * dataType for validation - set from rule definition
     */
    private ?string $dataType = null;

    /**
     * baseUrlSrc for checksum verification - set from rule definition or auto-detected
     */
    private ?string $baseUrlSrc = null;

    /**
     * When true: Update DeployRef.modified on each skipModified deployment (normal behavior).
     * - overwrites user data if source record is modified again and younger than users modification
     * When false: Keep original DeployRef.modified from first deployment, never overwrite
     * - user data remains intact on subsequent deployments.
     * - in future useful to show to user the incoming data which can be manually applied if wanted
     */
    private bool $skipModifiedActive = false;

    /**
     * @param mixed $content JSON template, rule names, or import data
     * @param Database|null $db Database instance
     * @param Store|null $store Store instance
     * @param bool $phpUnit PHPUnit test mode
     * @param bool $enableDeployTracking Enable DeployRef tracking (for deployments)
     * @throws \CodeException
     * @throws \UserFormException
     * @throws \DbException
     * @throws \UserReportException
     */
    public function __construct($content, $db = null, $store = null, $phpUnit = false, $enableDeployTracking = false) {
        if (defined('PHPUNIT_QFQ')) {
            $phpUnit = true;
        }

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

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

        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;
    }

    // Only use for debugging reasons
    private function debug($message) {
        $logFile = '/tmp/syncByRule_debug.log';
        file_put_contents($logFile, date('Y-m-d H:i:s') . ' ' . $message . "\n", FILE_APPEND);
    }

    /**
     * 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[SYNC_BY_RULE_JSON_ELEMENT]) || !is_array($template[SYNC_BY_RULE_JSON_ELEMENT])) {
            throw new \Exception("Invalid template: missing element array");
        }

        // Extract dataType from template if defined
        $this->dataType = $template[SYNC_BY_RULE_JSON_DATA_TYPE] ?? null;

        // Extract or auto-detect baseUrlSrc
        $this->baseUrlSrc = $template[SYNC_BY_RULE_JSON_BASE_URL_SRC] ?? null;
        if (empty($this->baseUrlSrc)) {
            $this->baseUrlSrc = $this->store->getVar(SYSTEM_BASE_URL, STORE_SYSTEM);
        }

        // Build result with version, timestamp, and optional ruleId
        $result = [
            SYNC_BY_RULE_JSON_VERSION => '1.0',
            SYNC_BY_RULE_JSON_EXPORTED_AT => date('c'),
        ];

        // Add dataType if defined
        if ($this->dataType !== null) {
            $result[SYNC_BY_RULE_JSON_DATA_TYPE] = $this->dataType;
        }

        // Add baseUrlSrc (always present for checksum verification)
        $result[SYNC_BY_RULE_JSON_BASE_URL_SRC] = $this->baseUrlSrc;

        // Placeholder for checksum (will be calculated after elements are added)
        $result[SYNC_BY_RULE_JSON_CHECKSUM] = '';

        // Include ruleId only if deployment tracking is enabled
        if ($this->enableDeployTracking && $this->ruleId !== null) {
            $result[SYNC_BY_RULE_JSON_RULE_ID] = $this->ruleId;
        }

        // Initialize element array
        $result[SYNC_BY_RULE_JSON_ELEMENT] = [];

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

        // Calculate and set checksum
        $result[SYNC_BY_RULE_JSON_CHECKSUM] = $this->calculateChecksum($result);

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

    /**
     * Handle Import: Parse JSON + overrides, check duplicates, insert/update with DeployRef tracking
     *
     * @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
     * @throws SyncByRuleValidationException For validation errors (dataType mismatch, table not found, checksum invalid)
     */
    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[SYNC_BY_RULE_JSON_ELEMENT])) {
            throw new Exception("Invalid JSON structure: missing element array");
        }

        // Parse import overrides to get expected dataType
        $overrides = [];
        $expectedDataType = null;
        if ($importOverrides) {
            $overrideData = json_decode($importOverrides, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                $expectedDataType = $overrideData[SYNC_BY_RULE_JSON_DATA_TYPE] ?? null;
                $overrides = $this->parseImportOverrides($importOverrides);
            }
        }

        // VALIDATION CHECKS (only for browser/clipboard usage, not for CLI deployments)
        if (!$this->enableDeployTracking) {

            // VALIDATION 1: Data Type Check
            $importDataType = $data[SYNC_BY_RULE_JSON_DATA_TYPE] ?? null;

            if ($expectedDataType !== null || $importDataType !== null) {
                // One have dataType defined - must match
                if ($expectedDataType !== $importDataType) {
                    throw new SyncByRuleValidationException(
                        "Data type mismatch: expected '{$expectedDataType}', got '{$importDataType}'",
                        SYNC_BY_RULE_ERROR_DATA_TYPE_MISMATCH
                    );
                }
            } else {
                // Neither has dataType - validate tables exist as fallback
                $this->validateTablesExist($data[SYNC_BY_RULE_JSON_ELEMENT]);
            }

            // VALIDATION 2: Checksum Verification
            $importChecksum = $data[SYNC_BY_RULE_JSON_CHECKSUM] ?? null;
            $importBaseUrlSrc = $data[SYNC_BY_RULE_JSON_BASE_URL_SRC] ?? null;

            if ($importChecksum !== null && $importBaseUrlSrc !== null) {
                $this->verifyChecksum($data, $importChecksum, $importBaseUrlSrc);
            }
        }

        // Extract ruleId from import data if present
        $importRuleId = $data[SYNC_BY_RULE_JSON_RULE_ID] ?? null;

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

        try {
            $results = [
                SYNC_BY_RULE_JSON_RESULT_SUCCESS => true,
                SYNC_BY_RULE_JSON_RESULT_IMPORTED => [],
                SYNC_BY_RULE_JSON_RESULT_ID_MAPPING => []
            ];

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

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

            return $results;

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

    /**
     * Calculate checksum for JSON data
     *
     * @param array $data JSON data array (checksum field will be ignored/excluded)
     * @return string Checksum hash
     */
    private function calculateChecksum(array $data): string {
        // Get encryption key from store
        $authKey = $this->store->getVar(SYSTEM_ENCRYPTION_KEY, STORE_SYSTEM);

        if (empty($authKey)) {
            throw new Exception("Encryption key not configured");
        }

        // Create a copy without the checksum field for calculation
        $dataForChecksum = $data;
        unset($dataForChecksum[SYNC_BY_RULE_JSON_CHECKSUM]);

        // Create deterministic JSON string (sorted keys, no pretty print)
        $jsonString = json_encode($dataForChecksum, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        // Calculate HMAC-SHA256
        return hash_hmac('sha256', $jsonString, $authKey);
    }

    /**
     * Verify checksum by calling source server's checksum API
     *
     * @param array $data Import data
     * @param string $checksum Checksum from import data
     * @param string $baseUrlSrc Source server base URL
     * @throws SyncByRuleValidationException If checksum verification fails
     */
    private function verifyChecksum(array $data, string $checksum, string $baseUrlSrc): void {
        // Prepare data for verification (exclude checksum)
        $dataForVerification = $data;
        unset($dataForVerification[SYNC_BY_RULE_JSON_CHECKSUM]);

        $jsonString = json_encode($dataForVerification, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

        // Build checksum API URL
        $checksumApiUrl = rtrim($baseUrlSrc, '/') . '/typo3conf/ext/qfq/Classes/External/checksum.php';

        // Make API call
        try {
            $response = HelperFile::httpPostJson($checksumApiUrl, [
                'checksum' => $checksum,
                'jsonData' => $jsonString
            ]);
        } catch (\UserFormException $e) {
            throw new SyncByRuleValidationException(
                "Checksum verification failed: Could not connect to source server",
                SYNC_BY_RULE_ERROR_CHECKSUM_VERIFICATION_FAILED
            );
        }

        $httpCode = $response['httpCode'];
        $body = $response['body'];

        if ($httpCode === 429) {
            throw new SyncByRuleValidationException(
                "Checksum verification failed: Rate limit exceeded on source server",
                SYNC_BY_RULE_ERROR_CHECKSUM_VERIFICATION_FAILED
            );
        }

        if ($httpCode !== 200) {
            throw new SyncByRuleValidationException(
                "Checksum verification failed: Source server returned HTTP {$httpCode}",
                SYNC_BY_RULE_ERROR_CHECKSUM_VERIFICATION_FAILED
            );
        }

        $result = json_decode($body, true);

        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new SyncByRuleValidationException(
                "Checksum verification failed: Invalid response from source server",
                SYNC_BY_RULE_ERROR_CHECKSUM_VERIFICATION_FAILED
            );
        }

        if (!isset($result['valid']) || $result['valid'] !== true) {
            $errorMessage = $result['message'] ?? 'Checksum mismatch - data may have been modified';
            throw new SyncByRuleValidationException(
                "Checksum verification failed: {$errorMessage}",
                SYNC_BY_RULE_ERROR_CHECKSUM_INVALID
            );
        }
    }

    /**
     * Validate that all tables in the import data exist in the target database
     *
     * @param array $elements Element array from import data
     * @throws SyncByRuleValidationException If any table does not exist
     */
    private function validateTablesExist(array $elements): void {
        $tables = $this->extractTableNames($elements);
        $missingTables = [];

        foreach ($tables as $tableName) {
            if (!$this->tableExists($tableName)) {
                $missingTables[] = $tableName;
            }
        }

        if (!empty($missingTables)) {
            throw new SyncByRuleValidationException(
                "Tables not found in target database: " . implode(', ', $missingTables),
                SYNC_BY_RULE_ERROR_TABLE_NOT_FOUND
            );
        }
    }

    /**
     * Extract all table names from elements recursively
     *
     * @param array $elements Element array
     * @return array Unique table names
     */
    private function extractTableNames(array $elements): array {
        $tables = [];

        foreach ($elements as $element) {
            if (isset($element[SYNC_BY_RULE_JSON_TABLE])) {
                $tables[] = $element[SYNC_BY_RULE_JSON_TABLE];
            }

            if (isset($element[SYNC_BY_RULE_JSON_ELEMENT]) && is_array($element[SYNC_BY_RULE_JSON_ELEMENT])) {
                $childTables = $this->extractTableNames($element[SYNC_BY_RULE_JSON_ELEMENT]);
                $tables = array_merge($tables, $childTables);
            }
        }

        return array_unique($tables);
    }

    /**
     * Check if a table exists in the database
     *
     * @param string $tableName Table name to check
     * @return bool True if table exists
     */
    private function tableExists(string $tableName): bool {
        try {
            // Use information_schema instead of SHOW TABLES (supports prepared statements)
            $query = "SELECT COUNT(*) as cnt FROM information_schema.tables 
                  WHERE table_schema = DATABASE() AND table_name = ?";
            $result = $this->db->sql($query, ROW_EXPECT_1, [$tableName]);
            return (int)($result['cnt'] ?? 0) > 0;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Load and merge multiple rule definitions from DeployRule 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");
        }

        $evaluate = new Evaluate($this->store, $this->db);

        $mergedTemplate = [
            SYNC_BY_RULE_JSON_ELEMENT => []
        ];

        // Track first rule ID for export metadata
        $firstRuleId = null;
        $mergedDataType = null;
        $mergedBaseUrlSrc = null;

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

            $query = "SELECT id, definition FROM DeployRule 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];

            // Store first rule ID
            if ($firstRuleId === null) {
                $firstRuleId = $row[SYNC_BY_RULE_DB_COLUMN_ID];
            }

            // Parse rule definition
            $ruleDefinition = $row[SYNC_BY_RULE_DB_COLUMN_DEFINITION];
            $ruleDefinition = $evaluate->parse($ruleDefinition);

            $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[SYNC_BY_RULE_JSON_ELEMENT]) && is_array($ruleTemplate[SYNC_BY_RULE_JSON_ELEMENT])) {
                $mergedTemplate[SYNC_BY_RULE_JSON_ELEMENT] = array_merge(
                    $mergedTemplate[SYNC_BY_RULE_JSON_ELEMENT],
                    $ruleTemplate[SYNC_BY_RULE_JSON_ELEMENT]
                );
            }

            // Merge dataType (first non-null wins)
            if ($mergedDataType === null && isset($ruleTemplate[SYNC_BY_RULE_JSON_DATA_TYPE])) {
                $mergedDataType = $ruleTemplate[SYNC_BY_RULE_JSON_DATA_TYPE];
            }

            // Merge baseUrlSrc (first non-null wins)
            if ($mergedBaseUrlSrc === null && isset($ruleTemplate[SYNC_BY_RULE_JSON_BASE_URL_SRC])) {
                $mergedBaseUrlSrc = $ruleTemplate[SYNC_BY_RULE_JSON_BASE_URL_SRC];
            }
        }

        // Set ruleId for export metadata
        $this->ruleId = $firstRuleId;

        // Add merged metadata to template
        if ($mergedDataType !== null) {
            $mergedTemplate[SYNC_BY_RULE_JSON_DATA_TYPE] = $mergedDataType;
        }
        if ($mergedBaseUrlSrc !== null) {
            $mergedTemplate[SYNC_BY_RULE_JSON_BASE_URL_SRC] = $mergedBaseUrlSrc;
        }

        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[SYNC_BY_RULE_JSON_TABLE] ?? null;
        $dataTemplate = $template[SYNC_BY_RULE_JSON_DATA] ?? null;
        $condition = $template[SYNC_BY_RULE_JSON_CONDITION] ?? null;
        $relation = $template[SYNC_BY_RULE_JSON_RELATION] ?? null;
        $identifier = $template[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null;
        $update = $template[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE;
        $keepId = $template[SYNC_BY_RULE_JSON_KEEP_ID] ?? false;
        $childTemplates = $template[SYNC_BY_RULE_JSON_ELEMENT] ?? [];

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

        // Normalize update mode
        $update = $this->normalizeUpdateMode($update);

        // 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
                // PASS PARENT TABLE NAME so fetchChildrenBatch can distinguish relation types
                $allChildRecords = $this->fetchChildrenBatch($childTemplate, $records, $table);

                // 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 = [
                SYNC_BY_RULE_JSON_TABLE => $table,
                SYNC_BY_RULE_JSON_DATA => $record,
                SYNC_BY_RULE_JSON_RELATION => $relation,
                SYNC_BY_RULE_JSON_IDENTIFIER => $identifier,
                SYNC_BY_RULE_JSON_CONDITION => $condition,
                SYNC_BY_RULE_JSON_UPDATE => $update,
                SYNC_BY_RULE_JSON_KEEP_ID => $keepId,
                SYNC_BY_RULE_JSON_ELEMENT => []
            ];

            // Assign pre-fetched children to this parent
            foreach ($childTemplates as $childIndex => $childTemplate) {
                $childRelation = $childTemplate[SYNC_BY_RULE_JSON_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[SYNC_BY_RULE_JSON_ELEMENT] = array_merge($element[SYNC_BY_RULE_JSON_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[SYNC_BY_RULE_JSON_ELEMENT] as $childElement) {
                    // Create unique key for deduplication (table + data identifier)
                    $childTable = $childElement[SYNC_BY_RULE_JSON_TABLE];
                    $childIdentifier = $childElement[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null;

                    // Use identifier fields or all data for uniqueness check
                    if ($childIdentifier && $childIdentifier !== '') {
                        if ($childIdentifier === '*') {
                            // Use all non-id fields
                            $keyData = $childElement[SYNC_BY_RULE_JSON_DATA];
                            unset($keyData[SYNC_BY_RULE_DB_COLUMN_ID], $keyData[SYNC_BY_RULE_DB_COLUMN_CREATED], $keyData[SYNC_BY_RULE_DB_COLUMN_MODIFIED]);
                        } else {
                            // Use specified identifier fields
                            $identifierFields = explode('-', $childIdentifier);
                            $keyData = [];
                            foreach ($identifierFields as $field) {
                                $keyData[$field] = $childElement[SYNC_BY_RULE_JSON_DATA][$field] ?? null;
                            }
                        }
                    } else {
                        // No identifier - use id or all data
                        $keyData = [SYNC_BY_RULE_DB_COLUMN_ID => $childElement[SYNC_BY_RULE_JSON_DATA][SYNC_BY_RULE_DB_COLUMN_ID] ?? json_encode($childElement[SYNC_BY_RULE_JSON_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 = [
                SYNC_BY_RULE_JSON_TABLE => $table,
                SYNC_BY_RULE_JSON_DATA => array_map(fn($e) => $e[SYNC_BY_RULE_JSON_DATA], $elements),
                SYNC_BY_RULE_JSON_RELATION => $relation,
                SYNC_BY_RULE_JSON_IDENTIFIER => $identifier,
                SYNC_BY_RULE_JSON_CONDITION => $condition,
                SYNC_BY_RULE_JSON_UPDATE => $update,
                SYNC_BY_RULE_JSON_KEEP_ID => $keepId,
                SYNC_BY_RULE_JSON_ELEMENT => $allChildElements
            ];

            return [$grouped];
        }

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

        return $elements;
    }

    /**
     * Normalize update mode - convert old boolean format to new string format
     *
     * @param mixed $update Update mode (boolean or string)
     * @return string Normalized update mode
     */
    private function normalizeUpdateMode($update): string {
        // Handle old boolean format for backwards compatibility
        if (is_bool($update)) {
            return $update ? SYNC_BY_RULE_UPDATE_ALWAYS : SYNC_BY_RULE_UPDATE_ONCE;
        }

        // Validate string format
        if (in_array($update, [SYNC_BY_RULE_UPDATE_ONCE, SYNC_BY_RULE_UPDATE_ALWAYS, SYNC_BY_RULE_UPDATE_SKIP_MODIFIED])) {
            return $update;
        }

        // Default to 'once' for invalid values
        return SYNC_BY_RULE_UPDATE_ONCE;
    }

    /**
     * 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
                $parentValue = null;

                if ($childCol === SYNC_BY_RULE_DB_COLUMN_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
                    $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][SYNC_BY_RULE_DB_COLUMN_ID])) {
                $ids = array_map(fn($d) => $d[SYNC_BY_RULE_DB_COLUMN_ID], $dataTemplate);
                $ids = array_filter($ids); // Remove nulls

                if (!empty($ids)) {
                    $placeholders = implode(',', array_fill(0, count($ids), '?'));
                    $whereConditions[] = $this->escapeIdentifier(SYNC_BY_RULE_DB_COLUMN_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
     *
     * @param array $childTemplate Child element template
     * @param array $parentRecords Parent records
     * @param string $parentTable Parent table name (needed to distinguish relation types)
     * @return array Child records
     */
    private function fetchChildrenBatch(array $childTemplate, array $parentRecords, string $parentTable): array {
        if (empty($parentRecords)) {
            return [];
        }

        $table = $childTemplate[SYNC_BY_RULE_JSON_TABLE] ?? null;
        $condition = $childTemplate[SYNC_BY_RULE_JSON_CONDITION] ?? null;
        $relation = $childTemplate[SYNC_BY_RULE_JSON_RELATION] ?? null;

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

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

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

            // Determine relation type and which column to use
            $relationIds = [];
            foreach ($parentRecords as $parent) {
                $value = null;

                // Check 1: Reverse relation (child.id references parent.fkCol)
                if ($childCol === SYNC_BY_RULE_DB_COLUMN_ID) {
                    if (isset($parent[$refCol]) && $parent[$refCol] !== null && $parent[$refCol] !== 0 && $parent[$refCol] !== '0') {
                        $value = $parent[$refCol];
                    }
                }
                // Check 2: Self-referencing (child and parent are same table)
                elseif ($refTable === $table) {
                    if (isset($parent[$refCol]) && $parent[$refCol] !== null && $parent[$refCol] !== 0 && $parent[$refCol] !== '0') {
                        $value = $parent[$refCol];
                    }
                }
                // Check 3: CROSS-TABLE HIERARCHY
                elseif ($parentTable !== $refTable && isset($parent[$childCol]) && $parent[$childCol] !== null && $parent[$childCol] !== 0 && $parent[$childCol] !== '0') {
                    $value = $parent[$childCol];
                }
                // Check 4: 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)
        $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
     */
    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);

            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[SYNC_BY_RULE_JSON_ELEMENT] ?? [];

        $element = [
            SYNC_BY_RULE_JSON_TABLE => $template[SYNC_BY_RULE_JSON_TABLE],
            SYNC_BY_RULE_JSON_DATA => $recordData,
            SYNC_BY_RULE_JSON_RELATION => $template[SYNC_BY_RULE_JSON_RELATION] ?? null,
            SYNC_BY_RULE_JSON_IDENTIFIER => $template[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null,
            SYNC_BY_RULE_JSON_CONDITION => $template[SYNC_BY_RULE_JSON_CONDITION] ?? null,
            SYNC_BY_RULE_JSON_UPDATE => $this->normalizeUpdateMode($template[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE),
            SYNC_BY_RULE_JSON_KEEP_ID => $template[SYNC_BY_RULE_JSON_KEEP_ID] ?? false,
            SYNC_BY_RULE_JSON_ELEMENT => []
        ];

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

        return [$element];
    }

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

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

        // Don't group if any element has children
        foreach ($elements as $element) {
            if (!empty($element[SYNC_BY_RULE_JSON_ELEMENT])) {
                return false;
            }
        }

        // Check if all elements have same metadata
        $first = $elements[0];
        $firstRelation = json_encode($first[SYNC_BY_RULE_JSON_RELATION]);
        $firstIdentifier = $first[SYNC_BY_RULE_JSON_IDENTIFIER];
        $firstCondition = $first[SYNC_BY_RULE_JSON_CONDITION];
        $firstUpdate = $first[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE;
        $firstKeepId = $first[SYNC_BY_RULE_JSON_KEEP_ID] ?? false;

        foreach ($elements as $element) {
            if (json_encode($element[SYNC_BY_RULE_JSON_RELATION]) !== $firstRelation ||
                $element[SYNC_BY_RULE_JSON_IDENTIFIER] !== $firstIdentifier ||
                $element[SYNC_BY_RULE_JSON_CONDITION] !== $firstCondition ||
                ($element[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE) !== $firstUpdate ||
                ($element[SYNC_BY_RULE_JSON_KEEP_ID] ?? false) !== $firstKeepId) {
                return false;
            }
        }

        return true;
    }

    /**
     * Group child elements that have the same table, relation, identifier, and condition
     */
    private function groupChildElements(array $childElements): array {
        if (empty($childElements)) {
            return [];
        }

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

        foreach ($childElements as $childElement) {
            $groupKey = json_encode([
                SYNC_BY_RULE_JSON_TABLE => $childElement[SYNC_BY_RULE_JSON_TABLE],
                SYNC_BY_RULE_JSON_RELATION => $childElement[SYNC_BY_RULE_JSON_RELATION] ?? null,
                SYNC_BY_RULE_JSON_IDENTIFIER => $childElement[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null,
                SYNC_BY_RULE_JSON_CONDITION => $childElement[SYNC_BY_RULE_JSON_CONDITION] ?? null,
                SYNC_BY_RULE_JSON_UPDATE => $childElement[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE,
                SYNC_BY_RULE_JSON_KEEP_ID => $childElement[SYNC_BY_RULE_JSON_KEEP_ID] ?? false
            ]);

            if (!isset($groups[$groupKey])) {
                $groups[$groupKey] = [
                    SYNC_BY_RULE_TEMPLATE => $childElement,
                    SYNC_BY_RULE_JSON_DATA => [],
                    SYNC_BY_RULE_CHILD_ELEMENTS => []
                ];
            }

            $groups[$groupKey][SYNC_BY_RULE_JSON_DATA][] = $childElement[SYNC_BY_RULE_JSON_DATA];

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

        // Build result
        $result = [];

        foreach ($groups as $group) {
            $template = $group[SYNC_BY_RULE_TEMPLATE];
            $dataRecords = $group[SYNC_BY_RULE_JSON_DATA];
            $grandchildren = $group[SYNC_BY_RULE_CHILD_ELEMENTS];

            // Flatten nested data arrays
            $flatDataRecords = [];
            foreach ($dataRecords as $dataRecord) {
                if (is_array($dataRecord) && isset($dataRecord[0]) && is_array($dataRecord[0])) {
                    foreach ($dataRecord as $nestedRecord) {
                        $flatDataRecords[] = $nestedRecord;
                    }
                } else {
                    $flatDataRecords[] = $dataRecord;
                }
            }

            $template[SYNC_BY_RULE_JSON_DATA] = count($flatDataRecords) === 1
                ? $flatDataRecords[0]
                : $flatDataRecords;

            // Deduplicate and group grandchildren
            if (!empty($grandchildren)) {
                $uniqueGrandchildren = [];
                $seenKeys = [];
                foreach ($grandchildren as $grandchild) {
                    $childTable = $grandchild[SYNC_BY_RULE_JSON_TABLE];
                    $childIdentifier = $grandchild[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null;
                    $grandchildData = $grandchild[SYNC_BY_RULE_JSON_DATA];

                    // Check if data is already grouped (array of records)
                    $isGroupedData = is_array($grandchildData) && isset($grandchildData[0]) && is_array($grandchildData[0]);

                    if ($isGroupedData) {
                        // For grouped data, build key from all records' identifiers
                        $allKeys = [];
                        foreach ($grandchildData as $record) {
                            if ($childIdentifier === '*') {
                                $recordKey = $record;
                                unset($recordKey[SYNC_BY_RULE_DB_COLUMN_ID], $recordKey[SYNC_BY_RULE_DB_COLUMN_CREATED], $recordKey[SYNC_BY_RULE_DB_COLUMN_MODIFIED]);
                            } elseif ($childIdentifier && $childIdentifier !== '') {
                                $identifierFields = explode('-', $childIdentifier);
                                $recordKey = [];
                                foreach ($identifierFields as $field) {
                                    $recordKey[$field] = $record[$field] ?? null;
                                }
                            } else {
                                $recordKey = [SYNC_BY_RULE_DB_COLUMN_ID => $record[SYNC_BY_RULE_DB_COLUMN_ID] ?? null];
                            }
                            $allKeys[] = $recordKey;
                        }
                        $keyData = $allKeys;
                    } elseif ($childIdentifier && $childIdentifier !== '') {
                        if ($childIdentifier === '*') {
                            $keyData = $grandchildData;
                            unset($keyData[SYNC_BY_RULE_DB_COLUMN_ID], $keyData[SYNC_BY_RULE_DB_COLUMN_CREATED], $keyData[SYNC_BY_RULE_DB_COLUMN_MODIFIED]);
                        } else {
                            $identifierFields = explode('-', $childIdentifier);
                            $keyData = [];
                            foreach ($identifierFields as $field) {
                                $keyData[$field] = $grandchildData[$field] ?? null;
                            }
                        }
                    } else {
                        $keyData = [SYNC_BY_RULE_DB_COLUMN_ID => $grandchildData[SYNC_BY_RULE_DB_COLUMN_ID] ?? json_encode($grandchildData)];
                    }

                    $uniqueKey = $childTable . ':' . json_encode($keyData);
                    if (!isset($seenKeys[$uniqueKey])) {
                        $uniqueGrandchildren[] = $grandchild;
                        $seenKeys[$uniqueKey] = true;
                    }
                }
                $template[SYNC_BY_RULE_JSON_ELEMENT] = $this->groupChildElements($uniqueGrandchildren);
            }

            $result[] = $template;
        }

        return $result;
    }

    /**
     * Parse import overrides from JSON structure
     */
    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[SYNC_BY_RULE_JSON_ELEMENT])) {
            // Return empty array if no element key - might just have dataType
            return [];
        }

        return $this->flattenOverrideElements($overrideData[SYNC_BY_RULE_JSON_ELEMENT]);
    }

    /**
     * Flatten override elements recursively into table => data mapping
     */
    private function flattenOverrideElements(array $elements): array {
        $result = [];

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

            if (!$table) {
                continue;
            }

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

            // Handle both single object and array of data
            if (isset($data[0]) && is_array($data[0])) {
                $mergedData = [];
                foreach ($data as $dataRecord) {
                    $mergedData = array_merge($mergedData, $dataRecord);
                }
                $data = $mergedData;
            }

            if (!isset($result[$table])) {
                $result[$table] = [];
            }

            $result[$table] = array_merge($result[$table], $data);

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

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

        return $result;
    }

    /**
     * Import element recursively with DeployRef tracking
     */
    private function importElementRecursive(array $element, array $overrides, ?int $ruleId = null): array {
        $table = $element[SYNC_BY_RULE_JSON_TABLE];
        $dataInput = $element[SYNC_BY_RULE_JSON_DATA];
        $identifier = $element[SYNC_BY_RULE_JSON_IDENTIFIER] ?? null;
        $relation = $element[SYNC_BY_RULE_JSON_RELATION] ?? [];
        $updateMode = $this->normalizeUpdateMode($element[SYNC_BY_RULE_JSON_UPDATE] ?? SYNC_BY_RULE_UPDATE_ONCE);
        $keepId = $element[SYNC_BY_RULE_JSON_KEEP_ID] ?? false;

        $result = [
            SYNC_BY_RULE_JSON_TABLE => $table,
            SYNC_BY_RULE_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)
        $hasChildRelations = false;
        if (!empty($dataArray)) {
            $firstData = $dataArray[0];
            if (isset($overrides[$table])) {
                $firstData = array_merge($firstData, $overrides[$table]);
            }
            $hasChildRelations = $this->hasChildRelations($firstData, $element[SYNC_BY_RULE_JSON_ELEMENT] ?? []);
        }

        // Collect child results
        $childResults = [];

        if ($hasChildRelations) {
            // CHILD-FIRST STRATEGY
            $childrenImported = false;

            foreach ($dataArray as $dataIndex => $data) {
                $exportId = $data[SYNC_BY_RULE_DB_COLUMN_ID] ?? null;
                $exportModified = $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? null;

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

                $oldId = $exportId;

                // Map PARENT relation keys
                $data = $this->mapRelationKeys($data, $relation);

                $itemResult = [
                    SYNC_BY_RULE_ITEM_RESULT_ACTION => null,
                    SYNC_BY_RULE_ITEM_RESULT_OLD_ID => $oldId,
                    SYNC_BY_RULE_ITEM_RESULT_NEW_ID => null,
                ];

                // Import children first (for child relations)
                if (!$childrenImported && !empty($element[SYNC_BY_RULE_JSON_ELEMENT])) {
                    foreach ($element[SYNC_BY_RULE_JSON_ELEMENT] as $childElement) {
                        $childResult = $this->importElementRecursive($childElement, $overrides, $ruleId);
                        $childResults[] = $childResult;
                    }
                    $childrenImported = true;
                }

                // Map child relation keys
                $data = $this->mapChildRelationKeys($data, $element[SYNC_BY_RULE_JSON_ELEMENT] ?? []);

                // Store original data for content column (before id removal)
                $originalData = $data;

                // Remove id from data (will be handled by insert/update logic)
                unset($data[SYNC_BY_RULE_DB_COLUMN_ID]);

                // Apply deployment logic
                $deployResult = $this->applyDeploymentLogic(
                    $table,
                    $data,
                    $identifier,
                    $relation,
                    $updateMode,
                    $keepId,
                    $oldId,
                    $exportModified,
                    $ruleId,
                    $originalData
                );

                $itemResult[SYNC_BY_RULE_ITEM_RESULT_ACTION] = $deployResult[SYNC_BY_RULE_ITEM_RESULT_ACTION];
                $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID] = $deployResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID];

                // Update ID mapping
                if ($oldId && $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID]) {
                    $this->idMapping[$table][$oldId] = $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID];
                }

                $result[SYNC_BY_RULE_ITEMS][] = $itemResult;
            }

        } else {
            // PARENT-FIRST STRATEGY
            foreach ($dataArray as $dataIndex => $data) {
                $exportId = $data[SYNC_BY_RULE_DB_COLUMN_ID] ?? null;
                $exportModified = $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? null;

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

                $oldId = $exportId;

                // Map PARENT relation keys
                $data = $this->mapRelationKeys($data, $relation);

                // Store original data for content column (before id removal)
                $originalData = $data;

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

                $itemResult = [
                    SYNC_BY_RULE_ITEM_RESULT_ACTION => null,
                    SYNC_BY_RULE_ITEM_RESULT_OLD_ID => $oldId,
                    SYNC_BY_RULE_ITEM_RESULT_NEW_ID => null,
                ];

                // Apply deployment logic
                $deployResult = $this->applyDeploymentLogic(
                    $table,
                    $data,
                    $identifier,
                    $relation,
                    $updateMode,
                    $keepId,
                    $oldId,
                    $exportModified,
                    $ruleId,
                    $originalData
                );

                $itemResult[SYNC_BY_RULE_ITEM_RESULT_ACTION] = $deployResult[SYNC_BY_RULE_ITEM_RESULT_ACTION];
                $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID] = $deployResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID];

                // Update ID mapping
                if ($oldId && $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID]) {
                    $this->idMapping[$table][$oldId] = $itemResult[SYNC_BY_RULE_ITEM_RESULT_NEW_ID];
                }

                $result[SYNC_BY_RULE_ITEMS][] = $itemResult;
            }

            // Import children after parents
            if (!empty($element[SYNC_BY_RULE_JSON_ELEMENT])) {
                foreach ($element[SYNC_BY_RULE_JSON_ELEMENT] as $childElement) {
                    $childResult = $this->importElementRecursive($childElement, $overrides, $ruleId);
                    $childResults[] = $childResult;
                }
            }
        }

        // Add child results
        if (!empty($childResults)) {
            $result[SYNC_BY_RULE_CHILDREN] = $childResults;
        }

        return $result;
    }

    /**
     * Apply deployment logic based on DeployRef, identifier, and update mode
     *
     * Implements the complex matrix logic for insert/update/skip decisions
     *
     * @param string $table Table name
     * @param array $data Record data (without id)
     * @param string|null $identifier Identifier columns
     * @param array $relation Relation mapping
     * @param string $updateMode Update mode (once, always, skipModified)
     * @param bool $keepId Whether to try keeping original ID
     * @param int|null $srcId Source record ID from export
     * @param string|null $exportModified Modified timestamp from export
     * @param int|null $ruleId DeployRule ID
     * @param array $originalData Original record data from export (for content column)
     * @return array ['action' => string, 'newId' => int]
     * @throws Exception
     */
    private function applyDeploymentLogic(
        string $table,
        array $data,
        ?string $identifier,
        array $relation,
        string $updateMode,
        bool $keepId,
        ?int $srcId,
        ?string $exportModified,
        ?int $ruleId,
        array $originalData = []
    ): array {
        // Check if DeployRef entry exists for this source record (only if tracking enabled)
        $deployRef = null;
        if ($this->enableDeployTracking && $srcId && $ruleId) {
            $deployRef = $this->getDeployRef($table, $srcId, $ruleId);
        }

        // Find existing records by identifier
        $existingRecords = [];
        if ($identifier !== null && $identifier !== '') {
            $existingRecords = $this->findByIdentifierMulti($table, $data, $identifier, $relation);
        }

        $foundCount = count($existingRecords);
        $targetId = $foundCount === 1 ? $existingRecords[0][SYNC_BY_RULE_DB_COLUMN_ID] : null;
        $targetModified = $foundCount === 1 ? ($existingRecords[0][SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? null) : null;

        // Prepare content JSON for skipModified mode
        $contentJson = null;
        if ($updateMode === SYNC_BY_RULE_UPDATE_SKIP_MODIFIED && $this->enableDeployTracking && !empty($originalData)) {
            $contentJson = json_encode($originalData, JSON_UNESCAPED_UNICODE);
        }

        // Apply matrix logic
        $action = null;
        $newId = null;

        // Case 1: DeployRef exists (srcId > 0)
        if ($deployRef) {
            $deployTargetId = $deployRef[SYNC_BY_RULE_DB_COLUMN_TARGET_ID];
            $deployModified = $deployRef[SYNC_BY_RULE_DB_COLUMN_MODIFIED];

            if ($identifier === null || $identifier === '') {
                // Identifier undefined -> always insert
                $preferredId = ($keepId && $srcId) ? $srcId : null;
                $newId = $this->insertRecord($table, $data, $preferredId);
                $action = 'inserted';

                // Update DeployRef with new targetId
                if ($this->enableDeployTracking) {
                    $this->updateDeployRef($deployRef[SYNC_BY_RULE_DB_COLUMN_ID], $newId, $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'), $contentJson);
                }

            } else {
                // Identifier defined
                if ($foundCount === 0) {
                    // Not found -> no changes (record was deleted in target)
                    $action = 'skipped';
                    $newId = $deployTargetId;

                } elseif ($foundCount === 1) {
                    // Found exactly one
                    if ($updateMode === SYNC_BY_RULE_UPDATE_ONCE) {
                        // once -> no changes
                        $action = 'existing';
                        $newId = $targetId;

                    } elseif ($updateMode === SYNC_BY_RULE_UPDATE_ALWAYS) {
                        // always -> update
                        $this->updateRecord($table, $targetId, $data);
                        $action = 'updated';
                        $newId = $targetId;

                        // Update DeployRef
                        if ($this->enableDeployTracking) {
                            $this->updateDeployRef($deployRef[SYNC_BY_RULE_DB_COLUMN_ID], $newId, $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'), null);
                        }

                    } elseif ($updateMode === SYNC_BY_RULE_UPDATE_SKIP_MODIFIED) {
                        // skipModified -> compare timestamps
                        if ($this->isSourceNewer($exportModified, $targetModified)) {
                            $this->updateRecord($table, $targetId, $data);
                            $action = 'updated';
                            $newId = $targetId;

                            // Update DeployRef (respect skipModifiedActive setting)
                            if ($this->enableDeployTracking) {
                                $modifiedToStore = $this->skipModifiedActive
                                    ? ($data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'))
                                    : null; // null = don't update modified column
                                $this->updateDeployRef($deployRef[SYNC_BY_RULE_DB_COLUMN_ID], $newId, $modifiedToStore, $contentJson);
                            }
                        } else {
                            $action = 'skipped';
                            $newId = $targetId;

                            // Still update content column even when skipped
                            if ($this->enableDeployTracking && $contentJson !== null) {
                                $this->updateDeployRefContent($deployRef[SYNC_BY_RULE_DB_COLUMN_ID], $contentJson);
                            }
                        }
                    }

                } else {
                    // Multiple records found -> exception
                    throw new Exception("Multiple records found for identifier in table {$table} (DeployRef exists)");
                }
            }

        } else {
            // Case 2: No DeployRef (srcId not exists or first deployment)
            if ($identifier === null || $identifier === '') {
                // Identifier undefined -> always insert
                $preferredId = ($keepId && $srcId) ? $srcId : null;
                $newId = $this->insertRecord($table, $data, $preferredId);
                $action = 'inserted';

                // Create DeployRef entry
                if ($this->enableDeployTracking && $srcId && $ruleId) {
                    $this->createDeployRef($table, $srcId, $newId, $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'), $ruleId, $contentJson);
                }

            } else {
                // Identifier defined
                if ($foundCount === 0) {
                    // Not found -> insert
                    $preferredId = ($keepId && $srcId) ? $srcId : null;
                    $newId = $this->insertRecord($table, $data, $preferredId);
                    $action = 'inserted';

                    // Create DeployRef entry
                    if ($this->enableDeployTracking && $srcId && $ruleId) {
                        $this->createDeployRef($table, $srcId, $newId, $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'), $ruleId, $contentJson);
                    }

                } elseif ($foundCount === 1) {
                    // Found exactly one
                    if ($updateMode === SYNC_BY_RULE_UPDATE_ONCE) {
                        // once -> link existing record in DeployRef
                        $action = 'existing';
                        $newId = $targetId;

                        // Create DeployRef entry (link to existing)
                        if ($this->enableDeployTracking && $srcId && $ruleId) {
                            $this->createDeployRef($table, $srcId, $newId, $targetModified ?? date('Y-m-d H:i:s'), $ruleId, null);
                        }

                    } elseif ($updateMode === SYNC_BY_RULE_UPDATE_ALWAYS) {
                        // always -> update and create DeployRef
                        $this->updateRecord($table, $targetId, $data);
                        $action = 'updated';
                        $newId = $targetId;

                        // Create DeployRef entry
                        if ($this->enableDeployTracking && $srcId && $ruleId) {
                            $this->createDeployRef($table, $srcId, $newId, $data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'), $ruleId, null);
                        }

                    } elseif ($updateMode === SYNC_BY_RULE_UPDATE_SKIP_MODIFIED) {
                        // skipModified -> compare timestamps
                        if ($this->isSourceNewer($exportModified, $targetModified)) {
                            $this->updateRecord($table, $targetId, $data);
                            $action = 'updated';
                            $newId = $targetId;
                        } else {
                            $action = 'skipped';
                            $newId = $targetId;
                        }

                        // Create DeployRef entry (use target's modified for first entry if skipModifiedActive is false)
                        if ($this->enableDeployTracking && $srcId && $ruleId) {
                            $modifiedToStore = $this->skipModifiedActive
                                ? ($data[SYNC_BY_RULE_DB_COLUMN_MODIFIED] ?? date('Y-m-d H:i:s'))
                                : ($targetModified ?? date('Y-m-d H:i:s'));
                            $this->createDeployRef($table, $srcId, $newId, $modifiedToStore, $ruleId, $contentJson);
                        }
                    }

                } else {
                    // Multiple records found -> exception
                    throw new Exception("Multiple records found for identifier in table {$table} (no DeployRef)");
                }
            }
        }

        return [
            SYNC_BY_RULE_ITEM_RESULT_ACTION => $action,
            SYNC_BY_RULE_ITEM_RESULT_NEW_ID => $newId
        ];
    }

    /**
     * Get DeployRef entry for source record
     */
    private function getDeployRef(string $table, int $srcId, int $ruleId): ?array {
        $query = "SELECT id, targetId, modified FROM DeployRef 
                  WHERE tableName = ? AND srcId = ? AND ruleId = ? 
                  LIMIT 1";

        $result = $this->db->sql($query, ROW_REGULAR, [$table, $srcId, $ruleId]);

        return $result[0] ?? null;
    }

    /**
     * Create DeployRef entry
     *
     * @param string $table Table name
     * @param int $srcId Source record ID
     * @param int $targetId Target record ID
     * @param string $modified Modified timestamp
     * @param int $ruleId DeployRule ID
     * @param string|null $content JSON content of the record data (for skipModified mode)
     */
    private function createDeployRef(string $table, int $srcId, int $targetId, string $modified, int $ruleId, ?string $content = null): void {
        $query = "INSERT INTO DeployRef (tableName, srcId, targetId, modified, ruleId, content) 
                  VALUES (?, ?, ?, ?, ?, ?)";

        $this->db->sql($query, ROW_REGULAR, [$table, $srcId, $targetId, $modified, $ruleId, $content]);
    }

    /**
     * Update DeployRef entry
     *
     * @param int $deployRefId DeployRef ID
     * @param int $targetId Target record ID
     * @param string|null $modified Modified timestamp (null = don't update this column)
     * @param string|null $content JSON content of the record data (null = don't update this column)
     */
    private function updateDeployRef(int $deployRefId, int $targetId, ?string $modified, ?string $content = null): void {
        $setClauses = ['targetId = ?'];
        $params = [$targetId];

        // Only update modified if provided (respects skipModifiedActive setting)
        if ($modified !== null) {
            $setClauses[] = 'modified = ?';
            $params[] = $modified;
        }

        // Only update content if provided
        if ($content !== null) {
            $setClauses[] = 'content = ?';
            $params[] = $content;
        }

        $params[] = $deployRefId;

        $query = "UPDATE DeployRef SET " . implode(', ', $setClauses) . " WHERE id = ?";

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

    /**
     * Update only the content column of DeployRef entry
     *
     * @param int $deployRefId DeployRef ID
     * @param string $content JSON content of the record data
     */
    private function updateDeployRefContent(int $deployRefId, string $content): void {
        $query = "UPDATE DeployRef SET content = ? WHERE id = ?";
        $this->db->sql($query, ROW_REGULAR, [$content, $deployRefId]);
    }

    /**
     * Compare timestamps to determine if source is newer
     */
    private function isSourceNewer(?string $exportModified, ?string $targetModified): bool {
        if ($exportModified === null || $targetModified === null) {
            // If either timestamp is missing, consider source as newer
            return true;
        }

        $exportTime = strtotime($exportModified);
        $targetTime = strtotime($targetModified);

        return $exportTime > $targetTime;
    }

    /**
     * Find existing record by identifier columns (returns single result or null)
     */
    private function findByIdentifier(string $table, array $data, string $identifier, array $relation = []): ?int {
        $results = $this->findByIdentifierMulti($table, $data, $identifier, $relation);
        return $results[0][SYNC_BY_RULE_DB_COLUMN_ID] ?? null;
    }

    /**
     * Find existing records by identifier columns (returns array of matching records)
     */
    private function findByIdentifierMulti(string $table, array $data, string $identifier, array $relation = []): array {
        $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 = [SYNC_BY_RULE_DB_COLUMN_ID, SYNC_BY_RULE_DB_COLUMN_CREATED, SYNC_BY_RULE_DB_COLUMN_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 [];
        }

        // 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 [];
        }

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

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

        return $result;
    }

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

        $tableName = $this->escapeIdentifier($table);

        // Try to use preferred ID if provided and available
        if ($preferredId !== null && $this->isIdAvailable($table, $preferredId)) {
            $data[SYNC_BY_RULE_DB_COLUMN_ID] = $preferredId;
        }

        $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})";

        $insertId = $this->db->sql($query, ROW_REGULAR, $values);

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

        return $insertId;
    }

    /**
     * Update existing record in database
     */
    private function updateRecord(string $table, int $id, array $data): int {
        unset($data[SYNC_BY_RULE_DB_COLUMN_ID]);

        if (empty($data)) {
            return $id;
        }

        $tableName = $this->escapeIdentifier($table);
        $setClauses = [];
        $values = [];

        foreach ($data as $column => $value) {
            $setClauses[] = $this->escapeIdentifier($column) . ' = ?';
            $values[] = $value;
        }

        $values[] = $id;

        $query = "UPDATE {$tableName} SET " . implode(', ', $setClauses) . " WHERE id = ?";

        $this->db->sql($query, ROW_REGULAR, $values);

        return $id;
    }

    /**
     * Check if a specific ID is available (not used) in a table
     */
    private function isIdAvailable(string $table, int $id): bool {
        $tableName = $this->escapeIdentifier($table);

        $query = "SELECT COUNT(*) as count FROM {$tableName} WHERE id = ?";
        $result = $this->db->sql($query, ROW_REGULAR, [$id]);

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

        return (int)$result[0]['count'] === 0;
    }

    /**
     * Map relation keys from old IDs to new IDs (PARENT relations)
     */
    private function mapRelationKeys(array $data, array $relation): array {
        $mapped = $data;

        foreach ($relation as $column => $reference) {
            [$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)
     */
    private function mapChildRelationKeys(array $data, array $childElements): array {
        $mapped = $data;

        foreach ($childElements as $childElement) {
            $childTable = $childElement[SYNC_BY_RULE_JSON_TABLE];
            $childRelation = $childElement[SYNC_BY_RULE_JSON_RELATION] ?? [];

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

                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)
     */
    private function hasChildRelations(array $data, array $childElements): bool {
        foreach ($childElements as $childElement) {
            $childRelation = $childElement[SYNC_BY_RULE_JSON_RELATION] ?? [];

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

                if ($parentCol !== SYNC_BY_RULE_DB_COLUMN_ID && isset($data[$parentCol])) {
                    return true;
                }
            }
        }

        return false;
    }

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

    /**
     * Decrypt multiple rule SIPs and merge their definitions
     *
     * @param array $ruleSips Array of encrypted SIP strings
     * @return string Merged JSON rule definition
     */
    public static function mergeRuleSips(array $ruleSips): string {
        $sip = new Sip();
        $mergedElements = [];
        $mergedDataType = null;
        $mergedBaseUrlSrc = null;

        foreach ($ruleSips as $sipString) {
            // Decrypt the SIP to get stored parameters
            $params = $sip->getVarsFromSip($sipString);

            // Get the rule value from decrypted params
            $ruleJson = $params[SYNC_BY_RULE_VALUE] ?? null;

            if (empty($ruleJson)) {
                continue;
            }

            // Parse the rule JSON
            $rule = json_decode($ruleJson, true);

            if (json_last_error() !== JSON_ERROR_NONE) {
                continue;
            }

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

            // Merge dataType (first non-null wins)
            if ($mergedDataType === null && isset($rule[SYNC_BY_RULE_JSON_DATA_TYPE])) {
                $mergedDataType = $rule[SYNC_BY_RULE_JSON_DATA_TYPE];
            }

            // Merge baseUrlSrc (first non-null wins)
            if ($mergedBaseUrlSrc === null && isset($rule[SYNC_BY_RULE_JSON_BASE_URL_SRC])) {
                $mergedBaseUrlSrc = $rule[SYNC_BY_RULE_JSON_BASE_URL_SRC];
            }
        }

        // Build merged result
        $result = ['element' => $mergedElements];

        if ($mergedDataType !== null) {
            $result[SYNC_BY_RULE_JSON_DATA_TYPE] = $mergedDataType;
        }

        if ($mergedBaseUrlSrc !== null) {
            $result[SYNC_BY_RULE_JSON_BASE_URL_SRC] = $mergedBaseUrlSrc;
        }

        // Return merged rule as JSON string
        return json_encode($result);
    }
}

/**
 * Custom exception for SyncByRule validation errors
 */
class SyncByRuleValidationException extends Exception {
    private string $errorType;

    public function __construct(string $message, string $errorType, int $code = 0, ?\Throwable $previous = null) {
        parent::__construct($message, $code, $previous);
        $this->errorType = $errorType;
    }

    public function getErrorType(): string {
        return $this->errorType;
    }
}