Source Code untuk Aplikasi Guru BK Menggunakan Appscript

Info Aplikasi: Ini adalah Source Code Full Version untuk Aplikasi Bimbingan Konseling (SIP-BK) berbasis Web App. Versi ini telah dimodifikasi untuk menghilangkan halaman login (langsung masuk dashboard) dan fitur pembuatan database otomatis.

Langkah 1: Kode Server (Back-End)

Buat file script baru di Google Apps Script (Extension > Apps Script) dengan nama Code.gs. Hapus semua isinya, lalu tempel kode berikut:

Code.gs

/* BACKEND - SIP BK SMPN 3 KERINCI (FINAL UPDATE + EDIT FITUR) */

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

/* --- API UTAMA --- */

function getInitialData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  setupDatabase(ss); 
  return {
    students: getSheetData(ss, "Data_Siswa"),
    violations: getSheetData(ss, "Data_Pelanggaran"),
    counselings: getSheetData(ss, "Data_Konseling"),
    achievements: getSheetData(ss, "Data_Prestasi"),
    homeVisits: getSheetData(ss, "Data_HomeVisit")
  };
}

/* Single Insert */
function addDataToSheet(sheetName, dataObj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  const rowData = headers.map(header => {
    if (header === 'id') return Date.now();
    let val = dataObj[header];
    return (val === undefined || val === null) ? "" : val;
  });
  
  sheet.appendRow(rowData);
  return getSheetData(ss, sheetName);
}

/* Edit Data (Fitur Baru) */
function editDataInSheet(sheetName, id, updatedData) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const idIndex = headers.indexOf('id');
  
  if (idIndex === -1) return [];

  let rowIndex = -1;
  // Cari baris berdasarkan ID (looping dari baris ke-2 / index 1)
  for (let i = 1; i < data.length; i++) {
    // Gunakan String() untuk memastikan perbandingan aman
    if (String(data[i][idIndex]) === String(id)) {
      rowIndex = i + 1; // Row di spreadsheet dimulai dari 1
      break;
    }
  }

  if (rowIndex !== -1) {
    const newRow = headers.map(header => {
      // ID tidak boleh berubah
      if (header === 'id') return id;
      // Ambil data baru
      let val = updatedData[header];
      return (val === undefined || val === null) ? "" : val;
    });
    
    // Update baris tersebut
    sheet.getRange(rowIndex, 1, 1, newRow.length).setValues([newRow]);
  }

  return getSheetData(ss, sheetName);
}

/* Delete Data */
function deleteDataFromSheet(sheetName, id) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const idIndex = data[0].indexOf('id');
  
  if (idIndex === -1) return;

  for (let i = 1; i < data.length; i++) {
    if (String(data[i][idIndex]) === String(id)) { 
      sheet.deleteRow(i + 1);
      break;
    }
  }
  return getSheetData(ss, sheetName);
}

/* BULK INSERT (Untuk Import Excel) */
function importBulkStudents(dataArray) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Data_Siswa");
  
  if (!dataArray || dataArray.length === 0) return getSheetData(ss, "Data_Siswa");

  const rows = dataArray.map((row, index) => {
    return [
      Date.now() + index, // ID Unik
      row.name || "",
      row.class || "",
      row.nis || "",
      "Aktif", 
      0 
    ];
  });

  const lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, rows.length, rows[0].length).setValues(rows);
  
  return getSheetData(ss, "Data_Siswa");
}

/* HELPER */
function getSheetData(ss, sheetName) {
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) return [];
  
  const rawData = sheet.getDataRange().getValues();
  const headers = rawData.shift();
  
  return rawData.map(row => {
    let obj = {};
    headers.forEach((header, index) => {
      let value = row[index];
      if (Object.prototype.toString.call(value) === '[object Date]') {
        value = Utilities.formatDate(value, Session.getScriptTimeZone(), "yyyy-MM-dd");
      }
      obj[header] = value;
    });
    return obj;
  });
}

function setupDatabase(ss) {
  const schema = {
    "Data_Siswa": ["id", "name", "class", "nis", "status", "points"],
    "Data_Pelanggaran": ["id", "studentId", "studentName", "date", "category", "type", "points", "note"],
    "Data_Konseling": ["id", "studentId", "studentName", "date", "time", "status", "topic"],
    "Data_Prestasi": ["id", "studentId", "studentName", "date", "title", "level", "note"],
    "Data_HomeVisit": ["id", "studentId", "studentName", "date", "parentName", "result", "petugas"]
  };

  for (let sheetName in schema) {
    if (!ss.getSheetByName(sheetName)) {
      let s = ss.insertSheet(sheetName);
      s.appendRow(schema[sheetName]);
    }
  }
}

Langkah 2: Kode Tampilan (Front-End)

Buat file HTML baru dengan nama Index.html. Kode ini sudah dimodifikasi agar langsung masuk ke Dashboard tanpa perlu login.

Index.html

<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SIP-BK SMPN 3 Kerinci</title>
  
  <script src="https://cdn.tailwindcss.com"></script>
  <script src="https://unpkg.com/lucide@latest"></script>
  
  <script src="https://cdn.sheetjs.com/xlsx-0.20.0/package/dist/xlsx.full.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.29/jspdf.plugin.autotable.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
  
  <script crossorigin src="https://unpkg.com/react@18/umd/react.production.min.js"></script>
  <script crossorigin src="https://unpkg.com/react-dom@18/umd/react-dom.production.min.js"></script>
  <script src="https://unpkg.com/@babel/standalone/babel.min.js"></script>

  <style>
    @media print {
      @page { size: A4 portrait; margin: 0; }
      html, body { height: 100%; margin: 0 !important; padding: 0 !important; overflow: hidden; background: white; }
      body * { visibility: hidden; }
      #printable-area, #printable-area * { visibility: visible !important; }
      #printable-area { position: fixed !important; left: 0 !important; top: 0 !important; width: 100% !important; height: 100% !important; z-index: 99999 !important; background: white !important; padding: 1.5cm !important; margin: 0 !important; }
      .no-print, header, nav, button { display: none !important; }
    }
    .kop-text h4 { font-size: 14pt; margin: 0; line-height: 1.1; }
    .kop-text h3 { font-size: 16pt; margin: 0; line-height: 1.1; }
    .kop-text h1 { font-size: 20pt; margin: 0; line-height: 1.1; letter-spacing: 1px; }
    .kop-text p { font-size: 10pt; margin: 0; line-height: 1.2; margin-top: 2px; }
  </style>
</head>
<body class="bg-slate-100 text-slate-900 font-sans">

  <div id="root"></div>

  <script type="text/babel">
    const { useState, useEffect, useRef, useMemo } = React;
    const { jsPDF } = window.jspdf;

    const Icon = ({ name, size = 20, className = "" }) => <i data-lucide={name} style={{ width: size, height: size }} className={className}></i>;

    // --- SIDEBAR ---
    const Sidebar = ({ activeTab, setActiveTab, isOpen, setIsOpen, isMobile }) => {
      const menuItems = [
        { id: 'dashboard', label: 'Dashboard', icon: 'layout-dashboard' },
        { id: 'monitoring', label: 'Pantauan Siswa', icon: 'eye' },
        { id: 'students', label: 'Data Siswa', icon: 'users' },
        { id: 'violations', label: 'Pelanggaran', icon: 'alert-triangle' },
        { id: 'letters', label: 'Surat Panggilan', icon: 'mail' },
        { id: 'achievements', label: 'Prestasi Siswa', icon: 'award' },
        { id: 'counseling', label: 'Jadwal Konseling', icon: 'calendar' },
        { id: 'homevisit', label: 'Home Visit', icon: 'home' },
        { id: 'reports', label: 'Laporan & Export', icon: 'file-text' },
      ];
      return (
        <>
          {isMobile && isOpen && <div className="fixed inset-0 bg-black bg-opacity-50 z-20 no-print" onClick={() => setIsOpen(false)} />}
          <div className={`fixed md:static inset-y-0 left-0 z-30 w-64 bg-slate-900 text-white transform transition-transform duration-300 ease-in-out ${isOpen ? 'translate-x-0' : '-translate-x-full md:translate-x-0'} flex flex-col h-full shadow-xl no-print`}>
            <div className="p-6 border-b border-slate-800 flex items-center justify-between">
              <div className="flex items-center gap-3"><div className="w-10 h-10 bg-blue-600 rounded-lg flex items-center justify-center font-bold text-xl">BK</div><div><h1 className="font-bold text-lg leading-tight">SIP-BK</h1><p className="text-xs text-slate-400">SMPN 3 Kerinci</p></div></div>
              {isMobile && <button onClick={() => setIsOpen(false)} className="text-slate-400 hover:text-white"><Icon name="x" size={24} /></button>}
            </div>
            <nav className="flex-1 p-4 space-y-2 overflow-y-auto">
              {menuItems.map((item) => (
                <button key={item.id} onClick={() => { setActiveTab(item.id); if (isMobile) setIsOpen(false); }} className={`w-full flex items-center gap-3 px-4 py-3 rounded-lg transition-colors ${activeTab === item.id ? 'bg-blue-600 text-white shadow-md' : 'text-slate-300 hover:bg-slate-800 hover:text-white'}`}>
                  <Icon name={item.icon} size={20} /> <span className="font-medium">{item.label}</span>
                </button>
              ))}
            </nav>
            <div className="p-4 border-t border-slate-800 text-center text-xs text-slate-500">Reni Emelisa, S. Pd., Gr.</div>
          </div>
        </>
      );
    };

    // --- STUDENTS VIEW ---
    const StudentsView = ({ students, onAddStudent, onDeleteStudent, onBulkImport }) => {
        const [search, setSearch] = useState("");
        const [showModal, setShowModal] = useState(false);
        const [showImportModal, setShowImportModal] = useState(false);
        const [newStudent, setNewStudent] = useState({ name: '', class: '', nis: '' });
        
        const filtered = (students||[]).filter(s => s.name.toLowerCase().includes(search.toLowerCase()) || s.nis.includes(search));
        const handleSubmit = (e) => { e.preventDefault(); onAddStudent(newStudent); setNewStudent({ name: '', class: '', nis: '' }); setShowModal(false); };
        
        const handleFileImport = (e) => {
            const file = e.target.files[0]; if(!file) return;
            const reader = new FileReader();
            reader.onload = (evt) => {
                const wb = XLSX.read(evt.target.result, { type: 'binary' });
                const ws = wb.Sheets[wb.SheetNames[0]];
                const data = XLSX.utils.sheet_to_json(ws);
                const formatted = data.map(r => ({ name: r["Nama Lengkap"] || r["Nama"], nis: r["NIS"], class: r["Kelas"] })).filter(i => i.name && i.class);
                if(formatted.length > 0 && confirm(`Import ${formatted.length} data?`)) { onBulkImport(formatted); setShowImportModal(false); }
            };
            reader.readAsBinaryString(file);
        };

        const downloadTemplate = () => { const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, XLSX.utils.json_to_sheet([{ "Nama Lengkap": "", "NIS": "", "Kelas": "" }]), "Template"); XLSX.writeFile(wb, "Template_Siswa.xlsx"); }

        return (
            <div className="space-y-6">
                 <div className="flex flex-col sm:flex-row justify-between items-center gap-4">
                    <h2 className="text-2xl font-bold text-slate-800">Data Siswa</h2>
                    <div className="flex gap-2">
                        <button onClick={() => setShowImportModal(true)} className="bg-green-600 text-white px-4 py-2 rounded-lg flex items-center gap-2 hover:bg-green-700"><Icon name="file-spreadsheet" size={18}/> Import Excel</button>
                        <button onClick={() => setShowModal(true)} className="bg-blue-600 text-white px-4 py-2 rounded-lg flex items-center gap-2 hover:bg-blue-700"><Icon name="user-plus" size={18}/> Tambah</button>
                    </div>
                 </div>
                 <input className="w-full p-3 border rounded-lg" placeholder="Cari nama atau NIS..." value={search} onChange={e=>setSearch(e.target.value)}/>
                 <div className="bg-white rounded-xl shadow overflow-hidden">
                    <table className="w-full text-sm text-left"><thead className="bg-slate-100 uppercase"><tr><th className="p-4">NIS</th><th className="p-4">Nama</th><th className="p-4">Kelas</th><th className="p-4">Aksi</th></tr></thead><tbody>{filtered.map(s => (<tr key={s.id} className="border-b hover:bg-slate-50"><td className="p-4">{s.nis}</td><td className="p-4 font-bold">{s.name}</td><td className="p-4"><span className="bg-blue-100 text-blue-800 px-2 py-1 rounded text-xs">{s.class}</span></td><td className="p-4"><button onClick={()=>onDeleteStudent(s.id)} className="text-red-500"><Icon name="trash-2" size={16}/></button></td></tr>))}</tbody></table>
                 </div>
                 {showModal && <div className="fixed inset-0 bg-black/50 flex items-center justify-center z-50"><div className="bg-white p-6 rounded-xl w-96"><h3 className="text-xl font-bold mb-4">Tambah Siswa</h3><form onSubmit={handleSubmit} className="space-y-4"><input required placeholder="Nama" className="w-full p-2 border rounded" value={newStudent.name} onChange={e=>setNewStudent({...newStudent, name:e.target.value})}/><input required placeholder="NIS" className="w-full p-2 border rounded" value={newStudent.nis} onChange={e=>setNewStudent({...newStudent, nis:e.target.value})}/><select required className="w-full p-2 border rounded" value={newStudent.class} onChange={e=>setNewStudent({...newStudent, class:e.target.value})}><option value="">Pilih Kelas</option>{"7A 7B 7C 8A 8B 8C 9A 9B 9C".split(" ").map(c=><option key={c} value={c}>{c}</option>)}</select><div className="flex justify-end gap-2"><button type="button" onClick={()=>setShowModal(false)} className="px-4 py-2 text-slate-500">Batal</button><button type="submit" className="px-4 py-2 bg-blue-600 text-white rounded">Simpan</button></div></form></div></div>}
                 {showImportModal && <div className="fixed inset-0 bg-black/50 flex items-center justify-center z-50"><div className="bg-white p-6 rounded-xl w-96"><h3 className="font-bold mb-4">Import Excel</h3><button onClick={downloadTemplate} className="text-blue-600 text-sm mb-4 block underline">Download Template</button><input type="file" accept=".xlsx" onChange={handleFileImport} className="block w-full text-sm text-slate-500 file:mr-4 file:py-2 file:px-4 file:rounded-full file:border-0 file:bg-blue-50 file:text-blue-700 hover:file:bg-blue-100"/><button onClick={()=>setShowImportModal(false)} className="mt-4 w-full text-slate-500">Batal</button></div></div>}
            </div>
        )
    };

    // --- VIOLATIONS VIEW ---
    const ViolationsView = ({ violations, students, onAddViolation, onEditViolation, onDeleteViolation }) => {
        const [showModal, setShowModal] = useState(false);
        const [isEditing, setIsEditing] = useState(false);
        
        const VIOLATION_TYPES = useMemo(() => ({
            "Ringan": { points: 5, items: ["Terlambat", "Pakaian tidak rapi/atribut tidak sesuai", "Membawa HP/motor ke sekolah (jika dilarang)"] },
            "Sedang": { points: 20, items: ["Membolos", "Merokok", "Tidur di kelas", "Sering tidak mengerjakan tugas"] },
            "Berat": { points: 75, items: ["Kekerasan fisik/psikologis", "Tindakan amoral (narkoba)", "Pencurian", "Perkelahian", "Merusak fasilitas sekolah", "Kekerasan seksual"] }
        }), []);

        const [form, setForm] = useState({ id: '', studentId: '', category: '', type: '', points: 0, note: '', date: new Date().toISOString().split('T')[0] });
        const resetForm = () => { setForm({ id: '', studentId: '', category: '', type: '', points: 0, note: '', date: new Date().toISOString().split('T')[0] }); setIsEditing(false); };

        const handleEditClick = (v) => {
            let foundCategory = "";
            Object.keys(VIOLATION_TYPES).forEach(cat => { if(VIOLATION_TYPES[cat].items.includes(v.type)) foundCategory = cat; });
            const studentObj = (students || []).find(s => s.name === v.studentName);
            setForm({ id: v.id, studentId: studentObj ? studentObj.id : "", category: foundCategory, type: v.type, points: v.points, note: v.note, date: v.date });
            setIsEditing(true); setShowModal(true);
        };
        const handleDeleteClick = (id) => { if(confirm("Yakin ingin menghapus data pelanggaran ini?")) { onDeleteViolation(id); } };
        const handleTypeChange = (e) => {
            const val = e.target.value; const cat = form.category;
            let points = 0; if (cat && VIOLATION_TYPES[cat]) points = VIOLATION_TYPES[cat].points;
            setForm({ ...form, type: val, points: points });
        };
        const handleSubmit = (e) => {
            e.preventDefault();
            const s = (students || []).find(s => String(s.id) === String(form.studentId));
            if (s) {
                const payload = { ...form, studentName: s.name, points: Number(form.points) };
                isEditing ? onEditViolation(form.id, payload) : onAddViolation(payload);
                setShowModal(false); resetForm();
            } else { alert("Silakan pilih siswa yang valid."); }
        };

        return (
            <div className="space-y-6">
                <div className="flex justify-between items-center"><h2 className="text-2xl font-bold text-slate-800">Buku Kasus & Pelanggaran</h2><button onClick={() => { resetForm(); setShowModal(true); }} className="bg-red-600 hover:bg-red-700 text-white px-4 py-2 rounded-lg flex gap-2 shadow-sm"><Icon name="plus" size={18} /> Catat Pelanggaran</button></div>
                <div className="bg-white shadow-sm border border-slate-200 rounded-xl overflow-hidden">
                    <table className="w-full text-sm text-left">
                        <thead className="bg-slate-50 border-b"><tr><th className="p-4">Tanggal</th><th className="p-4">Nama Siswa</th><th className="p-4">Jenis Pelanggaran</th><th className="p-4 text-center">Poin</th><th className="p-4 text-center">Aksi</th></tr></thead>
                        <tbody className="divide-y divide-slate-100">
                            {(violations || []).map((v, i) => (
                                <tr key={i} className="hover:bg-slate-50 transition-colors">
                                    <td className="p-4 whitespace-nowrap text-slate-500">{v.date}</td><td className="p-4 font-bold text-slate-800">{v.studentName}</td>
                                    <td className="p-4"><span className="font-medium text-slate-700">{v.type}</span>{v.note && <span className="text-xs text-slate-400 block mt-0.5">Catatan: {v.note}</span>}</td>
                                    <td className="p-4 text-center"><span className="bg-red-100 text-red-700 font-bold px-2 py-1 rounded text-xs">+{v.points}</span></td>
                                    <td className="p-4 text-center"><div className="flex items-center justify-center gap-2"><button onClick={() => handleEditClick(v)} className="text-blue-600 hover:bg-blue-50 p-1 rounded"><Icon name="edit-3" size={16} /></button><button onClick={() => handleDeleteClick(v.id)} className="text-red-600 hover:bg-red-50 p-1 rounded"><Icon name="trash-2" size={16} /></button></div></td>
                                </tr>
                            ))}
                        </tbody>
                    </table>
                </div>
                {showModal && (
                    <div className="fixed inset-0 bg-black/50 backdrop-blur-sm flex items-center justify-center z-50 p-4">
                        <div className="bg-white p-6 rounded-xl w-full max-w-md shadow-2xl">
                            <div className="flex justify-between items-center mb-6"><h3 className="font-bold text-xl text-slate-800">{isEditing ? 'Edit Data' : 'Catat Pelanggaran'}</h3><button onClick={() => setShowModal(false)}><Icon name="x" size={24} /></button></div>
                            <form onSubmit={handleSubmit} className="space-y-4">
                                <div><label className="block text-xs font-semibold text-slate-500 mb-1">Nama Siswa</label><select required className="w-full p-2.5 border rounded-lg" value={form.studentId} onChange={e => setForm({ ...form, studentId: e.target.value })} disabled={isEditing}><option value="">-- Pilih Siswa --</option>{(students || []).map(s => (<option key={s.id} value={s.id}>{s.name} - {s.class}</option>))}</select></div>
                                <div className="grid grid-cols-2 gap-4"><div><label className="block text-xs font-semibold text-slate-500 mb-1">Tanggal</label><input type="date" required className="w-full p-2.5 border rounded-lg" value={form.date} onChange={e => setForm({ ...form, date: e.target.value })} /></div><div><label className="block text-xs font-semibold text-slate-500 mb-1">Kategori</label><select className="w-full p-2.5 border rounded-lg" value={form.category} onChange={e => setForm({ ...form, category: e.target.value, type: '', points: 0 })}><option value="">Manual/Lainnya</option><option value="Ringan">Ringan (5 Poin)</option><option value="Sedang">Sedang (20 Poin)</option><option value="Berat">Berat (75 Poin)</option></select></div></div>
                                <div><label className="block text-xs font-semibold text-slate-500 mb-1">Jenis Pelanggaran</label>{form.category ? (<select required className="w-full p-2.5 border rounded-lg bg-slate-50" value={form.type} onChange={handleTypeChange}><option value="">-- Pilih Jenis --</option>{VIOLATION_TYPES[form.category].items.map((item, idx) => (<option key={idx} value={item}>{item}</option>))}</select>) : (<input placeholder="Ketik jenis pelanggaran..." required className="w-full p-2.5 border rounded-lg" value={form.type} onChange={e => setForm({...form, type: e.target.value})} />)}</div>
                                <div className="grid grid-cols-3 gap-4"><div className="col-span-1"><label className="block text-xs font-semibold text-slate-500 mb-1">Poin</label><input type="number" required className="w-full p-2.5 border rounded-lg font-bold text-red-600" value={form.points} onChange={e => setForm({ ...form, points: e.target.value })} /></div><div className="col-span-2"><label className="block text-xs font-semibold text-slate-500 mb-1">Catatan</label><input placeholder="Keterangan..." className="w-full p-2.5 border rounded-lg" value={form.note} onChange={e => setForm({ ...form, note: e.target.value })} /></div></div>
                                <div className="flex justify-end gap-3 mt-6 pt-4 border-t"><button type="button" onClick={() => setShowModal(false)} className="px-4 py-2 text-slate-600 hover:bg-slate-100 rounded-lg">Batal</button><button type="submit" className="px-6 py-2 bg-red-600 hover:bg-red-700 text-white font-bold rounded-lg shadow">{isEditing ? 'Simpan Perubahan' : 'Simpan Data'}</button></div>
                            </form>
                        </div>
                    </div>
                )}
            </div>
        );
    }

    // --- MONITORING VIEW ---
    const MonitoringView = ({ students, violations }) => {
        const calculatedStudents = useMemo(() => {
            return (students || []).map(student => {
                const studentViolations = (violations || []).filter(v => String(v.studentId) === String(student.id));
                const totalPoints = studentViolations.reduce((sum, v) => sum + Number(v.points || 0), 0);
                return { ...student, points: totalPoints, violationHistory: studentViolations };
            }).filter(s => s.points > 0).sort((a, b) => b.points - a.points);
        }, [students, violations]);

        const getStatusColor = (p) => p >= 50 ? 'bg-red-500' : p >= 20 ? 'bg-orange-500' : 'bg-blue-500';
        const getStatusText = (p) => p >= 50 ? 'BAHAYA (SP)' : p >= 20 ? 'PERLU PERHATIAN' : 'PEMBINAAN';

        return (
            <div className="space-y-6">
                <h2 className="text-2xl font-bold text-slate-800">Pantauan Siswa Bermasalah</h2>
                <div className="grid grid-cols-1 gap-6">
                    {calculatedStudents.map((s) => (
                        <div key={s.id} className="bg-white rounded-xl shadow-sm border border-slate-200 overflow-hidden flex flex-col md:flex-row">
                            <div className="p-6 md:w-1/3 bg-slate-50 flex flex-col items-center justify-center text-center border-r">
                                <div className={`w-20 h-20 rounded-full ${getStatusColor(s.points)} text-white flex items-center justify-center text-3xl font-bold mb-4 shadow-lg`}>{s.points}</div>
                                <h3 className="font-bold text-xl text-slate-800">{s.name}</h3>
                                <p className="text-slate-500 mb-2">Kelas {s.class} | NIS: {s.nis}</p>
                                <span className={`px-3 py-1 rounded-full text-xs font-bold text-white ${getStatusColor(s.points)}`}>{getStatusText(s.points)}</span>
                            </div>
                            <div className="p-6 md:w-2/3">
                                <h4 className="font-bold text-slate-700 mb-4 flex items-center gap-2"><Icon name="file-text" size={18} /> Riwayat Pelanggaran</h4>
                                <div className="space-y-3 max-h-60 overflow-y-auto">
                                    {s.violationHistory.map((v, idx) => (
                                        <div key={idx} className="flex justify-between text-sm p-3 bg-white border rounded-lg">
                                            <div><p className="font-semibold">{v.type}</p><p className="text-xs text-slate-500">{v.note}</p></div>
                                            <div className="text-right"><span className="text-red-600 font-bold">+{v.points}</span><p className="text-xs text-slate-400">{v.date}</p></div>
                                        </div>
                                    ))}
                                </div>
                            </div>
                        </div>
                    ))}
                    {calculatedStudents.length === 0 && (<div className="text-center p-12 bg-white rounded-xl border border-slate-200"><Icon name="award" size={48} className="mx-auto text-green-500 mb-4"/><h3 className="text-xl font-bold text-slate-800">Tidak Ada Siswa Bermasalah</h3></div>)}
                </div>
            </div>
        );
    };

    // --- LETTERS VIEW ---
    const LettersView = ({ students, violations }) => {
        const [formData, setFormData] = useState({ studentId: '', letterNo: '005/BK/SMPN3/2026', day: 'Senin', date: new Date().toISOString().split('T')[0], time: '09:00 WIB', place: 'Ruang BK SMPN 3 Kerinci', reason: 'Penyelesaian Masalah Kedisiplinan' });
        const [selectedStudent, setSelectedStudent] = useState(null);

        const studentsWithPoints = useMemo(() => {
            return (students || []).map(s => {
                const total = (violations || []).filter(v => String(v.studentId) === String(s.id)).reduce((acc, curr) => acc + Number(curr.points || 0), 0);
                return { ...s, totalPoints: total };
            });
        }, [students, violations]);

        const handleStudentChange = (e) => {
            const val = e.target.value; setFormData({...formData, studentId: val});
            if(!val) { setSelectedStudent(null); return; }
            setSelectedStudent(studentsWithPoints.find(s => String(s.id) === String(val)));
        };

        return (
            <div className="flex gap-6 flex-col lg:flex-row">
                 <div className="lg:w-1/3 bg-white p-6 rounded-xl shadow border h-fit no-print">
                    <h3 className="font-bold text-lg mb-4">Buat Surat</h3>
                    <div className="space-y-3">
                        <select className="w-full p-2 border rounded" value={formData.studentId} onChange={handleStudentChange}><option value="">-- Cari Nama Siswa --</option>{studentsWithPoints.map(s => (<option key={s.id} value={s.id}>{s.name} - {s.class} (Poin: {s.totalPoints})</option>))}</select>
                        <input className="w-full p-2 border rounded" value={formData.letterNo} onChange={e=>setFormData({...formData, letterNo:e.target.value})} placeholder="No. Surat"/>
                        <input type="date" className="w-full p-2 border rounded" value={formData.date} onChange={e=>setFormData({...formData, date:e.target.value})}/>
                        <input className="w-full p-2 border rounded" value={formData.time} onChange={e=>setFormData({...formData, time:e.target.value})} placeholder="Waktu"/>
                        <input className="w-full p-2 border rounded" value={formData.reason} onChange={e=>setFormData({...formData, reason:e.target.value})} placeholder="Keperluan"/>
                        <button onClick={()=>window.print()} disabled={!selectedStudent} className="w-full bg-blue-600 text-white p-2 rounded flex justify-center gap-2 mt-2 disabled:bg-gray-300"><Icon name="printer" size={16}/> Cetak Surat</button>
                    </div>
                 </div>
                 <div id="printable-area" className="lg:w-2/3 bg-white p-12 shadow max-w-[21cm] mx-auto min-h-[29.7cm] text-black font-serif relative">
                        <div className="flex items-center justify-between border-b-4 border-double border-black pb-2 mb-8">
                             <div className="w-24 flex justify-center"><img src="https://drive.google.com/thumbnail?id=1uZHh5ReKYrAL6ycC6nYXtnb6zhETXQlX" className="w-20 h-auto object-contain"/></div>
                             <div className="flex-1 text-center kop-text px-2"><h4 className="font-medium uppercase tracking-wide">PEMERINTAH KABUPATEN KERINCI</h4><h3 className="font-bold uppercase tracking-wide">DINAS PENDIDIKAN</h3><h1 className="font-extrabold uppercase">SMP NEGERI 3 KERINCI</h1><p className="italic">Alamat: Jl. Lempur Tengah, Kec. Gunung Raya, Kab. Kerinci, Jambi</p></div>
                             <div className="w-24 flex justify-center"><img src="https://drive.google.com/thumbnail?id=1QOvUSLtFM9-s0asnaZSCPXxsGOZnBUim" className="w-20 h-auto object-contain"/></div>
                        </div>
                        {selectedStudent ? (
                            <div className="leading-relaxed">
                                <div className="flex justify-between mb-8"><table><tbody><tr><td className="w-16">Nomor</td><td>: {formData.letterNo}</td></tr><tr><td>Lamp</td><td>: -</td></tr><tr><td>Hal</td><td>: <strong>Panggilan Orang Tua</strong></td></tr></tbody></table><div className="text-right">Kerinci, {new Date(formData.date).toLocaleDateString('id-ID', {day:'numeric', month:'long', year:'numeric'})}</div></div>
                                <div className="mb-6"><p>Yth. Orang Tua / Wali Murid dari :</p><p className="font-bold text-lg mt-2 underline uppercase">{selectedStudent.name}</p><p>Kelas : {selectedStudent.class}</p><p className="mt-1">di Tempat</p></div>
                                <p className="indent-12 mb-4 text-justify">Dengan hormat,<br/>Sehubungan dengan perlunya komunikasi dan kerjasama antara pihak sekolah dengan orang tua siswa demi perkembangan pendidikan dan kedisiplinan putra/putri Bapak/Ibu, maka kami mengharap kehadiran Bapak/Ibu ke sekolah pada:</p>
                                <div className="ml-12 mb-8 space-y-1 font-semibold"><div className="grid grid-cols-4"><span>Hari / Tanggal</span><span className="col-span-3">: {formData.day}, {new Date(formData.date).toLocaleDateString('id-ID', {day:'numeric', month:'long', year:'numeric'})}</span></div><div className="grid grid-cols-4"><span>Pukul</span><span className="col-span-3">: {formData.time}</span></div><div className="grid grid-cols-4"><span>Tempat</span><span className="col-span-3">: {formData.place}</span></div><div className="grid grid-cols-4"><span>Keperluan</span><span className="col-span-3">: {formData.reason}</span></div></div>
                                <p className="indent-12 mb-16 text-justify">Mengingat pentingnya hal tersebut, dimohon Bapak/Ibu dapat hadir tepat pada waktunya. Atas perhatian dan kerjasamanya kami ucapkan terima kasih.</p>
                                <div className="flex justify-between mt-12 px-4"><div className="text-center w-64"><p>Mengetahui,</p><p>Kepala Sekolah</p><br/><br/><br/><br/><p className="font-bold underline">Hamdani, S. Pd.</p><p>NIP. 197108142005021005</p></div><div className="text-center w-64"><p>Guru Bimbingan Konseling</p><br/><br/><br/><br/><br/><p className="font-bold underline">Reni Emelisa, S. Pd., Gr.</p><p>NIP. 199310112024212043</p></div></div>
                            </div>
                        ) : (<div className="text-center text-gray-400 py-20 border-2 border-dashed rounded"><p>Silakan pilih siswa untuk menampilkan surat.</p></div>)}
                 </div>
            </div>
        )
    };

    // --- REPORTS VIEW ---
    const ReportsView = ({ students, violations, counselings, achievements }) => {
        const exportToCSV = (data, filename) => { if (!data || data.length === 0) { alert("Data kosong"); return; } const header = Object.keys(data[0]).join(","); const rows = data.map(row => Object.values(row).map(v => `"${v}"`).join(",")).join("\n"); const blob = new Blob([header + "\n" + rows], { type: 'text/csv' }); const url = URL.createObjectURL(blob); const a = document.createElement('a'); a.href = url; a.download = filename + ".csv"; a.click(); };
        const exportTableToPDF = (data, title, columns) => { if (!data || data.length === 0) { alert("Data kosong"); return; } const doc = new jsPDF(); doc.setFontSize(14); doc.text("LAPORAN DATA BK - SMPN 3 KERINCI", 14, 15); doc.setFontSize(10); doc.text(title, 14, 22); doc.text(`Dicetak: ${new Date().toLocaleDateString('id-ID')}`, 14, 27); const tableRows = data.map(row => columns.map(col => row[col] || "")); doc.autoTable({ head: [columns.map(c => c.toUpperCase())], body: tableRows, startY: 32, theme: 'grid', styles: { fontSize: 8 } }); doc.save(`${title.replace(/\s+/g, '_')}.pdf`); };
        
        const safeViolations = violations || [];
        const violationsByClass = safeViolations.reduce((acc, curr) => { const student = (students||[]).find(s => s.name === curr.studentName); const cls = student ? student.class : 'Lainnya'; acc[cls] = (acc[cls] || 0) + 1; return acc; }, {});
        const maxVal = Math.max(...Object.values(violationsByClass), 1);

        return (
            <div className="space-y-6">
                <div className="flex justify-between items-center no-print"><h2 className="text-2xl font-bold text-slate-800">Laporan & Statistik</h2><button onClick={()=>window.print()} className="bg-slate-800 text-white px-4 py-2 rounded-lg flex items-center gap-2"><Icon name="printer" size={18} /> Cetak Visual</button></div>
                <div id="printable-area" className="bg-white p-8 rounded-xl shadow-sm border border-slate-200">
                    <div className="mb-8 border-b-2 border-slate-800 pb-4 text-center"><h1 className="text-2xl font-bold uppercase">Laporan Bimbingan Konseling</h1><h2 className="text-xl font-semibold text-slate-600">SMP Negeri 3 Kerinci</h2></div>
                    <div className="grid grid-cols-1 lg:grid-cols-2 gap-8">
                         <div className="p-4 border rounded-lg"><h3 className="font-bold mb-4">Statistik Pelanggaran</h3>{Object.entries(violationsByClass).map(([cls, count]) => (<div key={cls} className="mb-2"><div className="flex justify-between text-xs mb-1"><span>{cls}</span><span>{count}</span></div><div className="w-full bg-slate-100 h-2 rounded"><div className="bg-blue-600 h-2 rounded" style={{width: `${(count/maxVal)*100}%`}}></div></div></div>))}</div>
                         <div className="p-4 border rounded-lg"><h3 className="font-bold mb-4">Ringkasan</h3><table className="w-full text-sm"><tbody><tr className="border-b"><td className="py-2">Total Pelanggaran</td><td className="font-bold">{violations.length}</td></tr><tr className="border-b"><td className="py-2">Total Konseling</td><td className="font-bold">{counselings.length}</td></tr><tr className="border-b"><td className="py-2">Prestasi</td><td className="font-bold">{achievements.length}</td></tr></tbody></table></div>
                    </div>
                </div>
                <div className="no-print p-6 bg-slate-50 rounded-xl border"><h3 className="font-bold text-lg mb-4 flex gap-2"><Icon name="download" size={20}/> Export Data Lengkap</h3><div className="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-4"><div className="bg-white p-4 rounded border flex flex-col gap-2"><span className="font-bold flex gap-2 text-sm"><Icon name="users" size={16}/> Data Siswa</span><div className="flex gap-2 mt-2"><button onClick={()=>exportToCSV(students,'Siswa')} className="flex-1 text-xs bg-blue-50 text-blue-700 py-1 rounded border border-blue-200">CSV</button><button onClick={()=>exportTableToPDF(students, 'Data Siswa', ['nis','name','class'])} className="flex-1 text-xs bg-red-50 text-red-700 py-1 rounded border border-red-200">PDF</button></div></div><div className="bg-white p-4 rounded border flex flex-col gap-2"><span className="font-bold flex gap-2 text-sm"><Icon name="alert-triangle" size={16}/> Pelanggaran</span><div className="flex gap-2 mt-2"><button onClick={()=>exportToCSV(violations,'Pelanggaran')} className="flex-1 text-xs bg-blue-50 text-blue-700 py-1 rounded border border-blue-200">CSV</button><button onClick={()=>exportTableToPDF(violations, 'Data Pelanggaran', ['date','studentName','type','points','note'])} className="flex-1 text-xs bg-red-50 text-red-700 py-1 rounded border border-red-200">PDF</button></div></div></div></div>
            </div>
        )
    };

    // --- GENERIC FORM VIEW ---
    const GenericFormView = ({ title, type, dataList, students, onAdd }) => {
        const [showModal, setShowModal] = useState(false);
        const [form, setForm] = useState({});
        const handleSubmit = (e) => { e.preventDefault(); const s = (students||[]).find(s=>String(s.id)===String(form.studentId)); if(s){ onAdd({...form, studentName: s.name}); setShowModal(false); setForm({}); }};
        return <div className="space-y-6"><div className="flex justify-between items-center"><h2 className="text-2xl font-bold text-slate-800">{title}</h2><button onClick={()=>setShowModal(true)} className="bg-blue-600 text-white px-4 py-2 rounded-lg flex gap-2"><Icon name="plus" size={18}/> Tambah</button></div><div className="bg-white rounded-xl shadow overflow-hidden"><table className="w-full text-sm text-left"><thead className="bg-slate-100 uppercase"><tr><th className="p-4">Tanggal</th><th className="p-4">Nama</th><th className="p-4">Keterangan</th></tr></thead><tbody>{(dataList||[]).map((d,i) => (<tr key={i} className="border-b"><td className="p-4">{d.date}</td><td className="p-4 font-bold">{d.studentName}</td><td className="p-4">{d.type || d.title || d.topic || d.result} <span className="text-xs text-gray-500 block">{d.note}</span></td></tr>))}</tbody></table></div>{showModal && <div className="fixed inset-0 bg-black/50 flex items-center justify-center z-50"><div className="bg-white p-6 rounded-xl w-96"><h3 className="font-bold text-lg mb-4">Input Data</h3><form onSubmit={handleSubmit} className="space-y-3"><select required className="w-full p-2 border rounded" onChange={e=>setForm({...form, studentId:e.target.value})}><option value="">Pilih Siswa</option>{(students||[]).map(s=><option key={s.id} value={s.id}>{s.name} - {s.class}</option>)}</select><input type="date" required className="w-full p-2 border rounded" onChange={e=>setForm({...form, date:e.target.value})}/>{type==='achievement' && <><input placeholder="Prestasi" className="w-full p-2 border rounded" onChange={e=>setForm({...form, title:e.target.value})}/><select className="w-full p-2 border rounded" onChange={e=>setForm({...form, level:e.target.value})}><option value="Sekolah">Sekolah</option><option value="Kabupaten">Kabupaten</option></select></>}{type==='counseling' && <><input placeholder="Topik" className="w-full p-2 border rounded" onChange={e=>setForm({...form, topic:e.target.value})}/><input type="time" className="w-full p-2 border rounded" onChange={e=>setForm({...form, time:e.target.value})}/></>}{type==='homevisit' && <><input placeholder="Nama Wali" className="w-full p-2 border rounded" onChange={e=>setForm({...form, parentName:e.target.value})}/><textarea placeholder="Hasil" className="w-full p-2 border rounded" onChange={e=>setForm({...form, result:e.target.value})}/></>}<input placeholder="Catatan" className="w-full p-2 border rounded" onChange={e=>setForm({...form, note:e.target.value})}/><div className="flex justify-end gap-2 mt-4"><button type="button" onClick={()=>setShowModal(false)} className="text-gray-500">Batal</button><button type="submit" className="bg-blue-600 text-white px-4 py-2 rounded">Simpan</button></div></form></div></div>}</div>
    }

    // --- DASHBOARD VIEW ---
    const DashboardView = ({ students, violations, counselings, achievements }) => {
        const stats = [{ label: 'Total Siswa', value: (students||[]).length, icon: 'users', color: 'bg-blue-500' }, { label: 'Pelanggaran', value: (violations||[]).length, icon: 'alert-triangle', color: 'bg-red-500' }, { label: 'Prestasi', value: (achievements||[]).length, icon: 'award', color: 'bg-yellow-500' }, { label: 'Konseling', value: (counselings||[]).length, icon: 'calendar', color: 'bg-green-500' }];
        return <div className="space-y-6"><h2 className="text-2xl font-bold text-slate-800">Dashboard Ikhtisar</h2><div className="grid grid-cols-1 md:grid-cols-4 gap-4">{stats.map((stat, idx) => (<div key={idx} className="bg-white p-6 rounded-xl shadow-sm border flex items-center gap-4"><div className={`${stat.color} p-4 rounded-lg text-white`}><Icon name={stat.icon} size={24}/></div><div><p className="text-sm text-slate-500">{stat.label}</p><p className="text-2xl font-bold">{stat.value}</p></div></div>))}</div></div>
    };

    // --- MAIN APP (No Login Logic) ---
    const GuruBKApp = () => {
      const [activeTab, setActiveTab] = useState('dashboard');
      const [isSidebarOpen, setIsSidebarOpen] = useState(false);
      const [isMobile, setIsMobile] = useState(false);
      const [loading, setLoading] = useState(true);

      const [students, setStudents] = useState([]);
      const [violations, setViolations] = useState([]);
      const [counselings, setCounselings] = useState([]);
      const [achievements, setAchievements] = useState([]);
      const [homeVisits, setHomeVisits] = useState([]);

      useEffect(() => { lucide.createIcons(); }, [activeTab, students, violations, loading]);
      useEffect(() => {
        const checkMobile = () => setIsMobile(window.innerWidth < 768); checkMobile(); window.addEventListener('resize', checkMobile);
        google.script.run.withSuccessHandler((data) => { setStudents(data.students||[]); setViolations(data.violations||[]); setCounselings(data.counselings||[]); setAchievements(data.achievements||[]); setHomeVisits(data.homeVisits||[]); setLoading(false); }).getInitialData();
        return () => window.removeEventListener('resize', checkMobile);
      }, []);

      const handleAddStudent = (s) => { const n={...s, status:'Aktif', points:0, id:Date.now()}; setStudents([...students,n]); google.script.run.addDataToSheet("Data_Siswa",n); };
      const handleBulkImport = (data) => { google.script.run.withSuccessHandler((updated) => setStudents(updated)).importBulkStudents(data); };
      const handleDeleteStudent = (id) => { if(confirm("Hapus?")) { setStudents(students.filter(s=>s.id!==id)); google.script.run.deleteDataFromSheet("Data_Siswa", id); }};
      const handleAddViolation = (v) => { const n={...v, id:Date.now()}; setViolations([...violations,n]); google.script.run.addDataToSheet("Data_Pelanggaran",n); };
      
      const handleEditViolation = (id, updatedData) => {
          const updatedList = violations.map(v => (v.id === id ? { ...v, ...updatedData } : v));
          setViolations(updatedList);
          google.script.run.editDataInSheet("Data_Pelanggaran", id, updatedData);
      };
      const handleDeleteViolation = (id) => {
          setViolations(violations.filter(v => v.id !== id));
          google.script.run.deleteDataFromSheet("Data_Pelanggaran", id);
      };
      
      const handleAddGeneric = (t, d, set, list, sheet) => { const n={...d, id:Date.now()}; set([...list,n]); google.script.run.addDataToSheet(sheet,n); };

      const renderContent = () => {
        if(loading) return <div className="flex h-full items-center justify-center text-blue-600 font-bold animate-pulse">Memuat Data...</div>;
        switch (activeTab) {
          case 'dashboard': return <DashboardView students={students} violations={violations} counselings={counselings} achievements={achievements} />;
          case 'students': return <StudentsView students={students} onAddStudent={handleAddStudent} onDeleteStudent={handleDeleteStudent} onBulkImport={handleBulkImport} />;
          case 'violations': return <ViolationsView violations={violations} students={students} onAddViolation={handleAddViolation} onEditViolation={handleEditViolation} onDeleteViolation={handleDeleteViolation} />;
          case 'monitoring': return <MonitoringView students={students} violations={violations} />;
          case 'letters': return <LettersView students={students} violations={violations} />;
          case 'reports': return <ReportsView students={students} violations={violations} counselings={counselings} achievements={achievements} />;
          case 'achievements': return <GenericFormView title="Prestasi Siswa" type="achievement" dataList={achievements} students={students} onAdd={(d)=>handleAddGeneric('achievement', d, setAchievements, achievements, 'Data_Prestasi')} />;
          case 'counseling': return <GenericFormView title="Jadwal Konseling" type="counseling" dataList={counselings} students={students} onAdd={(d)=>handleAddGeneric('counseling', d, setCounselings, counselings, 'Data_Konseling')} />;
          case 'homevisit': return <GenericFormView title="Laporan Home Visit" type="homevisit" dataList={homeVisits} students={students} onAdd={(d)=>handleAddGeneric('homevisit', d, setHomeVisits, homeVisits, 'Data_HomeVisit')} />;
          default: return <DashboardView />;
        }
      };

      return (
        <div className="flex h-screen bg-slate-100 font-sans text-slate-900">
          <Sidebar activeTab={activeTab} setActiveTab={setActiveTab} isOpen={isSidebarOpen} setIsOpen={setIsSidebarOpen} isMobile={isMobile} />
          <div className="flex-1 flex flex-col h-screen overflow-hidden print:overflow-visible">
            <header className="h-16 bg-white border-b border-slate-200 flex items-center justify-between px-6 z-10 print:hidden">
              <div className="flex items-center gap-4">
                {isMobile && (<button onClick={() => setIsSidebarOpen(true)} className="text-slate-500 hover:text-slate-800"><Icon name="menu" size={24} /></button>)}
                <h2 className="text-lg font-bold text-slate-800 hidden sm:block">Aplikasi Bimbingan Konseling</h2>
              </div>
              <div className="flex items-center gap-3 pl-4 border-l border-slate-200">
                <div className="text-right hidden sm:block"><p className="text-sm font-bold text-slate-800">Reni Emelisa</p><p className="text-xs text-slate-500">Guru Pembimbing</p></div>
                <div className="w-9 h-9 bg-slate-200 rounded-full flex items-center justify-center text-slate-500"><Icon name="users" size={18} /></div>
              </div>
            </header>
            <main className="flex-1 overflow-y-auto p-4 md:p-8 print:p-0 print:overflow-visible"><div className="max-w-7xl mx-auto print:max-w-none print:mx-0">{renderContent()}</div></main>
          </div>
        </div>
      );
    };

    const root = ReactDOM.createRoot(document.getElementById('root'));
    root.render(<GuruBKApp />);
  </script>
</body>
</html>