from mm_stats.auth import PostgresDb
from mm_stats.definitions import logger


def run(_filter: str) -> None:
    """Create table for pop stats per region and per month."""
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
        drop table if exists "pop_per_region_per_month";
        create table "pop_per_region_per_month" as
        select
          b.region_name
          ,b.region_id
          ,a.month
          ,a.osm_feature
          ,a.impact_area_id
          ,sum(a.cum_population_mapped) as cum_population_mapped
          ,sum(a.cum_population_validated) as cum_population_validated
          ,sum(a.cum_area_sqkm_mapped) as cum_area_sqkm_mapped
          ,sum(a.cum_area_sqkm_validated) as cum_area_sqkm_validated
        from pop_stats_per_country_per_month a
        left join data_preparation.ne_10m_admin_0_countries b
            on a.id = b.id
        group by
            b.region_name
            ,b.region_id
            ,a.month
            ,a.osm_feature
            ,a.impact_area_id;
    """
    db.query(sql)
    logger.info("created table 'pop_per_region_per_month'.")