from services.db import get_connection

def population_ppri_data(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
            SUM(nbre_individus) as population_totale,

            SUM(nbre_individus) FILTER (
                WHERE ppri = 1
            ) as population_ppri


        FROM eau_poc.population_ppri_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, ppri = cur.fetchone()

        total = total or 1
        ppri = ppri or 0

        return {
            "ppri": round(ppri),
            "ppri_pct": round((ppri or 0) / total * 100, 1)
        }

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


def habitations_ppri_data(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
            SUM(nbre_habitations) as habitations_total,

            SUM(nbre_habitations) FILTER (
                WHERE ppri = 1
            ) as habitations_ppri


        FROM eau_poc.habitations_ppri_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, ppri = cur.fetchone()

        total = total or 1
        ppri = ppri or 0

        return {
            "ppri": round(ppri),
            "ppri_pct": round((ppri or 0) / total * 100, 1)
        }

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