<?php
// Dry-run by default. APPLY=1 performs the approved migration.
declare(strict_types=1);
$apply=getenv('APPLY')==='1';
$simulate=getenv('SIMULATE')==='1';
if($simulate)$apply=true;
$sc=include '/workspace/wwwroot/tnxl/project/application/config/prod/tnxl/database.php';
$dc=include '/workspace/wwwroot/tnxlgstyj/project/application/config/prod/tnxlgstyj/database.php';
function conn($c){return new PDO('mysql:host='.$c['hostname'].';dbname='.$c['database'].';charset=utf8mb4',$c['username'],$c['password'],[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC]);}
function all($p,$q,$a=[]){$s=$p->prepare($q);$s->execute($a);return $s->fetchAll();}
function one($p,$q,$a=[]){$s=$p->prepare($q);$s->execute($a);return $s->fetchColumn();}
function ins($p,$t,$r){unset($r['id']);$k=array_keys($r);$q=$p->prepare('INSERT INTO `'.$t.'` (`'.implode('`,`',$k).'`) VALUES ('.implode(',',array_fill(0,count($k),'?')).')');$q->execute(array_values($r));return(int)$p->lastInsertId();}
function uid(){ $x=strtoupper(bin2hex(random_bytes(16)));return substr($x,0,8).'-'.substr($x,8,4).'-'.substr($x,12,4).'-'.substr($x,16,4).'-'.substr($x,20,12); }
function unitKey($v){$v=trim((string)$v);$v=str_replace(['×',' ','^'],'',$v);return strtolower($v===''?'无单位':$v);}
function numSame($actual,$expected,$nullAsZero=false){if($nullAsZero){$actual=$actual??0;$expected=$expected??0;}elseif(($actual===null)!==($expected===null))return false;elseif($actual===null)return true;return abs((float)$actual-(float)$expected)<0.0005;}
function assertSemanticChain($d,$t,$k,$r,$cateUuid,$unitUuid,$hardwareUuid,$prefix=''){$rr=all($d,'SELECT * FROM tn_equipment_relation WHERE type=? AND k=? AND v=? AND is_del=0 AND is_norm=? AND COALESCE(unit,\'\')=COALESCE(?,\'\')',[$t,$k,$r['v'],(int)$r['is_norm'],$r['unit']]);if(count($rr)!==1||!numSame($rr[0]['upper_limit'],$r['upper_limit'])||!numSame($rr[0]['lower_limit'],$r['lower_limit']))throw new RuntimeException($prefix."relation content $t/$k");$nn=all($d,'SELECT n.* FROM tn_norm n JOIN tn_unit u ON u.uuid=n.unit_uuid AND u.del_flag=0 JOIN tn_norm_cate nc ON nc.norm_uuid=n.uuid AND nc.cate_uuid=? AND nc.del_flag=0 JOIN tn_cate c ON c.uuid=nc.cate_uuid AND c.del_flag=0 AND c.status=1 JOIN tn_equipment_hardware h ON h.uuid=n.equipment_uuid AND h.device_id=n.device_model_id WHERE n.del_flag=0 AND n.status=1 AND n.type=7 AND n.device_model_id=? AND n.en_name=? AND n.unit_uuid=? AND n.equipment_uuid=?',[$cateUuid,(string)$t,$k,$unitUuid,$hardwareUuid]);if(count($nn)!==1||!numSame($nn[0]['male_lower_limit'],$r['lower_limit'],true)||!numSame($nn[0]['male_upper_limit'],$r['upper_limit'],true)||!numSame($nn[0]['female_lower_limit'],$r['lower_limit'],true)||!numSame($nn[0]['female_upper_limit'],$r['upper_limit'],true))throw new RuntimeException($prefix."norm chain content $t/$k");}
$s=conn($sc);$d=conn($dc);
// Serialize apply runs. The lock is connection-scoped and is automatically
// released if this process exits with an exception.
$lockName='tnxlgstyj:functional-device-migration:v1';
if($apply && (int)one($d,'SELECT GET_LOCK(?,10)',[$lockName])!==1)throw new RuntimeException('could not acquire migration lock');

$modelMap=[
 'KX21N'=>[9,9],'BC-5385CRP'=>[1,1],'ADVIA® Centaur CP'=>[4,4],
 'BS-380'=>[2,2],'Clinitek Status'=>[3,3],'NX500i'=>[10,10],
];
$specimens=all($s,"SELECT s.*,h.name equipment_name,h.model_num hardware_model FROM tn_specimen s JOIN tn_equipment_hardware h ON h.uuid=s.equipment_uuid WHERE s.is_del=0 AND s.equipment_uuid IS NOT NULL ORDER BY s.id");
if(count($specimens)!==57)throw new RuntimeException('expected 57 specimens, got '.count($specimens));
$ids=array_map('intval',array_column($specimens,'id'));
$unknownModels=[];foreach($specimens as$r)if(!isset($modelMap[$r['equipment_no']]))$unknownModels[$r['equipment_no']]=1;
if($unknownModels)throw new RuntimeException('unknown source equipment models: '.implode(',',array_keys($unknownModels)));

$ath=all($d,"SELECT s.uuid,s.code,s.name,s.sex,s.birthday,s.height,s.weight,sd.department_uuid FROM tn_staff s JOIN tn_staff_department sd ON sd.staff_uuid=s.uuid JOIN tn_department dep ON dep.uuid=sd.department_uuid WHERE dep.name='演示运动队' AND s.name REGEXP '^运动员 [0-9]+$' AND s.del_flag=0 ORDER BY CAST(SUBSTRING_INDEX(s.name,' ',-1) AS UNSIGNED)");
if(count($ath)!==10)throw new RuntimeException('target athletes != 10');
$targetHw=all($d,'SELECT h.id,h.uuid,h.name,h.model_num,c.group_type FROM tn_equipment_hardware h JOIN tn_equipment_hardware_cate c ON c.uuid=h.cate_uuid WHERE h.is_show=1');
$hw=[];$hwBad=[];
foreach($specimens as $r){$m=array_values(array_filter($targetHw,function($x)use($r){return $x['name']===$r['equipment_name']&&($x['model_num']===$r['hardware_model']||$x['model_num']===$r['equipment_no']);}));if(count($m)===1)$hw[$r['id']]=$m[0];else $hwBad[]=['id'=>$r['id'],'device'=>$r['equipment_name'],'model'=>$r['equipment_no'],'matches'=>count($m)];}
if($hwBad)throw new RuntimeException('unresolved/ambiguous hardware: '.json_encode($hwBad,JSON_UNESCAPED_UNICODE));

// Build a semantic relation map. Existing target rows win; missing metrics inherit
// source display metadata and receive an unused target k21..k230 slot. All 96
// semantics used by this migration are intentionally norm-enabled.
$rels=[];$used=[];$relAdds=[];$metricBad=[];$targetRelationDedups=[];$relationSoftDeletes=[];
foreach([1,2,3,4,9,10,21,7100] as $t){$groups=[];foreach(all($d,'SELECT * FROM tn_equipment_relation WHERE type=? AND is_del=0 ORDER BY id',[$t]) as$r){$groups[$r['k']][]=$r;$used[$t][$r['v']]=1;}foreach($groups as$k=>$rows){$base=$rows[0];if(count($rows)>1){$same=true;foreach(array_slice($rows,1)as$dup)foreach(['unit','upper_limit','lower_limit','is_norm']as$field)if((string)($dup[$field]??'')!==(string)($base[$field]??'')){$same=false;break 2;}if(!$same){$metricBad[]=['reason'=>'conflicting duplicate target relation','type'=>$t,'metric'=>$k,'ids'=>array_map('intval',array_column($rows,'id'))];continue;}$drop=array_map('intval',array_column(array_slice($rows,1),'id'));$targetRelationDedups[]=['type'=>$t,'metric'=>$k,'selected_id'=>(int)$base['id'],'soft_delete_ids'=>$drop,'equal_fields'=>['unit','upper_limit','lower_limit','is_norm']];foreach($drop as$id)$relationSoftDeletes[$id]=1;}$rels[$t][$k]=$base;}}
$metricRows=all($s,"SELECT sp.equipment_no,di.test_item,MAX(di.test_xmmc) test_xmmc,MAX(di.test_dw) unit,MAX(di.test_cksx) upper_limit,MAX(di.test_ckxx) lower_limit FROM tn_specimen sp JOIN tn_detection_info di ON di.specimen_no=sp.specimen_no AND di.test_date=sp.test_date AND di.test_time=sp.test_time WHERE sp.id IN (".implode(',',$ids).") AND di.is_del=0 AND di.test_item<>'' GROUP BY sp.equipment_no,di.test_item ORDER BY sp.equipment_no,di.test_item");
$relationUpdates=[];$relationConflicts=[];$sourceRelationDedups=[];$semantic=[];
foreach($metricRows as$m){list($old,$t)=$modelMap[$m['equipment_no']];$semantic[$t][$m['test_item']][implode('|',[$m['unit'],$m['upper_limit'],$m['lower_limit']])][]=$m['equipment_no'];}
foreach($semantic as$t=>$items)foreach($items as$k=>$variants)if(count($variants)>1)$relationConflicts[]=['type'=>$t,'metric'=>$k,'variants'=>$variants];
$semanticCount=0;foreach($semantic as$items)$semanticCount+=count($items);
foreach($metricRows as $m){if(!isset($modelMap[$m['equipment_no']])){$metricBad[]=$m;continue;}list($old,$t)=$modelMap[$m['equipment_no']];$srcRel=all($s,'SELECT * FROM tn_equipment_relation WHERE type=? AND k=? AND is_del=0 ORDER BY id',[$old,$m['test_item']]);if(count($srcRel)>1){$base=$srcRel[0];$same=true;foreach(array_slice($srcRel,1)as$dup)foreach(['unit','upper_limit','lower_limit','is_norm']as$field)if((string)($dup[$field]??'')!==(string)($base[$field]??'')){$same=false;break 2;}if(!$same){$metricBad[]=['reason'=>'conflicting duplicate source relation','type'=>$old,'metric'=>$m['test_item'],'ids'=>array_map('intval',array_column($srcRel,'id'))];continue;}$sourceRelationDedups[]=['type'=>$old,'metric'=>$m['test_item'],'selected_id'=>(int)$base['id'],'deduplicated_ids'=>array_map('intval',array_column(array_slice($srcRel,1),'id')),'equal_fields'=>['unit','upper_limit','lower_limit','is_norm']];$srcRel=[$base];}$meta=$srcRel?$srcRel[0]:[];$desired=1;if(isset($rels[$t][$m['test_item']])){$current=$rels[$t][$m['test_item']];$desiredSlot=$current['v'];if(preg_match('/^k([0-9]+)$/',$desiredSlot,$vm)&&(int)$vm[1]<=20){$desiredSlot=null;for($i=21;$i<=230;$i++){if(!isset($used[$t]['k'.$i])){$desiredSlot='k'.$i;break;}}if(!$desiredSlot)throw new RuntimeException('no safe extend relation slot for '.$t);$used[$t][$desiredSlot]=1;}$changed=(int)$current['is_norm']!==$desired||$current['v']!==$desiredSlot;if($changed){$relationUpdates[$t.':'.$m['test_item']]=['type'=>$t,'k'=>$m['test_item'],'v'=>$desiredSlot,'is_norm'=>$desired];$rels[$t][$m['test_item']]['v']=$desiredSlot;$rels[$t][$m['test_item']]['is_norm']=$desired;}continue;}$slot=null;for($i=21;$i<=230;$i++){if(!isset($used[$t]['k'.$i])){$slot='k'.$i;break;}}if(!$slot)throw new RuntimeException('no relation slot for '.$t);$used[$t][$slot]=1;$r=['type'=>$t,'k'=>$m['test_item'],'v'=>$slot,'note'=>$meta['note']??$m['test_xmmc'],'note_en'=>$meta['note_en']??'','unit'=>$meta['unit']??$m['unit'],'upper_limit'=>$meta['upper_limit']??$m['upper_limit'],'lower_limit'=>$meta['lower_limit']??$m['lower_limit'],'is_del'=>0,'is_norm'=>$desired,'create_time'=>date('Y-m-d H:i:s'),'update_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration'];$rels[$t][$r['k']]=$r;$relAdds[]=$r;}

// Analyse chain: unit -> norm -> norm_cate -> visible cate, with norm bound to
// the unique target hardware for its independent relation type.
$typeHardware=[];$typeHardwareSets=[];foreach($specimens as$sp){$t=$modelMap[$sp['equipment_no']][1];$typeHardwareSets[$t][$hw[$sp['id']]['uuid']]=$hw[$sp['id']];}
$typeHardwareConflicts=[];foreach($typeHardwareSets as$t=>$set){if(count($set)!==1)$typeHardwareConflicts[]=['type'=>$t,'hardware'=>array_keys($set)];else$typeHardware[$t]=reset($set);}
$units=[];foreach(all($d,'SELECT * FROM tn_unit WHERE del_flag=0')as$u)$units[unitKey($u['cn_name']?:$u['en_name'])]=$u['uuid'];$unitAdds=[];
foreach($semantic as$t=>$items)foreach($items as$k=>$unused){if(!(int)$rels[$t][$k]['is_norm'])continue;$key=unitKey($rels[$t][$k]['unit']);if(!isset($units[$key])){$units[$key]=uid();$unitAdds[$key]=['uuid'=>$units[$key],'cn_name'=>$rels[$t][$k]['unit']?:'无单位','en_name'=>$rels[$t][$k]['unit']?:'none','order_no'=>0,'status'=>1,'del_flag'=>0,'demo'=>'机能设备迁移单位','create_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration'];}}
$cateRows=all($d,"SELECT * FROM tn_cate WHERE name='机能设备指标' AND type=7 AND del_flag=0");$cateBad=count($cateRows)>1;$cateAdd=null;$cateUuid=$cateRows?$cateRows[0]['uuid']:uid();if(!$cateRows)$cateAdd=['uuid'=>$cateUuid,'name'=>'机能设备指标','edit_flag'=>1,'type'=>7,'order_no'=>0,'norm_quantity'=>0,'status'=>1,'del_flag'=>0,'builtin'=>0,'demo'=>'机能设备指标','use_num'=>0,'create_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration'];
$normAdds=[];$normUpdates=[];$normCateAdds=[];$normBad=[];
foreach($semantic as$t=>$items)foreach($items as$k=>$unused){if(!(int)$rels[$t][$k]['is_norm'])continue;if(!isset($typeHardware[$t])){$normBad[]=['type'=>$t,'metric'=>$k,'reason'=>'hardware not unique'];continue;}$existing=all($d,'SELECT * FROM tn_norm WHERE del_flag=0 AND device_model_id=? AND en_name=?',[(string)$t,$k]);if(count($existing)>1){$normBad[]=['type'=>$t,'metric'=>$k,'matches'=>count($existing)];continue;}$normUuid=$existing?$existing[0]['uuid']:uid();$r=$rels[$t][$k];$desiredNorm=['unit_uuid'=>$units[unitKey($r['unit'])],'male_lower_limit'=>$r['lower_limit']??0,'male_upper_limit'=>$r['upper_limit']??0,'female_lower_limit'=>$r['lower_limit']??0,'female_upper_limit'=>$r['upper_limit']??0,'equipment_uuid'=>$typeHardware[$t]['uuid'],'type'=>7,'status'=>1];if(!$existing){$normAdds[]=['uuid'=>$normUuid,'cn_name'=>$r['note']?:$k,'en_name'=>$k,'pinyin'=>'','unit_uuid'=>$desiredNorm['unit_uuid'],'edit_flag'=>1,'status'=>1,'del_flag'=>0,'value_type'=>1,'float_digit'=>2,'male_symbol'=>0,'male_lower_limit'=>$desiredNorm['male_lower_limit'],'male_upper_limit'=>$desiredNorm['male_upper_limit'],'female_symbol'=>0,'female_lower_limit'=>$desiredNorm['female_lower_limit'],'female_upper_limit'=>$desiredNorm['female_upper_limit'],'use_num'=>0,'device_model_id'=>(string)$t,'equipment_uuid'=>$desiredNorm['equipment_uuid'],'create_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration','type'=>7,'valnum'=>0,'ascOrDesc'=>0];}else{$normUpdates[$normUuid]=$desiredNorm;}if(!(int)one($d,'SELECT COUNT(*) FROM tn_norm_cate WHERE cate_uuid=? AND norm_uuid=? AND del_flag=0',[$cateUuid,$normUuid]))$normCateAdds[]=['cate_uuid'=>$cateUuid,'del_flag'=>0,'norm_uuid'=>$normUuid,'use_num'=>0,'create_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration'];}

$min=strtotime(min(array_column($specimens,'test_time')));$max=strtotime(max(array_column($specimens,'test_time')));$toMin=strtotime('2026-05-01 08:00:00');$toMax=strtotime('2026-08-31 18:00:00');
function shiftF($v){global$min,$max,$toMin,$toMax;$x=strtotime($v);return(int)round($toMin+(($x-$min)/max(1,$max-$min))*($toMax-$toMin));}
$plans=[];$sourceRows=0;$activeRows=0;$deletedRows=0;$activeDuplicateRows=0;$realValues=0;$unmapped=[];$slotCollisions=[];$assignedMetricKeys=[];$assignedSlotKeys=[];
foreach($specimens as $i=>$sp){list($old,$type)=$modelMap[$sp['equipment_no']];$a=$ath[$i%10];$dt=shiftF($sp['test_time']);$full=date('Y-m-d H:i:s',$dt);$main=['business'=>'tnxlgstyj','equipment_id'=>$hw[$sp['id']]['id'],'equipment_mark'=>$sp['equipment_no'],'relation_type'=>$type,'department_uuid'=>$a['department_uuid'],'staff_uuid'=>$a['uuid'],'staff_height'=>$a['height'],'staff_weight'=>$a['weight'],'staff_age'=>(int)date('Y',$dt)-(int)substr($a['birthday'],0,4),'date'=>date('Y-m-d',$dt),'record_time'=>$full,'datetimes'=>date('YmdHis',$dt),'reference'=>'MIG-FUNC-'.$sp['id'],'k5'=>date('Y-m-d',$dt),'k6'=>$sp['specimen_no'],'k7'=>$a['uuid'],'k8'=>$a['name'],'k9'=>$full,'k10'=>'migration','k11'=>'source:tnxl_biaozhun/specimen/'.$sp['id'],'step'=>1,'firstbeatAccountId'=>'','is_del'=>0,'create_time'=>$full,'update_time'=>$full,'create_by'=>'migration'];$ext=[];$trace=[];$activeDuplicateTrace=[];$valueSlots=[];$details=all($s,'SELECT * FROM tn_detection_info WHERE specimen_no=? AND test_date=? AND test_time=? ORDER BY id',[$sp['specimen_no'],$sp['test_date'],$sp['test_time']]);foreach($details as$x){$sourceRows++;if((int)$x['is_del']===1){$deletedRows++;$trace[]=['id'=>(int)$x['id'],'is_del'=>1,'item'=>$x['test_item'],'value'=>$x['test_value']];continue;}$activeRows++;$r=$rels[$type][$x['test_item']]??null;if(!$r){$unmapped[]=['specimen_id'=>$sp['id'],'item'=>$x['test_item']];continue;}$v=$r['v'];$metricKey=(int)$sp['id'].':'.$x['test_item'];$slotKey=(int)$sp['id'].':'.$v;if(isset($assignedMetricKeys[$metricKey])){$activeDuplicateRows++;$activeDuplicateTrace[]=['id'=>(int)$x['id'],'is_del'=>0,'item'=>$x['test_item'],'value'=>$x['test_value'],'kept_slot'=>$v];continue;}if(isset($assignedSlotKeys[$slotKey])||array_key_exists($v,$main)){$slotCollisions[]=['specimen_id'=>(int)$sp['id'],'metric'=>$x['test_item'],'slot'=>$v,'detection_id'=>(int)$x['id']];continue;}$assignedMetricKeys[$metricKey]=1;$assignedSlotKeys[$slotKey]=1;$valueSlots[]=$v;$realValues++;if(preg_match('/^k([0-9]+)$/',$v,$mm)&&$mm[1]<=20)$main[$v]=$x['test_value'];else$ext[$v]=$x['test_value'];}$ext['other_info']=json_encode(['source_specimen_id'=>(int)$sp['id'],'active_duplicate_rows'=>$activeDuplicateTrace,'deleted_detection_rows'=>$trace],JSON_UNESCAPED_UNICODE);$plans[]=['source_id'=>(int)$sp['id'],'type'=>$type,'athlete'=>$a['name'],'main'=>$main,'extend'=>$ext,'value_slots'=>$valueSlots,'detail_count'=>count($details)];}

// Every dynamic relation slot must exist in the correct physical table before
// backup or transaction work begins.
$mainCols=array_flip(array_column(all($d,"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tn_equipment_result'"),'COLUMN_NAME'));
$extCols=array_flip(array_column(all($d,"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tn_equipment_result_extend'"),'COLUMN_NAME'));
$schemaMissing=[];foreach($semantic as$t=>$items)foreach($items as$k=>$unused){$v=$rels[$t][$k]['v'];if(!preg_match('/^k([0-9]+)$/',$v,$mm)){$schemaMissing[]=$v;continue;}$cols=((int)$mm[1]<=20)?$mainCols:$extCols;if(!isset($cols[$v]))$schemaMissing[]=$v;}$schemaMissing=array_values(array_unique($schemaMissing));

$before=['result'=>(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result WHERE reference LIKE 'MIG-FUNC-%'"),'extend'=>(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result_extend e JOIN tn_equipment_result r ON r.id=e.equipment_result_id WHERE r.reference LIKE 'MIG-FUNC-%'")];
$typeCounts=[];foreach($plans as$p)$typeCounts[$p['type']]=($typeCounts[$p['type']]??0)+1;

$athCounts=array_count_values(array_column($plans,'athlete'));
$hardwareTypePlan=[];$hardwareTypeConflicts=[];foreach($specimens as$sp){$id=(int)$hw[$sp['id']]['id'];$t=$modelMap[$sp['equipment_no']][1];if(isset($hardwareTypePlan[$id])&&$hardwareTypePlan[$id]!==$t)$hardwareTypeConflicts[]=['hardware_id'=>$id,'types'=>[$hardwareTypePlan[$id],$t]];$hardwareTypePlan[$id]=$t;}
$relationUpdateExisting=[];$relationMutationTypes=array_merge(array_column($relationUpdates,'type'),array_column($targetRelationDedups,'type'));foreach(array_values(array_unique($relationMutationTypes))as$t){$count=(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result WHERE relation_type=? AND is_del=0 AND (reference IS NULL OR reference NOT LIKE 'MIG-FUNC-%')",[$t]);if($count)$relationUpdateExisting[(string)$t]=$count;}
$summary=['mode'=>$apply?'apply':'dry-run','source_ids'=>$ids,'records'=>count($plans),'extends'=>count($plans),'semantic_relations'=>$semanticCount,'type_counts'=>$typeCounts,'source_detection_total'=>$sourceRows,'active_values_total'=>$activeRows,'slot_values'=>$realValues,'active_duplicate_trace_rows'=>$activeDuplicateRows,'deleted_trace_rows'=>$deletedRows,'slot_collisions'=>$slotCollisions,'athlete_counts'=>$athCounts,'relation_adds'=>count($relAdds),'relation_updates'=>array_values($relationUpdates),'source_relation_dedup'=>$sourceRelationDedups,'target_relation_dedup'=>$targetRelationDedups,'relation_update_existing_results'=>$relationUpdateExisting,'relation_conflicts'=>$relationConflicts,'metric_bad'=>$metricBad,'unit_adds'=>array_values($unitAdds),'cate_add'=>$cateAdd,'norm_adds'=>count($normAdds),'norm_updates'=>count($normUpdates),'norm_cate_adds'=>count($normCateAdds),'norm_conflicts'=>$normBad,'hardware_device_id_plan'=>$hardwareTypePlan,'hardware_type_conflicts'=>$hardwareTypeConflicts,'type_hardware_conflicts'=>$typeHardwareConflicts,'schema_missing_columns'=>$schemaMissing,'hardware_unresolved'=>$hwBad,'metric_unmapped'=>$unmapped,'target_existing'=>$before,'date_range'=>[date('c',$toMin),date('c',$toMax)]];echo json_encode($summary,JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES),PHP_EOL;
$bad=$hwBad||$metricBad||$unmapped||$relationConflicts||$relationUpdateExisting||$normBad||$cateBad||$hardwareTypeConflicts||$typeHardwareConflicts||$schemaMissing||$slotCollisions||count($hardwareTypePlan)!==6||$semanticCount!==96||count($normAdds)+count($normUpdates)!==96||$sourceRows!==914||$activeRows!==857||$realValues!==848||$activeDuplicateRows!==9||$realValues+$activeDuplicateRows!==857||$deletedRows!==57||count($assignedMetricKeys)!==848||count($assignedSlotKeys)!==848||count($athCounts)!==10;
if(!$apply)exit($bad?2:0);if($bad)throw new RuntimeException('precondition failed');

// Idempotent rerun: accept only a byte-for-byte equivalent prior migration;
// partial or divergent state remains a hard failure.
if($before['result']||$before['extend']){$same=$before['result']===57&&$before['extend']===57;foreach($plans as$p){$rows=all($d,'SELECT * FROM tn_equipment_result WHERE reference=?',[$p['main']['reference']]);if(count($rows)!==1){$same=false;break;}$row=$rows[0];foreach($p['main']as$k=>$v)if($k!=='create_time'&&$k!=='update_time'&&(string)($row[$k]??'')!==(string)$v){$same=false;break 2;}$er=all($d,'SELECT * FROM tn_equipment_result_extend WHERE equipment_result_id=?',[$row['id']]);if(count($er)!==1){$same=false;break;}$er=$er[0];foreach($p['extend']as$k=>$v)if((string)($er[$k]??'')!==(string)$v){$same=false;break 2;}}if(!$same)throw new RuntimeException('existing migration is partial or divergent');foreach($hardwareTypePlan as$id=>$type)if((string)one($d,'SELECT device_id FROM tn_equipment_hardware WHERE id=?',[$id])!==(string)$type)throw new RuntimeException('noop hardware chain mismatch id='.$id);foreach($semantic as$t=>$items)foreach($items as$k=>$unused){$r=$rels[$t][$k];assertSemanticChain($d,$t,$k,$r,$cateUuid,$units[unitKey($r['unit'])],$typeHardware[$t]['uuid'],'noop ');}one($d,'SELECT RELEASE_LOCK(?)',[$lockName]);echo json_encode(['applied'=>false,'noop'=>true,'records'=>57,'extends'=>57,'semantic_chains'=>96,'hardware_chains'=>count($hardwareTypePlan)],JSON_PRETTY_PRINT),PHP_EOL;exit(0);}

$suffix=date('Ymd_His');if(!$simulate)foreach(['tn_equipment_result','tn_equipment_result_extend','tn_equipment_relation','tn_equipment_hardware','tn_norm','tn_unit','tn_cate','tn_norm_cate']as$t){$b='migbak_'.$suffix.'_'.substr($t,3);$d->exec("CREATE TABLE `$b` LIKE `$t`");$d->exec("INSERT INTO `$b` SELECT * FROM `$t`");}
$d->beginTransaction();try{
 foreach($unitAdds as$u)ins($d,'tn_unit',$u);if($cateAdd)ins($d,'tn_cate',$cateAdd);
 foreach($relAdds as$r)ins($d,'tn_equipment_relation',$r);
 foreach(array_keys($relationSoftDeletes)as$id){$q=$d->prepare('UPDATE tn_equipment_relation SET is_del=1,update_time=NOW() WHERE id=? AND is_del=0');$q->execute([$id]);if((int)one($d,'SELECT COUNT(*) FROM tn_equipment_relation WHERE id=? AND is_del=1',[$id])!==1)throw new RuntimeException('target relation dedup mismatch id='.$id);}
 foreach($relationUpdates as$u){$q=$d->prepare('UPDATE tn_equipment_relation SET v=?,is_norm=?,update_time=NOW() WHERE type=? AND k=? AND is_del=0');$q->execute([$u['v'],$u['is_norm'],$u['type'],$u['k']]);if((int)one($d,'SELECT COUNT(*) FROM tn_equipment_relation WHERE type=? AND k=? AND v=? AND is_norm=? AND is_del=0',[$u['type'],$u['k'],$u['v'],$u['is_norm']])!==1)throw new RuntimeException('relation update mismatch '.$u['type'].'/'.$u['k']);}
 foreach($normAdds as$n)ins($d,'tn_norm',$n);foreach($normUpdates as$uuid=>$n){$q=$d->prepare('UPDATE tn_norm SET unit_uuid=?,male_lower_limit=?,male_upper_limit=?,female_lower_limit=?,female_upper_limit=?,equipment_uuid=?,type=?,status=?,update_time=NOW() WHERE uuid=? AND del_flag=0');$q->execute([$n['unit_uuid'],$n['male_lower_limit'],$n['male_upper_limit'],$n['female_lower_limit'],$n['female_upper_limit'],$n['equipment_uuid'],$n['type'],$n['status'],$uuid]);}foreach($normCateAdds as$nc)ins($d,'tn_norm_cate',$nc);
 foreach($hardwareTypePlan as$id=>$type){$q=$d->prepare('UPDATE tn_equipment_hardware SET device_id=? WHERE id=?');$q->execute([$type,$id]);if((string)one($d,'SELECT device_id FROM tn_equipment_hardware WHERE id=?',[$id])!==(string)$type)throw new RuntimeException('hardware device_id mismatch id='.$id);}
 foreach($plans as$p){$id=ins($d,'tn_equipment_result',$p['main']);$e=['equipment_result_id'=>$id]+$p['extend']+['is_del'=>0,'create_time'=>date('Y-m-d H:i:s'),'update_time'=>date('Y-m-d H:i:s'),'create_by'=>'migration'];ins($d,'tn_equipment_result_extend',$e);}
 // All acceptance-critical assertions execute inside the transaction.
 if((int)one($d,"SELECT COUNT(*) FROM tn_equipment_result WHERE reference LIKE 'MIG-FUNC-%'")!==57)throw new RuntimeException('result count !=57');
 if((int)one($d,"SELECT COUNT(*) FROM tn_equipment_result_extend e JOIN tn_equipment_result r ON r.id=e.equipment_result_id WHERE r.reference LIKE 'MIG-FUNC-%'")!==57)throw new RuntimeException('extend count !=57');
 $verifiedValues=0;$verifiedActiveDuplicates=0;$verifiedDeleted=0;foreach($plans as$p){$rr=all($d,'SELECT * FROM tn_equipment_result WHERE reference=?',[$p['main']['reference']]);if(count($rr)!==1)throw new RuntimeException('reference uniqueness '.$p['main']['reference']);$rr=$rr[0];$ee=all($d,'SELECT * FROM tn_equipment_result_extend WHERE equipment_result_id=?',[$rr['id']]);if(count($ee)!==1)throw new RuntimeException('extend uniqueness '.$p['main']['reference']);$ee=$ee[0];foreach($p['value_slots']as$k){$src=array_key_exists($k,$p['main'])?$p['main']:$p['extend'];$dst=array_key_exists($k,$p['main'])?$rr:$ee;$verifiedValues++;if((string)($dst[$k]??'')!==(string)$src[$k])throw new RuntimeException('persisted value mismatch '.$p['main']['reference'].'/'.$k);}$decoded=json_decode((string)$ee['other_info'],true);if(!is_array($decoded))throw new RuntimeException('invalid other_info '.$p['main']['reference']);$verifiedActiveDuplicates+=count($decoded['active_duplicate_rows']??[]);$verifiedDeleted+=count($decoded['deleted_detection_rows']??[]);if((string)$ee['other_info']!==(string)$p['extend']['other_info'])throw new RuntimeException('trace mismatch '.$p['main']['reference']);}if($verifiedValues!==848||$verifiedActiveDuplicates!==9||$verifiedDeleted!==57||$verifiedValues+$verifiedActiveDuplicates!==857)throw new RuntimeException('persisted value/trace totals mismatch');
 $preOrphans=(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result r LEFT JOIN tn_staff s ON s.uuid=r.staff_uuid LEFT JOIN tn_department dep ON dep.uuid=r.department_uuid LEFT JOIN tn_equipment_hardware h ON h.id=r.equipment_id LEFT JOIN tn_equipment_result_extend e ON e.equipment_result_id=r.id WHERE r.reference LIKE 'MIG-FUNC-%' AND (s.id IS NULL OR dep.id IS NULL OR h.id IS NULL OR e.id IS NULL)");if($preOrphans)throw new RuntimeException('precommit orphans='.$preOrphans);
 foreach($semantic as$t=>$items)foreach($items as$k=>$unused){$r=$rels[$t][$k];assertSemanticChain($d,$t,$k,$r,$cateUuid,$units[unitKey($r['unit'])],$typeHardware[$t]['uuid']);}
 if($simulate){$d->rollBack();one($d,'SELECT RELEASE_LOCK(?)',[$lockName]);echo json_encode(['simulated'=>true,'rolled_back'=>true,'records_verified'=>57,'extends_verified'=>57,'slot_values_verified'=>848,'active_duplicate_trace_verified'=>9,'deleted_trace_verified'=>57,'semantic_chains_verified'=>96],JSON_PRETTY_PRINT),PHP_EOL;exit(0);}$d->commit();
}catch(Throwable$e){if($d->inTransaction())$d->rollBack();throw$e;}
$orph=(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result r LEFT JOIN tn_staff s ON s.uuid=r.staff_uuid LEFT JOIN tn_department dep ON dep.uuid=r.department_uuid LEFT JOIN tn_equipment_hardware h ON h.id=r.equipment_id LEFT JOIN tn_equipment_result_extend e ON e.equipment_result_id=r.id WHERE r.reference LIKE 'MIG-FUNC-%' AND (s.id IS NULL OR dep.id IS NULL OR h.id IS NULL OR e.id IS NULL)");if($orph)throw new RuntimeException('orphans='.$orph);
echo json_encode(['applied'=>true,'backup_suffix'=>$suffix,'records'=>(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result WHERE reference LIKE 'MIG-FUNC-%'"),'extends'=>(int)one($d,"SELECT COUNT(*) FROM tn_equipment_result_extend e JOIN tn_equipment_result r ON r.id=e.equipment_result_id WHERE r.reference LIKE 'MIG-FUNC-%'"),'orphans'=>$orph],JSON_PRETTY_PRINT),PHP_EOL;
one($d,'SELECT RELEASE_LOCK(?)',[$lockName]);