breadcrumb-the-shire/tests/Architecture/DatabaseUpdatesContractTest.php

<?php

namespace MintyPHP\Tests\Architecture;

use DirectoryIterator;
use PHPUnit\Framework\TestCase;

/**
 * GR-CORE-010: Schema/data changes for existing installations MUST be shipped
 * as idempotent SQL scripts in db/updates/.
 */
class DatabaseUpdatesContractTest extends TestCase
{
    use ProjectFileAssertionSupport;

    private function updateScripts(): array
    {
        $dir = $this->projectRootPath() . '/db/updates';
        if (!is_dir($dir)) {
            return [];
        }

        $files = [];
        foreach (new DirectoryIterator($dir) as $entry) {
            if ($entry->isDot() || !$entry->isFile()) {
                continue;
            }
            $files[] = $entry->getFilename();
        }

        sort($files);

        return $files;
    }

    public function testUpdateScriptsUseSqlExtension(): void
    {
        $nonSql = [];
        foreach ($this->updateScripts() as $file) {
            if (!str_ends_with($file, '.sql')) {
                $nonSql[] = $file;
            }
        }

        $this->assertSame([], $nonSql, "Non-SQL files found in db/updates/:\n" . implode("\n", $nonSql));
    }

    public function testUpdateScriptsFollowNamingConvention(): void
    {
        $invalid = [];
        foreach ($this->updateScripts() as $file) {
            if (!preg_match('/^\d{4}-\d{2}-\d{2}-.+\.sql$/', $file)) {
                $invalid[] = $file;
            }
        }

        $this->assertSame(
            [],
            $invalid,
            "db/updates/ scripts must follow YYYY-MM-DD-description.sql naming:\n" . implode("\n", $invalid)
        );
    }

    public function testUpdateScriptsAreNotEmpty(): void
    {
        $empty = [];
        foreach ($this->updateScripts() as $file) {
            $content = trim($this->readProjectFile('db/updates/' . $file));
            if ($content === '') {
                $empty[] = $file;
            }
        }

        $this->assertSame([], $empty, "Empty scripts found in db/updates/:\n" . implode("\n", $empty));
    }

    public function testUpdateScriptsUseIdempotentPatterns(): void
    {
        $violations = [];

        foreach ($this->updateScripts() as $file) {
            $content = $this->readProjectFile('db/updates/' . $file);
            $upper = strtoupper($content);

            // Every script must contain at least one idempotent pattern.
            $hasIfNotExists = str_contains($upper, 'IF NOT EXISTS');
            $hasOnDuplicateKey = str_contains($upper, 'ON DUPLICATE KEY');
            $hasInsertIgnore = str_contains($upper, 'INSERT IGNORE');
            $hasIfExists = str_contains($upper, 'IF EXISTS');
            $hasDropIfExists = (bool) preg_match('/DROP\s+(TABLE|INDEX|COLUMN)\s+IF\s+EXISTS/i', $content);
            // UPDATE ... SET is inherently idempotent (convergent writes).
            $hasIdempotentUpdate = (bool) preg_match('/UPDATE\s+\S+\s+SET\s+/i', $content);

            if (!$hasIfNotExists && !$hasOnDuplicateKey && !$hasInsertIgnore && !$hasIfExists && !$hasDropIfExists && !$hasIdempotentUpdate) {
                $violations[] = $file . ' (no idempotent guard found)';
            }

            // Unguarded CREATE TABLE is forbidden.
            if (preg_match('/CREATE\s+TABLE\s+(?!IF\s+NOT\s+EXISTS)/i', $content)) {
                $violations[] = $file . ' (CREATE TABLE without IF NOT EXISTS)';
            }
        }

        $this->assertSame(
            [],
            $violations,
            "db/updates/ scripts must use idempotent SQL patterns (GR-CORE-010):\n" . implode("\n", $violations)
        );
    }
}