import config
import MySQLdb


db = MySQLdb.connect(user=config.MYSQL_USER, passwd=config.MYSQL_PASSWD, db=config.MYSQL_DB)
def logmsg(msg, to, sentby):
	c = db.cursor()
	try:
		c.execute("""INSERT INTO sendlog (msg, sent_by) VALUES (%s, %s)""", (msg, sentby))
		sendlogid = c.lastrowid

		for recipient in to:
			c.execute("""INSERT INTO sendlog_to (id, recipient) VALUES (%s, %s)""", (sendlogid, recipient))
		db.commit()
	except:
		print "um error"
		db.rollback()

def getloggedmsgs(lastOut):
	c = db.cursor(MySQLdb.cursors.DictCursor)
	c.execute("""SELECT sendlog.*, users.name AS sent_by_name FROM sendlog INNER JOIN users ON sendlog.sent_by = users.uid WHERE sendlog.id > %s ORDER BY id DESC""", (lastOut,))
	rows = c.fetchall()
	for r in rows:
		c.execute("""SELECT people.id, people.name FROM sendlog_to INNER JOIN people ON sendlog_to.recipient = people.id WHERE sendlog_to.id = %s """, (r['id'],))
		r['to'] = c.fetchall()
	db.commit()
	return rows

def loginboundmsg(inmsg):
	c = db.cursor()
	try:
		# check if this sender is known to us.
		shortfrom = inmsg['From'][2:] # also check for another form of number where +1 is omitted
		c.execute("""SELECT id FROM people WHERE people.phonenum = %s or people.phonenum = %s LIMIT 1""", (inmsg['From'], shortfrom))
		row = c.fetchone()
		sender_id = -1 # default to this if unknown...
		if row: # found? great, update sender_id.
			sender_id = row[0]

		c.execute("""INSERT INTO inbound_msgs (`From`, SmsMessageSid, AccountSid, Body, SmsStatus, from_known_person) VALUES (%s, %s, %s, %s, %s, %s)""", (inmsg['From'], inmsg['SmsMessageSid'], inmsg['AccountSid'], inmsg['Body'], inmsg['SmsStatus'], sender_id))
		db.commit()
	except:
		print "um error"
		print c._last_executed
		db.rollback()

def getinboundmsgs(lastIn):
	c = db.cursor(MySQLdb.cursors.DictCursor)
	c.execute("""SELECT inbound_msgs.id, inbound_msgs.from, inbound_msgs.body, people.id as pid, people.name FROM `inbound_msgs` LEFT JOIN people ON inbound_msgs.from_known_person = people.id WHERE inbound_msgs.id > %s ORDER BY inbound_msgs.id DESC""", (lastIn,))
	print c._last_executed
	rows = c.fetchall()
	db.commit()
	return rows