import asyncio
import sys
from pathlib import Path

from sqlalchemy import text

# Permet l'exécution directe du script depuis Windows et PowerShell.
PROJECT_ROOT = Path(__file__).resolve().parents[1]
if str(PROJECT_ROOT) not in sys.path:
    sys.path.insert(0, str(PROJECT_ROOT))

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


async def main() -> None:
    settings = get_settings()
    summary = settings.database_summary()

    print("Configuration PostgreSQL lue depuis .env")
    print(f"Serveur : {summary['serveur']}")
    print(f"Port : {summary['port']}")
    print(f"Base : {summary['base']}")
    print(f"Utilisateur : {summary['utilisateur']}")
    print(f"SSL : {summary['ssl']}")
    print()

    if not summary["serveur"] or not summary["utilisateur"] or not summary["base"]:
        raise RuntimeError(
            "Configuration PostgreSQL incomplète. Renseignez PGHOST, PGDATABASE, "
            "PGUSER et PGPASSWORD dans le fichier .env."
        )

    async with engine.connect() as connection:
        row = (
            await connection.execute(
                text(
                    "SELECT current_database(), current_schema(), "
                    "PostGIS_Version(), current_user"
                )
            )
        ).one()
        print("Connexion réussie")
        print(f"Base connectée : {row[0]}")
        print(f"Schéma courant : {row[1]}")
        print(f"PostGIS : {row[2]}")
        print(f"Utilisateur connecté : {row[3]}")

        tables = (
            await connection.execute(
                text(
                    "SELECT count(*) FROM information_schema.tables "
                    "WHERE table_schema = 'frelons'"
                )
            )
        ).scalar_one()
        print(f"Tables trouvées dans le schéma frelons : {tables}")
        communes = (await connection.execute(text("SELECT count(*) FROM frelons.communes WHERE actif = true"))).scalar_one()
        utilisateurs = (await connection.execute(text("SELECT count(*) FROM frelons.utilisateurs WHERE actif = true"))).scalar_one()
        administrateurs = (await connection.execute(text("SELECT count(*) FROM frelons.utilisateurs WHERE actif = true AND role::text = 'admin'"))).scalar_one()
        archive_status = (await connection.execute(text("""
            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'
            )
        """))).scalar_one()
        observation_history = (await connection.execute(text("""
            SELECT count(*) FROM frelons.observations
            WHERE status::text IN ('non_confirme', 'transforme', 'archive')
        """))).scalar_one()
        request_history = (await connection.execute(text("""
            SELECT count(*) FROM frelons.demandes_intervention
            WHERE status::text IN ('annulee', 'refusee', 'archive')
        """))).scalar_one()
        tariffs = (await connection.execute(text("""
            SELECT count(*) FROM frelons.tarifs_intervention WHERE actif = true
        """))).scalar_one()
        quotes = (await connection.execute(text("""
            SELECT count(*) FROM frelons.devis_intervention
        """))).scalar_one()
        print(f"Communes actives : {communes}")
        print(f"Utilisateurs actifs : {utilisateurs}")
        print(f"Administrateurs actifs : {administrateurs}")
        print(f"Statut archive des demandes : {'disponible' if archive_status else 'absent - lancez upgrade_v025.ps1'}")
        print(f"Observations dans l'historique : {observation_history}")
        print(f"Demandes dans l'historique : {request_history}")
        print(f"Tarifs d'intervention actifs : {tariffs}")
        print(f"Devis enregistres : {quotes}")

    await engine.dispose()


if __name__ == "__main__":
    try:
        asyncio.run(main())
    except Exception as exc:
        print()
        print(f"ERREUR : {exc}")
        raise SystemExit(1) from exc