MixedAssets/server/services/parts.js

const { all, audit, now, one, run, tx } = require('../db');

const STATUSES = ['active', 'inactive', 'discontinued'];
// Stock movements. receive/issue/adjust change on-hand; reserve/unreserve change the
// reserved (committed) quantity. `adjust` sets the on-hand count to an absolute value.
const TRANSACTION_TYPES = ['receive', 'issue', 'adjust', 'reserve', 'unreserve'];

const COLUMNS = [
  'part_number', 'name', 'description', 'category', 'manufacturer', 'manufacturer_part_number',
  'barcode', 'unit_of_measure', 'unit_cost', 'reorder_point', 'reorder_quantity', 'measurements',
  'supplier_contact_id', 'status', 'notes'
];

function round2(value) {
  return Math.round((Number(value || 0) + Number.EPSILON) * 100) / 100;
}

function num(value, fallback = 0) {
  const n = Number(value);
  return Number.isFinite(n) ? n : fallback;
}

function contactName(row, prefix) {
  if (!row || !row[`${prefix}_id`]) return null;
  const org = row[`${prefix}_org`];
  const person = [row[`${prefix}_first`], row[`${prefix}_last`]].filter(Boolean).join(' ').trim();
  return org || person || null;
}

function stockRows(partId) {
  return all(
    `SELECT s.*, c.organization AS location_org, c.first_name AS location_first, c.last_name AS location_last
     FROM part_stock s LEFT JOIN contacts c ON c.id = s.location_contact_id
     WHERE s.part_id = ? ORDER BY c.organization, s.aisle, s.bin, s.id`,
    [partId]
  ).map((s) => ({
    id: s.id,
    part_id: s.part_id,
    location_contact_id: s.location_contact_id,
    location_name: s.location_org || [s.location_first, s.location_last].filter(Boolean).join(' ').trim() || 'Unassigned',
    aisle: s.aisle,
    bin: s.bin,
    quantity: round2(s.quantity),
    reserved: round2(s.reserved),
    available: round2(s.quantity - s.reserved)
  }));
}

function partPhotos(partId) {
  return all('SELECT id, name, mime_type, data_base64 FROM part_photos WHERE part_id = ? ORDER BY id', [partId])
    .map((p) => ({ id: p.id, name: p.name, mime: p.mime_type, data: p.data_base64 }));
}

function partTransactions(partId, limit = 100) {
  return all(
    `SELECT t.id, t.type, t.quantity, t.unit_cost, t.reference, t.notes, t.created_at, t.asset_id,
       u.name AS created_by_name, a.asset_id AS asset_code,
       c.organization AS location_org, c.first_name AS location_first, c.last_name AS location_last,
       s.aisle, s.bin
     FROM part_transactions t
     LEFT JOIN users u ON u.id = t.created_by
     LEFT JOIN assets a ON a.id = t.asset_id
     LEFT JOIN part_stock s ON s.id = t.stock_id
     LEFT JOIN contacts c ON c.id = s.location_contact_id
     WHERE t.part_id = ? ORDER BY t.id DESC LIMIT ?`,
    [partId, limit]
  ).map((t) => ({
    id: t.id,
    type: t.type,
    quantity: round2(t.quantity),
    unit_cost: t.unit_cost == null ? null : round2(t.unit_cost),
    reference: t.reference,
    notes: t.notes,
    created_at: t.created_at,
    created_by_name: t.created_by_name,
    asset_code: t.asset_code,
    location_name: t.location_org || [t.location_first, t.location_last].filter(Boolean).join(' ').trim() || null,
    aisle: t.aisle,
    bin: t.bin
  }));
}

function totalsFor(stock) {
  const on_hand = round2(stock.reduce((sum, s) => sum + s.quantity, 0));
  const reserved = round2(stock.reduce((sum, s) => sum + s.reserved, 0));
  return { on_hand, reserved, available: round2(on_hand - reserved), location_count: stock.length };
}

function decorate(row, totals) {
  const onHand = totals.on_hand;
  return {
    ...row,
    supplier_name: contactName(row, 'supplier'),
    on_hand: onHand,
    reserved: totals.reserved,
    available: totals.available,
    location_count: totals.location_count,
    stock_value: round2(onHand * num(row.unit_cost)),
    low_stock: num(row.reorder_point) > 0 && onHand <= num(row.reorder_point)
  };
}

function listParts(query = {}, companyId) {
  const rows = all(
    `SELECT p.*,
       sup.id AS supplier_id, sup.organization AS supplier_org, sup.first_name AS supplier_first, sup.last_name AS supplier_last,
       COALESCE(st.on_hand, 0) AS on_hand, COALESCE(st.reserved, 0) AS reserved, COALESCE(st.locations, 0) AS location_count
     FROM parts p
     LEFT JOIN contacts sup ON sup.id = p.supplier_contact_id
     LEFT JOIN (
       SELECT part_id, SUM(quantity) AS on_hand, SUM(reserved) AS reserved, COUNT(*) AS locations
       FROM part_stock GROUP BY part_id
     ) st ON st.part_id = p.id
     WHERE (? IS NULL OR p.entity_id = ?)
       AND (? IS NULL OR p.status = ?)
       AND (? IS NULL OR p.category = ?)
       AND (? IS NULL OR (
         p.part_number LIKE '%' || ? || '%' OR p.name LIKE '%' || ? || '%' OR
         p.description LIKE '%' || ? || '%' OR p.barcode LIKE '%' || ? || '%' OR
         p.manufacturer_part_number LIKE '%' || ? || '%'
       ))
     ORDER BY p.name`,
    [
      companyId || null, companyId || null,
      query.status || null, query.status || null,
      query.category || null, query.category || null,
      query.search || null, query.search || null, query.search || null,
      query.search || null, query.search || null
    ]
  ).map((row) => {
    const onHand = round2(row.on_hand);
    const reserved = round2(row.reserved);
    return decorate(row, { on_hand: onHand, reserved, available: round2(onHand - reserved), location_count: row.location_count });
  });
  if (query.low_stock === 'true' || query.low_stock === true) return rows.filter((r) => r.low_stock);
  return rows;
}

function getPart(id, companyId) {
  const row = one(
    `SELECT p.*, sup.id AS supplier_id, sup.organization AS supplier_org, sup.first_name AS supplier_first, sup.last_name AS supplier_last
     FROM parts p LEFT JOIN contacts sup ON sup.id = p.supplier_contact_id WHERE p.id = ? AND (? IS NULL OR p.entity_id = ?)`,
    [id, companyId || null, companyId || null]
  );
  if (!row) return null;
  const stock = stockRows(id);
  const part = decorate(row, totalsFor(stock));
  part.stock = stock;
  part.photos = partPhotos(id);
  part.transactions = partTransactions(id);
  return part;
}

function normalize(body) {
  const input = {};
  for (const column of COLUMNS) {
    if (Object.prototype.hasOwnProperty.call(body, column)) input[column] = body[column];
  }
  input.status = STATUSES.includes(input.status) ? input.status : 'active';
  input.unit_of_measure = input.unit_of_measure || 'each';
  for (const numeric of ['unit_cost', 'reorder_point', 'reorder_quantity']) {
    if (input[numeric] !== undefined) input[numeric] = num(input[numeric]);
  }
  if (input.supplier_contact_id === '' || input.supplier_contact_id === undefined) input.supplier_contact_id = null;
  return input;
}

function createPart(body, userId, companyId) {
  const input = normalize(body);
  if (!input.part_number) throw Object.assign(new Error('A part number is required'), { status: 400 });
  if (one('SELECT id FROM parts WHERE part_number = ? AND entity_id = ?', [input.part_number, companyId])) {
    throw Object.assign(new Error('That part number already exists'), { status: 400 });
  }
  const ts = now();
  const cols = COLUMNS.filter((c) => input[c] !== undefined);
  const result = run(
    `INSERT INTO parts (entity_id, ${cols.join(', ')}, created_by, created_at, updated_at)
     VALUES (?, ${cols.map(() => '?').join(', ')}, ?, ?, ?)`,
    [companyId || null, ...cols.map((c) => input[c]), userId, ts, ts]
  );
  audit(userId, 'create', 'part', result.lastInsertRowid, null, { part_number: input.part_number });
  return getPart(result.lastInsertRowid, companyId);
}

function updatePart(id, body, userId, companyId) {
  const before = one('SELECT * FROM parts WHERE id = ?', [id]);
  if (!before || (companyId && before.entity_id !== companyId)) return null;
  const input = normalize({ ...before, ...body });
  if (input.part_number !== before.part_number && one('SELECT id FROM parts WHERE part_number = ? AND entity_id = ? AND id <> ?', [input.part_number, before.entity_id, id])) {
    throw Object.assign(new Error('That part number already exists'), { status: 400 });
  }
  run(
    `UPDATE parts SET ${COLUMNS.map((c) => `${c} = ?`).join(', ')}, updated_at = ? WHERE id = ?`,
    [...COLUMNS.map((c) => input[c] ?? null), now(), id]
  );
  audit(userId, 'update', 'part', id, before, body);
  return getPart(id, companyId);
}

function deletePart(id, userId, companyId) {
  const before = one('SELECT * FROM parts WHERE id = ?', [id]);
  if (!before || (companyId && before.entity_id !== companyId)) return false;
  run('DELETE FROM parts WHERE id = ?', [id]);
  audit(userId, 'delete', 'part', id, before, null);
  return true;
}

// ---- Stock locations -------------------------------------------------------

function partInCompany(partId, companyId) {
  return Boolean(one('SELECT id FROM parts WHERE id = ? AND (? IS NULL OR entity_id = ?)', [partId, companyId || null, companyId || null]));
}

function saveStock(partId, body, userId, companyId) {
  if (!partInCompany(partId, companyId)) return null;
  const ts = now();
  const location = body.location_contact_id || null;
  const aisle = body.aisle || null;
  const bin = body.bin || null;
  const quantity = num(body.quantity);
  const reserved = Math.max(0, Math.min(quantity, num(body.reserved)));
  if (body.id) {
    const existing = one('SELECT * FROM part_stock WHERE id = ? AND part_id = ?', [body.id, partId]);
    if (!existing) return null;
    run(
      'UPDATE part_stock SET location_contact_id = ?, aisle = ?, bin = ?, quantity = ?, reserved = ?, updated_at = ? WHERE id = ?',
      [location, aisle, bin, quantity, reserved, ts, body.id]
    );
    audit(userId, 'update', 'part_stock', body.id, existing, body);
  } else {
    const result = run(
      'INSERT INTO part_stock (part_id, location_contact_id, aisle, bin, quantity, reserved, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [partId, location, aisle, bin, quantity, reserved, ts, ts]
    );
    audit(userId, 'create', 'part_stock', result.lastInsertRowid, null, { part_id: partId });
  }
  return getPart(partId, companyId);
}

function deleteStock(partId, stockId, userId, companyId) {
  if (!partInCompany(partId, companyId)) return null;
  const result = run('DELETE FROM part_stock WHERE id = ? AND part_id = ?', [stockId, partId]);
  if (!result.changes) return null;
  audit(userId, 'delete', 'part_stock', stockId, { part_id: partId }, null);
  return getPart(partId, companyId);
}

// ---- Stock movements -------------------------------------------------------

function recordTransaction(partId, body, userId, companyId) {
  if (!partInCompany(partId, companyId)) return null;
  const type = TRANSACTION_TYPES.includes(body.type) ? body.type : 'receive';
  const stock = one('SELECT * FROM part_stock WHERE id = ? AND part_id = ?', [body.stock_id, partId]);
  if (!stock) throw Object.assign(new Error('Select a stock location for this movement'), { status: 400 });
  const qty = Math.abs(num(body.quantity));
  if (!qty && type !== 'adjust') throw Object.assign(new Error('Quantity must be greater than zero'), { status: 400 });

  let quantity = stock.quantity;
  let reserved = stock.reserved;
  switch (type) {
    case 'receive': quantity += qty; break;
    case 'issue': quantity = Math.max(0, quantity - qty); break;
    case 'adjust': quantity = num(body.quantity); break; // absolute count correction
    case 'reserve': reserved = Math.min(quantity, reserved + qty); break;
    case 'unreserve': reserved = Math.max(0, reserved - qty); break;
    default: break;
  }
  reserved = Math.min(reserved, quantity);

  return tx(() => {
    run('UPDATE part_stock SET quantity = ?, reserved = ?, updated_at = ? WHERE id = ?', [quantity, reserved, now(), stock.id]);
    run(
      `INSERT INTO part_transactions (part_id, stock_id, type, quantity, unit_cost, asset_id, reference, notes, created_by, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        partId, stock.id, type, type === 'adjust' ? num(body.quantity) : qty,
        body.unit_cost != null && body.unit_cost !== '' ? num(body.unit_cost) : null,
        body.asset_id || null, body.reference || null, body.notes || null, userId, now()
      ]
    );
    audit(userId, 'movement', 'part', partId, null, { type, quantity: qty, stock_id: stock.id });
    return getPart(partId, companyId);
  });
}

// ---- Photos ----------------------------------------------------------------

function addPhoto(partId, body, userId, companyId) {
  if (!partInCompany(partId, companyId)) return null;
  if (!body.data) throw Object.assign(new Error('Photo data is required'), { status: 400 });
  run(
    'INSERT INTO part_photos (part_id, name, mime_type, data_base64, created_at) VALUES (?, ?, ?, ?, ?)',
    [partId, body.name || null, body.mime || body.mime_type || 'image/jpeg', body.data, now()]
  );
  audit(userId, 'create', 'part_photo', partId, null, { name: body.name });
  return getPart(partId, companyId);
}

function deletePhoto(partId, photoId, userId, companyId) {
  if (!partInCompany(partId, companyId)) return null;
  const result = run('DELETE FROM part_photos WHERE id = ? AND part_id = ?', [photoId, partId]);
  if (!result.changes) return null;
  audit(userId, 'delete', 'part_photo', photoId, { part_id: partId }, null);
  return getPart(partId, companyId);
}

module.exports = {
  STATUSES,
  TRANSACTION_TYPES,
  addPhoto,
  createPart,
  deletePart,
  deletePhoto,
  deleteStock,
  getPart,
  listParts,
  recordTransaction,
  saveStock,
  updatePart
};