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


def run(_filter: str):
    """Table contains stats for each region, last 3 months and all time."""
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
        drop table if exists osm_stats_per_region;
        create table osm_stats_per_region as (

            with tm_osm_users as (
                select
                    region_id
                    ,region_name
                    ,max(cum_users_count) as tm_osm_users
                from tm_osm_users_per_region_per_month
                where region_id is not null
                group by region_id, region_name
            ),

            -- osm user stats
            osm_users as (
                select
                    a.region_id
                    ,a.region_name
                    ,max(a.cum_users_count) as osm_users
                from data_preparation.osm_users_per_region_per_month a,
                    ne_10m_admin_0_countries c
                where a.region_id = c.region_id and a.region_id is not null
                group by a.region_id, a.region_name
            )

            select
              a.region_id
              ,a.region_name
              ,min(first_month) as first_month
              ,max(last_month) as last_month
              ,sum(tm_projects) as tm_projects
              ,sum(tm_added_buildings) as tm_added_buildings
              ,sum(tm_added_roads) as tm_added_roads
              ,sum(a.healthcare) as healthcare
              ,sum(a.amenities) as amenities
              ,sum(a.places) as places
              ,sum(a.buildings) as buildings
              ,sum(a.roads_major) as roads_major
              ,max(b.tm_osm_users) as tm_osm_users
              ,max(c.osm_users) as osm_users
            from osm_stats_per_country as a
            left join tm_osm_users as b
                on a.region_id = b.region_id
            left join osm_users as c
                on a.region_id = c.region_id
            where a.region_id is not null
            group by a.region_id, a.region_name
        );
    """
    db.query(sql)
    logger.info(f"Created table for {_filter}: 'osm_stats_per_region'.")