import { getConnection } from '../../config/database.js';
import {
  findAll, findById, insert, update, softDelete, findMaxOrderNo, getStats, findByOrderId,
} from '../repositories/order.repo.js';
import {
  insert as insertProduct, updateStage as updateProductStage, markDispatched, markCancelled,
  softDeleteByOrderId, updateLine as updateProductLine, softDeleteLine as softDeleteProductLine,
  findByIdForUpdate as findOrderProductByIdForUpdate, updateJoNo as updateProductJoNo,
} from '../repositories/orderProduct.repo.js';
import {
  insert as insertStageHistory, findByOrderProductId, getCurrentStage, completeStage,
} from '../repositories/orderStageHistory.repo.js';
import { findByOrderId as findComments, insert as insertComment } from '../repositories/orderComment.repo.js';
import {
  insert as insertDispatchHistory, findByOrderProductId as findDispatchHistory,
  findById as findDispatchHistoryById, update as updateDispatchHistory, remove as removeDispatchHistory,
} from '../repositories/orderDispatchHistory.repo.js';
import { findByEmployeeId as findStageTransitions } from '../repositories/employeeStageTransition.repo.js';
import {
  markCompleted as markReturnCompleted, revertToActive as revertReturnToActive,
} from '../repositories/orderReturn.repo.js';
import { createWithId } from '../utils/id.js';
import { generateOrderNo, now } from '../utils/helpers.js';
import { NotFoundError, ConflictError, ForbiddenError } from '../utils/errors.js';
import { emitter } from '../events/emitter.js';
import { getStages } from './stage.service.js';
import { officeMinutesElapsed } from './officeCalendar.service.js';
import { PERMISSIONS } from '../constants/permissions.js';
import type { Order, OrderProduct, PaginatedResult, OrderComment } from '../types/index.js';
import type { PoolConnection } from 'mysql2/promise';

// A returned line (order_products.return_id set — see orderReturn.service.ts's
// approve()) needs its linked order_returns row kept in lockstep with the
// line's own dispatched state: 'active' while pending (re-)dispatch,
// 'completed' once fully dispatched again, and back to 'active' if a
// dispatch entry covering it is later edited/reverted below full. Every
// other order line has no return_id and this is a no-op for it.
async function syncReturnCompletion(
  returnId: string | undefined,
  fullyDispatched: boolean,
  userId: string,
  conn: PoolConnection,
): Promise<void> {
  if (!returnId) return;
  if (fullyDispatched) {
    await markReturnCompleted(returnId, userId, conn);
  } else {
    await revertReturnToActive(returnId, userId, conn);
  }
}

// 'not_started' is the same sentinel order_stage_history and
// employeeStageTransition rows already use for "hasn't entered any stage
// yet" — completedStages is 1-indexed into getStages(), 0 means not started.
function stageIdForCompletedStages(completedStages: number): string {
  if (completedStages <= 0) return 'not_started';
  return getStages()[completedStages - 1]?.id ?? 'not_started';
}

function stageLabel(stageId: string): string {
  if (stageId === 'not_started') return 'Not Started';
  return getStages().find((s) => s.id === stageId)?.name ?? stageId;
}

// Attaches office-hours-aware idle minutes to every line still sitting in a
// stage (stage_entered_at set) — see OrderProduct.stage_idle_minutes. Done
// here rather than in the repo so the frontend never has to recompute idle
// time itself from a raw Date.now() diff, which is exactly the bug this
// replaces (it ignored office hours/holidays entirely). Exported so
// dashboard.service.ts's ordersOverview() can attach the same field to its
// DASHBOARD_VIEW-only feed — without it, the Stage Idle modal's printed
// report can't tell which lines are actually idle for a dashboard-only login.
export function withIdleMinutes(order: Order): Order {
  const now = new Date();
  order.products = order.products.map((p) => {
    if (!p.stage_entered_at) return p;
    return { ...p, stage_idle_minutes: officeMinutesElapsed(new Date(p.stage_entered_at), now) };
  });
  return order;
}

export async function list(filters: Record<string, unknown>): Promise<PaginatedResult<Order>> {
  const { rows, total } = await findAll(filters as Parameters<typeof findAll>[0]);
  return { data: rows.map(withIdleMinutes), total, page: (filters.page as number) ?? 1, limit: (filters.limit as number) ?? 20 };
}

export async function get(id: string): Promise<Order> {
  const order = await findById(id);
  if (!order) throw new NotFoundError('Order not found');
  return withIdleMinutes(order);
}

export async function getNextOrderNo(): Promise<string> {
  const last = await findMaxOrderNo();
  return generateOrderNo(last ?? undefined);
}

export async function getOrderStats(): Promise<{ pending: number; completed: number; cancelled: number }> {
  return getStats();
}

// `approvalMeta` is only passed by orderApproval.service.ts's approve() when
// replaying a stored approval-request payload — it stamps the order with who
// originally requested it and who reviewed/approved it, instead of the
// default "creator approved their own order" set by order.repo.ts's insert().
// A normal direct create (by an orders.approve holder) omits it entirely and
// gets today's unchanged behavior.
export async function create(
  data: Partial<Order>,
  userId: string,
  approvalMeta?: { requestedByEmployeeId: string; approvedByEmployeeId: string },
): Promise<Order> {
  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    const orderData: Partial<Order> = approvalMeta
      ? {
          ...data,
          approval_status: 'approved',
          requested_by_employee_id: approvalMeta.requestedByEmployeeId,
          approved_by_employee_id: approvalMeta.approvedByEmployeeId,
          approved_at: now(),
        }
      : data;

    // order_no is separately unique-constrained, so it has to be recomputed
    // on every retry attempt alongside `id` — otherwise a collision on
    // order_no alone would keep failing the same way regardless of `id`.
    const { id } = await createWithId('orders', 'ORD', async (id) => {
      const lastNo = await findMaxOrderNo(conn);
      const orderNo = generateOrderNo(lastNo ?? undefined);
      return insert({ ...orderData, order_no: orderNo }, id, userId, conn);
    }, conn);

    for (const product of data.products ?? []) {
      const joNo = (product as OrderProduct).jo_no;

      // completed_stages defaults to 0 ("Not Started") — no stage_history
      // row until the user actually advances it via advanceStage(). Logging
      // a "Metal Issue entered" event here would misrepresent an order that
      // hasn't had any production stage actioned yet.
      await createWithId('order_products', 'OPRD', (opId) => insertProduct(
        { product_id: product.product_id as string, quantity: product.quantity as number, weight: product.weight as number, size: product.size as string | undefined, jo_no: joNo, remark: (product as OrderProduct).remark, hallmark_grade_id: (product as OrderProduct).hallmark_grade_id },
        opId, id, userId, conn,
      ), conn);
    }

    await conn.commit();

    emitter.emit('activity.create', { entity_type: 'order', entity_id: id, action: 'created', user_id: userId });

    const order = await findById(id);
    if (!order) throw new NotFoundError('Order not found');
    return order;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function updateOrder(id: string, data: Partial<Order>, userId: string): Promise<Order> {
  const existing = await findById(id);
  if (!existing) throw new NotFoundError('Order not found');

  // Stamp so_number_entered_at whenever the SO number is newly entered or
  // corrected to a different non-empty value — not touched otherwise (e.g.
  // saving the order again with the same SO number leaves it alone).
  const soNumberChanged = data.so_number !== undefined && data.so_number && data.so_number !== existing.so_number;
  const updateData: Partial<Order> = soNumberChanged ? { ...data, so_number_entered_at: now() } : data;

  const conn = await getConnection();
  try {
    await conn.beginTransaction();
    await update(id, updateData, userId, conn);

    if (data.products) {
      const incoming = data.products as (Partial<OrderProduct> & { id?: string })[];
      const incomingIds = new Set(incoming.filter((p) => p.id).map((p) => p.id));

      // A line missing from the incoming list was removed by the user in
      // the edit form — but only silently drop it if nothing has actually
      // happened to it yet. A dispatched or cancelled line carries real
      // transaction history and must be reversed through dispatch/cancel,
      // not discarded by editing the order around it.
      for (const existingLine of existing.products) {
        if (!incomingIds.has(existingLine.id) && !existingLine.dispatched && !existingLine.cancelled) {
          await softDeleteProductLine(existingLine.id, userId, conn);
        }
      }

      for (const product of incoming) {
        const joNo = product.jo_no ?? undefined;

        if (product.id) {
          const existingLine = existing.products.find((p) => p.id === product.id);
          if (!existingLine) continue;
          if (existingLine.dispatched || existingLine.cancelled) continue;

          const joNoChanged = Boolean(joNo) && joNo!.toLowerCase() !== (existingLine.jo_no ?? '').toLowerCase();

          await updateProductLine(product.id, {
            product_id: product.product_id as string,
            quantity: product.quantity as number,
            weight: product.weight as number,
            size: product.size as string | undefined,
            jo_no: joNo,
            jo_no_entered_at: joNoChanged ? now() : undefined,
            remark: product.remark,
            hallmark_grade_id: product.hallmark_grade_id,
          }, userId, conn);
        } else {
          // Same as order creation — starts at completed_stages = 0 with no
          // stage_history row until the user actually advances it.
          await createWithId('order_products', 'OPRD', (opId) => insertProduct(
            { product_id: product.product_id as string, quantity: product.quantity as number, weight: product.weight as number, size: product.size as string | undefined, jo_no: joNo, remark: product.remark, hallmark_grade_id: product.hallmark_grade_id },
            opId, id, userId, conn,
          ), conn);
        }
      }
    }

    await conn.commit();
    emitter.emit('activity.create', { entity_type: 'order', entity_id: id, action: 'updated', user_id: userId });
    return get(id);
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function remove(id: string, userId: string): Promise<void> {
  const existing = await findById(id);
  if (!existing) throw new NotFoundError('Order not found');

  const conn = await getConnection();
  try {
    await conn.beginTransaction();
    await softDelete(id, userId, conn);
    await softDeleteByOrderId(id, userId, conn);
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }

  emitter.emit('activity.create', { entity_type: 'order', entity_id: id, action: 'deleted', user_id: userId });
}

export async function advanceStage(
  orderId: string,
  productId: string,
  completedStages: number,
  remarks: string | null,
  userId: string,
  userPermissions: string[],
): Promise<OrderProduct> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');

  const product = order.products.find((p) => p.id === productId);
  if (!product) throw new NotFoundError('Product not found in order');

  const stageCount = getStages().length;
  if (completedStages > stageCount) {
    throw new ConflictError(`Completed stages must be <= ${stageCount}`);
  }

  // Full editors move any line through any stage freely, same as today.
  // Everyone else only gets restricted if an admin actually assigned them
  // stage transitions (see employee.service.ts) — no rows means unrestricted,
  // so this is opt-in tightening rather than a new requirement for every
  // stage-updater.
  const isUnrestricted = userPermissions.includes('*') || userPermissions.includes(PERMISSIONS.ORDERS_EDIT);
  if (!isUnrestricted) {
    const transitions = await findStageTransitions(userId);
    if (transitions.length > 0) {
      const fromStageId = stageIdForCompletedStages(product.completed_stages);
      const toStageId = stageIdForCompletedStages(completedStages);
      // Every assigned pair also implicitly permits the reverse move (e.g.
      // assigning "Not Started -> BM" lets an employee send a line back to
      // Not Started from BM too) — granted here at check time rather than
      // by also storing/showing a second row for it, so the admin's
      // "Stage assignments" list only ever shows exactly what they entered.
      const allowed = transitions.some(
        (t) =>
          (t.fromStageId === fromStageId && t.toStageId === toStageId) ||
          (t.fromStageId === toStageId && t.toStageId === fromStageId),
      );
      if (!allowed) {
        throw new ForbiddenError(`You are not allowed to move this item from "${stageLabel(fromStageId)}" to "${stageLabel(toStageId)}"`);
      }
    }
  }

  // The acting employee is always whoever is logged in — order_stage_history
  // used to accept an arbitrary employee_id from the request body, but
  // nothing ever populated it, so the audit trail (and the flowchart's
  // "moved by" label) always showed no one.
  const employeeId = userId;

  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    const prevStage = await getCurrentStage(productId, conn);
    if (prevStage) {
      await completeStage(productId, prevStage.stage_id, now(), conn);
    }

    // completedStages is 1-indexed ("1" means the first stage was just
    // entered, "0" means not started) — matches getStages()[n - 1] the
    // same way report.service.ts and the frontend already resolve it.
    //
    // A backward move (including reverting all the way to "Not Started",
    // where there's no matching stage) still needs its own history row
    // — otherwise log.service.ts's stage-change feed has no record the
    // transition ever happened, since it only ever reads *inserted* rows,
    // not the completed_at update above. 'not_started' is a sentinel stage_id
    // that intentionally doesn't match any stage in the master, so
    // log.service.ts's stageIndex() resolves it to -1 — the same value
    // already used everywhere else in this codebase to mean "Not Started".
    const newStage = getStages()[completedStages - 1];
    await createWithId('order_stage_history', 'STG', (historyId) => insertStageHistory(
      { order_product_id: productId, stage_id: newStage?.id ?? 'not_started', entered_at: now(), employee_id: employeeId ?? undefined, remarks: remarks ?? undefined },
      historyId, conn,
    ), conn);

    await updateProductStage(productId, completedStages, employeeId, userId, conn);

    await conn.commit();
    emitter.emit('activity.create', {
      entity_type: 'order_product', entity_id: productId, action: 'stage_changed',
      new_values: { completed_stages: completedStages }, user_id: userId,
    });

    const updatedOrder = await findById(orderId);
    const updatedProduct = updatedOrder?.products.find((p) => p.id === productId);
    if (!updatedProduct) throw new NotFoundError('Product not found after update');
    return updatedProduct;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

// Standalone JO-number edit from the Orders list's edit modal — narrower
// than routing through updateOrder(), which rewrites every line on the
// order and requires the full edit form's payload.
export async function updateJoNo(
  orderId: string,
  productId: string,
  joNo: string,
  userId: string,
): Promise<OrderProduct> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');

  const product = order.products.find((p) => p.id === productId);
  if (!product) throw new NotFoundError('Product not found in order');

  await updateProductJoNo(productId, { jo_no: joNo, jo_no_entered_at: now() }, userId);

  emitter.emit('activity.create', {
    entity_type: 'order_product', entity_id: productId, action: 'jo_no_changed',
    new_values: { jo_no: joNo }, user_id: userId,
  });

  const updatedOrder = await findById(orderId);
  const updatedProduct = updatedOrder?.products.find((p) => p.id === productId);
  if (!updatedProduct) throw new NotFoundError('Product not found after update');
  return updatedProduct;
}

export async function dispatchProducts(
  orderId: string,
  products: { order_product_id: string; quantity: number; weight?: number }[],
  dispatchDate: string,
  dispatchedByEmployeeId: string | null | undefined,
  userId: string,
  dispatchedByName?: string | null,
  receivedByName?: string | null,
): Promise<OrderProduct[]> {
  if (!dispatchedByEmployeeId && !dispatchedByName?.trim()) {
    throw new ConflictError('Select who is dispatching this item, or type their name');
  }
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');

  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    for (const item of products) {
      // Re-read the line under a row lock inside this transaction — the
      // `order` fetched above (before the transaction opened) can be stale
      // if another dispatch/cancel on the same line committed in between.
      // Locked by the line's own id, not product_id — an order can have
      // multiple lines for the same product (different JO numbers/sizes),
      // and locking by product_id alone would silently grab the wrong one.
      const op = await findOrderProductByIdForUpdate(item.order_product_id, conn);
      if (!op || op.order_id !== orderId) throw new NotFoundError(`Product line ${item.order_product_id} not found in order`);

      const dispatchableQty = op.quantity - (op.cancelled_quantity ?? 0);
      const pendingQty = dispatchableQty - (op.dispatched_quantity ?? 0);
      if (item.quantity > pendingQty) {
        throw new ConflictError(`Cannot dispatch ${item.quantity} unit(s) — only ${pendingQty} pending on this line`);
      }
      const dispatchWeight = item.weight ?? item.quantity * op.weight;
      const newDispatchedQty = (op.dispatched_quantity ?? 0) + item.quantity;
      const newDispatchedWt = (op.dispatched_weight ?? 0) + dispatchWeight;
      const fullyDispatched = newDispatchedQty >= dispatchableQty;

      await markDispatched(op.id, {
        dispatched_quantity: newDispatchedQty,
        dispatched_weight: newDispatchedWt,
        fully_dispatched: fullyDispatched,
        dispatch_date: dispatchDate,
        dispatched_by_employee_id: dispatchedByEmployeeId ?? null,
        dispatched_by_name: dispatchedByName ?? null,
        received_by_name: receivedByName ?? null,
      }, userId, conn);

      await createWithId('order_dispatch_history', 'DSH', (historyId) => insertDispatchHistory(
        {
          order_product_id: op.id, quantity: item.quantity, weight: dispatchWeight, dispatch_date: dispatchDate,
          dispatched_by_employee_id: dispatchedByEmployeeId ?? null, dispatched_by_name: dispatchedByName ?? null,
          received_by_name: receivedByName ?? null,
        },
        historyId, conn,
      ), conn);

      // If this line was created by an approved return (return_id set — see
      // orderReturn.service.ts), keep the linked order_returns row's status
      // in step now that its (re-)dispatch just changed.
      await syncReturnCompletion(op.return_id, fullyDispatched, userId, conn);
    }

    await conn.commit();
    emitter.emit('activity.create', { entity_type: 'order', entity_id: orderId, action: 'dispatched', user_id: userId });

    const updated = await findById(orderId);
    return updated?.products.filter((p) => products.some((d) => d.order_product_id === p.id)) ?? [];
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function cancelProducts(
  orderId: string,
  products: { order_product_id: string; quantity: number; weight: number }[],
  cancelDate: string,
  cancelledByEmployeeId: string,
  reason: string,
  userId: string,
): Promise<OrderProduct[]> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');

  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    for (const item of products) {
      const op = await findOrderProductByIdForUpdate(item.order_product_id, conn);
      if (!op || op.order_id !== orderId) throw new NotFoundError(`Product line ${item.order_product_id} not found in order`);

      const currentDispatchedQty = op.dispatched_quantity ?? 0;
      const currentCancelledQty = op.cancelled_quantity ?? 0;
      const cancellableQty = op.quantity - currentDispatchedQty - currentCancelledQty;

      if (cancellableQty <= 0 || op.cancelled) {
        throw new ConflictError(`This product line has no remaining quantity to cancel`);
      }
      if (item.quantity > cancellableQty) {
        throw new ConflictError(`Cannot cancel ${item.quantity} unit(s) — only ${cancellableQty} remaining unit(s) on this line`);
      }

      const newCancelledQty = currentCancelledQty + item.quantity;
      const newCancelledWt = (op.cancelled_weight ?? 0) + item.weight;
      const fullyCancelled = (newCancelledQty + currentDispatchedQty) >= op.quantity;

      await markCancelled(op.id, {
        cancelled: fullyCancelled,
        cancelled_quantity: newCancelledQty,
        cancelled_weight: newCancelledWt,
        cancel_date: cancelDate,
        cancelled_by_employee_id: cancelledByEmployeeId,
        cancel_reason: reason,
      }, userId, conn);
    }

    const remainingLines = await findByOrderId(orderId, conn);
    if (remainingLines.length > 0 && remainingLines.every((p) => p.cancelled)) {
      await update(orderId, { lifecycle: 'cancelled' }, userId, conn);
    }

    await conn.commit();
    emitter.emit('activity.create', { entity_type: 'order', entity_id: orderId, action: 'cancelled', user_id: userId });

    const updated = await findById(orderId);
    return updated?.products.filter((p) => products.some((d) => d.order_product_id === p.id)) ?? [];
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export async function getComments(orderId: string): Promise<OrderComment[]> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');
  return findComments(orderId);
}

export async function addComment(orderId: string, comment: string, employeeId: string): Promise<OrderComment> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');
  await createWithId('order_comments', 'CMT', (id) => insertComment({ order_id: orderId, employee_id: employeeId, comment }, id));
  const comments = await findComments(orderId);
  return comments[comments.length - 1]!;
}

export async function getDispatchHistory(orderId: string, productId: string) {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');
  const product = order.products.find((p) => p.id === productId);
  if (!product) throw new NotFoundError('Product not found in order');
  return findDispatchHistory(productId);
}

// Powers the order flowchart/timeline screen — one call returns the full
// per-transition stage history (entered_at/completed_at, not just the
// single overwritten stage_updated_at column) for every product on the
// order in one shot, keyed by order_product_id.
export async function getOrderTimeline(orderId: string): Promise<{ order: Order; productHistories: Record<string, Awaited<ReturnType<typeof findByOrderProductId>>> }> {
  const order = await findById(orderId);
  if (!order) throw new NotFoundError('Order not found');

  const entries = await Promise.all(
    order.products.map(async (product) => [product.id, await findByOrderProductId(product.id)] as const),
  );

  return { order, productHistories: Object.fromEntries(entries) };
}

export async function editDispatchHistory(
  orderId: string,
  orderProductId: string,
  historyId: string,
  data: { quantity: number; weight: number; dispatchDate: string; dispatchedByEmployeeId?: string | null; dispatchedByName?: string | null; receivedByName?: string | null },
  userId: string,
) {
  if (!data.dispatchedByEmployeeId && !data.dispatchedByName?.trim()) {
    throw new ConflictError('Select who is dispatching this item, or type their name');
  }
  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    const op = await findOrderProductByIdForUpdate(orderProductId, conn);
    if (!op || op.order_id !== orderId) throw new NotFoundError('Product not found in order');

    const entry = await findDispatchHistoryById(historyId, conn);
    if (!entry || entry.order_product_id !== orderProductId) throw new NotFoundError('Dispatch history entry not found');

    if (data.quantity <= 0 || data.weight <= 0) {
      throw new ConflictError('Quantity and weight must be greater than zero');
    }

    const dispatchableQty = op.quantity - (op.cancelled_quantity ?? 0);
    const otherDispatched = (op.dispatched_quantity ?? 0) - entry.quantity;
    const newTotalQty = otherDispatched + data.quantity;
    if (newTotalQty > dispatchableQty) {
      throw new ConflictError(`Cannot set this line's dispatched quantity to ${newTotalQty} — only ${dispatchableQty} on this line`);
    }

    await updateDispatchHistory(historyId, {
      quantity: data.quantity,
      weight: data.weight,
      dispatch_date: data.dispatchDate,
      dispatched_by_employee_id: data.dispatchedByEmployeeId ?? null,
      dispatched_by_name: data.dispatchedByName ?? null,
      received_by_name: data.receivedByName ?? null,
    }, conn);

    const history = await findDispatchHistory(orderProductId, conn);
    const totalQty = history.reduce((sum, h) => sum + h.quantity, 0);
    const totalWeight = history.reduce((sum, h) => sum + h.weight, 0);
    const latest = [...history].sort((a, b) => a.dispatch_date.localeCompare(b.dispatch_date)).at(-1);

    const fullyDispatchedAfterEdit = totalQty >= dispatchableQty;
    await markDispatched(op.id, {
      dispatched_quantity: totalQty,
      dispatched_weight: totalWeight,
      fully_dispatched: fullyDispatchedAfterEdit,
      dispatch_date: latest?.dispatch_date ?? data.dispatchDate,
      dispatched_by_employee_id: latest?.dispatched_by_employee_id ?? data.dispatchedByEmployeeId ?? null,
      dispatched_by_name: latest?.dispatched_by_name ?? data.dispatchedByName ?? null,
      received_by_name: latest?.received_by_name ?? data.receivedByName ?? null,
    }, userId, conn);

    // An edit can walk a return-originated line's dispatched quantity either
    // up to full or back down below it — keep the linked order_returns row
    // (op.return_id) in step either way.
    await syncReturnCompletion(op.return_id, fullyDispatchedAfterEdit, userId, conn);

    await conn.commit();
    emitter.emit('activity.create', { entity_type: 'order', entity_id: orderId, action: 'dispatch_history_edited', user_id: userId });

    return findDispatchHistory(orderProductId);
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

// Fully undoes a wrong dispatch: deletes the history entry outright (no
// "reverted" audit row kept — see orderDispatchHistory.repo.ts's remove())
// and recalculates the line's dispatched totals from whatever entries
// remain, exactly as editDispatchHistory() does after an edit.
export async function revertDispatchHistory(
  orderId: string,
  orderProductId: string,
  historyId: string,
  userId: string,
) {
  const conn = await getConnection();
  try {
    await conn.beginTransaction();

    const op = await findOrderProductByIdForUpdate(orderProductId, conn);
    if (!op || op.order_id !== orderId) throw new NotFoundError('Product not found in order');

    const entry = await findDispatchHistoryById(historyId, conn);
    if (!entry || entry.order_product_id !== orderProductId) throw new NotFoundError('Dispatch history entry not found');

    await removeDispatchHistory(historyId, conn);

    const dispatchableQty = op.quantity - (op.cancelled_quantity ?? 0);
    const remaining = await findDispatchHistory(orderProductId, conn);
    const totalQty = remaining.reduce((sum, h) => sum + h.quantity, 0);
    const totalWeight = remaining.reduce((sum, h) => sum + h.weight, 0);
    const latest = [...remaining].sort((a, b) => a.dispatch_date.localeCompare(b.dispatch_date)).at(-1);

    const fullyDispatchedAfterRevert = totalQty > 0 && totalQty >= dispatchableQty;
    await markDispatched(op.id, {
      dispatched_quantity: totalQty,
      dispatched_weight: totalWeight,
      fully_dispatched: fullyDispatchedAfterRevert,
      dispatch_date: latest?.dispatch_date ?? op.dispatch_date ?? now(),
      dispatched_by_employee_id: latest?.dispatched_by_employee_id ?? op.dispatched_by_employee_id ?? userId,
      dispatched_by_name: latest?.dispatched_by_name ?? op.dispatched_by_name ?? null,
      received_by_name: latest?.received_by_name ?? op.received_by_name ?? null,
    }, userId, conn);

    // Same reasoning as editDispatchHistory()'s hook above — undoing a
    // dispatch entry can walk a return-originated line back below full.
    await syncReturnCompletion(op.return_id, fullyDispatchedAfterRevert, userId, conn);

    await conn.commit();
    emitter.emit('activity.create', { entity_type: 'order', entity_id: orderId, action: 'dispatch_reverted', user_id: userId });

    return findDispatchHistory(orderProductId);
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}