import hashlib
import re
import sqlite3
from datetime import UTC, datetime
from pathlib import Path

from ..timeutil import iso


def migration_dir() -> Path:
    return Path(__file__).resolve().parents[3] / "migrations"


def apply_migrations(connection: sqlite3.Connection, directory: Path | None = None) -> int:
    folder = directory or migration_dir()
    connection.execute("CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL, checksum TEXT NOT NULL)")
    applied = {int(row[0]) for row in connection.execute("SELECT version FROM schema_migrations")}
    files = sorted(folder.glob("*.sql"))
    for file in files:
        match = re.match(r"(\d+)_.*\.sql$", file.name)
        if not match:
            continue
        version = int(match.group(1))
        sql = file.read_text(encoding="utf-8")
        checksum = hashlib.sha256(sql.encode()).hexdigest()
        if version in applied:
            stored = connection.execute("SELECT checksum FROM schema_migrations WHERE version = ?", (version,)).fetchone()
            if stored and stored[0] != checksum:
                raise RuntimeError(f"migration checksum changed: {file.name}")
            continue
        with connection:
            connection.executescript(sql)
            connection.execute(
                "INSERT INTO schema_migrations(version, name, applied_at, checksum) VALUES (?, ?, ?, ?)",
                (version, file.name, iso(datetime.now(UTC)), checksum),
            )
        applied.add(version)
    return max(applied, default=0)