#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Jun 30 10:45:34 2017

@author: jhartmann
"""

import calendar
import sys
import pymysql
import datetime


   
    
pw = 'Earwig53'
dbuser = 'jhartmann'
dbhost = 'flossdata.syr.edu'
db = ''

#########################################################################################################
### Gets the list of tables and which database they are in from the server
### this only works for emails, not irc chat
    
def getProjectList():
    db = 'information_schema'
    selectTables = 'SELECT TABLE_NAME, \
                    TABLE_SCHEMA \
                    From TABLES \
                    WHERE table_schema LIKE "apache%" \
                    AND TABLE_Name LIKE "%email"'
                    
    dbProjects = pymysql.connect(host= dbhost,
                             user= dbuser,
                             passwd= pw,
                             db= db,
                             use_unicode=True,
                             charset="utf8mb4")
    projCursor = dbProjects.cursor()
    projCursor.execute(selectTables)
    tableList = projCursor.fetchall()
    return tableList
    dbProjects.close()

#########################################################################################################
### Main portion that branches off to the seperate cleaning programs
### it also updates the table to add the new columns for the fresh data
### it does not return anything
### it leaads the the Update statement

def cleanData(db, table, i):
    dbName = db 
    dbtest = pymysql.connect(host = dbhost,
                         user = dbuser,
                         passwd = pw,
                         db = dbName,
                         use_unicode=True,
                         charset = "utf8mb4")
    cursor = dbtest.cursor()
    
    updateTableColumn = 'ALTER TABLE ' + table + ' \
                         ADD (email_date_clean VARCHAR(100),\
                             from_name_clean VARCHAR(200),\
                             screen_name VARCHAR(50),\
                             organization VARCHAR(100))'
    try:                         
        cursor.execute(updateTableColumn)        
    except: 
        print('tableUpdatedPreviously')   
    dbtest.close() 
                         
    for email in selectEmails(db, table):
        newDate = cleanDate(email[3])
        fromName = cleanName(email[1], email[2])[0]
        emailNum = email[4]
        femail = email[2]
        screenName = cleanName(email[1], email[2])[1]
        userOrg = getUserOrg(fromName, screenName)
        screenName = userOrg[0]
        org = userOrg[1]
        updateEmailTable(newDate, fromName, screenName, org, emailNum, femail, table, db, i)

#########################################################################################################
### Selects emails from the table and databse given,
### returns a list of all emails to be cleaned

def selectEmails(db, table):
    tableName = table
    dbName = db
    dbselect = pymysql.connect(host = dbhost,
                         user = dbuser,
                         passwd = pw,
                         db = dbName,
                         use_unicode=True,
                         charset = "utf8mb4")
    cursorS = dbselect.cursor()
    
    
    emailParts = 'SELECT email_list, \
                   from_name, \
                   from_email, \
                   email_date,\
                   email_id \
                   FROM '
    selectEmailsCommand = emailParts + tableName + ' WHERE email_id  > 9854' 
    cursorS.execute(selectEmailsCommand)
    emailList = cursorS.fetchall()
    dbselect.close()
    return emailList

#########################################################################################################
### takes in the determinned true name and screen name
### returns the organization afiliation if available
### if the screen name is not alerady determinned from the 
### fromName  this will try and get it from the committer table
  
def getUserOrg(name, svn):
    dbconnUser = pymysql.connect(host = dbhost,
                          user = dbuser,
                          passwd = pw,
                          db = 'test',
                          use_unicode=True,
                          charset="utf8mb4")
    cursorUser = dbconnUser.cursor()

    userName = ''
    organization = ''
    
    getUserNameServer = 'SELECT svn_id \
                         FROM apache_committers_updated \
                         WHERE real_name = %s'
                         
    getOrganization = 'SELECT organization_name \
                       FROM apache_committers_updated \
                       WHERE svn_id = %s \
                       AND organization_name <> ""' 
        
    getUserName2Server = 'SELECT svn_id \
                         FROM apache_committers_updated \
                         WHERE svn_id = %s'                   
    
    if svn == '':
        try:
            cursorUser.execute(getUserNameServer, (name))
            userName = cursorUser.fetchone()[0]
        except:
            userName = ''
   
    # Test to see if the svn from the from colum stored previously as svn
    # is a true svn, it can also just be a github username or some irrelevant
    # naming function that can't be connected with this program
    
    else:
        try:
            cursorUser.execute(getUserName2Server, (svn))
            userName = cursorUser.fetchone()[0]
        except:
            userName = ''
    
    if userName != '':
        try:
            cursorUser.execute(getOrganization, (userName))
            organization = cursorUser.fetchone()[0]
        except:
            organization = ''
    else:
        organization = ''
        
    infoList = []
    infoList.append(userName)
    infoList.append(organization)
    
    dbconnUser.close()
    return infoList
#########################################################################################################
### The date was previously stored in a text format in the database
### This funciton takes in the date as text
### it breaks it into the smaller parts 
### returns  the date/time in Datetime format

def cleanDate(textDate):
    try:
        date = textDate.split(' ', 1)[1]
        #25 Mar 2014 14:43:45 +0000 (UTC)
        date = date.split(' -')[0]
        date = date.split(' +')[0]
        #25 Mar 2014 14:43:45
        dateparts = []  
        dateparts = date.split(' ')
        if dateparts[0] == '':
            dateparts.pop(0)
                
        dateMonthWord = dateparts[1]
        monthNum = str(list(calendar.month_abbr).index(dateMonthWord))
            
      
        dateNum = dateparts[0]
        dateYear = dateparts[2]
        dateTime = dateparts[3]
        
        dateString = dateYear + '-' + monthNum + '-' + dateNum
        dateAndTime = dateString + ' '+ dateTime
    except: 
        dateAndTime = ''
    return dateAndTime
#########################################################################################################
### This function is executed when there is no known name from the email
### this function then executes the getRealName function if qualifications are met
### it returns a real Name 

def realNamefromEmail(femail):
    realName = ''
    if '@apache.org' in femail and 'jira' not in femail:
        userName = femail.split('@')[0]
        realName = getRealName(userName)
    
    return(realName)

#########################################################################################################
### getRealName takes in the user name and runs a Select query
### if successfull it returns the real name that is associated
### if unsuccessful the realName is returned as 'Unknown'

def getRealName(userName):
    dbconnUser = pymysql.connect(host = dbhost,
                          user = dbuser,
                          passwd = pw,
                          db = 'test',
                          use_unicode=True,
                          charset="utf8mb4")
    cursorUser = dbconnUser.cursor()

    getRealName = 'SELECT real_name FROM apache_committers_updated WHERE svn_id = %s' 
    try:
        cursorUser.execute(getRealName, (userName))
        name = cursorUser.fetchone()[0]   
        dbconnUser.close()
    except:
        name = 'Unknown'
        dbconnUser.close()
    return(name)
 
#########################################################################################################
### This function takes in the assumed trueName of the sender from the mbox getter and cleaner
### it also takes in the from email
### the name (if previously existing) is stripped and cleaned
### if no name is found, the email is used in attempt to retrieve the name
### this function may also refine the screen name during its processes
### which is returned as well (otherwishe screenName = '')

def cleanName(ogName, femail):
    trueName = ogName
    screenName = ''
    if '=' in ogName:
        trueName = 'Unknown'
    elif ogName == '':
        trueName = realNamefromEmail(femail)
    if '(' in ogName:
        trueName = ogName.split('(')[0]
    if '[' in ogName:
        trueName = ogName.split('[')[0]
    
    
    if ' ' in trueName:
        trueName = trueName.title()
    if ',' in trueName:
        first = trueName.split(', ')[1]
        last = trueName.split(', ')[0]
        trueName = first + ' ' + last  
        
    if ' ' not in trueName:
        if '@apache' in femail:
            trueName = realNamefromEmail(femail)
            screenName = femail.split('@')
        else:
            screenName = trueName
            trueName = getRealName(screenName)
        
    nameDetails = []
    nameDetails.append(trueName)
    nameDetails.append(screenName)
    return nameDetails
    
    
def updateEmailTable(date, name, screenName, org, emailNum, femail, tableName, dbName, i):
    updatePieces = ' from_name_clean = %s, \
                  email_date_clean = %s, \
                  last_updated = %s, \
                  screen_name = %s, \
                  organization = %s \
                  WHERE email_id = %s'
                  
    executeLine = 'UPDATE ' + tableName + ' SET ' + updatePieces
    dbupdate = pymysql.connect(host = dbhost,
                         user = dbuser,
                         passwd = pw,
                         db = dbName,
                         use_unicode=True,
                         charset = "utf8mb4")
    cursorU = dbupdate.cursor()
    now = datetime.datetime.now() 
    print(i, emailNum, date, screenName, org)
    
    try:
        cursorU.execute(executeLine,(name, date, now, screenName, org, emailNum))
        dbupdate.commit()
        dbupdate.close()
    except pymysql.Error as error:
             print(error)
             dbupdate.rollback()
    
   
                   
projectList = getProjectList()
#print(projectList)
i = 4
for project in projectList:
    databaseName = ''
    tableName = ''
    databaseName = projectList[i][1]
    tableName = projectList[i][0]
    print('############################################', databaseName, tableName)
    cleanData(databaseName, tableName, i)
    i = i + 1

         
