<?php
/**
 * stock_by_location.php
 *
 * Stockholding by location report - a live pivot of stock on hand for every
 * item across every store location, calculated straight from stock_movements
 * (SUM(qty_in - qty_out) per item per store), the same balance rule used by
 * sm_stock_balance() in stock_movements.php, so this report always agrees
 * with the movement screens.
 *
 * - One column per active store location, one row per item
 * - Per-item total quantity across locations + stock value
 * - Per-location stock value totals + grand total
 * - Filters: location, category, item search, show zero balances
 * - CSV export honouring the current filters
 *
 * Valuation basis: avg_cost when set, otherwise cost_price (same fallback
 * as sm_get_item_cost() in stock_movements.php).
 */

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

$pageTitle = 'Stock by Location';

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); }
}
if (!function_exists('sbl_qty_fmt')) {
    function sbl_qty_fmt($qty): string {
        // Trim trailing zeros so whole numbers read cleanly (12 not 12.0000)
        $s = number_format((float)$qty, 4, '.', '');
        $s = rtrim(rtrim($s, '0'), '.');
        return $s === '' || $s === '-0' ? '0' : $s;
    }
}
if (!function_exists('sbl_table_exists')) {
    function sbl_table_exists(PDO $pdo, string $tableName): bool {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table_name");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

/* =========================================================================
   Filters
   ========================================================================= */
$filterStore    = (int)($_GET['store_id'] ?? 0);
$filterCategory = (int)($_GET['category_id'] ?? 0);
$search         = trim((string)($_GET['q'] ?? ''));
$showZero       = !empty($_GET['show_zero']) ? 1 : 0;
$exportCsv      = (($_GET['export'] ?? '') === 'csv');

$errors = [];
$locations = [];
$categories = [];
$items = [];          // item_id => item detail row
$matrix = [];         // item_id => [store_id => balance_qty]
$itemTotals = [];     // item_id => total qty across shown locations
$locationValues = []; // store_id => total stock value
$grandValue = 0.00;

try {
    if (!sbl_table_exists($pdo, 'stock_movements') || !sbl_table_exists($pdo, 'store_locations') || !sbl_table_exists($pdo, 'inventory_items')) {
        throw new RuntimeException('Stock tables not found. Open Stock Movements once first so the inventory schema is created.');
    }

    /* Locations shown as matrix columns. A location filter narrows the
       matrix to that single column. */
    if ($filterStore > 0) {
        $stmt = $pdo->prepare("SELECT id, location_code, location_name, location_type FROM store_locations WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $filterStore]);
        $locations = array_values(array_filter([$stmt->fetch(PDO::FETCH_ASSOC)]));
        if (!$locations) { $filterStore = 0; }
    }
    if (!$locations) {
        $locations = $pdo->query("SELECT id, location_code, location_name, location_type FROM store_locations WHERE is_active = 1 ORDER BY location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
    }

    if (sbl_table_exists($pdo, 'inventory_item_categories')) {
        $categories = $pdo->query("SELECT id, category_name FROM inventory_item_categories WHERE is_active = 1 ORDER BY category_name ASC")->fetchAll(PDO::FETCH_ASSOC);
    }

    /* ---------------------------------------------------------------------
       Balances per item per location. GROUP BY lists exactly the selected
       non-aggregate columns (sm.item_id, sm.store_id) - safe under
       ONLY_FULL_GROUP_BY. Item/category filters are applied here so the
       matrix and the CSV export always match the visible filter state.
       --------------------------------------------------------------------- */
    $where = "1=1";
    $params = [];
    if ($filterStore > 0) {
        $where .= " AND sm.store_id = :store_id";
        $params[':store_id'] = $filterStore;
    }
    if ($filterCategory > 0) {
        $where .= " AND ii.category_id = :category_id";
        $params[':category_id'] = $filterCategory;
    }
    if ($search !== '') {
        $where .= " AND (ii.item_name LIKE :q_name OR ii.item_code LIKE :q_code OR ii.barcode LIKE :q_barcode)";
        $like = '%' . $search . '%';
        $params[':q_name'] = $like;
        $params[':q_code'] = $like;
        $params[':q_barcode'] = $like;
    }

    $stmt = $pdo->prepare("
        SELECT
            sm.item_id,
            sm.store_id,
            COALESCE(SUM(sm.qty_in - sm.qty_out), 0) AS balance_qty
        FROM stock_movements sm
        INNER JOIN inventory_items ii ON ii.id = sm.item_id
        WHERE {$where}
        GROUP BY sm.item_id, sm.store_id
    ");
    $stmt->execute($params);
    $balanceRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $itemIds = [];
    foreach ($balanceRows as $row) {
        $itemId  = (int)$row['item_id'];
        $storeId = (int)$row['store_id'];
        $qty     = round((float)$row['balance_qty'], 4);
        if (!isset($matrix[$itemId])) $matrix[$itemId] = [];
        $matrix[$itemId][$storeId] = $qty;
        $itemIds[$itemId] = $itemId;
    }

    /* Item details for every item that has movement history under the
       current filters. Inactive items are intentionally NOT excluded -
       an item that still holds stock must appear even if it was retired. */
    if ($itemIds) {
        $placeholders = [];
        $detailParams = [];
        $n = 0;
        foreach ($itemIds as $id) {
            $n++;
            $ph = ':item_' . $n;
            $placeholders[] = $ph;
            $detailParams[$ph] = $id;
        }
        $detailStmt = $pdo->prepare("
            SELECT
                ii.id,
                ii.item_code,
                ii.item_name,
                ii.reorder_level,
                ii.is_active,
                COALESCE(NULLIF(ii.avg_cost, 0), ii.cost_price) AS unit_cost,
                c.category_name,
                u.unit_code
            FROM inventory_items ii
            LEFT JOIN inventory_item_categories c ON c.id = ii.category_id
            LEFT JOIN inventory_units u ON u.id = ii.unit_id
            WHERE ii.id IN (" . implode(',', $placeholders) . ")
            ORDER BY c.category_name ASC, ii.item_name ASC
        ");
        $detailStmt->execute($detailParams);
        foreach ($detailStmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $items[(int)$row['id']] = $row;
        }
    }

    /* Totals, values, and the zero-balance filter. An item is "zero" when
       every shown location balance rounds to zero. */
    $shownStoreIds = [];
    foreach ($locations as $loc) $shownStoreIds[] = (int)$loc['id'];

    foreach ($items as $itemId => $detail) {
        $total = 0.00;
        foreach ($shownStoreIds as $sid) {
            $total += (float)($matrix[$itemId][$sid] ?? 0);
        }
        $total = round($total, 4);

        if (!$showZero && abs($total) < 0.0001) {
            $allZero = true;
            foreach ($shownStoreIds as $sid) {
                if (abs((float)($matrix[$itemId][$sid] ?? 0)) > 0.0001) { $allZero = false; break; }
            }
            if ($allZero) {
                unset($items[$itemId]);
                continue;
            }
        }

        $itemTotals[$itemId] = $total;

        $unitCost = (float)($detail['unit_cost'] ?? 0);
        foreach ($shownStoreIds as $sid) {
            $qty = (float)($matrix[$itemId][$sid] ?? 0);
            if (abs($qty) < 0.0001) continue;
            $value = round($qty * $unitCost, 2);
            $locationValues[$sid] = round((float)($locationValues[$sid] ?? 0) + $value, 2);
            $grandValue = round($grandValue + $value, 2);
        }
    }
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

/* =========================================================================
   CSV export - honours the exact same filters as the on-screen matrix.
   Runs BEFORE any HTML output.
   ========================================================================= */
if ($exportCsv && !$errors) {
    $filename = 'stock_by_location_' . date('Ymd_His') . '.csv';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $out = fopen('php://output', 'w');
    // UTF-8 BOM so Excel opens it with correct encoding
    fwrite($out, "\xEF\xBB\xBF");

    $headerRow = ['Item Code', 'Item', 'Category', 'Unit'];
    foreach ($locations as $loc) $headerRow[] = $loc['location_name'];
    $headerRow[] = 'Total Qty';
    $headerRow[] = 'Unit Cost';
    $headerRow[] = 'Stock Value';
    fputcsv($out, $headerRow);

    foreach ($items as $itemId => $detail) {
        $row = [
            (string)($detail['item_code'] ?? ''),
            (string)($detail['item_name'] ?? ''),
            (string)($detail['category_name'] ?? ''),
            (string)($detail['unit_code'] ?? ''),
        ];
        foreach ($locations as $loc) {
            $row[] = sbl_qty_fmt($matrix[$itemId][(int)$loc['id']] ?? 0);
        }
        $total = (float)($itemTotals[$itemId] ?? 0);
        $unitCost = (float)($detail['unit_cost'] ?? 0);
        $row[] = sbl_qty_fmt($total);
        $row[] = number_format($unitCost, 4, '.', '');
        $row[] = number_format(round($total * $unitCost, 2), 2, '.', '');
        fputcsv($out, $row);
    }

    $totalsRow = ['', 'TOTAL STOCK VALUE', '', ''];
    foreach ($locations as $loc) {
        $totalsRow[] = number_format((float)($locationValues[(int)$loc['id']] ?? 0), 2, '.', '');
    }
    $totalsRow[] = '';
    $totalsRow[] = '';
    $totalsRow[] = number_format($grandValue, 2, '.', '');
    fputcsv($out, $totalsRow);

    fclose($out);
    exit;
}

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

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-geo-alt"></i> Stores &amp; POS Inventory</div>
            <div class="hero-title mt-2">Stock by Location</div>
            <div class="hero-subtitle mt-2">
                Live stockholding for every item across each store location, straight from the stock movements ledger.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="stock_movements.php" class="btn btn-light me-2">
                <i class="bi bi-arrow-left-right me-2"></i>Movements
            </a>
            <a href="inventory_items.php" class="btn btn-light me-2">
                <i class="bi bi-box-seam me-2"></i>Items
            </a>
            <a href="store_locations.php" class="btn btn-outline-light">
                <i class="bi bi-shop me-2"></i>Stores
            </a>
        </div>
    </div>
</div>

<?php foreach ($errors as $error): ?>
    <div class="alert alert-danger"><?php echo h($error); ?></div>
<?php endforeach; ?>

<?php if (!$errors): ?>

<!-- Per-location value summary cards -->
<div class="row g-3 mb-3">
    <?php foreach ($locations as $loc): $sid = (int)$loc['id']; ?>
        <div class="col-6 col-md-4 col-xl-3">
            <div class="card panel-card h-100">
                <div class="card-body py-2 px-3">
                    <div class="small text-muted text-truncate" title="<?php echo h($loc['location_name']); ?>">
                        <i class="bi bi-shop me-1"></i><?php echo h($loc['location_name']); ?>
                    </div>
                    <div class="fw-bold fs-5"><?php echo money_fmt($locationValues[$sid] ?? 0); ?></div>
                    <div class="small text-muted"><?php
                        $locItemCount = 0;
                        foreach ($items as $iid => $d) {
                            if (abs((float)($matrix[$iid][$sid] ?? 0)) > 0.0001) $locItemCount++;
                        }
                        echo (int)$locItemCount; ?> item<?php echo $locItemCount === 1 ? '' : 's'; ?> holding stock</div>
                </div>
            </div>
        </div>
    <?php endforeach; ?>
    <div class="col-6 col-md-4 col-xl-3">
        <div class="card panel-card h-100 border-primary">
            <div class="card-body py-2 px-3">
                <div class="small text-muted"><i class="bi bi-cash-stack me-1"></i>Total Stockholding</div>
                <div class="fw-bold fs-5 text-primary"><?php echo money_fmt($grandValue); ?></div>
                <div class="small text-muted"><?php echo count($items); ?> item<?php echo count($items) === 1 ? '' : 's'; ?> shown</div>
            </div>
        </div>
    </div>
</div>

<!-- Filters -->
<div class="card panel-card mb-3">
    <div class="card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-3">
                <label class="form-label small mb-1">Location</label>
                <select name="store_id" class="form-select form-select-sm">
                    <option value="0">All locations</option>
                    <?php
                    $allLocations = $pdo->query("SELECT id, location_name FROM store_locations WHERE is_active = 1 ORDER BY location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
                    foreach ($allLocations as $loc): ?>
                        <option value="<?php echo (int)$loc['id']; ?>" <?php echo $filterStore === (int)$loc['id'] ? 'selected' : ''; ?>><?php echo h($loc['location_name']); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3">
                <label class="form-label small mb-1">Category</label>
                <select name="category_id" class="form-select form-select-sm">
                    <option value="0">All categories</option>
                    <?php foreach ($categories as $cat): ?>
                        <option value="<?php echo (int)$cat['id']; ?>" <?php echo $filterCategory === (int)$cat['id'] ? 'selected' : ''; ?>><?php echo h($cat['category_name']); ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-3">
                <label class="form-label small mb-1">Search item</label>
                <input type="text" name="q" value="<?php echo h($search); ?>" class="form-control form-control-sm" placeholder="Name, code or barcode">
            </div>
            <div class="col-md-3 d-flex gap-2 align-items-center">
                <div class="form-check mb-1">
                    <input class="form-check-input" type="checkbox" name="show_zero" value="1" id="sblShowZero" <?php echo $showZero ? 'checked' : ''; ?>>
                    <label class="form-check-label small" for="sblShowZero">Show zero balances</label>
                </div>
                <button class="btn btn-sm btn-primary"><i class="bi bi-funnel me-1"></i>Apply</button>
                <a class="btn btn-sm btn-outline-success"
                   href="?<?php echo h(http_build_query(['store_id'=>$filterStore,'category_id'=>$filterCategory,'q'=>$search,'show_zero'=>$showZero,'export'=>'csv'])); ?>">
                    <i class="bi bi-filetype-csv me-1"></i>Export
                </a>
            </div>
        </form>
    </div>
</div>

<!-- Stock matrix -->
<div class="card panel-card">
    <div class="card-header d-flex justify-content-between align-items-center">
        <span><i class="bi bi-grid-3x3 me-2"></i>Stock on Hand by Location</span>
        <span class="small text-muted">Qty shown per location &middot; value at avg cost (cost price fallback)</span>
    </div>
    <div class="card-body p-0">
        <?php if (!$items): ?>
            <div class="p-3 text-muted small">No stockholding found for the current filters.</div>
        <?php else: ?>
            <div class="sbl-table-wrap">
                <table class="table table-modern table-sm align-middle mb-0 sbl-table">
                    <thead>
                        <tr>
                            <th class="sbl-sticky-col">Item</th>
                            <th>Category</th>
                            <th>Unit</th>
                            <?php foreach ($locations as $loc): ?>
                                <th class="text-end" title="<?php echo h($loc['location_name']); ?>"><?php echo h($loc['location_code'] ?: $loc['location_name']); ?></th>
                            <?php endforeach; ?>
                            <th class="text-end table-secondary">Total Qty</th>
                            <th class="text-end">Unit Cost</th>
                            <th class="text-end table-secondary">Value</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($items as $itemId => $detail): ?>
                            <?php
                                $total = (float)($itemTotals[$itemId] ?? 0);
                                $unitCost = (float)($detail['unit_cost'] ?? 0);
                                $reorder = (float)($detail['reorder_level'] ?? 0);
                                $lowStock = $reorder > 0 && $total <= $reorder;
                            ?>
                            <tr>
                                <td class="sbl-sticky-col">
                                    <strong><?php echo h($detail['item_name']); ?></strong>
                                    <?php if (!empty($detail['item_code'])): ?>
                                        <span class="small text-muted ms-1"><?php echo h($detail['item_code']); ?></span>
                                    <?php endif; ?>
                                    <?php if ((int)($detail['is_active'] ?? 1) !== 1): ?>
                                        <span class="badge text-bg-secondary ms-1">Inactive</span>
                                    <?php endif; ?>
                                    <?php if ($lowStock): ?>
                                        <span class="badge text-bg-warning ms-1" title="At or below reorder level <?php echo h(sbl_qty_fmt($reorder)); ?>">Low</span>
                                    <?php endif; ?>
                                </td>
                                <td class="small text-muted"><?php echo h($detail['category_name'] ?? ''); ?></td>
                                <td class="small"><?php echo h($detail['unit_code'] ?? ''); ?></td>
                                <?php foreach ($locations as $loc): ?>
                                    <?php $qty = (float)($matrix[$itemId][(int)$loc['id']] ?? 0); ?>
                                    <td class="text-end <?php echo $qty < -0.0001 ? 'text-danger fw-bold' : (abs($qty) < 0.0001 ? 'text-muted' : ''); ?>">
                                        <?php echo abs($qty) < 0.0001 ? '&mdash;' : h(sbl_qty_fmt($qty)); ?>
                                    </td>
                                <?php endforeach; ?>
                                <td class="text-end fw-bold table-secondary <?php echo $total < -0.0001 ? 'text-danger' : ''; ?>"><?php echo h(sbl_qty_fmt($total)); ?></td>
                                <td class="text-end small"><?php echo money_fmt($unitCost); ?></td>
                                <td class="text-end fw-bold table-secondary"><?php echo money_fmt(round($total * $unitCost, 2)); ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                    <tfoot>
                        <tr class="table-dark">
                            <td class="sbl-sticky-col fw-bold">TOTAL STOCK VALUE</td>
                            <td></td>
                            <td></td>
                            <?php foreach ($locations as $loc): ?>
                                <td class="text-end fw-bold"><?php echo money_fmt($locationValues[(int)$loc['id']] ?? 0); ?></td>
                            <?php endforeach; ?>
                            <td></td>
                            <td></td>
                            <td class="text-end fw-bold"><?php echo money_fmt($grandValue); ?></td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        <?php endif; ?>
    </div>
</div>

<?php endif; ?>

<style>
.sbl-table-wrap{overflow:auto;max-height:72vh}
.sbl-table thead th{position:sticky;top:0;background:#fff;z-index:2;white-space:nowrap}
.sbl-table .sbl-sticky-col{position:sticky;left:0;background:#fff;z-index:1;min-width:220px;max-width:320px}
.sbl-table thead th.sbl-sticky-col{z-index:3}
.sbl-table tfoot td{position:sticky;bottom:0;z-index:2}
.sbl-table tfoot .sbl-sticky-col{z-index:3;background:inherit}
.sbl-table td,.sbl-table th{white-space:nowrap}
.hero-band{padding:10px 14px!important;margin-bottom:8px!important}
.hero-band .btn{padding:5px 9px!important;font-size:.78rem!important;border-radius:8px!important}
.panel-card .card-header{padding:9px 12px!important}
</style>

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