<?php
/**
 * AJAX API for cascading location dropdowns
 * Returns distinct Stores, Racks, Levels, Bins from items table
 */
require_once __DIR__ . '/../includes/init.php';

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

// Manual auth check returning JSON instead of HTML/redirect
if (!SessionManager::isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Not authenticated']);
    exit;
}
$user = current_user();
if (!$user || $user['role'] !== 'admin') {
    http_response_code(403);
    echo json_encode(['error' => 'Access denied']);
    exit;
}

$db = Database::getInstance();
$action = $_GET['action'] ?? '';

try {
    if ($action === 'stores') {
        $rows = $db->fetchAll("SELECT DISTINCT `Stores` as value FROM items WHERE `Stores` IS NOT NULL AND `Stores` != '' AND `Status` = 'Active' ORDER BY `Stores`");
        echo json_encode(array_column($rows, 'value'));
    } elseif ($action === 'racks') {
        $store = $_GET['store'] ?? '';
        if (!$store) { echo json_encode([]); exit; }
        $where = "`Stores` = ? AND `Status` = 'Active' AND `Rack` IS NOT NULL AND `Rack` != ''";
        $rows = $db->fetchAll("SELECT DISTINCT `Rack` as value FROM items WHERE $where ORDER BY `Rack`", [$store]);
        echo json_encode(array_column($rows, 'value'));
    } elseif ($action === 'levels') {
        $store = $_GET['store'] ?? '';
        $rack = $_GET['rack'] ?? '';
        if (!$store || !$rack) { echo json_encode([]); exit; }
        $where = "`Stores` = ? AND `Rack` = ? AND `Status` = 'Active' AND `Level` IS NOT NULL AND `Level` != ''";
        $rows = $db->fetchAll("SELECT DISTINCT `Level` as value FROM items WHERE $where ORDER BY `Level`", [$store, $rack]);
        echo json_encode(array_column($rows, 'value'));
    } elseif ($action === 'bins') {
        $store = $_GET['store'] ?? '';
        $rack = $_GET['rack'] ?? '';
        $level = $_GET['level'] ?? '';
        if (!$store || !$rack || !$level) { echo json_encode([]); exit; }
        $where = "`Stores` = ? AND `Rack` = ? AND `Level` = ? AND `Status` = 'Active' AND `Bin` IS NOT NULL AND `Bin` != ''";
        $rows = $db->fetchAll("SELECT DISTINCT `Bin` as value FROM items WHERE $where ORDER BY `Bin`", [$store, $rack, $level]);
        echo json_encode(array_column($rows, 'value'));
    } elseif ($action === 'count') {
        $conditions = ["`Status` = 'Active'"];
        $params = [];
        if (!empty($_GET['store'])) { $conditions[] = "`Stores` = ?"; $params[] = $_GET['store']; }
        if (!empty($_GET['rack'])) { $conditions[] = "`Rack` = ?"; $params[] = $_GET['rack']; }
        if (!empty($_GET['level'])) { $conditions[] = "`Level` = ?"; $params[] = $_GET['level']; }
        if (!empty($_GET['bin'])) { $conditions[] = "`Bin` = ?"; $params[] = $_GET['bin']; }
        $where = implode(' AND ', $conditions);
        $count = $db->fetchColumn("SELECT COUNT(*) FROM items WHERE $where", $params);
        echo json_encode(['count' => $count]);
    } else {
        http_response_code(400);
        echo json_encode(['error' => 'Invalid action']);
    }
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Server error: ' . $e->getMessage()]);
}
