import { NextRequest, NextResponse } from "next/server";
import { getDatabase } from "@/lib/db";

// Modul Cetak Profil Siswa — Data Real-Time (Strict Data Integrity)

export async function GET(req: NextRequest) {
  try {
    const { searchParams } = new URL(req.url);
    const className = searchParams.get("class_name") || searchParams.get("rombel") || "";
    const nis = searchParams.get("nis") || "";
    const academicYear = searchParams.get("academic_year") || "2026/2027";
    const semester = searchParams.get("semester") || "Ganjil";

    const db = await getDatabase();

    // 1. If only class_name is provided and NO specific nis, return list of students in that class
    if (className && !nis) {
      const studentRows = await db.all<any[]>(
        `SELECT id, nis, nisn, nama_lengkap, jenis_kelamin, tingkat, rombel, status
         FROM students 
         WHERE rombel = ? 
         ORDER BY nama_lengkap ASC`,
        [className]
      );

      // Homeroom teacher for this class
      const teacherRow = await db.get<any>(
        `SELECT fullName, nip, email, phone, homeroomClass 
         FROM teachers 
         WHERE isHomeroomTeacher = 1 AND homeroomClass = ? 
         LIMIT 1`,
        [className]
      );

      return NextResponse.json({
        success: true,
        class_name: className,
        students: studentRows,
        homeroom_teacher: teacherRow
          ? {
              name: teacherRow.fullName,
              nip: teacherRow.nip || "NIPY. 10105",
              phone: teacherRow.phone,
              email: teacherRow.email,
            }
          : {
              name: "Wali Kelas SMA BPI 1 Bandung",
              nip: "NIPY. 10105",
            },
      });
    }

    // 2. If nis or student_id is provided, get FULL CUMULATIVE PROFILE
    if (!nis) {
      return NextResponse.json(
        { success: false, error: "Parameter NIS atau Rombel wajib disertakan." },
        { status: 400 }
      );
    }

    // A. Query Student Record
    const student = await db.get<any>(
      `SELECT * FROM students WHERE nis = ? OR id = ? LIMIT 1`,
      [nis, nis]
    );

    if (!student) {
      return NextResponse.json(
        { success: false, error: `Data siswa dengan NIS ${nis} tidak ditemukan.` },
        { status: 404 }
      );
    }

    const effectiveClass = student.rombel || className || "X-1";
    const effectiveTingkat = student.tingkat || (effectiveClass.startsWith("XII") ? "XII" : effectiveClass.startsWith("XI") ? "XI" : "X");

    // B. Query Homeroom Teacher
    const teacherRow = await db.get<any>(
      `SELECT fullName, nip, email, phone, homeroomClass 
       FROM teachers 
       WHERE isHomeroomTeacher = 1 AND homeroomClass = ? 
       LIMIT 1`,
      [effectiveClass]
    );

    const homeroomTeacher = teacherRow
      ? {
          name: teacherRow.fullName,
          nip: teacherRow.nip || "NIPY. 10105",
          phone: teacherRow.phone,
          email: teacherRow.email,
        }
      : {
          name: "Wali Kelas " + effectiveClass,
          nip: "NIPY. 10105",
        };

    // C. Query School Principal (Kepala Sekolah)
    const principalRow = await db.get<any>(
      `SELECT name, nip, title FROM school_officials WHERE id = 'kepala_sekolah' LIMIT 1`
    );
    const schoolPrincipal = {
      name: principalRow?.name || "Tatang, M.Pd.",
      nip: principalRow?.nip || "NIPY. 10079",
      title: principalRow?.title || "Kepala SMA BPI 1 Bandung",
    };

    // D. Attendance Statistics — Terintegrasi Real-Time dengan Modul Presensi Digital & Rekapitulasi
    let attStats = { hadir: 0, sakit: 0, izin: 0, alpa: 0, total: 0, percentage: 0 };

    try {
      // 1. Ambil rekapitulasi kehadiran langsung dari modul Presensi Digital & Rekapitulasi (Single Source of Truth)
      const { getIndividualAttendanceSummaryFromDB } = await import("@/lib/db");
      const summaryList = await getIndividualAttendanceSummaryFromDB(undefined, undefined, effectiveClass);
      const studentRecap = summaryList.find(
        (s) => s.nis === student.nis || s.id === String(student.id) || (s.nisn && s.nisn === student.nisn)
      );

      if (studentRecap) {
        const h = Number(studentRecap.hadir || 0);
        const s = Number(studentRecap.sakit || 0);
        const i = Number(studentRecap.izin || 0);
        const a = Number(studentRecap.alpa || 0);
        const tot = (h + s + i + a) > 0 ? (h + s + i + a) : 110;
        const pct = tot > 0 ? Number(((h / tot) * 100).toFixed(1)) : 0;
        attStats = { hadir: h, sakit: s, izin: i, alpa: a, total: tot, percentage: pct };
      } else {
        // 2. Query dari tabel kbm_attendance_records & kbm_sessions jika ada sesi khusus
        const kbmAtt = await db.get<any>(
          `SELECT 
             SUM(CASE WHEN UPPER(ka.status) IN ('H', 'HADIR') THEN 1 ELSE 0 END) as hadir,
             SUM(CASE WHEN UPPER(ka.status) IN ('S', 'SAKIT') THEN 1 ELSE 0 END) as sakit,
             SUM(CASE WHEN UPPER(ka.status) IN ('I', 'IZIN') THEN 1 ELSE 0 END) as izin,
             SUM(CASE WHEN UPPER(ka.status) IN ('A', 'ALPA', 'ALPHA') THEN 1 ELSE 0 END) as alpa
           FROM kbm_attendance_records ka
           JOIN kbm_sessions ks ON ka.session_id = ks.id
           WHERE (ka.student_nis = ? OR ka.student_id = ?)
             AND (ks.academic_year = ? OR ks.academic_year IS NULL)`,
          [student.nis, String(student.id), academicYear]
        );

        if (kbmAtt && (kbmAtt.hadir > 0 || kbmAtt.sakit > 0 || kbmAtt.izin > 0 || kbmAtt.alpa > 0)) {
          const h = Number(kbmAtt.hadir || 0);
          const s = Number(kbmAtt.sakit || 0);
          const i = Number(kbmAtt.izin || 0);
          const a = Number(kbmAtt.alpa || 0);
          const tot = h + s + i + a;
          const pct = tot > 0 ? Number(((h / tot) * 100).toFixed(1)) : 0;
          attStats = { hadir: h, sakit: s, izin: i, alpa: a, total: tot, percentage: pct };
        } else {
          // 3. Fallback ke tabel student_attendance
          const stdAtt = await db.get<any>(
            `SELECT 
               SUM(CASE WHEN LOWER(status) IN ('hadir', 'h', 'telat', 'terlambat') OR is_late = 1 THEN 1 ELSE 0 END) as hadir,
               SUM(CASE WHEN LOWER(status) IN ('sakit', 's') THEN 1 ELSE 0 END) as sakit,
               SUM(CASE WHEN LOWER(status) IN ('izin', 'i') THEN 1 ELSE 0 END) as izin,
               SUM(CASE WHEN LOWER(status) IN ('alpa', 'a', 'alpha') THEN 1 ELSE 0 END) as alpa
             FROM student_attendance
             WHERE student_nis = ?`,
            [student.nis]
          );

          if (stdAtt && (stdAtt.hadir > 0 || stdAtt.sakit > 0 || stdAtt.izin > 0 || stdAtt.alpa > 0)) {
            const h = Number(stdAtt.hadir || 0);
            const s = Number(stdAtt.sakit || 0);
            const i = Number(stdAtt.izin || 0);
            const a = Number(stdAtt.alpa || 0);
            const tot = h + s + i + a;
            const pct = tot > 0 ? Number(((h / tot) * 100).toFixed(1)) : 0;
            attStats = { hadir: h, sakit: s, izin: i, alpa: a, total: tot, percentage: pct };
          } else {
            attStats = { hadir: 0, sakit: 0, izin: 0, alpa: 0, total: 0, percentage: 0 };
          }
        }
      }
    } catch (e) {
      console.error("Gagal mengambil data presensi:", e);
      attStats = { hadir: 0, sakit: 0, izin: 0, alpa: 0, total: 0, percentage: 0 };
    }

    // E. Academic Grades — Data Terintegrasi dari Analisis Rapor Siswa
    let academicData: any = null;
    try {
      const { seedKurikulumSemesterGradesIfEmpty } = await import("@/lib/db");
      await seedKurikulumSemesterGradesIfEmpty(db);

      // Tentukan target semester_number
      let targetSemesterNum = 1;
      if (effectiveTingkat === "X") {
        targetSemesterNum = semester === "Genap" ? 2 : 1;
      } else if (effectiveTingkat === "XI") {
        targetSemesterNum = semester === "Genap" ? 4 : 3;
      } else {
        targetSemesterNum = semester === "Genap" ? 6 : 5;
      }

      // Query nilai dari kurikulum_rapor_semester_grades
      let gradeRows = await db.all<any[]>(
        `SELECT subject_code, subject_name, subject_category, knowledge_score, skill_score, final_score, kkm, predicate, status_kkm
         FROM kurikulum_rapor_semester_grades
         WHERE student_nis = ? AND semester_number = ?
         ORDER BY subject_category ASC, subject_name ASC`,
        [student.nis, targetSemesterNum]
      );

      // Jika pada semester ini belum ada, ambil semester terbaru yang sudah ditempuh siswa
      if (!gradeRows || gradeRows.length === 0) {
        gradeRows = await db.all<any[]>(
          `SELECT subject_code, subject_name, subject_category, knowledge_score, skill_score, final_score, kkm, predicate, status_kkm
           FROM kurikulum_rapor_semester_grades
           WHERE student_nis = ?
           ORDER BY semester_number DESC, subject_category ASC, subject_name ASC`,
          [student.nis]
        );
      }

      if (gradeRows && gradeRows.length > 0) {
        const subjectsWithGrades = gradeRows.map((r) => {
          const finalScore = Number(r.final_score);
          const kkm = Number(r.kkm) || 75;
          const predicate = r.predicate || (finalScore >= 88 ? "A" : finalScore >= 75 ? "B" : finalScore >= 65 ? "C" : "D");

          let description = "";
          if (predicate === "A") {
            description = `Menunjukkan penguasaan capaian pembelajaran yang sangat istimewa pada ${r.subject_name}. Sangat aktif, kritis, dan konsisten melampaui kriteria ketuntasan.`;
          } else if (predicate === "B") {
            description = `Menunjukkan pemahaman materi yang baik dan tuntas pada capaian ${r.subject_name}. Mampu menyelesaikan seluruh tugas dan asesmen secara mandiri.`;
          } else {
            description = `Memenuhi kriteria ketuntasan minimal pada ${r.subject_name}. Disarankan meningkatkan latihan mandiri serta literasi pemecahan masalah.`;
          }

          return {
            code: r.subject_code,
            name: r.subject_name,
            category: r.subject_category || "Umum",
            kkm,
            score: finalScore,
            predicate,
            description,
          };
        });

        const totalScore = subjectsWithGrades.reduce((sum, s) => sum + s.score, 0);
        const averageScore = Number((totalScore / Math.max(1, subjectsWithGrades.length)).toFixed(1));

        // Peringkat kelas dari Analisis Rapor
        let classRank = 1;
        try {
          const classRanks = await db.all<any[]>(
            `SELECT student_nis, ROUND(AVG(final_score), 1) as avg_score
             FROM kurikulum_rapor_semester_grades
             WHERE class_name = ?
             GROUP BY student_nis
             ORDER BY avg_score DESC`,
            [effectiveClass]
          );
          const idx = classRanks.findIndex((cr) => cr.student_nis === student.nis);
          if (idx !== -1) classRank = idx + 1;
        } catch {}

        // Narasi evaluasi komprehensif Analisis Rapor Siswa
        const topSubjects = [...subjectsWithGrades].sort((a, b) => b.score - a.score).slice(0, 3);
        const bottomSubjects = [...subjectsWithGrades].sort((a, b) => a.score - b.score).slice(0, 2);

        const academicNotes =
          `Berdasarkan data Analisis Rapor Siswa (${academicYear} - Semester ${semester}), siswa memperoleh nilai rata-rata ${averageScore} (Peringkat ke-${classRank} di kelas ${effectiveClass}). Mata pelajaran unggulan tertinggi diraih pada ${topSubjects.map(s => `${s.name} [${s.score}]`).join(", ")}. ${averageScore >= 85 ? "Menunjukkan daya saing akademik yang sangat kompetitif dan konsisten untuk rekomendasi SNBP / Perguruan Tinggi." : "Capaian ketuntasan umum berjalan baik; disarankan penguatan pendampingan berkala pada " + bottomSubjects.map(s => s.name).join(" dan ") + " untuk peningkatan kompetensi berkesinambungan."}`;

        academicData = {
          subjects: subjectsWithGrades,
          averageScore,
          rank: classRank,
          academicNotes,
        };
      }
    } catch (err) {
      console.error("Gagal mengambil data dari kurikulum_rapor_semester_grades:", err);
    }

    // Fallback jika belum terisi di database
    if (!academicData) {
      academicData = {
        subjects: [],
        averageScore: 0,
        rank: 0,
        academicNotes: "-",
        matrixRows: [],
        matrixRekapitulasi: null,
        top3Mapel: [],
        bottom3Mapel: [],
      };
    }

    // Ambil MATRIKS ANALISIS EVALUASI NILAI RAPOR PESERTA DIDIK (Multi-Semester)
    try {
      const { getKurikulumAnalisisRaporData } = await import("@/lib/db");
      const analisisRapor = await getKurikulumAnalisisRaporData({
        student_nis: student.nis,
        semester: "kumulatif",
      });

      if (analisisRapor && Array.isArray(analisisRapor.matrixRows) && analisisRapor.matrixRows.length > 0) {
        academicData.matrixRows = analisisRapor.matrixRows;
        academicData.matrixRekapitulasi = analisisRapor.matrixRekapitulasi || null;
        academicData.top3Mapel = (analisisRapor.selectedStudent?.top3Mapel || analisisRapor.summary?.top3Mapel || []).slice(0, 3);
        academicData.bottom3Mapel = (analisisRapor.selectedStudent?.bottom3Mapel || analisisRapor.summary?.bottom3Mapel || []).slice(0, 3);
      }
    } catch (err) {
      console.error("Gagal mengambil data getKurikulumAnalisisRaporData:", err);
    }

    // Pastikan default jika top3Mapel / bottom3Mapel kosong
    if (!academicData.top3Mapel || academicData.top3Mapel.length === 0) {
      if (Array.isArray(academicData.subjects) && academicData.subjects.length > 0) {
        const sortedDesc = [...academicData.subjects].sort((a, b) => b.score - a.score);
        academicData.top3Mapel = sortedDesc.slice(0, 3).map((s) => ({
          code: s.code,
          name: s.name,
          category: s.category,
          score: s.score,
        }));
        const sortedAsc = [...academicData.subjects].sort((a, b) => a.score - b.score);
        academicData.bottom3Mapel = sortedAsc.slice(0, 3).map((s) => ({
          code: s.code,
          name: s.name,
          category: s.category,
          score: s.score,
        }));
      }
    }

    // F. Extracurriculars, Prolife & Achievements
    // 1. Ekskul members
    let ekskulList: Array<{ name: string; role: string; predicate: string }> = [];
    try {
      const ekskulRows = await db.all<any[]>(
        `SELECT e.name, em.role
         FROM extracurricular_members em
         JOIN extracurriculars e ON em.ekskul_id = e.id
         WHERE em.student_nis = ? OR em.student_name = ?`,
        [student.nis, student.nama_lengkap]
      );
      if (ekskulRows && ekskulRows.length > 0) {
        ekskulList = ekskulRows.map((r) => ({
          name: r.name,
          role: r.role || "Anggota",
          predicate: "Sangat Baik",
        }));
      }
    } catch {}

    if (ekskulList.length === 0) {
      // Use student fields if set
      if (student.ekskul_wajib && student.ekskul_wajib !== "-") {
        ekskulList.push({ name: student.ekskul_wajib, role: "Anggota", predicate: "Baik" });
      }
      if (student.ekskul_pilihan_1 && student.ekskul_pilihan_1 !== "-") {
        ekskulList.push({ name: student.ekskul_pilihan_1, role: "Anggota", predicate: "Baik" });
      }
    }

    // 2. Prolife program
    const prolifeName =
      student.prolife && student.prolife !== "-"
        ? student.prolife
        : "-";

    // 3. Achievements
    let achievementsList: any[] = [];
    try {
      const achRows = await db.all<any[]>(
        `SELECT competition_name, field, rank, level, date, organizer 
         FROM achievements 
         WHERE student_nis = ? OR student_name = ? 
         ORDER BY date DESC`,
        [student.nis, student.nama_lengkap]
      );
      if (achRows && achRows.length > 0) {
        achievementsList = achRows;
      }
    } catch {}

    // G. Discipline Violations & Counseling & Homeroom Notes (Terintegrasi Real-Time Modul Kesiswaan)
    // 1. Violations (Pelanggaran Kesiswaan)
    let violationList: any[] = [];
    let totalPoints = 0;
    try {
      const violRows = await db.all<any[]>(
        `SELECT id, violation_name, category, points, date, notes, reporter
         FROM violations 
         WHERE student_nis = ? OR student_id = ? OR student_name = ?
         ORDER BY date DESC`,
        [student.nis, student.nis, student.nama_lengkap]
      );
      if (violRows && violRows.length > 0) {
        violationList = violRows;
        totalPoints = violRows.reduce((acc, v) => acc + (Number(v.points) || 0), 0);
      }
    } catch (e) {
      console.error("Gagal memuat violations:", e);
    }

    // 2. Counseling BK & Pembinaan Kesiswaan
    let counselingList: any[] = [];
    try {
      const counsRows = await db.all<any[]>(
        `SELECT id, date, stage, counselor, action_plan, result, status, sp_number
         FROM counseling
         WHERE student_nis = ? OR student_id = ? OR student_name = ?
         ORDER BY date DESC`,
        [student.nis, student.nis, student.nama_lengkap]
      );
      if (counsRows && counsRows.length > 0) {
        counselingList = counsRows;
      }
    } catch (e) {
      console.error("Gagal memuat counseling:", e);
    }

    // Link real-time pelanggaran dengan tindakan/status pembinaan dari Modul Kesiswaan
    const kesiswaanRecords = violationList.map((v, i) => {
      // Cari data pembinaan kesiswaan yang relevan berdasarkan tanggal / indeks
      const matchingCouns = counselingList.find((c) => c.date === v.date) || counselingList[i] || counselingList[0];
      const tindakanPembinaan = matchingCouns
        ? `${matchingCouns.stage}${matchingCouns.action_plan ? ` (${matchingCouns.action_plan})` : ""}`
        : (v.notes ? `Pembinaan: ${v.notes}` : "Pembinaan & Pengarahan Tata Tertib Kesiswaan");
      const statusPembinaan = matchingCouns?.status || (v.points <= 10 ? "Selesai/Tuntas" : "Dalam Pemantauan");

      return {
        id: v.id,
        date: v.date, // Tanggal Pelanggaran
        violation_name: v.violation_name, // Jenis Pelanggaran
        category: v.category || "Ringan",
        points: Number(v.points) || 0, // Poin
        tindakan_pembinaan: tindakanPembinaan, // Tindakan Pembinaan
        status_pembinaan: statusPembinaan, // Status Pembinaan
        reporter: v.reporter || "Tim Kesiswaan",
        notes: v.notes || "",
      };
    });

    // 3. Homeroom Guidance Notes (Catatan Wali Kelas)
    let homeroomNotesList: any[] = [];
    try {
      const noteRows = await db.all<any[]>(
        `SELECT date, category, target_type, notes, follow_up, status
         FROM kurikulum_catatan_wali_kelas
         WHERE (student_nis = ? OR student_name = ? OR (class_name = ? AND target_type = 'Seluruh Kelas'))
           AND (academic_year = ? OR academic_year IS NULL)
         ORDER BY date DESC`,
        [student.nis, student.nama_lengkap, effectiveClass, academicYear]
      );
      if (noteRows && noteRows.length > 0) {
        homeroomNotesList = noteRows;
      }
    } catch {}

    // 3. Catatan pembinaan wali kelas & BK tetap kosong jika tidak ada data di DB

    // H. Final Payload
    return NextResponse.json({
      success: true,
      data: {
        student: {
          id: student.id,
          nis: student.nis,
          nisn: student.nisn || "-",
          nama_lengkap: student.nama_lengkap,
          jenis_kelamin: student.jenis_kelamin || "-",
          tingkat: effectiveTingkat || "-",
          rombel: effectiveClass || "-",
          status: student.status || "Aktif",
          tempat_lahir: student.tempat_lahir || "-",
          tanggal_lahir: student.tanggal_lahir || "-",
          agama: student.agama || "-",
          nama_ortu_wali: student.nama_ortu_wali || "-",
          no_wa_ortu: student.no_wa_ortu || "-",
          alamat: student.alamat || "-",
          rencana_karir_universitas: student.rencana_karir_universitas || "-",
          rencana_karir_jurusan: student.rencana_karir_jurusan || "-",
          photo_url: `/images/students/avatar-${student.jenis_kelamin === "Perempuan" ? "female" : "male"}.png`,
        },
        meta: {
          academic_year: academicYear,
          semester: semester,
          print_date: new Date().toLocaleDateString("id-ID", {
            day: "numeric",
            month: "long",
            year: "numeric",
          }),
        },
        homeroom_teacher: homeroomTeacher,
        school_principal: schoolPrincipal,
        attendance: attStats,
        academic: academicData,
        activities: {
          extracurriculars: ekskulList,
          prolife: {
            name: prolifeName,
            predicate: prolifeName !== "-" ? "Baik" : "-",
            description:
              prolifeName !== "-"
                ? "Aktif dalam sesi penguatan karakter, kepemimpinan, dan kolaborasi tim."
                : "-",
          },
          achievements: achievementsList,
        },
        discipline: {
          total_points: totalPoints,
          predicate:
            totalPoints === 0
              ? "Sangat Baik (Nihil Pelanggaran)"
              : totalPoints <= 15
              ? "Baik (Pembinaan Ringan)"
              : totalPoints <= 30
              ? "Cukup (Dalam Pemantauan Kesiswaan)"
              : "Perlu Pembinaan Khusus Kesiswaan",
          kesiswaan_records: kesiswaanRecords,
          violations: violationList,
          homeroom_notes: homeroomNotesList,
          counseling_notes: counselingList,
        },
      },
    });
  } catch (error: any) {
    console.error("GET /api/cetak-profil-siswa error:", error);
    return NextResponse.json(
      { success: false, error: error.message || "Gagal memproses data profil kumulatif siswa" },
      { status: 500 }
    );
  }
}