import sys, os, time
import paramiko as pm
import boto as bt
import pandas as pd

AWSAccessKeyId, AWSSecretKey = ( line.strip().split('=')[1] for line in open('/Users/brian/rootkey.csv','r') )
sshKeyName, instancePass, mysqlPass, myIP = ( line.strip().split('=')[1] for line in open('/Users/brian/passwords.csv','r') )

def waitUntilReady(obj,desiredStatus):
    while obj.update() != desiredStatus:
        sys.stdout.write(".")
        time.sleep(1)

ec2 = bt.connect_ec2( aws_access_key_id = AWSAccessKeyId, 
                      aws_secret_access_key = AWSSecretKey )

res = ec2.run_instances( 'ami-146e2a7c', # "Amazon Linux AMI"
                         key_name = sshKeyName, 
                         instance_type = 'r3.large', 
                         security_groups = ['onlyMyIP'], # previously-configured security group
                         placement = 'us-east-1a' ) # region

allInstances = res.instances
instance = allInstances[0]

mysqlVolume = ec2.create_volume(size=100, zone='us-east-1a', snapshot=None, volume_type='gp2')
givenDataVolume = ec2.create_volume(size=20, zone='us-east-1a', snapshot='snap-ac47f4c5', volume_type='gp2')

time.sleep(10) # to make sure the instance is registered before checking if it is ready
waitUntilReady(instance,'running')
waitUntilReady(mysqlVolume,'available')
waitUntilReady(givenDataVolume,'available')
instance, mysqlVolume, givenDataVolume

att1 = ec2.attach_volume(mysqlVolume.id, instance.id, '/dev/xvdc')
att1 = ec2.attach_volume(givenDataVolume.id, instance.id, '/dev/xvdb')

waitUntilReady(mysqlVolume,'in-use')
waitUntilReady(givenDataVolume,'in-use')

def establishSSHconnection():
    rsaKey = pm.RSAKey.from_private_key_file(sshKeyName + '.pem')
    ssh = pm.SSHClient()
    ssh.set_missing_host_key_policy(pm.AutoAddPolicy())
    totalTime = 0 
    connected = False
    while not connected:
        try:
            ssh.connect( str(instance.public_dns_name), 
                         username = 'ec2-user', 
                         pkey = rsaKey )
            connected = True
        except Exception as e: 
            sys.stdout.write(".")
            time.sleep(1)
            totalTime += 1
            if totalTime >= 300:
                sys.stdout.write( str(instance.public_dns_name) + ': ' + 
                                  "Timed out waiting for instance to get ssh ready\n" )
                break
    return ssh

def prettyOut(obj):
    l = [r.strip('\n').split('\t') for r in obj.readlines()]
    if len(l) > 1:
        df = pd.DataFrame(l[1:len(l)])
        df.columns = l[0]
    else:
        df = pd.DataFrame(l)
    return df

def sshCommandWait(cmd,pretty=False):
    ssh = establishSSHconnection()
    stdin, stdout, stderr = ssh.exec_command(cmd)
    if pretty:
        stdout = prettyOut(stdout)
        stderr = prettyOut(stderr)
    else:
        stdout = stdout.readlines()
        stderr = stderr.readlines()
    ssh.close()
    return stdout, stderr

def sshCommandNoWait(cmd):
    ssh = establishSSHconnection()
    ssh.exec_command(cmd)
    ssh.close()

def sftpSendFile(localFile,remoteFile):
    ssh = establishSSHconnection()
    ftp = ssh.open_sftp()
    ftp.put(localFile,remoteFile)
    ftp.close()
    ssh.close()

def sftpGetFile(remoteFile,localFile):
    ssh = establishSSHconnection()
    ftp = ssh.open_sftp()
    try:
        ftp.get(remoteFile,localFile)
        success = True
    except:
        success = False
    ftp.close()
    ssh.close()
    return success

o,e = sshCommandWait('sudo echo hello')
o,e

o,e = sshCommandWait('df -h')
o

o,e = sshCommandWait('lsblk')
o

o,e = sshCommandWait('sudo mkfs -t ext4 /dev/xvdc')

o,e = sshCommandWait('sudo mkdir /mnt/givenDataVolume')
o,e = sshCommandWait('sudo mkdir /mnt/mysqlVolume')
o,e = sshCommandWait('sudo mount /dev/xvdb /mnt/givenDataVolume')
o,e = sshCommandWait('sudo mount /dev/xvdc /mnt/mysqlVolume')

o,e = sshCommandWait('df -h')
o

o,e = sshCommandWait('sudo yum -y update')
o,e = sshCommandWait('sudo yum -y install mysql')
o,e = sshCommandWait('sudo mkdir /var/lib/mysql')
o,e = sshCommandWait('sudo yum -y install mysql-server')

o,e = sshCommandWait('sudo mkdir -p /mnt/mysqlVolume/data')
o,e = sshCommandWait('sudo mkdir -p /mnt/mysqlVolume/tmp')
o,e = sshCommandWait('sudo chown -R mysql.mysql /mnt/mysqlVolume/data')
o,e = sshCommandWait('sudo chown -R mysql.mysql /mnt/mysqlVolume/tmp')

cmd  = 'sudo sed -i '
cmd += '"s/datadir\=\/var\/lib\/mysql/datadir\=\/mnt\/mysqlVolume\/data\\ntmpdir\=\/mnt\/mysqlVolume\/tmp/g" '
cmd += '/etc/my.cnf'
o,e = sshCommandWait(cmd)
o,e

o,e = sshCommandWait('sudo /etc/init.d/mysqld start')

o,e = sshCommandWait('mysqladmin -u root password "' + mysqlPass + '"')
o,e = sshCommandWait('sudo chkconfig mysqld on')

o,e = sshCommandWait('mysql -e "SHOW DATABASES;" -u root --password=' + mysqlPass, True)
o

o,e = sshCommandWait('mysql -e "CREATE DATABASE weather;" -u root --password=' + mysqlPass)

sqlcommand  = 'CREATE TABLE country '
sqlcommand += '( FIPS_ID VARCHAR(2) PRIMARY KEY, country_name VARCHAR(47) ) '
sqlcommand += ';'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather')

sqlcommand = 'DESCRIBE country;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand  = 'CREATE TABLE station '
sqlcommand += '( USAF VARCHAR(6) PRIMARY KEY, WBAN VARCHAR(6), '
sqlcommand += '  station_name VARCHAR(30), CTRY VARCHAR(2), FIPS_ID VARCHAR(2), ST VARCHAR(2), callid VARCHAR(4), '
sqlcommand += '  lat INT, lon INT, elev INT ) '
sqlcommand += ';'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather')

sqlcommand = 'DESCRIBE station;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand  = 'CREATE TABLE observation '
sqlcommand += '( STNYMD VARCHAR(14) PRIMARY KEY, '
sqlcommand += '  STN VARCHAR(6), WBAN VARCHAR(6), year INT, month INT, day INT, '
sqlcommand += '  temp INT, dewp INT, slp INT, stp INT, visib INT, '
sqlcommand += '  wdsp INT, mxspd INT, gust INT, maxtemp INT, mintemp INT, '
sqlcommand += '  prcp INT, sndp INT, FRSHTT VARCHAR(6) ) '
sqlcommand += ';'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather')

sqlcommand = 'DESCRIBE observation;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand  = 'LOAD DATA LOCAL INFILE \'\/mnt\/givenDataVolume\/country-list.txt\' '
sqlcommand += 'INTO TABLE country '
sqlcommand += 'FIELDS TERMINATED BY \'          \' '
sqlcommand += 'LINES TERMINATED BY \'\n\' '
sqlcommand += 'IGNORE 2 LINES '
sqlcommand += '; '
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather --local-infile')

sqlcommand = 'SELECT * FROM country LIMIT 5;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand  = 'LOAD DATA LOCAL INFILE \'\/mnt\/givenDataVolume\/ish-history.csv\' '
sqlcommand += 'INTO TABLE station '
sqlcommand += 'FIELDS TERMINATED BY \',\' ENCLOSED BY \'\\"\''
sqlcommand += 'LINES TERMINATED BY \'\n\' '
sqlcommand += 'IGNORE 1 LINES '
sqlcommand += '; '
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather --local-infile')

sqlcommand = 'SELECT * FROM station LIMIT 5;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sftpSendFile('populateDB.py','populateDB.py')

sshCommandNoWait('sudo nohup python populateDB.py ' + mysqlPass + ' > proc.out 2> proc.err < /dev/null &')

fileReady = False
fillTime = 0
while not fileReady:
    fileReady = sftpGetFile('end.txt','test.txt')
    if not fileReady:
        time.sleep(60)
        fillTime += 1
        if fillTime > 100*60:
            break
print fillTime

sqlcommand = 'SELECT * FROM observation LIMIT 5;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand = "SHOW TABLE STATUS LIKE 'observation';"
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather')
o

o,e = sshCommandWait('df -h')
o

o,e = sshCommandWait('sudo umount -d /dev/xvdb')

givenDataVolume.detach()
waitUntilReady(givenDataVolume,'available')
givenDataVolume.delete()

sqlcommand  = 'ALTER TABLE station '
sqlcommand += 'ADD INDEX (FIPS_ID), '
sqlcommand += 'ADD INDEX (lat), '
sqlcommand += 'ADD INDEX (lon) '
sqlcommand += ';'
cmd  = 'sudo nohup mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather' 
cmd += ' > proc2.out 2> proc2.err < /dev/null &'
sshCommandNoWait(cmd)

sqlcommand = 'DESCRIBE station;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

sqlcommand  = 'ALTER TABLE observation '
sqlcommand += 'ADD INDEX (STN), '
sqlcommand += 'ADD INDEX (year), '
sqlcommand += 'ADD INDEX (month), '
sqlcommand += 'ADD INDEX (day), '
sqlcommand += 'ADD INDEX (temp), '
sqlcommand += 'ADD INDEX (maxtemp), '
sqlcommand += 'ADD INDEX (mintemp) '
sqlcommand += ';'
cmd  = 'sudo time nohup mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather' 
cmd += ' > proc2.out 2> proc2.err < /dev/null &'
sshCommandNoWait(cmd)

time.sleep(60*fillTime*2)

sqlcommand = 'DESCRIBE observation;'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass + ' weather', True)
o

o,e = sshCommandWait('df -h')
o

cmd = 'sudo sed -i "s/mysql\\.sock/mysql\\.sock\\nbind-address\\=0\\.0\\.0\\.0/g" /etc/my.cnf'
o,e = sshCommandWait(cmd)

o,e = sshCommandWait('sudo /etc/init.d/mysqld restart')

sqlcommand = 'GRANT ALL ON weather.* TO weatherdbuser@' + myIP + ' IDENTIFIED BY \'' + mysqlPass + '\';'
o,e = sshCommandWait('mysql -e "' + sqlcommand + '" -u root --password=' + mysqlPass)

import MySQLdb as mdb
print 'opening connection'
con = mdb.connect( host = instance.public_dns_name, 
                   passwd = mysqlPass, 
                   user = 'weatherdbuser', 
                   db='weather' );
cur = con.cursor()
cur.execute("SELECT * FROM observation LIMIT 10;")
for i in range(cur.rowcount):
    row = cur.fetchone()
    print row
print 'closing connection'
con.close()