<?php

namespace MintyPHP\Repository\Database;

use MintyPHP\DB;
use MintyPHP\DBError;
use mysqli;

/**
 * Manages the core_migrations tracking table and applies raw SQL statements.
 *
 * Owns its own mysqli connection (using the same credentials as MintyPHP\DB)
 * so we can run statements via mysqli->query() — bypassing prepared statements,
 * which MariaDB rejects for some DDL (e.g. ALTER TABLE … ADD CONSTRAINT CHECK).
 *
 * All SQL for core migration tracking lives here (strict layering).
 */
final class CoreMigrationRepository implements CoreMigrationRepositoryInterface
{
    private ?mysqli $mysqli = null;

    public function ensureTrackingTable(): void
    {
        $this->executeStatement(
            'CREATE TABLE IF NOT EXISTS `core_migrations` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                `filename` VARCHAR(255) NOT NULL,
                `applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `uq_core_migration` (`filename`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );
    }

    /**
     * @return array<string, true> Filenames already applied, keyed by filename
     */
    public function getAppliedFilenames(): array
    {
        $applied = [];
        $result = $this->connection()->query('SELECT filename FROM core_migrations');
        if ($result === false) {
            throw new DBError('Failed to query core_migrations: ' . $this->connection()->error);
        }
        while (($row = $result->fetch_assoc()) !== null) {
            $filename = trim((string) ($row['filename'] ?? ''));
            if ($filename !== '') {
                $applied[$filename] = true;
            }
        }
        $result->free();
        return $applied;
    }

    public function recordApplied(string $filename): void
    {
        $stmt = $this->connection()->prepare('INSERT INTO core_migrations (filename) VALUES (?)');
        if ($stmt === false) {
            throw new DBError('Failed to prepare INSERT into core_migrations: ' . $this->connection()->error);
        }
        $stmt->bind_param('s', $filename);
        $stmt->execute();
        if ($stmt->errno !== 0) {
            $error = $stmt->error;
            $stmt->close();
            throw new DBError('Failed to insert into core_migrations: ' . $error);
        }
        $stmt->close();
    }

    public function beginTransaction(): void
    {
        $this->executeStatement('START TRANSACTION');
    }

    public function commit(): void
    {
        $this->executeStatement('COMMIT');
    }

    public function rollback(): void
    {
        $this->executeStatement('ROLLBACK');
    }

    public function executeStatement(string $sql): void
    {
        $mysqli = $this->connection();
        $result = $mysqli->query($sql);
        if ($result === false) {
            throw new DBError($mysqli->error);
        }
        // For SELECT-style results, free the result set so the connection
        // doesn't get stuck in "commands out of sync" state.
        if ($result instanceof \mysqli_result) {
            $result->free();
        }
    }

    private function connection(): mysqli
    {
        if ($this->mysqli instanceof mysqli) {
            return $this->mysqli;
        }

        // Reuse the MintyPHP\DB connection settings (public statics) so we
        // hit the same database the rest of the app uses, but with our own
        // socket — the vendor DB class always uses prepared statements,
        // which MariaDB rejects for some DDL.
        $args = array_filter(
            [DB::$host, DB::$username, DB::$password, DB::$database, DB::$port, DB::$socket],
            static fn ($v): bool => $v !== null
        );

        $reflect = new \ReflectionClass(mysqli::class);
        $mysqli = $reflect->newInstanceArgs($args);
        if ($mysqli->connect_errno !== 0) {
            throw new DBError('Failed to connect to database for core migrations: ' . $mysqli->connect_error);
        }
        if (!$mysqli->set_charset('utf8mb4')) {
            throw new DBError('Failed to set charset for core migrations connection: ' . $mysqli->error);
        }

        $this->mysqli = $mysqli;
        return $mysqli;
    }
}
