<?php

namespace App\Support;

use PDO;
use RuntimeException;

class SqliteToMysqlDumpExporter
{
    public function export(string $sqlitePath, string $outputDir, string $databaseName = 'saleemsial_cms'): string
    {
        if (! file_exists($sqlitePath)) {
            throw new RuntimeException("SQLite database not found at {$sqlitePath}");
        }

        $sqlite = new PDO('sqlite:' . $sqlitePath);
        $sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $this->exportFromPdo($sqlite, $outputDir, $databaseName);
    }

    public function exportFromPdo(PDO $sqlite, string $outputDir, string $databaseName = 'saleemsial_cms'): string
    {
        if (! is_dir($outputDir) && ! mkdir($outputDir, 0777, true) && ! is_dir($outputDir)) {
            throw new RuntimeException("Unable to create backup directory at {$outputDir}");
        }

        $outputPath = rtrim($outputDir, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . $databaseName . '_' . date('Y-m-d_His') . '.sql';

        $tablesWithoutData = [
            'cache',
            'cache_locks',
            'failed_jobs',
            'job_batches',
            'jobs',
            'sessions',
            'visits',
        ];

        $tables = $sqlite->query("
            SELECT name, sql
            FROM sqlite_master
            WHERE type = 'table'
              AND name NOT LIKE 'sqlite_%'
            ORDER BY CASE WHEN name = 'migrations' THEN 0 ELSE 1 END, name
        ")->fetchAll(PDO::FETCH_ASSOC);

        $sql = [];
        $sql[] = '-- Saleem Sial Analytics MySQL backup';
        $sql[] = '-- Generated from SQLite on ' . date('Y-m-d H:i:s');
        $sql[] = 'SET NAMES utf8mb4;';
        $sql[] = 'SET FOREIGN_KEY_CHECKS=0;';
        $sql[] = 'CREATE DATABASE IF NOT EXISTS `' . $databaseName . '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;';
        $sql[] = 'USE `' . $databaseName . '`;';
        $sql[] = '';

        $deferredForeignKeys = [];

        foreach ($tables as $table) {
            $name = $table['name'];
            $createSql = (string) $table['sql'];

            $columns = $sqlite->query('PRAGMA table_info("' . str_replace('"', '""', $name) . '")')->fetchAll(PDO::FETCH_ASSOC);
            $indexes = $sqlite->query('PRAGMA index_list("' . str_replace('"', '""', $name) . '")')->fetchAll(PDO::FETCH_ASSOC);
            $foreignKeys = $sqlite->query('PRAGMA foreign_key_list("' . str_replace('"', '""', $name) . '")')->fetchAll(PDO::FETCH_ASSOC);

            $columnDefinitions = [];
            $primaryColumns = [];

            foreach ($columns as $column) {
                $columnName = $column['name'];
                $sqliteType = strtoupper((string) $column['type']);
                $mysqlType = $this->mapSqliteTypeToMysql($sqliteType, $columnName);
                $nullable = (int) $column['notnull'] === 0 ? ' NULL' : ' NOT NULL';
                $default = $this->formatDefaultValue($column['dflt_value'], $mysqlType);
                $isPrimary = (int) $column['pk'] > 0;
                $autoIncrement = $this->shouldAutoIncrement($createSql, $sqliteType, $columnName, $isPrimary);

                $definition = '  `' . $columnName . '` ' . $mysqlType;

                if ($autoIncrement) {
                    $definition .= ' NOT NULL AUTO_INCREMENT';
                } else {
                    $definition .= $nullable;
                }

                if ($default !== '') {
                    $definition .= ' DEFAULT ' . $default;
                }

                $columnDefinitions[] = $definition;

                if ($isPrimary) {
                    $primaryColumns[(int) $column['pk']] = '`' . $columnName . '`';
                }
            }

            ksort($primaryColumns);

            if ($primaryColumns !== []) {
                $columnDefinitions[] = '  PRIMARY KEY (' . implode(', ', $primaryColumns) . ')';
            }

            foreach ($indexes as $index) {
                $indexName = $index['name'];
                if ($index['origin'] === 'pk' || str_starts_with($indexName, 'sqlite_autoindex')) {
                    continue;
                }

                $indexColumns = $sqlite->query('PRAGMA index_info("' . str_replace('"', '""', $indexName) . '")')->fetchAll(PDO::FETCH_ASSOC);
                if ($indexColumns === []) {
                    continue;
                }

                usort($indexColumns, static fn (array $a, array $b): int => $a['seqno'] <=> $b['seqno']);
                $columnList = implode(', ', array_map(static fn (array $item): string => '`' . $item['name'] . '`', $indexColumns));
                $prefix = (int) $index['unique'] === 1 ? '  UNIQUE KEY ' : '  KEY ';
                $columnDefinitions[] = $prefix . '`' . $indexName . '` (' . $columnList . ')';
            }

            if ($foreignKeys !== []) {
                $groupedForeignKeys = [];

                foreach ($foreignKeys as $foreignKey) {
                    $groupedForeignKeys[$foreignKey['id']][] = $foreignKey;
                }

                foreach ($groupedForeignKeys as $groupId => $groupRows) {
                    usort($groupRows, static fn (array $a, array $b): int => $a['seq'] <=> $b['seq']);

                    $fromColumns = implode(', ', array_map(static fn (array $item): string => '`' . $item['from'] . '`', $groupRows));
                    $toColumns = implode(', ', array_map(static fn (array $item): string => '`' . $item['to'] . '`', $groupRows));
                    $referenceTable = $groupRows[0]['table'];
                    $constraintName = 'fk_' . $name . '_' . $groupId;
                    $constraint = 'ALTER TABLE `' . $name . '` ADD CONSTRAINT `' . $constraintName . '` FOREIGN KEY (' . $fromColumns . ') REFERENCES `' . $referenceTable . '` (' . $toColumns . ')';

                    if (! empty($groupRows[0]['on_update']) && strtoupper((string) $groupRows[0]['on_update']) !== 'NO ACTION') {
                        $constraint .= ' ON UPDATE ' . strtoupper((string) $groupRows[0]['on_update']);
                    }

                    if (! empty($groupRows[0]['on_delete']) && strtoupper((string) $groupRows[0]['on_delete']) !== 'NO ACTION') {
                        $constraint .= ' ON DELETE ' . strtoupper((string) $groupRows[0]['on_delete']);
                    }

                    $deferredForeignKeys[] = $constraint . ';';
                }
            }

            $sql[] = 'DROP TABLE IF EXISTS `' . $name . '`;';
            $sql[] = 'CREATE TABLE `' . $name . '` (';
            $sql[] = implode(",\n", $columnDefinitions);
            $sql[] = ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;';
            $sql[] = '';

            if (in_array($name, $tablesWithoutData, true)) {
                continue;
            }

            $rows = $sqlite->query('SELECT * FROM "' . str_replace('"', '""', $name) . '"')->fetchAll(PDO::FETCH_ASSOC);
            if ($rows === []) {
                continue;
            }

            $columnNames = array_keys($rows[0]);
            $quotedColumns = implode(', ', array_map(static fn (string $columnName): string => '`' . $columnName . '`', $columnNames));

            foreach (array_chunk($rows, 100) as $chunk) {
                $valueSets = [];

                foreach ($chunk as $row) {
                    $values = [];
                    foreach ($columnNames as $columnName) {
                        $values[] = $this->exportValue($row[$columnName] ?? null);
                    }
                    $valueSets[] = '(' . implode(', ', $values) . ')';
                }

                $sql[] = 'INSERT INTO `' . $name . '` (' . $quotedColumns . ') VALUES';
                $sql[] = implode(",\n", $valueSets) . ';';
                $sql[] = '';
            }
        }

        $sql[] = '-- Deferred foreign keys';
        foreach ($deferredForeignKeys as $foreignKeySql) {
            $sql[] = $foreignKeySql;
        }
        $sql[] = '';
        $sql[] = 'SET FOREIGN_KEY_CHECKS=1;';
        $sql[] = '';

        file_put_contents($outputPath, implode(PHP_EOL, $sql));

        return $outputPath;
    }

    private function mapSqliteTypeToMysql(string $sqliteType, string $columnName): string
    {
        $normalized = trim($sqliteType);
        $isIdentifier = $columnName === 'id' || str_ends_with($columnName, '_id');

        if ($normalized === '') {
            return 'LONGTEXT';
        }

        if ($normalized === 'VARCHAR') {
            return 'VARCHAR(255)';
        }

        if (preg_match('/^VARCHAR\((\d+)\)$/i', $normalized, $match)) {
            return 'VARCHAR(' . $match[1] . ')';
        }

        if ($normalized === 'CHAR') {
            return 'CHAR(255)';
        }

        if (preg_match('/^CHAR\((\d+)\)$/i', $normalized, $match)) {
            return 'CHAR(' . $match[1] . ')';
        }

        if (preg_match('/^DECIMAL\((\d+),\s*(\d+)\)$/i', $normalized, $match)) {
            return 'DECIMAL(' . $match[1] . ', ' . $match[2] . ')';
        }

        if (str_contains($normalized, 'BIGINT')) {
            return $isIdentifier ? 'BIGINT UNSIGNED' : 'BIGINT';
        }

        if (str_contains($normalized, 'TINYINT')) {
            return 'TINYINT';
        }

        if (str_contains($normalized, 'SMALLINT')) {
            return 'SMALLINT';
        }

        if (str_contains($normalized, 'INT')) {
            return $isIdentifier ? 'BIGINT UNSIGNED' : 'INT';
        }

        if ($normalized === 'BOOLEAN') {
            return 'TINYINT(1)';
        }

        if (str_contains($normalized, 'JSON')) {
            return 'JSON';
        }

        if (str_contains($normalized, 'LONGTEXT')) {
            return 'LONGTEXT';
        }

        if (str_contains($normalized, 'TEXT')) {
            return 'TEXT';
        }

        if (str_contains($normalized, 'BLOB')) {
            return 'LONGBLOB';
        }

        if (str_contains($normalized, 'DOUBLE')) {
            return 'DOUBLE';
        }

        if (str_contains($normalized, 'FLOAT')) {
            return 'FLOAT';
        }

        if ($normalized === 'DATE') {
            return 'DATE';
        }

        if ($normalized === 'DATETIME') {
            return 'DATETIME';
        }

        if (str_contains($normalized, 'TIMESTAMP')) {
            return 'TIMESTAMP';
        }

        return 'LONGTEXT';
    }

    private function formatDefaultValue(mixed $value, string $mysqlType): string
    {
        if ($value === null) {
            return '';
        }

        $trimmed = trim((string) $value);
        if ($trimmed === '') {
            return "''";
        }

        $upper = strtoupper($trimmed);
        if (in_array($upper, ['CURRENT_TIMESTAMP', 'NULL'], true)) {
            return $upper;
        }

        if (preg_match('/^-?\d+(\.\d+)?$/', $trimmed) === 1) {
            return $trimmed;
        }

        if ($trimmed[0] === "'" && str_ends_with($trimmed, "'")) {
            $trimmed = substr($trimmed, 1, -1);
            $trimmed = str_replace("''", "'", $trimmed);
        }

        return "'" . str_replace("'", "''", $trimmed) . "'";
    }

    private function shouldAutoIncrement(string $createSql, string $sqliteType, string $columnName, bool $isPrimary): bool
    {
        if (! $isPrimary || $columnName !== 'id' || ! str_contains(strtoupper($sqliteType), 'INT')) {
            return false;
        }

        return str_contains(strtoupper($createSql), 'AUTOINCREMENT')
            || preg_match('/\b`?id`?\s+integer\s+primary\s+key\b/i', $createSql) === 1;
    }

    private function exportValue(mixed $value): string
    {
        if ($value === null) {
            return 'NULL';
        }

        if (is_bool($value)) {
            return $value ? '1' : '0';
        }

        if (is_int($value) || is_float($value)) {
            return (string) $value;
        }

        $stringValue = (string) $value;

        return "'" . str_replace(
            ["\\", "'", "\0", "\n", "\r", "\x1a"],
            ["\\\\", "\\'", "\\0", "\\n", "\\r", "\\Z"],
            $stringValue
        ) . "'";
    }
}
