irdb/api/tests/Integration/MigrationsTest.php

<?php

declare(strict_types=1);

namespace App\Tests\Integration;

use App\Infrastructure\Db\ConnectionFactory;
use Doctrine\DBAL\Connection;
use Phinx\Config\Config;
use Phinx\Migration\Manager;
use PHPUnit\Framework\TestCase;
use Symfony\Component\Console\Input\ArrayInput;
use Symfony\Component\Console\Output\NullOutput;

/**
 * Boots an in-memory SQLite database, runs every Phinx migration against it,
 * and asserts that the resulting schema has every SPEC §4 table with the
 * expected key columns. This catches gross mistakes (missing tables, missing
 * binary columns) without depending on MySQL being available locally.
 */
final class MigrationsTest extends TestCase
{
    private string $sqlitePath;
    private Connection $connection;

    protected function setUp(): void
    {
        $this->sqlitePath = sys_get_temp_dir() . '/irdb-migrations-' . bin2hex(random_bytes(6)) . '.sqlite';

        $config = new Config([
            'paths' => [
                'migrations' => __DIR__ . '/../../db/migrations',
                'seeds' => __DIR__ . '/../../db/seeds',
            ],
            'environments' => [
                'default_migration_table' => 'phinxlog',
                'default_environment' => 'test',
                'test' => [
                    'adapter' => 'sqlite',
                    'name' => $this->sqlitePath,
                    'suffix' => '',
                ],
            ],
            'version_order' => 'creation',
        ]);

        $manager = new Manager($config, new ArrayInput([]), new NullOutput());
        $manager->migrate('test');
        $manager->seed('test');

        $factory = new ConnectionFactory([
            'driver' => 'sqlite',
            'sqlite_path' => $this->sqlitePath,
            'mysql_host' => '',
            'mysql_port' => 3306,
            'mysql_database' => '',
            'mysql_username' => '',
            'mysql_password' => '',
        ]);
        $this->connection = $factory->create();
    }

    protected function tearDown(): void
    {
        $this->connection->close();
        if (file_exists($this->sqlitePath)) {
            @unlink($this->sqlitePath);
        }
    }

    public function testEverySpecTableExists(): void
    {
        $expected = [
            'users',
            'oidc_role_mappings',
            'reporters',
            'consumers',
            'policies',
            'policy_category_thresholds',
            'categories',
            'api_tokens',
            'reports',
            'ip_scores',
            'ip_enrichment',
            'manual_blocks',
            'allowlist',
            'audit_log',
            'job_locks',
            'job_runs',
        ];

        $rows = $this->connection
            ->executeQuery("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")
            ->fetchAllAssociative();
        $tables = array_column($rows, 'name');

        foreach ($expected as $name) {
            self::assertContains($name, $tables, "Missing table: {$name}");
        }
    }

    public function testReportsHasExpectedColumns(): void
    {
        $columns = $this->columnsOf('reports');

        foreach (['id', 'ip_bin', 'ip_text', 'category_id', 'reporter_id', 'weight_at_report', 'received_at', 'metadata_json'] as $col) {
            self::assertArrayHasKey($col, $columns, "reports missing column {$col}");
        }
    }

    public function testIpScoresHasCompositePrimaryKey(): void
    {
        $rows = $this->connection
            ->executeQuery('PRAGMA table_info(ip_scores)')
            ->fetchAllAssociative();

        $pkCols = [];
        foreach ($rows as $r) {
            if ((int) $r['pk'] > 0) {
                $pkCols[(int) $r['pk']] = $r['name'];
            }
        }
        ksort($pkCols);

        self::assertSame(['ip_bin', 'category_id'], array_values($pkCols));
    }

    public function testPolicyCategoryThresholdsHasCompositePrimaryKey(): void
    {
        $rows = $this->connection
            ->executeQuery('PRAGMA table_info(policy_category_thresholds)')
            ->fetchAllAssociative();

        $pkCols = [];
        foreach ($rows as $r) {
            if ((int) $r['pk'] > 0) {
                $pkCols[(int) $r['pk']] = $r['name'];
            }
        }
        ksort($pkCols);

        self::assertSame(['policy_id', 'category_id'], array_values($pkCols));
    }

    public function testJobLocksPkIsJobName(): void
    {
        $rows = $this->connection
            ->executeQuery('PRAGMA table_info(job_locks)')
            ->fetchAllAssociative();

        $pkCols = [];
        foreach ($rows as $r) {
            if ((int) $r['pk'] > 0) {
                $pkCols[(int) $r['pk']] = $r['name'];
            }
        }

        self::assertSame(['job_name'], array_values($pkCols));
    }

    public function testIpEnrichmentPkIsIpBin(): void
    {
        $rows = $this->connection
            ->executeQuery('PRAGMA table_info(ip_enrichment)')
            ->fetchAllAssociative();

        $pkCols = [];
        foreach ($rows as $r) {
            if ((int) $r['pk'] > 0) {
                $pkCols[(int) $r['pk']] = $r['name'];
            }
        }

        self::assertSame(['ip_bin'], array_values($pkCols));
    }

    public function testReportsHasIpBinIndex(): void
    {
        $rows = $this->connection
            ->executeQuery("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='reports'")
            ->fetchAllAssociative();
        $indexNames = array_column($rows, 'name');

        $matchesCompound = false;
        foreach ($indexNames as $n) {
            if (str_contains($n, 'ip_cat_received') || str_contains($n, 'ip_bin')) {
                $matchesCompound = true;
            }
        }

        self::assertTrue($matchesCompound, 'reports should have an index covering ip_bin');
    }

    public function testApiTokensCheckConstraintEnforced(): void
    {
        // valid: kind=admin, both NULL
        $this->connection->executeStatement(
            "INSERT INTO api_tokens (token_hash, token_prefix, kind) VALUES ('a', 'admxxxx', 'admin')"
        );

        // invalid: kind=reporter without reporter_id
        $threw = false;
        try {
            $this->connection->executeStatement(
                "INSERT INTO api_tokens (token_hash, token_prefix, kind) VALUES ('b', 'repxxxx', 'reporter')"
            );
        } catch (\Throwable) {
            $threw = true;
        }
        self::assertTrue($threw, 'CHECK constraint should reject kind=reporter without reporter_id');

        // invalid: kind=service with reporter_id set
        $threw = false;
        try {
            // Need a reporter row first for the FK to be satisfiable.
            $this->connection->executeStatement(
                "INSERT INTO reporters (name, trust_weight, is_active) VALUES ('rx', 1.0, 1)"
            );
            $rid = (int) $this->connection->lastInsertId();
            $this->connection->executeStatement(
                'INSERT INTO api_tokens (token_hash, token_prefix, kind, reporter_id) VALUES (?, ?, ?, ?)',
                ['c', 'svcxxxx', 'service', $rid]
            );
        } catch (\Throwable) {
            $threw = true;
        }
        self::assertTrue($threw, 'CHECK constraint should reject kind=service with reporter_id set');
    }

    public function testForeignKeysEnforcedOnSqlite(): void
    {
        // foreign_keys PRAGMA must be on for the constraint to fire.
        $threw = false;
        try {
            $this->connection->executeStatement(
                'INSERT INTO consumers (name, policy_id, is_active) VALUES (?, ?, ?)',
                ['x', 99999, 1]
            );
        } catch (\Throwable) {
            $threw = true;
        }
        self::assertTrue($threw, 'consumers.policy_id FK should reject unknown policy id');
    }

    public function testSeedsPopulatedDefaults(): void
    {
        $catCount = (int) $this->connection->fetchOne('SELECT COUNT(*) FROM categories');
        $polCount = (int) $this->connection->fetchOne('SELECT COUNT(*) FROM policies');
        $thrCount = (int) $this->connection->fetchOne('SELECT COUNT(*) FROM policy_category_thresholds');

        self::assertSame(5, $catCount);
        self::assertSame(3, $polCount);
        self::assertSame(15, $thrCount);
    }

    public function testSeedersAreIdempotent(): void
    {
        // Run seed again; counts must not change.
        $config = new Config([
            'paths' => [
                'migrations' => __DIR__ . '/../../db/migrations',
                'seeds' => __DIR__ . '/../../db/seeds',
            ],
            'environments' => [
                'default_migration_table' => 'phinxlog',
                'default_environment' => 'test',
                'test' => [
                    'adapter' => 'sqlite',
                    'name' => $this->sqlitePath,
                    'suffix' => '',
                ],
            ],
            'version_order' => 'creation',
        ]);
        $manager = new Manager($config, new ArrayInput([]), new NullOutput());
        $manager->seed('test');

        self::assertSame(5, (int) $this->connection->fetchOne('SELECT COUNT(*) FROM categories'));
        self::assertSame(3, (int) $this->connection->fetchOne('SELECT COUNT(*) FROM policies'));
        self::assertSame(15, (int) $this->connection->fetchOne('SELECT COUNT(*) FROM policy_category_thresholds'));
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    private function columnsOf(string $table): array
    {
        $rows = $this->connection
            ->executeQuery(sprintf('PRAGMA table_info(%s)', $table))
            ->fetchAllAssociative();

        $cols = [];
        foreach ($rows as $r) {
            $cols[$r['name']] = $r;
        }

        return $cols;
    }
}