import { sql } from "drizzle-orm";
import { db } from "@/db";
import { BarChart } from "@/components/admin";
import { ugx } from "@/lib/format";
export default async function R() {
  const monthly = (await db.execute(sql`select to_char(date_trunc('month', created_at), 'Mon YY') as label, coalesce(sum(total) filter (where payment_status='Paid'),0)::int as value, count(*)::int as n from orders where created_at > now() - interval '12 months' group by 1, date_trunc('month', created_at) order by date_trunc('month', created_at)`)).rows as { label: string; value: number; n: number }[];
  const byMethod = (await db.execute(sql`select payment_method as m, count(*)::int as n, coalesce(sum(total) filter (where payment_status='Paid'),0)::int as v from orders group by 1 order by 3 desc`)).rows as { m: string; n: number; v: number }[];
  const byStatus = (await db.execute(sql`select status as s, count(*)::int as n from orders group by 1 order by 2 desc`)).rows as { s: string; n: number }[];
  const byCat = (await db.execute(sql`select coalesce(c.name,'Uncategorised') as c, sum(oi.qty * oi.price)::int as v from order_items oi join orders o on o.id=oi.order_id and o.payment_status='Paid' left join products p on p.id=oi.product_id left join categories c on c.id=p.category_id group by 1 order by 2 desc`)).rows as { c: string; v: number }[];
  const inv = (await db.execute(sql`select coalesce(sum(stock*price),0)::bigint as v, coalesce(sum(stock),0)::int as u from products where active`)).rows[0] as { v: string; u: number };
  return <div><div className="flex justify-between mb-5"><h1 className="text-2xl font-extrabold">Reports & Analytics</h1><a href="/api/admin/export?type=orders" className="btn btn-outline !py-2">Export orders CSV</a></div>
    <div className="card p-6 mb-5"><h3 className="font-bold mb-4">Paid revenue by month</h3>{monthly.length ? <BarChart data={monthly.map((m) => ({ label: m.label, value: Number(m.value) }))} /> : <p className="text-sm text-slate-500">No data yet.</p>}</div>
    <div className="grid lg:grid-cols-3 gap-5"><div className="card p-6"><h3 className="font-bold mb-3">By payment method</h3>{byMethod.map((r) => <div key={r.m} className="flex justify-between text-sm py-1 border-b"><span className="uppercase">{r.m}</span><span>{r.n} orders · {ugx(r.v)}</span></div>)}</div><div className="card p-6"><h3 className="font-bold mb-3">By status</h3>{byStatus.map((r) => <div key={r.s} className="flex justify-between text-sm py-1 border-b"><span>{r.s}</span><b>{r.n}</b></div>)}</div><div className="card p-6"><h3 className="font-bold mb-3">Sales by category</h3>{byCat.map((r) => <div key={r.c} className="flex justify-between text-sm py-1 border-b"><span>{r.c}</span><b>{ugx(r.v)}</b></div>)}<div className="mt-4 text-sm">Inventory value: <b>{ugx(Number(inv.v))}</b> ({inv.u} units)</div></div></div></div>;
}