import { createRouter, publicQuery } from "../middleware";
import { getDb } from "../queries/connection";
import { orders, products, contacts, subscribers, activityLogs, users } from "@db/schema";
import { sql, desc } from "drizzle-orm";

export const analyticsRouter = createRouter({
  dashboard: publicQuery.query(async () => {
    const db = getDb();
    const [totalOrders] = await db.select({ count: sql<number>`count(*)` }).from(orders);
    const [totalRevenue] = await db.select({ sum: sql<number>`COALESCE(SUM(total_amount), 0)` }).from(orders);
    const [totalProducts] = await db.select({ count: sql<number>`count(*)` }).from(products);
    const [totalUsers] = await db.select({ count: sql<number>`count(*)` }).from(users);
    const [totalContacts] = await db.select({ count: sql<number>`count(*)` }).from(contacts);
    const [totalSubscribers] = await db.select({ count: sql<number>`count(*)` }).from(subscribers);
    const [totalSales] = await db.select({ count: sql<number>`count(*)` }).from(activityLogs).where(sql`type = 'sale'`);
    const [pendingOrders] = await db.select({ count: sql<number>`count(*)` }).from(orders).where(sql`status = 'pending'`);

    // Recent orders
    const recentOrders = await db.select().from(orders).orderBy(desc(orders.createdAt)).limit(10);

    // Top selling products
    const topProducts = await db
      .select({
        name: products.name,
        soldCount: products.soldCount,
        price: products.price,
      })
      .from(products)
      .orderBy(desc(products.soldCount))
      .limit(10);

    // Sales by day (last 7 days)
    const salesByDay = await db
      .select({
        date: sql<string>`DATE(created_at)`,
        count: sql<number>`count(*)`,
        revenue: sql<number>`COALESCE(SUM(amount), 0)`,
      })
      .from(activityLogs)
      .where(sql`type = 'sale' AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)`)
      .groupBy(sql`DATE(created_at)`)
      .orderBy(sql`DATE(created_at)`);

    return {
      stats: {
        totalOrders: totalOrders?.count || 0,
        totalRevenue: totalRevenue?.sum || 0,
        totalProducts: totalProducts?.count || 0,
        totalUsers: totalUsers?.count || 0,
        totalContacts: totalContacts?.count || 0,
        totalSubscribers: totalSubscribers?.count || 0,
        totalSales: totalSales?.count || 0,
        pendingOrders: pendingOrders?.count || 0,
      },
      recentOrders,
      topProducts,
      salesByDay,
    };
  }),
});