<?php
// ============================================================
//  MediCore — Smart Requisition
//  File: product/smart-requisition.php
// ============================================================
$page  = 'product/smart-requisition';
$title = 'Smart Requisition';
require_once '../includes/header.php';
$today = date('Y-m-d');
require_once '../includes/requisition-tabs.php';

$cfg      = $settings;
$cur      = $cfg['currency'];
$reqPrefix= $cfg['req_prefix'] ?? 'REQ';
$today    = today();

// Load suppliers and companies for filters
$suppliers = db_rows("SELECT id, name FROM suppliers ORDER BY name");
$companies = db_rows("SELECT id, name FROM companies ORDER BY name");

// ── Smart priority query ──────────────────────────────────────
// Only products: purchased at least once + below low_stock_alert
$smartProducts = db_rows(
    "SELECT p.*,
            g.name  AS generic_name,
            c.name  AS company_name,
            -- Purchase history
            COUNT(DISTINCT pi.purchase_id)                  AS purchase_count,
            -- Last supplier
            (SELECT s.name FROM purchases po2
               JOIN purchase_items pi2 ON pi2.purchase_id = po2.id
               JOIN suppliers s ON s.id = po2.supplier_id
              WHERE pi2.product_id = p.id
              ORDER BY po2.date DESC LIMIT 1)               AS last_supplier_name,
            (SELECT po2.supplier_id FROM purchases po2
               JOIN purchase_items pi2 ON pi2.purchase_id = po2.id
              WHERE pi2.product_id = p.id
              ORDER BY po2.date DESC LIMIT 1)               AS last_supplier_id,
            -- Last purchase price
            (SELECT pi2.btp_vat FROM purchase_items pi2
               JOIN purchases po2 ON po2.id = pi2.purchase_id
              WHERE pi2.product_id = p.id
              ORDER BY po2.date DESC LIMIT 1)               AS last_btp_vat,
            -- Sales last 30 days
            COALESCE((SELECT SUM(si.qty) FROM sale_items si
               JOIN sales s2 ON s2.id = si.sale_id
              WHERE si.product_id = p.id
                AND s2.date >= DATE_SUB(NOW(), INTERVAL 30 DAY)),0) AS sales_30d,
            -- Avg monthly sales (3 months)
            COALESCE((SELECT SUM(si.qty)/3 FROM sale_items si
               JOIN sales s2 ON s2.id = si.sale_id
              WHERE si.product_id = p.id
                AND s2.date >= DATE_SUB(NOW(), INTERVAL 90 DAY)),0) AS avg_monthly,
            -- Pending requisition qty
            COALESCE((SELECT SUM(ri.qty_box * p.qty_in_box)
               FROM requisition_items ri
               JOIN requisitions r ON r.id = ri.requisition_id
              WHERE ri.product_id = p.id
                AND r.status IN ('active','ordered')),0)    AS pending_qty
       FROM products p
  LEFT JOIN generics  g  ON g.id  = p.generic_id
  LEFT JOIN companies c  ON c.id  = p.company_id
  LEFT JOIN purchase_items pi ON pi.product_id = p.id
      WHERE p.is_active = 1
        AND p.low_stock_alert > 0
        AND p.stock_pcs < p.low_stock_alert
      GROUP BY p.id
     HAVING purchase_count > 0
      ORDER BY p.stock_pcs ASC, sales_30d DESC"
);

// ── Summary counts ────────────────────────────────────────────
$totalLow  = count($smartProducts);
$outOfStock= count(array_filter($smartProducts, fn($p) => (int)$p['stock_pcs'] <= 0));
$urgent    = count(array_filter($smartProducts, fn($p) =>
    (int)$p['sales_30d'] > 10 || (int)$p['stock_pcs'] <= 0));

// Estimated reorder value
$reorderValue = 0;
foreach ($smartProducts as $p) {
    $sugQty   = max(
        (int)$p['low_stock_alert'] * 2,
        ceil((float)$p['avg_monthly'] * 1.5)
    ) - (int)$p['stock_pcs'];
    $sugBoxes = max(1, ceil($sugQty / max(1,(int)$p['qty_in_box'])));
    $reorderValue += $sugBoxes * (float)($p['last_btp_vat'] ?? $p['btp_vat']);
}
?>

<!-- ── Page actions ───────────────────────────────── -->
<script>
document.getElementById('ph-actions').innerHTML = `
  <button class="btn btn-primary btn-sm" onclick="createRequisitions()">
    📝 Create Requisitions by Supplier
  </button>`;
</script>

<!-- ── Dashboard widgets ──────────────────────────── -->
<div style="display:grid;grid-template-columns:repeat(4,1fr);
            gap:10px;margin-bottom:14px">
  <div class="sc" style="border-top:3px solid var(--warn)">
    <div class="slabel">Low Stock Products</div>
    <div class="sval b" style="color:var(--warn)"><?= $totalLow ?></div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">Below alert level</div>
  </div>
  <div class="sc" style="border-top:3px solid var(--danger)">
    <div class="slabel">Out of Stock</div>
    <div class="sval b" style="color:var(--danger)"><?= $outOfStock ?></div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">Zero stock</div>
  </div>
  <div class="sc" style="border-top:3px solid #ea580c">
    <div class="slabel">Urgent Items</div>
    <div class="sval b" style="color:#ea580c"><?= $urgent ?></div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">High sales + low stock</div>
  </div>
  <div class="sc" style="border-top:3px solid var(--brand)">
    <div class="slabel">Est. Reorder Value</div>
    <div class="sval b" style="color:var(--brand)">
      <?= $cur . number_format($reorderValue, 0) ?>
    </div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">Approx. cost</div>
  </div>
</div>

<!-- ── Legend ─────────────────────────────────────── -->
<div style="display:flex;gap:14px;margin-bottom:10px;font-size:11.5px;
            flex-wrap:wrap;align-items:center">
  <span><span style="display:inline-block;width:10px;height:10px;
    background:#fde8e8;border:1px solid var(--danger);border-radius:2px;
    margin-right:4px"></span>🔴 Out of Stock</span>
  <span><span style="display:inline-block;width:10px;height:10px;
    background:#fff3dc;border:1px solid var(--warn);border-radius:2px;
    margin-right:4px"></span>🟠 Below Alert Level</span>
  <span><span style="display:inline-block;width:10px;height:10px;
    background:#d4f0e6;border:1px solid var(--accent);border-radius:2px;
    margin-right:4px"></span>🟢 Sufficient</span>

  <!-- Filters -->
  <div style="margin-left:auto;display:flex;gap:8px;align-items:center">
    <select id="f-supplier" onchange="applyFilters()"
      style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);
             font-size:11.5px;outline:none">
      <option value="">All Suppliers</option>
      <?php foreach ($suppliers as $s): ?>
      <option value="<?= $s['id'] ?>"><?= htmlspecialchars($s['name']) ?></option>
      <?php endforeach; ?>
    </select>
    <select id="f-company" onchange="applyFilters()"
      style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);
             font-size:11.5px;outline:none">
      <option value="">All Companies</option>
      <?php foreach ($companies as $c): ?>
      <option value="<?= htmlspecialchars($c['name']) ?>">
        <?= htmlspecialchars($c['name']) ?>
      </option>
      <?php endforeach; ?>
    </select>
    <label style="font-size:11.5px;display:flex;align-items:center;gap:4px">
      <input type="checkbox" id="f-out" onchange="applyFilters()">
      Out of Stock Only
    </label>
    <input type="text" id="f-search" placeholder="🔍 Search…"
           oninput="applyFilters()"
      style="padding:4px 8px;border:1px solid var(--border);border-radius:var(--rsm);
             font-size:11.5px;outline:none;width:140px">
  </div>
</div>

<!-- ── Product priority table ─────────────────────── -->
<div class="card" style="padding:0">
  <div class="ch" style="padding:10px 14px">
    <div class="ct">📋 Priority Reorder List</div>
    <div style="display:flex;gap:8px;align-items:center">
      <span id="sel-count" style="font-size:11px;color:var(--text3)">0 selected</span>
      <button class="btn btn-sm" onclick="selectAll()">✓ All</button>
      <button class="btn btn-sm" onclick="clearAll()">✕ Clear</button>
    </div>
  </div>
  <div class="tw" style="overflow-x:auto">
    <table id="req-table">
      <thead>
        <tr>
          <th style="width:36px;text-align:center">
            <input type="checkbox" id="chk-all" onchange="toggleAll(this.checked)">
          </th>
          <th>Product</th>
          <th>Company</th>
          <th style="text-align:center">Stock</th>
          <th style="text-align:center">Alert Qty</th>
          <th style="text-align:center">Sales/30d</th>
          <th style="text-align:center">Avg/Month</th>
          <th style="text-align:center">Suggested Boxes</th>
          <th style="text-align:center">Order Boxes</th>
          <th style="text-align:right">Last Price/Box</th>
          <th>Last Supplier</th>
          <th style="text-align:center">Pending</th>
        </tr>
      </thead>
      <tbody id="req-body">
        <?php
        $rank = 0;
        foreach ($smartProducts as $p):
          $rank++;
          $stock    = (int)$p['stock_pcs'];
          $alert    = (int)$p['low_stock_alert'];
          $qib      = max(1, (int)$p['qty_in_box']);
          $avg      = (float)$p['avg_monthly'];
          $sales30  = (float)$p['sales_30d'];
          $pending  = (int)$p['pending_qty'];

          // Suggested boxes
          $sugPcs   = max($alert * 2, (int)ceil($avg * 1.5)) - $stock;
          $sugBoxes = max(1, (int)ceil($sugPcs / $qib));

          // Status
          $isOut    = $stock <= 0;
          $rowBg    = $isOut ? '#fff5f5' : '#fffbf0';
          $stockBg  = $isOut ? '#fde8e8' : '#fff3dc';
          $stockCl  = $isOut ? 'var(--danger)' : '#ea580c';

          // Priority badge
          $priority = $sales30 > 50 || $isOut ? '🔥 Urgent'
                    : ($sales30 > 20 ? '⚡ High' : '📦 Normal');
          $priCl    = $sales30 > 50 || $isOut ? 'var(--danger)'
                    : ($sales30 > 20 ? '#ea580c' : 'var(--text2)');

          $lastPrice= (float)($p['last_btp_vat'] ?? $p['btp_vat']);
        ?>
        <tr class="req-row"
            style="background:<?= $rowBg ?>"
            data-id="<?= $p['id'] ?>"
            data-supplier-id="<?= htmlspecialchars($p['last_supplier_id'] ?? '') ?>"
            data-supplier="<?= htmlspecialchars($p['last_supplier_name'] ?? '') ?>"
            data-company="<?= htmlspecialchars($p['company_name'] ?? '') ?>"
            data-out="<?= $isOut ? '1' : '0' ?>"
            data-name="<?= htmlspecialchars(strtolower($p['name'])) ?>">
          <td style="text-align:center">
            <input type="checkbox" class="row-chk" data-id="<?= $p['id'] ?>"
                   onchange="onCheck(this)">
          </td>
          <td>
            <div style="font-weight:600;font-size:12px">
              <?= htmlspecialchars($p['name']) ?>
            </div>
            <div style="font-size:10px;color:var(--text3)">
              <?= htmlspecialchars($p['strength'] ?? '') ?>
              <?= $p['form'] ? ' · ' . htmlspecialchars($p['form']) : '' ?>
              <?= $p['rack'] ? ' · Rack: ' . htmlspecialchars($p['rack']) : '' ?>
            </div>
            <span style="font-size:9.5px;color:<?= $priCl ?>;font-weight:700">
              <?= $priority ?>
            </span>
          </td>
          <td style="font-size:11.5px;color:var(--text2)">
            <?= htmlspecialchars($p['company_name'] ?? '—') ?>
          </td>
          <td style="text-align:center">
            <span class="badge" style="background:<?= $stockBg ?>;color:<?= $stockCl ?>;
                  font-weight:700;font-size:12px">
              <?= $stock ?>
            </span>
          </td>
          <td style="text-align:center">
            <span class="badge"><?= $alert ?></span>
          </td>
          <td style="text-align:center;font-weight:600;color:var(--brand)">
            <?= $sales30 > 0 ? number_format($sales30, 0) : '—' ?>
          </td>
          <td style="text-align:center;color:var(--text2)">
            <?= $avg > 0 ? number_format($avg, 0) : '—' ?>
          </td>
          <td style="text-align:center;font-weight:700;color:var(--accent)">
            <?= $sugBoxes ?> box<?= $sugBoxes > 1 ? 'es' : '' ?>
          </td>
          <td style="text-align:center">
            <input type="number" class="order-qty" data-id="<?= $p['id'] ?>"
                   value="<?= $sugBoxes ?>" min="1"
                   style="width:64px;text-align:center"
                   oninput="updateSummary()">
          </td>
          <td class="mono" style="text-align:right;font-size:11.5px">
            <?= $lastPrice > 0 ? $cur . number_format($lastPrice, 2) : '—' ?>
          </td>
          <td style="font-size:11px;color:var(--text2)">
            <?= htmlspecialchars($p['last_supplier_name'] ?? '—') ?>
          </td>
          <td style="text-align:center">
            <?= $pending > 0
              ? "<span class='badge' style='background:#e8eaf6;color:var(--brand)'>$pending pcs</span>"
              : '<span style="color:var(--text3)">—</span>' ?>
          </td>
        </tr>
        <?php endforeach; ?>
        <?php if (!$smartProducts): ?>
        <tr><td colspan="12" class="empty-state">
          ✅ No products below alert level. All stock levels are sufficient.
        </td></tr>
        <?php endif; ?>
      </tbody>
    </table>
  </div>
  <div id="req-footer"
       style="padding:8px 14px;font-size:11px;color:var(--text3);
              border-top:1px solid var(--border)"></div>
</div>

<!-- ── Selected summary panel ─────────────────────── -->
<div id="summary-panel"
     style="display:none;margin-top:12px;padding:14px;
            background:var(--brand-xlight);border:1px solid var(--brand-light);
            border-radius:var(--r)">
  <div style="font-weight:700;color:var(--brand);margin-bottom:10px;font-size:13px">
    📊 Requisition Preview — Grouped by Supplier
  </div>
  <div id="supplier-groups"></div>
  <div style="margin-top:12px;display:flex;gap:8px;align-items:center">
    <button class="btn btn-primary" onclick="createRequisitions()">
      📝 Create Requisitions by Supplier
    </button>
    <span style="font-size:11px;color:var(--text2)">
      Separate requisition will be created per supplier
    </span>
  </div>
</div>

<script>
const CUR      = '<?= addslashes($cur) ?>';
const API      = '../api/requisitions.php';
const REQ_DATE = '<?= $today ?>';
let selected   = {};   // { product_id: { name, suppId, suppName, qty, price, qib } }

// ── Checkbox logic ────────────────────────────────────────────
function onCheck(chk) {
  const row  = chk.closest('tr');
  const id   = parseInt(chk.dataset.id);
  const qty  = parseInt(row.querySelector('.order-qty').value) || 1;
  const price= parseFloat(row.cells[9].textContent.replace(CUR,'').replace(/,/g,'')) || 0;

  if (chk.checked) {
    selected[id] = {
      name:     row.dataset.name,
      suppId:   row.dataset.supplierId,
      suppName: row.dataset.supplier || 'Unknown Supplier',
      company:  row.dataset.company  || '',
      qty,
      price,
      qib:      1,
    };
  } else {
    delete selected[id];
  }
  updateSel();
  updateSummary();
}

function onQtyChange(id) {
  if (selected[id]) {
    const inp = document.querySelector(`.order-qty[data-id="${id}"]`);
    selected[id].qty = Math.max(1, parseInt(inp?.value) || 1);
    updateSummary();
  }
}

// Add oninput listener to all qty fields
document.querySelectorAll('.order-qty').forEach(inp => {
  inp.addEventListener('input', () => {
    const id = parseInt(inp.dataset.id);
    if (selected[id]) {
      selected[id].qty = Math.max(1, parseInt(inp.value) || 1);
      updateSummary();
    }
  });
});

function toggleAll(checked) {
  document.querySelectorAll('.req-row:not([style*="display:none"]) .row-chk')
    .forEach(chk => { if (chk.checked !== checked) { chk.checked = checked; onCheck(chk); } });
}
function selectAll()  { document.getElementById('chk-all').checked = true;  toggleAll(true); }
function clearAll()   { document.getElementById('chk-all').checked = false; toggleAll(false); }

function updateSel() {
  const n = Object.keys(selected).length;
  document.getElementById('sel-count').textContent = `${n} selected`;
  document.getElementById('req-footer').textContent =
    n > 0 ? `${n} product(s) selected for requisition` : '';
}

// ── Filters ───────────────────────────────────────────────────
function applyFilters() {
  const suppId  = document.getElementById('f-supplier').value;
  const company = document.getElementById('f-company').value.toLowerCase();
  const outOnly = document.getElementById('f-out').checked;
  const q       = document.getElementById('f-search').value.toLowerCase();

  document.querySelectorAll('.req-row').forEach(row => {
    const matchSupp    = !suppId  || row.dataset.supplierId === suppId;
    const matchCompany = !company || row.dataset.company.toLowerCase().includes(company);
    const matchOut     = !outOnly || row.dataset.out === '1';
    const matchSearch  = !q      || row.dataset.name.includes(q);
    row.style.display  = (matchSupp && matchCompany && matchOut && matchSearch) ? '' : 'none';
  });
}

// ── Supplier-grouped summary ──────────────────────────────────
function updateSummary() {
  const ids  = Object.keys(selected);
  const panel= document.getElementById('summary-panel');
  const grpEl= document.getElementById('supplier-groups');

  if (!ids.length) { panel.style.display = 'none'; return; }
  panel.style.display = 'block';

  // Group by supplier
  const groups = {};
  ids.forEach(id => {
    const it = selected[id];
    const key = it.suppId || 'unknown';
    if (!groups[key]) groups[key] = { name: it.suppName, items: [] };
    groups[key].items.push({ id, ...it });
  });

  grpEl.innerHTML = Object.entries(groups).map(([suppId, grp]) => {
    const rows = grp.items.map(it => `
      <div style="display:flex;justify-content:space-between;
                  padding:3px 0;font-size:11.5px;border-bottom:1px solid var(--brand-light)">
        <span>${esc(it.name)}</span>
        <span class="mono" style="color:var(--brand)">
          ${it.qty} box(es)
          ${it.price > 0 ? ' · ' + CUR + (it.qty * it.price).toFixed(2) : ''}
        </span>
      </div>`).join('');

    const totalVal = grp.items.reduce((s,it) => s + it.qty*(it.price||0), 0);

    return `<div style="background:var(--surface);border-radius:var(--rsm);
                         padding:10px 12px;margin-bottom:8px;
                         border-left:3px solid var(--brand)">
      <div style="font-weight:700;color:var(--brand);margin-bottom:6px;font-size:12px">
        🏢 ${esc(grp.name)}
        <span style="font-weight:400;color:var(--text3);font-size:11px">
          (${grp.items.length} product(s))
        </span>
        ${totalVal > 0
          ? `<span class="mono" style="float:right;color:var(--brand)">
               Est: ${CUR}${totalVal.toFixed(2)}</span>` : ''}
      </div>
      ${rows}
    </div>`;
  }).join('');
}

// ── Create Requisitions ───────────────────────────────────────
async function createRequisitions() {
  const ids = Object.keys(selected);
  if (!ids.length) { alert('Select at least one product.'); return; }

  // Group by supplier
  const groups = {};
  ids.forEach(id => {
    const it  = selected[id];
    const key = it.suppId || 'no-supplier';
    if (!groups[key]) groups[key] = { suppId: it.suppId, suppName: it.suppName, items: [] };
    const row  = document.querySelector(`tr[data-id="${id}"]`);
    const qty  = parseInt(row?.querySelector('.order-qty')?.value || it.qty);
    const stock= parseInt(row?.cells[3]?.querySelector('.badge')?.textContent || 0);
    const alert= parseInt(row?.cells[4]?.querySelector('.badge')?.textContent || 0);
    const sugg = parseInt(row?.cells[7]?.textContent || qty);
    groups[key].items.push({
      product_id:   parseInt(id),
      product_name: row?.dataset.name || it.name,
      qty_box:      qty,
      current_stock:stock,
      alert_qty:    alert,
      suggested_qty:sugg,
      btp_vat:      it.price,
      total_price:  qty * (it.price || 0),
    });
  });

  const btn = document.querySelector('[onclick="createRequisitions()"]');
  if (btn) { btn.disabled = true; btn.textContent = '⏳ Creating…'; }

  const results = [];
  for (const [, grp] of Object.entries(groups)) {
    const r = await apiPost(API, {
      action:        'save',
      ref:           '',   // auto-generate
      supplier_id:   grp.suppId || '',
      supplier_name: grp.suppName,
      date:          REQ_DATE,
      total_price:   grp.items.reduce((s,it) => s + it.total_price, 0),
      notes:         'Auto-generated from Smart Requisition',
      items:         grp.items,
    });
    results.push({ supplier: grp.suppName, success: r.success, ref: r.ref, error: r.error });
  }

  if (btn) { btn.disabled = false; btn.textContent = '📝 Create Requisitions by Supplier'; }

  const ok  = results.filter(r => r.success);
  const bad = results.filter(r => !r.success);
  let msg   = `✅ ${ok.length} Requisition(s) created:\n` +
              ok.map(r => `  • ${r.ref} — ${r.supplier}`).join('\n');
  if (bad.length) msg += `\n\n⚠ ${bad.length} failed:\n` + bad.map(r => r.error).join('\n');

  alert(msg);
  if (ok.length) location.href = 'req-record.php';
}

function esc(s) {
  return String(s||'').replace(/&/g,'&amp;').replace(/</g,'&lt;')
                      .replace(/>/g,'&gt;').replace(/"/g,'&quot;');
}
</script>

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