from sqlalchemy import create_engine
import sys, os
import pandas as pd

constr = 'mysql+pymysql://{user}:{pwd}@{host}'.format(user=os.environ['MYSQL_USERNAME'],
                                                      pwd=os.environ['MYSQL_PASSWORD'],
                                                      host=os.environ['MYSQL_HOST'])
def _revs_by_gender_in_month(wiki, month=None, year=None):
    if month is None:
        month = datetime.date.today().month
    if year is None:
        year = datetime.date.today().year
    # TODO: validate params
    next_month = datetime.date(year, month + 1, 1).isoformat()
    month = datetime.date(year, month, 1).isoformat()
    
    constr = 'mysql+pymysql://{user}:{pwd}@{host}'.format(user=os.environ['MYSQL_USERNAME'],
                                                      pwd=os.environ['MYSQL_PASSWORD'],
                                                      host=os.environ['MYSQL_HOST'])
    con = create_engine(constr)
    
    edits_in_month = pd.read_sql(
        '''select rv.rev_user, COUNT(rv.rev_id) as edits
        from {wiki}.revision rv
        where rv.rev_timestamp BETWEEN DATE_FORMAT("{month}" ,'%%Y%%m%%d%%H%%i%%s') 
                                   and DATE_FORMAT("{next_month}" ,'%%Y%%m%%d%%H%%i%%s')
        and rv.rev_user != 0
        group by rv.rev_user;'''.format(wiki=wiki, month=month, next_month=next_month),
        con)
    edits_in_month_with_gender = pd.read_sql(
        '''select rv.rev_user, ps.up_value as gender
        from {wiki}.revision rv left join {wiki}.user_properties ps on rv.rev_user = ps.up_user
        where rv.rev_timestamp BETWEEN DATE_FORMAT("{month}" ,'%%Y%%m%%d%%H%%i%%s') and DATE_FORMAT("{next_month}" ,'%%Y%%m%%d%%H%%i%%s')
        and rv.rev_user != 0
        and (ps.up_property = 'gender')
        group by rv.rev_user;'''.format(wiki=wiki, month=month, next_month=next_month),
        con)
    edits_in_month = edits_in_month.merge(edits_in_month_with_gender, on='rev_user', how="outer")
    edits_in_month['gender'] = edits_in_month['gender'].str.decode('utf8')
    edits_in_month.fillna('not specified', inplace=True)
    return edits_in_month


def edits_by_gender_in_month(wiki, month=None, year=None):
    return _revs_by_gender_in_month(wiki, month, year).groupby('gender')['rev_user'].count().to_json()
edits_by_gender_in_month('ptwiki_p')
'{"female":54,"male":680,"not specified":3627}'
edits_by_gender_in_month('ptwiki_p')
'{"female":54,"male":680,"not specified":3627}'