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

CREATE DATABASE IF NOT EXISTS db_presensi;
USE db_presensi;

-- =====================================
-- TABEL 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 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;
SELECT * FROM kelas;
SELECT * FROM siswa;
SELECT * FROM presensi;
SELECT * FROM v_history_presensi;