import {
  BillingType,
  BugStatus,
  Prisma,
  SystemRole,
  TaskLifecycle,
  TaskStage,
} from '@prisma/client';
import { prisma } from '../db';
import { formatDate, hoursToHhmm } from '../lib/dates';
import { AuthUser, visibleProjectIds } from '../lib/rbac';
import { systemRoleFromDb } from '../lib/roles';
import { STAGE_LABELS, stageFromDb } from '../lib/stages';
import { billingToDb } from '../serializers/task';
import { statusFromBugDb } from '../serializers/bug';
import { categoryFromTimesheetDb } from '../serializers/timesheet';

export type ReportFilters = {
  from?: string;
  to?: string;
  projectId?: string;
  clientId?: string;
  employeeId?: string;
  billingType?: string;
};

function stampClient(where: Prisma.TaskWhereInput | Prisma.TimesheetWhereInput, f: ReportFilters) {
  if (f.clientId) where.project = { clientId: f.clientId };
}

function stampBugClient(where: Prisma.BugWhereInput, f: ReportFilters) {
  if (!f.clientId) return;
  const parent = (where.parentTask && typeof where.parentTask === 'object'
    ? { ...(where.parentTask as Prisma.TaskWhereInput) }
    : {}) as Prisma.TaskWhereInput;
  parent.project = { clientId: f.clientId };
  where.parentTask = parent;
}

function round2(n: number) {
  return Math.round(n * 100) / 100;
}

function dateKey(d: Date) {
  return d.toISOString().slice(0, 10);
}

function todayKey() {
  const d = new Date();
  return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
}

function offsetKey(days: number) {
  const d = new Date();
  d.setHours(12, 0, 0, 0);
  d.setDate(d.getDate() + days);
  return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
}

function inRange(dateStr: string, from?: string, to?: string) {
  if (!dateStr) return false;
  if (from && dateStr < from) return false;
  if (to && dateStr > to) return false;
  return true;
}

function formatDateTime(iso: Date | string) {
  const d = typeof iso === 'string' ? new Date(iso) : iso;
  if (Number.isNaN(d.getTime())) return String(iso);
  const dd = String(d.getDate()).padStart(2, '0');
  const mm = String(d.getMonth() + 1).padStart(2, '0');
  const yyyy = d.getFullYear();
  const hh = String(d.getHours()).padStart(2, '0');
  const mi = String(d.getMinutes()).padStart(2, '0');
  return `${dd}-${mm}-${yyyy} ${hh}:${mi}`;
}

async function projectScope(user: AuthUser) {
  return visibleProjectIds(user);
}

function billingLabel(b: BillingType) {
  return b === BillingType.NonBillable ? 'Non-billable' : 'Billable';
}

export function rowsToCsv(rows: Record<string, unknown>[]): string {
  if (!rows.length) return '';
  const keys = Object.keys(rows[0]);
  const esc = (v: unknown) => {
    const s = String(v ?? '');
    if (/[",\n\r]/.test(s)) return `"${s.replace(/"/g, '""')}"`;
    return s;
  };
  return [keys.join(','), ...rows.map((r) => keys.map((k) => esc(r[k])).join(','))].join('\n');
}

async function loadTimesheets(user: AuthUser, f: ReportFilters, clampSelf: boolean) {
  const visible = await projectScope(user);
  const where: Prisma.TimesheetWhereInput = {};
  if (visible !== 'all') where.projectId = { in: visible.length ? visible : ['00000000-0000-0000-0000-000000000000'] };
  if (f.projectId) where.projectId = f.projectId;
  stampClient(where, f);
  if (f.employeeId) where.employeeId = f.employeeId;
  if (f.billingType) where.billingType = billingToDb(f.billingType);
  if (clampSelf) where.employeeId = user.id;

  const rows = await prisma.timesheet.findMany({
    where,
    include: {
      employee: { select: { id: true, name: true, role: true } },
      project: { select: { id: true, code: true, name: true } },
      task: { select: { id: true, code: true } },
      bug: { select: { id: true, code: true } },
    },
    take: 5000,
  });

  return rows.filter((t) => inRange(dateKey(t.workDate), f.from, f.to));
}

async function taskAllocHours(taskId: string) {
  const assignments = await prisma.taskAssignment.findMany({ where: { taskId } });
  if (assignments.length) {
    return round2(assignments.reduce((a, x) => a + Number(x.allocatedHours), 0));
  }
  const t = await prisma.task.findUnique({ where: { id: taskId }, select: { allocatedHours: true } });
  return t ? Number(t.allocatedHours || 0) : 0;
}

async function projectAllocatedHours(projectId: string) {
  const tasks = await prisma.task.findMany({
    where: { projectId, parentId: null },
    select: { id: true, allocatedHours: true },
  });
  let sum = 0;
  for (const t of tasks) {
    const a = await taskAllocHours(t.id);
    sum += a || Number(t.allocatedHours || 0);
  }
  return round2(sum);
}

async function actualHoursForTask(taskId: string, includeSubtasks: boolean) {
  const ids = [taskId];
  if (includeSubtasks) {
    const kids = await prisma.task.findMany({ where: { parentId: taskId }, select: { id: true } });
    ids.push(...kids.map((k) => k.id));
  }
  const agg = await prisma.timesheet.aggregate({
    where: { taskId: { in: ids } },
    _sum: { hours: true },
  });
  return round2(Number(agg._sum.hours || 0));
}

function effortCols(allocated: number, actual: number) {
  const alloc = round2(allocated);
  const act = round2(actual);
  return {
    allocated: hoursToHhmm(alloc),
    actual: hoursToHhmm(act),
    variance: hoursToHhmm(round2(act - alloc)),
  };
}

/** Assigned hours for one employee (task + bug allocations), scoped by project/client/billing. */
async function employeeAssignedHours(
  employeeId: string,
  f: ReportFilters,
  visible: string[] | 'all'
) {
  const taskFilter: Prisma.TaskWhereInput = {};
  if (visible !== 'all') {
    taskFilter.projectId = { in: visible.length ? visible : ['00000000-0000-0000-0000-000000000000'] };
  }
  if (f.projectId) taskFilter.projectId = f.projectId;
  stampClient(taskFilter, f);
  if (f.billingType) taskFilter.billingType = billingToDb(f.billingType);

  const taskAssigns = await prisma.taskAssignment.findMany({
    where: { employeeId, task: taskFilter },
    select: { allocatedHours: true },
  });
  let sum = taskAssigns.reduce((a, x) => a + Number(x.allocatedHours || 0), 0);

  const bugParent: Prisma.TaskWhereInput = {};
  if (visible !== 'all') {
    bugParent.projectId = { in: visible.length ? visible : ['00000000-0000-0000-0000-000000000000'] };
  }
  if (f.projectId) bugParent.projectId = f.projectId;
  if (f.clientId) bugParent.project = { clientId: f.clientId };

  const bugAssigns = await prisma.bugAssignment.findMany({
    where: {
      employeeId,
      bug: { parentTask: bugParent },
    },
    select: { allocatedHours: true },
  });
  sum += bugAssigns.reduce((a, x) => a + Number(x.allocatedHours || 0), 0);
  return round2(sum);
}

export async function buildReportRows(
  type: string,
  user: AuthUser,
  f: ReportFilters
): Promise<Record<string, unknown>[]> {
  const isNarrow =
    user.role === SystemRole.Developer ||
    user.role === SystemRole.TesterQA ||
    user.role === SystemRole.BA;
  const clampSelf = isNarrow && (type === 'employee' || type === 'daily');
  const timesheets = await loadTimesheets(user, f, clampSelf);
  const visible = await projectScope(user);

  if (type === 'project') {
    const map = new Map<string, number>();
    timesheets.forEach((t) => {
      map.set(t.projectId, (map.get(t.projectId) || 0) + Number(t.hours));
    });
    const out = [];
    for (const [id, hrs] of map.entries()) {
      const p = await prisma.project.findUnique({ where: { id } });
      const allocated = await projectAllocatedHours(id);
      out.push({
        project: p?.code || id,
        name: p?.name || id,
        ...effortCols(allocated, hrs),
      });
    }
    return out;
  }

  if (type === 'employee') {
    const actualMap = new Map<string, number>();
    timesheets.forEach((t) => {
      actualMap.set(t.employeeId, (actualMap.get(t.employeeId) || 0) + Number(t.hours));
    });

    const empWhere: Prisma.EmployeeWhereInput = { active: true };
    if (clampSelf) empWhere.id = user.id;
    else if (f.employeeId) empWhere.id = f.employeeId;

    const employees = await prisma.employee.findMany({
      where: empWhere,
      select: { id: true, name: true, role: true },
      orderBy: { name: 'asc' },
      take: 2000,
    });

    const out = [];
    for (const e of employees) {
      const assigned = await employeeAssignedHours(e.id, f, visible);
      const actual = round2(actualMap.get(e.id) || 0);
      if (assigned <= 0 && actual <= 0) continue;
      out.push({
        employee: e.name,
        role: systemRoleFromDb(e.role),
        ...effortCols(assigned, actual),
      });
    }
    return out;
  }

  if (type === 'task') {
    const where: Prisma.TaskWhereInput = {};
    if (visible !== 'all') where.projectId = { in: visible };
    if (f.projectId) where.projectId = f.projectId;
    stampClient(where, f);
    if (f.billingType) where.billingType = billingToDb(f.billingType);
    const tasks = await prisma.task.findMany({
      where,
      include: { assignments: true },
      orderBy: { code: 'asc' },
      take: 2000,
    });
    const out = [];
    for (const t of tasks) {
      const allocated = t.assignments.length
        ? round2(t.assignments.reduce((a, x) => a + Number(x.allocatedHours), 0))
        : Number(t.allocatedHours || 0);
      const actual = await actualHoursForTask(t.id, !t.parentId);
      out.push({
        id: t.code,
        title: t.title,
        parent: t.parentId || '',
        stage: stageFromDb(t.stage),
        billing: billingLabel(t.billingType),
        ...effortCols(allocated, actual),
      });
    }
    return out;
  }

  if (type === 'bug') {
    const where: Prisma.BugWhereInput = {};
    if (visible !== 'all') {
      where.parentTask = { projectId: { in: visible } };
    }
    if (f.projectId) where.parentTask = { projectId: f.projectId };
    stampBugClient(where, f);
    const bugs = await prisma.bug.findMany({
      where,
      include: {
        parentTask: { select: { id: true, code: true, projectId: true } },
        assignments: true,
      },
      take: 2000,
    });
    const today = Date.now();
    const out = [];
    for (const b of bugs) {
      const agg = await prisma.timesheet.aggregate({
        where: { bugId: b.id },
        _sum: { hours: true },
      });
      const allocated = b.assignments.length
        ? round2(b.assignments.reduce((a, x) => a + Number(x.allocatedHours), 0))
        : Number(b.allocatedHours || 0);
      const actual = round2(Number(agg._sum.hours || 0));
      out.push({
        id: b.code,
        title: b.title,
        task: b.parentTask?.code || b.parentTaskId,
        status: statusFromBugDb(b.status),
        severity: b.severity,
        ...effortCols(allocated, actual),
        age_days: Math.round((today - b.createdAt.getTime()) / 86400000),
      });
    }
    return out;
  }

  if (type === 'overbudget') {
    const where: Prisma.TaskWhereInput = { parentId: null };
    if (visible !== 'all') where.projectId = { in: visible };
    if (f.projectId) where.projectId = f.projectId;
    stampClient(where, f);
    const tasks = await prisma.task.findMany({
      where,
      include: { assignments: true },
      take: 2000,
    });
    const out = [];
    for (const t of tasks) {
      const allocated = t.assignments.length
        ? round2(t.assignments.reduce((a, x) => a + Number(x.allocatedHours), 0))
        : Number(t.allocatedHours || 0);
      const actual = await actualHoursForTask(t.id, true);
      if (allocated > 0 && actual > allocated) {
        out.push({
          id: t.code,
          title: t.title,
          ...effortCols(allocated, actual),
        });
      }
    }
    return out;
  }

  if (type === 'daily') {
    type DailyAgg = {
      dateKey: string;
      employeeId: string;
      employee: string;
      actual: number;
    };
    const map = new Map<string, DailyAgg>();
    timesheets.forEach((t) => {
      const dk = dateKey(t.workDate);
      const key = dk + '|' + t.employeeId;
      const cur = map.get(key) || {
        dateKey: dk,
        employeeId: t.employeeId,
        employee: t.employee?.name || t.employeeId,
        actual: 0,
      };
      cur.actual += Number(t.hours);
      map.set(key, cur);
    });
    const out = [];
    for (const row of map.values()) {
      const allocated = await employeeAssignedHours(row.employeeId, f, visible);
      out.push({
        date: formatDate(row.dateKey),
        employee: row.employee,
        ...effortCols(allocated, row.actual),
      });
    }
    return out.sort((a, b) => {
      const byDate = String(b.date).localeCompare(String(a.date));
      if (byDate) return byDate;
      return String(a.employee).localeCompare(String(b.employee));
    });
  }

  if (type === 'transitions') {
    const logs = await prisma.activityLog.findMany({
      where: {
        action: { in: ['stage_change', 'transition_blocked', 'reopened'] },
        entityType: 'task',
      },
      include: { user: { select: { name: true } } },
      orderBy: { at: 'desc' },
      take: 500,
    });
    const out = [];
    for (const a of logs) {
      const task = await prisma.task.findUnique({
        where: { id: a.entityId },
        select: { code: true, projectId: true, project: { select: { clientId: true } } },
      });
      if (!task) continue;
      if (visible !== 'all' && !visible.includes(task.projectId)) continue;
      if (f.projectId && task.projectId !== f.projectId) continue;
      if (f.clientId && task.project?.clientId !== f.clientId) continue;
      const details =
        typeof a.details === 'string'
          ? a.details
          : a.details
            ? JSON.stringify(a.details)
            : '';
      out.push({
        when: formatDateTime(a.at),
        task: task.code,
        user: a.user?.name || '',
        action: a.action,
        details,
      });
    }
    return out;
  }

  if (type === 'qa') {
    const where: Prisma.TaskWhereInput = { qaResult: { not: null } };
    if (visible !== 'all') where.projectId = { in: visible };
    if (f.projectId) where.projectId = f.projectId;
    stampClient(where, f);
    const qaTasks = await prisma.task.findMany({ where, take: 500 });
    const rows: Record<string, unknown>[] = qaTasks.map((t) => {
      const ev = t.qaEvidence as { note?: string } | null;
      return {
        type: 'task_qa',
        id: t.code,
        result: t.qaResult,
        evidence: ev?.note || '',
        approver: t.testerApproval ? 'Yes' : '',
      };
    });

    const bugWhere: Prisma.BugWhereInput = { status: BugStatus.Reopened };
    if (visible !== 'all') bugWhere.parentTask = { projectId: { in: visible } };
    if (f.projectId) bugWhere.parentTask = { projectId: f.projectId };
    stampBugClient(bugWhere, f);
    const reopened = await prisma.bug.findMany({
      where: bugWhere,
      include: { reporter: { select: { name: true } } },
      take: 500,
    });
    reopened.forEach((b) => {
      rows.push({
        type: 'bug_reopened',
        id: b.code,
        result: statusFromBugDb(b.status),
        evidence: b.resolutionNotes || '',
        approver: b.reporter?.name || '',
      });
    });
    return rows;
  }

  return [];
}

export async function buildMyWork(user: AuthUser) {
  const myTasks = await prisma.task.findMany({
    where: {
      OR: [
        { assigneeId: user.id },
        { assignments: { some: { employeeId: user.id } } },
        { contributors: { has: user.id } },
      ],
    },
    include: { assignments: true },
  });
  const myBugs = await prisma.bug.findMany({
    where: {
      OR: [
        { assigneeId: user.id },
        { reporterId: user.id },
        { assignments: { some: { employeeId: user.id } } },
      ],
    },
  });
  const today = todayKey();
  const weekStart = offsetKey(-6);
  const myTs = await prisma.timesheet.findMany({ where: { employeeId: user.id } });
  const todayH = myTs
    .filter((t) => dateKey(t.workDate) === today)
    .reduce((a, t) => a + Number(t.hours), 0);
  const weekH = myTs
    .filter((t) => dateKey(t.workDate) >= weekStart)
    .reduce((a, t) => a + Number(t.hours), 0);
  const top = myTasks.filter((t) => !t.parentId);
  const alloc = top.reduce((a, t) => {
    const fromAssign = t.assignments.reduce((s, x) => s + Number(x.allocatedHours), 0);
    return a + (fromAssign || Number(t.allocatedHours || 0));
  }, 0);
  const actual = myTs.reduce((a, t) => a + Number(t.hours), 0);
  const upcoming = top
    .filter((t) => t.dueDate && dateKey(t.dueDate) >= today && t.stage !== TaskStage.Done)
    .sort((a, b) => dateKey(a.dueDate!).localeCompare(dateKey(b.dueDate!)))
    .slice(0, 6)
    .map((t) => ({
      id: t.id,
      code: t.code,
      title: t.title,
      dueDate: dateKey(t.dueDate!),
    }));

  const pending = myTasks
    .filter(
      (t) =>
        (t.stage === TaskStage.InProgress && !t.unitTestEvidence) ||
        (t.stage === TaskStage.CodeReviewQA && !t.qaResult) ||
        (t.stage === TaskStage.Backlog && t.assigneeId === user.id && !t.baAnalysisComplete)
    )
    .slice(0, 8)
    .map((t) => ({
      id: t.id,
      code: t.code,
      title: t.title,
      stage: stageFromDb(t.stage),
    }));

  const myTaskRows = top
    .filter(
      (t) =>
        t.lifecycle !== TaskLifecycle.Cancelled &&
        t.lifecycle !== TaskLifecycle.Done &&
        t.stage !== TaskStage.Done
    )
    .map((t) => ({
      id: t.id,
      code: t.code,
      title: t.title,
      stage: stageFromDb(t.stage),
      dueDate: t.dueDate ? dateKey(t.dueDate) : null,
      priority: t.priority,
    }));

  const openBugs = myBugs
    .filter((b) => b.status !== BugStatus.Closed && b.status !== BugStatus.Cancelled)
    .map((b) => ({
      id: b.id,
      code: b.code,
      title: b.title,
      status: statusFromBugDb(b.status),
    }));

  return {
    stats: [
      { label: 'My tasks', value: String(myTaskRows.length) },
      {
        label: 'My bugs',
        value: String(openBugs.length),
        danger: openBugs.length > 0,
      },
      {
        label: 'Today / Week hours',
        value: `${hoursToHhmm(todayH)} / ${hoursToHhmm(weekH)}`,
      },
      {
        label: 'Alloc vs Actual',
        value: `${hoursToHhmm(alloc)} / ${hoursToHhmm(actual)}`,
      },
    ],
    upcoming,
    pending,
    myTasks: myTaskRows,
    openBugs,
  };
}

export async function buildDashboard(user: AuthUser) {
  if (user.role === SystemRole.SuperAdmin) {
    const projects = await prisma.project.findMany();
    const employees = await prisma.employee.findMany({ where: { active: true } });
    const tasks = await prisma.task.findMany({
      where: { parentId: null },
      include: { assignments: true },
    });
    const bugs = await prisma.bug.findMany({ where: { status: { not: BugStatus.Closed } } });
    const timesheets = await prisma.timesheet.findMany();
    const today = todayKey();
    const overdue = tasks.filter(
      (t) => t.dueDate && dateKey(t.dueDate) < today && t.stage !== TaskStage.Done
    );
    const critical = bugs.filter((b) => b.severity === 'Critical');
    let alloc = 0;
    for (const p of projects) alloc += await projectAllocatedHours(p.id);
    const actual = timesheets.reduce((a, t) => a + Number(t.hours), 0);
    let overBudget = 0;
    for (const t of tasks) {
      const a = t.assignments.length
        ? t.assignments.reduce((s, x) => s + Number(x.allocatedHours), 0)
        : Number(t.allocatedHours || 0);
      const act = await actualHoursForTask(t.id, true);
      if (a > 0 && act > a) overBudget += 1;
    }
    const byStage: Record<string, number> = {};
    STAGE_LABELS.forEach((st) => {
      byStage[st] = tasks.filter((t) => stageFromDb(t.stage) === st).length;
    });
    const workload = [];
    for (const e of employees) {
      const hrs = timesheets
        .filter((t) => t.employeeId === e.id)
        .reduce((a, t) => a + Number(t.hours), 0);
      workload.push({
        name: e.name,
        role: systemRoleFromDb(e.role),
        hours: round2(hrs),
        hoursLabel: hoursToHhmm(hrs),
      });
    }
    const activities = await prisma.activityLog.findMany({
      include: { user: { select: { name: true } } },
      orderBy: { at: 'desc' },
      take: 8,
    });
    return {
      kind: 'super_admin' as const,
      stats: [
        {
          label: 'Active projects',
          value: String(projects.filter((p) => p.status === 'Active').length),
        },
        { label: 'Active employees', value: String(employees.length) },
        {
          label: 'Overdue tasks',
          value: String(overdue.length),
          danger: overdue.length > 0,
        },
        {
          label: 'Open / Critical bugs',
          value: `${bugs.length} / ${critical.length}`,
          danger: critical.length > 0,
        },
        {
          label: 'Alloc vs Actual',
          value: `${hoursToHhmm(alloc)} / ${hoursToHhmm(actual)}`,
        },
        {
          label: 'Over-budget tasks',
          value: String(overBudget),
          danger: overBudget > 0,
        },
      ],
      byStage,
      workload,
      activities: activities.map((a) => ({
        at: formatDateTime(a.at),
        user: a.user?.name || '',
        action: a.action,
        details:
          typeof a.details === 'string'
            ? a.details
            : a.details
              ? JSON.stringify(a.details)
              : '',
      })),
    };
  }

  if (user.role === SystemRole.ProjectAdmin) {
    const visible = await projectScope(user);
    const projectIds = visible === 'all' ? (await prisma.project.findMany()).map((p) => p.id) : visible;
    const projects = await prisma.project.findMany({
      where: { id: { in: projectIds } },
      include: { members: true },
    });
    const cards = [];
    const today = todayKey();
    for (const p of projects) {
      const tasks = await prisma.task.findMany({
        where: { projectId: p.id, parentId: null },
      });
      const done = tasks.filter((t) => t.stage === TaskStage.Done).length;
      const openBugs = await prisma.bug.count({
        where: {
          status: { not: BugStatus.Closed },
          parentTask: { projectId: p.id },
        },
      });
      const overdue = tasks.filter(
        (t) => t.dueDate && dateKey(t.dueDate) < today && t.stage !== TaskStage.Done
      ).length;
      const alloc = await projectAllocatedHours(p.id);
      const agg = await prisma.timesheet.aggregate({
        where: { projectId: p.id },
        _sum: { hours: true },
      });
      const actual = Number(agg._sum.hours || 0);
      const members = [];
      for (const m of p.members) {
        const emp = await prisma.employee.findUnique({ where: { id: m.employeeId } });
        const hrsAgg = await prisma.timesheet.aggregate({
          where: { projectId: p.id, employeeId: m.employeeId },
          _sum: { hours: true },
        });
        members.push({
          name: emp?.name || m.employeeId,
          hoursLabel: hoursToHhmm(Number(hrsAgg._sum.hours || 0)),
        });
      }
      cards.push({
        id: p.id,
        name: p.name,
        code: p.code,
        stats: [
          {
            label: 'Progress',
            value: tasks.length ? `${Math.round((done / tasks.length) * 100)}%` : '0%',
          },
          {
            label: 'Alloc / Actual',
            value: `${hoursToHhmm(alloc)} / ${hoursToHhmm(actual)}`,
          },
          { label: 'Open bugs', value: String(openBugs), danger: openBugs > 0 },
          { label: 'Overdue', value: String(overdue), danger: overdue > 0 },
        ],
        members,
      });
    }
    return { kind: 'project_admin' as const, projects: cards };
  }

  const my = await buildMyWork(user);
  return { kind: 'my_work' as const, ...my };
}

type DeadlineItem = {
  itemType: 'task' | 'subtask' | 'bug';
  id: string;
  code: string;
  title: string;
  stage: string;
  internalStatus: string;
  priority: number;
  severity?: string;
  dueDate: string;
  assigneeId: string | null;
  assigneeName: string;
  parentId?: string | null;
  parentCode?: string;
  blocking?: boolean;
  released?: boolean;
  href: string;
};

/** Project-level upcoming / overdue deadlines: tasks, subtasks, and open bugs. */
export async function buildDeadlineTracking(user: AuthUser) {
  const visible = await projectScope(user);
  const today = todayKey();
  const projectWhere: Prisma.ProjectWhereInput =
    visible === 'all' ? {} : { id: { in: visible.length ? visible : ['00000000-0000-0000-0000-000000000000'] } };

  const projects = await prisma.project.findMany({
    where: projectWhere,
    orderBy: { code: 'asc' },
    select: { id: true, code: true, name: true, endDate: true, status: true, clientId: true },
  });
  const projectIds = projects.map((p) => p.id);
  const projectById = new Map(projects.map((p) => [p.id, p]));

  // Include all dated tasks (incl. Released). UI filters Released by default.
  const tasks = await prisma.task.findMany({
    where: {
      dueDate: { not: null },
      projectId: { in: projectIds },
    },
    include: {
      assignee: { select: { id: true, name: true } },
      internalStatus: { select: { text: true } },
      parent: { select: { id: true, code: true } },
    },
    orderBy: [{ dueDate: 'asc' }, { code: 'asc' }],
  });

  const openBugs = await prisma.bug.findMany({
    where: {
      status: { not: BugStatus.Closed },
      parentTask: { projectId: { in: projectIds } },
    },
    include: {
      assignee: { select: { id: true, name: true } },
      parentTask: {
        select: {
          id: true,
          code: true,
          title: true,
          projectId: true,
          dueDate: true,
          parentId: true,
          internalStatus: { select: { text: true } },
        },
      },
    },
    orderBy: [{ priority: 'desc' }, { code: 'asc' }],
  });

  const itemsByProject = new Map<string, DeadlineItem[]>();

  function pushItem(projectId: string, item: DeadlineItem) {
    const list = itemsByProject.get(projectId) || [];
    list.push(item);
    itemsByProject.set(projectId, list);
  }

  tasks.forEach((t) => {
    const isSub = !!t.parentId;
    const statusText = t.internalStatus?.text || '';
    pushItem(t.projectId, {
      itemType: isSub ? 'subtask' : 'task',
      id: t.id,
      code: t.code,
      title: t.title,
      stage: stageFromDb(t.stage),
      internalStatus: statusText,
      priority: t.priority,
      dueDate: dateKey(t.dueDate!),
      assigneeId: t.assigneeId,
      assigneeName: t.assignee?.name || '',
      parentId: t.parentId,
      parentCode: t.parent?.code || '',
      released: statusText === 'Released',
      href: '#/tasks?task=' + encodeURIComponent(t.id),
    });
  });

  openBugs.forEach((b) => {
    const parent = b.parentTask;
    if (!parent) return;
    const proj = projectById.get(parent.projectId);
    const due = parent.dueDate
      ? dateKey(parent.dueDate)
      : proj?.endDate
        ? dateKey(proj.endDate)
        : today;
    const parentReleased = parent.internalStatus?.text === 'Released';
    pushItem(parent.projectId, {
      itemType: 'bug',
      id: b.id,
      code: b.code,
      title: b.title,
      stage: statusFromBugDb(b.status),
      internalStatus: parent.code ? 'on ' + parent.code : '',
      priority: b.priority,
      severity: b.severity,
      dueDate: due,
      assigneeId: b.assigneeId,
      assigneeName: b.assignee?.name || '',
      parentId: parent.id,
      parentCode: parent.code,
      blocking: !!b.blocking,
      released: parentReleased,
      href: '#/bugs?bug=' + encodeURIComponent(b.id),
    });
  });

  const out = projects
    .map((p) => {
      const list = itemsByProject.get(p.id) || [];
      if (!list.length && !p.endDate) return null;

      const deadlineMap = new Map<string, DeadlineItem[]>();
      list.forEach((item) => {
        const bucket = deadlineMap.get(item.dueDate) || [];
        bucket.push(item);
        deadlineMap.set(item.dueDate, bucket);
      });

      const deadlines = [...deadlineMap.entries()]
        .sort((a, b) => a[0].localeCompare(b[0]))
        .map(([date, items]) => {
          const kind = date < today ? 'overdue' : date === today ? 'today' : 'upcoming';
          const sorted = items.slice().sort((a, b) => {
            const typeOrder = { task: 0, subtask: 1, bug: 2 };
            const ta = typeOrder[a.itemType] ?? 9;
            const tb = typeOrder[b.itemType] ?? 9;
            if (ta !== tb) return ta - tb;
            if (a.parentCode !== b.parentCode) return String(a.parentCode || '').localeCompare(String(b.parentCode || ''));
            return a.code.localeCompare(b.code);
          });
          return {
            date,
            label: formatDate(date),
            kind,
            items: sorted,
            // alias for older clients
            tasks: sorted,
          };
        });

      const overdue = deadlines.filter((d) => d.kind === 'overdue').reduce((n, d) => n + d.items.length, 0);
      const upcoming = deadlines.filter((d) => d.kind !== 'overdue').reduce((n, d) => n + d.items.length, 0);
      const nextDate = deadlines.find((d) => d.kind !== 'overdue')?.date
        || deadlines[0]?.date
        || (p.endDate ? dateKey(p.endDate) : null);

      const taskCount = list.filter((i) => i.itemType === 'task').length;
      const subtaskCount = list.filter((i) => i.itemType === 'subtask').length;
      const bugCount = list.filter((i) => i.itemType === 'bug').length;

      return {
        id: p.id,
        code: p.code,
        name: p.name,
        clientId: p.clientId,
        status: p.status,
        endDate: p.endDate ? dateKey(p.endDate) : null,
        nextDeadline: nextDate,
        stats: {
          overdue,
          upcoming,
          total: list.length,
          tasks: taskCount,
          subtasks: subtaskCount,
          bugs: bugCount,
        },
        deadlines,
      };
    })
    .filter(Boolean)
    .sort((a, b) => {
      const aOver = a!.stats.overdue > 0 ? 0 : 1;
      const bOver = b!.stats.overdue > 0 ? 0 : 1;
      if (aOver !== bOver) return aOver - bOver;
      const ad = a!.nextDeadline || '9999';
      const bd = b!.nextDeadline || '9999';
      return ad.localeCompare(bd);
    });

  return {
    asOf: today,
    projects: out,
  };
}