<?php

Lib::loadClass('Type_Field');
Lib::loadClass('Type_RefConfig');
Lib::loadClass('Api_WfsNs');
Lib::loadClass('ACL');
Lib::loadClass('SchemaVersionUpgrade');

/*
	RefStorage - for ref data manipulation
	RefConfig - for ref config

	`CRM_REF_CONFIG`:
	- `ID`: primaryKey (used to generate **Storage** name for data)
	- `ROOT_OBJECT_NS`: root object namespace
	- `CHILD_NAME`: child typeName (or alias name - not supported yet)
	- `CHILD_NS`: child namespace (required when use `CHILD_NAME` as alias)
	- `A_STATUS`: status
		- 'WAITING': not Active, 'NORMAL': Active, 'DELETED': removed
	- `VERSION`: **Storage** version
	- `A_LAST_ACTION_DATE`: last action timestamp
	- `SOURCE`: place where data is stored
		- 'table': table with data
		- 'view': view by flat relation cache
		- 'backRef': view on ref table with replaced fields

	**Storage** - table or view - place where data is stored

	RefConfig::isActive($objectNamespace, $childTypeName) // Ref is active when: CRM_REF_CONFIG.A_STATUS = NORMAL
	RefConfig::getRefTable($objectNamespace, $childTypeName) // ref table - TODO not needed?
 */

class RefConfig {

	static $REF_TABLE_VERSION = 6;
	// TODO: add unique key to (PRIMARY_KEY, REMOTE_PRIMARY_KEY)
	// $REF_TABLE_VERSION = 4; // added ref event log table - `CRM__#REF_LOG__*`
	static function getVersion() {
		return self::$REF_TABLE_VERSION;
	}
	static function getToUpdateTotal() {
		return DB::getPDO()->fetchValue("
			select count(*) as cnt
			from `CRM_REF_CONFIG` c
			where c.A_STATUS = 'NORMAL' and c.VERSION < :version
				and c.SOURCE in ( 'table', 'view', 'backRef' )
		", [
			':version' => self::$REF_TABLE_VERSION,
		]);
	}
	static function getToUpdateItems($type = 'table', $limit = 0, $idLast = 0) {
		$sqlAndWhere = "";
		$sqlLimit = "";
		if ($limit > 0) { // && $idLast > 0) {
			$sqlAndWhere .= " and c.ID > {$idLast} ";
			$sqlLimit .= " order by c.ID ASC limit {$limit} ";
		}
		switch ($type) {
			case 'backRef': $sqlAndWhere .= " and c.SOURCE in ( 'backRef' ) "; break;
			default: $sqlAndWhere .= " and c.SOURCE in ( 'table', 'view' ) "; break;
		}
		return DB::getPDO()->fetchAll("
			select c.ID, c.ROOT_OBJECT_NS, c.CHILD_NAME, c.CHILD_NS
			from `CRM_REF_CONFIG` c
			where c.A_STATUS = 'NORMAL' and c.VERSION < :version {$sqlAndWhere}
			{$sqlLimit}
		", [
			':version' => self::$REF_TABLE_VERSION,
		]);
	}
	static function backRefCheck() {
		// TODO: p_ID != NULL - parent Ref must exist
		// TODO: p_SOURCE != 'backRef' - only ( 'table', 'view' ) allowed as source ref
		// TODO: p_SOURCE = 'NORMAL'
		//       whene p_SOURCE = 'WAITING' then update p_SOURCE to 'WAITING' too
		//       whene p_SOURCE = 'DELETED' then update p_SOURCE to 'DELETED' too
		$backRefCheckSql = "
			select c.ID, c.SOURCE, c.A_STATUS, c.ROOT_OBJECT_NS, c.CHILD_NAME, c.CHILD_NS
				, p.ID as p_ID, p.SOURCE as p_SOURCE, p.A_STATUS as p_A_STATUS, p.ROOT_OBJECT_NS as p_ROOT_OBJECT_NS, p.CHILD_NAME as p_CHILD_NAME, p.CHILD_NS as p_CHILD_NS
			from `CRM_REF_CONFIG` c
				left join `CRM_REF_CONFIG` p on ( p.ROOT_OBJECT_NS = c.CHILD_NS and p.CHILD_NS = c.ROOT_OBJECT_NS )
			where c.A_STATUS = 'NORMAL' and c.SOURCE in ( 'backRef' )
		";
		return DB::getPDO()->fetchAll($backRefCheckSql);
	}


	static function isActive($objectNamespace, $childTypeName) {
		$refInfo = self::fetch($objectNamespace, $childTypeName);
		return ('NORMAL' === $refInfo->status);
	}

	/** static function getRefConfig(Type_Namespace $rootObjectNamespace, Type_TypeName $childName, Type_Namespace $childNamespace = null): Type_RefConfig */
	static function getRefConfig($rootObjectNamespace, $childTypeName, $childNamespace = null) {
		static $cacheRefConfigs = array();
		$cacheKey = "{$rootObjectNamespace}/{$childTypeName}";
		if (array_key_exists($cacheKey, $cacheRefConfigs)) return $cacheRefConfigs[$cacheKey];

		$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
		if (!($rootAcl instanceof AntAclBase)) throw new Exception("Ref allowed only for AntAcl objects");
		$fieldInfo = $rootAcl->_getField($childTypeName); // throws Exception if field not exists

		$refConfig = self::fetch($rootObjectNamespace, $childTypeName, $childNamespace);
		if ('WAITING' == $refConfig->status || $refConfig->version < self::$REF_TABLE_VERSION) {
			$typeField = Type_Field::build($fieldInfo);
			self::update($rootObjectNamespace, $childTypeName, $typeField, $refConfig);
			$refConfig = self::fetch($rootObjectNamespace, $childTypeName, $childNamespace);
		}

		$cacheRefConfigs[$cacheKey] = $refConfig;
		return $refConfig;
	}
	static function getRefConfigById($id) {
		$refInfo = DB::getPDO()->fetchFirst("
			select c.ID, c.A_STATUS, c.VERSION, c.SOURCE
				, c.ROOT_OBJECT_NS
				, c.CHILD_NAME
			from `CRM_REF_CONFIG` c
			where c.ID = :id
		", [
			':id' => $id,
		]);
		if (!$refInfo['ID']) throw new Exception("Ref table not found in ref config table (id '{$id}')");
		$rootObjectNamespace = $refInfo['ROOT_OBJECT_NS'];
		$childTypeName = $refInfo['CHILD_NAME'];
		$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
		if (!($rootAcl instanceof AntAclBase)) throw new Exception("Ref allowed only for AntAcl objects");
		$fieldInfo = $rootAcl->_getField($childTypeName); // throws Exception if field not exists
		$refConfig = Type_RefConfig::build($refInfo);
		if ('WAITING' == $refConfig->status || $refConfig->version < self::$REF_TABLE_VERSION) {
			$typeField = Type_Field::build($fieldInfo);
			self::update($rootObjectNamespace, $childTypeName, $typeField, $refConfig);
			$refConfig = self::fetch($rootObjectNamespace, $childTypeName, $childNamespace);
		}

		$cacheRefConfigs[$cacheKey] = $refConfig;
		return $refConfig;
	}

	/** static function fetch(Type_Namespace $rootObjectNamespace, Type_TypeName $childName, Type_Namespace $childNamespace = null): Type_RefConfig */
	static function fetch($rootObjectNamespace, $childName, $childNamespace = null) { // @returns Type_RefConfig
		SchemaVersionUpgrade::upgradeSchema();

		$rootObjectNamespace = ACL::getBaseNamespace($rootObjectNamespace);

		$rootAcl = ACL::getAclByNamespace($rootObjectNamespace);
		if (!($rootAcl instanceof AntAclBase)) throw new Exception("Ref allowed only for AntAcl objects");
		// $fieldInfo = $rootAcl->_getField($childName); // throws Exception if field not exists
		$childFieldXsdType = $rootAcl->getXsdFieldType($childName); // throws Exception if field not exists

		if (!$childNamespace && false !== strpos($childName, '__x3A__') && false !== strpos($childName, ':')) $childNamespace = Api_WfsNs::namespaceFromTypeName($childName);
		if (!$childNamespace) {
			$childXsdType = $rootAcl->getXsdFieldType($childName);
			list($typePrefix, $childTypeName) = explode(':', $childXsdType, 2);
			$childNamespace = Api_WfsNs::namespaceFromTypeName($childTypeName);
			DBG::log(['$childXsdType' => $childXsdType, '$typePrefix' => $typePrefix, '$childNamespace' => $childNamespace], 'array', "DBG get ref table ...");
			switch ($typePrefix) {
				case 'ref_uri': $childAcl = ACL::getAclByNamespace($childNamespace); break;
				case 'ref': $childAcl = ACL::getAclByTypeName($childNamespace); break;
				default: throw new Exception("Expected ref type for field '{$childName}' in object '{$rootObjectNamespace}'");
			}
		}
		$refInfo = DB::getPDO()->fetchFirst("
			select c.ID, c.A_STATUS, c.VERSION, c.SOURCE
			from `CRM_REF_CONFIG` c
			where c.ROOT_OBJECT_NS = :ROOT_OBJECT_NS
				and c.CHILD_NAME = :CHILD_NAME
				and c.CHILD_NS = :CHILD_NS
		", [
			':ROOT_OBJECT_NS' => $rootObjectNamespace,
			':CHILD_NAME' => $childName,
			':CHILD_NS' => $childNamespace,
		]);
		if (empty($refInfo)) {
			$refInfo = [ 'ID' => 0, 'A_STATUS' => 'WAITING', 'VERSION' => 1, 'SOURCE' => 'table',
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace,
			];
			$refInfo['ID'] = DB::getPDO()->insert("CRM_REF_CONFIG", [
				'ROOT_OBJECT_NS' => $rootObjectNamespace,
				'CHILD_NAME' => $childName,
				'CHILD_NS' => $childNamespace,
				'VERSION' => 1 // need update @see getRefConfig - require update SOURCE if needed
			]);
		}

		if (!$refInfo['ID']) throw new Exception("Ref table not found in ref config table for field '{$childName}' in object '{$rootObjectNamespace}'");
		return Type_RefConfig::build($refInfo);
	}

	static function getChildRefFullList($namespace) {
		$namespace = ACL::getBaseNamespace($namespace);
		if (!$namespace) throw new Exception("Missing namespace");
		return array_map('Type_RefConfig::build', DB::getPDO()->fetchAll("
			select c.ID
				, c.A_STATUS
				, c.SOURCE
				, c.CHILD_NAME
			from CRM_REF_CONFIG c
			where c.ROOT_OBJECT_NS = :namespace
			--	and c.A_STATUS = 'NORMAL'
		", [
			':namespace' => $namespace,
		]));
	}

	static function isRefField($objectNamespace, $fieldName) {
		return (false !== strpos($fieldName, ':'));
	}

	/*
		@param $appInfo = [
			[type] => ref:default_db__x3A__BI_audit_CEIDG:BI_audit_CEIDG
			[minOccurs] => 0
			[maxOccurs] => unbounded
			[restrictions] => []
			[appInfo] => []
		]
	*/
	static function needUpdate($objectNamespace, $childTypeName, Type_Field $newField, Type_Field $oldField = null) {
		DBG::log(['objectNamespace' => $objectNamespace, 'childTypeName' => $childTypeName, 'newField' => $newField, 'oldField' => $oldField], 'array', "RefConfig::needUpdate...");
		if (!$oldField) throw new Exception("Missig oldField in RefConfig::needUpdate({$objectNamespace}, {$childTypeName}, ...) - TODO: fetch from #acl cache");

		if (!($newField instanceof Type_Field_Ref)) return false;
		if (!($oldField instanceof Type_Field_Ref)) return false;

		$oldRefActive = self::isActive($objectNamespace, $childTypeName);
		if (!$oldRefActive) return false; // if old not installed / adtivated then just fix struct and install

		$newRefSource = $newField->source;
		$oldRefConf = self::fetch($objectNamespace, $childTypeName);
		$oldRefSource = $oldRefConf->source;
		if ($newRefSource !== $oldRefSource) DBG::log("RefConfig::needUpdate Change ref source from '{$oldRefSource}' to '{$newRefSource}'");
		if ($newRefSource !== $oldRefSource) return true;

		return false;
	}

	static function update($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		if (!($newField instanceof Type_Field_Ref)) return;

		// $oldRefActive = self::isActive($objectNamespace, $childTypeName);
		// if (!$oldRefActive) return; // if old not installed / adtivated then just fix struct and install

		$newRefSource = $newField->source;
		$refConfig = ($refConfig) ? $refConfig : self::fetch($objectNamespace, $childTypeName);
		$oldRefSource = $refConfig->source;
		if ($newRefSource !== $oldRefSource) DBG::log("RefConfig::update Change ref source from '{$oldRefSource}' to '{$newRefSource}'");
		// always update ref config at reinstall - drop / create ref tables (table or view)

		if ($refConfig->version < 4) self::installEventLogTable($objectNamespace, $childTypeName, $newField, $refConfig);

		// if ($refConfig->version < 5) {
		// 	Lib::loadClass('RefConfig_UpdateToVersion5');
		// 	RefConfig_UpdateToVersion4::updateToVersion5($objectNamespace, $childTypeName, $newField, $refConfig);
		// }

		switch ($newRefSource) {
			case 'table': return self::installRefTable($objectNamespace, $childTypeName, $newField, $refConfig);
			case 'view': return self::installRefView($objectNamespace, $childTypeName, $newField, $refConfig);
			case 'backRef': return self::installBackRef($objectNamespace, $childTypeName, $newField, $refConfig);
			default: throw new Exception("Not Implemented ref source '{$newRefSource}'");
		}
	}

	static function createRefTable($objectNamespace, $childTypeName, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		// TODO: check if table has data
		// TODO: if no data in table then DROP ?
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `{$refConfig->tableName}` (
				`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
				, UNIQUE KEY `unique_ref` (`PRIMARY_KEY`,`REMOTE_PRIMARY_KEY`)
				, KEY `PRIMARY_KEY` (`PRIMARY_KEY`)
				, KEY `REMOTE_PRIMARY_KEY` (`REMOTE_PRIMARY_KEY`)
				, KEY `TRANSACTION_ID` (`TRANSACTION_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='{$objectNamespace} #REF {$childTypeName}';
		");
		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'A_STATUS' => "NORMAL",
			// 'VERSION' => self::$REF_TABLE_VERSION
		]);
		self::upgradeRefTableFrom1to2($refConfig);
		// self::upgradeRefTableFrom4to5($refConfig);
		// self::upgradeRefTableFrom5to6($refConfig);
	}
	static function upgradeRefTableFrom1to2(Type_RefConfig $refConfig) { // TODO: rm ACL::upgradeRefConfigFrom1to2
		if (1 == $refConfig->version) {
			if ('table' === $refConfig->source && 'NORMAL' == $refConfig->status) {
				try {
					DB::getPDO()->execSql(" CREATE INDEX `TRANSACTION_ID` ON `{$refConfig->tableName}` (`TRANSACTION_ID`) ");
				} catch (Exception $e) {
					DBG::log($e);
				}
			}
			$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
				'VERSION' => 2
			]);
		}
	}
	// static function upgradeRefTableFrom4to5(Type_RefConfig $refConfig) { // TODO: rm ACL::upgradeRefConfigFrom1to2
	// 	if ($refConfig->version < 5) {
	// 		if ('table' === $refConfig->source && 'NORMAL' == $refConfig->status) {
	// 			try {
	// 				DB::getPDO()->execSql(" ALTER TABLE `{$refConfig->tableName}` ADD `REF_PARAMS` varchar(1024) DEFAULT '' ");
	// 			} catch (Exception $e) {
	// 				DBG::log($e);
	// 			}
	// 		}
	// 		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
	// 			'VERSION' => 5
	// 		]);
	// 	}
	// }
	// static function upgradeRefTableFrom5to6(Type_RefConfig $refConfig) { // TODO: rm ACL::upgradeRefConfigFrom1to2
	// 	if ($refConfig->version < 6) {
	// 		if ('table' === $refConfig->source && 'NORMAL' == $refConfig->status) {
	// 			try {
	// 				DB::getPDO()->execSql(" ALTER TABLE `{$refConfig->tableName}` DROP COLUMN `REF_PARAMS` ");
	// 			} catch (Exception $e) {
	// 				DBG::log($e);
	// 			}
	// 		}
	// 	}
	// 	$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
	// 		'VERSION' => 6
	// 	]);
	// }

	static function installRefTable($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		self::createRefTable($objectNamespace, $childTypeName, $refConfig);
		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => "table",
			'A_STATUS' => "NORMAL",
			'VERSION' => self::$REF_TABLE_VERSION,
		]);
	}

	static function installRefView($objectNamespace, $childTypeName, Type_Field $typeField, Type_RefConfig $refConfig = null) {
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		Lib::loadClass('SchemaFactory');
		$item = SchemaFactory::loadDefaultObject('SystemObject')->getItem($objectNamespace, [ 'propertyName' => '*,field' ]);
		$appInfo = (!empty($item['appInfo'])) ? @json_decode($item['appInfo'], $assoc = true) : null;
		$charset = (!empty($appInfo) && !empty($appInfo['table_structure']['@charset'])) ? $appInfo['table_structure']['@charset'] : null;
		$viewSelectSql = RefConfig::generateRefSelectSqlByFlatRelationCache($objectNamespace, $childTypeName, $typeField, $charset);
		$refTableViewName = Type_RefConfig::generateTableName($refConfig->id, 'view');
		DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$refTableViewName}` AS {$viewSelectSql} ");

		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => 'view',
			'A_STATUS' => "NORMAL",
			'VERSION' => self::$REF_TABLE_VERSION,
		]);
	}

	static function installBackRef($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		$viewSelectSql = self::generateRefSelectSqlByBackRef($objectNamespace, $childTypeName);
		if (!$refConfig) $refConfig = self::fetch($objectNamespace, $childTypeName);
		$backRefTableViewName = Type_RefConfig::generateTableName($refConfig->id, 'backRef');
		try {
			DB::getPDO()->execSql(" CREATE OR REPLACE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `{$backRefTableViewName}` AS {$viewSelectSql} ");
		} catch (Exception $e) {
			DBG::log($e);
			if ('HY000' === $e->getCode()) { // SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation ' IN '
				// Missing @charset=latin2 in `CRM_#CACHE_ACL_OBJECT`.`appInfo`
				// FIX: add xs:appInfo: <system_cache__appinfo:table_structure system_cache__appinfo:charset="latin2"/>
			}
			throw $e;
		}

		$affected = DB::getPDO()->update("CRM_REF_CONFIG", 'ID', $refConfig->id, [
			'SOURCE' => 'backRef',
			'A_STATUS' => "NORMAL",
			'VERSION' => self::$REF_TABLE_VERSION,
		]);
	}

	static function generateRefSelectSqlByBackRef($rootObjectNamespace, $childName) {
		// 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);
		// $refConfig = self::getRefConfig($childNs, $rootTypeName); // NOTE: Uwaga getRefConfig recurence loop
		$refConfig = self::fetch($childNs, $rootTypeName);
		if ('WAITING' == $refConfig->status || $refConfig->version < self::$REF_TABLE_VERSION) {
			throw new Exception("Error: Install/Update ref table from {$childNs} to {$rootTypeName} first");
		}
		$backRefTable = $refConfig->tableName;
		DBG::log($backRefTable, 'array', "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;
	}

	static function generateRefSelectSqlByFlatRelationCache($rootObjectNamespace, $childName, Type_Field $typeField, $charset = 'utf8') { // CRM_REF_CONFIG
		$appInfo = $typeField->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 = ACL::getAclByNamespace($rootObjectNamespace);
		$childXsdType = $rootAcl->getXsdFieldType($childName);
		list($typePrefix, $childNamespace) = explode(':', $childXsdType, 2);
		switch ($typePrefix) {
			case 'ref_uri': $childAcl = ACL::getAclByNamespace($childNamespace); break;
			case 'ref': $childAcl = ACL::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 '{$rootObjectNamespace}' field '{$childName}'");
			}
		}
		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, $charset) {
						$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(function ($option) use ($charset) {
									return ($charset && $charset !== 'utf8')
									?	"CONVERT(" . DB::getPDO()->quote($option) . " using {$charset})"
									:	DB::getPDO()->quote($option);
								}, array_keys($restrictions['enumeration']))) . ")";
							}
							if (array_key_exists('minInclusive', $restrictions)) {
								$minInclusive = (int)$restrictions['minInclusive'];
								$sqlRestrictions[] = "{$sqlTablePrefix}.`{$field['fieldNamespace']}` > {$minInclusive}";
							}
						}
						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, $charset) {
						$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(function ($option) use ($charset) {
									return ($charset && $charset !== 'utf8')
									?	"CONVERT(" . DB::getPDO()->quote($option) . " using {$charset})"
									:	DB::getPDO()->quote($option);
								}, array_keys($restrictions['enumeration']))) . ")";
							}
						}
						return $sqlRestrictions;
					}, $childLocalFieldsWithRestrictions),
					function ($ret, $cur) {
						return array_merge($ret, array_filter($cur, ['V', 'filterNotEmpty']));
					},
					$sqlWhereFromRestrictions
				);
			}
		}
		$sqlWhereFromRestrictions = (!empty($sqlWhereFromRestrictions)) ? implode("\n\t 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;
	}

	static function remove(Type_RefConfig $refConfig) {
		DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refConfig->id, [
			'A_STATUS' => 'DELETED',
			'A_LAST_ACTION_DATE' => 'NOW()',
		]);
	}

	static function reactivate(Type_RefConfig $refConfig) {
		DB::getPDO()->update('CRM_REF_CONFIG', 'ID', $refConfig->id, [ // TODO: update ref table, update source -- fixed below by RefConfig::update
			'A_STATUS' => 'WAITING',
			'A_LAST_ACTION_DATE' => 'NOW()',
		]);
	}

	static function getRefEventLogTable($objectNamespace, $childTypeName) {
		$refConfig = self::fetch($objectNamespace, $childTypeName);
		return "CRM__#REF_LOG__{$refConfig->id}";
	}
	static function installEventLogTable($objectNamespace, $childTypeName, Type_Field $newField, Type_RefConfig $refConfig = null) {
		// $refConfig->id
		// $refConfig->source
		// $refConfig->version
		// $refConfig->tableName
		$sqlLogTableName = self::getRefEventLogTable($objectNamespace, $childTypeName);
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `{$sqlLogTableName}` (
				`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_ACTION_DATE` timestamp NOT NULL DEFAULT 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='{$objectNamespace} #REF {$childTypeName}';
		");
	}

}