-- ============================================================
-- SIAKAD - Sistem Informasi Akademik Sekolah
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = "+07:00";

-- USE `siakad`; -- Dihapus: sesuaikan nama database di cPanel sebelum import

-- ============================================================
-- Hapus tabel lama jika ada (urutan terbalik karena FK)
-- ============================================================
DROP TABLE IF EXISTS `nilai`;
DROP TABLE IF EXISTS `wali`;
DROP TABLE IF EXISTS `orang_tua`;
DROP TABLE IF EXISTS `alamat_siswa`;
DROP TABLE IF EXISTS `siswa`;
DROP TABLE IF EXISTS `mata_pelajaran`;
DROP TABLE IF EXISTS `kelas`;
DROP TABLE IF EXISTS `guru`;
DROP TABLE IF EXISTS `pengaturan`;
DROP TABLE IF EXISTS `users`;

-- ============================================================
-- Tabel: users
-- ============================================================
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `nama_lengkap` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `role` enum('admin','operator','guru') NOT NULL DEFAULT 'operator',
  `foto` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: pengaturan
-- ============================================================
CREATE TABLE `pengaturan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama_sekolah` varchar(200) NOT NULL DEFAULT 'Nama Sekolah',
  `npsn` varchar(20) DEFAULT NULL,
  `alamat` text DEFAULT NULL,
  `telepon` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `kepala_sekolah` varchar(100) DEFAULT NULL,
  `nip_kepala` varchar(30) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `tahun_ajaran` varchar(20) DEFAULT '2024/2025',
  `semester` enum('Ganjil','Genap') DEFAULT 'Ganjil',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: guru
-- ============================================================
CREATE TABLE `guru` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nip` varchar(30) DEFAULT NULL,
  `nama` varchar(100) NOT NULL,
  `jenis_kelamin` enum('Laki-laki','Perempuan') NOT NULL,
  `tempat_lahir` varchar(50) DEFAULT NULL,
  `tanggal_lahir` date DEFAULT NULL,
  `agama` varchar(20) DEFAULT NULL,
  `pendidikan` varchar(50) DEFAULT NULL,
  `jurusan_pendidikan` varchar(100) DEFAULT NULL,
  `mata_pelajaran` varchar(100) DEFAULT NULL,
  `status_kepegawaian` enum('PNS','GTT','GTY','Honorer') DEFAULT 'GTT',
  `no_hp` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `alamat` text DEFAULT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `tahun_masuk` year DEFAULT NULL,
  `status` enum('Aktif','Tidak Aktif') DEFAULT 'Aktif',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: kelas
-- ============================================================
CREATE TABLE `kelas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama_kelas` varchar(50) NOT NULL,
  `tingkat` varchar(10) NOT NULL,
  `jurusan` varchar(50) DEFAULT NULL,
  `wali_kelas_id` int(11) DEFAULT NULL,
  `tahun_ajaran` varchar(20) DEFAULT '2024/2025',
  `kapasitas` int(11) DEFAULT 30,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_kelas_guru` (`wali_kelas_id`),
  CONSTRAINT `fk_kelas_guru` FOREIGN KEY (`wali_kelas_id`) REFERENCES `guru` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: siswa
-- ============================================================
CREATE TABLE `siswa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no` int(11) DEFAULT NULL,
  `nis` varchar(20) DEFAULT NULL,
  `nisn` varchar(20) DEFAULT NULL,
  `nik` varchar(20) DEFAULT NULL,
  `nama` varchar(100) NOT NULL,
  `jenis_kelamin` enum('Laki-laki','Perempuan') NOT NULL,
  `kelas_id` int(11) DEFAULT NULL,
  `agama` varchar(20) DEFAULT NULL,
  `tempat_lahir` varchar(50) DEFAULT NULL,
  `tanggal_lahir` date DEFAULT NULL,
  `anak_ke` int(11) DEFAULT NULL,
  `tgl_masuk` date DEFAULT NULL,
  `asal_sekolah` varchar(100) DEFAULT NULL,
  `status` enum('Aktif','Tidak Aktif','Lulus','Pindah','Keluar','Gaib') DEFAULT 'Aktif',
  `foto` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_siswa_kelas` (`kelas_id`),
  CONSTRAINT `fk_siswa_kelas` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: alamat_siswa
-- ============================================================
CREATE TABLE `alamat_siswa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siswa_id` int(11) NOT NULL,
  `alamat` text DEFAULT NULL,
  `rt` varchar(5) DEFAULT NULL,
  `rw` varchar(5) DEFAULT NULL,
  `dusun` varchar(50) DEFAULT NULL,
  `kelurahan` varchar(50) DEFAULT NULL,
  `kecamatan` varchar(50) DEFAULT NULL,
  `kabupaten` varchar(50) DEFAULT NULL,
  `provinsi` varchar(50) DEFAULT NULL,
  `kode_pos` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_alamat_siswa` (`siswa_id`),
  CONSTRAINT `fk_alamat_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: orang_tua
-- ============================================================
CREATE TABLE `orang_tua` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siswa_id` int(11) NOT NULL,
  `nama_ayah` varchar(100) DEFAULT NULL,
  `nama_ibu` varchar(100) DEFAULT NULL,
  `pekerjaan_ayah` varchar(100) DEFAULT NULL,
  `pekerjaan_ibu` varchar(100) DEFAULT NULL,
  `alamat_orang_tua` text DEFAULT NULL,
  `no_hp_ayah` varchar(20) DEFAULT NULL,
  `no_hp_ibu` varchar(20) DEFAULT NULL,
  `penghasilan` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ortu_siswa` (`siswa_id`),
  CONSTRAINT `fk_ortu_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: wali
-- ============================================================
CREATE TABLE `wali` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siswa_id` int(11) NOT NULL,
  `nama_wali` varchar(100) DEFAULT NULL,
  `alamat_wali` text DEFAULT NULL,
  `pekerjaan_wali` varchar(100) DEFAULT NULL,
  `no_hp_wali` varchar(20) DEFAULT NULL,
  `hubungan` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_wali_siswa` (`siswa_id`),
  CONSTRAINT `fk_wali_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: mata_pelajaran
-- ============================================================
CREATE TABLE `mata_pelajaran` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kode` varchar(10) DEFAULT NULL,
  `nama` varchar(100) NOT NULL,
  `guru_id` int(11) DEFAULT NULL,
  `kkm` int(11) DEFAULT 70,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabel: nilai
-- ============================================================
CREATE TABLE `nilai` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siswa_id` int(11) NOT NULL,
  `kelas_id` int(11) NOT NULL,
  `mapel_id` int(11) NOT NULL,
  `tahun_ajaran` varchar(20) DEFAULT '2024/2025',
  `semester_no` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1-6',
  `nilai_rapor` decimal(5,2) DEFAULT NULL,
  `predikat` varchar(5) DEFAULT NULL,
  `keterangan` varchar(200) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_nilai` (`siswa_id`,`kelas_id`,`mapel_id`,`tahun_ajaran`,`semester_no`),
  KEY `fk_nilai_siswa` (`siswa_id`),
  KEY `fk_nilai_kelas` (`kelas_id`),
  KEY `fk_nilai_mapel` (`mapel_id`),
  CONSTRAINT `fk_nilai_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_nilai_kelas` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_nilai_mapel` FOREIGN KEY (`mapel_id`) REFERENCES `mata_pelajaran` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DATA AWAL
-- ============================================================

-- User admin
-- Password akan direset via reset_password.php setelah install
-- Default password: admin123 (reset jika tidak bisa login)
INSERT INTO `users` (`username`, `password`, `nama_lengkap`, `email`, `role`) VALUES
('admin', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77bXm6', 'Administrator', 'admin@siakad.sch.id', 'admin');

-- Pengaturan sekolah
INSERT INTO `pengaturan` (`nama_sekolah`, `npsn`, `alamat`, `telepon`, `kepala_sekolah`, `tahun_ajaran`, `semester`) VALUES
('SMP Negeri 1 Contoh', '12345678', 'Jl. Pendidikan No. 1, Kota Contoh', '(021) 1234567', 'Drs. Budi Santoso, M.Pd', '2024/2025', 'Ganjil');

-- Data Guru
INSERT INTO `guru` (`nip`, `nama`, `jenis_kelamin`, `tempat_lahir`, `tanggal_lahir`, `agama`, `mata_pelajaran`, `status_kepegawaian`, `no_hp`, `status`) VALUES
('197001011995011001', 'Drs. Ahmad Fauzi', 'Laki-laki', 'Jakarta', '1970-01-01', 'Islam', 'Matematika', 'PNS', '081234567890', 'Aktif'),
('197505152000122001', 'Siti Rahayu, S.Pd', 'Perempuan', 'Bandung', '1975-05-15', 'Islam', 'Bahasa Indonesia', 'PNS', '081234567891', 'Aktif'),
('198003202005011002', 'Budi Prasetyo, S.Pd', 'Laki-laki', 'Surabaya', '1980-03-20', 'Islam', 'IPA', 'GTT', '081234567892', 'Aktif'),
('198510102010012003', 'Dewi Lestari, S.Pd', 'Perempuan', 'Yogyakarta', '1985-10-10', 'Islam', 'IPS', 'GTT', '081234567893', 'Aktif'),
('199001012015011004', 'Rizky Pratama, S.Pd', 'Laki-laki', 'Semarang', '1990-01-01', 'Islam', 'Bahasa Inggris', 'Honorer', '081234567894', 'Aktif');

-- Data Kelas
INSERT INTO `kelas` (`nama_kelas`, `tingkat`, `wali_kelas_id`, `tahun_ajaran`) VALUES
('VII A', 'VII', 1, '2024/2025'),
('VII B', 'VII', 2, '2024/2025'),
('VIII A', 'VIII', 3, '2024/2025'),
('VIII B', 'VIII', 4, '2024/2025'),
('IX A', 'IX', 5, '2024/2025'),
('IX B', 'IX', NULL, '2024/2025');

-- Data Siswa
INSERT INTO `siswa` (`no`, `nis`, `nisn`, `nik`, `nama`, `jenis_kelamin`, `kelas_id`, `agama`, `tempat_lahir`, `tanggal_lahir`, `anak_ke`, `tgl_masuk`, `asal_sekolah`, `status`) VALUES
(1, '2024001', '0012345678', '3201010101100001', 'Ahmad Rizki Pratama', 'Laki-laki', 1, 'Islam', 'Jakarta', '2011-01-01', 1, '2024-07-15', 'SD Negeri 1', 'Aktif'),
(2, '2024002', '0012345679', '3201010101100002', 'Siti Nurhaliza', 'Perempuan', 1, 'Islam', 'Bandung', '2011-02-14', 2, '2024-07-15', 'SD Negeri 2', 'Aktif'),
(3, '2024003', '0012345680', '3201010101100003', 'Budi Santoso', 'Laki-laki', 2, 'Islam', 'Surabaya', '2011-03-20', 1, '2024-07-15', 'SD Negeri 3', 'Aktif'),
(4, '2024004', '0012345681', '3201010101100004', 'Dewi Anggraini', 'Perempuan', 2, 'Kristen', 'Medan', '2011-04-05', 3, '2024-07-15', 'SD Swasta 1', 'Aktif'),
(5, '2024005', '0012345682', '3201010101100005', 'Eko Wahyudi', 'Laki-laki', 3, 'Islam', 'Semarang', '2010-05-10', 2, '2023-07-15', 'SD Negeri 4', 'Aktif'),
(6, '2024006', '0012345683', '3201010101100006', 'Fitri Handayani', 'Perempuan', 3, 'Islam', 'Yogyakarta', '2010-06-15', 1, '2023-07-15', 'SD Negeri 5', 'Aktif'),
(7, '2024007', '0012345684', '3201010101100007', 'Galih Permana', 'Laki-laki', 4, 'Islam', 'Malang', '2010-07-20', 2, '2023-07-15', 'SD Negeri 6', 'Aktif'),
(8, '2024008', '0012345685', '3201010101100008', 'Hani Safitri', 'Perempuan', 4, 'Islam', 'Bogor', '2010-08-25', 1, '2023-07-15', 'SD Swasta 2', 'Aktif'),
(9, '2024009', '0012345686', '3201010101100009', 'Irfan Maulana', 'Laki-laki', 5, 'Islam', 'Depok', '2009-09-01', 3, '2022-07-15', 'SD Negeri 7', 'Aktif'),
(10, '2024010', '0012345687', '3201010101100010', 'Juwita Rahayu', 'Perempuan', 5, 'Katolik', 'Bekasi', '2009-10-10', 2, '2022-07-15', 'SD Negeri 8', 'Aktif');

-- Alamat Siswa
INSERT INTO `alamat_siswa` (`siswa_id`, `alamat`, `rt`, `rw`, `dusun`, `kelurahan`, `kecamatan`, `kabupaten`, `provinsi`) VALUES
(1, 'Jl. Mawar No. 1', '001', '002', 'Dusun Mawar', 'Kel. Mawar', 'Kec. Mawar', 'Kota Contoh', 'Jawa Barat'),
(2, 'Jl. Melati No. 2', '003', '004', 'Dusun Melati', 'Kel. Melati', 'Kec. Melati', 'Kota Contoh', 'Jawa Barat'),
(3, 'Jl. Anggrek No. 3', '005', '006', 'Dusun Anggrek', 'Kel. Anggrek', 'Kec. Anggrek', 'Kota Contoh', 'Jawa Barat'),
(4, 'Jl. Dahlia No. 4', '007', '008', 'Dusun Dahlia', 'Kel. Dahlia', 'Kec. Dahlia', 'Kota Contoh', 'Jawa Barat'),
(5, 'Jl. Kenanga No. 5', '009', '010', 'Dusun Kenanga', 'Kel. Kenanga', 'Kec. Kenanga', 'Kota Contoh', 'Jawa Barat');

-- Orang Tua
INSERT INTO `orang_tua` (`siswa_id`, `nama_ayah`, `nama_ibu`, `pekerjaan_ayah`, `pekerjaan_ibu`, `alamat_orang_tua`) VALUES
(1, 'Pratama Jaya', 'Sari Dewi', 'Wiraswasta', 'Ibu Rumah Tangga', 'Jl. Mawar No. 1'),
(2, 'Halim Santoso', 'Nurul Hidayah', 'PNS', 'Guru', 'Jl. Melati No. 2'),
(3, 'Santoso Budi', 'Wati Rahayu', 'Petani', 'Pedagang', 'Jl. Anggrek No. 3'),
(4, 'Angga Wijaya', 'Lestari Dewi', 'Karyawan Swasta', 'Ibu Rumah Tangga', 'Jl. Dahlia No. 4'),
(5, 'Wahyu Eko', 'Handayani', 'TNI', 'Ibu Rumah Tangga', 'Jl. Kenanga No. 5');

-- Wali Siswa
INSERT INTO `wali` (`siswa_id`, `nama_wali`, `alamat_wali`, `pekerjaan_wali`, `hubungan`) VALUES
(1, 'Paman Ahmad', 'Jl. Mawar No. 10', 'Wiraswasta', 'Paman'),
(2, 'Bibi Sari', 'Jl. Melati No. 20', 'Pedagang', 'Bibi');

-- Mata Pelajaran (nama singkat sesuai kode)
INSERT INTO `mata_pelajaran` (`kode`, `nama`, `guru_id`, `kkm`) VALUES
('BIND',    'BIND',    2,    75),
('BING',    'BING',    5,    70),
('IPA',     'IPA',     3,    70),
('IPS',     'IPS',     4,    70),
('MTK',     'MTK',     1,    70),
('PAI',     'PAI',     NULL, 75),
('PJOK',    'PJOK',    NULL, 70),
('PPKN',    'PPKN',    NULL, 75),
('PRAKARYA','PRAKARYA',NULL, 70),
('TIK',     'TIK',     NULL, 70),
('SWAJAIB', 'SWAJAIB', NULL, 70),
('FIQIH',   'FIQIH',   NULL, 75),
('BK',      'BK',      NULL, 70),
('BDAERAH', 'BDAERAH', NULL, 70),
('BTQ',     'BTQ',     NULL, 75),
('BARAB',   'BARAB',   NULL, 75);

-- Nilai Rapor
INSERT INTO `nilai` (`siswa_id`, `kelas_id`, `mapel_id`, `tahun_ajaran`, `semester_no`, `nilai_rapor`, `predikat`) VALUES
(1, 1, 1, '2024/2025', 1, 80.00, 'B'),
(1, 1, 2, '2024/2025', 1, 85.00, 'A'),
(1, 1, 3, '2024/2025', 1, 75.00, 'B'),
(2, 1, 1, '2024/2025', 1, 90.00, 'A'),
(2, 1, 2, '2024/2025', 1, 87.00, 'A'),
(5, 3, 1, '2024/2025', 3, 70.00, 'B'),
(5, 3, 2, '2024/2025', 3, 72.00, 'B');

SET FOREIGN_KEY_CHECKS = 1;
