<?php
	namespace Mocha\Oms;

	use InvalidArgumentException;
	use Mocha\Core\InstanceKey;
	use Mocha\Core\InstanceReference;
	use Mocha\Core\TenantReference;
	use Phast\UUID;

	class MySQLDatabaseOms extends DatabaseOms
	{
		private \PDO $PDO;

		private ?InstanceReference $_currentUser = null;
		public function setCurrentUser(?InstanceReference $userInstance)
		{
			$this->_currentUser = $userInstance;
		}
		public function getCurrentUser() : ?InstanceReference
		{
			return $this->_currentUser;
		}

		/**
		 * Raised when the tenant is changed via setTenant.
		 */
		public function onTenantChanged(?TenantReference $oldTenant, ?TenantReference $newTenant)
		{
			$query = "CALL mocha_select_tenant(:tenant_id);";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array
			(
				"tenant_id" => $newTenant->ID
			));
			if ($result === false)
			{
				return false;
			}
			return true;
		}

		/**
		 * Gets the tenant with the specified name, or NULl if no tenant with the specified name exists on the server.
		 * 
		 * @param string $tenantName the name of the desired tenant
		 * 
		 * @return ?TenantReference
		 */
		public function getTenantByName(string $tenantName) : ?TenantReference
		{
			$query = "SELECT * FROM mocha_tenants WHERE tenant_name = :tenant_name;";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array
			(
				"tenant_name" => $tenantName
			));
			$values = $stmt->fetch();
			if ($values !== false)
			{
				$tenant = new TenantReference($values["id"], $values["tenant_name"], $values["global_identifier"]);
				return $tenant;
			}
			return null;
		}


		public function __construct($hostname, $port, $databasename, $username, $password)
		{
			parent::__construct();
			$this->PDO = new \PDO("mysql:host=" . $hostname . ";port=" . $port . ";dbname=" . $databasename, $username, $password);
		}

		public function getDbId($instRef)
		{
			if ($instRef == null)
				return 0;

			return $instRef->DatabaseId;
		}

		public function createInstanceOf(InstanceReference $classInstance) : ?InstanceReference
		{
			$assigned_id = null;
			$query = "CALL mocha_create_instance_of(mocha_get_instance_by_key(1, :class_index), :global_identifier, :user_inst_id, :effective_date, @assigned_id);";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array
			(
				"class_index" => $classInstance->InstanceKey->InstanceIndex,
				"global_identifier" => null,
				"user_inst_id" => null,
				"effective_date" => null
			));

			$values = $stmt->fetch();
			$stmt->closeCursor();
			
			$results = current($this->PDO->query("SELECT @assigned_id AS assigned_id")->fetchAll());

			if (isset($results["assigned_id"]))
			{
				return $this->getInstanceByDBID($results["assigned_id"]);
			}
			return null;
		}

		public function getUserByUserName(string $username) : ?InstanceReference
		{
			$query = "SELECT mocha_get_user_by_username(:username);";
			$stmt = $this->PDO->prepare($query);

			try
			{
				$result = $stmt->execute(array
				(
					"username" => $username
				));
				$values = $stmt->fetch();
			}
			catch (\PDOException $ex)
			{
				// this may result in a "String data, right truncated" if we pass in
				// text longer than whatever the stored procedure expects; in this case
				// we should just treat it as the user doesn't exist and return null
				//
				// * Note that if any methods actually allow us to create users with names
				// * this long, this will prevent them from logging in!
				return null;
			}

			if ($values[0] == null)
				return null;
			
			$dbid = $values[0];
			$ir = new InstanceReference($this, $dbid, $this->getInstanceKey($dbid), $this->getGlobalIdentifier($dbid));
			return $ir;
		}

		public function getGlobalIdentifier($dbid) : string
		{
			$query = "SELECT global_identifier FROM mocha_instances WHERE id = :id";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array("id" => $dbid));
			$values = $stmt->fetch();
			return $values[0];
		}

		public function getInstanceKey($dbid) : InstanceKey
		{
			$query = "SELECT class_id, inst_id FROM mocha_instances WHERE id = :id";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array("id" => $dbid));
			$values = $stmt->fetch();
			return new InstanceKey($values["class_id"], $values["inst_id"]);
		}

		public function getInstanceByDBID(int $dbid) : ?InstanceReference
		{
			$tenant = $this->getTenant();
			if ($tenant === null)
			{
				trigger_error("tenant cannot be null", \E_USER_ERROR);
				return null;
			}

			$query = "SELECT * FROM mocha_instances WHERE id = :id";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array
			(
				"id" => $dbid
			));
			$values = $stmt->fetch();

			$ir = new InstanceReference($this, $values["id"], new InstanceKey($values["class_id"], $values["inst_id"]), $values["global_identifier"]);
			return $ir;
		}

		public function getInstanceByKey(InstanceKey $instanceKey) : ?InstanceReference
		{
			$tenant = $this->getTenant();
			if ($tenant === null)
			{
				trigger_error("tenant cannot be null", \E_USER_ERROR);
				return null;
			}

			$query = "CALL mocha_get_instance_by_key(:class_id, :inst_id)";
			$stmt = $this->PDO->prepare($query);
			$parms = array
			(
				"class_id" => $instanceKey->ClassIndex,
				"inst_id" => $instanceKey->InstanceIndex
			);
			$result = $stmt->execute($parms);

			if ($result === false)
			{
				trigger_error("unknown database error, query was:", \E_USER_ERROR);
				trigger_error($query, \E_USER_NOTICE);
				return null;
			}

			$values = $stmt->fetchAll();
			if (count($values) == 0)
			{
				trigger_error("no records found, query was:", \E_USER_NOTICE);
				trigger_error($query, \E_USER_NOTICE);
				foreach ($parms as $key => $value)
				{
					trigger_error($key . ": '" . $value . "'", \E_USER_NOTICE);
				}
				return null;
			}

			$dbid = $values[0]["id"];
			$class_id = $values[0]["class_id"];
			$inst_id = $values[0]["inst_id"];
			$global_id = $values[0]["global_identifier"];
			
			$ir = new InstanceReference($this, $dbid, new InstanceKey($class_id, $inst_id), $global_id);
			return $ir;
		}
		
		private $_instanceCache;
		private static bool $useInstanceCache = true; // makes a 0.2ms difference!

		public function getInstanceByGlobalIdentifier(UUID|string $globalIdentifier) : ?InstanceReference
		{
			$tenant = $this->getTenant();
			if ($tenant === null)
			{
				trigger_error("tenant cannot be null", \E_USER_ERROR);
				return null;
			}

			if (is_string($globalIdentifier))
			{
				$globalIdentifier = UUID::parse($globalIdentifier);
			}

			if ($this->_instanceCache == null || !MySQLDatabaseOms::$useInstanceCache)
			{
				$this->_instanceCache = array();
			}
			if (array_key_exists($globalIdentifier->__toString(), $this->_instanceCache))
			{
				return $this->_instanceCache[$globalIdentifier->__toString()];
			}

			$query = "CALL mocha_get_instance_by_global_identifier(mocha_normalize_uuid(:global_identifier))";
			$stmt = $this->PDO->prepare($query);
			$parms = array
			(
				"global_identifier" => $globalIdentifier->__toString()
			);
			$result = $stmt->execute($parms);

			if ($result === false)
			{
				trigger_error("unknown database error, query was:", \E_USER_ERROR);
				trigger_error($query, \E_USER_NOTICE);
				return null;
			}

			$values = $stmt->fetchAll();
			if (count($values) == 0)
			{
				trigger_error("no records found, query was:", \E_USER_NOTICE);
				trigger_error($query, \E_USER_NOTICE);
				foreach ($parms as $key => $value)
				{
					trigger_error($key . ": '" . $value . "'", \E_USER_NOTICE);
				}
				return null;
			}

			$dbid = $values[0]["id"];
			$class_id = $values[0]["class_id"];
			$inst_id = $values[0]["inst_id"];
			$global_id = $values[0]["global_identifier"];
			
			$ir = new InstanceReference($this, $dbid, new InstanceKey($class_id, $inst_id), $global_id);
			$this->_instanceCache[$ir->GlobalIdentifier->__toString()] = $ir;
			return $ir;
		}
		
		protected function getRelatedInstancesInternal(InstanceReference $sourceInstance, InstanceReference $relationshipInstance, \DateTime $effectiveDate = null) : ?array
		{
			$dt = null;
			if ($effectiveDate !== null)
			{
				$dt = $effectiveDate->format("Y-m-d");
			}
			$query = "CALL mocha_get_related_instances(:src_inst_id, :rel_inst_id, :eff_date)";
			$stmt = $this->PDO->prepare($query);
			$parms = array
			(
				"src_inst_id" => $this->getDbId($sourceInstance),
				"rel_inst_id" => $this->getDbId($relationshipInstance),
				"eff_date" => $dt
			);
			$result = $stmt->execute($parms);

			// trigger_error($query, \E_USER_NOTICE);
			// trigger_error("source inst id: " . $sourceInstance->InstanceKey, \E_USER_NOTICE);
			// trigger_error("relationship inst id: " . $relationshipInstance->InstanceKey, \E_USER_NOTICE);
			// foreach ($parms as $key => $value)
			// {
			// 	trigger_error($key . ": '" . $value . "'", \E_USER_NOTICE);
			// }
			
			if ($result === false)
			{
				return null;
			}

			$values = $stmt->fetchAll();
			if (count($values) >= 0)
			{
				$ret = array();
				foreach ($values as $value)
				{
					$retval = $this->getInstanceFromDatabase($value);
					if ($retval !== null)
					{
						$ret[] = $retval;
					}
				}
				return $ret;
			}
			return [];
		}

		protected function getAttributeValueInternal(InstanceReference $sourceInstance, InstanceReference $attributeInstance, $defaultValue = null, $effectiveDate = null) : ?string
		{
			$query = "CALL mocha_get_attribute_value(:src_inst_id, :attr_inst_id, :eff_date)";
			$stmt = $this->PDO->prepare($query);

			$vars = array
			(
				"src_inst_id" => $this->getDbId($sourceInstance),
				"attr_inst_id" => $this->getDbId($attributeInstance),
				"eff_date" => $effectiveDate
			);
			$result = $stmt->execute($vars);
			
			if ($result === false)
			{
				return $defaultValue;
			}

			$values = $stmt->fetchAll();
			if (count($values) > 0)
			{
				$value = $values[0]["att_value"];
				return $value;
			}
			return $defaultValue;
		}
		public function setAttributeValueInternal(InstanceReference $sourceInstance, InstanceReference $attributeInstance, mixed $value, ?\DateTime $effectiveDate = null)
		{
			$currentUser = $this->getCurrentUser();

			$cudbid = null;
			if ($currentUser !== null)
			{
				$cudbid = $currentUser->DatabaseId;
			}
			
			$query = "CALL mocha_set_attribute_value(:src_inst_id, :attr_inst_id, :attr_value, :user_inst_id, :eff_date)";
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute(array
			(
				"src_inst_id" => $this->getDbId($sourceInstance),
				"attr_inst_id" => $this->getDbId($attributeInstance),
				"attr_value" => $value,
				"user_inst_id" => $cudbid,
				"eff_date" => $effectiveDate
			));
		}
		
		protected function assignRelationshipInternal(InstanceReference $sourceInstance, InstanceReference $relationshipInstance, InstanceReference|array $targetInstances, ?\DateTime $effectiveDate = null)
		{
			if (is_array($targetInstances))
			{
				foreach ($targetInstances as $targetInstance)
				{
					$query = "CALL mocha_assign_relationship(:src_inst_id, :rel_inst_id, :dest_inst_id, :user_inst_id, :eff_date)";
					$stmt = $this->PDO->prepare($query);
					$result = $stmt->execute(array
					(
						"src_inst_id" => $this->getDbId($sourceInstance),
						"rel_inst_id" => $this->getDbId($relationshipInstance),
						"dest_inst_id" => $this->getDbId($targetInstance),
						"user_inst_id" => null,
						"eff_date" => null
					));
				}
			}
			else
			{
				$query = "CALL mocha_assign_relationship(:src_inst_id, :rel_inst_id, :dest_inst_id, :user_inst_id, :eff_date)";
				$stmt = $this->PDO->prepare($query);
				$result = $stmt->execute(array
				(
					"src_inst_id" => $this->getDbId($sourceInstance),
					"rel_inst_id" => $this->getDbId($relationshipInstance),
					"dest_inst_id" => $this->getDbId($targetInstances),
					"user_inst_id" => null,
					"eff_date" => null
				));
			}
		}

		public function getInstancesByAttributes($parms)
		{
			// FIXME: NOT IMPLEMENTED
			//usage:
			// getInstanceByAttributes (array ( getInstanceByGlobalIdentifier(NAME_ATTRIBUTE) => "zq-developer" ))
			$query = "SELECT mocha_instances.* FROM mocha_instances, mocha_attributes WHERE mocha_instances.tenant_id = mocha_get_current_tenant() AND mocha_attributes.tenant_id = mocha_get_current_tenant() AND mocha_instances.id = mocha_attributes.src_inst_id";
			if (count($parms) > 0)
			{
				$query .= " AND ";
				$i = 0;
				$queryParms = array();
				foreach ($parms as $key => $value)
				{
					$query .= "(att_value = :att_value$i AND att_inst_id = mocha_get_instance_by_global_identifier(:att_inst_id$i))";
					$queryParms["att_inst_id$i"] = $key;
					$queryParms["att_value$i"] = $value;

					$i++;
					if ($i < count($parms) - 1)
					{
						$query .= " AND ";
					}
				}
			}
			$stmt = $this->PDO->prepare($query);
			$result = $stmt->execute($queryParms);
			$values = $stmt->fetchAll();

			$insts = array();
			if (count($values) > 0)
			{
				foreach ($values as $value)
				{
					$inst = $this->getInstanceFromDatabase($value);
					$insts[] = $inst;
				}
			}
			return $insts;
		}

		public function getInstanceFromDatabase($value)
		{
			if ($value["class_id"] === null)
				return null;
			
			$retval = new InstanceReference($this, $value["id"], new InstanceKey($value["class_id"], $value["inst_id"]), $value["global_identifier"]);
			return $retval;
		}
	}
?>