<?php
declare(strict_types=1);

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

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

requireApiLogin();
enforceCsrfForUnsafeRequest();

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

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

try {
    switch ($action) {
        case 'reference_data':
            handleReferenceData($pdo);
            break;
        case 'fuel_entries':
            handleFuelEntries($pdo);
            break;
        case 'fuel_entry':
            handleFuelEntry($pdo);
            break;
        case 'fuel_types':
            handleFuelTypes($pdo);
            break;
        case 'fuel_vehicle_types':
            handleFuelVehicleTypes($pdo);
            break;
        case 'fuel_prices':
            handleFuelPrices($pdo);
            break;
        case 'fuel_companies':
            handleFuelCompanies($pdo);
            break;
        case 'fuel_filling_stations':
            handleFuelFillingStations($pdo);
            break;
        case 'fuel_offices':
            handleFuelOffices($pdo);
            break;
        case 'fuel_approval_users':
            handleFuelApprovalUsers($pdo);
            break;
        case 'price_lookup':
            handlePriceLookup($pdo);
            break;
        case 'save_fuel_entry':
            handleSaveFuelEntry($pdo);
            break;
        case 'save_fuel_type':
            handleSaveFuelType($pdo);
            break;
        case 'save_fuel_vehicle_type':
            handleSaveFuelVehicleType($pdo);
            break;
        case 'save_fuel_price':
            handleSaveFuelPrice($pdo);
            break;
        case 'save_fuel_company':
            handleSaveFuelCompany($pdo);
            break;
        case 'save_fuel_filling_station':
            handleSaveFuelFillingStation($pdo);
            break;
        case 'save_fuel_office':
            handleSaveFuelOffice($pdo);
            break;
        case 'save_fuel_approval_user':
            handleSaveFuelApprovalUser($pdo);
            break;
        case 'approve_fuel_request':
            handleApproveFuelRequest($pdo);
            break;
        case 'fuel_entry_counts':
            handleFuelEntryCounts($pdo);
            break;
        case 'fuel_price_count':
            handleFuelPriceCount($pdo);
            break;
        case 'fuel_export_csv':
            handleFuelExportCsv($pdo);
            break;
        case 'delete_fuel_type':
            handleDeleteFuelType($pdo);
            break;
        case 'delete_fuel_office':
            handleDeleteFuelOffice($pdo);
            break;
        case 'delete_fuel_company':
            handleDeleteFuelCompany($pdo);
            break;
        case 'delete_fuel_filling_station':
            handleDeleteFuelFillingStation($pdo);
            break;
        case 'delete_fuel_approval_user':
            handleDeleteFuelApprovalUser($pdo);
            break;
        case 'delete_fuel_price':
            handleDeleteFuelPrice($pdo);
            break;
        case 'delete_fuel_entry':
            handleDeleteFuelEntry($pdo);
            break;
        case 'last_vehicle_fuel_entry':
            handleLastVehicleFuelEntry($pdo);
            break;
        case 'restore':
            $table = trim((string)($_POST['table'] ?? ''));
            $id = (int)($_POST['id'] ?? 0);
            $allowed = ['fuel_types', 'fuel_companies', 'fuel_filling_stations', 'fuel_price_history', 'fuel_approval_users', 'vehicle_fuel_entries', 'offices'];
            if (!in_array($table, $allowed, true) || $id <= 0) {
                respond(['status' => 'error', 'message' => 'Invalid parameters']);
            }
            restoreRecord($pdo, $table, $id);
            audit($pdo, (int)$_SESSION['user_id'], 'restore', $table, $id, ucfirst(str_replace('_', ' ', $table)) . ' restored');
            respond(['status' => 'success']);
            break;
        case 'fuel_request_report':
            handleFuelRequestReport($pdo);
            break;
        default:
            respond(['status' => 'error', 'message' => 'Invalid action']);
    }
} catch (Throwable $e) {
    error_log('fuel.php: ' . $e->getMessage());
    respond(['status' => 'error', 'message' => 'An unexpected error occurred']);
}

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

    $stmt = $pdo->prepare("
        SELECT end_km, actual_mileage, record_date
        FROM vehicle_fuel_entries
        WHERE vehicle_id = :vehicle_id
          AND deleted_at IS NULL
          AND end_km IS NOT NULL
        ORDER BY id DESC
        LIMIT 1
    ");
    $stmt->execute(['vehicle_id' => $vehicleId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($row) {
        respond(['status' => 'success', 'data' => [
            'end_km' => (int)($row['end_km'] ?? 0),
            'record_date' => $row['record_date']
        ]]);
    } else {
        respond(['status' => 'success', 'data' => null]);
    }
}

function handleReferenceData(PDO $pdo): void
{
    $vehicles = $pdo->query("
        SELECT
            v.id,
            CONCAT(v.plate_number, ' - ', COALESCE(v.vehicle_model, v.vehicle_type, 'Vehicle')) AS label,
            v.plate_number,
            COALESCE(NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, ''), 'Vehicle') AS vehicle_name,
            v.vehicle_type,
            v.vehicle_model,
            v.standard_fuel_consumption_l_per_100km,
            v.office_id,
            o.name AS office_name,
            COALESCE(NULLIF(v.owner, ''), NULLIF(v.property, ''), s.name, 'Company') AS company_name,
            fc.id AS company_id,
            va.driver_id,
            COALESCE(d.full_name, CONCAT_WS(' ', d.first_name, d.surname), 'Unassigned') AS driver_name
        FROM vehicles v
        LEFT JOIN offices o ON o.id = v.office_id
        LEFT JOIN suppliers s ON s.id = v.supplier_id
        LEFT JOIN fuel_companies fc
            ON fc.name = COALESCE(NULLIF(v.owner, ''), NULLIF(v.property, ''), s.name)
        LEFT JOIN (
            SELECT va1.*
            FROM vehicle_assignments va1
            INNER JOIN (
                SELECT vehicle_id, MAX(id) AS max_id
                FROM vehicle_assignments
                WHERE assignment_end IS NULL OR status = 'Active'
                GROUP BY vehicle_id
            ) va2 ON va2.max_id = va1.id
        ) va ON va.vehicle_id = v.id
        LEFT JOIN drivers d ON d.id = va.driver_id
        WHERE v.deleted_at IS NULL
        ORDER BY v.plate_number ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $fuelTypes = $pdo->query("
        SELECT id, name AS label, code, unit_label
        FROM fuel_types
        WHERE is_active = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $fuelVehicleTypes = $pdo->query("
        SELECT id, name AS label, code
        FROM fuel_vehicle_types
        WHERE is_active = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $drivers = $pdo->query("
        SELECT
            id,
            COALESCE(full_name, CONCAT_WS(' ', first_name, surname), driver_id_code) AS label,
            phone
        FROM drivers
        WHERE active_status = 1
          AND deleted_at IS NULL
        ORDER BY label ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $states = $pdo->query("
        SELECT id, name AS label
        FROM nigeria_states
        WHERE is_active = 1
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $offices = $pdo->query("
        SELECT id, name AS label
        FROM offices
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $users = $pdo->query("
        SELECT id, name AS label
        FROM users
        WHERE COALESCE(is_active, 1) = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $supervisors = $pdo->query("
        SELECT u.id, u.name AS label
        FROM fuel_approval_users fau
        INNER JOIN users u ON u.id = fau.user_id
        WHERE fau.is_active = 1
          AND fau.deleted_at IS NULL
          AND COALESCE(u.is_active, 1) = 1
          AND u.deleted_at IS NULL
        ORDER BY u.name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $companies = $pdo->query("
        SELECT id, name AS label
        FROM fuel_companies
        WHERE is_active = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $fillingStations = $pdo->query("
        SELECT id, name AS label, state_id
        FROM fuel_filling_stations
        WHERE is_active = 1
          AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $fuelCards = $pdo->query("
        SELECT fc.id,
               CONCAT(fc.card_number_masked, ' (', COALESCE(p.name, 'No Provider'), ') - ₦', fc.current_balance) AS label,
               fc.current_balance,
               fc.status
        FROM fuel_cards fc
        LEFT JOIN fuel_card_providers p ON p.id = fc.provider_id
        WHERE fc.status = 'active'
          AND fc.deleted_at IS NULL
        ORDER BY fc.created_at DESC
    ")->fetchAll(PDO::FETCH_ASSOC);

    respond([
        'status' => 'success',
        'data' => [
            'vehicles' => $vehicles,
            'drivers' => $drivers,
            'fuel_types' => $fuelTypes,
            'fuel_vehicle_types' => $fuelVehicleTypes,
            'states' => $states,
            'offices' => $offices,
            'users' => $users,
            'companies' => $companies,
            'supervisors' => $supervisors,
            'filling_stations' => $fillingStations,
            'fuel_cards' => $fuelCards,
            'can_approve' => canApproveFuel($pdo, (int)($_SESSION['user_id'] ?? 0))
        ]
    ]);
}

function handleFuelEntries(PDO $pdo): void
{
    $vehicleId = (int)($_GET['vehicle_id'] ?? 0);
    $monthYear = trim((string)($_GET['month_year'] ?? ''));
    $approvalStatus = trim((string)($_GET['approval_status'] ?? ''));
    $search = trim((string)($_GET['search'] ?? ''));
    $vehicleSearch = trim((string)($_GET['vehicle_search'] ?? ''));
    $lastDate = trim((string)($_GET['last_date'] ?? ''));
    $lastId = (int)($_GET['last_id'] ?? 0);
    $startDate = trim((string)($_GET['start_date'] ?? ''));
    $endDate = trim((string)($_GET['end_date'] ?? ''));

    $where = [];
    $params = [];

    $where[] = 'vfe.deleted_at IS NULL';

    if ($vehicleId > 0) {
        $where[] = 'vfe.vehicle_id = :vehicle_id';
        $params['vehicle_id'] = $vehicleId;
    }
    if ($monthYear !== '') {
        $where[] = 'vfe.month_year = :month_year';
        $params['month_year'] = $monthYear;
    }
    if ($approvalStatus !== '') {
        $where[] = 'vfe.approval_status = :approval_status';
        $params['approval_status'] = $approvalStatus;
    }
    if ($search !== '') {
        $where[] = '(vfe.voucher_id LIKE :search OR v.plate_number LIKE :search OR vfe.vehicle_no LIKE :search OR vfe.driver_name LIKE :search OR vfe.company_name LIKE :search OR vfe.filling_station LIKE :search)';
        $params['search'] = '%' . $search . '%';
    }
    if ($lastDate !== '' && $lastId > 0) {
        $where[] = '(vfe.record_date < :last_date OR (vfe.record_date = :last_date AND vfe.id < :last_id))';
        $params['last_date'] = $lastDate;
        $params['last_id'] = $lastId;
    }
    if ($startDate !== '') {
        $where[] = 'vfe.record_date >= :start_date';
        $params['start_date'] = $startDate;
    }
    if ($endDate !== '') {
        $where[] = 'vfe.record_date <= :end_date';
        $params['end_date'] = $endDate;
    }

    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    $limit = 101;
    $stmt = $pdo->prepare("
        SELECT
            vfe.*,
            v.plate_number,
            COALESCE(NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, ''), 'Vehicle') AS vehicle_name,
            COALESCE(ft.name, 'Unspecified') AS fuel_type_name,
            s.name AS state_name,
            fs.name AS filling_station_name,
            creator.name AS created_by_name,
            approver.name AS approved_by_name
        FROM vehicle_fuel_entries vfe
        INNER JOIN vehicles v ON v.id = vfe.vehicle_id
        LEFT JOIN fuel_types ft ON ft.id = vfe.fuel_type_id
        LEFT JOIN nigeria_states s ON s.id = vfe.state_id
        LEFT JOIN fuel_filling_stations fs ON fs.id = vfe.filling_station_id
        LEFT JOIN users creator ON creator.id = vfe.created_by_user_id
        LEFT JOIN users approver ON approver.id = vfe.approved_by_user_id
        {$whereSql}
        ORDER BY vfe.record_date DESC, vfe.id DESC
        LIMIT {$limit}
    ");
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $hasMore = count($rows) === $limit;
    if ($hasMore) {
        array_pop($rows);
    }

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

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

    $stmt = $pdo->prepare("
        SELECT
            vfe.*,
            v.plate_number,
            COALESCE(NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, ''), 'Vehicle') AS vehicle_name,
            COALESCE(ft.name, 'Unspecified') AS fuel_type_name,
            s.name AS state_name,
            fs.name AS filling_station_name,
            creator.name AS created_by_name,
            approver.name AS approved_by_name
        FROM vehicle_fuel_entries vfe
        INNER JOIN vehicles v ON v.id = vfe.vehicle_id
        LEFT JOIN fuel_types ft ON ft.id = vfe.fuel_type_id
        LEFT JOIN nigeria_states s ON s.id = vfe.state_id
        LEFT JOIN fuel_filling_stations fs ON fs.id = vfe.filling_station_id
        LEFT JOIN users creator ON creator.id = vfe.created_by_user_id
        LEFT JOIN users approver ON approver.id = vfe.approved_by_user_id
        WHERE vfe.id = ?
          AND vfe.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([$id]);
    $entry = $stmt->fetch(PDO::FETCH_ASSOC);

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

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

function handleFuelTypes(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT id, name, code, unit_label, is_active, created_at
        FROM fuel_types
        WHERE deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelVehicleTypes(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT id, name, code, is_active, created_at
        FROM fuel_vehicle_types
        WHERE deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelPrices(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT
            fph.id,
            fph.fuel_type_id,
            fph.state_id,
            fph.price_per_liter,
            fph.effective_date,
            fph.vendor_name,
            fph.remarks,
            fph.is_active,
            ft.name AS fuel_type_name,
            s.name AS state_name
        FROM fuel_price_history fph
        INNER JOIN fuel_types ft ON ft.id = fph.fuel_type_id
        LEFT JOIN nigeria_states s ON s.id = fph.state_id
        WHERE fph.is_active = 1
          AND fph.deleted_at IS NULL
        ORDER BY fph.effective_date DESC, ft.name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelPriceCount(PDO $pdo): void
{
    $count = $pdo->query("SELECT COUNT(*) FROM fuel_price_history WHERE is_active = 1 AND deleted_at IS NULL")->fetchColumn();
    respond(['status' => 'success', 'data' => ['count' => (int)$count]]);
}

function handleFuelCompanies(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT id, name, code, is_active, created_at
        FROM fuel_companies
        WHERE deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelFillingStations(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT
            ffs.id,
            ffs.name,
            ffs.state_id,
            ns.name AS state_name,
            ffs.address,
            ffs.contact_phone,
            ffs.is_active,
            ffs.created_at
        FROM fuel_filling_stations ffs
        LEFT JOIN nigeria_states ns ON ns.id = ffs.state_id
        WHERE ffs.deleted_at IS NULL
        ORDER BY ffs.name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelOffices(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT id, name
        FROM offices
        WHERE deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handleFuelApprovalUsers(PDO $pdo): void
{
    $rows = $pdo->query("
        SELECT
            fau.id,
            fau.user_id,
            u.name AS user_name,
            u.email,
            fau.is_active,
            fau.created_at
        FROM fuel_approval_users fau
        INNER JOIN users u ON u.id = fau.user_id
        WHERE fau.deleted_at IS NULL
          AND u.deleted_at IS NULL
        ORDER BY u.name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

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

function handlePriceLookup(PDO $pdo): void
{
    $fuelTypeId = (int)($_GET['fuel_type_id'] ?? 0);
    $stateId = (int)($_GET['state_id'] ?? 0);
    $effectiveDate = normalizeNullableDate($_GET['effective_date'] ?? date('Y-m-d')) ?? date('Y-m-d');

    if ($fuelTypeId <= 0) {
        respond(['status' => 'error', 'message' => 'Fuel type is required']);
    }

    $stmt = $pdo->prepare("
        SELECT
            fph.id,
            fph.price_per_liter,
            fph.effective_date,
            ft.name AS fuel_type_name,
            s.name AS state_name
        FROM fuel_price_history fph
        INNER JOIN fuel_types ft ON ft.id = fph.fuel_type_id
        LEFT JOIN nigeria_states s ON s.id = fph.state_id
        WHERE fph.fuel_type_id = :fuel_type_id
          AND fph.is_active = 1
          AND fph.deleted_at IS NULL
          AND fph.effective_date <= :effective_date
          AND (fph.state_id = :state_id OR (:state_id = 0 AND fph.state_id IS NULL))
        ORDER BY
            CASE WHEN fph.state_id = :state_id THEN 1 ELSE 2 END,
            fph.effective_date DESC,
            fph.id DESC
        LIMIT 1
    ");
    $stmt->execute([
        'fuel_type_id' => $fuelTypeId,
        'state_id' => $stateId,
        'effective_date' => $effectiveDate
    ]);

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

function handleSaveFuelEntry(PDO $pdo): void
{
    enforceFuelWritePermission();

    $id = (int)($_POST['id'] ?? 0);
    $wasUpdate = $id > 0;
    $vehicleId = (int)($_POST['vehicle_id'] ?? 0);
    $recordDate = normalizeNullableDate($_POST['record_date'] ?? null);

    if ($vehicleId <= 0 || $recordDate === null) {
        respond(['status' => 'error', 'message' => 'Vehicle and record date are required']);
    }

    $pdo->beginTransaction();

    $monthYear = substr($recordDate, 0, 7);
    $driverId = normalizeNullableInt($_POST['driver_id'] ?? null);
    $fuelTypeId = normalizeNullableInt($_POST['fuel_type_id'] ?? null);
    $stateId = normalizeNullableInt($_POST['state_id'] ?? null);
    $priceHistoryId = normalizeNullableInt($_POST['price_history_id'] ?? null);
    $officeId = normalizeNullableInt($_POST['office_id'] ?? null);
    $companyId = normalizeNullableInt($_POST['company_id'] ?? null);
    $fillingStationId = normalizeNullableInt($_POST['filling_station_id'] ?? null);
    $approvedByUserId = normalizeNullableSignedInt($_POST['approved_by_user_id'] ?? null);
    $fleetSupervisorUserId = normalizeNullableSignedInt($_POST['fleet_supervisor_user_id'] ?? null);

    $fuelPurchaseLiters = normalizeNullableDecimal($_POST['quantity_litres'] ?? ($_POST['fuel_purchase_liters'] ?? null));
    $nairaPerLiter = normalizeNullableDecimal($_POST['naira_per_liter'] ?? null);
    $actualMileage = normalizeNullableInt($_POST['actual_mileage'] ?? null);
    $mileageKm = normalizeNullableInt($_POST['mileage_km'] ?? ($_POST['monthly_mileage_km'] ?? null));
    $beginKm = normalizeNullableInt($_POST['begin_km'] ?? null);
    $endKm = normalizeNullableInt($_POST['end_km'] ?? null);
    $monthlyMileageKm = $mileageKm;
    $driverName = normalizeNullableString($_POST['driver_name'] ?? null);
    $notes = normalizeNullableString($_POST['notes'] ?? null);
    $commentText = normalizeNullableString($_POST['comment_text'] ?? null);
    $fuelingTime = normalizeNullableTime($_POST['fueling_time'] ?? null);
    $fuelingType = normalizeNullableString($_POST['fueling_type'] ?? null);
    $fuelingStatus = normalizeNullableString($_POST['fueling_status'] ?? null);
    $approvalStatus = normalizeNullableString($_POST['approval_status'] ?? null) ?? 'Pending';
    $fuelLevel = normalizeNullableString($_POST['fuel_level'] ?? null);
    $dispensedLitres = normalizeNullableDecimal($_POST['dispensed_litres'] ?? null) ?? $fuelPurchaseLiters;
    $fillingStation = normalizeNullableString($_POST['filling_station'] ?? null);
    $mileageDate = normalizeNullableDate($_POST['mileage_date'] ?? null) ?? $recordDate;
    $mileageTime = normalizeNullableTime($_POST['mileage_time'] ?? null);
    $cardBalance = normalizeNullableDecimal($_POST['card_balance'] ?? null);
    $fuelCardId = normalizeNullableInt($_POST['fuel_card_id'] ?? null);

    if ($actualMileage !== null && $mileageKm !== null) {
        $endKm = $actualMileage;
        $beginKm = max(0, $actualMileage - $mileageKm);
    } elseif ($monthlyMileageKm === null && $beginKm !== null && $endKm !== null) {
        $monthlyMileageKm = max(0, $endKm - $beginKm);
    }

    $distanceKm = $monthlyMileageKm;
    $totalFuelConsumption = $dispensedLitres ?? $fuelPurchaseLiters;
    $totalAmount = calculateTotalAmount($fuelPurchaseLiters, $nairaPerLiter);

    $baseline = getVehicleFuelBaseline($pdo, $vehicleId);
    $baselineDifference = null;
    $overBaselineRate = null;

    if ($baseline !== null && $distanceKm !== null && $totalFuelConsumption !== null) {
        $expectedLiters = ($distanceKm * $baseline) / 100;
        $baselineDifference = round($totalFuelConsumption - $expectedLiters, 2);
        $overBaselineRate = $expectedLiters > 0 ? round(($baselineDifference / $expectedLiters) * 100, 2) : null;
    }

    $vehicle = fetchVehicleSnapshot($pdo, $vehicleId);
    $voucherId = normalizeNullableString($_POST['voucher_id'] ?? null);
    if ($id <= 0 && $voucherId === null) {
        $voucherId = generateFuelVoucherId($pdo, $recordDate);
    }

    if (strcasecmp($approvalStatus, 'Pending') !== 0 && !canApproveFuel($pdo, (int)($_SESSION['user_id'] ?? 0))) {
        $pdo->rollBack();
        respond(['status' => 'error', 'message' => 'You are not allowed to approve fuel requests']);
    }

    $payload = [
        'voucher_id' => $voucherId,
        'created_by_user_id' => (int)($_SESSION['user_id'] ?? 0),
        'approved_by_user_id' => $approvedByUserId,
        'approval_status' => $approvalStatus,
        'vehicle_id' => $vehicleId,
        'office_id' => $officeId,
        'office_name' => normalizeNullableString($_POST['office_name'] ?? null) ?? ($vehicle['office_name'] ?? null),
        'company_id' => $companyId,
        'company_name' => normalizeNullableString($_POST['company_name'] ?? null) ?? ($vehicle['company_name'] ?? null),
        'vehicle_type_setup_id' => normalizeNullableInt($_POST['vehicle_type_setup_id'] ?? null),
        'driver_id' => $driverId,
        'fleet_supervisor_user_id' => $fleetSupervisorUserId,
        'fleet_supervisor_name' => normalizeNullableString($_POST['fleet_supervisor_name'] ?? null),
        'fuel_type_id' => $fuelTypeId,
        'fuel_level' => $fuelLevel,
        'quantity_litres' => $fuelPurchaseLiters,
        'dispensed_litres' => $dispensedLitres,
        'filling_station' => $fillingStation,
        'filling_station_id' => $fillingStationId,
        'state_id' => $stateId,
        'city_id' => null,
        'location_id' => null,
        'price_history_id' => $priceHistoryId,
        'record_date' => $recordDate,
        'fueling_time' => $fuelingTime,
        'fueling_type' => $fuelingType,
        'fueling_status' => $fuelingStatus,
        'month_year' => $monthYear,
        'vehicle_no' => $vehicle['plate_number'] ?? normalizeNullableString($_POST['vehicle_no'] ?? null),
        'vehicle_type_label' => normalizeNullableString($_POST['vehicle_type_label'] ?? null) ?? ($vehicle['vehicle_type_label'] ?? null),
        'driver_name' => $driverName,
        'fuel_purchase_liters' => $fuelPurchaseLiters,
        'naira_per_liter' => $nairaPerLiter,
        'total_amount' => $totalAmount,
        'card_balance' => $cardBalance,
        'fuel_card_id' => $fuelCardId,
        'actual_mileage' => $actualMileage,
        'mileage_km' => $mileageKm,
        'mileage_date' => $mileageDate,
        'mileage_time' => $mileageTime,
        'begin_km' => $beginKm,
        'end_km' => $endKm,
        'monthly_mileage_km' => $monthlyMileageKm,
        'distance_km' => $distanceKm,
        'total_fuel_consumption_ltr_month' => $totalFuelConsumption,
        'baseline_difference' => $baselineDifference,
        'over_baseline_rate' => $overBaselineRate,
        'notes' => $notes,
        'comment_text' => $commentText
    ];

    if ($id > 0) {
        $before = fetchFuelEntry($pdo, $id);
        if (!$before) {
            respond(['status' => 'error', 'message' => 'Fuel entry not found']);
        }
        $payload['created_by_user_id'] = (int)($before['created_by_user_id'] ?? ($_SESSION['user_id'] ?? 0));
        $payload['voucher_id'] = $before['voucher_id'] ?? $payload['voucher_id'];

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

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

        audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle_fuel_entry', $id, 'Fuel entry updated', $before, $payload);
    } else {
        $columns = implode(', ', array_keys($payload));
        $values = implode(', ', array_map(static fn(string $column): string => ':' . $column, array_keys($payload)));

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

        audit($pdo, (int)$_SESSION['user_id'], 'create', 'vehicle_fuel_entry', $id, 'Fuel entry created', null, $payload);
    }

    $pdo->commit();

    if (!$wasUpdate && strcasecmp($approvalStatus, 'Approved') === 0 && $fuelCardId !== null) {
        deductFuelCardBalance($pdo, $fuelCardId, (float)($totalAmount ?? 0), $voucherId, $id, (int)($_SESSION['user_id'] ?? 0), 'Auto-logged from fuel entry save');
    }

    syncVehicleFuelMonthly($pdo, $payload);
    sendFuelEntryAlert($pdo, $id, $payload, $wasUpdate ? 'updated' : 'created');

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

function handleSaveFuelType(PDO $pdo): void
{
    enforceFuelWritePermission();

    $name = normalizeNullableString($_POST['name'] ?? null);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Fuel type name is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_types (name, code, unit_label, is_active)
        VALUES (:name, :code, :unit_label, 1)
        ON DUPLICATE KEY UPDATE
            code = VALUES(code),
            unit_label = VALUES(unit_label),
            is_active = 1
    ");
    $stmt->execute([
        'name' => $name,
        'code' => normalizeNullableString($_POST['code'] ?? null),
        'unit_label' => trim((string)($_POST['unit_label'] ?? 'Litre')) ?: 'Litre'
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelVehicleType(PDO $pdo): void
{
    enforceFuelWritePermission();

    $name = normalizeNullableString($_POST['name'] ?? null);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Vehicle type name is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_vehicle_types (name, code, is_active)
        VALUES (:name, :code, 1)
        ON DUPLICATE KEY UPDATE
            code = VALUES(code),
            is_active = 1
    ");
    $stmt->execute([
        'name' => $name,
        'code' => normalizeNullableString($_POST['code'] ?? null)
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelPrice(PDO $pdo): void
{
    enforceFuelWritePermission();

    $fuelTypeId = (int)($_POST['fuel_type_id'] ?? 0);
    $effectiveDate = normalizeNullableDate($_POST['effective_date'] ?? null);
    $pricePerLiter = normalizeNullableDecimal($_POST['price_per_liter'] ?? null);

    if ($fuelTypeId <= 0 || $effectiveDate === null || $pricePerLiter === null) {
        respond(['status' => 'error', 'message' => 'Fuel type, effective date, and price are required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_price_history (
            fuel_type_id,
            state_id,
            city_id,
            location_id,
            price_per_liter,
            effective_date,
            vendor_name,
            remarks,
            is_active
        ) VALUES (
            :fuel_type_id,
            :state_id,
            :city_id,
            :location_id,
            :price_per_liter,
            :effective_date,
            :vendor_name,
            :remarks,
            1
        )
    ");
    $stmt->execute([
        'fuel_type_id' => $fuelTypeId,
        'state_id' => normalizeNullableInt($_POST['state_id'] ?? null),
        'city_id' => null,
        'location_id' => null,
        'price_per_liter' => $pricePerLiter,
        'effective_date' => $effectiveDate,
        'vendor_name' => normalizeNullableString($_POST['vendor_name'] ?? null),
        'remarks' => normalizeNullableString($_POST['remarks'] ?? null)
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelCompany(PDO $pdo): void
{
    enforceFuelWritePermission();

    $name = normalizeNullableString($_POST['name'] ?? null);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Company name is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_companies (name, code, is_active)
        VALUES (:name, :code, 1)
        ON DUPLICATE KEY UPDATE
            code = VALUES(code),
            is_active = 1
    ");
    $stmt->execute([
        'name' => $name,
        'code' => normalizeNullableString($_POST['code'] ?? null)
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelFillingStation(PDO $pdo): void
{
    enforceFuelWritePermission();

    $name = normalizeNullableString($_POST['name'] ?? null);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Filling station name is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_filling_stations (name, state_id, address, contact_phone, is_active)
        VALUES (:name, :state_id, :address, :contact_phone, 1)
        ON DUPLICATE KEY UPDATE
            state_id = VALUES(state_id),
            address = VALUES(address),
            contact_phone = VALUES(contact_phone),
            is_active = 1
    ");
    $stmt->execute([
        'name' => $name,
        'state_id' => normalizeNullableInt($_POST['state_id'] ?? null),
        'address' => normalizeNullableString($_POST['address'] ?? null),
        'contact_phone' => normalizeNullableString($_POST['contact_phone'] ?? null)
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelOffice(PDO $pdo): void
{
    enforceFuelWritePermission();

    $name = normalizeNullableString($_POST['name'] ?? null);
    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Office name is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO offices (name)
        VALUES (:name)
    ");
    $stmt->execute([
        'name' => $name
    ]);

    respond(['status' => 'success']);
}

function handleSaveFuelApprovalUser(PDO $pdo): void
{
    enforceFuelWritePermission();

    $userId = normalizeNullableSignedInt($_POST['user_id'] ?? null);
    if ($userId === null) {
        respond(['status' => 'error', 'message' => 'User is required']);
    }

    $stmt = $pdo->prepare("
        INSERT INTO fuel_approval_users (user_id, is_active)
        VALUES (:user_id, 1)
        ON DUPLICATE KEY UPDATE
            is_active = 1
    ");
    $stmt->execute([
        'user_id' => $userId
    ]);

    respond(['status' => 'success']);
}

function handleApproveFuelRequest(PDO $pdo): void
{
    $userId = (int)($_SESSION['user_id'] ?? 0);
    if (!canApproveFuel($pdo, $userId)) {
        respond(['status' => 'error', 'message' => 'You are not allowed to approve fuel requests']);
    }

    $id = (int)($_POST['id'] ?? 0);
    $approvalStatus = trim((string)($_POST['approval_status'] ?? ''));

    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid fuel request']);
    }
    if (!in_array($approvalStatus, ['Approved', 'Rejected'], true)) {
        respond(['status' => 'error', 'message' => 'Invalid approval status']);
    }

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

    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
            UPDATE vehicle_fuel_entries
            SET approval_status = :approval_status,
                approved_by_user_id = :approved_by_user_id,
                updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([
            'approval_status' => $approvalStatus,
            'approved_by_user_id' => $userId,
            'id' => $id
        ]);

        audit($pdo, $userId, 'update', 'vehicle_fuel_entry', $id, 'Fuel request approval updated', $before, [
            'approval_status' => $approvalStatus,
            'approved_by_user_id' => $userId
        ]);

        if ($approvalStatus === 'Approved') {
            autoCalculateVehicleBaselines($pdo, (int)$before['vehicle_id']);

            if (!empty($before['fuel_card_id'])) {
                $cardId = (int)$before['fuel_card_id'];
                $totalAmount = (float)($before['total_amount'] ?? 0);
                if ($cardId > 0 && $totalAmount > 0) {
                    deductFuelCardBalance($pdo, $cardId, $totalAmount, $before['voucher_id'] ?? null, $id, (int)($_SESSION['user_id'] ?? 0), 'Auto-logged from fuel approval');
                }
            }
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        error_log('fuel.php handleApproveFuelRequest: ' . $e->getMessage());
        respond(['status' => 'error', 'message' => 'Approval failed']);
    }

    sendFuelApprovalAlert($pdo, $id, $before, $approvalStatus);

    respond(['status' => 'success']);
}

function sendFuelEntryAlert(PDO $pdo, int $entryId, array $payload, string $verb): void
{
    try {
        if (strcasecmp((string)($payload['approval_status'] ?? 'Pending'), 'Pending') !== 0) {
            return;
        }

        $recipientIds = fuelApprovalUserIds($pdo);
        if (!$recipientIds) {
            return;
        }

        $voucher = (string)($payload['voucher_id'] ?? ('Fuel #' . $entryId));
        $vehicle = (string)($payload['vehicle_no'] ?? 'Vehicle');
        $litres = $payload['quantity_litres'] ?? $payload['fuel_purchase_liters'] ?? null;
        $amount = $payload['total_amount'] ?? null;
        $title = 'Fuel request pending approval: ' . $voucher;
        $message = "A fuel request was {$verb} and is pending approval.\n"
            . "Voucher: {$voucher}\n"
            . "Vehicle: {$vehicle}\n"
            . "Driver: " . (string)($payload['driver_name'] ?? '-') . "\n"
            . "Litres: " . ($litres !== null ? (string)$litres : '-') . "\n"
            . "Amount: " . ($amount !== null ? number_format((float)$amount, 2) : '-');

        alertUsersByIds($pdo, $recipientIds, $title, $message, [
            'event' => 'fuel.request_pending',
            'type' => 'fuel',
            'url' => 'fuel.php',
            'reference_type' => 'vehicle_fuel_entry',
            'reference_id' => $entryId,
            'channels' => ['in_app']
        ]);
    } catch (Throwable) {
        // Alert delivery must never block fuel capture.
    }
}

function sendFuelApprovalAlert(PDO $pdo, int $entryId, array $before, string $approvalStatus): void
{
    try {
        $recipientIds = array_values(array_unique(array_filter(array_map('intval', [
            $before['created_by_user_id'] ?? null,
            $before['fleet_supervisor_user_id'] ?? null,
        ]))));
        if (!$recipientIds) {
            return;
        }

        $voucher = (string)($before['voucher_id'] ?? ('Fuel #' . $entryId));
        $title = 'Fuel request ' . strtolower($approvalStatus) . ': ' . $voucher;
        $message = "Fuel request {$voucher} has been {$approvalStatus}.\n"
            . "Vehicle: " . (string)($before['vehicle_no'] ?? '-') . "\n"
            . "Driver: " . (string)($before['driver_name'] ?? '-') . "\n"
            . "Amount: " . (isset($before['total_amount']) ? number_format((float)$before['total_amount'], 2) : '-');

        alertUsersByIds($pdo, $recipientIds, $title, $message, [
            'event' => 'fuel.approval_' . strtolower($approvalStatus),
            'type' => 'fuel',
            'url' => 'fuel.php',
            'reference_type' => 'vehicle_fuel_entry',
            'reference_id' => $entryId,
            'channels' => ['in_app']
        ]);
    } catch (Throwable) {
        // Alert delivery must never block approval.
    }
}

function fuelApprovalUserIds(PDO $pdo): array
{
    try {
        $stmt = $pdo->query("
            SELECT DISTINCT user_id
            FROM fuel_approval_users
            WHERE is_active = 1
              AND deleted_at IS NULL
        ");
        return array_values(array_filter(array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN))));
    } catch (Throwable) {
        return [];
    }
}

function fetchVehicleSnapshot(PDO $pdo, int $vehicleId): array
{
    $stmt = $pdo->prepare("
        SELECT
            v.plate_number,
            office_id,
            o.name AS office_name,
            fc.id AS company_id,
            COALESCE(NULLIF(v.owner, ''), NULLIF(v.property, ''), s.name, 'Company') AS company_name,
            COALESCE(NULLIF(v.vehicle_model, ''), NULLIF(v.vehicle_type, ''), 'Vehicle') AS vehicle_type_label
        FROM vehicles v
        LEFT JOIN offices o ON o.id = v.office_id
        LEFT JOIN suppliers s ON s.id = v.supplier_id
        LEFT JOIN fuel_companies fc
            ON fc.name = COALESCE(NULLIF(v.owner, ''), NULLIF(v.property, ''), s.name)
        WHERE v.id = ?
          AND v.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([$vehicleId]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
}

function getVehicleFuelBaseline(PDO $pdo, int $vehicleId): ?float
{
    $stmt = $pdo->prepare("
        SELECT COALESCE(v.standard_fuel_consumption_l_per_100km, vfc.standard_consumption) AS baseline
        FROM vehicles v
        LEFT JOIN vehicle_fuel_config vfc ON vfc.vehicle_id = v.id
        WHERE v.id = ?
          AND v.deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([$vehicleId]);
    $baseline = $stmt->fetchColumn();
    return $baseline !== false && $baseline !== null ? (float)$baseline : null;
}

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

function syncVehicleFuelMonthly(PDO $pdo, array $payload): void
{
    $aggregate = $pdo->prepare("
        SELECT
            SUM(COALESCE(quantity_litres, fuel_purchase_liters, 0)) AS fuel_purchase_liters,
            CASE
                WHEN SUM(COALESCE(quantity_litres, fuel_purchase_liters, 0)) > 0
                    THEN ROUND(SUM(COALESCE(total_amount, 0)) / SUM(COALESCE(quantity_litres, fuel_purchase_liters, 0)), 2)
                ELSE NULL
            END AS naira_per_liter,
            SUM(COALESCE(mileage_km, monthly_mileage_km, distance_km, 0)) AS monthly_mileage_km,
            MIN(begin_km) AS begin_km,
            MAX(end_km) AS end_km,
            SUM(COALESCE(distance_km, mileage_km, monthly_mileage_km, 0)) AS distance_km,
            SUM(COALESCE(dispensed_litres, total_fuel_consumption_ltr_month, fuel_purchase_liters, 0)) AS total_fuel_consumption,
            SUM(COALESCE(baseline_difference, 0)) AS baseline_difference,
            AVG(COALESCE(over_baseline_rate, 0)) AS over_baseline_rate
        FROM vehicle_fuel_entries
        WHERE vehicle_id = :vehicle_id
          AND month_year = :month_year
          AND deleted_at IS NULL
    ");
    $aggregate->execute([
        'vehicle_id' => $payload['vehicle_id'],
        'month_year' => $payload['month_year']
    ]);
    $rollup = $aggregate->fetch(PDO::FETCH_ASSOC) ?: [];

    $monthlyPayload = [
        'vehicle_id' => $payload['vehicle_id'],
        'month_year' => $payload['month_year'],
        'fuel_purchase_liters' => normalizeRollupDecimal($rollup['fuel_purchase_liters'] ?? null),
        'naira_per_liter' => normalizeRollupDecimal($rollup['naira_per_liter'] ?? null),
        'monthly_mileage_km' => normalizeRollupInt($rollup['monthly_mileage_km'] ?? null),
        'begin_km' => normalizeRollupInt($rollup['begin_km'] ?? null),
        'end_km' => normalizeRollupInt($rollup['end_km'] ?? null),
        'distance_km' => normalizeRollupInt($rollup['distance_km'] ?? null),
        'total_fuel_consumption' => normalizeRollupDecimal($rollup['total_fuel_consumption'] ?? null),
        'baseline_difference' => normalizeRollupDecimal($rollup['baseline_difference'] ?? null),
        'over_baseline_rate' => normalizeRollupDecimal($rollup['over_baseline_rate'] ?? null)
    ];

    $stmt = $pdo->prepare("
        INSERT INTO vehicle_fuel_monthly (
            vehicle_id, 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
        ) VALUES (
            :vehicle_id, :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
        )
        ON DUPLICATE KEY UPDATE
            fuel_purchase_liters = VALUES(fuel_purchase_liters),
            naira_per_liter = VALUES(naira_per_liter),
            monthly_mileage_km = VALUES(monthly_mileage_km),
            begin_km = VALUES(begin_km),
            end_km = VALUES(end_km),
            distance_km = VALUES(distance_km),
            total_fuel_consumption = VALUES(total_fuel_consumption),
            baseline_difference = VALUES(baseline_difference),
            over_baseline_rate = VALUES(over_baseline_rate)
    ");
    $stmt->execute($monthlyPayload);

    autoCalculateVehicleBaselines($pdo, (int)$payload['vehicle_id']);
}

function handleDeleteFuelType(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM fuel_types WHERE id = ?");
    $before->execute([$id]);
    $record = $before->fetch(PDO::FETCH_ASSOC);
    softDelete($pdo, 'fuel_types', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'fuel_type', $id, 'Fuel type deactivated', $record);
    respond(['status' => 'success']);
}

function handleDeleteFuelOffice(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM offices WHERE id = ?");
    $before->execute([$id]);
    softDelete($pdo, 'offices', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'office', $id, 'Office deleted', $before->fetch(PDO::FETCH_ASSOC));
    respond(['status' => 'success']);
}

function handleDeleteFuelCompany(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM fuel_companies WHERE id = ?");
    $before->execute([$id]);
    $record = $before->fetch(PDO::FETCH_ASSOC);
    softDelete($pdo, 'fuel_companies', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'fuel_company', $id, 'Fuel company deactivated', $record);
    respond(['status' => 'success']);
}

function handleDeleteFuelFillingStation(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM fuel_filling_stations WHERE id = ?");
    $before->execute([$id]);
    $record = $before->fetch(PDO::FETCH_ASSOC);
    softDelete($pdo, 'fuel_filling_stations', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'fuel_filling_station', $id, 'Filling station deactivated', $record);
    respond(['status' => 'success']);
}

function handleDeleteFuelApprovalUser(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM fuel_approval_users WHERE id = ?");
    $before->execute([$id]);
    softDelete($pdo, 'fuel_approval_users', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'fuel_approval_user', $id, 'Approval user removed', $before->fetch(PDO::FETCH_ASSOC));
    respond(['status' => 'success']);
}

function handleDeleteFuelPrice(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
    $before = $pdo->prepare("SELECT * FROM fuel_price_history WHERE id = ?");
    $before->execute([$id]);
    softDelete($pdo, 'fuel_price_history', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'fuel_price_history', $id, 'Fuel price record deleted', $before->fetch(PDO::FETCH_ASSOC));
    respond(['status' => 'success']);
}

function handleDeleteFuelEntry(PDO $pdo): void
{
    enforceFuelWritePermission();
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);

    $before = fetchFuelEntry($pdo, $id);
    if (!$before) respond(['status' => 'error', 'message' => 'Fuel entry not found']);

    try {
        $pdo->beginTransaction();

        softDelete($pdo, 'vehicle_fuel_entries', $id);
        audit($pdo, (int)$_SESSION['user_id'], 'delete', 'vehicle_fuel_entry', $id, 'Fuel entry deleted', $before);

        if (!empty($before['vehicle_id']) && !empty($before['month_year'])) {
            $payload = ['vehicle_id' => $before['vehicle_id'], 'month_year' => $before['month_year']];
            syncVehicleFuelMonthly($pdo, $payload);
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        error_log('fuel.php handleDeleteFuelEntry: ' . $e->getMessage());
        respond(['status' => 'error', 'message' => 'Delete failed']);
    }

    respond(['status' => 'success']);
}

function handleFuelEntryCounts(PDO $pdo): void
{
    $stmt = $pdo->query("
        SELECT
            SUM(approval_status = 'Pending') AS pending,
            SUM(approval_status = 'Approved') AS approved,
            SUM(approval_status = 'Rejected') AS rejected
        FROM vehicle_fuel_entries
        WHERE deleted_at IS NULL
    ");
    $counts = $stmt->fetch(PDO::FETCH_ASSOC);
    respond([
        'status' => 'success',
        'data' => [
            'pending' => (int)($counts['pending'] ?? 0),
            'approved' => (int)($counts['approved'] ?? 0),
            'rejected' => (int)($counts['rejected'] ?? 0)
        ]
    ]);
}

function handleFuelExportCsv(PDO $pdo): void
{
    $vehicleId = (int)($_GET['vehicle_id'] ?? 0);
    $monthYear = trim((string)($_GET['month_year'] ?? ''));
    $approvalStatus = trim((string)($_GET['approval_status'] ?? ''));
    $search = trim((string)($_GET['search'] ?? ''));

    $where = ['1=1'];
    $params = [];

    $where[] = 'vfe.deleted_at IS NULL';

    if ($vehicleId > 0) {
        $where[] = 'vfe.vehicle_id = :vehicle_id';
        $params['vehicle_id'] = $vehicleId;
    }
    if ($monthYear !== '') {
        $where[] = 'vfe.month_year = :month_year';
        $params['month_year'] = $monthYear;
    }
    if ($approvalStatus !== '') {
        $where[] = 'vfe.approval_status = :approval_status';
        $params['approval_status'] = $approvalStatus;
    }
    if ($search !== '') {
        $where[] = '(vfe.voucher_id LIKE :search OR v.plate_number LIKE :search OR vfe.vehicle_no LIKE :search OR vfe.driver_name LIKE :search OR vfe.company_name LIKE :search OR vfe.filling_station LIKE :search)';
        $params['search'] = '%' . $search . '%';
    }

    $stmt = $pdo->prepare("
        SELECT
            vfe.voucher_id, vfe.record_date, vfe.fueling_time, vfe.fueling_type,
            v.plate_number, vfe.vehicle_no, vfe.driver_name,
            ft.name AS fuel_type,
            vfe.quantity_litres, vfe.fuel_purchase_liters, vfe.naira_per_liter, vfe.total_amount,
            vfe.mileage_km, vfe.monthly_mileage_km, vfe.distance_km,
            vfe.begin_km, vfe.end_km,
            vfe.baseline_difference, vfe.over_baseline_rate,
            vfe.approval_status, creator.name AS created_by, vfe.created_at,
            vfe.filling_station, vfe.notes, vfe.comment_text
        FROM vehicle_fuel_entries vfe
        INNER JOIN vehicles v ON v.id = vfe.vehicle_id
        LEFT JOIN fuel_types ft ON ft.id = vfe.fuel_type_id
        LEFT JOIN users creator ON creator.id = vfe.created_by_user_id
        WHERE " . implode(' AND ', $where) . "
        ORDER BY vfe.record_date DESC, vfe.id DESC
    ");
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (ob_get_level()) { ob_clean(); }
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="fuel_entries_' . date('Ymd_His') . '.csv"');

    $out = fopen('php://output', 'w');
    fprintf($out, chr(0xEF) . chr(0xBB) . chr(0xBF));

    $headers = [
        'Voucher ID', 'Date', 'Time', 'Fueling Type',
        'Plate Number', 'Vehicle No', 'Driver',
        'Fuel Type', 'Quantity (L)', 'Liters Purchased', 'Rate (NGN/L)', 'Total Amount',
        'Mileage', 'Monthly Mileage', 'Distance (km)',
        'Begin KM', 'End KM',
        'Baseline Diff', 'Over Baseline Rate',
        'Approval Status', 'Created By', 'Created At',
        'Filling Station', 'Notes'
    ];
    fputcsv($out, $headers);

    foreach ($rows as $row) {
        fputcsv($out, [
            $row['voucher_id'], $row['record_date'], $row['fueling_time'], $row['fueling_type'],
            $row['plate_number'], $row['vehicle_no'], $row['driver_name'],
            $row['fuel_type'], $row['quantity_litres'], $row['fuel_purchase_liters'], $row['naira_per_liter'], $row['total_amount'],
            $row['mileage_km'], $row['monthly_mileage_km'], $row['distance_km'],
            $row['begin_km'], $row['end_km'],
            $row['baseline_difference'], $row['over_baseline_rate'],
            $row['approval_status'], $row['created_by'], $row['created_at'],
            $row['filling_station'], $row['notes'] ?: $row['comment_text']
        ]);
    }

    fclose($out);
    exit;
}

function deductFuelCardBalance(PDO $pdo, int $fuelCardId, float $amount, ?string $voucherId, int $entryId, int $userId, string $notes): void
{
    if ($amount <= 0) return;
    $stmt = $pdo->prepare("SELECT current_balance FROM fuel_cards WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$fuelCardId]);
    $card = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$card) return;
    $balBefore = (float)($card['current_balance'] ?? 0);
    $balAfter = $balBefore - $amount;
    $pdo->prepare("UPDATE fuel_cards SET current_balance = ? WHERE id = ?")->execute([$balAfter, $fuelCardId]);
    $pdo->prepare("
        INSERT INTO fuel_card_transactions (fuel_card_id, transaction_type, amount, balance_before, balance_after, reference, vehicle_fuel_entry_id, notes, created_by_user_id)
        VALUES (?, 'purchase', ?, ?, ?, ?, ?, ?, ?)
    ")->execute([$fuelCardId, $amount, $balBefore, $balAfter, $voucherId, $entryId, $notes, $userId]);
}

function calculateTotalAmount(?string $liters, ?string $nairaPerLiter): ?string
{
    if ($liters === null || $nairaPerLiter === null) {
        return null;
    }
    return number_format(((float)$liters * (float)$nairaPerLiter), 2, '.', '');
}

function enforceFuelWritePermission(): void
{
    if (!isAdmin() && !can('fuel.create')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }
}

function canApproveFuel(PDO $pdo, int $userId): bool
{
    if ($userId <= 0) {
        return false;
    }
    if (isAdmin() || can('fuel.approve')) {
        return true;
    }

    $stmt = $pdo->prepare("
        SELECT 1
        FROM fuel_approval_users
        WHERE user_id = ?
          AND is_active = 1
          AND deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([$userId]);
    return (bool)$stmt->fetchColumn();
}

function generateFuelVoucherId(PDO $pdo, string $recordDate): string
{
    $prefix = 'FUEL-' . date('Ymd', strtotime($recordDate));
    $stmt = $pdo->prepare("
        SELECT COALESCE(MAX(CAST(SUBSTRING_INDEX(voucher_id, '-', -1) AS UNSIGNED)), 0) + 1
        FROM vehicle_fuel_entries
        WHERE voucher_id LIKE CONCAT(:prefix, '-%')
          AND deleted_at IS NULL
        FOR UPDATE
    ");
    $stmt->execute(['prefix' => $prefix]);
    $sequence = (int)$stmt->fetchColumn();

    return $prefix . '-' . str_pad((string)$sequence, 4, '0', STR_PAD_LEFT);
}

function handleFuelRequestReport(PDO $pdo): void
{
    $start = normalizeNullableDate($_GET['start_date'] ?? null);
    $end = normalizeNullableDate($_GET['end_date'] ?? null);
    $status = normalizeNullableString($_GET['approval_status'] ?? null);
    $vehicleId = (int)($_GET['vehicle_id'] ?? 0);
    $search = trim((string)($_GET['search'] ?? ''));

    $where = ['vfe.deleted_at IS NULL'];
    $params = [];

    if ($start) {
        $where[] = 'vfe.record_date >= :start';
        $params['start'] = $start;
    }
    if ($end) {
        $where[] = 'vfe.record_date <= :end';
        $params['end'] = $end;
    }
    if ($status !== '') {
        $where[] = 'vfe.approval_status = :status';
        $params['status'] = $status;
    }
    if ($vehicleId > 0) {
        $where[] = 'vfe.vehicle_id = :vehicle_id';
        $params['vehicle_id'] = $vehicleId;
    }
    if ($search !== '') {
        $where[] = '(vfe.voucher_id LIKE :search OR v.plate_number LIKE :search OR vfe.vehicle_no LIKE :search OR vfe.driver_name LIKE :search OR vfe.company_name LIKE :search OR vfe.filling_station LIKE :search)';
        $params['search'] = '%' . $search . '%';
    }
    if ($vehicleSearch !== '') {
        $where[] = 'v.plate_number LIKE :vehicle_search';
        $params['vehicle_search'] = '%' . $vehicleSearch . '%';
    }

    $whereSql = 'WHERE ' . implode(' AND ', $where);

    $stmt = $pdo->prepare("
        SELECT
            vfe.id, vfe.voucher_id, vfe.record_date, vfe.vehicle_no, vfe.vehicle_type_label,
            vfe.driver_name, vfe.fuel_type_id, ft.name AS fuel_type_name,
            vfe.fuel_purchase_liters, vfe.naira_per_liter, vfe.total_amount,
            vfe.filling_station, vfe.approval_status, vfe.month_year,
            vfe.created_by_user_id, vfe.approved_by_user_id,
            cu.name AS created_by_name, au.name AS approved_by_name,
            vfe.mileage_km, vfe.actual_mileage
        FROM vehicle_fuel_entries vfe
        LEFT JOIN fuel_types ft ON ft.id = vfe.fuel_type_id
        LEFT JOIN users cu ON cu.id = vfe.created_by_user_id
        LEFT JOIN users au ON au.id = vfe.approved_by_user_id
        {$whereSql}
        ORDER BY vfe.record_date DESC, vfe.id DESC
        LIMIT 500
    ");
    $stmt->execute($params);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $kpis = [
        'total_count' => 0,
        'total_liters' => 0,
        'total_amount' => 0,
        'pending_count' => 0,
        'pending_amount' => 0,
        'approved_count' => 0,
        'approved_amount' => 0,
        'rejected_count' => 0,
        'rejected_amount' => 0,
    ];

    foreach ($data as $row) {
        $amount = (float)($row['total_amount'] ?? 0);
        $liters = (float)($row['fuel_purchase_liters'] ?? 0);
        $kpis['total_count']++;
        $kpis['total_liters'] += $liters;
        $kpis['total_amount'] += $amount;

        $statusVal = trim((string)($row['approval_status'] ?? ''));
        if ($statusVal === 'Approved') {
            $kpis['approved_count']++;
            $kpis['approved_amount'] += $amount;
        } elseif ($statusVal === 'Rejected') {
            $kpis['rejected_count']++;
            $kpis['rejected_amount'] += $amount;
        } else {
            $kpis['pending_count']++;
            $kpis['pending_amount'] += $amount;
        }
    }

    $kpis['total_liters'] = round($kpis['total_liters'], 2);
    $kpis['total_amount'] = round($kpis['total_amount'], 2);
    $kpis['approved_amount'] = round($kpis['approved_amount'], 2);
    $kpis['rejected_amount'] = round($kpis['rejected_amount'], 2);
    $kpis['pending_amount'] = round($kpis['pending_amount'], 2);

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

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