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


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

            with tm_osm_contributions_per_project as (
                select
                    p.ne_id
                    ,p.ne_name
                    ,p.region_id
                    ,p.region_name
                    ,a.project_id
                    ,MIN(a.month) as first_edit
                    ,MAX(a.month) as last_edit
                    ,MAX(b.cum_building_creation) as tm_added_buildings
                    ,MAX(b.cum_highway_creation) as tm_added_roads
                from project_activity_sessions_per_month a
                left join projects p
                on p.project_id = a.project_id
                left join tm_osm_contributions_per_project_per_month as b
                on b.project_id = a.project_id and b.month = a.month
                group by p.ne_id, p.ne_name, p.region_id, p.region_name, a.project_id
            ),

            tm_osm_contributions as (
                select
                  ne_id
                  ,count(distinct project_id) as tm_projects
                  ,MIN(first_edit) as first_month
                  ,MAX(last_edit) as last_month
                  ,sum(tm_added_buildings) as tm_added_buildings
                  ,sum(tm_added_roads) as tm_added_roads
                from tm_osm_contributions_per_project
                where ne_id is not null
                group by ne_id, ne_name, region_id, region_name
            ),

            osm_snapshots_per_month as (
                select
                  a.*
                from data_preparation.osm_stats_per_country_per_month a,
                    ne_10m_admin_0_countries c
                where a.ne_id = c.id
                order by ne_id, a.month
            ),

            osm_snapshots as (
                select
                  ne_id
                  ,(array_agg(healthcare)) as healthcare
                  ,(array_agg(amenities)) as amenities
                  ,(array_agg(places)) as places
                  ,(array_agg(buildings)) as buildings
                  ,(array_agg(roads_major)) as roads_major
                from osm_snapshots_per_month
                group by ne_id
            ),

            tm_osm_users as (
                select
                    ne_id
                    ,ne_name
                    ,max(cum_users_count) as tm_osm_users
                from tm_osm_users_per_country_per_month
                where ne_id is not null
                group by ne_id, ne_name
            ),

            -- osm user stats
            osm_users as (
                select
                    a.ne_id
                    ,c.name
                    ,max(a.cum_users_count) as osm_users
                from data_preparation.osm_users_per_country_per_month a,
                    ne_10m_admin_0_countries c
                where a.ne_id = c.id
                group by a.ne_id, c.name
            )

            select
              x.id as ne_id
              ,x.iso_a3
              ,x.name as ne_name
              ,x.region_id
              ,x.region_name
              ,a.tm_projects
              ,a.first_month
              ,a.last_month
              ,a.tm_added_buildings
              ,a.tm_added_roads
              -- make sure to get latest values
              ,b.healthcare[array_length(b.healthcare, 1)] as healthcare
              ,b.amenities[array_length(b.amenities, 1)] as amenities
              ,b.places[array_length(b.places, 1)] as places
              ,b.buildings[array_length(b.buildings, 1)] as buildings
              ,b.roads_major[array_length(b.roads_major, 1)] as roads_major
              ,c.tm_osm_users
              ,d.osm_users
            from ne_10m_admin_0_countries x
            left join tm_osm_contributions as a
                on x.id = a.ne_id
            left join osm_snapshots as b
                on x.id = b.ne_id
            left join tm_osm_users as c
                on x.id = c.ne_id
            left join osm_users as d
                on x.id = d.ne_id
        );
    """
    db.query(sql)
    logger.info(f"Created table for {_filter}: 'osm_stats_per_country'.")
