Setup SITEGUH

/**
 * SITEGUH - Sistem Informasi Terpadu Guru Hebat
 * Backend REST API (Google Apps Script + Google Spreadsheet)
 *
 * CARA PAKAI
 * 1. Buat Google Spreadsheet baru.
 * 2. Menu Extensions > Apps Script, hapus isinya, tempel seluruh kode ini.
 * 3. Deploy > New deployment > Type: Web app
 *    - Execute as: Me
 *    - Who has access: Anyone
 * 4. Salin URL Web App (/exec) lalu tempel di halaman Setup aplikasi SITEGUH.
 * 5. Akun admin awal: username "admin" password "admin123" (WAJIB diganti).
 */

var SS = SpreadsheetApp.getActiveSpreadsheet();
var SALT = 'siteguh-v1-salt';
var FOLDER_NAME = 'SITEGUH Uploads';

var SHEETS = {
  Settings: ['key', 'value'],
  Users: ['id', 'nama', 'username', 'password', 'role', 'nip', 'mapel', 'telepon', 'foto'],
  Classes: ['id', 'nama', 'waliKelas', 'tingkat'],
  Students: ['nis', 'nama', 'idKelas', 'jenisKelamin'],
  Schedules: ['id', 'username', 'hari', 'jamKe', 'jamMulai', 'jamSelesai', 'kelas', 'mapel'],
  Attendance: ['id', 'waktu', 'username', 'nama', 'jenis', 'lat', 'long', 'jarak', 'status', 'foto'],
  Journals: ['id', 'waktu', 'username', 'nama', 'jamKe', 'kelas', 'mapel', 'materi', 'catatan', 'bukti'],
  Grades: ['id', 'username', 'nis', 'namaSiswa', 'kelas', 'mapel', 'jenis', 'nilai', 'tanggal']
};

var DEFAULT_SETTINGS = {
  namaSekolah: 'SITEGUH',
  logo: '',
  alamat: '',
  lat: '0',
  long: '0',
  radius: '50',
  jamMasuk: '07:00',
  jamPulang: '15:00'
};

/* ============ ENTRY POINTS ============ */

function doGet(e) {
  var action = (e && e.parameter && e.parameter.action) || 'ping';
  if (action === 'ping') return json({ ok: true, data: { service: 'SITEGUH API', version: '1.0' } });
  return handle(action, {}, null);
}

function doPost(e) {
  var body = {};
  try {
    body = JSON.parse(e.postData.contents);
  } catch (err) {
    return json({ ok: false, error: 'Body request tidak valid.' });
  }
  return handle(body.action, body.payload || {}, body.token || null);
}

function doOptions() {
  return ContentService.createTextOutput('').setMimeType(ContentService.MimeType.TEXT);
}

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

/* ============ ROUTER ============ */

function handle(action, p, token) {
  try {
    var lock = LockService.getScriptLock();
    lock.waitLock(20000);
    try {
      var out = route(action, p, token);
      return json({ ok: true, data: out });
    } finally {
      lock.releaseLock();
    }
  } catch (err) {
    return json({ ok: false, error: String(err && err.message ? err.message : err) });
  }
}

function route(action, p, token) {
  switch (action) {
    case 'ping': return { service: 'SITEGUH API', version: '1.0', initialized: hasSheet('Users') };
    case 'init': return initDatabase();
    case 'login': return login(p);
    case 'session': return auth(token);

    case 'getSettings': return getSettings();
    case 'saveSettings': requireRole(token, 'admin'); return saveSettings(p);

    case 'changePassword': return changePassword(auth(token), p);
    case 'me': return auth(token);

    case 'listUsers': requireRole(token, 'admin'); return readAll('Users').map(stripPassword);
    case 'saveUser': requireRole(token, 'admin'); return saveUser(p);
    case 'deleteUser': requireRole(token, 'admin'); return removeRow('Users', 'id', p.id);

    case 'listClasses': auth(token); return readAll('Classes');
    case 'saveClass': requireRole(token, 'admin'); return upsert('Classes', 'id', p, true);
    case 'deleteClass': requireRole(token, 'admin'); return removeRow('Classes', 'id', p.id);

    case 'listStudents': auth(token); return readAll('Students');
    case 'saveStudent': requireRole(token, 'admin'); return upsert('Students', 'nis', p, false);
    case 'deleteStudent': requireRole(token, 'admin'); return removeRow('Students', 'nis', p.nis);
    case 'importStudents': requireRole(token, 'admin'); return importRows('Students', 'nis', p.rows || []);

    case 'listSchedules': auth(token); return readAll('Schedules').map(function (r) {
      r.jamMulai = toHHmm(r.jamMulai, '');
      r.jamSelesai = toHHmm(r.jamSelesai, '');
      return r;
    });
    case 'saveSchedule': requireRole(token, 'admin');
      p.jamMulai = "'" + toHHmm(p.jamMulai, '');
      p.jamSelesai = "'" + toHHmm(p.jamSelesai, '');
      return upsert('Schedules', 'id', p, true);
    case 'deleteSchedule': requireRole(token, 'admin'); return removeRow('Schedules', 'id', p.id);

    case 'checkAttendance': return attend(auth(token), p);
    case 'myAttendance': return myAttendance(auth(token), p);
    case 'listAttendance': requireRole(token, 'admin'); return filterByDate(readAll('Attendance'), p, 'waktu');

    case 'saveJournal': return saveJournal(auth(token), p);
    case 'myJournals': return byUser(readAll('Journals'), auth(token).username);
    case 'listJournals': requireRole(token, 'admin'); return filterByDate(readAll('Journals'), p, 'waktu');

    case 'saveGrades': return saveGrades(auth(token), p);
    case 'myGrades': return byUser(readAll('Grades'), auth(token).username);
    case 'listGrades': requireRole(token, 'admin'); return readAll('Grades');

    case 'dashboard': requireRole(token, 'admin'); return dashboard();
    default: throw new Error('Action tidak dikenal: ' + action);
  }
}

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

function hasSheet(name) {
  return !!SS.getSheetByName(name);
}

function sheet(name) {
  var sh = SS.getSheetByName(name);
  if (!sh) throw new Error('Sheet "' + name + '" belum ada. Jalankan Inisialisasi Database.');
  return sh;
}

function initDatabase() {
  Object.keys(SHEETS).forEach(function (name) {
    var sh = SS.getSheetByName(name);
    if (!sh) sh = SS.insertSheet(name);
    var headers = SHEETS[name];
    var current = sh.getLastColumn() > 0 ? sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0] : [];
    if (current.join('|') !== headers.join('|')) {
      sh.getRange(1, 1, 1, headers.length).setValues([headers]);
      sh.setFrozenRows(1);
      sh.getRange(1, 1, 1, headers.length).setFontWeight('bold');
    }
  });

  var st = sheet('Settings');
  var existing = {};
  readAll('Settings').forEach(function (r) { existing[r.key] = true; });
  Object.keys(DEFAULT_SETTINGS).forEach(function (k) {
    if (!existing[k]) st.appendRow([k, DEFAULT_SETTINGS[k]]);
  });

  var users = readAll('Users');
  var created = false;
  if (users.length === 0) {
    sheet('Users').appendRow([
      'U' + Date.now(), 'Administrator', 'admin', hashPassword('admin123'), 'admin', '', '', '', ''
    ]);
    created = true;
  }
  return { sheets: Object.keys(SHEETS), adminCreated: created };
}

function readAll(name) {
  var sh = sheet(name);
  var last = sh.getLastRow();
  if (last < 2) return [];
  var headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
  var values = sh.getRange(2, 1, last - 1, sh.getLastColumn()).getValues();
  return values.map(function (row) {
    var obj = {};
    headers.forEach(function (h, i) { obj[h] = row[i]; });
    return obj;
  }).filter(function (o) { return String(o[headers[0]]).length > 0; });
}

function upsert(name, key, payload, autoId) {
  var sh = sheet(name);
  var headers = SHEETS[name];
  if (autoId && !payload[key]) payload[key] = name.charAt(0) + Date.now();
  var rows = readAll(name);
  var index = -1;
  for (var i = 0; i < rows.length; i++) {
    if (String(rows[i][key]) === String(payload[key])) { index = i; break; }
  }
  var record = headers.map(function (h) {
    return payload[h] !== undefined && payload[h] !== null ? payload[h] : (index >= 0 ? rows[index][h] : '');
  });
  if (index >= 0) sh.getRange(index + 2, 1, 1, headers.length).setValues([record]);
  else sh.appendRow(record);
  var out = {};
  headers.forEach(function (h, i) { out[h] = record[i]; });
  return out;
}

function importRows(name, key, rows) {
  rows.forEach(function (r) { upsert(name, key, r, false); });
  return { imported: rows.length };
}

function removeRow(name, key, value) {
  var sh = sheet(name);
  var rows = readAll(name);
  for (var i = 0; i < rows.length; i++) {
    if (String(rows[i][key]) === String(value)) {
      sh.deleteRow(i + 2);
      return { deleted: true };
    }
  }
  throw new Error('Data tidak ditemukan.');
}

/* ============ AUTH ============ */

function hashPassword(pwd) {
  var raw = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    SALT + String(pwd)
  );
  return raw.map(function (b) {
    var v = (b < 0 ? b + 256 : b).toString(16);
    return v.length === 1 ? '0' + v : v;
  }).join('');
}

function makeToken(user) {
  var exp = Date.now() + 1000 * 60 * 60 * 12;
  var payload = user.username + '|' + exp;
  return Utilities.base64EncodeWebSafe(payload + '|' + hashPassword(payload));
}

function auth(token) {
  if (!token) throw new Error('Sesi tidak ditemukan. Silakan login kembali.');
  var decoded;
  try {
    decoded = Utilities.newBlob(Utilities.base64DecodeWebSafe(token)).getDataAsString();
  } catch (e) {
    throw new Error('Sesi tidak valid.');
  }
  var parts = decoded.split('|');
  if (parts.length !== 3) throw new Error('Sesi tidak valid.');
  if (hashPassword(parts[0] + '|' + parts[1]) !== parts[2]) throw new Error('Sesi tidak valid.');
  if (Number(parts[1]) < Date.now()) throw new Error('Sesi berakhir. Silakan login kembali.');
  var user = findUser(parts[0]);
  if (!user) throw new Error('Akun tidak ditemukan.');
  return stripPassword(user);
}

function requireRole(token, role) {
  var user = auth(token);
  if (String(user.role) !== role) throw new Error('Akses ditolak. Khusus ' + role + '.');
  return user;
}

function findUser(identifier) {
  var key = String(identifier || '').trim().toLowerCase();
  if (!key) return null;
  var rows = readAll('Users');
  var i;
  for (i = 0; i < rows.length; i++) {
    if (String(rows[i].username).trim().toLowerCase() === key) return rows[i];
  }
  // fallback: login memakai NIP
  for (i = 0; i < rows.length; i++) {
    var nip = String(rows[i].nip || '').trim().toLowerCase();
    if (nip && nip === key) return rows[i];
  }
  return null;
}

function stripPassword(u) {
  return {
    id: u.id, nama: u.nama, username: u.username, role: u.role,
    nip: u.nip, mapel: u.mapel, telepon: u.telepon, foto: u.foto
  };
}

function login(p) {
  if (!hasSheet('Users')) initDatabase();
  var user = findUser(p.username || '');
  if (!user) throw new Error('Username tidak terdaftar.');
  if (String(user.password) !== hashPassword(p.password || '')) throw new Error('Password salah.');
  return { token: makeToken(user), user: stripPassword(user), exp: Date.now() + 1000 * 60 * 60 * 12 };
}

function saveUser(p) {
  var data = {
    id: p.id || 'U' + Date.now(),
    nama: p.nama, username: p.username, role: p.role || 'guru',
    nip: p.nip || '', mapel: p.mapel || '', telepon: p.telepon || '', foto: p.foto || ''
  };
  if (p.password) data.password = hashPassword(p.password);
  var saved = upsert('Users', 'id', data, false);
  return stripPassword(saved);
}

function changePassword(user, p) {
  var full = findUser(user.username);
  if (String(full.password) !== hashPassword(p.oldPassword || '')) throw new Error('Password lama salah.');
  if (!p.newPassword || String(p.newPassword).length < 6) throw new Error('Password baru minimal 6 karakter.');
  upsert('Users', 'id', { id: full.id, password: hashPassword(p.newPassword) }, false);
  return { updated: true };
}

/* ============ SETTINGS ============ */

/** Ubah nilai jam (Date dari Sheets atau teks) menjadi "HH:mm". */
function toHHmm(value, fallback) {
  if (value === null || value === undefined || value === '') return fallback || '';
  if (Object.prototype.toString.call(value) === '[object Date]') {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'HH:mm');
  }
  var s = String(value).trim();
  var m = /^(\d{1,2}):(\d{2})/.exec(s);
  if (m) return ('0' + m[1]).slice(-2) + ':' + m[2];
  var d = new Date(s);
  if (!isNaN(d.getTime())) return Utilities.formatDate(d, Session.getScriptTimeZone(), 'HH:mm');
  return fallback || s;
}

function getSettings() {
  if (!hasSheet('Settings')) initDatabase();
  var out = {};
  Object.keys(DEFAULT_SETTINGS).forEach(function (k) { out[k] = DEFAULT_SETTINGS[k]; });
  readAll('Settings').forEach(function (r) { out[r.key] = r.value; });
  out.lat = Number(out.lat) || 0;
  out.long = Number(out.long) || 0;
  out.radius = Number(out.radius) || 50;
  out.jamMasuk = toHHmm(out.jamMasuk, DEFAULT_SETTINGS.jamMasuk);
  out.jamPulang = toHHmm(out.jamPulang, DEFAULT_SETTINGS.jamPulang);
  return out;
}

function saveSettings(p) {
  var sh = sheet('Settings');
  var rows = readAll('Settings');
  Object.keys(p).forEach(function (key) {
    var value = p[key];
    var isTime = key === 'jamMasuk' || key === 'jamPulang';
    if (isTime) value = toHHmm(value, '');
    var found = -1;
    for (var i = 0; i < rows.length; i++) { if (rows[i].key === key) { found = i; break; } }
    if (found >= 0) {
      var cell = sh.getRange(found + 2, 2);
      if (isTime) cell.setNumberFormat('@');
      cell.setValue(value);
    } else {
      sh.appendRow([key, '']);
      var newCell = sh.getRange(sh.getLastRow(), 2);
      if (isTime) newCell.setNumberFormat('@');
      newCell.setValue(value);
      rows.push({ key: key, value: value });
    }
  });
  return getSettings();
}

/* ============ FILE UPLOAD ============ */

function getFolder() {
  var it = DriveApp.getFoldersByName(FOLDER_NAME);
  if (it.hasNext()) return it.next();
  return DriveApp.createFolder(FOLDER_NAME);
}

function saveBase64(dataUrl, prefix) {
  if (!dataUrl || String(dataUrl).indexOf('data:') !== 0) return dataUrl || '';
  var parts = String(dataUrl).split(',');
  var meta = parts[0];
  var contentType = meta.substring(meta.indexOf(':') + 1, meta.indexOf(';'));
  var ext = contentType.split('/')[1] || 'jpg';
  var blob = Utilities.newBlob(Utilities.base64Decode(parts[1]), contentType, prefix + '-' + Date.now() + '.' + ext);
  var file = getFolder().createFile(blob);
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  return 'https://drive.google.com/thumbnail?id=' + file.getId() + '&sz=w1000';
}

/* ============ ABSENSI ============ */

function attend(user, p) {
  var settings = getSettings();
  var jenis = p.jenis === 'Pulang' ? 'Pulang' : 'Masuk';
  var now = new Date();
  var today = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  var existing = readAll('Attendance').filter(function (r) {
    return String(r.username) === user.username &&
      Utilities.formatDate(new Date(r.waktu), Session.getScriptTimeZone(), 'yyyy-MM-dd') === today &&
      String(r.jenis) === jenis;
  });
  if (existing.length > 0) throw new Error('Anda sudah melakukan absen ' + jenis + ' hari ini.');

  var jarak = Number(p.jarak || 0);
  if (settings.radius > 0 && jarak > settings.radius) {
    throw new Error('Anda berada ' + jarak + ' m dari sekolah (maks ' + settings.radius + ' m).');
  }

  var jam = Utilities.formatDate(now, Session.getScriptTimeZone(), 'HH:mm');
  var status = 'Tepat Waktu';
  var jamMasuk = toHHmm(settings.jamMasuk, '07:00');
  var jamPulang = toHHmm(settings.jamPulang, '15:00');
  if (jenis === 'Masuk' && jam > jamMasuk) status = 'Terlambat';
  if (jenis === 'Pulang' && jam < jamPulang) status = 'Pulang Awal';

  var foto = saveBase64(p.foto, 'selfie-' + user.username);
  var record = {
    id: 'A' + Date.now(),
    waktu: now.toISOString(),
    username: user.username,
    nama: user.nama,
    jenis: jenis,
    lat: p.lat, long: p.long, jarak: jarak,
    status: status,
    foto: foto
  };
  upsert('Attendance', 'id', record, false);
  return record;
}

function myAttendance(user, p) {
  var rows = byUser(readAll('Attendance'), user.username);
  if (p && p.date) {
    rows = rows.filter(function (r) {
      return Utilities.formatDate(new Date(r.waktu), Session.getScriptTimeZone(), 'yyyy-MM-dd') === p.date;
    });
  }
  return rows;
}

function byUser(rows, username) {
  return rows.filter(function (r) { return String(r.username) === String(username); })
    .sort(function (a, b) { return new Date(b.waktu || b.tanggal) - new Date(a.waktu || a.tanggal); });
}

function filterByDate(rows, p, field) {
  var from = p && p.from ? new Date(p.from + 'T00:00:00') : null;
  var to = p && p.to ? new Date(p.to + 'T23:59:59') : null;
  return rows.filter(function (r) {
    var d = new Date(r[field]);
    if (from && d < from) return false;
    if (to && d > to) return false;
    if (p && p.username && String(r.username) !== String(p.username)) return false;
    return true;
  }).sort(function (a, b) { return new Date(b[field]) - new Date(a[field]); });
}

/* ============ JURNAL & NILAI ============ */

function saveJournal(user, p) {
  var record = {
    id: 'J' + Date.now(),
    waktu: new Date().toISOString(),
    username: user.username,
    nama: user.nama,
    jamKe: p.jamKe || '', kelas: p.kelas || '', mapel: p.mapel || '',
    materi: p.materi || '', catatan: p.catatan || '',
    bukti: saveBase64(p.bukti, 'bukti-' + user.username)
  };
  upsert('Journals', 'id', record, false);
  return record;
}

function saveGrades(user, p) {
  var list = p.items || [];
  var saved = [];
  list.forEach(function (item) {
    var record = {
      id: 'G' + Date.now() + '-' + item.nis,
      username: user.username,
      nis: item.nis, namaSiswa: item.namaSiswa || '',
      kelas: p.kelas || '', mapel: p.mapel || '', jenis: p.jenis || 'Harian',
      nilai: item.nilai, tanggal: new Date().toISOString()
    };
    upsert('Grades', 'id', record, false);
    saved.push(record);
  });
  return { saved: saved.length };
}

/* ============ DASHBOARD ============ */

function dashboard() {
  var tz = Session.getScriptTimeZone();
  var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  var att = readAll('Attendance');
  var users = readAll('Users').filter(function (u) { return String(u.role) === 'guru'; });
  var todayIn = att.filter(function (r) {
    return Utilities.formatDate(new Date(r.waktu), tz, 'yyyy-MM-dd') === today && r.jenis === 'Masuk';
  });
  var hadirUsernames = {};
  todayIn.forEach(function (r) { hadirUsernames[r.username] = true; });

  var trend = [];
  for (var i = 6; i >= 0; i--) {
    var d = new Date();
    d.setDate(d.getDate() - i);
    var key = Utilities.formatDate(d, tz, 'yyyy-MM-dd');
    var count = att.filter(function (r) {
      return Utilities.formatDate(new Date(r.waktu), tz, 'yyyy-MM-dd') === key && r.jenis === 'Masuk';
    }).length;
    trend.push({ tanggal: key, hadir: count });
  }

  var journalsToday = readAll('Journals').filter(function (r) {
    return Utilities.formatDate(new Date(r.waktu), tz, 'yyyy-MM-dd') === today;
  });

  return {
    totalGuru: users.length,
    hadir: todayIn.length,
    terlambat: todayIn.filter(function (r) { return r.status === 'Terlambat'; }).length,
    belumAbsen: users.filter(function (u) { return !hadirUsernames[u.username]; })
      .map(function (u) { return { nama: u.nama, username: u.username }; }),
    jurnalHariIni: journalsToday.length,
    totalKelas: readAll('Classes').length,
    totalSiswa: readAll('Students').length,
    trend: trend
  };
}