-- MODUL JADWAL MENGAJAR: aman untuk db_presensi_uji yang sudah ada.
-- Jalankan satu kali setelah backup database. Tidak menghapus/mengubah tabel lama.
CREATE TABLE IF NOT EXISTS jadwal_jam_pelajaran (
  slot_key VARCHAR(32) NOT NULL PRIMARY KEY,
  hari TINYINT UNSIGNED NOT NULL,
  jenis VARCHAR(16) NOT NULL,
  nomor TINYINT UNSIGNED DEFAULT NULL,
  label VARCHAR(90) NOT NULL,
  mulai TIME NOT NULL,
  selesai TIME NOT NULL,
  INDEX idx_jadwal_jam_hari (hari,jenis,nomor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jadwal_mengajar (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  guru_key VARCHAR(100) NOT NULL,
  hari TINYINT UNSIGNED NOT NULL,
  kelas VARCHAR(30) NOT NULL,
  mapel VARCHAR(120) NOT NULL,
  jp_mulai TINYINT UNSIGNED NOT NULL,
  jp_selesai TINYINT UNSIGNED NOT NULL,
  semester VARCHAR(24) NOT NULL DEFAULT '2026/2027-1',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_jadwal_guru_hari (guru_key,hari),
  INDEX idx_jadwal_kelas_hari (kelas,hari)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jadwal_kewajiban_hadir (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  guru_key VARCHAR(100) NOT NULL,
  hari TINYINT UNSIGNED NOT NULL,
  jenis VARCHAR(24) NOT NULL,
  jam_masuk TIME DEFAULT NULL,
  jam_pulang TIME DEFAULT NULL,
  catatan VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_jadwal_kewajiban (guru_key,hari)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Slot dari foto jadwal SMP Muslimin Samarang Semester 1 2026/2027.
-- Jam khusus Jumat, upacara Senin, dan senam Sabtu dipisahkan dari JP.
-- Menggunakan INSERT IGNORE agar impor ulang tidak menimpa jam yang sudah disesuaikan Admin.
INSERT IGNORE INTO jadwal_jam_pelajaran (slot_key,hari,jenis,nomor,label,mulai,selesai) VALUES
('1_UPACARA',1,'KEGIATAN',NULL,'Upacara','07:00:00','08:00:00'),
('1_JP1',1,'JP',1,'Jam ke-1','08:00:00','08:40:00'),
('1_JP2',1,'JP',2,'Jam ke-2','08:40:00','09:20:00'),
('1_JP3',1,'JP',3,'Jam ke-3','09:20:00','10:00:00'),
('1_JP4',1,'JP',4,'Jam ke-4','10:30:00','11:10:00'),
('1_JP5',1,'JP',5,'Jam ke-5','11:10:00','11:50:00'),
('1_JP6',1,'JP',6,'Jam ke-6','11:50:00','12:30:00'),
('1_JP7',1,'JP',7,'Jam ke-7','12:30:00','13:10:00'),
('1_ISTIRAHAT1',1,'ISTIRAHAT',NULL,'Istirahat','10:00:00','10:30:00'),
('2_JP1',2,'JP',1,'Jam ke-1','07:20:00','08:00:00'),
('2_JP2',2,'JP',2,'Jam ke-2','08:00:00','08:40:00'),
('2_JP3',2,'JP',3,'Jam ke-3','08:40:00','09:20:00'),
('2_JP4',2,'JP',4,'Jam ke-4','09:20:00','10:00:00'),
('2_JP5',2,'JP',5,'Jam ke-5','10:30:00','11:10:00'),
('2_JP6',2,'JP',6,'Jam ke-6','11:10:00','11:50:00'),
('2_JP7',2,'JP',7,'Jam ke-7','11:50:00','12:30:00'),
('2_ISTIRAHAT1',2,'ISTIRAHAT',NULL,'Istirahat','10:00:00','10:30:00'),
('3_JP1',3,'JP',1,'Jam ke-1','07:20:00','08:00:00'),
('3_JP2',3,'JP',2,'Jam ke-2','08:00:00','08:40:00'),
('3_JP3',3,'JP',3,'Jam ke-3','08:40:00','09:20:00'),
('3_JP4',3,'JP',4,'Jam ke-4','09:20:00','10:00:00'),
('3_JP5',3,'JP',5,'Jam ke-5','10:30:00','11:10:00'),
('3_JP6',3,'JP',6,'Jam ke-6','11:10:00','11:50:00'),
('3_JP7',3,'JP',7,'Jam ke-7','11:50:00','12:30:00'),
('3_ISTIRAHAT1',3,'ISTIRAHAT',NULL,'Istirahat','10:00:00','10:30:00'),
('4_JP1',4,'JP',1,'Jam ke-1','07:20:00','08:00:00'),
('4_JP2',4,'JP',2,'Jam ke-2','08:00:00','08:40:00'),
('4_JP3',4,'JP',3,'Jam ke-3','08:40:00','09:20:00'),
('4_JP4',4,'JP',4,'Jam ke-4','09:20:00','10:00:00'),
('4_JP5',4,'JP',5,'Jam ke-5','10:30:00','11:10:00'),
('4_JP6',4,'JP',6,'Jam ke-6','11:10:00','11:50:00'),
('4_JP7',4,'JP',7,'Jam ke-7','11:50:00','12:30:00'),
('4_ISTIRAHAT1',4,'ISTIRAHAT',NULL,'Istirahat','10:00:00','10:30:00'),
('5_DHUHA',5,'KEGIATAN',NULL,'Sholat Dhuha','07:00:00','07:40:00'),
('5_JP1',5,'JP',1,'Jam ke-1','07:40:00','08:20:00'),
('5_JP2',5,'JP',2,'Jam ke-2','08:20:00','09:00:00'),
('5_JP3',5,'JP',3,'Jam ke-3','09:20:00','10:00:00'),
('5_JP4',5,'JP',4,'Jam ke-4','10:00:00','10:40:00'),
('5_ISTIRAHAT1',5,'ISTIRAHAT',NULL,'Istirahat','09:00:00','09:20:00'),
('6_SENAM',6,'KEGIATAN',NULL,'Senam Bersama','07:00:00','08:00:00'),
('6_JP1',6,'JP',1,'Jam ke-1','08:00:00','08:40:00'),
('6_JP2',6,'JP',2,'Jam ke-2','08:40:00','09:20:00'),
('6_JP3',6,'JP',3,'Jam ke-3','09:50:00','10:30:00'),
('6_JP4',6,'JP',4,'Jam ke-4','10:30:00','11:10:00'),
('6_JP5',6,'JP',5,'Jam ke-5','11:10:00','11:50:00'),
('6_JP6',6,'JP',6,'Jam ke-6','11:50:00','12:30:00'),
('6_ISTIRAHAT1',6,'ISTIRAHAT',NULL,'Istirahat','09:20:00','09:50:00');