<?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)
// Use alias pgrp.name for price group name
$columns = [
    'pgrp.name',
    'mis.name',
    'mis.price_initial',
    'mis.price_per_extra_days',
    'mis.extra_days',
    'mis.price_per_extra_months',
    'mis.price_max',
    'mis.active'
];

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

// Base SQL query with join to mailbox_item_size_price_group to get price group name
$sqlBase = "FROM mailbox_item_sizes mis LEFT JOIN mailbox_item_size_price_group pgrp ON mis.price_group = pgrp.item_size_group_key WHERE mis.store_key = ? AND mis.item_type = 1";

// Parameters for prepared statement
$params = [$storeKey];
$paramTypes = "i";

// Add search filter if present (search on mis.name and pgrp.name)
if (!empty($searchValue)) {
    $sqlBase .= " AND (mis.name LIKE ? OR pgrp.name LIKE ?)";
    $paramTypes .= "ss";
    $searchTerm = "%$searchValue%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

// Get total records count (without filtering), include item_type = 1 filter 
$totalRecordsQuery = "SELECT COUNT(*) FROM mailbox_item_sizes WHERE store_key = ? AND item_type = 1";
$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
// Select price group name (pgrp.name) instead of mis.price_group
$dataQuery = "SELECT mis.item_size_key, pgrp.name AS price_group_name, mis.name, mis.price_initial, mis.price_per_extra_days, mis.extra_days, mis.price_per_extra_months, mis.price_max, mis.active $sqlBase ORDER BY $orderColumn $orderDir";

$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
$paramTypesWithLimit = $paramTypes . "ii";
$paramsWithLimit = array_merge($params, [$start, $length]);

// Prepare references for bind_param
$refs = [];
foreach ($params as $key => $value) {
    $refs[$key] = &$params[$key];
}

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

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

$data = [];

while ($row = $result->fetch_assoc()) {
    // Format active status
    $activeText = intval($row['active']) === 1 ? 'Yes' : 'No';

    // Format prices (if you want to format as currency, adjust here)
    $formatPrice = function($price) {
        return number_format($price, 2); // Adjust decimals if needed
    };

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

    $data[] = [
        htmlspecialchars($row['price_group_name']),
        htmlspecialchars($row['name']),
        $formatPrice($row['price_initial']),
        $formatPrice($row['price_per_extra_days']),
        intval($row['extra_days']),
        $formatPrice($row['price_per_extra_months']),
        $formatPrice($row['price_max']),
        $activeText,
        $actionsHtml
    ];
}

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

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