stock: rework KitlockerAssemblies import for new CSV layout

New columns: Title, KLID, Data, KLSGL, KL3M, Group. Match existing
assemblies by KLID xref (not title) so tidier titles are applied on
re-run; wipe and replace old kitlocker xrefs each time.

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

1 files changed, 79 insertions, 48 deletions

diff --git a/import/load_kitlocker_assemblies.php b/import/load_kitlocker_assemblies.php
index edea9d6..ecdb13e 100644
--- a/import/load_kitlocker_assemblies.php
+++ b/import/load_kitlocker_assemblies.php
@@ -2,11 +2,13 @@
 /**
  * Phase 2: Import KitlockerAssemblies.csv under their KitLocker group assemblies.
  *
- * CSV columns: Title, Description, SGL, KL3M, Group
+ * CSV columns: Title, KLID, Data, KLSGL, KL3M, Group
  * Group is numeric — 'G' is prepended to match the KLID xref stored by
- * load_kitlocker_groups.php. SGL and KL3M are stored as xref values.
+ * load_kitlocker_groups.php.
+ *
+ * Existing assemblies are matched by KLID xref so the title can be updated to
+ * a tidier version. Old kitlocker xrefs are wiped and replaced on each run.
  *
- * Already-existing assemblies (matched by title) are skipped.
  * Run load_kitlocker_groups.php first.
  *
  * @package stock
@@ -23,16 +25,20 @@ $gBitSystem->verifyPermission( 'p_stock_admin' );
 
 require_once STOCK_PKG_CLASS_PATH.'StockAssembly.php';
 
-$csvFile = __DIR__.'/data/KitlockerAssemblies.csv';
-$loaded  = $skipped = 0;
-$errors  = [];
+$csvFile  = __DIR__.'/data/KitlockerAssemblies.csv';
+$loaded   = $skipped = $updated = 0;
+$errors   = [];
 
-// Build KLID → content_id map from xref table
+// KLID → content_id map for group assemblies (G1, G2, …)
 $klidMap = $gBitDb->getAssoc(
-	"SELECT `xkey`, `content_id` FROM `".BIT_DB_PREFIX."liberty_xref` WHERE `item`=?",
-	[ 'KLID' ]
+	"SELECT lx.xkey, lx.content_id FROM `".BIT_DB_PREFIX."liberty_xref` lx
+	 INNER JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id=lx.content_id
+	 WHERE lx.item=? AND lc.content_type_guid=?",
+	[ 'KLID', STOCKASSEMBLY_CONTENT_TYPE_GUID ]
 );
 
+$kitlockerXrefItems = [ 'KLID', 'KLPR', 'KLSGL', 'KL3M', 'KLURL', 'KLSGL' ];
+
 if( empty( $klidMap ) ) {
 	$errors[] = 'No KLID xref entries found — run load_kitlocker_groups.php first.';
 } elseif( !file_exists( $csvFile ) ) {
@@ -42,57 +48,81 @@ if( empty( $klidMap ) ) {
 	$rowNum = 0;
 	while( ($cols = fgetcsv( $fh, 0, ',', '"', '' )) !== false ) {
 		$rowNum++;
-		if( $rowNum === 1 ) continue; // header: Title, Description, SGL, KL3M, Group
+		if( $rowNum === 1 ) continue; // header: Title, KLID, Data, KLSGL, KL3M, Group
 
-		$title       = trim( $cols[0] ?? '' );
-		$description = trim( $cols[1] ?? '' );
-		$sgl         = trim( $cols[2] ?? '' );
-		$kl3m        = trim( $cols[3] ?? '' );
-		$groupNum    = trim( $cols[4] ?? '' );
-		$klid        = 'G'.$groupNum;
+		$title    = trim( $cols[0] ?? '' );
+		$klid     = trim( $cols[1] ?? '' );
+		$data     = trim( $cols[2] ?? '' );
+		$klsgl    = trim( $cols[3] ?? '' );
+		$kl3m     = trim( $cols[4] ?? '' );
+		$groupNum = trim( $cols[5] ?? '' );
+		$groupKey = 'G'.$groupNum;
 
-		if( $title === '' || $groupNum === '' ) {
+		if( $title === '' || $klid === '' || $groupNum === '' ) {
 			$skipped++;
 			continue;
 		}
 
-		if( !isset( $klidMap[$klid] ) ) {
-			$errors[] = "Row $rowNum: '$title' — group '$klid' not in KLID map, skipped.";
+		if( !isset( $klidMap[$groupKey] ) ) {
+			$errors[] = "Row $rowNum: '$title' — group '$groupKey' not in KLID map, skipped.";
 			$skipped++;
 			continue;
 		}
 
-		// Skip if assembly already exists by title
-		if( $gBitDb->getOne(
-			"SELECT lc.`content_id` FROM `".BIT_DB_PREFIX."liberty_content` lc
-			 WHERE lc.`content_type_guid`='".STOCKASSEMBLY_CONTENT_TYPE_GUID."' AND lc.`title` = ?",
-			[ $title ]
-		) ) {
-			$skipped++;
-			continue;
-		}
+		$groupContentId = (int)$klidMap[$groupKey];
 
-		$assembly = new StockAssembly();
-		$pHash = [ 'title' => $title, 'edit' => $description, 'format_guid' => 'bithtml' ];
-		if( !$assembly->store( $pHash ) ) {
-			$errors[] = "Row $rowNum: failed to create '$title'";
-			$skipped++;
-			continue;
-		}
+		// Find existing assembly by its own KLID xref
+		$contentId = (int)$gBitDb->getOne(
+			"SELECT lx.content_id FROM `".BIT_DB_PREFIX."liberty_xref` lx
+			 INNER JOIN `".BIT_DB_PREFIX."liberty_content` lc ON lc.content_id=lx.content_id
+			 WHERE lx.item=? AND lx.xkey=? AND lc.content_type_guid=?",
+			[ 'KLID', $klid, STOCKASSEMBLY_CONTENT_TYPE_GUID ]
+		);
 
-		$contentId      = $assembly->mContentId;
-		$groupContentId = (int)$klidMap[$klid];
+		if( $contentId ) {
+			// Update title and description
+			$assembly = new StockAssembly( null, $contentId );
+			$assembly->load();
+			$pHash = [ 'content_id' => $contentId, 'title' => $title, 'edit' => $data, 'format_guid' => 'bithtml' ];
+			$assembly->store( $pHash );
 
-		// Add as child of the group assembly
-		$gBitDb->getOne(
-			"INSERT INTO `".BIT_DB_PREFIX."stock_assembly_map`
-			 (`assembly_content_id`, `item_content_id`, `item_position`) VALUES (?,?,NULL)",
-			[ $groupContentId, $contentId ]
-		);
+			// Wipe old kitlocker xrefs
+			$gBitDb->query(
+				"DELETE FROM `".BIT_DB_PREFIX."liberty_xref`
+				 WHERE content_id=? AND item IN ('KLID','KLPR','KLSGL','KL3M','KLURL')",
+				[ $contentId ]
+			);
+			$updated++;
+		} else {
+			// Create new assembly
+			$assembly = new StockAssembly();
+			$pHash = [ 'title' => $title, 'edit' => $data, 'format_guid' => 'bithtml' ];
+			if( !$assembly->store( $pHash ) ) {
+				$errors[] = "Row $rowNum: failed to create '$title'";
+				$skipped++;
+				continue;
+			}
+			$contentId = $assembly->mContentId;
+
+			// Link to parent group
+			$alreadyLinked = (int)$gBitDb->getOne(
+				"SELECT COUNT(*) FROM `".BIT_DB_PREFIX."stock_assembly_map`
+				 WHERE assembly_content_id=? AND item_content_id=?",
+				[ $groupContentId, $contentId ]
+			);
+			if( !$alreadyLinked ) {
+				$gBitDb->query(
+					"INSERT INTO `".BIT_DB_PREFIX."stock_assembly_map`
+					 (assembly_content_id, item_content_id, item_position) VALUES (?,?,NULL)",
+					[ $groupContentId, $contentId ]
+				);
+			}
+			$loaded++;
+		}
 
-		// Store SGL and KL3M xrefs
-		foreach( [ 'SGL' => $sgl, 'KL3M' => $kl3m ] as $item => $value ) {
-			if( $value !== '' && is_numeric( $value ) ) {
+		// Store kitlocker xrefs
+		foreach( [ 'KLID' => $klid, 'KLSGL' => $klsgl, 'KL3M' => $kl3m ] as $item => $value ) {
+			if( $value !== '' ) {
 				$xrefId = $gBitDb->GenID( 'liberty_xref_seq' );
 				$gBitDb->associateInsert( BIT_DB_PREFIX.'liberty_xref', [
 					'xref_id'          => $xrefId,
@@ -105,14 +135,15 @@ if( empty( $klidMap ) ) {
 			}
 		}
 
-		$loaded++;
+		// Keep contentId in klidMap for subsequent lookups within this run
+		$klidMap[$klid] = $contentId;
 	}
 	fclose( $fh );
 }
 
 $gBitSmarty->assign( 'loaded',   $loaded );
 $gBitSmarty->assign( 'skipped',  $skipped );
-$gBitSmarty->assign( 'deleted',  0 );
+$gBitSmarty->assign( 'deleted',  $updated );
 $gBitSmarty->assign( 'errors',   $errors );
 $gBitSmarty->assign( 'csvFile',  $csvFile );
 $gBitSmarty->assign( 'movement', null );