<?php
// get_mitra_by_distance.php
include_once 'config/koneksi.php';

if (session_status() === PHP_SESSION_NONE) { session_start(); }

// 1. Ambil tahun aktif
$result_tahun = $conn->query("SELECT id FROM tahun_aktif WHERE is_aktif = 1 LIMIT 1");
$tahun_aktif_id = ($result_tahun && $result_tahun->num_rows > 0) ? $result_tahun->fetch_assoc()['id'] : null;

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

if (!$desa_id || !$tahun_aktif_id) {
    echo json_encode([]);
    exit;
}

// 2. Ambil koordinat desa
$sql_desa = "SELECT latitude, longitude FROM wilayahtugas WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql_desa);
$stmt->bind_param("s", $desa_id);
$stmt->execute();
$res_desa = $stmt->get_result()->fetch_assoc();

// Jika desa tidak punya koordinat, kirim semua mitra tahun aktif urut abjad
if (!$res_desa || is_null($res_desa['latitude']) || is_null($res_desa['longitude'])) {
    $sql_f = "SELECT id, nama, NULL as jarak FROM mitra WHERE tahun_aktif_id = ? ORDER BY nama ASC";
    $st = $conn->prepare($sql_f);
    $st->bind_param("i", $tahun_aktif_id);
    $st->execute();
    echo json_encode($st->get_result()->fetch_all(MYSQLI_ASSOC));
    exit;
}

$lat1 = $res_desa['latitude'];
$lon1 = $res_desa['longitude'];

// 3. Query "Smart Order": 
// - Jika ada koordinat: Hitung jarak asli.
// - Jika koordinat NULL: Beri nilai 99999 (supaya pindah ke paling bawah).
$sql_mitra = "SELECT id, nama, 
    CASE 
        WHEN latitude IS NULL OR longitude IS NULL THEN NULL
        ELSE (6371 * acos(
            LEAST(1, GREATEST(-1, 
                cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) + 
                sin(radians(?)) * sin(radians(latitude))
            ))
        ))
    END AS jarak 
    FROM mitra 
    WHERE tahun_aktif_id = ? 
    ORDER BY (jarak IS NULL), jarak ASC, nama ASC";

$stmt_m = $conn->prepare($sql_mitra);
$stmt_m->bind_param("dddi", $lat1, $lon1, $lat1, $tahun_aktif_id);
$stmt_m->execute();
$res_mitra = $stmt_m->get_result()->fetch_all(MYSQLI_ASSOC);

echo json_encode($res_mitra);