<?php
/**
 * stock_card.php - per-item stock card (item ledger).
 *
 * The all-items list in stock_movements.php can filter to one item, but it
 * shows the transactions flat: no opening balance, no running balance after
 * each line, no closing. This page is the stock card a storeman actually reads
 * - pick an item and a store, and see the balance move line by line:
 *
 *     Opening balance                              24.000
 *     04 Jul  TRANSFER_IN   +12.000               36.000
 *     05 Jul  ISSUE         -12.000               24.000
 *     06 Jul  GRN           +12.000               36.000
 *     Closing balance                              36.000
 *
 * PER STORE by default, because a running balance across stores is
 * meaningless - a store-to-store transfer nets to zero, so a single running
 * total would just wander. An "All stores" option is offered: it lists every
 * movement across stores for context, but shows NO running balance, because
 * there is no single balance to run.
 *
 * PHP 7.2 compatible. Read-only: this page never writes to stock_movements.
 */

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

$pageTitle = 'Stock Card';

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('sc_qty_fmt')) {
    // Stock quantities: 4dp in the DB, but trailing zeros are noise on screen.
    function sc_qty_fmt($amount): string {
        $n = (float)$amount;
        $s = rtrim(rtrim(number_format($n, 4, '.', ''), '0'), '.');
        return $s === '' || $s === '-0' ? '0' : $s;
    }
}
if (!function_exists('sc_table_exists')) {
    function sc_table_exists(PDO $pdo, string $t): bool {
        try {
            $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :t");
            $s->execute([':t' => $t]);
            return (int)$s->fetchColumn() > 0;
        } catch (Throwable $e) { return false; }
    }
}
if (!function_exists('sc_column_exists')) {
    function sc_column_exists(PDO $pdo, string $t, string $c): bool {
        try {
            $s = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c");
            $s->execute([':t' => $t, ':c' => $c]);
            return (int)$s->fetchColumn() > 0;
        } catch (Throwable $e) { return false; }
    }
}

/* ---------------------------------------------------------------------------
   Movement direction and labels - kept identical to stock_movements.php so the
   two pages describe the same movement the same way.
--------------------------------------------------------------------------- */
$scMovementLabel = function (string $type): string {
    $map = [
        'OPENING'        => 'Opening Balance',
        'GRN'            => 'GRN / Purchase',
        'ISSUE'          => 'Issue / End Use',
        'SALE'           => 'Sale',
        'TRANSFER_IN'    => 'Transfer In',
        'TRANSFER_OUT'   => 'Transfer Out',
        'ADJUSTMENT_IN'  => 'Adjustment In',
        'ADJUSTMENT_OUT' => 'Adjustment Out',
        'WASTAGE'        => 'Wastage',
        'RETURN'         => 'Return',
    ];
    return isset($map[$type]) ? $map[$type] : $type;
};
$scMovementBadge = function (string $type): string {
    if (in_array($type, ['OPENING', 'GRN', 'TRANSFER_IN', 'ADJUSTMENT_IN', 'RETURN'], true)) return 'text-bg-success';
    if (in_array($type, ['ISSUE', 'SALE', 'TRANSFER_OUT', 'ADJUSTMENT_OUT', 'WASTAGE'], true)) return 'text-bg-danger';
    return 'text-bg-secondary';
};

/* ---------------------------------------------------------------------------
   Inputs
--------------------------------------------------------------------------- */
$itemId    = (int)($_GET['item_id'] ?? 0);
$storeId   = (int)($_GET['store_id'] ?? 0);   // 0 = all stores
$dateFrom  = trim((string)($_GET['date_from'] ?? ''));
$dateTo    = trim((string)($_GET['date_to'] ?? ''));
$allStores = ($storeId === 0);

/* ---------------------------------------------------------------------------
   Lookups for the pickers
--------------------------------------------------------------------------- */
$items = [];
if (sc_table_exists($pdo, 'inventory_items')) {
    // Only items that actually have movements - a picker of 879 items, most
    // with no stock history, is not usable.
    $items = $pdo->query("
        SELECT i.id, i.item_name, i.item_code
        FROM inventory_items i
        INNER JOIN (SELECT DISTINCT item_id FROM stock_movements) m ON m.item_id = i.id
        ORDER BY i.item_name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);
}

$stores = [];
if (sc_table_exists($pdo, 'store_locations')) {
    $stores = $pdo->query("SELECT id, location_name FROM store_locations WHERE is_active = 1 ORDER BY location_name ASC")->fetchAll(PDO::FETCH_ASSOC);
}

/* ---------------------------------------------------------------------------
   Resolve the selected item / store names
--------------------------------------------------------------------------- */
$itemRow = null;
if ($itemId > 0) {
    foreach ($items as $it) {
        if ((int)$it['id'] === $itemId) { $itemRow = $it; break; }
    }
    if (!$itemRow && sc_table_exists($pdo, 'inventory_items')) {
        $s = $pdo->prepare("SELECT id, item_name, item_code FROM inventory_items WHERE id = :id LIMIT 1");
        $s->execute([':id' => $itemId]);
        $itemRow = $s->fetch(PDO::FETCH_ASSOC) ?: null;
    }
}
$storeName = 'All stores';
if ($storeId > 0) {
    foreach ($stores as $st) {
        if ((int)$st['id'] === $storeId) { $storeName = $st['location_name']; break; }
    }
}

/* ---------------------------------------------------------------------------
   Build the ledger
   ---------------------------------------------------------------------------
   OPENING BALANCE:
   When a date-from is set, the opening balance is the NET of every movement
   BEFORE that date (for this item, in this store). That is what makes the card
   correct for a mid-period view - the running balance has to start from
   real stock on hand, not from zero. Without a date-from, the opening balance
   is the item's own OPENING movement and everything after it is shown.
--------------------------------------------------------------------------- */
$rows = [];
$openingBalance = 0.0;
$totalIn = 0.0;
$totalOut = 0.0;
$ledgerError = '';

if ($itemId > 0) {
    try {
        // Opening balance = net movement strictly before the window start.
        // Only meaningful per store (all-stores has no single balance).
        if (!$allStores && $dateFrom !== '') {
            $ob = $pdo->prepare("
                SELECT COALESCE(SUM(qty_in - qty_out), 0)
                FROM stock_movements
                WHERE item_id = :item_id
                  AND store_id = :store_id
                  AND movement_date < :from
            ");
            $ob->execute([':item_id' => $itemId, ':store_id' => $storeId, ':from' => $dateFrom . ' 00:00:00']);
            $openingBalance = (float)$ob->fetchColumn();
        }

        // The movements themselves.
        $where = ['sm.item_id = :item_id'];
        $params = [':item_id' => $itemId];
        if (!$allStores) {
            $where[] = 'sm.store_id = :store_id';
            $params[':store_id'] = $storeId;
        }
        if ($dateFrom !== '') {
            $where[] = 'sm.movement_date >= :from';
            $params[':from'] = $dateFrom . ' 00:00:00';
        }
        if ($dateTo !== '') {
            $where[] = 'sm.movement_date <= :to';
            $params[':to'] = $dateTo . ' 23:59:59';
        }

        $storeJoin = sc_table_exists($pdo, 'store_locations')
            ? 'LEFT JOIN store_locations sl ON sl.id = sm.store_id'
            : '';
        $storeSel = sc_table_exists($pdo, 'store_locations')
            ? 'sl.location_name AS store_name'
            : 'NULL AS store_name';

        // Ordered by date then id: id is the tiebreaker so two movements on the
        // same timestamp (an OPENING import stamps many rows 00:00:00) still
        // have a stable, repeatable running balance.
        $sql = "
            SELECT sm.id, sm.movement_date, sm.movement_type, sm.qty_in, sm.qty_out,
                   sm.unit_cost, sm.total_cost, sm.store_id, sm.reference_type,
                   sm.reference_id, sm.batch_no, sm.notes, sm.destination_name,
                   {$storeSel}
            FROM stock_movements sm
            {$storeJoin}
            WHERE " . implode(' AND ', $where) . "
            ORDER BY sm.movement_date ASC, sm.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $movements = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Walk the movements, carrying the running balance.
        $running = $openingBalance;
        foreach ($movements as $m) {
            $in  = (float)$m['qty_in'];
            $out = (float)$m['qty_out'];
            $totalIn  += $in;
            $totalOut += $out;

            // A running balance is only shown per store. Across stores it would
            // be nonsense (transfers net to zero), so it is left null and the
            // column renders blank.
            if (!$allStores) {
                $running += ($in - $out);
                $m['running_balance'] = $running;
            } else {
                $m['running_balance'] = null;
            }
            $rows[] = $m;
        }
    } catch (Throwable $e) {
        $ledgerError = $e->getMessage();
    }
}

$closingBalance = $allStores ? null : ($openingBalance + $totalIn - $totalOut);

/* ---------------------------------------------------------------------------
   CSV export - one item's card as a file, the literal "movement file" reading.
--------------------------------------------------------------------------- */
if (($_GET['export'] ?? '') === 'csv' && $itemId > 0) {
    header('Content-Type: text/csv; charset=utf-8');
    $fnItem = preg_replace('/[^A-Za-z0-9_-]+/', '_', (string)($itemRow['item_name'] ?? ('item_' . $itemId)));
    header('Content-Disposition: attachment; filename="stock_card_' . $fnItem . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Date', 'Type', 'Store', 'In', 'Out', 'Running Balance', 'Unit Cost', 'Reference', 'Batch', 'Notes']);
    if (!$allStores) {
        fputcsv($out, [$dateFrom !== '' ? $dateFrom : '', 'OPENING BALANCE', $storeName, '', '', sc_qty_fmt($openingBalance), '', '', '', '']);
    }
    foreach ($rows as $r) {
        fputcsv($out, [
            date('Y-m-d H:i', strtotime((string)$r['movement_date'])),
            $r['movement_type'],
            (string)($r['store_name'] ?? $r['store_id']),
            $r['qty_in'] > 0 ? sc_qty_fmt($r['qty_in']) : '',
            $r['qty_out'] > 0 ? sc_qty_fmt($r['qty_out']) : '',
            $r['running_balance'] === null ? '' : sc_qty_fmt($r['running_balance']),
            money_fmt($r['unit_cost']),
            trim((string)$r['reference_type'] . ' ' . (string)$r['reference_id']),
            (string)$r['batch_no'],
            (string)$r['notes'],
        ]);
    }
    if (!$allStores) {
        fputcsv($out, ['', 'CLOSING BALANCE', $storeName, sc_qty_fmt($totalIn), sc_qty_fmt($totalOut), sc_qty_fmt($closingBalance), '', '', '', '']);
    }
    fclose($out);
    exit;
}

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

<div class="hero-band">
    <div class="container-fluid">
        <h1 class="h4 mb-1"><i class="bi bi-card-list me-2"></i>Stock Card</h1>
        <p class="text-muted mb-0">One item's movement history in a single store, with a running balance after every line.</p>
    </div>
</div>

<div class="container-fluid py-3">

    <form method="get" class="card mb-3">
        <div class="card-body">
            <div class="row g-3 align-items-end">
                <div class="col-md-4">
                    <label class="form-label small fw-semibold text-uppercase">Item</label>
                    <select name="item_id" class="form-select" required onchange="this.form.submit()">
                        <option value="0">Select an item...</option>
                        <?php foreach ($items as $it): ?>
                            <option value="<?php echo (int)$it['id']; ?>" <?php echo $itemId === (int)$it['id'] ? 'selected' : ''; ?>>
                                <?php echo h($it['item_name']); ?><?php echo !empty($it['item_code']) ? ' (' . h($it['item_code']) . ')' : ''; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label small fw-semibold text-uppercase">Store</label>
                    <select name="store_id" class="form-select" onchange="this.form.submit()">
                        <option value="0" <?php echo $allStores ? 'selected' : ''; ?>>All stores (no running balance)</option>
                        <?php foreach ($stores as $st): ?>
                            <option value="<?php echo (int)$st['id']; ?>" <?php echo $storeId === (int)$st['id'] ? 'selected' : ''; ?>>
                                <?php echo h($st['location_name']); ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <label class="form-label small fw-semibold text-uppercase">From</label>
                    <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
                </div>
                <div class="col-md-2">
                    <label class="form-label small fw-semibold text-uppercase">To</label>
                    <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
                </div>
                <div class="col-md-1">
                    <button class="btn btn-primary w-100"><i class="bi bi-search"></i></button>
                </div>
            </div>
        </div>
    </form>

    <?php if ($itemId <= 0): ?>
        <div class="alert alert-info">Select an item above to see its stock card.</div>
    <?php elseif ($ledgerError !== ''): ?>
        <div class="alert alert-danger">Could not load the stock card: <?php echo h($ledgerError); ?></div>
    <?php else: ?>

        <div class="d-flex justify-content-between align-items-center mb-2 flex-wrap gap-2">
            <div>
                <h5 class="mb-0"><?php echo h($itemRow['item_name'] ?? ('Item #' . $itemId)); ?></h5>
                <div class="text-muted small">
                    <?php echo h($storeName); ?>
                    <?php if ($dateFrom !== '' || $dateTo !== ''): ?>
                        &middot; <?php echo h($dateFrom !== '' ? $dateFrom : 'start'); ?> to <?php echo h($dateTo !== '' ? $dateTo : 'now'); ?>
                    <?php endif; ?>
                </div>
            </div>
            <a class="btn btn-outline-secondary btn-sm"
               href="?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>">
                <i class="bi bi-download me-1"></i>Export CSV
            </a>
        </div>

        <?php if ($allStores): ?>
            <div class="alert alert-warning py-2 small">
                Showing movements across all stores. A running balance is not shown, because
                store-to-store transfers cancel out and there is no single balance to run.
                Pick one store to see a running balance.
            </div>
        <?php endif; ?>

        <div class="row g-2 mb-3">
            <?php if (!$allStores): ?>
                <div class="col-md-3"><div class="card"><div class="card-body py-2">
                    <div class="small text-muted">Opening Balance</div>
                    <div class="h5 mb-0"><?php echo sc_qty_fmt($openingBalance); ?></div>
                </div></div></div>
            <?php endif; ?>
            <div class="col-md-3"><div class="card"><div class="card-body py-2">
                <div class="small text-muted">Total In</div>
                <div class="h5 mb-0 text-success">+<?php echo sc_qty_fmt($totalIn); ?></div>
            </div></div></div>
            <div class="col-md-3"><div class="card"><div class="card-body py-2">
                <div class="small text-muted">Total Out</div>
                <div class="h5 mb-0 text-danger">-<?php echo sc_qty_fmt($totalOut); ?></div>
            </div></div></div>
            <?php if (!$allStores): ?>
                <div class="col-md-3"><div class="card border-primary"><div class="card-body py-2">
                    <div class="small text-muted">Closing Balance</div>
                    <div class="h5 mb-0 fw-bold"><?php echo sc_qty_fmt($closingBalance); ?></div>
                </div></div></div>
            <?php endif; ?>
        </div>

        <div class="table-responsive">
            <table class="table table-sm table-hover align-middle">
                <thead class="table-light">
                    <tr>
                        <th>Date</th>
                        <th>Type</th>
                        <?php if ($allStores): ?><th>Store</th><?php endif; ?>
                        <th class="text-end">In</th>
                        <th class="text-end">Out</th>
                        <?php if (!$allStores): ?><th class="text-end">Balance</th><?php endif; ?>
                        <th class="text-end">Unit Cost</th>
                        <th>Reference</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!$allStores): ?>
                        <tr class="table-secondary">
                            <td colspan="2"><strong>Opening Balance</strong>
                                <?php if ($dateFrom !== ''): ?><span class="text-muted small">as at <?php echo h($dateFrom); ?></span><?php endif; ?>
                            </td>
                            <td class="text-end"></td>
                            <td class="text-end"></td>
                            <td class="text-end fw-bold"><?php echo sc_qty_fmt($openingBalance); ?></td>
                            <td colspan="2"></td>
                        </tr>
                    <?php endif; ?>

                    <?php if (!$rows): ?>
                        <tr><td colspan="<?php echo $allStores ? 6 : 7; ?>" class="text-center text-muted py-3">
                            No movements for this item<?php echo $allStores ? '' : ' in this store'; ?><?php echo ($dateFrom !== '' || $dateTo !== '') ? ' in this date range' : ''; ?>.
                        </td></tr>
                    <?php else: foreach ($rows as $r): ?>
                        <tr>
                            <td class="text-nowrap small"><?php echo h(date('d M Y H:i', strtotime((string)$r['movement_date']))); ?></td>
                            <td><span class="badge <?php echo $scMovementBadge((string)$r['movement_type']); ?>"><?php echo h($scMovementLabel((string)$r['movement_type'])); ?></span></td>
                            <?php if ($allStores): ?><td class="small"><?php echo h((string)($r['store_name'] ?? $r['store_id'])); ?></td><?php endif; ?>
                            <td class="text-end text-success"><?php echo $r['qty_in'] > 0 ? '+' . sc_qty_fmt($r['qty_in']) : ''; ?></td>
                            <td class="text-end text-danger"><?php echo $r['qty_out'] > 0 ? '-' . sc_qty_fmt($r['qty_out']) : ''; ?></td>
                            <?php if (!$allStores): ?>
                                <td class="text-end fw-semibold"><?php echo sc_qty_fmt($r['running_balance']); ?></td>
                            <?php endif; ?>
                            <td class="text-end small"><?php echo money_fmt($r['unit_cost']); ?></td>
                            <td class="small text-muted">
                                <?php
                                    $ref = trim((string)$r['reference_type']);
                                    if ($r['batch_no']) $ref .= ($ref !== '' ? ' &middot; ' : '') . h((string)$r['batch_no']);
                                    echo $ref !== '' ? $ref : '&mdash;';
                                ?>
                            </td>
                        </tr>
                    <?php endforeach; endif; ?>
                </tbody>

                <?php if (!$allStores && $rows): ?>
                    <tfoot>
                        <tr class="table-primary">
                            <td colspan="2"><strong>Closing Balance</strong></td>
                            <td class="text-end fw-bold text-success">+<?php echo sc_qty_fmt($totalIn); ?></td>
                            <td class="text-end fw-bold text-danger">-<?php echo sc_qty_fmt($totalOut); ?></td>
                            <td class="text-end fw-bold"><?php echo sc_qty_fmt($closingBalance); ?></td>
                            <td colspan="2"></td>
                        </tr>
                    </tfoot>
                <?php endif; ?>
            </table>
        </div>
    <?php endif; ?>
</div>

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