from .db import DBPoolManager

import cherrypy


BUGSTATUS=["OPEN", "CLOSED", "UNCONF", "REJECT", "UPSTRM"]
BUGTYPES=["BUG", "DISCUS", "PATCH"]

class Site():
	def __init__(self,dbpool:DBPoolManager):
		self.dbpool=dbpool
		cherrypy.engine.subscribe("db-started",self.on_db_connect)

		self.bugcache={}
	
	def on_db_connect(self):
		with self.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute(
				"CREATE TABLE IF NOT EXISTS trackers ("
				"name VARCHAR(80),"
				"category VARCHAR(80),"
				"homepage VARCHAR(1024),"
				"readme_url VARCHAR(1024)"
				")"
				)

			cur.execute(
				"CREATE TABLE IF NOT EXISTS bugs ("
				"tracker VARCHAR(80),"
				"bugid INT,"
				"subject VARCHAR(1024),"
				"description TEXT,"
				"status VARCHAR(6),"
				"type VARCHAR(6)"
				")"
				)

			cur.execute(
				"CREATE TABLE IF NOT EXISTS subscribers ("
				"tracker VARCHAR(80),"
				"bugid INT,"
				"email VARCHAR(80)"
				")"
				)

	def getbug(self,tracker:str,bugid):
		# make sure bug exists
		with self.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT 1 FROM bugs WHERE tracker=? AND bugid=? LIMIT 1",(tracker,bugid))
			if not cur.fetchone():
				return None

		# fetch already existent bug object if possible, otherwise make new one
		if (tracker,bugid) in self.bugcache:
			bug=self.bugcache[(tracker,bugid)]
		else:
			bug=Bug(self,tracker,bugid)
			self.bugcache[(tracker,bugid)]=bug

		return bug

	def newbug(self,tracker:str,bugtype:str):
		if not bugtype in BUGTYPES:
			raise ValueError("Bugtype illegal")
		with self.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT TOP 1 bugid FROM bugs WHERE tracker=? SORT BY bugid DESC",(tracker,))
			nr=cur.fetchone()
			if not nr:
				nr=0

			nr+=1
			cur.execute("INSERT INTO bugs VALUES (?,?)",(tracker,nr,"","","OPEN",bugtype))

		return self.getbug(tracker=tracker,bugid=nr)

	

class Bug():
	def __init__(self,site:Site,tracker:str,bugid:int):
		self.site=site
		self.tracker=tracker
		self.bugid=bugid
		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT (subject,description,status,type) FROM bugs WHERE tracker=? AND bugid=? LIMIT 1",(tracker,bugid))
			data=cur.fetchone()
			if not data:
				raise ValueError("Bug %s#%d does not exists!"%(tracker,bugid))

			self._cache={
					"subject":data[0],
					"description":data[1],
					"status":data[2],
					"type":data[3]
				}
	
	# NOTE: this is a lot of repeated code, maybe we can rewrite this into a __setattr__ and __getattribute__ method?


	def __repr__(self):
		return "<acit.types.Bug\n\tsubject=%s\n\ttype=%s\n\tstatus=%s\n\t>"%(self.subject,self.type,self.status)

	@property
	def subject(self):
		"The subject of this bug"
		return self._cache["subject"]

	@subject.setter
	def subject(self,value):
		if len(value)>1024:
			raise ValueError("Subject length not allowed to be higher than 1024, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE bugs SET subject=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["subject"]=value

	@property
	def description(self):
		"The description of this bug"
		return self._cache["description"]

	@description.setter
	def description(self,value):
		if len(value)>65535:
			raise ValueError("Subject length not allowed to be higher than 65535, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE bugs SET description=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["description"]=value

	@property
	def status(self):
		"The status of this bug"
		return self._cache["status"]

	@status.setter
	def status(self,value):
		if not value in BUGSTATUS:
			raise ValueError("Invalid status given.")

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE bugs SET status=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["status"]=value

	@property
	def type(self):
		"The type of this bug"
		return self._cache["type"]

	@type.setter
	def type(self,value):
		if not value in BUGTYPES:
			raise ValueError("Invalid type given.")

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE bugs SET type=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["type"]=value
	
	@property
	def subscribers(self):
		"""
		List of emailaddresses subscribed. Readonly, to add or remove, use appropriate methods.
		NOTE: this executes an SQL query.
		"""
		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT email FROM subscribers WHERE tracker=? AND bugid=?",(self.tracker,self.bugid))
			return [ value[0] for value in cur ]
	
	def addsubscriber(self,email):
		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("INSERT INTO subscribers VALUES (?,?,?)",(self.tracker,self.bugid,email))

	def rmsubscriber(self,email):
		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("DELETE FROM subscribers WHERE tracker=? AND bugid=? AND email=?)",(self.tracker,self.bugid,email))


class Tracker():
	def __init__(self,site:Site,tracker:str):
		self.site=site
		self.tracker=tracker
		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT (name,category,homepage,readme) FROM trackers WHERE name=? LIMIT 1",(tracker,))
			data=cur.fetchone()
			if not data:
				raise ValueError("Tracker %s does not exists!"%tracker)

			self._cache={
					"name":data[0],
					"category":data[1],
					"homepage":data[2],
					"readme":data[3]
				}
	
	# NOTE: this is a lot of repeated code, maybe we can rewrite this into a __setattr__ and __getattribute__ method?
	@property
	def name(self):
		"The name of this tracker"
		return self._cache["name"]

	@name.setter
	def name(self,value):
		if len(value)>80:
			raise ValueError("Subject length not allowed to be higher than 80, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE trackers SET name=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["name"]=value

	@property
	def category(self):
		"The category of this tracker"
		return self._cache["category"]

	@category.setter
	def category(self,value):
		if len(value)>80:
			raise ValueError("Subject length not allowed to be higher than 80, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE trackers SET category=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["category"]=value

	@property
	def homepage(self):
		"The homepage of this tracker"
		return self._cache["homepage"]

	@homepage.setter
	def homepage(self,value):
		if len(value)>1024:
			raise ValueError("Subject length not allowed to be higher than 1024, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE trackers SET homepage=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["homepage"]=value

	@property
	def readme_url(self):
		"The readme_url of this tracker"
		return self._cache["readme_url"]

	@readme_url.setter
	def readme_url(self,value):
		if len(value)>1024:
			raise ValueError("Subject length not allowed to be higher than 1024, this is %d"%len(value))

		with self.site.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("UPDATE trackers SET readme_url=? WHERE tracker=? AND bugid=?",(value,self.tracker,self.bugid))
			self._cache["readme_url"]=value