from flask import render_template, request, redirect, url_for, flash, jsonify, send_file
from flask_login import LoginManager, UserMixin, login_user, logout_user, login_required, current_user
from werkzeug.security import generate_password_hash, check_password_hash
from functools import wraps
from datetime import datetime, date, timedelta
import sqlite3
import qrcode
import io
import base64
from app import app, login_manager
import calendar
import os
from werkzeug.utils import secure_filename
import csv
from flask import Response, stream_with_context 

UPLOAD_FOLDER = 'static/uploads'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

# Database setup
def get_db():
    conn = sqlite3.connect('presensi.db')
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()
    
    # Tabel users
    c.execute('''CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT NOT NULL,
        nama_lengkap TEXT NOT NULL,
        qr_code TEXT, 
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )''')
    
    # Tabel kelas
    c.execute('''CREATE TABLE IF NOT EXISTS kelas (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nama_kelas TEXT NOT NULL,
        wali_kelas_id INTEGER,
        FOREIGN KEY (wali_kelas_id) REFERENCES guru(id)
    )''')

    # Tabel guru
    c.execute('''CREATE TABLE IF NOT EXISTS guru (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nip TEXT UNIQUE NOT NULL,
        nama TEXT NOT NULL,
        user_id INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )''')
    
    # Tabel siswa
    c.execute('''CREATE TABLE IF NOT EXISTS siswa (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nisn TEXT UNIQUE NOT NULL,
        nama TEXT NOT NULL,
        kelas_id INTEGER,
        status_aktif INTEGER DEFAULT 1,
        qr_code TEXT,
        user_id INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (kelas_id) REFERENCES kelas(id),
        FOREIGN KEY (user_id) REFERENCES users(id)
    )''')

    # 2. UPDATE TABEL ABSENSI: Menggunakan user_id
    # Cek apakah tabel absensi masih menggunakan siswa_id (struktur lama)
    try:
        c.execute("SELECT user_id FROM absensi LIMIT 1")
    except sqlite3.OperationalError:
        # Jika kolom user_id tidak ada, kita drop tabel lama dan buat baru
        # PERINGATAN: Ini akan mereset data absensi lama. 
        # Jika butuh migrasi data, perlu script terpisah.
        c.execute("DROP TABLE IF EXISTS absensi")
    
    # Tabel absensi
    c.execute('''CREATE TABLE IF NOT EXISTS absensi (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        tanggal DATE NOT NULL,
        jam_masuk TIME,
        jam_pulang TIME,
        status TEXT DEFAULT 'Alpa',
        keterangan TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )''')

    # TABEL PENGATURAN
    c.execute('''CREATE TABLE IF NOT EXISTS pengaturan (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nama_sekolah TEXT DEFAULT 'SMA NEGERI CONTOH',
        alamat_sekolah TEXT DEFAULT 'Jl. Pendidikan No. 123',
        logo TEXT DEFAULT 'default_logo.png'
    )''')
    c.execute("SELECT * FROM pengaturan")
    if not c.fetchone():
        c.execute("INSERT INTO pengaturan (nama_sekolah, alamat_sekolah, logo) VALUES (?, ?, ?)",
                 ('SMA NEGERI CONTOH', 'Jl. Pendidikan No. 123', 'default_logo.png'))

    # Buat admin default jika belum ada
    c.execute("SELECT * FROM users WHERE username = 'admin'")
    if not c.fetchone():
        hashed_pw = generate_password_hash('admin123')
        # Generate QR untuk Admin
        qr_data = "admin|Administrator"
        qr_code_img = generate_qr_code_base64(qr_data)
        c.execute("INSERT INTO users (username, password, role, nama_lengkap, qr_code) VALUES (?, ?, ?, ?, ?)",
                 ('admin', hashed_pw, 'Admin Sekolah', 'Administrator', qr_code_img))
    
    conn.commit()
    conn.close()

@app.context_processor
def inject_sekolah_info():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM pengaturan LIMIT 1")
    sekolah = c.fetchone()
    conn.close()
    return dict(sekolah=sekolah)

# User class untuk Flask-Login
class User(UserMixin):
    def __init__(self, id, username, role, nama_lengkap):
        self.id = id
        self.username = username
        self.role = role
        self.nama_lengkap = nama_lengkap

@login_manager.user_loader
def load_user(user_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT * FROM users WHERE id = ?", (user_id,))
    user_data = c.fetchone()
    conn.close()
    if user_data:
        return User(user_data['id'], user_data['username'], user_data['role'], user_data['nama_lengkap'])
    return None

# Decorator untuk role checking
def role_required(*roles):
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            if not current_user.is_authenticated:
                return redirect(url_for('login'))
            if current_user.role not in roles:
                flash('Anda tidak memiliki akses ke halaman ini', 'danger')
                return redirect(url_for('dashboard'))
            return f(*args, **kwargs)
        return decorated_function
    return decorator

# Generate QR Code
def generate_qr_code(nisn, nama):
    qr_data = f"{nisn}|{nama}"
    qr = qrcode.QRCode(version=1, box_size=10, border=5)
    qr.add_data(qr_data)
    qr.make(fit=True)
    img = qr.make_image(fill_color="black", back_color="white")
    
    # Convert to base64
    buffered = io.BytesIO()
    img.save(buffered, format="PNG")
    img_str = base64.b64encode(buffered.getvalue()).decode()
    return f"data:image/png;base64,{img_str}"

def generate_qr_code_base64(data):
    qr = qrcode.QRCode(version=1, box_size=10, border=5)
    qr.add_data(data)
    qr.make(fit=True)
    img = qr.make_image(fill_color="black", back_color="white")
    buffered = io.BytesIO()
    img.save(buffered, format="PNG")
    img_str = base64.b64encode(buffered.getvalue()).decode()
    return f"data:image/png;base64,{img_str}"

def update_role_guru(guru_id):
    if not guru_id: return
    
    conn = get_db()
    c = conn.cursor()
    
    # 1. Cek User ID dari Guru ini
    c.execute("SELECT user_id FROM guru WHERE id = ?", (guru_id,))
    guru_data = c.fetchone()
    
    if guru_data and guru_data['user_id']:
        user_id = guru_data['user_id']
        
        # 2. Cek apakah guru ini menjadi Wali Kelas di tabel kelas?
        c.execute("SELECT count(*) as total FROM kelas WHERE wali_kelas_id = ?", (guru_id,))
        count = c.fetchone()['total']
        
        # 3. Logika Update Role
        if count > 0:
            # Jika mengampu 1 kelas atau lebih -> Jadi Wali Kelas
            c.execute("UPDATE users SET role = 'Wali Kelas' WHERE id = ?", (user_id,))
        else:
            # Jika tidak mengampu kelas sama sekali -> Jadi Guru Biasa
            # (Hanya ubah jika role saat ini adalah Wali Kelas, jangan ubah jika dia Admin)
            c.execute("SELECT role FROM users WHERE id = ?", (user_id,))
            curr_role = c.fetchone()['role']
            if curr_role == 'Wali Kelas':
                c.execute("UPDATE users SET role = 'Guru' WHERE id = ?", (user_id,))
                
        conn.commit()
    conn.close()

# Routes
@app.route('/')
def index():
    if current_user.is_authenticated:
        return redirect(url_for('dashboard'))
    return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if current_user.is_authenticated:
        return redirect(url_for('dashboard'))
    
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        conn = get_db()
        c = conn.cursor()
        c.execute("SELECT * FROM users WHERE username = ?", (username,))
        user_data = c.fetchone()
        conn.close()
        
        if user_data and check_password_hash(user_data['password'], password):
            user = User(user_data['id'], user_data['username'], user_data['role'], user_data['nama_lengkap'])
            login_user(user)
            flash(f'Selamat datang, {user.nama_lengkap}!', 'success')
            return redirect(url_for('dashboard'))
        else:
            flash('Username atau password salah', 'danger')
    
    return render_template('login.html')

@app.route('/logout')
@login_required
def logout():
    logout_user()
    flash('Anda telah keluar', 'info')
    return redirect(url_for('login'))

@app.route('/dashboard')
@login_required
def dashboard():
    conn = get_db()
    c = conn.cursor()
    stats = {}
    today = date.today()
    
    # ... (Bagian Admin/Operator dan Wali Kelas Biarkan Saja) ...

    # PERBAIKAN DI SINI (LOGIKA WALI SISWA)
    if current_user.role == 'Wali Siswa':
        # Ambil data siswa untuk tampilan nama/nisn
        c.execute("SELECT * FROM siswa WHERE user_id = ?", (current_user.id,))
        siswa = c.fetchone()
        
        if siswa:
            stats['nama_siswa'] = siswa['nama']
            stats['nisn'] = siswa['nisn']
            
            # Update: Gunakan user_id (current_user.id), BUKAN siswa_id
            first_day = today.replace(day=1)
            c.execute("""SELECT COUNT(*) as total FROM absensi 
                        WHERE user_id = ? AND tanggal >= ? AND status = 'Hadir'""", 
                     (current_user.id, first_day))
            stats['hadir_bulan_ini'] = c.fetchone()['total']
            
            # Total hari sekolah
            days_in_month = (today - first_day).days + 1
            stats['hari_sekolah'] = days_in_month
    
    conn.close()
    return render_template('dashboard.html', stats=stats)

# ============ MANAJEMEN USER ============
@app.route('/users')
@login_required
@role_required('Admin Sekolah')
def users():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    
    conn = get_db()
    c = conn.cursor()
    
    query = "SELECT * FROM users WHERE nama_lengkap LIKE ? OR username LIKE ?"
    count_query = "SELECT COUNT(*) as total FROM users WHERE nama_lengkap LIKE ? OR username LIKE ?"
    
    search_param = f'%{search}%'
    c.execute(count_query, (search_param, search_param))
    total = c.fetchone()['total']
    
    offset = (page - 1) * per_page
    c.execute(query + " ORDER BY id DESC LIMIT ? OFFSET ?", (search_param, search_param, per_page, offset))
    users = c.fetchall()
    
    conn.close()
    
    total_pages = (total + per_page - 1) // per_page
    return render_template('users.html', users=users, page=page, total_pages=total_pages, search=search)

@app.route('/users/add', methods=['POST'])
@login_required
@role_required('Admin Sekolah')
def add_user():
    username = request.form.get('username')
    password = request.form.get('password')
    role = request.form.get('role')
    nama_lengkap = request.form.get('nama_lengkap')
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        hashed_pw = generate_password_hash(password)
        # Generate QR Code: username|nama
        qr_data = f"{username}|{nama_lengkap}"
        qr_code = generate_qr_code_base64(qr_data)
        
        c.execute("INSERT INTO users (username, password, role, nama_lengkap, qr_code) VALUES (?, ?, ?, ?, ?)",
                 (username, hashed_pw, role, nama_lengkap, qr_code))
        conn.commit()
        flash('User berhasil ditambahkan (QR Code dibuat)', 'success')
    except sqlite3.IntegrityError:
        flash('Username sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('users'))

@app.route('/users/edit/<int:id>', methods=['POST'])
@login_required
@role_required('Admin Sekolah')
def edit_user(id):
    username = request.form.get('username')
    role = request.form.get('role')
    nama_lengkap = request.form.get('nama_lengkap')
    password = request.form.get('password')
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        if password:
            hashed_pw = generate_password_hash(password)
            c.execute("UPDATE users SET username = ?, password = ?, role = ?, nama_lengkap = ? WHERE id = ?",
                     (username, hashed_pw, role, nama_lengkap, id))
        else:
            c.execute("UPDATE users SET username = ?, role = ?, nama_lengkap = ? WHERE id = ?",
                     (username, role, nama_lengkap, id))
        conn.commit()
        flash('User berhasil diupdate', 'success')
    except sqlite3.IntegrityError:
        flash('Username sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('users'))

@app.route('/users/delete/<int:id>')
@login_required
@role_required('Admin Sekolah')
def delete_user(id):
    if id == current_user.id:
        flash('Tidak dapat menghapus user yang sedang login', 'danger')
        return redirect(url_for('users'))
    
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM users WHERE id = ?", (id,))
    conn.commit()
    conn.close()
    
    flash('User berhasil dihapus', 'success')
    return redirect(url_for('users'))

# ============ MANAJEMEN KELAS ============
@app.route('/kelas')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def kelas():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    
    conn = get_db()
    c = conn.cursor()
    
    query = """SELECT k.*, g.nama as wali_kelas_nama 
               FROM kelas k 
               LEFT JOIN guru g ON k.wali_kelas_id = g.id
               WHERE k.nama_kelas LIKE ?"""
    count_query = "SELECT COUNT(*) as total FROM kelas WHERE nama_kelas LIKE ?"
    
    search_param = f'%{search}%'
    c.execute(count_query, (search_param,))
    total = c.fetchone()['total']
    
    offset = (page - 1) * per_page
    c.execute(query + " ORDER BY k.id DESC LIMIT ? OFFSET ?", (search_param, per_page, offset))
    kelas_list = c.fetchall()
    
    # Get all guru for dropdown
    c.execute("SELECT * FROM guru ORDER BY nama")
    guru_list = c.fetchall()
    
    conn.close()
    
    total_pages = (total + per_page - 1) // per_page
    return render_template('kelas.html', kelas_list=kelas_list, guru_list=guru_list, page=page, total_pages=total_pages, search=search)

@app.route('/kelas/add', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def add_kelas():
    nama_kelas = request.form.get('nama_kelas')
    wali_kelas_id = request.form.get('wali_kelas_id') or None
    
    conn = get_db()
    c = conn.cursor()
    c.execute("INSERT INTO kelas (nama_kelas, wali_kelas_id) VALUES (?, ?)", (nama_kelas, wali_kelas_id))
    conn.commit()
    conn.close()
    
    # Update Role Guru Terkait
    if wali_kelas_id:
        update_role_guru(wali_kelas_id)
    
    flash('Kelas berhasil ditambahkan', 'success')
    return redirect(url_for('kelas'))

@app.route('/kelas/edit/<int:id>', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def edit_kelas(id):
    nama_kelas = request.form.get('nama_kelas')
    new_wali_id = request.form.get('wali_kelas_id') or None
    
    conn = get_db()
    c = conn.cursor()
    
    # 1. Ambil ID Wali Kelas Lama (Sebelum diupdate)
    c.execute("SELECT wali_kelas_id FROM kelas WHERE id = ?", (id,))
    old_data = c.fetchone()
    old_wali_id = old_data['wali_kelas_id'] if old_data else None
    
    # 2. Update Data Kelas
    c.execute("UPDATE kelas SET nama_kelas = ?, wali_kelas_id = ? WHERE id = ?", (nama_kelas, new_wali_id, id))
    conn.commit()
    conn.close()
    
    # 3. Update Role User
    # Cek Guru Lama (Mungkin dia tidak lagi jadi Wali Kelas di manapun)
    if old_wali_id:
        update_role_guru(old_wali_id)
        
    # Cek Guru Baru (Dia sekarang jadi Wali Kelas)
    if new_wali_id:
        update_role_guru(new_wali_id)
    
    flash('Kelas berhasil diupdate', 'success')
    return redirect(url_for('kelas'))

@app.route('/kelas/delete/<int:id>')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def delete_kelas(id):
    conn = get_db()
    c = conn.cursor()
    
    # Ambil wali kelas sebelum dihapus
    c.execute("SELECT wali_kelas_id FROM kelas WHERE id = ?", (id,))
    data = c.fetchone()
    wali_id = data['wali_kelas_id'] if data else None
    
    c.execute("DELETE FROM kelas WHERE id = ?", (id,))
    conn.commit()
    conn.close()
    
    # Update Role Guru (Cek apakah dia turun pangkat jadi Guru biasa)
    if wali_id:
        update_role_guru(wali_id)
    
    flash('Kelas berhasil dihapus', 'success')
    return redirect(url_for('kelas'))

# ============ MANAJEMEN GURU ============
@app.route('/guru')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def guru():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    
    conn = get_db()
    c = conn.cursor()
    
    query = "SELECT * FROM guru WHERE nama LIKE ? OR nip LIKE ?"
    count_query = "SELECT COUNT(*) as total FROM guru WHERE nama LIKE ? OR nip LIKE ?"
    
    search_param = f'%{search}%'
    c.execute(count_query, (search_param, search_param))
    total = c.fetchone()['total']
    
    offset = (page - 1) * per_page
    c.execute(query + " ORDER BY id DESC LIMIT ? OFFSET ?", (search_param, search_param, per_page, offset))
    guru_list = c.fetchall()
    
    conn.close()
    
    total_pages = (total + per_page - 1) // per_page
    return render_template('guru.html', guru_list=guru_list, page=page, total_pages=total_pages, search=search)

@app.route('/guru/add', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def add_guru():
    nip = request.form.get('nip')
    nama = request.form.get('nama')
    username = request.form.get('username')
    password = request.form.get('password')
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        hashed_pw = generate_password_hash(password)
        # Generate QR Code
        qr_content = f"{username}|{nama}"
        qr_code = generate_qr_code_base64(qr_content)
        
        # PERUBAHAN DISINI: Role default adalah 'Guru', bukan 'Wali Kelas'
        c.execute("INSERT INTO users (username, password, role, nama_lengkap, qr_code) VALUES (?, ?, ?, ?, ?)",
                 (username, hashed_pw, 'Guru', nama, qr_code))
        user_id = c.lastrowid
        
        c.execute("INSERT INTO guru (nip, nama, user_id) VALUES (?, ?, ?)", (nip, nama, user_id))
        conn.commit()
        flash('Guru berhasil ditambahkan (Role: Guru)', 'success')
    except sqlite3.IntegrityError:
        flash('NIP atau Username sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('guru'))

@app.route('/guru/edit/<int:id>', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def edit_guru(id):
    nip = request.form.get('nip')
    nama = request.form.get('nama')
    username = request.form.get('username')
    password = request.form.get('password')
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        # Get guru data
        c.execute("SELECT user_id FROM guru WHERE id = ?", (id,))
        guru_data = c.fetchone()
        
        if guru_data and guru_data['user_id']:
            # Update user
            if password:
                hashed_pw = generate_password_hash(password)
                c.execute("UPDATE users SET username = ?, password = ?, nama_lengkap = ? WHERE id = ?",
                         (username, hashed_pw, nama, guru_data['user_id']))
            else:
                c.execute("UPDATE users SET username = ?, nama_lengkap = ? WHERE id = ?",
                         (username, nama, guru_data['user_id']))
        
        # Update guru
        c.execute("UPDATE guru SET nip = ?, nama = ? WHERE id = ?", (nip, nama, id))
        conn.commit()
        flash('Guru berhasil diupdate', 'success')
    except sqlite3.IntegrityError:
        flash('NIP atau Username sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('guru'))

@app.route('/guru/delete/<int:id>')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def delete_guru(id):
    conn = get_db()
    c = conn.cursor()
    
    # Get user_id
    c.execute("SELECT user_id FROM guru WHERE id = ?", (id,))
    guru_data = c.fetchone()
    
    # Delete guru
    c.execute("DELETE FROM guru WHERE id = ?", (id,))
    
    # Delete user if exists
    if guru_data and guru_data['user_id']:
        c.execute("DELETE FROM users WHERE id = ?", (guru_data['user_id'],))
    
    conn.commit()
    conn.close()
    
    flash('Guru berhasil dihapus', 'success')
    return redirect(url_for('guru'))

# ============ MANAJEMEN SISWA ============
@app.route('/siswa')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def siswa():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    per_page = 10
    
    conn = get_db()
    c = conn.cursor()
    
    # Perbaikan: Mengubah s.nik menjadi s.nisn
    query = """SELECT s.*, k.nama_kelas 
               FROM siswa s 
               LEFT JOIN kelas k ON s.kelas_id = k.id
               WHERE s.nama LIKE ? OR s.nisn LIKE ?"""
    
    # Perbaikan: Mengubah nik menjadi nisn
    count_query = "SELECT COUNT(*) as total FROM siswa WHERE nama LIKE ? OR nisn LIKE ?"
    
    search_param = f'%{search}%'
    c.execute(count_query, (search_param, search_param))
    total = c.fetchone()['total']
    
    offset = (page - 1) * per_page
    c.execute(query + " ORDER BY s.id DESC LIMIT ? OFFSET ?", (search_param, search_param, per_page, offset))
    siswa_list = c.fetchall()
    
    # Get all kelas for dropdown
    c.execute("SELECT * FROM kelas ORDER BY nama_kelas")
    kelas_list = c.fetchall()
    
    conn.close()
    
    total_pages = (total + per_page - 1) // per_page
    return render_template('siswa.html', siswa_list=siswa_list, kelas_list=kelas_list, page=page, total_pages=total_pages, search=search)

@app.route('/siswa/add', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def add_siswa():
    nisn = request.form.get('nisn')
    nama = request.form.get('nama')
    kelas_id = request.form.get('kelas_id')
    status_aktif = 1
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        # Buat user untuk wali siswa (orang tua)
        hashed_pw = generate_password_hash(nisn)  # Password default = NISN
        c.execute("INSERT INTO users (username, password, role, nama_lengkap) VALUES (?, ?, ?, ?)",
                 (nisn, hashed_pw, 'Wali Siswa', f'Wali dari {nama}'))
        user_id = c.lastrowid
        
        # Generate QR Code
        qr_code = generate_qr_code(nisn, nama)
        
        # Buat data siswa
        c.execute("INSERT INTO siswa (nisn, nama, kelas_id, status_aktif, qr_code, user_id) VALUES (?, ?, ?, ?, ?, ?)",
                 (nisn, nama, kelas_id, status_aktif, qr_code, user_id))
        conn.commit()
        flash('Siswa berhasil ditambahkan. QR Code telah dibuat', 'success')
    except sqlite3.IntegrityError:
        flash('NISN sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('siswa'))

@app.route('/siswa/edit/<int:id>', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def edit_siswa(id):
    nisn = request.form.get('nisn')
    nama = request.form.get('nama')
    kelas_id = request.form.get('kelas_id')
    status_aktif = request.form.get('status_aktif', 1, type=int)
    
    conn = get_db()
    c = conn.cursor()
    
    try:
        # Get siswa data
        c.execute("SELECT user_id, qr_code FROM siswa WHERE id = ?", (id,))
        siswa_data = c.fetchone()
        
        # Regenerate QR Code if NISN or nama changed
        qr_code = generate_qr_code(nisn, nama)
        
        if siswa_data and siswa_data['user_id']:
            # Update user (username tetap NISN)
            c.execute("UPDATE users SET username = ?, nama_lengkap = ? WHERE id = ?",
                     (nisn, f'Wali dari {nama}', siswa_data['user_id']))
        
        # Update siswa
        c.execute("UPDATE siswa SET nisn = ?, nama = ?, kelas_id = ?, status_aktif = ?, qr_code = ? WHERE id = ?",
                 (nisn, nama, kelas_id, status_aktif, qr_code, id))
        conn.commit()
        flash('Siswa berhasil diupdate', 'success')
    except sqlite3.IntegrityError:
        flash('NISN sudah digunakan', 'danger')
    finally:
        conn.close()
    
    return redirect(url_for('siswa'))

@app.route('/siswa/delete/<int:id>')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def delete_siswa(id):
    conn = get_db()
    c = conn.cursor()
    
    # Get user_id
    c.execute("SELECT user_id FROM siswa WHERE id = ?", (id,))
    siswa_data = c.fetchone()
    
    # Delete siswa
    c.execute("DELETE FROM siswa WHERE id = ?", (id,))
    
    # Delete user if exists
    if siswa_data and siswa_data['user_id']:
        c.execute("DELETE FROM users WHERE id = ?", (siswa_data['user_id'],))
    
    conn.commit()
    conn.close()
    
    flash('Siswa berhasil dihapus', 'success')
    return redirect(url_for('siswa'))

@app.route('/siswa/qrcode/<int:id>')
@login_required
def siswa_qrcode(id):
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT nisn, nama, qr_code FROM siswa WHERE id = ?", (id,))
    siswa = c.fetchone()
    conn.close()
    
    if siswa:
        return render_template('qrcode_view.html', siswa=siswa)
    else:
        flash('Siswa tidak ditemukan', 'danger')
        return redirect(url_for('siswa'))

@app.route('/user/qrcode/<int:id>')
@login_required
def user_qrcode(id):
    conn = get_db()
    c = conn.cursor()
    # Ambil data user beserta QR Code-nya
    c.execute("SELECT * FROM users WHERE id = ?", (id,))
    user = c.fetchone()
    conn.close()
    
    if user:
        # Kita format data agar mirip dengan struktur data siswa di template
        data = {
            'nama': user['nama_lengkap'],
            'identitas': user['username'], # Username sebagai identitas (pengganti NISN)
            'role': user['role'],
            'qr_code': user['qr_code']
        }
        return render_template('qrcode_view.html', siswa=data, is_user=True)
    else:
        flash('User tidak ditemukan', 'danger')
        return redirect(url_for('dashboard'))

# ============ SCAN QR CODE ============
@app.route('/scan-masuk')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def scan_masuk():
    return render_template('scan_masuk.html')

@app.route('/scan-pulang')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def scan_pulang():
    return render_template('scan_pulang.html')

@app.route('/api/scan-masuk', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def api_scan_masuk():
    data = request.get_json()
    qr_data = data.get('qr_data', '')
    
    parts = qr_data.split('|')
    if not parts:
        return jsonify({'success': False, 'message': 'QR Code tidak valid'})
        
    username_key = parts[0] 
    today = date.today()
    now = datetime.now().strftime('%H:%M:%S')
    
    conn = get_db()
    c = conn.cursor()
    
    # PERBAIKAN: Join ke tabel siswa untuk mengambil nama asli siswa
    c.execute("""
        SELECT u.*, s.nama as nama_siswa 
        FROM users u 
        LEFT JOIN siswa s ON u.id = s.user_id 
        WHERE u.username = ?
    """, (username_key,))
    user = c.fetchone()
    
    if not user:
        conn.close()
        return jsonify({'success': False, 'message': 'User tidak ditemukan'})
        
    # LOGIKA BARU: Jika dia siswa, pakai nama_siswa. Jika guru/admin, pakai nama_lengkap
    display_name = user['nama_siswa'] if user['nama_siswa'] else user['nama_lengkap']
    
    # 2. Cek Absensi hari ini berdasarkan user_id
    c.execute("SELECT * FROM absensi WHERE user_id = ? AND tanggal = ?", (user['id'], today))
    existing = c.fetchone()
    
    if existing and existing['jam_masuk']:
        conn.close()
        return jsonify({'success': False, 'message': f'{display_name} sudah absen masuk'})
    
    if existing:
        c.execute("UPDATE absensi SET jam_masuk = ?, status = 'Hadir' WHERE id = ?", (now, existing['id']))
    else:
        c.execute("INSERT INTO absensi (user_id, tanggal, jam_masuk, status) VALUES (?, ?, ?, ?)",
                 (user['id'], today, now, 'Hadir'))
    
    conn.commit()
    conn.close()
    
    # Info tambahan untuk feedback suara/teks
    role_info = user['role']
    if role_info == 'Wali Siswa': role_info = 'Siswa' 
    
    return jsonify({'success': True, 'message': f'Masuk Berhasil ({role_info}) - {display_name}'})

@app.route('/api/scan-pulang', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def api_scan_pulang():
    data = request.get_json()
    qr_data = data.get('qr_data', '')
    
    parts = qr_data.split('|')
    if not parts: return jsonify({'success': False, 'message': 'QR Error'})
    
    username_key = parts[0]
    today = date.today()
    now = datetime.now().strftime('%H:%M:%S')
    
    conn = get_db()
    c = conn.cursor()
    
    # PERBAIKAN: Join ke tabel siswa untuk mengambil nama asli siswa
    c.execute("""
        SELECT u.*, s.nama as nama_siswa 
        FROM users u 
        LEFT JOIN siswa s ON u.id = s.user_id 
        WHERE u.username = ?
    """, (username_key,))
    user = c.fetchone()
    
    if not user:
        conn.close()
        return jsonify({'success': False, 'message': 'User tidak ditemukan'})
        
    # LOGIKA BARU: Tentukan nama yang akan diucapkan/ditampilkan
    display_name = user['nama_siswa'] if user['nama_siswa'] else user['nama_lengkap']
    
    # 2. Cek Absensi
    c.execute("SELECT * FROM absensi WHERE user_id = ? AND tanggal = ?", (user['id'], today))
    existing = c.fetchone()
    
    if not existing or not existing['jam_masuk']:
        conn.close()
        return jsonify({'success': False, 'message': f'{display_name} belum absen masuk'})
    
    if existing['jam_pulang']:
        conn.close()
        return jsonify({'success': False, 'message': f'{display_name} sudah absen pulang'})
    
    c.execute("UPDATE absensi SET jam_pulang = ? WHERE id = ?", (now, existing['id']))
    conn.commit()
    conn.close()
    
    return jsonify({'success': True, 'message': f'Pulang Berhasil - {display_name}'})

# ============ DATA ABSENSI ============
@app.route('/absensi')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah', 'Wali Kelas', 'Guru')
def absensi():
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    tanggal = request.args.get('tanggal', date.today().strftime('%Y-%m-%d'))
    kelas_filter = request.args.get('kelas', '')
    role_filter = request.args.get('role', '')
    per_page = 10
    
    conn = get_db()
    c = conn.cursor()
    
    base_query = """SELECT a.*, 
                    CASE WHEN s.nisn IS NOT NULL THEN s.nisn ELSE u.username END as identitas,
                    u.nama_lengkap as nama, 
                    u.role, 
                    k.nama_kelas
                    FROM absensi a
                    JOIN users u ON a.user_id = u.id
                    LEFT JOIN siswa s ON u.id = s.user_id
                    LEFT JOIN kelas k ON s.kelas_id = k.id
                    WHERE a.tanggal = ?"""
    
    count_query = """SELECT COUNT(*) as total FROM absensi a
                     JOIN users u ON a.user_id = u.id
                     LEFT JOIN siswa s ON u.id = s.user_id
                     WHERE a.tanggal = ?"""
    
    params = [tanggal]
    
    if role_filter:
        if role_filter == 'Siswa':
            base_query += " AND s.id IS NOT NULL"
            count_query += " AND s.id IS NOT NULL"
        elif role_filter == 'Guru':
            base_query += " AND (u.role = 'Wali Kelas' OR u.role = 'Guru')"
            count_query += " AND (u.role = 'Wali Kelas' OR u.role = 'Guru')"
        else:
            base_query += " AND u.role = ?"
            count_query += " AND u.role = ?"
            params.append(role_filter)
    
    if kelas_filter:
        base_query += " AND s.kelas_id = ?"
        count_query += " AND s.kelas_id = ?"
        params.append(kelas_filter)

    if search:
        base_query += " AND (u.nama_lengkap LIKE ? OR u.username LIKE ?)"
        count_query += " AND (u.nama_lengkap LIKE ? OR u.username LIKE ?)"
        search_param = f'%{search}%'
        params.extend([search_param, search_param])
        
    # NOTE: Restriksi Wali Kelas dihapus agar bisa akses semua kelas

    c.execute(count_query, params)
    total_data = c.fetchone()['total']
    
    offset = (page - 1) * per_page
    c.execute(base_query + " ORDER BY a.id DESC LIMIT ? OFFSET ?", params + [per_page, offset])
    absensi_list = c.fetchall()
    
    c.execute("SELECT * FROM kelas ORDER BY nama_kelas")
    kelas_list = c.fetchall()
    conn.close()
    
    total_pages = (total_data + per_page - 1) // per_page
    return render_template('absensi.html', absensi_list=absensi_list, kelas_list=kelas_list, page=page, total_pages=total_pages, search=search, tanggal=tanggal, kelas_filter=kelas_filter, role_filter=role_filter)

@app.route('/absensi/update/<int:id>', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def update_absensi(id):
    status = request.form.get('status')
    keterangan = request.form.get('keterangan', '')
    
    conn = get_db()
    c = conn.cursor()
    c.execute("UPDATE absensi SET status = ?, keterangan = ? WHERE id = ?", (status, keterangan, id))
    conn.commit()
    conn.close()
    
    flash('Status absensi berhasil diupdate', 'success')
    return redirect(url_for('absensi'))

# ============ PROSES OTOMATIS ALPA ============
@app.route('/proses-alpa')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def proses_alpa():
    today = date.today()
    
    conn = get_db()
    c = conn.cursor()
    
    # 1. Ambil user_id dari semua siswa aktif
    c.execute("SELECT user_id FROM siswa WHERE status_aktif = 1")
    siswa_list = c.fetchall()
    
    count = 0
    for s in siswa_list:
        uid = s['user_id']
        
        # 2. Cek apakah user_id tersebut sudah ada di tabel absensi hari ini?
        c.execute("SELECT id FROM absensi WHERE user_id = ? AND tanggal = ?", (uid, today))
        existing = c.fetchone()
        
        if not existing:
            # 3. Insert menggunakan user_id
            c.execute("INSERT INTO absensi (user_id, tanggal, status) VALUES (?, ?, ?)",
                     (uid, today, 'Alpa'))
            count += 1
    
    conn.commit()
    conn.close()
    
    flash(f'{count} siswa ditandai Alpa', 'info')
    return redirect(url_for('absensi'))

# ============ MONITORING WALI KELAS ============
@app.route('/monitoring')
@login_required
@role_required('Wali Kelas')
def monitoring():
    periode = request.args.get('periode', 'hari')
    tanggal = request.args.get('tanggal', date.today().strftime('%Y-%m-%d'))
    
    conn = get_db()
    c = conn.cursor()
    
    # 1. CARI KELAS MILIK USER YANG LOGIN
    # Alur: User Login (ID) -> Tabel Guru (user_id) -> Tabel Kelas (wali_kelas_id)
    c.execute("""SELECT k.* FROM kelas k 
                JOIN guru g ON k.wali_kelas_id = g.id 
                WHERE g.user_id = ?""", (current_user.id,))
    kelas_data = c.fetchone()
    
    # Jika user login sebagai Wali Kelas tapi belum di-assign ke kelas manapun
    if not kelas_data:
        conn.close()
        flash('Anda terdaftar sebagai Wali Kelas, tetapi belum ditugaskan ke kelas manapun.', 'warning')
        return render_template('monitoring.html', kelas=None, stats={})
    
    stats = {}
    kelas_id = kelas_data['id']
    
    # 2. AMBIL DATA SESUAI PERIODE
    if periode == 'hari':
        # Statistik Harian
        # Join: Absensi -> Users -> Siswa (untuk filter kelas)
        c.execute("""SELECT 
                    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                    COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                    COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                    FROM absensi a
                    JOIN users u ON a.user_id = u.id
                    JOIN siswa s ON u.id = s.user_id
                    WHERE s.kelas_id = ? AND a.tanggal = ?""", 
                 (kelas_id, tanggal))
        stats = dict(c.fetchone())
        
        # Detail Daftar Siswa & Statusnya Hari Ini
        # Left Join agar siswa yang belum absen tetap muncul di list
        c.execute("""SELECT s.nisn, s.nama, a.jam_masuk, a.jam_pulang, a.status, a.keterangan
                    FROM siswa s
                    JOIN users u ON s.user_id = u.id
                    LEFT JOIN absensi a ON u.id = a.user_id AND a.tanggal = ?
                    WHERE s.kelas_id = ? AND s.status_aktif = 1
                    ORDER BY s.nama""", (tanggal, kelas_id))
        detail = c.fetchall()
        stats['detail'] = detail
    
    elif periode == 'minggu':
        # Hitung rentang minggu
        tanggal_obj = datetime.strptime(tanggal, '%Y-%m-%d').date()
        start_week = tanggal_obj - timedelta(days=tanggal_obj.weekday())
        end_week = start_week + timedelta(days=6)
        
        c.execute("""SELECT 
                    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                    COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                    COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                    FROM absensi a
                    JOIN users u ON a.user_id = u.id
                    JOIN siswa s ON u.id = s.user_id
                    WHERE s.kelas_id = ? AND a.tanggal BETWEEN ? AND ?""", 
                 (kelas_id, start_week, end_week))
        stats = dict(c.fetchone())
    
    elif periode == 'bulan':
        # Hitung awal bulan
        tanggal_obj = datetime.strptime(tanggal, '%Y-%m-%d').date()
        first_day = tanggal_obj.replace(day=1)
        
        c.execute("""SELECT 
                    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                    COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                    COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                    FROM absensi a
                    JOIN users u ON a.user_id = u.id
                    JOIN siswa s ON u.id = s.user_id
                    WHERE s.kelas_id = ? AND a.tanggal >= ?""", 
                 (kelas_id, first_day))
        stats = dict(c.fetchone())
    
    conn.close()
    return render_template('monitoring.html', kelas=kelas_data, stats=stats, periode=periode, tanggal=tanggal)

# ============ RIWAYAT WALI SISWA ============
@app.route('/riwayat')
@login_required
@role_required('Wali Siswa')
def riwayat():
    periode = request.args.get('periode', 'bulan')
    
    conn = get_db()
    c = conn.cursor()
    
    # Ambil data siswa hanya untuk Info Header (Nama/NISN)
    c.execute("SELECT * FROM siswa WHERE user_id = ?", (current_user.id,))
    siswa = c.fetchone()
    
    if not siswa:
        conn.close()
        return render_template('riwayat.html', siswa=None, riwayat=[])
    
    today = date.today()
    
    # PERBAIKAN: Menggunakan user_id (current_user.id) BUKAN siswa_id
    if periode == 'bulan':
        first_day = today.replace(day=1)
        c.execute("""SELECT * FROM absensi 
                    WHERE user_id = ? AND tanggal >= ?
                    ORDER BY tanggal DESC""", (current_user.id, first_day))
    else:  # semua
        c.execute("""SELECT * FROM absensi 
                    WHERE user_id = ?
                    ORDER BY tanggal DESC LIMIT 100""", (current_user.id,))
    
    riwayat_list = c.fetchall()
    
    # Statistik (Update ke user_id)
    first_day_stat = today.replace(day=1)
    c.execute("""SELECT 
                COUNT(CASE WHEN status = 'Hadir' THEN 1 END) as hadir,
                COUNT(CASE WHEN status = 'Ijin' THEN 1 END) as ijin,
                COUNT(CASE WHEN status = 'Sakit' THEN 1 END) as sakit,
                COUNT(CASE WHEN status = 'Alpa' THEN 1 END) as alpa
                FROM absensi 
                WHERE user_id = ? AND tanggal >= ?""", (current_user.id, first_day_stat))
    stats = dict(c.fetchone())
    
    conn.close()
    return render_template('riwayat.html', siswa=siswa, riwayat=riwayat_list, stats=stats, periode=periode)

# ============ REKAPITULASI ============
@app.route('/rekap')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah', 'Wali Kelas', 'Guru')
def rekap():
    tipe = request.args.get('tipe', 'siswa') 
    bulan = request.args.get('bulan', date.today().strftime('%Y-%m'))
    
    conn = get_db()
    c = conn.cursor()
    data = []
    
    if tipe == 'siswa':
        # Restriksi Wali Kelas dihapus dari query
        query = """SELECT s.nisn as id_nomor, s.nama, k.nama_kelas as info_tambahan,
                  COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                  COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                  COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                  COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                  FROM siswa s
                  LEFT JOIN users u ON s.user_id = u.id
                  LEFT JOIN kelas k ON s.kelas_id = k.id
                  LEFT JOIN absensi a ON u.id = a.user_id AND strftime('%Y-%m', a.tanggal) = ?
                  WHERE s.status_aktif = 1
                  GROUP BY s.id ORDER BY k.nama_kelas, s.nama"""
        c.execute(query, (bulan,))
        data = c.fetchall()
    
    elif tipe in ['Guru', 'Admin Sekolah', 'Operator Sekolah']:
        query = """SELECT u.username as id_nomor, u.nama_lengkap as nama, u.role as info_tambahan,
                  COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                  COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                  COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                  COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                  FROM users u
                  LEFT JOIN absensi a ON u.id = a.user_id AND strftime('%Y-%m', a.tanggal) = ?
                  WHERE 1=1 """
        params = [bulan]
        if tipe == 'Guru':
            query += " AND (u.role = 'Guru' OR u.role = 'Wali Kelas')"
        else:
            query += " AND u.role = ?"
            params.append(tipe)
        query += " GROUP BY u.id ORDER BY u.nama_lengkap"
        c.execute(query, params)
        data = c.fetchall()

    elif tipe == 'kelas':
        c.execute("""SELECT k.id, k.nama_kelas, g.nama as wali_kelas,
                    COUNT(DISTINCT s.id) as total_siswa,
                    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                    COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                    COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                    FROM kelas k
                    LEFT JOIN guru g ON k.wali_kelas_id = g.id
                    LEFT JOIN siswa s ON k.id = s.kelas_id AND s.status_aktif = 1
                    LEFT JOIN users u ON s.user_id = u.id
                    LEFT JOIN absensi a ON u.id = a.user_id AND strftime('%Y-%m', a.tanggal) = ?
                    GROUP BY k.id ORDER BY k.nama_kelas""", (bulan,))
        data = c.fetchall()
    
    elif tipe == 'global':
        c.execute("""SELECT COUNT(DISTINCT u.id) as total_siswa,
                    COUNT(CASE WHEN a.status = 'Hadir' THEN 1 END) as hadir,
                    COUNT(CASE WHEN a.status = 'Ijin' THEN 1 END) as ijin,
                    COUNT(CASE WHEN a.status = 'Sakit' THEN 1 END) as sakit,
                    COUNT(CASE WHEN a.status = 'Alpa' THEN 1 END) as alpa
                    FROM users u
                    LEFT JOIN absensi a ON u.id = a.user_id AND strftime('%Y-%m', a.tanggal) = ?
                    """, (bulan,))
        data = [c.fetchone()]
    
    conn.close()
    return render_template('rekap.html', tipe=tipe, bulan=bulan, data=data)

@app.route('/pengaturan', methods=['GET', 'POST'])
@login_required
@role_required('Admin Sekolah')
def pengaturan():
    conn = get_db()
    c = conn.cursor()
    
    if request.method == 'POST':
        nama = request.form.get('nama_sekolah')
        alamat = request.form.get('alamat_sekolah')
        
        # Handle Upload Logo
        logo_file = request.files.get('logo')
        logo_filename = None
        
        if logo_file and logo_file.filename != '':
            filename = secure_filename(logo_file.filename)
            logo_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            logo_file.save(logo_path)
            logo_filename = filename
            
        if logo_filename:
            c.execute("UPDATE pengaturan SET nama_sekolah = ?, alamat_sekolah = ?, logo = ? WHERE id = 1",
                     (nama, alamat, logo_filename))
        else:
            c.execute("UPDATE pengaturan SET nama_sekolah = ?, alamat_sekolah = ? WHERE id = 1",
                     (nama, alamat))
            
        conn.commit()
        flash('Pengaturan berhasil disimpan', 'success')
        return redirect(url_for('pengaturan'))
    
    c.execute("SELECT * FROM pengaturan LIMIT 1")
    sekolah = c.fetchone()
    conn.close()
    return render_template('pengaturan.html', sekolah=sekolah)

@app.route('/siswa/export')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def export_siswa():
    conn = get_db()
    c = conn.cursor()
    
    # Ambil data siswa join dengan kelas
    query = """SELECT s.nisn, s.nama, k.nama_kelas, 
               CASE WHEN s.status_aktif = 1 THEN 'Aktif' ELSE 'Tidak Aktif' END as status
               FROM siswa s 
               LEFT JOIN kelas k ON s.kelas_id = k.id
               ORDER BY k.nama_kelas, s.nama"""
    c.execute(query)
    siswa_data = c.fetchall()
    conn.close()

    # Membuat CSV di memory
    output = io.StringIO()
    writer = csv.writer(output)
    
    # Header CSV
    writer.writerow(['NISN', 'Nama Lengkap', 'Nama Kelas', 'Status'])
    
    # Isi Data
    for row in siswa_data:
        writer.writerow([row['nisn'], row['nama'], row['nama_kelas'], row['status']])
    
    output.seek(0)
    
    return Response(
        output.getvalue(),
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=data_siswa.csv"}
    )

@app.route('/siswa/import', methods=['POST'])
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def import_siswa():
    if 'file' not in request.files:
        flash('Tidak ada file yang diupload', 'danger')
        return redirect(url_for('siswa'))
        
    file = request.files['file']
    if file.filename == '':
        flash('Tidak ada file yang dipilih', 'danger')
        return redirect(url_for('siswa'))
        
    if not file.filename.endswith('.csv'):
        flash('File harus berformat CSV', 'danger')
        return redirect(url_for('siswa'))

    try:
        # Baca file CSV
        stream = io.StringIO(file.stream.read().decode("UTF-8"), newline=None)
        csv_input = csv.reader(stream)
        
        # Skip header
        next(csv_input, None)
        
        conn = get_db()
        c = conn.cursor()
        
        sukses = 0
        gagal = 0
        
        for row in csv_input:
            # Format CSV diharapkan: NISN, Nama, Nama Kelas
            if len(row) < 3:
                continue
                
            nisn = row[0].strip()
            nama = row[1].strip()
            nama_kelas = row[2].strip()
            
            # 1. Cek apakah NISN sudah ada
            c.execute("SELECT id FROM siswa WHERE nisn = ?", (nisn,))
            if c.fetchone():
                gagal += 1
                continue # Skip jika duplikat
                
            # 2. Cari ID Kelas berdasarkan Nama Kelas
            kelas_id = None
            if nama_kelas:
                c.execute("SELECT id FROM kelas WHERE nama_kelas LIKE ?", (nama_kelas,))
                k = c.fetchone()
                if k:
                    kelas_id = k['id']
            
            # 3. Buat User Wali Siswa
            try:
                hashed_pw = generate_password_hash(nisn) # Password default = NISN
                c.execute("INSERT INTO users (username, password, role, nama_lengkap) VALUES (?, ?, ?, ?)",
                         (nisn, hashed_pw, 'Wali Siswa', f'Wali dari {nama}'))
                user_id = c.lastrowid
                
                # 4. Generate QR
                qr_code = generate_qr_code(nisn, nama)
                
                # 5. Insert Siswa
                c.execute("INSERT INTO siswa (nisn, nama, kelas_id, status_aktif, qr_code, user_id) VALUES (?, ?, ?, ?, ?, ?)",
                         (nisn, nama, kelas_id, 1, qr_code, user_id))
                
                sukses += 1
            except Exception as e:
                print(f"Error import row {nisn}: {e}")
                gagal += 1
                
        conn.commit()
        conn.close()
        
        msg_type = 'success' if sukses > 0 else 'warning'
        flash(f'Import selesai: {sukses} berhasil, {gagal} gagal/duplikat.', msg_type)
        
    except Exception as e:
        flash(f'Terjadi kesalahan saat membaca file: {e}', 'danger')
        
    return redirect(url_for('siswa'))

@app.route('/siswa/template')
@login_required
def download_template_siswa():
    # Gunakan io.StringIO untuk membuat file di memori (tanpa simpan ke disk)
    output = io.StringIO()
    writer = csv.writer(output)
    
    # Tulis Header (Sesuai urutan yang dibaca di fungsi import_siswa)
    writer.writerow(['NISN', 'Nama Lengkap', 'Nama Kelas'])
    
    # Tulis 2 Baris Contoh Data
    writer.writerow(['1234567890', 'Ahmad Dahlan', 'X IPA 1'])
    writer.writerow(['0987654321', 'Siti Walidah', 'XI IPS 2'])
    
    # Kembali ke awal file
    output.seek(0)
    
    return Response(
        output.getvalue(),
        mimetype="text/csv",
        headers={"Content-disposition": "attachment; filename=template_import_siswa.csv"}
    )

@app.route('/rekap/bulanan')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah', 'Wali Kelas')
def rekap_bulanan():
    bulan_input = request.args.get('bulan', date.today().strftime('%Y-%m'))
    kelas_id = request.args.get('kelas_id')
    
    if not kelas_id:
        flash('Pilih kelas terlebih dahulu', 'warning')
        return redirect(url_for('rekap'))

    year, month = map(int, bulan_input.split('-'))
    num_days = calendar.monthrange(year, month)[1] # Mendapatkan jumlah hari (28-31)
    
    conn = get_db()
    c = conn.cursor()
    
    # Ambil info kelas
    c.execute("SELECT k.*, g.nama as wali_nama FROM kelas k LEFT JOIN guru g ON k.wali_kelas_id = g.id WHERE k.id = ?", (kelas_id,))
    kelas_info = c.fetchone()
    
    # Ambil daftar siswa
    c.execute("SELECT id, nisn, nama FROM siswa WHERE kelas_id = ? AND status_aktif = 1 ORDER BY nama", (kelas_id,))
    siswa_list = c.fetchall()
    
    # PERBAIKAN DI SINI:
    # 1. Join tabel absensi dengan tabel siswa berdasarkan user_id
    # 2. Filter berdasarkan kelas_id agar data yang diambil spesifik kelas tersebut
    # 3. Ambil s.id sebagai 'siswa_id' agar logika mapping di bawah tidak perlu diubah
    c.execute("""SELECT s.id as siswa_id, strftime('%d', a.tanggal) as hari, a.status 
                 FROM absensi a
                 JOIN siswa s ON a.user_id = s.user_id
                 WHERE strftime('%Y-%m', a.tanggal) = ? AND s.kelas_id = ?""", 
                 (bulan_input, kelas_id))
    absensi_data = c.fetchall()
    
    # Mapping data agar mudah diakses di template: {siswa_id: {hari: status}}
    map_absen = {}
    for row in absensi_data:
        s_id = row['siswa_id']
        hari = int(row['hari'])
        if s_id not in map_absen:
            map_absen[s_id] = {}
        map_absen[s_id][hari] = row['status']
    
    conn.close()
    
    # Nama bulan Indonesia
    nama_bulan = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", 
                  "Juli", "Agustus", "September", "Oktober", "November", "Desember"]
    bulan_str = f"{nama_bulan[month-1]} {year}"

    return render_template('rekap_bulanan.html', 
                           siswa_list=siswa_list, 
                           num_days=num_days, 
                           map_absen=map_absen, 
                           kelas_info=kelas_info,
                           bulan_str=bulan_str,
                           bulan_val=bulan_input)

# Initialize database on startup
init_db()

@app.route('/users/cetak-semua-qr')
@login_required
@role_required('Admin Sekolah', 'Operator Sekolah')
def cetak_semua_qr():
    role = request.args.get('role', 'Siswa')
    conn = get_db()
    c = conn.cursor()
    
    if role == 'Siswa':
        # Ambil data Siswa Aktif
        c.execute("""SELECT s.nama, s.nisn as identitas, s.qr_code, k.nama_kelas as info 
                     FROM siswa s 
                     LEFT JOIN kelas k ON s.kelas_id = k.id 
                     WHERE s.status_aktif = 1 
                     ORDER BY k.nama_kelas, s.nama""")
    else:
        # Ambil data Guru/Staff (Admin & Operator)
        c.execute("""SELECT nama_lengkap as nama, username as identitas, qr_code, role as info 
                     FROM users 
                     WHERE role != 'Wali Siswa' 
                     ORDER BY role, nama_lengkap""")
        
    users_data = c.fetchall()
    conn.close()
    return render_template('cetak_kolektif_qr.html', users_data=users_data, role=role)