#!/usr/bin/python
# -*- coding: utf-8 -*-

import gtk, sqlite3, xml.etree.ElementTree, os, hashlib, logging
from distutils import version
from lib import appinfo_reader, urlstuff


def dl_icon(url):
	if url == None:
		return "icons/default-app.png"
	
	file = urlstuff.url2file(url)
	
	if not os.path.isfile('cache/icons/' + file):
		try:
			urlstuff.httpDownload(url, 'cache/icons/' + file)
		except:
			return "icons/default-app.png"
	return 'cache/icons/' + file

def get_element(elementtree, item, default=""):
	try:	value = elementtree.get(item)
	except:	return default
	
	if value == "" or value == None:
		return default
	
	return value

def get_element_text(elementtree, item, default=""):
	try:	value = elementtree.find(item).text
	except:	return default
	
	if value == "" or value == None:
		return default
	
	return value

class database():
	"""Classe permettant de gérer la base de donnée"""
	def __init__(self):
		"""Connection à la base de donnée"""
		if not os.path.isfile("cache/SynApps.sqlite"):
			logging.info("Le fichier cache/SynApps.sqlite n'existe pas, création de la base de donnée.")
			self.connection = sqlite3.connect("cache/SynApps.sqlite")
			self.connection.row_factory = sqlite3.Row
			self.curseur = self.connection.cursor()
			
			#  Création des tables
			logging.debug("Création des tables.")
			# Configuration
			self.curseur.execute("CREATE TABLE config (name TEXT, value TEXT)")
			
			# Sources
			self.curseur.execute("CREATE TABLE sources ("
				"srcid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,"
				"name TEXT,"
				"url TEXT,"
				"active BOOLEAN DEFAULT 1,"
				"repository TEXT,"
				"md5 TEXT DEFAULT 0)")
			self.curseur.execute("CREATE TABLE sources_app ("
				"srcid INTEGER,"
				"appid INTEGER)")
			
			# Catégories
			self.curseur.execute("CREATE TABLE categories ("
				"catid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,"
				"parent INTEGER,"
				"name TEXT,"
				"icon TEXT)")
			self.curseur.execute("CREATE TABLE categories_app ("
				"catid INTEGER,"
				"appid INTEGER)")
			
			# Applications
			self.curseur.execute("CREATE TABLE applications ("
				"appid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,"
				"parentname TEXT,"
				"name TEXT,"
				"icon TEXT,"
				"desc TEXT,"
				"longdesc TEXT,"
				"zipsize INTEGER,"
				"size INTEGER,"
				"framasoft TEXT,"
				"framakey TEXT,"
				"website TEXT,"
				"installdir TEXT,"
				"dir TEXT,"
				"exe TEXT,"
				"licence TEXT,"
				"installedversion TEXT DEFAULT 0,"
				"state INTEGER DEFAULT 0,"
				"change INTEGER DEFAULT 0)")
			
			# Téléchargements
			self.curseur.execute("CREATE TABLE downloads ("
				"dlid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,"
				"appid INT,"
				"srcid INT,"
				"version TEXT,"
				"link TEXT,"
				"author TEXT)")
			
			# Dossiers à sauvegarder
			self.curseur.execute("CREATE TABLE dirsaves ("
				"dsid INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,"
				"appid INT,"
				"dossier TEXT)")
			
			# Ajout des sources par défaut
			logging.debug("Ajout des dépôts Framakey.")
			self.execute("INSERT INTO sources (srcid, name, url, repository, active) VALUES (0,'Local','.','',1)")
			self.add_src('http://files.framakey.org/testing/non-free.xml')
			self.add_src('http://files.framakey.org/testing/main.xml')
			self.add_src('http://files.framakey.org/unstable/non-free.xml')
			self.add_src('http://files.framakey.org/unstable/main.xml')
			self.add_src('http://files.framakey.org/stable/non-free.xml')
			self.add_src('http://files.framakey.org/stable/main.xml')

			# Ajout de la configuration par défaut
			logging.debug("Ajout de la configuration par défaut.")
			self.set_config('appspath', '../../../../Apps')
			self.set_config('version', '0.2.0.7')
			self.set_config('proxycheckbox', False)
			
			# Exécution
			self.connection.commit()
		else:
			logging.debug("Le fichier cache/SynApps.sqlite existe.")
			self.connection = sqlite3.connect("cache/SynApps.sqlite")
			self.connection.row_factory = sqlite3.Row
			self.curseur = self.connection.cursor()
			# si besoin, maj de la bdd
			if self.get_config('version') != "0.2.0.7":
				self.set_config('version', '0.2.0.7')
			if self.get_config('proxycheckbox') == None:
				self.set_config('proxycheckbox', False)
			
	def query(self, query, data=()):
		self.curseur.execute(query, data)
		return self.curseur.fetchall()
		
	def execute(self, query, data=()):
		self.curseur.execute(query, data)
		self.connection.commit()
		
		return self.curseur.lastrowid

	def remove_all(self, src=False):
		"""Vide les tables contenant le catégories, les applications et les téléchargements"""
		# Sources
		if src:
			self.curseur.execute("DELETE FROM sources")
			self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'sources'")
		self.curseur.execute("DELETE FROM sources_app")
		
		# Catégories
		self.curseur.execute("DELETE FROM categories")
		self.curseur.execute("DELETE FROM categories_app")
		self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'categories'")
		
		# Applications
		self.curseur.execute("DELETE FROM applications")
		self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'applications'")
		
		# Téléchargements
		self.curseur.execute("DELETE FROM downloads")
		self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'downloads'")
		
		# Dossiers à sauvegarder
		self.curseur.execute("DELETE FROM dirsaves")
		self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'dirsaves'")
		
		self.connection.commit()

	def remove_all_from_src(self, srcid):
		apps = self.query("SELECT appid FROM sources_app WHERE srcid = ?", (srcid,))
		
		self.curseur.execute("DELETE FROM sources_app WHERE srcid = ?", (srcid,))
		
		for app in apps:
			app = app["appid"]
			srcs = self.query("SELECT srcid FROM sources_app WHERE appid = ?", (app,))
			
			if len(srcs) < 1:
				self.curseur.execute("DELETE FROM applications WHERE appid = ?", (app,))
				self.curseur.execute("DELETE FROM categories_app WHERE appid = ?", (app,))
				self.curseur.execute("DELETE FROM dirsaves WHERE appid = ?", (app,))
			
			while gtk.events_pending():
						gtk.main_iteration_do(False)
		
		self.curseur.execute("DELETE FROM downloads WHERE srcid = ?", (srcid,))
		
		self.connection.commit()
	
	def remove_empty_categories(self):
		cats = self.query("SELECT catid FROM categories ORDER BY parent DESC")
		
		for cat in cats:
			if len(self.query("SELECT catid FROM categories WHERE parent = ?", (cat["catid"],))) == 0:
				if self.query("SELECT * FROM applications, categories_app, sources_app, sources WHERE applications.appid = categories_app.appid AND categories_app.catid = ?", (cat["catid"],)) == 0:
					self.curseur.execute("DELETE FROM categories WHERE catid = ?", (cat["catid"],))
			
				while gtk.events_pending():
					gtk.main_iteration_do(False)
		
		self.connection.commit()
	
	def remove_src(self):
		self.curseur.execute("DELETE FROM sources WHERE srcid != 0")
		self.curseur.execute("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'sources'")
		
		self.connection.commit()
	
	def add_src(self, url, active=True):
		url = url.replace("\n", "")
		try:
			content = xml.etree.ElementTree.fromstring(urlstuff.urlopen(url).read())
			
			id = self.execute("INSERT INTO sources (name, url, repository, active) VALUES (?,?,?,?)", (content.get("name", "Depot"), url, content.get("repository", "Stable"), active))
		except:
			raise IOError("Erreur: Le fichier " + url + " est invalide.")
	
	# Méthodes utilisées pour l'importation des dépôts
	
	def update(self, force=False):
		logging.info("Mise à jour de la base de données.")
		errors = []
		
		# Mise en place du proxy
		logging.debug("Mise en place du proxy")
		if self.get_config("proxycheckbox", False) == "True":
			urlstuff.proxydef(self.get_config("proxyhost", ""), self.get_config("proxyport", ""), self.get_config("proxylogin", ""), self.get_config("proxypass", ""))
		
		logging.debug("Proxy en place")
		try:
			urlstuff.urlopen('http://files.framakey.org/index.html')
		except urlstuff.ConnectError:
			logging.debug("Pas de connexion")
			raise IOError("Erreur, votre connexion Internet n'a pas l'air de fonctionner.\nVeuillez vérifier le paramétrage du proxy dans les préférences")
		else:
			for row in self.query("SELECT name, srcid, url, md5 FROM sources WHERE srcid != 0"):
				logging.debug("Mise à jour du dépôt : %s (%s)" % (row['name'], row['url']))
				try:
					tmp = urlstuff.urlopen(row['url']).read()
					content = xml.etree.ElementTree.fromstring(tmp)
				except IOError:
					errors.append(row['url'])
				else:
					if hashlib.md5(tmp).hexdigest() != row["md5"] or force:
						logging.debug("Le dépôt a été modifié depuis la dernière mise à jour.")
						self.remove_all_from_src(row["srcid"])
						self.execute("UPDATE sources SET name = ?, repository = ?, md5 = ? WHERE url = ?", (content.get("name", "Depot"), content.get("repository", "Stable"), hashlib.md5(tmp).hexdigest(), row['url']))
						self.import_cat(content, row['srcid'])
						
						while gtk.events_pending():
							gtk.main_iteration_do(False)
					else:
						logging.debug("Le dépôt n'a pas été modifié depuis la dernière mise à jour.")
			
			self.execute("UPDATE applications SET state = 0, change = 0, installedversion = '0'")
			
			self.remove_all_from_src(0)
			self.import_installed_apps()
			self.import_installed_tools()
				
			if len(errors) > 0:
				raise IOError("Erreur, les dépôts suivants sont invalides: " + ', '.join(errors))
			
		logging.info("Fin de la mise à jour de la base de données")

	def import_installed(self, targetpath):
		if os.path.isdir(targetpath):
			for filename in os.listdir(targetpath):
				if os.path.isdir(os.path.join(targetpath, filename)) and os.path.isfile(os.path.join(targetpath, filename, "App/AppInfo/appinfo.ini")):
					infos = appinfo_reader.get_infos(filename, targetpath)
					
					if infos != None:
						logging.debug("Importation de l'applications : %s" % infos['name'])
						
						try:
							typeid = self.query("SELECT catid FROM categories WHERE parent = 0 AND name = ?", (infos["type"],))[0]["catid"]
						except:
							typeid = self.execute("INSERT INTO categories (parent, name, icon) VALUES (0,?,?)", (infos["type"], "icons/default-cat.png"))
						
						try:
							catid = self.query("SELECT catid FROM categories WHERE parent = ? AND name = ?", (typeid, infos["category"],))[0]["catid"]
						except:
							catid = self.execute("INSERT INTO categories (parent, name, icon) VALUES (?,?,?)", (typeid, infos["type"], "icons/default-cat.png"))
						
						app = self.get_app(name=infos["name"])
						if app != None:
							downloads = self.get_downloads(app["appid"])
							
							if downloads:
								state = 1
								if version.LooseVersion(downloads[0]["version"]) <= version.LooseVersion(infos["version"]):
									state = 2
							else:
								state = 2
							
							self.curseur.execute("UPDATE applications SET state=?, change=?, installedversion=? WHERE appid = ?", (state, state, infos["version"], app["appid"]))
						
						else:
							try:
								appid = self.execute("INSERT INTO applications (parentname, name, icon, desc, longdesc, size, framasoft, framakey, website, installdir, dir, exe, licence, installedversion, state, change) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", 
													(infos["parentname"], infos["name"], infos["icon"], infos["desc"], infos["longdesc"], infos["size"], infos["framasoft"], infos["framakey"], infos["website"], infos["installdir"], infos["dir"], infos["exe"], infos["licence"], infos["version"], 2, 2))
							except:
								continue
							for dirsave in infos["dirsave"]:
								if dirsave != "":
									self.curseur.execute("INSERT INTO dirsaves (appid, dossier) VALUES (?,?)", (appid, dirsave))
									
							self.curseur.execute("INSERT INTO sources_app (srcid, appid) VALUES (?,?)", (0, appid))
				
							for parent in self.get_parents(catid, [catid]):
								self.curseur.execute("INSERT INTO categories_app (catid, appid) VALUES (?,?)", (parent, appid))
							
							self.connection.commit()
											
							while gtk.events_pending():
								gtk.main_iteration_do(False)
		else:
			raise IOError("Erreur: Le dossier Apps (" + targetpath + ") n'existe pas. Modifiez le dans les préférences")
			

	def import_installed_apps(self):
		appspath = self.get_config("appspath", "../../../../Apps")
		
		logging.debug("Importation des applications déjà installées")
		
		self.import_installed(appspath)

	def import_installed_tools(self):
		try:
			toolspath = os.environ['FramaPython'].replace("\PythonPortable", "")
		except:
			toolspath = "../../../"
		
		logging.debug("Importation des outils déjà installés")
		
		self.import_installed(toolspath)
		
	def import_cat(self, element, srcid, parent = 0):
		categories = element.findall("category")
		for elementtree in categories:
			try:	name = elementtree.get("name")
			except:	return
			
			logging.debug("Importation de la catégorie : %s" % name)
			
			result = self.query("SELECT catid FROM categories WHERE name=? AND parent=? LIMIT 0,1", (name, parent))
			if len(result) == 0:
				icon = dl_icon(elementtree.get("icon"))

				id = self.execute("INSERT INTO categories (parent, name, icon) VALUES (?,?,?)", (parent, name, icon))
			else:
				id = result[0]['catid']
			
			self.import_apps(elementtree, srcid, id)
			self.import_cat(elementtree, srcid, id)
		
	def import_apps(self, elementtree, srcid, catid):
		"""Récupère les informations depuis un element xml"""
		apps = elementtree.findall("app")

		for elementtree in apps:
			name = get_element(elementtree, "name")
			
			logging.debug("Importation de l'application : %s" % name)
			
			result = self.query("SELECT appid, state, installedversion FROM applications WHERE name=? LIMIT 0,1", (name,))
			if len(result) == 0:
				parentname = get_element(elementtree, "parentname")
					
				icon = get_element_text(elementtree, "icon", "icons/default-app.png")
				if icon != "icons/default-app.png":
					icon = dl_icon(icon)
				
				desc = get_element_text(elementtree, "desc")
				longdesc = get_element_text(elementtree, "longdesc")
				
				zipsize = int(get_element_text(elementtree, "zipsize", 0))
				size = int(get_element_text(elementtree, "size", 0))
				
				framasoft = get_element_text(elementtree, "framasoft")
				framakey = get_element_text(elementtree, "framakey")
				website = get_element_text(elementtree, "website")
				
				dir = get_element_text(elementtree, "dir", name)
				if dir == "*None*":
					dir = ""
				exe = get_element_text(elementtree, "exe", name + ".exe")
				installdir = get_element_text(elementtree, "installdir")
				licence = get_element_text(elementtree, "licence")
				
				id = self.execute("INSERT INTO applications	(parentname, name, icon, desc, longdesc, zipsize, size, framasoft, framakey, website, installdir, dir, exe, licence) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", 
															(parentname, name, icon, desc, longdesc, zipsize, size, framasoft, framakey, website, installdir, dir, exe, licence))
				
				self.curseur.execute("INSERT INTO sources_app (srcid, appid) VALUES (?,?)", (srcid, id))
				
				for parent in self.get_parents(catid, [catid]):
					self.curseur.execute("INSERT INTO categories_app (catid, appid) VALUES (?,?)", (parent, id))
				
				self.import_dirsave(elementtree, id)
			else:
				id = result[0]["appid"]
				installedversion = result[0]["installedversion"]
				installed = (result[0]["state"] > 0)
			
			self.import_downloads(elementtree, id, srcid)
			
			self.connection.commit()
		
	def import_dirsave(self, elementtree, appid):
		dirsaves = elementtree.findall("dirsave")
		for dirsave in dirsaves:
			if dirsave.text == None or dirsave.text == "":
				return
			else:
				self.curseur.execute("INSERT INTO dirsaves (appid, dossier) VALUES (?,?)", (appid, dirsave.text))

		
	def import_downloads(self, elementtree, appid, srcid):
		downloads = elementtree.findall("download")
		for download in downloads:
			version = get_element(download, "version", 0)
			author = get_element(download, "author")
			link = get_element_text(elementtree, "download")
			
			self.curseur.execute("INSERT INTO downloads (appid, srcid, version, link, author) VALUES (?,?,?,?,?)", (appid, srcid, version, link, author))
				
			while gtk.events_pending():
				gtk.main_iteration_do(False)
	
	# Méthodes utilisées directement par SynApps
	
	def get_sources(self):
		return self.query("SELECT * FROM sources WHERE srcid != 0")
	
	def get_parents(self, cat, listparents=[]):
		if cat <= 0:
			return []
		else:
			parent = self.query("SELECT parent FROM categories WHERE catid = ? ORDER BY LOWER(categories.name) LIMIT 0,1", (cat,))[0]['parent']
			listparents.append(parent)
			if parent != 0:
				return self.get_parents(parent, listparents)
			else:
				listparents.reverse()
				return listparents
		
	def get_cats(self, parent=None):
		if parent == None:
			return [self.get_cat(0), self.get_cat(-1), self.get_cat(-2)]
		else:
			return self.query("SELECT * FROM categories WHERE parent = ? AND EXISTS (SELECT * FROM categories_app, sources_app, sources WHERE categories.catid = categories_app.catid AND categories_app.appid = sources_app.appid AND sources_app.srcid == sources.srcid AND sources.active = 1) ORDER BY LOWER(name)", (parent,))
		
	def get_cat(self, id):
		if id == -2:
			return {'catid': -2, 'parent': None, 'name': _("Mises à jour disponibles"), 'icon': "icons/catupdated.png"}
		elif id == -1:
			return {'catid': -1, 'parent': None, 'name': _("Logiciels installés"), 'icon': "icons/catinstalled.png"}
		elif id == 0:
			return {'catid': 0, 'parent': None, 'name': _("Obtenir des logiciels libres"), 'icon': "icons/SynApps24.png"}
		else:
			return self.query("SELECT * FROM categories WHERE catid = ? LIMIT 0,1", (id,))[0]
			
	def get_apps(self, cat = 0, search=""):
		if cat == -2:
			return self.query("SELECT * FROM applications WHERE state = 1 AND (name LIKE '%" + search + "%' OR desc LIKE '%" + search + "%') ORDER BY LOWER(parentname)")
		elif cat == -1:
			return self.query("SELECT * FROM applications WHERE state > 0 AND (name LIKE '%" + search + "%' OR desc LIKE '%" + search + "%') ORDER BY LOWER(parentname)")
		else:
			return self.query("SELECT * FROM applications, categories_app, sources_app, sources WHERE applications.appid = sources_app.appid AND sources_app.srcid = sources.srcid AND sources.active = 1 AND applications.appid = categories_app.appid AND categories_app.catid = ? AND (applications.name LIKE '%" + search + "%' OR applications.desc LIKE '%" + search + "%') ORDER BY LOWER(applications.parentname)", (cat,))
	
	def get_apps_to_install(self):
		return self.query("SELECT * FROM applications WHERE state = 0 AND change > 0 ORDER BY LOWER(parentname)")
		
	def get_apps_to_update(self):
		return self.query("SELECT * FROM applications WHERE state = 1 AND change = 2 ORDER BY LOWER(parentname)")
		
	def get_apps_to_uninstall(self):
		return self.query("SELECT * FROM applications WHERE state > 0 AND change = 0 ORDER BY LOWER(parentname)")
	
	def get_app(self, id=None, name=None):
		if id != None:
			apps = self.query("SELECT * FROM applications WHERE appid = ?", (id,))
		if name != None:
			apps = self.query("SELECT * FROM applications WHERE name = ?", (name,))
		
		if len(apps) > 0:
			return apps[0]
		else:
			return None
		
	def get_downloads(self, appid):
		return self.query("SELECT * FROM downloads, sources WHERE downloads.srcid = sources.srcid AND sources.active = 1 AND downloads.appid = ? ORDER BY downloads.version DESC", (appid,))
	
	def get_dirsave(self, appid):
		list = [x["dossier"] for x in self.query("SELECT * FROM dirsaves WHERE appid = ?", (appid,))]
		list.append("Data")
		return list
	
	def get_config(self, name=None, default=None):
		if name == None:
			return self.query("SELECT * FROM config")
		else:
			self.curseur.execute("SELECT * FROM config WHERE name = ?", (name,))
			value = self.curseur.fetchone()
			if value == None:
				return default
			else:
				if value['value'] == None:
					return default
				else:
					if value['value'].isdigit():
						return int(value['value'])
					else:
						return value['value']
		
	def set_config(self, name, value):
		self.curseur.execute("SELECT * FROM config WHERE name = ?", (name,))
		if self.curseur.fetchone() == None:
			self.execute("INSERT INTO config (name, value) VALUES (?, ?)", (name, str(value)))
		else:
			self.execute("UPDATE config SET value = ? WHERE name = ?", (str(value), name))
	
	def set_change(self, id, change):
		self.execute("UPDATE applications SET change = ? WHERE appid = ?", (change, id))
	
	def set_active(self, id, active):
		self.execute("UPDATE sources SET active = ? WHERE srcid = ?", (active, id))
	
def _(str):
	return str
