<?php

declare(strict_types=1);

namespace MeshVault\Tests\Integration;

use PDO;
use PDOException;
use PHPUnit\Framework\Attributes\DataProvider;
use PHPUnit\Framework\TestCase;
use RuntimeException;

final class CatalogMigrationTest extends TestCase
{
    #[DataProvider('badPaths')]
    public function testUpgradePreflightLeavesEverythingIntact(string $path, bool $duplicate): void
    {
        if ($duplicate) {
            $this->pdo->exec("INSERT INTO files (id, model_id, folder_id, name, managed_path)
                VALUES (31, 21, 10, 'other', 'parts/part.stl')");
        } else {
            $statement = $this->pdo->prepare('UPDATE files SET managed_path = ? WHERE id = 30');
            $statement->execute([$path]);
        }
        $this->assertPreflightFails(false, $duplicate ? ['30', '31'] : ['30']);
    }

    public static function badPaths(): array
    {
        return [['parts/part.stl', true], ['a/../part.stl', false], ['a//part.stl', false], ['C:/part.stl', false]];
    }

    #[DataProvider('downgradeConflicts')]
    public function testDowngradePreflightLeavesEverythingIntact(bool $unassigned): void
    {
        RepositoryDatabase::migrateTo($this->path, self::NEW);
        if ($unassigned) {
            $this->pdo->exec('UPDATE files SET model_id = NULL WHERE id = 30');
        } else {
            $this->pdo->exec("UPDATE models SET name = 'First' WHERE id = 21");
        }
        $this->assertPreflightFails(true, $unassigned ? ['30'] : ['20', '21']);
    }

    public static function downgradeConflicts(): array
    {
        return [[true], [false]];
    }

    private function assertPreflightFails(bool $down, array $ids): void
    {
        $before = $this->snapshot();
        try {
            if ($down) {
                RepositoryDatabase::rollbackTo($this->path, self::OLD);
            } else {
                RepositoryDatabase::migrateTo($this->path, self::NEW);
            }
            self::fail('Expected preflight rejection');
        } catch (RuntimeException $error) {
            foreach ($ids as $id) {
                self::assertStringContainsString($id, $error->getMessage());
            }
            self::assertStringContainsString('Resolve', $error->getMessage());
        }
        self::assertSame($before, $this->snapshot());
    }

    private function rows(): array
    {
        $rows = [];
        $tables = ['folders', 'models', 'files', 'tags', 'model_tags', 'thumbnails', 'import_jobs', 'import_items'];
        foreach ($tables as $table) {
            $rows[$table] = $this->pdo->query('SELECT * FROM ' . $table . ' ORDER BY 1')->fetchAll(PDO::FETCH_ASSOC);
        }
        return $rows;
    }

    private function indexes(): array
    {
        $indexes = [];
        if ($this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite') {
            foreach (['files', 'models'] as $table) {
                foreach ($this->pdo->query('PRAGMA index_list(' . $table . ')')->fetchAll(PDO::FETCH_ASSOC) as $row) {
                    $indexes[$table][$row['name']] = (int) $row['unique'];
                }
                ksort($indexes[$table]);
            }
        }
        return $indexes;
    }

    private function snapshot(): array
    {
        $snapshot = [$this->rows(), $this->pdo->query('SELECT * FROM phinxlog ORDER BY version')->fetchAll()];
        if ($this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite') {
            $snapshot[] = $this->pdo->query('SELECT * FROM sqlite_master ORDER BY type, name')->fetchAll();
            foreach (['files', 'models'] as $table) {
                $snapshot[] = $this->pdo->query('PRAGMA foreign_key_list(' . $table . ')')->fetchAll();
                $snapshot[] = $this->pdo->query('PRAGMA index_list(' . $table . ')')->fetchAll();
            }
        }
        return $snapshot;
    }

    private function assertRejected(string $sql): void
    {
        try {
            $this->pdo->exec($sql);
            self::fail('Expected database constraint rejection');
        } catch (PDOException) {
            self::assertTrue(true);
        }
    }

    private const OLD = 20261011120006;
    private const NEW = 20261012120001;
    private string $directory;
    private string $path;
    private PDO $pdo;

    protected function setUp(): void
    {
        $this->directory = RepositoryDatabase::directory('catalog-migration');
        $this->path = $this->directory . '/catalog.sqlite';
        $this->pdo = RepositoryDatabase::migrate($this->path, self::OLD);
        $this->pdo->exec("INSERT INTO folders (id, name, path) VALUES (10, 'root', '/')");
        $this->pdo->exec("INSERT INTO models (id, folder_id, name, notes) VALUES
            (20, 10, 'First', 'keep'), (21, 10, 'Second', 'also keep')");
        $this->pdo->exec("INSERT INTO files (id, model_id, folder_id, name, managed_path, hash, size)
            VALUES (30, 20, 10, 'part.stl', 'parts/part.stl', 'abc', 123)");
        // Explicit fixture IDs do not advance PostgreSQL sequences.
        if ($this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'pgsql') {
            foreach (['folders' => 10, 'models' => 21, 'files' => 30] as $table => $id) {
                $this->pdo->query("SELECT setval(pg_get_serial_sequence('$table', 'id'), $id, true)")->closeCursor();
            }
        }
        $this->pdo->exec("INSERT INTO tags (id, name) VALUES (40, 'tag')");
        $this->pdo->exec('INSERT INTO model_tags (model_id, tag_id) VALUES (20, 40)');
        $this->pdo->exec("INSERT INTO thumbnails (model_id, managed_path, hash, size, mime_type)
            VALUES (20, 'thumb.png', 'thumbhash', 12, 'image/png')");
        $this->pdo->exec("INSERT INTO import_jobs (id, source_path) VALUES (50, 'source')");
        $this->pdo->exec("INSERT INTO import_items (job_id, source_path, model_id) VALUES (50, 'part.stl', 20)");
    }

    protected function tearDown(): void
    {
        unset($this->pdo);
        RepositoryDatabase::cleanup($this->path);
        RepositoryDatabase::remove($this->directory);
    }

    public function testRoundTripPreservesRowsAndConstraints(): void
    {
        $before = $this->rows();
        $oldIndexes = $this->indexes();
        RepositoryDatabase::migrateTo($this->path, self::NEW);
        $newIndexes = $this->indexes();
        if ($newIndexes !== []) {
            self::assertSame(1, $newIndexes['files']['idx_files_managed_path']);
            self::assertSame(0, $newIndexes['files']['idx_files_model_id']);
            self::assertSame(0, $newIndexes['models']['idx_models_folder_name']);
            foreach (['idx_files_hash', 'idx_files_status', 'idx_files_folder_id'] as $index) {
                self::assertSame($oldIndexes['files'][$index], $newIndexes['files'][$index]);
            }
            self::assertSame(
                $oldIndexes['models']['idx_models_added_at'],
                $newIndexes['models']['idx_models_added_at']
            );
        }
        self::assertSame($before, $this->rows());
        $this->assertRejected("INSERT INTO files (model_id, folder_id, name, managed_path)
            VALUES (NULL, 10, 'duplicate', 'parts/part.stl')");
        $this->assertRejected('UPDATE files SET model_id = 999 WHERE id = 30');
        $this->assertRejected('DELETE FROM models WHERE id = 20');
        $this->assertRejected('DELETE FROM folders WHERE id = 10');
        $this->pdo->exec('UPDATE files SET model_id = NULL WHERE id = 30');
        $this->assertRejected("INSERT INTO files (model_id, folder_id, name, managed_path)
            VALUES (NULL, 10, 'duplicate', 'parts/part.stl')");
        $this->pdo->exec('UPDATE files SET model_id = 20 WHERE id = 30');
        RepositoryDatabase::rollbackTo($this->path, self::OLD);
        self::assertSame($oldIndexes, $this->indexes());
        self::assertSame($before, $this->rows());
        $this->assertRejected('UPDATE files SET model_id = NULL WHERE id = 30');
        $this->assertRejected("INSERT INTO models (folder_id, name) VALUES (10, 'First')");
        RepositoryDatabase::migrateTo($this->path, self::NEW);
        self::assertSame($before, $this->rows());
        $model = $this->pdo->query("INSERT INTO models (folder_id, name) VALUES (10, 'First') RETURNING id");
        self::assertGreaterThan(21, (int) $model->fetchColumn());
        $model->closeCursor();
        $file = $this->pdo->query("INSERT INTO files (model_id, folder_id, name, managed_path)
            VALUES (NULL, 10, 'new', 'new.stl') RETURNING id");
        self::assertGreaterThan(30, (int) $file->fetchColumn());
        $file->closeCursor();
        $this->pdo->exec('UPDATE files SET model_id = NULL WHERE id = 30');
        $this->pdo->exec('DELETE FROM models WHERE id = 20');
        self::assertSame(2, (int) $this->pdo->query('SELECT COUNT(*) FROM files')->fetchColumn());
        if ($this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite') {
            self::assertSame([], $this->pdo->query('PRAGMA foreign_key_check')->fetchAll());
        }
    }
}