<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Stock Valuation Report';

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2); }
}

$errors = [];

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS store_locations (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        location_code VARCHAR(50) NULL,
        location_name VARCHAR(150) NOT NULL,
        location_type ENUM('main_store','bar','restaurant','curio','kitchen','housekeeping','maintenance','other') NOT NULL DEFAULT 'main_store',
        is_pos_location TINYINT(1) NOT NULL DEFAULT 0,
        allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        notes TEXT NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_store_location_code (location_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_item_categories (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        category_name VARCHAR(120) NOT NULL,
        category_type ENUM('stock','food','bar','curio','consumable','service','other') NOT NULL DEFAULT 'stock',
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_category_name (category_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_units (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        unit_code VARCHAR(30) NOT NULL,
        unit_name VARCHAR(80) NOT NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_unit_code (unit_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS inventory_items (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        item_code VARCHAR(60) NULL,
        item_name VARCHAR(180) NOT NULL,
        category_id INT UNSIGNED NULL,
        unit_id INT UNSIGNED NULL,
        item_type ENUM('stock','food','bar','curio','consumable','service','recipe','other') NOT NULL DEFAULT 'stock',
        barcode VARCHAR(100) NULL,
        description TEXT NULL,
        cost_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        avg_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        selling_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        tax_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
        reorder_level DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        reorder_qty DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        is_stock_item TINYINT(1) NOT NULL DEFAULT 1,
        is_pos_item TINYINT(1) NOT NULL DEFAULT 0,
        allow_room_charge TINYINT(1) NOT NULL DEFAULT 1,
        track_expiry TINYINT(1) NOT NULL DEFAULT 0,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_inventory_item_code (item_code),
        INDEX idx_inventory_item_name (item_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS stock_movements (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        item_id INT UNSIGNED NOT NULL,
        store_id INT UNSIGNED NOT NULL,
        movement_type ENUM('OPENING','GRN','ISSUE','SALE','TRANSFER_IN','TRANSFER_OUT','ADJUSTMENT_IN','ADJUSTMENT_OUT','WASTAGE','RETURN') NOT NULL,
        qty_in DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        qty_out DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        total_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
        reference_type VARCHAR(60) NULL,
        reference_id BIGINT UNSIGNED NULL,
        notes TEXT NULL,
        movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_stock_item_store (item_id, store_id),
        INDEX idx_stock_movement_date (movement_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
} catch (Throwable $e) {
    $errors[] = 'Could not prepare report tables: ' . $e->getMessage();
}

$stores = [];
$categories = [];
try {
    $stores = $pdo->query("SELECT * FROM store_locations WHERE is_active=1 ORDER BY location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
    $categories = $pdo->query("SELECT * FROM inventory_item_categories WHERE is_active=1 ORDER BY category_name ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $errors[] = 'Could not load filters: ' . $e->getMessage();
}

$asAt = trim((string)($_GET['as_at'] ?? date('Y-m-d')));
$storeId = (int)($_GET['store_id'] ?? 0);
$categoryId = (int)($_GET['category_id'] ?? 0);
$status = trim((string)($_GET['status'] ?? ''));
$showZero = (int)($_GET['show_zero'] ?? 0);
$search = trim((string)($_GET['q'] ?? ''));

if (strtotime($asAt) === false) $asAt = date('Y-m-d');
$asAtDateTime = date('Y-m-d 23:59:59', strtotime($asAt));

$where = "ii.is_stock_item = 1";
$params = [
    ':as_at_qty' => $asAtDateTime,
    ':as_at_value' => $asAtDateTime,
];

if ($storeId > 0) {
    $where .= " AND sl.id = :store_id";
    $params[':store_id'] = $storeId;
}
if ($categoryId > 0) {
    $where .= " AND ii.category_id = :category_id";
    $params[':category_id'] = $categoryId;
}
if ($status !== '') {
    $where .= " AND ii.is_active = :is_active";
    $params[':is_active'] = (int)$status;
}
if ($search !== '') {
    $where .= " AND (
        ii.item_name LIKE :q_item_name
        OR ii.item_code LIKE :q_item_code
        OR ii.barcode LIKE :q_barcode
        OR c.category_name LIKE :q_category
        OR sl.location_name LIKE :q_store
    )";
    $searchLike = '%' . $search . '%';
    $params[':q_item_name'] = $searchLike;
    $params[':q_item_code'] = $searchLike;
    $params[':q_barcode'] = $searchLike;
    $params[':q_category'] = $searchLike;
    $params[':q_store'] = $searchLike;
}

$having = $showZero === 1 ? "" : "HAVING ABS(balance_qty) > 0.0001";

$rows = [];
$summary = [
    'items' => 0,
    'stores' => 0,
    'qty' => 0,
    'value' => 0,
    'negative' => 0,
    'low_stock' => 0,
];

try {
    $stmt = $pdo->prepare("
        SELECT
            ii.id AS item_id,
            ii.item_code,
            ii.item_name,
            ii.barcode,
            ii.item_type,
            ii.avg_cost,
            ii.cost_price,
            ii.reorder_level,
            ii.reorder_qty,
            ii.is_active,
            c.category_name,
            c.category_type,
            u.unit_code,
            sl.id AS store_id,
            sl.location_name,
            sl.location_type,
            COALESCE(SUM(CASE WHEN sm.movement_date <= :as_at_qty THEN sm.qty_in - sm.qty_out ELSE 0 END),0) AS balance_qty,
            COALESCE(SUM(CASE WHEN sm.movement_date <= :as_at_value THEN (sm.qty_in - sm.qty_out) * sm.unit_cost ELSE 0 END),0) AS movement_value
        FROM inventory_items ii
        CROSS JOIN store_locations sl
        LEFT JOIN inventory_item_categories c ON c.id = ii.category_id
        LEFT JOIN inventory_units u ON u.id = ii.unit_id
        LEFT JOIN stock_movements sm ON sm.item_id = ii.id AND sm.store_id = sl.id
        WHERE $where
          AND sl.is_active = 1
        GROUP BY ii.id, sl.id
        $having
        ORDER BY c.category_name ASC, ii.item_name ASC, sl.location_name ASC
        LIMIT 2000
    ");
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $itemSeen = [];
    $storeSeen = [];

    foreach ($rows as $row) {
        $qty = (float)$row['balance_qty'];
        $avgCost = (float)($row['avg_cost'] ?: $row['cost_price']);
        $value = $qty * $avgCost;

        $summary['qty'] += $qty;
        $summary['value'] += $value;

        $itemSeen[(int)$row['item_id']] = true;
        $storeSeen[(int)$row['store_id']] = true;

        if ($qty < 0) $summary['negative']++;
        if ((float)$row['reorder_level'] > 0 && $qty <= (float)$row['reorder_level']) $summary['low_stock']++;
    }

    $summary['items'] = count($itemSeen);
    $summary['stores'] = count($storeSeen);
} catch (Throwable $e) {
    $errors[] = 'Could not load stock valuation: ' . $e->getMessage();
}

$categoryTotals = [];
$storeTotals = [];

foreach ($rows as $row) {
    $cat = $row['category_name'] ?: 'Uncategorised';
    $store = $row['location_name'] ?: 'Store';
    $qty = (float)$row['balance_qty'];
    $avgCost = (float)($row['avg_cost'] ?: $row['cost_price']);
    $value = $qty * $avgCost;

    if (!isset($categoryTotals[$cat])) $categoryTotals[$cat] = ['qty' => 0, 'value' => 0, 'items' => []];
    $categoryTotals[$cat]['qty'] += $qty;
    $categoryTotals[$cat]['value'] += $value;
    $categoryTotals[$cat]['items'][(int)$row['item_id']] = true;

    if (!isset($storeTotals[$store])) $storeTotals[$store] = ['qty' => 0, 'value' => 0, 'items' => []];
    $storeTotals[$store]['qty'] += $qty;
    $storeTotals[$store]['value'] += $value;
    $storeTotals[$store]['items'][(int)$row['item_id']] = true;
}


/*
 * Excel export.
 *
 * Uses the same already-loaded $rows, $summary, $categoryTotals and $storeTotals,
 * so every active report filter is preserved exactly.
 */
if (($_GET['export'] ?? '') === 'excel') {
    $safeDate = preg_replace('/[^0-9-]/', '', $asAt);
    $filename = 'stock_valuation_' . ($safeDate !== '' ? $safeDate : date('Y-m-d')) . '_' . date('His') . '.xls';

    header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo "\xEF\xBB\xBF";
    ?>
    <!doctype html>
    <html>
    <head>
        <meta charset="UTF-8">
        <style>
            body{font-family:Arial,sans-serif;font-size:11px;color:#111827}
            h1{font-size:18px;margin:0 0 4px}
            h2{font-size:14px;margin:18px 0 6px}
            .meta{margin-bottom:12px;color:#475569}
            table{border-collapse:collapse;width:100%;margin-bottom:14px}
            th,td{border:1px solid #cbd5e1;padding:5px 7px}
            th{background:#e2e8f0;font-weight:bold;text-align:left}
            .number{text-align:right;mso-number-format:"0.0000"}
            .money{text-align:right;mso-number-format:"0.00"}
            .total td,.total th{background:#0f172a;color:#fff;font-weight:bold}
            .negative{background:#fee2e2}
            .low{background:#fef3c7}
            .ok{background:#dcfce7}
        </style>
    </head>
    <body>
        <h1>Stock Valuation Report</h1>
        <div class="meta">
            As at <?php echo h(date('d M Y', strtotime($asAt))); ?>
            <?php if ($storeId > 0): ?>
                | Store filter:
                <?php
                $selectedStoreName = '';
                foreach ($stores as $store) {
                    if ((int)$store['id'] === $storeId) {
                        $selectedStoreName = (string)$store['location_name'];
                        break;
                    }
                }
                echo h($selectedStoreName !== '' ? $selectedStoreName : ('Store #' . $storeId));
                ?>
            <?php endif; ?>
            <?php if ($categoryId > 0): ?>
                | Category filter:
                <?php
                $selectedCategoryName = '';
                foreach ($categories as $category) {
                    if ((int)$category['id'] === $categoryId) {
                        $selectedCategoryName = (string)$category['category_name'];
                        break;
                    }
                }
                echo h($selectedCategoryName !== '' ? $selectedCategoryName : ('Category #' . $categoryId));
                ?>
            <?php endif; ?>
            <?php if ($status !== ''): ?>
                | Status: <?php echo $status === '1' ? 'Active' : 'Inactive'; ?>
            <?php endif; ?>
            <?php if ($search !== ''): ?>
                | Search: <?php echo h($search); ?>
            <?php endif; ?>
            | Zero balances: <?php echo $showZero === 1 ? 'Included' : 'Excluded'; ?>
        </div>

        <table>
            <tr>
                <th>Distinct Items</th>
                <th>Stores</th>
                <th>Total Qty</th>
                <th>Total Stock Value</th>
                <th>Negative Lines</th>
                <th>Low Stock Lines</th>
            </tr>
            <tr>
                <td class="number"><?php echo (int)$summary['items']; ?></td>
                <td class="number"><?php echo (int)$summary['stores']; ?></td>
                <td class="number"><?php echo number_format((float)$summary['qty'], 4, '.', ''); ?></td>
                <td class="money"><?php echo number_format((float)$summary['value'], 2, '.', ''); ?></td>
                <td class="number"><?php echo (int)$summary['negative']; ?></td>
                <td class="number"><?php echo (int)$summary['low_stock']; ?></td>
            </tr>
        </table>

        <h2>Stock Valuation Detail</h2>
        <table>
            <thead>
            <tr>
                <th>Category</th>
                <th>Item ID</th>
                <th>Item Code</th>
                <th>Item Name</th>
                <th>Barcode</th>
                <th>Store</th>
                <th>Store Type</th>
                <th>Qty On Hand</th>
                <th>Unit</th>
                <th>Average Cost</th>
                <th>Stock Value</th>
                <th>Reorder Level</th>
                <th>Reorder Qty</th>
                <th>Status</th>
                <th>Item Active</th>
            </tr>
            </thead>
            <tbody>
            <?php if (!$rows): ?>
                <tr><td colspan="15">No stock valuation rows found.</td></tr>
            <?php else: ?>
                <?php foreach ($rows as $row): ?>
                    <?php
                    $qty = (float)$row['balance_qty'];
                    $avgCost = (float)($row['avg_cost'] ?: $row['cost_price']);
                    $value = $qty * $avgCost;
                    $isNegative = $qty < 0;
                    $isLow = (float)$row['reorder_level'] > 0 && $qty <= (float)$row['reorder_level'];
                    $statusLabel = $isNegative ? 'Negative' : ($isLow ? 'Low' : 'OK');
                    $statusClass = $isNegative ? 'negative' : ($isLow ? 'low' : 'ok');
                    ?>
                    <tr class="<?php echo $statusClass; ?>">
                        <td><?php echo h($row['category_name'] ?: 'Uncategorised'); ?></td>
                        <td class="number"><?php echo (int)$row['item_id']; ?></td>
                        <td><?php echo h($row['item_code'] ?: ''); ?></td>
                        <td><?php echo h($row['item_name']); ?></td>
                        <td><?php echo h($row['barcode'] ?: ''); ?></td>
                        <td><?php echo h($row['location_name']); ?></td>
                        <td><?php echo h($row['location_type']); ?></td>
                        <td class="number"><?php echo number_format($qty, 4, '.', ''); ?></td>
                        <td><?php echo h($row['unit_code'] ?: ''); ?></td>
                        <td class="money"><?php echo number_format($avgCost, 4, '.', ''); ?></td>
                        <td class="money"><?php echo number_format($value, 2, '.', ''); ?></td>
                        <td class="number"><?php echo number_format((float)$row['reorder_level'], 4, '.', ''); ?></td>
                        <td class="number"><?php echo number_format((float)$row['reorder_qty'], 4, '.', ''); ?></td>
                        <td><?php echo h($statusLabel); ?></td>
                        <td><?php echo (int)$row['is_active'] === 1 ? 'Active' : 'Inactive'; ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
            <tfoot>
            <tr class="total">
                <th colspan="7">Totals</th>
                <th class="number"><?php echo number_format((float)$summary['qty'], 4, '.', ''); ?></th>
                <th></th>
                <th></th>
                <th class="money"><?php echo number_format((float)$summary['value'], 2, '.', ''); ?></th>
                <th colspan="4"></th>
            </tr>
            </tfoot>
        </table>

        <h2>Category Totals</h2>
        <table>
            <thead><tr><th>Category</th><th>Items</th><th>Qty</th><th>Value</th></tr></thead>
            <tbody>
            <?php if (!$categoryTotals): ?>
                <tr><td colspan="4">No category totals.</td></tr>
            <?php else: ?>
                <?php foreach ($categoryTotals as $catName => $tot): ?>
                    <tr>
                        <td><?php echo h($catName); ?></td>
                        <td class="number"><?php echo count($tot['items']); ?></td>
                        <td class="number"><?php echo number_format((float)$tot['qty'], 4, '.', ''); ?></td>
                        <td class="money"><?php echo number_format((float)$tot['value'], 2, '.', ''); ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
        </table>

        <h2>Store Totals</h2>
        <table>
            <thead><tr><th>Store</th><th>Items</th><th>Qty</th><th>Value</th></tr></thead>
            <tbody>
            <?php if (!$storeTotals): ?>
                <tr><td colspan="4">No store totals.</td></tr>
            <?php else: ?>
                <?php foreach ($storeTotals as $storeName => $tot): ?>
                    <tr>
                        <td><?php echo h($storeName); ?></td>
                        <td class="number"><?php echo count($tot['items']); ?></td>
                        <td class="number"><?php echo number_format((float)$tot['qty'], 4, '.', ''); ?></td>
                        <td class="money"><?php echo number_format((float)$tot['value'], 2, '.', ''); ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
        </table>
    </body>
    </html>
    <?php
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band no-print">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-clipboard-data"></i> Inventory Reports</div>
            <div class="hero-title mt-2">Stock Valuation Report</div>
            <div class="hero-subtitle mt-2">
                Stock on hand by item, store and category using average cost valuation.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <?php
            $excelParams = $_GET;
            $excelParams['export'] = 'excel';
            $excelUrl = 'stock_valuation_report.php?' . http_build_query($excelParams);
            ?>
            <button onclick="window.print()" class="btn btn-light me-2">
                <i class="bi bi-printer me-2"></i>Print
            </button>
            <a href="<?php echo h($excelUrl); ?>" class="btn btn-success me-2">
                <i class="bi bi-file-earmark-spreadsheet me-2"></i>Export Excel
            </a>
            <a href="inventory_dashboard.php" class="btn btn-outline-light">
                <i class="bi bi-speedometer2 me-2"></i>Dashboard
            </a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger no-print">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0"><?php foreach ($errors as $err): ?><li><?php echo h($err); ?></li><?php endforeach; ?></ul>
    </div>
<?php endif; ?>

<div class="print-title">
    <h3 class="mb-1">Stock Valuation Report</h3>
    <div class="text-muted">As at <?php echo h(date('d M Y', strtotime($asAt))); ?></div>
</div>

<div class="card panel-card mb-4 no-print">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2">
                <label class="form-label">As At</label>
                <input type="date" name="as_at" class="form-control" value="<?php echo h($asAt); ?>">
            </div>
            <div class="col-md-2">
                <label class="form-label">Store</label>
                <select name="store_id" class="form-select">
                    <option value="0">All stores</option>
                    <?php foreach ($stores as $store): ?>
                        <option value="<?php echo (int)$store['id']; ?>" <?php echo $storeId === (int)$store['id'] ? 'selected' : ''; ?>>
                            <?php echo h($store['location_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Category</label>
                <select name="category_id" class="form-select">
                    <option value="0">All categories</option>
                    <?php foreach ($categories as $cat): ?>
                        <option value="<?php echo (int)$cat['id']; ?>" <?php echo $categoryId === (int)$cat['id'] ? 'selected' : ''; ?>>
                            <?php echo h($cat['category_name']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Item Status</label>
                <select name="status" class="form-select">
                    <option value="">All</option>
                    <option value="1" <?php echo $status === '1' ? 'selected' : ''; ?>>Active</option>
                    <option value="0" <?php echo $status === '0' ? 'selected' : ''; ?>>Inactive</option>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label">Search</label>
                <input type="text" name="q" class="form-control" value="<?php echo h($search); ?>" placeholder="Item/code/store">
            </div>
            <div class="col-md-1">
                <label class="form-label">Zero</label>
                <select name="show_zero" class="form-select">
                    <option value="0" <?php echo $showZero === 0 ? 'selected' : ''; ?>>Hide</option>
                    <option value="1" <?php echo $showZero === 1 ? 'selected' : ''; ?>>Show</option>
                </select>
            </div>
            <div class="col-md-1 d-grid">
                <button class="btn btn-primary"><i class="bi bi-funnel"></i></button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-box-seam"></i></div>
            <div><div class="metric-label">Items</div><div class="metric-value"><?php echo (int)$summary['items']; ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-shop"></i></div>
            <div><div class="metric-label">Stores</div><div class="metric-value"><?php echo (int)$summary['stores']; ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-123"></i></div>
            <div><div class="metric-label">Qty On Hand</div><div class="metric-value small-value"><?php echo money_fmt($summary['qty']); ?></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-cash-stack"></i></div>
            <div><div class="metric-label">Stock Value</div><div class="metric-value small-value"><?php echo money_fmt($summary['value']); ?></div></div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4 no-print">
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Category Totals</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Category</th><th class="text-end">Items</th><th class="text-end">Qty</th><th class="text-end">Value</th></tr></thead>
                    <tbody>
                    <?php if (!$categoryTotals): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No category totals.</td></tr>
                    <?php else: ?>
                        <?php foreach ($categoryTotals as $catName => $tot): ?>
                            <tr>
                                <td><strong><?php echo h($catName); ?></strong></td>
                                <td class="text-end"><?php echo count($tot['items']); ?></td>
                                <td class="text-end"><?php echo money_fmt($tot['qty']); ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($tot['value']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <div class="col-xl-6">
        <div class="card panel-card h-100">
            <div class="card-header"><h5 class="mb-0">Store Totals</h5></div>
            <div class="card-body p-0">
                <table class="table table-modern mb-0">
                    <thead><tr><th>Store</th><th class="text-end">Items</th><th class="text-end">Qty</th><th class="text-end">Value</th></tr></thead>
                    <tbody>
                    <?php if (!$storeTotals): ?>
                        <tr><td colspan="4" class="text-center text-muted py-4">No store totals.</td></tr>
                    <?php else: ?>
                        <?php foreach ($storeTotals as $storeName => $tot): ?>
                            <tr>
                                <td><strong><?php echo h($storeName); ?></strong></td>
                                <td class="text-end"><?php echo count($tot['items']); ?></td>
                                <td class="text-end"><?php echo money_fmt($tot['qty']); ?></td>
                                <td class="text-end fw-bold"><?php echo money_fmt($tot['value']); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="card panel-card">
    <div class="card-header d-flex justify-content-between align-items-center">
        <h5 class="mb-0">Stock Valuation Detail</h5>
        <span class="text-muted small">Showing up to 2,000 rows</span>
    </div>
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-modern align-middle mb-0 valuation-table">
                <thead>
                <tr>
                    <th>Category</th>
                    <th>Item</th>
                    <th>Store</th>
                    <th class="text-end">Qty</th>
                    <th>Unit</th>
                    <th class="text-end">Avg Cost</th>
                    <th class="text-end">Value</th>
                    <th class="text-end">Reorder</th>
                    <th>Status</th>
                </tr>
                </thead>
                <tbody>
                <?php if (!$rows): ?>
                    <tr><td colspan="9" class="text-center text-muted py-4">No stock valuation rows found.</td></tr>
                <?php else: ?>
                    <?php foreach ($rows as $row): ?>
                        <?php
                            $qty = (float)$row['balance_qty'];
                            $avgCost = (float)($row['avg_cost'] ?: $row['cost_price']);
                            $value = $qty * $avgCost;
                            $isNegative = $qty < 0;
                            $isLow = (float)$row['reorder_level'] > 0 && $qty <= (float)$row['reorder_level'];
                            $rowClass = $isNegative ? 'table-danger' : ($isLow ? 'table-warning' : '');
                        ?>
                        <tr class="<?php echo h($rowClass); ?>">
                            <td><?php echo h($row['category_name'] ?: 'Uncategorised'); ?></td>
                            <td>
                                <strong><?php echo h($row['item_name']); ?></strong>
                                <div class="small text-muted">
                                    Code: <?php echo h($row['item_code'] ?: '—'); ?>
                                    <?php if (!empty($row['barcode'])): ?> • Barcode: <?php echo h($row['barcode']); ?><?php endif; ?>
                                </div>
                            </td>
                            <td><?php echo h($row['location_name']); ?></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($qty); ?></td>
                            <td><?php echo h($row['unit_code'] ?: ''); ?></td>
                            <td class="text-end"><?php echo money_fmt($avgCost); ?></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($value); ?></td>
                            <td class="text-end"><?php echo money_fmt($row['reorder_level']); ?></td>
                            <td>
                                <?php if ($isNegative): ?>
                                    <span class="badge text-bg-danger">Negative</span>
                                <?php elseif ($isLow): ?>
                                    <span class="badge text-bg-warning">Low</span>
                                <?php else: ?>
                                    <span class="badge text-bg-success">OK</span>
                                <?php endif; ?>
                                <?php if ((int)$row['is_active'] !== 1): ?>
                                    <span class="badge text-bg-secondary">Inactive</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                </tbody>
                <tfoot>
                <tr>
                    <th colspan="3" class="text-end">Totals</th>
                    <th class="text-end"><?php echo money_fmt($summary['qty']); ?></th>
                    <th></th>
                    <th></th>
                    <th class="text-end"><?php echo money_fmt($summary['value']); ?></th>
                    <th colspan="2"></th>
                </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

<style>
.small-value{font-size:1rem!important;letter-spacing:0!important}
.print-title{display:none}
.valuation-table tfoot th{background:#0f172a!important;color:#fff!important}
@media print{
    .no-print,.sidebar,.enterprise-sidebar,.app-sidebar,.topbar,.navbar,.header,.footer{display:none!important}
    .print-title{display:block;margin-bottom:16px}
    .panel-card,.card{box-shadow:none!important;border:1px solid #ddd!important}
    body{background:#fff!important}
    .table{font-size:11px}
    .metric-card{box-shadow:none!important;border:1px solid #ddd!important}
}
</style>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
