import { Router, type IRouter } from "express";
import { eq, asc, inArray, sql } from "drizzle-orm";
import { db } from "@workspace/db";
import {
  servicesTable,
  serviceCategoriesTable,
  serviceOrdersTable,
  auditLogsTable,
  usersTable,
} from "@workspace/db";
import { requireAuth, requireAdmin } from "../middlewares/auth";
import {
  CreateServiceBody,
  UpdateServiceBody,
  GetServiceParams,
  UpdateServiceParams,
  DeleteServiceParams,
  ReorderServicesBody,
} from "@workspace/api-zod";

const router: IRouter = Router();

router.get("/services", requireAuth, async (_req, res): Promise<void> => {
  const services = await db
    .select()
    .from(servicesTable)
    .orderBy(asc(servicesTable.sortOrder), asc(servicesTable.nameEn));

  if (services.length === 0) {
    res.json([]);
    return;
  }

  // Compute orderCount per service so the admin delete dialog can warn
  // before the first click. The lazy 409 fallback in DELETE /services/:id
  // remains as a safety net.
  const serviceIds = services.map((s) => s.id);
  const orderRows = await db
    .select({
      serviceId: serviceOrdersTable.serviceId,
      count: sql<number>`count(*)::int`,
    })
    .from(serviceOrdersTable)
    .where(inArray(serviceOrdersTable.serviceId, serviceIds))
    .groupBy(serviceOrdersTable.serviceId);
  const orderMap = new Map(orderRows.map((r) => [r.serviceId, r.count]));

  res.json(
    services.map((s) => ({
      ...s,
      orderCount: orderMap.get(s.id) ?? 0,
    })),
  );
});

router.post("/services", requireAdmin, async (req, res): Promise<void> => {
  const parsed = CreateServiceBody.safeParse(req.body);
  if (!parsed.success) {
    res.status(400).json({ error: parsed.error.message });
    return;
  }

  const [service] = await db.insert(servicesTable).values(parsed.data).returning();
  res.status(201).json(service);
});

router.get("/service-categories", requireAuth, async (_req, res): Promise<void> => {
  const categories = await db
    .select()
    .from(serviceCategoriesTable)
    .orderBy(asc(serviceCategoriesTable.sortOrder));
  res.json(categories);
});

// Bulk reorder. Body carries the desired full ordering as a list of
// service ids; the server validates it covers the entire catalogue
// exactly once (no missing/duplicate/unknown ids) and then rewrites
// `sort_order` to the 0-based index. Wrapped in a transaction so the
// catalogue never observes a partially-applied order. Declared BEFORE
// the `/services/:id` routes so Express doesn't try to parse "reorder"
// as a numeric id and return a 400 from GetServiceParams.
router.patch("/services/reorder", requireAdmin, async (req, res): Promise<void> => {
  const parsed = ReorderServicesBody.safeParse(req.body);
  if (!parsed.success) {
    res.status(400).json({ error: parsed.error.message });
    return;
  }
  const { orderedIds } = parsed.data;

  // Validate the provided ids are unique and all reference real
  // services. We intentionally do NOT require the client to send
  // every service id — the iPad UI may filter or hide rows, and
  // forcing a full snapshot caused false 400s ("orderedIds must
  // contain every service id exactly once"). Any services that
  // weren't included are pushed to the end of the list so they
  // remain visible without colliding on sortOrder.
  const existing = await db
    .select({ id: servicesTable.id })
    .from(servicesTable);
  const existingIds = new Set(existing.map((s) => s.id));

  const seen = new Set<number>();
  for (const id of orderedIds) {
    if (!existingIds.has(id) || seen.has(id)) {
      res.status(400).json({
        error: "orderedIds contains a duplicate or unknown service id",
      });
      return;
    }
    seen.add(id);
  }

  const trailingIds = existing
    .map((s) => s.id)
    .filter((id) => !seen.has(id));

  await db.transaction(async (tx) => {
    let index = 0;
    for (const id of orderedIds) {
      await tx
        .update(servicesTable)
        .set({ sortOrder: index })
        .where(eq(servicesTable.id, id));
      index += 1;
    }
    for (const id of trailingIds) {
      await tx
        .update(servicesTable)
        .set({ sortOrder: index })
        .where(eq(servicesTable.id, id));
      index += 1;
    }
  });

  res.sendStatus(204);
});

router.get("/services/:id", requireAuth, async (req, res): Promise<void> => {
  const params = GetServiceParams.safeParse(req.params);
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  const [service] = await db
    .select()
    .from(servicesTable)
    .where(eq(servicesTable.id, params.data.id));

  if (!service) {
    res.status(404).json({ error: "Service not found" });
    return;
  }

  res.json(service);
});

router.patch("/services/:id", requireAdmin, async (req, res): Promise<void> => {
  const params = UpdateServiceParams.safeParse(req.params);
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  const parsed = UpdateServiceBody.safeParse(req.body);
  if (!parsed.success) {
    res.status(400).json({ error: parsed.error.message });
    return;
  }

  const [service] = await db
    .update(servicesTable)
    .set(parsed.data)
    .where(eq(servicesTable.id, params.data.id))
    .returning();

  if (!service) {
    res.status(404).json({ error: "Service not found" });
    return;
  }

  res.json(service);
});

// Drill-in for the "<count> orders" badge on the admin Services row.
// Mirrors the pagination shape used by /admin/apps/:id/dependents/* so
// the frontend can reuse a single drill-in component.
const SERVICE_DEPENDENTS_DEFAULT_LIMIT = 50;
const SERVICE_DEPENDENTS_MAX_LIMIT = 200;

router.get(
  "/admin/services/:id/dependents/orders",
  requireAdmin,
  async (req, res): Promise<void> => {
    const id = Number(req.params.id);
    if (!Number.isInteger(id) || id <= 0) {
      res.status(400).json({ error: "Invalid id" });
      return;
    }
    const [service] = await db
      .select({ id: servicesTable.id })
      .from(servicesTable)
      .where(eq(servicesTable.id, id));
    if (!service) {
      res.status(404).json({ error: "Service not found" });
      return;
    }
    const limitRaw = Number(req.query.limit);
    const offsetRaw = Number(req.query.offset);
    const limit =
      Number.isFinite(limitRaw) && limitRaw > 0
        ? Math.min(Math.floor(limitRaw), SERVICE_DEPENDENTS_MAX_LIMIT)
        : SERVICE_DEPENDENTS_DEFAULT_LIMIT;
    const offset =
      Number.isFinite(offsetRaw) && offsetRaw >= 0 ? Math.floor(offsetRaw) : 0;

    const [{ count: totalCount }] = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(serviceOrdersTable)
      .where(eq(serviceOrdersTable.serviceId, id));
    const items = await db
      .select({
        id: serviceOrdersTable.id,
        status: serviceOrdersTable.status,
        notes: serviceOrdersTable.notes,
        createdAt: serviceOrdersTable.createdAt,
        userId: usersTable.id,
        username: usersTable.username,
        displayNameAr: usersTable.displayNameAr,
        displayNameEn: usersTable.displayNameEn,
      })
      .from(serviceOrdersTable)
      .innerJoin(usersTable, eq(serviceOrdersTable.userId, usersTable.id))
      .where(eq(serviceOrdersTable.serviceId, id))
      .orderBy(sql`${serviceOrdersTable.createdAt} desc`)
      .limit(limit)
      .offset(offset);
    const nextOffset = offset + items.length < totalCount ? offset + items.length : null;
    res.json({ items, totalCount, limit, offset, nextOffset });
  },
);

router.delete("/services/:id", requireAdmin, async (req, res): Promise<void> => {
  const params = DeleteServiceParams.safeParse(req.params);
  if (!params.success) {
    res.status(400).json({ error: params.error.message });
    return;
  }

  const serviceId = params.data.id;

  const [existing] = await db
    .select()
    .from(servicesTable)
    .where(eq(servicesTable.id, serviceId));
  if (!existing) {
    res.status(404).json({ error: "Service not found" });
    return;
  }

  const force = req.query.force === "true" || req.query.force === "1";

  const [orderRow] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(serviceOrdersTable)
    .where(eq(serviceOrdersTable.serviceId, serviceId));

  const orderCount = orderRow?.count ?? 0;
  const hasDeps = orderCount > 0;

  if (hasDeps && !force) {
    res.status(409).json({
      error: "Service has dependent records",
      orderCount,
    });
    return;
  }

  // #195: keep the delete + audit insert in one transaction so we never
  // end up with a removed service and no matching audit row (or vice versa).
  // Forced deletes keep the dedicated `service.force_delete` action so the
  // existing forced-only filter and metadata shape (orderCount) stay
  // backwards compatible; plain deletes get a `service.delete` row mirroring
  // the user.delete pattern.
  await db.transaction(async (tx) => {
    if (hasDeps) {
      // service_orders.service_id has ON DELETE RESTRICT, so we must clear
      // dependent orders first before the service itself can be removed.
      await tx
        .delete(serviceOrdersTable)
        .where(eq(serviceOrdersTable.serviceId, serviceId));
    }
    await tx.delete(servicesTable).where(eq(servicesTable.id, serviceId));

    if (hasDeps && force) {
      await tx.insert(auditLogsTable).values({
        actorUserId: req.session.userId ?? null,
        action: "service.force_delete",
        targetType: "service",
        targetId: serviceId,
        metadata: {
          nameEn: existing.nameEn,
          nameAr: existing.nameAr,
          orderCount,
        },
      });
    } else {
      await tx.insert(auditLogsTable).values({
        actorUserId: req.session.userId ?? null,
        action: "service.delete",
        targetType: "service",
        targetId: serviceId,
        metadata: {
          nameEn: existing.nameEn,
          nameAr: existing.nameAr,
        },
      });
    }
  });

  res.sendStatus(204);
});

export default router;
