CREATE DATABASE IF NOT EXISTS scoutapp;

USE scoutapp;

CREATE TABLE IF NOT EXISTS kegiatan_pramuka (
    id_kegiatan INT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL,
    materi VARCHAR(255) NOT NULL,
    pembimbing VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS galeri (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_kegiatan INT,
    tanggal DATE NOT NULL,
    deskripsi TEXT,
    FOREIGN KEY (id_kegiatan) REFERENCES kegiatan_pramuka(id_kegiatan)
);

CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('guru', 'siswa') DEFAULT 'siswa'
);

CREATE TABLE IF NOT EXISTS absensi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_kegiatan INT,
    id_siswa INT,
    status ENUM('hadir', 'absen', 'izin', 'alpa') DEFAULT 'hadir',
    tanggal_input TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_kegiatan) REFERENCES kegiatan_pramuka(id_kegiatan),
    FOREIGN KEY (id_siswa) REFERENCES users(id)
);
