import pandas as pd
import numpy as np

import datetime as dt

from wmfdata import hive, mariadb

query = """ 
SELECT 
    up_value as skin,
    COUNT(*) as users
FROM user_properties
WHERE 
    up_property = 'VectorSkinVersion'
GROUP BY up_value """

#define list of target wikis
wikis = ['euwiki','frwiktionary', 'ptwikiversity', 'fawiki','hewiki', 'frwiki']

up_skin=list()
for wiki in wikis:
    prefs = mariadb.run(
      query.format(),
      wiki
    )
    up_skin.append(prefs)

skin= pd.concat(up_skin)

skin_aliases = {
    "0":"unknown",
    "1":"legacy",
    "2":"modern"
}

skin= skin.replace({"skin": skin_aliases})

#List of wikis to correspond to data values 
wikis_list = ['euwiki','euwiki', 'frwiktionary',  'frwiktionary',  'frwiktionary', 'ptwikiversity', 'ptwikiversity','fawiki', 'fawiki', 'fawiki','hewiki', 'hewiki', 'hewiki', 'frwiki', 'frwiki', 'frwiki']

skin['wiki'] = wikis_list

user_skin_bywiki=pd.pivot_table(skin, index=['wiki','skin'],values=['users'],aggfunc=np.sum)

# collect total number of users on each wiki

query = """ 
SELECT 
    COUNT(DISTINCT user_id) AS num_users
FROM user"""

user_count = mariadb.run(commands = query, dbs = wikis, format="pandas")

user_count['wiki'] = wikis

# Create list of legacy users - these are all users that opt-out assuming modern is the default

legacy_users = skin[skin['skin']=='legacy']
#rename colums

legacy_users.columns = ['skin', 'num_legacy_users', 'wiki']

# join to user_count table to obtain opt-out rate for each wiki

opt_out_rate = legacy_users.merge(user_count, left_on = 'wiki', right_on = 'wiki')

HIVE_SNAPSHOT = "2020-09"
START_OF_DATA = "2019-09-01"
END_OF_DATA = "2020-10-01"

#all active editors from the past one year

active_editor_query = """

WITH yr_proj_edits as (
    select
        event_user_text as user,
        event_user_id as user_id,
        wiki_db as proj,
        sum(if(wiki_db = "wikidatawiki", 0.1, 1)) as content_edits,
        max(event_timestamp) as latest_edit
    from wmf.mediawiki_history
    where
        -- review target wikis
        wiki_db IN ('euwiki','frwiktionary', 'ptwikiversity', 'fawiki','hewiki', 'frwiki') and
        -- REGISTERED
        event_user_is_anonymous = false and
        
        -- NON-BOT
        size(event_user_is_bot_by) = 0 and
        not array_contains(event_user_groups, "bot") and
        
        -- CONTENT EDITS
        event_entity = "revision" and
        event_type = "create" and
        page_namespace_is_content = true and
        
        -- FROM THE LAST YEAR
        event_timestamp >= "{START_OF_DATA}" and event_timestamp < "{END_OF_DATA}" and
        
        -- FROM THE LATEST SNAPSHOT
        snapshot = "{hive_snapshot}"
    
    -- PER USER, PER WIKI
    group by event_user_text, event_user_id, wiki_db
)

-- FINAL SELECT OF
select 
    user as user_name,
    user_id as user_id,
    proj as wiki,
    global_edits

from 
-- JOINED TO THEIR HOME WIKI AND GLOBAL EDITS
(
    select
        user,
        user_id,
        proj,
        -- in the unlikely event that wikis are tied by edit count and latest edit, 
        -- row_number() will break it somehow
        row_number() over (partition by user order by content_edits desc, latest_edit desc) as rank,
        sum(content_edits) over (partition by user) as global_edits
    from yr_proj_edits
) yr_edits
where
rank = 1
and global_edits>= 5
"""

active_editor = hive.run(
    active_editor_query.format(
        hive_snapshot = HIVE_SNAPSHOT,
        START_OF_DATA= START_OF_DATA,
        END_OF_DATA=END_OF_DATA
    )
)

#Querying user_properties for getting the skin preferences set by the active editors we got in the above query

query='''
SELECT 
  up_value AS skin, 
  COUNT(*) AS users
FROM user_properties
WHERE up_user in ({users})
AND up_property = "VectorSkinVersion"
GROUP BY up_value
'''

#define list of target wikis
wikis = ['euwiki','frwiktionary', 'ptwikiversity', 'fawiki','hewiki', 'frwiki']

# Looping through each wiki for the list of users for each skin

up_skin=list()
for wiki in wikis:
    user_ids = active_editor[active_editor['wiki'] == wiki]["user_id"]
    user_list = ','.join([str(u) for u in user_ids])
    prefs = mariadb.run(
      query.format(users=user_list),
      wiki
    )
    up_skin.append(prefs)

skin= pd.concat(up_skin)

#List of wikis to correspond to data values 
wikis_list = ['euwiki','euwiki', 'frwiktionary', 'frwiktionary', 'ptwikiversity', 'ptwikiversity','fawiki', 'fawiki', 'fawiki','hewiki', 'hewiki', 'frwiki', 'frwiki', 'frwiki']

# add wiki column
skin['wiki'] = wikis_list

# Define skin type for each property values
skin_aliases = {
    "0":"unknown",
    "1":"legacy",
    "2":"modern"
}

skin= skin.replace({"skin": skin_aliases})