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

require_once("definitions.php");

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

$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;
}

$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;
}

$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';

// Columns for ordering - map to table columns (name, order, active)
$columns = [
    'name',
    '`order`',
    'active',
];

$orderColumn = $columns[$orderColumnIndex] ?? $columns[0];
$orderDir = (strtolower($orderDir) === 'desc') ? 'DESC' : 'ASC';

// Base query
$sqlBase = "FROM mailbox_item_size_price_group WHERE store_key = ?";

$params = [$storeKey];
$paramTypes = "i";

if (!empty($searchValue)) {
    $sqlBase .= " AND (name LIKE ?)";
    $paramTypes .= "s";
    $searchTerm = "%$searchValue%";
    $params[] = $searchTerm;
}

// Get total records (without filtering)
$totalRecordsQuery = "SELECT COUNT(*) FROM mailbox_item_size_price_group 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(*) $sqlBase";
    $countStmt = $conn->prepare($countQuery);
    if ($countStmt) {
        $countStmt->bind_param($paramTypes, ...$params);
        $countStmt->execute();
        $countStmt->bind_result($filteredRecords);
        $countStmt->fetch();
        $countStmt->close();
    }
}

// Fetch data with limit and order
$dataQuery = "SELECT item_size_group_key, name, `order`, active $sqlBase 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;
}

$paramTypesWithLimit = $paramTypes . "ii";
$paramsWithLimit = array_merge($params, [$start, $length]);

$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()) {
    $activeText = intval($row['active']) === 1 ? 'Yes' : 'No';

    $actionsHtml = 
    
    '
        <a href="javascript:void(0);" class="text-info fs-14 lh-1 editPackageGroupBtn" data-package-group-key="' . intval($row['item_size_group_key']) . '" title="Edit">
            <i class="ri-edit-line"></i>
        </a>
    ';

    $data[] = [
        htmlspecialchars($row['name']),
        intval($row['order']),
        $activeText,
        $actionsHtml
    ];
}

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

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => $recordsTotal,
    "recordsFiltered" => $filteredRecords,
    "data" => $data
]);