"""Minimal SQLite connection and deterministic migration support for Activity storage."""

from __future__ import annotations

import hashlib
import re
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone
from importlib import resources
from pathlib import Path
from typing import Iterator

MIGRATION_PACKAGE = "mountain_twin.activity.persistence.migrations"


class MigrationChecksumMismatchError(RuntimeError):
    pass


class ActivitySqliteDatabase:
    """Configurable SQLite database; callers own lifecycle and no default path is assumed."""

    def __init__(self, path: str | Path):
        self.path = str(path)

    def connect(self) -> sqlite3.Connection:
        connection = sqlite3.connect(self.path, isolation_level=None)
        connection.row_factory = sqlite3.Row
        connection.execute("PRAGMA foreign_keys = ON")
        if self.path != ":memory:":
            connection.execute("PRAGMA journal_mode = WAL")
        apply_migrations(connection)
        return connection


@contextmanager
def transaction(connection: sqlite3.Connection) -> Iterator[None]:
    """Use one explicit transaction; nested repository calls share the active transaction."""
    if connection.in_transaction:
        yield
        return
    connection.execute("BEGIN IMMEDIATE")
    try:
        yield
    except Exception:
        connection.rollback()
        raise
    else:
        try:
            connection.commit()
        except Exception:
            connection.rollback()
            raise


def apply_migrations(connection: sqlite3.Connection) -> None:
    connection.execute(
        """CREATE TABLE IF NOT EXISTS schema_migrations (
            version INTEGER PRIMARY KEY,
            checksum TEXT NOT NULL,
            applied_at TEXT NOT NULL
        )"""
    )
    for version, name, sql in _migration_resources():
        checksum = hashlib.sha256(sql.encode()).hexdigest()
        applied = connection.execute(
            "SELECT checksum FROM schema_migrations WHERE version = ?", (version,)
        ).fetchone()
        if applied is not None:
            if applied["checksum"] != checksum:
                raise MigrationChecksumMismatchError(
                    f"migration {version:03d} checksum differs from the applied migration"
                )
            continue
        _apply_migration(connection, version, checksum, name, sql)


def schema_version(connection: sqlite3.Connection) -> int:
    row = connection.execute("SELECT COALESCE(MAX(version), 0) AS version FROM schema_migrations").fetchone()
    return int(row["version"])


def _migration_resources() -> tuple[tuple[int, str, str], ...]:
    candidates = []
    for resource in resources.files(MIGRATION_PACKAGE).iterdir():
        match = re.fullmatch(r"(\d{3})_[a-z0-9_]+\.sql", resource.name)
        if match:
            candidates.append((int(match.group(1)), resource.name, resource.read_text(encoding="utf-8")))
    versions = [item[0] for item in candidates]
    if len(versions) != len(set(versions)):
        raise RuntimeError("Activity migrations must have unique versions")
    return tuple(sorted(candidates))


def _apply_migration(
    connection: sqlite3.Connection, version: int, checksum: str, _name: str, sql: str
) -> None:
    quoted_checksum = checksum.replace("'", "''")
    applied_at = datetime.now(timezone.utc).isoformat().replace("'", "''")
    script = (
        "BEGIN IMMEDIATE;\n"
        + sql
        + "\n"
        + "INSERT INTO schema_migrations(version, checksum, applied_at) VALUES "
        + f"({version}, '{quoted_checksum}', '{applied_at}');\nCOMMIT;"
    )
    try:
        connection.executescript(script)
    except Exception:
        if connection.in_transaction:
            connection.rollback()
        raise