Telering Journaling
Jurnal refleksi pribadi berbasis kalender mood dengan database Google Spreadsheet portabel.
Web Telering hanya mediatorDB pada Spreadsheet PribadiCache Browser
Catatan: Analisis mood otomatis hanya estimasi reflektif, bukan diagnosis klinis. Simpan data sensitif di Spreadsheet pribadi, gunakan PIN kuat, dan jangan bagikan URL Web App/Spreadsheet ke pihak yang tidak berwenang.
User Journey:
Tes Koneksi API → Siapkan/Cek Database → Jika belum ada profil, buat akun pertama kali → Jika profil sudah ada, langsung login dengan PIN → Isi jurnal lewat kalender.
Hubungkan Database

URL Web App Apps Script pengguna Spreadsheet ID atau URL Ingat URL Web App dan Spreadsheet ID di cache browser perangkat ini
Data koneksi disimpan hanya di localStorage browser perangkat ini jika opsi diaktifkan. PIN tidak pernah disimpan.
Code.gs untuk Apps Script

Preview Code.gs
Cara Penggunaan Code.gs
/**
 * Telering Reflect Journal - Patient Flow API Bridge v21
 * Pasang kode ini di Google Apps Script dari Spreadsheet pengguna.
 * Deploy sebagai Web App:
 * - Execute as: Me
 * - Who has access: Anyone
 */

const TRJ = {
  VERSION: '21.0.0-verified-save-calendar-sync',
  CLIENT_CODE: 'ME',
  TEXT_CELL_LIMIT: 6000,
  SHEETS: {
    SETTINGS: 'Settings',
    CLIENTS: 'Clients',
    ENTRIES: 'JournalEntries',
    AUDIT: 'AuditLog'
  },
  HEADERS: {
    SETTINGS: ['key', 'value', 'updatedAt'],
    CLIENTS: ['clientCode', 'clientName', 'pinHash', 'email', 'phone', 'psychologistName', 'notes', 'status', 'createdAt', 'updatedAt'],
    ENTRIES: [
      'entryId', 'clientCode', 'date', 'moodManual', 'moodIntensity', 'moodScore', 'moodAuto', 'emotionTags',
      'journalText', 'triggerText', 'copingText', 'topicToDiscuss', 'gratitudeText', 'sleepQuality', 'anxietyLevel',
      'riskFlag', 'riskKeywords', 'needFeedback', 'therapistFeedback', 'createdAt', 'updatedAt',
      'sleepDuration', 'anxietyFelt', 'sadnessFelt', 'sadnessLevel', 'angerFelt', 'angerLevel', 'disappointmentFelt', 'disappointmentLevel'
    ],
    AUDIT: ['timestamp', 'actor', 'action', 'clientCode', 'entryId', 'detail']
  }
};

function doGet(e) {
  const p = (e && e.parameter) || {};

  if (p.action === 'rpc') {
    return jsonpResponse_(p.callback, p.rpcId || '', function () {
      const fn = String(p.fn || '').trim();
      let args = [];
      if (p.a || p.args64) {
        args = JSON.parse(decodeBase64WebSafe_(p.a || p.args64));
      } else if (p.args) {
        args = JSON.parse(p.args);
      }
      if (!Array.isArray(args)) throw new Error('Format args tidak valid.');
      return handleRpc_(fn, args);
    });
  }

  const text = 'Telering Reflect Journal API Bridge aktif. Version: ' + TRJ.VERSION + '. Gunakan URL ini di halaman Telering.';
  return ContentService.createTextOutput(text).setMimeType(ContentService.MimeType.TEXT);
}

function decodeBase64WebSafe_(value) {
  const s = String(value || '');
  return Utilities.newBlob(Utilities.base64DecodeWebSafe(s)).getDataAsString('UTF-8');
}

function doPost(e) {
  const p = (e && e.parameter) || {};

  if (p.action === 'rpc_post' || p.action === 'rpc') {
    return postMessageResponse_(p.rpcId || '', function () {
      const fn = String(p.fn || '').trim();
      let args = [];
      if (p.a || p.args64) {
        args = JSON.parse(decodeBase64WebSafe_(p.a || p.args64));
      } else if (p.args) {
        args = JSON.parse(p.args);
      }
      if (!Array.isArray(args)) throw new Error('Format args tidak valid.');
      return handleRpc_(fn, args);
    });
  }

  return postMessageResponse_('', function () { return getHealth(); });
}

function postMessageResponse_(rpcId, runner) {
  const payload = { trj: 'api-result', id: rpcId || '', ok: false, result: null, error: '' };
  try {
    payload.result = runner();
    payload.ok = true;
  } catch (err) {
    payload.ok = false;
    payload.error = err && err.message ? err.message : String(err);
  }

  const encoded = Utilities.base64EncodeWebSafe(JSON.stringify(payload), Utilities.Charset.UTF_8);
  const html = '<!doctype html><html><head><meta charset="utf-8"></head><body><script>(function(){try{window.parent.postMessage({trj:"api-result-b64",b64:"' + encoded + '"},"*");}catch(e){}})();</script></body></html>';
  return HtmlService.createHtmlOutput(html).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function handleRpc_(fn, args) {
  const map = {
    getHealth,
    getAppStatus,
    setupDatabase,
    createProfile,
    validateProfile,
    updateProfile,
    getMonthData,
    getEntry,
    verifyEntrySaved,
    saveEntry
  };
  if (!map[fn]) throw new Error('Fungsi API tidak dikenal: ' + fn);
  return map[fn].apply(null, args || []);
}

function jsonpResponse_(callbackName, rpcId, runner) {
  const cb = sanitizeCallback_(callbackName || 'callback');
  const payload = { trj: 'api-result', id: rpcId || '', ok: false, result: null, error: '' };
  try {
    payload.result = runner();
    payload.ok = true;
  } catch (err) {
    payload.ok = false;
    payload.error = err && err.message ? err.message : String(err);
  }
  const js = cb + '(' + JSON.stringify(payload) + ');';
  return ContentService.createTextOutput(js).setMimeType(ContentService.MimeType.JAVASCRIPT);
}

function sanitizeCallback_(name) {
  const n = String(name || '').trim();
  if (/^[A-Za-z_$][0-9A-Za-z_$\.]*$/.test(n)) return n;
  return 'callback';
}

function getHealth() {
  return {
    ok: true,
    version: TRJ.VERSION,
    message: 'API Bridge aktif. Lanjutkan dengan Cek/Siapkan Database.'
  };
}

function setupDatabase(spreadsheetIdOrUrl) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  writeAudit_(ss, 'system', 'SETUP_DATABASE', '', '', 'Database prepared');
  return getAppStatus(spreadsheetIdOrUrl);
}

function getAppStatus(spreadsheetIdOrUrl) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  const profile = getProfile_(ss);
  return {
    ok: true,
    spreadsheetId: ss.getId(),
    spreadsheetName: ss.getName(),
    databaseReady: true,
    hasProfile: !!profile,
    profile: profile ? safeProfile_(profile) : null,
    message: profile ? 'Database siap. Profil jurnal sudah ada.' : 'Database siap. Belum ada profil jurnal. Silakan buat akun pertama.'
  };
}

function createProfile(spreadsheetIdOrUrl, profileData) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  const existing = getProfile_(ss);
  if (existing) throw new Error('Profil jurnal sudah ada di spreadsheet ini. Silakan login atau ubah profil setelah masuk.');

  const name = clean_(profileData && profileData.clientName);
  const pin = String((profileData && profileData.pin) || '').trim();
  if (!name) throw new Error('Nama/inisial wajib diisi.');
  if (pin.length < 4) throw new Error('PIN minimal 4 karakter.');

  const sheet = ss.getSheetByName(TRJ.SHEETS.CLIENTS);
  const now = now_();
  const obj = {
    clientCode: TRJ.CLIENT_CODE,
    clientName: name,
    pinHash: hash_(pin),
    email: clean_(profileData && profileData.email),
    phone: clean_(profileData && profileData.phone),
    psychologistName: clean_(profileData && profileData.psychologistName),
    notes: clean_(profileData && profileData.notes),
    status: 'active',
    createdAt: now,
    updatedAt: now
  };
  appendObject_(sheet, TRJ.HEADERS.CLIENTS, obj);
  setSetting_(ss, 'HAS_PROFILE', 'yes');
  setSetting_(ss, 'APP_VERSION', TRJ.VERSION);
  writeAudit_(ss, TRJ.CLIENT_CODE, 'CREATE_PROFILE', TRJ.CLIENT_CODE, '', 'Profile created');

  return {
    ok: true,
    message: 'Akun jurnal berhasil dibuat.',
    spreadsheetId: ss.getId(),
    spreadsheetName: ss.getName(),
    profile: safeProfile_(obj),
    dashboard: buildDashboard_(ss, TRJ.CLIENT_CODE)
  };
}

function validateProfile(spreadsheetIdOrUrl, pin) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  const profile = verifyProfile_(ss, pin);
  writeAudit_(ss, TRJ.CLIENT_CODE, 'LOGIN_PROFILE', TRJ.CLIENT_CODE, '', 'Profile login');
  return {
    ok: true,
    spreadsheetId: ss.getId(),
    spreadsheetName: ss.getName(),
    profile: safeProfile_(profile),
    dashboard: buildDashboard_(ss, TRJ.CLIENT_CODE)
  };
}

function updateProfile(spreadsheetIdOrUrl, pin, profileData) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  const verified = verifyProfile_(ss, pin);

  const found = findProfileRow_(ss);
  if (!found) throw new Error('Profil tidak ditemukan.');

  const sheet = ss.getSheetByName(TRJ.SHEETS.CLIENTS);
  const updates = {
    clientName: clean_(profileData && profileData.clientName) || verified.clientName,
    email: clean_(profileData && profileData.email),
    phone: clean_(profileData && profileData.phone),
    psychologistName: clean_(profileData && profileData.psychologistName),
    notes: clean_(profileData && profileData.notes),
    status: 'active',
    updatedAt: now_()
  };

  const newPin = String((profileData && profileData.newPin) || '').trim();
  if (newPin) {
    if (newPin.length < 4) throw new Error('PIN baru minimal 4 karakter.');
    updates.pinHash = hash_(newPin);
  }

  writeObjectToRow_(sheet, found.rowIndex, TRJ.HEADERS.CLIENTS, updates);
  writeAudit_(ss, TRJ.CLIENT_CODE, 'UPDATE_PROFILE', TRJ.CLIENT_CODE, '', newPin ? 'Profile and PIN updated' : 'Profile updated');

  const fresh = getProfile_(ss);
  return {
    ok: true,
    message: newPin ? 'Profil dan PIN berhasil diperbarui.' : 'Profil berhasil diperbarui.',
    profile: safeProfile_(fresh),
    pinChanged: !!newPin
  };
}

function getMonthData(spreadsheetIdOrUrl, pin, year, month) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  verifyProfile_(ss, pin);

  year = Number(year);
  month = Number(month);

  const sheet = ss.getSheetByName(TRJ.SHEETS.ENTRIES);
  const rows = getRows_(sheet);
  const items = [];

  rows.forEach(function(row) {
    if (normalizeCode_(row.clientCode) !== TRJ.CLIENT_CODE) return;
    const d = normalizeDateSoft_(row.date);
    if (!d) return;
    const parts = d.split('-');
    if (Number(parts[0]) === year && Number(parts[1]) === month) {
      items.push(calendarEntry_(row, d));
    }
  });

  return {
    ok: true,
    year: year,
    month: month,
    entries: items,
    dashboard: buildDashboard_(ss, TRJ.CLIENT_CODE)
  };
}

function getEntry(spreadsheetIdOrUrl, pin, date) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  verifyProfile_(ss, pin);
  const d = normalizeDate_(date);
  const found = findEntry_(ss, TRJ.CLIENT_CODE, d);
  return {
    ok: true,
    entry: found ? normalizeEntryForOutput_(found.rowObject) : blankEntry_(TRJ.CLIENT_CODE, d)
  };
}


function verifyEntrySaved(spreadsheetIdOrUrl, pin, date) {
  const ss = getSpreadsheet_(spreadsheetIdOrUrl);
  ensureDatabase_(ss);
  verifyProfile_(ss, pin);
  const d = normalizeDate_(date);
  const found = findEntry_(ss, TRJ.CLIENT_CODE, d);
  if (!found) {
    return { ok: true, saved: false, date: d, entry: null, calendarEntry: null };
  }
  return {
    ok: true,
    saved: true,
    date: d,
    entry: compactEntryForSave_(found.rowObject),
    calendarEntry: calendarEntry_(found.rowObject, d),
    dashboard: buildDashboard_(ss, TRJ.CLIENT_CODE)
  };
}

function saveEntry(payload) {
  const p = payload || {};
  const ss = getSpreadsheet_(p.spreadsheetId);
  ensureDatabase_(ss);
  verifyProfile_(ss, p.pin);

  const date = normalizeDate_(p.date);
  const cleaned = {
    date: date,
    moodManual: clean_(p.moodManual),
    moodIntensity: sanitizeScore_(p.moodIntensity),
    journalText: textCell_(p.journalText),
    triggerText: textCell_(p.triggerText),
    copingText: textCell_(p.copingText),
    gratitudeText: textCell_(p.gratitudeText),
    sleepDuration: sanitizeSleepDuration_(p.sleepDuration),
    sleepQuality: clean_(p.sleepQuality),
    anxietyFelt: yesNo_(p.anxietyFelt),
    anxietyLevel: sanitizeScore_(p.anxietyLevel),
    sadnessFelt: yesNo_(p.sadnessFelt),
    sadnessLevel: sanitizeScore_(p.sadnessLevel),
    angerFelt: yesNo_(p.angerFelt),
    angerLevel: sanitizeScore_(p.angerLevel),
    disappointmentFelt: yesNo_(p.disappointmentFelt),
    disappointmentLevel: sanitizeScore_(p.disappointmentLevel),
    needFeedback: p.needFeedback ? 'yes' : 'no',
    topicToDiscuss: p.needFeedback ? textCell_(p.topicToDiscuss) : ''
  };
  validateJournalPayload_(cleaned);

  const analysis = analyzeJournal_(cleaned);
  const sheet = ss.getSheetByName(TRJ.SHEETS.ENTRIES);
  ensureSheetColumns_(sheet, TRJ.HEADERS.ENTRIES);
  const found = findEntry_(ss, TRJ.CLIENT_CODE, date);
  const now = now_();

  const obj = {
    entryId: found ? found.rowObject.entryId : Utilities.getUuid(),
    clientCode: TRJ.CLIENT_CODE,
    date: date,
    moodManual: cleaned.moodManual,
    moodIntensity: cleaned.moodIntensity,
    moodScore: analysis.score,
    moodAuto: analysis.moodAuto,
    emotionTags: analysis.tags.join(', '),
    journalText: cleaned.journalText,
    triggerText: cleaned.triggerText,
    copingText: cleaned.copingText,
    topicToDiscuss: cleaned.topicToDiscuss,
    gratitudeText: cleaned.gratitudeText,
    sleepQuality: cleaned.sleepQuality,
    anxietyLevel: cleaned.anxietyLevel,
    sleepDuration: cleaned.sleepDuration,
    anxietyFelt: cleaned.anxietyFelt,
    sadnessFelt: cleaned.sadnessFelt,
    sadnessLevel: cleaned.sadnessLevel,
    angerFelt: cleaned.angerFelt,
    angerLevel: cleaned.angerLevel,
    disappointmentFelt: cleaned.disappointmentFelt,
    disappointmentLevel: cleaned.disappointmentLevel,
    riskFlag: analysis.riskFlag ? 'yes' : 'no',
    riskKeywords: analysis.riskKeywords.join(', '),
    needFeedback: cleaned.needFeedback,
    therapistFeedback: found ? clean_(found.rowObject.therapistFeedback) : '',
    createdAt: found ? found.rowObject.createdAt : now,
    updatedAt: now
  };

  if (found) {
    writeObjectToRow_(sheet, found.rowIndex, TRJ.HEADERS.ENTRIES, obj);
  } else {
    appendObject_(sheet, TRJ.HEADERS.ENTRIES, obj);
  }

  writeAudit_(ss, TRJ.CLIENT_CODE, found ? 'UPDATE_ENTRY' : 'CREATE_ENTRY', TRJ.CLIENT_CODE, obj.entryId, date);

  return {
    ok: true,
    message: found ? 'Jurnal tanggal ini berhasil diperbarui.' : 'Jurnal tanggal ini berhasil disimpan.',
    analysis: analysis,
    // Respons dibuat ringkas agar callback JSONP tidak terlalu besar. Isi catatan tetap disimpan penuh di Spreadsheet.
    entry: compactEntryForSave_(obj),
    calendarEntry: calendarEntry_(obj, date),
    dashboard: buildDashboard_(ss, TRJ.CLIENT_CODE)
  };
}

function compactEntryForSave_(obj) {
  const e = normalizeEntryForOutput_(obj);
  ['journalText', 'triggerText', 'copingText', 'gratitudeText', 'topicToDiscuss', 'therapistFeedback'].forEach(function(k) {
    delete e[k];
  });
  return e;
}

function validateJournalPayload_(p) {
  if (!p.date) throw new Error('Tanggal wajib diisi.');
  if (!hasText_(p.sleepDuration)) throw new Error('Durasi tidur wajib diisi.');
  if (!p.sleepQuality) throw new Error('Kualitas tidur wajib dipilih.');
  if (!p.moodManual) throw new Error('Mood hari ini wajib dipilih.');
  if (!hasText_(p.moodIntensity)) throw new Error('Skala mood wajib diisi.');
  if (!hasText_(p.anxietyLevel)) throw new Error('Skala kecemasan wajib diisi.');
  if (!hasText_(p.sadnessLevel)) throw new Error('Skala sedih wajib diisi.');
  if (!hasText_(p.angerLevel)) throw new Error('Skala marah wajib diisi.');
  if (!hasText_(p.disappointmentLevel)) throw new Error('Skala kecewa wajib diisi.');
}

function hasText_(v) {
  return v !== undefined && v !== null && String(v).trim() !== '';
}

function getSpreadsheet_(spreadsheetIdOrUrl) {
  const id = extractSpreadsheetId_(spreadsheetIdOrUrl);
  if (!id) throw new Error('Spreadsheet ID/URL wajib diisi.');
  try {
    return SpreadsheetApp.openById(id);
  } catch (err) {
    throw new Error('Spreadsheet tidak bisa dibuka. Pastikan ID benar dan Apps Script dipasang dari akun yang punya akses spreadsheet. Detail: ' + err.message);
  }
}

function extractSpreadsheetId_(value) {
  const s = String(value || '').trim();
  if (!s) return '';
  const m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
  if (m) return m[1];
  const m2 = s.match(/[?&]id=([a-zA-Z0-9-_]+)/);
  if (m2) return m2[1];
  return s.replace(/[^a-zA-Z0-9-_]/g, '');
}

function ensureDatabase_(ss) {
  ensureSheet_(ss, TRJ.SHEETS.SETTINGS, TRJ.HEADERS.SETTINGS);
  ensureSheet_(ss, TRJ.SHEETS.CLIENTS, TRJ.HEADERS.CLIENTS);
  ensureSheet_(ss, TRJ.SHEETS.ENTRIES, TRJ.HEADERS.ENTRIES);
  ensureSheet_(ss, TRJ.SHEETS.AUDIT, TRJ.HEADERS.AUDIT);
  setSettingIfEmpty_(ss, 'APP_NAME', 'Telering Reflect Journal');
  setSettingIfEmpty_(ss, 'APP_VERSION', TRJ.VERSION);
}

function ensureSheet_(ss, name, headers) {
  let sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    try { sheet.setFrozenRows(1); } catch (e) {}
    try { sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold'); } catch (e) {}
    return sheet;
  }

  const lastCol = Math.max(sheet.getLastColumn(), 1);
  let current = sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(function(h) { return String(h || '').trim(); });
  const hasAnyHeader = current.some(function(h) { return h; });
  if (!hasAnyHeader) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    current = headers.slice();
  }
  headers.forEach(function(h) {
    if (current.indexOf(h) === -1) {
      sheet.getRange(1, current.length + 1).setValue(h);
      current.push(h);
    }
  });
  try { sheet.setFrozenRows(1); } catch (e) {}
  try { sheet.getRange(1, 1, 1, current.length).setFontWeight('bold'); } catch (e) {}
  return sheet;
}

function getRows_(sheet) {
  const range = sheet.getDataRange();
  const values = range.getValues();
  if (values.length < 2) return [];
  const headers = values[0].map(function(h) { return String(h || '').trim(); });
  return values.slice(1).filter(function(row) {
    return row.some(function(v) { return v !== '' && v != null; });
  }).map(function(row) {
    const obj = {};
    headers.forEach(function(h, i) { if (h) obj[h] = row[i]; });
    return obj;
  });
}

function getHeaderMap_(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(function(h) { return String(h || '').trim(); });
  const map = {};
  headers.forEach(function(h, i) { if (h) map[h] = i + 1; });
  return map;
}

function appendObject_(sheet, headers, obj) {
  ensureSheetColumns_(sheet, headers);
  const actualHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(function(h) { return String(h || '').trim(); });
  sheet.appendRow(actualHeaders.map(function(h) { return obj[h] == null ? '' : obj[h]; }));
}

function ensureSheetColumns_(sheet, headers) {
  const lastCol = Math.max(sheet.getLastColumn(), 1);
  const current = sheet.getRange(1, 1, 1, lastCol).getValues()[0].map(function(h) { return String(h || '').trim(); });
  headers.forEach(function(h) {
    if (current.indexOf(h) === -1) {
      sheet.getRange(1, current.length + 1).setValue(h);
      current.push(h);
    }
  });
}

function writeObjectToRow_(sheet, rowIndex, headers, obj) {
  const map = getHeaderMap_(sheet);
  Object.keys(obj).forEach(function(k) {
    if (map[k]) sheet.getRange(rowIndex, map[k]).setValue(obj[k] == null ? '' : obj[k]);
  });
}

function setSettingIfEmpty_(ss, key, value) {
  const sheet = ss.getSheetByName(TRJ.SHEETS.SETTINGS);
  const rows = getRows_(sheet);
  const exists = rows.some(function(r) { return String(r.key) === key; });
  if (!exists) appendObject_(sheet, TRJ.HEADERS.SETTINGS, { key: key, value: value, updatedAt: now_() });
}

function setSetting_(ss, key, value) {
  const sheet = ss.getSheetByName(TRJ.SHEETS.SETTINGS);
  const values = sheet.getDataRange().getValues();
  for (let i = 1; i < values.length; i++) {
    if (String(values[i][0]) === key) {
      sheet.getRange(i + 1, 2).setValue(value);
      sheet.getRange(i + 1, 3).setValue(now_());
      return;
    }
  }
  appendObject_(sheet, TRJ.HEADERS.SETTINGS, { key: key, value: value, updatedAt: now_() });
}

function getProfile_(ss) {
  const found = findProfileRow_(ss);
  return found ? normalizeProfile_(found.rowObject) : null;
}

function findProfileRow_(ss) {
  const sheet = ss.getSheetByName(TRJ.SHEETS.CLIENTS);
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return null;
  const headers = values[0].map(function(h) { return String(h || '').trim(); });
  for (let i = 1; i < values.length; i++) {
    const obj = {};
    headers.forEach(function(h, j) { if (h) obj[h] = values[i][j]; });
    if (normalizeCode_(obj.clientCode) === TRJ.CLIENT_CODE) return { rowIndex: i + 1, rowObject: obj };
  }
  return null;
}

function verifyProfile_(ss, pin) {
  const profile = getProfile_(ss);
  if (!profile) throw new Error('Profil jurnal belum dibuat. Klik Cek/Siapkan Database lalu buat akun pertama.');
  if (String(profile.status || '').toLowerCase() !== 'active') throw new Error('Profil tidak aktif.');
  if (profile.pinHash !== hash_(pin)) throw new Error('PIN salah.');
  return profile;
}

function normalizeProfile_(obj) {
  return {
    clientCode: TRJ.CLIENT_CODE,
    clientName: clean_(obj.clientName),
    pinHash: String(obj.pinHash || ''),
    email: clean_(obj.email),
    phone: clean_(obj.phone),
    psychologistName: clean_(obj.psychologistName),
    notes: clean_(obj.notes),
    status: clean_(obj.status) || 'active',
    createdAt: normalizeMaybeDate_(obj.createdAt),
    updatedAt: normalizeMaybeDate_(obj.updatedAt)
  };
}

function safeProfile_(profile) {
  const p = normalizeProfile_(profile);
  delete p.pinHash;
  return p;
}

function findEntry_(ss, clientCode, date) {
  const code = normalizeCode_(clientCode);
  const d = normalizeDate_(date);
  const sheet = ss.getSheetByName(TRJ.SHEETS.ENTRIES);
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return null;
  const headers = values[0].map(function(h) { return String(h || '').trim(); });
  for (let i = 1; i < values.length; i++) {
    const obj = {};
    headers.forEach(function(h, j) { if (h) obj[h] = values[i][j]; });
    if (normalizeCode_(obj.clientCode) === code && normalizeDateSoft_(obj.date) === d) {
      return { rowIndex: i + 1, rowObject: obj };
    }
  }
  return null;
}

function blankEntry_(clientCode, date) {
  const obj = {};
  TRJ.HEADERS.ENTRIES.forEach(function(h) { obj[h] = ''; });
  obj.clientCode = normalizeCode_(clientCode);
  obj.date = normalizeDate_(date);
  obj.needFeedback = 'no';
  obj.riskFlag = 'no';
  return obj;
}

function normalizeEntryForOutput_(row) {
  const obj = {};
  TRJ.HEADERS.ENTRIES.forEach(function(h) { obj[h] = row[h] == null ? '' : row[h]; });
  obj.date = normalizeDateSoft_(obj.date) || obj.date;
  obj.createdAt = normalizeMaybeDate_(obj.createdAt);
  obj.updatedAt = normalizeMaybeDate_(obj.updatedAt);
  ['journalText', 'triggerText', 'copingText', 'gratitudeText', 'topicToDiscuss', 'therapistFeedback'].forEach(function(k) { obj[k] = displayText_(obj[k]); });
  obj.moodScore = Number(obj.moodScore || 0);
  obj.moodIntensity = Number(obj.moodIntensity || 0);
  obj.anxietyLevel = Number(obj.anxietyLevel || 0);
  obj.sleepDuration = sanitizeSleepDuration_(obj.sleepDuration);
  obj.sadnessLevel = Number(obj.sadnessLevel || 0);
  obj.angerLevel = Number(obj.angerLevel || 0);
  obj.disappointmentLevel = Number(obj.disappointmentLevel || 0);
  obj.anxietyFelt = yesNo_(obj.anxietyFelt);
  obj.sadnessFelt = yesNo_(obj.sadnessFelt);
  obj.angerFelt = yesNo_(obj.angerFelt);
  obj.disappointmentFelt = yesNo_(obj.disappointmentFelt);
  obj.riskFlag = String(obj.riskFlag || '') === 'yes' ? 'yes' : 'no';
  obj.needFeedback = String(obj.needFeedback || '') === 'yes' ? 'yes' : 'no';
  // Rehitung analisis saat dibaca agar entri lama ikut memakai rumus skoring terbaru.
  try {
    const a = analyzeJournal_(obj);
    obj.moodScore = a.score;
    obj.moodAuto = a.moodAuto;
    obj.emotionTags = a.emotionTags;
    obj.analysisNote = a.note;
    obj.riskFlag = a.riskFlag ? 'yes' : 'no';
    obj.riskKeywords = (a.riskKeywords || []).join(', ');
  } catch (err) {}
  return obj;
}

function calendarEntry_(row, normalizedDate) {
  const e = normalizeEntryForOutput_(row);
  const d = normalizedDate || normalizeDateSoft_(e.date);
  return {
    entryId: e.entryId,
    date: d,
    moodManual: e.moodManual,
    moodIntensity: Number(e.moodIntensity || 0),
    moodScore: Number(e.moodScore || 0),
    moodAuto: e.moodAuto,
    emotionTags: e.emotionTags,
    sleepQuality: e.sleepQuality,
    sleepDuration: e.sleepDuration,
    anxietyFelt: e.anxietyFelt,
    anxietyLevel: Number(e.anxietyLevel || 0),
    sadnessFelt: e.sadnessFelt,
    sadnessLevel: Number(e.sadnessLevel || 0),
    angerFelt: e.angerFelt,
    angerLevel: Number(e.angerLevel || 0),
    disappointmentFelt: e.disappointmentFelt,
    disappointmentLevel: Number(e.disappointmentLevel || 0),
    needFeedback: e.needFeedback === 'yes',
    hasFeedback: !!e.therapistFeedback,
    riskFlag: e.riskFlag === 'yes',
    riskKeywords: e.riskKeywords
  };
}

function buildDashboard_(ss, clientCode) {
  const code = normalizeCode_(clientCode);
  const rows = getRows_(ss.getSheetByName(TRJ.SHEETS.ENTRIES))
    .map(function(r) { return normalizeEntryForOutput_(r); })
    .filter(function(r) { return normalizeCode_(r.clientCode) === code && normalizeDateSoft_(r.date); })
    .sort(function(a, b) { return String(b.date).localeCompare(String(a.date)); });

  const last30 = rows.slice(0, 30);
  const total = rows.length;
  const avgMood = last30.length ? round1_(last30.reduce(function(sum, r) { return sum + Number(r.moodScore || 0); }, 0) / last30.length) : 0;
  const riskCount = last30.filter(function(r) { return String(r.riskFlag) === 'yes'; }).length;
  const feedbackNeeded = rows.filter(function(r) { return String(r.needFeedback) === 'yes' && !r.therapistFeedback; }).length;

  const moodCounter = {};
  const triggerCounter = {};
  last30.forEach(function(r) {
    const mood = String(r.moodAuto || r.moodManual || 'Belum terbaca');
    moodCounter[mood] = (moodCounter[mood] || 0) + 1;
    String(r.triggerText || '').split(/[,\.\n;]/).map(function(x) { return x.trim(); }).filter(Boolean).forEach(function(t) {
      const key = t.length > 30 ? t.slice(0, 30) + '…' : t;
      triggerCounter[key] = (triggerCounter[key] || 0) + 1;
    });
  });

  return {
    totalEntries: total,
    last30Entries: last30.length,
    avgMoodLast30: avgMood,
    dominantMoodLast30: topKey_(moodCounter) || '-',
    topTriggerLast30: topKey_(triggerCounter) || '-',
    riskCountLast30: riskCount,
    feedbackNeeded: feedbackNeeded,
    latestDate: rows[0] ? rows[0].date : '-'
  };
}

function analyzeJournal_(payload) {
  const text = [payload.journalText, payload.triggerText, payload.copingText, payload.topicToDiscuss, payload.gratitudeText, payload.moodManual, payload.sleepQuality]
    .map(clean_).join(' ').toLowerCase();
  const moodManual = String(payload.moodManual || '').toLowerCase();
  const intensity = Number(payload.moodIntensity || 0);
  const anxietyRaw = Number(payload.anxietyLevel || 0);
  const sadnessRaw = Number(payload.sadnessLevel || 0);
  const angerRaw = Number(payload.angerLevel || 0);
  const disappointmentRaw = Number(payload.disappointmentLevel || 0);
  // Skor emosi negatif hanya dihitung sebagai beban jika pengguna memilih "Ya".
  // Jika memilih "Tidak", angka skala tetap disimpan sebagai catatan, tetapi tidak menurunkan skor mood.
  const anxiety = yesNo_(payload.anxietyFelt) === 'yes' ? anxietyRaw : 0;
  const sadness = yesNo_(payload.sadnessFelt) === 'yes' ? sadnessRaw : 0;
  const anger = yesNo_(payload.angerFelt) === 'yes' ? angerRaw : 0;
  const disappointment = yesNo_(payload.disappointmentFelt) === 'yes' ? disappointmentRaw : 0;
  const sleepQuality = String(payload.sleepQuality || '').toLowerCase();

  const dict = {
    positive: ['senang', 'bahagia', 'lega', 'bersyukur', 'tenang', 'bangga', 'semangat', 'berhasil', 'baik', 'damai', 'optimis', 'terbantu', 'membaik', 'produktif'],
    sad: ['sedih', 'kosong', 'menangis', 'putus asa', 'kecewa', 'murung', 'sendiri', 'hampa', 'terpuruk'],
    anxious: ['cemas', 'khawatir', 'panik', 'takut', 'gelisah', 'overthinking', 'deg-degan', 'bingung', 'was-was'],
    angry: ['marah', 'kesal', 'jengkel', 'benci', 'emosi', 'frustrasi', 'tersinggung'],
    tired: ['capek', 'lelah', 'burnout', 'letih', 'tidak kuat', 'kewalahan', 'sulit tidur', 'kurang tidur']
  };
  const riskWords = ['bunuh diri','ingin bunuh diri','mau bunuh diri','pengen bunuh diri','kepikiran bunuh diri','mengakhiri hidup','ingin mengakhiri hidup','mau mengakhiri hidup','akhiri hidup','menyudahi hidup','tidak mau hidup','tidak ingin hidup','nggak mau hidup','gak mau hidup','ingin mati','mau mati','pengen mati','lebih baik mati','mati saja','mending mati','ingin hilang','ingin lenyap','ingin menghilang','ingin menghilang selamanya','tidur selamanya','ingin tidur selamanya','capek hidup','lelah hidup','tidak kuat hidup','nggak kuat hidup','gak kuat hidup','tidak sanggup hidup','hidup tidak ada artinya','hidup tidak berarti','tidak ada alasan hidup','tidak ada harapan','putus asa','menyakiti diri','menyakiti diri sendiri','melukai diri','melukai diri sendiri','melukai tubuh','self harm','self-harm','hurt myself','hurting myself','harm myself','harming myself','cut myself','cutting myself','end my life','ending my life','take my life','taking my life','kill myself','killing myself','suicide','suicidal','suicidal thoughts','suicidal ideation','i want to die','want to die','wanna die','i wish i was dead','wish i was dead','better off dead','i do not want to live',"i don't want to live",'do not want to live',"don't want to live",'no reason to live','nothing to live for','life is meaningless','life has no meaning','i want to disappear','want to disappear','disappear forever','sleep forever','hopeless','i am hopeless','cannot go on',"can't go on",'tired of living','done with life','end it all','i want to end it all'];

  const counts = {};
  Object.keys(dict).forEach(function(k) { counts[k] = countKeywords_(text, dict[k]); });
  const riskKeywords = riskWords.filter(function(w) { return text.indexOf(w) >= 0; });

  const manualPositive = ['senang', 'tenang', 'bersyukur'];
  const manualNeutral = ['netral', 'campur'];
  const manualNegative = ['cemas', 'sedih', 'marah', 'takut', 'lelah'];

  let score = 0;

  // Skala mood 0–10 diperlakukan sebagai kesejahteraan umum:
  // 0 sangat berat, 5 netral/campur, 10 sangat baik.
  score += (intensity - 5) * 0.55;

  if (manualPositive.indexOf(moodManual) >= 0) score += 0.75;
  if (moodManual === 'campur') score -= 0.15;
  if (manualNegative.indexOf(moodManual) >= 0) score -= 0.95;
  if (manualNeutral.indexOf(moodManual) >= 0) score += 0;

  // Beban emosi negatif hanya dari emosi yang benar-benar dipilih "Ya".
  score -= anxiety * 0.16;
  score -= sadness * 0.18;
  score -= anger * 0.20;
  score -= disappointment * 0.14;

  if (yesNo_(payload.anxietyFelt) === 'yes' && anxiety === 0) score -= 0.2;
  if (yesNo_(payload.sadnessFelt) === 'yes' && sadness === 0) score -= 0.2;
  if (yesNo_(payload.angerFelt) === 'yes' && anger === 0) score -= 0.2;
  if (yesNo_(payload.disappointmentFelt) === 'yes' && disappointment === 0) score -= 0.15;

  if (sleepQuality === 'baik') score += 0.25;
  if (sleepQuality === 'kurang') score -= 0.35;
  if (sleepQuality === 'sulit tidur') score -= 0.7;
  if (payload.needFeedback) score -= 0.25;

  if (sadness >= 6) counts.sad += 1;
  if (anxiety >= 6) counts.anxious += 1;
  if (anger >= 6) counts.angry += 1;
  if (disappointment >= 6) counts.sad += 1;

  score += counts.positive * 0.30;
  score -= counts.sad * 0.35;
  score -= counts.anxious * 0.35;
  score -= counts.angry * 0.35;
  score -= counts.tired * 0.25;

  if (riskKeywords.length) score = -5;
  score = Math.max(-5, Math.min(5, round1_(score)));

  const maxNeg = Math.max(anxiety, sadness, anger, disappointment);
  const severeEmotion = maxNeg >= 8 || sadness >= 8 || anger >= 8 || anxiety >= 8;
  const negativeManualLowMood = manualNegative.indexOf(moodManual) >= 0 && intensity <= 3;

  let moodAuto = 'Netral/Campur';
  if (riskKeywords.length) moodAuto = 'Risk keyword';
  else if (score <= -1.8 || severeEmotion || negativeManualLowMood) moodAuto = 'Berat';
  else if (score >= 1.1 && maxNeg <= 4 && manualNegative.indexOf(moodManual) < 0) moodAuto = 'Positif';
  else moodAuto = 'Netral/Campur';

  const tags = [];
  if (counts.positive) tags.push('positive');
  if (counts.sad) tags.push('sad');
  if (counts.anxious) tags.push('anxious');
  if (counts.angry) tags.push('angry');
  if (counts.tired) tags.push('tired');
  if (!tags.length) tags.push('neutral');

  let note = 'Analisis mood otomatis bersifat estimasi reflektif, bukan diagnosis klinis.';
  if (riskKeywords.length) note = 'Kategori Risk keyword karena ada kata/kalimat yang perlu perhatian lebih. Segera hubungi profesional atau kontak darurat jika ada risiko keselamatan.';
  else if (moodAuto === 'Berat') note = 'Kategori Berat karena skor mood rendah dan/atau emosi negatif tinggi. Hari ini sebaiknya diperlakukan sebagai hari yang perlu perhatian, pemulihan, dan bila perlu pendampingan.';
  else if (moodAuto === 'Positif') note = 'Kategori Positif karena mood dan skala emosi menunjukkan kondisi relatif baik. Pertahankan aktivitas dan coping yang membantu.';
  else note = 'Kategori Netral/Campur karena indikator positif dan berat relatif seimbang atau belum cukup kuat mengarah ke satu kategori.';

  return {
    score: score,
    moodAuto: moodAuto,
    tags: tags,
    riskFlag: riskKeywords.length > 0,
    riskKeywords: riskKeywords,
    note: note
  };
}

function countKeywords_(text, list) {
  return list.reduce(function(sum, w) { return sum + (text.indexOf(w) >= 0 ? 1 : 0); }, 0);
}

function writeAudit_(ss, actor, action, clientCode, entryId, detail) {
  const sheet = ss.getSheetByName(TRJ.SHEETS.AUDIT);
  appendObject_(sheet, TRJ.HEADERS.AUDIT, { timestamp: now_(), actor: actor, action: action, clientCode: clientCode, entryId: entryId, detail: detail });
}

function normalizeCode_(v) {
  return String(v || '').trim().toUpperCase().replace(/\s+/g, '-');
}

function normalizeDate_(v) {
  const d = normalizeDateSoft_(v);
  if (!d) throw new Error('Format tanggal tidak valid. Gunakan YYYY-MM-DD.');
  return d;
}

function normalizeDateSoft_(v) {
  if (v instanceof Date && !isNaN(v.getTime())) return Utilities.formatDate(v, Session.getScriptTimeZone(), 'yyyy-MM-dd');
  const s = String(v || '').trim();
  if (!s) return '';
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
  const d = new Date(s);
  if (isNaN(d.getTime())) return '';
  return Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function normalizeMaybeDate_(v) {
  if (v instanceof Date && !isNaN(v.getTime())) return Utilities.formatDate(v, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  return String(v || '');
}

function clean_(v) {
  return String(v == null ? '' : v).trim();
}

function textCell_(v) {
  let s = String(v == null ? '' : v).replace(/\x00/g, '').trim();
  if (s.length > TRJ.TEXT_CELL_LIMIT) s = s.slice(0, TRJ.TEXT_CELL_LIMIT);
  // Paksa semua isi catatan sebagai teks biasa di Google Sheets.
  // Jika diawali = + - @ atau pola apa pun, tetap disimpan sebagai teks, bukan formula.
  if (s) s = "'" + s;
  return s;
}

function displayText_(v) {
  let s = String(v == null ? '' : v);
  if (s.charAt(0) === "'") s = s.slice(1);
  return s;
}

function sanitizeScore_(v) {
  const s = String(v == null ? '' : v).replace(/[^0-9]/g, '');
  if (s === '') return '';
  let n = parseInt(s, 10);
  if (!isFinite(n)) return '';
  if (n < 0) n = 0;
  if (n > 10) n = 10;
  return String(n);
}

function sanitizeSleepDuration_(v) {
  const s = String(v == null ? '' : v).replace(/,/g, '.').replace(/[^0-9.]/g, '');
  if (!s) return '';
  const parts = s.split('.');
  const normalized = parts.shift() + (parts.length ? '.' + parts.join('') : '');
  const n = parseFloat(normalized);
  if (!isFinite(n)) return '';
  const clamped = Math.min(24, Math.max(0, n));
  return String(clamped).replace(/\.0+$/, '').replace(/(\.\d*?)0+$/, '$1');
}

function yesNo_(v) {
  const s = String(v == null ? '' : v).trim().toLowerCase();
  return (s === 'yes' || s === 'ya' || s === 'true' || s === '1') ? 'yes' : 'no';
}

function now_() {
  return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
}

function round1_(n) {
  return Math.round(Number(n || 0) * 10) / 10;
}

function topKey_(obj) {
  let best = '', max = -Infinity;
  Object.keys(obj || {}).forEach(function(k) {
    if (obj[k] > max) { max = obj[k]; best = k; }
  });
  return best;
}

function capitalize_(s) {
  s = String(s || '');
  return s ? s.charAt(0).toUpperCase() + s.slice(1) : s;
}

function hash_(value) {
  const raw = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(value || ''), Utilities.Charset.UTF_8);
  return raw.map(function(b) {
    const v = (b < 0 ? b + 256 : b).toString(16);
    return v.length === 1 ? '0' + v : v;
  }).join('');
}
Telering Journaling

Hubungkan Database

Akun Jurnal

Buat Akun Pertama

Masuk ke Jurnal

Code.gs untuk Apps Script

Ubah Profil / Password

Jurnal Harian

Tanggal, Tidur, dan Mood

Emosi yang Dirasakan

Catatan Harian

Sesi Psikolog
