from __future__ import annotations

import asyncio
import json
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" / "003_upgrade_v024.sql"
    geojson_path = settings.communes_geojson_path
    if not sql_path.is_file():
        raise FileNotFoundError(sql_path)
    if not geojson_path.is_file():
        raise FileNotFoundError(geojson_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.4...")
        await connection.execute(sql_path.read_text(encoding="utf-8"))

        data = json.loads(geojson_path.read_text(encoding="utf-8"))
        features = data.get("features") or []
        if not features:
            raise RuntimeError("Le fichier GeoJSON ne contient aucune commune.")

        await connection.execute("UPDATE frelons.communes SET actif = false")
        query = """
            INSERT INTO frelons.communes (
                insee_com, nom, nom_m, siren_epci, population,
                surf_km2, actif, geom, synchronise_le
            )
            VALUES (
                $1, $2, $3, $4, $5, $6, true,
                ST_Multi(ST_SetSRID(ST_GeomFromGeoJSON($7), 2154)), now()
            )
            ON CONFLICT (insee_com) DO UPDATE SET
                nom = EXCLUDED.nom,
                nom_m = EXCLUDED.nom_m,
                siren_epci = EXCLUDED.siren_epci,
                population = EXCLUDED.population,
                surf_km2 = EXCLUDED.surf_km2,
                actif = true,
                geom = EXCLUDED.geom,
                synchronise_le = now()
        """
        rows = []
        for feature in features:
            props = feature.get("properties") or {}
            code = str(props.get("insee_com") or "").strip()
            name = str(props.get("nom") or "").strip()
            geometry = feature.get("geometry")
            if not code or not name or not geometry:
                continue
            rows.append(
                (
                    code,
                    name,
                    props.get("nom_m"),
                    str(props.get("siren_epci") or "") or None,
                    props.get("population"),
                    props.get("surf_km2"),
                    json.dumps(geometry, ensure_ascii=False),
                )
            )
        await connection.executemany(query, rows)
        await connection.execute("""
            UPDATE frelons.observations o
            SET commune_code = c.insee_com
            FROM frelons.communes c
            WHERE o.commune_code IS NULL AND lower(trim(o.commune)) = lower(trim(c.nom))
        """)
        await connection.execute("""
            UPDATE frelons.demandes_intervention d
            SET commune_code = c.insee_com
            FROM frelons.communes c
            WHERE d.commune_code IS NULL AND lower(trim(d.commune)) = lower(trim(c.nom))
        """)
        count = await connection.fetchval(
            "SELECT count(*) FROM frelons.communes WHERE actif = true"
        )
        print(f"Communes actives importees : {count}")
        if count != 103:
            print("ATTENTION : le nombre attendu est 103.")
        print("Mise a niveau V0.2.4 terminee.")
    finally:
        await connection.close()


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