<?php
declare(strict_types=1);
function load_test_data(PDO $pdo):array{
 if((int)$pdo->query("SELECT COUNT(*) FROM afguards WHERE serial_number LIKE 'V___/82___/_'")->fetchColumn()>0)throw new RuntimeException('Synthetic AFGTracv4 test data is already loaded. Use a fresh test database to reload it.');
 $pdo->beginTransaction();try{
  foreach([['AeroAndes Test','CO'],['Maple Aviation Test','CA'],['Caribbean Fuel Test','TT'],['Pacific Aero Test','PE']] as [$name,$cc])$pdo->prepare('INSERT OR IGNORE INTO clients(name,country_code) VALUES(?,?)')->execute([$name,$cc]);
  $clients=$pdo->query("SELECT id,name FROM clients WHERE name LIKE '% Test'")->fetchAll(PDO::FETCH_KEY_PAIR);$ids=array_keys($clients);
  for($i=1;$i<=100;$i++){ $serial=sprintf('V%03d/%05d/%s',$i,82000+$i,chr(97+(($i-1)%26)));$owned=$i<=60?'sefiso':'client';$cid=$owned==='client'?$ids[($i-61)%count($ids)]:null;$model=$i%3?'InMetro':'Offshore';$condition=$i<=10?'new':($i>95?'scrap':'used');$designation=$condition==='new'?'For sale':($condition==='scrap'?null:['For sale','Rental','Loan'][($i-11)%3]);$pdo->prepare('INSERT OR IGNORE INTO afguards(serial_number,owned_by,owner_client_id,model,asset_condition,designation,current_location,recertification_expiration_date,record_updated_by,notes) VALUES(?,?,?,?,?,?,?,?,?,?)')->execute([$serial,$owned,$cid,$model,$condition,$designation,'SERM1',$condition==='used'?'2027-06-30':null,'testdata','Synthetic AFGTracv4 test sensor']); }
  // 6 active annual workflows with sales orders; 4 active interim
  for($j=61;$j<=66;$j++){ $aid=$pdo->query("SELECT id FROM afguards WHERE serial_number LIKE 'V".sprintf('%03d',$j)."/%'")->fetchColumn();$cid=$pdo->query("SELECT owner_client_id FROM afguards WHERE id=$aid")->fetchColumn();$so='SO-TEST-'.sprintf('%05d',$j);$pdo->prepare("INSERT INTO receiving_intakes(afguard_id,intake_type,client_id,sales_order_number,received_condition,received_at,received_by,resulting_designation) VALUES(?,'Client Annual Recertification',?,?,'used',datetime('now','-'||?||' hours'),'testdata','Recertification')")->execute([$aid,$cid,$so,$j-60]);$intake=$pdo->lastInsertId();$pdo->prepare("INSERT INTO recertification_workflows(afguard_id,receiving_intake_id,workflow_type,client_id,designated_at,record_updated_by) VALUES(?,?, 'Annual Recertification',?,datetime('now','-'||?||' hours'),'testdata')")->execute([$aid,$intake,$cid,$j-60]);$pdo->prepare("UPDATE afguards SET designation='Recertification',current_location='Laboratory' WHERE id=?")->execute([$aid]); }
  for($j=11;$j<=14;$j++){ $aid=$pdo->query("SELECT id FROM afguards WHERE serial_number LIKE 'V".sprintf('%03d',$j)."/%'")->fetchColumn();$pdo->prepare("INSERT INTO receiving_intakes(afguard_id,intake_type,received_condition,received_at,received_by,resulting_designation) VALUES(?,'Rental Return','used',datetime('now','-'||?||' hours'),'testdata','Interim Recert')")->execute([$aid,$j-8]);$intake=$pdo->lastInsertId();$pdo->prepare("INSERT INTO recertification_workflows(afguard_id,receiving_intake_id,workflow_type,designated_at,record_updated_by) VALUES(?,?,'Interim Recertification',datetime('now','-'||?||' hours'),'testdata')")->execute([$aid,$intake,$j-8]);$pdo->prepare("UPDATE afguards SET designation='Interim Recert',current_location='Laboratory' WHERE id=?")->execute([$aid]); }
  // shipping tasks: one GmbH, one client with voucher, one client no voucher
  $dest=$pdo->query("SELECT id FROM shipping_destinations WHERE code='GMBH-DE'")->fetchColumn();
  $aid=$pdo->query("SELECT id FROM afguards WHERE serial_number LIKE 'V015/%'")->fetchColumn();$pdo->prepare("INSERT INTO shipments(afguard_id,shipment_type,destination_id,shipping_status,record_updated_by,notes) VALUES(?,'GmbH Recalibration',?,'Required','testdata','Synthetic failed recertification')")->execute([$aid,$dest]);$pdo->prepare("UPDATE afguards SET designation='Recalibration',current_location='Shipping' WHERE id=?")->execute([$aid]);
  foreach([[67,'GNVF-AVPT-TNM2-NRSO'],[68,null]] as [$j,$voucher]){$aid=$pdo->query("SELECT id FROM afguards WHERE serial_number LIKE 'V".sprintf('%03d',$j)."/%'")->fetchColumn();$cid=$pdo->query("SELECT owner_client_id FROM afguards WHERE id=$aid")->fetchColumn();$pdo->prepare("INSERT INTO shipments(afguard_id,shipment_type,client_id,sales_order_number,voucher_number,shipping_status,record_updated_by) VALUES(?,'Return to Client',?,?,?,'Required','testdata')")->execute([$aid,$cid,'SO-TEST-'.sprintf('%05d',$j),$voucher]);}
  $pdo->commit();return ['assets'=>100,'annual'=>6,'interim'=>4,'shipments'=>3];
 }catch(Throwable $e){$pdo->rollBack();throw $e;}
}
