เว็บแอพบันทึกรายรับรายจ่าย

1/**2 * Money Diary — เว็บแอปบันทึกรายรับรายจ่าย (ใช้คนเดียว ล็อกด้วย PIN)3 * ไฟล์นี้: doGet, การจัดการ Sheet, PIN + session, ตัวช่วยทั่วไป4 */56var APP_NAME = 'Money Diary';7var TZ = 'Asia/Bangkok';8var PIN_ITER = 10000;9var MAX_PIN_FAILS = 5;10var PIN_LOCK_MINUTES = 15;11var SESSION_SHORT_MS = 12 * 60 * 60 * 1000;      // ไม่ติ๊ก "จำเครื่องนี้"12var SESSION_LONG_MS = 30 * 24 * 60 * 60 * 1000;  // จำเครื่องนี้ 30 วัน1314var SCHEMA = {15  Transactions: {16    headers: ['id', 'date', 'type', 'categoryId', 'amount', 'note', 'slipFileId', 'reminderId', 'createdAt', 'updatedAt'],17    textCols: ['id', 'date', 'type', 'categoryId', 'slipFileId', 'reminderId', 'createdAt', 'updatedAt'],18    userText: ['note']19  },20  Categories: {21    headers: ['id', 'name', 'type', 'icon', 'color', 'budget', 'sort'],22    textCols: ['id', 'type', 'icon', 'color'],23    userText: ['name']24  },25  Reminders: {26    headers: ['id', 'title', 'type', 'categoryId', 'amount', 'freq', 'dueDate', 'anchorDay', 'advanceDays', 'onDay', 'active', 'snoozeUntil', 'lastDoneDate', 'note', 'createdAt'],27    textCols: ['id', 'type', 'categoryId', 'freq', 'dueDate', 'advanceDays', 'snoozeUntil', 'lastDoneDate', 'createdAt'],28    userText: ['title', 'note']29  },30  Sessions: {31    headers: ['tokenHash', 'createdAt', 'expiresAt', 'device'],32    textCols: ['tokenHash', 'createdAt'],33    userText: ['device']34  },35  Settings: {36    headers: ['key', 'value', 'note'],37    textCols: ['key', 'value'],38    userText: ['note']39  }40};4142// ===================== Web app =====================4344function doGet(e) {45  var __egsDiag = e && e.parameter && e.parameter.__egsdiag === 'egsverify';46  var __egsLogs = [];47  if (__egsDiag) {48    try {49      var __ol = Logger.log;50      Logger.log = function (m) { try { __egsLogs.push(String(m)); } catch (_) {} return __ol.apply(Logger, arguments); };51    } catch (_) {}52  }53  try {54    var info = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL);55    if (info.getAuthorizationStatus() === ScriptApp.AuthorizationStatus.REQUIRED) {56      return HtmlService.createHtmlOutput('<p style="font-family:Sarabun,sans-serif;text-align:center;padding:24px">แอปต้องการสิทธิ์เพิ่ม <a target="_top" href="' + info.getAuthorizationUrl() + '">กดอนุญาตสิทธิ์</a></p>')57        .setTitle(APP_NAME)58        .addMetaTag('viewport', 'width=device-width, initial-scale=1')59        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);60    }6162    try {63      var ss = getDataSpreadsheet_();64      ensureAllSheets_(ss);65      Logger.log('[doGet] sheets ready: ' + ss.getId());66    } catch (setupErr) {67      Logger.log('[doGet] setup failed: ' + (setupErr && setupErr.stack ? setupErr.stack : setupErr));68    }6970    var url = '';71    try {72      url = ScriptApp.getService().getUrl() || '';73      var props = PropertiesService.getScriptProperties();74      if (url && props.getProperty('APP_URL') !== url) props.setProperty('APP_URL', url);75    } catch (urlErr) {76      Logger.log('[doGet] cannot read app url: ' + urlErr);77    }7879    var tpl = HtmlService.createTemplateFromFile('Index');80    tpl.appUrl = url;81    Logger.log('[doGet] render Index');82    return tpl.evaluate()83      .setTitle(APP_NAME)84      .addMetaTag('viewport', 'width=device-width, initial-scale=1, viewport-fit=cover')85      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);86  } catch (err) {87    if (__egsDiag)88      return ContentService.createTextOutput(89        'EGS_ERROR: ' + (err && err.stack ? err.stack : err) + '\nEGS_LOGS: ' + JSON.stringify(__egsLogs)90      ).setMimeType(ContentService.MimeType.TEXT);91    return HtmlService.createHtmlOutput('<p style="font-family:sans-serif;padding:16px">ขออภัย เกิดข้อผิดพลาด ลองใหม่อีกครั้ง</p>')92      .setTitle(APP_NAME)93      .addMetaTag('viewport', 'width=device-width, initial-scale=1')94      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);95  }96}9798function include(filename) {99  return HtmlService.createHtmlOutputFromFile(filename).getContent();100}101102// ===================== Spreadsheet =====================103104function getDataSpreadsheet_() {105  var props = PropertiesService.getScriptProperties();106  var id = props.getProperty('DATA_SS_ID');107  if (id) return SpreadsheetApp.openById(id);108  var lock = LockService.getScriptLock();109  lock.waitLock(30000);110  try {111    id = props.getProperty('DATA_SS_ID');112    if (id) return SpreadsheetApp.openById(id);113    var ss = SpreadsheetApp.create(APP_NAME + ' Data');114    props.setProperty('DATA_SS_ID', ss.getId());115    Logger.log('[getDataSpreadsheet_] created ' + ss.getId());116    return ss;117  } finally {118    lock.releaseLock();119  }120}121122function ensureAllSheets_(ss) {123  Object.keys(SCHEMA).forEach(function (name) { setupSheet_(ss, name); });124}125126function getSheet_(name) {127  return setupSheet_(getDataSpreadsheet_(), name);128}129130function setupSheet_(ss, name) {131  var sheet = ss.getSheetByName(name);132  if (sheet) return sheet;133  var lock = LockService.getScriptLock();134  lock.waitLock(30000);135  try {136    sheet = ss.getSheetByName(name);137    if (!sheet) {138      var def = SCHEMA[name];139      var all = ss.getSheets();140      var reuse = all.length === 1 && all[0].getLastRow() === 0 && !SCHEMA[all[0].getName()];141      sheet = reuse ? all[0].setName(name) : ss.insertSheet(name);142      def.textCols.forEach(function (h) {143        var col = def.headers.indexOf(h) + 1;144        sheet.getRange(1, col, sheet.getMaxRows(), 1).setNumberFormat('@');145      });146      sheet.appendRow(def.headers);147      sheet.getRange(1, 1, 1, def.headers.length).setFontWeight('bold');148      sheet.setFrozenRows(1);149      if (name === 'Categories') seedCategories_(sheet);150      if (name === 'Settings') sheet.appendRow(['NOTIFY_EMAIL', '', 'อีเมลรับการแจ้งเตือน (แก้ในแอป หรือแก้ในช่องนี้ก็ได้)']);151      SpreadsheetApp.flush();152      Logger.log('[setupSheet_] created ' + name);153    }154    return sheet;155  } finally {156    lock.releaseLock();157  }158}159160/** อ่านทั้งชีตครั้งเดียว คืน array ของ object + __row (เลขแถว 1-based) */161function readRows_(sheet) {162  var values = sheet.getDataRange().getValues();163  if (values.length < 2) return [];164  var headers = values[0].map(String);165  var tz = sheet.getParent().getSpreadsheetTimeZone() || TZ;166  var out = [];167  for (var i = 1; i < values.length; i++) {168    var row = values[i];169    if (row.join('') === '') continue;170    var o = { __row: i + 1 };171    for (var j = 0; j < headers.length; j++) {172      var v = row[j];173      if (v instanceof Date) v = Utilities.formatDate(v, tz, 'yyyy-MM-dd');174      o[headers[j]] = v;175    }176    out.push(o);177  }178  return out;179}180181function headersOf_(sheet) {182  return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(String);183}184185function toRow_(name, headers, obj) {186  var userText = SCHEMA[name].userText;187  return headers.map(function (h) {188    var v = obj[h];189    if (v === undefined || v === null) v = '';190    // ข้อความที่ผู้ใช้พิมพ์: ใส่ ' นำหน้าเสมอ → Sheets เก็บเป็นข้อความล้วน (กันสูตร, เลข 0 นำหน้าหาย, "3/4" กลายเป็นวันที่)191    if (userText.indexOf(h) >= 0 && String(v) !== '') v = "'" + String(v);192    return v;193  });194}195196function appendObj_(sheet, name, obj) {197  sheet.appendRow(toRow_(name, headersOf_(sheet), obj));198}199200function writeObj_(sheet, name, rowIndex, obj) {201  var headers = headersOf_(sheet);202  sheet.getRange(rowIndex, 1, 1, headers.length).setValues([toRow_(name, headers, obj)]);203}204205/** ลบแถวเดียว (rowIndex เป็นเลขแถวจริง 1-based จากการอ่านสดภายใต้ lock) */206function removeRow_(sheet, rowIndex) {207  if (sheet.getMaxRows() <= 2) {208    sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).clearContent();209  } else {210    sheet.deleteRow(rowIndex);211  }212}213214// ===================== ตัวช่วยทั่วไป =====================215216function merge_() {217  var out = {};218  for (var i = 0; i < arguments.length; i++) {219    var o = arguments[i] || {};220    for (var k in o) if (Object.prototype.hasOwnProperty.call(o, k)) out[k] = o[k];221  }222  return out;223}224225function indexBy_(rows, key) {226  var m = {};227  rows.forEach(function (r) { m[String(r[key])] = r; });228  return m;229}230231function newId_(prefix) {232  return prefix + Utilities.formatDate(new Date(), TZ, 'yyMMddHHmmss') + Utilities.getUuid().replace(/-/g, '').slice(0, 4);233}234235function nowText_() {236  return Utilities.formatDate(new Date(), TZ, "yyyy-MM-dd'T'HH:mm:ss");237}238239function todayYmd_() {240  return Utilities.formatDate(new Date(), TZ, 'yyyy-MM-dd');241}242243function toBool_(v) {244  return v === true || String(v).toUpperCase() === 'TRUE';245}246247function str_(v, max, label, required) {248  var s = String(v === undefined || v === null ? '' : v).trim();249  if (required && !s) throw new Error('กรุณากรอก' + label);250  if (s.length > max) throw new Error(label + 'ยาวเกิน ' + max + ' ตัวอักษร');251  return s;252}253254function amount_(v, allowZero) {255  var raw = String(v === undefined || v === null ? '' : v).replace(/,/g, '').trim();256  if (raw === '' && allowZero) return 0;257  var n = Number(raw);258  if (!isFinite(n) || n < 0 || (!allowZero && n <= 0)) throw new Error('จำนวนเงินไม่ถูกต้อง');259  if (n > 1000000000) throw new Error('จำนวนเงินมากเกินไป');260  return Math.round(n * 100) / 100;261}262263function ymdOk_(s) {264  if (!/^\d{4}-\d{2}-\d{2}$/.test(String(s))) return false;265  var p = String(s).split('-').map(Number);266  var d = new Date(Date.UTC(p[0], p[1] - 1, p[2]));267  return p[0] >= 2000 && p[0] <= 2100 && d.getUTCFullYear() === p[0] && d.getUTCMonth() === p[1] - 1 && d.getUTCDate() === p[2];268}269270function ymdToUtc_(s) {271  var p = String(s).split('-').map(Number);272  return Date.UTC(p[0], p[1] - 1, p[2]);273}274275function utcToYmd_(ms) {276  var d = new Date(ms);277  return d.getUTCFullYear() + '-' + ('0' + (d.getUTCMonth() + 1)).slice(-2) + '-' + ('0' + d.getUTCDate()).slice(-2);278}279280function addDays_(s, n) {281  return utcToYmd_(ymdToUtc_(s) + n * 86400000);282}283284function diffDays_(from, to) {285  return Math.round((ymdToUtc_(to) - ymdToUtc_(from)) / 86400000);286}287288function daysInMonth_(y, m) {289  return new Date(Date.UTC(y, m, 0)).getUTCDate();290}291292function escapeHtml_(s) {293  return String(s === undefined || s === null ? '' : s)294    .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;')295    .replace(/"/g, '&quot;').replace(/'/g, '&#39;');296}297298// ===================== PIN + Session =====================299300function authError_(msg) {301  var e = new Error(msg || 'กรุณาใส่ PIN อีกครั้ง');302  e.isAuth = true;303  return e;304}305306/** ตัวห่อทุกฟังก์ชันที่ต้องล็อกอิน: ตรวจ session แล้วคืน envelope {ok,data} / {ok:false,message} */307function withSession_(name, token, fn) {308  try {309    var session = requireSession_(token);310    var data = fn(session);311    Logger.log('[' + name + '] ok');312    return { ok: true, data: data === undefined ? null : data };313  } catch (err) {314    return errorResult_(name, err);315  }316}317318/** แปลง error เป็น envelope ที่หน้าเว็บอ่านได้ */319function errorResult_(name, err) {320  Logger.log('[' + name + '] ' + (err && err.stack ? err.stack : err));321  var res = { ok: false, auth: !!(err && err.isAuth), stale: !!(err && err.stale), message: friendlyMessage_(err) };322  if (isPermissionError_(err)) {323    // เจ้าของยังไม่ได้กดอนุญาตสิทธิ์ที่เพิ่มเข้ามา (เช่น ตั้งเวลาอัตโนมัติ/ส่งอีเมล) — ส่งลิงก์อนุญาตกลับไปให้กดได้เลย324    res.message = 'แอปยังไม่ได้รับสิทธิ์สำหรับงานนี้ กรุณากด "อนุญาตสิทธิ์" แล้วลองใหม่';325    try { res.authUrl = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL).getAuthorizationUrl() || ''; } catch (_) {}326  }327  return res;328}329330function isPermissionError_(err) {331  var msg = err && err.message ? err.message : String(err);332  return /permission|authoriz|required permissions|สิทธิ์ที่ระบุไว้ไม่เพียงพอ|ไม่ได้รับอนุญาต/i.test(msg);333}334335function friendlyMessage_(err) {336  var msg = err && err.message ? err.message : String(err);337  if (/lock|ล็อก/i.test(msg) && /timeout|หมดเวลา/i.test(msg)) return 'ระบบกำลังบันทึกรายการอื่นอยู่ กรุณาลองใหม่อีกครั้ง';338  return msg;339}340341function sha256Hex_(s) {342  var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(s), Utilities.Charset.UTF_8);343  return bytes.map(function (b) { return ('0' + (b & 0xff).toString(16)).slice(-2); }).join('');344}345346function randomToken_() {347  return (Utilities.getUuid() + Utilities.getUuid()).replace(/-/g, '');348}349350/**351 * hash PIN: HMAC-SHA256 วนซ้ำ PIN_ITER รอบ (ผลลัพธ์เท่าเดิมทุกประการ — PIN ที่ตั้งไว้แล้วใช้ต่อได้)352 * เดิมเรียก Utilities.computeHmacSha256Signature ทีละรอบ ซึ่งแต่ละครั้งช้ามากใน Apps Script (10,000 รอบ ≈ เกือบนาที)353 * ตอนนี้คำนวณ SHA-256 ด้วย JavaScript ล้วน (เร็วกว่าหลายร้อยเท่า) และตรวจเทียบกับ Utilities ก่อนใช้เสมอ354 */355function hashPin_(pin, salt, pepper) {356  var keyB = Utilities.newBlob(String(salt)).getBytes();357  var msgB = Utilities.newBlob(String(pin) + String(pepper)).getBytes();358  if (fastHmacOk_()) {359    return Utilities.base64Encode(toSigned_(hmacSha256Iter_(toUnsigned_(keyB), toUnsigned_(msgB), PIN_ITER)));360  }361  Logger.log('[hashPin_] fast HMAC self-test failed — using slow path');362  var bytes = msgB;363  for (var i = 0; i < PIN_ITER; i++) bytes = Utilities.computeHmacSha256Signature(bytes, keyB);364  return Utilities.base64Encode(bytes);365}366367function toUnsigned_(arr) { return arr.map(function (b) { return b & 0xff; }); }368function toSigned_(arr) { return arr.map(function (b) { return b > 127 ? b - 256 : b; }); }369370/** ตรวจว่า HMAC แบบ JavaScript ให้ผลตรงกับของ Google ทุกไบต์ (จำผลไว้ 6 ชม.) */371function fastHmacOk_() {372  var cache = CacheService.getScriptCache();373  var cached = cache.get('fast_hmac_ok');374  if (cached) return cached === '1';375  var ok = false;376  try {377    var key = Utilities.newBlob('salt-self-test-' + APP_NAME).getBytes();378    var msg = Utilities.newBlob('123456' + 'pepper-self-test-0123456789abcdef0123456789abcdef0123456789abcdef').getBytes();379    var slow = msg;380    for (var i = 0; i < 3; i++) slow = Utilities.computeHmacSha256Signature(slow, key);381    var fast = toSigned_(hmacSha256Iter_(toUnsigned_(key), toUnsigned_(msg), 3));382    ok = Utilities.base64Encode(slow) === Utilities.base64Encode(fast);383  } catch (e) {384    Logger.log('[fastHmacOk_] ' + e);385  }386  try { cache.put('fast_hmac_ok', ok ? '1' : '0', 6 * 3600); } catch (_) {}387  return ok;388}389390var SHA256_K_ = [391  0x428a2f98, 0x71374491, 0xb5c0fbcf, 0xe9b5dba5, 0x3956c25b, 0x59f111f1, 0x923f82a4, 0xab1c5ed5,392  0xd807aa98, 0x12835b01, 0x243185be, 0x550c7dc3, 0x72be5d74, 0x80deb1fe, 0x9bdc06a7, 0xc19bf174,393  0xe49b69c1, 0xefbe4786, 0x0fc19dc6, 0x240ca1cc, 0x2de92c6f, 0x4a7484aa, 0x5cb0a9dc, 0x76f988da,394  0x983e5152, 0xa831c66d, 0xb00327c8, 0xbf597fc7, 0xc6e00bf3, 0xd5a79147, 0x06ca6351, 0x14292967,395  0x27b70a85, 0x2e1b2138, 0x4d2c6dfc, 0x53380d13, 0x650a7354, 0x766a0abb, 0x81c2c92e, 0x92722c85,396  0xa2bfe8a1, 0xa81a664b, 0xc24b8b70, 0xc76c51a3, 0xd192e819, 0xd6990624, 0xf40e3585, 0x106aa070,397  0x19a4c116, 0x1e376c08, 0x2748774c, 0x34b0bcb5, 0x391c0cb3, 0x4ed8aa4a, 0x5b9cca4f, 0x682e6ff3,398  0x748f82ee, 0x78a5636f, 0x84c87814, 0x8cc70208, 0x90befffa, 0xa4506ceb, 0xbef9a3f7, 0xc67178f2399];400var SHA256_IV_ = [0x6a09e667, 0xbb67ae85, 0x3c6ef372, 0xa54ff53a, 0x510e527f, 0x9b05688c, 0x1f83d9ab, 0x5be0cd19];401402function sha256Block_(H, buf, off, W) {403  var t, s0, s1;404  for (t = 0; t < 16; t++) {405    var p = off + t * 4;406    W[t] = (buf[p] << 24) | (buf[p + 1] << 16) | (buf[p + 2] << 8) | buf[p + 3];407  }408  for (t = 16; t < 64; t++) {409    var w15 = W[t - 15], w2 = W[t - 2];410    s0 = ((w15 >>> 7) | (w15 << 25)) ^ ((w15 >>> 18) | (w15 << 14)) ^ (w15 >>> 3);411    s1 = ((w2 >>> 17) | (w2 << 15)) ^ ((w2 >>> 19) | (w2 << 13)) ^ (w2 >>> 10);412    W[t] = (W[t - 16] + s0 + W[t - 7] + s1) | 0;413  }414  var a = H[0], b = H[1], c = H[2], d = H[3], e = H[4], f = H[5], g = H[6], h = H[7];415  for (t = 0; t < 64; t++) {416    var S1 = ((e >>> 6) | (e << 26)) ^ ((e >>> 11) | (e << 21)) ^ ((e >>> 25) | (e << 7));417    var ch = (e & f) ^ (~e & g);418    var t1 = (h + S1 + ch + SHA256_K_[t] + W[t]) | 0;419    var S0 = ((a >>> 2) | (a << 30)) ^ ((a >>> 13) | (a << 19)) ^ ((a >>> 22) | (a << 10));420    var maj = (a & b) ^ (a & c) ^ (b & c);421    var t2 = (S0 + maj) | 0;422    h = g; g = f; f = e; e = (d + t1) | 0; d = c; c = b; b = a; a = (t1 + t2) | 0;423  }424  H[0] = (H[0] + a) | 0; H[1] = (H[1] + b) | 0; H[2] = (H[2] + c) | 0; H[3] = (H[3] + d) | 0;425  H[4] = (H[4] + e) | 0; H[5] = (H[5] + f) | 0; H[6] = (H[6] + g) | 0; H[7] = (H[7] + h) | 0;426}427428/** SHA-256 ต่อจาก state ที่ประมวลผลไปแล้ว prefixLen ไบต์ (ใช้กับ HMAC ที่คำนวณ ipad/opad ไว้ล่วงหน้า) */429function sha256Finish_(state, bytes, prefixLen, W) {430  var H = state.slice();431  var len = bytes.length;432  var padLen = Math.ceil((len + 9) / 64) * 64;433  var buf = new Array(padLen);434  for (var i = 0; i < padLen; i++) buf[i] = i < len ? bytes[i] : 0;435  buf[len] = 0x80;436  var bits = (prefixLen + len) * 8;437  buf[padLen - 4] = (bits >>> 24) & 0xff;438  buf[padLen - 3] = (bits >>> 16) & 0xff;439  buf[padLen - 2] = (bits >>> 8) & 0xff;440  buf[padLen - 1] = bits & 0xff;441  for (var off = 0; off < padLen; off += 64) sha256Block_(H, buf, off, W);442  var out = new Array(32);443  for (var j = 0; j < 8; j++) {444    out[j * 4] = (H[j] >>> 24) & 0xff;445    out[j * 4 + 1] = (H[j] >>> 16) & 0xff;446    out[j * 4 + 2] = (H[j] >>> 8) & 0xff;447    out[j * 4 + 3] = H[j] & 0xff;448  }449  return out;450}451452/** HMAC-SHA256 วนซ้ำ: x = HMAC(key, x) จำนวน iterations รอบ (byte 0..255) */453function hmacSha256Iter_(keyBytes, msgBytes, iterations) {454  var W = new Array(64);455  var key = keyBytes.length > 64 ? sha256Finish_(SHA256_IV_, keyBytes, 0, W) : keyBytes;456  var ipad = new Array(64), opad = new Array(64);457  for (var i = 0; i < 64; i++) {458    var k = i < key.length ? key[i] : 0;459    ipad[i] = k ^ 0x36;460    opad[i] = k ^ 0x5c;461  }462  var innerState = SHA256_IV_.slice();463  sha256Block_(innerState, ipad, 0, W);464  var outerState = SHA256_IV_.slice();465  sha256Block_(outerState, opad, 0, W);466  var cur = msgBytes;467  for (var n = 0; n < iterations; n++) {468    cur = sha256Finish_(outerState, sha256Finish_(innerState, cur, 64, W), 64, W);469  }470  return cur;471}472473function requireSession_(token) {474  if (!token || typeof token !== 'string' || token.length > 200) throw authError_();475  var hash = sha256Hex_(token);476  var epoch = PropertiesService.getScriptProperties().getProperty('SESSION_EPOCH') || '0';477  var cache = CacheService.getScriptCache();478  var key = 'ses_' + epoch + '_' + hash;479  var now = Date.now();480  var cached = cache.get(key);481  if (cached && Number(cached) > now) return { hash: hash };482483  var rows = readRows_(getSheet_('Sessions'));484  for (var i = 0; i < rows.length; i++) {485    var r = rows[i];486    if (String(r.tokenHash) === hash && Number(r.expiresAt) > now) {487      var ttl = Math.min(600, Math.floor((Number(r.expiresAt) - now) / 1000));488      if (ttl > 0) { try { cache.put(key, String(r.expiresAt), ttl); } catch (_) {} }489      return { hash: hash };490    }491  }492  throw authError_();493}494495function createSession_(remember, device) {496  var sheet = getSheet_('Sessions');497  var token = randomToken_();498  var now = Date.now();499  var expiresAt = now + (remember ? SESSION_LONG_MS : SESSION_SHORT_MS);500  var lock = LockService.getScriptLock();501  lock.waitLock(15000);502  try {503    var expired = readRows_(sheet)504      .filter(function (r) { return Number(r.expiresAt) <= now; })505      .map(function (r) { return r.__row; })506      .sort(function (a, b) { return b - a; });507    expired.forEach(function (row) { removeRow_(sheet, row); });508    appendObj_(sheet, 'Sessions', {509      tokenHash: sha256Hex_(token),510      createdAt: nowText_(),511      expiresAt: expiresAt,512      device: String(device || '').slice(0, 60)513    });514  } finally {515    lock.releaseLock();516  }517  Logger.log('[createSession_] session created, remember=' + !!remember);518  return { token: token, expiresAt: expiresAt };519}520521/** ตรวจ PIN — ต้องเรียกภายใน script lock (นับครั้งที่ผิด + ล็อกชั่วคราว) */522function checkPin_(props, pin) {523  var hash = props.getProperty('PIN_HASH');524  if (!hash) return { ok: false, needSetup: true, message: 'ยังไม่ได้ตั้ง PIN' };525  var now = Date.now();526  var until = Number(props.getProperty('PIN_LOCK_UNTIL') || 0);527  if (until > now) {528    return { ok: false, locked: true, message: 'ใส่ PIN ผิดหลายครั้ง กรุณารอ ' + Math.ceil((until - now) / 60000) + ' นาทีแล้วลองใหม่' };529  }530  if (hashPin_(pin, props.getProperty('PIN_SALT'), props.getProperty('PIN_PEPPER')) === hash) {531    props.setProperties({ PIN_FAILS: '0', PIN_LOCK_UNTIL: '0', PIN_LOCK_STRIKES: '0' });532    return { ok: true };533  }534  var fails = Number(props.getProperty('PIN_FAILS') || 0) + 1;535  if (fails >= MAX_PIN_FAILS) {536    // ล็อกนานขึ้นเรื่อย ๆ ทุกครั้งที่โดนล็อกซ้ำ (15 → 30 → 60 นาที … สูงสุด 24 ชม.) กันการเดา PIN แบบไล่ทีละรอบ537    var strikes = Number(props.getProperty('PIN_LOCK_STRIKES') || 0) + 1;538    var mins = Math.min(PIN_LOCK_MINUTES * Math.pow(2, strikes - 1), 24 * 60);539    props.setProperties({ PIN_FAILS: '0', PIN_LOCK_UNTIL: String(now + mins * 60000), PIN_LOCK_STRIKES: String(strikes) });540    return { ok: false, locked: true, message: 'ใส่ PIN ผิด ' + MAX_PIN_FAILS + ' ครั้ง ระบบล็อกไว้ ' + mins + ' นาที' };541  }542  props.setProperty('PIN_FAILS', String(fails));543  return { ok: false, message: 'PIN ไม่ถูกต้อง (เหลืออีก ' + (MAX_PIN_FAILS - fails) + ' ครั้ง)' };544}545546function getAuthState() {547  try {548    var props = PropertiesService.getScriptProperties();549    var pinSet = !!props.getProperty('PIN_HASH');550    if (!pinSet) ensureSetupCode_();551    return { ok: true, data: { pinSet: pinSet } };552  } catch (err) {553    Logger.log('[getAuthState] ' + (err && err.stack ? err.stack : err));554    return { ok: false, message: friendlyMessage_(err) };555  }556}557558/**559 * รหัสตั้งค่าครั้งแรก: แอปเปิดให้ทุกคนที่มีลิงก์ จึงต้องพิสูจน์ว่าเป็นเจ้าของก่อนตั้ง PIN ครั้งแรก560 * รหัสนี้เขียนไว้ในแท็บ Settings ของ Sheet ใน Drive ของเจ้าของ (คนอื่นเปิดไม่ได้) และถูกล้างทิ้งเมื่อตั้ง PIN สำเร็จ561 */562var SETUP_CODE_KEY = 'SETUP_CODE';563564function ensureSetupCode_() {565  var props = PropertiesService.getScriptProperties();566  var code = props.getProperty(SETUP_CODE_KEY);567  if (code) return code;568  var sheet = getSheet_('Settings');569  var lock = LockService.getScriptLock();570  lock.waitLock(15000);571  try {572    code = props.getProperty(SETUP_CODE_KEY);573    if (code) return code;574    code = Utilities.getUuid().replace(/-/g, '').slice(0, 8).toUpperCase();575    props.setProperty(SETUP_CODE_KEY, code);576    writeSetupCodeRow_(sheet, code, 'รหัสตั้งค่าครั้งแรก — ใส่ในแอปตอนตั้ง PIN ครั้งแรก (ลบให้อัตโนมัติเมื่อตั้ง PIN แล้ว)');577    Logger.log('[ensureSetupCode_] setup code created');578    return code;579  } finally {580    lock.releaseLock();581  }582}583584/** เขียน/ล้างแถว SETUP_CODE ในแท็บ Settings — ต้องเรียกภายใน script lock */585function writeSetupCodeRow_(sheet, code, note) {586  var rows = readRows_(sheet);587  var cur = null;588  for (var i = 0; i < rows.length; i++) if (String(rows[i].key) === SETUP_CODE_KEY) { cur = rows[i]; break; }589  var obj = { key: SETUP_CODE_KEY, value: code, note: note };590  if (cur) writeObj_(sheet, 'Settings', cur.__row, obj);591  else if (code) appendObj_(sheet, 'Settings', obj);592  SpreadsheetApp.flush();593}594595function setupPin(pin, remember, device, setupCode) {596  try {597    pin = String(pin || '');598    if (!/^\d{6}$/.test(pin)) throw new Error('PIN ต้องเป็นตัวเลข 6 หลัก');599    var code = String(setupCode || '').replace(/\s+/g, '').toUpperCase();600    getSheet_('Sessions');601    var settings = getSheet_('Settings');602    var props = PropertiesService.getScriptProperties();603    var lock = LockService.getScriptLock();604    lock.waitLock(15000);605    try {606      if (props.getProperty('PIN_HASH')) throw new Error('ตั้ง PIN ไว้แล้ว กรุณาใส่ PIN เพื่อเข้าใช้งาน');607      var expected = props.getProperty(SETUP_CODE_KEY);608      if (!expected || !code || code !== expected) {609        Logger.log('[setupPin] bad setup code');610        return { ok: false, badCode: true, message: 'รหัสตั้งค่าครั้งแรกไม่ถูกต้อง ดูรหัสได้ในไฟล์ "' + APP_NAME + ' Data" ใน Google Drive ของคุณ แท็บ Settings ช่อง SETUP_CODE' };611      }612      var pepper = props.getProperty('PIN_PEPPER') || randomToken_();613      var salt = Utilities.getUuid();614      props.setProperties({615        PIN_PEPPER: pepper,616        PIN_SALT: salt,617        PIN_HASH: hashPin_(pin, salt, pepper),618        PIN_FAILS: '0',619        PIN_LOCK_UNTIL: '0',620        PIN_LOCK_STRIKES: '0'621      });622      props.deleteProperty(SETUP_CODE_KEY);623      try { writeSetupCodeRow_(settings, '', 'ตั้ง PIN แล้ว — ไม่ต้องใช้รหัสนี้อีก'); } catch (clearErr) { Logger.log('[setupPin] clear code row failed: ' + clearErr); }624    } finally {625      lock.releaseLock();626    }627    Logger.log('[setupPin] pin created');628    return { ok: true, data: createSession_(!!remember, device) };629  } catch (err) {630    Logger.log('[setupPin] ' + (err && err.stack ? err.stack : err));631    return { ok: false, message: friendlyMessage_(err) };632  }633}634635function unlock(pin, remember, device) {636  try {637    pin = String(pin || '');638    if (!/^\d{6}$/.test(pin)) throw new Error('PIN ต้องเป็นตัวเลข 6 หลัก');639    getSheet_('Sessions');640    var props = PropertiesService.getScriptProperties();641    var lock = LockService.getScriptLock();642    lock.waitLock(15000);643    var res;644    try {645      res = checkPin_(props, pin);646    } finally {647      lock.releaseLock();648    }649    if (!res.ok) {650      Logger.log('[unlock] rejected');651      return res;652    }653    return { ok: true, data: createSession_(!!remember, device) };654  } catch (err) {655    Logger.log('[unlock] ' + (err && err.stack ? err.stack : err));656    return { ok: false, message: friendlyMessage_(err) };657  }658}659660function changePin(token, oldPin, newPin) {661  return withSession_('changePin', token, function (session) {662    oldPin = String(oldPin || '');663    newPin = String(newPin || '');664    if (!/^\d{6}$/.test(oldPin) || !/^\d{6}$/.test(newPin)) throw new Error('PIN ต้องเป็นตัวเลข 6 หลัก');665    if (oldPin === newPin) throw new Error('PIN ใหม่ต้องไม่ซ้ำกับ PIN เดิม');666    var sheet = getSheet_('Sessions');667    var props = PropertiesService.getScriptProperties();668    var lock = LockService.getScriptLock();669    lock.waitLock(15000);670    try {671      var res = checkPin_(props, oldPin);672      if (!res.ok) throw new Error(String(res.message).replace('PIN ไม่ถูกต้อง', 'PIN เดิมไม่ถูกต้อง'));673      var salt = Utilities.getUuid();674      var newHash = hashPin_(newPin, salt, props.getProperty('PIN_PEPPER'));675      // ออกจากระบบอุปกรณ์อื่นทั้งหมดก่อน (เหลือเครื่องนี้) — ถ้าขั้นนี้พัง PIN เดิมยังใช้ได้ ไม่เกิดสถานะครึ่ง ๆ กลาง ๆ676      readRows_(sheet)677        .filter(function (r) { return String(r.tokenHash) !== session.hash; })678        .map(function (r) { return r.__row; })679        .sort(function (a, b) { return b - a; })680        .forEach(function (row) { removeRow_(sheet, row); });681      SpreadsheetApp.flush();682      props.setProperty('SESSION_EPOCH', String(Number(props.getProperty('SESSION_EPOCH') || 0) + 1));683      // ถอนสิทธิ์เครื่องอื่นสำเร็จแล้ว ค่อยบันทึก PIN ใหม่684      props.setProperties({ PIN_SALT: salt, PIN_HASH: newHash });685      Logger.log('[changePin] other sessions revoked, pin updated');686    } finally {687      lock.releaseLock();688    }689    return { changed: true };690  });691}692693function logout(token) {694  try {695    if (!token || typeof token !== 'string' || token.length > 200) return { ok: true, data: null };696    var hash = sha256Hex_(token);697    var sheet = getSheet_('Sessions');698    var lock = LockService.getScriptLock();699    lock.waitLock(15000);700    try {701      readRows_(sheet)702        .filter(function (r) { return String(r.tokenHash) === hash; })703        .map(function (r) { return r.__row; })704        .sort(function (a, b) { return b - a; })705        .forEach(function (row) { removeRow_(sheet, row); });706    } finally {707      lock.releaseLock();708    }709    var epoch = PropertiesService.getScriptProperties().getProperty('SESSION_EPOCH') || '0';710    CacheService.getScriptCache().remove('ses_' + epoch + '_' + hash);711    Logger.log('[logout] ok');712    return { ok: true, data: null };713  } catch (err) {714    Logger.log('[logout] ' + (err && err.stack ? err.stack : err));715    return { ok: false, message: friendlyMessage_(err) };716  }717}718719function logoutAll(token) {720  return withSession_('logoutAll', token, function () {721    var sheet = getSheet_('Sessions');722    var props = PropertiesService.getScriptProperties();723    var lock = LockService.getScriptLock();724    lock.waitLock(15000);725    try {726      var last = sheet.getLastRow();727      if (last > 1) sheet.getRange(2, 1, last - 1, sheet.getLastColumn()).clearContent();728      props.setProperty('SESSION_EPOCH', String(Number(props.getProperty('SESSION_EPOCH') || 0) + 1));729    } finally {730      lock.releaseLock();731    }732    return { loggedOut: true };733  });734}735736/** รับ error จากหน้าเว็บมาเก็บใน log (ช่วยไล่ปัญหาบนมือถือที่ไม่มี DevTools) — ไม่เก็บข้อมูลส่วนตัว */737function logClientError(payload) {738  try {739    var p = payload && typeof payload === 'object' ? payload : {};740    Logger.log('[client error] ' + JSON.stringify({741      message: String(p.message || '').slice(0, 300),742      source: String(p.source || '').slice(0, 200),743      line: Number(p.line) || 0,744      ua: String(p.ua || '').slice(0, 200)745    }));746  } catch (_) {}747  return { ok: true, data: null };748}749750/** ล้าง session ที่หมดอายุ (เรียกจาก trigger รายวัน) */751function pruneSessions_() {752  var sheet = getSheet_('Sessions');753  var now = Date.now();754  var lock = LockService.getScriptLock();755  lock.waitLock(30000);756  try {757    var rows = readRows_(sheet)758      .filter(function (r) { return Number(r.expiresAt) <= now; })759      .map(function (r) { return r.__row; })760      .sort(function (a, b) { return b - a; });761    rows.forEach(function (row) { removeRow_(sheet, row); });762    Logger.log('[pruneSessions_] removed ' + rows.length);763  } finally {764    lock.releaseLock();765  }766}767