<?php
/**
 * ACTION IMPORT - Updated untuk Handle Format Aggregate
 * 
 * Format yang didukung:
 * 1. Format Aggregate: "PIUTANG SPP DKT DSP SERAGAM" (semua kategori jadi satu)
 * 2. Format Separated: SPP, DKT, DSP terpisah
 */

// Include parser functions
require_once 'parser_excel_aggregate.php';

function action_import(Database $db): void {
    // Check admin access
    if (!isAdmin()) {
        jsonResponse(false, null, 'Akses ditolak. Hanya admin yang dapat import.', 403);
    }
    
    // Validate file upload
    if (!isset($_FILES['file'])) {
        throw new Exception('File tidak ditemukan dalam request');
    }
    
    $file = $_FILES['file'];
    
    // Check upload errors
    if ($file['error'] !== UPLOAD_ERR_OK) {
        $errors = [
            UPLOAD_ERR_INI_SIZE => 'File terlalu besar (melebihi upload_max_filesize)',
            UPLOAD_ERR_FORM_SIZE => 'File terlalu besar (melebihi MAX_FILE_SIZE)',
            UPLOAD_ERR_PARTIAL => 'File hanya terupload sebagian',
            UPLOAD_ERR_NO_FILE => 'Tidak ada file yang diupload',
            UPLOAD_ERR_NO_TMP_DIR => 'Temporary folder tidak ditemukan',
            UPLOAD_ERR_CANT_WRITE => 'Gagal menulis file ke disk',
            UPLOAD_ERR_EXTENSION => 'Upload dihentikan oleh ekstensi PHP'
        ];
        throw new Exception($errors[$file['error']] ?? 'Error upload tidak diketahui');
    }
    
    // Check file size
    if ($file['size'] > MAX_UPLOAD_SIZE) {
        throw new Exception('Ukuran file terlalu besar. Maksimal ' . (MAX_UPLOAD_SIZE / 1024 / 1024) . 'MB');
    }
    
    // Validate file extension
    $allowedExtensions = ['xlsx', 'xls', 'csv'];
    $fileExtension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    
    if (!in_array($fileExtension, $allowedExtensions)) {
        throw new Exception("Format file tidak didukung. Gunakan: " . implode(', ', $allowedExtensions));
    }
    
    // Parse file based on extension
    try {
        if ($fileExtension === 'csv') {
            $parsed = parseCSV($file['tmp_name']);
        } else {
            // Use aggregate parser for Excel
            $parsed = parseExcelAggregate($file['tmp_name']);
        }
    } catch (Exception $e) {
        throw new Exception("Gagal parsing file: " . $e->getMessage());
    }
    
    // Validate parsed data
    if (empty($parsed['period'])) {
        throw new Exception('Period tidak ditemukan dalam file. Pastikan format file sesuai template');
    }
    
    if (empty($parsed['rows'])) {
        throw new Exception('Tidak ada data valid untuk diimport');
    }
    
    // Start transaction
    $db->beginTransaction();
    
    try {
        // Prepare statements
        $checkStmt = $db->prepare("
            SELECT id FROM kepatuhan_pembayaran 
            WHERE TRIM(UPPER(ta)) = TRIM(UPPER(?)) 
            AND TRIM(period) = TRIM(?) 
            AND TRIM(UPPER(category)) = TRIM(UPPER(?)) 
            AND TRIM(month) = TRIM(?)
            LIMIT 1
        ");
        
        $insertStmt = $db->prepare("
            INSERT INTO kepatuhan_pembayaran 
            (ta, period, category, month, target, capaian, piutang, persen, kategori) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $updateStmt = $db->prepare("
            UPDATE kepatuhan_pembayaran 
            SET target = ?, capaian = ?, piutang = ?, persen = ?, kategori = ?, updated_at = NOW()
            WHERE TRIM(UPPER(ta)) = TRIM(UPPER(?)) 
            AND TRIM(period) = TRIM(?) 
            AND TRIM(UPPER(category)) = TRIM(UPPER(?)) 
            AND TRIM(month) = TRIM(?)
        ");
        
        if (!$checkStmt || !$insertStmt || !$updateStmt) {
            throw new Exception("Gagal prepare SQL statement: " . $db->getConnection()->error);
        }
        
        $imported = 0;
        $updated = 0;
        $skipped = 0;
        $errors = [];
        
        foreach ($parsed['rows'] as $index => $row) {
            $ta = trim($row['ta']);
            $period = trim($row['period']);
            $category = trim($row['category']);
            $month = trim($row['month']);
            $kategori = $row['kategori'];
            
            // Validate required fields
            if (empty($ta) || empty($period)) {
                $errors[] = "Baris " . ($index + 1) . ": TA atau Period kosong";
                continue;
            }
            
            // Skip jika data benar-benar kosong (target dan capaian = 0)
            if ($row['target'] == 0 && $row['capaian'] == 0) {
                $skipped++;
                continue;
            }
            
            // Check if record exists
            $checkStmt->bind_param("ssss", $ta, $period, $category, $month);
            $checkStmt->execute();
            $checkResult = $checkStmt->get_result();
            
            if ($checkResult->num_rows > 0) {
                // Record exists - UPDATE
                $updateStmt->bind_param(
                    "ddddsssss",
                    $row['target'],
                    $row['capaian'],
                    $row['piutang'],
                    $row['persen'],
                    $kategori,
                    $ta,
                    $period,
                    $category,
                    $month
                );
                
                if ($updateStmt->execute()) {
                    if ($updateStmt->affected_rows > 0) {
                        $updated++;
                    } else {
                        $skipped++;
                    }
                }
            } else {
                // Record doesn't exist - INSERT
                $insertStmt->bind_param(
                    "ssssdddds",
                    $ta,
                    $period,
                    $category,
                    $month,
                    $row['target'],
                    $row['capaian'],
                    $row['piutang'],
                    $row['persen'],
                    $kategori
                );
                
                if ($insertStmt->execute()) {
                    $imported++;
                }
            }
        }
        
        $db->commit();
        
        // Build response message
        $message = "";
        if ($imported > 0) {
            $message = "✅ Import berhasil: $imported data baru ditambahkan";
        } elseif ($updated > 0) {
            $message = "⚠️ Import selesai: $updated data diperbarui";
        } elseif ($skipped > 0) {
            $message = "ℹ️ Import selesai: $skipped data di-skip (kosong atau duplikat)";
        } else {
            $message = "ℹ️ Tidak ada data yang diimport";
        }
        
        jsonResponse(true, [
            'message' => $message,
            'imported' => $imported,
            'updated' => $updated,
            'skipped' => $skipped,
            'total_processed' => $imported + $updated + $skipped,
            'period' => $parsed['period'],
            'errors' => $errors
        ]);
        
    } catch (Exception $e) {
        $db->rollback();
        throw $e;
    }
}

/**
 * Simple CSV parser (fallback)
 */
function parseCSV(string $filepath): array {
    $handle = @fopen($filepath, 'r');
    if (!$handle) {
        throw new Exception('Tidak dapat membuka file CSV');
    }

    $rows = [];
    $headers = [];
    $lineNum = 0;
    $period = '';
    
    while (($data = fgetcsv($handle, 10000, ',')) !== false) {
        $lineNum++;
        
        // Skip empty lines
        if (empty(array_filter($data))) continue;
        
        // First line = headers
        if ($lineNum === 1) {
            $headers = array_map('trim', $data);
            continue;
        }
        
        // Try to detect period from early rows
        if (empty($period) && count($data) > 2) {
            $period = trim($data[2] ?? '');
        }
        
        // Skip if not enough columns
        if (count($data) < 7) continue;
        
        $ta = trim($data[1] ?? '');
        
        // Stop at TOTAL
        if (strtoupper($ta) === 'TOTAL' || empty($ta)) {
            break;
        }
        
        $target = cleanNum($data[5] ?? 0);
        $capaian = cleanNum($data[6] ?? 0);
        $piutang = $target - $capaian;
        $persen = calculatePercentage($capaian, $target);
        
        $rows[] = [
            'ta' => sanitizeString($ta),
            'period' => $period,
            'category' => 'TOTAL',
            'month' => 'TOTAL',
            'target' => $target,
            'capaian' => $capaian,
            'piutang' => $piutang,
            'persen' => $persen,
            'kategori' => getKategoriLabel($persen)
        ];
    }
    
    fclose($handle);
    
    if (empty($rows)) {
        throw new Exception('Tidak ada data valid dalam CSV');
    }
    
    return [
        'period' => $period,
        'category' => 'TOTAL',
        'rows' => $rows,
        'total_rows' => count($rows)
    ];
}