from __future__ import annotations

import asyncio
import os
import sys
from pathlib import Path

import asyncpg

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))
os.chdir(ROOT)

from backend.app.config import get_settings  # noqa: E402


async def main() -> None:
    settings = get_settings()
    sql_path = ROOT / "sql" / "004_upgrade_v025.sql"
    if not sql_path.is_file():
        raise FileNotFoundError(sql_path)

    connection = await asyncpg.connect(
        host=settings.pghost,
        port=settings.pgport,
        database=settings.pgdatabase,
        user=settings.pguser,
        password=settings.pgpassword,
        ssl=False if settings.pgsslmode == "disable" else settings.pgsslmode,
    )
    try:
        print("Application de la mise a niveau SQL V0.2.5...")
        await connection.execute(sql_path.read_text(encoding="utf-8"))
        enum_exists = await connection.fetchval(
            """
            SELECT EXISTS (
              SELECT 1
              FROM pg_enum e
              JOIN pg_type t ON t.oid = e.enumtypid
              JOIN pg_namespace n ON n.oid = t.typnamespace
              WHERE n.nspname = 'frelons'
                AND t.typname = 'request_status'
                AND e.enumlabel = 'archive'
            )
            """
        )
        print(f"Statut archive disponible pour les demandes : {'oui' if enum_exists else 'non'}")
        print("Mise a niveau V0.2.5 terminee.")
    finally:
        await connection.close()


if __name__ == "__main__":
    asyncio.run(main())