import json
import requests

from wmfdata import mariadb, spark, utils
import pandas as pd

wiki_codes = utils.get_dblist('growthexperiments')
wikis = spark.run('''
SELECT *
FROM canonical_data.wikis
WHERE database_code IN ({dbnames})
'''.format(dbnames=', '.join(["'%s'" % x for x in wiki_codes])))

dfs = []
for _, row in wikis.iterrows():
    print(row.database_code)
    menteesDf = mariadb.run('''
    SELECT gemm_mentor_id, COUNT(*) AS mentees
    FROM growthexperiments_mentor_mentee
    WHERE gemm_mentor_role = 'primary'
    GROUP BY gemm_mentor_id
    ''', row.database_code, use_x1=True)
    
    if menteesDf.gemm_mentor_id.count() == 0:
        #print('Skipping %s' % row.database_code)
        continue
    
    usernamesDf = mariadb.run('''
    SELECT actor_user, actor_name AS mentor_name
    FROM actor
    WHERE actor_user IN ({ids})
    '''.format(ids=', '.join([str(x) for x in menteesDf.gemm_mentor_id])), row.database_code)
    
    # download the list of mentors
    r = requests.get('https://%s/w/index.php?title=MediaWiki:GrowthMentors.json&action=raw&ctype=application/json' % row.domain_name)
    mentors = r.json()['Mentors']
    if len(mentors) == 0:
        print('Skipping %s, no mentors' % row.database_code)
        continue
    mentorsDf = pd.read_json(json.dumps(mentors), orient='index')
    mentorsDf.index.name = 'mentor_id'
    
    usernamesDf.set_index('actor_user', inplace=True)
    menteesDf.set_index('gemm_mentor_id', inplace=True)
    
    df = menteesDf.join(usernamesDf).join(mentorsDf)
    df.reset_index(inplace=True)
    df['wiki'] = row.domain_name
    df = df[['wiki', 'mentor_name', 'mentees', 'automaticallyAssigned']].fillna('Not a mentor')
    dfs.append(df)

df = pd.concat(dfs)
utils.df_to_remarkup(df.loc[df.automaticallyAssigned == True][['wiki', 'mentees']].groupby('wiki').mean().reset_index())