/** 關西旅費記帳 — paste this file into a standalone Apps Script project. */
const KS_HEADERS = ['ID', '日期', '開支名稱', '付款人', '金額（JPY）', '貨幣', '建立時間'];
const KS_TAB = '開支紀錄';
const KS_MAX_RECORDS = 5000;
const KS_MAX_REQUEST_BYTES = 8192;
const KS_UUID = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
const KS_ERRORS = Object.freeze({
  auth: '連線金鑰無效，請檢查連線設定。',
  setup: '尚未完成試算表設定，請先執行 setup。',
  input: '資料格式不正確，請檢查後重試。',
  busy: '目前有其他操作，請稍後以相同記錄編號重試。',
  schema: '試算表格式已變更，請聯絡擁有人檢查欄位及資料。',
  conflict: '相同記錄編號已有不同內容，請重新新增。',
  full: '記錄已達上限，請聯絡擁有人備份並建立新的帳本。',
  internal: '未能完成操作，請稍後以相同記錄編號重試。'
});

/** Owner-only editor action. Never invoked from the public web app. */
function setup() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) throw new Error(KS_ERRORS.busy);
  try {
    const properties = PropertiesService.getScriptProperties();
    let sheetId = properties.getProperty('SHEET_ID');
    let accessKey = properties.getProperty('ACCESS_KEY');
    if (Boolean(sheetId) !== Boolean(accessKey)) throw new Error(KS_ERRORS.setup);
    let spreadsheet;
    if (sheetId && accessKey) {
      spreadsheet = SpreadsheetApp.openById(sheetId);
      ksReadRecords_(spreadsheet.getSheetByName(KS_TAB));
    } else {
      // Create a NEW independent spreadsheet; never select or overwrite an active one.
      spreadsheet = SpreadsheetApp.create('關西旅費記帳', KS_MAX_RECORDS + 1, KS_HEADERS.length);
      const sheet = spreadsheet.getSheets()[0];
      sheet.setName(KS_TAB);
      const header = sheet.getRange(1, 1, 1, KS_HEADERS.length);
      header.setNumberFormat('@').setRichTextValues([KS_HEADERS.map(ksLiteral_)]);
      header.setFontWeight('bold');
      sheet.setFrozenRows(1);
      sheet.autoResizeColumns(1, KS_HEADERS.length);
      SpreadsheetApp.flush();
      ksReadRecords_(sheet); // Verify initialized schema before persisting the connection.
      sheetId = spreadsheet.getId();
      accessKey = Utilities.getUuid().replace(/-/g, '') + Utilities.getUuid().replace(/-/g, '');
      properties.setProperties({ SHEET_ID: sheetId, ACCESS_KEY: accessKey });
    }
    const output = { spreadsheetUrl: spreadsheet.getUrl(), accessKey: accessKey };
    // Intentionally available ONLY to the owner running setup() in the editor.
    Logger.log(JSON.stringify(output));
    return output;
  } catch (error) {
    // Do not emit raw Google exceptions, IDs or credentials even in editor errors.
    throw new Error(ksPublicError_(error));
  } finally {
    lock.releaseLock();
  }
}

function doPost(event) {
  let lock = null;
  let acquired = false;
  try {
    const contents = event && event.postData && event.postData.contents;
    if (typeof contents !== 'string' || contents.length > KS_MAX_REQUEST_BYTES ||
        Utilities.newBlob(contents).getBytes().length > KS_MAX_REQUEST_BYTES) {
      throw new Error(KS_ERRORS.input);
    }
    const body = JSON.parse(contents);
    if (!ksObject_(body)) throw new Error(KS_ERRORS.input);
    const properties = PropertiesService.getScriptProperties();
    const expectedKey = properties.getProperty('ACCESS_KEY');
    // Key verification MUST precede opening a spreadsheet or reading any sheet cell.
    if (!expectedKey || !ksKeyMatches_(body.token, expectedKey)) throw new Error(KS_ERRORS.auth);
    const sheetId = properties.getProperty('SHEET_ID');
    if (!sheetId) throw new Error(KS_ERRORS.setup);
    if (body.action !== 'list' && body.action !== 'add') throw new Error(KS_ERRORS.input);
    const record = body.action === 'add' ? ksValidateRecord_(body.record) : null;
    lock = LockService.getScriptLock();
    acquired = lock.tryLock(5000);
    if (!acquired) throw new Error(KS_ERRORS.busy);
    // Recheck under the lock in case owner rotated the key while this request waited.
    if (!ksKeyMatches_(body.token, properties.getProperty('ACCESS_KEY')) ||
        sheetId !== properties.getProperty('SHEET_ID')) throw new Error(KS_ERRORS.auth);
    const spreadsheet = SpreadsheetApp.openById(sheetId);
    const sheet = spreadsheet.getSheetByName(KS_TAB);
    let records = ksReadRecords_(sheet);
    if (record) {
      const existing = records.find(function (r) { return r.id.toLowerCase() === record.id.toLowerCase(); });
      if (existing) {
        if (!ksSamePayload_(existing, record)) throw new Error(KS_ERRORS.conflict);
      } else {
        if (records.length >= KS_MAX_RECORDS) throw new Error(KS_ERRORS.full);
        const createdAt = new Date().toISOString();
        const row = [record.id, record.date, record.name, record.person, String(record.amount), 'JPY', createdAt];
        const rowNumber = records.length + 2;
        // All text is literal RichText. NEVER hand user strings to setValue(s):
        // those APIs interpret leading '=' as formulas, even with display formatting.
        // First write a complete valid row in one rich-text operation. A numeric
        // conversion failure still leaves a valid row that an identical UUID retry finds.
        sheet.getRange(rowNumber, 1, 1, KS_HEADERS.length)
          .setNumberFormat('@').setRichTextValues([row.map(ksLiteral_)]);
        sheet.getRange(rowNumber, 5).setNumberFormat('0').setValue(record.amount);
        SpreadsheetApp.flush(); // Commit while holding the script lock.
        records = ksReadRecords_(sheet); // Read back the exact target before confirming success.
        const persisted = records.find(function (r) { return r.id.toLowerCase() === record.id.toLowerCase(); });
        if (!persisted || !ksSamePayload_(persisted, record) || persisted.createdAt !== createdAt) {
          throw new Error(KS_ERRORS.internal);
        }
      }
    }
    return ksJson_({ ok: true, records: records, spreadsheetUrl: spreadsheet.getUrl() });
  } catch (error) {
    // Never log request bodies, keys, deployment IDs or raw exception messages.
    return ksJson_({ ok: false, error: ksPublicError_(error) });
  } finally {
    if (acquired) {
      // A write can succeed before a later formatting/number conversion throws.
      // Flush even that error path while still locked; never expose cleanup errors.
      try { SpreadsheetApp.flush(); } catch (error) { /* safe response already prepared */ }
      try { lock.releaseLock(); } catch (error) { /* released automatically on completion */ }
    }
  }
}

function doGet() {
  return ksJson_({ ok: false, error: '請使用記帳網站連線，不支援此操作。' });
}

function ksObject_(value) {
  return value !== null && typeof value === 'object' && !Array.isArray(value);
}

function ksKeyMatches_(candidate, expected) {
  if (typeof candidate !== 'string' || typeof expected !== 'string' || candidate.length > 256 ||
      candidate.length < 32 || candidate.length !== expected.length) return false;
  let difference = 0;
  for (let i = 0; i < expected.length; i++) difference |= candidate.charCodeAt(i) ^ expected.charCodeAt(i);
  return difference === 0;
}

function ksDateValid_(value) {
  if (typeof value !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(value)) return false;
  const year = Number(value.slice(0, 4));
  const month = Number(value.slice(5, 7));
  const day = Number(value.slice(8, 10));
  if (year < 1 || month < 1 || month > 12 || day < 1) return false;
  const leap = year % 4 === 0 && (year % 100 !== 0 || year % 400 === 0);
  const days = [31, leap ? 29 : 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
  return day <= days[month - 1];
}

function ksValidateRecord_(value) {
  if (!ksObject_(value) || typeof value.id !== 'string' || !KS_UUID.test(value.id) ||
      !ksDateValid_(value.date) || typeof value.name !== 'string' ||
      /[\u0000-\u001f\u007f-\u009f]/.test(value.name) ||
      (value.person !== 'Connie' && value.person !== 'Hei') ||
      !Number.isSafeInteger(value.amount) || value.amount < 1 || value.amount > 999999999) {
    throw new Error(KS_ERRORS.input);
  }
  const name = value.name.trim();
  if (!name || Array.from(name).length > 80) throw new Error(KS_ERRORS.input);
  return { id: value.id, date: value.date, name: name, person: value.person, amount: value.amount };
}

function ksReadRecords_(sheet) {
  if (!sheet || sheet.getLastColumn() !== KS_HEADERS.length) throw new Error(KS_ERRORS.schema);
  const lastRow = sheet.getLastRow();
  if (lastRow < 1) throw new Error(KS_ERRORS.schema);
  if (lastRow > KS_MAX_RECORDS + 1) throw new Error(KS_ERRORS.full);
  const range = sheet.getRange(1, 1, lastRow, KS_HEADERS.length);
  const values = range.getValues();
  // Prevent returning evaluated formulas introduced via manual edits.
  const formulas = range.getFormulas();
  if (formulas.some(function (row) { return row.some(function (formula) { return formula !== ''; }); })) {
    throw new Error(KS_ERRORS.schema);
  }
  if (!KS_HEADERS.every(function (name, index) { return values[0][index] === name; })) throw new Error(KS_ERRORS.schema);
  const records = [];
  const ids = new Set();
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    // RichText is written atomically for the entire row before amount becomes numeric.
    const amount = typeof row[4] === 'number' ? row[4] :
      (typeof row[4] === 'string' && /^[1-9]\d{0,8}$/.test(row[4]) ? Number(row[4]) : NaN);
    let record;
    try {
      record = ksValidateRecord_({ id: row[0], date: row[1], name: row[2], person: row[3], amount: amount });
    } catch (error) { throw new Error(KS_ERRORS.schema); }
    const createdAt = row[6];
    if (record.name !== row[2] || row[5] !== 'JPY' || typeof createdAt !== 'string' ||
        !/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}Z$/.test(createdAt) ||
        !Number.isFinite(Date.parse(createdAt)) || new Date(createdAt).toISOString() !== createdAt ||
        ids.has(record.id.toLowerCase())) throw new Error(KS_ERRORS.schema);
    ids.add(record.id.toLowerCase());
    records.push({ id: record.id, date: record.date, name: record.name, person: record.person,
      amount: record.amount, currency: 'JPY', createdAt: createdAt });
  }
  return records;
}

function ksSamePayload_(a, b) {
  return a.id.toLowerCase() === b.id.toLowerCase() && a.date === b.date && a.name === b.name &&
    a.person === b.person && a.amount === b.amount;
}

function ksLiteral_(text) {
  return SpreadsheetApp.newRichTextValue().setText(text).build();
}

function ksPublicError_(error) {
  const message = error && error.message;
  return Object.keys(KS_ERRORS).some(function (key) { return KS_ERRORS[key] === message; }) ? message : KS_ERRORS.internal;
}

function ksJson_(value) {
  return ContentService.createTextOutput(JSON.stringify(value)).setMimeType(ContentService.MimeType.JSON);
}