import MySQLdb
import json
import base64


# Test insertion and retreive of json formatted data in mysql

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()

    sql = """SELECT * FROM servers where name='Portsmouth';"""
    cr.execute(sql)
    result = cr.fetchall()

    sp = result[0][8]
    switchports = json.loads(base64.b64decode(sp))
    print switchports


producerData ={   
    "name"        : "Portsmouth",
    "type"        : "producer",
    "disabled"    : False,
    "active"      : True,
    "reserved"    : "Devops",
    "flicid"      : "1234",
    "location"    : "packet",
    "ports"       : ["00", "01", "02", "03", "10", "11", "12", "13"],
    "portconfig"  : ["nap", "nap", "nap", "nap", "nap", "nap", "nap", "nap"],
    "switchports" : ["01", "02", "03", "04", "17", "18", "19", "20"],
    "pathstart"   : [[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0]],
    "pathend"     : [[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0]],
    "rate"        : [0, 0, 0, 0, 0, 0, 0, 0],
    "pcaploop"    : [0, 0, 0, 0, 0, 0, 0, 0],
    "linkstatus"  : [True, True, True, True, True, True, True, True],
    "playstatus"  : ["stop","stop","stop","stop","stop","stop","stop","stop" ],
    "pcaps"       : ["1.pcap", "2.pcap", "3.pcap", "4.pcap", "11.pcap", "12.pcap", "13.pcap", "14.pcap"],
    "stroke"      : "black",
    "fill"        : "#ffdddd"
}

datablock = json.dumps(producerData)

insertData(datablock)