from threading import RLock
from datetime import datetime, timedelta

import cherrypy

from .db import DBPoolManager

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={}
		self.last_tracker_update=datetime.fromtimestamp(0)
		self.tracker_update_lock=RLock()
	
	def on_db_connect(self):
		cherrypy.log("Setting up database",context="SITE")
		with self.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute(
				"CREATE TABLE IF NOT EXISTS trackers ("
				"name VARCHAR(80) PRIMARY KEY,"
				"homepage 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)"
				")"
				)

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

			cur.execute(
				"CREATE TABLE IF NOT EXISTS tokens ("
				"email VARCHAR(80),"
				"token VARCHAR(80),"
				"tracker VARCHAR(80),"
				"bugid INT,"
				"usedin TINYTEXT DEFAULT NULL)"
			 	)
			
			conn.commit()

			self.update_all_bugpages(conn,cur)
	

	def update_all_bugpages(self,conn,cur,trackerfilter=None):
		if trackerfilter:
			cherrypy.engine.publish("regen",trackerfilter,None)
		else:
			cur.execute("SELECT name FROM trackers")
			for [tracker] in cur:
				cherrypy.engine.publish("regen",tracker,None)

		cur.execute("SELECT tracker,bugid FROM bugs")
		for [tracker,bugid] in cur:
			if trackerfilter and not trackerfilter==tracker:
				continue
			cherrypy.engine.publish("regen",tracker,bugid)


	def update_trackers(self,force=False):
		from os import getenv
		import subprocess

		def log(msg,traceback=False):
			cherrypy.log(msg=msg,traceback=traceback,context="REFRESH")

		with self.tracker_update_lock:
			if not force and self.last_tracker_update+timedelta(minutes=5)>datetime.now():
				return
			log("Updating trackerlist")
			try:
				with self.dbpool.get_connection() as conn, conn.cursor() as cur:
					script=getenv("ACIT_LIST_TRACKERS","/usr/lib/acit-list-trackers")
					proc=subprocess.run(script,capture_output=True)

					cur.execute("SELECT name FROM trackers")
					old_trackers=[ item[0] for item in cur ]

					if proc.stderr:
						log("Refresh script generated STDERR:")
						log(proc.stderr)

					rows=proc.stdout.decode().split("\n")
					for row in rows:
						if row: # skip empty lines
							data=row.split("\t")

							if not len(data)==2:
								log("Error processing line:\n> %s\nWeird value count (expected 2)."%row)
								continue

							name,homepage=data

							#log(name)


							cur.execute("REPLACE INTO trackers (name,homepage) VALUES (?,?)",(name,homepage))
							conn.commit()

							#self.update_all_bugpages(conn,cur,trackerfilter=name)
							if not name in old_trackers:
								cherrypy.engine.publish("regen",name,None)

					self.last_tracker_update=datetime.now()

			except Exception:
				cherrypy.log("Error refreshing trackers using script.",traceback=True,context="SITE")
			

	def getbug(self,tracker:str,bugid:int):
		# 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 bugid FROM bugs WHERE tracker=? ORDER BY bugid DESC LIMIT 1",(tracker,))
			nr=cur.fetchone()
			if not nr:
				nr=0
			else:
				nr=nr[0]

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

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

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

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

		return trackerobj

	def findtrackers(self,query:str):
		self.update_trackers()
		with self.dbpool.get_connection() as conn, conn.cursor() as cur:
			cur.execute("SELECT name FROM trackers")
			trackers:list[str]=[ value[0] for value in cur.fetchall() ]
			results=[]
			for tracker in trackers:
				if tracker==query:
					return [tracker]
				else:
					if tracker.endswith(query):
						results.append(tracker)
			return results



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))
			conn.commit()
			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))
			conn.commit()
			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))
			conn.commit()
			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))
			conn.commit()
			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))
			conn.commit()

	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))
			conn.commit()


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,homepage 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],
					"homepage":data[1],
					#"readme":data[2] # NOTE: could implement later, let's do that though
				}
	
	# 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. readonly, changing a tracker's name randomly seriously messes with the design."
		return self._cache["name"]

	@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))
			conn.commit()
			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))
			conn.commit()
			self._cache["readme_url"]=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 IS NULL",(self.tracker,))
			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 (?,NULL,?)",(self.tracker,email))
			conn.commit()
	
	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 IS NULL AND email=?)",(self.tracker,email))
			conn.commit()