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


def run(_filter: str) -> None:
    """Create table for number of users mapping OSM through the Tasking Manager.

    Here we only consider users that have mapped buildings or highways.
    """
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
        drop table if exists tm_osm_contributions_per_country_per_month;
        create table tm_osm_contributions_per_country_per_month as (
            with all_months_and_countries as (
                select
                    generate_series(min(month), max(month), '1 month') as month
                    ,id
                    ,name
                from tm_osm_contributions_per_project_per_month
                left join ne_10m_admin_0_countries on 1=1
                group by id, name
                order by id, name, month
            ),
            tm_osm_contributions_sum as (
                select
                  b.ne_id
                  ,b.ne_name
                  ,a."month"
                  ,SUM(session_count) as session_count
                  ,SUM(contribution_count) as contribution_count
                  ,SUM(building_creation) as building_creation
                  ,SUM(building_change) as building_change
                  ,SUM(building_deletion) as building_deletion
                  ,SUM(highway_creation) as highway_creation
                  ,SUM(highway_change) as highway_change
                  ,SUM(highway_deletion) as highway_deletion
                from tm_osm_contributions_per_project_per_month a
                left join projects b
                    on a.project_id = b.project_id
                group by ne_id, ne_name, month
                order by ne_id, ne_name, month
            ),
            tm_osm_contributions_sum_all_months as (
                select
                    a.id as ne_id
                    ,a.name as ne_name
                    ,a.month as month
                    ,case
                        when session_count is null then 0 else session_count
                    end as session_count
                    ,case
                        when contribution_count is null then 0 else contribution_count
                    end as contribution_count
                    ,case
                        when building_creation is null then 0 else building_creation
                    end as building_creation
                    ,case
                        when building_change is null then 0 else building_change
                    end as building_change
                    ,case
                        when building_deletion is null then 0 else building_deletion
                    end as building_deletion	
                    ,case
                        when highway_creation is null then 0 else highway_creation
                    end as highway_creation
                    ,case
                        when highway_change is null then 0 else highway_change
                    end as highway_change
                    ,case
                        when highway_deletion is null then 0 else highway_deletion
                    end as highway_deletion	
                from all_months_and_countries a
                left join tm_osm_contributions_sum b on
                    a.month = b.month
                    and a.name = b.ne_name
            ),
            tm_osm_contributions_cum_sum as (
                select
                  ne_id
                  ,month
                  ,SUM(session_count) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_session_count
                  ,SUM(contribution_count) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_contribution_count
                  ,SUM(building_creation) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_building_creation
                  ,SUM(building_change) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_building_change
                  ,SUM(building_deletion) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_building_deletion
                  ,SUM(highway_creation) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_highway_creation
                  ,SUM(highway_change) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_highway_change
                  ,SUM(highway_deletion) OVER (
                    PARTITION BY ne_id
                    ORDER BY month
                  ) as cum_highway_deletion
                from tm_osm_contributions_sum_all_months a
                order by ne_id, ne_name, month
            )
            select
              a.ne_id
              ,a.ne_name
              ,a.month
              ,session_count
              ,cum_session_count
              ,contribution_count
              ,cum_contribution_count
              --
              ,building_creation
              ,building_change
              ,building_deletion
              ,highway_creation
              ,highway_change
              ,highway_deletion
              --
              ,cum_building_creation
              ,cum_building_change
              ,cum_building_deletion
              ,cum_highway_creation
              ,cum_highway_change
              ,cum_highway_deletion
            from tm_osm_contributions_sum_all_months a
            left join tm_osm_contributions_cum_sum b
                on a.ne_id = b.ne_id and a.month = b.month
            order by month
        );
    """
    db.query(sql)
    logger.info(
        f"Created table for {_filter}: 'tm_osm_contributions_per_country_per_month'."
    )