from .Oms import Oms
from .DatabaseOms import DatabaseOms

from ..core import InstanceKey, InstanceReference, TenantReference

import mysql.connector

class MySQLDatabaseOms(DatabaseOms):
	
	def __init__(self, hostname, port, username, password, database):
		
		self.db = mysql.connector.connect(
			host=hostname,
			port=port,
			user=username,
			password=password,
			database=database
		)

	def _call_proc(self, name, parms : tuple):
		pass

	def get_instance_by_key(self, key : InstanceKey):
		mycur = self.db.cursor()

		if key is None:
			return None

		#resu = mycur.execute("CALL mocha_get_instances(NULL)", multi=True)
		resu = mycur.callproc("mocha_get_instance_by_key", (key.class_id, key.inst_id))
		data = mycur.stored_results()
		inst = None

		# tuple: (id, class_id, inst_id, global_identifier)
		for result in data:
			for row in result:
				inst_key = InstanceKey(row[1], row[2])
				global_id = row[3]
				inst = InstanceReference(row[0], inst_key, global_id)
				return inst

	def get_instances(self, of_class : InstanceReference = None):
		mycur = self.db.cursor()

		#resu = mycur.execute("CALL mocha_get_instances(NULL)", multi=True)
		pclassid = None
		if of_class is not None:
			pclassid = of_class.get_instance_key().get_class_index()

		resu = mycur.callproc("mocha_get_instances", (pclassid,))
		data = mycur.stored_results()

		"""
		sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
		val = [
		('Peter', 'Lowstreet 4'),
		('Amy', 'Apple st 652'),
		('Hannah', 'Mountain 21'),
		('Michael', 'Valley 345'),
		('Sandy', 'Ocean blvd 2'),
		('Betty', 'Green Grass 1'),
		('Richard', 'Sky st 331'),
		('Susan', 'One way 98'),
		('Vicky', 'Yellow Garden 2'),
		('Ben', 'Park Lane 38'),
		('William', 'Central st 954'),
		('Chuck', 'Main Road 989'),
		('Viola', 'Sideway 1633')
		]

		mycursor.executemany(sql, val)
		"""

		insts = []

		# tuple: (id, class_id, inst_id, global_identifier)
		for result in data:
			for row in result:
				inst_key = row[0]
				global_id = row[1]
				insts.append({ "inst_key": InstanceKey.parse(inst_key), "global_identifier": global_id })

		return insts

	def get_attribute_value(self, inst, attr_inst, eff_date = None):
		mycur = self.db.cursor()

		#resu = mycur.execute("CALL mocha_get_instances(NULL)", multi=True)
		resu = mycur.callproc("mocha_get_attribute_value", (inst.get_dbid(), attr_inst.get_dbid(), eff_date))
		data = mycur.stored_results()

		"""
		sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
		val = [
		('Peter', 'Lowstreet 4'),
		('Amy', 'Apple st 652'),
		('Hannah', 'Mountain 21'),
		('Michael', 'Valley 345'),
		('Sandy', 'Ocean blvd 2'),
		('Betty', 'Green Grass 1'),
		('Richard', 'Sky st 331'),
		('Susan', 'One way 98'),
		('Vicky', 'Yellow Garden 2'),
		('Ben', 'Park Lane 38'),
		('William', 'Central st 954'),
		('Chuck', 'Main Road 989'),
		('Viola', 'Sideway 1633')
		]

		mycursor.executemany(sql, val)
		"""

		# tuple: (id, class_id, inst_id, global_identifier)
		for result in data:
			rows = result.fetchall()
			for row in rows:
				return row[0]

		return None

	def get_tenant_by_name(self, tenant_name : str) -> TenantReference:
		mycur = self.db.cursor()

		#resu = mycur.execute("CALL mocha_get_instances(NULL)", multi=True)
		resu = mycur.callproc("mocha_get_tenant_by_name", (tenant_name, None))
		data = mycur.stored_results()
		for result in data:
			rows = result.fetchall()
			for row in rows:
				global_id = row[0]
				return TenantReference(tenant_name, global_id)

		return None
