import json

from config import Config
from services.db import get_connection, scope_clause

SCHEMA = Config.SCHEMA


def volumes_preleves_par_usage(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, params = scope_clause()

        query = f"""
            SELECT usage_de_l_eau_prélevée,
                   SUM(prélèvement_annuel__m3_) as total_volume
            FROM {SCHEMA}.prelevements_aeap_aula
            WHERE année_d_activité = %s AND {clause}
        """

        params = [Config.ANNEE_PRELEVEMENTS] + params

        if epci:
            query += " AND nom_epci = %s"
            params.append(epci)

        if communes:
            query += " AND nom = ANY(%s::text[])"
            params.append(communes)

        query += """
            GROUP BY usage_de_l_eau_prélevée
            ORDER BY total_volume DESC
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        result = [
            {"usage": row[0], "volume": float(row[1] or 0)}
            for row in rows
        ]

        return result

    finally:
        cur.close()
        conn.close()


def provenance_eau_par_usage(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, params = scope_clause()

        query = f"""
            SELECT usage_de_l_eau_prélevée,
                   origine_de_l_eau_prélevée,
                   SUM(prélèvement_annuel__m3_) as total_volume
            FROM {SCHEMA}.prelevements_aeap_aula
            WHERE année_d_activité = %s AND {clause}
        """

        params = [Config.ANNEE_PRELEVEMENTS] + params

        if epci:
            query += " AND nom_epci = %s"
            params.append(epci)

        if communes:
            query += " AND nom = ANY(%s::text[])"
            params.append(communes)

        query += """
            GROUP BY usage_de_l_eau_prélevée, origine_de_l_eau_prélevée
            ORDER BY total_volume DESC
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        result = [
            {
                "usage": row[0],
                "origine": row[1],
                "volume": float(row[2] or 0)
            }
            for row in rows
        ]

        return result

    finally:
        cur.close()
        conn.close()


def captages_carte_data(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        # -----------------------------
        # FILTRE
        # -----------------------------

        clause, base_params = scope_clause()

        filter_sql = ""
        extra_params = []

        if epci:
            filter_sql += " AND nom_epci = %s"
            extra_params.append(epci)

        if communes:
            filter_sql += " AND nom = ANY(%s::text[])"
            extra_params.append(communes)

        params = base_params + extra_params

        # -----------------------------
        # CAPTAGES NON AEP
        # -----------------------------

        query_points = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform((ST_Dump(geom)).geom,4326)),
        etat_de_l_usage_prélèvement_du_captage

        FROM {SCHEMA}.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Parcelle' AND {clause}
        {filter_sql}
        """

        cur.execute(query_points, params)

        captages = []

        for geom, statut in cur.fetchall():

            captages.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "statut": statut
                }
            })

        # -----------------------------
        # COMMUNES
        # -----------------------------

        query_communes = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform(geom,4326)),
        nom
        FROM {SCHEMA}.communes
        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_communes, params)

        communes_geo = []

        for geom, nom in cur.fetchall():

            communes_geo.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "nom": nom
                }
            })

        # -----------------------------
        # COMMUNES AVEC CAPTAGE AEP
        # -----------------------------

        query_communes_captage = f"""
        SELECT DISTINCT nom

        FROM {SCHEMA}.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Commune' AND {clause}
        {filter_sql}
        """

        query_stats = f"""
        SELECT
        nom,
        etat_de_l_usage_prélèvement_du_captage,
        COUNT(*)

        FROM {SCHEMA}.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Commune' AND {clause}
        {filter_sql}

        GROUP BY nom, etat_de_l_usage_prélèvement_du_captage
        """

        # NB : dans la version précédente, cette requête ne reprenait pas
        # le filtre EPCI/communes dans son SQL bien que les paramètres lui
        # soient passés -> erreur psycopg2 dès qu'un filtre était appliqué.
        # Corrigé ici : {clause}{filter_sql} est bien inclus.
        cur.execute(query_stats, params)

        stats = {}

        for nom, statut, nb in cur.fetchall():

            if nom not in stats:
                stats[nom] = {}

            stats[nom][statut] = nb

        cur.execute(query_communes_captage, params)

        communes_captage = [row[0] for row in cur.fetchall()]

        return {

            "captages": captages,
            "communes": communes_geo,
            "communes_captage": communes_captage,
            "stats_captages": stats

        }

    finally:

        cur.close()
        conn.close()


def captages_abandonnes(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, params = scope_clause()

        query = f"""

        SELECT
            COUNT(DISTINCT n°_du_captage) AS total,

            COUNT(DISTINCT n°_du_captage) FILTER (
                WHERE etat_de_l_usage_prélèvement_du_captage = 'Abandonné (fermé)'
            ) AS abandonnes,

            COUNT(DISTINCT n°_du_captage) FILTER (
                WHERE etat_de_l_usage_prélèvement_du_captage = 'Perspective d''abandon'
            ) AS perspective

        FROM {SCHEMA}.captages_aeap_aula

        WHERE {clause}

        """

        if epci:
            query += " AND nom_epci = %s"
            params.append(epci)

        if communes:
            query += " AND nom = ANY(%s::text[])"
            params.append(communes)

        cur.execute(query, params)

        total, abandonnes, perspective = cur.fetchone()

        abandonnes = abandonnes or 0
        perspective = perspective or 0
        total = total or 1  # éviter division par 0

        return {
            "abandonnes": abandonnes,
            "abandonnes_pct": round(abandonnes / total * 100, 1),
            "perspective": perspective,
            "perspective_pct": round(perspective / total * 100, 1)
        }

    finally:
        cur.close()
        conn.close()


def qualite_eau_souterraine_data(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        # -----------------------------
        # FILTRE (communes uniquement : les masses d'eau souterraines
        # ne sont pas rattachées à un EPCI, elles chevauchent le
        # territoire, donc pas de clause de périmètre ici)
        # -----------------------------

        clause, base_params = scope_clause()

        filter_sql = ""
        extra_params = []

        if epci:
            filter_sql += " AND nom_epci = %s"
            extra_params.append(epci)

        if communes:
            filter_sql += " AND nom = ANY(%s::text[])"
            extra_params.append(communes)

        params = base_params + extra_params

        # -----------------------------
        # MASSES D'EAU SOUTERRAINES
        # -----------------------------

        query_masses_eau_souterr = f"""
        SELECT
            ST_AsGeoJSON(ST_Transform((ST_Dump(geom)).geom,4326)),
            cdmassedea,
            nommassede,
            etatquanti,
            etatchimiq,
            paramdecla

        FROM {SCHEMA}.masses_eau_souterraines_aula

        WHERE cdmassedea != 'AG318'

        """

        cur.execute(query_masses_eau_souterr)

        masses_eau_souterraines = []

        for geom, code, libelle, etat_q, etat_c, param in cur.fetchall():

            masses_eau_souterraines.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "code": code,
                    "libelle": libelle,
                    "etat_quantitatif": etat_q,
                    "etat_chimique": etat_c,
                    "parametres_declassement": param
                }
            })

        # -----------------------------
        # COMMUNES
        # -----------------------------

        query_communes = f"""
        SELECT
            ST_AsGeoJSON(ST_Transform(geom,4326)),
            nom
        FROM {SCHEMA}.communes
        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_communes, params)

        communes_geo = []

        for geom, nom in cur.fetchall():

            communes_geo.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "nom": nom
                }
            })

        return {

            "masses_eau_souterraines": masses_eau_souterraines,
            "communes": communes_geo,

        }

    finally:

        cur.close()
        conn.close()


def qualite_eco_carte(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, base_params = scope_clause()

        # -----------------------------
        # FILTRE
        # -----------------------------

        filter_sql = ""
        extra_params = []

        if epci:
            filter_sql += " AND nom_epci = %s"
            extra_params.append(epci)

        if communes:
            filter_sql += " AND nom = ANY(%s::text[])"
            extra_params.append(communes)

        params = base_params + extra_params

        # -----------------------------
        # QUALITE COURS D'EAU
        # -----------------------------

        query_lines = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform((ST_Dump(geom)).geom,4326)),
        libel_classetat,
        libelle_me

        FROM {SCHEMA}.qualite_eco_cours_eau_aula

        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_lines, params)

        qualite = []

        for geom, etat, libelle in cur.fetchall():

            qualite.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "statut": etat,
                    "cours_eau": libelle
                }
            })

        # -----------------------------
        # RESEAU HYDRO
        # -----------------------------

        query_hydro = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform(geom,4326)),
        libelle
        FROM {SCHEMA}.reseau_hydro_simplifie
        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_hydro, params)

        hydro_geo = []

        for geom, libelle in cur.fetchall():

            hydro_geo.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "nom": libelle
                }
            })

        # -----------------------------
        # COMMUNES
        # -----------------------------

        query_communes = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform(geom,4326)),
        nom
        FROM {SCHEMA}.communes
        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_communes, params)

        communes_geo = []

        for geom, nom in cur.fetchall():

            communes_geo.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "nom": nom
                }
            })

        return {
            "qualite": qualite,
            "communes": communes_geo,
            "reseau_hydro": hydro_geo
        }

    finally:

        cur.close()
        conn.close()


def step_data(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, base_params = scope_clause()

        # -----------------------------
        # FILTRE
        # -----------------------------

        filter_sql = ""
        extra_params = []

        if epci:
            filter_sql += " AND nom_epci = %s"
            extra_params.append(epci)

        if communes:
            filter_sql += " AND nom = ANY(%s::text[])"
            extra_params.append(communes)

        params = base_params + extra_params

        # -----------------------------
        # STATIONS D'EPURATION
        # -----------------------------

        query_step = f"""
        SELECT
            ST_AsGeoJSON(ST_Transform(geom,4326)),
            code_du_steu,
            nom_du_steu,
            capacité_nominale_en_eh,
            charge_maximale_entrante__eh_,
            conformité_réglementaire_équipement_steu,
            conformité_globale_steu_réglementaire_performances

        FROM {SCHEMA}.step_aula

        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_step, params)

        step = []

        for geom, code, libelle, capacite, charge, conformite_eq, conformite_perf in cur.fetchall():

            step.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "code": code,
                    "libelle": libelle,
                    "capacite_nominale": capacite,
                    "charge_maximale": charge,
                    "conformite_equipement": conformite_eq,
                    "conformite_performances": conformite_perf
                }
            })

        # -----------------------------
        # COMMUNES
        # -----------------------------

        query_communes = f"""
        SELECT
            ST_AsGeoJSON(ST_Transform(geom,4326)),
            nom
        FROM {SCHEMA}.communes
        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query_communes, params)

        communes_geo = []

        for geom, nom in cur.fetchall():

            communes_geo.append({
                "type": "Feature",
                "geometry": json.loads(geom),
                "properties": {
                    "nom": nom
                }
            })

        return {

            "step": step,
            "communes": communes_geo,

        }

    finally:

        cur.close()
        conn.close()


def step_chiffres_cles(epci=None, communes=None):

    conn = get_connection()
    cur = conn.cursor()

    try:

        clause, params = scope_clause()

        filter_sql = ""

        if epci:
            filter_sql += " AND nom_epci = %s"
            params.append(epci)

        if communes:
            filter_sql += " AND nom = ANY(%s::text[])"
            params.append(communes)

        query = f"""
        SELECT
            COUNT(*) as total,

            COUNT(*) FILTER (
                WHERE conformité_réglementaire_équipement_steu = 'Oui'
            ) as equipement_conforme,

            COUNT(*) FILTER (
                WHERE conformité_globale_steu_réglementaire_performances = 'Oui'
            ) as performance_conforme,

            SUM(capacité_nominale_en_eh - charge_maximale_entrante__eh_) as capacite_restante

        FROM {SCHEMA}.step_aula

        WHERE {clause}
        {filter_sql}
        """

        cur.execute(query, params)

        total, equipement, performance, capacite = cur.fetchone()

        total = total or 1

        return {
            "equipement_pct": round((equipement or 0) / total * 100, 1),
            "performance_pct": round((performance or 0) / total * 100, 1),
            "capacite_restante": int(capacite or 0)
        }

    finally:
        cur.close()
        conn.close()