<?php
require_once('connectionSC.php');

// Check if 'spv' and 'nasabah' parameters are set
if (isset($_GET['spv']) && isset($_GET['nasabah'])) {
    $spv = $_GET['spv'];
    $nasabah = $_GET['nasabah'];
    $status = isset($_GET['status']) ? $_GET['status'] : ''; // Optional status filter

    // Base SQL query
    $sql = "SELECT nasabah, plafond, marketing, surveyor, tanggal_add, status 
            FROM hasil 
            WHERE spv LIKE ? 
            AND nasabah LIKE ?";

    // Add filter for status if provided
    if (!empty($status)) {
        $sql .= " AND status = ?";
    }

    $sql .= " ORDER BY tanggal_add DESC";

    // Prepare the SQL statement
    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        echo json_encode(["error" => "Failed to prepare statement."]);
        exit();
    }

    // Bind parameters
    if (!empty($status)) {
        $spvParam = "%$spv%";
        $nasabahParam = "%$nasabah%";
        $stmt->bind_param("sss", $spvParam, $nasabahParam, $status);
    } else {
        $spvParam = "%$spv%";
        $nasabahParam = "%$nasabah%";
        $stmt->bind_param("ss", $spvParam, $nasabahParam);
    }

    // Execute the query
    if (!$stmt->execute()) {
        echo json_encode(["error" => "Failed to execute query."]);
        exit();
    }

    $result = $stmt->get_result();

    // Check if there are any results
    if ($result->num_rows > 0) {
        $return_arr['hasil'] = [];

        // Fetch the results and process them
        while ($row = $result->fetch_assoc()) {
            // Replace surveyor 'EXPRESS' with 'REGULER'
          if ($row['surveyor'] !== 'EXPRESS' && $row['surveyor'] !== 'Tanpa Survey') {
                $surveyor = 'REGULER';
            } else {
                $surveyor = $row['surveyor']; // Keep the original value if it's 'EXPRESS' or 'TANPA SURVEY'
            }

            $return_arr['hasil'][] = [
                'surveyor' => strtoupper($surveyor),
                'nasabah' => strtoupper($row['nasabah']),
                'plafond' => $row['plafond'],
                'status' => strtoupper($row['status']),
                'tanggal_add' => $row['tanggal_add'],
                'marketing' => strtoupper($row['marketing']),
            ];
        }

        // Return the results as JSON
        echo json_encode($return_arr);
    } else {
        // No results found
        echo json_encode(["status" => "empty", "message" => "No data found."]);
    }

    // Close the statement
    $stmt->close();
} else {
    // If required parameters are not provided
    echo json_encode(["error" => "Missing required parameters."]);
}

// Close the connection
$conn->close();
?>
