<?php
// Include database connection
require_once('connectionSC.php');

// Cek koneksi database
if ($conn->connect_error) {
    die(json_encode([
        "status" => "error",
        "message" => "Error in connecting to Database: " . $conn->connect_error
    ]));
}

// Mendapatkan parameter cabang
if (isset($_GET['branch'])) {
    $branch = $_GET['branch'];
} else {
    echo json_encode([
        "status" => "error",
        "message" => "Branch parameter is required."
    ]);
    exit();
}

// Sanitize branch untuk menghindari SQL injection
$branch = strtoupper(trim($branch)); // Uppercase untuk konsistensi nama cabang

// Menentukan kondisi query berdasarkan branch
$whereClause = "";
if ($branch !== "SEMUA CABANG") {
    $whereClause = "AND UPPER(h_cabang) = '$branch'";
}

// Query untuk mengambil data ranking berdasarkan SPV dan cabang
$query = "SELECT 
    UPPER(surveyor) AS Nama
FROM hasil
WHERE (MONTH(tanggal_edit) = MONTH(NOW()) AND YEAR(tanggal_edit) = YEAR(NOW()))
    AND NOT (MONTH(tanggal_edit) = 12 AND YEAR(tanggal_edit) < YEAR(NOW()))
 $whereClause
GROUP BY surveyor
ORDER BY COUNT(nik_nasabah) DESC";  // Corrected the ORDER BY to use the correct aggregation (COUNT)

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

// Looping data nama surveyor
while ($row = $result->fetch_assoc()) {
    $nama = $conn->real_escape_string($row['Nama']);  // Sanitize the surveyor name

    // Query untuk cek jumlah surveyor yang memiliki nama yang sama
    $checkQuery = "SELECT COUNT(*) AS jumlahNama
                   FROM (
                       SELECT UPPER(surveyor) AS Nama
                       FROM hasil
                       WHERE (MONTH(tanggal_edit) = MONTH(NOW()) AND YEAR(tanggal_edit) = YEAR(NOW()))
                       AND NOT (MONTH(tanggal_edit) = 12 AND YEAR(tanggal_edit) < YEAR(NOW()))
                       AND surveyor LIKE '%$nama%'
                       GROUP BY surveyor
                   ) AS jumlahNama;";

    $checkResult = $conn->query($checkQuery);
    $checkRow = $checkResult->fetch_assoc();

    // Jika count lebih dari 1, lakukan update
    if ($checkRow['jumlahNama'] > 1) {
        $updateQuery = "UPDATE hasil
                        SET surveyor = '$nama'
                        WHERE (MONTH(tanggal_edit) = MONTH(NOW()) AND YEAR(tanggal_edit) = YEAR(NOW()))
                        AND NOT (MONTH(tanggal_edit) = 12 AND YEAR(tanggal_edit) < YEAR(NOW()))
                        AND surveyor LIKE '%$nama%';";
        $conn->query($updateQuery);
    }
}

// Ambil ulang data ranking setelah update
$query = "SELECT 
    UPPER(surveyor) AS Nama,
    COUNT(nik_nasabah) AS Total
FROM hasil
WHERE (MONTH(tanggal_edit) = MONTH(NOW()) AND YEAR(tanggal_edit) = YEAR(NOW()))
    AND NOT (MONTH(tanggal_edit) = 12 AND YEAR(tanggal_edit) < YEAR(NOW())) and surveyor not in ('EXPRESS','Tanpa Survey') and status in ('Cair', 'Acc','Reject', 'Batal')
 $whereClause
GROUP BY surveyor
ORDER BY Total DESC;";

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

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

if ($result && $result->num_rows > 0) {
    $data = [];
    // Mengambil data untuk ranking
    while ($row = $result->fetch_assoc()) {
        $data[] = [
            "nama" => $row['Nama'],
            "total" => $row['Total']
        ];
    }

    // Output data sebagai JSON
    echo json_encode([
        "status" => "success",
        "ranking" => $data
    ]);
} else {
    echo json_encode([
        "status" => "empty",
        "message" => "No data found for the current month in branch $branch."
    ]);
}

// Menutup koneksi
$conn->close();
?>
