#!/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
import mailbox
import email.utils
from bs4 import BeautifulSoup
import urllib.request
try:
    import urllib.request as urllib2
except ImportError:
    import urllib2
   
    
pw = ''
dbuser = ''
dbhost = 'flossdata.syr.edu'
db = ''

dbconn = pymysql.connect(host= dbhost,
                         user= dbuser,
                         passwd= pw,
                         db= db,
                         use_unicode=True,
                         charset="utf8mb4")
cursor = dbconn.cursor()

#dbtest = pymysql.connect(host = dbhost,
#                         user = dbuser
#                         passwd = pw
#                         db = databaseName
#                         use_unicode=True,
#                         charset = "utf8mb4")
#cursorTest = dbtest.cursor()

def getDatabaseNames():

    hdr = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11',
           'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
           'Accept-Charset': 'ISO-8859-1,utf-8;q=0.7,*;q=0.3',
           'Accept-Encoding': 'none',
           'Accept-Language': 'en-US,en;q=0.8',
           'Connection': 'keep-alive'}
       
    archiveHome = 'http://mail-archives.apache.org/mod_mbox/'       
    req = urllib2.Request(archiveHome,headers=hdr)
    archiveListPage = urllib2.urlopen(req).read()
    
    schemaList = []
    projectList = mainSoup.find_all('option')
    for Schema in projectList:
        schemaName = Schema.get('value')
        schemaList.append(schemaName)
    print(schemaList)

### TODO: connect to each database before it selects emails,
###       place connection close lines,
###       test, and then test cycling.




def selectEmails(projectName):
    tableName = projectName.replace('-','_')+'_email'
    
    emailParts = 'SELECT email_list, \
                   from_name, \
                   from_email, \
                   email_date,\
                   email_id, \
                   FROM'
    executeLine = emailParts + tableName
   
    cursor.execute(executeLine)
    emailList = cursor.fetchall()
    for email in emailList:
        newDate = cleanDate(email[3])
        fromName = cleanName(email[1])
        emailNum = email[4]
        femail = email[2]
        updateEmailTable(newDate, fromName, tableName, emailNum, femail)
       
def cleanDate(textDate):
    date = textDate.split(', ')[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(' ')
    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
    return dateAndTime

    
def cleanName(ogName):
    if '=' in ogName:
        name = 'Unknown'
    elif ogName = '':
        name = 'Unknown'
    if '(' in ogName:
            trueName = split('(')[0]
    elif '[' in ogName:
        trueName = split('[')[0]
    
    
    if ' ' in trueName:
        trueName = titlecase(trueName)
    if ',' in trueName:
        first = trueName.split(', ')[1]
        last = trueName.split(', ')[0]
        trueName = first + ' ' + last
    
    
def updateEmailTable(date, name, tableName, emailNum, femail):
    firstLine = 'from_name_clean = %s \
                 email_date_clean = %s \
                 WHERE email_id = %s \
                 AND from_email = %s'
    executeLine = 'UPDATE ' + tableName + ' SET ' + firstLine
   
    cursor.execute(executeUpdate,(name, date, emailNum, femail))
    dbconn.commit.commit
    
    
    #Tue, 25 Mar 2014 14:43:45 +0000 (UTC)
    
cleanDate('Tue, 25 Mar 2014 14:43:45 +0000 (UTC)')
                   
#getDatabaseNames

         
