<?php

declare(strict_types=1);

$sqlitePath = __DIR__ . '/database/database.sqlite';

if (!file_exists($sqlitePath)) {
    exit("SQLite file not found: {$sqlitePath}\n");
}

$sqlite = new PDO('sqlite:' . $sqlitePath);
$mysql = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=gaga_api;charset=utf8mb4',
    'root',
    ''
);

$sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$tables = $sqlite->query("
    SELECT name
    FROM sqlite_master
    WHERE type = 'table'
      AND name NOT LIKE 'sqlite_%'
")->fetchAll(PDO::FETCH_COLUMN);

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

foreach ($tables as $table) {
    $safeTable = str_replace('`', '``', $table);

    $exists = $mysql
        ->query("SHOW TABLES LIKE " . $mysql->quote($table))
        ->fetchColumn();

    if (!$exists) {
        echo "SKIP {$table}: table not found in MySQL\n";
        continue;
    }

    $rows = $sqlite
        ->query('SELECT * FROM "' . str_replace('"', '""', $table) . '"')
        ->fetchAll(PDO::FETCH_ASSOC);

    if (!$rows) {
        echo "EMPTY {$table}\n";
        continue;
    }

    $mysqlColumns = $mysql
        ->query("SHOW COLUMNS FROM `{$safeTable}`")
        ->fetchAll(PDO::FETCH_COLUMN);

    $sourceColumns = array_keys($rows[0]);
    $columns = array_values(array_intersect($sourceColumns, $mysqlColumns));

    if (!$columns) {
        echo "SKIP {$table}: no matching columns\n";
        continue;
    }

    $columnSql = implode(
        ', ',
        array_map(
            fn (string $column): string => '`' . str_replace('`', '``', $column) . '`',
            $columns
        )
    );

    $placeholders = implode(', ', array_fill(0, count($columns), '?'));

    $statement = $mysql->prepare(
        "INSERT INTO `{$safeTable}` ({$columnSql}) VALUES ({$placeholders})"
    );

    $mysql->beginTransaction();

    try {
        $mysql->exec("DELETE FROM `{$safeTable}`");

        foreach ($rows as $row) {
            $values = array_map(
                fn (string $column) => $row[$column] ?? null,
                $columns
            );

            $statement->execute($values);
        }

        $mysql->commit();
        echo "COPIED {$table}: " . count($rows) . " rows\n";
    } catch (Throwable $e) {
        $mysql->rollBack();
        echo "ERROR {$table}: {$e->getMessage()}\n";
    }
}

$mysql->exec('SET FOREIGN_KEY_CHECKS=1');

echo "Done.\n"