import socket
import time
import xml.etree.ElementTree as ET
import MySQLdb as mdb
import sys

TCP_IP = ''
TCP_PORT = 51717
BUFFER_SIZE = 1024
MESSAGE = "sprava"

s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind((TCP_IP, TCP_PORT))
s.listen(1)

def insert_node (add):
	line = "INSERT INTO nodes(address) VALUES('%s')" % (add)
	cur.execute(line)    
	dbcon.commit()
	print "Node %s added!" % (add) 
	return
	
def insert_packet (src,dst,rssi,lqi,tx_power ):
	line = "INSERT INTO packets(source_address,destination_address,rssi,lqi,tx_power) VALUES('%s','%s','%d','%d','%d')" % (src,dst,rssi,lqi,tx_power)
	cur.execute(line)    
	dbcon.commit()
	print "Packet added!" 
	return
	
def search_node (add):
	line = "SELECT 1 FROM nodes WHERE address='%s'" % (add)
	query = cur.execute(line)
	if query == 1:    
		print "Node %s exists" % (add)
	else:
		insert_node (add)
	return
	
def check_table(name):
	line = "SHOW TABLES LIKE '%s'" % (name)
	query = cur.execute(line)
	if query == 1:    
		print "Table %s exists" % (name)
	elif name == "nodes":
		line = "CREATE TABLE nodes (address CHAR(20) PRIMARY KEY,fw_version  FLOAT,node_type VARCHAR(60))"
		cur.execute(line)    
		con.commit()
		print "Table %s added!" % (name)
	elif name == "packets":
		line = "CREATE TABLE packets (packet_ID BIGINT PRIMARY KEY AUTO_INCREMENT,source_address CHAR(20),destination_address CHAR(20),rssi INT, lqi INT, tx_power INT, timestamp TIMESTAMP)"
		cur.execute(line)    
		con.commit()
		print "Table %s added!" % (name)
	else:
		print "Table %s does not exist" % (name)
	return

conn, addr = s.accept()
print 'Connection address:', addr

while True:
	data = ""
	data = conn.recv(BUFFER_SIZE)
	if not "<" in data: continue
	try:
		tree = ET.fromstring(data)
	except Exception, e:
		print "Not well formated XML"
		continue
	for child in tree.iter('txp'):
		txp = child.text
	for child in tree.iter('rssi'):
		RSSI = child.text
	for child in tree.iter('lqi'):
		lqi = child.text
	for child in tree.iter('src'):
		src = child.text
	for child in tree.iter('dst'):
		dst = child.text
	
	try:
		dbcon = mdb.connect('localhost', 'server', 'server123', 'WSN')
		cur = dbcon.cursor()
		check_table("nodes")
		check_table("packets") 
		search_node(src)
		search_node(dst)
		insert_packet (src,dst,int(RSSI),int(lqi),int(txp))
    
	except mdb.Error, e:
  
		print "Error %d: %s" % (e.args[0], e.args[1])
		sys.exit(1)

	finally:
    
		if dbcon:
			dbcon.close()
conn.close()

