<?php
require("php_con.php");

if(isset($_POST["query"])) {
    $search = mysqli_real_escape_string($connation, $_POST["query"]);
    $query = "SELECT ip.plan_id, cd.cs_name, cd.cs_mobile, ip.product_name, ip.remaining_balance
              FROM installment_plans ip
              JOIN customer_details cd ON ip.cs_id = cd.cs_id
              WHERE ip.status = 'active'
              AND (cd.cs_name LIKE '%".$search."%' OR cd.cs_mobile LIKE '%".$search."%')
              ORDER BY ip.start_date DESC";
    $result = mysqli_query($connation, $query);
    
    $output = '<option value="">Select a plan</option>';
    if(mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_array($result)) {
            $output .= '<option value="'.$row["plan_id"].'">'.$row["cs_name"].' ('.$row["cs_mobile"].') - '.$row["product_name"].' (Remaining: '.$row["remaining_balance"].')</option>';
        }
    } else {
        $output .= '<option value="">No matching plans found</option>';
    }
    echo $output;
}
?>