-- ========================================
-- DATABASE: belloto_db
-- Sistem: Bel Sekolah Otomatis SMAN 1 Bantaran
-- Created: 2026
-- ========================================

-- Buat database
CREATE DATABASE IF NOT EXISTS belloto_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE belloto_db;

-- ========================================
-- TABEL: belloto_audio
-- Deskripsi: Menyimpan data audio bel sekolah
-- ========================================
CREATE TABLE belloto_audio (
    id INT(11) NOT NULL AUTO_INCREMENT,
    nama_audio VARCHAR(100) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    durasi INT(11) DEFAULT 0 COMMENT 'Durasi dalam detik',
    uploaded_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY unique_nama_audio (nama_audio),
    INDEX idx_uploaded_at (uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABEL: belloto_jadwal
-- Deskripsi: Menyimpan jadwal bel sekolah
-- ========================================
CREATE TABLE belloto_jadwal (
    id INT(11) NOT NULL AUTO_INCREMENT,
    hari ENUM('Senin','Selasa','Rabu','Kamis','Jumat','Sabtu') NOT NULL,
    jam TIME NOT NULL,
    nama_kegiatan VARCHAR(100) NOT NULL,
    audio_file VARCHAR(255) NOT NULL COMMENT 'Nama file audio',
    aktif TINYINT(1) DEFAULT 1 COMMENT '1=Aktif, 0=Nonaktif',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_hari (hari),
    INDEX idx_jam (jam),
    INDEX idx_aktif (aktif),
    INDEX idx_hari_jam (hari, jam)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABEL: belloto_log
-- Deskripsi: Menyimpan log aktivitas bel
-- ========================================
CREATE TABLE belloto_log (
    id INT(11) NOT NULL AUTO_INCREMENT,
    tanggal DATE NOT NULL,
    jam TIME NOT NULL,
    jadwal_bel_id INT(11) DEFAULT NULL COMMENT 'Relasi ke tabel jadwal',
    kegiatan VARCHAR(100) NOT NULL,
    audio_file VARCHAR(255) DEFAULT NULL,
    status ENUM('Berhasil','Gagal') DEFAULT 'Berhasil',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_tanggal (tanggal),
    KEY idx_jam (jam),
    KEY idx_jadwal_bel_id (jadwal_bel_id),
    KEY idx_status (status),
    KEY idx_tanggal_jam (tanggal, jam),
    CONSTRAINT fk_log_jadwal_bel 
        FOREIGN KEY (jadwal_bel_id) 
        REFERENCES belloto_jadwal(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABEL: belloto_pengaturan
-- Deskripsi: Menyimpan pengaturan sistem
-- ========================================
CREATE TABLE belloto_pengaturan (
    id INT(11) NOT NULL AUTO_INCREMENT,
    kunci VARCHAR(100) NOT NULL,
    nilai TEXT DEFAULT NULL,
    kategori VARCHAR(50) DEFAULT 'umum',
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY unique_kunci (kunci),
    INDEX idx_kategori (kategori)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- TABEL: belloto_users
-- Deskripsi: User sistem (admin/operator)
-- ========================================
CREATE TABLE belloto_users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    level ENUM('admin','operator') DEFAULT 'operator',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY unique_username (username),
    INDEX idx_level (level),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- DATA AWAL (SEED DATA)
-- ========================================

-- Insert pengaturan default
INSERT INTO belloto_pengaturan (kunci, nilai, kategori) VALUES
('nama_sekolah', 'SMAN 1 BANTARAN', 'identitas'),
('alamat_sekolah', 'Jl. Raya Tempuran No.139, Krajan, Tempuran, Kec. Bantaran, Kabupaten Probolinggo, Jawa Timur 67261', 'identitas'),
('timezone', 'Asia/Jakarta', 'sistem'),
('volume_default', '100', 'audio'),
('auto_start', '1', 'sistem'),
('check_interval', '10', 'sistem');

-- Insert user default (username: admin, password: admin123)
-- Password di-hash menggunakan password_hash() PHP
INSERT INTO belloto_users (username, password, nama_lengkap, email, level, is_active) VALUES
('admin', '$2y$10$ybzhRu5erGw7Wnls.TmcxOA1i/RgL7eHfuUrT3GWG3lDimFuGmV.O', 'Administrator', 'admin@sman1bantaran.sch.id', 'admin', 1);

-- Insert jadwal bel default (contoh)
INSERT INTO belloto_jadwal (hari, jam, nama_kegiatan, audio_file, aktif) VALUES
('Senin', '06:45:00', 'Masuk Sekolah', 'masuk_sekolah.mp3', 1),
('Senin', '07:00:00', 'Upacara Bendera', 'upacara.mp3', 1),
('Senin', '07:45:00', 'Pelajaran 1', 'masuk_kelas.mp3', 1),
('Senin', '08:35:00', 'Pelajaran 2', 'pindah_pelajaran.mp3', 1),
('Senin', '09:25:00', 'Pelajaran 3', 'pindah_pelajaran.mp3', 1),
('Senin', '10:15:00', 'Istirahat', 'istirahat.mp3', 1),
('Senin', '10:45:00', 'Pelajaran 4', 'masuk_kelas.mp3', 1),
('Senin', '11:35:00', 'Pelajaran 5', 'pindah_pelajaran.mp3', 1),
('Senin', '12:25:00', 'Pelajaran 6', 'pindah_pelajaran.mp3', 1),
('Senin', '13:15:00', 'Pulang Sekolah', 'pulang_sekolah.mp3', 1),
('Selasa', '06:45:00', 'Masuk Sekolah', 'masuk_sekolah.mp3', 1),
('Selasa', '07:00:00', 'Pelajaran 1', 'masuk_kelas.mp3', 1),
('Selasa', '07:45:00', 'Pelajaran 2', 'pindah_pelajaran.mp3', 1),
('Selasa', '08:35:00', 'Pelajaran 3', 'pindah_pelajaran.mp3', 1),
('Selasa', '09:25:00', 'Pelajaran 4', 'pindah_pelajaran.mp3', 1),
('Selasa', '10:15:00', 'Istirahat', 'istirahat.mp3', 1),
('Selasa', '10:45:00', 'Pelajaran 5', 'masuk_kelas.mp3', 1),
('Selasa', '11:35:00', 'Pelajaran 6', 'pindah_pelajaran.mp3', 1),
('Selasa', '12:25:00', 'Pulang Sekolah', 'pulang_sekolah.mp3', 1);

-- ========================================
-- INDEXES & CONSTRAINTS CHECK
-- ========================================

-- Verify foreign keys
SET FOREIGN_KEY_CHECKS=1;

-- ========================================
-- QUERY UNTUK CEK STRUKTUR
-- ========================================

-- Lihat semua tabel
-- SHOW TABLES;

-- Lihat struktur tabel
-- DESCRIBE belloto_audio;
-- DESCRIBE belloto_jadwal;
-- DESCRIBE belloto_log;
-- DESCRIBE belloto_pengaturan;
-- DESCRIBE belloto_users;

-- ========================================
-- CATATAN PENTING
-- ========================================
-- 1. Pastikan folder 'audio/' sudah dibuat di root aplikasi
-- 2. Set permission folder audio: chmod 755 audio/
-- 3. Upload file audio MP3 ke folder audio/
-- 4. Default login: username=admin, password=admin123
-- 5. Ganti password default setelah login pertama
-- ========================================