CREATE DATABASE IF NOT EXISTS absensi_rfid_smk;
USE absensi_rfid_smk;

CREATE TABLE siswa (
    id_siswa INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    kelas VARCHAR(20) NOT NULL,
    angkatan YEAR NOT NULL,
    uid_rfid VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE absensi (
    id_absen INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20),
    tanggal DATE NOT NULL,
    jam TIME NOT NULL,
    status ENUM('Masuk', 'Pulang', 'Izin', 'Sakit', 'Terlambat') NOT NULL,
    FOREIGN KEY (nis) REFERENCES siswa(nis)
);

CREATE TABLE pelanggaran (
    id_pelanggaran INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20),
    tanggal DATE NOT NULL,
    jenis_pelanggaran VARCHAR(255) NOT NULL,
    poin INT DEFAULT 0,
    keterangan TEXT,
    FOREIGN KEY (nis) REFERENCES siswa(nis)
);

CREATE TABLE log_admin (
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    waktu_login DATETIME,
    waktu_logout DATETIME
);
-- View rekap absensi
CREATE VIEW rekap_absensi AS
SELECT 
    s.nis, s.nama, s.kelas,
    COUNT(CASE WHEN a.status = 'Masuk' THEN 1 END) AS hadir,
    COUNT(CASE WHEN a.status = 'Pulang' THEN 1 END) AS pulang,
    COUNT(CASE WHEN a.status = 'Izin' THEN 1 END) AS izin,
    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) AS sakit,
    COUNT(CASE WHEN a.status = 'Terlambat' THEN 1 END) AS terlambat
FROM siswa s
LEFT JOIN absensi a ON s.nis = a.nis
GROUP BY s.nis;

-- View rekap pelanggaran
CREATE VIEW rekap_pelanggaran AS
SELECT 
    s.nis, s.nama, s.kelas,
    COUNT(p.id_pelanggaran) AS jumlah_pelanggaran,
    SUM(p.poin) AS total_poin
FROM siswa s
LEFT JOIN pelanggaran p ON s.nis = p.nis
GROUP BY s.nis;
