import { Router, type IRouter } from "express";
import { eq, and, inArray, sql } from "drizzle-orm";
import { db } from "@workspace/db";
import {
  appsTable,
  appPermissionsTable,
  rolesTable,
  rolePermissionsTable,
  userRolesTable,
  servicesTable,
  notificationsTable,
  usersTable,
  messagesTable,
  messageReadsTable,
} from "@workspace/db";
import { requireAuth, requireAdmin } from "../middlewares/auth";

const router: IRouter = Router();

router.get("/stats/home", requireAuth, async (req, res): Promise<void> => {
  const userId = req.session.userId!;

  // Determine if the user is an admin and which apps they can access.
  const userRoleRows = await db
    .select({ roleName: rolesTable.name, roleId: userRolesTable.roleId })
    .from(userRolesTable)
    .innerJoin(rolesTable, eq(userRolesTable.roleId, rolesTable.id))
    .where(eq(userRolesTable.userId, userId));

  const isAdmin = userRoleRows.some((r) => r.roleName === "admin");

  let totalApps = 0;
  if (isAdmin) {
    const [row] = await db
      .select({ count: sql<number>`count(*)` })
      .from(appsTable)
      .where(eq(appsTable.isActive, true));
    totalApps = Number(row?.count ?? 0);
  } else {
    const userRoleIds = userRoleRows.map((r) => r.roleId);
    const userPermissionIds = userRoleIds.length > 0
      ? (await db
          .select({ permissionId: rolePermissionsTable.permissionId })
          .from(rolePermissionsTable)
          .where(inArray(rolePermissionsTable.roleId, userRoleIds))
        ).map((r) => r.permissionId)
      : [];

    const restrictedRows = await db
      .select({ appId: appPermissionsTable.appId })
      .from(appPermissionsTable);
    const restrictedAppIds = new Set(restrictedRows.map((r) => r.appId));

    const allowedRestrictedAppIds = userPermissionIds.length > 0
      ? new Set(
          (await db
            .select({ appId: appPermissionsTable.appId })
            .from(appPermissionsTable)
            .where(inArray(appPermissionsTable.permissionId, userPermissionIds))
          ).map((r) => r.appId),
        )
      : new Set<number>();

    const activeApps = await db
      .select({ id: appsTable.id })
      .from(appsTable)
      .where(eq(appsTable.isActive, true));
    totalApps = activeApps.filter(
      (a) => !restrictedAppIds.has(a.id) || allowedRestrictedAppIds.has(a.id),
    ).length;
  }

  const appsCount = { count: totalApps };

  const [servicesCount] = await db
    .select({ count: sql<number>`count(*)` })
    .from(servicesTable);

  const [unreadNotifCount] = await db
    .select({ count: sql<number>`count(*)` })
    .from(notificationsTable)
    .where(
      and(
        eq(notificationsTable.userId, userId),
        eq(notificationsTable.isRead, false),
      ),
    );

  const [usersCount] = await db
    .select({ count: sql<number>`count(*)` })
    .from(usersTable);

  const [unreadMsgCount] = await db
    .select({ count: sql<number>`count(*)` })
    .from(messagesTable)
    .where(
      and(
        sql`${messagesTable.id} NOT IN (
          SELECT message_id FROM message_reads WHERE user_id = ${userId}
        )`,
        sql`${messagesTable.senderId} != ${userId}`,
      ),
    );

  res.json({
    totalApps: Number(appsCount?.count ?? 0),
    totalServices: Number(servicesCount?.count ?? 0),
    unreadNotifications: Number(unreadNotifCount?.count ?? 0),
    unreadMessages: Number(unreadMsgCount?.count ?? 0),
    totalUsers: Number(usersCount?.count ?? 0),
  });
});

router.get("/stats/admin", requireAuth, requireAdmin, async (_req, res): Promise<void> => {
  const now = new Date();
  const startOfTodayUtc = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
  const sevenDaysAgo = new Date(startOfTodayUtc.getTime() - 6 * 24 * 60 * 60 * 1000);
  const fourteenDaysAgo = new Date(startOfTodayUtc.getTime() - 13 * 24 * 60 * 60 * 1000);
  const last7Start = sevenDaysAgo;
  const prev7Start = fourteenDaysAgo;

  const [newUsersLast7Row] = await db
    .select({ count: sql<number>`count(*)` })
    .from(usersTable)
    .where(sql`${usersTable.createdAt} >= ${last7Start.toISOString()}`);

  const [newUsersPrev7Row] = await db
    .select({ count: sql<number>`count(*)` })
    .from(usersTable)
    .where(
      and(
        sql`${usersTable.createdAt} >= ${prev7Start.toISOString()}`,
        sql`${usersTable.createdAt} < ${last7Start.toISOString()}`,
      ),
    );

  const [activeServicesRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(servicesTable)
    .where(eq(servicesTable.isAvailable, true));

  const [inactiveServicesRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(servicesTable)
    .where(eq(servicesTable.isAvailable, false));

  const signupRows = await db
    .select({
      day: sql<string>`to_char(date_trunc('day', ${usersTable.createdAt} AT TIME ZONE 'UTC'), 'YYYY-MM-DD')`,
      count: sql<number>`count(*)`,
    })
    .from(usersTable)
    .where(sql`${usersTable.createdAt} >= ${last7Start.toISOString()}`)
    .groupBy(sql`date_trunc('day', ${usersTable.createdAt} AT TIME ZONE 'UTC')`);

  const countsByDay = new Map<string, number>();
  for (const row of signupRows) {
    countsByDay.set(row.day, Number(row.count));
  }

  const signupsByDay: { date: string; count: number }[] = [];
  for (let i = 6; i >= 0; i--) {
    const d = new Date(startOfTodayUtc.getTime() - i * 24 * 60 * 60 * 1000);
    const key = d.toISOString().slice(0, 10);
    signupsByDay.push({ date: key, count: countsByDay.get(key) ?? 0 });
  }

  res.json({
    newUsersLast7Days: Number(newUsersLast7Row?.count ?? 0),
    newUsersPrev7Days: Number(newUsersPrev7Row?.count ?? 0),
    activeServices: Number(activeServicesRow?.count ?? 0),
    inactiveServices: Number(inactiveServicesRow?.count ?? 0),
    signupsByDay,
  });
});

export default router;
