<?php
// ============================================================
//  MediCore — Expired Products
//  File: product/expired.php
// ============================================================
$page  = 'product/expired';
$title = 'Expired Products';
require_once '../includes/header.php';

$cur      = $settings['currency'];
$thisMonth= date('Y-m');
$nextMonth= date('Y-m', strtotime('+1 month'));
$in2Month = date('Y-m', strtotime('+2 months'));

// Count summaries
$expiredCount  = (int) db_val(
    "SELECT COUNT(DISTINCT pi.id) FROM purchase_items pi
      WHERE pi.exp != '' AND pi.exp < ?", [$thisMonth]);
$expiringCount = (int) db_val(
    "SELECT COUNT(DISTINCT pi.id) FROM purchase_items pi
      WHERE pi.exp >= ? AND pi.exp <= ?", [$thisMonth, $nextMonth]);
$soon2Count    = (int) db_val(
    "SELECT COUNT(DISTINCT pi.id) FROM purchase_items pi
      WHERE pi.exp > ? AND pi.exp <= ?", [$nextMonth, $in2Month]);

// Fetch all expired + expiring batches
$batches = db_rows(
    "SELECT pi.*,
            p.name         AS product_name,
            p.form,
            p.strength,
            p.rack,
            po.invoice     AS po_invoice,
            po.date        AS po_date,
            s.name         AS supplier_name,
            s.phone        AS supplier_phone
       FROM purchase_items pi
       JOIN products   p  ON p.id  = pi.product_id
       JOIN purchases  po ON po.id = pi.purchase_id
  LEFT JOIN suppliers  s  ON s.id  = po.supplier_id
      WHERE pi.exp != ''
        AND pi.exp <= ?
      ORDER BY pi.exp ASC, p.name ASC",
    [$in2Month]
);

// Separate into buckets
$expired  = array_filter($batches, fn($b) => $b['exp'] <  $thisMonth);
$expiring = array_filter($batches, fn($b) => $b['exp'] >= $thisMonth && $b['exp'] <= $nextMonth);
$soon     = array_filter($batches, fn($b) => $b['exp'] >  $nextMonth && $b['exp'] <= $in2Month);

function renderBatch(array $b, string $cur, string $label, string $bg, string $cl): string {
    $qty = (int)$b['qty_pcs'] + (int)$b['free'];
    return '
    <tr>
      <td>
        <div style="font-weight:600;font-size:12px">' . form_icon_name($b['form'], $b['product_name']) . '</div>
        <div style="font-size:10px;color:var(--text3)">
          ' . htmlspecialchars($b['strength'] ?? '') . '
          ' . ($b['form'] ? ' · ' . htmlspecialchars($b['form']) : '') . '
          ' . ($b['rack'] ? ' · Rack: ' . htmlspecialchars($b['rack']) : '') . '
        </div>
      </td>
      <td style="text-align:center">
        <span class="badge" style="background:' . $bg . ';color:' . $cl . ';
              font-weight:700;font-size:12px">' . htmlspecialchars($b['exp']) . '</span>
      </td>
      <td class="mono" style="text-align:center;font-weight:700;
                               color:' . $cl . '">' . $qty . ' pcs</td>
      <td>
        <div style="font-weight:600;font-size:12px">' . htmlspecialchars($b['supplier_name'] ?? '—') . '</div>
        ' . ($b['supplier_phone'] ? '<div style="font-size:10px;color:var(--text3)">📞 ' . htmlspecialchars($b['supplier_phone']) . '</div>' : '') . '
      </td>
      <td class="mono" style="font-size:11px;color:var(--text3)">
        ' . htmlspecialchars($b['po_invoice']) . '<br>
        <span style="font-size:10px">' . htmlspecialchars($b['po_date']) . '</span>
      </td>
    </tr>';
}
?>

<!-- ── Summary cards ──────────────────────────────── -->
<div style="display:grid;grid-template-columns:repeat(3,1fr);
            gap:10px;margin-bottom:14px">
  <div class="sc" style="border-top:3px solid var(--danger);
       background:<?= $expiredCount > 0 ? '#fff5f5' : 'var(--surface)' ?>">
    <div class="slabel">Expired Batches</div>
    <div class="sval b" style="color:var(--danger);font-size:20px">
      <?= $expiredCount ?>
    </div>
    <div style="font-size:10px;color:var(--danger);margin-top:2px;font-weight:600">
      <?= $expiredCount > 0 ? '⚠ Requires immediate action' : '✅ None expired' ?>
    </div>
  </div>
  <div class="sc" style="border-top:3px solid var(--warn)">
    <div class="slabel">Expiring This Month</div>
    <div class="sval b" style="color:var(--warn)"><?= $expiringCount ?></div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">
      Expires by <?= date('M Y') ?>
    </div>
  </div>
  <div class="sc" style="border-top:3px solid var(--text2)">
    <div class="slabel">Expiring Next Month</div>
    <div class="sval b"><?= $soon2Count ?></div>
    <div style="font-size:10px;color:var(--text3);margin-top:2px">
      Expires by <?= date('M Y', strtotime('+2 months')) ?>
    </div>
  </div>
</div>

<?php if (!$batches): ?>
  <div class="al al-s" style="font-size:13px">
    ✅ No expired or expiring products in the next 2 months.
  </div>
<?php endif; ?>

<!-- ── Expired ────────────────────────────────────── -->
<?php if ($expired): ?>
<div class="card" style="padding:0;margin-bottom:14px;
     border-left:4px solid var(--danger)">
  <div class="ch" style="padding:10px 14px;background:#fff5f5">
    <div class="ct" style="color:var(--danger)">
      🚫 Expired Batches
      <span class="badge" style="background:#fde8e8;color:var(--danger);
            margin-left:6px"><?= count($expired) ?></span>
    </div>
    <div style="font-size:11px;color:var(--danger)">
      Remove from shelf immediately
    </div>
  </div>
  <div class="tw">
    <table>
      <thead><tr>
        <th>Product</th>
        <th style="text-align:center">Expiry</th>
        <th style="text-align:center">Qty in Stock</th>
        <th>Supplier</th>
        <th>PO Invoice</th>
      </tr></thead>
      <tbody>
        <?php foreach ($expired as $b):
          echo renderBatch($b, $cur, 'EXPIRED', '#fde8e8', 'var(--danger)');
        endforeach; ?>
      </tbody>
    </table>
  </div>
</div>
<?php endif; ?>

<!-- ── Expiring this month ────────────────────────── -->
<?php if ($expiring): ?>
<div class="card" style="padding:0;margin-bottom:14px;
     border-left:4px solid var(--warn)">
  <div class="ch" style="padding:10px 14px;background:#fffbf0">
    <div class="ct" style="color:var(--warn)">
      ⚠ Expiring This Month
      <span class="badge" style="background:#fff3dc;color:var(--warn);
            margin-left:6px"><?= count($expiring) ?></span>
    </div>
    <div style="font-size:11px;color:var(--warn)">
      Sell or return before end of <?= date('M Y') ?>
    </div>
  </div>
  <div class="tw">
    <table>
      <thead><tr>
        <th>Product</th>
        <th style="text-align:center">Expiry</th>
        <th style="text-align:center">Qty in Stock</th>
        <th>Supplier</th>
        <th>PO Invoice</th>
      </tr></thead>
      <tbody>
        <?php foreach ($expiring as $b):
          echo renderBatch($b, $cur, 'THIS MONTH', '#fff3dc', 'var(--warn)');
        endforeach; ?>
      </tbody>
    </table>
  </div>
</div>
<?php endif; ?>

<!-- ── Expiring next month ────────────────────────── -->
<?php if ($soon): ?>
<div class="card" style="padding:0;
     border-left:4px solid var(--text2)">
  <div class="ch" style="padding:10px 14px">
    <div class="ct">
      📅 Expiring Next Month
      <span class="badge" style="background:var(--surface2);
            margin-left:6px"><?= count($soon) ?></span>
    </div>
    <div style="font-size:11px;color:var(--text3)">
      Plan ahead — expires in <?= date('M Y', strtotime('+2 months')) ?>
    </div>
  </div>
  <div class="tw">
    <table>
      <thead><tr>
        <th>Product</th>
        <th style="text-align:center">Expiry</th>
        <th style="text-align:center">Qty in Stock</th>
        <th>Supplier</th>
        <th>PO Invoice</th>
      </tr></thead>
      <tbody>
        <?php foreach ($soon as $b):
          echo renderBatch($b, $cur, 'NEXT MONTH', 'var(--surface2)', 'var(--text2)');
        endforeach; ?>
      </tbody>
    </table>
  </div>
</div>
<?php endif; ?>

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