// Rangkuman Table Component - Gender-Split Pivot Table Format
const RangkumanTable = () => {
    const [rekapData, setRekapData] = useState([]);
    const [pivotData, setPivotData] = useState([]);
    const [schoolPivotData, setSchoolPivotData] = useState({});
    const [emptyKelasCount, setEmptyKelasCount] = useState(0);
    const [invalidData, setInvalidData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [expandedKecamatan, setExpandedKecamatan] = useState(new Set());
    const [filters, setFilters] = useState({
        jenisKelamin: 'Semua',
        status: 'Semua'
    });

    useEffect(() => {
        fetchRekapData();
    }, []);

    const fetchRekapData = () => {
        setLoading(true);
        Papa.parse(CSV_URL_REKAP, {
            download: true,
            header: false,
            complete: (res) => {
                const data = res.data.slice(1).map((row, idx) => {
                    if (!row[0]) return null;
                    return {
                        id: idx,
                        kecamatan: row[1],
                        npsn: row[2],
                        sekolah: row[3], // Nama sekolah
                        jenjang: row[4],
                        nama: row[6],
                        nik: row[7],
                        jenisKelamin: row[8],
                        kelas: row[9],
                        status: row[11]
                    };
                }).filter(Boolean);

                console.log('Rekap data loaded for pivot:', data.length, 'records');
                setRekapData(data);
                processPivotData(data, filters);
                setLoading(false);
            },
            error: (err) => {
                console.error('Error loading rekap data:', err);
                setLoading(false);
            }
        });
    };

    useEffect(() => {
        if (rekapData.length > 0) {
            processPivotData(rekapData, filters);
        }
    }, [filters, rekapData]);

    const toggleKecamatan = (kecamatan) => {
        setExpandedKecamatan(prev => {
            const next = new Set(prev);
            if (next.has(kecamatan)) next.delete(kecamatan);
            else next.add(kecamatan);
            return next;
        });
    };

    const expandAll = () => {
        setExpandedKecamatan(new Set(pivotData.map(r => r.kecamatan)));
    };

    const collapseAll = () => {
        setExpandedKecamatan(new Set());
    };

    const processPivotData = (data, currentFilters) => {
        // Filter data based on selected filters
        let filteredData = [...data];

        if (currentFilters.jenisKelamin !== 'Semua') {
            filteredData = filteredData.filter(d => d.jenisKelamin === currentFilters.jenisKelamin);
        }

        if (currentFilters.status !== 'Semua') {
            filteredData = filteredData.filter(d => d.status === currentFilters.status);
        }

        // Track invalid data and valid data processed
        const invalidRecords = [];
        let validCount = 0;

        // Create pivot structure
        const pivot = {};
        const schoolPivot = {}; // kecamatan -> sekolah -> same structure

        const makeEmptyRow = (kecamatan, sekolah) => ({
            kecamatan,
            sekolah: sekolah || '',
            putus: {
                sd: { '1_l': 0, '1_p': 0, '2_l': 0, '2_p': 0, '3_l': 0, '3_p': 0, '4_l': 0, '4_p': 0, '5_l': 0, '5_p': 0, '6_l': 0, '6_p': 0 },
                smp: { '7_l': 0, '7_p': 0, '8_l': 0, '8_p': 0, '9_l': 0, '9_p': 0 }
            },
            ltm: {
                sd: { 'l': 0, 'p': 0 },
                smp: { 'l': 0, 'p': 0 }
            }
        });

        filteredData.forEach(item => {
            if (!pivot[item.kecamatan]) {
                pivot[item.kecamatan] = makeEmptyRow(item.kecamatan, null);
            }
            // School pivot
            const kec = item.kecamatan;
            const sch = item.sekolah || '(Tidak Diketahui)';
            if (!schoolPivot[kec]) schoolPivot[kec] = {};
            if (!schoolPivot[kec][sch]) schoolPivot[kec][sch] = makeEmptyRow(kec, sch);

            const jenjang = item.jenjang?.toLowerCase() || '';
            const kelas = item.kelas?.toString().toLowerCase().trim() || '';
            const status = item.status?.toLowerCase() || '';
            const jk = item.jenisKelamin?.toLowerCase() || '';
            const nama = item.nama?.trim() || '';
            const nik = item.nik?.trim() || '';

            // Check if data is invalid (missing critical fields)
            const isInvalid = !nama || !nik || !kelas;
            if (isInvalid) {
                invalidRecords.push({
                    kecamatan: item.kecamatan || '-',
                    sekolah: item.sekolah || '-',
                    jenjang: item.jenjang || '-',
                    nama: nama || '(kosong)',
                    nik: nik || '(kosong)',
                    kelas: kelas || '(kosong)',
                    jenisKelamin: item.jenisKelamin || '-',
                    status: item.status || '-',
                    alasan: 'Data tidak lengkap (Nama/NIK/Kelas kosong)'
                });
                return; // Skip this record from pivot calculation
            }

            // Determine gender suffix
            const genderSuffix = (jk.includes('laki') || jk === 'l') ? 'l' : 'p';

            // Check if this is Putus or LTM
            const isPutus = status.includes('putus');
            const isLTM = status.includes('tidak melanjutkan') || kelas.includes('lulus');

            let processed = false;

            if (isPutus) {
                // Handle Putus Sekolah
                if (jenjang.includes('sd') || jenjang.includes('mi')) {
                    const kelasKey = `${kelas}_${genderSuffix}`;
                    if (pivot[kec].putus.sd[kelasKey] !== undefined) {
                        pivot[kec].putus.sd[kelasKey]++;
                        schoolPivot[kec][sch].putus.sd[kelasKey]++;
                        validCount++;
                        processed = true;
                    }
                } else if (jenjang.includes('smp') || jenjang.includes('mts')) {
                    const kelasKey = `${kelas}_${genderSuffix}`;
                    if (pivot[kec].putus.smp[kelasKey] !== undefined) {
                        pivot[kec].putus.smp[kelasKey]++;
                        schoolPivot[kec][sch].putus.smp[kelasKey]++;
                        validCount++;
                        processed = true;
                    }
                }
            } else if (isLTM) {
                // Handle LTM (Lulus Tidak Melanjutkan) - separated by SD and SMP
                if (jenjang.includes('sd') || jenjang.includes('mi')) {
                    pivot[kec].ltm.sd[genderSuffix]++;
                    schoolPivot[kec][sch].ltm.sd[genderSuffix]++;
                    validCount++;
                    processed = true;
                } else if (jenjang.includes('smp') || jenjang.includes('mts')) {
                    pivot[kec].ltm.smp[genderSuffix]++;
                    schoolPivot[kec][sch].ltm.smp[genderSuffix]++;
                    validCount++;
                    processed = true;
                }
            }

            // If not processed, track as invalid
            if (!processed) {
                let alasan = '';
                if (!isPutus && !isLTM) {
                    alasan = 'Status tidak termasuk Putus Sekolah atau LTM';
                } else if (isPutus && !jenjang.includes('sd') && !jenjang.includes('mi') && !jenjang.includes('smp') && !jenjang.includes('mts')) {
                    alasan = 'Jenjang tidak valid (bukan SD/MI/SMP/MTS)';
                } else {
                    alasan = `Kelas "${kelas}" tidak sesuai dengan range (SD: 1-6, SMP: 7-9)`;
                }

                invalidRecords.push({
                    kecamatan: item.kecamatan || '-',
                    sekolah: item.sekolah || '-',
                    jenjang: item.jenjang || '-',
                    nama: nama,
                    nik: nik,
                    kelas: kelas,
                    jenisKelamin: item.jenisKelamin || '-',
                    status: item.status || '-',
                    alasan: alasan
                });
            }
        });

        // Store invalid data for display
        setInvalidData(invalidRecords);
        setEmptyKelasCount(0); // Not needed anymore, already tracked in invalidRecords

        console.log('Data Summary:', {
            total: filteredData.length,
            valid: validCount,
            invalid: invalidRecords.length,
            check: validCount + invalidRecords.length === filteredData.length ? '✓' : '✗'
        });

        // Helper to compute row totals in-place
        const computeTotals = (row) => {
            row.totalPutusSD_L = row.putus.sd['1_l'] + row.putus.sd['2_l'] + row.putus.sd['3_l'] +
                row.putus.sd['4_l'] + row.putus.sd['5_l'] + row.putus.sd['6_l'];
            row.totalPutusSD_P = row.putus.sd['1_p'] + row.putus.sd['2_p'] + row.putus.sd['3_p'] +
                row.putus.sd['4_p'] + row.putus.sd['5_p'] + row.putus.sd['6_p'];
            row.totalPutusSD = row.totalPutusSD_L + row.totalPutusSD_P;
            row.totalPutusSMP_L = row.putus.smp['7_l'] + row.putus.smp['8_l'] + row.putus.smp['9_l'];
            row.totalPutusSMP_P = row.putus.smp['7_p'] + row.putus.smp['8_p'] + row.putus.smp['9_p'];
            row.totalPutusSMP = row.totalPutusSMP_L + row.totalPutusSMP_P;
            row.totalPutusGabungan_L = row.totalPutusSD_L + row.totalPutusSMP_L;
            row.totalPutusGabungan_P = row.totalPutusSD_P + row.totalPutusSMP_P;
            row.totalPutusGabungan = row.totalPutusSD + row.totalPutusSMP;
            row.totalLTM_SD = row.ltm.sd.l + row.ltm.sd.p;
            row.totalLTM_SMP = row.ltm.smp.l + row.ltm.smp.p;
            row.totalLTM_L = row.ltm.sd.l + row.ltm.smp.l;
            row.totalLTM_P = row.ltm.sd.p + row.ltm.smp.p;
            row.totalLTM = row.totalLTM_SD + row.totalLTM_SMP;
            row.grandTotal_L = row.totalPutusGabungan_L + row.totalLTM_L;
            row.grandTotal_P = row.totalPutusGabungan_P + row.totalLTM_P;
            row.grandTotal = row.totalPutusGabungan + row.totalLTM;
        };

        // Convert to array and sort by kecamatan
        const pivotArray = Object.values(pivot).sort((a, b) =>
            a.kecamatan.localeCompare(b.kecamatan)
        );

        // Compute totals for school-level rows
        const computedSchoolPivot = {};
        Object.entries(schoolPivot).forEach(([kec, schools]) => {
            computedSchoolPivot[kec] = Object.values(schools)
                .map(row => { computeTotals(row); return row; })
                .sort((a, b) => a.sekolah.localeCompare(b.sekolah));
        });
        setSchoolPivotData(computedSchoolPivot);

        // Calculate row totals
        pivotArray.forEach(row => {
            // Total Putus SD by gender
            row.totalPutusSD_L = row.putus.sd['1_l'] + row.putus.sd['2_l'] + row.putus.sd['3_l'] +
                row.putus.sd['4_l'] + row.putus.sd['5_l'] + row.putus.sd['6_l'];
            row.totalPutusSD_P = row.putus.sd['1_p'] + row.putus.sd['2_p'] + row.putus.sd['3_p'] +
                row.putus.sd['4_p'] + row.putus.sd['5_p'] + row.putus.sd['6_p'];
            row.totalPutusSD = row.totalPutusSD_L + row.totalPutusSD_P;

            // Total Putus SMP by gender
            row.totalPutusSMP_L = row.putus.smp['7_l'] + row.putus.smp['8_l'] + row.putus.smp['9_l'];
            row.totalPutusSMP_P = row.putus.smp['7_p'] + row.putus.smp['8_p'] + row.putus.smp['9_p'];
            row.totalPutusSMP = row.totalPutusSMP_L + row.totalPutusSMP_P;

            // Total Putus Gabungan (SD + SMP)
            row.totalPutusGabungan_L = row.totalPutusSD_L + row.totalPutusSMP_L;
            row.totalPutusGabungan_P = row.totalPutusSD_P + row.totalPutusSMP_P;
            row.totalPutusGabungan = row.totalPutusSD + row.totalPutusSMP;

            // Total LTM by section
            row.totalLTM_SD = row.ltm.sd.l + row.ltm.sd.p;
            row.totalLTM_SMP = row.ltm.smp.l + row.ltm.smp.p;
            row.totalLTM_L = row.ltm.sd.l + row.ltm.smp.l;
            row.totalLTM_P = row.ltm.sd.p + row.ltm.smp.p;
            row.totalLTM = row.totalLTM_SD + row.totalLTM_SMP;

            // Grand Total (Putus + LTM)
            row.grandTotal_L = row.totalPutusGabungan_L + row.totalLTM_L;
            row.grandTotal_P = row.totalPutusGabungan_P + row.totalLTM_P;
            row.grandTotal = row.totalPutusGabungan + row.totalLTM;
        });

        setPivotData(pivotArray);
    };

    const resetFilters = () => {
        setFilters({
            jenisKelamin: 'Semua',
            status: 'Semua'
        });
    };

    // Calculate column totals
    const calculateColumnTotals = () => {
        const totals = {
            putus: {
                sd: { '1_l': 0, '1_p': 0, '2_l': 0, '2_p': 0, '3_l': 0, '3_p': 0, '4_l': 0, '4_p': 0, '5_l': 0, '5_p': 0, '6_l': 0, '6_p': 0 },
                smp: { '7_l': 0, '7_p': 0, '8_l': 0, '8_p': 0, '9_l': 0, '9_p': 0 }
            },
            ltm: {
                sd: { 'l': 0, 'p': 0 },
                smp: { 'l': 0, 'p': 0 }
            },
            totalPutusSD_L: 0,
            totalPutusSD_P: 0,
            totalPutusSD: 0,
            totalPutusSMP_L: 0,
            totalPutusSMP_P: 0,
            totalPutusSMP: 0,
            totalPutusGabungan_L: 0,
            totalPutusGabungan_P: 0,
            totalPutusGabungan: 0,
            totalLTM_SD: 0,
            totalLTM_SMP: 0,
            totalLTM_L: 0,
            totalLTM_P: 0,
            totalLTM: 0,
            grandTotal_L: 0,
            grandTotal_P: 0,
            grandTotal: 0
        };

        pivotData.forEach(row => {
            // Sum Putus SD
            Object.keys(totals.putus.sd).forEach(key => {
                totals.putus.sd[key] += row.putus.sd[key];
            });

            // Sum Putus SMP
            Object.keys(totals.putus.smp).forEach(key => {
                totals.putus.smp[key] += row.putus.smp[key];
            });

            // Sum LTM
            totals.ltm.sd.l += row.ltm.sd.l;
            totals.ltm.sd.p += row.ltm.sd.p;
            totals.ltm.smp.l += row.ltm.smp.l;
            totals.ltm.smp.p += row.ltm.smp.p;

            // Sum aggregates
            totals.totalPutusSD_L += row.totalPutusSD_L;
            totals.totalPutusSD_P += row.totalPutusSD_P;
            totals.totalPutusSD += row.totalPutusSD;
            totals.totalPutusSMP_L += row.totalPutusSMP_L;
            totals.totalPutusSMP_P += row.totalPutusSMP_P;
            totals.totalPutusSMP += row.totalPutusSMP;
            totals.totalPutusGabungan_L += row.totalPutusGabungan_L;
            totals.totalPutusGabungan_P += row.totalPutusGabungan_P;
            totals.totalPutusGabungan += row.totalPutusGabungan;
            totals.totalLTM_SD += row.totalLTM_SD;
            totals.totalLTM_SMP += row.totalLTM_SMP;
            totals.totalLTM_L += row.totalLTM_L;
            totals.totalLTM_P += row.totalLTM_P;
            totals.totalLTM += row.totalLTM;
            totals.grandTotal_L += row.grandTotal_L;
            totals.grandTotal_P += row.grandTotal_P;
            totals.grandTotal += row.grandTotal;
        });

        return totals;
    };

    // Helper: convert a row (kecamatan or school) to export object
    const rowToExportObj = (no, label, row) => ({
        'No': no,
        'Kecamatan': label,
        'SD K1 L': row.putus.sd['1_l'],
        'SD K1 P': row.putus.sd['1_p'],
        'SD K2 L': row.putus.sd['2_l'],
        'SD K2 P': row.putus.sd['2_p'],
        'SD K3 L': row.putus.sd['3_l'],
        'SD K3 P': row.putus.sd['3_p'],
        'SD K4 L': row.putus.sd['4_l'],
        'SD K4 P': row.putus.sd['4_p'],
        'SD K5 L': row.putus.sd['5_l'],
        'SD K5 P': row.putus.sd['5_p'],
        'SD K6 L': row.putus.sd['6_l'],
        'SD K6 P': row.putus.sd['6_p'],
        'Total Putus SD (Gabungan) L': row.totalPutusSD_L,
        'Total Putus SD (Gabungan) P': row.totalPutusSD_P,
        'Total Putus SD': row.totalPutusSD,
        'SMP K7 L': row.putus.smp['7_l'],
        'SMP K7 P': row.putus.smp['7_p'],
        'SMP K8 L': row.putus.smp['8_l'],
        'SMP K8 P': row.putus.smp['8_p'],
        'SMP K9 L': row.putus.smp['9_l'],
        'SMP K9 P': row.putus.smp['9_p'],
        'Total Putus SMP (Gabungan) L': row.totalPutusSMP_L,
        'Total Putus SMP (Gabungan) P': row.totalPutusSMP_P,
        'Total Putus SMP': row.totalPutusSMP,
        'Total Putus SD+SMP (Gabungan) L': row.totalPutusGabungan_L,
        'Total Putus SD+SMP (Gabungan) P': row.totalPutusGabungan_P,
        'Total Putus SD+SMP': row.totalPutusGabungan,
        'LTM Jenjang SD L': row.ltm.sd.l,
        'LTM Jenjang SD P': row.ltm.sd.p,
        'LTM Jenjang SMP L': row.ltm.smp.l,
        'LTM Jenjang SMP P': row.ltm.smp.p,
        'Total LTM L': row.totalLTM_L,
        'Total LTM P': row.totalLTM_P,
        'Total Putus dan LTM L': row.grandTotal_L,
        'Total Putus dan LTM P': row.grandTotal_P
    });

    const handleExportExcel = () => {
        const exportData = [];
        pivotData.forEach((row, idx) => {
            // Kecamatan row
            exportData.push(rowToExportObj(idx + 1, row.kecamatan, row));
            // School sub-rows (if expanded)
            if (expandedKecamatan.has(row.kecamatan)) {
                const schools = schoolPivotData[row.kecamatan] || [];
                schools.forEach(sch => {
                    exportData.push(rowToExportObj('', `  - ${sch.sekolah}`, sch));
                });
            }
        });

        // Add totals row
        const totals = calculateColumnTotals();
        exportData.push(rowToExportObj('', 'TOTAL', totals));

        const ws = XLSX.utils.json_to_sheet(exportData);
        const wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, 'Rangkuman');
        XLSX.writeFile(wb, `Rangkuman_Detail_${new Date().toISOString().split('T')[0]}.xlsx`);
    };

    if (loading) {
        return (
            <Card className="p-8">
                <div className="flex items-center justify-center">
                    <div className="animate-spin rounded-full h-12 w-12 border-t-4 border-purple-500"></div>
                    <p className="ml-4 text-slate-600">Memuat rangkuman...</p>
                </div>
            </Card>
        );
    }

    const totals = calculateColumnTotals();

    return (
        <Card className="overflow-hidden mt-8">
            <div className="px-6 py-5 border-b border-slate-100 bg-gradient-to-r from-purple-50 to-pink-50">
                <div className="flex flex-col md:flex-row justify-between items-start md:items-center gap-4">
                    <div>
                        <h2 className="text-2xl font-bold text-slate-800">Rangkuman Detail per Kecamatan (Putus & LTM)</h2>
                        <p className="text-sm text-slate-500 mt-1">
                            Total Kecamatan: <span className="font-bold text-purple-600">{pivotData.length}</span> |
                            Total Siswa: <span className="font-bold text-pink-600">{totals.grandTotal}</span>
                        </p>
                    </div>
                    <div className="flex gap-2 flex-wrap">
                        <button
                            onClick={expandAll}
                            className="flex items-center gap-2 px-4 py-2 bg-blue-100 hover:bg-blue-200 text-blue-800 rounded-lg text-sm font-medium transition-all"
                        >
                            <Icon name="ChevronDown" size={16} />
                            Expand All
                        </button>
                        <button
                            onClick={collapseAll}
                            className="flex items-center gap-2 px-4 py-2 bg-slate-100 hover:bg-slate-200 text-slate-700 rounded-lg text-sm font-medium transition-all"
                        >
                            <Icon name="ChevronUp" size={16} />
                            Collapse All
                        </button>
                        <button
                            onClick={resetFilters}
                            className="flex items-center gap-2 px-4 py-2 bg-slate-100 hover:bg-slate-200 rounded-lg text-sm font-medium transition-all"
                        >
                            <Icon name="RefreshCw" size={16} />
                            Reset
                        </button>
                        <button
                            onClick={handleExportExcel}
                            className="flex items-center gap-2 px-4 py-2 bg-green-600 hover:bg-green-700 text-white rounded-lg text-sm font-bold transition-all"
                        >
                            <Icon name="Download" size={16} />
                            Export Excel
                        </button>
                    </div>
                </div>

                {/* Filters */}
                <div className="grid grid-cols-1 md:grid-cols-2 gap-3 mt-4">
                    <select
                        value={filters.jenisKelamin}
                        onChange={(e) => setFilters({ ...filters, jenisKelamin: e.target.value })}
                        className="px-4 py-2 border border-slate-200 rounded-lg text-sm focus:ring-2 focus:ring-purple-500 focus:border-purple-500"
                    >
                        <option value="Semua">Semua Jenis Kelamin</option>
                        <option value="Laki-Laki">Laki-Laki</option>
                        <option value="Perempuan">Perempuan</option>
                    </select>
                    <select
                        value={filters.status}
                        onChange={(e) => setFilters({ ...filters, status: e.target.value })}
                        className="px-4 py-2 border border-slate-200 rounded-lg text-sm focus:ring-2 focus:ring-purple-500 focus:border-purple-500"
                    >
                        <option value="Semua">Semua Status</option>
                        <option value="Putus Sekolah">Putus Sekolah</option>
                        <option value="Tidak Melanjutkan">Tidak Melanjutkan (LTM)</option>
                    </select>
                </div>
            </div>

            <div className="overflow-x-auto">
                <table className="w-full border-collapse text-xs">
                    <thead>
                        {/* Row 1: Main Categories */}
                        <tr className="bg-slate-100 border-b border-slate-300">
                            <th rowSpan="3" className="px-2 py-2 text-center text-[10px] font-bold text-slate-700 uppercase border border-slate-300">No</th>
                            <th rowSpan="3" className="px-3 py-2 text-center text-[10px] font-bold text-slate-700 uppercase border border-slate-300 min-w-[140px]">Kecamatan</th>
                            <th colSpan="12" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-100">Jenjang SD</th>
                            <th rowSpan="2" colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-red-200">TOTAL PUTUS SD<br />(GABUNGAN)</th>
                            <th rowSpan="3" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-red-100">TOTAL<br />PUTUS<br />SD</th>
                            <th colSpan="6" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-indigo-100">Jenjang SMP</th>
                            <th rowSpan="2" colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-indigo-200">TOTAL PUTUS SMP<br />(GABUNGAN)</th>
                            <th rowSpan="3" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-red-100">TOTAL<br />PUTUS<br />SMP</th>
                            <th rowSpan="2" colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-red-200">TOTAL<br />PUTUS SD +<br />PUTUS SMP<br />(GABUNGAN)</th>
                            <th rowSpan="3" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-red-200">TOTAL<br />PUTUS SD +<br />PUTUS SMP</th>
                            <th colSpan="4" className="px-2 py-2 text-center text-[10px] font-bold text-slate-700 uppercase border border-slate-300 bg-orange-100">LTM</th>
                            <th rowSpan="3" className="px-2 py-2 text-center text-[10px] font-bold text-slate-700 uppercase border border-slate-300 bg-orange-200">TOTAL<br />LTM<br />(Jenjang SD +<br />Jenjang SMP)</th>
                            <th rowSpan="3" className="px-3 py-2 text-center text-[10px] font-bold text-slate-700 uppercase border border-slate-300 bg-purple-100">TOTAL<br />PUTUS<br />DAN LTM</th>
                        </tr>

                        {/* Row 2: Class Names & Sub-sections */}
                        <tr className="bg-slate-50 border-b border-slate-300">
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 1</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 2</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 3</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 4</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 5</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-blue-50">Kelas 6</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-indigo-50">Kelas 7</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-indigo-50">Kelas 8</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-indigo-50">Kelas 9</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-orange-50">Jenjang SD</th>
                            <th colSpan="2" className="px-2 py-2 text-center text-[10px] font-bold text-slate-600 border border-slate-300 bg-orange-50">Jenjang SMP</th>
                        </tr>

                        {/* Row 3: Gender Split (L/P) */}
                        <tr className="bg-white border-b-2 border-slate-400">
                            {/* SD Classes Gender */}
                            {['1', '2', '3', '4', '5', '6'].map(kelas => (
                                <React.Fragment key={`sd-${kelas}`}>
                                    <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-blue-50">L</th>
                                    <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-50">P</th>
                                </React.Fragment>
                            ))}

                            {/* TOTAL PUTUS SD (GABUNGAN) Gender */}
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-blue-100">L</th>
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-100">P</th>

                            {/* SMP Classes Gender */}
                            {['7', '8', '9'].map(kelas => (
                                <React.Fragment key={`smp-${kelas}`}>
                                    <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-indigo-50">L</th>
                                    <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-50">P</th>
                                </React.Fragment>
                            ))}


                            {/* TOTAL PUTUS SMP (GABUNGAN) Gender */}
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-indigo-100">L</th>
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-100">P</th>

                            {/* TOTAL PUTUS SD + PUTUS SMP (GABUNGAN) Gender */}
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-red-200">L</th>
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-200">P</th>

                            {/* LTM Jenjang SD Gender */}
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-orange-50">L</th>
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-50">P</th>

                            {/* LTM Jenjang SMP Gender */}
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-orange-50">L</th>
                            <th className="px-1 py-1 text-center text-[9px] font-semibold text-slate-600 border border-slate-300 bg-pink-50">P</th>
                        </tr>
                    </thead>
                    <tbody>
                        {pivotData.length === 0 ? (
                            <tr>
                                <td colSpan="36" className="px-4 py-8 text-center text-slate-500 border border-slate-300">
                                    Tidak ada data
                                </td>
                            </tr>
                        ) : (
                            pivotData.map((row, idx) => {
                                const isExpanded = expandedKecamatan.has(row.kecamatan);
                                const schools = schoolPivotData[row.kecamatan] || [];
                                return (
                                    <React.Fragment key={idx}>
                                        <tr className="hover:bg-slate-50 transition-colors">
                                            <td className="px-2 py-2 text-center border border-slate-300">{idx + 1}</td>
                                            <td
                                                className="px-3 py-2 font-medium border border-slate-300 cursor-pointer select-none"
                                                onClick={() => toggleKecamatan(row.kecamatan)}
                                            >
                                                <span className="inline-flex items-center gap-1">
                                                    <span style={{ display: 'inline-block', transition: 'transform 0.2s', transform: isExpanded ? 'rotate(90deg)' : 'rotate(0deg)', fontSize: '10px', color: '#7c3aed' }}>▶</span>
                                                    {row.kecamatan}
                                                </span>
                                            </td>

                                            {/* Putus SD Classes */}
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['1_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['1_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['2_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['2_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['3_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['3_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['4_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['4_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['5_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['5_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-blue-50/20">{row.putus.sd['6_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.sd['6_p'] || ''}</td>

                                            {/* Total Putus SD (Gabungan) by gender - DIPINDAHKAN KE SINI */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-blue-100">{row.totalPutusSD_L || ''}</td>
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-pink-100">{row.totalPutusSD_P || ''}</td>

                                            {/* Total Putus SD */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-red-50">{row.totalPutusSD || ''}</td>

                                            {/* Putus SMP Classes */}
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-indigo-50/20">{row.putus.smp['7_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.smp['7_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-indigo-50/20">{row.putus.smp['8_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.smp['8_p'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-indigo-50/20">{row.putus.smp['9_l'] || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.putus.smp['9_p'] || ''}</td>

                                            {/* Total Putus SMP (Gabungan) by gender - KOLOM BARU */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-indigo-100">{row.totalPutusSMP_L || ''}</td>
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-pink-100">{row.totalPutusSMP_P || ''}</td>

                                            {/* Total Putus SMP */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-red-50">{row.totalPutusSMP || ''}</td>

                                            {/* Total Putus SD+SMP (Gabungan) by gender - DIPINDAHKAN KE SINI */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-red-200">{row.totalPutusGabungan_L || ''}</td>
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-red-200">{row.totalPutusGabungan_P || ''}</td>

                                            {/* Total Putus SD+SMP */}
                                            <td className="px-2 py-2 text-center font-bold border border-slate-300 bg-red-100">{row.totalPutusGabungan || ''}</td>

                                            {/* LTM Jenjang SD */}
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-orange-50/20">{row.ltm.sd.l || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.ltm.sd.p || ''}</td>

                                            {/* LTM Jenjang SMP */}
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-orange-50/20">{row.ltm.smp.l || ''}</td>
                                            <td className="px-1 py-2 text-center border border-slate-300 bg-pink-50/20">{row.ltm.smp.p || ''}</td>

                                            {/* Total LTM */}
                                            <td className="px-2 py-2 text-center font-semibold border border-slate-300 bg-orange-100">{row.totalLTM || ''}</td>

                                            {/* Grand Total */}
                                            <td className="px-2 py-2 text-center font-bold border border-slate-300 bg-purple-100">{row.grandTotal || ''}</td>
                                        </tr>
                                        {/* School sub-rows */}
                                        {isExpanded && schools.map((sch, sIdx) => (
                                            <tr key={`sch-${idx}-${sIdx}`} className="bg-violet-50 border-l-4 border-l-violet-400 text-[10px]">
                                                <td className="px-2 py-1 text-center border border-slate-200 text-slate-400"></td>
                                                <td className="px-3 py-1 border border-slate-200">
                                                    <span className="ml-4 text-violet-700 font-medium">{sch.sekolah}</span>
                                                </td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['1_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['1_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['2_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['2_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['3_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['3_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['4_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['4_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['5_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['5_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-blue-50/30">{sch.putus.sd['6_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.sd['6_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-blue-100">{sch.totalPutusSD_L || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-pink-100">{sch.totalPutusSD_P || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-red-50">{sch.totalPutusSD || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-indigo-50/30">{sch.putus.smp['7_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.smp['7_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-indigo-50/30">{sch.putus.smp['8_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.smp['8_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-indigo-50/30">{sch.putus.smp['9_l'] || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.putus.smp['9_p'] || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-indigo-100">{sch.totalPutusSMP_L || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-pink-100">{sch.totalPutusSMP_P || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-red-50">{sch.totalPutusSMP || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-red-200">{sch.totalPutusGabungan_L || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-red-200">{sch.totalPutusGabungan_P || ''}</td>
                                                <td className="px-1 py-1 text-center font-bold border border-slate-200 bg-red-100">{sch.totalPutusGabungan || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-orange-50/30">{sch.ltm.sd.l || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.ltm.sd.p || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-orange-50/30">{sch.ltm.smp.l || ''}</td>
                                                <td className="px-1 py-1 text-center border border-slate-200 bg-pink-50/30">{sch.ltm.smp.p || ''}</td>
                                                <td className="px-1 py-1 text-center font-semibold border border-slate-200 bg-orange-100">{sch.totalLTM || ''}</td>
                                                <td className="px-1 py-1 text-center font-bold border border-slate-200 bg-purple-100">{sch.grandTotal || ''}</td>
                                            </tr>
                                        ))}
                                    </React.Fragment>
                                );
                            })
                        )}
                        {/* TOTAL Row */}
                        <tr className="bg-slate-200 font-bold border-t-2 border-slate-400">
                            <td colSpan="2" className="px-3 py-2 text-center border border-slate-400">TOTAL</td>

                            {/* Putus SD Totals */}
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['1_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['1_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['2_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['2_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['3_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['3_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['4_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['4_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['5_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['5_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['6_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.sd['6_p']}</td>

                            <td className="px-2 py-2 text-center border border-slate-400 bg-blue-100">{totals.totalPutusSD_L}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-pink-100">{totals.totalPutusSD_P}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-red-100">{totals.totalPutusSD}</td>

                            {/* Putus SMP Totals */}
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['7_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['7_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['8_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['8_p']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['9_l']}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.putus.smp['9_p']}</td>

                            <td className="px-2 py-2 text-center border border-slate-400 bg-indigo-100">{totals.totalPutusSMP_L}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-pink-100">{totals.totalPutusSMP_P}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-red-100">{totals.totalPutusSMP}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-red-200">{totals.totalPutusGabungan_L}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-red-200">{totals.totalPutusGabungan_P}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-red-200">{totals.totalPutusGabungan}</td>

                            {/* LTM Totals */}
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.ltm.sd.l}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.ltm.sd.p}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.ltm.smp.l}</td>
                            <td className="px-1 py-2 text-center border border-slate-400">{totals.ltm.smp.p}</td>
                            <td className="px-2 py-2 text-center border border-slate-400 bg-orange-200">{totals.totalLTM}</td>

                            {/* Grand Total */}
                            <td className="px-2 py-2 text-center border border-slate-400 bg-purple-200">{totals.grandTotal}</td>
                        </tr>
                    </tbody>
                </table>
            </div>

            {/* Empty Kelas Warning */}
            {emptyKelasCount > 0 && (
                <div className="px-6 py-4 bg-yellow-50 border-t border-yellow-200">
                    <div className="flex items-center gap-2 text-yellow-800">
                        <Icon name="AlertTriangle" size={18} />
                        <p className="text-sm font-medium">
                            <span className="font-bold">{emptyKelasCount}</span> data memiliki kolom kelas yang kosong dan tidak ditampilkan dalam tabel.
                        </p>
                    </div>
                </div>
            )}

            {/* Invalid Data Warning */}
            {invalidData.length > 0 && (
                <div className="border-t border-red-200">
                    <div className="px-6 py-4 bg-red-50">
                        <div className="flex items-start gap-3">
                            <Icon name="AlertCircle" size={20} className="text-red-600 mt-0.5 flex-shrink-0" />
                            <div className="flex-1">
                                <h3 className="text-sm font-bold text-red-900 mb-2">
                                    Data Tidak Valid: {invalidData.length} siswa
                                </h3>
                                <p className="text-xs text-red-700 mb-3">
                                    Data-data berikut tidak ditampilkan dalam tabel karena tidak memiliki informasi lengkap (Nama, NIK, atau Kelas kosong):
                                </p>

                                {/* Group invalid data by kecamatan */}
                                {(() => {
                                    const grouped = invalidData.reduce((acc, item) => {
                                        if (!acc[item.kecamatan]) {
                                            acc[item.kecamatan] = {};
                                        }
                                        if (!acc[item.kecamatan][item.jenjang]) {
                                            acc[item.kecamatan][item.jenjang] = {};
                                        }
                                        if (!acc[item.kecamatan][item.jenjang][item.sekolah]) {
                                            acc[item.kecamatan][item.jenjang][item.sekolah] = [];
                                        }
                                        acc[item.kecamatan][item.jenjang][item.sekolah].push(item);
                                        return acc;
                                    }, {});

                                    return (
                                        <div className="space-y-3">
                                            {Object.entries(grouped).map(([kecamatan, jenjangs]) => (
                                                <div key={kecamatan} className="bg-white rounded-lg p-3 border border-red-200">
                                                    <h4 className="text-xs font-bold text-red-800 mb-2">
                                                        Kecamatan {kecamatan}
                                                    </h4>
                                                    {Object.entries(jenjangs).map(([jenjang, sekolahs]) => (
                                                        <div key={jenjang} className="ml-3 mb-2">
                                                            <p className="text-xs font-semibold text-red-700 mb-1">
                                                                {jenjang}
                                                            </p>
                                                            {Object.entries(sekolahs).map(([sekolah, items]) => (
                                                                <div key={sekolah} className="ml-4 mb-1">
                                                                    <p className="text-xs text-red-600">
                                                                        <span className="font-medium">{sekolah}</span>
                                                                        <span className="text-red-500 ml-1">
                                                                            ({items.length} siswa)
                                                                        </span>
                                                                    </p>
                                                                    <div className="ml-2 mt-1 text-[10px] text-red-500 max-h-32 overflow-y-auto">
                                                                        {items.map((item, idx) => (
                                                                            <div key={idx} className="mb-1 border-l-2 border-red-300 pl-2 py-0.5">
                                                                                <div className="font-medium">{item.nama}</div>
                                                                                <div className="text-[9px] text-red-400">
                                                                                    NIK: {item.nik} | Kelas: {item.kelas} | JK: {item.jenisKelamin}
                                                                                </div>
                                                                                <div className="text-[9px] text-red-600 font-medium italic">
                                                                                    ⚠ {item.alasan}
                                                                                </div>
                                                                            </div>
                                                                        ))}
                                                                    </div>
                                                                </div>
                                                            ))}
                                                        </div>
                                                    ))}
                                                </div>
                                            ))}
                                        </div>
                                    );
                                })()}
                            </div>
                        </div>
                    </div>
                </div>
            )}
        </Card>
    );
};