import pandas as pd

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


def get_active_users_count(_filter: str):
    """Calculate the number of active users for a month.

    For each month the number of active contributors is defined by the
    number of users which have been active in the past three months.
    Hence, this value is likely to be bigger than the "actual" number of
    osm editors for this month.
    """
    db = PostgresDb()
    sql = f"set schema '{_filter}';"
    sql += f"""
            alter table tm_osm_users_per_country_per_month
            add column active_users_count int;
        """
    db.query(sql)

    sql = f"set schema '{_filter}';"
    sql += f"""
            select
                min(month) as first_month
                ,max(month) as last_month
            from tm_osm_users_per_country_per_month
        """
    first_month, last_month = db.retr_query(sql)[0]

    all_months = (
        pd.date_range(first_month, last_month, freq="MS").strftime("%Y-%m-%d").tolist()
    )

    for month in all_months:
        sql = f"set schema '{_filter}';"
        sql += f"""
            update tm_osm_users_per_country_per_month a
            set active_users_count = foo.active_users_count
            from
            (
                select
                  ne_id
                  -- this month
                  ,%(month)s::date as month
                  ,count(distinct userid) as active_users_count
                from tm_user_contributions_per_country_per_month
                where
                  -- this month
                  month <= %(month)s::date
                  and
                  -- including the last 3 months
                  month >= %(month)s::date - '2 months'::interval
                  and
                  (
                  (building_creation + building_change + building_deletion) > 0
                  or
                  (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
                  )
                group by ne_id
                order by ne_id
            ) as foo
            where a.month = %(month)s and a.ne_id = foo.ne_id
        """
        db.query(sql, {"month": month})
    logger.info("added active users count")


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"""
        -- this takes around 3 minutes for the hot-audacious projects
        DROP TABLE if exists tm_user_contributions_per_country_per_month;
        CREATE TABLE tm_user_contributions_per_country_per_month as (
            with tm_user_contributions_per_project_per_day as (
                select
                  project_id
                  ,userid
                  ,date_trunc('day', starttime) as day
                  ,count(*) session_count
                from sessions s
                group by project_id, userid, day
            )

            select
              p.ne_id
              ,p.ne_name
              ,a.userid
              ,date_trunc('month', a.day) as month
              ,sum(a.session_count) as session_count
              ,sum(b.total_count) as contribution_count
              -- here we just consider building and highway contributions
              ,sum(b.building_creation) as building_creation
              ,sum(b.building_change) as building_change
              ,sum(b.building_deletion) as building_deletion
              ,sum(b.highway_creation) as highway_creation
              ,sum(b.highway_change) as highway_change
              ,sum(b.highway_deletion) as highway_deletion
            from tm_user_contributions_per_project_per_day as a
            left join projects p
                on p.project_id = a.project_id
            left join data_preparation.osm_user_contributions_per_project_per_day
                as b on
                -- Here we match session in the HOT Tasking Manager and
                -- contributions made to OSM. We assume that a user that worked
                -- on a session in the HOT Tasking Manager and that mapped in OSM
                -- on the same day in the project region can be counted as a
                -- mapper that edited OSM through the HOT Tasking Manager
                    a.project_id = b.project_id
                    and
                    a.userid = b.userid
                    and
                    a.day = b.day
                group by ne_id, ne_name, month, a.userid
        );

        drop table if exists tm_osm_users_per_country_per_month;
        create table tm_osm_users_per_country_per_month as (

        with new_users_per_country as (
            -- Here we get the first time a users
            -- has mapped in OSM through the tasking manager
            -- for a specific country.
            -- These users have made an edit to OSM
            -- for either buildings or highways.
            select
              userid
              ,ne_id
              ,ne_name
              ,min(month) as month
            from tm_user_contributions_per_country_per_month
            where
              (building_creation + building_change + building_deletion) > 0
              or
              (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
            group by userid, ne_id, ne_name
        ),

        users_count_per_country as (
            -- number of users per month
            -- these users have made an edit to OSM
            -- for either buildings or highways
            select
              ne_id
              ,ne_name
              ,month
              ,count(userid) as users_count
            from tm_user_contributions_per_country_per_month
            where
              (building_creation + building_change + building_deletion) > 0
              or
              (highway_creation + abs(highway_change) + abs(highway_deletion)) > 0
            group by ne_id, ne_name, month
        ),

        new_users_count_per_country as (
            select
              ne_id
              ,ne_name
              ,month
              ,count(month) as new_users_count
            from new_users_per_country
            group by ne_id, ne_name, month
        ),

        cum_users_count_per_country as (
            select
              ne_id
              ,ne_name
              ,month
              ,SUM(new_users_count) over (
                partition by ne_id, ne_name order by month
              ) as cum_users_count
            from new_users_count_per_country
        )

        select
          a.ne_id
          ,a.ne_name
          ,a.month
          ,a.users_count
          ,b.cum_users_count
        from users_count_per_country a
        left join cum_users_count_per_country b
            on b.ne_id = a.ne_id and a.month = b.month
        order by a.ne_id, a.month
    );
    """
    db.query(sql)

    get_active_users_count(_filter)
    logger.info(f"Created table for {_filter}: 'tm_osm_users_per_country_per_month'.")