google-apps-script/Code.gs

const SHEET_NAME = 'Productos';
const MOVEMENTS_SHEET_NAME = 'Movimientos';
const HEADERS = [
  'ID',
  'Producto',
  'Precio',
  'Cantidad',
  'Unidad',
  'FechaCaducidad',
  'FechaIngreso',
  'FechaProduccion',
  'Categoria',
  'CaducidadEstimada',
  'Notas',
  'Fuente',
  'StockActual',
  'ConsumidoTotal',
  'CreadoEn',
  'ActualizadoEn'
];
const MOVEMENT_HEADERS = [
  'ID',
  'ProductID',
  'Producto',
  'Tipo',
  'Cantidad',
  'Unidad',
  'Notas',
  'Fuente',
  'CreadoEn'
];

function doGet(e) {
  return handleRequest_({
    method: 'GET',
    params: e.parameter || {}
  });
}

function doPost(e) {
  const body = e.postData && e.postData.contents ? JSON.parse(e.postData.contents) : {};
  return handleRequest_({
    method: 'POST',
    params: body
  });
}

function handleRequest_(request) {
  try {
    const params = request.params || {};
    validateToken_(params.token);

    const action = params.action;
    if (action === 'addRecord') return jsonResponse_(addRecord_(params.record || {}));
    if (action === 'listRecords') return jsonResponse_(listRecords_(params.query || ''));
    if (action === 'consumeProduct') return jsonResponse_(consumeProduct_(params.consumption || {}));
    if (action === 'listMovements') return jsonResponse_(listMovements_());
    if (action === 'replaceSnapshot') {
      return jsonResponse_(replaceSnapshot_(params.records || [], params.movements || []));
    }

    return jsonResponse_({ ok: false, error: 'Accion no soportada.' });
  } catch (error) {
    return jsonResponse_({ ok: false, error: error.message });
  }
}

function getSheet_() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName(SHEET_NAME);

  if (!sheet) sheet = spreadsheet.insertSheet(SHEET_NAME);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
    sheet.setFrozenRows(1);
  }

  return sheet;
}

function getMovementSheet_() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName(MOVEMENTS_SHEET_NAME);

  if (!sheet) sheet = spreadsheet.insertSheet(MOVEMENTS_SHEET_NAME);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, MOVEMENT_HEADERS.length).setValues([MOVEMENT_HEADERS]);
    sheet.setFrozenRows(1);
  }

  return sheet;
}

function validateToken_(token) {
  const scriptToken = PropertiesService.getScriptProperties().getProperty('API_TOKEN');
  if (!scriptToken) throw new Error('Falta API_TOKEN en Script Properties.');
  if (token !== scriptToken) throw new Error('Token invalido.');
}

function addRecord_(record) {
  const sheet = getSheet_();
  const movementSheet = getMovementSheet_();
  const createdAt = new Date().toISOString();
  const id = record.id || Utilities.getUuid();

  sheet.appendRow([
    id,
    record.producto || '',
    Number(record.precio || 0),
    Number(record.cantidad || 0),
    record.unidad || 'unidad',
    record.fechaCaducidad || '',
    record.fechaIngreso || '',
    record.fechaProduccion || '',
    record.categoria || '',
    String(Boolean(record.caducidadEstimada || false)),
    record.notas || '',
    record.fuente || 'web',
    Number(record.stockActual != null ? record.stockActual : record.cantidad || 0),
    Number(record.consumidoTotal || 0),
    record.createdAt || createdAt,
    record.updatedAt || createdAt
  ]);

  movementSheet.appendRow([
    Utilities.getUuid(),
    id,
    record.producto || '',
    'ingreso',
    Number(record.cantidad || 0),
    record.unidad || 'unidad',
    record.notas || '',
    record.fuente || 'web',
    createdAt
  ]);

  return { ok: true, id: id, createdAt: createdAt };
}

function listRecords_(query) {
  const sheet = getSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length <= 1) return { ok: true, records: [] };

  const headers = values[0];
  const rows = values.slice(1).map(function(row) {
    return toRecord_(headers, row);
  });

  const normalizedQuery = String(query || '').toLowerCase().trim();
  const records = normalizedQuery
    ? rows.filter(function(record) {
        return JSON.stringify(record).toLowerCase().indexOf(normalizedQuery) >= 0;
      })
    : rows;

  return { ok: true, records: records };
}

function toRecord_(headers, row) {
  const result = {};
  headers.forEach(function(header, index) {
    result[header] = row[index];
  });

  return {
    id: result.ID || '',
    producto: result.Producto || '',
    precio: result.Precio || 0,
    cantidad: result.Cantidad || 0,
    unidad: result.Unidad || 'unidad',
    fechaCaducidad: formatDateValue_(result.FechaCaducidad),
    fechaIngreso: formatDateValue_(result.FechaIngreso),
    fechaProduccion: formatDateValue_(result.FechaProduccion),
    categoria: result.Categoria || '',
    caducidadEstimada: String(result.CaducidadEstimada).toLowerCase() === 'true',
    notas: result.Notas || '',
    fuente: result.Fuente || '',
    stockActual: result.StockActual || 0,
    consumidoTotal: result.ConsumidoTotal || 0,
    createdAt: formatDateValue_(result.CreadoEn),
    updatedAt: formatDateValue_(result.ActualizadoEn)
  };
}

function consumeProduct_(consumption) {
  const sheet = getSheet_();
  const movementSheet = getMovementSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length <= 1) throw new Error('No hay productos registrados.');

  const headers = values[0];
  const rows = values.slice(1);
  const targetProductId = consumption.productId || '';
  let rowIndex = -1;

  for (var i = 0; i < rows.length; i++) {
    var currentRecord = toRecord_(headers, rows[i]);
    if (targetProductId) {
      if (currentRecord.id === targetProductId) {
        rowIndex = i + 2;
        break;
      }
    } else if (
      String(currentRecord.producto).toLowerCase() === String(consumption.producto || '').toLowerCase() &&
      Number(currentRecord.stockActual || 0) > 0
    ) {
      rowIndex = i + 2;
      break;
    }
  }

  if (rowIndex === -1) throw new Error('No se encontro el producto para consumir.');

  const recordValues = sheet.getRange(rowIndex, 1, 1, headers.length).getValues()[0];
  const record = toRecord_(headers, recordValues);
  const quantity = Number(consumption.cantidad || 0);

  if (record.unidad !== consumption.unidad) throw new Error('La unidad no coincide.');
  if (Number(record.stockActual) < quantity) throw new Error('Stock insuficiente en Google Sheets.');

  const newStock = Number(record.stockActual) - quantity;
  const newConsumed = Number(record.consumidoTotal || 0) + quantity;
  const updatedAt = new Date().toISOString();
  const columns = headerIndexMap_(headers);

  sheet.getRange(rowIndex, columns.StockActual).setValue(newStock);
  sheet.getRange(rowIndex, columns.ConsumidoTotal).setValue(newConsumed);
  sheet.getRange(rowIndex, columns.ActualizadoEn).setValue(updatedAt);

  movementSheet.appendRow([
    Utilities.getUuid(),
    record.id,
    record.producto,
    'consumo',
    quantity,
    record.unidad,
    consumption.notas || '',
    consumption.fuente || 'telegram-consumo',
    updatedAt
  ]);

  return {
    ok: true,
    id: record.id,
    stockActual: newStock,
    consumidoTotal: newConsumed,
    updatedAt: updatedAt
  };
}

function listMovements_() {
  const sheet = getMovementSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length <= 1) return { ok: true, movements: [] };

  const headers = values[0];
  const movements = values.slice(1).map(function(row) {
    const result = {};
    headers.forEach(function(header, index) {
      result[header] = row[index];
    });

    return {
      id: result.ID || '',
      productId: result.ProductID || '',
      producto: result.Producto || '',
      tipo: result.Tipo || '',
      cantidad: result.Cantidad || 0,
      unidad: result.Unidad || 'unidad',
      notas: result.Notas || '',
      fuente: result.Fuente || '',
      createdAt: formatDateValue_(result.CreadoEn)
    };
  });

  return { ok: true, movements: movements };
}

function replaceSnapshot_(records, movements) {
  const productSheet = getSheet_();
  const movementSheet = getMovementSheet_();

  productSheet.clearContents();
  movementSheet.clearContents();

  productSheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  movementSheet.getRange(1, 1, 1, MOVEMENT_HEADERS.length).setValues([MOVEMENT_HEADERS]);

  if (records.length) {
    const productRows = records.map(function(record) {
      return [
        record.id || Utilities.getUuid(),
        record.producto || '',
        Number(record.precio || 0),
        Number(record.cantidad || 0),
        record.unidad || 'unidad',
        record.fechaCaducidad || '',
        record.fechaIngreso || '',
        record.fechaProduccion || '',
        record.categoria || '',
        String(Boolean(record.caducidadEstimada || false)),
        record.notas || '',
        record.fuente || 'web',
        Number(record.stockActual || 0),
        Number(record.consumidoTotal || 0),
        record.createdAt || '',
        record.updatedAt || record.createdAt || ''
      ];
    });
    productSheet.getRange(2, 1, productRows.length, HEADERS.length).setValues(productRows);
  }

  if (movements.length) {
    const movementRows = movements.map(function(movement) {
      return [
        movement.id || Utilities.getUuid(),
        movement.productId || '',
        movement.producto || '',
        movement.tipo || '',
        Number(movement.cantidad || 0),
        movement.unidad || 'unidad',
        movement.notas || '',
        movement.fuente || '',
        movement.createdAt || ''
      ];
    });
    movementSheet.getRange(2, 1, movementRows.length, MOVEMENT_HEADERS.length).setValues(movementRows);
  }

  productSheet.setFrozenRows(1);
  movementSheet.setFrozenRows(1);

  return { ok: true, records: records.length, movements: movements.length };
}

function headerIndexMap_(headers) {
  const result = {};
  headers.forEach(function(header, index) {
    result[header] = index + 1;
  });
  return result;
}

function formatDateValue_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value)) {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  }
  return value || '';
}

function jsonResponse_(payload) {
  return ContentService
    .createTextOutput(JSON.stringify(payload))
    .setMimeType(ContentService.MimeType.JSON);
}