<?php
namespace Installer;

use PDO;
use Exception;

class SqlImporter
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function importFromFile(string $sqlFile): void
    {
        if (!file_exists($sqlFile)) {
            throw new Exception("SQL file not found: " . $sqlFile);
        }

        $sql = file_get_contents($sqlFile);
        if ($sql === false) {
            throw new Exception("Unable to read SQL file: " . $sqlFile);
        }

        // Remove SQL comments
        $sql = preg_replace('/^\s*--.*$/m', '', $sql);
        $sql = preg_replace('#/\*.*?\*/#s', '', $sql);

        $statements = $this->splitStatements($sql);
        $allowedInserts = ['settings', 'theme_settings'];

        $this->pdo->exec('SET FOREIGN_KEY_CHECKS = 0');

        try {
            foreach ($statements as $stmt) {
                $stmt = trim($stmt);

                if ($stmt === '') {
                    continue;
                }

                $stmt = $this->normalizeStatement($stmt, $allowedInserts);

                if ($stmt === '') {
                    continue;
                }

                try {
                    $this->pdo->exec($stmt);
                } catch (\PDOException $e) {
                    if ($this->shouldIgnoreImportError($e)) {
                        continue;
                    }

                    throw $e;
                }
            }
        } finally {
            $this->pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
        }
    }

    private function normalizeStatement(string $stmt, array $allowedInserts): string
    {
        // Normalize selected INSERT statements to INSERT IGNORE
        if (preg_match('/^\s*INSERT\s+INTO\s+`?([a-zA-Z0-9_]+)`?/i', $stmt, $m)) {
            $table = strtolower($m[1]);

            if (!in_array($table, $allowedInserts, true)) {
                return $stmt;
            }

            if (preg_match('/^\s*INSERT\s+IGNORE\s+INTO\b/i', $stmt)) {
                return $stmt;
            }

            return preg_replace('/^\s*INSERT\s+INTO\b/i', 'INSERT IGNORE INTO', $stmt, 1) ?: $stmt;
        }

        // Normalize CREATE TABLE to CREATE TABLE IF NOT EXISTS only if missing
        if (preg_match('/^\s*CREATE\s+TABLE\s+/i', $stmt)) {
            if (preg_match('/^\s*CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+/i', $stmt)) {
                return $stmt;
            }

            return preg_replace('/^\s*CREATE\s+TABLE\s+/i', 'CREATE TABLE IF NOT EXISTS ', $stmt, 1) ?: $stmt;
        }

        return $stmt;
    }

    private function shouldIgnoreImportError(\PDOException $e): bool
    {
        $sqlState   = strtoupper((string) ($e->getCode() ?? ''));
        $driverCode = isset($e->errorInfo[1]) ? (int) $e->errorInfo[1] : 0;
        $message    = strtolower($e->getMessage());

        // Common MySQL/MariaDB duplicate / already exists cases
        if (in_array($driverCode, [1050, 1060, 1061, 1091, 1826], true)) {
            return true;
        }

        if (in_array($sqlState, ['42S01', '42S21'], true)) {
            return true;
        }

        foreach ([
            'already exists',
            'duplicate column name',
            'duplicate key name',
            'duplicate foreign key',
            "can't drop",
            'check that column/key exists',
        ] as $needle) {
            if (strpos($message, $needle) !== false) {
                return true;
            }
        }

        return false;
    }

    private function splitStatements(string $sql): array
    {
        $out = [];
        $buf = '';
        $inString = false;
        $quote = '';

        $len = strlen($sql);

        for ($i = 0; $i < $len; $i++) {
            $c = $sql[$i];

            if (($c === "'" || $c === '"')) {
                if (!$inString) {
                    $inString = true;
                    $quote = $c;
                } else {
                    $prev = $sql[$i - 1] ?? '';
                    if ($quote === $c && $prev !== '\\') {
                        $inString = false;
                        $quote = '';
                    }
                }
            }

            if ($c === ';' && !$inString) {
                $out[] = $buf;
                $buf = '';
            } else {
                $buf .= $c;
            }
        }

        if (trim($buf) !== '') {
            $out[] = $buf;
        }

        return $out;
    }
}