import Link from "next/link";
import { and, desc, eq, gte, lte, sql, ne } from "drizzle-orm";
import { db } from "@/db";
import { orders, customers, products, prescriptions, consultations, payments, orderItems } from "@/db/schema";
import { ugx, fmtDate } from "@/lib/format";
import { BarChart } from "@/components/admin";

const RANGES: Record<string, number> = { today: 1, week: 7, month: 30, year: 365 };

export default async function Dashboard({ searchParams }: { searchParams: Promise<{ range?: string; from?: string; to?: string }> }) {
  const sp = await searchParams;
  const range = sp.range || "month";
  const from = sp.from ? new Date(sp.from) : new Date(Date.now() - (RANGES[range] || 30) * 864e5);
  const to = sp.to ? new Date(sp.to + "T23:59:59") : new Date();
  const inRange = and(gte(orders.createdAt, from), lte(orders.createdAt, to));
  const one = async <T,>(q: Promise<T[]>) => (await q)[0];
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const [rev, todayRev, cnt, cust, prod, rx, tc, pay] = await Promise.all([
    one(db.select({ v: sql<number>`coalesce(sum(${orders.total}),0)::int`, n: sql<number>`count(*)::int` }).from(orders).where(and(inRange, eq(orders.paymentStatus, "Paid")))),
    one(db.select({ v: sql<number>`coalesce(sum(${orders.total}),0)::int` }).from(orders).where(and(gte(orders.createdAt, today), eq(orders.paymentStatus, "Paid")))),
    one(db.select({ total: sql<number>`count(*)::int`, new: sql<number>`count(*) filter (where ${orders.status}='New')::int`, pending: sql<number>`count(*) filter (where ${orders.status} in ('New','Awaiting Payment','Prescription Review Required','Payment Confirmed','Processing','Packed','Dispatched','Out for Delivery'))::int`, done: sql<number>`count(*) filter (where ${orders.status}='Delivered')::int` }).from(orders).where(inRange)),
    one(db.select({ n: sql<number>`count(*)::int` }).from(customers)),
    one(db.select({ active: sql<number>`count(*) filter (where ${products.active})::int`, low: sql<number>`count(*) filter (where ${products.active} and ${products.stock} > 0 and ${products.stock} <= ${products.lowStock})::int`, out: sql<number>`count(*) filter (where ${products.active} and ${products.stock} <= 0)::int`, exp: sql<number>`count(*) filter (where ${products.expiryDate} is not null and ${products.expiryDate} <> '' and ${products.expiryDate}::date < current_date + 90)::int` }).from(products)),
    one(db.select({ n: sql<number>`count(*)::int` }).from(prescriptions).where(eq(prescriptions.status, "Pending Review"))),
    one(db.select({ n: sql<number>`count(*)::int` }).from(consultations).where(eq(consultations.status, "Requested"))),
    one(db.select({ n: sql<number>`count(*)::int` }).from(payments).where(and(gte(payments.createdAt, from), lte(payments.createdAt, to)))),
  ]);
  const daily = await db.execute(sql`select to_char(d, 'DD Mon') as label, coalesce(sum(o.total) filter (where o.payment_status='Paid'),0)::int as value from generate_series(current_date - 13, current_date, '1 day') d left join orders o on o.created_at::date = d group by d order by d`);
  const recent = await db.select().from(orders).orderBy(desc(orders.id)).limit(8);
  const best = await db.select({ name: orderItems.name, qty: sql<number>`sum(${orderItems.qty})::int`, rev: sql<number>`sum(${orderItems.qty} * ${orderItems.price})::int` }).from(orderItems).innerJoin(orders, eq(orders.id, orderItems.orderId)).where(and(inRange, ne(orders.status, "Cancelled"))).groupBy(orderItems.name).orderBy(desc(sql`sum(${orderItems.qty})`)).limit(6);
  const cards: [string, string | number, string, string?][] = [
    ["Total Revenue", ugx(rev.v), "bg-brand text-white"], ["Today's Revenue", ugx(todayRev.v), "bg-royal text-white"], ["Total Orders", cnt.total, ""], ["New Orders", cnt.new, "", "/admin/orders?status=New"],
    ["Pending Orders", cnt.pending, "", "/admin/orders"], ["Completed Orders", cnt.done, ""], ["Total Customers", cust.n, "", "/admin/customers"], ["Active Products", prod.active, "", "/admin/products"],
    ["Low Stock", prod.low, "text-amber-600", "/admin/inventory?filter=low"], ["Out of Stock", prod.out, "text-red-600", "/admin/inventory?filter=out"], ["Expiring ≤ 90 days", prod.exp, "text-red-600", "/admin/inventory?filter=expiring"], ["Pending Prescriptions", rx.n, "text-royal", "/admin/prescriptions"],
    ["Pending Teleconsultations", tc.n, "text-royal", "/admin/consultations"], ["Payment Transactions", pay.n, "", "/admin/payments"],
  ];
  return (
    <div>
      <div className="flex flex-wrap items-end justify-between gap-4 mb-6">
        <div><h1 className="text-2xl font-extrabold">Dashboard</h1><p className="text-sm text-slate-500">{fmtDate(from)} – {fmtDate(to)}</p></div>
        <form className="flex flex-wrap gap-2 items-center">
          {Object.keys(RANGES).map((r) => <Link key={r} href={`/admin?range=${r}`} className={`px-3 py-1.5 rounded-full text-xs font-semibold capitalize ${range === r && !sp.from ? "bg-brand text-white" : "bg-white border"}`}>{r}</Link>)}
          <input type="date" name="from" defaultValue={sp.from} className="input !w-auto !py-1.5 !text-xs" /><input type="date" name="to" defaultValue={sp.to} className="input !w-auto !py-1.5 !text-xs" /><button className="btn btn-blue !py-1.5 !text-xs">Custom</button>
        </form>
      </div>
      <div className="grid grid-cols-2 md:grid-cols-4 xl:grid-cols-7 gap-3">
        {cards.map(([l, v, cls, href]) => {
          const inner = <div className={`card p-4 h-full hover:shadow-md transition ${cls?.includes("bg-") ? cls + " border-0" : ""}`}><div className={`text-[11px] font-semibold ${cls?.includes("bg-") ? "text-white/80" : "text-slate-500"}`}>{l}</div><div className={`text-xl font-extrabold mt-1 ${cls && !cls.includes("bg-") ? cls : ""}`}>{v}</div></div>;
          return href ? <Link key={l} href={href}>{inner}</Link> : <div key={l}>{inner}</div>;
        })}
      </div>
      <div className="grid xl:grid-cols-[2fr_1fr] gap-5 mt-5">
        <div className="card p-6"><h3 className="font-bold mb-4">Paid revenue – last 14 days</h3><BarChart data={(daily.rows as { label: string; value: number }[]).map((r) => ({ label: r.label.split(" ")[0], value: Number(r.value) }))} /></div>
        <div className="card p-6"><h3 className="font-bold mb-4">Best selling products</h3>{best.length ? best.map((b, i) => <div key={b.name} className="flex items-center gap-3 py-2 border-b last:border-0 text-sm"><span className="w-6 h-6 rounded-full bg-mint text-brand text-xs font-bold grid place-items-center">{i + 1}</span><span className="flex-1 line-clamp-1">{b.name}</span><span className="text-slate-500">{b.qty} sold</span></div>) : <p className="text-sm text-slate-500">No sales in this period.</p>}</div>
      </div>
      <div className="card p-6 mt-5 overflow-x-auto"><div className="flex justify-between mb-3"><h3 className="font-bold">Recent orders</h3><Link href="/admin/orders" className="text-sm text-brand font-semibold">View all</Link></div>
        <table className="w-full text-sm"><thead><tr className="text-left text-slate-500 text-xs"><th className="py-2">Order</th><th>Customer</th><th>Date</th><th>Status</th><th>Payment</th><th className="text-right">Total</th></tr></thead>
          <tbody>{recent.map((o) => <tr key={o.id} className="border-t"><td className="py-2.5"><Link href={`/admin/orders/${o.id}`} className="text-brand font-semibold">{o.number}</Link></td><td>{o.name}</td><td>{fmtDate(o.createdAt)}</td><td><span className="px-2 py-0.5 rounded-full bg-softblue text-royal text-xs font-semibold">{o.status}</span></td><td>{o.paymentStatus}</td><td className="text-right font-semibold">{ugx(o.total)}</td></tr>)}</tbody></table>
        {!recent.length && <p className="text-sm text-slate-500 py-4">No orders yet.</p>}
      </div>
    </div>
  );
}