<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
ini_set('log_errors', 1);
ini_set('error_log', __DIR__ . '/php-error.log');
$startTime = microtime(true);

require_once("definitions.php");

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

// Get store_key from session
$storeKey = isset($_SESSION['storekey']) ? intval($_SESSION['storekey']) : 0;
if ($storeKey === 0) {
    echo json_encode([]);
    exit;
}

// Check if cache refresh requested (GET or POST)
$refreshCache = (isset($_GET['refresh_cache']) && $_GET['refresh_cache'] == '1') 
             || (isset($_POST['refresh_cache']) && $_POST['refresh_cache'] == '1');

// Connect Redis
$redis = null;
try {
    $redis = new Redis();
    $redis->connect('127.0.0.1', 6379);
} catch (Exception $e) {
    // Redis connection failed, disable caching
    $redis = null;
}

// Helper function to generate cache key for this query
function generateCacheKey($storeKey, $prefix, $params = []) {
    $key = "store_{$storeKey}_{$prefix}";
    if (!empty($params)) {
        $key .= '_' . md5(json_encode($params));
    }
    return $key;
}

// Cache expiry in seconds (e.g., 1 hour)
$cacheExpiry = 3600;

// Generate cache key for this request
// Include storeKey and refreshCache param in key parameters if needed
$cacheKeyParams = [
    'refreshCache' => $refreshCache,
    // You can add more parameters here if the query depends on them (e.g., search, pagination)
];
$cacheKey = generateCacheKey($storeKey, 'customer_mailboxes', $cacheKeyParams);

// Try to get cached output if not refreshing cache
if (!$refreshCache && $redis && ($cachedJson = $redis->get($cacheKey)) !== false) {
    echo $cachedJson;
    exit;
}

// No cached data or refresh requested, proceed with DB query

// Connect to DB
$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([]);
    exit;
}

// Prepared SQL statement
$sql = "SELECT 
            c.customer_key, 
            IF(c.business != 0 AND linked_c.firstname IS NOT NULL, linked_c.firstname, c.firstname) AS firstname,
            IF(c.business != 0 AND linked_c.lastname IS NOT NULL, linked_c.lastname, c.lastname) AS lastname,
            c.alias,
            c.business,
            IF(COALESCE(m.mailbox_name, '') != '', m.mailbox_name, CAST(m.mailboxnumber AS CHAR)) AS mailbox_name,
            ce_min.email
            FROM customers c
            LEFT JOIN customers linked_c ON linked_c.customer_key = c.linked_key
            LEFT JOIN customer_mailboxes cm 
            ON cm.customer_key = c.customer_key 
            AND cm.date_open <= NOW()
            AND (cm.date_closed IS NULL OR cm.date_closed = '0000-00-00 00:00:00')
            LEFT JOIN mailboxes m 
            ON m.mailbox_key = cm.mailbox_key
            LEFT JOIN (
            SELECT customer_key, email
            FROM customer_email ce1
            WHERE customer_email_key = (
                SELECT MIN(customer_email_key)
                FROM customer_email ce2
                WHERE ce2.customer_key = ce1.customer_key
            )
            ) AS ce_min ON ce_min.customer_key = c.customer_key
            WHERE m.store_key = ?
            GROUP BY c.customer_key, mailbox_name, firstname, lastname, c.alias, ce_min.email, c.business
            ORDER BY 
            COALESCE(NULLIF(c.lastname, ''), c.alias),
            mailbox_name,
            COALESCE(NULLIF(c.firstname, ''), c.alias);";

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

if (!$stmt) {
    echo json_encode([]);
    exit;
}

$stmt->bind_param("i", $storeKey);
$stmt->execute();

$result = $stmt->get_result();

if ($result === false) {
    echo json_encode(['error' => $stmt->error]);
    exit;
}

$output = [];

while ($row = $result->fetch_assoc()) {
    // Filter out empty firstname/lastname to avoid extra spaces
    $nameParts = array_filter([$row['firstname'], $row['lastname']], fn($v) => strlen(trim($v)) > 0);
    $fullName = implode(' ', array_map('htmlspecialchars', $nameParts));

    // Business or alias inside parentheses
    if ($row['business'] == 2) {
        $parentheses = $fullName;    
        $fullName = htmlspecialchars($row['alias']);
        $fullName .= " ({$parentheses})";
    } elseif (!empty($row['alias'])) {
        $parentheses = htmlspecialchars($row['alias']);
        $fullName .= " ({$parentheses})";
    }

    $email = htmlspecialchars($row['email'] ?? '');

    $mailboxDisplay = '';
    if (!empty($row['mailbox_name'])) {
        $mailboxDisplay = htmlspecialchars($row['mailbox_name']);
    }

    $label = "{$fullName}, {$email} #{$mailboxDisplay}";

    $output[] = [
        "label" => $label,
        "value" => $label,
        "id" => intval($row['customer_key']),
        "email" => $email,
        "mailboxnumber" => $mailboxDisplay,
        "business" => $row['business'],
        "alias" => $row['alias'],
    ];
}

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

$jsonOutput = json_encode($output);

// Save result to Redis cache if available
if ($redis) {
    $redis->setex($cacheKey, $cacheExpiry, $jsonOutput);
}

$endTime = microtime(true);
$executionTime = $endTime - $startTime;
error_log("_get_autocomplete_customers.php Script execution time: {$executionTime} seconds");

// Output JSON
echo $jsonOutput;

?>