<?php
declare(strict_types=1);

require_once '../config/database.php';
require_once '../app/helpers/rbac.php';
require_once '../app/helpers/audit.php';
require_once '../app/helpers/api_security.php';
require_once '../app/helpers/fuel_calc.php';
require_once '../app/helpers/soft_delete.php';

header('Content-Type: application/json');

requireApiLogin();
enforceCsrfForUnsafeRequest();

if (!isAdmin() && !can('vehicle.view')) {
    respond(['status' => 'error', 'message' => 'Unauthorized']);
}

$action = $_GET['action'] ?? '';

try {
    switch ($action) {
        case 'profile':
            handleProfile($pdo);
            break;
        case 'fuel_monthly':
            handleFuelMonthly($pdo);
            break;
        case 'insurance':
            handleInsurance($pdo);
            break;
        case 'licenses':
            handleLicenses($pdo);
            break;
        case 'save_vehicle':
            handleSaveVehicle($pdo);
            break;
        case 'save_dates':
            handleSaveDates($pdo);
            break;
        case 'save_fuel_config':
            handleSaveFuelConfig($pdo);
            break;
        case 'save_insurance':
            handleSaveInsurance($pdo);
            break;
        case 'save_license':
            handleSaveLicense($pdo);
            break;
        case 'save_maintenance':
            handleSaveMaintenance($pdo);
            break;
        case 'delete_insurance':
            handleDeleteInsurance($pdo);
            break;
        case 'delete_license':
            handleDeleteLicense($pdo);
            break;
        case 'date_references':
            handleDateReferences($pdo);
            break;
        case 'restore':
            $restoreTable = trim((string)($_GET['table'] ?? ''));
            $restoreId = (int)($_GET['id'] ?? 0);
            if ($restoreTable === '' || $restoreId <= 0) {
                respond(['status' => 'error', 'message' => 'Invalid parameters']);
            }
            $allowedTables = ['vehicle_insurance', 'vehicle_licenses', 'roadworthiness'];
            if (!in_array($restoreTable, $allowedTables, true)) {
                respond(['status' => 'error', 'message' => 'Invalid table']);
            }
            restoreRecord($pdo, $restoreTable, $restoreId);
            audit($pdo, (int)$_SESSION['user_id'], 'restore', $restoreTable, $restoreId, ucfirst(str_replace('_', ' ', $restoreTable)) . ' restored', null, null);
            respond(['status' => 'success']);
            break;
        default:
            respond(['status' => 'error', 'message' => 'Invalid action']);
    }
} catch (Throwable $e) {
    respond(['status' => 'error', 'message' => $e->getMessage()]);
}

function handleProfile(PDO $pdo): void
{
    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $stmt = $pdo->prepare("
        SELECT
            v.*,
            d.full_name AS driver_name,
            va.assignment_start,
            va.assignment_end,
            va.leased_days,
            va.status AS assignment_status,
            vfc.efficiency_baseline AS fuel_efficiency_baseline,
            vfc.standard_consumption,
            COALESCE(vfc.auto_calculate_baseline, 1) AS auto_calculate_baseline,
            monthly.distance_km AS latest_distance_km,
            s.name AS supplier_name,
            c.contract_code,
            c.account_name AS contract_account_name,
            co.name AS country_name,
            ci.name AS city_name,
            r.name AS region_name,
            dep.name AS department_name,
            p.name AS project_name,
            o.name AS office_name,
            l.name AS location_name,
            sch.schedule_name
        FROM vehicles v
        LEFT JOIN vehicle_assignments va
            ON va.vehicle_id = v.id
            AND va.id = (
                SELECT va2.id FROM vehicle_assignments va2
                WHERE va2.vehicle_id = v.id AND va2.status = 'active'
                ORDER BY va2.assignment_start DESC
                LIMIT 1
            )
        LEFT JOIN drivers d
            ON d.id = va.driver_id
        LEFT JOIN vehicle_fuel_config vfc
            ON vfc.vehicle_id = v.id
        LEFT JOIN (
            SELECT base.vehicle_id, base.distance_km
            FROM vehicle_fuel_monthly base
            INNER JOIN (
                SELECT vehicle_id, MAX(month_year) AS latest_month
                FROM vehicle_fuel_monthly
                GROUP BY vehicle_id
            ) latest
                ON latest.vehicle_id = base.vehicle_id
               AND latest.latest_month = base.month_year
        ) monthly
            ON monthly.vehicle_id = v.id
        LEFT JOIN suppliers s
            ON s.id = v.supplier_id
        LEFT JOIN contracts c
            ON c.id = v.contract_id
        LEFT JOIN countries co
            ON co.id = v.country_id
        LEFT JOIN cities ci
            ON ci.id = v.city_id
        LEFT JOIN regions r
            ON r.id = v.region_id
        LEFT JOIN departments dep
            ON dep.id = v.department_id
        LEFT JOIN projects p
            ON p.id = v.project_id
        LEFT JOIN offices o
            ON o.id = v.office_id
        LEFT JOIN locations l
            ON l.id = v.location_id
        LEFT JOIN schedules sch
            ON sch.id = v.schedule_id
        WHERE v.id = ? AND v.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([$id]);
    $vehicle = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$vehicle) {
        respond(['status' => 'error', 'message' => 'Vehicle not found']);
    }

    $licenseCount = countRows($pdo, 'SELECT COUNT(*) FROM vehicle_licenses WHERE vehicle_id = ?', [$id]);
    $insuranceCount = countRows($pdo, 'SELECT COUNT(*) FROM vehicle_insurance WHERE vehicle_id = ?', [$id]);
    $vehicle['compliance_count'] = $licenseCount + $insuranceCount;

    respond(['status' => 'success', 'data' => $vehicle]);
}

function handleFuelMonthly(PDO $pdo): void
{
    $id = (int)($_GET['id'] ?? 0);
    $stmt = $pdo->prepare("
        SELECT
            month_year,
            fuel_purchase_liters,
            naira_per_liter,
            monthly_mileage_km,
            begin_km,
            end_km,
            distance_km,
            total_fuel_consumption,
            baseline_difference,
            over_baseline_rate
        FROM vehicle_fuel_monthly
        WHERE vehicle_id = ?
        ORDER BY month_year DESC
        LIMIT 24
    ");
    $stmt->execute([$id]);

    respond(['status' => 'success', 'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
}

function handleInsurance(PDO $pdo): void
{
    $id = (int)($_GET['id'] ?? 0);
    $stmt = $pdo->prepare("
        SELECT p.id, p.vehicle_id, p.insurer_id, p.policy_number, p.policy_type, p.coverage_start, p.coverage_end, p.premium_amount, p.is_active, p.notes, p.created_at,
               COALESCE(i.name, p.insurer_name) AS insurer_name,
               DATEDIFF(p.coverage_end, CURDATE()) AS days_remaining,
               (SELECT COUNT(*) FROM insurance_claims WHERE policy_id = p.id) AS claim_count
        FROM vehicle_insurance_policies p
        LEFT JOIN insurers i ON i.id = p.insurer_id
        WHERE p.vehicle_id = ? AND p.deleted_at IS NULL
        ORDER BY p.coverage_end DESC, p.id DESC
    ");
    $stmt->execute([$id]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as &$row) {
        $row['record_status'] = dateStatus($row['coverage_end'] ?? null);
    }
    unset($row);

    respond(['status' => 'success', 'data' => $rows]);
}

function handleLicenses(PDO $pdo): void
{
    $id = (int)($_GET['id'] ?? 0);
    $stmt = $pdo->prepare("
        SELECT
            id,
            vehicle_license_no,
            issued_date,
            valid_until,
            notification_date
        FROM vehicle_licenses
        WHERE vehicle_id = ? AND deleted_at IS NULL
        ORDER BY valid_until DESC, id DESC
    ");
    $stmt->execute([$id]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as &$row) {
        $row['record_status'] = dateStatus($row['valid_until'] ?? null);
    }
    unset($row);

    respond(['status' => 'success', 'data' => $rows]);
}

function handleSaveVehicle(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['vehicleId'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $payload = [
        'plate_number' => trim((string)($_POST['plate'] ?? '')),
        'year_of_manufacturing' => $_POST['year'] !== '' ? (int)$_POST['year'] : null,
        'owner' => trim((string)($_POST['owner'] ?? '')),
        'property' => trim((string)($_POST['property'] ?? '')),
        'vehicle_type' => trim((string)($_POST['type'] ?? '')),
        'vehicle_model' => trim((string)($_POST['model'] ?? '')),
        'vehicle_use_type' => trim((string)($_POST['useType'] ?? '')),
        'vehicle_source' => trim((string)($_POST['source'] ?? '')),
        'displacement' => trim((string)($_POST['displacement'] ?? '')),
        'seats' => $_POST['seats'] !== '' ? (int)$_POST['seats'] : null,
        'need_obd' => (int)($_POST['needObd'] ?? 0),
        'obd_device_no' => trim((string)($_POST['obdDeviceNo'] ?? '')),
        'use_mr_car' => (int)($_POST['useMrCar'] ?? 0),
        'schedule_expiration_date' => trim((string)($_POST['scheduleExpirationDate'] ?? '')) ?: null,
        'last_updated_by' => $_SESSION['user_name'] ?? 'User',
        'last_update_date' => date('Y-m-d H:i:s')
    ];

    if ($payload['plate_number'] === '') {
        respond(['status' => 'error', 'message' => 'Plate number is required']);
    }

    $before = fetchVehicle($pdo, $id);
    if (!$before) {
        respond(['status' => 'error', 'message' => 'Vehicle not found']);
    }

    $setParts = [];
    foreach (array_keys($payload) as $column) {
        $setParts[] = "{$column} = :{$column}";
    }
    $payload['id'] = $id;

    $stmt = $pdo->prepare("UPDATE vehicles SET " . implode(', ', $setParts) . " WHERE id = :id");
    $stmt->execute($payload);

    audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle', $id, 'Vehicle details updated', $before, $payload);
    respond(['status' => 'success']);
}

function handleSaveDates(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['vehicleId'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $payload = [
        'registration_date' => trim((string)($_POST['registrationDate'] ?? '')) ?: null,
        'first_effective_date' => trim((string)($_POST['firstEffectiveDate'] ?? '')) ?: null,
        'entry_date' => trim((string)($_POST['entryDate'] ?? '')) ?: null,
        'exit_date' => trim((string)($_POST['exitDate'] ?? '')) ?: null,
        'supplier_id' => $_POST['supplierId'] !== '' ? (int)$_POST['supplierId'] : null,
        'contract_id' => $_POST['contractId'] !== '' ? (int)$_POST['contractId'] : null,
        'country_id' => $_POST['countryId'] !== '' ? (int)$_POST['countryId'] : null,
        'city_id' => $_POST['cityId'] !== '' ? (int)$_POST['cityId'] : null,
        'region_id' => $_POST['regionId'] !== '' ? (int)$_POST['regionId'] : null,
        'department_id' => $_POST['departmentId'] !== '' ? (int)$_POST['departmentId'] : null,
        'project_id' => $_POST['projectId'] !== '' ? (int)$_POST['projectId'] : null,
        'office_id' => $_POST['officeId'] !== '' ? (int)$_POST['officeId'] : null,
        'location_id' => $_POST['locationId'] !== '' ? (int)$_POST['locationId'] : null,
        'schedule_id' => $_POST['scheduleId'] !== '' ? (int)$_POST['scheduleId'] : null,
        'last_updated_by' => $_SESSION['user_name'] ?? 'User',
        'last_update_date' => date('Y-m-d H:i:s')
    ];

    $before = fetchVehicle($pdo, $id);
    if (!$before) {
        respond(['status' => 'error', 'message' => 'Vehicle not found']);
    }

    $setParts = [];
    foreach (array_keys($payload) as $column) {
        $setParts[] = "{$column} = :{$column}";
    }
    $payload['id'] = $id;

    $stmt = $pdo->prepare("UPDATE vehicles SET " . implode(', ', $setParts) . " WHERE id = :id");
    $stmt->execute($payload);

    audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle', $id, 'Vehicle dates and references updated', $before, $payload);
    respond(['status' => 'success']);
}

function handleSaveFuelConfig(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $vehicleId = (int)($_POST['vehicleId'] ?? 0);
    if ($vehicleId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $autoCalculate = !empty($_POST['auto_calculate_baseline']);
    $efficiency = null;
    $standard = null;

    if (!$autoCalculate) {
        $efficiency = $_POST['efficiency'] !== '' ? (float)$_POST['efficiency'] : null;
        $standard = $_POST['standardConsumption'] !== '' ? (float)$_POST['standardConsumption'] : null;
    }

    // Upsert: check for existing record first
    $stmt = $pdo->prepare("SELECT id FROM vehicle_fuel_config WHERE vehicle_id = ? AND deleted_at IS NULL");
    $stmt->execute([$vehicleId]);
    $existing = $stmt->fetchColumn();

    if ($existing) {
        $stmt = $pdo->prepare("UPDATE vehicle_fuel_config SET efficiency_baseline = ?, standard_consumption = ?, auto_calculate_baseline = ? WHERE vehicle_id = ?");
        $stmt->execute([$efficiency, $standard, $autoCalculate ? 1 : 0, $vehicleId]);
    } else {
        $stmt = $pdo->prepare("INSERT INTO vehicle_fuel_config (vehicle_id, efficiency_baseline, standard_consumption, auto_calculate_baseline, created_at) VALUES (?, ?, ?, ?, NOW())");
        $stmt->execute([$vehicleId, $efficiency, $standard, $autoCalculate ? 1 : 0]);
    }

    // If auto-calculate is enabled, compute baselines immediately
    if ($autoCalculate) {
        autoCalculateVehicleBaselines($pdo, $vehicleId);
    }

    audit($pdo, (int)$_SESSION['user_id'], 'upsert', 'vehicle_fuel_config', $vehicleId, 'Fuel configuration saved', null, [
        'vehicle_id' => $vehicleId,
        'efficiency_baseline' => $efficiency,
        'standard_consumption' => $standard,
        'auto_calculate_baseline' => $autoCalculate
    ]);
    respond(['status' => 'success']);
}

function handleSaveInsurance(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['id'] ?? 0);
    $vehicleId = (int)($_POST['vehicle_id'] ?? 0);
    if ($vehicleId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $insurerId = !empty($_POST['insurer_id']) ? (int)$_POST['insurer_id'] : null;
    $policyNumber = trim((string)($_POST['policy_number'] ?? ''));
    $policyType = $_POST['policy_type'] ?? 'comprehensive';
    $coverageStart = trim((string)($_POST['coverage_start'] ?? '')) ?: null;
    $coverageEnd = trim((string)($_POST['coverage_end'] ?? '')) ?: null;
    $premiumAmount = !empty($_POST['premium_amount']) ? (float)$_POST['premium_amount'] : null;
    $isActive = (int)($_POST['is_active'] ?? 1);
    $notes = trim((string)($_POST['notes'] ?? ''));

    // Auto-populate insurer_name from insurers table
    $insurerName = '';
    if ($insurerId) {
        $stmt = $pdo->prepare("SELECT name FROM insurers WHERE id = ?");
        $stmt->execute([$insurerId]);
        $insurerName = (string)$stmt->fetchColumn();
    }

    if (!$policyNumber || !$insurerName || !$coverageStart || !$coverageEnd) {
        respond(['status' => 'error', 'message' => 'Policy number, insurer, coverage start, and coverage end are required']);
    }

    if ($id > 0) {
        $stmt = $pdo->prepare("
            UPDATE vehicle_insurance_policies
            SET vehicle_id = ?, insurer_id = ?, policy_number = ?, insurer_name = ?, policy_type = ?,
                coverage_start = ?, coverage_end = ?, premium_amount = ?, is_active = ?, notes = ?, updated_at = NOW()
            WHERE id = ?
        ");
        $stmt->execute([$vehicleId, $insurerId, $policyNumber, $insurerName, $policyType, $coverageStart, $coverageEnd, $premiumAmount, $isActive, $notes, $id]);
        audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle_insurance_policies', $id, 'Insurance policy updated', null, $_POST);
    } else {
        $stmt = $pdo->prepare("
            INSERT INTO vehicle_insurance_policies (vehicle_id, insurer_id, policy_number, insurer_name, policy_type, coverage_start, coverage_end, premium_amount, is_active, notes, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([$vehicleId, $insurerId, $policyNumber, $insurerName, $policyType, $coverageStart, $coverageEnd, $premiumAmount, $isActive, $notes, (int)$_SESSION['user_id']]);
        $id = (int)$pdo->lastInsertId();
        audit($pdo, (int)$_SESSION['user_id'], 'create', 'vehicle_insurance_policies', $id, 'Insurance policy added', null, $_POST);
    }

    respond(['status' => 'success', 'data' => ['id' => $id]]);
}

function handleDeleteInsurance(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid insurance ID']);
    }

    softDelete($pdo, 'vehicle_insurance_policies', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'vehicle_insurance_policies', $id, 'Insurance policy deleted', null, null);
    respond(['status' => 'success']);
}

function handleSaveLicense(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['licenseId'] ?? 0);
    $vehicleId = (int)($_POST['vehicleId'] ?? 0);
    if ($vehicleId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $licenseNo = trim((string)($_POST['licenseNumber'] ?? ''));
    $issuedDate = trim((string)($_POST['issuedDate'] ?? '')) ?: null;
    $validUntil = trim((string)($_POST['validUntil'] ?? '')) ?: null;
    $notificationDays = $_POST['notificationDays'] !== '' ? (int)$_POST['notificationDays'] : 30;

    if ($licenseNo === '') {
        respond(['status' => 'error', 'message' => 'License number is required']);
    }

    $notificationDate = null;
    if ($validUntil) {
        $dt = DateTime::createFromFormat('Y-m-d', $validUntil);
        if ($dt) {
            $dt->modify("-{$notificationDays} days");
            $notificationDate = $dt->format('Y-m-d');
        }
    }

    if ($id > 0) {
        $stmt = $pdo->prepare("
            UPDATE vehicle_licenses
            SET vehicle_license_no = ?, issued_date = ?, valid_until = ?, notification_date = ?
            WHERE id = ? AND vehicle_id = ?
        ");
        $stmt->execute([$licenseNo, $issuedDate, $validUntil, $notificationDate, $id, $vehicleId]);
        audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle_licenses', $id, 'License record updated', null, $_POST);
    } else {
        $stmt = $pdo->prepare("
            INSERT INTO vehicle_licenses (vehicle_id, vehicle_license_no, issued_date, valid_until, notification_date)
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->execute([$vehicleId, $licenseNo, $issuedDate, $validUntil, $notificationDate]);
        $id = (int)$pdo->lastInsertId();
        audit($pdo, (int)$_SESSION['user_id'], 'create', 'vehicle_licenses', $id, 'License record added', null, $_POST);
    }

    respond(['status' => 'success', 'data' => ['id' => $id]]);
}

function handleDeleteLicense(PDO $pdo): void
{
    if (!isAdmin() && !can('vehicle.edit')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['licenseId'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid license ID']);
    }

    softDelete($pdo, 'vehicle_licenses', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'vehicle_licenses', $id, 'License record deleted', null, null);
    respond(['status' => 'success']);
}

function handleSaveMaintenance(PDO $pdo): void
{
    if (!isAdmin() && !can('maintenance.create')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $vehicleId = (int)($_POST['vehicleId'] ?? 0);
    if ($vehicleId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid vehicle']);
    }

    $date = trim((string)($_POST['date'] ?? '')) ?: date('Y-m-d');
    $type = trim((string)($_POST['type'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $technician = trim((string)($_POST['technician'] ?? ''));
    $cost = $_POST['cost'] !== '' ? (float)$_POST['cost'] : null;
    $mileage = $_POST['mileage'] !== '' ? (int)$_POST['mileage'] : null;

    $statusMap = [
        'completed' => ['workflow' => 'Closed', 'status' => 'Completed'],
        'scheduled' => ['workflow' => 'Complaint Logged', 'status' => 'Pending'],
        'in_progress' => ['workflow' => 'Workshop In Progress', 'status' => 'In Progress'],
        'cancelled' => ['workflow' => 'Cancelled', 'status' => 'Cancelled'],
    ];
    $formStatus = trim((string)($_POST['status'] ?? 'scheduled'));
    $mapped = $statusMap[$formStatus] ?? ['workflow' => 'Complaint Logged', 'status' => 'Pending'];

    $payload = [
        'vehicle_id' => $vehicleId,
        'complaint_date' => $date,
        'complaint_log_date' => $date,
        'complaint_title' => $type ?: 'Maintenance',
        'complaint_description' => $description ?: null,
        'complaint_source' => 'Fleet Office',
        'workshop' => $technician ?: null,
        'cost_of_repair' => $cost,
        'current_service_mileage_km' => $mileage,
        'workflow_stage' => $mapped['workflow'],
        'status' => $mapped['status']
    ];

    $columns = implode(', ', array_keys($payload));
    $values = implode(', ', array_map(static fn(string $col): string => ':' . $col, array_keys($payload)));

    $stmt = $pdo->prepare("INSERT INTO maintenance_jobs ({$columns}) VALUES ({$values})");
    $stmt->execute($payload);
    $newId = (int)$pdo->lastInsertId();

    audit($pdo, (int)$_SESSION['user_id'], 'create', 'maintenance_job', $newId, 'Maintenance record created from vehicle profile', null, $payload);
    respond(['status' => 'success', 'data' => ['id' => $newId]]);
}

function countRows(PDO $pdo, string $sql, array $params): int
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return (int)$stmt->fetchColumn();
}

function dateStatus(?string $date): string
{
    if (!$date) {
        return 'Unknown';
    }

    $today = strtotime(date('Y-m-d'));
    $expiry = strtotime($date);

    if ($expiry === false) {
        return 'Unknown';
    }
    if ($expiry < $today) {
        return 'Expired';
    }
    if ($expiry <= strtotime('+30 days', $today)) {
        return 'Expiring Soon';
    }

    return 'Valid';
}

function handleDateReferences(PDO $pdo): void
{
    $existing = [];
    $stmt = $pdo->query("SHOW TABLES");
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $existing[strtolower($row[0])] = true;
    }

    $refs = [];

    if (isset($existing['suppliers'])) {
        $refs['suppliers'] = $pdo->query("SELECT id, name FROM suppliers WHERE deleted_at IS NULL ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $refs['suppliers'] = [];
    }

    if (isset($existing['contracts'])) {
        $refs['contracts'] = $pdo->query("SELECT id, CONCAT(COALESCE(contract_code, ''), ' - ', COALESCE(account_name, '')) AS name FROM contracts ORDER BY contract_code")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE contracts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, contract_code VARCHAR(60) DEFAULT NULL, account_name VARCHAR(150) DEFAULT NULL)");
        $refs['contracts'] = [];
    }

    if (isset($existing['countries'])) {
        $refs['countries'] = $pdo->query("SELECT id, name FROM countries ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE countries (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL)");
        $refs['countries'] = [];
    }

    if (isset($existing['cities'])) {
        $refs['cities'] = $pdo->query("SELECT id, name FROM cities ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE cities (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, region_id BIGINT UNSIGNED DEFAULT NULL)");
        $refs['cities'] = [];
    }

    if (isset($existing['regions'])) {
        $refs['regions'] = $pdo->query("SELECT id, name FROM regions ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE regions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL)");
        $refs['regions'] = [];
    }

    if (isset($existing['departments'])) {
        $refs['departments'] = $pdo->query("SELECT id, name FROM departments ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE departments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL)");
        $refs['departments'] = [];
    }

    if (isset($existing['projects'])) {
        $refs['projects'] = $pdo->query("SELECT id, name FROM projects ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE projects (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL)");
        $refs['projects'] = [];
    }

    if (isset($existing['offices'])) {
        $refs['offices'] = $pdo->query("SELECT id, name FROM offices ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE offices (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL)");
        $refs['offices'] = [];
    }

    if (isset($existing['locations'])) {
        $refs['locations'] = $pdo->query("SELECT id, name FROM locations ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE locations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL)");
        $refs['locations'] = [];
    }

    if (isset($existing['schedules'])) {
        $refs['schedules'] = $pdo->query("SELECT id, schedule_name AS name FROM schedules ORDER BY schedule_name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE schedules (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, schedule_name VARCHAR(120) NOT NULL)");
        $refs['schedules'] = [];
    }

    if (isset($existing['vehicle_types'])) {
        $refs['vehicle_types'] = $pdo->query("SELECT id, name FROM vehicle_types WHERE is_active = 1 AND deleted_at IS NULL ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE vehicle_types (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, is_active TINYINT DEFAULT 1)");
        $refs['vehicle_types'] = [];
    }

    if (isset($existing['vehicle_models'])) {
        $refs['vehicle_models'] = $pdo->query("SELECT id, name FROM vehicle_models WHERE is_active = 1 AND deleted_at IS NULL ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE vehicle_models (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, is_active TINYINT DEFAULT 1)");
        $refs['vehicle_models'] = [];
    }

    if (isset($existing['owners'])) {
        $refs['owners'] = $pdo->query("SELECT id, name FROM owners WHERE is_active = 1 AND deleted_at IS NULL ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $pdo->exec("CREATE TABLE owners (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, is_active TINYINT DEFAULT 1)");
        $refs['owners'] = [];
    }

    respond(['status' => 'success', 'data' => $refs]);
}

function fetchVehicle(PDO $pdo, int $id): ?array
{
    $stmt = $pdo->prepare("SELECT * FROM vehicles WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

function respond(array $payload): void { apiRespond($payload); }
