from typing import Optional,Any,Generator
from typing_extensions import Self
from dataclasses import dataclass,fields,field
from datetime import datetime
import MySQLdb, MySQLdb.cursors
from Utils import toInt as i
from pprint import pprint
from json import dumps
import Utils
import sys
from Utils import Debug,toInt,toList,toStr

_DEBUG = True

from MySQLdb import converters

def convert_to_bool(bit):
	try:
		if int(bit)>0:
			converted_value = True
		else:
			converted_value = False
	except Exception as e:
		Utils.Debug(title="Trouble with bool",data=e)
		converted_value = False
	return converted_value

data_conversation = converters.conversions
data_conversation[MySQLdb.constants.FIELD_TYPE.TINY] = convert_to_bool

class DB_Configs():
	"""Credentials live in io/.config (gitignored), one section per environment."""

	@classmethod
	def get(cls,key:str) -> dict[str,str] | None:
		return Utils.config(key) or None

	@classmethod
	def default(cls) -> dict[str,str] | None:
		return cls.get("pp")

class DB():
	_debug:bool = False
	error:str = ""
	rows_affected:int = 0
	last_id:Any

	def __init__(self,config:Optional[dict]=None):
		config = config or DB_Configs.default() or {}
		self.db = MySQLdb.connect(host=config["host"], user=config["user"], passwd=config["passwd"], db=config["db"], cursorclass=MySQLdb.cursors.DictCursor,use_unicode=True,conv=data_conversation)
		self.db.set_character_set('utf8')
		self.dbName = config["db"]
		self.cur = None
		self._debug = False

	def debug(self, sql):
		if self._debug:
			Utils.Debug(title="SQL",data=sql)

	@classmethod
	def fuzzyr(cls, val):
		return f"{val}%"

	@classmethod
	def fuzzyl(cls, val):
		return f"%{val}"

	@classmethod
	def fuzzy(cls, val):
		return f"%{val}%"

	def escape(self,val):
		return self.db.escape(val)

	def close(self):
		if self.db and self.db.open:
			self.db.close()

	def commit(self):
		self.db.commit()

	def get_keys(self,table:str):
		keys = []
		fields = []
		for row in self.query(sql=f"describe {table}"):
			fields.append(row.get("Field"))
			if row.get("Key") == "PRI":
				keys.append(row.get("Field"))
		return (keys,fields)

	def query(self, sql:str, values:Optional[list]=None,commit:bool=False, debug:bool=False):
		cur = self.db.cursor(MySQLdb.cursors.DictCursor)
		self.error = ""
		self.rows_affected = 0
		values = toList(values)
		try:
			if values:
				cur.execute(sql, values)
			else:
				cur.execute(sql)
		except Exception as e:
			self.error = str(e)
			Utils.Debug(title="Query Debug",data={"Error":self.error,"sql":sql,"parms":values,"records":cur.rowcount},debug=True)
			return []
		if commit:
			self.db.commit()
		self.db.commit()
		self.rows_affected = cur.rowcount
		self.last_id = cur.lastrowid
		#Utils.Debug(title="Query Debug",data={"sql":sql,"parms":values,"commit":commit,"records":cur.rowcount},debug=debug)

		for row in cur.fetchall():
			yield row

	def cursor(self,cursor_type:Any = MySQLdb.cursors.DictCursor):
		return self.db.cursor(cursor_type)

class data_row(dict):	
	def __str__(self):
		return self.__repr__()

	def __repr__(self):
		return Utils.toJSON(self)

@dataclass(slots=True)
class user_row():
	id: int
	user: str
	pw: str

	def __getitem__(self,key):
		return getattr(self,key)

class row(dict):
	pass

class row_ext():
	_db: DB
	_table:str
	_data:data_row

	def __init__(self,table:str,db:DB,data:Any,row_cls:str = "data_row"):
		self._table = table
		self._db = db

		if row_cls:			
			self._data = getattr(sys.modules[__name__], row_cls)(**data)
		else:
			self._data = data


	def __getitem__(self,key):
		return getattr(self._data,key)


	def __getattribute__(self,key:str):		
		try:
			return object.__getattribute__(self,key)
		except:
			try:
				return getattr(self._data,key)
			except:
				return ""

	def get(self,key:str,default:Any = "") -> Any:
		try:
			return self._data[key]
		except:
			return default
	
	def items(self):
		return self._data.items()
	
	def __str__(self) -> str:
		return Utils.toJSON(self._data)
	
	def __repr__(self) -> str:	
		return Utils.toJSON(self)

class Row(dict):
	_table:str = ""
	_db:DB
	_fields:Optional[list] = None
	_keys:Optional[list] = None
	
	def __init__(self,table:str,data:dict,db:DB):
		self.table = table
		self._db = db
		super().__init__(data)

	@property
	def table(self):
		return self._table

	@table.setter
	def table(self,table:str) -> str:
		self.table = table
		return self._table 

	@property
	def fields(self) -> list:
		if not self._fields:
			self._keys,self._fields = self._db.get_keys(self.table)
		return self._fields or []

class Query_Builder():
	_query:str = ""
	_parms:list
	_join:str = ""
	_limit:str = ""
	_order:str = ""
	_group:str = ""
	_having:str = ""
	_suffix:str = ""
	_keys:Optional[list]
	_q_cache:dict[str,str]
	_field_names:Optional[list] = None
	_values:dict[str,Any] = {}
	_commit:bool = False
	debug:bool = False
	def __init__(self,action:str="select",filter:str="",parms:Optional[list]=None,limit:str="",order:str="",fields:str="*",join:str="",table:str="",group:str="",having:str="",suffix:str="",q_cache:Optional[dict]=None,vals:Optional[dict]=None,parent:str="",keys:Optional[list] = None,field_names:Optional[list]=None):
		self._query = filter or ""
		self._parms = parms or []
		self._action = str(action).lower()
		self._table = table
		self._fields = fields
		self._suffix = suffix
		self._q_cache = q_cache or {}
		self._parent = parent
		self._values = vals or {}
		self._keys = keys or []
		self._field_names = field_names
		self.action(action)
		self.order(order)
		self.limit(limit)
		self.group(group)
		self.join(join)
		self.having(having)
	
	@property
	def commit(self) -> bool:
		return self._commit

	def action(self,action:str) -> Self:
		self._action = str(action).lower()
		return self

	# def select(self,**args):
	# 	return self._parent.select(**args)

	def having(self,having:str) -> Self:
		if self._having:
			self._having +=f" and {having}"
		else:
			self._having = having
		return self

	def group(self,group:str) -> Self:
		if str(group).find("group by")>=0:
			group = group.replace("group by ","")
		self._group = group
		return self

	def values(self,data:dict) -> Self:
		Utils.Debug(title="before insert val/parm",data=[self._values,self._parms])
		
		if isinstance(data,dict):
			self._values.update(data)
		return self

	def join(self,join:str) -> Self:		
		if self._q_cache and join in self._q_cache:
			join = self._q_cache.get(join) or ""

		if isinstance(join,str) and join and join.find("join")<0:
			join = "join {join}"

		if self._join:
			self._join +=f" {join}"
		else:
			self._join = join
		return self

	def limit(self,limit:str) -> Self:
		self._limit = limit
		return self

	def fields(self,fields:str) -> Self:
		self._fields = fields
		return self

	def order(self,field:str,direction:str="") -> Self:
		if str(field).find("order by")>=0:
			field = field.replace("order by","")
		self._order = field
		if direction:
			self._order+=f" {direction}"
		return self

	def filter_item(self,field:str,val:Optional[str]=None,op:str='=') -> Self:
		q = f"{field} {op} %s"
		self.filter(query=q,parms=[val])
		return self

	def filter_data(self,data:dict,op:str = '=',condition:str="and",filter_join="and") -> Self:
		try:
			assert isinstance(data,dict), "Data must be a dictionary"
			filterStr = ""
			key_list = []
			parms = []
			for k,v in data.items():
				key_list.append(f"`{k}`{op}%s")
				parms.append(v)
			filterStr = f" {filter_join} ".join(key_list)
			self.filter(query=f"({filterStr})",parms=parms,op=condition)

		except Exception as e:
			Debug(title="trouble with sQuery.filter_data",data=e)
		return self

	def filter_list(self,field:str,items:list,op:str='=',condition:str="and",filter_join:str="and") -> Self:
		try:
			assert items, "No items"
			assert field, "No field"
			filterStr = ""
			parms = []
			q = []
			for item in items:
				q.append(f"{field} {op} %s")
				# filterStr+=" {} {} {} %s".format(condition,field,op)
				parms.append(item)

			filterStr = " {} ".format(condition).join(q)
			# filterStr = "({})".format(filterStr[len(condition)+2:])
			self.filter(query=f"({filterStr})",parms=parms,op=filter_join)
			return self
		except Exception as e:
			Debug(title="Trouble",data=e)
			return self

	def filter(self,query:str,parms:Optional[list]=None,op:str="and") -> Self:
		parms = toList(parms)
		if self._query:
			self._query+=f" {op} {query}"
		else:
			self._query = query

		self._parms += parms
		return self

	def lookup_keys(self) -> list:
		return self._keys
	
	def insert(self,data:dict) -> Self:
		self.action("insert")
		self.values(data)

		return self
	
	def update(self,data:dict,filter:Optional[str]=None,parms:Optional[list]=None) -> Self:
		self.action("update")
		self.values(data)
		if filter:
			self.filter(query=filter,parms=parms)
		if self._keys:
			key_parms = {key:data.get(key) for key in self._keys if key in self._values}
			self.filter_data(data=key_parms)
		return self

	def delete(self,data:Optional[dict[str,Any]],filter:Optional[str]=None,parms:Optional[list]=None) -> Self:
		self.action("delete")
		if filter:
			self.filter(query=filter,parms=parms)
		elif data:
			try:
				filter_d = {key:data[key] for key in self._keys}
				self.filter_data(data=filter_d)
			except Exception as e:
				Utils.Debug(title="Missing key for delete",data=e)
		
		
		return self

	def __str__(self) -> str:
		self.build()
		s = f"Query: {self._query}\n"
		s += f"sql: {self.build()}\n"
		s += f"Parms: {self._parms}\n"
		s += f"Values: {self._values}\n"
		s += f"Commit: {self.commit}\n"
		return s

	def build(self) -> tuple:
		field_names = self._field_names or (list(self._values.keys()) if self.values else [])
		sql = ""
		if self._action.lower() == 'update':
			if self._values:
				sql = f"update {self._table}"
				sql+=" set "
				val_set = self._values or {}
				sql_set = []
				for k,v in val_set.copy().items():
					if v is None:
						val_set[k] = None
					##### Remove field not in table #####
					if k not in field_names or k in self._keys:
						del val_set[k]
					
					elif v !="null":
						sql_set.append(f"`{k}`=%s")
					else:
						sql_set.append(f"`{k}`=null")
						del val_set[k]
				sql+=",".join(sql_set)
				self._parms = list(val_set.values()) + list(self._parms)

		elif self._action.lower() == 'insert':
			if self._values:
				keys = self._keys or []
				Debug(title="Keys",data=keys)
				sql = f"insert into {self._table}"
				val_set = self._values or {}
				for k,v in val_set.copy().items():
					######## Remove fields not in table###########
					if (k not in field_names) or (k in keys):
						del val_set[k]
					
					elif v is None or v == "null":
						val_set[k] = None

				######## Build the field list ############
				names = [f"`{name}`" for name in val_set if name in field_names]
				######## Build the value list ############
				vals = ["%s" for name in val_set if name in field_names]


				sql+="({}) values({})".format(",".join(names),",".join(vals))
				self._parms = list(list(val_set.values()) + list(self._parms))
				Utils.Debug(title="After parms",data=self._parms)

		elif self._action.lower() == 'delete':
			sql = f"delete from {self._table}"
		else:
			sql = f"select {self._fields} from {self._table}"

		query = ""
		if self._query:
			query = self._query

		if self._suffix and self._action.lower()!='insert' and self._action.lower()!='update':
			if query:
				query+=" and "
			query+=self._suffix

		if self._join:
			sql += f" {self._join}"
		if self._query or query:
			sql += f" where {query}"
		if self._having:
			sql += f" having {self._having}"
		if self._group:
			sql += f" group by {self._group}"
		if self._order:
			sql += f" order by {self._order}"
		if self._limit:
			sql += f" limit {self._limit}"

		self._commit = False if self._action.lower() == "select" else True
		return (sql,self._parms)

class Model:
	table:str = ""
	qb:Optional[Query_Builder]
	db:DB
	row_cls = "data_row"
	_key_names:Optional[list] = None
	_field_names:Optional[list] = None
	_error:str = ""

	def __init__(self):
		self.db = DB()
		self.qb = self.qb_init()

	@property
	def key_names(self):
		if not self._key_names:
			self.table_struct()
		return self._key_names

	def log_error(self,error) -> Self:
		if isinstance(error,Exception):
			self._error = Utils.exception_info(error)
		else:
			self._error = str(error)
		return self

	@property
	def error(self) -> str:
		return str(self._error)

	def querySuffix(self) -> str:
		return ""

	def table_struct(self):
		self._key_names,self._field_names = self.db.get_keys(table=self.table)

	def mk_row(self,data:dict) -> row:
		return row(data)
		# return row(table=self.table,data=data,db=self.db,row_cls=self.row_cls)

	def qb_init(self,*argc,**argv) -> Query_Builder:
		argv["table"] = self.table
		argv["keys"] = self._key_names
		argv["field_names"] = self._field_names
		argv["keys"] = self._key_names
		argv["suffix"] = self.querySuffix()
		self.qb = Query_Builder(*argc,**argv)
		return self.qb

	def select_one(self) -> row:
		try:
			return list(self.select())[0]
		except:
			return self.mk_row({})

	def select_iter(self) -> Generator[row,list,None]:
		try:
			assert self.qb is not None

			sql,vals = self.qb.build()

			if self.qb.debug:
				Utils.Debug(title="SQL Query",data=[sql,vals])
			self.qb = None

			for row in self.db.query(sql=sql,values=vals,debug=True):
				yield self.mk_row(data=row)
		except Exception as e:
			Utils.Debug(title="select exception",data=e)

	def select(self) -> list:
		data = []
		try:
			assert self.qb is not None

			sql,vals = self.qb.build()

			if self.qb.debug:
				Utils.Debug(title="SQL Query",data=[sql,vals])
			self.qb = None

			for row in self.db.query(sql=sql,values=vals,debug=True):
				Utils.Debug(title="Row",data=[row])
				data.append(self.mk_row(data=row))
		except Exception as e:
			Utils.Debug(title="select exception",data=e)
		return data

	def execute(self) -> int:
		try:
			assert self.qb is not None
			sql,vals = self.qb.build()
			if self.qb.debug:
				Utils.Debug(title="Sql Exec Debug",data=[sql,vals])
			resp = list(self.db.query(sql=sql,values=vals,commit=True,debug=self.qb.debug))
			self.qb = None
			return self.db.rows_affected
		except:
			return 0

	@property
	def last_row_id(self):
		return self.db.db.insert_id()

	def add(self,data:dict) -> int:
		self.table_struct()
		self.qb = self.qb_init()
		self.qb.insert(data=data)
		self.qb.debug = True
		if self.execute():
			last_id = self.db.last_id
			print(f"Last ID {last_id}")
			Utils.Debug(title="Error",data=self.db.error)
			return last_id
		else:
			return 0

	def update(self,data:dict) -> int:
		self.table_struct()
		self.qb = self.qb_init()
		self.qb.update(data=data)
		self.qb.debug = True
		Debug(title="Calling update",data=data)
		return self.execute()

	def delete(self,data:Optional[dict]=None,query:Optional[str]=None,parms:Optional[list]=None) -> int:
		self.table_struct()
		self.qb = self.qb_init()
		self.qb.debug = True
		if data or query:
			self.qb.delete(data=data,filter=query,parms=parms)
		self.qb.debug = True
		return self.execute()

	def get(self,filter:Optional[str],parms:Optional[list]):
		self.qb = self.qb_init(limit="1")
		assert self.qb is not None

		if filter:
			self.qb.filter(query=filter,parms=parms)

		return Utils.first(list(self.select()),{})

	def all(self,filter:Optional[str]=None,parms:Optional[list]=None) -> list:
		try:
			self.qb_init()
			assert self.qb is not None
			if filter:
				self.qb.filter(query=filter,parms=parms)
			self.qb.debug = True
			return list(self.select())
		except Exception as e:
			Utils.Debug(title="DB trouble",data=str(e))
			return []

def empty_gen():
	yield