-- SQL Update for Missing Features (Attendance, Grades, Announcements)

-- 1. Table: Attendance (Absensi)
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    student_id INT NOT NULL,
    meeting_date DATE NOT NULL,
    status ENUM('Hadir', 'Sakit', 'Izin', 'Alpha') NOT NULL DEFAULT 'Alpha',
    notes TEXT, -- Optional: Catatan pertemuan
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    -- Prevent duplicate attendance for same student in same class on same day
    UNIQUE KEY unique_attendance (class_id, student_id, meeting_date) 
);

-- 2. Table: Grades (Nilai - UTS, UAS, Quiz)
-- Note: 'Tugas' might strictly be from submissions, but this allows manual override or offline tasks
CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    student_id INT NOT NULL,
    type ENUM('Tugas', 'UTS', 'UAS', 'Kuis') NOT NULL,
    score DECIMAL(5, 2) NOT NULL, -- e.g. 85.50
    feedback TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. Table: Announcements (Pengumuman)
CREATE TABLE announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    target_role ENUM('all', 'mahasiswa', 'dosen') NOT NULL DEFAULT 'all',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Sample Data for Testing (Opsional)
-- Insert sample announcement
INSERT INTO announcements (author_id, title, content, target_role) VALUES 
(3, 'Selamat Datang di Semester Baru', 'Perkuliahan dimulai tanggal 1 September. Harap segera selesaikan KRS.', 'all');

-- 5. Table: Payments (Keuangan)
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    title VARCHAR(100) NOT NULL, -- e.g. "SPP Semester Genap 2023/2024"
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('paid', 'unpaid') NOT NULL DEFAULT 'unpaid',
    due_date DATE NOT NULL,
    payment_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Sample Data for Payments
INSERT INTO payments (student_id, title, amount, status, due_date) VALUES 
(1, 'SPP Semester Ganjil 2024/2025', 5000000, 'unpaid', '2024-08-31'),
(1, 'Biaya Pembangunan', 2000000, 'paid', '2024-08-01');
