-- =========================================================
-- CMS Sekolah - Skema Database MySQL (untuk ekspor ke hosting)
-- Dibuat dari skema aplikasi (SQLite/node:sqlite) agar mudah diimpor ke MySQL/MariaDB
-- Rekomendasi: MySQL 8.0+ / MariaDB 10.4+
-- =========================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

-- ---------------------------------------------------------
-- Tabel: users
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` VARCHAR(20) NOT NULL,
  `nis` VARCHAR(100) DEFAULT NULL,
  `nisn` VARCHAR(100) DEFAULT NULL,
  `nip` VARCHAR(100) DEFAULT NULL,
  `kelas_id` INT DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `photo` TEXT DEFAULT NULL,
  `face_descriptor` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `emis_id` VARCHAR(100) DEFAULT NULL,
  `rdm_id` VARCHAR(100) DEFAULT NULL,
  `theme_color` VARCHAR(30) DEFAULT 'blue',
  `theme_mode` VARCHAR(20) DEFAULT 'light',
  `rfid_uid` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_email` (`email`),
  KEY `idx_users_role` (`role`),
  KEY `idx_users_kelas_id` (`kelas_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kelas
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kelas`;
CREATE TABLE `kelas` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nama` VARCHAR(255) NOT NULL,
  `tingkat` VARCHAR(50) DEFAULT NULL,
  `wali_id` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `emis_id` VARCHAR(100) DEFAULT NULL,
  `rdm_id` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: mapel
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `mapel`;
CREATE TABLE `mapel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nama` VARCHAR(255) NOT NULL,
  `kode` VARCHAR(100) DEFAULT NULL,
  `guru_id` INT DEFAULT NULL,
  `kelas_id` INT DEFAULT NULL,
  `deskripsi` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mapel_guru_id` (`guru_id`),
  KEY `idx_mapel_kelas_id` (`kelas_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: materi
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `materi`;
CREATE TABLE `materi` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mapel_id` INT NOT NULL,
  `judul` VARCHAR(255) NOT NULL,
  `konten` LONGTEXT DEFAULT NULL,
  `file_path` TEXT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_materi_mapel_id` (`mapel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: tugas
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `tugas`;
CREATE TABLE `tugas` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mapel_id` INT NOT NULL,
  `judul` VARCHAR(255) NOT NULL,
  `deskripsi` TEXT DEFAULT NULL,
  `due_date` DATETIME DEFAULT NULL,
  `file_path` TEXT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tugas_mapel_id` (`mapel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: pengumpulan
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `pengumpulan`;
CREATE TABLE `pengumpulan` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `tugas_id` INT NOT NULL,
  `siswa_id` INT NOT NULL,
  `file_path` TEXT DEFAULT NULL,
  `catatan` TEXT DEFAULT NULL,
  `nilai` INT DEFAULT NULL,
  `submitted_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pengumpulan_tugas_id` (`tugas_id`),
  KEY `idx_pengumpulan_siswa_id` (`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: absensi
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `absensi`;
CREATE TABLE `absensi` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `kelas_id` INT DEFAULT NULL,
  `tanggal` DATE NOT NULL,
  `jam` TIME DEFAULT NULL,
  `status` VARCHAR(20) NOT NULL,
  `metode` VARCHAR(30) DEFAULT 'manual',
  `keterangan` TEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_absensi_user_tanggal` (`user_id`,`tanggal`),
  KEY `idx_absensi_kelas_tanggal` (`kelas_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: pengumuman
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `pengumuman`;
CREATE TABLE `pengumuman` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `judul` VARCHAR(255) NOT NULL,
  `isi` LONGTEXT NOT NULL,
  `target` VARCHAR(30) DEFAULT 'semua',
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: integrasi_config
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `integrasi_config`;
CREATE TABLE `integrasi_config` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sistem` VARCHAR(100) NOT NULL,
  `config_key` VARCHAR(100) NOT NULL,
  `config_value` TEXT DEFAULT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_integrasi_sistem_key` (`sistem`,`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: sync_log
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `sync_log`;
CREATE TABLE `sync_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sistem` VARCHAR(100) NOT NULL,
  `entity` VARCHAR(100) NOT NULL,
  `arah` VARCHAR(20) DEFAULT 'push',
  `status` VARCHAR(30) NOT NULL,
  `jumlah` INT DEFAULT 0,
  `pesan` TEXT DEFAULT NULL,
  `payload` LONGTEXT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sync_log_sistem` (`sistem`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: sekolah_profil (1 baris, id=1)
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `sekolah_profil`;
CREATE TABLE `sekolah_profil` (
  `id` INT NOT NULL,
  `nama_sekolah` VARCHAR(255) DEFAULT NULL,
  `npsn` VARCHAR(50) DEFAULT NULL,
  `nss` VARCHAR(50) DEFAULT NULL,
  `alamat` TEXT DEFAULT NULL,
  `kelurahan` VARCHAR(150) DEFAULT NULL,
  `kecamatan` VARCHAR(150) DEFAULT NULL,
  `kota` VARCHAR(150) DEFAULT NULL,
  `provinsi` VARCHAR(150) DEFAULT NULL,
  `kode_pos` VARCHAR(20) DEFAULT NULL,
  `telepon` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `website` VARCHAR(255) DEFAULT NULL,
  `kepala_sekolah` VARCHAR(255) DEFAULT NULL,
  `nip_kepala` VARCHAR(100) DEFAULT NULL,
  `logo` TEXT DEFAULT NULL,
  `footer_bg` TEXT DEFAULT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `latitude` DOUBLE DEFAULT NULL,
  `longitude` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kuis
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kuis`;
CREATE TABLE `kuis` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mapel_id` INT NOT NULL,
  `judul` VARCHAR(255) NOT NULL,
  `deskripsi` TEXT DEFAULT NULL,
  `durasi_menit` INT DEFAULT 0,
  `mulai` DATETIME DEFAULT NULL,
  `selesai` DATETIME DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT 'draft',
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_kuis_mapel_id` (`mapel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kuis_soal
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kuis_soal`;
CREATE TABLE `kuis_soal` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `kuis_id` INT NOT NULL,
  `tipe` VARCHAR(30) NOT NULL DEFAULT 'pilihan_ganda',
  `pertanyaan` LONGTEXT NOT NULL,
  `poin` INT DEFAULT 1,
  `urutan` INT DEFAULT 0,
  `kunci_jawaban` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_kuis_soal_kuis_id` (`kuis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kuis_opsi
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kuis_opsi`;
CREATE TABLE `kuis_opsi` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `soal_id` INT NOT NULL,
  `label` VARCHAR(20) DEFAULT NULL,
  `teks` TEXT NOT NULL,
  `benar` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_kuis_opsi_soal_id` (`soal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kuis_attempt
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kuis_attempt`;
CREATE TABLE `kuis_attempt` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `kuis_id` INT NOT NULL,
  `siswa_id` INT NOT NULL,
  `mulai_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `selesai_at` DATETIME DEFAULT NULL,
  `skor` DOUBLE DEFAULT NULL,
  `status` VARCHAR(30) DEFAULT 'sedang_mengerjakan',
  PRIMARY KEY (`id`),
  KEY `idx_kuis_attempt_kuis_id` (`kuis_id`),
  KEY `idx_kuis_attempt_siswa_id` (`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: kuis_jawaban
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `kuis_jawaban`;
CREATE TABLE `kuis_jawaban` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `attempt_id` INT NOT NULL,
  `soal_id` INT NOT NULL,
  `jawaban` TEXT DEFAULT NULL,
  `opsi_id` INT DEFAULT NULL,
  `benar` TINYINT(1) DEFAULT NULL,
  `poin` DOUBLE DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_kuis_jawaban_attempt_id` (`attempt_id`),
  KEY `idx_kuis_jawaban_soal_id` (`soal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: hit_counter (1 baris, id=1)
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `hit_counter`;
CREATE TABLE `hit_counter` (
  `id` INT NOT NULL,
  `total` INT DEFAULT 0,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: artikel
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `artikel`;
CREATE TABLE `artikel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `judul` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) DEFAULT NULL,
  `ringkasan` TEXT DEFAULT NULL,
  `isi` LONGTEXT DEFAULT NULL,
  `gambar` TEXT DEFAULT NULL,
  `kategori` VARCHAR(150) DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT 'draft',
  `penulis_id` INT DEFAULT NULL,
  `published_at` DATETIME DEFAULT NULL,
  `views` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_artikel_slug` (`slug`),
  KEY `idx_artikel_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tabel: galeri
-- ---------------------------------------------------------
DROP TABLE IF EXISTS `galeri`;
CREATE TABLE `galeri` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `judul` VARCHAR(255) NOT NULL,
  `deskripsi` TEXT DEFAULT NULL,
  `tipe` VARCHAR(20) NOT NULL DEFAULT 'foto',
  `file_path` TEXT DEFAULT NULL,
  `video_url` TEXT DEFAULT NULL,
  `thumbnail` TEXT DEFAULT NULL,
  `kategori` VARCHAR(150) DEFAULT NULL,
  `album` VARCHAR(150) DEFAULT NULL,
  `sub_album` VARCHAR(150) DEFAULT NULL,
  `status` VARCHAR(20) DEFAULT 'publik',
  `created_by` INT DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_galeri_status` (`status`),
  KEY `idx_galeri_album` (`album`),
  KEY `idx_galeri_kategori` (`kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Data awal (seed)
-- ---------------------------------------------------------

-- Admin default (email: admin@sekolah.id, password: admin123)
INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `theme_color`, `theme_mode`, `created_at`)
VALUES (1, 'Administrator', 'admin@sekolah.id', '$2a$10$LEsb1S/0Qq/PLnXlxW1g4Olcx12DCfrpArZx3AfOu5mFCyCR03nNO', 'admin', 'blue', 'light', NOW())
ON DUPLICATE KEY UPDATE `id` = `id`;

-- Profil sekolah default
INSERT INTO `sekolah_profil` (`id`, `nama_sekolah`, `updated_at`)
VALUES (1, 'CMS Sekolah', NOW())
ON DUPLICATE KEY UPDATE `id` = `id`;

-- Hit counter default
INSERT INTO `hit_counter` (`id`, `total`, `updated_at`)
VALUES (1, 0, NOW())
ON DUPLICATE KEY UPDATE `id` = `id`;

SET foreign_key_checks = 1;

-- =========================================================
-- CATATAN
-- - File ini berisi SKEMA + data awal (admin/profil/hit counter).
--   Untuk memindahkan data SQLite yang sudah ada, ekspor data dari
--   database/sekolah.db lalu impor/konversi ke MySQL terpisah.
-- - Aplikasi saat ini memakai SQLite (node:sqlite) di config/database.js.
--   Untuk menjalankan aplikasi langsung di MySQL, perlu penyesuaian
--   koneksi/query (driver & sintaks) pada kode aplikasi.
-- - Folder upload (public/uploads, public/uploads/artikel, public/uploads/galeri)
--   tetap perlu disalin ke hosting.
-- - Login default: admin@sekolah.id / admin123 (segera ganti setelah login).
-- =========================================================