<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once("definitions.php");

// Set JSON response header
header('Content-Type: application/json');

// Create DB connection
$conn = new mysqli(PACKAGE_LOG_DB_SERVER, PACKAGE_LOG_DB_USER, PACKAGE_LOG_DB_PASS, PACKAGE_LOG_DB_NAME);
if ($conn->connect_error) {
    echo json_encode([
        "draw" => intval($_GET['draw'] ?? 0),
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
        "data" => [],
        "error" => "Database connection failed"
    ]);
    exit;
}

// Get store key from session
$storeKey = isset($_SESSION['storekey']) ? intval($_SESSION['storekey']) : 0;

if ($storeKey === 0) {
    echo json_encode([
        "draw" => intval($_GET['draw'] ?? 0),
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
        "data" => [],
        "error" => "Invalid store key"
    ]);
    $conn->close();
    exit;
}

// DataTables parameters
$draw = intval($_GET['draw'] ?? 0);
$start = intval($_GET['start'] ?? 0);
$length = intval($_GET['length'] ?? 10);
$searchValue = $_GET['search']['value'] ?? '';
$orderColumnIndex = $_GET['order'][0]['column'] ?? 0;
$orderDir = $_GET['order'][0]['dir'] ?? 'asc';

// Define columns for ordering (must match the columns in the output)
$columns = ['mb.mailboxnumber', 'ma.name', '', '']; // 3rd and 4th columns no ordering (Occupants, Actions)

// Validate order column and direction
$orderColumn = $columns[$orderColumnIndex] ?? $columns[0];
$orderDir = ($orderDir === 'desc') ? 'DESC' : 'ASC';

// Prepare base SQL with join
$sql = "FROM mailboxes mb
        LEFT JOIN mailbox_attributes ma ON mb.mailbox_attributes_key = ma.mailbox_attributes_key AND ma.store_key = mb.store_key
        WHERE mb.store_key = ?";

// Add search filter if present
$params = [];
$paramTypes = "i";
$params[] = $storeKey;

if (!empty($searchValue)) {
    $sql .= " AND (mb.mailboxnumber LIKE ? OR ma.name LIKE ?)";
    $paramTypes .= "ss";
    $searchTerm = "%$searchValue%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

// Get total records count (without filtering)
$totalRecordsQuery = "SELECT COUNT(*) FROM mailboxes WHERE store_key = ?";
$totalStmt = $conn->prepare($totalRecordsQuery);
$totalStmt->bind_param("i", $storeKey);
$totalStmt->execute();
$totalStmt->bind_result($recordsTotal);
$totalStmt->fetch();
$totalStmt->close();

// Get filtered records count
$filteredRecords = $recordsTotal;
if (!empty($searchValue)) {
    $countQuery = "SELECT COUNT(*) $sql";
    $countStmt = $conn->prepare($countQuery);
    if ($countStmt) {
        // Bind parameters dynamically
        $countStmt->bind_param($paramTypes, ...$params);
        $countStmt->execute();
        $countStmt->bind_result($filteredRecords);
        $countStmt->fetch();
        $countStmt->close();
    }
}

// Fetch data with limit and order
$dataQuery = "SELECT mb.mailbox_key, mb.mailboxnumber, mb.mailbox_name, ma.name AS attribute_name $sql ORDER BY $orderColumn $orderDir LIMIT ?, ?";
$dataStmt = $conn->prepare($dataQuery);
if (!$dataStmt) {
    echo json_encode([
        "draw" => $draw,
        "recordsTotal" => $recordsTotal,
        "recordsFiltered" => $filteredRecords,
        "data" => [],
        "error" => "Prepare statement failed: " . $conn->error
    ]);
    $conn->close();
    exit;
}

// Bind parameters with limit and offset
// Add two integers for LIMIT ?, ?
$paramTypesWithLimit = $paramTypes . "ii";
$paramsWithLimit = array_merge($params, [$start, $length]);

// Use call_user_func_array to bind params dynamically
// Prepare references for bind_param
$refs = [];
foreach ($paramsWithLimit as $key => $value) {
    $refs[$key] = &$paramsWithLimit[$key];
}

call_user_func_array([$dataStmt, 'bind_param'], array_merge([$paramTypesWithLimit], $refs));

$dataStmt->execute();
$result = $dataStmt->get_result();

$data = [];
while ($row = $result->fetch_assoc()) {
    // Build Occupants HTML placeholder (same as original)
    $occupantsHtml = '
        <div class="avatar-list-stacked">
            <a class="avatar avatar-sm bg-info text-fixed-white avatar-rounded" href="javascript:void(0);">
                +0
            </a>
        </div>
    ';

    // Actions HTML
    $actionsHtml = '
        <a href="javascript:void(0);" class="text-info fs-14 lh-1 editMailboxBtn" data-mailbox-key="' . intval($row['mailbox_key']) . '">
            <i class="ri-edit-line"></i>
        </a>
        <a href="javascript:void(0);" class="text-danger fs-14 lh-1 deleteMailboxBtn" data-mailbox-key="' . intval($row['mailbox_key']) . '">
            <i class="ri-delete-bin-5-line"></i>
        </a>
    ';

    $data[] = [
        htmlspecialchars($row['mailboxnumber']),
        '<button class="btn btn-sm btn-primary-light">' . htmlspecialchars($row['attribute_name'] ?? 'N/A') . '</button>',
        $occupantsHtml,
        $actionsHtml
    ];
}

$dataStmt->close();
$conn->close();

// Output JSON for DataTables
echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $recordsTotal,
    "recordsFiltered" => $filteredRecords,
    "data" => $data
]);