<?php
/**
 * Bulk Bahrain test data for Transport AI planning and related modules.
 *
 * Creates (by default):
 *   - 1,000 customer sites + transport locations (Bahrain)
 *   - 250 companies
 *   - 1,000 active service contracts
 *   - 3,000 staff with shift assignments
 *   - 3,000 contract staff assignments (3 per contract)
 *   - 1,000 contract transport site mappings
 *   - Fleet: depots, 100 vehicles, 80 drivers
 *   - 2,000 transport schedules
 *
 * Run: php scripts/seed_transport_bulk_data.php
 *
 * Safe to re-run: skips if marker employee BHST00001 already exists.
 * To re-seed, delete rows with prefixes BHST/BHC/BHS/BHCT/BHLOC/BHTRP/BHFLEET first.
 */

declare(strict_types=1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

const SEED_SITES      = 1000;
const SEED_COMPANIES  = 250;
const SEED_CONTRACTS  = 1000;
const SEED_EMPLOYEES  = 3000;
const SEED_SCHEDULES  = 2000;
const SEED_VEHICLES   = 100;
const SEED_DRIVERS    = 80;
const SEED_DEPOTS     = 5;
const STAFF_PER_CONTRACT = 3;

$host = '127.0.0.1';
$user = 'root';
$pass = '';
$db   = 'erp_foundation';

$envFile = dirname(__DIR__) . DIRECTORY_SEPARATOR . '.env';
if (is_file($envFile)) {
    foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#' || strpos($line, '=') === false) {
            continue;
        }
        list($k, $v) = array_map('trim', explode('=', $line, 2));
        $v = trim($v, " \t\"'");
        if ($k === 'database.default.hostname') {
            $host = $v;
        } elseif ($k === 'database.default.username') {
            $user = $v;
        } elseif ($k === 'database.default.password') {
            $pass = $v;
        } elseif ($k === 'database.default.database') {
            $db = $v;
        }
    }
}

$m = new mysqli($host, $user, $pass, $db);
$m->set_charset('utf8mb4');

$now   = date('Y-m-d H:i:s');
$today = date('Y-m-d');
$year  = (int) date('Y');

function esc(mysqli $m, ?string $v): string
{
    if ($v === null) {
        return 'NULL';
    }

    return "'" . $m->real_escape_string($v) . "'";
}

function one(mysqli $m, string $sql): ?array
{
    $r = $m->query($sql);

    return $r ? $r->fetch_assoc() : null;
}

function exists(mysqli $m, string $table, string $where): bool
{
    $r = $m->query("SELECT 1 FROM `{$table}` WHERE {$where} LIMIT 1");

    return $r && $r->num_rows > 0;
}

/** @param list<array<string, scalar|null>> $rows */
function batchInsert(mysqli $m, string $table, array $rows, int $chunk = 250): int
{
    if ($rows === []) {
        return 0;
    }

    $inserted = 0;
    $columns  = array_keys($rows[0]);

    foreach (array_chunk($rows, $chunk) as $chunkRows) {
        $valueSets = [];
        foreach ($chunkRows as $row) {
            $vals = [];
            foreach ($columns as $col) {
                $v = $row[$col] ?? null;
                $vals[] = $v === null ? 'NULL' : esc($m, (string) $v);
            }
            $valueSets[] = '(' . implode(',', $vals) . ')';
        }

        $sql = 'INSERT INTO `' . $table . '` (`' . implode('`,`', $columns) . '`) VALUES '
            . implode(',', $valueSets);
        $m->query($sql);
        $inserted += count($chunkRows);
    }

    return $inserted;
}

function bahrainCoord(int $seed, float $baseLat, float $baseLng): array
{
    $latOffset = (($seed * 17) % 200 - 100) / 10000.0;
    $lngOffset = (($seed * 23) % 200 - 100) / 10000.0;

    return [
        'lat' => round($baseLat + $latOffset, 7),
        'lng' => round($baseLng + $lngOffset, 7),
    ];
}

echo "Seeding Transport bulk test data into {$db}...\n";

if (exists($m, 'employees', "employee_number = 'BHST00001' AND deleted_at IS NULL")) {
    echo "Bulk transport data already exists (BHST00001 found). Skipping.\n";
    echo "Delete BH-prefixed seed rows to re-run.\n";
    exit(0);
}

$admin = one($m, "SELECT id FROM users WHERE username = 'admin' AND deleted_at IS NULL LIMIT 1");
$adminId = $admin ? (int) $admin['id'] : 1;

$branch = one($m, "SELECT id FROM branches WHERE code = 'HQ' LIMIT 1");
$branchId = $branch ? (int) $branch['id'] : 1;

$dept = one($m, "SELECT id FROM departments WHERE name = 'Operations' LIMIT 1");
if ($dept) {
    $deptId = (int) $dept['id'];
} else {
    $m->query(
        "INSERT INTO departments (branch_id, name, status, created_at, updated_at)
         VALUES ({$branchId}, 'Operations', 'active', '" . $m->real_escape_string($now) . "', '" . $m->real_escape_string($now) . "')"
    );
    $deptId = (int) $m->insert_id;
}

$shiftRows = [];
$sh = $m->query("SELECT id, code FROM attendance_shifts WHERE status = 'active' ORDER BY id ASC");
while ($row = $sh->fetch_assoc()) {
    $shiftRows[] = $row;
}
if ($shiftRows === []) {
    echo "No attendance shifts found. Run attendance.sql first.\n";
    exit(1);
}
$shiftIds = array_map(function (array $r) {
    return (int) $r['id'];
}, $shiftRows);

$groupMap = [];
$gr = $m->query('SELECT id, code FROM customer_groups');
while ($row = $gr->fetch_assoc()) {
    $groupMap[$row['code']] = (int) $row['id'];
}
$defaultGroupId = $groupMap['STD'] ?? ($groupMap ? reset($groupMap) : null);

$bahrainAreas = [
    ['Manama', 'Capital', 26.2235, 50.5876],
    ['Muharraq', 'Muharraq', 26.2572, 50.6119],
    ['Riffa', 'Southern', 26.1299, 50.5550],
    ['Hamad Town', 'Northern', 26.1153, 50.5065],
    ['Isa Town', 'Central', 26.1736, 50.5478],
    ['Sitra', 'Southern', 26.1547, 50.6206],
    ['Budaiya', 'Northern', 26.2289, 50.4539],
    ['Juffair', 'Capital', 26.2128, 50.6070],
    ['Seef', 'Capital', 26.2361, 50.5411],
    ['Sanad', 'Central', 26.2117, 50.5650],
    ['Zallaq', 'Southern', 26.0536, 50.5136],
    ['Hidd', 'Muharraq', 26.2450, 50.6500],
    ['Aali', 'Central', 26.1550, 50.5250],
    ['Jidhafs', 'Capital', 26.2050, 50.5450],
    ['Tubli', 'Capital', 26.1900, 50.5700],
];

$firstNamesMale   = ['Ahmed', 'Mohammed', 'Hassan', 'Khalid', 'Omar', 'Yusuf', 'Ali', 'Rashid', 'Tariq', 'Faisal', 'Saeed', 'Hamad', 'Jassim', 'Majid', 'Nasser'];
$firstNamesFemale = ['Fatima', 'Noor', 'Maryam', 'Sara', 'Layla', 'Aisha', 'Huda', 'Amira', 'Dana', 'Reem', 'Mariam', 'Latifa', 'Zainab', 'Salma', 'Hessa'];
$lastNames        = ['Al-Khalifa', 'Al-Mannai', 'Al-Jalahma', 'Hassan', 'Ibrahim', 'Ahmed', 'Darwish', 'Mahmood', 'Nasser', 'Abdulla', 'Hameed', 'Saleh', 'Juma', 'Faisal', 'Ali', 'Mohammed', 'Khalifa', 'Al-Binali', 'Al-Dosari', 'Al-Hajri'];
$nationalities    = ['Bahraini', 'Indian', 'Pakistani', 'Filipino', 'Bangladeshi', 'Egyptian', 'Jordanian', 'Nepalese', 'Sri Lankan'];
$industries       = ['Facilities', 'Retail', 'Hospitality', 'Healthcare', 'Construction', 'Logistics', 'Manufacturing', 'Education', 'Banking', 'Technology'];
$siteTypes        = ['office', 'mall', 'warehouse', 'hospital', 'school', 'factory', 'hotel', 'site', 'depot', 'camp'];
$jobTitles        = ['Cleaner', 'Security Guard', 'Technician', 'Supervisor', 'Operator', 'Driver', 'Maintenance', 'Porter', 'Steward', 'Labourer'];
$contractRoles    = ['Staff', 'Supervisor', 'Technician', 'Operator', 'Team Lead'];
$contractTypes    = ['service', 'service', 'service', 'product', 'project'];
$contractStatuses = array_merge(
    array_fill(0, 950, 'active'),
    array_fill(0, 30, 'draft'),
    array_fill(0, 20, 'expired')
);

$m->begin_transaction();

try {
    echo "Creating " . SEED_DEPOTS . " depots...\n";
    $depotCoords = [
        ['Central Depot — Manama', 'Manama', 26.2235, 50.5876],
        ['Northern Depot — Muharraq', 'Muharraq', 26.2572, 50.6119],
        ['Southern Depot — Riffa', 'Riffa', 26.1299, 50.5550],
        ['Western Depot — Budaiya', 'Budaiya', 26.2289, 50.4539],
        ['Eastern Depot — Hidd', 'Hidd', 26.2450, 50.6500],
    ];
    $depotLocationIds = [];
    $depotRows = [];
    foreach ($depotCoords as $i => $depot) {
        $n = $i + 1;
        $coord = bahrainCoord($n, $depot[2], $depot[3]);
        $depotRows[] = [
            'number'         => sprintf('BHLOC-D%03d', $n),
            'name'           => $depot[0],
            'code'           => sprintf('DEP-%03d', $n),
            'location_type'  => 'depot',
            'address'        => 'Industrial Area, ' . $depot[1],
            'city'           => $depot[1],
            'country'        => 'Bahrain',
            'latitude'       => (string) $coord['lat'],
            'longitude'      => (string) $coord['lng'],
            'status'         => 'active',
            'created_by'     => (string) $adminId,
            'created_at'     => $now,
            'updated_at'     => $now,
        ];
    }
    batchInsert($m, 'transport_locations', $depotRows);
    $depotResult = $m->query("SELECT id FROM transport_locations WHERE number LIKE 'BHLOC-D%' ORDER BY id ASC");
    while ($row = $depotResult->fetch_assoc()) {
        $depotLocationIds[] = (int) $row['id'];
    }

    echo 'Creating ' . SEED_SITES . " customer sites + transport locations...\n";
    $siteRows = [];
    $locationRows = [];
    for ($i = 1; $i <= SEED_SITES; $i++) {
        $area = $bahrainAreas[($i - 1) % count($bahrainAreas)];
        $coord = bahrainCoord($i, $area[2], $area[3]);
        $block = 100 + ($i % 900);
        $road  = 4400 + ($i % 600);
        $siteType = $siteTypes[$i % count($siteTypes)];
        $siteName = sprintf('%s %s — Block %d', $area[0], ucfirst($siteType), $block);

        $siteRows[] = [
            'name'        => $siteName,
            'code'        => sprintf('BHS%04d', $i),
            'site_type'   => $siteType,
            'address'     => sprintf('Road %d, Block %d', $road, $block),
            'city'        => $area[0],
            'country'     => 'Bahrain',
            'description' => 'Bulk seed site for transport testing',
            'status'      => 'active',
            'created_by'  => (string) $adminId,
            'created_at'  => $now,
            'updated_at'  => $now,
        ];

        $locationRows[] = [
            'number'         => sprintf('BHLOC-S%05d', $i),
            'name'           => $siteName,
            'code'           => sprintf('SITE-%05d', $i),
            'location_type'  => $i % 7 === 0 ? 'hub' : 'customer',
            'address'        => sprintf('Road %d, Block %d', $road, $block),
            'city'           => $area[0],
            'country'        => 'Bahrain',
            'latitude'       => (string) $coord['lat'],
            'longitude'      => (string) $coord['lng'],
            'status'         => 'active',
            'created_by'     => (string) $adminId,
            'created_at'     => $now,
            'updated_at'     => $now,
        ];
    }
    batchInsert($m, 'customer_sites', $siteRows);
    batchInsert($m, 'transport_locations', $locationRows);

    $customerSiteIds = [];
    $siteIdResult = $m->query("SELECT id FROM customer_sites WHERE code LIKE 'BHS%' ORDER BY code ASC");
    while ($row = $siteIdResult->fetch_assoc()) {
        $customerSiteIds[] = (int) $row['id'];
    }

    $transportSiteIds = [];
    $locIdResult = $m->query("SELECT id FROM transport_locations WHERE number LIKE 'BHLOC-S%' ORDER BY number ASC");
    while ($row = $locIdResult->fetch_assoc()) {
        $transportSiteIds[] = (int) $row['id'];
    }

    echo 'Creating ' . SEED_COMPANIES . " companies...\n";
    $companyRows = [];
    for ($i = 1; $i <= SEED_COMPANIES; $i++) {
        $area = $bahrainAreas[($i - 1) % count($bahrainAreas)];
        $industry = $industries[$i % count($industries)];
        $siteIdx = ($i - 1) % count($customerSiteIds);
        $groupCodes = ['STD', 'VIP', 'PARTNER'];
        $groupCode = $groupCodes[$i % 3];
        $companyRows[] = [
            'name'              => sprintf('%s Services WLL — %s', $industry, $area[0]),
            'code'              => sprintf('BHC%04d', $i),
            'email'             => sprintf('contact%04d@bhseed.demo', $i),
            'phone'             => sprintf('+97317%05d', 10000 + $i),
            'industry'          => $industry,
            'customer_group_id' => isset($groupMap[$groupCode]) ? (string) $groupMap[$groupCode] : ($defaultGroupId !== null ? (string) $defaultGroupId : null),
            'customer_type'     => $contractTypes[$i % count($contractTypes)],
            'is_one_time'       => '0',
            'site_id'           => (string) $customerSiteIds[$siteIdx],
            'address'           => sprintf('Office %d, %s', $i, $area[0]),
            'city'              => $area[0],
            'country'           => 'Bahrain',
            'status'            => 'active',
            'notes'             => 'Bulk seed company',
            'created_by'        => (string) $adminId,
            'created_at'        => $now,
            'updated_at'        => $now,
        ];
    }
    batchInsert($m, 'companies', $companyRows);

    $companyIds = [];
    $companyResult = $m->query("SELECT id FROM companies WHERE code LIKE 'BHC%' ORDER BY code ASC");
    while ($row = $companyResult->fetch_assoc()) {
        $companyIds[] = (int) $row['id'];
    }

    echo 'Creating ' . SEED_CONTRACTS . " contracts...\n";
    $contractRows = [];
    $contractLineRows = [];
    for ($i = 1; $i <= SEED_CONTRACTS; $i++) {
        $companyIdx = ($i - 1) % count($companyIds);
        $siteIdx = ($i - 1) % count($customerSiteIds);
        $area = $bahrainAreas[($i - 1) % count($bahrainAreas)];
        $ctype = $contractTypes[$i % count($contractTypes)];
        $cstatus = $contractStatuses[$i - 1];
        $startOffset = -6 - ($i % 12);
        $endOffset = 6 + ($i % 18);
        $startDate = date('Y-m-d', strtotime("{$startOffset} months"));
        $endDate = date('Y-m-d', strtotime("+{$endOffset} months"));
        if ($cstatus === 'expired') {
            $startDate = date('Y-m-d', strtotime('-24 months'));
            $endDate = date('Y-m-d', strtotime('-2 months'));
        } elseif ($cstatus === 'draft') {
            $startDate = date('Y-m-d', strtotime('+1 month'));
            $endDate = date('Y-m-d', strtotime('+13 months'));
        }
        $value = round(500 + ($i % 50) * 175 + ($i % 7) * 250, 2);
        $contractRows[] = [
            'number'          => sprintf('BHCT-%05d', $i),
            'title'           => sprintf('%s Contract — %s Site %d', ucfirst($ctype), $area[0], $siteIdx + 1),
            'company_id'      => (string) $companyIds[$companyIdx],
            'contract_type'   => $ctype,
            'status'          => $cstatus,
            'start_date'      => $startDate,
            'end_date'        => $endDate,
            'value'           => (string) $value,
            'total_hours'     => (string) (160 * (12 + ($i % 6))),
            'hourly_rate'     => (string) round($value / max(1, 160 * 6), 2),
            'location'        => $area[0],
            'project_name'    => sprintf('Project %s-%d', $area[0], $i),
            'notes'           => 'Bulk seed contract for transport AI testing',
            'created_by'      => (string) $adminId,
            'created_at'      => $now,
            'updated_at'      => $now,
        ];
    }
    batchInsert($m, 'contracts', $contractRows);

    $contractIds = [];
    $contractMeta = [];
    $contractResult = $m->query(
        "SELECT id, number, status, start_date, end_date FROM contracts WHERE number LIKE 'BHCT-%' ORDER BY number ASC"
    );
    while ($row = $contractResult->fetch_assoc()) {
        $contractIds[] = (int) $row['id'];
        $contractMeta[(int) $row['id']] = $row;
    }

    foreach ($contractIds as $idx => $contractId) {
        $i = $idx + 1;
        $unitPrice = round(400 + ($i % 20) * 25, 2);
        $qty = 6 + ($i % 12);
        $contractLineRows[] = [
            'contract_id' => (string) $contractId,
            'item_code'   => sprintf('SVC-%04d', ($i % 100) + 1),
            'item_name'   => 'Monthly service package',
            'quantity'    => (string) $qty,
            'unit'        => 'month',
            'unit_price'  => (string) $unitPrice,
            'line_total'  => (string) round($unitPrice * $qty, 2),
            'sort_order'  => '1',
            'created_at'  => $now,
        ];
    }
    batchInsert($m, 'contract_lines', $contractLineRows);

    echo 'Creating ' . SEED_EMPLOYEES . " staff...\n";
    $employeeRows = [];
    for ($i = 1; $i <= SEED_EMPLOYEES; $i++) {
        $isFemale = $i % 3 === 0;
        $first = $isFemale ? $firstNamesFemale[$i % count($firstNamesFemale)] : $firstNamesMale[$i % count($firstNamesMale)];
        $last = $lastNames[$i % count($lastNames)];
        $area = $bahrainAreas[($i - 1) % count($bahrainAreas)];
        $block = 200 + ($i % 800);
        $employeeRows[] = [
            'employee_number'         => sprintf('BHST%05d', $i),
            'full_name'               => $first . ' ' . $last,
            'department_id'           => (string) $deptId,
            'branch_id'               => (string) $branchId,
            'job_title'               => $jobTitles[$i % count($jobTitles)],
            'email'                   => sprintf('staff%05d@bhseed.demo', $i),
            'phone'                   => sprintf('+97336%06d', 100000 + $i),
            'mobile'                  => sprintf('+97366%06d', 100000 + $i),
            'cpr_number'              => sprintf('9%09d', 100000000 + $i),
            'nationality'             => $nationalities[$i % count($nationalities)],
            'date_of_birth'           => date('Y-m-d', strtotime(sprintf('-%d years', 22 + ($i % 28)))),
            'gender'                  => $isFemale ? 'female' : 'male',
            'marital_status'          => $i % 2 ? 'married' : 'single',
            'address'                 => sprintf('Building %d, Road %d, Block %d', ($i % 90) + 10, 4400 + ($i % 500), $block),
            'city'                    => $area[0],
            'country'                 => 'Bahrain',
            'emergency_contact_name'  => 'Emergency Contact',
            'emergency_contact_phone' => sprintf('+97339%06d', 100000 + $i),
            'hire_date'               => date('Y-m-d', strtotime(sprintf('-%d months', 3 + ($i % 60)))),
            'employment_type'         => $i % 11 === 0 ? 'part_time' : 'full_time',
            'bank_name'               => $i % 2 ? 'NBB' : 'BBK',
            'bank_account'            => sprintf('BH00SEED%010d', $i),
            'status'                  => $i % 97 === 0 ? 'inactive' : 'active',
            'created_by'              => (string) $adminId,
            'created_at'              => $now,
            'updated_at'              => $now,
        ];
    }
    batchInsert($m, 'employees', $employeeRows, 200);

    $employeeIds = [];
    $activeEmployeeIds = [];
    $empResult = $m->query(
        "SELECT id, status FROM employees WHERE employee_number LIKE 'BHST%' ORDER BY employee_number ASC"
    );
    while ($row = $empResult->fetch_assoc()) {
        $id = (int) $row['id'];
        $employeeIds[] = $id;
        if ($row['status'] === 'active') {
            $activeEmployeeIds[] = $id;
        }
    }

    echo "Assigning shifts to staff...\n";
    $shiftAssignmentRows = [];
    foreach ($employeeIds as $idx => $employeeId) {
        $shiftAssignmentRows[] = [
            'employee_id'    => (string) $employeeId,
            'shift_id'       => (string) $shiftIds[$idx % count($shiftIds)],
            'effective_from' => date('Y-m-d', strtotime('-12 months')),
            'created_by'     => (string) $adminId,
            'created_at'     => $now,
            'updated_at'     => $now,
        ];
    }
    batchInsert($m, 'employee_shift_assignments', $shiftAssignmentRows, 300);

    echo 'Assigning staff to contracts (' . STAFF_PER_CONTRACT . " per contract)...\n";
    $contractEmployeeRows = [];
    $empPointer = 0;
    foreach ($contractIds as $contractIdx => $contractId) {
        for ($s = 0; $s < STAFF_PER_CONTRACT; $s++) {
            if ($empPointer >= count($activeEmployeeIds)) {
                break 2;
            }
            $employeeId = $activeEmployeeIds[$empPointer++];
            $contractEmployeeRows[] = [
                'contract_id'      => (string) $contractId,
                'employee_id'      => (string) $employeeId,
                'role_on_contract' => $contractRoles[($contractIdx + $s) % count($contractRoles)],
                'hours_allocated'  => (string) (160 + ($s * 40)),
                'created_at'       => $now,
            ];
        }
    }
    batchInsert($m, 'contract_employees', $contractEmployeeRows, 300);

    echo 'Mapping contract transport sites...' . "\n";
    $contractSiteRows = [];
    foreach ($contractIds as $idx => $contractId) {
        if (($contractMeta[$contractId]['status'] ?? '') !== 'active') {
            continue;
        }
        $contractSiteRows[] = [
            'contract_id'           => (string) $contractId,
            'transport_location_id' => (string) $transportSiteIds[$idx % count($transportSiteIds)],
            'default_shift_id'      => (string) $shiftIds[$idx % count($shiftIds)],
            'status'                => 'active',
            'notes'                 => 'Bulk seed contract site mapping',
            'created_by'            => (string) $adminId,
            'created_at'            => $now,
            'updated_at'            => $now,
        ];
    }
    batchInsert($m, 'transport_contract_sites', $contractSiteRows);

    echo 'Creating fleet (' . SEED_VEHICLES . ' vehicles, ' . SEED_DRIVERS . " drivers)...\n";
    $vehicleTypes = ['van', 'van', 'van', 'bus', 'car', 'truck'];
    $makes = ['Toyota', 'Nissan', 'Hyundai', 'Mercedes', 'Ford', 'Isuzu'];
    $vehicleRows = [];
    for ($i = 1; $i <= SEED_VEHICLES; $i++) {
        $vtype = $vehicleTypes[$i % count($vehicleTypes)];
        if ($vtype === 'bus') {
            $capacity = 30 + ($i % 15);
        } elseif ($vtype === 'van') {
            $capacity = 12 + ($i % 8);
        } elseif ($vtype === 'car') {
            $capacity = 4;
        } elseif ($vtype === 'truck') {
            $capacity = 8;
        } else {
            $capacity = 15;
        }
        $vehicleRows[] = [
            'number'                => sprintf('BHFLEET-V%04d', $i),
            'registration_number'   => sprintf('BH-%04d-%s', 1000 + $i, chr(65 + ($i % 26))),
            'make'                  => $makes[$i % count($makes)],
            'model'                 => $vtype === 'bus' ? 'Coaster' : ($vtype === 'van' ? 'Hiace' : 'Model-' . ($i % 50)),
            'year'                  => (string) (2018 + ($i % 7)),
            'vehicle_type'          => $vtype,
            'fuel_type'             => $i % 5 === 0 ? 'diesel' : 'petrol',
            'capacity'              => (string) $capacity,
            'fuel_consumption_l_per_100km' => $vtype === 'bus' ? '22.00' : ($vtype === 'van' ? '12.00' : '8.00'),
            'odometer_km'           => (string) (10000 + $i * 137),
            'current_location_id'   => (string) $depotLocationIds[$i % count($depotLocationIds)],
            'latitude'              => null,
            'longitude'             => null,
            'status'                => $i % 19 === 0 ? 'maintenance' : 'active',
            'created_by'            => (string) $adminId,
            'created_at'            => $now,
            'updated_at'            => $now,
        ];
    }
    batchInsert($m, 'transport_vehicles', $vehicleRows);

    $vehicleIds = [];
    $activeVehicleIds = [];
    $vehResult = $m->query("SELECT id, status FROM transport_vehicles WHERE number LIKE 'BHFLEET-%' ORDER BY number ASC");
    while ($row = $vehResult->fetch_assoc()) {
        $id = (int) $row['id'];
        $vehicleIds[] = $id;
        if ($row['status'] === 'active') {
            $activeVehicleIds[] = $id;
        }
    }

    $driverRows = [];
    for ($i = 1; $i <= SEED_DRIVERS; $i++) {
        if (! isset($activeEmployeeIds[$i - 1])) {
            break;
        }
        $driverRows[] = [
            'number'          => sprintf('BHFLEET-D%04d', $i),
            'employee_id'     => (string) $activeEmployeeIds[$i - 1],
            'license_number'  => sprintf('BH-LIC-%06d', 200000 + $i),
            'license_class'   => $i % 4 === 0 ? 'C' : 'B',
            'license_expiry'  => date('Y-m-d', strtotime('+' . (6 + ($i % 24)) . ' months')),
            'phone'           => sprintf('+97339%06d', 200000 + $i),
            'status'          => $i % 13 === 0 ? 'inactive' : 'active',
            'created_by'      => (string) $adminId,
            'created_at'      => $now,
            'updated_at'      => $now,
        ];
    }
    batchInsert($m, 'transport_drivers', $driverRows);

    $driverIds = [];
    $activeDriverIds = [];
    $drvResult = $m->query("SELECT id, status FROM transport_drivers WHERE number LIKE 'BHFLEET-D%' ORDER BY number ASC");
    while ($row = $drvResult->fetch_assoc()) {
        $id = (int) $row['id'];
        $driverIds[] = $id;
        if ($row['status'] === 'active') {
            $activeDriverIds[] = $id;
        }
    }

    echo 'Creating transport routes...' . "\n";
    $routeRows = [];
    $routeCount = min(500, count($transportSiteIds));
    for ($i = 1; $i <= $routeCount; $i++) {
        $origin = $depotLocationIds[$i % count($depotLocationIds)];
        $dest = $transportSiteIds[($i - 1) % count($transportSiteIds)];
        $routeRows[] = [
            'number'                     => sprintf('BHRT-%05d', $i),
            'name'                       => sprintf('Depot Route %d', $i),
            'code'                       => sprintf('R-%05d', $i),
            'origin_location_id'         => (string) $origin,
            'destination_location_id'    => (string) $dest,
            'distance_km'                => (string) round(5 + ($i % 35) + ($i % 7) * 1.5, 2),
            'estimated_duration_minutes' => (string) (15 + ($i % 45)),
            'status'                     => 'active',
            'created_by'                 => (string) $adminId,
            'created_at'                 => $now,
            'updated_at'                 => $now,
        ];
    }
    batchInsert($m, 'transport_routes', $routeRows);

    $routeIds = [];
    $routeResult = $m->query("SELECT id FROM transport_routes WHERE number LIKE 'BHRT-%' ORDER BY number ASC");
    while ($row = $routeResult->fetch_assoc()) {
        $routeIds[] = (int) $row['id'];
    }

    echo 'Creating ' . SEED_SCHEDULES . " transport schedules...\n";
    $scheduleStatuses = array_merge(
        array_fill(0, 600, 'planned'),
        array_fill(0, 700, 'scheduled'),
        array_fill(0, 400, 'completed'),
        array_fill(0, 200, 'in_progress'),
        array_fill(0, 100, 'cancelled')
    );
    $priorities = ['low', 'normal', 'normal', 'normal', 'high', 'urgent'];
    $scheduleRows = [];
    for ($i = 1; $i <= SEED_SCHEDULES; $i++) {
        $dayOffset = ($i % 60) - 30;
        $scheduleDate = date('Y-m-d', strtotime("{$dayOffset} days"));
        $status = $scheduleStatuses[$i - 1];
        $depHour = 5 + ($i % 4);
        $depMin = ($i % 4) * 15;
        $arrHour = $depHour + 1 + ($i % 2);
        $arrMin = ($depMin + 30) % 60;
        $routeIdx = ($i - 1) % max(1, count($routeIds));
        $driverIdx = ($i - 1) % max(1, count($activeDriverIds));
        $vehicleIdx = ($i - 1) % max(1, count($activeVehicleIds));

        $row = [
            'number'          => sprintf('BHTRP-%06d', $i),
            'title'           => sprintf('Workforce Run %d — %s', $i, date('d M', strtotime($scheduleDate))),
            'route_id'        => $routeIds !== [] ? (string) $routeIds[$routeIdx] : null,
            'vehicle_id'      => $activeVehicleIds !== [] ? (string) $activeVehicleIds[$vehicleIdx] : null,
            'driver_id'       => $activeDriverIds !== [] ? (string) $activeDriverIds[$driverIdx] : null,
            'schedule_date'   => $scheduleDate,
            'departure_time'  => sprintf('%02d:%02d:00', $depHour, $depMin),
            'arrival_time'    => sprintf('%02d:%02d:00', $arrHour, $arrMin),
            'priority'        => $priorities[$i % count($priorities)],
            'status'          => $status,
            'notes'           => 'Bulk seed transport schedule',
            'created_by'      => (string) $adminId,
            'created_at'      => $now,
            'updated_at'      => $now,
        ];

        if ($status === 'completed') {
            $row['started_at'] = $scheduleDate . ' ' . $row['departure_time'];
            $row['completed_at'] = $scheduleDate . ' ' . $row['arrival_time'];
        } elseif ($status === 'in_progress') {
            $row['started_at'] = $scheduleDate . ' ' . $row['departure_time'];
        }

        $scheduleRows[] = $row;
    }
    batchInsert($m, 'transport_schedules', $scheduleRows, 200);

    $m->commit();

    $counts = [
        'customer_sites'            => one($m, "SELECT COUNT(*) AS c FROM customer_sites WHERE code LIKE 'BHS%'")['c'] ?? 0,
        'companies'                 => one($m, "SELECT COUNT(*) AS c FROM companies WHERE code LIKE 'BHC%'")['c'] ?? 0,
        'contracts'                 => one($m, "SELECT COUNT(*) AS c FROM contracts WHERE number LIKE 'BHCT-%'")['c'] ?? 0,
        'employees'                 => one($m, "SELECT COUNT(*) AS c FROM employees WHERE employee_number LIKE 'BHST%'")['c'] ?? 0,
        'contract_employees'        => one($m, 'SELECT COUNT(*) AS c FROM contract_employees ce JOIN employees e ON e.id = ce.employee_id WHERE e.employee_number LIKE \'BHST%\'')['c'] ?? 0,
        'transport_contract_sites'  => one($m, 'SELECT COUNT(*) AS c FROM transport_contract_sites tcs JOIN contracts c ON c.id = tcs.contract_id WHERE c.number LIKE \'BHCT-%\'')['c'] ?? 0,
        'transport_locations'       => one($m, "SELECT COUNT(*) AS c FROM transport_locations WHERE number LIKE 'BHLOC-%'")['c'] ?? 0,
        'transport_vehicles'        => one($m, "SELECT COUNT(*) AS c FROM transport_vehicles WHERE number LIKE 'BHFLEET-%'")['c'] ?? 0,
        'transport_drivers'         => one($m, "SELECT COUNT(*) AS c FROM transport_drivers WHERE number LIKE 'BHFLEET-D%'")['c'] ?? 0,
        'transport_schedules'       => one($m, "SELECT COUNT(*) AS c FROM transport_schedules WHERE number LIKE 'BHTRP-%'")['c'] ?? 0,
        'active_plannable_contracts'=> one($m, "SELECT COUNT(*) AS c FROM transport_contract_sites tcs JOIN contracts c ON c.id = tcs.contract_id JOIN transport_locations l ON l.id = tcs.transport_location_id WHERE c.number LIKE 'BHCT-%' AND c.status = 'active' AND tcs.status = 'active' AND l.latitude IS NOT NULL")['c'] ?? 0,
    ];

    echo "\n==============================================\n";
    echo " Transport bulk test data ready\n";
    echo "----------------------------------------------\n";
    foreach ($counts as $label => $count) {
        echo sprintf(" %-28s %s\n", $label . ':', number_format((int) $count));
    }
    echo "----------------------------------------------\n";
    echo " Next: Transport → AI Planning → Generate Plan\n";
    echo " Prefixes: BHST/BHC/BHS/BHCT/BHLOC/BHTRP/BHFLEET\n";
    echo "==============================================\n";
} catch (Throwable $e) {
    $m->rollback();
    fwrite(STDERR, 'Seed failed: ' . $e->getMessage() . "\n");
    exit(1);
}
