<?php
// Database connection
include("../include/connection.php");

$conn = new mysqli($host, $user, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Read POST data sent by DataTables
$draw = $_POST['draw']; // For tracking
$start = $_POST['start']; // For pagination (start position)
$length = $_POST['length']; // For pagination (number of records to fetch)
$searchValue = $_POST['search']['value']; // Search value
$orderColumn = $_POST['order'][0]['column']; // Column index for ordering
$orderDir = $_POST['order'][0]['dir']; // asc or desc




// Column array to map DataTables column index to database columns
$columns = ['c.memberNo', 'm.memberName', 'm.memberPhoneNo', 'l.amount', 'c.amountPaid', 'c.receiptNo', 'c.paidDate', 'o.offerName'];

// Calculate total number of records (before filtering)
$totalRecordsQuery = "SELECT COUNT(*) AS total FROM collection";
$totalRecordsResult = $conn->query($totalRecordsQuery);
$totalRecords = $totalRecordsResult->fetch_assoc()['total'];

// Query to fetch filtered records with search and pagination
$sql = "
    SELECT c.memberNo, m.memberName, m.memberPhoneNo, l.amount, c.amountPaid, c.receiptNo, c.paidDate, c.officerID, o.offerName AS officerName
    FROM collection c
    LEFT JOIN member m ON c.memberNo = m.memberNo
    LEFT JOIN officer o ON c.officerID = o.officerNo
    LEFT JOIN loan l ON l.loanNo = c.loanNo
    WHERE 1 = 1";

// Add search filtering
if (!empty($searchValue)) {
    $sql .= " AND (m.memberName LIKE ? OR m.memberPhoneNo LIKE ? OR c.PaidDate LIKE ? OR o.offerName LIKE ? )";
}

// Add ordering
 $sql .= " ORDER BY " . $columns[$orderColumn] . " $orderDir";

// Add pagination
$sql .= " LIMIT ?, ?";

// Prepare the query
$stmt = $conn->prepare($sql);

// Bind parameters for search, start, and length
if (!empty($searchValue)) {
    $searchTerm = '%' . $searchValue . '%';
    $stmt->bind_param('ssssii', $searchTerm, $searchTerm, $searchTerm, $searchTerm, $start, $length);
} else {
    $stmt->bind_param('ii', $start, $length);
}

// Execute the query
$stmt->execute();
$result = $stmt->get_result();

// Fetch the data
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

// Calculate the total number of filtered records
$filteredRecordsQuery = "SELECT COUNT(*) AS filtered FROM collection c
    LEFT JOIN member m ON c.memberNo = m.memberNo
    LEFT JOIN officer o ON c.officerID = o.officerNo
    LEFT JOIN loan l ON l.loanNo = c.loanNo
    WHERE 1 = 1";

if (!empty($searchValue)) {
 /*   $filteredRecordsQuery .= " AND (m.memberName LIKE '%$searchValue%' OR m.memberPhoneNo LIKE '%$searchValue%' OR c.memberNo LIKE '%$searchValue%' OR o.offerName LIKE '%$searchValue%')"; */
    $filteredRecordsQuery .= " AND (m.memberName LIKE '%$searchValue%' OR m.memberPhoneNo LIKE '%$searchValue%' OR c.PaidDate LIKE '%$searchValue%' OR o.offerName LIKE '%$searchValue%')";
}

$filteredRecordsResult = $conn->query($filteredRecordsQuery);
$totalFilteredRecords = $filteredRecordsResult->fetch_assoc()['filtered'];

// Prepare response for DataTables
$response = [
    "draw" => intval($draw),
    "recordsTotal" => intval($totalRecords),
    "recordsFiltered" => intval($totalFilteredRecords),
    "data" => $data
];

// Send JSON response
echo json_encode($response);

$conn->close();
?>
