<?php
require_once 'config/database.php';

$csvFile = __DIR__ . '/vehicles_import.csv';
if (!file_exists($csvFile)) {
    echo "CSV file not found: $csvFile\n";
    echo "Please export your spreadsheet to vehicles_import.csv in the project root.\n";
    exit(1);
}

if (($handle = fopen($csvFile, 'r')) === false) {
    echo "Unable to open CSV file.\n";
    exit(1);
}

$header = fgetcsv($handle);
if (!$header) {
    echo "CSV file is empty or malformed.\n";
    exit(1);
}

$allowedColumns = [
    'plate_number', 'owner', 'property', 'vehicle_type', 'vehicle_model',
    'year_of_manufacturing', 'displacement', 'seats', 'vehicle_use_type',
    'vehicle_status', 'vehicle_source', 'supplier_id', 'contract_id',
    'need_obd', 'obd_device_no', 'use_mr_car', 'standard_fuel_consumption_l_per_100km',
    'efficiency_baseline_km', 'registration_date', 'first_effective_date',
    'entry_date', 'exit_date', 'country_id', 'city_id', 'region_id',
    'department_id', 'project_id', 'office_id', 'location_id', 'schedule_id',
    'schedule_expiration_date'
];

$columns = array_map('trim', $header);
$columns = array_map('strtolower', $columns);

$unknown = array_diff($columns, $allowedColumns);
if ($unknown) {
    echo "The following CSV columns are not recognized and will be ignored:\n";
    echo implode(', ', $unknown) . "\n";
}

$columns = array_values(array_intersect($columns, $allowedColumns));
if (empty($columns)) {
    echo "No valid columns found in CSV header.\n";
    exit(1);
}

$insertColumns = $columns;
$placeholders = array_map(fn($c) => ':' . $c, $insertColumns);
$sql = 'INSERT INTO vehicles (' . implode(', ', $insertColumns) . ') VALUES (' . implode(', ', $placeholders) . ')';
$stmt = $pdo->prepare($sql);

$imported = 0;
$skipped = 0;
$rowNumber = 1;

function normalizeDate(string $value): ?string {
    $value = trim($value);
    if ($value === '') {
        return null;
    }

    $formats = ['Y-m-d', 'd m Y', 'd/m/Y', 'd-M-Y', 'd F Y'];
    foreach ($formats as $format) {
        $date = DateTime::createFromFormat($format, $value);
        if ($date && $date->format($format) === $value) {
            return $date->format('Y-m-d');
        }
    }

    $date = date_create($value);
    return $date ? $date->format('Y-m-d') : null;
}

function resolveReferenceId(PDO $pdo, string $table, string $field, string $value): ?int {
    if ($value === '') {
        return null;
    }
    if (is_numeric($value)) {
        return (int)$value;
    }

    $stmt = $pdo->prepare("SELECT id FROM {$table} WHERE {$field} = :value LIMIT 1");
    $stmt->execute(['value' => trim($value)]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ? (int)$row['id'] : null;
}

while (($row = fgetcsv($handle)) !== false) {
    $rowNumber++;
    if (!is_array($row) || count(array_filter($row, fn($v) => trim((string)$v) !== '')) === 0) {
        continue;
    }

    $data = array_combine($columns, $row);
    if (!$data) {
        echo "Skipping malformed row {$rowNumber}.\n";
        $skipped++;
        continue;
    }

    if (trim((string)$data['plate_number']) === '') {
        echo "Skipping row {$rowNumber} because plate_number is empty.\n";
        $skipped++;
        continue;
    }

    foreach ($data as $key => $value) {
        $value = trim((string)$value);
        if ($value === '') {
            $data[$key] = null;
            continue;
        }

        if (in_array($key, ['seats', 'contract_id', 'city_id', 'region_id', 'department_id', 'project_id', 'office_id', 'location_id', 'schedule_id'], true)) {
            $data[$key] = (int)$value;
            continue;
        }

        if ($key === 'supplier_id') {
            $data[$key] = resolveReferenceId($pdo, 'suppliers', 'name', $value);
            continue;
        }

        if ($key === 'country_id') {
            $data[$key] = resolveReferenceId($pdo, 'countries', 'name', $value);
            continue;
        }

        if (in_array($key, ['need_obd', 'use_mr_car'], true)) {
            $data[$key] = in_array(strtolower($value), ['1', 'yes', 'true', 'y'], true) ? 1 : 0;
            continue;
        }

        if (in_array($key, ['registration_date', 'first_effective_date', 'entry_date', 'exit_date', 'schedule_expiration_date'], true)) {
            $data[$key] = normalizeDate($value);
            continue;
        }

        $data[$key] = $value;
    }

    try {
        $stmt->execute($data);
        $imported++;
    } catch (Throwable $e) {
        echo "Error importing row {$rowNumber}: " . $e->getMessage() . "\n";
        $skipped++;
    }
}

fclose($handle);

echo "Import complete. Imported: {$imported}, Skipped: {$skipped}.\n";
