Code.js

const DB_CONFIG = {
  spreadsheetId: '12tIuZo3tclKlkWLD5dSb2k2-hkneApAJZroHWpguwcY',
  sheets: {
    USERS: 'Users',
    GROUPS: 'Groups',
    GROUP_MEMBERS: 'GroupMembers',
    INCOMES: 'Incomes',
    EXPENSES: 'Expenses',
    TAX_CONFIG: 'TaxConfig',
  },
};

const ROLE_RANK = { PUBLIC: 0, EDITOR: 1, ADMIN: 2 };

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('CrowWealth')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

/* ============== Helpers ============== */

function getSheet(name) {
  const sheet = SpreadsheetApp.openById(DB_CONFIG.spreadsheetId).getSheetByName(name);
  if (!sheet) throw new Error('No existe la hoja "' + name + '".');
  return sheet;
}

function getHeaders(sheet) {
  const lastColumn = sheet.getLastColumn();
  if (!lastColumn) return [];
  return sheet.getRange(1, 1, 1, lastColumn).getValues()[0].map(function (h) { return String(h).trim(); });
}

function getRows(sheet) {
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  if (lastRow < 2 || !lastColumn) return [];
  return sheet.getRange(2, 1, lastRow - 1, lastColumn).getValues();
}

function mapRow(headers, row) {
  return headers.reduce(function (acc, h, i) { acc[h] = row[i]; return acc; }, {});
}

function readAll(sheetName) {
  const sheet = getSheet(sheetName);
  const headers = getHeaders(sheet);
  const rows = getRows(sheet);
  return {
    sheet: sheet,
    headers: headers,
    rows: rows,
    records: rows.map(function (r) { return mapRow(headers, r); }).filter(hasAnyValue),
  };
}

function hasAnyValue(record) {
  return Object.values(record).some(function (v) { return String(v == null ? '' : v).trim() !== ''; });
}

function appendRecord(sheetName, record) {
  const sheet = getSheet(sheetName);
  const headers = getHeaders(sheet);
  const row = headers.map(function (h) { return h in record ? record[h] : ''; });
  sheet.appendRow(row);
  return record;
}

function findRowIndex(rows, headers, key, value) {
  const idx = headers.indexOf(key);
  if (idx === -1) return -1;
  return rows.findIndex(function (r) { return String(r[idx]).trim() === String(value).trim(); });
}

function updateRecord(sheetName, key, value, patch) {
  const data = readAll(sheetName);
  const rowIdx = findRowIndex(data.rows, data.headers, key, value);
  if (rowIdx === -1) throw new Error('Registro no encontrado en ' + sheetName + '.');
  const current = mapRow(data.headers, data.rows[rowIdx]);
  const next = Object.assign({}, current, patch);
  const values = data.headers.map(function (h) { return h in next ? next[h] : ''; });
  data.sheet.getRange(rowIdx + 2, 1, 1, data.headers.length).setValues([values]);
  return next;
}

function deleteRecord(sheetName, key, value) {
  const data = readAll(sheetName);
  const rowIdx = findRowIndex(data.rows, data.headers, key, value);
  if (rowIdx === -1) throw new Error('Registro no encontrado en ' + sheetName + '.');
  data.sheet.deleteRow(rowIdx + 2);
}

function deleteWhere(sheetName, predicate) {
  const data = readAll(sheetName);
  const headers = data.headers;
  const sheet = data.sheet;
  for (let i = data.rows.length - 1; i >= 0; i--) {
    const record = mapRow(headers, data.rows[i]);
    if (predicate(record)) sheet.deleteRow(i + 2);
  }
}

function sanitizeUser(user) {
  if (!user) return null;
  const clone = Object.assign({}, user);
  delete clone.password;
  return clone;
}

function findUserByUuid(uuid) {
  const data = readAll(DB_CONFIG.sheets.USERS);
  return data.records.find(function (u) { return String(u.uuid).trim() === String(uuid).trim(); }) || null;
}

function findUserByEmail(email) {
  const norm = String(email || '').trim().toLowerCase();
  const data = readAll(DB_CONFIG.sheets.USERS);
  return data.records.find(function (u) { return String(u.email || '').trim().toLowerCase() === norm; }) || null;
}

function getMemberRecord(groupUuid, userUuid) {
  const data = readAll(DB_CONFIG.sheets.GROUP_MEMBERS);
  return data.records.find(function (m) {
    return String(m.groupUuid).trim() === String(groupUuid).trim() &&
      String(m.userUuid).trim() === String(userUuid).trim();
  }) || null;
}

function requireRole(actorUuid, groupUuid, minRole) {
  const actor = findUserByUuid(actorUuid);
  if (!actor) throw new Error('Sesion invalida.');
  if (actor.type === 'ADMIN') return { actor: actor, role: 'ADMIN' };
  const member = getMemberRecord(groupUuid, actorUuid);
  if (!member) throw new Error('No tienes acceso a este grupo.');
  if (ROLE_RANK[member.role] < ROLE_RANK[minRole]) {
    throw new Error('Permisos insuficientes (' + member.role + ').');
  }
  return { actor: actor, role: member.role };
}

/* ============== Auth ============== */

function loginUser(email, password) {
  if (!String(email || '').trim()) throw new Error('Email obligatorio.');
  if (!String(password || '').trim()) throw new Error('Password obligatoria.');
  const user = findUserByEmail(email);
  if (!user || String(user.password || '').trim() !== String(password).trim()) {
    throw new Error('Credenciales invalidas.');
  }
  return sanitizeUser(user);
}

/* ============== Users CRUD ============== */

function getUsers() {
  return readAll(DB_CONFIG.sheets.USERS).records.map(sanitizeUser);
}

function createUser(payload) {
  const record = {
    uuid: payload.uuid || Utilities.getUuid(),
    name: payload.name || '',
    lastname: payload.lastname || '',
    email: String(payload.email || '').trim().toLowerCase(),
    phone: payload.phone || '',
    password: String(payload.password || '').trim(),
    type: payload.type || 'PUBLIC',
  };
  appendRecord(DB_CONFIG.sheets.USERS, record);
  return sanitizeUser(record);
}

function updateUser(uuid, payload) {
  const patch = {};
  if (payload.name !== undefined) patch.name = payload.name;
  if (payload.lastname !== undefined) patch.lastname = payload.lastname;
  if (payload.email !== undefined) patch.email = String(payload.email).trim().toLowerCase();
  if (payload.phone !== undefined) patch.phone = payload.phone;
  if (payload.password) patch.password = String(payload.password).trim();
  if (payload.type !== undefined) patch.type = payload.type;
  const next = updateRecord(DB_CONFIG.sheets.USERS, 'uuid', uuid, patch);
  return sanitizeUser(next);
}

function deleteUser(uuid) {
  deleteRecord(DB_CONFIG.sheets.USERS, 'uuid', uuid);
  deleteWhere(DB_CONFIG.sheets.GROUP_MEMBERS, function (m) { return String(m.userUuid) === String(uuid); });
  return { success: true, uuid: uuid };
}

/* ============== Context ============== */

function getMyContext(userUuid) {
  const user = findUserByUuid(userUuid);
  if (!user) throw new Error('Usuario no encontrado.');
  const groupsData = readAll(DB_CONFIG.sheets.GROUPS).records;
  const membersData = readAll(DB_CONFIG.sheets.GROUP_MEMBERS).records;
  let groupsWithRole = [];
  if (user.type === 'ADMIN') {
    groupsWithRole = groupsData.map(function (g) { return { group: g, role: 'ADMIN' }; });
  } else {
    const myMemberships = membersData.filter(function (m) { return String(m.userUuid) === String(userUuid); });
    groupsWithRole = myMemberships.map(function (m) {
      const group = groupsData.find(function (g) { return String(g.uuid) === String(m.groupUuid); });
      return group ? { group: group, role: m.role } : null;
    }).filter(function (x) { return !!x; });
  }
  return { user: sanitizeUser(user), groups: groupsWithRole };
}

/* ============== Groups CRUD ============== */

function createGroup(payload) {
  const owner = findUserByUuid(payload.ownerUuid);
  if (!owner) throw new Error('Owner invalido.');
  const uuid = Utilities.getUuid();
  const group = {
    uuid: uuid,
    ownerUuid: owner.uuid,
    name: payload.name || '',
    description: payload.description || '',
    createdAt: new Date().toISOString(),
  };
  appendRecord(DB_CONFIG.sheets.GROUPS, group);
  appendRecord(DB_CONFIG.sheets.GROUP_MEMBERS, {
    uuid: Utilities.getUuid(),
    groupUuid: uuid,
    userUuid: owner.uuid,
    role: 'ADMIN',
  });
  return group;
}

function updateGroup(uuid, payload, actorUuid) {
  requireRole(actorUuid, uuid, 'EDITOR');
  const patch = {};
  if (payload.name !== undefined) patch.name = payload.name;
  if (payload.description !== undefined) patch.description = payload.description;
  return updateRecord(DB_CONFIG.sheets.GROUPS, 'uuid', uuid, patch);
}

function deleteGroup(uuid, actorUuid) {
  requireRole(actorUuid, uuid, 'ADMIN');
  deleteRecord(DB_CONFIG.sheets.GROUPS, 'uuid', uuid);
  deleteWhere(DB_CONFIG.sheets.GROUP_MEMBERS, function (m) { return String(m.groupUuid) === String(uuid); });
  deleteWhere(DB_CONFIG.sheets.INCOMES, function (i) { return String(i.groupUuid) === String(uuid); });
  deleteWhere(DB_CONFIG.sheets.EXPENSES, function (e) { return String(e.groupUuid) === String(uuid); });
  return { success: true, uuid: uuid };
}

function getGroupDetail(uuid, actorUuid) {
  requireRole(actorUuid, uuid, 'PUBLIC');
  const groups = readAll(DB_CONFIG.sheets.GROUPS).records;
  const group = groups.find(function (g) { return String(g.uuid) === String(uuid); });
  if (!group) throw new Error('Grupo no encontrado.');
  const incomes = readAll(DB_CONFIG.sheets.INCOMES).records.filter(function (i) {
    return String(i.groupUuid) === String(uuid);
  }).map(normalizeIncome);
  const expenses = readAll(DB_CONFIG.sheets.EXPENSES).records.filter(function (e) {
    return String(e.groupUuid) === String(uuid);
  }).map(normalizeExpense);
  const members = readAll(DB_CONFIG.sheets.GROUP_MEMBERS).records.filter(function (m) {
    return String(m.groupUuid) === String(uuid);
  });
  const users = readAll(DB_CONFIG.sheets.USERS).records;
  const membersWithUser = members.map(function (m) {
    const u = users.find(function (x) { return String(x.uuid) === String(m.userUuid); });
    return Object.assign({}, m, { user: sanitizeUser(u) });
  });
  const taxConfig = getTaxConfig();
  const breakdown = computeBreakdown(incomes, taxConfig);
  return {
    group: group,
    incomes: incomes,
    expenses: expenses,
    members: membersWithUser,
    breakdown: breakdown,
  };
}

/* ============== Incomes CRUD ============== */

function normalizeIncome(i) {
  return {
    uuid: i.uuid,
    groupUuid: i.groupUuid,
    kind: i.kind || 'OTHER',
    amount: Number(i.amount) || 0,
    currency: i.currency || 'COP',
    frequency: i.frequency || 'MONTHLY',
    isEmployee: String(i.isEmployee).toLowerCase() === 'true' || i.isEmployee === true,
    description: i.description || '',
    date: i.date ? String(i.date) : '',
  };
}

function createIncome(payload, actorUuid) {
  requireRole(actorUuid, payload.groupUuid, 'EDITOR');
  const record = {
    uuid: Utilities.getUuid(),
    groupUuid: payload.groupUuid,
    kind: payload.kind || 'OTHER',
    amount: Number(payload.amount) || 0,
    currency: payload.currency || 'COP',
    frequency: payload.frequency || 'MONTHLY',
    isEmployee: !!payload.isEmployee,
    description: payload.description || '',
    date: payload.date || new Date().toISOString().slice(0, 10),
  };
  appendRecord(DB_CONFIG.sheets.INCOMES, record);
  return record;
}

function updateIncome(uuid, payload, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.INCOMES);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Ingreso no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'EDITOR');
  const patch = {};
  ['kind','amount','currency','frequency','description','date'].forEach(function (k) {
    if (payload[k] !== undefined) patch[k] = payload[k];
  });
  if (payload.isEmployee !== undefined) patch.isEmployee = !!payload.isEmployee;
  if (patch.amount !== undefined) patch.amount = Number(patch.amount) || 0;
  return updateRecord(DB_CONFIG.sheets.INCOMES, 'uuid', uuid, patch);
}

function deleteIncome(uuid, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.INCOMES);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Ingreso no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'EDITOR');
  deleteRecord(DB_CONFIG.sheets.INCOMES, 'uuid', uuid);
  return { success: true, uuid: uuid };
}

/* ============== Expenses CRUD ============== */

function normalizeExpense(e) {
  return {
    uuid: e.uuid,
    groupUuid: e.groupUuid,
    category: e.category || 'OTROS',
    amount: Number(e.amount) || 0,
    currency: e.currency || 'COP',
    date: e.date ? String(e.date) : '',
    description: e.description || '',
    recurring: String(e.recurring).toLowerCase() === 'true' || e.recurring === true,
  };
}

function createExpense(payload, actorUuid) {
  requireRole(actorUuid, payload.groupUuid, 'EDITOR');
  const record = {
    uuid: Utilities.getUuid(),
    groupUuid: payload.groupUuid,
    category: payload.category || 'OTROS',
    amount: Number(payload.amount) || 0,
    currency: payload.currency || 'COP',
    date: payload.date || new Date().toISOString().slice(0, 10),
    description: payload.description || '',
    recurring: !!payload.recurring,
  };
  appendRecord(DB_CONFIG.sheets.EXPENSES, record);
  return record;
}

function updateExpense(uuid, payload, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.EXPENSES);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Gasto no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'EDITOR');
  const patch = {};
  ['category','amount','currency','date','description'].forEach(function (k) {
    if (payload[k] !== undefined) patch[k] = payload[k];
  });
  if (payload.recurring !== undefined) patch.recurring = !!payload.recurring;
  if (patch.amount !== undefined) patch.amount = Number(patch.amount) || 0;
  return updateRecord(DB_CONFIG.sheets.EXPENSES, 'uuid', uuid, patch);
}

function deleteExpense(uuid, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.EXPENSES);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Gasto no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'EDITOR');
  deleteRecord(DB_CONFIG.sheets.EXPENSES, 'uuid', uuid);
  return { success: true, uuid: uuid };
}

/* ============== Members CRUD ============== */

function addMember(payload, actorUuid) {
  requireRole(actorUuid, payload.groupUuid, 'ADMIN');
  let target = null;
  if (payload.email) target = findUserByEmail(payload.email);
  else if (payload.userUuid) target = findUserByUuid(payload.userUuid);
  if (!target) throw new Error('Usuario no encontrado.');
  const existing = getMemberRecord(payload.groupUuid, target.uuid);
  if (existing) throw new Error('El usuario ya es miembro de este grupo.');
  const record = {
    uuid: Utilities.getUuid(),
    groupUuid: payload.groupUuid,
    userUuid: target.uuid,
    role: payload.role || 'PUBLIC',
  };
  appendRecord(DB_CONFIG.sheets.GROUP_MEMBERS, record);
  return Object.assign({}, record, { user: sanitizeUser(target) });
}

function removeMember(uuid, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.GROUP_MEMBERS);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Miembro no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'ADMIN');
  deleteRecord(DB_CONFIG.sheets.GROUP_MEMBERS, 'uuid', uuid);
  return { success: true, uuid: uuid };
}

function updateMemberRole(uuid, role, actorUuid) {
  const data = readAll(DB_CONFIG.sheets.GROUP_MEMBERS);
  const current = data.records.find(function (r) { return String(r.uuid) === String(uuid); });
  if (!current) throw new Error('Miembro no encontrado.');
  requireRole(actorUuid, current.groupUuid, 'ADMIN');
  return updateRecord(DB_CONFIG.sheets.GROUP_MEMBERS, 'uuid', uuid, { role: role });
}

/* ============== Tax Config + Breakdown ============== */

function getTaxConfig() {
  try {
    const data = readAll(DB_CONFIG.sheets.TAX_CONFIG);
    if (!data.records.length) return null;
    const sorted = data.records.slice().sort(function (a, b) { return Number(b.year) - Number(a.year); });
    const row = sorted[0];
    return {
      year: Number(row.year) || new Date().getFullYear(),
      uvtValue: Number(row.uvtValue) || 49799,
      smlmv: Number(row.smlmv) || 1623500,
      fspThresholdSmlmv: Number(row.fspThresholdSmlmv) || 4,
    };
  } catch (e) {
    return null;
  }
}

function computeBreakdown(incomes, taxConfig) {
  const cfg = taxConfig || { uvtValue: 49799, smlmv: 1623500 };
  const RETENCION_RANGES = [
    { from: 0, to: 95, rate: 0, baseUVT: 0 },
    { from: 95, to: 150, rate: 0.19, baseUVT: 0 },
    { from: 150, to: 360, rate: 0.28, baseUVT: 10 },
    { from: 360, to: 640, rate: 0.33, baseUVT: 69 },
    { from: 640, to: 945, rate: 0.35, baseUVT: 162 },
    { from: 945, to: 2300, rate: 0.37, baseUVT: 268 },
    { from: 2300, to: Infinity, rate: 0.39, baseUVT: 770 },
  ];
  const FSP_RANGES = [
    { from: 4, to: 16, rate: 0.01 },
    { from: 16, to: 17, rate: 0.012 },
    { from: 17, to: 18, rate: 0.014 },
    { from: 18, to: 19, rate: 0.016 },
    { from: 19, to: 20, rate: 0.018 },
    { from: 20, to: Infinity, rate: 0.02 },
  ];
  function toMonthly(i) {
    if (i.frequency === 'MONTHLY') return i.amount;
    if (i.frequency === 'BIWEEKLY') return i.amount * 2;
    if (i.frequency === 'ONE_TIME') return i.amount / 12;
    return i.amount;
  }
  const isEmployee = incomes.some(function (i) { return i.kind === 'SALARY' && i.isEmployee; });
  const gross = incomes.reduce(function (s, i) { return s + toMonthly(i); }, 0);
  const salaryGross = incomes.filter(function (i) { return i.kind === 'SALARY'; }).reduce(function (s, i) { return s + toMonthly(i); }, 0);
  let salud = 0, pension = 0;
  if (isEmployee) {
    salud = salaryGross * 0.04;
    pension = salaryGross * 0.04;
  } else {
    const ibc = gross * 0.4;
    salud = ibc * 0.125;
    pension = ibc * 0.16;
  }
  let fsp = 0;
  if (cfg.smlmv > 0) {
    const sInS = salaryGross / cfg.smlmv;
    for (let r of FSP_RANGES) {
      if (sInS >= r.from && sInS < r.to) { fsp = salaryGross * r.rate; break; }
    }
  }
  const rentaExenta = (gross - salud - pension - fsp) * 0.25;
  const baseGravable = Math.max(0, gross - salud - pension - fsp - rentaExenta);
  const baseGravableUVT = baseGravable / cfg.uvtValue;
  let retencion = 0;
  for (let r of RETENCION_RANGES) {
    if (baseGravableUVT > r.from && baseGravableUVT <= r.to) {
      const exceso = baseGravableUVT - r.from;
      retencion = Math.max(0, (exceso * r.rate + r.baseUVT) * cfg.uvtValue);
      break;
    }
  }
  const totalDeducciones = salud + pension + fsp + retencion;
  return {
    gross: gross,
    salud: salud,
    pension: pension,
    fsp: fsp,
    baseGravable: baseGravable,
    baseGravableUVT: baseGravableUVT,
    retencionEnFuente: retencion,
    totalDeducciones: totalDeducciones,
    neto: gross - totalDeducciones,
  };
}

/* ============== Backwards compat ============== */

function getDashboardBootstrap() {
  return { appName: 'CrowWealth' };
}