api-server/src/routes/dashboard.ts

import { Router } from "express";
import { db } from "@workspace/db";
import { tasksTable, clientsTable } from "@workspace/db";
import { sql, eq, and, isNotNull, lt, ne, desc, gte, count, inArray } from "drizzle-orm";
import { GetRecentActivityQueryParams } from "@workspace/api-zod";
import { AuthRequest } from "../middleware/auth.js";
import { getTaskVisibilityFilter, User, isAdmin, canAccessAllTeams } from "../lib/auth-utils.js";

const router = Router();

interface CacheEntry<T> {
  data: T;
  timestamp: number;
}

const cache = new Map<string, CacheEntry<any>>();
const CACHE_TTL_MS = 60 * 1000; // 1 minute TTL

export function invalidateDashboardCache() {
  cache.clear();
}

function getCached<T>(key: string): T | null {
  const entry = cache.get(key);
  if (!entry) return null;
  if (Date.now() - entry.timestamp > CACHE_TTL_MS) {
    cache.delete(key);
    return null;
  }
  return entry.data;
}

function setCached<T>(key: string, data: T): void {
  cache.set(key, { data, timestamp: Date.now() });
}

function serializeTask(task: typeof tasksTable.$inferSelect, extras: Record<string, unknown> = {}) {
  return {
    ...task,
    dueDate: task.dueDate ? task.dueDate.toISOString() : null,
    completedAt: task.completedAt ? task.completedAt.toISOString() : null,
    createdAt: task.createdAt.toISOString(),
    updatedAt: task.updatedAt.toISOString(),
    ...extras,
  };
}

router.get("/dashboard/stats", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const cacheKey = `stats_${user.id}`;
    const cachedData = getCached(cacheKey);
    if (cachedData) {
      res.json(cachedData);
      return;
    }

    const visibilityFilter = getTaskVisibilityFilter(user);
    const now = new Date();

    const [total, notStarted, inProgress, inReview, completed, overdue] = await Promise.all([
      db.select({ cnt: count() }).from(tasksTable).where(visibilityFilter).then(r => Number(r[0].cnt)),
      db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.status, "not_started"), visibilityFilter)).then(r => Number(r[0].cnt)),
      db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.status, "in_progress"), visibilityFilter)).then(r => Number(r[0].cnt)),
      db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.status, "review"), visibilityFilter)).then(r => Number(r[0].cnt)),
      db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.status, "completed"), visibilityFilter)).then(r => Number(r[0].cnt)),
      db.select({ cnt: count() }).from(tasksTable).where(
        and(isNotNull(tasksTable.dueDate), lt(tasksTable.dueDate, now), ne(tasksTable.status, "completed"), visibilityFilter)
      ).then(r => Number(r[0].cnt)),
    ]);

    const result = {
      totalTasks: total,
      notStarted,
      inProgress,
      inReview,
      completed,
      overdue,
      completionRate: total > 0 ? Math.round((completed / total) * 100) : 0,
    };

    setCached(cacheKey, result);
    res.json(result);
  } catch (err) {
    req.log.error({ err }, "Failed to get dashboard stats");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.get("/dashboard/team-stats", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const cacheKey = `team_stats_${user.id}`;
    const cachedData = getCached(cacheKey);
    if (cachedData) {
      res.json(cachedData);
      return;
    }

    const now = new Date();
    let teams = ["Account Manager", "Creative", "SEO Specialist", "Media Buying", "Social Media", "Operation"];
    
    // Only Admins and Head of Team see all teams' stats
    if (!canAccessAllTeams(user)) {
      teams = teams.filter(t => t === user.team);
    }

    const statsPromises = teams.map(async (team) => {
      const [total, notStarted, inProgress, completed, overdue] = await Promise.all([
        db.select({ cnt: count() }).from(tasksTable).where(eq(tasksTable.assignedTeam, team)).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "not_started"))).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "in_progress"))).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "completed"))).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(
          and(eq(tasksTable.assignedTeam, team), isNotNull(tasksTable.dueDate), lt(tasksTable.dueDate, now), ne(tasksTable.status, "completed"))
        ).then(r => Number(r[0].cnt)),
      ]);
      return { team, totalTasks: total, notStarted, inProgress, completed, overdue };
    });

    const stats = await Promise.all(statsPromises);
    setCached(cacheKey, stats);
    res.json(stats);
  } catch (err) {
    req.log.error({ err }, "Failed to get team stats");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.get("/dashboard/overdue-tasks", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const visibilityFilter = getTaskVisibilityFilter(user);
    const now = new Date();
    const tasks = await db.select().from(tasksTable).where(
      and(isNotNull(tasksTable.dueDate), lt(tasksTable.dueDate, now), ne(tasksTable.status, "completed"), visibilityFilter)
    ).orderBy(tasksTable.dueDate);

    res.json(tasks.map(t => serializeTask(t, {
      createdByUser: null,
      assignedUser: null,
      commentCount: 0,
    })));
  } catch (err) {
    req.log.error({ err }, "Failed to get overdue tasks");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.get("/dashboard/recent-activity", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const visibilityFilter = getTaskVisibilityFilter(user);
    const params = GetRecentActivityQueryParams.parse(req.query);
    const limit = params.limit ? Number(params.limit) : 20;

    const tasks = await db.select().from(tasksTable)
      .where(visibilityFilter)
      .orderBy(desc(tasksTable.updatedAt))
      .limit(limit);

    const activity = tasks.map((t, i) => ({
      id: i + 1,
      taskId: t.id,
      taskTitle: t.title,
      action: t.status === "completed" ? "completed" : t.status === "in_progress" ? "started" : "created",
      fromTeam: t.fromTeam,
      assignedTeam: t.assignedTeam,
      status: t.status,
      createdAt: t.updatedAt.toISOString(),
    }));

    res.json(activity);
  } catch (err) {
    req.log.error({ err }, "Failed to get recent activity");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.get("/dashboard/analytics", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const cacheKey = `analytics_${user.id}`;
    const cachedData = getCached(cacheKey);
    if (cachedData) {
      res.json(cachedData);
      return;
    }

    const visibilityFilter = getTaskVisibilityFilter(user);
    const now = new Date();
    const startOfThisWeek = new Date(now);
    startOfThisWeek.setDate(now.getDate() - now.getDay());
    startOfThisWeek.setHours(0, 0, 0, 0);
    const startOfLastWeek = new Date(startOfThisWeek);
    startOfLastWeek.setDate(startOfLastWeek.getDate() - 7);

    const days = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
    const weekly = await Promise.all(days.map(async (day, i) => {
      const thisStart = new Date(startOfThisWeek);
      thisStart.setDate(thisStart.getDate() + i);
      const thisEnd = new Date(thisStart);
      thisEnd.setDate(thisEnd.getDate() + 1);

      const lastStart = new Date(startOfLastWeek);
      lastStart.setDate(lastStart.getDate() + i);
      const lastEnd = new Date(lastStart);
      lastEnd.setDate(lastEnd.getDate() + 1);

      const [thisWeekRow] = await db.select({ cnt: count() }).from(tasksTable)
        .where(and(gte(tasksTable.createdAt, thisStart), lt(tasksTable.createdAt, thisEnd), visibilityFilter));
      const [lastWeekRow] = await db.select({ cnt: count() }).from(tasksTable)
        .where(and(gte(tasksTable.createdAt, lastStart), lt(tasksTable.createdAt, lastEnd), visibilityFilter));

      return { day, thisWeek: Number(thisWeekRow.cnt), lastWeek: Number(lastWeekRow.cnt) };
    }));

    let teams = ["Account Manager", "Creative", "SEO Specialist", "Media Buying", "Social Media", "Operation"];
    if (!canAccessAllTeams(user)) {
      teams = teams.filter(t => t === user.team);
    }
    const byTeam = await Promise.all(teams.map(async (team) => {
      const [total, notStarted, inProgress, completed, overdue] = await Promise.all([
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), visibilityFilter)).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "not_started"), visibilityFilter)).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "in_progress"), visibilityFilter)).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), eq(tasksTable.status, "completed"), visibilityFilter)).then(r => Number(r[0].cnt)),
        db.select({ cnt: count() }).from(tasksTable).where(and(eq(tasksTable.assignedTeam, team), isNotNull(tasksTable.dueDate), lt(tasksTable.dueDate, now), ne(tasksTable.status, "completed"), visibilityFilter)).then(r => Number(r[0].cnt)),
      ]);
      return { team, totalTasks: total, notStarted, inProgress, completed, overdue };
    }));

    const clientTaskCounts = await db.select({
      clientId: tasksTable.clientId,
      total: count(),
    }).from(tasksTable)
      .where(and(isNotNull(tasksTable.clientId), visibilityFilter))
      .groupBy(tasksTable.clientId)
      .orderBy(desc(count()))
      .limit(5);

    const topClientIds = clientTaskCounts.map(r => r.clientId).filter(Boolean) as number[];
    const topClientRows = topClientIds.length > 0
      ? await db.select({ id: clientsTable.id, name: clientsTable.name }).from(clientsTable).where(inArray(clientsTable.id, topClientIds))
      : [];
    const clientNameMap = Object.fromEntries(topClientRows.map(c => [c.id, c.name]));

    const topClients = await Promise.all(clientTaskCounts.map(async (row) => {
      if (!row.clientId) return null;
      const [{ cnt: completedCnt }] = await db.select({ cnt: count() }).from(tasksTable)
        .where(and(eq(tasksTable.clientId, row.clientId), eq(tasksTable.status, "completed"), visibilityFilter));
      return {
        id: row.clientId,
        name: clientNameMap[row.clientId] ?? "Unknown",
        totalTasks: Number(row.total),
        completedTasks: Number(completedCnt),
      };
    }));

    const result = {
      weekly,
      byTeam,
      topClients: topClients.filter(Boolean),
    };

    setCached(cacheKey, result);
    res.json(result);
  } catch (err) {
    req.log.error({ err }, "Failed to get analytics");
    res.status(500).json({ error: "Internal server error" });
  }
});

export default router;