const { Router } = require("express");
const prisma = require("../prisma");
const { requireAuth, requirePermissions } = require("../middleware/auth");
const { requireActiveBranch } = require("../middleware/branch");

const router = Router();

router.get("/dashboard", requireAuth, requireActiveBranch, async (req, res, next) => {
  try {
    const branchId = req.activeBranchId;
    const now = new Date();
    const todayStart = new Date(now.getFullYear(), now.getMonth(), now.getDate());
    const todayEnd = new Date(todayStart);
    todayEnd.setDate(todayEnd.getDate() + 1);

    const saleWhere = { branchId, status: "Completed" };
    const expenseWhere = { branchId, status: { in: ["Approved", "Paid"] } };

    const [
      totalSalesAgg,
      dailyRevenueAgg,
      totalExpensesAgg,
      cogsRows,
      topProducts,
      recentSales,
      salesCount,
      productCount,
      customerCount,
    ] = await Promise.all([
      prisma.sale.aggregate({
        _sum: { total: true, subtotal: true },
        _count: true,
        where: saleWhere,
      }),
      prisma.sale.aggregate({
        _sum: { total: true },
        _count: true,
        where: { ...saleWhere, saleDate: { gte: todayStart, lt: todayEnd } },
      }),
      prisma.expense.aggregate({
        _sum: { amount: true },
        where: expenseWhere,
      }),
      prisma.$queryRaw`
        SELECT COALESCE(SUM(sl.quantity * COALESCE(p."costPrice", 0)), 0)::float AS cogs
        FROM "SaleLine" sl
        INNER JOIN "Sale" s ON s.id = sl."saleId"
        INNER JOIN "Product" p ON p.id = sl."productId"
        WHERE s.status = 'Completed'
          AND s."branchId" = ${branchId}
          AND s."deletedAt" IS NULL
      `,
      prisma.saleLine.groupBy({
        by: ["productId"],
        _sum: { quantity: true },
        where: { sale: saleWhere },
        orderBy: { _sum: { quantity: "desc" } },
        take: 5,
      }),
      prisma.sale.findMany({
        where: { branchId },
        take: 10,
        orderBy: { createdAt: "desc" },
        include: {
          customer: { select: { id: true, name: true } },
          warehouse: { select: { id: true, name: true } },
        },
      }),
      prisma.sale.count({ where: { branchId } }),
      prisma.product.count({ where: { branchId, deletedAt: null } }),
      prisma.customer.count({ where: { branchId, deletedAt: null } }),
    ]);

    let topProductDetails = [];
    if (topProducts.length > 0) {
      const ids = topProducts.map((tp) => tp.productId);
      const products = await prisma.product.findMany({
        where: { id: { in: ids }, branchId },
        select: { id: true, name: true, sku: true, quantity: true, images: true },
      });
      const productMap = Object.fromEntries(products.map((p) => [p.id, p]));
      topProductDetails = topProducts.map((tp) => {
        const p = productMap[tp.productId] || {};
        return {
          productId: tp.productId,
          name: p.name || "Unknown",
          sku: p.sku || null,
          image: (Array.isArray(p.images) && p.images[0]) || null,
          unitsSold: tp._sum.quantity || 0,
          currentStock: p.quantity ?? 0,
        };
      });
    }

    const totalSales = totalSalesAgg._sum.total || 0;
    const salesSubtotal = totalSalesAgg._sum.subtotal || 0;
    const dailyRevenue = dailyRevenueAgg._sum.total || 0;
    const totalExpenses = totalExpensesAgg._sum.amount || 0;
    const costOfGoodsSold = Number(cogsRows[0]?.cogs) || 0;
    const grossProfit = salesSubtotal - costOfGoodsSold;
    const netProfit = grossProfit - totalExpenses;

    const last30 = [];
    for (let i = 29; i >= 0; i--) {
      const d = new Date(now);
      d.setDate(d.getDate() - i);
      last30.push(new Date(d.getFullYear(), d.getMonth(), d.getDate()));
    }

    const salesTrendRaw = await prisma.$queryRawUnsafe(
      `SELECT DATE("saleDate") as day, COALESCE(SUM(total), 0) as revenue
       FROM "Sale"
       WHERE status = 'Completed' AND "saleDate" >= $1 AND "branchId" = $2
       GROUP BY DATE("saleDate")
       ORDER BY day`,
      last30[0],
      branchId
    );
    const trendMap = {};
    for (const row of salesTrendRaw) {
      const key = new Date(row.day).toISOString().slice(0, 10);
      trendMap[key] = Number(row.revenue) || 0;
    }
    const salesTrend = last30.map((d) => {
      const key = d.toISOString().slice(0, 10);
      return { date: key, revenue: trendMap[key] || 0 };
    });

    return res.json({
      totalSales,
      salesSubtotal,
      dailyRevenue,
      dailySalesCount: dailyRevenueAgg._count || 0,
      totalExpenses,
      costOfGoodsSold,
      grossProfit,
      netProfit,
      salesCount,
      productCount,
      customerCount,
      topProducts: topProductDetails,
      recentSales: recentSales.map((s) => ({
        id: s.id,
        number: s.number,
        status: s.status,
        total: s.total,
        saleDate: s.saleDate,
        customerName: s.customer?.name || null,
        warehouseName: s.warehouse?.name || null,
      })),
      salesTrend,
    });
  } catch (err) {
    return next(err);
  }
});

// Global admin overview across all branches
router.get("/overview", requireAuth, requirePermissions(["admin:*"]), async (req, res, next) => {
  try {
    const now = new Date();
    const todayStart = new Date(now.getFullYear(), now.getMonth(), now.getDate());
    const todayEnd = new Date(todayStart);
    todayEnd.setDate(todayEnd.getDate() + 1);

    const saleWhere = { status: "Completed" };
    const expenseWhere = { status: { in: ["Approved", "Paid"] } };

    const [totalSalesAgg, dailyRevenueAgg, totalExpensesAgg, cogsRows, branchRows] = await Promise.all([
      prisma.sale.aggregate({
        _sum: { total: true, subtotal: true },
        where: saleWhere,
      }),
      prisma.sale.aggregate({
        _sum: { total: true },
        where: { ...saleWhere, saleDate: { gte: todayStart, lt: todayEnd } },
      }),
      prisma.expense.aggregate({
        _sum: { amount: true },
        where: expenseWhere,
      }),
      prisma.$queryRaw`
        SELECT COALESCE(SUM(sl.quantity * COALESCE(p."costPrice", 0)), 0)::float AS cogs
        FROM "SaleLine" sl
        INNER JOIN "Sale" s ON s.id = sl."saleId"
        INNER JOIN "Product" p ON p.id = sl."productId"
        WHERE s.status = 'Completed'
          AND s."deletedAt" IS NULL
      `,
      prisma.$queryRaw`
        SELECT
          b.id,
          b.name,
          COALESCE(SUM(CASE WHEN s.status = 'Completed' THEN s.total END), 0)::float AS "totalSales",
          COALESCE(
            SUM(
              CASE
                WHEN s.status = 'Completed'
                  AND s."saleDate" >= ${todayStart}
                  AND s."saleDate" < ${todayEnd}
                THEN s.total
                ELSE 0
              END
            ),
            0
          )::float AS "dailyRevenue",
          COALESCE(
            (
              SELECT SUM(e.amount)
              FROM "Expense" e
              WHERE e."branchId" = b.id
                AND e.status IN ('Approved', 'Paid')
            ),
            0
          )::float AS "totalExpenses"
        FROM "Branch" b
        LEFT JOIN "Sale" s
          ON s."branchId" = b.id
         AND s."deletedAt" IS NULL
        GROUP BY b.id, b.name
        ORDER BY "totalSales" DESC
      `,
    ]);

    const totalSales = totalSalesAgg._sum.total || 0;
    const salesSubtotal = totalSalesAgg._sum.subtotal || 0;
    const dailyRevenue = dailyRevenueAgg._sum.total || 0;
    const totalExpenses = totalExpensesAgg._sum.amount || 0;
    const costOfGoodsSold = Number(cogsRows[0]?.cogs) || 0;
    const grossProfit = salesSubtotal - costOfGoodsSold;
    const netProfit = grossProfit - totalExpenses;

    const last30 = [];
    for (let i = 29; i >= 0; i--) {
      const d = new Date(now);
      d.setDate(d.getDate() - i);
      last30.push(new Date(d.getFullYear(), d.getMonth(), d.getDate()));
    }

    const salesTrendRaw = await prisma.$queryRawUnsafe(
      `SELECT DATE("saleDate") as day, COALESCE(SUM(total), 0) as revenue
       FROM "Sale"
       WHERE status = 'Completed' AND "saleDate" >= $1
       GROUP BY DATE("saleDate")
       ORDER BY day`,
      last30[0]
    );
    const trendMap = {};
    for (const row of salesTrendRaw) {
      const key = new Date(row.day).toISOString().slice(0, 10);
      trendMap[key] = Number(row.revenue) || 0;
    }
    const salesTrend = last30.map((d) => {
      const key = d.toISOString().slice(0, 10);
      return { date: key, revenue: trendMap[key] || 0 };
    });

    return res.json({
      totalSales,
      salesSubtotal,
      dailyRevenue,
      totalExpenses,
      costOfGoodsSold,
      grossProfit,
      netProfit,
      branchSummaries: branchRows.map((b) => ({
        branchId: b.id,
        branchName: b.name,
        totalSales: Number(b.totalSales) || 0,
        dailyRevenue: Number(b.dailyRevenue) || 0,
        totalExpenses: Number(b.totalExpenses) || 0,
      })),
      salesTrend,
    });
  } catch (err) {
    return next(err);
  }
});

router.get("/reports", requireAuth, requireActiveBranch, async (req, res, next) => {
  try {
    const branchId = req.activeBranchId;
    const { type = "sales", from, to } = req.query;

    const dateFilter = {};
    if (from) dateFilter.gte = new Date(from);
    if (to) {
      const toDate = new Date(to);
      toDate.setDate(toDate.getDate() + 1);
      dateFilter.lt = toDate;
    }
    const hasDateFilter = Object.keys(dateFilter).length > 0;

    if (type === "sales") {
      const where = { branchId, status: "Completed" };
      if (hasDateFilter) where.saleDate = dateFilter;

      const [rows, agg] = await Promise.all([
        prisma.sale.findMany({
          where,
          orderBy: { saleDate: "desc" },
          include: {
            customer: { select: { id: true, name: true } },
            warehouse: { select: { id: true, name: true } },
            paymentMethod: { select: { id: true, name: true } },
          },
        }),
        prisma.sale.aggregate({
          _sum: { total: true, subtotal: true, taxTotal: true },
          _count: true,
          where,
        }),
      ]);

      return res.json({
        type: "sales",
        summary: {
          count: agg._count || 0,
          subtotal: agg._sum.subtotal || 0,
          taxTotal: agg._sum.taxTotal || 0,
          total: agg._sum.total || 0,
        },
        rows: rows.map((r) => ({
          id: r.id,
          number: r.number,
          date: r.saleDate,
          customerName: r.customer?.name || "Walk-in",
          warehouseName: r.warehouse?.name || "—",
          paymentMethod: r.paymentMethod?.name || "—",
          subtotal: r.subtotal,
          tax: r.taxTotal,
          total: r.total,
          status: r.status,
        })),
      });
    }

    if (type === "purchases") {
      const where = { branchId, status: "Received" };
      if (hasDateFilter) where.purchaseDate = dateFilter;

      const [rows, agg] = await Promise.all([
        prisma.purchase.findMany({
          where,
          orderBy: { purchaseDate: "desc" },
          include: {
            supplier: { select: { id: true, name: true } },
            warehouse: { select: { id: true, name: true } },
          },
        }),
        prisma.purchase.aggregate({
          _sum: { total: true, subtotal: true, taxTotal: true },
          _count: true,
          where,
        }),
      ]);

      return res.json({
        type: "purchases",
        summary: {
          count: agg._count || 0,
          subtotal: agg._sum.subtotal || 0,
          taxTotal: agg._sum.taxTotal || 0,
          total: agg._sum.total || 0,
        },
        rows: rows.map((r) => ({
          id: r.id,
          number: r.number,
          date: r.purchaseDate,
          supplierName: r.supplier?.name || "—",
          warehouseName: r.warehouse?.name || "—",
          subtotal: r.subtotal,
          tax: r.taxTotal,
          total: r.total,
          status: r.status,
        })),
      });
    }

    if (type === "expenses") {
      const where = { branchId, status: { in: ["Approved", "Paid"] } };
      if (hasDateFilter) where.expenseDate = dateFilter;

      const [rows, agg] = await Promise.all([
        prisma.expense.findMany({
          where,
          orderBy: { expenseDate: "desc" },
          include: {
            category: { select: { id: true, name: true } },
          },
        }),
        prisma.expense.aggregate({
          _sum: { amount: true },
          _count: true,
          where,
        }),
      ]);

      return res.json({
        type: "expenses",
        summary: {
          count: agg._count || 0,
          total: agg._sum.amount || 0,
        },
        rows: rows.map((r) => ({
          id: r.id,
          reference: r.reference,
          title: r.title,
          date: r.expenseDate,
          categoryName: r.category?.name || "—",
          amount: r.amount,
          status: r.status,
        })),
      });
    }

    return res.status(400).json({ error: "Invalid report type. Use: sales, purchases, expenses" });
  } catch (err) {
    return next(err);
  }
});

module.exports = router;
