import sqlite3
from werkzeug.security import generate_password_hash
import qrcode
import io
import base64
from datetime import datetime, date, timedelta
import random

# Konfigurasi Database
DB_PATH = 'presensi.db'

def generate_qr_dummy(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 seed_data():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()

    print("Memulai proses seeding data dummy...")

    # 1. Kosongkan data lama (Opsional, agar tidak duplikat saat running ulang)
    tables = ['absensi', 'siswa', 'guru', 'kelas', 'users', 'pengaturan']
    for table in tables:
        c.execute(f"DELETE FROM {table}")
    
    # Reset Auto Increment
    for table in tables:
        c.execute(f"DELETE FROM sqlite_sequence WHERE name='{table}'")

    # 2. SEED PENGATURAN
    c.execute("""INSERT INTO pengaturan (id, nama_sekolah, alamat_sekolah, logo) 
                 VALUES (1, 'SMP NEGERI 2 RUMBIO JAYA', 'Jl. Pendidikan No. 45, Rumbio Jaya, Kampar, Riau', 'default_logo.png')""")

    # 3. SEED USERS (Admin & Operator)
    pw_hash = generate_password_hash('password123')
    
    users_staff = [
        ('admin', pw_hash, 'Admin Sekolah', 'Administrator Utama'),
        ('operator', pw_hash, 'Operator Sekolah', 'Staf Tata Usaha'),
    ]
    
    for u in users_staff:
        qr = generate_qr_dummy(f"{u[0]}|{u[3]}")
        c.execute("INSERT INTO users (username, password, role, nama_lengkap, qr_code) VALUES (?,?,?,?,?)", 
                  (u[0], u[1], u[2], u[3], qr))

    # 4. SEED GURU & USER GURU
    nama_guru = [
        ('197901052007101005', 'SAMSUAR, S. PD'),
        ('198205122010012015', 'NURBAITI, M. PD'),
        ('197508201999031002', 'AHMAD JUNAIDI, S. AG'),
        ('199002252015042001', 'SITI AMINAH, S. PD'),
        ('198811102012121003', 'BUDI SANTOSO, S. KOM')
    ]

    guru_ids = []
    for nip, nama in nama_guru:
        username = nip
        qr = generate_qr_dummy(f"{username}|{nama}")
        # Insert User
        c.execute("INSERT INTO users (username, password, role, nama_lengkap, qr_code) VALUES (?,?,?,?,?)", 
                  (username, pw_hash, 'Wali Kelas', nama, qr))
        user_id = c.lastrowid
        # Insert Guru
        c.execute("INSERT INTO guru (nip, nama, user_id) VALUES (?,?,?)", (nip, nama, user_id))
        guru_ids.append(c.lastrowid)

    # 5. SEED KELAS
    nama_kelas = ['VII-1', 'VII-2', 'VIII-1', 'VIII-2', 'IX-1']
    kelas_ids = []
    for i, n_kelas in enumerate(nama_kelas):
        c.execute("INSERT INTO kelas (nama_kelas, wali_kelas_id) VALUES (?,?)", (n_kelas, guru_ids[i]))
        kelas_ids.append(c.lastrowid)

    # 6. SEED SISWA & USER WALI SISWA
    nama_siswa = [
        ('2021001', 'ADITYA PRATAMA'), ('2021002', 'ANNISA FITRI'), ('2021003', 'BAGAS SAPUTRA'),
        ('2021004', 'CHINTYA BELLA'), ('2021005', 'DEDI KURNIAWAN'), ('2021006', 'EKA PUTRI'),
        ('2021007', 'FAHRI RAMADHAN'), ('2021008', 'GITA LESTARI'), ('2021009', 'HENDRA WIJAYA'),
        ('2021010', 'INDAH PERMATA'), ('2021011', 'JOKO SUSILO'), ('2021012', 'KIKI AMALIA'),
        ('2021013', 'LUKMAN HAKIM'), ('2021014', 'MAYA SOPHIA'), ('2021015', 'NANDA RIZKI')
    ]

    siswa_user_ids = []
    for i, (nisn, nama) in enumerate(nama_siswa):
        # Setiap 3 siswa masuk ke kelas yang berbeda
        kelas_id = kelas_ids[i // 3]
        qr = generate_qr_dummy(f"{nisn}|{nama}")
        
        # Insert User (Role Wali Siswa)
        c.execute("INSERT INTO users (username, password, role, nama_lengkap) VALUES (?,?,?,?)", 
                  (nisn, pw_hash, 'Wali Siswa', f'Wali dari {nama}'))
        user_id = c.lastrowid
        siswa_user_ids.append(user_id)
        
        # Insert Siswa
        c.execute("INSERT INTO siswa (nisn, nama, kelas_id, status_aktif, qr_code, user_id) VALUES (?,?,?,1,?,?)", 
                  (nisn, nama, kelas_id, qr, user_id))

    # 7. SEED ABSENSI (Data 7 Hari Terakhir)
    print("Sedang menggenerasi data absensi historis...")
    status_pilihan = ['Hadir', 'Hadir', 'Hadir', 'Hadir', 'Hadir', 'Ijin', 'Sakit', 'Alpa']
    
    # Ambil semua user yang butuh absen (Siswa + Guru)
    c.execute("SELECT id, role FROM users WHERE role IN ('Wali Kelas', 'Wali Siswa', 'Guru')")
    all_users = c.fetchall()

    for day_offset in range(7):
        tanggal = date.today() - timedelta(days=day_offset)
        # Jangan absen di hari Minggu
        if tanggal.weekday() == 6: continue 

        for user in all_users:
            status = random.choice(status_pilihan)
            
            if status == 'Hadir':
                jam_m = f"07:{random.randint(10,59)}:00"
                jam_p = f"14:{random.randint(0,30)}:00"
                c.execute("INSERT INTO absensi (user_id, tanggal, jam_masuk, jam_pulang, status) VALUES (?,?,?,?,?)",
                          (user['id'], tanggal.strftime('%Y-%m-%d'), jam_m, jam_p, status))
            else:
                ket = "Keterangan dummy" if status != 'Alpa' else ""
                c.execute("INSERT INTO absensi (user_id, tanggal, status, keterangan) VALUES (?,?,?,?)",
                          (user['id'], tanggal.strftime('%Y-%m-%d'), status, ket))

    conn.commit()
    conn.close()
    print("--- SUCCESS ---")
    print("Database berhasil diisi dengan data dummy!")
    print("Gunakan password: 'password123' untuk semua akun.")

if __name__ == '__main__':
    seed_data()