import MySQLdb
import simplejson
import pyowm
import urllib
import json
import smtplib
import math

ID_COL = 0
VAR_COL = 1
LOG_COL = 2
VAL_COL = 3
RAN_COL = 4
MAT_COL = 6
ALT_COL = 7

#Create a list of all recipes, multiple recipes will be put into a list and that list will subsequently be put into the final recipe list
#example final recipe list = [recipe1, recipe2, [recipe3a, recipe3b, recipe3c], recipe4, [recipe5a, recipe5b]]
def getRecipeList(recipes): 
    oldmatch = -1
    list = []
    matchingList = []
	
    for recipe in recipes:
        match = recipe[MAT_COL]
        if match == "null":
            if matchingList:
                list.append(matchingList)
                matchingList = []
            list.append(recipe)
        else:
            if match == oldmatch:
                matchingList.append(recipe)
            else:
                if matchingList:
                    list.append(matchingList)
                    matchingList = []
                matchingList.append(recipe)
		
        oldmatch = match
    if matchingList:
        list.append(matchingList)
        matchingList = []
    return list

#Creates a list of all fia values that match the associated recipe list
def getFIAList(recipelist,x):
    fiaList = []
	
    for recipes in recipelist:
            fiaValues = getFIAValues(recipes,x)
            for values in fiaValues:
                fiaList.append(values)
				
    return fiaList

#Converts variable names to database variable names
def convertName(name):
    if name == 'Elevation':
	    return 'elevation'
    elif name == 'Wind Speed':
        return 'windspeed'
    elif name == 'Wind Direction':
        return 'winddir'
    elif name == 'Humidity':
        return 'humidity'
    elif name == 'Temperature':
        return 'temp'
    elif name == 'Rain':
        return 'rain'
    elif name == 'Cloud Coverage':
        return 'clouds'

#gets fia array index of certain values
def getArrayIndex(name):
    if name == 'Elevation':
	    return 8
    elif name == 'Wind Speed':
        return 9
    elif name == 'Wind Direction':
        return 10
    elif name == 'Humidity':
        return 11
    elif name == 'Temperature':
        return 12
    elif name == 'Rain':
        return 13
    elif name == 'Cloud Coverage':
        return 14
    elif name == 'Alert':
        return 15
    elif name == 'Phone':
        return 1	
    elif name == 'GPS':
        return 2
    elif name == 'Range':
        return 16

#queries the mysql db for the fia entries that match the associated recipes
def getFIAValues(recipe, x):
    count = len(recipe)
    sqlStatement = "SELECT * FROM api_fia WHERE"
    firstIter = True
    variable = None
    logic = None
    value = None
    alert = None
    range = None
    #build the sqlstatement for the recipe
    for item in recipe:
        variable = item[VAR_COL]
        logic = item[LOG_COL]
        value = item[VAL_COL]
        alert = item[ALT_COL]
        range = item[RAN_COL]
        if firstIter:
            sqlStatement += " "+ str(str(convertname(variable))+ " " + str(logic) + " " + str(value)) 
            firstIter = False
        else:
            sqlStatement += " and " + str(str(convertName(variable))+ " " + str(logic) + " " + str(value)) 
    
    x.execute(sqlStatement)
    
    #append the range and the alert type to the fia value
    array = []
    count = 0
    y=x.fetchall()
    while count < len(y):
        count2 = 0
        array2 = []
        while count2 < len(y[count]):
            array2.append(y[count][count2])
            count2+=1
        array2.append(alert)
        array2.append(range)		
        array.append(array2)
        count+=1
		
    return array

#creates a new array with only the gps, alert, and range of the fia entry
def getGPSAndAlert(list):
    array = []
    count = 0
    for item in list:
        array2 = []
        array2.append(item[getArrayIndex('GPS')])
        array2.append(item[getArrayIndex('Alert')])
        array2.append(item[getArrayIndex('Range')])
        array.append(array2)
    return array

def removeDuplicates(x):
    a = []
    for i in x:
        if i not in a:
            a.append(i)
    return a

#takes the list of farms that match the recipe and finds all farms within the specified range of the initial farms. Fills array with the farm phone and alert type
def findFarms(list, x):
    farmList = []
    farmFeatures = []
    x.execute("""SELECT * FROM api_fia""")
    farms = x.fetchall()
    for item in list:
        range=item[2]
        gps1 = json.loads(item[0])
        lat1 = float(gps1['latitude'])
        lon1 = float(gps1['longitude'])
        for farm in farms:
            gps2 = json.loads(farm[2])
            lat2 = float(gps2['latitude'])
            lon2 = float(gps2['longitude'])
            R = 6371
            dLat = math.radians(lat2-lat1)
            dLon = math.radians(lon2-lon1)
            lat1 = math.radians(lat1)
            lat2 = math.radians(lat2)

            a = math.sin(dLat/2) * math.sin(dLat/2) + math.sin(dLon/2) * math.sin(dLon/2) * math.cos(lat1) * math.cos(lat2)
            c = 2 * math.atan2(math.sqrt(a),math.sqrt(1-a))
            d = R * c
            if d <= range:
                farmFeatures.append(farm[1])
                farmFeatures.append(item[1])
                farmList.append(farmFeatures)
                farmFeatures = []
                print farmList

    return removeDuplicates(farmList)

#Sends a text to the farms using google smtp server. Only works with fido carrier for now (Should add different sms gateways)
def sendSMS(list):
    #using google smtp
    server = smtplib.SMTP( "smtp.gmail.com", 587 )
    server.ehlo()
    server.starttls()
    server.ehlo()
    server.login( 'fianotificationgenerator@gmail.com', 'expertsystem' )
    for item in list:
        phone = item[0]
        alert = item[1]
        if alert == "Spray":
            server.sendmail( 'eis', phone+'@sms.fido.ca', 'You need to spray' )
        else: 
            server.sendmail( 'eis', phone+'@sms.fido.ca', 'You need to scout' )
   
if __name__ == '__main__':
    try:
        #connect to mysql db
        conn = MySQLdb.connect(host = "localhost", user = "eis", passwd = "188esplanade", db="expertsystem")
        x=conn.cursor()
        x.execute("""SELECT * FROM api_recipe""")
        recipevals = x.fetchall()
	#get list of recipes	
        recipeList = getRecipeList(recipevals)
        #get list of fia entries that match recipes
        finalList = getFIAList(recipeList,x)
        finalList = removeDuplicates(finalList)
        #get list of gps, alert, range of matching fia entries
        finalList = getGPSAndAlert(finalList)
        #get list of farms within range of matching fia entries
        print findFarms(finalList, x)
    except Exception, Argument:
        print Argument
    finally:
        conn.close()
