import MySQLdb
from packetbroker import *
import redis
import json
import base64
import requests

# get all data from mysql, store in redis for ease of access and speed between python modules
def initializeDatabase():
    r = redis.Redis(host='127.0.0.1', port='6379')

    # clear the database to nothing, rebuild it
    r.flushdb()

    sql = "SELECT * FROM servers;"
    db = MySQLdb.connect(host="localhost", passwd="veronicaHammerhead27!", db="veronica")
    cr = db.cursor()

    cr.execute(sql)
    result = cr.fetchall()
    index = 1

    ashburnproducers  = 0
    ashburnprodports  = 0
    ashburnconsumers  = 0
    ashburnconports   = 0
    crozetproducers   = 0
    crozetprodports   = 0
    crozetconsumers   = 0
    crozetconports    = 0
    packetproducers   = 0
    packetprodports   = 0
    packetconsumers   = 0
    packetconports    = 0

    for data in result:
        # use flic id to query for reservation
        url = "http://artie.eng.fireeye.com/api/v1/machines?asset_id=%s" % data[6]
        response = requests.get(url, verify=False)
        try:
           payload = json.loads(response.text)
           m = payload['machines']
           machines = m[0]
           reserved = machines['reserved_by']
        except:
           reserved = ""
           pass

        serverData = {
        'index'       : data[0],
        'name'        : data[1],
        'type'        : data[2],
        'reserved'    : reserved,
        'disabled'    : data[4],
        'active'      : data[5],
        'flicid'      : data[6],
        'location'    : data[7],
        'ports'       : data[8],
        'portconfig'  : data[9],
        'switchports' : data[10],
        'pathstart'   : data[11],
        'pathend'     : data[12],
        'rate'        : data[13],
        'pcaploop'    : data[14],
        'linkstatus'  : data[15],
        'playstatus'  : data[16],
        'pcaps'       : data[17],
        'stroke'      : data[18],
        'fill'        : data[19],
        }

        # accumulate some totals to assist in drawing the images
        if data[2] == "producer":
           if data[7].lower() == "ashburn":
              ashburnproducers = ashburnproducers + 1
              prodports = json.loads(base64.b64decode(data[8]))
              pl = len(prodports)
              ashburnprodports = ashburnprodports + pl
           elif data[7].lower() == "crozet":
              crozetproducers = crozetproducers + 1
              prodports = json.loads(base64.b64decode(data[8]))
              pl = len(prodports)
              crozetprodports = crozetprodports + pl
           elif data[7].lower() == "packet":
              packetproducers = packetproducers + 1
              prodports = json.loads(base64.b64decode(data[8]))
              pl = len(prodports)
              packetprodports = packetprodports + pl
           else:
              pass

        elif data[2] == "consumer":
           if data[7].lower() == "ashburn":
              ashburnconsumers = ashburnconsumers + 1
              conports = json.loads(base64.b64decode(data[8]))
              cl = len(conports)
              ashburnconports = ashburnconports + cl
           elif data[7].lower() == "packet":
              packetconsumers = packetconsumers + 1
              conports = json.loads(base64.b64decode(data[8]))
              cl = len(conports)
       	      packetconports = packetconports + cl
           else:
              crozetconsumers = crozetconsumers + 1
              conports = json.loads(base64.b64decode(data[8]))
              cl = len(conports)
       	      crozetconports = crozetconports + cl

        rediskey = "server-%d" % index
        index = index + 1
        if(r.exists(rediskey) == 0):
           r.hmset(rediskey, serverData)

    sql = "SELECT COUNT(*) FROM servers;"
    db = MySQLdb.connect(host="localhost", passwd="veronicaHammerhead27!", db="veronica")
    cr = db.cursor()

    cr.execute(sql)
    result = cr.fetchall()

    r.set("totalBoxCount", int(str(result[0][0])))
    r.set("ashburnProducers", ashburnproducers)
    r.set("ashburnConsumers", ashburnconsumers)
    r.set("crozetProducers", crozetproducers)
    r.set("crozetConsumers", crozetconsumers)
    r.set("ashburnProdPorts", ashburnprodports)
    r.set("crozetProdPorts", crozetprodports)
    r.set("packetProducers", packetproducers)
    r.set("packetConsumers", packetconsumers)
    r.set("packetProdPorts", packetprodports)
    r.set("ashburnConPorts", ashburnconports)
    r.set("crozetConPorts", crozetconports)
    r.set("packetConPorts", packetconports)

    # query packet broker for switch IDs- saves the result in redis

    queryports()


# Insert DB record
def insertData(inputJson):
    indata	= json.loads(inputJson)
    servername  = indata['name']
    type        = indata['type']
    disabled    = str(indata['disabled'])
    active	= str(indata['active'])
    reserved    = str(indata['reserved'])
    flicid	= indata['flicid']
    location    = indata['location']
    ports	= base64.b64encode(json.dumps(indata['ports']))
    portconfig  = base64.b64encode(json.dumps(indata['portconfig']))
    switchports = base64.b64encode(json.dumps(indata['switchports']))
    pathstart   = base64.b64encode(json.dumps(indata['pathstart']))
    pathend     = base64.b64encode(json.dumps(indata['pathend']))
    rate        = base64.b64encode(json.dumps(indata['rate']))
    pcaploop    = base64.b64encode(json.dumps(indata['pcaploop']))
    linkstatus  = base64.b64encode(json.dumps(indata['linkstatus']))
    playstatus  = base64.b64encode(json.dumps(indata['playstatus']))
    pcaps	= base64.b64encode(json.dumps(indata['pcaps']))
    stroke	= indata['stroke']
    fill        = indata['fill']

    db = MySQLdb.connect(host="localhost", passwd="veronicaHammerhead27!", db="veronica")
    cr = db.cursor()

    sql = """INSERT INTO servers (name, type, reserved, disabled, active, flicid, location, ports, portconfig, switchports, pathstart, pathend, rate, pcaploop, linkstat, playstat, pcaps, stroke, fill) 
             VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');
          """ % (servername, type, reserved, disabled, active, flicid, location, ports, portconfig, switchports, pathstart, pathend, rate, pcaploop, linkstatus, playstatus, pcaps, stroke, fill)   #"""

    cr.execute(sql)
    db.commit()

    initializeDatabase()


# Update DB record
def updateData(inputJson):
    indata	= json.loads(inputJson)
    servername  = indata['name']
    type        = indata['type']
    disabled    = str(indata['disabled'])
    active	= str(indata['active'])
    reserved    = str(indata['reserved'])
    flicid	= indata['flicid']
    location    = indata['location']
    ports	= base64.b64encode(json.dumps(indata['ports']))
    portconfig  = base64.b64encode(json.dumps(indata['portconfig']))
    switchports = base64.b64encode(json.dumps(indata['switchports']))
    pathstart   = base64.b64encode(json.dumps(indata['pathstart']))
    pathend     = base64.b64encode(json.dumps(indata['pathend']))
    rate        = base64.b64encode(json.dumps(indata['rate']))
    pcaploop    = base64.b64encode(json.dumps(indata['pcaploop']))
    linkstatus  = base64.b64encode(json.dumps(indata['linkstatus']))
    playstatus  = base64.b64encode(json.dumps(indata['playstatus']))
    pcaps	= base64.b64encode(json.dumps(indata['pcaps']))
    stroke	= indata['stroke']
    fill        = indata['fill']

    db = MySQLdb.connect(host="localhost", passwd="veronicaHammerhead27!", db="veronica")
    cr = db.cursor()

    sql = """UPDATE servers SET name='%s', type='%s', reserved='%s', disabled='%s', active='%s', flicid='%s', location='%s', ports='%s', portconfig='%s', switchports='%s', pathstart='%s', pathend='%s', rate='%s', pcaploop='%s', linkstat='%s', playstat='%s', pcaps='%s', stroke='%s', fill='%s' WHERE name='%s'; 
          """ % (servername, type, reserved, disabled, active, flicid, location, ports, portconfig, switchports, pathstart, pathend, rate, pcaploop, linkstatus, playstatus,  pcaps, stroke, fill, servername)  #"""

    cr.execute(sql)
    db.commit()

    initializeDatabase()


# utility to search based on name or flic id
def fetchData(name=None, flicid=None):
    if name == None and flicid == None:
       return

    if name:
       sql = "SELECT * FROM servers where name='%s';" % name
    if flicid:
       sql = "SELECT * FROM servers where flicid='%s';" % flicid

    db = MySQLdb.connect(host="localhost", passwd="veronicaHammerhead27!", db="veronica")
    cr = db.cursor()

    cr.execute(sql)
    result = cr.fetchall()
    data = result[0]

    return(translateData(data))


# translate the data from the encoded record into a json object
def translateData(data):
    serverData = {
        'name'        : data[1],
        'type'        : data[2],
        'reserved'    : data[3],
        'disabled'    : data[4],
        'active'      : data[5],
        'flicid'      : data[6],
        'location'    : data[7],
        'ports'       : json.loads(base64.b64decode(data[8])),
        'portconfig'  : json.loads(base64.b64decode(data[9])),
        'switchports' : json.loads(base64.b64decode(data[10])),
        'pathstart'   : json.loads(base64.b64decode(data[11])),
        'pathend'     : json.loads(base64.b64decode(data[12])),
        'rate'        : json.loads(base64.b64decode(data[13])),
        'loop'        : json.loads(base64.b64decode(data[14])),
        'linkstatus'  : json.loads(base64.b64decode(data[15])),
        'playstatus'  : json.loads(base64.b64decode(data[16])),
        'pcaps'       : json.loads(base64.b64decode(data[17])),
        'stroke'      : data[18],
        'fill'        : data[19]
    }
    datablock = json.dumps(serverData)
    return datablock