<?php
/**
 * PARSER EXCEL - Format Aggregate
 * Handle format: "PIUTANG SPP DKT DSP SERAGAM" (semua kategori jadi satu)
 */

function parseExcelAggregate(string $filepath): array {
    if (!class_exists('PhpOffice\PhpSpreadsheet\IOFactory')) {
        throw new Exception('PhpSpreadsheet library tidak terinstal');
    }

    try {
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filepath);
        $sheet = $spreadsheet->getActiveSheet();
        $dataRows = $sheet->toArray(null, true, true, true);
    } catch (Exception $e) {
        throw new Exception('Gagal membaca file Excel: ' . $e->getMessage());
    }

    $rows = [];
    $detectedPeriod = '';
    $detectedCategory = 'TOTAL'; // Default untuk format aggregate
    
    foreach ($dataRows as $index => $row) {
        // Skip header row (row 1, 2, 3 biasanya header)
        if ($index <= 3) continue;
        
        // Kolom mapping berdasarkan gambar:
        // A = NO, B = TA, C = Period, D = Category, E = Month, F = TARGET, G = CAPAIAN, H = PIUTANG, I = %, J = KATEGORI
        $no = trim((string)($row['A'] ?? ''));
        $ta = trim((string)($row['B'] ?? ''));
        $period = trim((string)($row['C'] ?? ''));
        $category = trim((string)($row['D'] ?? ''));
        $month = trim((string)($row['E'] ?? ''));
        $target = cleanNum($row['F'] ?? 0);
        $capaian = cleanNum($row['G'] ?? 0);
        $piutang = cleanNum($row['H'] ?? 0);
        $persen_str = trim((string)($row['I'] ?? ''));
        $kategori = trim((string)($row['J'] ?? ''));
        
        // Stop di baris TOTAL
        if (strtoupper($ta) === 'TOTAL' || strtoupper($no) === 'TOTAL') {
            break;
        }
        
        // Skip baris kosong
        if (empty($ta) || $ta === '' || $ta === '-') {
            continue;
        }
        
        // Auto-detect period dari data pertama
        if (empty($detectedPeriod) && !empty($period)) {
            $detectedPeriod = $period;
        }
        
        // Clean percentage (remove % sign)
        $persen = cleanNum(str_replace('%', '', $persen_str));
        
        // Recalculate jika perlu
        if ($persen == 0 && $target > 0) {
            $persen = calculatePercentage($capaian, $target);
        }
        
        // Recalculate piutang jika perlu
        if ($piutang == 0) {
            $piutang = $target - $capaian;
        }
        
        // Auto kategori jika kosong
        if (empty($kategori) || $kategori === '-') {
            $kategori = getKategoriLabel($persen);
        }
        
        // Normalize category name
        // Jika category berisi "PIUTANG SPP DKT DSP SERAGAM" atau gabungan lainnya
        // Convert jadi "TOTAL" karena ini aggregate
        if (stripos($category, 'PIUTANG') !== false || 
            stripos($category, 'SPP DKT DSP') !== false ||
            empty($category)) {
            $category = 'TOTAL';
        }
        
        // Gunakan detected period jika kosong
        $finalPeriod = !empty($period) ? $period : $detectedPeriod;
        
        if (empty($finalPeriod)) {
            continue; // Skip jika period masih kosong
        }
        
        $rows[] = [
            'ta' => sanitizeString($ta),
            'period' => sanitizeString($finalPeriod),
            'category' => sanitizeString($category),
            'month' => 'TOTAL', // Hardcode karena data aggregate
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $persen,
            'kategori' => validateKategori($kategori)
        ];
    }
    
    if (empty($rows)) {
        throw new Exception('Tidak ada data valid yang ditemukan dalam file Excel');
    }
    
    return [
        'period' => $detectedPeriod,
        'category' => $detectedCategory,
        'rows' => $rows,
        'total_rows' => count($rows)
    ];
}

/**
 * Validate kategori string
 */
function validateKategori(string $kategori): string {
    $kategori = strtoupper(trim($kategori));
    $validKategori = ['EXCELLENT', 'GOOD', 'AVERAGE', 'POOR'];
    
    return in_array($kategori, $validKategori, true) ? $kategori : 'POOR';
}

/**
 * Calculate percentage safely
 */
function calculatePercentage(float $capaian, float $target): float {
    return $target > 0 ? round(($capaian / $target) * 100, 2) : 0.0;
}

/**
 * Get kategori label based on percentage
 */
function getKategoriLabel(float $persen): string {
    if ($persen >= 95) return 'EXCELLENT';
    if ($persen >= 85) return 'GOOD';
    if ($persen >= 70) return 'AVERAGE';
    return 'POOR';
}

/**
 * Clean numeric value from string
 */
function cleanNum($val): float {
    if (is_numeric($val)) {
        return (float)$val;
    }
    
    // Remove all non-numeric except dot and minus
    $cleaned = preg_replace('/[^\d.-]/', '', str_replace(',', '', (string)$val));
    return $cleaned !== '' ? (float)$cleaned : 0.0;
}

/**
 * Sanitize string input
 */
function sanitizeString(string $input): string {
    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}