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

SCHEMA = Config.SCHEMA


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

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

    try:

        clause, base_params = scope_clause()

        filter_sql = ""
        extra_params = []

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

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

        params = base_params + extra_params

        query = f"""
        SELECT
            SUM(nbre_individus) as population_totale,

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


        FROM {SCHEMA}.population_ppri_aula

        WHERE {clause}
        {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:

        clause, base_params = scope_clause()

        filter_sql = ""
        extra_params = []

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

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

        params = base_params + extra_params

        query = f"""
        SELECT
            SUM(nbre_habitations) as habitations_total,

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


        FROM {SCHEMA}.habitations_ppri_aula

        WHERE {clause}
        {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()