-- =========================================
-- DATABASE: db_absensi_digital (SKEMA LENGKAP)
-- =========================================
CREATE DATABASE IF NOT EXISTS db_absensi_digital;
USE db_absensi_digital;

-- =========================================
-- 1. TABEL USERS (Admin / Guru)
-- =========================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'guru') NOT NULL DEFAULT 'guru',
    failed_attempts INT NOT NULL DEFAULT 0,
    locked_until DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================================
-- 2. TABEL KELAS
-- =========================================
CREATE TABLE kelas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_kelas VARCHAR(50) NOT NULL,
    wali_kelas VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================================
-- 3. TABEL SISWA
-- =========================================
CREATE TABLE siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL UNIQUE,
    uid_rfid VARCHAR(50) DEFAULT NULL UNIQUE,
    nama_lengkap VARCHAR(150) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    kelas_id INT NOT NULL,
    status_aktif TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_siswa_kelas FOREIGN KEY (kelas_id)
        REFERENCES kelas(id) ON UPDATE CASCADE ON DELETE RESTRICT,

    INDEX idx_uid_rfid (uid_rfid),
    INDEX idx_kelas_id (kelas_id)
) ENGINE=InnoDB;

-- =========================================
-- 4. TABEL KIOSK_TOKENS (autentikasi perangkat kiosk RFID)
-- =========================================
CREATE TABLE kiosk_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_kiosk VARCHAR(100) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_used_at DATETIME DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_kiosk_creator FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL,

    INDEX idx_token (token)
) ENGINE=InnoDB;

-- =========================================
-- 5. TABEL ABSENSI
-- =========================================
CREATE TABLE absensi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    tanggal DATE NOT NULL,
    jam_absen TIME NOT NULL,
    status ENUM('Hadir', 'Sakit', 'Izin', 'Alpha', 'Bolos') NOT NULL,
    keterangan VARCHAR(255) DEFAULT NULL,
    metode ENUM('manual', 'rfid') NOT NULL DEFAULT 'manual',
    dicatat_oleh INT DEFAULT NULL,
    kiosk_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_absensi_siswa FOREIGN KEY (siswa_id)
        REFERENCES siswa(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_absensi_user FOREIGN KEY (dicatat_oleh)
        REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_absensi_kiosk FOREIGN KEY (kiosk_id)
        REFERENCES kiosk_tokens(id) ON DELETE SET NULL,

    -- Mencegah duplikat absen di hari yang sama & dipakai ON DUPLICATE KEY UPDATE
    -- oleh absensi manual (lihat controllers/AbsensiManualController.php)
    UNIQUE KEY uniq_siswa_tanggal (siswa_id, tanggal),

    INDEX idx_tanggal (tanggal),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- =========================================
-- SEED: Admin default
-- =========================================
-- Password default: admin123
-- GANTI hash di bawah dengan hasil `php generate_admin_hash.php <password_anda>`
-- sebelum dipakai di production — jangan pakai password default ini apa adanya.
INSERT INTO users (nama, username, password, role)
VALUES ('Administrator', 'admin', '$2y$10$PLACEHOLDER_HASH_GENERATE_VIA_PHP', 'admin');