<?php
session_start();
require_once '../includes/config.php';

// Database table name
$table = 'users';

// Primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables
$columns = array(
    array('db' => 'id', 'dt' => 0),
    array('db' => 'username', 'dt' => 1),
    array('db' => 'first_name', 'dt' => 2),
    array('db' => 'last_name', 'dt' => 3),
    array('db' => 'email', 'dt' => 4),
    array('db' => 'phone', 'dt' => 5),
    array('db' => 'id_number', 'dt' => 6),
    array('db' => 'available_balance', 'dt' => 7),
    array('db' => 'fund_balance', 'dt' => 8),
    array('db' => 'total_profit', 'dt' => 9),
    array('db' => 'status', 'dt' => 10),
    array('db' => 'registration_date', 'dt' => 11),
    array('db' => 'email_verified', 'dt' => 12)
);

// SQL server connection information
$sql_details = array(
    'user' => 'your_db_user',
    'pass' => 'your_db_password',
    'db'   => 'your_db_name',
    'host' => 'localhost'
);

// But for simplicity, we'll use direct mysqli queries
$request = $_REQUEST;

// Build WHERE clause
$where = "WHERE 1=1";

// Search filter
if(!empty($request['search']['value'])) {
    $search = mysqli_real_escape_string($conn, $request['search']['value']);
    $where .= " AND (username LIKE '%$search%' OR email LIKE '%$search%' OR first_name LIKE '%$search%' OR last_name LIKE '%$search%' OR phone LIKE '%$search%')";
}

// Status filter
if(!empty($_POST['status'])) {
    $status = mysqli_real_escape_string($conn, $_POST['status']);
    $where .= " AND status = '$status'";
}

// Balance filter
if(!empty($_POST['balance'])) {
    $balance = $_POST['balance'];
    if($balance == 'low') {
        $where .= " AND available_balance < 100";
    } elseif($balance == 'medium') {
        $where .= " AND available_balance BETWEEN 100 AND 1000";
    } elseif($balance == 'high') {
        $where .= " AND available_balance > 1000";
    }
}

// Date range filter
if(!empty($_POST['date_from'])) {
    $date_from = mysqli_real_escape_string($conn, $_POST['date_from']);
    $where .= " AND DATE(registration_date) >= '$date_from'";
}
if(!empty($_POST['date_to'])) {
    $date_to = mysqli_real_escape_string($conn, $_POST['date_to']);
    $where .= " AND DATE(registration_date) <= '$date_to'";
}

// Total records
$total_query = "SELECT COUNT(*) as total FROM users $where";
$total_result = mysqli_query($conn, $total_query);
$total_data = mysqli_fetch_assoc($total_result);
$totalRecords = $total_data['total'];

// Get filtered records
$filtered_query = "SELECT COUNT(*) as total FROM users $where";
$filtered_result = mysqli_query($conn, $filtered_query);
$filtered_data = mysqli_fetch_assoc($filtered_result);
$totalFiltered = $filtered_data['total'];

// Order by
$order_by = '';
if(isset($request['order'][0]['column'])) {
    $column_idx = intval($request['order'][0]['column']);
    $order_dir = mysqli_real_escape_string($conn, $request['order'][0]['dir']);
    
    $order_columns = array('id', 'username', 'first_name', 'last_name', 'email', 'phone', 'id_number', 'available_balance', 'fund_balance', 'total_profit', 'status', 'registration_date', 'email_verified');
    if(isset($order_columns[$column_idx])) {
        $order_by = "ORDER BY " . $order_columns[$column_idx] . " " . $order_dir;
    }
}

// Limit
$limit = '';
if(isset($request['start']) && $request['length'] != -1) {
    $limit = "LIMIT " . intval($request['start']) . ", " . intval($request['length']);
}

// Get data
$query = "SELECT * FROM users $where $order_by $limit";
$result = mysqli_query($conn, $query);

$data = array();
while($row = mysqli_fetch_assoc($result)) {
    $nestedData = array();
    
    $nestedData[] = $row['id'];
    $nestedData[] = $row['username'];
    $nestedData[] = $row['first_name'];
    $nestedData[] = $row['last_name'];
    $nestedData[] = $row['email'];
    $nestedData[] = $row['phone'];
    $nestedData[] = $row['id_number'];
    $nestedData[] = $row['available_balance'];
    $nestedData[] = $row['fund_balance'];
    $nestedData[] = $row['total_profit'];
    $nestedData[] = $row['status'];
    $nestedData[] = $row['registration_date'];
    $nestedData[] = $row['email_verified'];
    
    $data[] = $nestedData;
}

$json_data = array(
    "draw" => intval($request['draw']),
    "recordsTotal" => intval($totalRecords),
    "recordsFiltered" => intval($totalFiltered),
    "data" => $data
);

echo json_encode($json_data);
?>