<?php
require_once '../includes/auth.php';
requirePermission('inventory', 'view');
require_once '../db.php';
require_once '../includes/header.php';
require_once '../includes/sidebar.php';

$userRole = $_SESSION['user_role'] ?? '';

$filterCo = $cid;
$companies = null;
if (in_array($userRole, ['admin', 'master_staff'])) {
    $filterCo  = isset($_GET['co']) ? (int)$_GET['co'] : 0;
    $companies = $conn->query("SELECT id, company_name FROM company_master ORDER BY company_name");
}

$fco = $filterCo > 0 ? $filterCo : 0; // 0 = all companies

// Build company condition for subqueries
$poCoWhere  = $fco > 0 ? "AND po.company_id = $fco"            : "";
$invCoWhere = $fco > 0 ? "AND inv.company_id = $fco"           : "";
$poInnerCo  = $fco > 0 ? "AND po_inner.company_id = $fco"     : "";
$po1CoWhere = $fco > 0 ? "AND po1.company_id = $fco"          : "";
$prodCoWhere= $fco > 0 ? "AND p.company_id = $fco"            : "";

$rows = $conn->query("
    SELECT
        p.name          AS product_name,
        p.category,
        p.brand,
        p.hsn_code,
        p.unit,
        COALESCE(latest_pi.mrp,           0) AS mrp,
        COALESCE(latest_pi.purchase_price,0) AS purchase_price,
        COALESCE(latest_pi.selling_price, 0) AS selling_price,
        COALESCE(latest_pi.gst_percent,   0) AS gst_percent,
        COALESCE(v.name, '—')                AS vendor_name,
        COALESCE(pur.total_purchased,     0) AS total_purchased,
        COALESCE(sal.total_sold,          0) AS total_sold,
        COALESCE(stock.available_stock,   0) AS available_stock

    FROM products p

    LEFT JOIN (
        SELECT pi.product_id, SUM(pi.quantity) AS total_purchased
        FROM   purchase_items pi
        JOIN   purchase_orders po ON po.id = pi.purchase_order_id $poCoWhere
        GROUP  BY pi.product_id
    ) pur ON pur.product_id = p.id

    LEFT JOIN (
        SELECT si.product_id, SUM(si.sold_qty) AS total_sold
        FROM   sales_items si
        JOIN   sales_invoices inv ON inv.id = si.sales_invoice_id $invCoWhere
        GROUP  BY si.product_id
    ) sal ON sal.product_id = p.id

    LEFT JOIN (
        SELECT pi.product_id, SUM(pi.available_qty) AS available_stock
        FROM   purchase_items pi
        JOIN   purchase_orders po ON po.id = pi.purchase_order_id $poCoWhere
        GROUP  BY pi.product_id
    ) stock ON stock.product_id = p.id

    LEFT JOIN (
        SELECT pi1.*
        FROM purchase_items pi1
        JOIN purchase_orders po1 ON po1.id = pi1.purchase_order_id $po1CoWhere
        INNER JOIN (
            SELECT pi_inner.product_id, MAX(pi_inner.id) AS max_id
            FROM   purchase_items pi_inner
            JOIN   purchase_orders po_inner ON po_inner.id = pi_inner.purchase_order_id $poInnerCo
            GROUP  BY pi_inner.product_id
        ) pi2 ON pi2.product_id = pi1.product_id AND pi2.max_id = pi1.id
    ) latest_pi ON latest_pi.product_id = p.id

    LEFT JOIN purchase_orders po ON po.id = latest_pi.purchase_order_id
    LEFT JOIN vendors v          ON v.id  = po.vendor_id

    WHERE p.status = 1 $prodCoWhere
    ORDER BY p.name ASC
");
?>

<div class="page-header">
    <h2><i class="fas fa-clipboard-list" style="color:var(--primary);margin-right:8px;"></i>Product Inventory</h2>
</div>

<!-- Search & Company Filter bar -->
<div class="panel" style="margin-bottom:14px;">
    <div class="panel-body" style="padding:12px 16px;">
        <div style="display:flex;align-items:center;gap:12px;flex-wrap:wrap;">

            <?php if ($companies): ?>
            <form method="get" style="display:flex;align-items:center;gap:8px;margin:0;">
                <label style="font-size:13px;font-weight:600;color:var(--text-secondary);margin:0;">Company:</label>
                <select name="co" onchange="this.form.submit()" style="font-size:13px;padding:5px 10px;border:1px solid var(--border);border-radius:6px;">
                    <option value="0" <?= $filterCo == 0 ? 'selected' : '' ?>>All Companies</option>
                    <?php while ($co = $companies->fetch_assoc()): ?>
                    <option value="<?= $co['id'] ?>" <?= $filterCo == $co['id'] ? 'selected' : '' ?>>
                        <?= htmlspecialchars($co['company_name']) ?>
                    </option>
                    <?php endwhile; ?>
                </select>
                <?php if ($filterCo > 0): ?>
                <a href="product_inventory.php" class="btn btn-secondary btn-sm">Clear</a>
                <?php endif; ?>
            </form>
            <div style="width:1px;height:24px;background:var(--border);"></div>
            <?php endif; ?>

            <div style="position:relative;flex:1;max-width:400px;">
                <i class="fas fa-search" style="position:absolute;left:10px;top:50%;transform:translateY(-50%);color:var(--text-muted);font-size:13px;"></i>
                <input type="text" id="inventorySearch"
                       placeholder="Search product, brand, category, HSN..."
                       style="width:100%;padding-left:32px;">
            </div>
            <select id="invPerPage" style="padding:5px 8px;font-size:12px;border:1px solid var(--border);border-radius:6px;">
                <option value="10">10 / page</option>
                <option value="25" selected>25 / page</option>
                <option value="50">50 / page</option>
                <option value="100">100 / page</option>
                <option value="500">500 / page</option>
            </select>
            <span id="rowCount" style="font-size:13px;color:var(--text-muted);white-space:nowrap;"></span>
        </div>
    </div>
</div>

<div class="panel">
    <div class="inv-scroll-wrap">
        <table id="inventoryTable" class="list-table">
            <thead>
                <tr>
                    <th data-col="0" data-type="text">Product</th>
                    <th data-col="1" data-type="text">Category</th>
                    <th data-col="2" data-type="text">Brand</th>
                    <th data-col="3" data-type="text">HSN</th>
                    <th data-col="4" data-type="text">Unit</th>
                    <th data-col="5" data-type="num" style="text-align:right;">MRP</th>
                    <th data-col="6" data-type="num" style="text-align:right;">Purchase Price</th>
                    <th data-col="7" data-type="num" style="text-align:right;">Selling Price</th>
                    <th data-col="8" data-type="num" style="text-align:center;">GST%</th>
                    <th data-col="9" data-type="text">Vendor</th>
                    <th data-col="10" data-type="num" style="text-align:center;">Purchased</th>
                    <th data-col="11" data-type="num" style="text-align:center;">Sold</th>
                    <th data-col="12" data-type="num" style="text-align:center;">Available</th>
                </tr>
            </thead>
            <tbody>
            <?php
            $totalRows = 0;
            while ($row = $rows->fetch_assoc()):
                $totalRows++;
                $avail = (float)$row['available_stock'];
                $availColor = $avail == 0 ? 'var(--danger)' : ($avail < 10 ? 'var(--warning)' : 'var(--success)');
                $searchData = strtolower(
                    $row['product_name'] . ' ' .
                    $row['category']    . ' ' .
                    $row['brand']       . ' ' .
                    $row['hsn_code']    . ' ' .
                    $row['vendor_name']
                );
            ?>
            <tr data-search="<?= htmlspecialchars($searchData) ?>">
                <td><strong><?= htmlspecialchars($row['product_name']) ?></strong></td>
                <td style="font-size:12px;"><?= htmlspecialchars($row['category'] ?: '—') ?></td>
                <td style="font-size:12px;"><?= htmlspecialchars($row['brand'] ?: '—') ?></td>
                <td style="font-size:12px;"><?= htmlspecialchars($row['hsn_code'] ?: '—') ?></td>
                <td style="font-size:12px;"><?= htmlspecialchars($row['unit'] ?: '—') ?></td>
                <td style="text-align:right;">&#8377; <?= number_format((float)$row['mrp'], 2) ?></td>
                <td style="text-align:right;">&#8377; <?= number_format((float)$row['purchase_price'], 2) ?></td>
                <td style="text-align:right;">&#8377; <?= number_format((float)$row['selling_price'], 2) ?></td>
                <td style="text-align:center;"><?= number_format((float)$row['gst_percent'], 0) ?>%</td>
                <td style="font-size:12px;"><?= htmlspecialchars($row['vendor_name']) ?></td>
                <td style="text-align:center;"><?= number_format((float)$row['total_purchased'], 0) ?></td>
                <td style="text-align:center;"><?= number_format((float)$row['total_sold'], 0) ?></td>
                <td style="text-align:center;">
                    <span style="font-weight:700;color:<?= $availColor ?>;">
                        <?= number_format($avail, 0) ?>
                    </span>
                    <?php if ($avail == 0): ?>
                        <span class="badge badge-danger" style="font-size:10px;margin-left:4px;">Out</span>
                    <?php elseif ($avail < 10): ?>
                        <span class="badge badge-warning" style="font-size:10px;margin-left:4px;">Low</span>
                    <?php endif; ?>
                </td>
            </tr>
            <?php endwhile; ?>
            </tbody>
        </table>
    </div>
    <div id="pgBar" class="pg-bar"></div>
</div>

<style>
.inv-scroll-wrap { overflow-x:auto; overflow-y:auto; max-height:calc(100vh - 260px); }
.inv-scroll-wrap thead th { position:sticky; top:0; background:#f1f5f9; z-index:10; box-shadow:0 1px 0 var(--border,#e2e8f0); }
#inventoryTable td, #inventoryTable th { padding:5px 8px; font-size:12px; }
#inventoryTable th { font-size:11px; text-transform:uppercase; letter-spacing:0.03em; cursor:pointer; user-select:none; white-space:nowrap; }
#inventoryTable th:hover { background:#e2e8f0; }
#inventoryTable th::after { content:' ⇅'; font-size:10px; opacity:0.3; }
#inventoryTable th.sort-asc::after { content:' ▲'; opacity:1; color:var(--primary,#2563eb); }
#inventoryTable th.sort-desc::after { content:' ▼'; opacity:1; color:var(--primary,#2563eb); }
.pg-bar { display:flex; align-items:center; gap:8px; padding:8px 16px; border-top:1px solid var(--border,#e2e8f0); flex-wrap:wrap; background:#fafafa; border-radius:0 0 8px 8px; }
.pg-info { font-size:13px; color:var(--text-muted,#94a3b8); }
.pg-btns { display:flex; gap:3px; margin-left:auto; }
.pg-btns button { padding:3px 9px; font-size:12px; border:1px solid var(--border,#e2e8f0); border-radius:4px; background:#fff; cursor:pointer; min-width:32px; }
.pg-btns button:disabled { opacity:0.4; cursor:not-allowed; }
.pg-btns button.active { background:var(--primary,#2563eb); color:#fff; border-color:var(--primary,#2563eb); font-weight:600; }
</style>
<script>
(function(){
    const rows = Array.from(document.querySelectorAll('#inventoryTable tbody tr'));
    const tbody     = document.querySelector('#inventoryTable tbody');
    const searchEl  = document.getElementById('inventorySearch');
    const perPageEl = document.getElementById('invPerPage');
    const barEl     = document.getElementById('pgBar');
    const rowCountEl= document.getElementById('rowCount');
    let page = 1;
    let sortCol = -1, sortAsc = true;

    function cellVal(row, col, type) {
        const td = row.cells[col];
        if (!td) return type === 'num' ? 0 : '';
        const txt = td.textContent.trim();
        if (type === 'num') return parseFloat(txt.replace(/[^0-9.\-]/g, '')) || 0;
        return txt.toLowerCase();
    }

    function applySort() {
        if (sortCol < 0) return;
        const ths = document.querySelectorAll('#inventoryTable thead th');
        const type = ths[sortCol]?.dataset.type || 'text';
        rows.sort((a, b) => {
            const av = cellVal(a, sortCol, type);
            const bv = cellVal(b, sortCol, type);
            if (av < bv) return sortAsc ? -1 : 1;
            if (av > bv) return sortAsc ? 1 : -1;
            return 0;
        });
        rows.forEach(r => tbody.appendChild(r));
    }

    function getVisible() {
        const q = (searchEl?.value || '').trim().toLowerCase();
        return q ? rows.filter(r => (r.dataset.search||'').includes(q)) : rows;
    }

    function render() {
        const pp = parseInt(perPageEl?.value || 25);
        const vis = getVisible();
        const total = vis.length;
        const pages = Math.max(1, Math.ceil(total / pp));
        if (page > pages) page = pages;

        rows.forEach(r => r.style.display = 'none');
        const start = (page-1) * pp;
        vis.slice(start, start+pp).forEach(r => r.style.display = '');

        if (rowCountEl) rowCountEl.textContent = total + ' of <?= $totalRows ?> products';

        const s = Math.max(1, page-2), e = Math.min(pages, page+2);
        let html = `<span class="pg-info">Showing ${total===0?0:start+1}–${Math.min(start+pp,total)} of ${total}</span>`;
        html += `<div class="pg-btns">`;
        html += `<button onclick="pgGo(1)" ${page<=1?'disabled':''}>«</button>`;
        html += `<button onclick="pgGo(${page-1})" ${page<=1?'disabled':''}>‹</button>`;
        for(let i=s;i<=e;i++) html += `<button onclick="pgGo(${i})" ${i===page?'class="active"':''}>${i}</button>`;
        html += `<button onclick="pgGo(${page+1})" ${page>=pages?'disabled':''}>›</button>`;
        html += `<button onclick="pgGo(${pages})" ${page>=pages?'disabled':''}>»</button>`;
        html += `</div>`;
        barEl.innerHTML = html;
    }

    /* Sort on header click */
    document.querySelectorAll('#inventoryTable thead th[data-col]').forEach(th => {
        th.addEventListener('click', function() {
            const col = parseInt(this.dataset.col);
            if (sortCol === col) { sortAsc = !sortAsc; }
            else { sortCol = col; sortAsc = true; }

            document.querySelectorAll('#inventoryTable thead th').forEach((h, i) => {
                h.classList.remove('sort-asc', 'sort-desc');
                if (i === col) h.classList.add(sortAsc ? 'sort-asc' : 'sort-desc');
            });

            applySort();
            page = 1;
            render();
        });
    });

    window.pgGo = function(p){ page=parseInt(p); render(); };
    searchEl?.addEventListener('input', function(){ page=1; render(); });
    perPageEl?.addEventListener('change', function(){ page=1; render(); });
    render();
})();
</script>

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