from __future__ import annotations

from pathlib import Path
import sys

from sqlalchemy.exc import OperationalError, ProgrammingError
from sqlalchemy import text

ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

from services.shared.db import engine
from services.shared.schema_migrations import (
    MIGRATIONS_DIR,
    applied_migration_versions,
    database_backend_name,
    ensure_schema_migrations_table,
    list_migration_files,
)


def _split_statements(sql: str) -> list[str]:
    chunks = []
    for part in sql.split(";"):
        stmt = part.strip()
        if stmt:
            chunks.append(stmt)
    return chunks


def _apply_file(path: Path) -> None:
    # Some checked-in SQL files may contain a UTF-8 BOM; accept them for both
    # local SQLite runs and Postgres migration jobs.
    statements = _split_statements(path.read_text(encoding="utf-8-sig"))
    with engine.begin() as conn:
        for stmt in statements:
            try:
                conn.execute(text(stmt))
            except (OperationalError, ProgrammingError) as exc:
                message = str(exc).lower()
                duplicate_markers = [
                    "duplicate column name",
                    "already exists",
                    "duplicate key value violates unique constraint",
                ]
                if any(marker in message for marker in duplicate_markers):
                    continue
                raise
        conn.execute(
            text("INSERT INTO schema_migrations(version, applied_at) VALUES (:v, CURRENT_TIMESTAMP)"),
            {"v": path.name},
        )


def main() -> None:
    files = list_migration_files()
    if not files:
        raise RuntimeError(f"No migration files found for dialect in {MIGRATIONS_DIR}")

    ensure_schema_migrations_table()
    applied = applied_migration_versions()

    applied_now = []
    for file_path in files:
        if file_path.name in applied:
            continue
        _apply_file(file_path)
        applied_now.append(file_path.name)

    print(f"Dialect: {database_backend_name()}")
    if applied_now:
        print("Applied migrations:")
        for m in applied_now:
            print(f"- {m}")
    else:
        print("No new migrations to apply")


if __name__ == "__main__":
    main()