AppsScript.gs

/**
 * ============================================================
 *  TRIP SPLIT — Google Apps Script backend
 * ============================================================
 *
 *  This file is also embedded in index.html (between the gs:embed markers) so
 *  the web app's setup screen can hand it to someone with no repo access.
 *  CHANGE ONE, RE-EMBED THE OTHER.
 *
 *  FIRST-TIME SETUP
 *  1. Paste this whole file into Extensions > Apps Script
 *  2. In the editor, select the function `setupSheets` and click Run.
 *     (Grant permissions when asked. This creates/repairs all 4 tabs.)
 *  3. Deploy > New deployment > Web app
 *        Execute as:      Me
 *        Who has access:  Anyone
 *  4. Copy the /exec URL into index.html (SCRIPT_URL)
 *
 *  Data model
 *  ----------
 *  Trip         : field | value   (name, description, startDate, endDate)
 *  People       : name | active
 *  Tags         : tag  | emoji
 *  Expenses     : id | paidBy | description | amount | tag | splitBetween | splitMode | timestamp | date
 *  Settlements  : id | fromPerson | toPerson | amount | note | timestamp | date
 *
 *  date      = YYYY-MM-DD, the day the money moved. This is what the ledger and
 *              every chart group by. Set by whoever records the entry.
 *  timestamp = when the row was written. Never edited, used only to order two
 *              entries inside the same day.
 *
 *  splitMode = 'equal'  -> splitBetween is "ALL" or "Asha,Dhruv,Sakshi"
 *  splitMode = 'custom' -> splitBetween is "Asha:400,Dhruv:1100"
 *
 *  People and Tags are optional seed lists only. The lists the web app shows
 *  are derived from the sheet data itself: anyone who appears as a payer, in a
 *  split, or in a settlement is a person, and any tag used on an expense is a
 *  category. A missing or differently-labelled tab is not an error.
 *
 *  All balance math lives in the client. This script is a data layer only.
 * ============================================================
 */

const SHEETS = {
  trip: {
    name: 'Trip',
    headers: ['field', 'value']
  },
  people: {
    name: 'People',
    headers: ['name', 'active']
  },
  tags: {
    name: 'Tags',
    headers: ['tag', 'emoji']
  },
  expenses: {
    name: 'Expenses',
    headers: ['id', 'paidBy', 'description', 'amount', 'tag', 'splitBetween', 'splitMode', 'timestamp', 'date']
  },
  settlements: {
    name: 'Settlements',
    headers: ['id', 'fromPerson', 'toPerson', 'amount', 'note', 'timestamp', 'date']
  }
};

// Key/value rather than columns, so a new detail later costs one row.
const TRIP_FIELDS = ['name', 'description', 'startDate', 'endDate'];

const DEFAULT_TAGS = [
  ['stay', '🏨'],
  ['food', '🍽️'],
  ['travel', '🚕'],
  ['activities', '🎟️'],
  ['gifts', '🎁'],
  ['misc', '📦']
];


/* ============================================================
   SETUP — run this once, manually, from the editor
   ============================================================ */

function setupSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const report = [];

  Object.keys(SHEETS).forEach(function (key) {
    const cfg = SHEETS[key];
    let sheet = ss.getSheetByName(cfg.name);

    // ponytail: never rename or replace a tab that has data in it. Reads match
    // columns by the tab's own header row, so an odd layout is fine as-is.
    if (sheet) {
      if (sheet.getLastRow() === 0) {
        sheet.getRange(1, 1, 1, cfg.headers.length).setValues([cfg.headers]);
        report.push(cfg.name + ': headers written into empty tab');
        return;
      }
      const added = addMissingHeaders(sheet, cfg);
      report.push(cfg.name + ': kept ' + (sheet.getLastRow() - 1) + ' data rows' +
        (added.length ? ', added column' + (added.length > 1 ? 's ' : ' ') + added.join(', ') : ''));
      return;
    }

    sheet = ss.insertSheet(cfg.name);
    sheet.getRange(1, 1, 1, cfg.headers.length).setValues([cfg.headers]);
    sheet.getRange(1, 1, 1, cfg.headers.length)
      .setFontWeight('bold')
      .setBackground('#e8eef0');
    sheet.setFrozenRows(1);
    sheet.autoResizeColumns(1, cfg.headers.length);

    report.push(cfg.name + ': created');
  });

  /** Appends any header this script expects but the tab does not have yet. */
function addMissingHeaders(sheet, cfg) {
  const lastCol = Math.max(sheet.getLastColumn(), 1);
  const existing = sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(normHeader);
  const missing = cfg.headers.filter(function (h) { return existing.indexOf(normHeader(h)) === -1; });

  missing.forEach(function (h, i) {
    const col = lastCol + 1 + i;
    sheet.getRange(1, col).setValue(h)
      .setFontWeight('bold')
      .setBackground('#e8eef0');
  });

  return missing;
}

/** Fills a blank date cell from the row's own timestamp. Only touches blanks. */
function backfillDates(cfg) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cfg.name);
  if (!sheet || sheet.getLastRow() < 2) return 0;

  const map = headerMap(sheet, cfg).map;
  const dateCol = map.date, tsCol = map.timestamp;
  const rows = sheet.getLastRow() - 1;

  const dates = sheet.getRange(2, dateCol, rows, 1).getValues();
  const stamps = sheet.getRange(2, tsCol, rows, 1).getValues();

  let filled = 0;
  for (let i = 0; i < rows; i++) {
    if (toDateStr(dates[i][0])) continue;
    const from = toDateStr(stamps[i][0]);
    if (!from) continue;
    dates[i][0] = from;
    filled++;
  }

  if (filled) sheet.getRange(2, dateCol, rows, 1).setValues(dates);
  return filled;
}

// Seed default tags if the Tags tab is empty
  const tagSheet = ss.getSheetByName(SHEETS.tags.name);
  if (tagSheet.getLastRow() < 2) {
    tagSheet.getRange(2, 1, DEFAULT_TAGS.length, 2).setValues(DEFAULT_TAGS);
    report.push('Tags: seeded ' + DEFAULT_TAGS.length + ' default tags');
  }

  // Lay out the trip detail rows so the tab explains itself in the sheet
  const tripSheet = ss.getSheetByName(SHEETS.trip.name);
  if (tripSheet && tripSheet.getLastRow() < 2) {
    tripSheet.getRange(2, 1, TRIP_FIELDS.length, 2).setValues(
      TRIP_FIELDS.map(function (f) { return [f, '']; })
    );
    report.push('Trip: added the detail rows — name it from the web app');
  }

  // Give older rows a date so the sheet sorts and filters like the app reads it
  [SHEETS.expenses, SHEETS.settlements].forEach(function (cfg) {
    const filled = backfillDates(cfg);
    if (filled) report.push(cfg.name + ': filled in ' + filled + ' blank date' + (filled > 1 ? 's' : '') + ' from the timestamps');
  });

  // Nudge if there are no people yet
  const peopleSheet = ss.getSheetByName(SHEETS.people.name);
  if (peopleSheet.getLastRow() < 2) {
    report.push('People: empty — names are also picked up from the expense rows themselves');
    const backups = ss.getSheets()
      .map(function (sh) { return sh.getName(); })
      .filter(function (n) { return n.indexOf(SHEETS.people.name + '_backup_') === 0; });
    if (backups.length) {
      report.push('People: an earlier setup run moved your names to ' + backups.join(', ') +
                  ' — copy them back into the People tab');
    }
  }

  Logger.log(report.join('\n'));
  try {
    SpreadsheetApp.getUi().alert('Trip Split setup\n\n' + report.join('\n'));
  } catch (e) {
    // No UI context — the log is enough.
  }
}


/* ============================================================
   READ
   ============================================================ */

function doGet() {
  try {
    const expenses = readExpenses();
    const settlements = readSettlements();

    return jsonOutput({
      ok: true,
      trip: readTrip(),
      people: derivePeople(expenses, settlements, readPeople()),
      tags: deriveTags(expenses, readTags()),
      expenses: expenses,
      settlements: settlements,
      serverTime: new Date().toISOString()
    });
  } catch (err) {
    return jsonOutput({ ok: false, error: err.message });
  }
}

/**
 * The People tab is a seed, not the source of truth: everyone who shows up as
 * a payer, inside a split, or in a settlement is part of the trip. First
 * spelling of a name wins; comparison is case-insensitive.
 */
function derivePeople(expenses, settlements, seedNames) {
  const seen = {};
  const order = [];

  const add = function (raw) {
    const name = String(raw == null ? '' : raw).trim();
    if (!name || name.toUpperCase() === 'ALL') return;
    const key = name.toLowerCase();
    if (seen[key]) return;
    seen[key] = true;
    order.push(name);
  };

  (seedNames || []).forEach(add);
  (expenses || []).forEach(function (e) {
    add(e.paidBy);
    splitNames(e).forEach(add);
  });
  (settlements || []).forEach(function (s) {
    add(s.fromPerson);
    add(s.toPerson);
  });

  return order;
}

/** Names in splitBetween, for both "A,B" and "A:400,B:600" shapes. */
function splitNames(e) {
  return String(e && e.splitBetween || '')
    .split(',')
    .map(function (part) { return String(part).split(':')[0].trim(); })
    .filter(Boolean);
}

/** Tags tab plus any tag actually used on an expense. */
function deriveTags(expenses, seedTags) {
  const seen = {};
  const out = [];

  (seedTags || []).forEach(function (t) {
    const key = String(t.tag || '').trim().toLowerCase();
    if (!key || seen[key]) return;
    seen[key] = true;
    out.push({ tag: String(t.tag).trim(), emoji: String(t.emoji || '').trim() });
  });

  (expenses || []).forEach(function (e) {
    const tag = String(e.tag || '').trim();
    const key = tag.toLowerCase();
    if (!tag || seen[key]) return;
    seen[key] = true;
    out.push({ tag: tag, emoji: '' });
  });

  return out;
}

const INACTIVE = ['false', 'no', 'n', '0', 'inactive', 'left'];
const HEADERISH = ['name', 'names', 'person', 'people', 'member'];

function readTrip() {
  const out = { name: '', description: '', startDate: '', endDate: '' };

  readRows(SHEETS.trip).forEach(function (r) {
    const key = String(r.field == null ? '' : r.field).trim().toLowerCase();
    const field = TRIP_FIELDS.filter(function (f) { return f.toLowerCase() === key; })[0];
    if (!field) return;
    out[field] = field.indexOf('Date') !== -1
      ? toDateStr(r.value)
      : String(r.value == null ? '' : r.value).trim();
  });

  return out;
}

function readPeople() {
  return readRows(SHEETS.people)
    .filter(function (r) {
      const name = String(r.name == null ? '' : r.name).trim().toLowerCase();
      return name !== '' && HEADERISH.indexOf(name) === -1;
    })
    // Blank, TRUE, "yes", a checkbox, a stray note — all mean active. Only an
    // explicit off-value drops someone from the pickers.
    .filter(function (r) {
      return INACTIVE.indexOf(String(r.active == null ? '' : r.active).trim().toLowerCase()) === -1;
    })
    .map(function (r) { return String(r.name).trim(); });
}

function readTags() {
  return readRows(SHEETS.tags)
    .filter(function (r) { return String(r.tag).trim() !== ''; })
    .map(function (r) {
      return { tag: String(r.tag).trim(), emoji: String(r.emoji || '').trim() };
    });
}

function readExpenses() {
  return readRows(SHEETS.expenses)
    .filter(function (r) { return String(r.id).trim() !== ''; })
    .map(function (r) {
      return {
        id: String(r.id),
        paidBy: String(r.paidBy || '').trim(),
        description: String(r.description || ''),
        amount: Number(r.amount) || 0,
        tag: String(r.tag || '').trim(),
        splitBetween: String(r.splitBetween || 'ALL').trim(),
        splitMode: String(r.splitMode || 'equal').trim().toLowerCase(),
        timestamp: toIso(r.timestamp),
        date: toDateStr(r.date) || toDateStr(r.timestamp)
      };
    });
}

function readSettlements() {
  return readRows(SHEETS.settlements)
    .filter(function (r) { return String(r.id).trim() !== ''; })
    .map(function (r) {
      return {
        id: String(r.id),
        fromPerson: String(r.fromPerson || '').trim(),
        toPerson: String(r.toPerson || '').trim(),
        amount: Number(r.amount) || 0,
        note: String(r.note || ''),
        timestamp: toIso(r.timestamp),
        date: toDateStr(r.date) || toDateStr(r.timestamp)
      };
    });
}


/* ============================================================
   WRITE
   ============================================================ */

function doPost(e) {
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(20000);
  } catch (err) {
    return jsonOutput({ ok: false, error: 'Server busy, please retry' });
  }

  try {
    const body = JSON.parse(e.postData.contents);

    switch (body.action) {
      case 'addExpense':       return jsonOutput(addExpense(body));
      case 'updateExpense':    return jsonOutput(updateExpense(body));
      case 'deleteExpense':    return jsonOutput(deleteById(SHEETS.expenses, body.id));
      case 'addSettlement':    return jsonOutput(addSettlement(body));
      case 'deleteSettlement': return jsonOutput(deleteById(SHEETS.settlements, body.id));
      case 'saveTrip':         return jsonOutput(saveTrip(body));
      case 'addPerson':        return jsonOutput(addPerson(body));
      case 'deletePerson': {
        const res = deleteByFirstCol(SHEETS.people, body.name);
        if (!res.ok) {
          res.error = body.name + ' is not a row in the People tab — that name comes from ' +
                      'the recorded expenses, so it stays until those rows change.';
        }
        return jsonOutput(res);
      }
      case 'addTag':           return jsonOutput(addTag(body));
      case 'deleteTag':        return jsonOutput(deleteByFirstCol(SHEETS.tags, body.tag));
      default:
        return jsonOutput({ ok: false, error: 'Unknown action: ' + body.action });
    }
  } catch (err) {
    return jsonOutput({ ok: false, error: err.message });
  } finally {
    lock.releaseLock();
  }
}

function addExpense(body) {
  const clean = validateExpense(body);
  if (clean.error) return { ok: false, error: clean.error };

  const id = Utilities.getUuid();
  clean.id = id;
  clean.timestamp = new Date().toISOString();
  clean.date = clean.date || today();

  appendObj(getSheet(SHEETS.expenses), SHEETS.expenses, clean);
  return { ok: true, id: id };
}

function updateExpense(body) {
  if (!body.id) return { ok: false, error: 'Missing id' };

  const sheet = getSheet(SHEETS.expenses);
  const rowIndex = findRowIndex(sheet, 1, body.id);
  if (rowIndex === -1) return { ok: false, error: 'Expense not found' };

  const cur = readRowObj(sheet, SHEETS.expenses, rowIndex);

  // Merge incoming fields over what's already stored, then validate the result.
  const pick = function (key) { return body[key] !== undefined ? body[key] : cur[key]; };

  const clean = validateExpense({
    paidBy:       pick('paidBy'),
    description:  pick('description'),
    amount:       pick('amount'),
    tag:          pick('tag'),
    splitBetween: pick('splitBetween'),
    splitMode:    pick('splitMode'),
    date:         pick('date')
  });
  if (clean.error) return { ok: false, error: clean.error };

  // A blank date on an older row heals from its timestamp — never from "today",
  // which would silently redate the entry on an unrelated edit.
  clean.date = clean.date || toDateStr(cur.timestamp);

  updateRow(sheet, SHEETS.expenses, rowIndex, clean);
  return { ok: true, id: body.id };
}

function validateExpense(body) {
  const paidBy = String(body.paidBy || '').trim();
  if (!paidBy) return { error: 'Payer is required' };

  const amount = Number(body.amount);
  if (!isFinite(amount) || amount <= 0) return { error: 'Amount must be a positive number' };

  const date = normDate(body.date);
  if (date.error) return { error: date.error };

  const splitMode = String(body.splitMode || 'equal').trim().toLowerCase() === 'custom' ? 'custom' : 'equal';
  let splitBetween = String(body.splitBetween || 'ALL').trim();
  if (!splitBetween) splitBetween = 'ALL';

  if (splitMode === 'custom') {
    // Expect "Name:amount,Name:amount" and require the parts to add up.
    const parts = splitBetween.split(',').map(function (s) { return s.trim(); }).filter(Boolean);
    if (!parts.length) return { error: 'Custom split has no entries' };

    let sum = 0;
    for (let i = 0; i < parts.length; i++) {
      const bits = parts[i].split(':');
      if (bits.length !== 2 || !bits[0].trim()) {
        return { error: 'Bad custom split entry: ' + parts[i] };
      }
      const v = Number(bits[1]);
      if (!isFinite(v) || v < 0) return { error: 'Bad custom split amount for ' + bits[0] };
      sum += v;
    }
    if (Math.abs(sum - amount) > 0.5) {
      return { error: 'Custom split total (' + sum.toFixed(2) + ') does not match amount (' + amount.toFixed(2) + ')' };
    }
  }

  return {
    paidBy: paidBy,
    description: String(body.description || '').trim(),
    amount: Math.round(amount * 100) / 100,
    tag: String(body.tag || '').trim(),
    splitBetween: splitBetween,
    splitMode: splitMode,
    date: date.value
  };
}

function addSettlement(body) {
  const from = String(body.fromPerson || '').trim();
  const to = String(body.toPerson || '').trim();
  const amount = Number(body.amount);

  if (!from || !to) return { ok: false, error: 'Both people are required' };
  if (from === to) return { ok: false, error: 'From and to cannot be the same person' };
  if (!isFinite(amount) || amount <= 0) return { ok: false, error: 'Amount must be a positive number' };

  const date = normDate(body.date);
  if (date.error) return { ok: false, error: date.error };

  const id = Utilities.getUuid();

  appendObj(getSheet(SHEETS.settlements), SHEETS.settlements, {
    id: id,
    fromPerson: from,
    toPerson: to,
    amount: Math.round(amount * 100) / 100,
    note: String(body.note || '').trim(),
    timestamp: new Date().toISOString(),
    date: date.value || today()
  });

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

function saveTrip(body) {
  const clean = {};

  if (body.name !== undefined) clean.name = String(body.name).trim();
  if (body.description !== undefined) clean.description = String(body.description).trim();

  const dateFields = ['startDate', 'endDate'];
  for (let i = 0; i < dateFields.length; i++) {
    const key = dateFields[i];
    if (body[key] === undefined) continue;
    const d = normDate(body[key]);
    if (d.error) return { ok: false, error: d.error };
    clean[key] = d.value;
  }

  // Validate the range the row will actually hold, not just the half being sent.
  const merged = Object.assign({}, readTrip(), clean);
  const rangeError = tripRangeError(merged.startDate, merged.endDate);
  if (rangeError) return { ok: false, error: rangeError };

  const sheet = getSheet(SHEETS.trip);
  Object.keys(clean).forEach(function (key) { upsertTripField(sheet, key, clean[key]); });

  return { ok: true, trip: merged };
}

function tripRangeError(startDate, endDate) {
  if (startDate && endDate && endDate < startDate) return 'The trip cannot end before it starts';
  return '';
}

/** Writes a detail row in place if the field is already listed, appends it if not. */
function upsertTripField(sheet, key, value) {
  const h = headerMap(sheet, SHEETS.trip);
  const lastRow = sheet.getLastRow();

  if (lastRow >= 2) {
    const keys = sheet.getRange(2, h.map.field, lastRow - 1, 1).getValues();
    for (let i = 0; i < keys.length; i++) {
      if (String(keys[i][0]).trim().toLowerCase() === key.toLowerCase()) {
        sheet.getRange(i + 2, h.map.value).setValue(value);
        return;
      }
    }
  }

  appendObj(sheet, SHEETS.trip, { field: key, value: value });
}

function addPerson(body) {
  const name = String(body.name || '').trim();
  if (!name) return { ok: false, error: 'Name is required' };

  const existing = readRows(SHEETS.people).map(function (r) {
    return String(r.name).trim().toLowerCase();
  });
  if (existing.indexOf(name.toLowerCase()) !== -1) {
    return { ok: false, error: name + ' already exists' };
  }

  appendObj(getSheet(SHEETS.people), SHEETS.people, { name: name, active: true });
  return { ok: true, name: name };
}

function addTag(body) {
  const tag = String(body.tag || '').trim();
  if (!tag) return { ok: false, error: 'Tag is required' };

  const existing = readRows(SHEETS.tags).map(function (r) {
    return String(r.tag).trim().toLowerCase();
  });
  if (existing.indexOf(tag.toLowerCase()) !== -1) {
    return { ok: false, error: tag + ' already exists' };
  }

  appendObj(getSheet(SHEETS.tags), SHEETS.tags, { tag: tag, emoji: String(body.emoji || '').trim() });
  return { ok: true, tag: tag };
}


/* ============================================================
   HELPERS
   ============================================================ */

function getSheet(cfg) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cfg.name);
  if (!sheet) {
    throw new Error('Sheet tab "' + cfg.name + '" is missing. Run setupSheets() once from the Apps Script editor.');
  }
  return sheet;
}

/**
 * Resolves every field this script knows about to a 1-indexed column, by
 * matching the tab's OWN header row. Renamed-case, reordered, or extra columns
 * still land in the right field. Every read and every write goes through this,
 * so the two can never disagree about where a column is.
 *   matched = the first row really is a header row
 *   width   = how many columns a whole-row read/write needs to cover
 */
function headerMap(sheet, cfg) {
  const lastCol = Math.max(sheet.getLastColumn(), 1);
  const header = sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(normHeader);

  const map = {};
  let matched = false;
  let widest = lastCol;

  cfg.headers.forEach(function (h, i) {
    const found = header.indexOf(normHeader(h));
    if (found !== -1) matched = true;
    map[h] = (found === -1 ? i : found) + 1;
    if (map[h] > widest) widest = map[h];
  });

  return { map: map, matched: matched, width: Math.min(widest, sheet.getMaxColumns()) };
}

function readRows(cfg) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cfg.name);
  if (!sheet) return [];                       // tab not there yet — reads as empty

  const lastRow = sheet.getLastRow();
  if (lastRow < 1) return [];

  const h = headerMap(sheet, cfg);
  const grid = sheet.getRange(1, 1, lastRow, h.width).getValues();
  const body = h.matched ? grid.slice(1) : grid;   // no header row at all — row 1 is data

  return body.map(function (row) {
    const obj = {};
    cfg.headers.forEach(function (k) {
      const c = h.map[k] - 1;
      obj[k] = c < row.length ? row[c] : '';
    });
    return obj;
  });
}

/** Reads one row as an object keyed by field name. */
function readRowObj(sheet, cfg, rowIndex) {
  const h = headerMap(sheet, cfg);
  const row = sheet.getRange(rowIndex, 1, 1, h.width).getValues()[0];
  const obj = {};
  cfg.headers.forEach(function (k) { obj[k] = row[h.map[k] - 1]; });
  return obj;
}

/** Appends an object, each field going to whichever column its header sits in. */
function appendObj(sheet, cfg, obj) {
  const h = headerMap(sheet, cfg);
  const row = [];
  for (let i = 0; i < h.width; i++) row.push('');
  cfg.headers.forEach(function (k) {
    if (obj[k] !== undefined) row[h.map[k] - 1] = obj[k];
  });
  sheet.appendRow(row);
}

/** Patches only the supplied fields of an existing row, leaving the rest alone. */
function updateRow(sheet, cfg, rowIndex, obj) {
  const h = headerMap(sheet, cfg);
  const range = sheet.getRange(rowIndex, 1, 1, h.width);
  const row = range.getValues()[0];
  cfg.headers.forEach(function (k) {
    if (obj[k] !== undefined) row[h.map[k] - 1] = obj[k];
  });
  range.setValues([row]);
}

function normHeader(s) {
  return String(s == null ? '' : s).trim().toLowerCase().replace(/[^a-z0-9]/g, '');
}

function findRowIndex(sheet, col, value) {
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return -1;

  const values = sheet.getRange(2, col, lastRow - 1, 1).getValues();
  const target = String(value).trim();

  for (let i = 0; i < values.length; i++) {
    if (String(values[i][0]).trim() === target) return i + 2; // 1-indexed + header row
  }
  return -1;
}

function deleteById(cfg, id) {
  if (!id) return { ok: false, error: 'Missing id' };
  const sheet = getSheet(cfg);
  const rowIndex = findRowIndex(sheet, 1, id);
  if (rowIndex === -1) return { ok: false, error: 'Row not found' };
  sheet.deleteRow(rowIndex);
  return { ok: true };
}

function deleteByFirstCol(cfg, value) {
  if (!value) return { ok: false, error: 'Missing value' };
  const sheet = getSheet(cfg);
  const rowIndex = findRowIndex(sheet, 1, value);
  if (rowIndex === -1) return { ok: false, error: 'Not found' };
  sheet.deleteRow(rowIndex);
  return { ok: true };
}

function toIso(v) {
  if (v instanceof Date) return v.toISOString();
  return String(v || '');
}

function today() {
  return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

/** Anything date-ish -> 'YYYY-MM-DD', or '' if it is not a date at all. */
function toDateStr(v) {
  if (v instanceof Date) return Utilities.formatDate(v, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  const m = String(v == null ? '' : v).trim().match(/^\d{4}-\d{2}-\d{2}/);
  return m ? m[0] : '';
}

/**
 * Validates a supplied date. Nothing supplied -> { value: '' } and the caller
 * decides the default; junk or an impossible day -> { error }. Never guesses.
 */
function normDate(v) {
  if (String(v == null ? '' : v).trim() === '') return { value: '' };

  const s = toDateStr(v);
  if (!s) return { error: 'Date must look like YYYY-MM-DD' };

  const p = s.split('-').map(Number);
  const d = new Date(p[0], p[1] - 1, p[2]);
  if (d.getFullYear() !== p[0] || d.getMonth() !== p[1] - 1 || d.getDate() !== p[2]) {
    return { error: 'There is no such date as ' + s };
  }
  return { value: s };
}

function jsonOutput(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}


/* ============================================================
   SELF CHECK — select runSelfTest in the editor and hit Run.
   Touches no sheet; fails loudly if the derive logic breaks.
   ============================================================ */

function runSelfTest() {
  const expenses = [
    { paidBy: 'Ravi',  splitBetween: 'ALL',                 splitMode: 'equal',  tag: 'food' },
    { paidBy: 'Asha',  splitBetween: 'Asha:400,Dhruv:600',  splitMode: 'custom', tag: ''     },
    { paidBy: 'Meera', splitBetween: 'Meera, Ravi',          splitMode: 'equal',  tag: 'stay' }
  ];
  const settlements = [{ fromPerson: 'Dhruv', toPerson: 'ravi', amount: 100 }];

  assertEq(derivePeople(expenses, settlements, ['Asha']).join(','),
    'Asha,Ravi,Dhruv,Meera',
    'people derived from rows: seed first, ALL skipped, case-folded dupes dropped');

  assertEq(derivePeople([], [], []).length, 0, 'nothing in, nothing out');

  assertEq(deriveTags(expenses, [{ tag: 'food', emoji: '🍽️' }])
    .map(function (t) { return t.tag + '|' + t.emoji; }).join(','),
    'food|🍽️,stay|',
    'tags: seed emoji kept, unseeded tag picked up from expenses');

  // --- dates ---
  assertEq(normDate('2026-08-14').value, '2026-08-14', 'a good date passes through');
  assertEq(normDate('').value, '', 'nothing supplied stays blank for the caller to default');
  assertEq(normDate(new Date(2026, 7, 14)).value, '2026-08-14', 'a real Date from the sheet is accepted');
  assertEq(!!normDate('2026-02-30').error, true, '30 February is rejected, not shifted to March');
  assertEq(!!normDate('14/08/2026').error, true, 'ambiguous day-first text is rejected');
  assertEq(toDateStr('2026-08-14T10:00:00.000Z'), '2026-08-14', 'a timestamp yields its day');
  assertEq(toDateStr('rubbish'), '', 'non-dates yield blank');

  assertEq(validateExpense({ paidBy: 'A', amount: 10, date: '2026-08-14' }).date, '2026-08-14',
    'validateExpense keeps the supplied date');
  assertEq(!!validateExpense({ paidBy: 'A', amount: 10, date: 'nope' }).error, true,
    'validateExpense refuses a junk date');

  // --- trip details ---
  assertEq(tripRangeError('2026-08-14', '2026-08-19'), '', 'a forward range is fine');
  assertEq(tripRangeError('2026-08-19', '2026-08-14') !== '', true, 'ending before starting is refused');
  assertEq(tripRangeError('', '2026-08-19'), '', 'half a range is not yet an error');
  assertEq(!!saveTripWouldReject('nonsense'), true, 'a junk trip date is refused');

  Logger.log('self test OK');
}

/** Pure slice of saveTrip's validation, so the self check needs no sheet. */
function saveTripWouldReject(dateValue) {
  return normDate(dateValue).error || '';
}

function assertEq(actual, expected, label) {
  if (actual !== expected) {
    throw new Error('FAIL — ' + label + '\n  got:  ' + actual + '\n  want: ' + expected);
  }
}