-- =====================================
-- DATABASE PRESENSI SISWA
-- MIS NURUL AZMI PADANG TUALANG
-- (Redesign: Guru-based auth)
-- =====================================

CREATE DATABASE IF NOT EXISTS db_presensi;
USE db_presensi;

-- =====================================
-- TABEL KELAS
-- =====================================

DROP TABLE IF EXISTS presensi;
DROP TABLE IF EXISTS siswa;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS kelas;

CREATE TABLE kelas (
    id_kelas INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(20) NOT NULL
);

INSERT INTO kelas (nama_kelas) VALUES
('Kelas 1'),
('Kelas 2'),
('Kelas 3'),
('Kelas 4'),
('Kelas 5'),
('Kelas 6');

-- =====================================
-- TABEL USERS (GURU / WALI KELAS)
-- =====================================

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    id_kelas INT,

    CONSTRAINT fk_users_kelas
    FOREIGN KEY (id_kelas)
    REFERENCES kelas(id_kelas)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

INSERT INTO users (username, password, nama_lengkap, id_kelas) VALUES
('pita',     MD5('guru123'), 'Pita Hidayah, S.Pd',         1),
('putri',    MD5('guru123'), 'Putri Yolanda, S.Pd',        2),
('nanalia',  MD5('guru123'), 'Nanalia, S.Pd',              3),
('purwani',  MD5('guru123'), 'Purwani, S.Pd',              4),
('faradila', MD5('guru123'), 'Faradila Amalia, S.Pd',      5),
('bella',    MD5('guru123'), 'Bella Adlia Habibah, S.Pd',  6);

-- =====================================
-- TABEL SISWA
-- =====================================

CREATE TABLE siswa (
    nisn VARCHAR(20) PRIMARY KEY,
    id_kelas INT NOT NULL,
    nama VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('Laki-laki','Perempuan'),
    alamat TEXT,

    CONSTRAINT fk_siswa_kelas
    FOREIGN KEY (id_kelas)
    REFERENCES kelas(id_kelas)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

INSERT INTO siswa VALUES
('3203389233',1,'BILQIS HUMAIRAH','Perempuan','Dusun IV Tambak Rejo'),
('3190875884',1,'FAISYA ULJANNAH RATIFA','Perempuan','Dusun IV Tambak Rejo'),
('0000000000',1,'JOVANKA','Laki-laki','Dusun IV Tambak Rejo'),
('3187113643',2,'FEBBY ARSYLA','Perempuan','Dusun IV Tambak Rejo'),
('3185827821',2,'HAFIS HERLAMBANG','Laki-laki','Dusun IV Tambak Rejo'),
('3186624301',2,'MIFTAHUL JANNAH','Perempuan','Dusun IV Tambak Rejo'),
('3166122574',3,'ARYA PRAMANA','Laki-laki','Dusun IV Tambak Rejo'),
('3167635595',3,'DISA NAFIZA','Perempuan','Teladan'),
('3164896935',3,'FEBIOLA KANZA KAMILA','Perempuan','Dusun IV Tambak Rejo'),
('3124871623',3,'M. AZIZ A RASIT','Laki-laki','Dusun IV Tambak Rejo'),
('0131609493',4,'ABDAN HAMELAN','Laki-laki','Dusun IV Tambak Rejo'),
('0138143094',4,'ABDUL HUSAEN','Laki-laki','Dusun IV Tambak Rejo'),
('3160104963',4,'ANJUN FIKRI PRATAMA SETIAWAN','Laki-laki','Dusun IV Tambak Rejo'),
('3151271201',5,'ALANG ISKANDAR SHAH AB ROKAN','Laki-laki','Dusun V Jatimulyo Pasar 6'),
('3156781226',5,'ALIN TRISKA DEWI','Perempuan','Dusun IV Tambak Rejo'),
('3145174635',5,'MUHAMMAD RIVALDI','Laki-laki','Dusun IV Tambak Rejo'),
('3147487188',6,'ABDI PRASETYO','Laki-laki','Dusun IV Tambak Rejo'),
('3121202549',6,'ADELIA SYAHFITRI','Perempuan','Dusun IV Tambak Rejo'),
('3147533248',6,'ADELLIA PRATIWI NASUTION','Perempuan','Dusun IV Tambak Rejo');

-- =====================================
-- TABEL PRESENSI
-- =====================================

CREATE TABLE presensi (
    id_presensi INT AUTO_INCREMENT PRIMARY KEY,
    nisn VARCHAR(20) NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM('Hadir','Izin','Sakit','Alpha') NOT NULL,
    id_user INT,

    UNIQUE KEY unique_presensi (nisn, tanggal),

    CONSTRAINT fk_presensi_siswa
    FOREIGN KEY (nisn)
    REFERENCES siswa(nisn)
    ON UPDATE CASCADE
    ON DELETE CASCADE,

    CONSTRAINT fk_presensi_user
    FOREIGN KEY (id_user)
    REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

-- =====================================
-- DATA PRESENSI CONTOH
-- =====================================

INSERT INTO presensi (nisn, tanggal, status, id_user) VALUES
('3203389233','2026-06-01','Hadir', 1),
('3190875884','2026-06-01','Hadir', 1),
('0000000000','2026-06-01','Alpha', 1),
('3187113643','2026-06-01','Hadir', 2),
('3185827821','2026-06-01','Hadir', 2),
('3186624301','2026-06-01','Hadir', 2),
('3166122574','2026-06-01','Hadir', 3),
('3167635595','2026-06-01','Hadir', 3),
('3164896935','2026-06-01','Hadir', 3),
('3124871623','2026-06-01','Hadir', 3),
('0131609493','2026-06-01','Hadir', 4),
('0138143094','2026-06-01','Hadir', 4),
('3160104963','2026-06-01','Hadir', 4),
('3151271201','2026-06-01','Hadir', 5),
('3156781226','2026-06-01','Hadir', 5),
('3145174635','2026-06-01','Hadir', 5),
('3147487188','2026-06-01','Hadir', 6),
('3121202549','2026-06-01','Hadir', 6),
('3147533248','2026-06-01','Hadir', 6);

-- =====================================
-- VIEW HISTORY PRESENSI
-- =====================================

DROP VIEW IF EXISTS v_history_presensi;

CREATE VIEW v_history_presensi AS
SELECT
    p.id_presensi,
    p.tanggal,
    s.nisn,
    s.nama,
    s.id_kelas,
    k.nama_kelas,
    p.status,
    p.id_user,
    u.nama_lengkap AS nama_guru
FROM presensi p
JOIN siswa s ON p.nisn = s.nisn
JOIN kelas k ON s.id_kelas = k.id_kelas
LEFT JOIN users u ON p.id_user = u.id;

-- =====================================
-- QUERY TEST
-- =====================================

SELECT * FROM users;
SELECT * FROM kelas;
SELECT * FROM siswa;
SELECT * FROM presensi;
SELECT * FROM v_history_presensi;