-- =====================================================================
-- SIMAP — Tabel pendukung aplikasi siswa
--
--   mysql -u root -p simap_db < database/11_aplikasi_siswa.sql
--
-- Dua hal yang dibutuhkan aplikasi siswa tetapi belum ada di skema:
--   1. catatan materi yang sudah dibaca, supaya poin membaca hanya
--      diberikan sekali per materi
--   2. penanda halaman buku, supaya siswa dapat menandai bagian penting
--      dan kembali ke sana tanpa mencari-cari
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS materi_baca (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  materi_id INT UNSIGNED NOT NULL,
  siswa_id  INT UNSIGNED NOT NULL,
  dibaca_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Kunci unik inilah yang menjaga poin tidak berlipat saat materi
  -- yang sama dibuka berulang kali
  UNIQUE KEY uq_materi_baca (materi_id, siswa_id),
  CONSTRAINT fk_mb_materi FOREIGN KEY (materi_id) REFERENCES materi(id) ON DELETE CASCADE,
  CONSTRAINT fk_mb_siswa  FOREIGN KEY (siswa_id)  REFERENCES siswa(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS penanda_baca (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  siswa_id   INT UNSIGNED NOT NULL,
  buku_id    INT UNSIGNED NOT NULL,
  halaman    INT UNSIGNED NOT NULL,
  catatan    VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_penanda (siswa_id, buku_id),
  CONSTRAINT fk_pb_siswa FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  CONSTRAINT fk_pb_buku  FOREIGN KEY (buku_id)  REFERENCES buku(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------- KOLEKSI AWAL
-- Perpustakaan yang kosong membuat aplikasi siswa terasa mati saat pertama
-- dibuka, jadi disertakan koleksi awal. Judul-judul di bawah dipilih yang
-- lazim ada di perpustakaan SMP; sekolah tinggal menyunting atau menghapus.
--
-- Kolom file_ebook sengaja dikosongkan karena berkas PDF-nya tidak ikut
-- dalam paket. Unggah berkasnya lewat menu Perpustakaan, lalu buku itu
-- langsung dapat dibaca di dalam aplikasi siswa.

INSERT IGNORE INTO buku
 (kode_buku, judul, pengarang, penerbit, tahun_terbit, jumlah_halaman,
  kategori_id, jenis, jenjang_baca, sinopsis, poin_baca,
  jumlah_eks, jumlah_tersedia, lokasi_rak, status)
VALUES
 ('BK-001','Laskar Pelangi','Andrea Hirata','Bentang Pustaka',2005,529,9,'KEDUANYA','UMUM',
  'Kisah sepuluh anak Belitung yang gigih bersekolah di tengah keterbatasan.',30,3,3,'Rak A-1','AKTIF'),
 ('BK-002','Bumi Manusia','Pramoedya Ananta Toer','Lentera Dipantara',1980,535,9,'FISIK','UMUM',
  'Perjalanan Minke menghadapi ketidakadilan pada masa kolonial.',35,2,2,'Rak A-2','AKTIF'),
 ('BK-003','Sang Pemimpi','Andrea Hirata','Bentang Pustaka',2006,292,9,'KEDUANYA','UMUM',
  'Lanjutan kisah Ikal dan Arai yang mengejar mimpi hingga ke Eropa.',25,3,3,'Rak A-1','AKTIF'),
 ('BK-004','Fisika untuk SMP Kelas VIII','Tim Penyusun','Erlangga',2022,248,6,'KEDUANYA','UMUM',
  'Materi getaran, gelombang, cahaya, dan tekanan untuk kelas VIII.',20,5,5,'Rak B-1','AKTIF'),
 ('BK-005','Matematika Menyenangkan','Ridwan Abdullah','Grasindo',2021,196,6,'KEDUANYA','UMUM',
  'Konsep aljabar dan geometri dijelaskan lewat contoh sehari-hari.',20,4,4,'Rak B-2','AKTIF'),
 ('BK-006','Atlas Sejarah Indonesia','Tim Redaksi','Gramedia',2020,164,4,'FISIK','UMUM',
  'Peta dan lini masa peristiwa penting sejarah Indonesia.',15,2,2,'Rak C-1','AKTIF'),
 ('BK-007','Kisah 25 Nabi dan Rasul','H. Abdul Karim','Pustaka Amani',2019,212,3,'KEDUANYA','UMUM',
  'Riwayat para nabi dan rasul beserta teladan akhlaknya.',20,4,4,'Rak D-1','AKTIF'),
 ('BK-008','Rahasia Tubuh Manusia','dr. Siti Nurhaliza','Elex Media',2021,148,7,'DIGITAL','UMUM',
  'Cara kerja organ tubuh dijelaskan dengan bahasa yang mudah dipahami.',20,0,0,NULL,'AKTIF'),
 ('BK-009','Kamus Bahasa Inggris Pelajar','Tim Penyusun','Balai Pustaka',2018,624,5,'FISIK','UMUM',
  'Kosakata dan contoh kalimat untuk jenjang SMP dan SMA.',10,6,6,'Rak E-1','AKTIF'),
 ('BK-010','Antologi Puisi Nusantara','Berbagai Penyair','Balai Pustaka',2017,132,9,'KEDUANYA','UMUM',
  'Kumpulan puisi penyair Indonesia dari berbagai zaman.',20,2,2,'Rak A-3','AKTIF');

-- ------------------------------------------- CONTOH UJIAN DARING
-- Tanpa contoh ujian, guru harus menyusun bank soal dari nol hanya untuk
-- mengetahui apakah fitur ujiannya berjalan. Satu paket kecil di bawah ini
-- cukup untuk mencoba alur lengkap: kerjakan, koreksi otomatis, lihat hasil.

INSERT IGNORE INTO bank_soal
 (id, mapel_id, tingkat, fase, guru_id, tipe, level_kognitif, tingkat_kesulitan,
  pertanyaan, opsi, kunci, pembahasan, skor, status)
VALUES
 (1, (SELECT id FROM mapel WHERE kode='BIN-SMP'), 7, 'D', 1, 'PG', 'C2', 'SEDANG',
  'Kalimat manakah yang termasuk kalimat deskripsi?',
  '["Ayah pergi ke pasar pagi tadi.","Rumah itu bercat biru muda dengan pagar kayu rendah.","Mengapa kamu terlambat?","Tutup pintunya!"]',
  'B',
  'Kalimat deskripsi menggambarkan ciri suatu objek sehingga pembaca dapat membayangkannya.',
  1, 'AKTIF'),
 (2, (SELECT id FROM mapel WHERE kode='BIN-SMP'), 7, 'D', 1, 'PG', 'C1', 'MUDAH',
  'Struktur teks deskripsi yang benar adalah…',
  '["Identifikasi - deskripsi bagian - simpulan","Orientasi - komplikasi - resolusi","Tesis - argumen - penegasan","Abstrak - orientasi - krisis"]',
  'A',
  'Teks deskripsi diawali identifikasi objek, dirinci pada deskripsi bagian, lalu ditutup simpulan.',
  1, 'AKTIF'),
 (3, (SELECT id FROM mapel WHERE kode='BIN-SMP'), 7, 'D', 1, 'PG', 'C3', 'SEDANG',
  'Kata yang paling tepat melengkapi kalimat "Air terjun itu ___ dari tebing setinggi tiga puluh meter." adalah…',
  '["menetes","tergenang","tercurah","merembes"]',
  'C',
  'Kata tercurah menggambarkan air yang jatuh deras dari ketinggian.',
  1, 'AKTIF'),
 (4, (SELECT id FROM mapel WHERE kode='BIN-SMP'), 7, 'D', 1, 'URAIAN', 'C4', 'SULIT',
  'Tuliskan tiga kalimat deskripsi tentang ruang kelasmu. Gunakan kata sifat yang berbeda pada setiap kalimat.',
  NULL, NULL,
  'Jawaban benar bila memuat tiga kalimat, menggambarkan objek nyata di kelas, dan memakai kata sifat berbeda.',
  3, 'AKTIF');

INSERT IGNORE INTO ujian
 (id, tahun_ajaran_id, kode, judul, jenis, kategori_nilai, mapel_id, guru_id, tingkat,
  deskripsi, mulai, selesai, durasi_menit, acak_soal, acak_opsi, layar_penuh,
  maks_pelanggaran, tampil_hasil, tampil_pembahasan, maks_percobaan, kkm, status)
SELECT 1, (SELECT id FROM tahun_ajaran WHERE is_aktif = 1), 'UH-BIN-01',
       'Ulangan Harian: Teks Deskripsi', 'ULANGAN_HARIAN', 'FORMATIF',
       (SELECT id FROM mapel WHERE kode='BIN-SMP'), 1, 7,
       'Empat soal tentang ciri dan struktur teks deskripsi.',
       NOW(), NOW() + INTERVAL 30 DAY, 45, 1, 1, 1, 3, 1, 1, 1, 75, 'BERLANGSUNG';

INSERT IGNORE INTO ujian_soal (ujian_id, soal_id, urutan, bobot)
VALUES (1,1,1,1), (1,2,2,1), (1,3,3,1), (1,4,4,3);

-- Ujian berlaku untuk seluruh kelas pada tingkatnya
INSERT IGNORE INTO ujian_kelas (ujian_id, kelas_id)
SELECT 1, id FROM kelas WHERE tingkat = 7;

-- Satu buku dalam keadaan sedang dibaca, supaya bagian "Sedang dibaca"
-- pada aplikasi siswa tidak kosong saat pertama dibuka
INSERT IGNORE INTO ebook_progres
 (buku_id, siswa_id, halaman_terakhir, total_halaman, persen, menit_total, selesai, terakhir_at)
SELECT 3, s.id, 88, 292, 30, 45, 0, NOW()
  FROM siswa s WHERE s.status = 'AKTIF' LIMIT 3;

SELECT 'tabel aplikasi siswa siap' AS info,
       (SELECT COUNT(*) FROM information_schema.tables
         WHERE table_schema = DATABASE()
           AND table_name IN ('materi_baca','penanda_baca')) AS tabel,
       (SELECT COUNT(*) FROM buku) AS koleksi_buku,
       (SELECT COUNT(*) FROM ujian) AS ujian_contoh;