Add liberty_xref package-agnostic cross-reference mechanism

New tables: liberty_xref_type (text PK, package, sort_order),
liberty_xref_source (package column for direct equality filtering),
liberty_xref (data rows keyed by content_id, works for any package).
Sequence liberty_xref_seq and indices on content_id and package columns.

LibertyXref and LibertyXrefType classes provide the full store/load/step
mechanism. Contact and stock register their own source types against the
shared tables. Upgrade script 5.0.1.php creates the tables on existing installs.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

4 files changed, 333 insertions, 0 deletions

diff --git a/admin/schema_inc.php b/admin/schema_inc.php
index e2462d5..a77678f 100755
--- a/admin/schema_inc.php
+++ b/admin/schema_inc.php
@@ -218,6 +218,42 @@ $tables = [
 				, CONSTRAINT `lib_attachment_meta_title_ref` FOREIGN KEY (`meta_title_id`) REFERENCES `" . BIT_DB_PREFIX . "liberty_meta_titles` (`meta_title_id`) '
 ",
 
+	'liberty_xref_type'   => "
+	xref_type C(32) PRIMARY,
+	package C(20) NOTNULL,
+	title C(64),
+	sort_order I2,
+	role_id I4,
+	type_href C(256)
+",
+
+	'liberty_xref_source' => "
+	source C(20) PRIMARY,
+	package C(20) NOTNULL,
+	xref_type C(32),
+	cross_ref_title C(64),
+	multi I2,
+	role_id I4,
+	cross_ref_href C(256),
+	template C(32),
+	data X
+",
+
+	'liberty_xref'        => "
+	xref_id I8 PRIMARY,
+	content_id I8 NOTNULL,
+	source C(20),
+	xorder I2,
+	xref I8,
+	xkey C(32),
+	xkey_ext C(250),
+	data X,
+	start_date T,
+	last_update_date T,
+	entry_date T,
+	end_date T
+",
+
 ];
 
 global $gBitInstaller;
@@ -258,6 +294,9 @@ $indices = [
 	'lib_attachment_meta_idx'        => [ 'table' => 'liberty_attachment_meta_data', 'cols' => 'attachment_id', 'opts' => null ],
 	'lib_attachment_meta_type_idx'   => [ 'table' => 'liberty_attachment_meta_data', 'cols' => 'meta_type_id', 'opts' => null ],
 	'lib_attachment_meta_title_idx'  => [ 'table' => 'liberty_attachment_meta_data', 'cols' => 'meta_title_id', 'opts' => null ],
+	'liberty_xref_content_idx'       => [ 'table' => 'liberty_xref', 'cols' => 'content_id', 'opts' => null ],
+	'liberty_xref_source_pkg_idx'    => [ 'table' => 'liberty_xref_source', 'cols' => 'package', 'opts' => null ],
+	'liberty_xref_type_pkg_idx'      => [ 'table' => 'liberty_xref_type', 'cols' => 'package', 'opts' => null ],
 ];
 
 foreach( array_keys($constraints) AS $tableName ) {
@@ -280,6 +319,7 @@ $sequences = [
 	'liberty_structures_id_seq'  => [ 'start' => 4 ],
 	'liberty_meta_types_id_seq'  => [ 'start' => 1 ],
 	'liberty_meta_titles_id_seq' => [ 'start' => 1 ],
+	'liberty_xref_seq'           => [ 'start' => 1 ],
 ];
 $gBitInstaller->registerSchemaSequences( LIBERTY_PKG_NAME, $sequences );
 
diff --git a/admin/upgrades/5.0.1.php b/admin/upgrades/5.0.1.php
new file mode 100644
index 0000000..f20d190
--- /dev/null
+++ b/admin/upgrades/5.0.1.php
@@ -0,0 +1,63 @@
+<?php
+/**
+ * @package liberty
+ */
+
+global $gBitInstaller;
+
+$gBitInstaller->registerPackageUpgrade(
+	[
+		'package'     => 'liberty',
+		'version'     => '5.0.1',
+		'description' => 'Add liberty_xref tables: a package-agnostic cross-reference mechanism for attaching typed, multi-valued, date-tracked attributes to any liberty content.',
+	],
+	[
+		[ 'DATADICT' => [
+			[ 'CREATE' => [
+				'liberty_xref_type'   => "
+					xref_type C(32) PRIMARY,
+					package C(20) NOTNULL,
+					title C(64),
+					sort_order I2,
+					role_id I4,
+					type_href C(256)
+				",
+				'liberty_xref_source' => "
+					source C(20) PRIMARY,
+					package C(20) NOTNULL,
+					xref_type C(32),
+					cross_ref_title C(64),
+					multi I2,
+					role_id I4,
+					cross_ref_href C(256),
+					template C(32),
+					data X
+				",
+				'liberty_xref'        => "
+					xref_id I8 PRIMARY,
+					content_id I8 NOTNULL,
+					source C(20),
+					xorder I2,
+					xref I8,
+					xkey C(32),
+					xkey_ext C(250),
+					data X,
+					start_date T,
+					last_update_date T,
+					entry_date T,
+					end_date T
+				",
+			]],
+			[ 'CREATESEQUENCE' => [
+				[ 'liberty_xref_seq' ],
+			]],
+			[ 'CREATEINDEX' => [
+				[
+					'liberty_xref_content_idx'    => [ 'liberty_xref',        'content_id', null ],
+					'liberty_xref_source_pkg_idx' => [ 'liberty_xref_source', 'package',    null ],
+					'liberty_xref_type_pkg_idx'   => [ 'liberty_xref_type',   'package',    null ],
+				],
+			]],
+		]],
+	]
+);
diff --git a/includes/classes/LibertyXref.php b/includes/classes/LibertyXref.php
new file mode 100644
index 0000000..244f93a
--- /dev/null
+++ b/includes/classes/LibertyXref.php
@@ -0,0 +1,177 @@
+<?php
+/**
+ * @package liberty
+ * @subpackage classes
+ */
+
+namespace Bitweaver\Liberty;
+
+use Bitweaver\BitBase;
+use Bitweaver\BitDate;
+
+class LibertyXref extends LibertyBase {
+	public $mType;
+	public $mSource;
+	public $mXrefId;
+	public $mContentId;
+	public $mDate;
+
+	public function __construct( $iXrefId = NULL ) {
+		$this->mXrefId = NULL;
+		$this->mSource = NULL;
+		parent::__construct();
+		if( $iXrefId ) {
+			$this->load( $iXrefId );
+		}
+
+		$this->mDate = new BitDate();
+		$this->mDate->get_display_offset();
+	}
+
+	public function isValid() {
+		return $this->verifyId( $this->mXrefId );
+	}
+
+	public function load( $pXrefId = NULL ) {
+		if( BitBase::verifyId( $pXrefId ) ) {
+			$sql = "SELECT x.*, CASE
+					WHEN x.`xorder` = 0 THEN s.`cross_ref_title`
+					ELSE s.`cross_ref_title` || '-' || x.`xorder` END
+					AS source_title, s.`source`, s.`xref_type`,
+					CASE WHEN x.`start_date` IS NULL THEN 'y' ELSE 'n' END AS `ignore_start_date`,
+					CASE WHEN x.`end_date` IS NULL THEN 'y' ELSE 'n' END AS `ignore_end_date`,
+					s.`cross_ref_title` AS `template_title`, s.`template`
+					FROM `".BIT_DB_PREFIX."liberty_xref` x
+					JOIN `".BIT_DB_PREFIX."liberty_xref_source` s ON s.`source` = x.`source`
+					WHERE x.`xref_id` = ?
+					ORDER BY x.`xorder`";
+			$result = $this->mDb->getRow( $sql, [ $pXrefId ] );
+			if( $result['content_id'] ) {
+				$this->mXrefId    = $pXrefId;
+				$this->mContentId = $result['content_id'];
+				$this->mType      = $result['xref_type'];
+				$this->mSource    = $result['source'];
+				$this->mInfo['title']       = $result['source_title'];
+				$this->mInfo['format_guid'] = 'text';
+				unset( $result['source_title'] );
+				$this->mInfo['data'] = $result;
+			}
+		}
+	}
+
+	public function verify( &$pParamHash ) {
+		$pParamHash['xref_id'] = ( @$this->verifyId( $pParamHash['xref_id'] ) ) ? (int) $pParamHash['xref_id'] : null;
+
+		if( isset( $pParamHash['content_id'] ) ) {
+			$pParamHash['xref_store']['content_id'] = $pParamHash['content_id'];
+		}
+		if( isset( $pParamHash['source'] ) ) {
+			$pParamHash['xref_store']['source'] = $pParamHash['source'];
+		}
+
+		$pParamHash['xref_store']['xorder'] = 0;
+
+		if( isset( $pParamHash['fAddXref'] ) ) {
+			$pParamHash['xref_store']['source']     = isset( $pParamHash['Array_xref_type_list'] ) ? $pParamHash['Array_xref_type_list']['Array.source'] : $pParamHash['source'];
+			$pParamHash['xref_store']['content_id'] = $pParamHash['content_id'];
+			$sql  = "SELECT x.`multi` FROM `".BIT_DB_PREFIX."liberty_xref_source` x WHERE x.`source` = ?";
+			$next = $this->mDb->getOne( $sql, [ $pParamHash['xref_store']['source'] ] );
+			if( $next > 0 ) {
+				$sql  = "SELECT COALESCE( MAX(x.`xorder`) + 1, 1 ) FROM `".BIT_DB_PREFIX."liberty_xref` x WHERE x.`content_id` = ? AND x.`source` = ?";
+				$next = $this->mDb->getOne( $sql, [ $pParamHash['xref_store']['content_id'], $pParamHash['xref_store']['source'] ] );
+			}
+			$pParamHash['xref_store']['xorder'] = $next;
+		}
+
+		if( isset( $pParamHash['fStepXref'] ) ) {
+			$pParamHash['xref_store']['source']     = $this->mSource;
+			$pParamHash['xref_store']['xorder']     = $this->mInfo['data']['xorder'] + 1;
+			$pParamHash['xref_store']['content_id'] = $this->mContentId;
+			$pParamHash['start_date']               = $this->mDb->NOW();
+			$pParamHash['ignore_end_date']          = 'on';
+			$pParamHash['xref_store']['xref']       = 0;
+			$pParamHash['xref_store']['xkey']       = '';
+			$pParamHash['xref_store']['xkey_ext']   = '';
+			$pParamHash['xref_store']['data']       = '';
+		}
+
+		if( isset( $pParamHash['xref'] ) )     { $pParamHash['xref_store']['xref']     = $pParamHash['xref']; }
+		if( isset( $pParamHash['xkey'] ) )     { $pParamHash['xref_store']['xkey']     = $pParamHash['xkey']; }
+		if( isset( $pParamHash['xkey_ext'] ) ) { $pParamHash['xref_store']['xkey_ext'] = $pParamHash['xkey_ext']; }
+		if( isset( $pParamHash['edit'] ) )     { $pParamHash['xref_store']['data']     = $pParamHash['edit']; }
+
+		$pParamHash['xref_store']['last_update_date'] = $this->mDb->NOW();
+
+		if( !empty( $pParamHash['start_Month'] ) ) {
+			$dateString = $this->mDate->gmmktime(
+				$pParamHash['start_Hour'],
+				$pParamHash['start_Minute'],
+				$pParamHash['start_Second'] ?? 0,
+				$pParamHash['start_Month'],
+				$pParamHash['start_Day'],
+				$pParamHash['start_Year'],
+			);
+			$timestamp = $this->mDate->getUTCFromDisplayDate( $dateString );
+			if( $timestamp !== -1 ) { $pParamHash['start_date'] = $timestamp; }
+		}
+		if( !empty( $pParamHash['start_date'] ) )                                                    { $pParamHash['xref_store']['start_date'] = $pParamHash['start_date']; }
+		if( isset( $pParamHash['ignore_start_date'] ) && $pParamHash['ignore_start_date'] == 'on' ) { $pParamHash['xref_store']['start_date']  = ''; }
+
+		if( !empty( $pParamHash['end_Month'] ) ) {
+			$dateString = $this->mDate->gmmktime(
+				$pParamHash['end_Hour'],
+				$pParamHash['end_Minute'],
+				$pParamHash['end_Second'] ?? 0,
+				$pParamHash['end_Month'],
+				$pParamHash['end_Day'],
+				$pParamHash['end_Year'],
+			);
+			$timestamp = $this->mDate->getUTCFromDisplayDate( $dateString );
+			if( $timestamp !== -1 ) { $pParamHash['end_date'] = $timestamp; }
+		}
+		if( !empty( $pParamHash['end_date'] ) )                                                    { $pParamHash['xref_store']['end_date'] = $pParamHash['end_date']; }
+		if( isset( $pParamHash['ignore_end_date'] ) && $pParamHash['ignore_end_date'] == 'on' ) { $pParamHash['xref_store']['end_date']  = ''; }
+
+		return count( $this->mErrors ) == 0;
+	}
+
+	public function store( &$pParamHash = NULL ) {
+		if( $this->verify( $pParamHash ) ) {
+			$table = BIT_DB_PREFIX."liberty_xref";
+			$this->mDb->StartTrans();
+			if( isset( $pParamHash['xref_id'] ) ) {
+				$this->mDb->associateUpdate( $table, $pParamHash['xref_store'], [ "xref_id" => $pParamHash['xref_id'] ] );
+			} else {
+				$this->mXrefId                        = $this->mDb->GenID( 'liberty_xref_seq' );
+				$pParamHash['xref_id']                = $this->mXrefId;
+				$pParamHash['xref_store']['xref_id']  = $this->mXrefId;
+				$this->mDb->associateInsert( $table, $pParamHash['xref_store'] );
+			}
+			$this->load( $this->mXrefId );
+			$this->mDb->CompleteTrans();
+			return true;
+		}
+		return false;
+	}
+
+	public function stepXref( &$pParamHash = NULL ) {
+		if( isset( $pParamHash["expunge"] ) ) {
+			switch( $pParamHash["expunge"] ) {
+				case 2:
+					$pParamHash['end_date'] = $this->mDb->NOW();
+					$this->store( $pParamHash );
+					unset( $pParamHash['xref_id'] );
+					$pParamHash['fStepXref'] = 1;
+					break;
+				case 1:
+					$pParamHash['end_date'] = $this->mDb->NOW();
+					break;
+				default:
+					$pParamHash['ignore_end_date'] = 'on';
+					break;
+			}
+		}
+		$this->store( $pParamHash );
+		return true;
+	}
+}
diff --git a/includes/classes/LibertyXrefType.php b/includes/classes/LibertyXrefType.php
new file mode 100644
index 0000000..28ff4bb
--- /dev/null
+++ b/includes/classes/LibertyXrefType.php
@@ -0,0 +1,53 @@
+<?php
+/**
+ * @package liberty
+ * @subpackage classes
+ */
+
+namespace Bitweaver\Liberty;
+
+use Bitweaver\BitBase;
+
+class LibertyXrefType extends LibertyBase {
+
+	public function __construct() {
+		parent::__construct();
+	}
+
+	public static function getXrefTypeList( $pOptionHash = NULL ) {
+		global $gBitSystem;
+
+		$where     = '';
+		$bindVars  = [];
+
+		if( !empty( $pOptionHash['package'] ) ) {
+			$where     = " WHERE cxs.`package` = ? ";
+			$bindVars[] = $pOptionHash['package'];
+		}
+		if( !empty( $pOptionHash['active_role'] ) ) {
+			$where     = " WHERE cxs.`role_id` = ? ";
+			$bindVars[] = $pOptionHash['active_role'];
+		}
+		if( !empty( $pOptionHash['source'] ) ) {
+			$where     = " WHERE cxs.`source` = ? ";
+			$bindVars[] = $pOptionHash['source'];
+		}
+
+		$query = "SELECT cxs.*
+				  FROM `".BIT_DB_PREFIX."liberty_xref_source` cxs
+				  $where ORDER BY cxs.`xref_type`, cxs.`source`";
+
+		$result = $gBitSystem->mDb->query( $query, $bindVars );
+
+		$ret = [];
+		while( $res = $result->fetchRow() ) {
+			$res["num_entries"] = $gBitSystem->mDb->getOne(
+				"SELECT COUNT(*) FROM `".BIT_DB_PREFIX."liberty_xref` WHERE `source` = ?",
+				[ $res["source"] ]
+			);
+			$ret[] = $res;
+		}
+
+		return $ret;
+	}
+}