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_region_per_month;
        create table tm_osm_contributions_per_region_per_month as (
            with tm_osm_contributions_sum as (
                select
                  b.region_id
                  ,b.region_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 region_id, region_name, month
                order by region_id, region_name, month
            ),

            tm_osm_contributions_cum_sum as (
                select
                  region_id
                  ,month
                  ,SUM(session_count) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_session_count
                  ,SUM(contribution_count) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_contribution_count
                  ,SUM(building_creation) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_building_creation
                  ,SUM(building_change) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_building_change
                  ,SUM(building_deletion) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_building_deletion
                  ,SUM(highway_creation) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_highway_creation
                  ,SUM(highway_change) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_highway_change
                  ,SUM(highway_deletion) OVER (
                    PARTITION BY region_id
                    ORDER BY month
                  ) as cum_highway_deletion
                from tm_osm_contributions_sum a
                order by region_id, region_name, month
            )

            select
              a.region_id
              ,a.region_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 a
            left join tm_osm_contributions_cum_sum b
                on a.region_id = b.region_id and a.month = b.month
        );
    """
    db.query(sql)
    logger.info(
        f"Created table for {_filter}: 'tm_osm_contributions_per_region_per_month'."
    )