# Trouve-KA — exécution des migrations SQL
# Author: Simon-Pierre Boucher
# Contact: contact@spboucher.ai

"""Applique les migrations SQL de infrastructure/migrations dans l'ordre."""

import os
import pathlib
import re

import asyncpg


def _find_migrations_dir() -> pathlib.Path:
    """Résout le dossier de migrations : env, cwd (container /app), ou racine du dépôt (editable)."""
    candidates = [
        os.environ.get("TROUVEKA_MIGRATIONS_DIR"),
        pathlib.Path.cwd() / "infrastructure" / "migrations",
        pathlib.Path(__file__).resolve().parents[2] / "infrastructure" / "migrations",
    ]
    for cand in candidates:
        if cand and pathlib.Path(cand).is_dir():
            return pathlib.Path(cand)
    raise FileNotFoundError("dossier infrastructure/migrations introuvable")


async def run_migrations(database_url: str) -> list[str]:
    """Applique les migrations manquantes. Retourne la liste des fichiers appliqués."""
    migrations_dir = _find_migrations_dir()
    conn = await asyncpg.connect(database_url)
    applied: list[str] = []
    try:
        await conn.execute(
            "CREATE TABLE IF NOT EXISTS schema_migrations ("
            " version INTEGER PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT now())"
        )
        done = {r["version"] for r in await conn.fetch("SELECT version FROM schema_migrations")}
        for path in sorted(migrations_dir.glob("*.sql")):
            match = re.match(r"^(\d+)_", path.name)
            if not match or int(match.group(1)) in done:
                continue
            await conn.execute(path.read_text(encoding="utf-8"))
            applied.append(path.name)
    finally:
        await conn.close()
    return applied