<?php
/**
 * Remove bulk transport seed rows (BH* prefixes). FK-safe order.
 */
$m = new mysqli('127.0.0.1', 'root', '', 'erp_foundation');
$m->set_charset('utf8mb4');
$m->query('SET FOREIGN_KEY_CHECKS = 0');

$stmts = [
    "DELETE FROM transport_schedules WHERE number LIKE 'BHTRP-%'",
    "DELETE FROM transport_ai_plan_stops WHERE plan_route_id IN (SELECT id FROM transport_ai_plan_routes WHERE plan_id IN (SELECT id FROM transport_ai_plans WHERE number LIKE 'BH%'))",
    "DELETE FROM transport_ai_plan_routes WHERE plan_id IN (SELECT id FROM transport_ai_plans WHERE number LIKE 'BH%')",
    "DELETE FROM transport_ai_plans WHERE number LIKE 'BH%'",
    "DELETE FROM transport_route_stops WHERE route_id IN (SELECT id FROM transport_routes WHERE number LIKE 'BHRT-%')",
    "DELETE FROM transport_routes WHERE number LIKE 'BHRT-%'",
    "DELETE FROM transport_drivers WHERE number LIKE 'BHFLEET-D%'",
    "DELETE FROM transport_vehicles WHERE number LIKE 'BHFLEET-%'",
    "DELETE FROM transport_contract_sites WHERE contract_id IN (SELECT id FROM contracts WHERE number LIKE 'BHCT-%')",
    "DELETE FROM contract_employees WHERE contract_id IN (SELECT id FROM contracts WHERE number LIKE 'BHCT-%')",
    "DELETE FROM contract_lines WHERE contract_id IN (SELECT id FROM contracts WHERE number LIKE 'BHCT-%')",
    "DELETE FROM contracts WHERE number LIKE 'BHCT-%'",
    "DELETE FROM employee_shift_assignments WHERE employee_id IN (SELECT id FROM employees WHERE employee_number LIKE 'BHST%')",
    "DELETE FROM employees WHERE employee_number LIKE 'BHST%'",
    "DELETE FROM companies WHERE code LIKE 'BHC%'",
    "DELETE FROM transport_locations WHERE number LIKE 'BHLOC-%'",
    "DELETE FROM customer_sites WHERE code LIKE 'BHS%'",
];

foreach ($stmts as $sql) {
    $m->query($sql);
    echo basename(strtok($sql, ' ')) . ' ... ' . $m->affected_rows . " rows\n";
}

$m->query('SET FOREIGN_KEY_CHECKS = 1');
echo "Cleanup complete.\n";
