<?php
/**
 * Contact content class — person or business contact stored in liberty_content.
 *
 * Person contacts carry name parts in a $00 xref (xkey_ext = prefix|forename|surname|suffix).
 * Business contacts store the organisation name directly in lc.title.
 *
 * @package contact
 */
namespace Bitweaver\Contact;

use Bitweaver\BitBase;
use Bitweaver\BitDate;
use Bitweaver\Liberty\LibertyContent;		// Contact base class
require_once CONTACT_PKG_PATH.'lib/phpcoord-2.3.php';

define( 'CONTACT_CONTENT_TYPE_GUID', 'contact' );
defined( 'CONTACTPERSON_CONTENT_TYPE_GUID' )   || define( 'CONTACTPERSON_CONTENT_TYPE_GUID',   'contactperson' );
defined( 'CONTACTBUSINESS_CONTENT_TYPE_GUID' ) || define( 'CONTACTBUSINESS_CONTENT_TYPE_GUID', 'contactbusiness' );

class Contact extends LibertyContent {

	public $mParentId;
	public $mDate;
	public $mTypes;

	protected $mXrefTypeKey = 'contact_types';

	/**
	 * @param int|null $pContactId  Unused legacy param — pass null.
	 * @param int|null $pContentId  liberty_content.content_id to load.
	 */
	public function __construct( $pContactId = NULL, $pContentId = NULL ) {
		parent::__construct();
		$this->registerContentType( CONTACT_CONTENT_TYPE_GUID, [
				'content_type_guid' => CONTACT_CONTENT_TYPE_GUID,
				'content_name' => 'Contact Entry',
				'handler_class' => 'Contact',
				'handler_package' => 'contact',
				'handler_file' => 'Contact.php',
				'maintainer_url' => 'http://lsces.co.uk',
			] );
		$this->mContentId = (int)$pContentId;
		$this->mContentTypeGuid = CONTACT_CONTENT_TYPE_GUID;

		// Date object to handle date and time display
		$this->mDate = new BitDate();
		$offset = $this->mDate->get_display_offset();

		// Permission setup
		$this->mViewContentPerm  = 'p_contact_view';
		$this->mCreateContentPerm  = 'p_contact_create';
		$this->mUpdateContentPerm  = 'p_contact_update';
		$this->mExpungeContentPerm  = 'p_contact_expunge';
		$this->mAdminContentPerm = 'p_contact_admin';

		$this->mTypes = new ContactType();
	}

	/**
	 * Load type-tag xref rows (P01/P02/B01–B04) directly from liberty_xref.
	 *
	 * The schema-driven getContentTypeMarkers() requires liberty_xref_item rows to
	 * exist at the contactperson/contactbusiness level — they don't exist until the
	 * 5.0.3 upgrade runs. Reading liberty_xref directly makes type tags visible in
	 * both pre- and post-upgrade states. Schema labels are enriched where available.
	 */
	public function loadXrefTypeList(): void {
		if ( !$this->isValid() || !empty( $this->mInfo[$this->mXrefTypeKey] ) ) return;

		$result = $this->mDb->query(
			"SELECT x.`item`, x.`xkey_ext`, x.`content_id`,
			        COALESCE( (SELECT FIRST 1 i2.`cross_ref_title`
			                   FROM `".BIT_DB_PREFIX."liberty_xref_item` i2
			                   WHERE i2.`item` = x.`item`),
			                  x.`item` ) AS `cross_ref_title`
			 FROM `".BIT_DB_PREFIX."liberty_xref` x
			 WHERE x.`content_id` = ?
			   AND ( x.`item` STARTING WITH 'P'
			      OR x.`item` STARTING WITH 'B'
			      OR x.`item` STARTING WITH '\$' )
			 ORDER BY x.`item`",
			[ $this->mContentId ]
		);

		$this->mInfo[$this->mXrefTypeKey] = [];
		while ( $row = $result->fetchRow() ) {
			$this->mInfo[$this->mXrefTypeKey][] = $row;
		}
	}

	/**
	 * Return all available type-tag options for this contact's edit form.
	 *
	 * Uses the schema (liberty_xref_item via getTypeMarkers) post-upgrade.
	 * Falls back to a hard-coded list pre-upgrade so edit checkboxes always appear.
	 * P01 is always excluded — it is implied for every person and is not a user choice.
	 *
	 * @return array[]  Each element: ['item' => string, 'name' => string]
	 */
	public function getAvailableTypeItems(): array {
		$markers = $this->xrefType()->getTypeMarkers();
		if ( !empty( $markers ) ) {
			return array_values( array_filter( $markers, fn( $m ) => $m['item'] !== 'P01' ) );
		}
		// Pre-upgrade fallback — schema rows not yet migrated
		if ( $this->mContentTypeGuid === CONTACTPERSON_CONTENT_TYPE_GUID ) {
			return [ [ 'item' => 'P02', 'name' => 'MERG Kit Elf' ] ];
		}
		return [
			[ 'item' => 'B01', 'name' => 'Service' ],
			[ 'item' => 'B02', 'name' => 'Manufacturer' ],
			[ 'item' => 'B03', 'name' => 'Distributor' ],
			[ 'item' => 'B04', 'name' => 'Supplier' ],
		];
	}

	/**
	 * Load contact record, name parts, and xref groups into $this->mInfo.
	 *
	 * @param int|null   $pContentId    Override mContentId for this load.
	 * @param array|null $pPluginParams Passed through to LibertyContent::load().
	 */
	public function load( $pContentId = NULL, $pPluginParams = NULL ) {
		if ( $pContentId ) $this->mContentId = (int)$pContentId;
		if( $this->verifyId( $this->mContentId ) ) {
			$query = "select con.*, lc.*,
 				ap.*, xhA.`xkey_ext` AS house,
 				img.`xkey` AS client_gallery,
 				x00.`xkey_ext` as name, lc.`title` as organisation,
 				xhL.`xkey` as x_coordinate, xhL.`xkey_ext` as y_coordinate,
 				uue.`login` AS modifier_user, uue.`real_name` AS modifier_real_name,
				uuc.`login` AS creator_user, uuc.`real_name` AS creator_real_name,
				uu.`login` AS linked_user_login, uu.`real_name` AS linked_user_name
				FROM `".BIT_DB_PREFIX."contact` con
				LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id = con.content_id
				LEFT JOIN `".BIT_DB_PREFIX."users_users` uue ON (uue.`user_id` = lc.`modifier_user_id`)
				LEFT JOIN `".BIT_DB_PREFIX."users_users` uuc ON (uuc.`user_id` = lc.`user_id`)
				LEFT JOIN `".BIT_DB_PREFIX."users_users` uu ON uu.`user_id` = con.`role_id`
				LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` img ON img.`content_id` = con.`content_id` AND img.`item` = 'IMG'
				LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` x00 ON x00.`content_id` = con.`content_id` AND x00.`item` = 'P01'
				LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` xhA ON xhA.`content_id` = con.`content_id` AND xhA.`item` = '#S' AND ( xhA.`end_date` IS NULL OR xhA.`end_date` > CURRENT_TIMESTAMP )
				LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` xhL ON xhL.`content_id` = con.`content_id` AND xhL.`item` = '#L' AND ( xhL.`end_date` IS NULL OR xhL.`end_date` > CURRENT_TIMESTAMP )
				LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhA.`xkey`
				WHERE con.`content_id`=?";
			$result = $this->mDb->query( $query, [ $this->mContentId ] );
//				LEFT JOIN `".BIT_DB_PREFIX."contact` ci ON ci.contact_id = pro.owner_id
//				LEFT JOIN `".BIT_DB_PREFIX."contact_address` a ON a.contact_id = pro.address_id
//				LEFT JOIN `".BIT_DB_PREFIX."postcode` p ON p.`postcode` = a.`postcode`

			if ( $result && $result->numRows() ) {
				$this->mInfo = $result->fields;
				$this->mContentId = (int)$result->fields['content_id'];
//				$this->mParentId = (int)$result->fields['usn'];
				$this->mContactName = $result->fields['title'];
				$this->mInfo['creator'] = $result->fields['creator_real_name'] ?? $result->fields['creator_user'];
				$this->mInfo['editor'] = $result->fields['modifier_real_name'] ?? $result->fields['modifier_user'];
				$this->mInfo['display_url'] = $this->getDisplayUrl();
				$this->mInfo['organisation'] = trim($this->mInfo['organisation'] ?? '');
				$name = explode( '|', $this->mInfo['name'] ?? '' );
				$this->mInfo['prefix'] = $name[0] ?? '';
				$this->mInfo['forename'] = $name[1] ?? '';
				$this->mInfo['surname'] = $name[2] ?? '';
				$this->mInfo['suffix'] = $name[3] ?? '';
				$this->mInfo['name'] = $this->mInfo['prefix'];
				$this->mInfo['name'] = trim($this->mInfo['name']).' '.$this->mInfo['forename'];
				$this->mInfo['name'] = trim($this->mInfo['name']).' '.$this->mInfo['surname'];
				$this->mInfo['name'] = trim($this->mInfo['name']).' '.$this->mInfo['suffix'];
				$this->mInfo['name'] = trim($this->mInfo['name']);

				if ( !$this->mInfo['x_coordinate'] and $this->mInfo['postcode'] and $this->mInfo['grideast'] <> '00000' ) {
					$os1 = new \OSRef( $this->mInfo['grideast']*10, $this->mInfo['gridnorth']*10 );
					$ll1 = $os1->toLatLng();
					$this->mInfo['y_coordinate'] = $ll1->lat;
					$this->mInfo['x_coordinate'] = $ll1->lng;
				}

				$this->loadXrefTypeList();
				$this->loadXrefInfo();
			}
		}
		LibertyContent::load();

	}

	/**
	 * Validate and normalise $pParamHash before storing.
	 *
	 * Builds lc.title from surname (person) or organisation (business).
	 * Pipe-encodes name parts into $pParamHash['name'] for the $00 xref.
	 *
	 * @param  array $pParamHash  Data to store; modified in place.
	 * @return bool  TRUE if valid; FALSE with $this->mErrors set on failure.
	 */
	public function verify( &$pParamHash ): bool {
		// make sure we're all loaded up if everything is valid
		if( $this->isValid() && empty( $this->mInfo ) ) {
			$this->load( TRUE );
		}

		// It is possible a derived class set this to something different
		if( empty( $pParamHash['content_type_guid'] ) ) {
			$pParamHash['content_type_guid'] = $this->mContentTypeGuid;
		}

		if( !empty( $this->mContentId ) ) {
			$pParamHash['content_id'] = $this->mContentId;
			$pParamHash['contact_store']['content_id'] = $this->mContentId;
		} else {
			unset( $pParamHash['content_id'] );
		}

		if( isset( $pParamHash['surname'] ) ) {
			$pParamHash['name'] = $pParamHash['prefix'].'|'.$pParamHash['forename'].'|'.$pParamHash['surname'].'|'.$pParamHash['suffix'];

			if ( strlen($pParamHash['surname']) > 0 ) {
				$pParamHash['title'] = $pParamHash['surname'];
				if ( strlen($pParamHash['prefix']) > 0 ) $pParamHash['title'] .= ', '.$pParamHash['prefix'].' '.$pParamHash['forename'];
				else if ( strlen($pParamHash['forename']) > 0 ) $pParamHash['title'] .= ', '.$pParamHash['forename'];
			}
		}
		if( empty( $pParamHash['title'] ) ) {
			$pParamHash['title'] = $pParamHash['organisation'] ?? '';
		}
		$pParamHash['title'] = trim( $pParamHash['title'] );
		$pParamHash['contact_store']['xkey'] = $pParamHash['xkey'];
		if( array_key_exists( 'user_id', $pParamHash ) ) {
			$pParamHash['contact_store']['role_id'] = $pParamHash['user_id'] ? (int)$pParamHash['user_id'] : null;
		}
		return count( $this->mErrors ) == 0;
	}

	/**
	 * Persist contact and its type xrefs inside a transaction.
	 *
	 * Calls verify() then LibertyContent::store(). On a new record also
	 * inserts the contact_address stub row. Writes contact_types xrefs
	 * (including $00 person-name and $01 organisation) if present in hash.
	 *
	 * @param  array $pParamHash  Data to persist; modified in place.
	 * @return bool  TRUE on success; FALSE with $this->mErrors set on failure.
	 */
	public function store( &$pParamHash ): bool {
		if( $this->verify( $pParamHash ) ) {

			// Start a transaction wrapping the whole insert into liberty

			$this->mDb->StartTrans();
			if ( LibertyContent::store( $pParamHash ) ) {
				$table = BIT_DB_PREFIX."contact";
				$atable = BIT_DB_PREFIX."contact_address";

				// mContentId will not be set until the secondary data has commited
				if( !empty( $pParamHash['contact_store']['content_id'] ) ) {
					$result = $this->mDb->associateUpdate( $table, $pParamHash['contact_store'], [ "content_id" => $this->mContentId ] );
				} else {
					$pParamHash['contact_store']['content_id'] = $pParamHash['content_id'];
					$pParamHash['contact_store']['parent_id'] = $pParamHash['content_id'];
					$pParamHash['contact_store']['address_id'] = $pParamHash['content_id'];
					$pParamHash['contact_store']['xkey'] = $pParamHash['xkey'];
					$this->mParentId = $pParamHash['contact_store']['parent_id'];
					$this->mContentId = $pParamHash['content_id'];
					$result = $this->mDb->associateInsert( $table, $pParamHash['contact_store'] );
					// Dummy contact addresss entry ... need edit page for address without using nlpg data
					unset($pParamHash['contact_store']['parent_id']);
					unset($pParamHash['contact_store']['xkey']);
					$result = $this->mDb->associateInsert( $atable, $pParamHash['contact_store'] );
				}
				if( !empty( $pParamHash['contact_types'] ) ) {
					// P01 carries the pipe-encoded name — always rewrite it independently of the checkbox set
					$this->mDb->query( "DELETE FROM `".BIT_DB_PREFIX."liberty_xref` WHERE `content_id` = ? AND `item` = 'P01'", [ $this->mContentId ] );
					if( !empty( $pParamHash['name'] ) ) {
						$this->mDb->query(
							"INSERT INTO `".BIT_DB_PREFIX."liberty_xref` (`xref_id`, `content_id`, `item`, `xkey_ext`, `last_update_date`) VALUES (?, ?, 'P01', ?, NULL)",
							[ $this->mDb->GenID('liberty_xref_seq'), $this->mContentId, $pParamHash['name'] ]
						);
					}
					// Optional type tags (P02+, B01+) come from the form checkboxes
					$this->mDb->query(
						"DELETE FROM `".BIT_DB_PREFIX."liberty_xref` WHERE `content_id` = ? AND (`item` STARTING WITH 'P' OR `item` STARTING WITH 'B') AND `item` <> 'P01'",
						[ $this->mContentId ]
					);
					foreach ( $pParamHash['contact_types'] as $source ) {
						if ( $source !== 'P01' ) {
							$this->mDb->query(
								"INSERT INTO `".BIT_DB_PREFIX."liberty_xref` (`xref_id`, `content_id`, `item`, `last_update_date`) VALUES (?, ?, ?, NULL)",
								[ $this->mDb->GenID('liberty_xref_seq'), $this->mContentId, $source ]
							);
						}
					}
				}
				// load before completing transaction as firebird isolates results
				$this->load();
				$this->mDb->CompleteTrans();
			} else {
				$this->mDb->RollbackTrans();
				$this->mErrors['store'] = 'Failed to store this contact.';
			}
		}
		return count( $this->mErrors ) == 0;
	}

	/**
	 * Delete this contact and all its liberty_xref rows inside a transaction.
	 *
	 * @return bool TRUE on success; FALSE if the contact is not valid or the delete fails.
	 */
	public function expunge(): bool
	{
		$ret = FALSE;
		if ($this->isValid() ) {
			$this->mDb->StartTrans();
			$query = "DELETE FROM `".BIT_DB_PREFIX."liberty_xref` WHERE `content_id` = ?";
			$result = $this->mDb->query($query, [$this->mContentId ] );
			if (LibertyContent::expunge() ) {
			$ret = TRUE;
				$this->mDb->CompleteTrans();
			} else {
				$this->mDb->RollbackTrans();
			}
		}
		return $ret;
	}

	/** @return bool Always TRUE — contacts support comments. */
	public function isCommentable(){
		global $gBitSystem;
		return TRUE; // $gBitSystem->isFeatureActive( 'contact_post_comments' );
	}

	/**
	 * @param  int|null $pContentId  Defaults to $this->mContentId.
	 * @return string   URL to display_contact.php for this contact.
	 */
	public function getDisplayUrl( $pContentId=NULL ) {
		global $gBitSystem;
		if( empty( $pContentId ) ) {
			$pContentId = $this->mContentId;
		}

		return CONTACT_PKG_URL.'display_contact.php?content_id='.$pContentId;
	}

	/**
	 * @param  string|null $pLinkText  Unused — link text is derived from mInfo.
	 * @param  array|null  $pMixed     mInfo-style hash; must contain content_id and title.
	 * @param  string|null $pAnchor    Unused.
	 * @return string      HTML anchor element.
	 */
	public function getDisplayLink( $pLinkText=NULL, $pMixed=NULL, $pAnchor=NULL ) {
		if ( $this->mContentId != $pMixed['content_id'] ) $this->load($pMixed['content_id']);

		$ret = ( empty( $this->mInfo['content_id'] ) )
			? '<a href="' . $this->getDisplayUrl( $pMixed['content_id'] ) . '">' . $pMixed['title'] . '</a>'
			: '<a href="' . $this->getDisplayUrl( $pMixed['content_id'] ) . '">' . "Contact - " . $this->mInfo['title'] . '</a>';
		return $ret;
	}

	/**
	 * @param  array|null $pHash     mInfo-style hash; defaults to $this->mInfo.
	 * @param  bool       $pDefault  Unused; kept for LibertyContent interface compatibility.
	 * @return string|null           Prefixed title ("Contact - <name>") or null if empty.
	 */
	public function getTitle( $pHash = NULL, $pDefault=TRUE ) {
		$ret = NULL;
		if( empty( $pHash ) ) {
			$pHash = &$this->mInfo;
		} else {
			if ( $this->mContentId != $pHash['content_id'] ) {
				$this->load($pHash['content_id']);
				$pHash = &$this->mInfo;
			}
		}

		if( !empty( $pHash['title'] ) ) {
			$ret = "Contact - ".$this->mInfo['title'];
		} elseif( !empty( $pHash['content_name'] ) ) {
			$ret = $pHash['content_name'];
		}
		return $ret;
	}

	/**
	 * Return a paged list of contacts matching filter criteria.
	 *
	 * Recognised keys in $pParamHash: user_id (filters by linked user; stored in con.role_id), contact_type_guid, find_xref,
	 * find_title, find_location, find_postcode, active, sort_mode, max_records, offset.
	 * Sets $pParamHash['cant'] and $pParamHash['listInfo'] on return.
	 *
	 * @param  array $pParamHash  Filter and pagination params; modified in place.
	 * @return array              Flat array of result row hashes.
	 */
	public function getList( &$pParamHash ) {
		global $gBitSystem, $gBitUser;

		LibertyContent::prepGetList( $pParamHash );

		$findSql = '';
		$selectSql = '';
		$joinSql = '';
		$whereSql = '';
		$bindVars = [];

		if ( isset( $pParamHash['user_id'] ) ) {
			array_push( $bindVars, $this->mContentTypeGuid );
			if ( $pParamHash['user_id'] > 0 ) {
				$whereSql .= " AND con.`role_id` = ? ";
				$bindVars[] = (int)$pParamHash['user_id'];
			}
		}
		elseif ( isset( $pParamHash['contact_type_guid'][0] ) ) {
			$joinSql .= "JOIN `".BIT_DB_PREFIX."liberty_xref` cx ON cx.`content_id` = con.`content_id` AND cx.`item` = ? ";
			$bindVars[] = $pParamHash['contact_type_guid'][0];
		}

		// this will set $find, $sort_mode, $max_records and $offset
		extract( $pParamHash );

		if( isset( $find_xref ) and is_string( $find_xref ) and $find_xref <> '' ) {
			$joinSql .= "JOIN `".BIT_DB_PREFIX."liberty_xref` cy ON cy.`content_id` = con.`content_id` AND cy.`xkey` like ? ";
			$bindVars[] = '%' . strtoupper( $find_xref ). '%';
			$pParamHash["listInfo"]["ihash"]["find_xref"] = $find_xref;
		}

		if ( !isset( $pParamHash['user_id'] ) ) {
			array_push( $bindVars, $this->mContentTypeGuid );
		}

		$this->getServicesSql( 'content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pParamHash );

//		$pParamHash["listInfo"]["ihash"]['contact_type_guid'] = $contact_type_guid;

		$t = $gBitSystem->getUTCTime();
		if ( isset( $active ) ) {
			if ( $active === 'Inactive' ) {
				$whereSql .= " AND ( lc.`event_time` > 0 AND lc.`event_time` < $t ) ";
			}
		} else {
			$active = 'Active';
		}
		if ( $active == 'Active' ) {
			$whereSql .= " AND ( lc.`event_time` = 0 OR lc.`event_time` > $t ) ";
		}
		$pParamHash["listInfo"]["active"] = $active;


		if( isset( $find_title ) and is_string( $find_title ) and $find_title <> '' ) {
			$whereSql .= " AND UPPER( lc.`title` ) like ? ";
			$bindVars[] = '%' . strtoupper( $find_title ). '%';
			$pParamHash["listInfo"]["ihash"]["find_title"] = $find_title;
		}

/*		if( isset( $find_name ) and is_string( $find_name ) and $find_name <> '' ) {
			$split = preg_split('|[,. ]|', $find_name, 2);
			$whereSql .= " AND UPPER( ci.`surname` ) STARTING ? ";
			$bindVars[] = strtoupper( $split[0] );
			if ( array_key_exists( 1, $split ) ) {
				$split[1] = trim( $split[1] );
				$whereSql .= " AND UPPER( ci.`forename` ) STARTING ? ";
				$bindVars[] = strtoupper( $split[1] );
			}
			$pParamHash["listInfo"]["ihash"]["find_name"] = $find_name;
		}
*/
		if( isset( $find_location ) and is_string( $find_location ) and $find_location <> '' ) {
			$whereSql .= " AND ( UPPER( ap.`add2` ) like ? OR UPPER( ap.`add3` ) like ? OR UPPER( ap.`add4` ) like ? OR UPPER( ap.`town` ) like ? )";
			$uploc = '%' . strtoupper( $find_location ). '%';;
			$bindVars[] = $uploc;
			$bindVars[] = $uploc;
			$bindVars[] = $uploc;
			$bindVars[] = $uploc;
			$pParamHash["listInfo"]["ihash"]["find_location"] = $find_location;
		}

		if( isset( $find_postcode ) and is_string( $find_postcode ) and $find_postcode <> '' ) {
			$whereSql .= " AND UPPER( `ap.postcode` ) LIKE ? ";
			$bindVars[] = '%' . strtoupper( $find_postcode ). '%';
			$pParamHash["listInfo"]["ihash"]["find_postcode"] = $find_postcode;
		}

		$query = "SELECT con.`content_id` as content_id, con.*, lc.*,
			ap.*, xhA.`xkey_ext` AS house,
			xhL.`xkey` as x_coordinate, xhL.`xkey_ext` as y_coordinate,
 			(SELECT COUNT(*) FROM `".BIT_DB_PREFIX."liberty_xref` x WHERE x.`content_id` = con.`content_id` AND x.`item` NOT STARTING WITH '$' ) AS refs
			FROM `".BIT_DB_PREFIX."contact` con
			LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.`content_id` = con.`content_id`
			LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` xhA ON xhA.`content_id` = con.`content_id` AND ( xhA.`item` IN ( SELECT `item` FROM `".BIT_DB_PREFIX."liberty_xref_item` WHERE `template` = 'address' AND `content_type_guid` = 'contact' ) ) AND ( xhA.`end_date` IS NULL OR xhA.`end_date` > CURRENT_TIMESTAMP )
			LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` xhL ON xhL.`content_id` = con.`content_id` AND xhL.`item` = '#L' AND ( xhL.`end_date` IS NULL OR xhL.`end_date` > CURRENT_TIMESTAMP )
			LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhA.`xkey`
			$findSql
			$joinSql
			WHERE lc.`content_type_guid` = ? $whereSql
			ORDER BY ".$this->mDb->convertSortmode( $sort_mode );

//			(SELECT COUNT(*) FROM `".BIT_DB_PREFIX."task_ticket` e WHERE e.usn = ci.usn ) AS enquiries $selectSql
		$query_cant = "SELECT COUNT( * )
			FROM `".BIT_DB_PREFIX."contact` con
			LEFT JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id = con.content_id
			LEFT JOIN `".BIT_DB_PREFIX."liberty_xref` xhA ON xhA.`content_id` = con.`content_id` AND xhA.`item` = '#S' AND ( xhA.`end_date` IS NULL OR xhA.`end_date` > CURRENT_TIMESTAMP )
			LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = xhA.`xkey`
			$joinSql WHERE lc.`content_type_guid` = ? $whereSql ";
		$result = $this->mDb->query( $query, $bindVars, $max_records, $offset );

		$ret = [];
		while( $res = $result->fetchRow() ) {
			if (!empty($parse_split)) {
				$res = array_merge($this->parseSplit($res), $res);
			}
			$ret[] = $res;
		}
		$pParamHash["cant"] = $this->mDb->getOne( $query_cant, $bindVars );
		$pParamHash["listInfo"]["count"] = $pParamHash["cant"];

		LibertyContent::postGetList( $pParamHash );
		return $ret;
	}

/**
	 * Load contacts that reference this one via the '#A' xref item into $this->mInfo['client_list'].
	 *
	 * Used for contacts handled by a third party (e.g. alarm maintainer, call centre).
	 */
	public function loadClientList() {
		if( $this->isValid() ) {
			global $gBitUser;

			$roles = array_keys($gBitUser->mRoles);
			$bindVars = [];
			array_push( $bindVars, $this->mDb->NOW() );
			array_push( $bindVars, $this->mContentId );
//			$bindVars = array_merge( $bindVars, $roles, array( $gBitUser->mUserId ) );

			$sql = "SELECT r.`xref_id`, r.`content_id`, r.`last_update_date`, c.`title`,
					CASE
					WHEN r.`end_date` < ? THEN 'history'
					ELSE r.`item` END as type_source,
					r.`xkey`, r.`xkey_ext`, r.`data`,
					r.`start_date`, r.`end_date`
					FROM `".BIT_DB_PREFIX."liberty_xref` r
					JOIN `".BIT_DB_PREFIX."liberty_content` c ON c.`content_id` = r.`content_id`
					WHERE r.`xref` = ? AND r.`item` = '#A'
					ORDER BY c.`title`";
//					LEFT OUTER JOIN `".BIT_DB_PREFIX."users_roles_map` purm ON ( purm.`user_id`=".$gBitUser->mUserId." ) AND ( purm.`role_id` = s.`role_id` )
//					AND (s.`role_id` IN(". implode(',', array_fill(0, count($roles), '?')) ." ) OR purm.`user_id`=?)

			$result = $this->mDb->query( $sql, $bindVars );

			while( $res = $result->fetchRow() ) {
				$this->mInfo['client_list'][] = $res;
			}
		}
	}

	/**
	 * Load address xref rows (template='address') into $this->mInfo['address'].
	 *
	 * Each row is joined to address_postcode and has grid-reference coordinates
	 * converted to lat/lng when available.
	 */
	public function loadAddressList() {
		if( $this->isValid() && empty( $this->mInfo['xref'] ) ) {
			global $gBitUser;

			$roles = array_keys($gBitUser->mRoles);
			$bindVars = [];
			array_push( $bindVars, $this->mDb->NOW() );
			array_push( $bindVars, $this->mContentId );
			$bindVars = array_merge( $bindVars, $roles, [ $gBitUser->mUserId ] );

			$sql = "SELECT s.`x_group`, x.`xref_id`, x.`last_update_date`, x.`item`, t.`title` AS type_title,
					CASE
					WHEN x.`end_date` < ? THEN 'history'
					ELSE t.`x_group` END as type_source,
					CASE
					WHEN x.`xorder` = 0 THEN s.`cross_ref_title`
					ELSE s.`cross_ref_title` || '-' || x.`xorder` END
					AS source_title,
					x.`xkey_ext` AS house, ap.`add1`, ap.`add2`, ap.`add3`, ap.`add4`, ap.`town`, ap.`county`, x.`xkey` AS postcode, ap.`grideast`, ap.`gridnorth`, x.`data`,
					x.`start_date`, x.`end_date`
					FROM `".BIT_DB_PREFIX."liberty_xref` x
					JOIN `".BIT_DB_PREFIX."liberty_xref_item` s ON s.`item` = x.`item` AND s.`content_type_guid` = 'contact'
					JOIN `".BIT_DB_PREFIX."liberty_xref_group` t ON t.`x_group` = s.`x_group` AND t.`content_type_guid` = 'contact'
					LEFT JOIN `".BIT_DB_PREFIX."address_postcode` ap ON ap.`postcode` = x.`xkey`
					LEFT OUTER JOIN `".BIT_DB_PREFIX."users_roles_map` purm ON ( purm.`user_id`=".(int)($gBitUser->mUserId ?? 0)." ) AND ( purm.`role_id`=s.`role_id` )
					WHERE x.content_id = ? AND s.`template` = 'address' AND (s.`role_id` IN(". implode(',', array_fill(0, count($roles), '?')) ." ) OR purm.`user_id`=?)
					ORDER BY x.`item`, x.`xorder`";

			$result = $this->mDb->query( $sql, $bindVars );

			while( $res = $result->fetchRow() ) {
				if ( $res['grideast'] and $res['grideast'] <> '00000' ) {
					$os1 = new \OSRef( $res['grideast']*10, $res['gridnorth']*10 );
					$ll1 = $os1->toLatLng();
					$res['x_coordinate'] = $ll1->lng;
					$res['y_coordinate'] = $ll1->lat;
				} else {
					$res['house'] = $res['house'].' - '.$res['data'];
				}

				$this->mInfo['address'][] = $res;
			}
		}
	}
}