<?php
declare(strict_types=1);

namespace App\Database;

use PDO;
use RuntimeException;
use Throwable;

/**
 * One authoritative database-upgrade path shared by CLI and administration.
 *
 * Normal migrations are applied once. If the recorded history is inconsistent
 * with the runtime schema (for example an interrupted legacy upgrade), the
 * service replays only the stabilization migrations that were deliberately
 * authored to be idempotent. This repairs missing tables/columns without
 * deleting user data or rewriting migration history.
 */
final class DatabaseUpgradeService
{
    /** @var list<string> */
    private const REPAIRABLE_MIGRATIONS = [
        '012_create_language_management.sql',
        '013_create_notifications_contact_settings.sql',
        '014_create_mail_settings.sql',
        '015_create_core_qa_plus.sql',
        '016_add_user_public_profiles.sql',
        '017_add_taxonomy_seo_and_synonyms.sql',
        '018_create_audit_moderation.sql',
        '019_locale_scoped_tag_names.sql',
    ];

    public function __construct(
        private readonly PDO $pdo,
        private readonly string $migrationsDirectory,
    ) {
    }

    /**
     * @return array{applied:list<string>,repaired:list<string>,health:array<string,mixed>}
     */
    public function upgrade(): array
    {
        $lock = $this->pdo->query("SELECT GET_LOCK('quaeriva_schema_upgrade', 10)");
        if ((int) ($lock ? $lock->fetchColumn() : 0) !== 1) {
            throw new RuntimeException('Could not acquire the database migration lock. Try again shortly.');
        }

        try {
            $migrator = new Migrator($this->pdo, $this->migrationsDirectory);
            $applied = $migrator->migrate();
            $healthService = new SchemaHealthService($this->pdo, $this->migrationsDirectory);
            $health = $healthService->inspect();
            $repaired = [];

            // Pending migrations are not synonymous with an unusable database.
            // Repair is only invoked when actual required tables/columns are
            // missing after the normal migration pass.
            if (!($health['runtime_compatible'] ?? false)) {
                $repaired = $migrator->reapply(self::REPAIRABLE_MIGRATIONS);
                $health = $healthService->inspect();
            }

            return ['applied' => $applied, 'repaired' => $repaired, 'health' => $health];
        } finally {
            try {
                $release = $this->pdo->query("SELECT RELEASE_LOCK('quaeriva_schema_upgrade')");
                if ($release) $release->closeCursor();
            } catch (Throwable) {
                // The connection closing also releases the advisory lock.
            }
        }
    }
}
