import type { PoolConnection } from 'mysql2/promise';
import { execute, queryWithConn } from '../../config/database.js';
import { now } from '../utils/helpers.js';
import type { OrderProduct } from '../types/index.js';

function rowToOrderProduct(row: Record<string, unknown>): OrderProduct {
  return {
    id: row.id as string,
    order_id: row.order_id as string,
    product_id: row.product_id as string,
    quantity: row.quantity as number,
    weight: Number(row.weight),
    size: (row.size as string) ?? undefined,
    jo_no: (row.jo_no as string) ?? undefined,
    jo_no_entered_at: row.jo_no_entered_at instanceof Date ? row.jo_no_entered_at.toISOString() : undefined,
    remark: (row.remark as string) ?? undefined,
    hallmark_grade_id: (row.hallmark_grade_id as string) ?? undefined,
    completed_stages: row.completed_stages as number,
    stage_updated_at: row.stage_updated_at instanceof Date ? row.stage_updated_at.toISOString() : undefined,
    dispatched: Boolean(row.dispatched),
    dispatched_quantity: (row.dispatched_quantity as number) ?? undefined,
    dispatched_weight: row.dispatched_weight != null ? Number(row.dispatched_weight) : undefined,
    dispatch_date: row.dispatch_date instanceof Date ? row.dispatch_date.toISOString() : undefined,
    dispatched_by_employee_id: (row.dispatched_by_employee_id as string) ?? undefined,
    dispatched_by_name: (row.dispatched_by_name as string) ?? undefined,
    received_by_name: (row.received_by_name as string) ?? undefined,
    cancelled: Boolean(row.cancelled),
    cancelled_quantity: (row.cancelled_quantity as number) ?? undefined,
    cancelled_weight: row.cancelled_weight != null ? Number(row.cancelled_weight) : undefined,
    cancel_date: row.cancel_date instanceof Date ? row.cancel_date.toISOString() : undefined,
    cancelled_by_employee_id: (row.cancelled_by_employee_id as string) ?? undefined,
    cancel_reason: (row.cancel_reason as string) ?? undefined,
    created_at: row.created_at instanceof Date ? row.created_at.toISOString() : undefined,
    updated_at: row.updated_at instanceof Date ? row.updated_at.toISOString() : undefined,
    return_id: (row.return_id as string) ?? undefined,
  };
}

// Locks a line by its own id (order_product.id) — distinct from
// order.repo.ts's findProductForUpdate, which looks a line up by the
// (order_id, product_id) pair instead.
export async function findByIdForUpdate(id: string, conn: PoolConnection): Promise<OrderProduct | null> {
  const sql = `
    SELECT id, order_id, product_id, quantity, weight, size, jo_no, jo_no_entered_at, remark, hallmark_grade_id,
           completed_stages, stage_updated_at, dispatched, dispatched_quantity, dispatched_weight, dispatch_date,
           dispatched_by_employee_id, dispatched_by_name, received_by_name, cancelled, cancelled_quantity, cancelled_weight, cancel_date,
           cancelled_by_employee_id, cancel_reason, created_at, updated_at, return_id
    FROM order_products
    WHERE id = ? AND deleted_at IS NULL
    FOR UPDATE
  `;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToOrderProduct(rows[0]!) : null;
}

// Used to block deleting a variant that's still on a *pending* order line —
// a line that's already fully dispatched or fully cancelled is historical
// and no longer needs the variant to stay live, so those don't block. This
// is what lets "closed" orders keep referencing a deleted variant (the join
// in order.repo.ts already resolves a soft-deleted variant's name to NULL,
// which the frontend shows as "Unknown item" instead of breaking).
export async function findOpenOrdersReferencingProduct(
  productId: string,
  conn?: PoolConnection,
): Promise<{ orderNo: string; joNo?: string }[]> {
  const rows = await queryWithConn(
    `SELECT o.order_no as order_no, op.jo_no as jo_no
     FROM order_products op
     JOIN orders o ON o.id = op.order_id AND o.deleted_at IS NULL
     WHERE op.product_id = ? AND op.deleted_at IS NULL AND op.dispatched = FALSE AND op.cancelled = FALSE`,
    [productId], conn,
  ) as unknown as { order_no: string; jo_no: string | null }[];
  return rows.map((r) => ({ orderNo: r.order_no, joNo: r.jo_no ?? undefined }));
}

export async function insert(
  data: { product_id: string; quantity: number; weight: number; size?: string; jo_no?: string; remark?: string; hallmark_grade_id?: string | null },
  id: string,
  orderId: string,
  createdBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    `INSERT INTO order_products (id, order_id, product_id, quantity, weight, size, jo_no, jo_no_entered_at, remark, hallmark_grade_id, created_by)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id, orderId, data.product_id, data.quantity, data.weight, data.size ?? null, data.jo_no ?? null,
      // Stamped at insert time whenever a JO number is provided up front —
      // updateOrder() in order.service.ts handles the "added/changed later" case.
      data.jo_no ? now() : null,
      data.remark ?? null, data.hallmark_grade_id ?? null, createdBy,
    ],
  );
}

// Used only by orderReturn.service.ts's approve() to apply an approved
// return to the *original* line in place — rolls its dispatched counters
// back by the returned quantity/weight (the exact inverse of what
// markDispatched above does for a normal dispatch) and marks it as
// currently at the chosen re-entry stage. Deliberately narrow: it never
// touches dispatch_date/dispatched_by_name/received_by_name/product_id/
// quantity/weight/size/jo_no, so the line's *original* delivery details
// stay visible until the line is actually redelivered (which goes through
// the ordinary markDispatched() path above and overwrites them then).
export async function applyReturn(
  productId: string,
  data: { dispatched_quantity: number; dispatched_weight: number; dispatched: boolean; return_id: string; completed_stages: number },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    `UPDATE order_products SET dispatched_quantity = ?, dispatched_weight = ?, dispatched = ?, return_id = ?,
     completed_stages = ?, stage_updated_at = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL`,
    [data.dispatched_quantity, data.dispatched_weight, data.dispatched, data.return_id, data.completed_stages, now(), updatedBy, productId],
  );
}

// Used only by orderReturn.service.ts's revert() — undoes an approved
// return's applyReturn() above by restoring the dispatched counters back
// toward what they were. Deliberately narrower still: doesn't touch
// return_id/completed_stages (the "Returned" badge disappears purely
// because order.repo.ts's return_status subquery excludes 'reverted'
// returns, not because this clears the FK — see that subquery's comment)
// or stage_updated_at (a delivered line's stage display is already hidden
// by orderProduct.dispatched in the frontend, same as any other line).
export async function applyRevert(
  productId: string,
  data: { dispatched_quantity: number; dispatched_weight: number; dispatched: boolean },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    `UPDATE order_products SET dispatched_quantity = ?, dispatched_weight = ?, dispatched = ?, updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [data.dispatched_quantity, data.dispatched_weight, data.dispatched, updatedBy, productId],
  );
}

export async function updateLine(
  id: string,
  data: { product_id: string; quantity: number; weight: number; size?: string | null; jo_no?: string | null; jo_no_entered_at?: string | null; remark?: string | null; hallmark_grade_id?: string | null },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  const sets = ['product_id = ?', 'quantity = ?', 'weight = ?', 'size = ?', 'jo_no = ?', 'remark = ?', 'hallmark_grade_id = ?'];
  const params: (string | number | null)[] = [data.product_id, data.quantity, data.weight, data.size ?? null, data.jo_no ?? null, data.remark ?? null, data.hallmark_grade_id ?? null];

  // Only set by updateOrder() in order.service.ts, and only when the JO
  // number for this line actually changed to a non-empty value.
  if (data.jo_no_entered_at !== undefined) {
    sets.push('jo_no_entered_at = ?');
    params.push(data.jo_no_entered_at);
  }

  sets.push('updated_by = ?');
  params.push(updatedBy, id);
  await e(`UPDATE order_products SET ${sets.join(', ')} WHERE id = ? AND deleted_at IS NULL`, params);
}

// Narrow update used by the Orders list's standalone "edit JO number" modal
// — unlike updateLine (which rewrites a whole line as part of a full order
// edit), this only ever touches jo_no/jo_no_entered_at.
export async function updateJoNo(
  id: string,
  data: { jo_no: string; jo_no_entered_at: string },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    'UPDATE order_products SET jo_no = ?, jo_no_entered_at = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL',
    [data.jo_no, data.jo_no_entered_at, updatedBy, id],
  );
}

export async function softDeleteLine(id: string, deletedBy: string, conn?: PoolConnection): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    'UPDATE order_products SET deleted_at = NOW(), deleted_by = ? WHERE id = ? AND deleted_at IS NULL',
    [deletedBy, id],
  );
}

export async function updateStage(
  productId: string,
  completedStages: number,
  employeeId: string | null,
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    'UPDATE order_products SET completed_stages = ?, stage_updated_at = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL',
    [completedStages, now(), updatedBy, productId],
  );
}

export async function markDispatched(
  productId: string,
  data: {
    dispatched_quantity: number;
    dispatched_weight: number;
    fully_dispatched: boolean;
    dispatch_date: string;
    dispatched_by_employee_id?: string | null;
    dispatched_by_name?: string | null;
    received_by_name?: string | null;
  },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    `UPDATE order_products SET dispatched = ?, dispatched_quantity = ?, dispatched_weight = ?,
     dispatch_date = ?, dispatched_by_employee_id = ?, dispatched_by_name = ?, received_by_name = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL`,
    [
      // mysql2 rejects a raw ISO string ("...T...Z") for a DATETIME column —
      // it only auto-formats a JS Date object correctly.
      data.fully_dispatched, data.dispatched_quantity, data.dispatched_weight,
      new Date(data.dispatch_date), data.dispatched_by_employee_id ?? null, data.dispatched_by_name ?? null, data.received_by_name ?? null, updatedBy, productId,
    ],
  );
}

export async function markCancelled(
  productId: string,
  data: { cancelled: boolean; cancelled_quantity: number; cancelled_weight: number; cancel_date: string; cancelled_by_employee_id: string; cancel_reason: string },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    `UPDATE order_products SET cancelled = ?, cancelled_quantity = ?, cancelled_weight = ?,
     cancel_date = ?, cancelled_by_employee_id = ?, cancel_reason = ?, updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [data.cancelled, data.cancelled_quantity, data.cancelled_weight, new Date(data.cancel_date),
     data.cancelled_by_employee_id, data.cancel_reason, updatedBy, productId],
  );
}

// Used by stage.service.ts to block deleting a stage that's already been
// reached (or passed through) by any order line — completed_stages is a
// position into the ordered stage list, so any line at or beyond this
// stage's position has real production history recorded against it.
export async function hasProductsAtOrPastStage(sortOrder: number, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    'SELECT 1 FROM order_products WHERE deleted_at IS NULL AND completed_stages >= ? LIMIT 1',
    [sortOrder], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}

export async function softDeleteByOrderId(orderId: string, deletedBy: string, conn?: PoolConnection): Promise<void> {
  const e = conn ? conn.execute.bind(conn) : execute;
  await e(
    'UPDATE order_products SET deleted_at = NOW(), deleted_by = ? WHERE order_id = ? AND deleted_at IS NULL',
    [deletedBy, orderId],
  );
}