import { asc, desc, eq, sql } from "drizzle-orm";
import { db } from "@/db";
import { products, stockMovements } from "@/db/schema";
import { adjustStock } from "@/app/admin/actions";
import { fmtDate } from "@/lib/format";
export default async function Inv({ searchParams }: { searchParams: Promise<{ filter?: string }> }) {
  const { filter } = await searchParams;
  const all = await db.select().from(products).where(eq(products.active, true)).orderBy(asc(products.stock));
  const soon = new Date(Date.now() + 90 * 864e5).toISOString().slice(0, 10); const today = new Date().toISOString().slice(0, 10);
  const rows = all.filter((p) => filter === "low" ? p.stock > 0 && p.stock <= p.lowStock : filter === "out" ? p.stock <= 0 : filter === "expiring" ? !!p.expiryDate && p.expiryDate <= soon : true);
  const reserved = await db.execute(sql`select oi.product_id, sum(oi.qty)::int as q from order_items oi join orders o on o.id = oi.order_id where o.status in ('New','Awaiting Payment','Prescription Review Required','Payment Confirmed','Processing','Packed') group by oi.product_id`);
  const res = new Map((reserved.rows as { product_id: number; q: number }[]).map((r) => [r.product_id, r.q]));
  const mv = await db.select({ m: stockMovements, name: products.name }).from(stockMovements).leftJoin(products, eq(products.id, stockMovements.productId)).orderBy(desc(stockMovements.id)).limit(25);
  return (
    <div><h1 className="text-2xl font-extrabold mb-4">Inventory</h1>
      <div className="flex gap-2 mb-4 text-xs">{[["", "All"], ["low", "Low stock"], ["out", "Out of stock"], ["expiring", "Expired / expiring ≤90d"]].map(([k, l]) => <a key={k} href={`/admin/inventory${k ? "?filter=" + k : ""}`} className={`px-3 py-1.5 rounded-full font-semibold ${filter === k || (!filter && !k) ? "bg-brand text-white" : "bg-white border"}`}>{l}</a>)}</div>
      <div className="card overflow-x-auto"><table className="w-full text-sm"><thead className="bg-cloud text-xs text-slate-500 text-left"><tr><th className="p-3">Product</th><th>SKU</th><th>Available</th><th>Reserved (open orders)</th><th>Batch</th><th>Expiry</th><th>Supplier</th><th className="p-3">Adjust</th></tr></thead><tbody>
        {rows.map((p) => { const expired = p.expiryDate && p.expiryDate <= today; return <tr key={p.id} className="border-t"><td className="p-3 font-semibold">{p.name}{expired && <span className="ml-2 text-[10px] bg-red-100 text-red-700 px-1.5 py-0.5 rounded">EXPIRED – hidden from store</span>}</td><td>{p.sku}</td><td className={p.stock <= 0 ? "text-red-600 font-bold" : p.stock <= p.lowStock ? "text-amber-600 font-bold" : ""}>{p.stock}</td><td>{res.get(p.id) ?? 0}</td><td>{p.batchNo}</td><td className={expired ? "text-red-600 font-bold" : ""}>{p.expiryDate}</td><td>{p.supplier}</td>
          <td className="p-3"><form action={adjustStock} className="flex gap-1"><input type="hidden" name="id" value={p.id} /><input name="change" type="number" placeholder="±qty" className="input !w-20 !py-1 !text-xs" required /><input name="reason" placeholder="Reason / supplier ref" className="input !w-36 !py-1 !text-xs" /><input name="batchNo" placeholder="Batch" className="input !w-20 !py-1 !text-xs" /><input name="expiryDate" type="date" className="input !w-32 !py-1 !text-xs" /><button className="btn btn-green !py-1 !px-3 !text-xs">OK</button></form></td></tr>; })}
      </tbody></table></div>
      <div className="card p-5 mt-5"><h3 className="font-bold mb-3">Recent stock movements</h3>{mv.map(({ m, name }) => <div key={m.id} className="flex justify-between text-sm py-1.5 border-b"><span>{name} – {m.reason}</span><span className="text-slate-400 text-xs">{fmtDate(m.createdAt)}</span><b className={m.change > 0 ? "text-brand" : "text-red-600"}>{m.change > 0 ? "+" : ""}{m.change}</b></div>)}</div>
    </div>
  );
}