api-server/src/routes/tasks.ts

import { Router } from "express";
import { AuthRequest, requireManagerOrAdmin, requireAuth } from "../middleware/auth.js";
import { getTaskVisibilityFilter, canAccessTask, canModifyTask, User, isAdmin, isHeadOfTeam, isTeamLead, isMember } from "../lib/auth-utils.js";
import { logAudit } from "../lib/audit-logger.js";
import { z } from "zod";
import { invalidateDashboardCache } from "./dashboard.js";

import { db } from "@workspace/db";
import { tasksTable, usersTable, notificationsTable, commentsTable, clientsTable, taskAttachmentsTable } from "@workspace/db";
import { eq, and, count, sql, inArray } from "drizzle-orm";
import multer from "multer";
import { uploadAttachment, deleteAttachmentFromStorage } from "./auth.js";
import {
  CreateTaskBody,
  UpdateTaskBody,
  UpdateTaskParams,
  DeleteTaskParams,
  UpdateTaskStatusParams,
  UpdateTaskStatusBody,
  ListTasksQueryParams,
} from "@workspace/api-zod";

const router = Router();

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("/tasks", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const params = ListTasksQueryParams.parse(req.query);
    const conditions = [];

    // Apply visibility filter
    const visibilityFilter = getTaskVisibilityFilter(user);
    if (visibilityFilter) conditions.push(visibilityFilter);

    if (params.assignedTeam) conditions.push(eq(tasksTable.assignedTeam, params.assignedTeam));
    if (params.fromTeam) conditions.push(eq(tasksTable.fromTeam, params.fromTeam));
    if (params.status) conditions.push(eq(tasksTable.status, params.status));
    if (params.taskType) conditions.push(eq(tasksTable.taskType, params.taskType));
    if (params.assignedUserId) conditions.push(eq(tasksTable.assignedUserId, Number(params.assignedUserId)));
    if (params.clientId) conditions.push(eq(tasksTable.clientId, Number(params.clientId)));
    if (params.date) {
      const d = new Date(params.date);
      const next = new Date(d);
      next.setDate(next.getDate() + 1);
      conditions.push(sql`${tasksTable.createdAt} >= ${d.toISOString()} AND ${tasksTable.createdAt} < ${next.toISOString()}`);
    }

    const tasks = await db.select().from(tasksTable)
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(sql`${tasksTable.createdAt} DESC`);

    const userIds = [...new Set([
      ...tasks.map(t => t.createdByUserId).filter(Boolean),
      ...tasks.map(t => t.assignedUserId).filter(Boolean),
    ])] as number[];

    const users = userIds.length > 0
      ? await db.select().from(usersTable).where(inArray(usersTable.id, userIds))
      : [];
    const userMap = Object.fromEntries(users.map(u => [u.id, { ...u, createdAt: u.createdAt.toISOString() }]));

    const commentCounts = await db.select({
      taskId: commentsTable.taskId,
      cnt: count(),
    }).from(commentsTable).groupBy(commentsTable.taskId);
    const commentCountMap = Object.fromEntries(commentCounts.map(c => [c.taskId, Number(c.cnt)]));

    const clientIds = [...new Set(tasks.map(t => t.clientId).filter(Boolean))] as number[];
    const clients = clientIds.length > 0
      ? await db.select({ id: clientsTable.id, name: clientsTable.name }).from(clientsTable).where(inArray(clientsTable.id, clientIds))
      : [];
    const clientMap = Object.fromEntries(clients.map(c => [c.id, c.name]));

    const result = tasks.map(task => serializeTask(task, {
      createdByUser: task.createdByUserId ? userMap[task.createdByUserId] : null,
      assignedUser: task.assignedUserId ? userMap[task.assignedUserId] : null,
      commentCount: commentCountMap[task.id] ?? 0,
      clientName: task.clientId ? (clientMap[task.clientId] ?? null) : null,
    }));

    res.json(result);
  } catch (err) {
    req.log.error({ err }, "Failed to list tasks");
    res.status(500).json({ error: "Internal server error" });
  }
});

const createTaskSchema = z.object({
  title: z.string().trim().min(1, "العنوان مطلوب"),
  description: z.string().optional().default(""),
  assignedUserId: z.coerce.number().int().positive("معرف المستخدم غير صالح").optional(),
  team: z.string().optional(),
  assignedTeam: z.string().optional(),
  fromTeam: z.string().optional(),
  status: z.enum(["open", "in_progress", "done", "not_started", "review", "completed", "cancelled"]).optional().default("not_started"),
  dueDate: z.string().or(z.date()).optional(),
  taskType: z.string().optional().default("general"),
  priority: z.string().optional().default("medium"),
  clientId: z.coerce.number().int().positive().optional(),
  createdByUserId: z.coerce.number().int().positive().optional(),
}).strict();

router.post("/tasks", requireAuth, async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    if (!user) { res.status(401).json({ error: "Unauthorized" }); return; }

    if (isMember(user)) {
      res.status(403).json({ error: "Forbidden: members cannot create tasks" });
      return;
    }

    const parseResult = createTaskSchema.safeParse(req.body);
    if (!parseResult.success) {
      res.status(400).json({ error: parseResult.error.errors[0].message });
      return;
    }

    const { title, description, assignedUserId, team, assignedTeam, fromTeam, status, dueDate, taskType, priority, clientId } = parseResult.data;

    const targetTeam = team ?? assignedTeam ?? user.team;
    const sourceTeam = fromTeam ?? user.team;

    let assignee = null;
    if (assignedUserId) {
      const [foundAssignee] = await db.select().from(usersTable).where(eq(usersTable.id, assignedUserId));
      if (!foundAssignee) {
        res.status(400).json({ error: "Assigned user not found" });
        return;
      }
      assignee = foundAssignee;
    }

    if (!isAdmin(user)) {
      if ((assignee && assignee.team !== user.team) || targetTeam !== user.team) {
        res.status(403).json({ error: "Forbidden: you can only create tasks for users in your team" });
        return;
      }
    }

    const [task] = await db.insert(tasksTable).values({
      title,
      description: description || null,
      taskType,
      status: status === "open" ? "not_started" : (status === "done" ? "completed" : status),
      priority,
      fromTeam: sourceTeam,
      assignedTeam: targetTeam,
      clientId: clientId ?? null,
      createdByUserId: user.id,
      assignedUserId: assignedUserId ?? null,
      dueDate: dueDate ? new Date(dueDate) : null,
    }).returning();

    logAudit({
      userId: user.id,
      action: "task_created",
      entityType: "task",
      entityId: task.id,
      details: {
        taskId: task.id,
        title: task.title,
        description: task.description,
        assignedUserId: task.assignedUserId,
        team: task.assignedTeam,
        priority: task.priority,
        status: task.status,
        dueDate: task.dueDate ? task.dueDate.toISOString() : null,
        clientId: task.clientId,
      },
      req,
    });

    if (assignedUserId) {
      await db.insert(notificationsTable).values({
        userId: assignedUserId,
        taskId: task.id,
        type: "task_assigned",
        message: `مهمة جديدة: "${title}" من فريق ${sourceTeam}`,
      });
    }

    invalidateDashboardCache();
    res.status(201).json(serializeTask(task, {
      createdByUser: { ...user, createdAt: user.createdAt.toISOString() },
      assignedUser: assignee ? { ...assignee, createdAt: assignee.createdAt.toISOString() } : null,
      commentCount: 0,
    }));
  } catch (err) {
    req.log.error({ err: err instanceof Error ? err.message : String(err) }, "Failed to create task");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.get("/tasks/:id", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const { id } = UpdateTaskParams.parse({ id: Number(req.params.id) });
    const [task] = await db.select().from(tasksTable).where(eq(tasksTable.id, id));
    if (!task) { res.status(404).json({ error: "Task not found" }); return; }

    if (!canAccessTask(user, task)) {
      res.status(403).json({ error: "Forbidden: You do not have access to this task" });
      return;
    }

    const [creator, assignee] = await Promise.all([
      task.createdByUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.createdByUserId)).then(r => r[0]) : null,
      task.assignedUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.assignedUserId)).then(r => r[0]) : null,
    ]);

    const [{ cnt }] = await db.select({ cnt: count() }).from(commentsTable).where(eq(commentsTable.taskId, id));

    res.json(serializeTask(task, {
      createdByUser: creator ? { ...creator, createdAt: creator.createdAt.toISOString() } : null,
      assignedUser: assignee ? { ...assignee, createdAt: assignee.createdAt.toISOString() } : null,
      commentCount: Number(cnt),
    }));
  } catch (err) {
    req.log.error({ err }, "Failed to get task");
    res.status(500).json({ error: "Internal server error" });
  }
});

const updateTaskSchema = z.object({
  title: z.string().trim().min(1, "العنوان مطلوب").optional(),
  description: z.string().optional(),
  assignedUserId: z.coerce.number().int().positive("معرف المستخدم غير صالح").optional(),
  team: z.string().optional(),
  assignedTeam: z.string().optional(),
  status: z.enum(["open", "in_progress", "done", "not_started", "review", "completed", "cancelled"]).optional(),
  dueDate: z.string().or(z.date()).optional(),
  taskType: z.string().optional(),
  priority: z.string().optional(),
  clientId: z.coerce.number().int().positive().optional(),
}).strict();

const updateTaskParamsSchema = z.object({
  id: z.coerce.number().int().positive("معرف المهمة غير صالح"),
});

router.patch("/tasks/:id", requireAuth, async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    if (!user) { res.status(401).json({ error: "Unauthorized" }); return; }

    const paramsResult = updateTaskParamsSchema.safeParse({ id: req.params.id });
    if (!paramsResult.success) {
      res.status(400).json({ error: paramsResult.error.errors[0].message });
      return;
    }
    const { id } = paramsResult.data;

    const parseResult = updateTaskSchema.safeParse(req.body);
    if (!parseResult.success) {
      res.status(400).json({ error: parseResult.error.errors[0].message });
      return;
    }
    const body = parseResult.data;

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

    if (isMember(user)) {
      if (existingTask.assignedUserId !== user.id) {
        res.status(403).json({ error: "Forbidden: You can only update tasks assigned to you" });
        return;
      }
      if (body.title !== undefined || body.assignedUserId !== undefined || body.team !== undefined || body.assignedTeam !== undefined || body.clientId !== undefined || body.taskType !== undefined || body.priority !== undefined || body.dueDate !== undefined) {
        res.status(403).json({ error: "Forbidden: Members can only update status and description" });
        return;
      }
    } else if (!isAdmin(user)) {
      let taskUserTeam: string | null = null;
      if (existingTask.assignedUserId) {
        const [assignee] = await db.select().from(usersTable).where(eq(usersTable.id, existingTask.assignedUserId));
        if (assignee) taskUserTeam = assignee.team;
      }
      const isMyTeamTask = existingTask.assignedTeam === user.team || existingTask.fromTeam === user.team || taskUserTeam === user.team;
      if (!isMyTeamTask) {
        res.status(403).json({ error: "Forbidden: You can only update tasks in your team" });
        return;
      }

      if (body.assignedUserId !== undefined && body.assignedUserId !== null) {
        const [newAssignee] = await db.select().from(usersTable).where(eq(usersTable.id, body.assignedUserId));
        if (!newAssignee || newAssignee.team !== user.team) {
          res.status(403).json({ error: "Forbidden: You can only assign tasks to users in your team" });
          return;
        }
      }
    }

    // Track changes for audit log
    const changes: any = {};
    if (body.title !== undefined && body.title !== existingTask.title) changes.title = { old: existingTask.title, new: body.title };
    if (body.description !== undefined && body.description !== existingTask.description) changes.description = { old: existingTask.description, new: body.description };
    if (body.taskType !== undefined && body.taskType !== existingTask.taskType) changes.taskType = { old: existingTask.taskType, new: body.taskType };
    if (body.priority !== undefined && body.priority !== existingTask.priority) changes.priority = { old: existingTask.priority, new: body.priority };
    if (body.assignedUserId !== undefined && body.assignedUserId !== existingTask.assignedUserId) changes.assignedUserId = { old: existingTask.assignedUserId, new: body.assignedUserId };
    if (body.dueDate !== undefined) {
      const oldDue = existingTask.dueDate ? existingTask.dueDate.toISOString() : null;
      const newDue = body.dueDate ? new Date(body.dueDate).toISOString() : null;
      if (oldDue !== newDue) changes.dueDate = { old: oldDue, new: newDue };
    }
    if (body.status !== undefined) {
      const st = body.status === "open" ? "not_started" : (body.status === "done" ? "completed" : body.status);
      if (st !== existingTask.status) changes.status = { old: existingTask.status, new: st };
    }
    if (body.team !== undefined && body.team !== existingTask.assignedTeam) changes.team = { old: existingTask.assignedTeam, new: body.team };
    if (body.assignedTeam !== undefined && body.assignedTeam !== existingTask.assignedTeam) changes.team = { old: existingTask.assignedTeam, new: body.assignedTeam };
    if (body.clientId !== undefined && body.clientId !== existingTask.clientId) changes.clientId = { old: existingTask.clientId, new: body.clientId };

    const updates: Partial<typeof tasksTable.$inferInsert> = {
      updatedAt: new Date(),
    };
    if (body.title !== undefined) updates.title = body.title;
    if (body.description !== undefined) updates.description = body.description ?? null;
    if (body.taskType !== undefined) updates.taskType = body.taskType;
    if (body.priority !== undefined) updates.priority = body.priority;
    if (body.assignedUserId !== undefined) updates.assignedUserId = body.assignedUserId ?? null;
    if (body.dueDate !== undefined) updates.dueDate = body.dueDate ? new Date(body.dueDate) : null;
    if (body.status !== undefined) {
      const st = body.status === "open" ? "not_started" : (body.status === "done" ? "completed" : body.status);
      updates.status = st;
      if (st === "completed") updates.completedAt = new Date();
    }
    if (body.team !== undefined) updates.assignedTeam = body.team;
    if (body.assignedTeam !== undefined) updates.assignedTeam = body.assignedTeam;
    if (body.clientId !== undefined) updates.clientId = body.clientId;

    const [task] = await db.update(tasksTable).set(updates).where(eq(tasksTable.id, id)).returning();
    if (!task) { res.status(404).json({ error: "Task not found" }); return; }

    invalidateDashboardCache();

    if (Object.keys(changes).length > 0) {
      logAudit({
        userId: user.id,
        action: "task_updated",
        entityType: "task",
        entityId: task.id,
        details: {
          taskId: task.id,
          title: task.title,
          changes,
        },
        req,
      });
    }

    const [creator, assignee] = await Promise.all([
      task.createdByUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.createdByUserId)).then(r => r[0]) : null,
      task.assignedUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.assignedUserId)).then(r => r[0]) : null,
    ]);

    const [{ cnt }] = await db.select({ cnt: count() }).from(commentsTable).where(eq(commentsTable.taskId, id));

    res.json(serializeTask(task, {
      createdByUser: creator ? { ...creator, createdAt: creator.createdAt.toISOString() } : null,
      assignedUser: assignee ? { ...assignee, createdAt: assignee.createdAt.toISOString() } : null,
      commentCount: Number(cnt),
    }));
  } catch (err) {
    req.log.error({ err: err instanceof Error ? err.message : String(err) }, "Failed to update task");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.delete("/tasks/:id", requireManagerOrAdmin, async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const { id } = DeleteTaskParams.parse({ id: Number(req.params.id) });

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

    if (!canModifyTask(user, existingTask)) {
      res.status(403).json({ error: "Forbidden: You do not have permission to delete this task" });
      return;
    }

    logAudit({
      userId: user.id,
      action: "task_deleted",
      entityType: "task",
      entityId: existingTask.id,
      details: {
        taskId: existingTask.id,
        title: existingTask.title,
        assignedUserId: existingTask.assignedUserId,
        team: existingTask.assignedTeam,
        status: existingTask.status,
        dueDate: existingTask.dueDate ? existingTask.dueDate.toISOString() : null,
      },
      req,
    });

    await db.delete(tasksTable).where(eq(tasksTable.id, id));
    invalidateDashboardCache();
    res.status(204).send();
  } catch (err) {
    req.log.error({ err }, "Failed to delete task");
    res.status(500).json({ error: "Internal server error" });
  }
});

router.patch("/tasks/:id/status", async (req: AuthRequest, res) => {
  try {
    const user = req.user as User;
    const { id } = UpdateTaskStatusParams.parse({ id: Number(req.params.id) });
    const { status } = req.body as { status: string };
    const changedByUserId = req.userId;
    UpdateTaskStatusBody.parse({ status });

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

    if (!canAccessTask(user, existingTask)) {
      res.status(403).json({ error: "Forbidden: You do not have permission to update this task's status" });
      return;
    }

    const updates: Partial<typeof tasksTable.$inferInsert> = {
      status,
      updatedAt: new Date(),
    };
    if (status === "completed") {
      updates.completedAt = new Date();
    }

    const [task] = await db.update(tasksTable).set(updates).where(eq(tasksTable.id, id)).returning();
    if (!task) { res.status(404).json({ error: "Task not found" }); return; }

    invalidateDashboardCache();

    if (status !== existingTask.status) {
      logAudit({
        userId: req.userId,
        action: "task_updated",
        entityType: "task",
        entityId: task.id,
        details: {
          taskId: task.id,
          title: task.title,
          changes: {
            status: { old: existingTask.status, new: task.status },
          },
        },
        req,
      });
    }

    const statusLabels: Record<string, string> = {
      not_started: "لم يبدأ",
      in_progress: "قيد التنفيذ",
      review: "قيد المراجعة",
      completed: "تم الإنجاز",
      cancelled: "ملغى",
    };

    const statusLabel = statusLabels[status] ?? status;

    await db.insert(commentsTable).values({
      taskId: id,
      userId: changedByUserId ?? null,
      content: `تم تغيير حالة المهمة إلى: ${statusLabel}`,
      isSystem: true,
    });

    const notifyUserIds = new Set<number>();
    if (task.createdByUserId) notifyUserIds.add(task.createdByUserId);
    if (task.assignedUserId) notifyUserIds.add(task.assignedUserId);

    if (notifyUserIds.size > 0) {
      await db.insert(notificationsTable).values(
        [...notifyUserIds].map(userId => ({
          userId,
          taskId: task.id,
          type: "status_changed",
          message: `تم تحديث حالة المهمة "${task.title}" إلى: ${statusLabel}`,
        }))
      );
    }

    const [creator, assignee] = await Promise.all([
      task.createdByUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.createdByUserId)).then(r => r[0]) : null,
      task.assignedUserId ? db.select().from(usersTable).where(eq(usersTable.id, task.assignedUserId)).then(r => r[0]) : null,
    ]);

    const [{ cnt }] = await db.select({ cnt: count() }).from(commentsTable).where(eq(commentsTable.taskId, id));

    res.json(serializeTask(task, {
      createdByUser: creator ? { ...creator, createdAt: creator.createdAt.toISOString() } : null,
      assignedUser: assignee ? { ...assignee, createdAt: assignee.createdAt.toISOString() } : null,
      commentCount: Number(cnt),
    }));
  } catch (err) {
    res.status(400).json({ error: "Invalid request" });
  }
});

// Configure Multer with memory storage and 10MB file size limit
const upload = multer({
  storage: multer.memoryStorage(),
  limits: { fileSize: 10 * 1024 * 1024 }
});

const ALLOWED_MIME_TYPES = [
  "image/jpeg",
  "image/png",
  "image/gif",
  "image/webp",
  "application/pdf",
  "application/msword", // .doc
  "application/vnd.openxmlformats-officedocument.wordprocessingml.document", // .docx
  "application/vnd.ms-excel", // .xls
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" // .xlsx
];

const ALLOWED_EXTENSIONS = [
  "jpg", "jpeg", "png", "gif", "webp", "pdf", "doc", "docx", "xls", "xlsx"
];

// POST /tasks/:id/attachments - Upload an attachment
router.post("/tasks/:id/attachments", requireAuth, upload.single("file"), async (req: AuthRequest, res) => {
  try {
    const taskId = Number(req.params.id);
    const user = req.user as User;
    
    // Check if task exists and user has access to it
    const [task] = await db.select().from(tasksTable).where(eq(tasksTable.id, taskId));
    if (!task) {
      return res.status(404).json({ error: "المهمة غير موجودة" });
    }
    
    if (!canAccessTask(user, task)) {
      return res.status(403).json({ error: "ليس لديك صلاحية للوصول لهذه المهمة" });
    }

    if (!req.file) {
      return res.status(400).json({ error: "لم يتم رفع أي ملف" });
    }

    const ext = req.file.originalname.split(".").pop()?.toLowerCase();
    if (!ext || !ALLOWED_EXTENSIONS.includes(ext)) {
      return res.status(400).json({ error: "نوع الملف غير مدعوم" });
    }

    // Upload to Supabase Storage
    const publicUrl = await uploadAttachment(req.file.buffer, req.file.originalname, req.file.mimetype);

    // Save to Database
    const [inserted] = await db.insert(taskAttachmentsTable).values({
      taskId,
      fileName: req.file.originalname,
      fileUrl: publicUrl,
      fileSize: req.file.size,
      mimeType: req.file.mimetype,
      uploadedByUserId: user.id
    }).returning();

    // Audit log
    await logAudit({
      userId: String(user.id),
      action: "attachment_uploaded",
      entityType: "task",
      entityId: taskId,
      details: {
        attachmentId: inserted.id,
        fileName: inserted.fileName,
        fileSize: inserted.fileSize,
        supabaseUid: user.userId
      },
      req
    });

    res.status(201).json({
      ...inserted,
      uploadedByUser: {
        id: user.id,
        name: user.name
      }
    });
    return;

  } catch (err: any) {
    console.error("Error uploading attachment:", err);
    res.status(500).json({ error: err.message || "حدث خطأ أثناء رفع الملف" });
    return;
  }
});

// GET /tasks/:id/attachments - Get all task attachments
router.get("/tasks/:id/attachments", requireAuth, async (req: AuthRequest, res) => {
  try {
    const taskId = Number(req.params.id);
    const user = req.user as User;

    // Check if task exists and user has access
    const [task] = await db.select().from(tasksTable).where(eq(tasksTable.id, taskId));
    if (!task) {
      return res.status(404).json({ error: "المهمة غير موجودة" });
    }

    if (!canAccessTask(user, task)) {
      return res.status(403).json({ error: "ليس لديك صلاحية للوصول لهذه المهمة" });
    }

    // Retrieve attachments joined with user
    const attachments = await db
      .select({
        id: taskAttachmentsTable.id,
        taskId: taskAttachmentsTable.taskId,
        fileName: taskAttachmentsTable.fileName,
        fileUrl: taskAttachmentsTable.fileUrl,
        fileSize: taskAttachmentsTable.fileSize,
        mimeType: taskAttachmentsTable.mimeType,
        uploadedByUserId: taskAttachmentsTable.uploadedByUserId,
        createdAt: taskAttachmentsTable.createdAt,
        uploadedByUser: {
          id: usersTable.id,
          name: usersTable.name
        }
      })
      .from(taskAttachmentsTable)
      .leftJoin(usersTable, eq(taskAttachmentsTable.uploadedByUserId, usersTable.id))
      .where(eq(taskAttachmentsTable.taskId, taskId))
      .orderBy(taskAttachmentsTable.createdAt);

    res.json(attachments.map(att => ({
      ...att,
      createdAt: att.createdAt.toISOString()
    })));
    return;

  } catch (err: any) {
    console.error("Error fetching attachments:", err);
    res.status(500).json({ error: "حدث خطأ أثناء جلب المرفقات" });
    return;
  }
});

// DELETE /tasks/:id/attachments/:attachmentId - Delete an attachment
router.delete("/tasks/:id/attachments/:attachmentId", requireAuth, async (req: AuthRequest, res) => {
  try {
    const taskId = Number(req.params.id);
    const attachmentId = Number(req.params.attachmentId);
    const user = req.user as User;

    const [attachment] = await db.select().from(taskAttachmentsTable).where(eq(taskAttachmentsTable.id, attachmentId));
    if (!attachment) {
      return res.status(404).json({ error: "المرفق غير موجود" });
    }

    if (attachment.taskId !== taskId) {
      return res.status(400).json({ error: "المرفق لا ينتمي لهذه المهمة" });
    }

    const isUploader = attachment.uploadedByUserId === user.id;
    const canDelete = isUploader || isAdmin(user) || 
                      isHeadOfTeam(user) || isTeamLead(user);

    if (!canDelete) {
      return res.status(403).json({ error: "غير مصرح لك بحذف هذا المرفق" });
    }

    // Delete from Supabase Storage
    try {
      await deleteAttachmentFromStorage(attachment.fileUrl);
    } catch (storageErr) {
      console.warn("Storage deletion warning (continuing DB deletion):", storageErr);
    }

    // Delete from DB
    await db.delete(taskAttachmentsTable).where(eq(taskAttachmentsTable.id, attachmentId));

    // Audit log
    await logAudit({
      userId: String(user.id),
      action: "attachment_deleted",
      entityType: "task",
      entityId: taskId,
      details: {
        attachmentId,
        fileName: attachment.fileName,
        supabaseUid: user.userId
      },
      req
    });

    res.status(204).end();
    return;

  } catch (err: any) {
    console.error("Error deleting attachment:", err);
    res.status(500).json({ error: "حدث خطأ أثناء حذف المرفق" });
    return;
  }
});

export default router;