<?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(scoring) AS Nama,
    COUNT(CASE WHEN STATUS IN ('Cair', 'Acc') THEN nik_nasabah END) AS Acc,
    COUNT(CASE WHEN STATUS IN ('Reject', 'Batal') THEN nik_nasabah END) AS Reject,
    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 status in ('Cair', 'Acc','Reject', 'Batal')
 $whereClause  -- Filter berdasarkan cabang jika bukan 'SEMUA CABANG'
GROUP BY scoring
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()) {
        $nama = $row['Nama'];
        $acc = (int)$row['Acc'];
        $reject = $row['Reject'];
        $total = $row['Total'];  // Tambahkan nama cabang ke output

        // Menambahkan data ranking ke dalam array
        $data[] = [
            "nama" => $nama,
            "acc" => $acc,
            "reject" => $reject,
            "total" => $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();
?>
