"""
Generate SQL seed data untuk 52 siswa (sampel penelitian).
- 26 siswa XII A (15 L, 11 P)
- 26 siswa XII B (14 L, 12 P)
- Setiap siswa: akun users + data siswa + 1 konsultasi + 15 jawaban + hasil identifikasi
- Jawaban dirancang agar hasil CF bervariasi: rendah, sedang, tinggi
"""
import hashlib, os, random, json

random.seed(42)  # reproducible

# ========================
# NAMA SISWA (realistis)
# ========================
NAMES_L = [
    "Adrian Ferdinan Devianto","Ahmad Fauzan Hidayat","Bagus Setiawan","Candra Wijaya",
    "Dimas Pratama Aditya","Eko Saputro","Fajar Nugraha","Galih Mahendra",
    "Hendra Gunawan","Irfan Maulana","Joko Susilo","Krisna Aditya Pratama",
    "Lukman Hakim","Mhd. Fadli Akbar","Nur Hidayatullah","Oki Dermawan",
    "Putra Bangsa Wijaya","Rizki Ramadhan","Surya Darma Saputra","Taofik Hidayat",
    "Ulil Amri","Vino Bastian Pratama","Wahyu Nugroho","Yusuf Maulana",
    "Zainal Abidin","Rahmat Hidayat","Andi Muh. Rifaldi","Bayu Aji Santoso",
    "Doni Kurniawan",
]
NAMES_P = [
    "Anisa Putri Maharani","Bunga Lestari","Citra Ayu Lestari","Dewi Sartika",
    "Eka Pratiwi","Fitriani Rahmawati","Gita Anggraini","Hesti Purnama",
    "Indah Permata Sari","Jihan Salsabila","Kartika Sari Dewi","Lestari Andini",
    "Maya Sari Putri","Nabila Az-Zahra","Olivia Maheswari","Putri Ayu Ningsih",
    "Qori Aulia","Rina Marlina","Siti Nurhaliza","Tika Wulandari",
    "Ulfa Zahra","Vivi Andriani","Wati Susanti","Yuni Astuti",
    "Zahra Khairunnisa","Nurul Magfira","Fitriani Andi Masse","Rahmawati Putri",
]

# ========================
# PROFIL JAWABAN (max nilai per gejala)
# ========================
# Setiap profil: [max_G01, max_G02, max_G03, max_G04, max_G05]
# Dari analisis: cf_pakar = (4-skala)/3 → G01=0.3333, G02=0.6667, G03=0.6667, G04=1.0, G05=1.0
# cf_user = (nilai-1)/3 → 1=0.0, 2=0.3333, 3=0.6667, 4=1.0
# cf_gejala = cf_pakar * cf_user
# cf_akhir = kombinasi saturasi

PROFILES = [
    # RENDAH (target: 0-39%) — 16 siswa
    ([1,1,1,1,1], "Rendah - tidak ada gejala"),
    ([2,1,1,1,1], "Rendah - G01 ringan"),
    ([2,1,1,1,1], "Rendah - G01 ringan"),
    ([1,2,1,1,1], "Rendah - G02 ringan"),
    ([2,2,1,1,1], "Rendah - G01-G02 ringan"),
    ([2,2,1,1,1], "Rendah - G01-G02 ringan"),
    ([2,1,2,1,1], "Rendah - G01,G03 ringan"),
    ([1,2,2,1,1], "Rendah - G02,G03 ringan"),
    ([2,2,2,1,1], "Rendah-Sedang boundary"),  # ~46% → Sedang actually
    ([1,1,1,2,1], "Rendah - G04 ringan"),
    ([2,1,1,2,1], "Rendah - G01,G04 ringan"),
    ([1,2,1,2,1], "Rendah - G02,G04 ringan"),
    ([1,1,1,1,2], "Rendah - G05 ringan"),
    ([2,1,1,1,2], "Rendah - G01,G05 ringan"),
    ([1,1,2,1,2], "Rendah - G03,G05 ringan"),
    ([2,2,1,1,2], "Rendah - G01,G02,G05 ringan"),
    # SEDANG (target: 40-70%) — 20 siswa
    ([2,2,2,1,1], "Sedang - G01-G03 sedang"),  # ~46%
    ([2,2,2,1,1], "Sedang - G01-G03 sedang"),
    ([2,2,2,2,1], "Sedang - G01-G04 sedang"),  # ~64%
    ([2,2,2,2,1], "Sedang - G01-G04 sedang"),
    ([2,2,2,1,2], "Sedang - G01-G03,G05 sedang"),
    ([3,2,1,1,1], "Sedang - G01 tinggi"),      # ~39% boundary
    ([3,2,2,1,1], "Sedang - G01-G03"),
    ([3,2,2,1,1], "Sedang - G01-G03"),
    ([3,3,2,1,1], "Sedang - G01-G02 tinggi"),  # ~66%
    ([3,3,2,1,1], "Sedang - G01-G02 tinggi"),
    ([3,2,2,2,1], "Sedang - G01-G04"),
    ([2,3,2,2,1], "Sedang - G02 tinggi"),
    ([2,2,3,2,1], "Sedang - G03 tinggi"),
    ([3,2,1,2,1], "Sedang - G01,G04"),
    ([2,3,2,1,2], "Sedang - G02,G05"),
    ([2,2,2,2,2], "Sedang-Tinggi boundary"),   # ~76% → Tinggi
    ([3,2,2,2,1], "Sedang - G01-G04 mix"),
    ([2,3,3,1,1], "Sedang - G02-G03 tinggi"),
    ([3,2,3,1,1], "Sedang - G01,G03 tinggi"),
    ([2,2,2,3,1], "Sedang - G04 tinggi"),
    ([2,2,2,1,3], "Sedang - G05 tinggi"),
    # TINGGI (target: >70%) — 16 siswa
    ([3,3,3,2,2], "Tinggi - sedang-tinggi"),   # ~89%
    ([3,3,3,2,2], "Tinggi - sedang-tinggi"),
    ([3,3,3,3,2], "Tinggi - G01-G04 tinggi"),  # ~95%
    ([3,3,3,2,3], "Tinggi - G01-G03,G05"),
    ([4,3,3,2,2], "Tinggi - G01 sangat tinggi"),
    ([3,4,3,2,2], "Tinggi - G02 sangat tinggi"),
    ([3,3,4,2,2], "Tinggi - G03 sangat tinggi"),
    ([4,3,3,3,2], "Tinggi - G01,G04 tinggi"),
    ([3,3,3,3,3], "Tinggi - semua tinggi"),    # ~97%
    ([4,4,3,2,2], "Tinggi - G01-G02 sangat tinggi"),
    ([4,3,4,2,2], "Tinggi - G01,G03 sangat tinggi"),
    ([3,4,4,2,2], "Tinggi - G02,G03 sangat tinggi"),
    ([4,4,4,3,3], "Tinggi - hampir semua sangat tinggi"),  # ~99%
    ([4,4,4,3,3], "Tinggi - hampir semua sangat tinggi"),
    ([4,4,4,4,3], "Tinggi - sangat tinggi"),
    ([4,4,4,3,4], "Tinggi - sangat tinggi"),
]

# ========================
# CF CALCULATION
# ========================
SKALA_PAKAR = {"G01": 3, "G02": 2, "G03": 2, "G04": 1, "G05": 1}
GEJALA_CODES = ["G01", "G02", "G03", "G04", "G05"]


def cf_pakar(skala: int) -> float:
    """Sesuai mesin CF PHP: (4 - skala) / 3."""
    return (4 - skala) / 3.0


def cf_user(nilai: int) -> float:
    """Sesuai mesin CF PHP: (nilai - 1) / 3."""
    return (nilai - 1) / 3.0


def calculate_cf(max_per_gejala):
    """Hitung CF per gejala, CF akhir, persentase, kategori — identik dengan mesin CF PHP.

    Aturan:
      CF_user   = (nilai - 1) / 3
      CF_pakar  = (4 - skala_pakar) / 3
      CF_gejala = CF_pakar * CF_user  (menggunakan nilai max per gejala)
      CF_akhir  = kombinasi saturasi iteratif
      Persentase = CF_akhir * 100 (dibulatkan 2 desimal)
    """
    # CF per gejala — hitung pakai float penuh, baru bulatkan hanya untuk penyimpanan JSON
    cf_per_gejala_float = {}
    for i, kode in enumerate(GEJALA_CODES):
        skala = SKALA_PAKAR[kode]
        cf_gejala = cf_pakar(skala) * cf_user(max_per_gejala[i])
        cf_per_gejala_float[kode] = cf_gejala

    # Kombinasi saturasi (identik dengan CertaintyFactor::cfAkhir)
    cf_akhir = 0.0
    for kode in GEJALA_CODES:
        cf = cf_per_gejala_float[kode]
        cf_akhir = cf + cf_akhir * (1 - cf)

    persentase = round(cf_akhir * 100, 2)

    # Kategori (sama dengan mesin CF + threshold default 40/70)
    if persentase < 40:
        kategori = "rendah"
    elif persentase <= 70:
        kategori = "sedang"
    else:
        kategori = "tinggi"

    # Bulatkan cf_per_gejala untuk disimpan di JSON (sesuai format seed yang ada)
    cf_per_gejala_rounded = {k: round(v, 4) for k, v in cf_per_gejala_float.items()}
    cf_akhir_rounded = round(cf_akhir, 4)

    return cf_per_gejala_rounded, cf_akhir_rounded, persentase, kategori

# ========================
# GENERATE INDIVIDUAL ANSWERS
# ========================
def generate_answers(max_per_gejala):
    """Generate 3 jawaban per gejala dimana max = target."""
    pernyataan_ids = list(range(1, 16))  # P1-P15 → id 1-15
    answers = []
    for g_idx, max_val in enumerate(max_per_gejala):
        # 3 pernyataan per gejala: ids = g_idx*3+1, g_idx*3+2, g_idx*3+3
        for j in range(3):
            if j == 0:
                answers.append(max_val)  # pastikan max tercapai
            else:
                # random antara 1 dan max_val
                answers.append(random.randint(1, max_val))
    return answers  # 15 values for P1-P15

# ========================
# GENERATE SQL
# ========================
SARAN = {
    "rendah": "Tingkat adiksi media sosial Anda tergolong RENDAH. Pertahankan pola penggunaan yang sehat: tetap perhatikan waktu layar, tidur, dan kegiatan belajar Anda.",
    "sedang": "Tingkat adiksi media sosial Anda tergolong SEDANG. Mulailah membatasi waktu penggunaan harian, atur jadwal bermain media sosial, dan sempatkan kegiatan di dunia nyata. Bila diperlukan, konsultasikan dengan guru BK.",
    "tinggi": "Tingkat adiksi media sosial Anda tergolong TINGGI. Segera kurangi intensitas penggunaan, libatkan orang tua dan guru BK untuk pendampingan, dan cari bantuan profesional bila gejala berlanjut.",
}

# Build student list
students = []
name_idx_l = 0
name_idx_p = 0
nis_base = 20220304001

# XII A: 26 siswa (15 L, 11 P)
for i in range(15):
    students.append(("XII A", "L", NAMES_L[name_idx_l], nis_base + i))
    name_idx_l += 1
for i in range(11):
    students.append(("XII A", "P", NAMES_P[name_idx_p], nis_base + 15 + i))
    name_idx_p += 1

# XII B: 26 siswa (14 L, 12 P) 
for i in range(14):
    students.append(("XII B", "L", NAMES_L[name_idx_l], nis_base + 26 + i))
    name_idx_l += 1
for i in range(12):
    students.append(("XII B", "P", NAMES_P[name_idx_p], nis_base + 40 + i))
    name_idx_p += 1

# Assign profiles (shuffle slightly for realism)
profile_indices = list(range(len(PROFILES)))
random.shuffle(profile_indices)

# ========================
# OUTPUT SQL
# ========================
lines = []
lines.append("-- ============================================================")
lines.append("-- SisPak — Seed Data 52 Siswa Sampel Penelitian")
lines.append("-- Kelas XII A (26 siswa) & XII B (26 siswa)")
lines.append("-- Dibuat untuk testing/uji coba sistem")
lines.append("-- ============================================================")
lines.append("SET NAMES utf8mb4;")
lines.append("USE sispak_adiksi;")
lines.append("")
lines.append("-- Hapus SELURUH data siswa lama agar seed idempoten (termasuk akun demo")
lines.append("-- `siswa01` dari database.sql yang memakai NIS 20220304039 — dulu menyebabkan")
lines.append("-- ERROR 1062 Duplicate entry saat impor). Akun admin tidak disentuh.")
lines.append("DELETE FROM hasil_identifikasi;")
lines.append("DELETE FROM jawaban;")
lines.append("DELETE FROM konsultasi;")
lines.append("DELETE FROM siswa;")
lines.append("DELETE FROM users WHERE role = 'siswa';")
lines.append("")
lines.append("-- ============================================================")
lines.append("-- AKUN SISWA + DATA SISWA")
lines.append("-- ============================================================")

user_id = 3  # setelah admin(1) dan siswa01(2)
siswa_id = 2  # setelah siswa uji coba(1)
konsultasi_id_start = 2  # setelah uji coba
konsultasi_id = konsultasi_id_start
hasil_id = 2

# Stats
stats = {"rendah": 0, "sedang": 0, "tinggi": 0}
all_results = []

for idx, (kelas, jk, nama, nis) in enumerate(students):
    profile_idx = profile_indices[idx] if idx < len(profile_indices) else 0
    max_gejala, desc = PROFILES[profile_idx]
    cf_gejala, cf_akhir, persentase, kategori = calculate_cf(max_gejala)
    stats[kategori] += 1
    
    # Generate username
    username = f"siswa{idx+2:02d}"
    # Generate password hash (using PHP password_hash format — we'll use a fixed hash)
    # Default password: Siswa123!
    password_hash = "$2y$10$KdrQPfw4u7sCc55MSRxT6umiONXc8ANIme/8tjM2tUdjoWF9CwhCu"
    
    # Generate individual answers
    answers = generate_answers(max_gejala)
    
    # Format tanggal (sebar dalam Juni-Agustus 2026)
    month = random.choice([6, 7, 8])
    day = random.randint(1, 28)
    hour = random.randint(8, 16)
    minute = random.randint(0, 59)
    tanggal = f"2026-{month:02d}-{day:02d} {hour:02d}:{minute:02d}:00"
    
    # Insert user
    lines.append(f"INSERT INTO users (id, nama, username, password_hash, role) VALUES")
    lines.append(f"({user_id}, '{nama}', '{username}', '{password_hash}', 'siswa');")
    
    # Insert siswa
    lines.append(f"INSERT INTO siswa (id, user_id, nis, nama, kelas, jk) VALUES")
    lines.append(f"({siswa_id}, {user_id}, '{nis}', '{nama}', '{kelas}', '{jk}');")
    
    # Insert konsultasi
    lines.append(f"INSERT INTO konsultasi (id, siswa_id, mulai_pada, selesai_pada, status) VALUES")
    lines.append(f"({konsultasi_id}, {siswa_id}, '{tanggal}', '{tanggal}', 'selesai');")
    
    # Insert 15 jawaban
    lines.append(f"INSERT INTO jawaban (konsultasi_id, pernyataan_id, nilai, cf_user) VALUES")
    jwb_values = []
    for p_idx in range(15):
        nilai = answers[p_idx]
        cf_u = round((nilai - 1) / 3.0, 4)
        pernyataan_id = p_idx + 1
        jwb_values.append(f"({konsultasi_id}, {pernyataan_id}, {nilai}, {cf_u})")
    lines.append(",\n".join(jwb_values) + ";")
    
    # Insert hasil identifikasi
    cf_json = json.dumps(cf_gejala)
    lines.append(f"INSERT INTO hasil_identifikasi (id, konsultasi_id, siswa_id, cf_per_gejala, cf_akhir, persentase, kategori, saran, dihasilkan_pada) VALUES")
    lines.append(f"({hasil_id}, {konsultasi_id}, {siswa_id}, '{cf_json}', {cf_akhir}, {persentase}, '{kategori}', '{SARAN[kategori]}', '{tanggal}');")
    
    lines.append("")
    
    all_results.append({
        "nama": nama, "kelas": kelas, "jk": jk,
        "persentase": persentase, "kategori": kategori,
        "username": username
    })
    
    user_id += 1
    siswa_id += 1
    konsultasi_id += 1
    hasil_id += 1

# Summary
lines.append("-- ============================================================")
lines.append("-- RINGKASAN HASIL")
lines.append("-- ============================================================")
lines.append(f"-- Total siswa: {len(students)}")
lines.append(f"-- Rendah:  {stats['rendah']} siswa")
lines.append(f"-- Sedang: {stats['sedang']} siswa")
lines.append(f"-- Tinggi: {stats['tinggi']} siswa")
lines.append("")

# Print summary to stdout
print(f"Total siswa: {len(students)}")
print(f"Rendah:  {stats['rendah']} siswa")
print(f"Sedang: {stats['sedang']} siswa")
print(f"Tinggi: {stats['tinggi']} siswa")
print()
print("Detail per siswa:")
for r in all_results:
    print(f"  {r['username']:10s} | {r['nama']:30s} | {r['kelas']} | {r['jk']} | {r['persentase']:6.2f}% | {r['kategori'].upper()}")

# Write SQL file
seed_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'sql', 'seed_siswa_52.sql')
with open(seed_path, 'w', encoding='utf-8') as f:
    f.write("\n".join(lines))

print(f"\nSQL file: sql/seed_siswa_52.sql")
print(f"Total lines: {len(lines)}")