#!/usr/bin/python

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)
			VALUES (%d, %s);
			""" % (id, 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 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 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 keywords_box,
			keywords_sort, keywords_reverse
			FROM preferences WHERE userid = %d;
			""" % (self.userid,))
		res = self.crs.fetchone()
		return {
		   'keywords_box' : res[0],
		   'keywords_sort' : res[1],
		   'keywords_reverse': res[2]
		   }
	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
			WHERE userid = %d;
			""" % (prefs['keywords_box'],
			sql_quote(prefs['keywords_sort']),
			rev, 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 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 select_bookmarks(self, has_kw = [0], except_kw = []):
	        """
Return a list of id bookmarks who has /has_kw/ set but not /except_kw/.
"""
		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 = qry + ("INTERSECT " + basic_qry % (kw, self.userid))
		for kw in except_kw:
			qry = qry + ("EXCEPT " + basic_qry % (kw, self.userid))
		qry = qry + ';'
		self.crs.execute(qry)
		return self.crs.fetchall()
	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)