import pgdb
import config
from bkmark import Bookmark
import os

def sql_quote(str):
	return pgdb._quote(str)

class MyDbConnexion:
	def __init__(self, name):
		self.cnx = pgdb.connect(database = config.database,
			host = config.host+':%d'%config.port,
			user = config.user, password = config.passwd)
		self.crs = self.cnx.cursor();
		self.userid = self.get_user_id(name)
		if self.userid == None:
			self.userid = self.create_user(name)
		else:
			self.userid = self.userid[0]
	def get_user_id(self, name):
		self.crs.execute("""
			SELECT id FROM users
			WHERE name = %s;
			""" % (sql_quote(name),))
		return self.crs.fetchone()
	def create_user(self, name):
		id = self.get_next_id('users')
		self.crs.execute("""
			INSERT INTO users(id, name, full_name)
			VALUES (%d, %s, %s);
			""" % (id, sql_quote(name), sql_quote(name)))
		self.crs.execute("""
			INSERT INTO keywords(id, userid, name)
			VALUES (0, %d, '--');
			""" %(id,))
		self.crs.execute("""
			INSERT INTO db_sequence(seq_name, userid, nextid)
			VALUES ('keywords', %d, 1);
			""" %(id,))
		self.crs.execute("""
			INSERT INTO db_sequence(seq_name, userid, nextid)
			VALUES ('bookmarks', %d, 0);
			""" %(id,))
		self.crs.execute("""
			INSERT INTO folders(id, keyword, parent, userid)
			VALUES (0, 0, -1, %d);
			""" %(id,))
		self.crs.execute("""
			INSERT INTO db_sequence(seq_name, userid, nextid)
			VALUES ('folders', %d, 1);
			""" %(id,))
		self.crs.execute("""
			INSERT INTO preferences(userid)
			VALUES (%d);
			""" %(id,))
		self.cnx.commit()
		return id
	def delete_user(self, userid):
		self.crs.execute("""
			DELETE FROM users WHERE id = %d;
			DELETE FROM bookmarks_keywords WHERE userid = %d;
			DELETE FROM bookmarks WHERE userid = %d;
			DELETE FROM preferences WHERE userid = %d;
			DELETE FROM keywords WHERE userid = %d;
			DELETE FROM db_sequence WHERE userid = %d;
			""" % (userid,userid,userid,userid,userid))
		self.cnx.commit()
	def get_preferences(self):
		self.crs.execute("""
			SELECT p.keywords_box,
			p.keywords_sort, p.keywords_reverse,
			u.full_name, p.default_view
			FROM preferences p, users u
			WHERE u.id = %d AND p.userid = u.id;
			""" % (self.userid,))
		res = self.crs.fetchone()
		return {
		   'keywords_box' : res[0],
		   'keywords_sort' : res[1],
		   'keywords_reverse': res[2],
		   'fullname': res[3],
		   'default_view': res[4]
		   }
	def set_preferences(self, prefs):
	        if prefs['keywords_reverse']:
			rev = 'TRUE'
		else:
			rev = 'FALSE'
		self.crs.execute("""
			UPDATE preferences
			SET keywords_box = %d,
			keywords_sort = %s,
			keywords_reverse = %s,
			default_view = %d
			WHERE userid = %d;
			""" % (prefs['keywords_box'],
			sql_quote(prefs['keywords_sort']),
			rev, prefs['default_view'], self.userid))
		self.crs.execute("""
			UPDATE users
			SET full_name = %s
			WHERE id = %d;
			""" % (sql_quote(prefs["fullname"]),
				self.userid))
		self.cnx.commit()
	def add_bookmark(self, bk):
		id = self.get_next_id('bookmarks')
		self.crs.execute("""
			INSERT INTO bookmarks(id, userid, url, name, ldesc, added)
			VALUES (%d, %d, %s, %s, %s, %s);
			INSERT INTO bookmarks_keywords(bookmark, keyword, userid)
			VALUES (%d, 0, %d);
			""" % (id, self.userid, sql_quote(bk.url),
			sql_quote(bk.name), sql_quote(bk.desc),
			sql_quote(bk.added),
			id, self.userid))
		self.cnx.commit()
		return id
	def get_bookmarks(self, ids):
		qry = """
			SELECT id, url, name, ldesc, added
			FROM bookmarks
			WHERE userid = %d AND (id = %d
			""" % (self.userid, ids.pop(),)
		for id in ids:
			qry = qry + " OR id = %d" % (id,)
		qry = qry + ");"
		self.crs.execute(qry)
		bks = []
		list = self.crs.fetchall()
		for res in list:
			bk = Bookmark()
			bk.id = res[0]
			bk.url = res[1]
			bk.name = res[2]
			bk.desc = res[3]
			bk.added = res[4]
			bks.append(bk)
		return bks
	def update_bookmark(self, bk):
		self.crs.execute("""
			UPDATE bookmarks
			SET url = %s, name = %s,
			ldesc = %s, added = %s
			WHERE userid = %d AND id = %d;
			""" % (sql_quote(bk.url), sql_quote(bk.name),
			sql_quote(bk.desc),
			sql_quote(bk.added), self.userid, bk.id))
		self.cnx.commit()
	def update_keywords(self, id, keywords):
		self.crs.execute("""
			DELETE FROM bookmarks_keywords
			WHERE userid = %d AND bookmark = %d AND
			keyword <> 0;""" % (self.userid, id))
		for kw in keywords:
			if kw == 0: continue
			self.crs.execute("""
				INSERT INTO
				bookmarks_keywords(bookmark, keyword, userid)
				VALUES (%d, %d, %d);""" % (id, kw, self.userid))
		self.cnx.commit()	
	def remove_bookmark(self, id):
		self.crs.execute("""
			DELETE FROM bookmarks_keywords
			WHERE userid = %d AND bookmark = %d;
			DELETE FROM bookmarks
			WHERE userid = %d AND id = %d;
			""" % (self.userid, id, self.userid, id))
		self.cnx.commit()
	def add_keyword(self, name):
		id = self.get_next_id('keywords')
		self.crs.execute("""
			INSERT INTO keywords(id, userid, name)
			VALUES (%d, %d, %s);
			""" % (id, self.userid, sql_quote(name)))
		self.cnx.commit()
		return id
	def get_keyword(self, id):
		self.crs.execute("""
			SELECT name FROM keywords
			WHERE userid = %d AND id = %d;
			""" % (self.userid, id))
		return self.crs.fetchone()[0]
	def get_keywords(self, bk_ids):
		if len(bk_ids) == 0:
			return []
		qry = """
			SELECT DISTINCT k.id, k.name
			FROM keywords k, bookmarks_keywords bk
			WHERE bk.userid = %d
			AND k.userid = %d
			AND k.id = bk.keyword
			AND (
			""" % (self.userid, self.userid)
		for id in bk_ids:
			qry += "bk.bookmark = %d OR " % (id,)
		qry = qry[:-3] + ") ORDER BY k.id;"
		self.crs.execute(qry)
		return self.crs.fetchall()
	def update_keyword(self, id, name):
		self.crs.execute("""
			UPDATE keywords
			SET name = %s
			WHERE userid = %d AND id = %d;
			""" % (sql_quote(name), self.userid, id))
		self.cnx.commit()
	def remove_keyword(self, id):
		if id == 0:
			raise "Can't remove default keyword!"
		self.crs.execute("""
			DELETE FROM bookmarks_keywords
			WHERE keyword = %d AND userid = %d;
			DELETE FROM keywords
			WHERE id = %d AND userid = %d;
			""" % (id, self.userid, id, self.userid))
		self.cnx.commit()
	def add_keywords(self, add_id, into_id):
		self.crs.execute("""
			INSERT INTO bookmarks_keywords (userid,
			bookmark, keyword)
			SELECT %d, bookmark, %d
			FROM bookmarks_keywords
			WHERE keyword = %d
			AND userid = %d
			AND bookmark NOT IN 
			  (SELECT bookmark FROM bookmarks_keywords
			  WHERE userid = %d AND keyword = %d )
			;
			""" % (self.userid, into_id, add_id, self.userid,
			self.userid, into_id))
		self.cnx.commit()
	def merge_keywords(self, merge_id, into_id):
		self.crs.execute("""
			UPDATE bookmarks_keywords
			SET keyword = %d
			WHERE keyword = %d
			AND userid = %d
			AND bookmark NOT IN 
			  (SELECT bookmark FROM bookmarks_keywords
			  WHERE userid = %d AND keyword = %d )
			;
			""" % (into_id, merge_id, self.userid,
			self.userid, into_id))
		self.remove_keyword(merge_id)
	def get_all_keywords(self):
		"""Return a list of doublets [id, keyword, count]."""
		self.crs.execute("""SELECT k.id, k.name
			FROM keywords k
			WHERE k.userid = %d
			ORDER BY k.id;""" % (self.userid,))
		res = self.crs.fetchall()
		return res
	def get_keywords_count(self, bks = []):
		"""Return a list of doublets [id, count].
Does with a count of zero are ignored."""
		qry = """SELECT bk.keyword, COUNT(bk.keyword)
			FROM bookmarks_keywords bk
			WHERE bk.userid = %d""" % (self.userid,)
		if len(bks) > 0:
			qry += " AND ( bk.bookmark = %d" % bks[0]
			for bk in bks[1:]:
				qry += " OR bk.bookmark = %d" % bk
			qry += " )"
		qry += " GROUP BY bk.keyword ORDER BY bk.keyword;"
		self.crs.execute(qry)
		res = self.crs.fetchall()
		if not res:
			res = [[0,0]]
		return res
	def _bookmarks_selection(self, has_kw = [0], except_kw = []):
	        """
Return a bookmarks selection query used in at least two function.
"""
		basic_qry = """
		  SELECT bk.bookmark FROM bookmarks_keywords bk
		  WHERE bk.keyword = %d
		  AND bk.userid = %d
		  """
		
		qry = basic_qry % (has_kw[0], self.userid)
		for kw in has_kw[1:]:
			qry += ("INTERSECT " + basic_qry % (kw, self.userid))
		for kw in except_kw:
			qry += ("EXCEPT " + basic_qry % (kw, self.userid))
		return qry
	def select_bookmarks(self, has_kw = [0], except_kw = []):
	        """
Return a list of id bookmarks who has /has_kw/ set but not /except_kw/.
"""
		qry = self._bookmarks_selection(has_kw, except_kw) + ';'
		self.crs.execute(qry)
		return self.crs.fetchall()
	def get_bookmarks_count(self, has_kw = [0], except_kw = [], opt_kw = []):
	        """
Return a count of bookmarks which would have been return on
select_bookmarks qry.
"""
		qry = """
			SELECT keyword, COUNT(keyword)
			FROM bookmarks_keywords
			WHERE userid = %d
			""" % self.userid
		if len(opt_kw) > 0:
			qry += "AND ( "
			qry += " OR ".join(map(lambda e: "keyword = %d" % e, opt_kw))
			qry += ") "
		qry += "AND bookmark IN (" + self._bookmarks_selection(has_kw, except_kw) + ") "
		qry += "GROUP BY keyword ORDER BY keyword;"
		self.crs.execute(qry)
		return self.crs.fetchall()
	def get_folder_keyword(self, id):
		self.crs.execute("""
			SELECT k.id, k.name
			FROM keywords k, folders f
			WHERE
			f.userid = %d AND f.id = %d
			AND k.userid = f.userid
			AND k.id = f.keyword;
			""" %(self.userid, id))
		return self.crs.fetchone()
	def get_subfolders(self, id):
		self.crs.execute("""
			SELECT f.id, k.id, k.name
			FROM folders f, keywords k
			WHERE f.userid = %d
			AND f.parent = %d
			AND k.userid = f.userid
			AND k.id = f.keyword
			ORDER BY k.name;
			""" %(self.userid, id))
		res = self.crs.fetchall()
		return map(lambda e: {
			'id' : e[0],
			'name' : e[2],
			'keyword' : e[1] },
			res)
	def get_folder_parents_and_self(self, id):
		"""Return self and parents in descending order."""
		if id < 0:
			return []
		self.crs.execute("""
			SELECT f.id, k.id, k.name, f.parent
			FROM folders f, keywords k
			WHERE
			f.id = %d AND f.userid = %d
			AND k.userid = f.userid
			AND k.id = f.keyword;
			""" %(id, self.userid))
		cur = self.crs.fetchone()
		res = [{ 'id': cur[0],
		        'name' : cur[2],
			'keyword': cur[1] }]
		return res + self.get_folder_parents_and_self(cur[3])
	def add_folder(self, keyword, parent):
		id = self.get_next_id('folders')
		self.crs.execute("""
			INSERT INTO folders(userid, id, keyword, parent)
			VALUES (%d, %d, %d, %d);
			""" % (self.userid, id, keyword, parent))
		self.cnx.commit()
	def remove_folders(self, id, commit = 1):
		if id == 0:
			raise "Can't erase base folder!"
		subs = self.get_subfolders(id)
		for sub in subs:
			self.remove_folders(sub['id'], 0)
		self.crs.execute("""
			DELETE FROM folders
			WHERE id = %d AND userid = %d;
			""" % (id, self.userid))	
		if commit:
			self.cnx.commit()
	def get_next_id(self, seq_name):
		if seq_name == 'users':
			userid = -1
		else:
			userid = self.userid
		self.crs.execute("""
			SELECT nextid FROM db_sequence
			WHERE seq_name = '%s'
			AND userid = %d
			FOR UPDATE;
			""" % (seq_name,userid))
		id = self.crs.fetchone()[0]
		self.crs.execute("""
			UPDATE db_sequence SET nextid = %d
			WHERE seq_name = '%s'
			AND userid = %d;
			""" % (id+1, seq_name, userid))
		return id

def connect(username):
	return MyDbConnexion(username)