from services.db import get_connection

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

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

    try:

        query = """
        SELECT
            annee,
            SUM(population) as population
        FROM eau_poc.pop_omphale
        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)


        query += """
        GROUP BY annee
        ORDER BY annee
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        return [
            {
                "annee": row[0],
                "population": int(row[1])
            }
            for row in rows
        ]

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


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

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

    try:

        query = """
        SELECT
            annee,
            SUM(population)*150 as consommation
        FROM eau_poc.pop_omphale
        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)


        query += """
        GROUP BY annee
        ORDER BY annee
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        return [
            {
                "annee": row[0],
                "consommation": int(row[1])
            }
            for row in rows
        ]

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

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

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

    try:

        query = """
        SELECT SUM(mesure) AS total_piscines,
        SUM(mesure)*2500 AS total_m3
        FROM eau_poc.economie_eau
        WHERE nom_epci IS NOT NULL
        """

        params = []

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


        cur.execute(query, params)

        totalp, totalm = cur.fetchone()

        return {
            "piscines": int(totalp or 0),
            "metrecube": int(totalm or 0)
        }

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

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

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

    try:

        query = """
        SELECT
            année,

            AVG(eau_potable__€_m3_) AS eau,
            AVG(assainissement__€_m3_) AS assainissement,
            AVG(redevances_de_l_agence_de_l_eau__€_m3_) AS redevance,
            AVG(taxe_vnf__€_m3_) AS vnf,
            AVG(tva__€_m3_) AS tva

        FROM eau_poc.eau_potable_prix

        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)

        query += """
        GROUP BY année
        ORDER BY année
        """

        cur.execute(query, params)

        rows = cur.fetchall()

        return [

            {
                "annee": row[0],
                "eau": float(row[1] or 0),
                "assainissement": float(row[2] or 0),
                "redevance": float(row[3] or 0),
                "vnf": float(row[4] or 0),
                "tva": float(row[5] or 0)
            }

            for row in rows
        ]

    finally:

        cur.close()
        conn.close()

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

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

    try:

        query = """
        SELECT

            AVG(eau_potable__€_m3_) AS eau,
            AVG(assainissement__€_m3_) AS assainissement,
            AVG(redevances_de_l_agence_de_l_eau__€_m3_) AS redevance,
            AVG(taxe_vnf__€_m3_) AS vnf,
            AVG(tva__€_m3_) AS tva

        FROM eau_poc.eau_potable_prix

        WHERE année = '2024'

        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)

        cur.execute(query, params)

        row = cur.fetchone()

        return {

            "eau": float(row[0] or 0),
            "assainissement": float(row[1] or 0),
            "redevance": float(row[2] or 0),
            "vnf": float(row[3] or 0),
            "tva": float(row[4] or 0)

        }

    finally:

        cur.close()
        conn.close()