from services.db import get_connection

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

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

    
    try:

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

        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 année_d_activité, usage_de_l_eau_prélevée
            ORDER BY année_d_activité
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        result = [
            {
                "annee": row[0],
                "usage": row[1],
                "volume": float(row[2] or 0)
            }
            for row in rows
        ]
        return result
    
    finally :
        cur.close()
        conn.close()


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

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

    
    try:

        query = """
            SELECT
                annee_pub_jo,
                COUNT(DISTINCT cod_nat_catnat) as nombre_arretes
            FROM eau_poc.catnat_inondation
            WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane', 'CC des Sept Vallées', 'CC du Ternois')
        """

        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 annee_pub_jo
            ORDER BY annee_pub_jo
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        result = [
            {
                "annee": row[0],
                "arrete": int(row[1] or 0),
            }
            for row in rows
        ]
        return result
    
    finally :
        cur.close()
        conn.close()


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

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

    
    try:

        query = """
            SELECT
                COUNT(DISTINCT cod_nat_catnat) as nombre_arretes
            FROM eau_poc.catnat_inondation
            WHERE nom_epci IN ('CA de Béthune-Bruay, Artois-Lys Romane', 'CC des Sept Vallées', 'CC du Ternois')
        """

        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)

        row = cur.fetchone()

        result = {
                "arretes": int(row[0] or 0),
            }

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