<?php
/**
 * Import fleet vehicles, drivers, and assignments from the fleet management document.
 * Usage: php import_fleet_from_document.php
 */

require_once __DIR__ . '/config/database.php';

// Normalize plate: strip all spaces and uppercase
function normalizePlate($p) {
    return strtoupper(preg_replace('/\s+/', '', trim($p)));
}

// Find vehicle ID by normalized plate
function findVehicleId($pdo, $plate) {
    $norm = normalizePlate($plate);
    $stmt = $pdo->prepare("SELECT id FROM vehicles WHERE REPLACE(UPPER(plate_number), ' ', '') = ?");
    $stmt->execute([$norm]);
    return $stmt->fetchColumn() ?: null;
}

// Find driver ID by full name
function findDriverId($pdo, $name) {
    $name = trim($name);
    if (!$name) return null;
    $stmt = $pdo->prepare("SELECT id FROM drivers WHERE full_name = ?");
    $stmt->execute([$name]);
    return $stmt->fetchColumn() ?: null;
}

// Find contract_vehicle_type ID by name
function findContractTypeId($pdo, $name) {
    $name = trim($name);
    if (!$name) return null;
    $stmt = $pdo->prepare("SELECT id FROM contract_vehicle_types WHERE name = ?");
    $stmt->execute([$name]);
    return $stmt->fetchColumn() ?: null;
}

// The fleet data parsed from the document.
// Structure: [plate, vehicle_type, vehicle_model, vehicle_use_type, year, driver_name, driver_phone, contract_vehicle_type]
// Empty driver_name or phone = no driver
$fleet = [
    ['AAA270YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, '', ''],
    ['AAA271YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, 'Ganiyu Waheed Akolawole Olanrewaju', ''],
    ['AAA272YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, 'Ige David Bukola', ''],
    ['AAA273YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, 'Nwachukwu Chibuzor', ''],
    ['AAA383YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, 'Raymond Etim', ''],
    ['AAA808JV', 'Toyota', 'Toyota Prado 2024', 'Commercial', 2024, 'Lukmon Odedina', '+2348063630100'],
    ['ABC553DH', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, '', ''],
    ['AGL128JW', 'Toyota', 'Highlander 2019', 'Commercial', 2019, 'Ayoola Ayomidipupo Emma', '+2347046150764'],
    ['AGL129JW', 'Toyota', 'Highlander 2019', 'Commercial', 2019, 'Bolarinwa Lukman', '+2349126062065'],
    ['AGL130JW', 'Toyota', 'Highlander 2019', 'Commercial', 2019, 'Hussein Abdulgafar Bayo', '+2348148659335'],
    ['AGL905KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, '', ''],
    ['AGL906KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, 'Umoren Daniel Adekunle Mathew', '+2347033439116'],
    ['AGL907KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, 'Olatunbosun Oladiti', '+2348035749555'],
    ['AGL908KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, 'Michael Ogunaike', '+2348028554084'],
    ['AGL909KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, '', ''],
    ['AGL910KP', 'Toyota', 'Toyota Prado 2025', 'Commercial', 2025, '', ''],
    ['AKD574DX', 'Toyota', 'Landcruiser 2015', 'Commercial', 2015, '', ''],
    ['AKD939YM', 'Pickup', 'Himla 2025 Model', 'Commercial', 2025, '', ''],
    ['EKY988JU', 'Toyota', 'Toyota Prado 2024', 'Commercial', 2024, 'Julius Olakonu', '+2348062939018'],
    ['EPE980JU', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Emmanuel Asu', '+2349138547963'],
    ['FST257YL', 'Pickup', 'Foton', 'Commercial', 2025, '', ''],
    ['FST258YL', 'Pickup', 'Foton', 'Commercial', 2025, '', ''],
    ['FST259YL', 'Pickup', 'Foton', 'Commercial', 2025, '', ''],
    ['FST255YL', 'Pickup', 'Foton', 'Commercial', 2025, '', ''],
    ['FST256YL', 'Pickup', 'Foton', 'Commercial', 2025, '', ''],
    ['GGE60HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, 'Adebayo Shobanjo', '+2348023186157'],
    ['GGE61HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, 'Nasiru Lawal', '+2349165489542'],
    ['GGE62HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, 'David Ochgbo', '+2348036540660'],
    ['GGE63HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, 'James Mayowa Abraham', '+2347064699921'],
    ['GGE65HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, '', ''],
    ['GGE66HG', 'Toyota', 'Toyota Landcruiser 2022', 'Commercial', 2022, 'Shola Richard', '+2347070845527'],
    ['GGE67HG', 'Toyota', 'Toyota Prado 2022', 'Commercial', 2022, 'Benedict Monday', '+2348036060752'],
    ['GGE68HG', 'Toyota', 'Toyota Landcruiser 2022', 'Commercial', 2022, 'Reuben Nse Effiong', '+2349131293859'],
    ['GGE886JV', 'Toyota', 'Toyota Prado 2024', 'Commercial', 2024, 'Mathew Nehemiah', '+2348167544748'],
    ['JJJ695JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Orhempaga Gabriel Olohedegwa', '+2348024906023'],
    ['JJJ696JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Iorwuese Ushahemba', '+2347035014403'],
    ['JJJ697JV', 'Chery', 'Jetour X70 Plus', 'Commercial', 2025, 'Thomas Bisong', '+2347074760123'],
    ['JJJ698JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Olaniyi Omotola', '+2348140206978'],
    ['JJJ699JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Akinsinde Solomon', '+2347076954676'],
    ['JJJ700JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Shodunke Sodiq', '+2349068828947'],
    ['JJJ701JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Aminullahi Taiye', '+2348137572590'],
    ['JJJ702JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Sulaimon Akudu', '+2348030519497'],
    ['JJJ703JV', 'Chery', 'Jetour X70', 'Commercial', 2025, 'Victor Chinazor', '+2347078026374'],
    ['JJJ709JV', 'Chery', 'Jetour X70 Plus', 'Commercial', 2025, 'Segun Odutola', '+2349034495700'],
    ['KRD67KF', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Oladele Derek Oiji', '+2348025572820'],
    ['KRD68KF', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Ademola Imoudu', '+2348061772742'],
    ['KRD69KF', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Okon Daniel Eyo', '+2348099446631'],
    ['KRD70KF', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Yinusa Nurudeen', '+2348039553926'],
    ['KSF557JX', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Sunday Eniola', '+2348060649374'],
    ['KSF558JX', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Joseph Akinyemi', '+2348160808569'],
    ['KSF559JX', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Ezulike Uchenna Julian', '+2347037549750'],
    ['KSF560JX', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, '', ''],
    ['KSF561JX', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, '', ''],
    ['KSF580JU', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, '', ''],
    ['KSF960JU', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Utolo Innocent Olofu Dele', '+2348039553893'],
    ['KSF961JU', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Oriade Oluwasegun', '+2347074800599'],
    ['KSF962JU', 'Chery', 'Tiggo8 Pro 2024 Model', 'Commercial', 2024, 'Azuamairo Ifeanyi', '+2349136674468'],
];

// Map vehicle make -> vehicle_type in DB convention
$typeMap = [
    'Pickup' => 'PICKUP',
    'Toyota' => 'OFF-ROAD',
    'Chery'  => 'SUV',
];

// Map document model description -> DB vehicle_model convention
$modelMap = [
    'Himla 2025 Model'          => 'Cherry HIMLA',
    'Toyota Prado 2024'         => 'Toyota Land Cruiser Prado',
    'Toyota Prado 2025'         => 'Toyota Land Cruiser Prado',
    'Toyota Prado 2022'         => 'Toyota Land Cruiser Prado',
    'Highlander 2019'           => 'Toyota Highlander',
    'Landcruiser 2015'          => 'Toyota Land Cruiser',
    'Toyota Landcruiser 2022'   => 'Toyota Land Cruiser',
    'Tiggo8 Pro 2024 Model'     => 'Chery Tiggo',
    'Jetour X70'                => 'Jetour X70',
    'Jetour X70 Plus'           => 'Jetour X70',
    'Foton'                     => 'Foton Tunland',
];

// Map document description -> contract_vehicle_type name
$contractTypeMap = [
    'Himla 2025 Model'          => 'Himla 2025 Model',
    'Toyota Prado 2024'         => 'Toyota Prado 2024',
    'Toyota Prado 2025'         => 'Toyota Prado 2025',
    'Toyota Prado 2022'         => 'Toyota Prado 2022',
    'Highlander 2019'           => 'Highlander 2019',
    'Landcruiser 2015'          => 'Landcruiser 2015',
    'Toyota Landcruiser 2022'   => 'Toyota Landcruiser 2022',
    'Tiggo8 Pro 2024 Model'     => 'Tiggo8 Pro 2024 Model',
    'Jetour X70'                => 'Jetour X70',
    'Jetour X70 Plus'           => 'Jetour X70 Plus',
    'Foton'                     => 'Foton',
];

$pdo->beginTransaction();
try {
    $importedVehicles = 0;
    $importedDrivers = 0;
    $importedAssignments = 0;
    $skipped = 0;

    // Pre-find all existing vehicle IDs
    $existingIds = [];

    foreach ($fleet as $i => $row) {
        $line = $i + 1;
        $plate = trim($row[0]);
        $make = $row[1];
        $modelDesc = $row[2];
        $useType = $row[3];
        $year = $row[4];
        $driverName = trim($row[5]);
        $driverPhone = trim($row[6]);

        $normPlate = normalizePlate($plate);
        if (!$normPlate) {
            echo "Line {$line}: Skipping empty plate\n";
            $skipped++;
            continue;
        }

        // Find or create vehicle
        $vehicleId = findVehicleId($pdo, $plate);
        if (!$vehicleId) {
            $dbType = $typeMap[$make] ?? strtoupper($make);
            $dbModel = $modelMap[$modelDesc] ?? $modelDesc;
            $contractTypeName = $contractTypeMap[$modelDesc] ?? $modelDesc;

            // Look up contract_vehicle_type
            $contractTypeId = findContractTypeId($pdo, $contractTypeName);

            $stmt = $pdo->prepare("INSERT INTO vehicles 
                (plate_number, vehicle_type, vehicle_model, vehicle_use_type, year_of_manufacturing, vehicle_status, contract_id)
                VALUES (?, ?, ?, ?, ?, 'Active', ?)");
            $stmt->execute([$plate, $dbType, $dbModel, $useType, $year, $contractTypeId]);
            $vehicleId = $pdo->lastInsertId();
            $importedVehicles++;
            echo "  Created vehicle: {$plate} ({$dbType} / {$dbModel})\n";
        } else {
            echo "  Found existing vehicle: {$plate} (id={$vehicleId})\n";
        }

        // Create driver and assignment if driver name present
        if ($driverName) {
            $driverId = findDriverId($pdo, $driverName);
            if (!$driverId) {
                // Split name into parts for surname/first_name
                $parts = explode(' ', $driverName);
                $surname = array_shift($parts);
                $firstName = $parts ? implode(' ', $parts) : '';

                $code = 'DRV-' . str_pad((string)random_int(1000, 9999), 4, '0', STR_PAD_LEFT);
                $stmt = $pdo->prepare("INSERT INTO drivers 
                    (driver_id_code, full_name, surname, first_name, phone, driver_status, active_status, status)
                    VALUES (?, ?, ?, ?, ?, 'Active', 1, 'Active')");
                $stmt->execute([$code, $driverName, $surname, $firstName, $driverPhone ?: null]);
                $driverId = $pdo->lastInsertId();
                $importedDrivers++;
                echo "    Created driver: {$driverName}\n";
            } else {
                // Update phone if missing and we have one
                if ($driverPhone) {
                    $stmt = $pdo->prepare("UPDATE drivers SET phone = COALESCE(NULLIF(phone, ''), ?) WHERE id = ? AND (phone IS NULL OR phone = '')");
                    $stmt->execute([$driverPhone, $driverId]);
                }
                echo "    Found existing driver: {$driverName} (id={$driverId})\n";
            }

            // Create vehicle_assignment (if not already assigned)
            $stmt = $pdo->prepare("SELECT id FROM vehicle_assignments 
                WHERE vehicle_id = ? AND driver_id = ? AND status = 'active'
                AND (assignment_end IS NULL OR assignment_end >= CURDATE())");
            $stmt->execute([$vehicleId, $driverId]);
            if (!$stmt->fetchColumn()) {
                $stmt = $pdo->prepare("INSERT INTO vehicle_assignments 
                    (vehicle_id, driver_id, assignment_start, status)
                    VALUES (?, ?, CURDATE(), 'active')");
                $stmt->execute([$vehicleId, $driverId]);
                $importedAssignments++;
                echo "    Assigned driver to vehicle\n";
            } else {
                echo "    Driver already assigned to this vehicle\n";
            }
        }
    }

    $pdo->commit();

    echo "\n=== Import Summary ===\n";
    echo "Vehicles imported: {$importedVehicles}\n";
    echo "Drivers imported:  {$importedDrivers}\n";
    echo "Assignments made:  {$importedAssignments}\n";
    echo "Skipped (errors):  {$skipped}\n";

} catch (Throwable $e) {
    $pdo->rollBack();
    echo "ERROR: Import failed, transaction rolled back.\n";
    echo $e->getMessage() . "\n";
    echo $e->getTraceAsString() . "\n";
    exit(1);
}
