import * as XLSX from "xlsx";
import { ImportPreviewRow } from "@/app/(dashboard)/siswa/page";
import { mockAvailableClasses } from "@/data/mockStudentData";
import { SKLRecord, OFFICIAL_SKL_SUBJECTS, calculateSKLAverage } from "@/data/mockSKLData";

export const STUDENT_TEMPLATE_HEADERS = [
  "NIS",
  "NISN",
  "Nama Lengkap Siswa",
  "Jenis Kelamin (L/P)",
  "Rombel / Kelas",
  "Nama Orang Tua / Wali",
  "No. WhatsApp Orang Tua",
  "Ekskul Wajib",
  "Ekskul Pilihan 1",
  "Ekskul Pilihan 2",
];

export const TEMPLATE_HEADERS = STUDENT_TEMPLATE_HEADERS;

export function downloadStudentTemplateXLSX() {
  const sampleData = [
    STUDENT_TEMPLATE_HEADERS,
    [
      "252610001",
      "0089123450",
      "Fajar Nugraha Pratama",
      "L",
      "X-1",
      "Drs. Gunawan Pratama",
      "081223456789",
      "Pramuka",
      "Futsal",
      "Rohis",
    ],
    [
      "242510050",
      "0079123451",
      "Gita Permata Sari",
      "P",
      "XI-2",
      "Hj. Rina Permata",
      "081334567890",
      "Pramuka",
      "PMR",
      "Fotografi",
    ],
    [
      "232410080",
      "0069123452",
      "Jonathan Kevin",
      "L",
      "XII-5",
      "Kevin Wijaya",
      "081167890123",
      "Pramuka",
      "Robotika",
      "Coding & IT",
    ],
  ];

  const worksheet = XLSX.utils.aoa_to_sheet(sampleData);

  // Set column widths in Excel
  worksheet["!cols"] = [
    { wch: 15 }, // NIS
    { wch: 16 }, // NISN
    { wch: 28 }, // Nama Lengkap Siswa
    { wch: 20 }, // Jenis Kelamin (L/P)
    { wch: 18 }, // Rombel / Kelas
    { wch: 26 }, // Nama Orang Tua / Wali
    { wch: 24 }, // No. WhatsApp Orang Tua
    { wch: 18 }, // Ekskul Wajib
    { wch: 20 }, // Ekskul Pilihan 1
    { wch: 20 }, // Ekskul Pilihan 2
  ];

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Template Siswa BPI 1");

  // Generate binary XLSX download
  XLSX.writeFile(workbook, "Template_Data_Siswa_SMA_BPI_1.xlsx", {
    bookType: "xlsx",
    type: "binary",
  });
}

export function parseExcelFile(file: File): Promise<ImportPreviewRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });

        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        const rawRows: Array<Array<string | number | undefined>> = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          defval: "",
        });

        if (rawRows.length <= 1) {
          resolve([]);
          return;
        }

        const validRombels = new Set(mockAvailableClasses);
        const parsedRows: ImportPreviewRow[] = [];

        // Dynamic header detection
        const headerCols = (rawRows[0] || []).map((c) => String(c).toUpperCase().trim());
        const hasHeaderRow = headerCols.some((c) => c.includes("NIS") || c.includes("NAMA"));

        const nisCol = headerCols.findIndex((c) => c.includes("NIS") && !c.includes("NISN"));
        const nisnCol = headerCols.findIndex((c) => c.includes("NISN"));
        const nameCol = headerCols.findIndex((c) => (c.includes("NAMA") || c.includes("SISWA")) && !c.includes("ORANG TUA") && !c.includes("WALI") && !c.includes("ORTU"));
        const genderCol = headerCols.findIndex((c) => c.includes("KELAMIN") || c === "JK" || c.includes("GENDER"));
        const rombelCol = headerCols.findIndex((c) => c.includes("ROMBEL") || c.includes("ROMBONGAN") || c.includes("KELAS"));
        const gradeCol = headerCols.findIndex((c) => (c.includes("TINGKAT") || (c.includes("KELAS") && !c.includes("ROMBEL"))) && !c.includes("ROMBEL / KELAS"));
        const statusCol = headerCols.findIndex((c) => c.includes("STATUS"));
        const ekskulWajibCol = headerCols.findIndex((c) => c.includes("EKSKUL WAJIB") || c.includes("EKSTRAKURIKULER WAJIB") || c.includes("WAJIB"));
        const ekskul1Col = headerCols.findIndex((c) => (c.includes("PILIHAN 1") || c.includes("PILIHAN I") || (c.includes("PILIHAN") && c.includes("1"))));
        const ekskul2Col = headerCols.findIndex((c) => (c.includes("PILIHAN 2") || c.includes("PILIHAN II") || (c.includes("PILIHAN") && c.includes("2"))));
        const prolifeCol = headerCols.findIndex((c) => c.includes("PROLIFE") || c.includes("PROFIL"));
        const univCol = headerCols.findIndex((c) => c.includes("UNIVERSITAS") || c.includes("KAMPUS") || c.includes("PERGURUAN"));
        const jurusanCol = headerCols.findIndex((c) => c.includes("JURUSAN") || c.includes("PRODI"));
        const birthPlaceCol = headerCols.findIndex((c) => c.includes("TEMPAT"));
        const birthDateCol = headerCols.findIndex((c) => c.includes("TANGGAL LAHIR") || c.includes("TGL"));
        const parentNameCol = headerCols.findIndex((c) => c.includes("ORANG TUA") || c.includes("WALI") || c.includes("ORTU"));
        const parentPhoneCol = headerCols.findIndex(
          (c) =>
            (c.includes("TELEPON") ||
              c.includes("WHATSAPP") ||
              c.includes("HP") ||
              c.includes("NO. WA") ||
              c.includes("NO WA") ||
              c.includes("NOMOR WA") ||
              c.includes("KONTAK") ||
              c.includes("NO TELP") ||
              /\bWA\b/.test(c)) &&
            !c.includes("NAMA") &&
            !c.includes("SISWA")
        );
        const addressCol = headerCols.findIndex((c) => c.includes("ALAMAT"));

        const startIndex = hasHeaderRow ? 1 : 0;

        for (let i = startIndex; i < rawRows.length; i++) {
          const row = rawRows[i];
          // Skip completely empty rows
          if (!row || row.every((c) => String(c).trim() === "")) continue;

          let nis = "";
          let nisn = "";
          let fullName = "";
          let genderRaw = "";
          let gradeRaw = "";
          let rombelRaw = "";
          let statusRaw = "Aktif";
          let ekskulWajib = "Pramuka";
          let ekskul1 = "";
          let ekskul2 = "";
          let prolife = "";
          let univ = "";
          let jurusan = "";
          let birthPlace = "";
          let birthDate = "";
          let parentName = "";
          let parentPhone = "";
          let address = "";

          if (hasHeaderRow) {
            nis = String(row[nisCol >= 0 ? nisCol : 0] ?? "").trim();
            nisn = String(row[nisnCol >= 0 ? nisnCol : 1] ?? "").trim();
            fullName = String(row[nameCol >= 0 ? nameCol : 2] ?? "").trim();
            genderRaw = String(row[genderCol >= 0 ? genderCol : 3] ?? "").trim().toUpperCase();
            rombelRaw = String(row[rombelCol >= 0 ? rombelCol : 4] ?? "").trim().toUpperCase();
            gradeRaw = gradeCol >= 0 && gradeCol !== rombelCol ? String(row[gradeCol] ?? "").trim().toUpperCase() : "";
            statusRaw = statusCol >= 0 ? String(row[statusCol] ?? "").trim() : "Aktif";
            ekskulWajib = ekskulWajibCol >= 0 ? String(row[ekskulWajibCol] ?? "").trim() : "Pramuka";
            ekskul1 = ekskul1Col >= 0 ? String(row[ekskul1Col] ?? "").trim() : "";
            ekskul2 = ekskul2Col >= 0 ? String(row[ekskul2Col] ?? "").trim() : "";
            prolife = prolifeCol >= 0 ? String(row[prolifeCol] ?? "").trim() : "";
            univ = univCol >= 0 ? String(row[univCol] ?? "").trim() : "";
            jurusan = jurusanCol >= 0 ? String(row[jurusanCol] ?? "").trim() : "";
            birthPlace = birthPlaceCol >= 0 ? String(row[birthPlaceCol] ?? "").trim() : "";
            birthDate = birthDateCol >= 0 ? String(row[birthDateCol] ?? "").trim() : "";
            parentName = parentNameCol >= 0 ? String(row[parentNameCol] ?? "").trim() : (row.length === 10 ? String(row[5] ?? "").trim() : "");
            parentPhone = parentPhoneCol >= 0 ? String(row[parentPhoneCol] ?? "").trim() : (row.length === 10 ? String(row[6] ?? "").trim() : "");
            address = addressCol >= 0 ? String(row[addressCol] ?? "").trim() : "";
          } else if (row.length >= 18) {
            nis = String(row[0] ?? "").trim();
            nisn = String(row[1] ?? "").trim();
            fullName = String(row[2] ?? "").trim();
            genderRaw = String(row[3] ?? "").trim().toUpperCase();
            gradeRaw = String(row[4] ?? "").trim().toUpperCase();
            rombelRaw = String(row[5] ?? "").trim().toUpperCase();
            statusRaw = String(row[6] ?? "").trim();
            ekskulWajib = String(row[7] ?? "").trim();
            ekskul1 = String(row[8] ?? "").trim();
            ekskul2 = String(row[9] ?? "").trim();
            prolife = String(row[10] ?? "").trim();
            univ = String(row[11] ?? "").trim();
            jurusan = String(row[12] ?? "").trim();
            birthPlace = String(row[13] ?? "").trim();
            birthDate = String(row[14] ?? "").trim();
            parentName = String(row[15] ?? "").trim();
            parentPhone = String(row[16] ?? "").trim();
            address = String(row[17] ?? "").trim();
          } else if (row.length === 10 || row.length === 11) {
            nis = String(row[0] ?? "").trim();
            nisn = String(row[1] ?? "").trim();
            fullName = String(row[2] ?? "").trim();
            genderRaw = String(row[3] ?? "").trim().toUpperCase();
            rombelRaw = String(row[4] ?? "").trim().toUpperCase();
            parentName = String(row[5] ?? "").trim();
            parentPhone = String(row[6] ?? "").trim();
            ekskulWajib = String(row[7] ?? "").trim() || "Pramuka";
            ekskul1 = String(row[8] ?? "").trim();
            ekskul2 = String(row[9] ?? "").trim();
          } else {
            // Legacy 12 columns format
            nis = String(row[0] ?? "").trim();
            nisn = String(row[1] ?? "").trim();
            fullName = String(row[2] ?? "").trim();
            genderRaw = String(row[3] ?? "").trim().toUpperCase();
            gradeRaw = String(row[4] ?? "").trim().toUpperCase();
            rombelRaw = String(row[5] ?? "").trim().toUpperCase();
            statusRaw = String(row[6] ?? "").trim();
            birthPlace = String(row[7] ?? "").trim();
            birthDate = String(row[8] ?? "").trim();
            parentName = String(row[9] ?? "").trim();
            parentPhone = String(row[10] ?? "").trim();
            address = String(row[11] ?? "").trim();
          }

          const notes: string[] = [];

          if (!nis) notes.push("NIS tidak boleh kosong");
          if (!nisn) {
            notes.push("NISN wajib diisi");
          } else if (nisn !== "-" && (nisn.length !== 10 || !/^\d+$/.test(nisn))) {
            notes.push("NISN harus tepat 10 digit angka");
          }
          if (!fullName) notes.push("Nama siswa wajib diisi");

          // Normalize Grade Level
          let gradeLevel: "X" | "XI" | "XII" = "X";
          const gradeCheck = `${gradeRaw} ${rombelRaw}`.toUpperCase();
          if (gradeCheck.includes("XII") || gradeCheck.startsWith("XII") || gradeCheck === "12") gradeLevel = "XII";
          else if (gradeCheck.includes("XI") || gradeCheck.startsWith("XI") || gradeCheck === "11") gradeLevel = "XI";
          else gradeLevel = "X";

          // Normalize & Validate Rombel
          let className = rombelRaw;
          if (!className) {
            className = `${gradeLevel}-1`;
          }
          // If rombel contains space or lowercase, format e.g. "x-1" -> "X-1"
          className = className.replace(/\s+/g, "").toUpperCase();
          if (!validRombels.has(className)) {
            notes.push(`Rombel "${className}" tidak valid (Gunakan format ${gradeLevel}-1 s/d ${gradeLevel}-10)`);
          }

          // Normalize Gender
          const gender: "L" | "P" = genderRaw.startsWith("P") ? "P" : "L";

          // Normalize Status
          let status: "aktif" | "mutasi" | "lulus" = "aktif";
          const stLower = (statusRaw || "aktif").toLowerCase();
          if (stLower.includes("mutasi")) status = "mutasi";
          else if (stLower.includes("lulus")) status = "lulus";
          else status = "aktif";

          parsedRows.push({
            id: `imp-${i}-${Date.now().toString().slice(-4)}`,
            nis,
            nisn,
            fullName,
            gender,
            gradeLevel,
            className,
            status,
            ekskul_wajib: ekskulWajib || "Pramuka",
            ekskul_pilihan_1: ekskul1 || "-",
            ekskul_pilihan_2: ekskul2 || "-",
            prolife: prolife || "-",
            rencana_karir_universitas: univ || "-",
            rencana_karir_jurusan: jurusan || "-",
            birthPlace: birthPlace || "-",
            birthDate: birthDate || "-",
            parentName: parentName || "-",
            parentPhone: parentPhone || "-",
            address: address || "-",
            isValid: notes.length === 0,
            validationNotes: notes,
          });
        }

        resolve(parsedRows);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}

export const TEACHER_TEMPLATE_HEADERS = [
  "NIP",
  "NUPTK",
  "Nama Lengkap & Gelar",
  "Tempat Lahir",
  "Tanggal Lahir (DD/MM/YYYY)",
  "Jabatan",
  "Pendidikan Terakhir",
  "Mata Pelajaran",
  "Kepegawaian",
  "Wali Kelas",
  "Alamat",
  "Sertifikasi",
  "Jenis Kelamin (L/P)",
  "No. Telepon / WhatsApp",
  "Email Resmi Institusi",
];

export function downloadTeacherTemplateXLSX() {
  const sampleData = [
    TEACHER_TEMPLATE_HEADERS,
    [
      "197205121998021002",
      "4538750652200032",
      "Drs. H. Mulyadi, M.Pd.",
      "Bandung",
      "12/05/1972",
      "Wakasek Kurikulum",
      "S2 Pendidikan Matematika - UPI",
      "Matematika",
      "PNS DPK",
      "X-1",
      "Jl. Burangrang No. 8, Lengkong, Bandung",
      "Tersertifikasi",
      "Laki-laki",
      "081221345670",
      "mulyadi@smabpi1bdr.sch.id",
    ],
    [
      "197508202000032001",
      "7849753655300013",
      "Dra. Hj. Siti Rohmah, M.Si.",
      "Bandung",
      "20/08/1975",
      "Guru Mapel",
      "S2 Biologi - ITB",
      "Biologi",
      "Guru Tetap Yayasan (GTY)",
      "X-2",
      "Jl. Lodaya No. 12, Lengkong, Bandung",
      "Tersertifikasi",
      "Perempuan",
      "081398765432",
      "siti.rohmah@smabpi1bdr.sch.id",
    ],
    [
      "-",
      "3412750652200011",
      "Siti Rahmawati, S.Pd.",
      "Cimahi",
      "15/09/1990",
      "Guru Mapel",
      "S1 Pendidikan Bahasa Inggris - UNPAD",
      "Bahasa Inggris",
      "Guru Tidak Tetap (GTT)",
      "Bukan Wali Kelas",
      "Jl. Buah Batu No. 45, Bandung",
      "Belum Tersertifikasi",
      "Perempuan",
      "081334567890",
      "siti.rahma@smabpi1bdr.sch.id",
    ],
  ];

  const worksheet = XLSX.utils.aoa_to_sheet(sampleData);

  // Set column widths in Excel
  worksheet["!cols"] = [
    { wch: 24 }, // NIP
    { wch: 22 }, // NUPTK
    { wch: 34 }, // Nama Lengkap
    { wch: 20 }, // Tempat Lahir
    { wch: 26 }, // Tanggal Lahir (DD/MM/YYYY)
    { wch: 24 }, // Jabatan
    { wch: 38 }, // Pendidikan Terakhir
    { wch: 26 }, // Mata Pelajaran
    { wch: 28 }, // Kepegawaian
    { wch: 20 }, // Wali Kelas
    { wch: 45 }, // Alamat
    { wch: 22 }, // Sertifikasi
    { wch: 22 }, // JK
    { wch: 24 }, // No Telp
    { wch: 32 }, // Email
  ];

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Template Guru BPI 1");

  // Generate binary XLSX download
  XLSX.writeFile(workbook, "Template_Import_Guru_SMA_BPI_1.xlsx", {
    bookType: "xlsx",
    type: "binary",
  });
}

export interface TeacherImportPreviewRow {
  id: string;
  fullName: string;
  nip?: string;
  nuptk?: string;
  birthPlace?: string; // TEMPAT LAHIR
  birthDate?: string;  // TANGGAL LAHIR
  gender: "L" | "P";
  position?: string; // JABATAN
  education: string;  // PENDIDIKAN TERAKHIR
  primarySubject: string;
  secondarySubjects: string[];
  employmentStatus: "Guru Tetap Yayasan (GTY)" | "PNS DPK" | "Guru Tidak Tetap (GTT)";
  certificationStatus: "Tersertifikasi" | "Dalam Proses" | "Belum Tersertifikasi";
  isHomeroomTeacher: boolean;
  homeroomClass?: string;
  address?: string;   // ALAMAT
  teachingHoursPerWeek: number;
  email: string;
  phone: string;
  status: "aktif" | "cuti" | "non_aktif";
  isValid: boolean;
  validationNotes: string[];
}

export function parseTeacherExcelFile(file: File): Promise<TeacherImportPreviewRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });

        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        const rawRows: Array<Array<string | number | undefined>> = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          defval: "",
        });

        if (rawRows.length <= 1) {
          resolve([]);
          return;
        }

        const validRombels = new Set(mockAvailableClasses);
        const parsedRows: TeacherImportPreviewRow[] = [];

        // Detect column positions by examining header row
        const headerRow = (rawRows[0] || []).map((c) => String(c || "").trim().toLowerCase());

        const findCol = (keywords: string[], fallbackIdx: number) => {
          const idx = headerRow.findIndex((h) => keywords.some((k) => h.includes(k)));
          return idx !== -1 ? idx : fallbackIdx;
        };

        const idxNip = findCol(["nip"], 0);
        const idxNuptk = findCol(["nuptk"], 1);
        const idxNama = findCol(["nama", "fullname"], 2);
        const idxTempat = findCol(["tempat lahir", "tempat", "kota lahir", "birthplace"], 3);
        const idxTglLahir = findCol(["tanggal lahir", "tgl lahir", "tgl_lahir", "birthdate", "lahir"], 4);
        const idxJabatan = findCol(["jabatan", "posisi", "position"], 5);
        const idxPendidikan = findCol(["pendidikan", "gelar", "education"], 6);
        const idxMapel = findCol(["pelajaran", "mapel", "subject"], 7);
        const idxKepegawaian = findCol(["kepegawaian", "pegawai", "employment"], 8);
        const idxWali = findCol(["wali", "binaan", "homeroom", "rombel"], 9);
        const idxAlamat = findCol(["alamat", "address", "domisili"], 10);
        const idxSertifikasi = findCol(["sertifikasi", "serdik", "certification"], 11);
        const idxGender = findCol(["kelamin", "gender", "l/p"], 12);
        const idxPhone = findCol(["telepon", "telp", "wa", "hp", "phone"], 13);
        const idxEmail = findCol(["email", "surel"], 14);
        const idxHours = findCol(["jam", "beban", "hours"], 15);

        // Skip header row at index 0
        for (let i = 1; i < rawRows.length; i++) {
          const row = rawRows[i];
          if (!row || row.every((c) => String(c).trim() === "")) continue;

          const fullName = String(row[idxNama] ?? "").trim();
          let nip = String(row[idxNip] ?? "").trim();
          if (nip === "-" || nip === "0") nip = "";

          let nuptk = String(row[idxNuptk] ?? "").trim();
          if (nuptk === "-" || nuptk === "0") nuptk = "";

          let birthPlaceRaw = idxTempat !== -1 ? String(row[idxTempat] ?? "").trim() : "";
          if (!birthPlaceRaw || birthPlaceRaw === "-") birthPlaceRaw = "Bandung";

          let birthDateRaw = "";
          if (idxTglLahir !== -1) {
            const rawVal = row[idxTglLahir];
            if (typeof rawVal === "number") {
              const d = new Date(Math.round((rawVal - 25569) * 86400 * 1000));
              if (!isNaN(d.getTime())) {
                birthDateRaw = d.toISOString().slice(0, 10);
              }
            } else if (rawVal) {
              const s = String(rawVal).trim();
              const dmy = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);
              if (dmy) {
                birthDateRaw = `${dmy[3]}-${dmy[2].padStart(2, "0")}-${dmy[1].padStart(2, "0")}`;
              } else {
                birthDateRaw = s;
              }
            }
          }
          if (!birthDateRaw || birthDateRaw === "-") birthDateRaw = "1980-01-01";

          const positionRaw = String(row[idxJabatan] ?? "").trim() || "Guru Mapel";
          const educationRaw = String(row[idxPendidikan] ?? "").trim() || "S1";
          const subjectRaw = String(row[idxMapel] ?? "").trim();
          const employmentRaw = String(row[idxKepegawaian] ?? "").trim();
          const homeroomRaw = String(row[idxWali] ?? "").trim();
          const addressRaw = String(row[idxAlamat] ?? "").trim() || "-";
          const certRaw = String(row[idxSertifikasi] ?? "").trim();
          const genderRaw = String(row[idxGender] ?? "").trim().toUpperCase();
          const phoneRaw = String(row[idxPhone] ?? "").trim();
          const emailRaw = String(row[idxEmail] ?? "").trim();
          const hoursRaw = Number(row[idxHours]) || 24;

          const notes: string[] = [];

          if (!fullName) notes.push("Nama guru wajib diisi");
          if (!subjectRaw) notes.push("Mata pelajaran utama wajib diisi");

          if (nip && (nip.length !== 18 || !/^\d+$/.test(nip))) {
            notes.push("Format NIP harus 18 digit angka");
          }

          if (nuptk && (nuptk.length !== 16 || !/^\d+$/.test(nuptk))) {
            notes.push("Format NUPTK harus 16 digit angka");
          }

          // Email & Phone are optional
          if (emailRaw && emailRaw !== "-" && !emailRaw.includes("@")) {
            notes.push("Format email resmi tidak valid (harus menyertakan @)");
          }

          // Normalize Gender
          const gender: "L" | "P" = genderRaw.startsWith("P") || genderRaw.includes("PEREMPUAN") ? "P" : "L";

          // Normalize Employment Status
          let employmentStatus: "Guru Tetap Yayasan (GTY)" | "PNS DPK" | "Guru Tidak Tetap (GTT)" = "Guru Tetap Yayasan (GTY)";
          if (employmentRaw.toUpperCase().includes("PNS")) {
            employmentStatus = "PNS DPK";
          } else if (
            employmentRaw.toUpperCase().includes("GTT") ||
            employmentRaw.toUpperCase().includes("TIDAK TETAP") ||
            employmentRaw.toUpperCase().includes("HONOR") ||
            employmentRaw.toUpperCase().includes("KONTRAK")
          ) {
            employmentStatus = "Guru Tidak Tetap (GTT)";
          }

          // Normalize Certification Status
          let certificationStatus: "Tersertifikasi" | "Dalam Proses" | "Belum Tersertifikasi" = "Belum Tersertifikasi";
          if (certRaw.toLowerCase().includes("ter") || certRaw.toLowerCase().includes("sudah") || certRaw.toLowerCase().includes("ya")) {
            certificationStatus = "Tersertifikasi";
          } else if (certRaw.toLowerCase().includes("proses")) {
            certificationStatus = "Dalam Proses";
          }

          // Normalize Homeroom
          let isHomeroomTeacher = false;
          let homeroomClass = "";

          // Check if homeroomRaw mentions a class (e.g. "X-1", "Wali Kelas X-2", "XII-5")
          const classMatch = homeroomRaw.match(/(X|XI|XII)\s*[-–]\s*([0-9]+)/i);
          if (classMatch) {
            const parsedClass = `${classMatch[1].toUpperCase()}-${classMatch[2]}`;
            if (validRombels.has(parsedClass)) {
              isHomeroomTeacher = true;
              homeroomClass = parsedClass;
            } else {
              isHomeroomTeacher = true;
              homeroomClass = parsedClass;
              notes.push(`Rombel "${parsedClass}" tidak dikenali di daftar rombel aktif`);
            }
          } else if (
            homeroomRaw.toLowerCase().includes("ya") ||
            homeroomRaw.toLowerCase().includes("wali") ||
            homeroomRaw.toLowerCase().includes("true")
          ) {
            isHomeroomTeacher = true;
            homeroomClass = "X-1"; // default class if simply marked "Ya"
          }

          parsedRows.push({
            id: `tch-imp-${i}-${Date.now().toString().slice(-4)}`,
            fullName,
            nip: nip || undefined,
            nuptk: nuptk || undefined,
            birthPlace: birthPlaceRaw,
            birthDate: birthDateRaw,
            gender,
            position: positionRaw,
            education: educationRaw,
            primarySubject: subjectRaw || "Matematika",
            secondarySubjects: [],
            employmentStatus,
            certificationStatus,
            isHomeroomTeacher,
            homeroomClass: isHomeroomTeacher ? homeroomClass : undefined,
            address: addressRaw,
            teachingHoursPerWeek: hoursRaw > 0 ? hoursRaw : 24,
            email: emailRaw && emailRaw !== "-" ? emailRaw : "-",
            phone: phoneRaw && phoneRaw !== "-" ? phoneRaw : "-",
            status: "aktif",
            isValid: notes.length === 0,
            validationNotes: notes,
          });
        }

        resolve(parsedRows);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}

export interface GradeImportRow {
  id: string;
  nis: string;
  nisn: string;
  studentName: string;
  forScore: number | null;
  sumScore: number | null;
  ptsScore?: number | null;
  finalScore: number | null;
  notes: string;
  isValid: boolean;
  validationNotes: string[];
}

export function downloadGradeTemplateXLSX(params: {
  className: string;
  subjectCode: string;
  subjectName: string;
  monthName: string;
  hasPTS?: boolean;
  students: Array<{ nis: string; nisn?: string; fullName: string }>;
}) {
  const { className, subjectCode, subjectName, monthName, hasPTS, students } = params;

  // Header metadata rows
  const header = [
    ["TEMPLATE INPUT NILAI MATA PELAJARAN - SMA BPI 1 BANDUNG"],
    [`Mata Pelajaran: ${subjectName} (${subjectCode})`],
    [`Kelas / Rombel: Kelas ${className}`],
    [`Periode Bulan: ${monthName}`],
    [`Tahun Ajaran: 2026/2027 • Semester Ganjil`],
    [], // Empty row
    [
      "NO",
      "NIS",
      "NISN",
      "NAMA LENGKAP SISWA",
      "NILAI FORMATIF (0-100)",
      "NILAI SUMATIF (0-100)",
      ...(hasPTS ? ["NILAI PTS (0-100)"] : []),
      "CATATAN / KETERANGAN MAPEL",
    ],
  ];

  // Data rows - Keep grade cells clean/empty for teacher entry
  const dataRows = students.map((s, idx) => [
    idx + 1,
    s.nis,
    s.nisn || "-",
    s.fullName,
    "", // Formatif kosong siap diisi
    "", // Sumatif kosong siap diisi
    ...(hasPTS ? [""] : []), // PTS kosong jika ada
    "", // Catatan kosong
  ]);

  const allRows = [...header, ...dataRows];
  const worksheet = XLSX.utils.aoa_to_sheet(allRows);

  // Set column widths
  worksheet["!cols"] = [
    { wch: 6 },
    { wch: 15 },
    { wch: 18 },
    { wch: 32 },
    { wch: 24 },
    { wch: 24 },
    ...(hasPTS ? [{ wch: 20 }] : []),
    { wch: 45 },
  ];

  const workbook = XLSX.utils.book_new();
  const safeSheetName = `${className.replace(/[^a-zA-Z0-9]/g, "")}_${subjectCode.replace(/[^a-zA-Z0-9]/g, "")}`.slice(0, 31);
  XLSX.utils.book_append_sheet(workbook, worksheet, safeSheetName || "Nilai");

  const fileName = `Template_Nilai_${subjectName.replace(/\s+/g, "_")}_Kelas_${className}_${monthName.replace(/\s+/g, "_")}.xlsx`;
  XLSX.writeFile(workbook, fileName, { bookType: "xlsx" });
}

export function parseGradeExcelFile(file: File, hasPTS: boolean = false): Promise<GradeImportRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        const rawRows: Array<Array<string | number | undefined>> = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          defval: "",
        });

        if (rawRows.length === 0) {
          resolve([]);
          return;
        }

        // Find table header row (look for row containing "NIS" or "NAMA")
        let headerRowIdx = -1;
        for (let i = 0; i < Math.min(15, rawRows.length); i++) {
          const rowStr = (rawRows[i] || []).map((c) => String(c).toUpperCase()).join(" ");
          if (rowStr.includes("NIS") && (rowStr.includes("NAMA") || rowStr.includes("FORMATIF") || rowStr.includes("SUMATIF"))) {
            headerRowIdx = i;
            break;
          }
        }

        if (headerRowIdx === -1) {
          headerRowIdx = 0;
        }

        const headerCols = (rawRows[headerRowIdx] || []).map((c) => String(c).toUpperCase().trim());
        const nisCol = headerCols.findIndex((c) => c.includes("NIS") && !c.includes("NISN"));
        const nisnCol = headerCols.findIndex((c) => c.includes("NISN"));
        const nameCol = headerCols.findIndex((c) => c.includes("NAMA"));
        const forCol = headerCols.findIndex((c) => c.includes("FOR") || c.includes("FORMATIF"));
        const sumCol = headerCols.findIndex((c) => c.includes("SUM") || c.includes("SUMATIF"));
        const ptsCol = headerCols.findIndex((c) => c.includes("PTS") || c.includes("UTS") || c.includes("TENGAH"));
        const noteCol = headerCols.findIndex((c) => c.includes("CATATAN") || c.includes("KETERANGAN") || c.includes("REMARK"));

        const parsed: GradeImportRow[] = [];

        for (let i = headerRowIdx + 1; i < rawRows.length; i++) {
          const row = rawRows[i];
          if (!row || row.every((c) => String(c).trim() === "")) continue;

          const nis = String(row[nisCol >= 0 ? nisCol : 1] ?? "").trim();
          const nisn = String(row[nisnCol >= 0 ? nisnCol : 2] ?? "").trim();
          const studentName = String(row[nameCol >= 0 ? nameCol : 3] ?? "").trim();

          const forRaw = row[forCol >= 0 ? forCol : 4];
          const sumRaw = row[sumCol >= 0 ? sumCol : 5];
          const ptsRaw = hasPTS || ptsCol >= 0 ? row[ptsCol >= 0 ? ptsCol : 6] : undefined;
          const notes = String(row[noteCol >= 0 ? noteCol : (hasPTS ? 7 : 6)] ?? "").trim();

          if (!nis && !studentName) continue;

          const forVal = forRaw !== "" && forRaw !== undefined && !isNaN(Number(forRaw)) ? Number(forRaw) : null;
          const sumVal = sumRaw !== "" && sumRaw !== undefined && !isNaN(Number(sumRaw)) ? Number(sumRaw) : null;
          const ptsVal = ptsRaw !== "" && ptsRaw !== undefined && !isNaN(Number(ptsRaw)) ? Number(ptsRaw) : null;

          const validationNotes: string[] = [];
          if (!nis) validationNotes.push("NIS siswa tidak boleh kosong");
          if (forVal !== null && (forVal < 0 || forVal > 100)) validationNotes.push("Nilai Formatif harus antara 0 - 100");
          if (sumVal !== null && (sumVal < 0 || sumVal > 100)) validationNotes.push("Nilai Sumatif harus antara 0 - 100");
          if (ptsVal !== null && (ptsVal < 0 || ptsVal > 100)) {
            validationNotes.push("Nilai PTS harus antara 0 - 100");
          }

          const validNums = [forVal, sumVal, ptsVal].filter((v): v is number => v !== null);
          const finalScore = validNums.length > 0
            ? Math.round(validNums.reduce((a, b) => a + b, 0) / validNums.length)
            : null;

          parsed.push({
            id: `imp-${i}-${Date.now()}`,
            nis,
            nisn,
            studentName: studentName || `Siswa NIS ${nis}`,
            forScore: forVal !== null ? Math.min(100, Math.max(0, forVal)) : null,
            sumScore: sumVal !== null ? Math.min(100, Math.max(0, sumVal)) : null,
            ptsScore: ptsVal !== null ? Math.min(100, Math.max(0, ptsVal)) : null,
            finalScore,
            notes: notes || "",
            isValid: validationNotes.length === 0,
            validationNotes,
          });
        }

        resolve(parsed);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}

// =========================================================================
// FITUR PLOTTING KENAIKAN KELAS BATCH (FASE E KE F / X KE XI)
// =========================================================================

export interface PromotionPlottingRow {
  id: string;
  nis: string;
  nisn?: string;
  nama_lengkap: string;
  previousClass: string;
  action: "naik" | "tinggal" | "lulus" | "mutasi";
  targetClass: string;
  notes: string;
  isValid: boolean;
  validationNotes: string[];
}

export function downloadPromotionPlottingTemplateXLSX(
  originClass: string,
  targetGrade: "X" | "XI" | "XII",
  students: { nis: string; nisn?: string; nama_lengkap: string; rombel: string }[]
) {
  const isXII = originClass.startsWith("XII-");
  const defaultAction = isXII ? "Lulus" : "Naik Kelas";
  const defaultTargetClass = isXII
    ? "Alumni 2027"
    : originClass.startsWith("X-")
    ? `XI-${originClass.split("-")[1] || "1"}`
    : `XII-${originClass.split("-")[1] || "1"}`;

  const headers = [
    "NO",
    "NIS",
    "NISN",
    "NAMA LENGKAP SISWA",
    "KELAS ASAL",
    "KEPUTUSAN (Naik Kelas / Tinggal Kelas / Lulus / Mutasi)",
    "TARGET KELAS TUJUAN (Contoh: XI-1 s.d XI-10)",
    "CATATAN / KETERANGAN",
  ];

  const rows = students.map((st, idx) => [
    idx + 1,
    st.nis,
    st.nisn || "-",
    st.nama_lengkap,
    st.rombel,
    defaultAction,
    defaultTargetClass,
    "",
  ]);

  // If no students in origin class, provide sample rows
  const finalData =
    rows.length > 0
      ? [headers, ...rows]
      : [
          headers,
          [
            1,
            "242510001",
            "0081234501",
            "Achmad Maulana",
            originClass,
            defaultAction,
            defaultTargetClass,
            "Peringkat 1",
          ],
          [
            2,
            "242510002",
            "0081234502",
            "Berlian Putri",
            originClass,
            defaultAction,
            isXII ? "Alumni 2027" : originClass.startsWith("X-") ? "XI-2" : "XII-2",
            "",
          ],
        ];

  const worksheet = XLSX.utils.aoa_to_sheet(finalData);

  worksheet["!cols"] = [
    { wch: 6 },  // NO
    { wch: 15 }, // NIS
    { wch: 16 }, // NISN
    { wch: 30 }, // NAMA LENGKAP
    { wch: 14 }, // KELAS ASAL
    { wch: 30 }, // KEPUTUSAN
    { wch: 28 }, // TARGET KELAS TUJUAN
    { wch: 32 }, // CATATAN
  ];

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(
    workbook,
    worksheet,
    `Plotting ${originClass}`
  );

  XLSX.writeFile(
    workbook,
    `Template_Plotting_Kenaikan_Kelas_${originClass.replace("-", "_")}.xlsx`,
    {
      bookType: "xlsx",
      type: "binary",
    }
  );
}

export function parsePromotionPlottingExcel(
  file: File
): Promise<PromotionPlottingRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        const rawJson = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          blankrows: false,
        }) as (string | number)[][];

        if (rawJson.length <= 1) {
          resolve([]);
          return;
        }

        // Find header indices
        let headerRowIndex = 0;
        let nisCol = -1;
        let nameCol = -1;
        let nisnCol = -1;
        let originCol = -1;
        let actionCol = -1;
        let targetCol = -1;
        let notesCol = -1;

        for (let i = 0; i < Math.min(5, rawJson.length); i++) {
          const row = rawJson[i];
          if (!row || !Array.isArray(row)) continue;

          row.forEach((cell, idx) => {
            const val = String(cell || "").trim().toLowerCase();
            if (val === "nis" || val.includes("nomor induk siswa")) nisCol = idx;
            if (val.includes("nisn")) nisnCol = idx;
            if (val.includes("nama")) nameCol = idx;
            if (val.includes("asal") || val.includes("kelas asal")) originCol = idx;
            if (val.includes("keputusan") || val.includes("status akhir")) actionCol = idx;
            if (val.includes("target") || val.includes("tujuan")) targetCol = idx;
            if (val.includes("catatan") || val.includes("keterangan")) notesCol = idx;
          });

          if (nisCol >= 0 || nameCol >= 0) {
            headerRowIndex = i;
            break;
          }
        }

        // Fallbacks
        if (nisCol === -1) nisCol = 1;
        if (nisnCol === -1) nisnCol = 2;
        if (nameCol === -1) nameCol = 3;
        if (originCol === -1) originCol = 4;
        if (actionCol === -1) actionCol = 5;
        if (targetCol === -1) targetCol = 6;
        if (notesCol === -1) notesCol = 7;

        const parsed: PromotionPlottingRow[] = [];

        for (let i = headerRowIndex + 1; i < rawJson.length; i++) {
          const row = rawJson[i];
          if (!row || row.length === 0) continue;

          const nis = String(row[nisCol] ?? "").trim();
          const nisn = nisnCol >= 0 ? String(row[nisnCol] ?? "").trim() : "";
          const fullName = nameCol >= 0 ? String(row[nameCol] ?? "").trim() : "";
          const prevClass = originCol >= 0 ? String(row[originCol] ?? "").trim() : "";
          const rawAction = actionCol >= 0 ? String(row[actionCol] ?? "").trim().toLowerCase() : "naik";
          const rawTarget = targetCol >= 0 ? String(row[targetCol] ?? "").trim() : "";
          const notes = notesCol >= 0 ? String(row[notesCol] ?? "").trim() : "";

          if (!nis && !fullName) continue;

          // Map Action
          let action: "naik" | "tinggal" | "lulus" | "mutasi" = "naik";
          if (rawAction.includes("tinggal") || rawAction.includes("ulang")) {
            action = "tinggal";
          } else if (rawAction.includes("lulus") || rawAction.includes("alumni")) {
            action = "lulus";
          } else if (rawAction.includes("mutasi") || rawAction.includes("pindah") || rawAction.includes("keluar")) {
            action = "mutasi";
          } else {
            action = "naik";
          }

          // Target class cleanup
          let targetClass = rawTarget;
          if (!targetClass) {
            if (action === "lulus") targetClass = "Alumni 2027";
            else if (action === "tinggal") targetClass = prevClass;
            else if (action === "mutasi") targetClass = "Mutasi Keluar";
            else targetClass = prevClass.startsWith("X-") ? `XI-${prevClass.split("-")[1] || "1"}` : `XII-${prevClass.split("-")[1] || "1"}`;
          }

          const validationNotes: string[] = [];
          if (!nis) validationNotes.push("NIS siswa tidak boleh kosong");

          parsed.push({
            id: `promo-imp-${i}-${Date.now()}`,
            nis,
            nisn,
            nama_lengkap: fullName || `Siswa NIS ${nis}`,
            previousClass: prevClass,
            action,
            targetClass,
            notes,
            isValid: validationNotes.length === 0,
            validationNotes,
          });
        }

        resolve(parsed);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}

// ============================================================================
// NILAI AKHIR KELAS XII (FINAL GRADES & SKL INTEGRATION)
// ============================================================================

export interface FinalGradesImportRow {
  id: string;
  nis: string;
  nisn: string;
  fullName: string;
  className: string;
  peminatanPaket: string;
  status: "LULUS" | "TIDAK LULUS";
  subjectScores: { code: string; name: string; score: number }[];
  overallAverage: number;
  isValid: boolean;
  errors: string[];
}

export const FINAL_GRADES_HEADERS = [
  "NO",
  "NIS",
  "NISN",
  "NAMA SISWA",
  "KELAS",
  "PEMINATAN / PAKET",
  "PENDIDIKAN AGAMA DAN BUDI PEKERTI",
  "PENDIDIKAN PANCASILA",
  "BAHASA INDONESIA",
  "MATEMATIKA",
  "INFORMATIKA",
  "FISIKA",
  "KIMIA",
  "BIOLOGI",
  "EKONOMI",
  "BAHASA INGGRIS",
  "SEJARAH",
  "PENDIDIKAN JASMANI OLAHRAGA DAN KESEHATAN",
  "SENI",
  "BAHASA SUNDA",
  "MAPEL PILIHAN 1",
  "MAPEL PILIHAN 2",
  "RATA-RATA NILAI",
  "STATUS KELULUSAN",
];

export function downloadFinalGradesTemplateXLSX(
  className?: string,
  records?: SKLRecord[]
) {
  const data: (string | number)[][] = [FINAL_GRADES_HEADERS];

  // If filtered records provided, populate with those records
  if (records && records.length > 0) {
    records.forEach((rec, idx) => {
      // Find scores by code or order
      const getScore = (code: string) => {
        const item = rec.subjectScores.find((s) => s.code === code);
        return item != null ? item.score : "";
      };

      const pil1 = rec.subjectScores.find((s) => s.code === "SKL-15")?.score ?? "";
      const pil2 = rec.subjectScores.find((s) => s.code === "SKL-16")?.score ?? "";

      data.push([
        idx + 1,
        rec.nis,
        rec.nisn,
        rec.fullName,
        rec.className,
        rec.peminatanPaket,
        getScore("SKL-01"),
        getScore("SKL-02"),
        getScore("SKL-03"),
        getScore("SKL-04"),
        getScore("SKL-05"),
        getScore("SKL-06"),
        getScore("SKL-07"),
        getScore("SKL-08"),
        getScore("SKL-09"),
        getScore("SKL-10"),
        getScore("SKL-11"),
        getScore("SKL-12"),
        getScore("SKL-13"),
        getScore("SKL-14"),
        pil1,
        pil2,
        rec.overallAverage,
        rec.status,
      ]);
    });
  }

  const worksheet = XLSX.utils.aoa_to_sheet(data);

  worksheet["!cols"] = [
    { wch: 6 }, // NO
    { wch: 14 }, // NIS
    { wch: 16 }, // NISN
    { wch: 30 }, // NAMA SISWA
    { wch: 10 }, // KELAS
    { wch: 28 }, // PEMINATAN
    { wch: 18 }, // PAI
    { wch: 18 }, // Pancasila
    { wch: 18 }, // B Indo
    { wch: 15 }, // MTK
    { wch: 15 }, // Info
    { wch: 15 }, // Fisika
    { wch: 15 }, // Kimia
    { wch: 15 }, // Biologi
    { wch: 15 }, // Ekonomi
    { wch: 16 }, // B Ing
    { wch: 15 }, // Sejarah
    { wch: 18 }, // PJOK
    { wch: 15 }, // Seni
    { wch: 16 }, // Sunda
    { wch: 16 }, // Pilihan 1
    { wch: 16 }, // Pilihan 2
    { wch: 16 }, // Rata-rata
    { wch: 18 }, // Status
  ];

  const workbook = XLSX.utils.book_new();
  const sheetName = className && className !== "Semua" ? `Nilai Akhir ${className}` : "Nilai Akhir Kelas XII";
  XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);

  const fileName =
    className && className !== "Semua"
      ? `Template_Nilai_Akhir_Kelas_${className}.xlsx`
      : "Template_Nilai_Akhir_Kelas_XII_SMA_BPI_1.xlsx";

  XLSX.writeFile(workbook, fileName, {
    bookType: "xlsx",
    type: "binary",
  });
}

export function parseFinalGradesExcelFile(file: File): Promise<FinalGradesImportRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });

        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        if (!worksheet) {
          throw new Error("File Excel tidak memiliki lembar kerja (worksheet).");
        }

        const rawJson: any[][] = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          defval: "",
        });

        if (rawJson.length < 2) {
          throw new Error("File Excel kosong atau tidak memiliki baris data.");
        }

        // Find header row (search first 5 rows for 'nis' or 'nama')
        let headerRowIndex = 0;
        for (let r = 0; r < Math.min(5, rawJson.length); r++) {
          const rowStr = rawJson[r].join(" ").toLowerCase();
          if (rowStr.includes("nis") || rowStr.includes("nama") || rowStr.includes("mapel")) {
            headerRowIndex = r;
            break;
          }
        }

        const headerRow = rawJson[headerRowIndex].map((h: any) =>
          String(h).trim().toLowerCase()
        );

        // Find column indices
        const findCol = (predicate: (h: string) => boolean) =>
          headerRow.findIndex(predicate);

        const nisCol = findCol((h) => h === "nis" || (h.includes("nis") && !h.includes("nisn")));
        const nisnCol = findCol((h) => h.includes("nisn"));
        const nameCol = findCol((h) => h.includes("nama"));
        const classCol = findCol((h) => h.includes("kelas") || h.includes("rombel"));
        const peminatanCol = findCol((h) => h.includes("peminatan") || h.includes("paket"));
        const statusCol = findCol((h) => h.includes("status") || h.includes("kelulusan"));

        // Subject column mappings
        const paiCol = findCol((h) => h.includes("agama") || h.includes("pai") || h.includes("budi"));
        const pancasilaCol = findCol((h) => h.includes("pancasila") || h.includes("ppkn"));
        const indoCol = findCol((h) => h.includes("indonesia"));
        const mtkCol = findCol((h) => h.includes("matematika") || h.includes("mtk"));
        const infoCol = findCol((h) => h.includes("informatika") || h.includes("tik"));
        const fisikaCol = findCol((h) => h.includes("fisika"));
        const kimiaCol = findCol((h) => h.includes("kimia"));
        const biologiCol = findCol((h) => h.includes("biologi"));
        const ekonomiCol = findCol((h) => h.includes("ekonomi"));
        const inggrisCol = findCol((h) => h.includes("inggris"));
        const sejarahCol = findCol((h) => h.includes("sejarah"));
        const pjokCol = findCol((h) => h.includes("jasmani") || h.includes("pjok") || h.includes("penjas") || h.includes("olahraga"));
        const seniCol = findCol((h) => h.includes("seni") || h.includes("budaya"));
        const sundaCol = findCol((h) => h.includes("sunda"));
        const pil1Col = findCol((h) => h.includes("pilihan 1") || h.includes("pil 1"));
        const pil2Col = findCol((h) => h.includes("pilihan 2") || h.includes("pil 2"));

        const parsed: FinalGradesImportRow[] = [];

        for (let i = headerRowIndex + 1; i < rawJson.length; i++) {
          const row = rawJson[i];
          if (!row || row.length === 0) continue;

          const nis = nisCol >= 0 ? String(row[nisCol] ?? "").trim() : "";
          const nisn = nisnCol >= 0 ? String(row[nisnCol] ?? "").trim() : "";
          const fullName = nameCol >= 0 ? String(row[nameCol] ?? "").trim() : "";
          const className = classCol >= 0 ? String(row[classCol] ?? "").trim() : "XII-1";
          const peminatan = peminatanCol >= 0 ? String(row[peminatanCol] ?? "").trim() : `${className} / Reguler`;
          const rawStatus = statusCol >= 0 ? String(row[statusCol] ?? "").trim().toUpperCase() : "";

          // Skip completely empty rows
          if (!nis && !fullName) continue;

          const errors: string[] = [];
          if (!nis) errors.push("NIS siswa tidak boleh kosong");
          if (!fullName) errors.push("Nama siswa tidak boleh kosong");

          const parseScore = (colIdx: number, subjectName: string) => {
            if (colIdx < 0) return 0;
            const rawVal = row[colIdx];
            if (rawVal === undefined || rawVal === null || String(rawVal).trim() === "") {
              return 0;
            }
            const cleanStr = String(rawVal).replace(",", ".");
            const num = parseFloat(cleanStr);
            if (isNaN(num)) {
              errors.push(`Nilai ${subjectName} harus berupa angka (ditemukan: "${rawVal}")`);
              return 0;
            }
            if (num < 0 || num > 100) {
              errors.push(`Nilai ${subjectName} (${num}) harus berada dalam rentang 0 s.d. 100`);
            }
            return Number(num.toFixed(2));
          };

          const scores = [
            { code: "SKL-01", name: "Pendidikan Agama dan Budi Pekerti", score: parseScore(paiCol, "PAI") },
            { code: "SKL-02", name: "Pendidikan Pancasila", score: parseScore(pancasilaCol, "Pancasila") },
            { code: "SKL-03", name: "Bahasa Indonesia", score: parseScore(indoCol, "Bahasa Indonesia") },
            { code: "SKL-04", name: "Matematika", score: parseScore(mtkCol, "Matematika") },
            { code: "SKL-05", name: "Informatika", score: parseScore(infoCol, "Informatika") },
            { code: "SKL-06", name: "Fisika", score: parseScore(fisikaCol, "Fisika") },
            { code: "SKL-07", name: "Kimia", score: parseScore(kimiaCol, "Kimia") },
            { code: "SKL-08", name: "Biologi", score: parseScore(biologiCol, "Biologi") },
            { code: "SKL-09", name: "Ekonomi", score: parseScore(ekonomiCol, "Ekonomi") },
            { code: "SKL-10", name: "Bahasa Inggris", score: parseScore(inggrisCol, "Bahasa Inggris") },
            { code: "SKL-11", name: "Sejarah", score: parseScore(sejarahCol, "Sejarah") },
            { code: "SKL-12", name: "Pendidikan Jasmani Olahraga dan Kesehatan", score: parseScore(pjokCol, "PJOK") },
            { code: "SKL-13", name: "Seni", score: parseScore(seniCol, "Seni") },
            { code: "SKL-14", name: "Bahasa Sunda", score: parseScore(sundaCol, "Bahasa Sunda") },
          ];

          if (pil1Col >= 0) {
            const p1 = parseScore(pil1Col, "Mapel Pilihan 1");
            if (p1 > 0) scores.push({ code: "SKL-15", name: "Mapel Pilihan 1", score: p1 });
          }
          if (pil2Col >= 0) {
            const p2 = parseScore(pil2Col, "Mapel Pilihan 2");
            if (p2 > 0) scores.push({ code: "SKL-16", name: "Mapel Pilihan 2", score: p2 });
          }

          const avg = calculateSKLAverage(scores);

          let status: "LULUS" | "TIDAK LULUS" = "LULUS";
          if (rawStatus.includes("TIDAK")) {
            status = "TIDAK LULUS";
          } else if (rawStatus.includes("LULUS")) {
            status = "LULUS";
          } else {
            status = avg >= 70 ? "LULUS" : "TIDAK LULUS";
          }

          parsed.push({
            id: `final-grade-${i}-${Date.now()}`,
            nis,
            nisn,
            fullName: fullName.toUpperCase(),
            className,
            peminatanPaket: peminatan,
            status,
            subjectScores: scores,
            overallAverage: avg,
            isValid: errors.length === 0,
            errors,
          });
        }

        resolve(parsed);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}

/**
 * Download Template Excel Native (.xlsx) untuk Import Anggota Ekskul & Peserta Prolife
 */
export function downloadMemberTemplateXLSX(type: "ekskul" | "prolife", targetName?: string) {
  const isEkskul = type === "ekskul";
  const sampleData = [
    ["NO", "NIS", "NAMA SISWA", "KELAS"],
    [1, "2627.10001", "ADELINE AURELIA SOLIHIN", "X-1"],
    [2, "2627.10002", "AGHNAT MUTSLA AN'UMILLAH", "X-1"],
    [3, "2526.10015", "DIMAS ARYA PRATAMA", "XI-2"],
  ];

  const worksheet = XLSX.utils.aoa_to_sheet(sampleData);

  // Set explicit column widths
  worksheet["!cols"] = [
    { wch: 8 },  // NO
    { wch: 18 }, // NIS
    { wch: 34 }, // NAMA SISWA
    { wch: 14 }, // KELAS
  ];

  const workbook = XLSX.utils.book_new();
  const sheetName = isEkskul ? "Template Anggota Ekskul" : "Template Peserta Prolife";
  XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);

  const cleanName = (targetName || (isEkskul ? "Ekskul" : "Prolife"))
    .trim()
    .replace(/[^a-zA-Z0-9_\-\s]/g, "")
    .replace(/\s+/g, "_");

  const fileName = isEkskul
    ? `Template_Import_Anggota_Ekskul_${cleanName}.xlsx`
    : `Template_Import_Peserta_Prolife_${cleanName}.xlsx`;

  XLSX.writeFile(workbook, fileName, {
    bookType: "xlsx",
    type: "binary",
  });
}

export interface ParsedMemberImportRow {
  rowNum: number;
  nis: string;
  name: string;
  className: string;
}

/**
 * Parser file Excel native (.xlsx) untuk Import Anggota
 */
export function parseMemberImportExcelFile(file: File): Promise<ParsedMemberImportRow[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      try {
        const data = new Uint8Array(e.target?.result as ArrayBuffer);
        const workbook = XLSX.read(data, { type: "array" });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];

        const rawRows: Array<Array<string | number | undefined>> = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          defval: "",
        });

        if (rawRows.length < 2) {
          resolve([]);
          return;
        }

        // Dynamically locate column indices from header row
        let headerRowIndex = 0;
        let colIndexNIS = 1;
        let colIndexName = 2;
        let colIndexClass = 3;

        for (let r = 0; r < Math.min(6, rawRows.length); r++) {
          const row = rawRows[r].map((cell) => String(cell || "").trim().toUpperCase());
          const nisIdx = row.findIndex((c) => c.includes("NIS"));
          const nameIdx = row.findIndex((c) => c.includes("NAMA"));
          const classIdx = row.findIndex((c) => c.includes("KELAS") || c.includes("ROMBEL"));
          if (nisIdx !== -1) {
            headerRowIndex = r;
            colIndexNIS = nisIdx;
            if (nameIdx !== -1) colIndexName = nameIdx;
            if (classIdx !== -1) colIndexClass = classIdx;
            break;
          }
        }

        const parsedList: ParsedMemberImportRow[] = [];

        for (let i = headerRowIndex + 1; i < rawRows.length; i++) {
          const row = rawRows[i];
          if (!row || row.length === 0) continue;

          const rawNIS = String(row[colIndexNIS] ?? "").trim();
          const rawName = String(row[colIndexName] ?? "").trim();
          const rawClass = String(row[colIndexClass] ?? "").trim();

          // Hanya proses baris yang memiliki NIS bernilai valid
          if (!rawNIS) continue;
          if (
            rawNIS.toUpperCase() === "NIS" ||
            rawNIS.toUpperCase() === "NO" ||
            rawNIS === "-" ||
            rawNIS.toUpperCase().includes("CONTOH")
          ) {
            continue;
          }

          parsedList.push({
            rowNum: i + 1,
            nis: rawNIS,
            name: rawName,
            className: rawClass,
          });
        }

        resolve(parsedList);
      } catch (err) {
        reject(err);
      }
    };

    reader.onerror = (err) => reject(err);
    reader.readAsArrayBuffer(file);
  });
}