<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';
$user      = requireAuth();
$db        = getDB();
$action    = post('action','list');
$vehicleId = (int)post('vehicle_id',0);
if (!$vehicleId) apiError('Vehicle ID required.', 422);

// Helper: get latest odo reading for a vehicle across ALL sources
function getLatestOdo($db, $vehicleId) {
    $stmt = $db->prepare("
        SELECT MAX(odo) AS latest FROM (
            SELECT odo_reading AS odo FROM fleet_travel_log WHERE vehicle_id=? AND odo_reading IS NOT NULL
            UNION ALL
            SELECT odo_reading AS odo FROM fleet_costs WHERE vehicle_id=? AND odo_reading IS NOT NULL
            UNION ALL
            SELECT odo_start AS odo FROM job_cards WHERE vehicle_id=? AND odo_start IS NOT NULL
            UNION ALL
            SELECT odo_end AS odo FROM job_cards WHERE vehicle_id=? AND odo_end IS NOT NULL
        ) AS all_odos
    ");
    $stmt->execute([$vehicleId, $vehicleId, $vehicleId, $vehicleId]);
    $row = $stmt->fetch();
    return (int)($row['latest'] ?? 0);
}

if ($action === 'list') {
    $limit = (int)post('limit', 200);

    // Unified ODO log — CONVERT all strings to utf8mb4 to avoid collation mismatch
    $stmt = $db->prepare("
        SELECT * FROM (

            -- Manual entries from fleet_travel_log
            SELECT
                tl.id,
                tl.reading_date,
                tl.odo_reading,
                'manual' AS source,
                CAST(NULL AS CHAR) COLLATE utf8mb4_unicode_ci AS source_ref,
                CONVERT(COALESCE(tl.notes,'') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS notes,
                CONVERT(COALESCE(u.full_name,'System') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS recorded_by_name,
                tl.created_at
            FROM fleet_travel_log tl
            LEFT JOIN users u ON u.id = tl.recorded_by
            WHERE tl.vehicle_id = ? AND tl.source = 'manual'

            UNION ALL

            -- Fuel slip ODO readings
            SELECT
                fc.id,
                fc.cost_date AS reading_date,
                fc.odo_reading,
                'fuel_slip' AS source,
                CONVERT(CONCAT('Fuel: R', FORMAT(fc.amount,2)) USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source_ref,
                CONVERT(COALESCE(fc.description,'') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS notes,
                CONVERT(COALESCE(u.full_name,'System') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS recorded_by_name,
                fc.created_at
            FROM fleet_costs fc
            LEFT JOIN users u ON u.id = fc.created_by
            WHERE fc.vehicle_id = ? AND fc.odo_reading IS NOT NULL AND fc.odo_reading > 0

            UNION ALL

            -- Job card ODO start
            SELECT
                jc.id,
                COALESCE(jc.scheduled_date, DATE(jc.created_at)) AS reading_date,
                jc.odo_start AS odo_reading,
                'job_card' AS source,
                CONVERT(CONCAT(jc.job_number, ' (depart)') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source_ref,
                CONVERT(COALESCE(jc.title,'') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS notes,
                CONVERT(COALESCE(u.full_name,'System') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS recorded_by_name,
                jc.created_at
            FROM job_cards jc
            LEFT JOIN users u ON u.id = jc.created_by
            WHERE jc.vehicle_id = ? AND jc.odo_start IS NOT NULL AND jc.odo_start > 0

            UNION ALL

            -- Job card ODO end
            SELECT
                jc.id,
                COALESCE(DATE(jc.completed_at), jc.scheduled_date, DATE(jc.created_at)) AS reading_date,
                jc.odo_end AS odo_reading,
                'job_card' AS source,
                CONVERT(CONCAT(jc.job_number, ' (return)') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS source_ref,
                CONVERT(COALESCE(jc.title,'') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS notes,
                CONVERT(COALESCE(u.full_name,'System') USING utf8mb4) COLLATE utf8mb4_unicode_ci AS recorded_by_name,
                jc.updated_at AS created_at
            FROM job_cards jc
            LEFT JOIN users u ON u.id = jc.created_by
            WHERE jc.vehicle_id = ? AND jc.odo_end IS NOT NULL AND jc.odo_end > 0

        ) AS unified
        ORDER BY reading_date DESC, created_at DESC
        LIMIT ?
    ");
    $stmt->execute([$vehicleId, $vehicleId, $vehicleId, $vehicleId, $limit]);
    $logs = $stmt->fetchAll();

    // Metrics from unified view
    $metricsStmt = $db->prepare("
        SELECT
            MIN(odo) AS min_odo,
            MAX(odo) AS max_odo,
            COUNT(*) AS reading_count,
            MIN(rd) AS first_date,
            MAX(rd) AS last_date
        FROM (
            SELECT odo_reading AS odo, reading_date AS rd FROM fleet_travel_log WHERE vehicle_id=? AND odo_reading IS NOT NULL AND source='manual'
            UNION ALL
            SELECT odo_reading, cost_date FROM fleet_costs WHERE vehicle_id=? AND odo_reading IS NOT NULL AND odo_reading > 0
            UNION ALL
            SELECT odo_start, COALESCE(scheduled_date, DATE(created_at)) FROM job_cards WHERE vehicle_id=? AND odo_start IS NOT NULL AND odo_start > 0
            UNION ALL
            SELECT odo_end, COALESCE(DATE(completed_at), scheduled_date, DATE(created_at)) FROM job_cards WHERE vehicle_id=? AND odo_end IS NOT NULL AND odo_end > 0
        ) AS m
    ");
    $metricsStmt->execute([$vehicleId, $vehicleId, $vehicleId, $vehicleId]);
    $metrics = $metricsStmt->fetch();

    // Monthly summary (last 6 months)
    $monthStmt = $db->prepare("
        SELECT DATE_FORMAT(rd,'%Y-%m') AS month,
               MAX(odo) - MIN(odo) AS km_this_month,
               COUNT(*) AS readings
        FROM (
            SELECT odo_reading AS odo, reading_date AS rd FROM fleet_travel_log WHERE vehicle_id=? AND odo_reading IS NOT NULL AND source='manual'
            UNION ALL
            SELECT odo_reading, cost_date FROM fleet_costs WHERE vehicle_id=? AND odo_reading IS NOT NULL AND odo_reading > 0
            UNION ALL
            SELECT odo_start, COALESCE(scheduled_date, DATE(created_at)) FROM job_cards WHERE vehicle_id=? AND odo_start IS NOT NULL AND odo_start > 0
            UNION ALL
            SELECT odo_end, COALESCE(DATE(completed_at), scheduled_date, DATE(created_at)) FROM job_cards WHERE vehicle_id=? AND odo_end IS NOT NULL AND odo_end > 0
        ) AS m
        WHERE rd >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
        GROUP BY DATE_FORMAT(rd,'%Y-%m')
        ORDER BY month DESC
    ");
    $monthStmt->execute([$vehicleId, $vehicleId, $vehicleId, $vehicleId]);
    $monthly = $monthStmt->fetchAll();

    $latestOdo = (int)($metrics['max_odo'] ?? 0);

    apiSuccess(['logs' => $logs, 'metrics' => $metrics, 'monthly' => $monthly, 'latest_odo' => $latestOdo]);
}

if ($action === 'add') {
    $odo   = (int)post('odo_reading');
    $date  = post('reading_date', date('Y-m-d'));
    $notes = post('notes', '');
    if ($odo <= 0) apiError('ODO reading required.', 422);

    $latest = getLatestOdo($db, $vehicleId);
    if ($latest > 0 && $odo < $latest) {
        apiError("ODO reading cannot be less than the latest recorded reading ({$latest} km).", 422);
    }

    $db->prepare("INSERT INTO fleet_travel_log (vehicle_id,odo_reading,reading_date,source,recorded_by,notes) VALUES (?,?,?,'manual',?,?)")
       ->execute([$vehicleId, $odo, $date, $user['id'], $notes]);
    $db->prepare("UPDATE fleet_vehicles SET current_odo=GREATEST(COALESCE(current_odo,0),?) WHERE id=?")->execute([$odo, $vehicleId]);
    apiSuccess(['id' => (int)$db->lastInsertId()], 'Reading logged.');
}

if ($action === 'delete') {
    $db->prepare("DELETE FROM fleet_travel_log WHERE id=? AND vehicle_id=? AND source='manual'")->execute([(int)post('id'), $vehicleId]);
    apiSuccess([], 'Deleted.');
}
apiError('Unknown action.', 400);