from __future__ import annotations

import logging
import pathlib
import re

import aiosqlite

from server.settings import settings

logger = logging.getLogger(__name__)

_MIGRATION_RE = re.compile(r"^(\d+)_.*\.sql$")


def _migrations_dir() -> pathlib.Path:
    path = pathlib.Path(settings.migrations_dir)
    if path.is_absolute():
        return path
    return pathlib.Path(__file__).resolve().parents[1] / path


def _available_migrations() -> list[tuple[int, pathlib.Path]]:
    directory = _migrations_dir()
    if not directory.exists():
        return []
    migrations: list[tuple[int, pathlib.Path]] = []
    for item in directory.iterdir():
        match = _MIGRATION_RE.match(item.name)
        if match and item.is_file():
            migrations.append((int(match.group(1)), item))
    migrations.sort(key=lambda x: x[0])
    return migrations


async def _ensure_meta_table(db: aiosqlite.Connection) -> None:
    await db.execute(
        "CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL)"
    )


async def current_version(db: aiosqlite.Connection) -> int:
    await _ensure_meta_table(db)
    cur = await db.execute("SELECT MAX(version) FROM schema_version")
    row = await cur.fetchone()
    await cur.close()
    return row[0] or 0 if row else 0


async def _record_version(db: aiosqlite.Connection, version: int) -> None:
    from server.db import utc_now_iso

    await db.execute(
        "INSERT INTO schema_version (version, applied_at) VALUES (?, ?)",
        (version, utc_now_iso()),
    )


async def run_migrations(db: aiosqlite.Connection) -> None:
    """Apply pending SQL migrations safely. Idempotent, data-preserving."""
    await _ensure_meta_table(db)
    current = await current_version(db)
    available = _available_migrations()

    pending = [(v, p) for v, p in available if v > current]
    if not pending:
        logger.info("Database schema up to date (version %d)", current)
        return

    logger.info("Applying %d pending migration(s) (current version %d)", len(pending), current)
    for version, path in pending:
        sql = path.read_text(encoding="utf-8")
        logger.info("Applying migration %d: %s", version, path.name)
        await db.executescript(sql)
        await _record_version(db, version)
        await db.commit()
        logger.info("Migration %d applied", version)

    logger.info("Database migrations complete (version %d)", await current_version(db))