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

const router = Router();

async function formatTransaction(t: typeof transactionsTable.$inferSelect) {
  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(),
  };
}

router.get("/", async (req, res) => {
  const { type, accountId, from, to } = req.query as Record<string, string>;
  let rows = await db.select().from(transactionsTable).orderBy(sql`${transactionsTable.date} desc, ${transactionsTable.id} desc`);
  if (type) rows = rows.filter((r) => r.type === type);
  if (accountId) rows = rows.filter((r) => r.accountId === parseInt(accountId));
  if (from) rows = rows.filter((r) => r.date >= from);
  if (to) rows = rows.filter((r) => r.date <= to);
  const result = await Promise.all(rows.map(formatTransaction));
  res.json(result);
});

router.post("/", async (req, res) => {
  const { type, date, documentNo, accountId, supplierId, amount, notes } = req.body;
  if (!type || !date || !documentNo || !accountId || !amount) {
    return res.status(400).json({ error: "Missing required fields" });
  }
  const [t] = await db
    .insert(transactionsTable)
    .values({
      type,
      date,
      documentNo,
      accountId: parseInt(accountId),
      supplierId: supplierId ? parseInt(supplierId) : null,
      amount: String(amount),
      notes,
    })
    .returning();
  // Update account balance
  if (type === "receipt") {
    await db
      .update(accountsTable)
      .set({ balance: sql`${accountsTable.balance} + ${String(amount)}` })
      .where(eq(accountsTable.id, parseInt(accountId)));
  } else {
    await db
      .update(accountsTable)
      .set({ balance: sql`${accountsTable.balance} - ${String(amount)}` })
      .where(eq(accountsTable.id, parseInt(accountId)));
  }
  res.status(201).json(await formatTransaction(t));
});

router.get("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  const [t] = await db.select().from(transactionsTable).where(eq(transactionsTable.id, id));
  if (!t) return res.status(404).json({ error: "Not found" });
  res.json(await formatTransaction(t));
});

router.patch("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  const { date, documentNo, accountId, supplierId, amount, notes } = req.body;
  const updates: Record<string, unknown> = {};
  if (date !== undefined) updates.date = date;
  if (documentNo !== undefined) updates.documentNo = documentNo;
  if (accountId !== undefined) updates.accountId = parseInt(accountId);
  if (supplierId !== undefined) updates.supplierId = supplierId ? parseInt(supplierId) : null;
  if (amount !== undefined) updates.amount = String(amount);
  if (notes !== undefined) updates.notes = notes;
  const [t] = await db.update(transactionsTable).set(updates).where(eq(transactionsTable.id, id)).returning();
  if (!t) return res.status(404).json({ error: "Not found" });
  res.json(await formatTransaction(t));
});

router.delete("/:id", async (req, res) => {
  const id = parseInt(req.params.id);
  const [t] = await db.select().from(transactionsTable).where(eq(transactionsTable.id, id));
  if (t) {
    // Reverse account balance effect
    if (t.type === "receipt") {
      await db
        .update(accountsTable)
        .set({ balance: sql`${accountsTable.balance} - ${String(t.amount)}` })
        .where(eq(accountsTable.id, t.accountId));
    } else {
      await db
        .update(accountsTable)
        .set({ balance: sql`${accountsTable.balance} + ${String(t.amount)}` })
        .where(eq(accountsTable.id, t.accountId));
    }
    await db.delete(transactionsTable).where(eq(transactionsTable.id, id));
  }
  res.status(204).end();
});

export default router;