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,
  appOpensTable,
} 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),
  });
});

const DAY_MS = 24 * 60 * 60 * 1000;
const allowedRanges = ["7d", "30d", "90d", "custom"] as const;
type RangeKey = typeof allowedRanges[number];

type RangeWindow = {
  range: RangeKey;
  rangeStart: Date;
  rangeEndExclusive: Date;
  rangeDays: number;
  prevRangeStart: Date;
  rangeFromStr: string;
  rangeToStr: string;
};

function parseRangeWindow(query: {
  range?: unknown;
  from?: unknown;
  to?: unknown;
}): { window: RangeWindow } | { error: string } {
  const rangeParam = typeof query.range === "string" ? query.range : "7d";
  const fromParam = typeof query.from === "string" ? query.from : undefined;
  const toParam = typeof query.to === "string" ? query.to : undefined;
  const range: RangeKey = (allowedRanges as readonly string[]).includes(rangeParam)
    ? (rangeParam as RangeKey)
    : "7d";

  const isoDateRe = /^\d{4}-\d{2}-\d{2}$/;
  const parseUtcDate = (s: string): Date | null => {
    if (!isoDateRe.test(s)) return null;
    const d = new Date(s + "T00:00:00.000Z");
    if (Number.isNaN(d.getTime())) return null;
    if (d.toISOString().slice(0, 10) !== s) return null;
    return d;
  };

  const now = new Date();
  const startOfTodayUtc = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));

  let rangeStart: Date;
  let rangeEndExclusive: Date;
  let rangeDays: number;

  if (range === "custom") {
    if (!fromParam || !toParam) {
      return { error: "Missing from/to: 'range=custom' requires both 'from' and 'to' as YYYY-MM-DD dates" };
    }
    const fromDate = parseUtcDate(fromParam);
    const toDate = parseUtcDate(toParam);
    if (!fromDate || !toDate) {
      return { error: "Invalid from/to: expected YYYY-MM-DD dates" };
    }
    if (fromDate.getTime() > toDate.getTime()) {
      return { error: "Invalid from/to: 'from' must be on or before 'to'" };
    }
    const MAX_DAYS = 366;
    const days = Math.round((toDate.getTime() - fromDate.getTime()) / DAY_MS) + 1;
    if (days > MAX_DAYS) {
      return { error: "Range too large" };
    }
    rangeStart = fromDate;
    rangeEndExclusive = new Date(toDate.getTime() + DAY_MS);
    rangeDays = days;
  } else {
    rangeDays = range === "90d" ? 90 : range === "30d" ? 30 : 7;
    rangeStart = new Date(startOfTodayUtc.getTime() - (rangeDays - 1) * DAY_MS);
    rangeEndExclusive = new Date(startOfTodayUtc.getTime() + DAY_MS);
  }

  const prevRangeStart = new Date(rangeStart.getTime() - rangeDays * DAY_MS);
  const rangeFromStr = rangeStart.toISOString().slice(0, 10);
  const rangeToStr = new Date(rangeEndExclusive.getTime() - DAY_MS).toISOString().slice(0, 10);

  return {
    window: {
      range,
      rangeStart,
      rangeEndExclusive,
      rangeDays,
      prevRangeStart,
      rangeFromStr,
      rangeToStr,
    },
  };
}

router.get("/stats/admin", requireAuth, requireAdmin, async (req, res): Promise<void> => {
  const parsed = parseRangeWindow(req.query);
  if ("error" in parsed) {
    res.status(400).json({ error: parsed.error });
    return;
  }
  const {
    range,
    rangeStart,
    rangeEndExclusive,
    rangeDays,
    prevRangeStart,
    rangeFromStr,
    rangeToStr,
  } = parsed.window;

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

  const [newUsersPrevRangeRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(usersTable)
    .where(
      and(
        sql`${usersTable.createdAt} >= ${prevRangeStart.toISOString()}`,
        sql`${usersTable.createdAt} < ${rangeStart.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 buildSeries = (
    rows: { day: string; count: number }[],
  ): { date: string; count: number }[] => {
    const map = new Map<string, number>();
    for (const r of rows) map.set(r.day, Number(r.count));
    const out: { date: string; count: number }[] = [];
    for (let i = 0; i < rangeDays; i++) {
      const d = new Date(rangeStart.getTime() + i * DAY_MS);
      const key = d.toISOString().slice(0, 10);
      out.push({ date: key, count: map.get(key) ?? 0 });
    }
    return out;
  };

  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(
      and(
        sql`${usersTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${usersTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    )
    .groupBy(sql`date_trunc('day', ${usersTable.createdAt} AT TIME ZONE 'UTC')`);
  const signupsByDay = buildSeries(signupRows);

  const appOpenRows = await db
    .select({
      day: sql<string>`to_char(date_trunc('day', ${appOpensTable.createdAt} AT TIME ZONE 'UTC'), 'YYYY-MM-DD')`,
      count: sql<number>`count(*)`,
    })
    .from(appOpensTable)
    .where(
      and(
        sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    )
    .groupBy(sql`date_trunc('day', ${appOpensTable.createdAt} AT TIME ZONE 'UTC')`);
  const appOpensByDay = buildSeries(appOpenRows);

  const [appOpensInRangeRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(appOpensTable)
    .where(
      and(
        sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    );

  const [appOpensPrevRangeRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(appOpensTable)
    .where(
      and(
        sql`${appOpensTable.createdAt} >= ${prevRangeStart.toISOString()}`,
        sql`${appOpensTable.createdAt} < ${rangeStart.toISOString()}`,
      ),
    );

  const servicesCreatedRows = await db
    .select({
      day: sql<string>`to_char(date_trunc('day', ${servicesTable.createdAt} AT TIME ZONE 'UTC'), 'YYYY-MM-DD')`,
      count: sql<number>`count(*)`,
    })
    .from(servicesTable)
    .where(
      and(
        sql`${servicesTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${servicesTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    )
    .groupBy(sql`date_trunc('day', ${servicesTable.createdAt} AT TIME ZONE 'UTC')`);
  const servicesCreatedByDay = buildSeries(servicesCreatedRows);

  const [servicesCreatedInRangeRow] = await db
    .select({ count: sql<number>`count(*)` })
    .from(servicesTable)
    .where(
      and(
        sql`${servicesTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${servicesTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    );

  const topAppsRows = await db
    .select({
      appId: appsTable.id,
      slug: appsTable.slug,
      nameAr: appsTable.nameAr,
      nameEn: appsTable.nameEn,
      iconName: appsTable.iconName,
      color: appsTable.color,
      count: sql<number>`count(${appOpensTable.id})`,
    })
    .from(appOpensTable)
    .innerJoin(appsTable, eq(appOpensTable.appId, appsTable.id))
    .where(
      and(
        sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    )
    .groupBy(
      appsTable.id,
      appsTable.slug,
      appsTable.nameAr,
      appsTable.nameEn,
      appsTable.iconName,
      appsTable.color,
    )
    .orderBy(sql`count(${appOpensTable.id}) desc`)
    .limit(5);

  const topApps = topAppsRows.map((r) => ({
    appId: r.appId,
    slug: r.slug,
    nameAr: r.nameAr,
    nameEn: r.nameEn,
    iconName: r.iconName,
    color: r.color,
    count: Number(r.count),
  }));

  const mostActiveUsersRows = await db
    .select({
      userId: usersTable.id,
      username: usersTable.username,
      displayNameAr: usersTable.displayNameAr,
      displayNameEn: usersTable.displayNameEn,
      avatarUrl: usersTable.avatarUrl,
      count: sql<number>`count(${appOpensTable.id})`,
    })
    .from(appOpensTable)
    .innerJoin(usersTable, eq(appOpensTable.userId, usersTable.id))
    .where(
      and(
        sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
        sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
      ),
    )
    .groupBy(
      usersTable.id,
      usersTable.username,
      usersTable.displayNameAr,
      usersTable.displayNameEn,
      usersTable.avatarUrl,
    )
    .orderBy(sql`count(${appOpensTable.id}) desc`)
    .limit(5);

  const mostActiveUsers = mostActiveUsersRows.map((r) => ({
    userId: r.userId,
    username: r.username,
    displayNameAr: r.displayNameAr,
    displayNameEn: r.displayNameEn,
    avatarUrl: r.avatarUrl,
    count: Number(r.count),
  }));

  res.json({
    range,
    rangeDays,
    rangeFrom: rangeFromStr,
    rangeTo: rangeToStr,
    newUsersInRange: Number(newUsersInRangeRow?.count ?? 0),
    newUsersPrevRange: Number(newUsersPrevRangeRow?.count ?? 0),
    activeServices: Number(activeServicesRow?.count ?? 0),
    inactiveServices: Number(inactiveServicesRow?.count ?? 0),
    signupsByDay,
    appOpensByDay,
    appOpensInRange: Number(appOpensInRangeRow?.count ?? 0),
    appOpensPrevRange: Number(appOpensPrevRangeRow?.count ?? 0),
    servicesCreatedByDay,
    servicesCreatedInRange: Number(servicesCreatedInRangeRow?.count ?? 0),
    topApps,
    mostActiveUsers,
  });
});

const RECENT_OPENS_DEFAULT_LIMIT = 100;
const RECENT_OPENS_MAX_LIMIT = 200;

function parsePaging(query: { limit?: unknown; offset?: unknown }):
  | { limit: number; offset: number }
  | { error: string } {
  let limit = RECENT_OPENS_DEFAULT_LIMIT;
  let offset = 0;
  if (query.limit !== undefined) {
    const n = Number(query.limit);
    if (!Number.isInteger(n) || n <= 0) {
      return { error: "Invalid limit: must be a positive integer" };
    }
    if (n > RECENT_OPENS_MAX_LIMIT) {
      return { error: `Invalid limit: maximum is ${RECENT_OPENS_MAX_LIMIT}` };
    }
    limit = n;
  }
  if (query.offset !== undefined) {
    const n = Number(query.offset);
    if (!Number.isInteger(n) || n < 0) {
      return { error: "Invalid offset: must be a non-negative integer" };
    }
    offset = n;
  }
  return { limit, offset };
}

router.get(
  "/stats/admin/app-opens/by-app/:appId",
  requireAuth,
  requireAdmin,
  async (req, res): Promise<void> => {
    const appId = Number(req.params.appId);
    if (!Number.isInteger(appId) || appId <= 0) {
      res.status(400).json({ error: "Invalid appId" });
      return;
    }
    const parsed = parseRangeWindow(req.query);
    if ("error" in parsed) {
      res.status(400).json({ error: parsed.error });
      return;
    }
    const { range, rangeStart, rangeEndExclusive, rangeDays, rangeFromStr, rangeToStr } =
      parsed.window;

    const paging = parsePaging(req.query);
    if ("error" in paging) {
      res.status(400).json({ error: paging.error });
      return;
    }
    const { limit, offset } = paging;

    const [app] = await db
      .select({
        id: appsTable.id,
        slug: appsTable.slug,
        nameAr: appsTable.nameAr,
        nameEn: appsTable.nameEn,
        iconName: appsTable.iconName,
        color: appsTable.color,
      })
      .from(appsTable)
      .where(eq(appsTable.id, appId));
    if (!app) {
      res.status(404).json({ error: "App not found" });
      return;
    }

    const [totalRow] = await db
      .select({ count: sql<number>`count(*)` })
      .from(appOpensTable)
      .where(
        and(
          eq(appOpensTable.appId, appId),
          sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
          sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
        ),
      );

    const opensRows = await db
      .select({
        id: appOpensTable.id,
        createdAt: appOpensTable.createdAt,
        userId: usersTable.id,
        username: usersTable.username,
        displayNameAr: usersTable.displayNameAr,
        displayNameEn: usersTable.displayNameEn,
        avatarUrl: usersTable.avatarUrl,
      })
      .from(appOpensTable)
      .innerJoin(usersTable, eq(appOpensTable.userId, usersTable.id))
      .where(
        and(
          eq(appOpensTable.appId, appId),
          sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
          sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
        ),
      )
      .orderBy(sql`${appOpensTable.createdAt} desc`, sql`${appOpensTable.id} desc`)
      .limit(limit)
      .offset(offset);

    const totalCount = Number(totalRow?.count ?? 0);
    const nextOffset = offset + opensRows.length < totalCount ? offset + opensRows.length : null;

    res.json({
      appId: app.id,
      slug: app.slug,
      nameAr: app.nameAr,
      nameEn: app.nameEn,
      iconName: app.iconName,
      color: app.color,
      range,
      rangeDays,
      rangeFrom: rangeFromStr,
      rangeTo: rangeToStr,
      totalCount,
      limit,
      offset,
      nextOffset,
      opens: opensRows.map((r) => ({
        id: r.id,
        createdAt:
          r.createdAt instanceof Date ? r.createdAt.toISOString() : String(r.createdAt),
        userId: r.userId,
        username: r.username,
        displayNameAr: r.displayNameAr,
        displayNameEn: r.displayNameEn,
        avatarUrl: r.avatarUrl,
      })),
    });
  },
);

router.get(
  "/stats/admin/app-opens/by-user/:userId",
  requireAuth,
  requireAdmin,
  async (req, res): Promise<void> => {
    const userId = Number(req.params.userId);
    if (!Number.isInteger(userId) || userId <= 0) {
      res.status(400).json({ error: "Invalid userId" });
      return;
    }
    const parsed = parseRangeWindow(req.query);
    if ("error" in parsed) {
      res.status(400).json({ error: parsed.error });
      return;
    }
    const { range, rangeStart, rangeEndExclusive, rangeDays, rangeFromStr, rangeToStr } =
      parsed.window;

    const paging = parsePaging(req.query);
    if ("error" in paging) {
      res.status(400).json({ error: paging.error });
      return;
    }
    const { limit, offset } = paging;

    const [user] = await db
      .select({
        id: usersTable.id,
        username: usersTable.username,
        displayNameAr: usersTable.displayNameAr,
        displayNameEn: usersTable.displayNameEn,
        avatarUrl: usersTable.avatarUrl,
      })
      .from(usersTable)
      .where(eq(usersTable.id, userId));
    if (!user) {
      res.status(404).json({ error: "User not found" });
      return;
    }

    const [totalRow] = await db
      .select({ count: sql<number>`count(*)` })
      .from(appOpensTable)
      .where(
        and(
          eq(appOpensTable.userId, userId),
          sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
          sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
        ),
      );

    const opensRows = await db
      .select({
        id: appOpensTable.id,
        createdAt: appOpensTable.createdAt,
        appId: appsTable.id,
        slug: appsTable.slug,
        nameAr: appsTable.nameAr,
        nameEn: appsTable.nameEn,
        iconName: appsTable.iconName,
        color: appsTable.color,
      })
      .from(appOpensTable)
      .innerJoin(appsTable, eq(appOpensTable.appId, appsTable.id))
      .where(
        and(
          eq(appOpensTable.userId, userId),
          sql`${appOpensTable.createdAt} >= ${rangeStart.toISOString()}`,
          sql`${appOpensTable.createdAt} < ${rangeEndExclusive.toISOString()}`,
        ),
      )
      .orderBy(sql`${appOpensTable.createdAt} desc`, sql`${appOpensTable.id} desc`)
      .limit(limit)
      .offset(offset);

    const totalCount = Number(totalRow?.count ?? 0);
    const nextOffset = offset + opensRows.length < totalCount ? offset + opensRows.length : null;

    res.json({
      userId: user.id,
      username: user.username,
      displayNameAr: user.displayNameAr,
      displayNameEn: user.displayNameEn,
      avatarUrl: user.avatarUrl,
      range,
      rangeDays,
      rangeFrom: rangeFromStr,
      rangeTo: rangeToStr,
      totalCount,
      limit,
      offset,
      nextOffset,
      opens: opensRows.map((r) => ({
        id: r.id,
        createdAt:
          r.createdAt instanceof Date ? r.createdAt.toISOString() : String(r.createdAt),
        appId: r.appId,
        slug: r.slug,
        nameAr: r.nameAr,
        nameEn: r.nameEn,
        iconName: r.iconName,
        color: r.color,
      })),
    });
  },
);

export default router;
