<?php

Lib::loadClass('Core_AclHelper');
Lib::loadClass('AntAclBase');

class ACL {

	public static $REF_TABLE_VERSION = 2;

	/**
	 * Ids List of Proces Init for given tabel (skip filters)
	 */
	public static function getTableProcesInitIds($idTable) {
		$procesInitList = self::getTableProcesInitList($idTable);
		return array_keys($procesInitList);
	}

	/**
	 * List of Proces Init for given table (skip filters)
	 */
	public static function getTableProcesInitList($idTable) {
		$tableProcesInitList = array();
		$sqlIdProcesListSql = "
			select tpv.`ID_PROCES`
				from `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW` tpv
				where tpv.`ID_TABLE`='{$idTable}'
		";
		$fetchTableProcesInitListSql = "
			-- time ~0.07 -- no goto and return
			select p.`ID`, p.`DESC`
			from `CRM_PROCES` p
			where p.`ID` in(
						select i.`idx_PROCES_INIT_ID`
						from `CRM_PROCES_idx` i
						where i.`ID_PROCES` in({$sqlIdProcesListSql})
				)
				and p.`TYPE`='PROCES_INIT'
			order by p.`SORT_PRIO`
		";
		/*
			SELECT p.`ID` , p.`DESC`
			FROM `CRM_PROCES` p
			WHERE p.`ID`
			IN (
					SELECT i.`idx_PROCES_INIT_ID`
					FROM `CRM_PROCES_idx` i
					WHERE i.`ID_PROCES`
					IN (
							SELECT tpv.`ID_PROCES`
							FROM `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW` tpv
							WHERE tpv.`ID_TABLE` = '13051'
					)
			)
			AND p.`TYPE` = 'PROCES_INIT'
			order by p.`SORT_PRIO`
		*/
		$fetchTableProcesInitListSql = "
			-- time ~0.15s
			select p.`ID`, p.`DESC`
			from `CRM_PROCES` p
			where p.`ID` in(
						select i.`idx_PROCES_INIT_ID`
						from `CRM_PROCES_idx` i
						where i.`ID_PROCES` in({$sqlIdProcesListSql})
					union
						select ig.`idx_PROCES_INIT_ID`
						from `CRM_PROCES_idx` i
							join `CRM_PROCES_idx` ig on(ig.`ID_PROCES`=i.`idx_PROCES_WITH_GROUPS_ID`)
						where i.`ID_PROCES` in({$sqlIdProcesListSql})
				)
				and p.`TYPE`='PROCES_INIT'
			order by p.`SORT_PRIO`
		";
		$fetchTableProcesInitListSql = "
			-- time ~0.14
			select p.`ID`, p.`DESC`
			from `CRM_PROCES` p
			where p.`ID` in(
					select i.`idx_PROCES_INIT_ID`
					from `CRM_PROCES_idx` i
					where i.`ID_PROCES` in({$sqlIdProcesListSql})
						or i.`ID_PROCES` in(
							select ig.`idx_PROCES_WITH_GROUPS_ID`
							from `CRM_PROCES_idx` ig
							where ig.`ID_PROCES` in({$sqlIdProcesListSql})
						)
				)
				and p.`TYPE`='PROCES_INIT'
			order by p.`SORT_PRIO`
		";
		return array_map(function ($row) {
			return $row['DESC'];
		}, DB::getPDO()->fetchAllByKey($fetchTableProcesInitListSql, 'ID'));
	}

	public static function getProcesInitMapTreeOnlyIds($ids) {
		$mapTree = array();
		$map = self::getProcesInitMapOnlyIds($ids);
		foreach ($map as $row) {
			if ('PROCES_INIT' == $row['TYPE']) {
				$mapTree[ $row['ID_PROCES'] ] = array();
			}
		}
		foreach ($map as $row) {
			if ('GOTO_AND_RETURN' == $row['TYPE']) {
				$mapTree[ $row['idx_MAIN_PROCES_INIT_ID'] ][ $row['ID_PROCES'] ] = array();
			}
		}
		foreach ($map as $row) {
			if ('GOTO_AND_RETURN_LVL2' == $row['TYPE']) {
				$mapTree[ $row['idx_MAIN_PROCES_INIT_ID'] ][ $row['idx_GOTO_LVL2_INIT_ID'] ][ $row['ID_PROCES'] ] = true;
			}
		}
		return $mapTree;
	}

	public static function getProcesInitMapOnlyIds($ids) {
		$map = array();
		$sqlIds = V::filter($ids, array('V', 'filterPositiveInteger'));
		$sqlIds = implode(',', $sqlIds);
		if (empty($sqlIds)) return $map;
		$sql = "
			select i.`ID_PROCES`
				, i.`PARENT_ID`
				, i.`TYPE`
				, i.`idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID`
				, i.`idx_PROCES_WITH_GROUPS_ID`
				, IF(i.`TYPE`='GOTO_AND_RETURN_LVL2'
					, (select ig.`idx_PROCES_INIT_ID`
						from `CRM_PROCES_idx` ig
						where ig.`ID_PROCES`=i.`PARENT_ID`
						limit 1)
					, 0
				) as idx_GOTO_LVL2_INIT_ID
			from `CRM_PROCES_idx` i
			where i.`ID_PROCES` in({$sqlIds})
				and i.`idx_MAIN_PROCES_INIT_ID` in({$sqlIds})
		";
		return DB::getPDO()->fetchAll($sql);
	}

	public static function canGroupViewProces($idGroup, $idProcesInit) {
		$isAllowed = false;
		$idProcesInit = (int)$idProcesInit;
		if (!$idProcesInit) return false;
		$checkProcesAccessSql = "
			select count(*) as cnt
			from `CRM_PROCES_idx_GROUP_to_INIT_VIEW` giv
			where giv.`ID_GROUP` = '{$idGroup}'
				and giv.`ID_PROCES_INIT` = '{$idProcesInit}'
		";
		return ( DB::getPDO()->fetchValue($checkProcesAccessSql) > 0 );
	}

	public static function getStorageByNamespace($namespace, $forceTblAclInit = false) {
		Lib::loadClass('Core_AclHelper');
		Lib::loadClass('SchemaFactory');
		$ns = Core_AclHelper::parseNamespaceUrl($namespace);
		DBG::log($ns, 'array', "parseNamespaceUrl({$namespace})");
		if ('default_db' == $ns['prefix']) {
			$acl = User::getAcl()->getObjectAcl($ns['prefix'], $ns['name']);
		} else if ('objects' == $ns['prefix']) {
			$acl = SchemaFactory::loadDefaultObject($ns['name']);
		} else if ('default_objects' == $ns['prefix']) {
			$acl = SchemaFactory::loadDefaultObject($ns['name']);
		} else if ('default_db__x3A__' == substr($ns['prefix'], 0, 17)) {
			$rootTableName = strtolower(substr($ns['prefix'], 17));
			$acl = SchemaFactory::loadTableObject($rootTableName, $ns['name']);
		} else {
			throw new HttpException("Not Implemented", 501);
		}
		$acl->init($forceTblAclInit);
		return $acl;
	}

	public static function getBaseNamespace($namespace) {
		// map SystemObjects__x3A__{parent}/{name} to default_objects/{name}
		if ('SystemObjects/' === substr($namespace, 0, strlen('SystemObjects/'))) {
			$exNs = explode('/', $namespace);
			if (3 === count($exNs)) {
				return "default_objects/{$exNs[2]}";
			}
		}
		return $namespace;
	}

	public static function getAclByNamespace($namespace, $forceTblAclInit = false) {
		$namespace = ACL::getBaseNamespace($namespace);
		return Core_AclHelper::getAclByNamespace($namespace, $forceTblAclInit);
	}

	public static function getAclByTypeName($typeName, $forceTblAclInit = false) {
		return Core_AclHelper::getAclByNamespace(str_replace(':', '/', $typeName), $forceTblAclInit);
	}

	public static function getNamespaceFromId($idZasob) {
		$sqlIdZasob = DB::getPDO()->quote($idZasob, PDO::PARAM_INT);
		$zasob = DB::getPDO()->fetchFirst("
			select z.ID, z.DESC, z.PARENT_ID
			from CRM_LISTA_ZASOBOW z
			where z.ID = {$sqlIdZasob}
				and z.`TYPE` = 'TABELA'
				and z.A_STATUS != 'DELETED'
		");
		if (!$zasob) throw new Exception("Object not exists '{$idZasob}'");
		if ($zasob['PARENT_ID'] != DB::getPDO()->getZasobId()) {
			throw new Exception("TODO: getNamespaceFromId for remote database");
		}
		return ('default_db/' === substr($zasob['DESC'], 0, strlen('default_db/')))
			? $zasob['DESC']
			: "default_db/{$zasob['DESC']}"
		;
	}

	public static function parseNamespaceUrl($namespace) {// returns assoc array: [ 'name', 'url', 'prefix', 'sourceName' ]
		return Core_AclHelper::parseNamespaceUrl($namespace);
	}

	public static function getRefTable($rootObjectNamespace, $childName) { // CRM_REF_CONFIG
		static $cacheRefTables = array();

		DBG::log("DBG get ref table ({$rootObjectNamespace}, {$childName}) ...");
		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);
		$cacheKey = "{$rootObjectNamespace}/{$childName}";
		if (array_key_exists($cacheKey, $cacheRefTables)) return $cacheRefTables[$cacheKey];

		$rootAcl = self::getAclByNamespace($rootObjectNamespace);
		$childXsdType = $rootAcl->getXsdFieldType($childName);
		list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
		DBG::log(['$childXsdType' => $childXsdType, '$typePrefix' => $typePrefix, '$childNamespace' => $childNamespace], 'array', "DBG get ref table ...");
		switch ($typePrefix) {
			case 'ref_uri': $childAcl = self::getAclByNamespace($childNamespace); break;
			case 'ref': $childAcl = self::getAclByTypeName($childNamespace); break;
			default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
		}

		$refInfo = self::getRefConfig($rootObjectNamespace, $childName, $childNamespace);

		if ('view' === $refInfo['SOURCE']) {
			$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}_VIEW"; // view created by ACL::generateRefSelectSqlByFlatRelationCache
		} else if ('backRef' === $refInfo['SOURCE']) {
			$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}_VIEW"; // view created by ACL::generateRefSelectSqlByFlatRelationCache
		} else if ('table' === $refInfo['SOURCE']) {
			$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}";
			if ('WAITING' == $refInfo['A_STATUS']) {
				DB::getPDO()->execSql("
					CREATE TABLE IF NOT EXISTS `{$refTableName}` (
						`PRIMARY_KEY` int(11) NOT NULL
						, `REMOTE_PRIMARY_KEY` int(11) NOT NULL
						, `REMOTE_TYPENAME` varchar(255) NOT NULL DEFAULT ''
						, `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING'
						, `TRANSACTION_ID` int(11) NOT NULL
						, `A_LAST_ACTION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
						, KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
						, KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`)
						, KEY `TRANSACTION_ID` (`TRANSACTION_ID`)
					) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='{$rootObjectNamespace} #REF $childName ({$childNamespace})';
				");
				$refInfo['A_STATUS'] = "NORMAL";
				$refInfo['VERSION'] = self::$REF_TABLE_VERSION;
				$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refInfo['ID'], [
					'A_STATUS' => $refInfo['A_STATUS'],
					'VERSION' => $refInfo['VERSION']
				]);
			}
		} else {
			throw new Exception("Not Implemented ref SOURCE = '{$refInfo['SOURCE']}'");
		}

		if ($refInfo['VERSION'] < self::$REF_TABLE_VERSION) {
			if (1 == $refInfo['VERSION']) $refInfo = self::upgradeRefConfigFrom1to2($refInfo);
		}
		if ($refInfo['VERSION'] < self::$REF_TABLE_VERSION) throw new Exception("TODO: ref table {$refInfo['ID']} require upgrade - field '{$childName}' in object '{$rootObjectNamespace}'");

		$cacheRefTables[$cacheKey] = $refTableName;
		return $refTableName;
	}
	public static function getRefSource($rootObjectNamespace, $childName) { // CRM_REF_CONFIG
		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);
		$refInfo = self::getRefConfig($rootObjectNamespace, $childName);
		return V::get('SOURCE', 'table', $refInfo);
	}
	public static function decodeAppInfoJson($appInfoJsonString) {
		$appInfo = @json_decode($appInfoJsonString, $assoc = true);
		if (null == $appInfo && 0 !== json_last_error()) throw new Exception("Parsing Json failed: " . json_last_error());
		return $appInfo;
	}
	public static function generateRefSelectSqlByBackRef($rootObjectNamespace, $childName) { // TODO: mv to generateRefSelectSqlByFlatRelationCache
		// TODO: generate view which is select from {replaced(pk, remote pk) on ref table from backRef}
		// {
		// 	DBG::nicePrint($refInfo, "\$refInfo");
		// 	DBG::nicePrint($rootObjectNamespace, "\$rootObjectNamespace");
		// 	DBG::nicePrint($childName, "\$childName");
		// 	DBG::nicePrint($childNamespace, "\$childNamespace");
		// 	$replacedObjNs = Api_WfsNs::namespaceFromTypeName($childName);
		// 	$replacedChildName = Api_WfsNs::typeName($rootObjectNamespace);
		// 	DBG::nicePrint($replacedObjNs, "\$replacedObjNs");
		// 	DBG::nicePrint($replacedChildName, "\$replacedChildName");
		// 	return ACL::getRefTable($replacedObjNs, $replacedChildName, 1);
		// 	throw new Exception("Not Implemented ref SOURCE = '{$refInfo['SOURCE']}'");
		// }
		$childNs = Api_WfsNs::namespaceFromTypeName($childName);
		$rootTypeName = Api_WfsNs::typeName($rootObjectNamespace);
		$backRefTable = ACL::getRefTable($childNs, $rootTypeName);
		DBG::nicePrint($backRefTable, "ACL::getRefTable({$childNs}, {$rootTypeName})");

		// TODO: check if ref_config is not backRef to avoid loop // $refInfo = self::getRefConfig($fieldNs, $item['typeName'], $item['typeName']);

		$lastActionDateField = "NULL"; // , IF(l.A_RECORD_UPDATE_DATE > r.A_RECORD_UPDATE_DATE, l.A_RECORD_UPDATE_DATE, r.A_RECORD_UPDATE_DATE) as A_LAST_ACTION_DATE
		$sql = "
			select backRef.REMOTE_PRIMARY_KEY as PRIMARY_KEY
					, backRef.PRIMARY_KEY as REMOTE_PRIMARY_KEY
					, backRef.REMOTE_TYPENAME as REMOTE_TYPENAME
					, backRef.A_STATUS as A_STATUS
					, 0 as TRANSACTION_ID
					, {$lastActionDateField} as A_LAST_ACTION_DATE
			from `{$backRefTable}` backRef
		";
		DBG::log($sql, 'sql', "generateRefSelectSqlByBackRef");
		return $sql;
	}
	public static function generateRefSelectSqlByFlatRelationCache($rootObjectNamespace, $childName) { // CRM_REF_CONFIG
		$appInfo = DB::getPDO()->fetchValue("
			select f.appInfo
			from `CRM_#CACHE_ACL_OBJECT_FIELD` f
			where f.objectNamespace = '{$rootObjectNamespace}'
				and f.fieldNamespace = '{$childName}'
		");
		if (!$appInfo) throw new Exception("Missing app:info for field '{$rootObjectNamespace}/{$childName}'");
		$appInfo = ACL::decodeAppInfoJson($appInfo);
		if (empty($appInfo)) throw new Exception("Empty app:info for field '{$rootObjectNamespace}/{$childName}'");

		DBG::log(['$appInfo'=>$appInfo, '$rootObjectNamespace'=>$rootObjectNamespace, '$childName'=>$childName], 'array', "\$appInfo");
		$rootAcl = self::getAclByNamespace($rootObjectNamespace);
		$childXsdType = $rootAcl->getXsdFieldType($childName);
		list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
		switch ($typePrefix) {
			case 'ref_uri': $childAcl = self::getAclByNamespace($childNamespace); break;
			case 'ref': $childAcl = self::getAclByTypeName($childNamespace); break;
			default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
		}

		$lastActionDateField = "NULL"; // , IF(l.A_RECORD_UPDATE_DATE > r.A_RECORD_UPDATE_DATE, l.A_RECORD_UPDATE_DATE, r.A_RECORD_UPDATE_DATE) as A_LAST_ACTION_DATE
		$rootPrimaryKeyField = $rootAcl->getPrimaryKeyField();
		$childPrimaryKeyField = $childAcl->getPrimaryKeyField();
		$rootTableName = $rootAcl->getRootTableName();
		$childTableName = $childAcl->getRootTableName();
		//   '$appInfo' => [
		//     'flat_relation_cache' => [
		//       'source' => [
		//         '@name' => 'ID',
		//         '@xpath' => 'default_db__x3A__CRM_WSKAZNIK:CRM_WSKAZNIK/ID_PROCES',
		//       ),
		//     ),
		//   ),
		//   '$rootObjectNamespace' => 'default_db/CRM_PROCES/PROCES',
		//   '$childName' => 'default_db__x3A__CRM_WSKAZNIK:CRM_WSKAZNIK',

		//   '$appInfo' => [
		//   'flat_relation_cache' => [
		//     'source' => [
		//       '@name' => 'ID',
		//       '@xpath' => 'default_db__x3A__CRM_PROCES:PROCES/PARENT_ID',
		//     ),
		//   ),
		// ),
		// '$rootObjectNamespace' => 'default_db/CRM_PROCES/PROCES',
		// '$childName' => 'default_db__x3A__CRM_PROCES:PROCES',
		$appInfoRootFieldName = null;
		$appInfoChildFieldName = null;
		{
			if (empty($appInfo['flat_relation_cache']['source']['@name'])) throw new Exception("Missing flat_relation_cache/source/@name");
			if (empty($appInfo['flat_relation_cache']['source']['@xpath'])) throw new Exception("Missing flat_relation_cache/source/@xpath");
			$appInfoName = $appInfo['flat_relation_cache']['source']['@name'];
			$appInfoXpath = $appInfo['flat_relation_cache']['source']['@xpath'];
			// $rootNs = $rootAcl->getNamespace()
			if ("{$childName}/" === substr($appInfoXpath, 0, strlen("{$childName}/"))) {
				$appInfoRootFieldName = substr($appInfoXpath, strlen("{$childName}/"));
				$appInfoChildFieldName = $appInfoName;
			} else {
				throw new Exception("TODO parse flat_relation_cache");
			}
		}
		if (!$appInfoRootFieldName || !$appInfoChildFieldName) throw new Exception("Error Processing flat_relation_cache");
		$sqlWhereFromRestrictions = [];
		DBG::log(['root'=>$rootAcl->getFields(), 'child'=>$childAcl->getFields()], 'array', "rootAcl and childAcl fields - xsdRestrictions");
		if ($rootAcl instanceof AntAclBase && $childAcl instanceof AntAclBase) {
			$rootLocalFieldsWithRestrictions = array_filter($rootAcl->getFields(), function ($field) {
				if (!$field['isLocal']) return false;
				if (empty($field['xsdRestrictions'])) return false;
				if ('[]' == $field['xsdRestrictions']) return false;
				return true;
			});
			$childLocalFieldsWithRestrictions = array_filter($childAcl->getFields(), function ($field) {
				if (!$field['isLocal']) return false;
				if (empty($field['xsdRestrictions'])) return false;
				if ('[]' == $field['xsdRestrictions']) return false;
				return true;
			});
			DBG::log(['root'=>$rootLocalFieldsWithRestrictions, 'child'=>$childLocalFieldsWithRestrictions], 'array', "root and child fields with xsdRestrictions");
			if (!empty($rootLocalFieldsWithRestrictions)) {
				$sqlTablePrefix = 'root';
				$sqlWhereFromRestrictions = array_reduce(
					array_map(function ($field) use ($sqlTablePrefix) {
						$sqlRestrictions = [];
						// 'xsdRestrictions' => '{"enumeration":{"PROCES":"PROCES"}}',
						$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
						if (!empty($restrictions)) {
							if (!empty($restrictions['enumeration'])) {
								$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($restrictions['enumeration']))) . ")";
							}
						}
						return $sqlRestrictions;
					}, $rootLocalFieldsWithRestrictions),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					$sqlWhereFromRestrictions
				);
			}
			if (!empty($childLocalFieldsWithRestrictions)) {
				$sqlTablePrefix = 'child';
				$sqlWhereFromRestrictions = array_reduce(
					array_map(function ($field) use ($sqlTablePrefix) {
						$sqlRestrictions = [];
						// 'xsdRestrictions' => '{"enumeration":{"PROCES":"PROCES"}}',
						$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
						if (!empty($restrictions)) {
							if (!empty($restrictions['enumeration'])) {
								$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($restrictions['enumeration']))) . ")";
							}
						}
						return $sqlRestrictions;
					}, $childLocalFieldsWithRestrictions),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					$sqlWhereFromRestrictions
				);
			}
		}
		$sqlWhereFromRestrictions = (!empty($sqlWhereFromRestrictions)) ? implode(" and ", $sqlWhereFromRestrictions) : "1=1";
		$sqlChildFieldName = $childAcl->getSqlFieldName($appInfoRootFieldName);
		$sql = "
			select root.{$rootPrimaryKeyField} as PRIMARY_KEY
					, child.{$childPrimaryKeyField} as REMOTE_PRIMARY_KEY
					, '' as REMOTE_TYPENAME
					, 'WAITING' as A_STATUS
					, 0 as TRANSACTION_ID
					, {$lastActionDateField} as A_LAST_ACTION_DATE
			from `{$rootTableName}` root
				join `{$childTableName}` child on(child.{$sqlChildFieldName} = root.{$appInfoChildFieldName})
			where {$sqlWhereFromRestrictions}
		";
		DBG::log($sql, 'sql', "generateRefSelectSqlByFlatRelationCache");
		return $sql;
	}
	public static function setRefSource($rootObjectNamespace, $childName, $source, $viewSelectSql = null) { // CRM_REF_CONFIG
		if (!in_array($source, ['view', 'table', 'backRef'])) throw new Exception("Wrong param source - expected 'table', 'view' or 'backRef'");
		if ('view' === $source && !$viewSelectSql) throw new Exception("Missing create view sql");
		$refInfo = self::getRefConfig($rootObjectNamespace, $childName);
		if (true) { // if ($source != $refInfo['SOURCE']) {
			if ('view' === $source) {
				$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}_VIEW";
				DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$refTableName}` AS {$viewSelectSql} ");
			}
			if ('backRef' === $source) {
				$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}_VIEW";
				DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$refTableName}` AS {$viewSelectSql} ");
			}
			$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refInfo['ID'], [
				'SOURCE' => $source,
			]);
		}
	}
	public static function getRefConfig($rootObjectNamespace, $childName, $childNamespace = null) { // CRM_REF_CONFIG
		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);
		if (!$childNamespace) {
			$rootAcl = self::getAclByNamespace($rootObjectNamespace);
			$childXsdType = $rootAcl->getXsdFieldType($childName);
			list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
			DBG::log(['$childXsdType' => $childXsdType, '$typePrefix' => $typePrefix, '$childNamespace' => $childNamespace], 'array', "DBG get ref table ...");
			switch ($typePrefix) {
				case 'ref_uri': $childAcl = self::getAclByNamespace($childNamespace); break;
				case 'ref': $childAcl = self::getAclByTypeName($childNamespace); break;
				default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
			}
		}
		$refInfo = [];// define $refInfo = [ ID, A_STATUS, VERSION ]
		try {// check that ref config table exists
			$sqlRootTableNs = DB::getPDO()->quote($rootObjectNamespace, PDO::PARAM_STR);
			$sqlChildName = DB::getPDO()->quote($childName, PDO::PARAM_STR);
			$sqlChildNamespace = DB::getPDO()->quote($childNamespace, PDO::PARAM_STR);
			$refInfo = DB::getPDO()->fetchFirst("
				select c.ID, c.A_STATUS, c.VERSION, c.SOURCE
				from `CRM_REF_CONFIG` c
				where c.ROOT_OBJECT_NS = {$sqlRootTableNs}
					and c.CHILD_NAME = {$sqlChildName}
					and c.CHILD_NS = {$sqlChildNamespace}
			");
		} catch (Exception $e) {
			DB::getPDO()->execSql("
				CREATE TABLE IF NOT EXISTS `CRM_REF_CONFIG` (
					`ID` INT NOT NULL AUTO_INCREMENT
					, `ROOT_OBJECT_NS` VARCHAR(255) NOT NULL
					, `CHILD_NAME` VARCHAR(255) NOT NULL
					, `CHILD_NS` VARCHAR(255) NOT NULL
					, `A_STATUS` enum('WAITING', 'NORMAL', 'DELETED') NOT NULL DEFAULT 'WAITING'
					, `VERSION` int(11) NOT NULL DEFAULT 0
					, `A_LAST_ACTION_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
					, PRIMARY KEY (`ID`)
				) ENGINE = MyISAM DEFAULT CHARSET=latin2;
			");
			try {
				DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` ADD `SOURCE` enum('table', 'view') not null default 'table' ");
			} catch (Exception $e) {
				DBG::log($e);
			}
		}
		try {
			DB::getPDO()->execSql(" ALTER TABLE `CRM_REF_CONFIG` CHANGE `SOURCE` `SOURCE` enum('table', 'view', 'backRef') not null default 'table' ");
		} catch (Exception $e) {
			DBG::log($e);
		}
		if (empty($refInfo)) {
			$refInfo = [ 'ID' => 0, 'A_STATUS' => 'WAITING', 'VERSION' => 0, 'SOURCE' => 'table' ];
			$refInfo['ID'] = DB::getPDO()->insert("CRM_REF_CONFIG", [
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace
			]);
		}

		// { // TODO: fix source if ref for `SystemObjects__x3A__*` and defined flat_relation_cache - move to AclReinstall?
		// 	if ('SystemObjects__x3A__' === substr($childNamespace, 0, strlen('SystemObjects__x3A__'))) {
		// 		if ('table' === $refInfo['SOURCE']) {
		// 			$toUpdate = [
		// 				'SOURCE' => 'view',
		// 				'A_STATUS' => 'WAITING',
		// 			];
		// 			DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refInfo['ID'], $toUpdate);
		// 			$refInfo = array_merge($refInfo, $toUpdate);
		// 		}
		// 	}
		// }

		if (!$refInfo['ID']) throw new Exception("Ref table not found in ref config table for field '{$childName}' in object '{$rootObjectNamespace}'");
		return $refInfo;
	}
	public static function upgradeRefConfigFrom1to2($refInfo) {
		if (1 == $refInfo['VERSION']) {
			if ('table' === $refInfo['SOURCE'] && 'NORMAL' == $refInfo['A_STATUS']) {
				$refTableName = "CRM__#REF_TABLE__{$refInfo['ID']}";
				try {
					DB::getPDO()->execSql(" CREATE INDEX `TRANSACTION_ID` ON `{$refTableName}` (`TRANSACTION_ID`) ");
				} catch (Exception $e) {
					DBG::log($e);
				}
			}
			$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refInfo['ID'], [
				'VERSION' => 2
			]);
		}
		return array_merge($refInfo, [
			'VERSION' => 2
		]);
	}
	public static function getBackRefList($namespace) {
		if (!$namespace) throw new Exception("Missing namespace");
		$nsParts = explode('/', $namespace);
		$typeName = array_pop($nsParts);
		$typeName = implode("__x3A__", $nsParts) . ":{$typeName}";
		return DB::getPDO()->fetchAll("
			select c.ROOT_OBJECT_NS as namespace
				, i.id as idInstance
			from CRM_REF_CONFIG c
				join CRM_INSTANCE_CONFIG i on ( i.namespace = c.ROOT_OBJECT_NS )
			where ( c.CHILD_NAME = :type_name or c.CHILD_NAME = :namespace )
				and c.A_STATUS = 'NORMAL'
		", [
			':type_name' => $typeName,
			':namespace' => $namespace,
		]);
	}
	public static function getChildRefFullList($namespace) {
		$namespace = ACL::getBaseNamespace($namespace);
		if (!$namespace) throw new Exception("Missing namespace");
		return DB::getPDO()->fetchAll("
			select c.CHILD_NAME as namespace
				, c.A_STATUS
				, c.ID
				, c.SOURCE
			from CRM_REF_CONFIG c
			where c.ROOT_OBJECT_NS = :namespace
			--	and c.A_STATUS = 'NORMAL'
		", [
			':namespace' => $namespace,
		]);
	}
	public static function updateChildRefSource($id, $source) {
		if (!$id) throw new Exception("Missing id");
		if (!in_array($source, ['view', 'table', 'backRef'])) throw new Exception("Wrong source");
		return DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $id, [
			'SOURCE' => $source
		]);
	}

	public static function fetchRefs($namespace, $childNamespace, $primaryKey, $params = []) { // TODO: $params: limit, total
		if (!$namespace) throw new Exception("Missing namespace");
		if (!$childNamespace) throw new Exception("Missing child namespace");
		if (!$primaryKey) throw new Exception("Missing primary key");
		throw new Exception("TODO: fetch refs from '{$namespace}' where primaryKey = '{$primaryKey}'");
	}

	public static function fetchBackRefs($namespace, $primaryKey, $parentNamespace, $params = []) { // TODO: $params: limit, total
		if (!$namespace) throw new Exception("Missing namespace");
		if (!$parentNamespace) throw new Exception("Missing parent namespace");
		if (!$primaryKey) throw new Exception("Missing primary key");

		$typeName = Api_WfsNs::typeName($namespace);
		$refTable = ACL::getRefTable($parentNamespace, $typeName);
		if (V::get('total', false, $params)) {
			return DB::getPDO()->fetchValue(" select count(*) as cnt from `{$refTable}` where REMOTE_PRIMARY_KEY = :primary_key and A_STATUS not in ('DELETED') ", [ ':primary_key' => $primaryKey ]);
		}
		throw new Exception("TODO: fetch back refs from '{$namespace}' where primaryKey({$primaryKey}) by refTable({$refTable})");
	}

	public static function generateIsInstanceFunctionBody($namespace, $item = null) {
		if (!$item) $item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace, [ 'propertyName' => '*,field' ]);
		if (!in_array( $item['_type'], [ 'AntAcl' ] )) return null;
		$sqlFunBody = " RETURN 1; ";
		$localFieldsWithRestrictions = array_filter($item['field'], function ($field) {
			if (!$field['isLocal']) return false;
			if (empty($field['xsdRestrictions'])) return false;
			if ('[]' == $field['xsdRestrictions']) return false;
			return true;
		});
		// TODO: get fields with minOccurs > 1 (may require select by ref)
		$sqlTablePrefix = 'root';
		$sqlWhereFromRestrictions = (!empty($localFieldsWithRestrictions))
		?	array_reduce(
				array_map(function ($field) use ($sqlTablePrefix) {
					$sqlRestrictions = [];
					// 'xsdRestrictions' => '{"enumeration":{"PROCES":"PROCES"}}',
					$restrictions = @json_decode($field['xsdRestrictions'], $assoc = true);
					if (!empty($restrictions)) {
						if (!empty($restrictions['enumeration'])) {
							$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` in (" . implode(",", array_map([DB::getPDO(), 'quote'], array_keys($restrictions['enumeration']))) . ")";
						}
					}
					return $sqlRestrictions;
				}, $localFieldsWithRestrictions),
				function ($ret, $cur) {
					return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
				},
				[]
			)
		:	'';
		DBG::nicePrint($localFieldsWithRestrictions, "\$localFieldsWithRestrictions");
		DBG::nicePrint($sqlWhereFromRestrictions, "\$sqlWhereFromRestrictions");
		$sqlWhereFromRestrictions = (!empty($sqlWhereFromRestrictions)) ? implode(" and ", $sqlWhereFromRestrictions) : "1=1";
		$pkField = 'ID'; // TODO: primaryKeyField into SystemObject structure
		$rootTableName = $item['_rootTableName'];
		$sqlFunBody = (!empty($sqlWhereFromRestrictions))
		?	" RETURN IF(
				(select count(1) as cnt from `{$rootTableName}` root where root.`{$pkField}` = pk and {$sqlWhereFromRestrictions}) > 0
				, 1, 0)
			"
		:	" RETURN 1; ";
		return $sqlFunBody;
	}

	public static function getInstanceId($namespace) {
		$conf = self::getInstanceConfig($namespace);
		return $conf['id'];
	}
	public static function getInstanceConfig($namespace) { // @returns { id, namespace, rootNamespace, idInstanceBase, _createdAt }
		try {
			$conf = self::fetchInstanceConfig($namespace);
		} catch (Exception $e) {
			DB::getPDO()->execSql("
				create table if not exists `CRM_INSTANCE_CONFIG` (
					`id` int(11) not null AUTO_INCREMENT,
					`namespace` varchar(255) NOT NULL DEFAULT '',
					`rootNamespace` varchar(255) NOT NULL DEFAULT '',
					`idInstanceBase` int(11) NOT NULL DEFAULT 0,
					`_createdAt` datetime NOT NULL,
					UNIQUE KEY `namespace` (`namespace`),
					KEY `rootNamespace` (`rootNamespace`),
					PRIMARY KEY (`id`)
				) ENGINE=MyISAM  DEFAULT CHARSET=latin2
			");
			// TODO:?: `_tableInstalled` tinyint(1) not null default 0,
			$conf = self::fetchInstanceConfig($namespace);
		}
		if (!$conf) {
			$id = DB::getPDO()->insert("CRM_INSTANCE_CONFIG", [
				'namespace' => $namespace,
				'rootNamespace' => self::getRootNamespace($namespace),
				'_createdAt' => 'NOW()',
			]);
			$conf = self::fetchInstanceConfig($namespace);
		}
		if (!$conf) throw new Exception("Instance not found in config table '{$namespace}'");
		return $conf;
	}
	public static function fetchInstanceConfig($namespace) {
		return DB::getPDO()->fetchFirst("
			select c.*
			from `CRM_INSTANCE_CONFIG` c
			where c.namespace = '{$namespace}'
		");
	}
	public static function getRootNamespace($namespace) { // TODO: works only for relative urls! - mv to Acl->getRootNamespace
		Lib::loadClass('SchemaFactory');
		try {
			$objectItem = SchemaFactory::loadDefaultObject('SystemObject')->getItem($namespace);
		} catch (Exception $e) {
			throw new Exception("Object not installed '{$namespace}'");
		}
		if (!$objectItem['isStructInstalled']) throw new Exception("Object structure not installed '{$namespace}'");

		if ($objectItem['idDatabase'] != DB::getPDO()->getZasobId()) {
			if ('StorageAcl' === $objectItem['_type']) {
				DBG::log("getRootNamespace...");
				return $objectItem['namespace'];
			}
			else {
				throw new Exception("Only default_db supported"); // TODO: support more Sources
			}
		}

		return "default_db/{$objectItem['_rootTableName']}";
	}
	public static function getNamespaceSiblings($namespace) {
		return array_map(function ($row) {
			return $row['namespace'];
		}, DB::getPDO()->fetchAll("
			select s.namespace
			from CRM_INSTANCE_CONFIG c
				join CRM_INSTANCE_CONFIG s on ( s.rootNamespace = c.rootNamespace and s.namespace != c.rootNamespace )
			where c.namespace = :namespace
		", [
			'namespace' => $namespace
		]));
	}
	public static function getFeatureNamespaces($namespace, $pk) {
		$instanceTable = self::getInstanceTable($namespace);
		return array_map(function ($row) {
			return $row['namespace'];
		}, DB::getPDO()->fetchAll("
			select c.namespace
			from `{$instanceTable}` i
				join `CRM_INSTANCE_CONFIG` c on ( c.id = i.idInstance )
			where i.pk = :pk
		", [
			'pk' => $pk,
		]));
	}
	public static function getInstanceTable($namespace) { // @returns tableName with struct { pk, idInstance, _createdAt }
		$conf = self::getInstanceConfig($namespace);
		if (!empty($conf['idInstanceBase'])) return "CRM__#INSTANCE_TABLE__{$conf['idInstanceBase']}";

		$rootNs = $conf['rootNamespace'];
		$rootConf = self::getInstanceConfig($rootNs);
		$instanceTableName = "CRM__#INSTANCE_TABLE__{$rootConf['id']}";
		if (!empty($rootConf['idInstance'])) {
			$affected = DB::getPDO()->update("CRM_INSTANCE_CONFIG", 'rootNamespace', $rootNs, [
				'idInstanceBase' => $rootConf['id']
			]);
			return $instanceTableName;
		}

		// TODO: fetch primaryKeyType - TODO: store primaryKey and primaryKeyType in SystemObject item
		$pkType = 'int';
		DB::getPDO()->exec("
			CREATE TABLE IF NOT EXISTS `{$instanceTableName}` (
				`pk` int(11) NOT NULL COMMENT 'primary key'
				, `idInstance` int(11) NOT NULL
				, `_createdAt` datetime NOT NULL
				, KEY `pk` (`pk`)
				, KEY `idInstance` (`idInstance`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='{$rootNs} #INSTANCE';
		");
		$affected = DB::getPDO()->update("CRM_INSTANCE_CONFIG", 'rootNamespace', $rootNs, [
			'idInstanceBase' => $rootConf['id']
		]);
		return $instanceTableName;
	}

	// @params $from - ( ACL | tableName | namespace | etc... - only ACL)
	public static function query($from, $prefix = 't') {
		Lib::loadClass('AclQueryBuilder');
		$query = new AclQueryBuilder();
		$query->from($from, $prefix);
		return $query;
	}

	/**
	 * @param mixed $object - Core_AclBase or string - namespace
	 * @return Core_AclFields
	 */
	public static function getObjectFields($object) {
		// TODO: try to get structure from `CRM_#CACHE_ACL_OBJECT_FIELD`
		// if ($object is instance Core_AclBase) {
		//   if ($object->isStructInstalled) then get structure from `CRM_#CACHE_ACL_OBJECT_FIELD` and put into Core_AclFields
		//   else get from $object->getFields() and put into Core_AclFields
	}

	public static function canUserReadObject($idUser, $aclOrIdZasob) {
		throw new Exception("TODO: canUserReadObjec({$idUser}, {$aclOrIdZasob})");
	}
	public static function canUserCreateObject($idUser, $aclOrIdZasob) {
		throw new Exception("TODO: canUserCreateObjec({$idUser}, {$aclOrIdZasob})");
	}
	public static function canUserWriteObject($idUser, $aclOrIdZasob) {
		throw new Exception("TODO: canUserWriteObjec({$idUser}, {$aclOrIdZasob})");
	}
	public static function canUserReadObjectField($idUser, $aclOrIdZasob, $fieldNameOrXPath) {
		throw new Exception("TODO: canUserReadObjectFiel({$idUser}, {$aclOrIdZasob}, {$fieldNameOrXPath})");
	}
	public static function canUserCreateObjectField($idUser, $aclOrIdZasob, $fieldNameOrXPath) {
		throw new Exception("TODO: canUserCreateObjectFiel({$idUser}, {$aclOrIdZasob}, {$fieldNameOrXPath})");
	}
	public static function canUserWriteObjectField($idUser, $aclOrIdZasob, $fieldNameOrXPath) {
		throw new Exception("TODO: canUserWriteObjectFiel({$idUser}, {$aclOrIdZasob}, {$fieldNameOrXPath})");
	}
	// TODO: replace below:
	// AclBase->canCreateField
	// AclBase->canReadField
	// AclBase->canReadObjectField
	// AclBase->canWriteField
	// AclBase->canWriteObjectField
	// AclBase->canWriteRecord
	// AclBase->canReadRecord
}