import { Router } from "express";
import { db } from "@workspace/db";
import {
  suppliersTable,
  factoriesTable,
  supplyOrdersTable,
  supplyOrderItemsTable,
  transactionsTable,
  paymentsTable,
  accountsTable,
} from "@workspace/db";
import { eq, sql } from "drizzle-orm";

const router = Router();

router.get("/dashboard", async (req, res) => {
  const [{ totalSuppliers }] = await db.select({ totalSuppliers: sql<number>`count(*)` }).from(suppliersTable);
  const [{ totalFactories }] = await db.select({ totalFactories: sql<number>`count(*)` }).from(factoriesTable);
  const [{ totalSupplyOrders, totalSupplyAmount }] = await db
    .select({
      totalSupplyOrders: sql<number>`count(*)`,
      totalSupplyAmount: sql<number>`coalesce(sum(${supplyOrdersTable.totalAmount}), 0)`,
    })
    .from(supplyOrdersTable);
  const [{ totalReceipts }] = await db
    .select({ totalReceipts: sql<number>`coalesce(sum(${transactionsTable.amount}), 0)` })
    .from(transactionsTable)
    .where(sql`${transactionsTable.type} = 'receipt'`);
  const [{ totalPayments }] = await db
    .select({ totalPayments: sql<number>`coalesce(sum(${transactionsTable.amount}), 0)` })
    .from(transactionsTable)
    .where(sql`${transactionsTable.type} = 'payment'`);
  const [{ cashBalance }] = await db
    .select({ cashBalance: sql<number>`coalesce(sum(${accountsTable.balance}), 0)` })
    .from(accountsTable)
    .where(sql`${accountsTable.type} = 'cash' OR ${accountsTable.type} = 'bank'`);
  const recentRaw = await db
    .select()
    .from(transactionsTable)
    .orderBy(sql`${transactionsTable.date} desc, ${transactionsTable.id} desc`)
    .limit(10);
  const recentTransactions = await Promise.all(
    recentRaw.map(async (t) => {
      const [account] = await db.select().from(accountsTable).where(eq(accountsTable.id, t.accountId));
      let supplierName: string | null = null;
      if (t.supplierId) {
        const [supplier] = await db.select().from(suppliersTable).where(eq(suppliersTable.id, t.supplierId));
        supplierName = supplier?.name ?? null;
      }
      return {
        id: t.id,
        type: t.type,
        date: t.date,
        documentNo: t.documentNo,
        accountId: t.accountId,
        accountName: account?.name ?? "",
        supplierId: t.supplierId ?? null,
        supplierName,
        amount: Number(t.amount),
        notes: t.notes ?? null,
        createdAt: t.createdAt.toISOString(),
      };
    })
  );
  res.json({
    totalSuppliers: Number(totalSuppliers),
    totalFactories: Number(totalFactories),
    totalSupplyOrders: Number(totalSupplyOrders),
    totalSupplyAmount: Number(totalSupplyAmount),
    totalReceipts: Number(totalReceipts),
    totalPayments: Number(totalPayments),
    cashBalance: Number(cashBalance),
    recentTransactions,
  });
});

router.get("/supplier-summary", async (req, res) => {
  const suppliers = await db.select().from(suppliersTable);
  const result = await Promise.all(
    suppliers.map(async (s) => {
      const orders = await db.select().from(supplyOrdersTable).where(eq(supplyOrdersTable.supplierId, s.id));
      const [{ totalAmount }] = await db
        .select({ totalAmount: sql<number>`coalesce(sum(${supplyOrdersTable.totalAmount}), 0)` })
        .from(supplyOrdersTable)
        .where(eq(supplyOrdersTable.supplierId, s.id));
      const [{ totalPaid }] = await db
        .select({ totalPaid: sql<number>`coalesce(sum(${paymentsTable.amount}), 0)` })
        .from(paymentsTable)
        .where(eq(paymentsTable.supplierId, s.id));
      return {
        supplierId: s.id,
        supplierName: s.name,
        totalOrders: orders.length,
        totalAmount: Number(totalAmount),
        totalPaid: Number(totalPaid),
        balance: Number(totalAmount) - Number(totalPaid),
      };
    })
  );
  res.json(result);
});

router.get("/factory-summary", async (req, res) => {
  const factories = await db.select().from(factoriesTable);
  const result = await Promise.all(
    factories.map(async (f) => {
      const orders = await db.select().from(supplyOrdersTable).where(eq(supplyOrdersTable.factoryId, f.id));
      const [{ totalAmount }] = await db
        .select({ totalAmount: sql<number>`coalesce(sum(${supplyOrdersTable.totalAmount}), 0)` })
        .from(supplyOrdersTable)
        .where(eq(supplyOrdersTable.factoryId, f.id));
      const orderIds = orders.map((o) => o.id);
      let totalItems = 0;
      if (orderIds.length > 0) {
        const [{ cnt }] = await db
          .select({ cnt: sql<number>`count(*)` })
          .from(supplyOrderItemsTable)
          .where(sql`${supplyOrderItemsTable.orderId} = ANY(ARRAY[${sql.join(orderIds.map((id) => sql`${id}`), sql`, `)}]::int[])`);
        totalItems = Number(cnt);
      }
      return {
        factoryId: f.id,
        factoryName: f.name,
        totalOrders: orders.length,
        totalAmount: Number(totalAmount),
        totalItems,
      };
    })
  );
  res.json(result);
});

export default router;