<?php
declare(strict_types=1);
namespace Lcmigrator\LcMigrator\Importer\Database;

use Exception;
use Lcmigrator\LcMigrator\Database\DatabaseConnection;
use Lcmigrator\LcMigrator\Importer\QueryParser\SqlParser;
use Lcmigrator\LcMigrator\Logger\Logger;

class Importer {
    private $dbConnection;
    private $sqlParser;
    private $logger;
    private $replacements = 0;
    private $registry;

    public function __construct(DatabaseConnection $dbConnection, SqlParser $sqlParser, Logger $logger = null) {
        $this->dbConnection = $dbConnection;
        $this->sqlParser = $sqlParser;  
        $this->logger = $logger;
        $this->registry = new Registry();
    }

    public function importDatabase(string $sqlFile, string $oldUrl = '', string $newUrl = ''): bool {
        $errors = 0;
        try {
            $this->dbConnection->connect();
            $this->dbConnection->query("SET sql_mode = ''");
            $this->dbConnection->query('SET FOREIGN_KEY_CHECKS = 0');
            if (!$this->sqlParser->openFile($sqlFile)) {
                $this->logError("Failed to open SQL file");
                throw new Exception('Failed to open SQL file');
            }

            $this->registry->dbdumpsize = $this->sqlParser->getFileSize();
            $this->registry->old_url = $oldUrl;
            $this->registry->new_url = $newUrl;
            $this->registry->dbdumppos = $this->sqlParser->getPosition();

            $this->logSuccess('Starting import of SQL file: ' . basename($sqlFile));
            if ($oldUrl && $newUrl) {
                $this->logSuccess('URL migration: ' . $oldUrl . ' → ' . $newUrl);
            }

            while ($query = $this->sqlParser->getNextQuery()) {
                try {
                    $this->processQuery($query);
                } catch (Exception $e) {
                    $this->logError("Query failed: " . $e->getMessage());
                    $errors++;
                    continue;
                }
                $this->save_progress();
            }

            $this->dbConnection->query('SET FOREIGN_KEY_CHECKS = 1');
            if ($this->replacements > 0) {
                $this->logSuccess('Replacements made: ' . $this->replacements);
            }
            $message = $errors > 0 
                ? sprintf("Database import completed with %d errors", $errors)
                : "Database import completed successfully";
            $this->logSuccess($message);
            return $errors === 0;
        } catch (Exception $e) {
            $this->logError("Import failed: " . $e->getMessage());
            throw $e;
        }
    }

    private function processQuery(string $query): void {
        try {
            if ($this->registry->old_url && $this->registry->new_url) {
                $query = $this->replace($query);
            }
            $result = $this->dbConnection->query($query);
            if ($result === false) {
                throw new Exception("Query execution returned false");
            }
        } catch (Exception $e) {
            $this->logError("Query failed: " . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Perform replacements in query.
     *
     * @param string            $query    The database query to search and replace
     *
     * @return string The replaced query
     */
    private function replace(string $query): string
    {
        if (!$this->registry->old_url || !$this->registry->new_url) {
            return $query;
        }

        // Only operate on INSERT queries
        if (strtoupper(substr($query, 0, 6)) !== 'INSERT') {
            return $query;
        }

        // Cache the old query in case there is an error
        $old_query = $query;

        // Attempt to parse the query
        try {
            $matched = preg_match(
                '/^INSERT\s+INTO\s+.*\((?<fields>[^)]+)\)\s+VALUES\s*(?<values>.+)/is',
                $query,
                $matches,
                PREG_OFFSET_CAPTURE
            );

            if ($matched !== 1) {
                throw new Exception("Malformed query encountered; cannot perform replace. Query: " . $query);
            }

            $fields = preg_split('/,\s*/', $matches['fields'][0]);
            if ($fields === false) {
                throw new Exception("Malformed query encountered; cannot perform replace. Query: " . $query);
            }

            $fields = array_map(static function ($field): string {
                // Allow hierarchical fields
                $parts = explode('.', $field);

                return strtolower(trim(array_pop($parts), '`'));
            }, $fields);

            $position = $matches['values'][1];

            // Tokenize and process until query is parsed
            while ($position < \strlen($query)) {
                $query = $this->processNextToken($query, $position, $fields);
            }
        } catch (Exception $e) {
            // Always return original
            return $old_query;
        }

        return $query;
    }

    /**
     * Process next token in query.
     *
     * @param string            $query    The entire query being parsed
     * @param int               $position The current position of parsing
     * @param array<string>     $fields   The array of fields for this query
     *
     * @throws Exception If query is malformed
     *
     * @return string The query parsed at the next token
     */
    private function processNextToken(
        string $query,
        int &$position,
        array &$fields
    ): string {
        if (key($fields) === 0 && $query[$position] === '(') {
            ++$position;
        }

        $query = $this->processField($query, $position, $fields);

        // If next character is a comma, then we are moving to the next field
        if ($query[$position] === ',') {
            preg_match('/,\s*(?<remaining>.+)/sA', $query, $matches, PREG_OFFSET_CAPTURE, $position);
            $position = (int)$matches['remaining'][1];
            next($fields);
        } elseif ($query[$position] === ')') {
            // End of record
            ++$position;

            // If comma, we have another record
            if ($query[$position] === ',') {
                preg_match('/,\s*(?<remaining>.+)/sA', $query, $matches, PREG_OFFSET_CAPTURE, (int) $position);
                $position = (int)$matches['remaining'][1];
                reset($fields);
            } elseif ($query[$position] === ';') {
                ++$position;
            }
        }

        return $query;
    }

    /**
     * Process the next field.
     *
     * @param string            $query    The entire query being parsed
     * @param int               $position The current position of parsing
     * @param array<string>     $fields   The array of fields for this query
     *
     * @throws Exception If query is malformed
     *
     * @return string The query parsed at the next field
     */
    private function processField(
        string $query,
        int &$position,
        array $fields
    ): string {
        if (preg_match('/[^\'",)]+(?=,\s*|\))(?<remaining>.+)/sA', $query, $matches, PREG_OFFSET_CAPTURE, $position)) {
            // Non-string parameter, which we don't care about
            $position = (int)$matches['remaining'][1];
        } elseif (preg_match('/(?<value>([\'"]).?\2)(?=,\s*|\))(?<remaining>.+)/sA', $query, $matches, PREG_OFFSET_CAPTURE, $position)) {
            // One or zero characters in string value
            $position = (int)$matches['remaining'][1];
        } elseif (preg_match('/(?<value>([\'"]).+?[^\\\]\2)(?=,\s*|\))(?<remaining>.+)/sA', $query, $matches, 0, $position)) {
            $value = $matches['value'];

            if (current($fields) === 'guid') {
                // Skip any guid field
                $position += \strlen($value);
            } else {
                $new_value = $this->replaceValue(
                    $value
                );

                $query = substr_replace(
                    $query,
                    $new_value,
                    $position,
                    \strlen($value)
                );

                $position += \strlen($new_value);
            }
        } else {
            // Does not match, so error
            $this->logError("Malformed query encountered; cannot perform replace. Query: " . $query);
        }

        return $query;
    }

    /**
     * Perform replacement for migration on a specific value.
     *
     * @param string            $value    The value to search and replace
     *
     * @return string The replaced value
     */
    private function replaceValue(string $value): string
    {
        static $esc_old_url, $esc_new_url;
        if (!isset($esc_old_url)) {
            $esc_old_url = $this->dbConnection->escape($this->registry->old_url);
        }
        if (!isset($esc_new_url)) {
            $esc_new_url = $this->dbConnection->escape($this->registry->new_url);
        }

        // Fix serialized data first
        // It should be preceded by either ' or " (beginning of value in DB),
        // ; (from end of previous serialized value),
        // or { (from beginning of serialized array).
        // It should consist of: s:<length>:\"<value>\";
        // It should be followed by ' or " (end of value in DB),
        // } (end of serialized array),
        // or character denoting beginning of another serialized value.
        preg_match_all(
            '/(?<=[\'";{])(?<serialized>s:(?<length>\d+):\\\"(?<value>.+?)\\\";)(?=\'|"|\}|[sidbaO]:)/',
            $value,
            $matches,
            PREG_SET_ORDER
        );

        foreach ($matches as $match) {
            // Only replace if the old URL is contained within the value
            if (strpos($match['value'], $esc_old_url) === false) {
                continue;
            }

            $replaced = str_replace($esc_old_url, $esc_new_url, $match['value'], $count);
            $length = (int) $match['length']
                + ((\strlen($this->registry->new_url) - \strlen($this->registry->old_url))) * $count;
            $value = str_replace(
                $match['serialized'],
                "s:{$length}:\\\"{$replaced}\\\";",
                $value
            );
            $this->replacements += $count;
        }

        $value = str_replace(
            $esc_old_url,
            $esc_new_url,
            $value,
            $count
        );
        $this->replacements += $count;

        return $value;
    }

    /**
     * Save Restore Progress.
     *
     * Helper method for calculating the progress of DB file restore and saving it to registry.
     */
    private function save_progress(): void
    {
        $progress = (int) floor(($this->registry->dbdumppos['pos'] / $this->registry->dbdumpsize) * 100);
        $this->registry->migration_progress = $progress;
    }
    
    private function logError(string $message): void {
        if ($this->logger) {
            $this->logger->log($message, 'error');
        }
    }

    private function logSuccess(string $message): void {
        if ($this->logger) {
            $this->logger->log($message, 'INFO');
        }
    }
}

class Registry {
    public $dbdumpsize = 0;
    public $dbdumppos = null;
    public $migration_progress = 0;
    public $old_url = '';
    public $new_url = '';
}
