/**
 * GURU MADRUSPA - FULL STABLE VERSION
 * Developer: Parisya Shabiyyah Thafana
 */

const SS = SpreadsheetApp.getActiveSpreadsheet();

function doGet() {
  return HtmlService.createTemplateFromFile('Index')
    .evaluate()
    .setTitle('Guru Madruspa')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// --- AUTHENTICATION ---
function processLogin(username, password) {
  const sheet = SS.getSheetByName('Users');
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][0].toString() === username && data[i][1].toString() === password) {
      return { 
        status: "success", 
        name: data[i][2], 
        role: data[i][3],
        username: data[i][0]
      };
    }
  }
  return { status: "error", message: "Username atau password salah!" };
}

// --- DATA UTILITY ---
function getDataFromSheet(sheetName) {
  const sheet = SS.getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) return [];
  const data = sheet.getDataRange().getDisplayValues();
  const headers = data.shift();
  return data.map(row => {
    let obj = {};
    headers.forEach((header, i) => obj[header] = row[i]);
    return obj;
  });
}

// --- MANAJEMEN SISWA ---
function saveOrUpdateStudent(student) {
  const sheet = SS.getSheetByName('Students');
  const data = sheet.getDataRange().getValues();
  let foundRow = -1;
  for (let i = 1; i < data.length; i++) {
    if (data[i][0].toString() === student.nis.toString()) { foundRow = i + 1; break; }
  }
  const studentData = [student.nis, student.name, student.class, student.phone];
  if (foundRow !== -1) {
    sheet.getRange(foundRow, 1, 1, 4).setValues([studentData]);
    return "Data Siswa diperbarui!";
  } else {
    sheet.appendRow(studentData);
    return "Siswa baru ditambahkan!";
  }
}

function deleteStudent(nis) {
  const sheet = SS.getSheetByName('Students');
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][0].toString() === nis.toString()) { sheet.deleteRow(i + 1); return "Data dihapus."; }
  }
  return "Data tidak ditemukan.";
}

// --- MANAJEMEN GURU ---
function getTeachers() {
  const data = SS.getSheetByName('Users').getDataRange().getValues();
  data.shift();
  return data.map((row, index) => ({ 
    row: index + 2, 
    username: row[0], 
    password: row[1], 
    name: row[2], 
    role: row[3] 
  })).filter(u => u.role === "Guru");
}

function saveOrUpdateTeacher(t) {
  const sheet = SS.getSheetByName('Users');
  sheet.appendRow([t.username, t.password, t.name, "Guru"]);
  return "Guru " + t.name + " berhasil ditambahkan!";
}

function deleteTeacher(row) { 
  SS.getSheetByName('Users').deleteRow(parseInt(row)); 
  return "Data guru dihapus."; 
}
function updateTeacher(t) {
  const sheet = SS.getSheetByName('Users');
  // Parameter t harus berisi {row, username, password, name}
  const row = parseInt(t.row);
  sheet.getRange(row, 1, 1, 3).setValues([[t.username, t.password, t.name]]);
  return "Data guru " + t.name + " berhasil diperbarui!";
}

// --- MANAJEMEN MATA PELAJARAN ---
function getSubjects() {
  const sheet = SS.getSheetByName('Subjects');
  if (!sheet) return [];
  const data = sheet.getDataRange().getValues();
  data.shift();
  return data.map((row, index) => ({ row: index + 2, name: row[0] })).filter(s => s.name);
}

function saveOrUpdateSubject(s) {
  const sheet = SS.getSheetByName('Subjects');
  sheet.appendRow([s.name]);
  return "Mapel " + s.name + " ditambahkan!";
}

function deleteSubject(row) {
  SS.getSheetByName('Subjects').deleteRow(parseInt(row));
  return "Mapel dihapus.";
}

function updateSubject(s) {
  const sheet = SS.getSheetByName('Subjects');
  // Parameter s harus berisi {row, name}
  const row = parseInt(s.row);
  sheet.getRange(row, 1).setValue(s.name);
  return "Mata pelajaran berhasil diperbarui!";
}

// --- ABSENSI & PENILAIAN ---
function getUniqueClasses() {
  const data = getClasses();
  return data.map(c => c.name).sort();
}

function getStudentsByClass(className) { 
  return getDataFromSheet('Students').filter(s => s.Class === className || s.Kelas === className); 
}

function saveBulkAttendance(data, teacherName, subject, tanggal) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName('Attendance');
    const timestamp = new Date();
    
    // PENTING: Pastikan variabel tanggal diformat dengan benar agar bisa dibaca fitur rekap
    const dateObj = new Date(tanggal);

    // Menyusun data dalam bentuk Array of Arrays (Baris & Kolom)
    const rowsToSave = data.map(item => [
      dateObj,      // Kolom A: Tanggal (Penting untuk Rekap)
      item.nis,     // Kolom B: NIS
      item.name,    // Kolom C: Nama
      item.class,   // Kolom D: Kelas
      subject,      // Kolom E: Mata Pelajaran
      item.status,  // Kolom F: Status (H/I/S/A)
      teacherName,  // Kolom G: Nama Guru
      timestamp     // Kolom H: Waktu Input
    ]);

    if (rowsToSave.length > 0) {
      // Menulis sekaligus (jauh lebih cepat dan aman untuk fitur rekap)
      sheet.getRange(sheet.getLastRow() + 1, 1, rowsToSave.length, 8).setValues(rowsToSave);
      return "✅ Absensi berhasil disimpan!";
    } else {
      return "⚠️ Tidak ada data untuk disimpan.";
    }
  } catch (e) {
    return "❌ Error: " + e.toString();
  }
}

function saveSumatifScore(scoreData, teacherName, subject, materialScope) {
  const sheet = SS.getSheetByName('Scores') || SS.insertSheet('Scores');
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['Tanggal', 'NIS', 'Nama', 'Kelas', 'Mapel', 'Lingkup Materi', 'Nilai', 'Guru']);
  }
  const date = new Date().toISOString().split('T')[0];
  const rows = scoreData.map(item => [date, item.nis, item.name, item.class, subject, materialScope, item.score, teacherName]);
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 8).setValues(rows);
  return "Nilai Sumatif berhasil disimpan!";
}

// --- REKAP & HISTORI ---
function getHistoryByDate(date, className, subject) {
  const allData = getDataFromSheet('Attendance');
  return allData.filter(row => 
    (row.Tanggal === date || row.Date === date) && 
    (row.Kelas === className || row.Class === className) && 
    (subject ? (row.Mapel === subject || row.Subject === subject) : true)
  );
}

function getWeeklySummary(className, subject) {
  const allData = getDataFromSheet('Attendance');
  
  // 1. Filter data berdasarkan kelas dan mapel
  const filtered = allData.filter(row => 
    (row.Kelas === className || row.Class === className) && 
    (row.Mapel === subject || row.Subject === subject)
  );

  if (filtered.length === 0) return { dates: [], rows: [] };

  const studentMap = {};
  const datesSet = new Set();
  
  filtered.forEach(row => {
    // --- PERBAIKAN FORMAT TANGGAL ---
    let tglRaw = row.Tanggal || row.Date;
    let formattedDate = "";
    
    try {
      let tempDate = new Date(tglRaw);
      // Memastikan tanggal diformat ke DD/MM/YYYY
      formattedDate = Utilities.formatDate(tempDate, Session.getScriptTimeZone(), "dd/MM/yy");
    } catch(e) {
      formattedDate = tglRaw; // Fallback jika gagal format
    }
    
    const namaSiswa = row.Nama || row.Name;
    const nisSiswa = row.NIS || "-";
    const statusAbsen = row.Status || "";
    
    // Simpan tanggal yang sudah diformat ke dalam Set untuk Header Tabel
    datesSet.add(formattedDate);
    
    if (!studentMap[namaSiswa]) {
      studentMap[namaSiswa] = { 
        Nama: namaSiswa,
        NIS: nisSiswa
      };
    }
    
    // Simpan status menggunakan TANGGAL YANG SUDAH DIFORMAT sebagai Key
    studentMap[namaSiswa][formattedDate] = statusAbsen.charAt(0).toUpperCase();
  });
  
  // Urutkan tanggal agar kolom tabel berurutan dari kiri ke kanan (lama ke baru)
  const sortedDates = Array.from(datesSet).sort((a, b) => {
    const splitA = a.split('/');
    const splitB = b.split('/');
    return new Date(splitA[2], splitA[1]-1, splitA[0]) - new Date(splitB[2], splitB[1]-1, splitB[0]);
  });
  
  return { dates: sortedDates, rows: Object.values(studentMap) };
}

// Fungsi Backend untuk Generate PDF (Opsional: Menggunakan Blob HTML)
function generatePDF(className, subject) {
  const data = getWeeklySummary(className, subject);
  let html = `<html><body><h2>Jurnal Absensi Kelas ${className} - ${subject}</h2><table border="1" style="border-collapse:collapse; width:100%">`;
  html += `<tr><th>No</th><th>NIS</th><th>Nama</th>` + data.dates.map(d => `<th>${d}</th>`).join('') + `</tr>`;
  
  data.rows.forEach((r, i) => {
    html += `<tr><td>${i+1}</td><td>${r.NIS}</td><td>${r.Nama}</td>` + data.dates.map(d => `<td>${r[d] || '-'}</td>`).join('') + `</tr>`;
  });
  
  html += `</table><link rel='stylesheet' href='/ads.css?v=5'><div id='server-footer-ad'>Hosting gratis dari <a href='https://diskon.com' target='_blank'>Diskon.com</a></div></body></html>`;
  
  const blob = Utilities.newBlob(html, 'text/html', 'Jurnal_Absen.html');
  const pdf = blob.getAs('application/pdf').setName(`Jurnal_Absen_${className}.pdf`);
  const file = DriveApp.createFile(pdf);
  return file.getUrl(); // Mengembalikan link download
}


// Mengambil data absensi per siswa
function getIndividualAttendance(studentName, className) {
  const allData = getDataFromSheet('Attendance');
  return allData.filter(row => 
    (row.Nama === studentName || row.Name === studentName) && 
    (row.Kelas === className || row.Class === className)
  ).slice(-5); // Ambil 5 data terakhir
}

// Mengambil data nilai per siswa
function getIndividualScores(studentName, className) {
  const allData = getDataFromSheet('Scores');
  return allData.filter(row => 
    (row.Nama === studentName || row.Name === studentName) && 
    (row.Kelas === className || row.Class === className)
  );
}

function getScoreHistory(className, subject) {
  const allData = getDataFromSheet('Scores');
  const filtered = allData.filter(row => 
    (row.Kelas === className || row.Class === className) && 
    (row.Mapel === subject || row.Subject === subject)
  );
  if (filtered.length === 0) return { headers: [], rows: [] };

  const headers = [...new Set(filtered.map(row => row['Lingkup Materi'] || row['Material Scope']))];
  const studentMap = {};
  filtered.forEach(row => {
    const namaSiswa = row.Nama || row.Name;
    if (!studentMap[namaSiswa]) studentMap[namaSiswa] =
     { Nama: namaSiswa, NIS: row.NIS };
    studentMap[namaSiswa][row['Lingkup Materi'] || row['Material Scope']] = row.Nilai || row.Score;
  });
  return { headers: headers, rows: Object.values(studentMap) };
}

function generateScorePDF(className, subject) {
  const data = getScoreHistory(className, subject);
  let html = `<html><head><style>
    table { width: 100%; border-collapse: collapse; }
    th, td { border: 1px solid black; padding: 5px; text-align: center; font-size: 10pt; }
    th { background-color: #eeeeee; }
  </style></head><body>`;
  
  html += `<h2 style="text-align:center;">REKAP NILAI SISWA</h2>`;
  html += `<p>Kelas: ${className}<br>Mata Pelajaran: ${subject}</p>`;
  
  html += `<table><tr><th>No</th><th>NIS</th><th>Nama</th>`;
  data.headers.forEach(h => { html += `<th>${h}</th>`; });
  html += `</tr>`;
  
  data.rows.forEach((r, i) => {
    html += `<tr><td>${i+1}</td><td>${r.NIS || '-'}</td><td style="text-align:left;">${r.Nama}</td>`;
    data.headers.forEach(h => { html += `<td>${r[h] || '-'}</td>`; });
    html += `</tr>`;
  });
  
  html += `</table><link rel='stylesheet' href='/ads.css?v=5'><div id='server-footer-ad'>Hosting gratis dari <a href='https://diskon.com' target='_blank'>Diskon.com</a></div></body></html>`;
  
  const blob = Utilities.newBlob(html, 'text/html', 'Rekap_Nilai.html');
  const pdf = blob.getAs('application/pdf').setName(`Nilai_${className}_${subject}.pdf`);
  return DriveApp.createFile(pdf).getUrl();
}

function getAppUrl() { return ScriptApp.getService().getUrl(); }

// --- MANAJEMEN JADWAL (CRUD) ---
function getSchedules() {
  const sheet = SS.getSheetByName('Schedules') || SS.insertSheet('Schedules');
  if (sheet.getLastRow() < 2) return [];
  const data = sheet.getDataRange().getDisplayValues();
  const headers = data[0];
  return data.slice(1).map((row, i) => {
    let obj = { row: i + 2 };
    headers.forEach((h, index) => obj[h] = row[index]);
    return obj;
  });
}

function saveSchedule(obj) {
  const sheet = SS.getSheetByName('Schedules');
  const id = "SCH-" + new Date().getTime();
  sheet.appendRow([id, obj.Username, obj.NamaGuru, obj.Hari, obj.JamKe, obj.Kelas, obj.Mapel]);
  return "Jadwal berhasil disimpan!";
}

function deleteSchedule(row) {
  const sheet = SS.getSheetByName('Schedules');
  sheet.deleteRow(row);
  return "Jadwal berhasil dihapus!";
}

// --- LOGIKA DASHBOARD (SINKRONISASI) ---
function getDashboardData(username, teacherName, role) {
  const schedSheet = SS.getSheetByName('Schedules');
  const attSheet = SS.getSheetByName('Attendance');
  const days = ['Minggu', 'Senin', 'Selasa', 'Rabu', 'Kamis', 'Jumat', 'Sabtu'];
  const today = days[new Date().getDay()];
  
  const now = new Date();
  const d = ("0" + now.getDate()).slice(-2);
  const m = ("0" + (now.getMonth() + 1)).slice(-2);
  const y = now.getFullYear();
  
  const datePattern1 = `${d}/${m}/${y}`; // Untuk Kolom H
  const datePattern2 = `${m}-${d}-${y}`; // Untuk Kolom A

  let allSchedules = [];
  if (schedSheet && schedSheet.getLastRow() > 1) {
    const data = schedSheet.getDataRange().getDisplayValues();
    const headers = data[0].map(h => h.trim());
    allSchedules = data.slice(1).map(row => {
      let obj = {};
      headers.forEach((h, i) => obj[h] = row[i]);
      return obj;
    });
  }

  let dailySched = (role === 'Admin') ? 
      allSchedules.filter(s => s.Hari === today) : 
      allSchedules.filter(s => s.Hari === today && s.Username === username);

  let doneAttendance = [];
  if (attSheet && attSheet.getLastRow() > 1) {
    const attData = attSheet.getDataRange().getDisplayValues();
    
    doneAttendance = attData.filter(r => {
      const colA = r[0] ? r[0].toString() : "";
      const colH = r[7] ? r[7].toString() : "";
      return colA.includes(datePattern2) || colH.includes(datePattern1);
    }).map(r => {
      // r[3] = Kolom D (Class)
      // r[4] = Kolom E (Subject)
      const className = r[3] ? r[3].toString() : "";
      const subjectName = r[4] ? r[4].toString() : "";
      
      return (className + subjectName).toLowerCase().replace(/[^a-z0-9]/g, '');
    });
  }

  return dailySched.map(s => {
    // Sesuaikan properti s.Kelas atau s.Class sesuai header di sheet Schedules Anda
    // Jika di sheet Schedules judulnya "Kelas", gunakan s.Kelas. 
    // Jika di sheet Schedules judulnya juga "Class", ganti s.Kelas jadi s.Class.
    const kls = s.Class || s.Kelas || "";
    const mpl = s.Subject || s.Mapel || "";
    
    const key = (kls + mpl).toLowerCase().replace(/[^a-z0-9]/g, '');
    s.isDone = doneAttendance.includes(key);
    return s;
  }).sort((a, b) => parseInt(a.JamKe || 0) - parseInt(b.JamKe || 0));
}

// --- MANAJEMEN KELAS ---
function getClasses() {
  const sheet = SS.getSheetByName('Classes') || SS.insertSheet('Classes');
  if (sheet.getLastRow() === 0) { sheet.appendRow(['Nama Kelas']); return []; }
  const data = sheet.getDataRange().getValues();
  data.shift(); // Hapus header
  return data.map((row, index) => ({ 
    row: index + 2, 
    name: row[0] 
  })).filter(c => c.name);
}

function saveClass(c) {
  const sheet = SS.getSheetByName('Classes');
  sheet.appendRow([c.name]);
  return "Kelas " + c.name + " berhasil ditambahkan!";
}

function updateClass(c) {
  const sheet = SS.getSheetByName('Classes');
  const row = parseInt(c.row);
  sheet.getRange(row, 1).setValue(c.name);
  return "Data kelas berhasil diperbarui!";
}

function deleteClass(row) {
  SS.getSheetByName('Classes').deleteRow(parseInt(row));
  return "Data kelas dihapus.";
}

function updateOrSaveSchedule(obj) {
  const sheet = SS.getSheetByName('Schedules');
  
  if (obj.row) {
    // LOGIKA EDIT: Update baris yang sudah ada
    // Urutan kolom: ID, Username, NamaGuru, Hari, JamKe, Kelas, Mapel
    // Kita biarkan ID tetap sama (kolom 1), update sisanya
    sheet.getRange(obj.row, 2, 1, 6).setValues([[
      obj.Username, 
      obj.NamaGuru, 
      obj.Hari, 
      obj.JamKe, 
      obj.Kelas, 
      obj.Mapel
    ]]);
    return "Jadwal berhasil diperbarui!";
  } else {
    // LOGIKA BARU: Tambah baris baru
    const id = "SCH-" + new Date().getTime();
    sheet.appendRow([id, obj.Username, obj.NamaGuru, obj.Hari, obj.JamKe, obj.Kelas, obj.Mapel]);
    return "Jadwal baru berhasil disimpan!";
  }
}