from services.db import get_connection
import json

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

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

    try:

        query = """
            SELECT usage_de_l_eau_prélevée,
                   SUM(prélèvement_annuel__m3_) as total_volume
            FROM eau_poc.prelevements_aeap_aula
            WHERE année_d_activité = 2023 AND nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        """

        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:

        query = """
            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 eau_poc.prelevements_aeap_aula
            WHERE année_d_activité = 2023 AND nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        """

        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
        # -----------------------------

        filter_sql = ""
        params = []

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

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


        # -----------------------------
        # 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 eau_poc.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Parcelle' AND nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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 eau_poc.communes 
        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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 eau_poc.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Commune' AND nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {filter_sql}
        """

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

        FROM eau_poc.captages_aeap_aula

        WHERE précision_des_coordonnées__parcelle_ou_commune_ = 'Commune'AND nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')

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

        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:

        query = """
 
        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 eau_poc.captages_aeap_aula 

        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')

        """

        params = []

        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
        # -----------------------------

        filter_sql = ""
        params = []

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

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

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

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

        FROM eau_poc.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 eau_poc.communes
        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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:

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

        filter_sql = ""
        params = []

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

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


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

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

        FROM eau_poc.qualite_eco_cours_eau_aula

        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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
        # -----------------------------

        filter_sql_hydro = ""
        params_hydro = []

        if epci:
            filter_sql_hydro += " AND nom_epci = %s"
            params_hydro.append(epci)

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

        query_hydro = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform(geom,4326)),
        libelle
        FROM eau_poc.reseau_hydro_simplifie
        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {filter_sql_hydro}"""

        cur.execute(query_hydro,params_hydro)

        hydro_geo = []

        for geom, libelle in cur.fetchall():

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

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

        filter_sql_communes = ""
        params_communes = []

        if epci:
            filter_sql_communes += " AND nom_epci = %s"
            params_communes.append(epci)

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

        query_communes = f"""
        SELECT
        ST_AsGeoJSON(ST_Transform(geom,4326)),
        nom
        FROM eau_poc.communes 
        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {filter_sql_communes}"""

        cur.execute(query_communes,params_communes)

        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:

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

        filter_sql = ""
        params = []

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

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

        # -----------------------------
        # 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 eau_poc.step_aula

        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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 eau_poc.communes
        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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:

        filter_sql = ""
        params = []

        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 eau_poc.step_aula

        WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane','CC du Ternois', 'CC des Sept Vallées')
        {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()