google_apps_script.gs

const SHEET_NAME = 'records';
const SETTINGS_SHEET_NAME = 'rate';
const SPREADSHEET_ID = '1XfWFOiuikbyLldmR7Fyz4cJiBTWHtlRFh2hpLt_mHdo';
const SHARED_SECRET = 'CHANGE_ME_TO_A_LONG_RANDOM_TEXT';
const HEADERS = [
  'record_id',
  'saved_at',
  'client_1',
  'client_2',
  'client_3',
  'client_label',
  'loan_amount',
  'property_value',
  'input_json',
];
const SETTINGS_HEADERS = [
  'key',
  'updated_at',
  'settings_json',
];

function doPost(e) {
  try {
    const body = JSON.parse((e && e.postData && e.postData.contents) || '{}');
    if (body.token !== SHARED_SECRET) {
      return jsonResponse({ ok: false, error: 'Invalid token.' });
    }

    const action = String(body.action || '');
    const payload = body.payload || {};

    if (action === 'save') {
      return jsonResponse(saveRecord(payload.record || {}));
    }
    if (action === 'search') {
      return jsonResponse(searchRecords(payload.query || '', payload.limit || 20));
    }
    if (action === 'load') {
      return jsonResponse(loadRecord(payload.record_id || ''));
    }
    if (action === 'save_settings') {
      return jsonResponse(saveSettings(payload.key || 'policy_settings', payload.settings || {}));
    }
    if (action === 'load_settings') {
      return jsonResponse(loadSettings(payload.key || 'policy_settings'));
    }

    return jsonResponse({ ok: false, error: 'Unknown action.' });
  } catch (err) {
    return jsonResponse({ ok: false, error: String(err && err.message ? err.message : err) });
  }
}

function doGet() {
  try {
    const sheet = recordsSheet();
    return jsonResponse({
      ok: true,
      app: 'UMI Google Sheet API',
      sheet: sheet.getName(),
      rows: Math.max(sheet.getLastRow() - 1, 0),
    });
  } catch (err) {
    return jsonResponse({ ok: false, error: String(err && err.message ? err.message : err) });
  }
}

function jsonResponse(data) {
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

function recordsSheet() {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  let sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(SHEET_NAME);
  }

  const currentHeaders = sheet.getRange(1, 1, 1, HEADERS.length).getValues()[0];
  const needsHeaders = HEADERS.some((header, index) => currentHeaders[index] !== header);
  if (needsHeaders) {
    sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
    sheet.setFrozenRows(1);
  }
  return sheet;
}

function settingsSheet() {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  let sheet = spreadsheet.getSheetByName(SETTINGS_SHEET_NAME);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(SETTINGS_SHEET_NAME);
  }

  const currentHeaders = sheet.getRange(1, 1, 1, SETTINGS_HEADERS.length).getValues()[0];
  const needsHeaders = SETTINGS_HEADERS.some((header, index) => currentHeaders[index] !== header);
  if (needsHeaders) {
    sheet.getRange(1, 1, 1, SETTINGS_HEADERS.length).setValues([SETTINGS_HEADERS]);
    sheet.setFrozenRows(1);
  }
  return sheet;
}

function saveRecord(record) {
  const sheet = recordsSheet();
  const row = HEADERS.map((header) => record[header] == null ? '' : record[header]);
  sheet.appendRow(row);
  return { ok: true, saved: true, record_id: record.record_id || '' };
}

function rowsAsObjects() {
  const sheet = recordsSheet();
  const values = sheet.getDataRange().getValues();
  if (values.length <= 1) {
    return [];
  }
  return values.slice(1).map((row) => {
    const item = {};
    HEADERS.forEach((header, index) => {
      item[header] = row[index];
    });
    return item;
  });
}

function searchRecords(query, limit) {
  const normalizedQuery = String(query || '').trim().toLowerCase();
  const maxRows = Math.max(1, Math.min(Number(limit) || 20, 50));
  if (!normalizedQuery) {
    return { ok: true, records: [] };
  }

  const records = rowsAsObjects()
    .filter((record) => {
      const names = [
        record.client_1,
        record.client_2,
        record.client_3,
        record.client_label,
      ].join(' ').toLowerCase();
      return names.indexOf(normalizedQuery) !== -1;
    })
    .sort((a, b) => String(b.saved_at || '').localeCompare(String(a.saved_at || '')))
    .slice(0, maxRows);

  return { ok: true, records };
}

function loadRecord(recordId) {
  const targetId = String(recordId || '').trim();
  const record = rowsAsObjects().find((item) => String(item.record_id || '').trim() === targetId);
  if (!record) {
    return { ok: false, error: 'Record not found.' };
  }
  return { ok: true, record };
}

function saveSettings(key, settings) {
  const sheet = settingsSheet();
  const targetKey = String(key || 'policy_settings').trim();
  const settingsJson = JSON.stringify(settings || {});
  const updatedAt = new Date().toISOString();
  const values = sheet.getDataRange().getValues();
  let targetRow = 0;
  for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
    if (String(values[rowIndex][0] || '').trim() === targetKey) {
      targetRow = rowIndex + 1;
      break;
    }
  }
  const row = [targetKey, updatedAt, settingsJson];
  if (targetRow) {
    sheet.getRange(targetRow, 1, 1, SETTINGS_HEADERS.length).setValues([row]);
  } else {
    sheet.appendRow(row);
  }
  saveReadableSettings(settings || {}, updatedAt);
  return { ok: true, saved: true, key: targetKey, updated_at: updatedAt };
}

function loadSettings(key) {
  const sheet = settingsSheet();
  const targetKey = String(key || 'policy_settings').trim();
  const values = sheet.getDataRange().getValues();
  for (let rowIndex = values.length - 1; rowIndex >= 1; rowIndex--) {
    if (String(values[rowIndex][0] || '').trim() === targetKey) {
      const settings = mergeReadableSettings(JSON.parse(String(values[rowIndex][2] || '{}') || '{}'));
      return {
        ok: true,
        found: true,
        key: targetKey,
        updated_at: String(values[rowIndex][1] || ''),
        settings_json: JSON.stringify(settings),
      };
    }
  }
  return { ok: true, found: false, key: targetKey, settings_json: '' };
}

function replaceSheetRows(sheetName, headers, rows) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }
  sheet.setFrozenRows(1);
}

function sheetObjects(sheetName) {
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    return [];
  }
  const values = sheet.getDataRange().getValues();
  if (values.length <= 1) {
    return [];
  }
  const headers = values[0].map((header) => String(header || '').trim());
  return values.slice(1).filter((row) => row.some((value) => String(value || '').trim() !== '')).map((row) => {
    const item = {};
    headers.forEach((header, index) => {
      item[header] = row[index];
    });
    return item;
  });
}

function valueOrZero(value) {
  return value == null || value === '' ? 0 : value;
}

function saveReadableSettings(settings, updatedAt) {
  const rateHeaders = ['Lender', 'Source', 'Product', 'KO', 'Offset', 'Floating Rate', 'Fixed Loan 1yr', 'Fixed Loan 18 months', 'Fixed Loan 2yr', 'Fixed Loan 3yr', 'updated_at'];
  const rateRows = (settings.rate_matrix || []).map((row) => [
    row.Lender || '',
    row.Source || '',
    row.Product || '',
    row.KO || '',
    valueOrZero(row.Offset),
    valueOrZero(row['Floating Rate']),
    valueOrZero(row['Fixed Loan 1yr']),
    valueOrZero(row['Fixed Loan 18 months']),
    valueOrZero(row['Fixed Loan 2yr']),
    valueOrZero(row['Fixed Loan 3yr']),
    updatedAt,
  ]);
  replaceSheetRows('rate_matrix', rateHeaders, rateRows);

  const pepperHeaders = ['Lender', 'Product Class', 'Meaning', 'Floating Rate', 'Fixed Loan 1yr', 'Fixed Loan 18 months', 'Fixed Loan 2yr', 'Fixed Loan 3yr', 'updated_at'];
  const pepperRows = (settings.specialist_rates || []).map((row) => [
    row.Lender || '',
    row['Product Class'] || '',
    row.Meaning || '',
    valueOrZero(row['Floating Rate']),
    valueOrZero(row['Fixed Loan 1yr']),
    valueOrZero(row['Fixed Loan 18 months']),
    valueOrZero(row['Fixed Loan 2yr']),
    valueOrZero(row['Fixed Loan 3yr']),
    updatedAt,
  ]);
  replaceSheetRows('pepper_rates', pepperHeaders, pepperRows);

  const cashbackHeaders = ['Lender', 'Standard_Pct', 'NewBuild_Pct', 'Max_Limit', 'FHL_Fixed', 'FHL_Pct', 'updated_at'];
  const cashbackRows = Object.keys(settings.cashback || {}).map((lender) => {
    const row = settings.cashback[lender] || {};
    return [lender, valueOrZero(row.Standard_Pct), valueOrZero(row.NewBuild_Pct), valueOrZero(row.Max_Limit), valueOrZero(row.FHL_Fixed), valueOrZero(row.FHL_Pct), updatedAt];
  });
  replaceSheetRows('cashback', cashbackHeaders, cashbackRows);

  const lemHeaders = ['Lender', '80.01-85.00%', '85.01-90.00%', '90.01-95.00%', 'updated_at'];
  const lemRows = Object.keys(settings.lem || {}).map((lender) => {
    const row = settings.lem[lender] || {};
    return [lender, valueOrZero(row['80.01-85.00%']), valueOrZero(row['85.01-90.00%']), valueOrZero(row['90.01-95.00%']), updatedAt];
  });
  replaceSheetRows('lem', lemHeaders, lemRows);

  const testHeaders = ['Lender', 'Sheet', 'Cell', 'updated_at'];
  const testRows = Object.keys(settings.test_rate_cells || {}).map((lender) => {
    const value = settings.test_rate_cells[lender] || [];
    return [lender, value[0] || '', value[1] || '', updatedAt];
  });
  replaceSheetRows('test_rate_cells', testHeaders, testRows);

  const lvrHeaders = ['Lender', 'Existing', 'Investment', 'New Build', 'Apt', 'Work Visa', 'FHL', 'updated_at'];
  const lvrRows = Object.keys(settings.lvr_policy || {}).map((lender) => {
    const row = settings.lvr_policy[lender] || {};
    return [lender, row.Existing || '', row.Investment || '', row['New Build'] || '', row.Apt || '', row['Work Visa'] || '', row.FHL || '', updatedAt];
  });
  replaceSheetRows('lvr_policy', lvrHeaders, lvrRows);
}

function mergeReadableSettings(settings) {
  const rateRows = sheetObjects('rate_matrix');
  if (rateRows.length) {
    settings.rate_matrix = rateRows.map((row) => ({
      Lender: String(row.Lender || '').trim(),
      Source: String(row.Source || '').trim(),
      Product: String(row.Product || '').trim(),
      KO: String(row.KO || '').trim(),
      Offset: valueOrZero(row.Offset),
      'Floating Rate': valueOrZero(row['Floating Rate']),
      'Fixed Loan 1yr': valueOrZero(row['Fixed Loan 1yr']),
      'Fixed Loan 18 months': valueOrZero(row['Fixed Loan 18 months']),
      'Fixed Loan 2yr': valueOrZero(row['Fixed Loan 2yr']),
      'Fixed Loan 3yr': valueOrZero(row['Fixed Loan 3yr']),
    })).filter((row) => row.Lender && row.Product);
  }

  const pepperRows = sheetObjects('pepper_rates');
  if (pepperRows.length) {
    settings.specialist_rates = pepperRows.map((row) => ({
      Lender: String(row.Lender || '').trim(),
      'Product Class': String(row['Product Class'] || '').trim(),
      Meaning: String(row.Meaning || '').trim(),
      'Floating Rate': valueOrZero(row['Floating Rate']),
      'Fixed Loan 1yr': valueOrZero(row['Fixed Loan 1yr']),
      'Fixed Loan 18 months': valueOrZero(row['Fixed Loan 18 months']),
      'Fixed Loan 2yr': valueOrZero(row['Fixed Loan 2yr']),
      'Fixed Loan 3yr': valueOrZero(row['Fixed Loan 3yr']),
    })).filter((row) => row.Lender && row['Product Class']);
  }

  const cashbackRows = sheetObjects('cashback');
  if (cashbackRows.length) {
    settings.cashback = {};
    cashbackRows.forEach((row) => {
      const lender = String(row.Lender || '').trim();
      if (!lender) return;
      settings.cashback[lender] = {
        Standard_Pct: valueOrZero(row.Standard_Pct),
        NewBuild_Pct: valueOrZero(row.NewBuild_Pct),
        Max_Limit: valueOrZero(row.Max_Limit),
        FHL_Fixed: valueOrZero(row.FHL_Fixed),
        FHL_Pct: valueOrZero(row.FHL_Pct),
      };
    });
  }

  const lemRows = sheetObjects('lem');
  if (lemRows.length) {
    settings.lem = {};
    lemRows.forEach((row) => {
      const lender = String(row.Lender || '').trim();
      if (!lender) return;
      settings.lem[lender] = {
        '80.01-85.00%': valueOrZero(row['80.01-85.00%']),
        '85.01-90.00%': valueOrZero(row['85.01-90.00%']),
        '90.01-95.00%': valueOrZero(row['90.01-95.00%']),
      };
    });
  }

  const testRows = sheetObjects('test_rate_cells');
  if (testRows.length) {
    settings.test_rate_cells = {};
    testRows.forEach((row) => {
      const lender = String(row.Lender || '').trim();
      if (!lender) return;
      settings.test_rate_cells[lender] = [String(row.Sheet || '').trim(), String(row.Cell || '').trim()];
    });
  }

  const lvrRows = sheetObjects('lvr_policy');
  if (lvrRows.length) {
    settings.lvr_policy = {};
    lvrRows.forEach((row) => {
      const lender = String(row.Lender || '').trim();
      if (!lender) return;
      settings.lvr_policy[lender] = {
        Existing: row.Existing || '',
        Investment: row.Investment || '',
        'New Build': row['New Build'] || '',
        Apt: row.Apt || '',
        'Work Visa': row['Work Visa'] || '',
        FHL: row.FHL || '',
      };
    });
  }
  return settings;
}