import type { Context } from "hono";
import { error, success } from "@/lib/response";
import {
  EDM_WORKFLOW_INITIAL,
  isEDMEntityType,
} from "@/internal/edm/edm-workflow-states";
import { logError } from "@/lib/logger";
import { transporter, SMTP_FROM } from "@/lib/mailer";

// Email helpers for the SMTP mail alerts
async function sendAssignmentNotificationEmail(toEmail: string, entityName: string, entityType: string, assignorName: string) {
  try {
    await transporter.sendMail({
      from: SMTP_FROM,
      to: toEmail,
      subject: `[Workflow] Assigned: ${entityName}`,
      html: `
        <p>Dear Team Member,</p>
        <p>You have been assigned to work on the content for the ${entityType.toLowerCase().replace('_', ' ')}: <strong>${entityName}</strong> by <strong>${assignorName}</strong>.</p>
        <p>Please log in to the admin console to update the content.</p>
      `
    });
  } catch (err) {
    console.error("Failed to send assignment notification email:", err);
  }
}

async function sendCommentNotificationEmail(toEmail: string, commenterName: string, entityName: string, commentText: string) {
  try {
    await transporter.sendMail({
      from: SMTP_FROM,
      to: toEmail,
      subject: `[Workflow] New comment on: ${entityName}`,
      html: `
        <p>Hi,</p>
        <p><strong>${commenterName}</strong> left a comment on <strong>${entityName}</strong>:</p>
        <blockquote style="border-left: 4px solid #ccc; padding-left: 10px; margin-left: 0;">
          ${commentText}
        </blockquote>
      `
    });
  } catch (err) {
    console.error("Failed to send comment notification email:", err);
  }
}

async function sendChangeRequestEmail(toEmail: string, requesterName: string, entityName: string, description: string) {
  try {
    await transporter.sendMail({
      from: SMTP_FROM,
      to: toEmail,
      subject: `[Workflow] Change Request for: ${entityName}`,
      html: `
        <p>Hi,</p>
        <p><strong>${requesterName}</strong> requested changes for <strong>${entityName}</strong>:</p>
        <blockquote style="border-left: 4px solid #f00; padding-left: 10px; margin-left: 0;">
          ${description}
        </blockquote>
      `
    });
  } catch (err) {
    console.error("Failed to send change request email:", err);
  }
}

export const WorkflowController = (ctx: Context) => {
  const db = ctx.get("datastore");
  const mainDb = ctx.get("datastore");

  /**
   * Human-readable name for the record a task hangs off.
   *
   * Every notification path needs this, and each entity type lives in its own
   * table, so it is resolved in one place — a missed branch here does not fail
   * loudly, it just mails people about "Unknown Record".
   */
  const resolveEntityName = async (
    entityType: string,
    entityId: string,
  ): Promise<string> => {
    const table =
      entityType === "MEMBER_OFFER"
        ? "member_offers"
        : entityType === "RESORT"
          ? "resorts"
          : entityType === "EDM_TEMPLATE"
            ? "edm_templates"
            : entityType === "EDM_FOLDER"
              ? "edm_folders"
              : null;
    if (!table) return "Unknown Record";

    const row = await db
      .selectFrom(table as any)
      .select("name")
      .where("id", "=", entityId)
      .executeTakeFirst();
    return row?.name ?? "Unknown Record";
  };

  const handleNewAssignment = async (assigneeId: string, assignor: any, taskId: string, entityName: string, entityType: string) => {
    try {
      const assignee = await mainDb
        .selectFrom("console_users" as any)
        .select(["email", "first_name"])
        .where("id", "=", assigneeId)
        .executeTakeFirst();

      if (!assignee || !assignee.email) return;

      const title = "New Task Assigned";
      const message = `You have been assigned to ${entityType.toLowerCase().replace('_', ' ')}: "${entityName}" by ${assignor.first_name} ${assignor.last_name || ""}.`;

      // 1. Insert In-App Notification (Workflow database)
      await db
        .insertInto("workflow_notifications" as any)
        .values({
          recipient_id: assigneeId,
          title,
          message,
          task_id: taskId,
          is_read: false,
          created_at: new Date(),
        })
        .execute();

      // 2. Send SMTP Email Alert
      await sendAssignmentNotificationEmail(assignee.email, entityName, entityType, `${assignor.first_name} ${assignor.last_name || ""}`);
    } catch (err) {
      logError("Error in handleNewAssignment:", err);
    }
  };

  return {
    async ListTasks() {
      try {
        const q = ctx.req.query();
        const assigneeId = q.assignee_id?.trim();
        const entityType = q.entity_type?.trim();
        const entityId = q.entity_id?.trim();
        const status = q.status?.trim();

        let query = db
          .selectFrom("workflow_tasks as wt" as any)
          .select([
            "wt.id" as any,
            "wt.entity_type" as any,
            "wt.entity_id" as any,
            "wt.assignee_id" as any,
            "wt.assignor_id" as any,
            "wt.status" as any,
            "wt.due_at" as any,
            "wt.created_at" as any,
            "wt.updated_at" as any,
          ]);

        if (assigneeId) {
          query = query.where((eb: any) =>
            eb.or([
              eb("wt.assignee_id", "=", assigneeId),
              eb("wt.id", "in", db.selectFrom("workflow_task_assignees" as any).select("task_id").where("assignee_id", "=", assigneeId))
            ])
          );
        }
        if (entityType) query = query.where("wt.entity_type", "=", entityType);
        if (entityId) query = query.where("wt.entity_id", "=", entityId);
        if (status) query = query.where("wt.status", "=", status);

        const rawItems = await query.orderBy("wt.updated_at", "desc").execute();

        const taskIds = rawItems.map((item: any) => item.id);
        const taskAssigneesMap = new Map<string, string[]>();

        if (taskIds.length > 0) {
          const assigneesRows = await db
            .selectFrom("workflow_task_assignees" as any)
            .select(["task_id", "assignee_id"])
            .where("task_id", "in", taskIds)
            .execute();

          for (const row of assigneesRows) {
            const list = taskAssigneesMap.get(row.task_id) || [];
            list.push(row.assignee_id);
            taskAssigneesMap.set(row.task_id, list);
          }
        }

        // Perform memory-mapped join with console_users from mainDb
        const userIds = new Set<string>();
        for (const item of rawItems) {
          if (item.assignee_id) userIds.add(item.assignee_id);
          if (item.assignor_id) userIds.add(item.assignor_id);
          const taskAssignees = taskAssigneesMap.get(item.id) || [];
          for (const aId of taskAssignees) {
            userIds.add(aId);
          }
        }

        const userMap = new Map<string, { first_name: string; last_name: string; email: string }>();
        if (userIds.size > 0) {
          const users = await mainDb
            .selectFrom("console_users" as any)
            .select(["id", "first_name", "last_name", "email"])
            .where("id", "in", Array.from(userIds))
            .execute();
          for (const u of users) {
            userMap.set(u.id, {
              first_name: u.first_name || "",
              last_name: u.last_name || "",
              email: u.email || "",
            });
          }
        }

        const items = rawItems.map((item: any) => {
          const assignee = item.assignee_id ? userMap.get(item.assignee_id) : null;
          const assignor = item.assignor_id ? userMap.get(item.assignor_id) : null;
          const taskAssignees = taskAssigneesMap.get(item.id) || [];

          const assigneesList = taskAssignees.map((aId: string) => {
            const u = userMap.get(aId);
            return {
              id: aId,
              first_name: u?.first_name ?? "Unknown",
              last_name: u?.last_name ?? "",
              email: u?.email ?? "",
            };
          });

          return {
            ...item,
            assignee_first_name: assignee?.first_name ?? null,
            assignee_last_name: assignee?.last_name ?? null,
            assignee_email: assignee?.email ?? null,
            assignor_first_name: assignor?.first_name ?? null,
            assignor_last_name: assignor?.last_name ?? null,
            assignees: assigneesList,
            assignee_ids: taskAssignees,
          };
        });

        return success(ctx, items);
      } catch (err: any) {
        logError("[Workflows] ListTasks:", err);
        return error(ctx, err?.message ?? "Failed to list tasks", 500);
      }
    },

    /**
     * Open change requests pointing at given targets.
     *
     * The editor asks for this on every file open to draw gutter markers, so it
     * takes a comma-separated list of targets rather than one — a workspace with
     * eight tabs would otherwise be eight requests. Resolved requests are
     * excluded: a marker for something already fixed is noise.
     */
    async ListChangeRequestsByTarget() {
      try {
        const raw = (ctx.req.query("targets") ?? ctx.req.query("target_ref") ?? "").trim();
        const targets = raw
          .split(",")
          .map((t) => t.trim())
          .filter(Boolean)
          .slice(0, 50);

        if (targets.length === 0) return success(ctx, []);

        const rows = await db
          .selectFrom("workflow_change_requests as wcr" as any)
          .innerJoin("workflow_tasks as wt" as any, "wt.id" as any, "wcr.task_id" as any)
          .select([
            "wcr.id" as any,
            "wcr.task_id" as any,
            "wcr.description" as any,
            "wcr.status" as any,
            "wcr.created_at" as any,
            "wcr.requester_id" as any,
            "wcr.assignee_id" as any,
            "wcr.target_ref" as any,
            "wcr.target_label" as any,
            "wcr.line_number" as any,
            "wt.entity_type" as any,
            "wt.entity_id" as any,
          ])
          .where("wcr.target_ref", "in", targets)
          .where("wcr.status", "=", "PENDING")
          .orderBy("wcr.created_at", "desc")
          .execute();

        const userIds = new Set<string>();
        for (const r of rows) {
          if (r.requester_id) userIds.add(r.requester_id);
          if (r.assignee_id) userIds.add(r.assignee_id);
        }

        const userMap = new Map<string, { first_name: string; last_name: string }>();
        if (userIds.size > 0) {
          const users = await mainDb
            .selectFrom("console_users" as any)
            .select(["id", "first_name", "last_name"])
            .where("id", "in", Array.from(userIds))
            .execute();
          for (const u of users) {
            userMap.set(u.id, {
              first_name: u.first_name || "",
              last_name: u.last_name || "",
            });
          }
        }

        return success(
          ctx,
          rows.map((r: any) => ({
            ...r,
            requester_first_name: userMap.get(r.requester_id)?.first_name ?? null,
            requester_last_name: userMap.get(r.requester_id)?.last_name ?? null,
            assignee_first_name: r.assignee_id
              ? (userMap.get(r.assignee_id)?.first_name ?? null)
              : null,
            assignee_last_name: r.assignee_id
              ? (userMap.get(r.assignee_id)?.last_name ?? null)
              : null,
          })),
        );
      } catch (err: any) {
        logError("[Workflows] ListChangeRequestsByTarget:", err);
        return error(ctx, err?.message ?? "Failed to list change requests", 500);
      }
    },

    async GetTaskByEntity() {
      try {
        const { entityType, entityId } = ctx.req.param() as { entityType: string; entityId: string };

        const rawTask = await db
          .selectFrom("workflow_tasks as wt" as any)
          .select([
            "wt.id" as any,
            "wt.entity_type" as any,
            "wt.entity_id" as any,
            "wt.assignee_id" as any,
            "wt.assignor_id" as any,
            "wt.status" as any,
            "wt.due_at" as any,
            "wt.created_at" as any,
            "wt.updated_at" as any,
          ])
          .where("wt.entity_type", "=", entityType)
          .where("wt.entity_id", "=", entityId)
          .executeTakeFirst();

        if (!rawTask) {
          return success(ctx, {
            id: null,
            entity_type: entityType,
            entity_id: entityId,
            assignee_id: null,
            assignor_id: null,
            status: isEDMEntityType(entityType)
              ? EDM_WORKFLOW_INITIAL
              : "CONFIGURATION_DRAFT",
            comments: [],
            change_requests: [],
            assignees: [],
            assignee_ids: [],
          });
        }

        const taskAssigneeRows = await db
          .selectFrom("workflow_task_assignees" as any)
          .select("assignee_id")
          .where("task_id", "=", rawTask.id)
          .execute();
        const taskAssignees = taskAssigneeRows.map((r: any) => r.assignee_id);

        // Fetch task comments
        const rawComments = await db
          .selectFrom("workflow_comments as wc" as any)
          .select([
            "wc.id" as any,
            "wc.task_id" as any,
            "wc.comment_text" as any,
            "wc.created_at" as any,
            "wc.author_id" as any,
          ])
          .where("wc.task_id", "=", rawTask.id)
          .orderBy("wc.created_at", "asc")
          .execute();

        // Fetch task change requests
        const rawChangeRequests = await db
          .selectFrom("workflow_change_requests as wcr" as any)
          .select([
            "wcr.id" as any,
            "wcr.task_id" as any,
            "wcr.description" as any,
            "wcr.status" as any,
            "wcr.created_at" as any,
            "wcr.resolved_at" as any,
            "wcr.requester_id" as any,
            "wcr.resolved_by" as any,
            "wcr.target_ref" as any,
            "wcr.target_label" as any,
            "wcr.line_number" as any,
            "wcr.assignee_id" as any,
          ])
          .where("wcr.task_id", "=", rawTask.id)
          .orderBy("wcr.created_at", "desc")
          .execute();

        // Collect all console user IDs to fetch in a single batch query
        const userIds = new Set<string>();
        if (rawTask.assignee_id) userIds.add(rawTask.assignee_id);
        if (rawTask.assignor_id) userIds.add(rawTask.assignor_id);
        for (const aId of taskAssignees) {
          userIds.add(aId);
        }
        for (const c of rawComments) {
          if (c.author_id) userIds.add(c.author_id);
        }
        for (const cr of rawChangeRequests) {
          if (cr.requester_id) userIds.add(cr.requester_id);
          if (cr.resolved_by) userIds.add(cr.resolved_by);
          // A request can be handed to someone who is not on the task at all,
          // so their name is not covered by the assignee ids above.
          if (cr.assignee_id) userIds.add(cr.assignee_id);
        }

        const userMap = new Map<string, { first_name: string; last_name: string; email: string }>();
        if (userIds.size > 0) {
          const users = await mainDb
            .selectFrom("console_users" as any)
            .select(["id", "first_name", "last_name", "email"])
            .where("id", "in", Array.from(userIds))
            .execute();
          for (const u of users) {
            userMap.set(u.id, {
              first_name: u.first_name || "",
              last_name: u.last_name || "",
              email: u.email || "",
            });
          }
        }

        const assignee = rawTask.assignee_id ? userMap.get(rawTask.assignee_id) : null;
        const assignor = rawTask.assignor_id ? userMap.get(rawTask.assignor_id) : null;

        const assigneesList = taskAssignees.map((aId: string) => {
          const u = userMap.get(aId);
          return {
            id: aId,
            first_name: u?.first_name ?? "Unknown",
            last_name: u?.last_name ?? "",
            email: u?.email ?? "",
          };
        });

        const task = {
          ...rawTask,
          assignee_first_name: assignee?.first_name ?? null,
          assignee_last_name: assignee?.last_name ?? null,
          assignee_email: assignee?.email ?? null,
          assignor_first_name: assignor?.first_name ?? null,
          assignor_last_name: assignor?.last_name ?? null,
          assignees: assigneesList,
          assignee_ids: taskAssignees,
        };

        const comments = rawComments.map((c: any) => {
          const author = c.author_id ? userMap.get(c.author_id) : null;
          return {
            id: c.id,
            task_id: c.task_id,
            comment_text: c.comment_text,
            created_at: c.created_at,
            author_first_name: author?.first_name ?? null,
            author_last_name: author?.last_name ?? null,
          };
        });

        const changeRequests = rawChangeRequests.map((cr: any) => {
          const requester = cr.requester_id ? userMap.get(cr.requester_id) : null;
          const resolver = cr.resolved_by ? userMap.get(cr.resolved_by) : null;
          return {
            id: cr.id,
            task_id: cr.task_id,
            description: cr.description,
            status: cr.status,
            created_at: cr.created_at,
            resolved_at: cr.resolved_at,
            target_ref: cr.target_ref ?? null,
            target_label: cr.target_label ?? null,
            line_number: cr.line_number ?? null,
            assignee_id: cr.assignee_id ?? null,
            assignee_first_name: cr.assignee_id
              ? (userMap.get(cr.assignee_id)?.first_name ?? null)
              : null,
            assignee_last_name: cr.assignee_id
              ? (userMap.get(cr.assignee_id)?.last_name ?? null)
              : null,
            requester_first_name: requester?.first_name ?? null,
            requester_last_name: requester?.last_name ?? null,
            resolver_first_name: resolver?.first_name ?? null,
            resolver_last_name: resolver?.last_name ?? null,
          };
        });

        return success(ctx, { ...task, comments, change_requests: changeRequests });
      } catch (err: any) {
        logError("[Workflows] GetTaskByEntity:", err);
        return error(ctx, err?.message ?? "Failed to get task", 500);
      }
    },

    async UpsertTask() {
      try {
        const body = await ctx.req.json();
        const { entity_type, entity_id, status, due_at } = body;

        // CONFIGURATION_DRAFT belongs to the offer/resort vocabulary; an EDM
        // task with that status would show as unknown in the EDM picker.
        const defaultStatus = isEDMEntityType(entity_type ?? "")
          ? EDM_WORKFLOW_INITIAL
          : "CONFIGURATION_DRAFT";

        let assigneeIds: string[] | undefined = undefined;
        if (body.assignee_ids !== undefined) {
          assigneeIds = body.assignee_ids || [];
        } else if (body.assignee_id !== undefined) {
          assigneeIds = body.assignee_id ? [body.assignee_id] : [];
        }

        if (!entity_type || !entity_id) {
          return error(ctx, "entity_type and entity_id are required", 400);
        }

        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select(["id", "first_name", "last_name"])
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const entityName = await resolveEntityName(entity_type, entity_id);

        const existing = await db
          .selectFrom("workflow_tasks" as any)
          .select(["id", "assignee_id", "status"])
          .where("entity_type", "=", entity_type)
          .where("entity_id", "=", entity_id)
          .executeTakeFirst();

        let task: any;
        const now = new Date();

        if (existing) {
          let prevAssigneeIds: string[] = [];
          const rows = await db
            .selectFrom("workflow_task_assignees" as any)
            .select("assignee_id")
            .where("task_id", "=", existing.id)
            .execute();
          prevAssigneeIds = rows.map((r: any) => r.assignee_id);

          const updates: Record<string, any> = {
            updated_at: now,
            updated_by: adminEmail,
          };
          if (status !== undefined) updates.status = status;
          if (assigneeIds !== undefined) {
            updates.assignee_id = assigneeIds.length > 0 ? assigneeIds[0] : null;
          }
          if (due_at !== undefined) updates.due_at = due_at;

          task = await db
            .updateTable("workflow_tasks" as any)
            .set(updates)
            .where("id", "=", existing.id)
            .returningAll()
            .executeTakeFirstOrThrow();

          if (assigneeIds !== undefined) {
            const newlyAssigned = assigneeIds.filter((id: string) => !prevAssigneeIds.includes(id));

            await db
              .deleteFrom("workflow_task_assignees" as any)
              .where("task_id", "=", task.id)
              .execute();

            if (assigneeIds.length > 0) {
              await db
                .insertInto("workflow_task_assignees" as any)
                .values(assigneeIds.map((id: string) => ({ task_id: task.id, assignee_id: id })))
                .execute();
            }

            for (const newAssigneeId of newlyAssigned) {
              await handleNewAssignment(newAssigneeId, caller, task.id, entityName, entity_type);
            }
          }
        } else {
          task = await db
            .insertInto("workflow_tasks" as any)
            .values({
              entity_type,
              entity_id,
              assignee_id: assigneeIds && assigneeIds.length > 0 ? assigneeIds[0] : null,
              assignor_id: caller.id,
              status: status ?? defaultStatus,
              due_at: due_at ?? null,
              created_at: now,
              updated_at: now,
              created_by: adminEmail,
              updated_by: adminEmail,
            })
            .returningAll()
            .executeTakeFirstOrThrow();

          if (assigneeIds && assigneeIds.length > 0) {
            await db
              .insertInto("workflow_task_assignees" as any)
              .values(assigneeIds.map((id: string) => ({ task_id: task.id, assignee_id: id })))
              .execute();

            for (const newAssigneeId of assigneeIds) {
              await handleNewAssignment(newAssigneeId, caller, task.id, entityName, entity_type);
            }
          }
        }

        return success(ctx, task);
      } catch (err: any) {
        logError("[Workflows] UpsertTask:", err);
        return error(ctx, err?.message ?? "Failed to save task", 500);
      }
    },

    async AddComment() {
      try {
        const { id } = ctx.req.param() as { id: string };
        const body = await ctx.req.json();
        const { comment_text } = body;

        if (!comment_text?.trim()) {
          return error(ctx, "comment_text is required", 400);
        }

        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select(["id", "first_name", "last_name"])
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const task = await db
          .selectFrom("workflow_tasks" as any)
          .selectAll()
          .where("id", "=", id)
          .executeTakeFirst();
        if (!task) return error(ctx, "Task not found", 404);

        const now = new Date();
        const comment = await db
          .insertInto("workflow_comments" as any)
          .values({
            task_id: id,
            author_id: caller.id,
            comment_text: comment_text.trim(),
            created_at: now,
            updated_at: now,
          })
          .returningAll()
          .executeTakeFirstOrThrow();

        const entityName = await resolveEntityName(task.entity_type, task.entity_id);

        const assigneeRows = await db
          .selectFrom("workflow_task_assignees" as any)
          .select("assignee_id")
          .where("task_id", "=", task.id)
          .execute();
        const taskAssignees = assigneeRows.map((r: any) => r.assignee_id);

        const recipientIds = new Set<string>();
        for (const aId of taskAssignees) {
          if (aId !== caller.id) {
            recipientIds.add(aId);
          }
        }
        if (task.assignor_id && task.assignor_id !== caller.id) {
          recipientIds.add(task.assignor_id);
        }

        for (const recipientId of recipientIds) {
          const recipient = await mainDb
            .selectFrom("console_users" as any)
            .select(["email"])
            .where("id", "=", recipientId)
            .executeTakeFirst();

          if (recipient) {
            await db
              .insertInto("workflow_notifications" as any)
              .values({
                recipient_id: recipientId,
                title: "New Task Comment",
                message: `${caller.first_name} commented on "${entityName}": "${comment_text.trim().substring(0, 50)}..."`,
                task_id: task.id,
                is_read: false,
                created_at: new Date(),
              })
              .execute();

            if (recipient.email) {
              await sendCommentNotificationEmail(
                recipient.email,
                `${caller.first_name} ${caller.last_name || ""}`,
                entityName,
                comment_text.trim()
              );
            }
          }
        }

        return success(ctx, comment, "Comment added");
      } catch (err: any) {
        logError("[Workflows] AddComment:", err);
        return error(ctx, err?.message ?? "Failed to add comment", 500);
      }
    },

    async CreateChangeRequest() {
      try {
        const { id } = ctx.req.param() as { id: string };
        const body = await ctx.req.json();
        const {
          description,
          /**
           * Where the request points. All optional — a request about the whole
           * record is still a legitimate request, and the reviewer is not
           * always looking at a file when they raise one.
           */
          target_ref,
          target_label,
          line_number,
          assignee_id,
        } = body;

        if (!description?.trim()) {
          return error(ctx, "description is required", 400);
        }

        // A line with no file is meaningless — "line 42" of what? Reject it
        // rather than storing an anchor that can never be resolved.
        const lineNumber =
          line_number === null || line_number === undefined || line_number === ""
            ? null
            : Number(line_number);
        if (lineNumber !== null && (!Number.isInteger(lineNumber) || lineNumber < 1)) {
          return error(ctx, "line_number must be a positive integer", 400);
        }
        if (lineNumber !== null && !target_ref) {
          return error(ctx, "line_number requires target_ref", 400);
        }

        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select(["id", "first_name", "last_name"])
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const task = await db
          .selectFrom("workflow_tasks" as any)
          .selectAll()
          .where("id", "=", id)
          .executeTakeFirst();
        if (!task) return error(ctx, "Task not found", 404);

        const now = new Date();
        const cr = await db
          .insertInto("workflow_change_requests" as any)
          .values({
            task_id: id,
            requester_id: caller.id,
            description: description.trim(),
            status: "PENDING",
            created_at: now,
            target_ref: target_ref || null,
            target_label: target_label || null,
            line_number: lineNumber,
            assignee_id: assignee_id || null,
          })
          .returningAll()
          .executeTakeFirstOrThrow();

        await db
          .updateTable("workflow_tasks" as any)
          .set({ status: "CHANGE_REQUESTED", updated_at: now, updated_by: adminEmail })
          .where("id", "=", id)
          .execute();

        const entityName = await resolveEntityName(task.entity_type, task.entity_id);

        const assigneeRows = await db
          .selectFrom("workflow_task_assignees" as any)
          .select("assignee_id")
          .where("task_id", "=", task.id)
          .execute();
        const taskAssignees = assigneeRows.map((r: any) => r.assignee_id);

        /**
         * Who hears about it.
         *
         * A change request can name its own owner, and when it does that person
         * is told even if they are not on the task — being handed one line to
         * fix should not require being assigned the whole template. The task's
         * assignees are still notified, since the work is theirs to track.
         */
        const notifyIds = new Set<string>(taskAssignees);
        if (assignee_id) notifyIds.add(assignee_id);
        notifyIds.delete(caller.id);

        const anchorSuffix = cr.target_label
          ? ` (${cr.target_label}${cr.line_number ? `:${cr.line_number}` : ""})`
          : "";

        for (const assigneeId of notifyIds) {
          {
            const assignee = await mainDb
              .selectFrom("console_users" as any)
              .select(["email"])
              .where("id", "=", assigneeId)
              .executeTakeFirst();

            if (assignee) {
              await db
                .insertInto("workflow_notifications" as any)
                .values({
                  recipient_id: assigneeId,
                  title: "Change Request Created",
                  message: `Changes requested on "${entityName}"${anchorSuffix} by ${caller.first_name}.`,
                  task_id: task.id,
                  is_read: false,
                  created_at: new Date(),
                })
                .execute();

              if (assignee.email) {
                await sendChangeRequestEmail(
                  assignee.email,
                  `${caller.first_name} ${caller.last_name || ""}`,
                  `${entityName}${anchorSuffix}`,
                  description.trim()
                );
              }
            }
          }
        }

        return success(ctx, cr, "Change request created");
      } catch (err: any) {
        logError("[Workflows] CreateChangeRequest:", err);
        return error(ctx, err?.message ?? "Failed to create change request", 500);
      }
    },

    async ResolveChangeRequest() {
      try {
        const { id } = ctx.req.param() as { id: string };

        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select(["id", "first_name", "last_name"])
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const cr = await db
          .selectFrom("workflow_change_requests" as any)
          .selectAll()
          .where("id", "=", id)
          .executeTakeFirst();
        if (!cr) return error(ctx, "Change request not found", 404);

        const now = new Date();
        const updatedCr = await db
          .updateTable("workflow_change_requests" as any)
          .set({
            status: "RESOLVED",
            resolved_at: now,
            resolved_by: caller.id,
          })
          .where("id", "=", id)
          .returningAll()
          .executeTakeFirstOrThrow();

        await db
          .updateTable("workflow_tasks" as any)
          .set({ status: "CONTENT_IN_PROGRESS", updated_at: now, updated_by: adminEmail })
          .where("id", "=", cr.task_id)
          .execute();

        const task = await db
          .selectFrom("workflow_tasks" as any)
          .selectAll()
          .where("id", "=", cr.task_id)
          .executeTakeFirst();

        if (task && cr.requester_id !== caller.id) {
          const entityName = await resolveEntityName(task.entity_type, task.entity_id);

          await db
            .insertInto("workflow_notifications" as any)
            .values({
              recipient_id: cr.requester_id,
              title: "Change Request Resolved",
              message: `Change request on "${entityName}" has been resolved by ${caller.first_name}.`,
              task_id: task.id,
              is_read: false,
              created_at: new Date(),
            })
            .execute();
        }

        return success(ctx, updatedCr, "Change request resolved");
      } catch (err: any) {
        logError("[Workflows] ResolveChangeRequest:", err);
        return error(ctx, err?.message ?? "Failed to resolve change request", 500);
      }
    },

    async GetNotifications() {
      try {
        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select("id")
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const notifications = await db
          .selectFrom("workflow_notifications as wn" as any)
          .leftJoin("workflow_tasks as wt" as any, "wt.id" as any, "wn.task_id" as any)
          .select([
            "wn.id as id" as any,
            "wn.recipient_id as recipient_id" as any,
            "wn.title as title" as any,
            "wn.message as message" as any,
            "wn.is_read as is_read" as any,
            "wn.task_id as task_id" as any,
            "wn.created_at as created_at" as any,
            "wt.entity_type as entity_type" as any,
            "wt.entity_id as entity_id" as any,
          ])
          .where("wn.recipient_id", "=", caller.id)
          .where("wn.is_read", "=", false)
          .orderBy("wn.created_at", "desc")
          .execute();

        return success(ctx, notifications);
      } catch (err: any) {
        logError("[Workflows] GetNotifications:", err);
        return error(ctx, err?.message ?? "Failed to fetch notifications", 500);
      }
    },

    async MarkNotificationAsRead() {
      try {
        const { id } = ctx.req.param() as { id: string };

        const adminEmail = ctx.get("email") ?? ctx.get("adminEmail") ?? null;
        if (!adminEmail) return error(ctx, "Unauthorized", 401);

        const caller = await mainDb
          .selectFrom("console_users" as any)
          .select("id")
          .where("email", "=", adminEmail)
          .executeTakeFirst();
        if (!caller) return error(ctx, "User not found", 404);

        const notification = await db
          .updateTable("workflow_notifications" as any)
          .set({ is_read: true })
          .where("id", "=", id)
          .where("recipient_id", "=", caller.id)
          .returningAll()
          .executeTakeFirst();

        if (!notification) {
          return error(ctx, "Notification not found or access denied", 404);
        }

        return success(ctx, notification, "Notification marked as read");
      } catch (err: any) {
        logError("[Workflows] MarkNotificationAsRead:", err);
        return error(ctx, err?.message ?? "Failed to update notification", 500);
      }
    }
  };
};