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

const router = Router();

// List all suppliers with balance
router.get("/", async (req, res) => {
  const suppliers = await db.select().from(suppliersTable).orderBy(suppliersTable.name);
  // Compute balance: total supply orders - total payments to each supplier
  const result = await Promise.all(
    suppliers.map(async (s) => {
      const ordersSum = await db
        .select({ total: sql<number>`coalesce(sum(${supplyOrdersTable.totalAmount}), 0)` })
        .from(supplyOrdersTable)
        .where(eq(supplyOrdersTable.supplierId, s.id));
      const paymentsSum = await db
        .select({ total: sql<number>`coalesce(sum(${paymentsTable.amount}), 0)` })
        .from(paymentsTable)
        .where(eq(paymentsTable.supplierId, s.id));
      const balance = Number(ordersSum[0]?.total ?? 0) - Number(paymentsSum[0]?.total ?? 0);
      return {
        id: s.id,
        name: s.name,
        phone: s.phone,
        address: s.address,
        notes: s.notes,
        balance,
        createdAt: s.createdAt.toISOString(),
      };
    })
  );
  res.json(result);
});

// Create supplier
router.post("/", async (req, res) => {
  const { name, phone, address, notes } = req.body;
  if (!name) return res.status(400).json({ error: "name required" });
  const [s] = await db.insert(suppliersTable).values({ name, phone, address, notes }).returning();
  res.status(201).json({ id: s.id, name: s.name, phone: s.phone, address: s.address, notes: s.notes, balance: 0, createdAt: s.createdAt.toISOString() });
});

// Get supplier
router.get("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  const [s] = await db.select().from(suppliersTable).where(eq(suppliersTable.id, id));
  if (!s) return res.status(404).json({ error: "Not found" });
  const ordersSum = await db
    .select({ total: sql<number>`coalesce(sum(${supplyOrdersTable.totalAmount}), 0)` })
    .from(supplyOrdersTable)
    .where(eq(supplyOrdersTable.supplierId, id));
  const paymentsSum = await db
    .select({ total: sql<number>`coalesce(sum(${paymentsTable.amount}), 0)` })
    .from(paymentsTable)
    .where(eq(paymentsTable.supplierId, id));
  const balance = Number(ordersSum[0]?.total ?? 0) - Number(paymentsSum[0]?.total ?? 0);
  res.json({ id: s.id, name: s.name, phone: s.phone, address: s.address, notes: s.notes, balance, createdAt: s.createdAt.toISOString() });
});

// Update supplier
router.patch("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  const { name, phone, address, notes } = req.body;
  const updates: Record<string, unknown> = {};
  if (name !== undefined) updates.name = name;
  if (phone !== undefined) updates.phone = phone;
  if (address !== undefined) updates.address = address;
  if (notes !== undefined) updates.notes = notes;
  const [s] = await db.update(suppliersTable).set(updates).where(eq(suppliersTable.id, id)).returning();
  if (!s) return res.status(404).json({ error: "Not found" });
  res.json({ id: s.id, name: s.name, phone: s.phone, address: s.address, notes: s.notes, balance: 0, createdAt: s.createdAt.toISOString() });
});

// Delete supplier
router.delete("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  await db.delete(suppliersTable).where(eq(suppliersTable.id, id));
  res.status(204).end();
});

// Supplier statement
router.get("/../supplier-statement", async (req, res) => {
  // handled by parent router
  res.status(404).end();
});

export default router;