#!/usr/bin/python
# -*- coding: UTF-8 -*-
# $Id$

import xmpp
import datetime
import MySQLdb
# try to find elementtree
# http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/475126
try:
	import xml.etree.ElementTree as ET # in python >=2.5
except ImportError:
	try:
		import cElementTree as ET # effbot's C module
	except ImportError:
		try:
			import elementtree.ElementTree as ET # effbot's pure Python module
		except ImportError:
			try:
				import lxml.etree as ET # ElementTree API using libxml2
			except ImportError:
				raise ImportError

""" API for talking to Jolem """

__version__ = "$Revision$"

class JolemConfig:
	def Parse(self, config_file):
		""" Parses config xml document and saves in local config list. """
		xml_parser = ET.ElementTree()
		config_doc = xml_parser.parse(config_file)
		config = {}
		
		jid = config_doc.findtext("jid")
		if jid is None:
			raise ConfigurationError
		config["jid"] = jid
#		print "JID %s" % config["jid"]

		password = config_doc.findtext("password")
		if password is None:
			raise ConfigurationError
		config["password"] = password
#		print "pw %s" % config["password"]

		db = config_doc.find("db")
		if db is None:
			raise ConfigurationError
		config["db"] = {}
		for db_param in db:
			if db_param.text is None:
				db_param.text = ''
#			print "DB Setting %s %s" % (db_param.tag, db_param.text)
			config['db'][db_param.tag] = db_param.text
		
		return config

class JolemAPI:
	""" Handles interactions between Jolem """
	def __init__(self, config_file):
		self.config = {}
		
		# parse config
		jc = JolemConfig()
		self.config = jc.Parse(config_file)

		# connect to database
		self.db = None
		self.__connectDB()

	def __connectDB(self):
		""" Connects to the database server. """

		# connect to database
		self.config['db']['use_unicode'] = True
		self.config['db']['charset'] = "utf8"
		self.db = MySQLdb.connect(**self.config['db'])

	def addBlog(self, jid, subject, body, tags):
		""" Adds a blog to the database """
		c = self.db.cursor()

		c.execute("""INSERT INTO `blog` SET 
			`jid` = %s,
			`subject` = %s,
			`body` = %s,
			`posted` = NOW()""",
			(jid.getStripped(), subject, body,))
		c.execute("""SELECT LAST_INSERT_ID()""")
		blog_id = c.fetchone()
		blog_id = blog_id[0]

		# add any tags to the blog
		for tag in tags:
			c.execute("""INSERT INTO `blog_tag` SET
				`blog_id` = %s,
				`tag` = %s""", (blog_id, tag,))

	def addStatus(self, jid, status, status_message):
		""" Adds a status log to the database """

		status_id = None
		status_message_id = None

		# log status message
		if status_message is not None:
			#print "Status: %s" % (status)
			status_message_id = self.__log_status_message(jid, status_message);

		# get status message id for previous status
		if status_message_id is None:
			status_message_id = self.__get_status_status_message_id(jid, status)

		# log status
		status_id = self.__log_status(jid, status);

		# log the mapping of the status message to the status
		if status_id is not None and status_message_id is not None:
			#print "Logging SID %s and SMID %s" % (status_id, status_message_id)
			self.__log_status_map(status_id, status_message_id)

	def getConfig(self):
		""" Gets the config array for jolem """
		return self.config;

	def getRecentStatus(self, jid, entries=30):
		""" Gets sets of the most recent status to status messages sets"""

		c = self.db.cursor()

		q = """SELECT `map`.`added_at`,
			`s`.`resource`, `s`.`status`, `sm`.`message`
			FROM `jid_status` AS `s`
			INNER JOIN `jid_status_map` AS `map`
				ON `s`.`id` = `map`.`status_id`
			INNER JOIN `jid_status_message` AS `sm`
				ON `map`.`status_id` = `sm`.`id`
			WHERE `s`.`jid` = %s """
		if jid.getResource() is not None and jid.getResource() != '':
			q += """
				AND `s`.`resource` = %s"""
			vars = (jid.getStripped(), jid.getResource(), entries, )
		else:
			vars = (jid.getStripped(), entries, )
		q += """
			ORDER BY `map`.`added_at`
			DESC LIMIT %s"""

		c.execute(q, vars);
		id = c.fetchall()

		if id is not None:
			return id

	def __get_status_status_message_id(self, jid, status):
		""" Gets the status message for the most recent status for the jid  """

		c = self.db.cursor()

		c.execute("""SELECT `jid_status_map`.`status_message_id`
			FROM `jid_status`
			INNER JOIN `jid_status_map`
				ON `jid_status`.`id` = `jid_status_map`.`status_id`
			WHERE `jid_status`.`jid` = %s
			AND `jid_status`.`resource` = %s
			AND `jid_status`.`status` = %s
			ORDER BY `jid_status_map`.`added_at`
				DESC LIMIT 1""",
			(jid.getStripped(), jid.getResource(), status,));
		id = c.fetchone()

		if id is not None:
			return id[0]

	def __log_status(self, jid, status):
		""" Logs the status for the jid to the database """

		c = self.db.cursor()

		# find most recent status and reuse it if possible
		c.execute("""SELECT `id`, `status` FROM `jid_status`
			ORDER BY `added_at` DESC LIMIT 1""");
		id = c.fetchone()

		if id is not None and id[1] == status:
			return id[0]

		# log a new status
		c.execute("""INSERT INTO `jid_status` SET 
			`jid` = %s,
			`resource` = %s,
			`status` = %s,
			`added_at` = NOW()""",
			(jid.getStripped(), jid.getResource(), status,));
		c.execute("""SELECT LAST_INSERT_ID()""");
		id = c.fetchone()

		if id is not None:
			return id[0]

	def __log_status_message(self, jid, status_message):
		""" Logs the status message for the jid to the database """

		c = self.db.cursor()
		
		# find most recent status message and reuse it if possible
		c.execute("""SELECT `id`, `message` FROM `jid_status_message`
			ORDER BY `added_at` DESC LIMIT 1""");
		id = c.fetchone()

		if id is not None and id[1] == status_message:
			return id[0]

		# log new status message
		c.execute("""INSERT INTO `jid_status_message` SET
			`jid` = %s,
			`resource` = %s,
			`message` = %s,
			`added_at` = NOW()""",
			(jid.getStripped(), jid.getResource(), status_message,));
		c.execute("""SELECT LAST_INSERT_ID()""");
		id = c.fetchone()

		if id is not None:
			return id[0]

	def __log_status_map(self, status_id, status_message_id):
		""" Logs the mapping of the status to the status message to the database """

		c = self.db.cursor()
		c.execute("""INSERT IGNORE INTO `jid_status_map` SET
			`status_id` = %s,
			`status_message_id` = %s,
			`added_at` = NOW()""",
			(status_id, status_message_id,));