<?php
/**
 * Smart Import Module
 * Koperasi Management System
 */

require_once __DIR__ . '/../config/database.php';
$db = getDB();

// Function to convert Excel to CSV (simplified version)
function convertExcelToCsv($excelFile, $csvFile) {
    // For now, let's focus on CSV support and add proper Excel support later
    // This prevents complex conversion issues
    return false;
}

$type = $_POST['type'] ?? $_GET['type'] ?? '';
if (!$type || !in_array($type, ['anggota', 'pinjaman'])) {
    setFlash('danger', 'Tipe import tidak valid.');
    header('Location: index.php');
    exit;
}

$pageTitle = 'Smart Import ' . ucfirst($type);
$pageSubtitle = 'Pemetaan Kolom CSV ke Database';

// Define DB fields for each type
$fields = [
    'anggota' => [
        'nama' => 'Nama Lengkap (Wajib)',
        'nik' => 'NIK',
        'tempat_lahir' => 'Tempat Lahir',
        'tanggal_lahir' => 'Tanggal Lahir (YYYY-MM-DD)',
        'jenis_kelamin' => 'Jenis Kelamin (L/P)',
        'alamat' => 'Alamat',
        'telepon' => 'Telepon',
        'email' => 'Email',
        'pekerjaan' => 'Pekerjaan',
        'tanggal_daftar' => 'Tanggal Daftar (YYYY-MM-DD)'
    ],
    'pinjaman' => [
        'no_anggota_ref' => 'No Anggota (Utama untuk mencocokkan)',
        'nama_anggota_ref' => 'Atau Nama Anggota (Jika No Anggota tidak ada)',
        'jumlah_pinjaman' => 'Jumlah Pinjaman (Tanpa Rp / Titik)',
        'jasa_persen' => 'Jasa/Bunga per Bulan (%)',
        'lama_angsuran' => 'Lama Angsuran (Bulan)',
        'tanggal_pinjaman' => 'Tanggal Pinjaman (YYYY-MM-DD)',
        'catatan' => 'Catatan'
    ]
];

// Handle Step 2: Show Mapping
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file'])) {
    $file = $_FILES['file'];
    
    // Validate file
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    $allowedExtensions = ['csv', 'xlsx', 'xls'];
    if (!in_array($ext, $allowedExtensions)) {
        setFlash('danger', 'Hanya file format .CSV, .XLSX, atau .XLS yang diperbolehkan.');
        header("Location: $type.php");
        exit;
    }

    $tmpName = $file['tmp_name'];
    $filename = 'import_' . time() . '_' . preg_replace("/[^a-zA-Z0-9.]/", "_", $file['name']);
    $targetPath = __DIR__ . '/uploads/' . $filename;
    
    // Handle Excel files by converting to CSV first
    $isExcelFile = in_array($ext, ['xlsx', 'xls']);
    $originalFilePath = $targetPath;
    
    if (move_uploaded_file($tmpName, $targetPath)) {
        // Convert Excel to CSV if needed
        if ($isExcelFile) {
            $csvFilePath = $targetPath . '.csv';
            if (convertExcelToCsv($targetPath, $csvFilePath)) {
                // Use the converted CSV file
                $targetPath = $csvFilePath;
                $filename .= '.csv';
            } else {
                setFlash('danger', 'Gagal mengonversi file Excel ke format CSV.');
                header("Location: $type.php");
                exit;
            }
        }
        // Read headers
        $f = fopen($targetPath, 'r');
        $headers = fgetcsv($f, 0, ','); // Try comma
        
        // Remove BOM from first header if exists
        if (isset($headers[0])) {
            $headers[0] = preg_replace('/^\xEF\xBB\xBF/', '', $headers[0]);
        }
        
        // Better delimiter detection
        if (!$headers || count($headers) <= 1) {
            rewind($f);
            $headers = fgetcsv($f, 0, ';'); // Try semicolon
        }
        if (!$headers || count($headers) <= 1) {
            rewind($f);
            $headers = fgetcsv($f, 0, "\t"); // Try tab
        }
        fclose($f);
        
        if (!$headers) {
            setFlash('danger', 'Gagal membaca header file CSV atau file kosong.');
            header("Location: $type.php");
            exit;
        }
        
        include __DIR__ . '/../includes/header.php';
        ?>
        <div class="card">
            <div class="card-header bg-primary-50">
                <div style="flex: 1;">
                    <h2 style="color: var(--primary-800);"><i class="fas fa-project-diagram"></i> Konfigurasi Pemetaan Data</h2>
                    <p style="font-size: 0.9rem; color: var(--gray-600);">Silakan pilih kolom file Anda yang sesuai dengan data di sistem. <strong>Hanya kolom NAMA yang wajib diisi.</strong> Kolom lain boleh dikosongkan/diabaikan.</p>
                </div>
                <a href="<?= $type ?>.php" class="btn btn-outline">Batal</a>
            </div>
            
            <form action="import.php" method="POST">
                <input type="hidden" name="action" value="process">
                <input type="hidden" name="type" value="<?= $type ?>">
                <input type="hidden" name="file_path" value="<?= $filename ?>">
                
                <div class="modal-body p-0">
                    <table class="data-table">
                        <thead class="bg-gray-50">
                            <tr>
                                <th style="padding-left: 24px;">DATA SISTEM (TUJUAN)</th>
                                <th>KOLOM FILE ANDA (SUMBER)</th>
                                <th width="100">STATUS</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($fields[$type] as $key => $label): ?>
                            <tr>
                                <td style="padding-left: 24px;">
                                    <div style="font-weight: 600; color: var(--gray-800);"><?= $label ?></div>
                                    <div style="font-size: 0.75rem; color: var(--gray-500); font-family: monospace;"><?= $key ?></div>
                                </td>
                                <td>
                                    <select name="mapping[<?= $key ?>]" class="form-control" style="max-width: 400px; border-radius: 8px;">
                                        <option value="">-- Abaikan / Kosongkan --</option>
                                        <?php foreach ($headers as $idx => $header): 
                                            $selected = (strpos(strtolower($header), strtolower($key)) !== false || strpos(strtolower($header), strtolower($label)) !== false);
                                        ?>
                                            <option value="<?= $idx ?>" <?= $selected ? 'selected' : '' ?>>
                                                [Kolom <?= $idx+1 ?>] &nbsp; <?= htmlspecialchars($header) ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </td>
                                <td>
                                    <i class="fas fa-check-circle text-success" id="status_<?= $key ?>"></i>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                
                <div class="card-footer bg-gray-50" style="padding: 20px 24px; display: flex; align-items: center; gap: 15px;">
                    <div class="alert alert-warning m-0" style="flex: 1; font-size: 0.85rem; background: #fffbeb;">
                        <i class="fas fa-exclamation-triangle"></i> Pastikan format tanggal adalah <strong>YYYY-MM-DD</strong> (contoh: 2024-12-31) agar terbaca sistem.
                    </div>
                    <button type="submit" class="btn btn-primary btn-lg" style="height: 48px; padding: 0 32px;">
                        Mulai Import Sekarang <i class="fas fa-bolt" style="margin-left: 8px;"></i>
                    </button>
                </div>
            </form>
        </div>
        <?php
        include __DIR__ . '/../includes/footer.php';
        exit;
    } else {
        setFlash('danger', 'Gagal mengunggah file.');
        header("Location: $type.php");
        exit;
    }
}

// Handle Step 3: Process Import
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'process') {
    $filename = $_POST['file_path'];
    $mapping = $_POST['mapping'];
    $targetPath = __DIR__ . '/uploads/' . $filename;
    
    if (!file_exists($targetPath)) {
        setFlash('danger', 'File tidak ditemukan atau sesi sudah berakhir.');
        header("Location: $type.php");
        exit;
    }
    
    $f = fopen($targetPath, 'r');
    
    // Detect delimiter again
    $headers = fgetcsv($f, 0, ',');
    $delimiter = ',';
    
    // Remove BOM from first header if exists
    if (isset($headers[0])) {
        $headers[0] = preg_replace('/^\xEF\xBB\xBF/', '', $headers[0]);
    }
    
    if (count($headers) <= 1) {
        rewind($f);
        $headers = fgetcsv($f, 0, ';');
        $delimiter = ';';
    }
    if (count($headers) <= 1) {
        rewind($f);
        $headers = fgetcsv($f, 0, "\t");
        $delimiter = "\t";
    }

    $successCount = 0;
    $errorCount = 0;
    
    $db->beginTransaction();
    try {
        while (($row = fgetcsv($f, 0, $delimiter)) !== FALSE) {
            if (empty(array_filter($row))) continue;

            $data = [];
            foreach ($mapping as $key => $idx) {
                if ($idx !== '') {
                    $val = trim($row[$idx] ?? '');
                    // Clean Excel CSV formula format ="value" to handle text-forced cells
                    if (substr($val, 0, 2) === '="' && substr($val, -1) === '"') {
                        $val = substr($val, 2, -1);
                    } elseif (substr($val, 0, 1) === "'") {
                        // Clean single quote prefix often used in Excel to force text
                        $val = substr($val, 1);
                    }
                    $data[$key] = $val;
                } else {
                    $data[$key] = null;
                }
            }
            
            if ($type === 'anggota') {
                if (empty($data['nama'])) { $errorCount++; continue; }
                
                $no_anggota = generateNumber('AGT', 'anggota', 'no_anggota');
                $stmt = $db->prepare("INSERT INTO anggota (no_anggota, nama, nik, tempat_lahir, tanggal_lahir, jenis_kelamin, alamat, telepon, email, pekerjaan, tanggal_daftar) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $stmt->execute([
                    $no_anggota,
                    $data['nama'],
                    $data['nik'],
                    $data['tempat_lahir'],
                    $data['tanggal_lahir'] ?: null,
                    $data['jenis_kelamin'] ?: 'L',
                    $data['alamat'],
                    $data['telepon'],
                    $data['email'],
                    $data['pekerjaan'],
                    $data['tanggal_daftar'] ?: date('Y-m-d')
                ]);
                
                $anggotaId = $db->lastInsertId();
                $simpPokok = getSetting('simpanan_pokok', 100000);
                $stmtSimp = $db->prepare("INSERT INTO simpanan (anggota_id, jenis, tipe, jumlah, tanggal, keterangan, created_by) VALUES (?, 'pokok', 'setoran', ?, ?, 'Import: Simpanan pokok', ?)");
                $stmtSimp->execute([$anggotaId, $simpPokok, $data['tanggal_daftar'] ?: date('Y-m-d'), $_SESSION['user_id']]);
                
                $successCount++;
            } else if ($type === 'pinjaman') {
                $anggotaId = null;
                if (!empty($data['no_anggota_ref'])) {
                    $s = $db->prepare("SELECT id FROM anggota WHERE no_anggota = ?");
                    $s->execute([$data['no_anggota_ref']]);
                    $anggotaId = $s->fetchColumn();
                } 
                
                if (!$anggotaId && !empty($data['nama_anggota_ref'])) {
                    $s = $db->prepare("SELECT id FROM anggota WHERE nama LIKE ?");
                    $s->execute(['%' . $data['nama_anggota_ref'] . '%']);
                    $anggotaId = $s->fetchColumn();
                }
                
                if (!$anggotaId) { $errorCount++; continue; }

                $no_pinjaman = generateNumber('PNJ', 'pinjaman', 'no_pinjaman');
                $jumlah = (float)preg_replace('/[^0-9.]/', '', $data['jumlah_pinjaman'] ?? 0);
                $jasa = (float)($data['jasa_persen'] ?? 1);
                $lama = (int)($data['lama_angsuran'] ?? 12);
                $totaljasa = $jumlah * ($jasa / 100) * $lama;
                $totalBayar = $jumlah + $totaljasa;
                $angsuranPerBulan = ceil($totalBayar / $lama);
                $tanggalPinjaman = $data['tanggal_pinjaman'] ?: date('Y-m-d');
                $jatuhTempo = date('Y-m-d', strtotime("+$lama months", strtotime($tanggalPinjaman)));
                
                $stmt = $db->prepare("INSERT INTO pinjaman (anggota_id, no_pinjaman, jumlah_pinjaman, jasa_persen, lama_angsuran, jumlah_angsuran, total_bayar, sisa_pinjaman, sisa_pokok, sisa_jasa, tanggal_pinjaman, tanggal_jatuh_tempo, catatan, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $stmt->execute([
                    $anggotaId, $no_pinjaman, $jumlah, $jasa, $lama, $angsuranPerBulan, $totalBayar, $totalBayar, $jumlah, $totaljasa, $tanggalPinjaman, $jatuhTempo, $data['catatan'], $_SESSION['user_id']
                ]);
                $successCount++;
            }
        }
        $db->commit();
        fclose($f);
        @unlink($targetPath);
        // Clean up original Excel file if it was converted
        if ($isExcelFile && file_exists($originalFilePath)) {
            @unlink($originalFilePath);
        }
        
        if ($successCount > 0) {
            setFlash('success', "Smart Import Berhasil! Menambahkan $successCount data. " . ($errorCount > 0 ? "Terdapat $errorCount baris gagal karena data tidak lengkap." : ""));
        } else {
            setFlash('warning', "Tidak ada data yang berhasil diimport. Pastikan file Anda berisi data yang valid. " . ($errorCount > 0 ? "$errorCount baris gagal diproses." : ""));
        }
    } catch (Exception $e) {
        $db->rollBack();
        file_put_contents(__DIR__ . '/import_error.log', date('Y-m-d H:i:s') . " - Import Error: " . $e->getMessage() . "\n", FILE_APPEND);
        setFlash('danger', 'Kesalahan Sistem: ' . $e->getMessage());
    }
    
    header("Location: $type.php");
    exit;
}

setFlash('danger', 'Parameter tidak sesuai.');
header('Location: index.php');
exit;
