<?php

header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");

require_once("../config/db.php");

// 🔥 FINAL QUERY (WITH ALL DETAILS)
$query = "SELECT 
    v.id,
    v.name,
    v.mobile,
    v.selfie,
    v.suspicious_note,

    vg.village_name,

    vl.created_at AS flagged_date,

    u.name AS marked_by_name

FROM visitors v

LEFT JOIN visitor_logs vl 
    ON v.id = vl.visitor_id

LEFT JOIN villages vg 
    ON vl.village_id = vg.id

LEFT JOIN users u 
    ON vl.checked_by = u.id  -- ⚠️ change if column name different

WHERE v.is_suspicious = 1

ORDER BY vl.created_at DESC";

$result = $conn->query($query);

$data = [];

if ($result) {
    while ($row = $result->fetch_assoc()) {

        // 🔹 NULL HANDLING
        if (!$row['selfie']) {
            $row['selfie'] = "";
        }

        if (!$row['village_name']) {
            $row['village_name'] = "Unknown Village";
        }

        if (!$row['flagged_date']) {
            $row['flagged_date'] = "";
        }

        if (!$row['marked_by_name']) {
            $row['marked_by_name'] = "Unknown";
        }

        if (!$row['suspicious_note']) {
            $row['suspicious_note'] = "";
        }

        $data[] = $row;
    }
}

echo json_encode([
    "status" => true,
    "data" => $data
]);