<?php
// Dry-run by default. Run with APPLY=1 only after approval.
declare(strict_types=1);

$apply = getenv('APPLY') === '1';
$srcCfg = include '/workspace/wwwroot/tnxl/project/application/config/prod/tnxl/database.php';
$dstCfg = include '/workspace/wwwroot/tnxlgstyj/project/application/config/prod/tnxlgstyj/database.php';

function db(array $c): PDO {
    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 rows(PDO $p, string $sql, array $args=[]): array {
    $q=$p->prepare($sql); $q->execute($args); return $q->fetchAll();
}
function scalar(PDO $p, string $sql, array $args=[]) {
    $q=$p->prepare($sql); $q->execute($args); return $q->fetchColumn();
}
function cols(PDO $p, string $table): array {
    return array_column(rows($p, 'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? ORDER BY ORDINAL_POSITION', [$table]), 'COLUMN_NAME');
}
function newUuid(): string {
    $h=strtoupper(bin2hex(random_bytes(16)));
    return substr($h,0,8).'-'.substr($h,8,4).'-'.substr($h,12,4).'-'.substr($h,16,4).'-'.substr($h,20,12);
}
function insertRow(PDO $p, string $table, array $row, array $allowed): int {
    $row=array_intersect_key($row,array_flip($allowed)); unset($row['id']);
    $names=array_keys($row); if(!$names) throw new RuntimeException("no insertable columns for $table");
    $sql='INSERT INTO `'.$table.'` (`'.implode('`,`',$names).'`) VALUES ('.implode(',',array_fill(0,count($names),'?')).')';
    $q=$p->prepare($sql); $q->execute(array_values($row)); return (int)$p->lastInsertId();
}
function sourceTime(string $family, array $r): int {
    if($family==='gymware') return (int)$r['recorded'];
    foreach(['record_time','create_time','date'] as $k) if(!empty($r[$k])) return strtotime($r[$k]) ?: 0;
    return 0;
}

$src=db($srcCfg); $dst=db($dstCfg);
$families=[
    'lactate'=>['main'=>'tn_device_lactate','detail'=>null,'group'=>1,'time'=>'create_time'],
    'gymware'=>['main'=>'tn_gymware_reps','detail'=>'tn_gymware_reps_detail','group'=>2,'time'=>'recorded'],
    'eliga'=>['main'=>'tn_equipment_eliga','detail'=>'tn_equipment_eliga_details','group'=>3,'time'=>'record_time'],
    'miccogate'=>['main'=>'tn_equipment_miccogate','detail'=>'tn_equipment_miccogate_details','group'=>10,'time'=>'record_time'],
    'vald'=>['main'=>'tn_equipment_vald','detail'=>'tn_equipment_vald_details','group'=>11,'time'=>'date'],
    'concept2'=>['main'=>'tn_equipment_concept2','detail'=>'tn_equipment_concept2_details','group'=>12,'time'=>'record_time'],
    'wattbike'=>['main'=>'tn_equipment_wattbike','detail'=>'tn_equipment_wattbike_details','group'=>13,'time'=>'record_time'],
    'eliteform'=>['main'=>'tn_enginery_data','detail'=>'tn_enginery_data_detail','group'=>14,'time'=>'date'],
];
$groupByCategory=[
    '血乳酸分析仪'=>1,'功率自行车'=>13,'划船机'=>12,'数字化力量训练器'=>3,
    '测力台'=>11,'力量功率监测系统'=>2,'三维训练数据监测系统'=>14,'竞技运动素质测评系统'=>10,
];

$athletes=rows($dst,"SELECT s.uuid,s.code,s.name,s.sex,s.birthday,s.height,s.weight,d.uuid department_uuid FROM tn_staff s JOIN tn_staff_department sd ON sd.staff_uuid=s.uuid JOIN tn_department d ON d.uuid=sd.department_uuid WHERE d.name='演示运动队' AND s.name REGEXP '^运动员 [0-9]+$' AND s.del_flag=0 ORDER BY CAST(SUBSTRING_INDEX(s.name,' ',-1) AS UNSIGNED)");
if(count($athletes)!==10) throw new RuntimeException('target athlete count must be 10, got '.count($athletes));

$srcHardware=rows($src,"SELECT h.id,h.uuid,h.name,h.model_num,c.name category_name,c.type category_type FROM tn_equipment_hardware h JOIN tn_equipment_hardware_cate c ON c.uuid=h.cate_uuid WHERE h.is_show=1 AND c.is_del=0");
$dstHardware=rows($dst,"SELECT h.id,h.uuid,h.name,h.model_num,c.name category_name,c.type category_type FROM tn_equipment_hardware h JOIN tn_equipment_hardware_cate c ON c.uuid=h.cate_uuid WHERE h.is_show=1 AND c.is_del=0");
$hardwareMap=[]; $unresolved=[];
foreach($srcHardware as $s){
    $matches=array_values(array_filter($dstHardware,function($d) use ($s){return $d['name']===$s['name'] && $d['category_name']===$s['category_name'] && (int)$d['category_type']===(int)$s['category_type'];}));
    if(count($matches)===1) $hardwareMap[$s['uuid']]=$matches[0];
    else $unresolved[]=['source'=>$s,'matches'=>count($matches)];
}

$mainRows=[];
foreach($families as $family=>$cfg){
    if($family==='eliteform') $data=rows($src,'SELECT a.* FROM tn_enginery_data a WHERE EXISTS(SELECT 1 FROM tn_enginery_data_extend e WHERE e.data_id=a.id) ORDER BY a.id');
    else $data=rows($src,'SELECT * FROM `'.$cfg['main'].'` ORDER BY id');
    foreach($data as $r) $mainRows[]=['family'=>$family,'row'=>$r,'ts'=>sourceTime($family,$r)];
}
usort($mainRows,function($a,$b){return [$a['ts'],$a['family'],(int)$a['row']['id']] <=> [$b['ts'],$b['family'],(int)$b['row']['id']];});
if(count($mainRows)!==668) throw new RuntimeException('expected 668 main rows, got '.count($mainRows));
$validTs=array_values(array_filter(array_column($mainRows,'ts'))); $srcMin=min($validTs); $srcMax=max($validTs);
$dstMin=strtotime('2026-05-01 08:00:00'); $dstMax=strtotime('2026-08-31 18:00:00');
function shifted(int $ts): int { global $srcMin,$srcMax,$dstMin,$dstMax; if($ts<=0)return $dstMin; return (int)round($dstMin+(($ts-$srcMin)/max(1,$srcMax-$srcMin))*($dstMax-$dstMin)); }
function mapDateValue($v, bool $dateOnly=false) { if($v===null||$v==='')return $v; $t=is_numeric($v)?(int)$v:(strtotime((string)$v)?:0); $n=shifted($t); return $dateOnly?date('Y-m-d',$n):date('Y-m-d H:i:s',$n); }

$assignment=[]; $plannedByFamily=[];
foreach($mainRows as $i=>$item){
    $key=$item['family'].':'.$item['row']['id']; $assignment[$key]=$athletes[$i%10];
    $plannedByFamily[$item['family']]=($plannedByFamily[$item['family']]??0)+1;
}

// Resolve every main-row hardware reference. Eliga UUIDs are vendor UUIDs and
// must be mapped by device_name + device_model instead of retained.
$recordHardware=[]; $recordHardwareUnresolved=[];
foreach($mainRows as $item){
    $family=$item['family'];$r=$item['row'];$key=$family.':'.$r['id'];$match=null;
    foreach(['equipment_uuid','device_uuid'] as $field){
        if(!empty($r[$field]) && isset($hardwareMap[$r[$field]])){$match=$hardwareMap[$r[$field]];break;}
    }
    if(!$match && $family==='eliga'){
        $m=array_values(array_filter($dstHardware,function($d) use ($r){return $d['name']===$r['device_name'] && $d['model_num']===$r['device_model'] && $d['category_name']==='数字化力量训练器';}));
        if(count($m)===1)$match=$m[0];
    }
    if(!$match && $family==='lactate'){
        $expected=(int)$r['type']===2?'便携乳酸盐分析仪':'血乳酸测试仪';
        $m=array_values(array_filter($dstHardware,function($d) use ($expected){return $d['name']===$expected && $d['category_name']==='血乳酸分析仪';}));
        if(count($m)===1)$match=$m[0];
    }
    if(!$match && $family==='gymware'){
        $m=array_values(array_filter($dstHardware,function($d){return $d['name']==='力量功率监测系统' && $d['category_name']==='力量功率监测系统';}));
        if(count($m)===1)$match=$m[0];
    }
    if(!$match && $family==='eliteform'){
        $m=array_values(array_filter($dstHardware,function($d){return $d['name']==='三维训练数据监测系统' && $d['category_name']==='三维训练数据监测系统';}));
        if(count($m)===1)$match=$m[0];
    }
    if($match)$recordHardware[$key]=$match;
    else $recordHardwareUnresolved[]=['family'=>$family,'id'=>$r['id'],'equipment_uuid'=>$r['equipment_uuid']??null,'device_uuid'=>$r['device_uuid']??null,'device_name'=>$r['device_name']??null,'device_model'=>$r['device_model']??null];
}

$sourceActions=rows($src,'SELECT * FROM tn_enginery_action ORDER BY id');
$sourceActionIds=array_map('intval',array_column($sourceActions,'id'));
$referencedActionIds=[];
foreach(array_filter($mainRows,function($x){return $x['family']==='eliteform';}) as $x)if((int)$x['row']['action_id']>0)$referencedActionIds[]=(int)$x['row']['action_id'];

$dataTables=['tn_device_lactate','tn_gymware_reps','tn_gymware_reps_detail','tn_equipment_eliga','tn_equipment_eliga_details','tn_equipment_miccogate','tn_equipment_miccogate_details','tn_equipment_vald','tn_equipment_vald_details','tn_equipment_concept2','tn_equipment_concept2_details','tn_equipment_wattbike','tn_equipment_wattbike_details','tn_enginery_data','tn_enginery_data_extend','tn_enginery_data_detail','tn_enginery_action'];
$targetCounts=[]; foreach($dataTables as $t)$targetCounts[$t]=(int)scalar($dst,'SELECT COUNT(*) FROM `'.$t.'`');
$nonEmpty=array_filter($targetCounts); if($nonEmpty) throw new RuntimeException('target physical tables must be empty: '.json_encode($nonEmpty));

$detailPlan=[];
$detailPlan['tn_gymware_reps_detail']=(int)scalar($src,'SELECT COUNT(*) FROM tn_gymware_reps_detail');
$detailPlan['tn_equipment_eliga_details']=(int)scalar($src,'SELECT COUNT(*) FROM tn_equipment_eliga_details');
$detailPlan['tn_equipment_miccogate_details']=(int)scalar($src,'SELECT COUNT(*) FROM tn_equipment_miccogate_details');
$detailPlan['tn_equipment_vald_details']=(int)scalar($src,'SELECT COUNT(*) FROM tn_equipment_vald_details');
$detailPlan['tn_equipment_concept2_details']=(int)scalar($src,'SELECT COUNT(*) FROM tn_equipment_concept2_details d JOIN tn_equipment_concept2 m ON m.uuid=d.concept2_uuid');
$detailPlan['tn_equipment_wattbike_details']=(int)scalar($src,'SELECT COUNT(*) FROM tn_equipment_wattbike_details');
$eliteIds=array_column(array_filter($mainRows,function($x){return $x['family']==='eliteform';}),'row');
$eliteIds=array_column($eliteIds,'id'); $eliteUuids=array_column(array_filter($mainRows,function($x){return $x['family']==='eliteform';}),'row'); $eliteUuids=array_column($eliteUuids,'uuid');
$detailPlan['tn_enginery_data_extend']=count($eliteIds)?(int)scalar($src,'SELECT COUNT(*) FROM tn_enginery_data_extend WHERE data_id IN ('.implode(',',array_fill(0,count($eliteIds),'?')).')',$eliteIds):0;
$detailPlan['tn_enginery_data_detail']=count($eliteUuids)?(int)scalar($src,'SELECT COUNT(*) FROM tn_enginery_data_detail WHERE data_uuid IN ('.implode(',',array_fill(0,count($eliteUuids),'?')).')',$eliteUuids):0;

if($eliteUuids){
    $detailActions=rows($src,'SELECT DISTINCT action_id FROM tn_enginery_data_detail WHERE data_uuid IN ('.implode(',',array_fill(0,count($eliteUuids),'?')).') AND action_id>0',$eliteUuids);
    foreach($detailActions as $a)$referencedActionIds[]=(int)$a['action_id'];
}
$referencedActionIds=array_values(array_unique($referencedActionIds));sort($referencedActionIds);
$actionUnresolved=array_values(array_diff($referencedActionIds,$sourceActionIds));

// Audit target-only columns. A target-only NOT NULL column is safe only when it
// has a default or is auto-generated. VALD fields with empty-string/zero defaults
// are explicitly reported; record_time/reps are populated during apply.
$targetExtra=[];$unsafeExtra=[];
foreach($dataTables as $t){
    $srcCols=cols($src,$t);
    foreach(rows($dst,'SELECT COLUMN_NAME,IS_NULLABLE,COLUMN_DEFAULT,EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? ORDER BY ORDINAL_POSITION',[$t]) as $c){
        if(in_array($c['COLUMN_NAME'],$srcCols,true))continue;
        $targetExtra[$t][]=$c;
        if($c['IS_NULLABLE']==='NO' && $c['COLUMN_DEFAULT']===null && strpos($c['EXTRA'],'auto_increment')===false)$unsafeExtra[$t][]=$c['COLUMN_NAME'];
    }
}

$srcPublic='/workspace/wwwroot/tnxl/project/public/';$dstPublic='/workspace/wwwroot/tnxlgstyj/project/public/';
$xmlPlan=[];
foreach(rows($src,"SELECT id,file_path FROM tn_equipment_miccogate WHERE file_path<>''") as $f){
    $source=$srcPublic.str_replace('\\','/',$f['file_path']);$target=$dstPublic.str_replace('\\','/',$f['file_path']);
    $xmlPlan[]=['record_id'=>(int)$f['id'],'relative'=>$f['file_path'],'source_exists'=>is_file($source),'source_size'=>is_file($source)?filesize($source):null,'source_sha256'=>is_file($source)?hash_file('sha256',$source):null,'target'=>$target];
}

$summary=['mode'=>$apply?'apply':'dry-run','main_total'=>count($mainRows),'main_by_family'=>$plannedByFamily,'detail_rows'=>$detailPlan,'athletes'=>array_column($athletes,'name'),'source_range'=>[date('c',$srcMin),date('c',$srcMax)],'target_range'=>[date('c',$dstMin),date('c',$dstMax)],'catalog_hardware_mappings'=>count($hardwareMap),'catalog_hardware_unresolved'=>$unresolved,'record_hardware_mapped'=>count($recordHardware),'record_hardware_unresolved'=>$recordHardwareUnresolved,'action_ids'=>['source'=>count($sourceActionIds),'referenced'=>$referencedActionIds,'unresolved'=>$actionUnresolved],'target_extra_columns'=>$targetExtra,'unsafe_target_extra_columns'=>$unsafeExtra,'microgate_xml'=>$xmlPlan,'category_group_plan'=>$groupByCategory,'target_counts_before'=>$targetCounts,'backup_tables'=>$dataTables];
echo json_encode($summary,JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES),PHP_EOL;
if(!$apply) exit(0);
if($recordHardwareUnresolved) throw new RuntimeException('record hardware mappings unresolved');
if($actionUnresolved) throw new RuntimeException('action mappings unresolved');
if($unsafeExtra) throw new RuntimeException('unsafe target-only NOT NULL columns');
$missingXml=array_values(array_filter($xmlPlan,function($x){return !$x['source_exists'];}));
if($missingXml && getenv('ALLOW_MISSING_XML')!=='1') throw new RuntimeException('Microgate source XML files are missing; restore them or explicitly set ALLOW_MISSING_XML=1 after accepting DB-detail-only migration');

$backupSuffix=date('Ymd_His');
foreach(array_merge(['tn_equipment_hardware_cate'],$dataTables) as $t){
    $bak='migbak_'.$backupSuffix.'_'.substr($t,3);
    $dst->exec('CREATE TABLE `'.$bak.'` LIKE `'.$t.'`');
    $dst->exec('INSERT INTO `'.$bak.'` SELECT * FROM `'.$t.'`');
}

$allowed=[]; foreach($dataTables as $t)$allowed[$t]=cols($dst,$t);
$dst->beginTransaction();
try{
    foreach($groupByCategory as $name=>$group){$q=$dst->prepare('UPDATE tn_equipment_hardware_cate SET group_type=? WHERE name=? AND is_del=0');$q->execute([$group,$name]);}
    $actionIdMap=[];
    foreach($sourceActions as $r){$old=(int)$r['id'];$actionIdMap[$old]=insertRow($dst,'tn_enginery_action',$r,$allowed['tn_enginery_action']);}
    $uuidMap=[]; $idMap=[];
    foreach($mainRows as $item){
        $family=$item['family'];$r=$item['row'];$oldId=(int)$r['id'];$ath=$assignment[$family.':'.$oldId];$oldUuid=$r['uuid']??null;
        if(array_key_exists('staff_uuid',$r))$r['staff_uuid']=$ath['uuid'];
        if(array_key_exists('department_uuid',$r))$r['department_uuid']=$ath['department_uuid'];
        if(array_key_exists('athleteName',$r)){$r['athleteName']=$ath['name'];$r['athleteReference']=$ath['uuid'];$r['athleteWeight']=$ath['weight'];}
        if(array_key_exists('username',$r)){$r['username']=$ath['name'];$r['sex']=(int)$ath['sex']===1?'M':'F';$r['hight']=$ath['height'];}
        foreach(['equipment_uuid','device_uuid'] as $k)if(!empty($r[$k])&&isset($hardwareMap[$r[$k]]))$r[$k]=$hardwareMap[$r[$k]]['uuid'];
        if(in_array($family,['lactate','miccogate','concept2','wattbike'],true))$r['equipment_uuid']=$recordHardware[$family.':'.$oldId]['uuid'];
        if(in_array($family,['eliga','vald'],true))$r['device_uuid']=$recordHardware[$family.':'.$oldId]['uuid'];
        if($family==='eliteform' && (int)$r['action_id']>0)$r['action_id']=$actionIdMap[(int)$r['action_id']];
        if($oldUuid!==null){$r['uuid']=newUuid();$uuidMap[$family.':'.$oldUuid]=$r['uuid'];}
        if($family==='gymware'){$r['recorded']=shifted((int)$r['recorded']);if(!empty($r['modified']))$r['modified']=shifted((int)$r['modified']);}
        foreach(['date'] as $k)if(array_key_exists($k,$r)&&$r[$k]!=='')$r[$k]=mapDateValue($r[$k],true);
        foreach(['record_time','create_time','update_time'] as $k)if(array_key_exists($k,$r)&&$r[$k]!=='')$r[$k]=mapDateValue($r[$k]);
        if($family==='lactate'){ $t=strtotime($r['create_time']);foreach(['year'=>'Y','month'=>'n','day'=>'j','hour'=>'G','minute'=>'i','second'=>'s'] as $k=>$fmt)$r[$k]=date($fmt,$t); }
        if($family==='vald'){$r['record_time']=mapDateValue($item['row']['date']);$r['reps']=(int)scalar($src,'SELECT COALESCE(MAX(`row`)+1,0) FROM tn_equipment_vald_details WHERE vald_uuid=?',[$oldUuid]);}
        $newId=insertRow($dst,$families[$family]['main'],$r,$allowed[$families[$family]['main']]);$idMap[$family.':'.$oldId]=$newId;
    }
    // Gymware detail.reps_id references main.reference (vendor ID), not main.id.
    foreach(rows($src,'SELECT * FROM tn_gymware_reps_detail ORDER BY id') as $r){insertRow($dst,'tn_gymware_reps_detail',$r,$allowed['tn_gymware_reps_detail']);}
    foreach(rows($src,'SELECT * FROM tn_equipment_eliga_details ORDER BY id') as $r){$r['eliga_uuid']=$uuidMap['eliga:'.$r['eliga_uuid']];insertRow($dst,'tn_equipment_eliga_details',$r,$allowed['tn_equipment_eliga_details']);}
    foreach(rows($src,'SELECT * FROM tn_equipment_miccogate_details ORDER BY id') as $r){$parentId=(int)scalar($src,'SELECT id FROM tn_equipment_miccogate WHERE uuid=?',[$r['miccogate_uuid']]);$ath=$assignment['miccogate:'.$parentId];$r['miccogate_uuid']=$uuidMap['miccogate:'.$r['miccogate_uuid']];$r['staff_uuid']=$ath['uuid'];$r['username']=$ath['name'];$r['sex']=(int)$ath['sex']===1?'M':'F';$r['userhight']=$ath['height'];$r['equipment_uuid']=$recordHardware['miccogate:'.$parentId]['uuid'];$r['record_time']=mapDateValue($r['record_time']);$r['create_time']=mapDateValue($r['create_time']);insertRow($dst,'tn_equipment_miccogate_details',$r,$allowed['tn_equipment_miccogate_details']);}
    foreach(rows($src,'SELECT * FROM tn_equipment_vald_details ORDER BY id') as $r){$r['vald_uuid']=$uuidMap['vald:'.$r['vald_uuid']];$r['limb']='Trial';$r['description']='';$r['limb_name']='';insertRow($dst,'tn_equipment_vald_details',$r,$allowed['tn_equipment_vald_details']);}
    foreach(['concept2'=>['table'=>'tn_equipment_concept2_details','fk'=>'concept2_uuid'],'wattbike'=>['table'=>'tn_equipment_wattbike_details','fk'=>'puuid']] as $family=>$d){$detailSql='SELECT d.* FROM '.$d['table'].' d JOIN '.$families[$family]['main'].' m ON m.uuid=d.'.$d['fk'].' ORDER BY d.id';foreach(rows($src,$detailSql) as $r){$old=$r[$d['fk']];$mainId=(int)scalar($src,'SELECT id FROM '.$families[$family]['main'].' WHERE uuid=?',[$old]);$ath=$assignment[$family.':'.$mainId];$r[$d['fk']]=$uuidMap[$family.':'.$old];$r['staff_uuid']=$ath['uuid'];$r['equipment_uuid']=$recordHardware[$family.':'.$mainId]['uuid'];foreach(['date'] as $k)if(isset($r[$k]))$r[$k]=mapDateValue($r[$k],true);foreach(['record_time','create_time'] as $k)if(isset($r[$k]))$r[$k]=mapDateValue($r[$k]);insertRow($dst,$d['table'],$r,$allowed[$d['table']]);}}
    foreach($eliteIds as $oldId){foreach(rows($src,'SELECT * FROM tn_enginery_data_extend WHERE data_id=? ORDER BY id',[$oldId]) as $r){$r['data_id']=$idMap['eliteform:'.$oldId];insertRow($dst,'tn_enginery_data_extend',$r,$allowed['tn_enginery_data_extend']);}}
    foreach($eliteUuids as $oldUuid){$oldId=(int)scalar($src,'SELECT id FROM tn_enginery_data WHERE uuid=?',[$oldUuid]);$ath=$assignment['eliteform:'.$oldId];foreach(rows($src,'SELECT * FROM tn_enginery_data_detail WHERE data_uuid=? ORDER BY id',[$oldUuid]) as $r){$r['data_uuid']=$uuidMap['eliteform:'.$oldUuid];$r['staff_uuid']=$ath['uuid'];if((int)$r['action_id']>0)$r['action_id']=$actionIdMap[(int)$r['action_id']];foreach(['date'] as $k)if(isset($r[$k]))$r[$k]=mapDateValue($r[$k],true);foreach(['record_time','create_time'] as $k)if(isset($r[$k]))$r[$k]=mapDateValue($r[$k]);insertRow($dst,'tn_enginery_data_detail',$r,$allowed['tn_enginery_data_detail']);}}
    $dst->commit();
}catch(Throwable $e){if($dst->inTransaction())$dst->rollBack();throw $e;}

// XML files are import artifacts. Copy only when the source file still exists;
// DB detail display does not read them. Missing source files remain a reported
// external prerequisite and are never replaced with fabricated content.
$xmlCopied=[];
foreach($xmlPlan as $x){if(!$x['source_exists'])continue;$source=$srcPublic.str_replace('\\','/',$x['relative']);$target=$x['target'];if(!is_dir(dirname($target)))mkdir(dirname($target),0755,true);if(!copy($source,$target))throw new RuntimeException('XML copy failed: '.$x['relative']);$hash=hash_file('sha256',$target);if($hash!==$x['source_sha256'])throw new RuntimeException('XML hash mismatch: '.$x['relative']);$xmlCopied[]=['relative'=>$x['relative'],'sha256'=>$hash];}

$orphans=[
    'lactate_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_device_lactate a LEFT JOIN tn_staff s ON s.uuid=a.staff_uuid WHERE s.id IS NULL'),
    'lactate_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_device_lactate a LEFT JOIN tn_equipment_hardware h ON h.uuid=a.equipment_uuid WHERE h.id IS NULL'),
    'eliga_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_eliga a LEFT JOIN tn_staff s ON s.uuid=a.staff_uuid WHERE s.id IS NULL'),
    'eliga_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_eliga a LEFT JOIN tn_equipment_hardware h ON h.uuid=a.device_uuid WHERE h.id IS NULL'),
    'gymware_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_gymware_reps a LEFT JOIN tn_staff s ON s.name=a.athleteName WHERE s.id IS NULL'),
    'gymware_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_gymware_reps_detail d LEFT JOIN tn_gymware_reps m ON m.reference=d.reps_id WHERE m.id IS NULL'),
    'micco_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_miccogate_details d LEFT JOIN tn_equipment_miccogate m ON m.uuid=d.miccogate_uuid WHERE m.id IS NULL'),
    'micco_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_miccogate m LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE s.id IS NULL'),
    'micco_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_miccogate m LEFT JOIN tn_equipment_hardware h ON h.uuid=m.equipment_uuid WHERE h.id IS NULL'),
    'vald_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_vald_details d LEFT JOIN tn_equipment_vald m ON m.uuid=d.vald_uuid WHERE m.id IS NULL'),
    'vald_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_vald m LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE s.id IS NULL'),
    'vald_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_vald m LEFT JOIN tn_equipment_hardware h ON h.uuid=m.device_uuid WHERE h.id IS NULL'),
    'concept_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_concept2_details d LEFT JOIN tn_equipment_concept2 m ON m.uuid=d.concept2_uuid WHERE m.id IS NULL'),
    'concept_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_concept2 m LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE s.id IS NULL'),
    'concept_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_concept2 m LEFT JOIN tn_equipment_hardware h ON h.uuid=m.equipment_uuid WHERE h.id IS NULL'),
    'wattbike_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_wattbike_details d LEFT JOIN tn_equipment_wattbike m ON m.uuid=d.puuid WHERE m.id IS NULL'),
    'wattbike_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_wattbike m LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE s.id IS NULL'),
    'wattbike_device'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_equipment_wattbike m LEFT JOIN tn_equipment_hardware h ON h.uuid=m.equipment_uuid WHERE h.id IS NULL'),
    'elite_extend'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_enginery_data_extend d LEFT JOIN tn_enginery_data m ON m.id=d.data_id WHERE m.id IS NULL'),
    'elite_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_enginery_data_detail d LEFT JOIN tn_enginery_data m ON m.uuid=d.data_uuid WHERE m.id IS NULL'),
    'elite_staff'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_enginery_data m LEFT JOIN tn_staff s ON s.uuid=m.staff_uuid WHERE s.id IS NULL'),
    'elite_action_main'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_enginery_data d LEFT JOIN tn_enginery_action a ON a.id=d.action_id WHERE d.action_id>0 AND a.id IS NULL'),
    'elite_action_detail'=>(int)scalar($dst,'SELECT COUNT(*) FROM tn_enginery_data_detail d LEFT JOIN tn_enginery_action a ON a.id=d.action_id WHERE d.action_id>0 AND a.id IS NULL'),
];
if(array_sum($orphans)!==0)throw new RuntimeException('post-write orphan validation failed: '.json_encode($orphans));

$totalAfter=0;foreach($families as $f)$totalAfter+=(int)scalar($dst,'SELECT COUNT(*) FROM `'.$f['main'].'`');
echo json_encode(['applied'=>true,'backup_suffix'=>$backupSuffix,'target_main_total'=>$totalAfter,'date_checks'=>rows($dst,"SELECT MIN(dt) min_dt,MAX(dt) max_dt FROM (SELECT create_time dt FROM tn_device_lactate UNION ALL SELECT FROM_UNIXTIME(recorded) FROM tn_gymware_reps UNION ALL SELECT record_time FROM tn_equipment_eliga UNION ALL SELECT record_time FROM tn_equipment_miccogate UNION ALL SELECT record_time FROM tn_equipment_vald UNION ALL SELECT record_time FROM tn_equipment_concept2 UNION ALL SELECT record_time FROM tn_equipment_wattbike UNION ALL SELECT record_time FROM tn_enginery_data) x"),'orphans'=>$orphans,'xml_copied'=>$xmlCopied,'api_verification_samples'=>rows($dst,"SELECT c.group_type,MIN(x.id) sample_id,COUNT(*) total FROM tn_equipment_hardware_cate c JOIN (SELECT 1 group_type,id FROM tn_device_lactate UNION ALL SELECT 2,id FROM tn_gymware_reps UNION ALL SELECT 3,id FROM tn_equipment_eliga UNION ALL SELECT 10,id FROM tn_equipment_miccogate UNION ALL SELECT 11,id FROM tn_equipment_vald UNION ALL SELECT 12,id FROM tn_equipment_concept2 UNION ALL SELECT 13,id FROM tn_equipment_wattbike UNION ALL SELECT 14,id FROM tn_enginery_data) x ON x.group_type=c.group_type GROUP BY c.group_type ORDER BY c.group_type")],JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT),PHP_EOL;