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

SCHEMA = Config.SCHEMA


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

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

    try:

        clause, params = scope_clause()

        query = f"""
            SELECT
                année_d_activité,
                usage_de_l_eau_prélevée,
                SUM(prélèvement_annuel__m3_) as total_volume
            FROM {SCHEMA}.prelevements_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)

        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:

        clause, params = scope_clause()

        query = f"""
            SELECT
                annee_pub_jo,
                COUNT(DISTINCT cod_nat_catnat) as nombre_arretes
            FROM {SCHEMA}.catnat_inondation
            WHERE {clause}
        """

        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:

        clause, params = scope_clause()

        query = f"""
            SELECT
                COUNT(DISTINCT cod_nat_catnat) as nombre_arretes
            FROM {SCHEMA}.catnat_inondation
            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)

        row = cur.fetchone()

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

        return result

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