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

require_once("definitions.php");

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

// ======================================================
// STORE VALIDATION
// ======================================================
$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"
    ]);
    exit;
}

// ======================================================
// DATATABLES PARAMETERS
// ======================================================
$draw = intval($_GET['draw'] ?? 0);
$start = intval($_GET['start'] ?? 0);
$length = intval($_GET['length'] ?? 25);

$orderColumnIndex = $_GET['order'][0]['column'] ?? 0;
$orderDir = strtolower($_GET['order'][0]['dir'] ?? 'desc');
$orderDir = ($orderDir === 'asc') ? 'ASC' : 'DESC';

$searchValue = trim($_GET['search']['value'] ?? '');

// ======================================================
// OPTIONAL FILTERS
// ======================================================
$customerKey = isset($_GET['customer_key']) ? intval($_GET['customer_key']) : 0;
$mailboxKey = isset($_GET['mailbox_key']) ? intval($_GET['mailbox_key']) : 0;
$locationKey = isset($_GET['package_locations_key']) ? intval($_GET['package_locations_key']) : 0;

$packageFilterMode = $_GET['package_filter_mode'] ?? 'all';
// ======================================================
// ORDERABLE COLUMNS
// ======================================================
$columns = [
    "p.datein",
    "p.tracking",
    "c.lastname",
    "m.mailboxnumber",
    "pl.location_name",
    "mis.name",
    "p.sender"
];

$orderColumn = $columns[$orderColumnIndex] ?? $columns[0];

// ======================================================
// REDIS
// ======================================================
$redis = null;

try {
    $redis = new Redis();
    $redis->connect('127.0.0.1', 6379);
} catch (Exception $e) {
    $redis = null;
}

// ======================================================
// CACHE HELPERS
// ======================================================
function generateCacheKey($storeKey, $prefix, $params = [])
{
    $key = "store_{$storeKey}_{$prefix}";

    if (!empty($params)) {
        $key .= '_' . md5(json_encode($params));
    }

    return $key;
}

$cacheExpiry = 300;

// ======================================================
// BASE QUERY
// ======================================================
$sqlFrom = "
FROM packages p

LEFT JOIN customers c
    ON p.customer_key = c.customer_key

LEFT JOIN mailboxes m
    ON p.mailbox_key = m.mailbox_key

LEFT JOIN package_locations pl
    ON p.package_locations_key = pl.package_locations_key

LEFT JOIN mailbox_item_sizes mis
    ON p.item_id = mis.item_size_key

LEFT JOIN agent a
    ON p.user_key = a.agent_key
";

// ======================================================
// WHERE
// ======================================================
$whereClauses = [
    "p.store_key = ?"
];

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

// ------------------------------------------------------
// SEARCH
// ------------------------------------------------------
if (!empty($searchValue)) {

    $whereClauses[] = "(
        p.tracking LIKE ?
        OR p.barcode LIKE ?
        OR p.sender LIKE ?
        OR c.firstname LIKE ?
        OR c.lastname LIKE ?
        OR m.mailboxnumber LIKE ?
        OR pl.location_name LIKE ?
    )";

    $searchTerm = '%' . $searchValue . '%';

    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;

    $paramTypes .= "sssssss";
}

// ------------------------------------------------------
// FILTERS
// ------------------------------------------------------
if ($customerKey > 0) {
    $whereClauses[] = "p.customer_key = ?";
    $params[] = $customerKey;
    $paramTypes .= "i";
}

if ($mailboxKey > 0) {
    $whereClauses[] = "p.mailbox_key = ?";
    $params[] = $mailboxKey;
    $paramTypes .= "i";
}

if ($locationKey > 0) {
    $whereClauses[] = "p.package_locations_key = ?";
    $params[] = $locationKey;
    $paramTypes .= "i";
}

// ------------------------------------------------------
// ACTIVE PACKAGES ONLY
// ------------------------------------------------------
switch ($packageFilterMode) {

    case 'active':

        $whereClauses[] = "p.dateout IS NULL";

        break;

    case 'aging':

        $whereClauses[] = "
            p.dateout IS NULL
            AND p.datein <= NOW() - INTERVAL 60 DAY
        ";

        break;

    case 'all':
    default:
        // no additional filtering
        break;
}

$whereSql = "WHERE " . implode(" AND ", $whereClauses);

// ======================================================
// 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" => $draw,
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
        "data" => [],
        "error" => "Database connection failed"
    ]);

    exit;
}

// ======================================================
// TOTAL RECORDS
// ======================================================
$totalRecords = 0;

$totalCacheKey = generateCacheKey(
    $storeKey,
    'packages_total'
);

if ($redis && ($cached = $redis->get($totalCacheKey)) !== false) {

    $recordsTotal = intval($cached);

} else {

    $totalQuery = "
        SELECT COUNT(*)
        FROM packages p
        WHERE p.store_key = ?
    ";

    $stmt = $conn->prepare($totalQuery);
    $stmt->bind_param("i", $storeKey);
    $stmt->execute();
    $stmt->bind_result($recordsTotal);
    $stmt->fetch();
    $stmt->close();

    if ($redis) {
        $redis->setex($totalCacheKey, $cacheExpiry, $recordsTotal);
    }
}

// ======================================================
// FILTERED COUNT
// ======================================================
$filteredCacheKey = generateCacheKey(
    $storeKey,
    'packages_filtered',
    [
        'search' => $searchValue,
        'customer_key' => $customerKey,
        'mailbox_key' => $mailboxKey,
        'location_key' => $locationKey,
        'package_filter_mode' => $packageFilterMode    ]
);

if ($redis && ($cached = $redis->get($filteredCacheKey)) !== false) {

    $recordsFiltered = intval($cached);

} else {

    $countQuery = "
        SELECT COUNT(*)
        $sqlFrom
        $whereSql
    ";

    $stmt = $conn->prepare($countQuery);

    if (!$stmt) {

        echo json_encode([
            "draw" => $draw,
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => 0,
            "data" => [],
            "error" => "Failed preparing filtered count query"
        ]);

        exit;
    }

    $stmt->bind_param($paramTypes, ...$params);

    $stmt->execute();
    $stmt->bind_result($recordsFiltered);
    $stmt->fetch();
    $stmt->close();

    if ($redis) {
        $redis->setex($filteredCacheKey, $cacheExpiry, $recordsFiltered);
    }
}

// ======================================================
// DATA QUERY
// ======================================================
$dataCacheKey = generateCacheKey(
    $storeKey,
    'packages_data',
    [
        'search' => $searchValue,
        'start' => $start,
        'length' => $length,
        'orderColumn' => $orderColumn,
        'orderDir' => $orderDir,
        'customer_key' => $customerKey,
        'mailbox_key' => $mailboxKey,
        'location_key' => $locationKey,
        'package_filter_mode' => $packageFilterMode
    ]
);

$data = [];

if ($redis && ($cachedData = $redis->get($dataCacheKey)) !== false) {

    $data = json_decode($cachedData, true);

} else {

    $dataQuery = "
        SELECT
            p.package_key,
            p.tracking,
            p.sender,
            p.datein,
            p.dateout,
            p.barcode,

            c.firstname,
            c.lastname,

            m.mailboxnumber,

            pl.location_name,

            mis.name AS item_size,

            a.firstname AS received_firstname,
            a.lastname AS received_lastname

        $sqlFrom

        $whereSql

        ORDER BY $orderColumn $orderDir

        LIMIT ?, ?
    ";

    $stmt = $conn->prepare($dataQuery);

    if (!$stmt) {

        echo json_encode([
            "draw" => $draw,
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsFiltered,
            "data" => [],
            "error" => "Failed preparing data query"
        ]);

        exit;
    }

    $paramTypesWithLimit = $paramTypes . "ii";

    $paramsWithLimit = array_merge(
        $params,
        [$start, $length]
    );

    $refs = [];

    foreach ($paramsWithLimit as $key => $value) {
        $refs[$key] = &$paramsWithLimit[$key];
    }

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

    $stmt->execute();

    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {

        $customerName = '';

        if (!empty($row['firstname']) || !empty($row['lastname'])) {
            $customerName =
                htmlspecialchars($row['firstname']) . ' ' .
                htmlspecialchars($row['lastname']);
        }

        $receivedBy = trim(
            ($row['received_firstname'] ?? '') . ' ' .
            ($row['received_lastname'] ?? '')
        );

        $data[] = [

            htmlspecialchars($row['datein']),

            htmlspecialchars($row['tracking']),

            htmlspecialchars($customerName),

            htmlspecialchars($row['mailboxnumber'] ?? ''),

            htmlspecialchars($row['location_name'] ?? ''),

            htmlspecialchars($row['item_size'] ?? ''),

            htmlspecialchars($row['sender']),

            htmlspecialchars($receivedBy),

            intval($row['package_key'])
        ];
    }

    $stmt->close();

    if ($redis) {
        $redis->setex(
            $dataCacheKey,
            $cacheExpiry,
            json_encode($data)
        );
    }
}

// ======================================================
// CLOSE CONNECTION
// ======================================================
$conn->close();

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