<?php
/**
 * Smart Import Excel untuk Anggota
 * Fitur: Upload langsung, auto-detect kolom, minimal konfigurasi
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/auth.php';
requireAdmin();

$db = getDB();

// ─── DOWNLOAD TEMPLATE (MUST BE BEFORE include header) ───
if (isset($_GET['download_template']) && $_GET['download_template'] === 'anggota') {
    generateAnggotaTemplateExcel();
    exit;
}

function generateAnggotaTemplateExcel() {
    $headers = ['Nama', 'NIK', 'Tempat Lahir', 'Tanggal Lahir', 'Jenis Kelamin', 'Alamat', 'Telepon', 'Email', 'Pekerjaan', 'Tanggal Daftar'];
    $rows = [
        ['Budi Santoso', '1234567890123456', 'Jakarta', '1990-05-15', 'L', 'Jl. Merdeka No. 10', '08123456789', 'budi@example.com', 'Karyawan', '2024-01-15'],
        ['Siti Nurhaliza', '1234567890123457', 'Bandung', '1992-08-20', 'P', 'Jl. Diponegoro No. 5', '08234567890', 'siti@example.com', 'Guru', '2024-01-16']
    ];
    
    // Try to use PhpSpreadsheet if available
    try {
        if (file_exists(__DIR__ . '/vendor/autoload.php')) {
            require_once __DIR__ . '/../vendor/autoload.php';
            if (class_exists('PhpOffice\PhpSpreadsheet\Spreadsheet')) {
                $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
                $sheet = $spreadsheet->getActiveSheet();
                $sheet->setTitle('Anggota');
                
                // Add header
                $sheet->fromArray($headers, null, 'A1');
                
                // Style header
                $headerStyle = $sheet->getStyle('A1:J1');
                $headerStyle->getFont()->setBold(true)->setSize(12)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFFFF'));
                $headerStyle->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF4472C4');
                $headerStyle->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                $sheet->getRowDimension('1')->setRowHeight(25);
                
                // Add sample data
                $sheet->fromArray($rows, null, 'A2');
                
                // Style data cells
                $dataStyle = $sheet->getStyle('A2:J3');
                $dataStyle->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT)->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)->setWrapText(true);
                $sheet->getRowDimension('2')->setRowHeight(20);
                $sheet->getRowDimension('3')->setRowHeight(20);
                
                // Add borders
                $borderStyle = new \PhpOffice\PhpSpreadsheet\Style\Border();
                $borderStyle->setLeft(new \PhpOffice\PhpSpreadsheet\Style\Border\BorderEdge('thin', '000000'));
                $borderStyle->setRight(new \PhpOffice\PhpSpreadsheet\Style\Border\BorderEdge('thin', '000000'));
                $borderStyle->setTop(new \PhpOffice\PhpSpreadsheet\Style\Border\BorderEdge('thin', '000000'));
                $borderStyle->setBottom(new \PhpOffice\PhpSpreadsheet\Style\Border\BorderEdge('thin', '000000'));
                
                $sheet->getStyle('A1:J3')->setBorder($borderStyle);
                
                // Set column widths
                $columnWidths = [20, 18, 15, 15, 15, 25, 15, 25, 15, 15];
                foreach (range('A', 'J') as $index => $col) {
                    $sheet->getColumnDimension($col)->setWidth($columnWidths[$index]);
                }
                
                // Freeze header row
                $sheet->freezePane('A2');
                
                // Output
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment; filename="template_anggota.xlsx"');
                header('Cache-Control: no-cache');
                
                $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
                $writer->save('php://output');
                return;
            }
        }
    } catch (Exception $e) {}
    
    // Generate proper XLSX format
    createProperExcelFile('template_anggota', $headers, $rows);
}

function createProperExcelFile($filename, $headers, $rows) {
    // Create proper XLSX using ZIP archive
    $tmpfile = tempnam(sys_get_temp_dir(), 'xlsx_');
    
    $zip = new ZipArchive();
    if ($zip->open($tmpfile, ZipArchive::CREATE | ZipArchive::OVERWRITE) === false) {
        die('Gagal membuat file Excel');
    }
    
    // [Content_Types].xml
    $contentTypes = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/>
</Types>';
    $zip->addFromString('[Content_Types].xml', $contentTypes);
    
    // _rels/.rels
    $rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/app.xml"/>
</Relationships>';
    $zip->addFromString('_rels/.rels', $rels);
    
    // xl/_rels/workbook.xml.rels
    $workbookRels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>
</Relationships>';
    $zip->addFromString('xl/_rels/workbook.xml.rels', $workbookRels);
    
    // Calculate column widths
    $colWidths = array_fill(0, count($headers), 0);
    foreach ($headers as $i => $header) {
        $colWidths[$i] = max($colWidths[$i], strlen($header) + 2);
    }
    foreach ($rows as $row) {
        foreach ($row as $i => $cell) {
            $colWidths[$i] = max($colWidths[$i], strlen($cell) + 2);
        }
    }
    
    // Build worksheet data
    $sheetData = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<cols>';
    
    // Add column definitions with calculated widths
    foreach ($colWidths as $i => $width) {
        $colLetter = chr(65 + $i);
        $sheetData .= '<col min="' . ($i + 1) . '" max="' . ($i + 1) . '" width="' . ($width * 1.3) . '" customWidth="1"/>';
    }
    
    $sheetData .= '</cols><sheetData>';
    
    // Add header row
    $sheetData .= '<row r="1">';
    foreach ($headers as $index => $header) {
        $col = chr(65 + $index);
        $sheetData .= '<c r="' . $col . '1" t="str"><v>' . htmlspecialchars($header) . '</v></c>';
    }
    $sheetData .= '</row>';
    
    // Add data rows
    $rowNum = 2;
    foreach ($rows as $row) {
        $sheetData .= '<row r="' . $rowNum . '">';
        foreach ($row as $index => $cell) {
            $col = chr(65 + $index);
            $sheetData .= '<c r="' . $col . $rowNum . '" t="str"><v>' . htmlspecialchars($cell) . '</v></c>';
        }
        $sheetData .= '</row>';
        $rowNum++;
    }
    
    $sheetData .= '</sheetData></worksheet>';
    $zip->addFromString('xl/worksheets/sheet1.xml', $sheetData);
    
    // xl/workbook.xml
    $workbook = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>
<workbookPr defaultTheme="1"/>
<bookViews>
<workbookView xWindow="120" yWindow="120" windowWidth="18360" windowHeight="11448" tabRatio="600" activeTab="0"/>
</bookViews>
<sheets>
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
</sheets>
<calcPr calcId="144525" fullCalcOnLoad="false"/>
</workbook>';
    $zip->addFromString('xl/workbook.xml', $workbook);
    
    // xl/styles.xml
    $styles = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1"><font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font></fonts>
<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>
<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
<cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs>
<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
</styleSheet>';
    $zip->addFromString('xl/styles.xml', $styles);
    
    // Custom theme (minimal)
    $theme = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office"><a:themeElements></a:themeElements></a:theme>';
    $zip->addFromString('xl/theme/theme1.xml', $theme);
    
    // docProps/core.xml
    $core = '<?xml version="1.0" encoding="UTF-8"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/"/>'; 
    $zip->addFromString('docProps/core.xml', $core);
    
    // docProps/app.xml
    $app = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<AppVersion>12.0</AppVersion></Properties>';
    $zip->addFromString('docProps/app.xml', $app);
    
    $zip->close();
    
    // Send file
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '.xlsx"');
    header('Content-Length: ' . filesize($tmpfile));
    header('Cache-Control: no-cache');
    
    readfile($tmpfile);
    unlink($tmpfile);
}

// Try to load PHPExcel/PHPOffice libraries
$hasExcelLib = false;
try {
    if (file_exists(__DIR__ . '/vendor/autoload.php')) {
        require_once __DIR__ . '/vendor/autoload.php';
        $hasExcelLib = true;
    }
} catch (Exception $e) {}

$pageTitle = 'Import Anggota';
$pageSubtitle = 'Unggah file Excel untuk menambah data anggota';

// Handle file upload
$uploadSuccess = false;
$uploadError = null;
$importResult = null;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
        $file = $_FILES['file'];
        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        
        // Validate file type
        if (!in_array($ext, ['xlsx', 'xls', 'csv'])) {
            $uploadError = "❌ Hanya file Excel (.xlsx, .xls) atau CSV yang diperbolehkan.";
        } else {
            // Process CSV directly
            if ($ext === 'csv') {
                $importResult = processCSVImport($file['tmp_name']);
            } else {
                // For Excel files
                if ($hasExcelLib) {
                    $importResult = processExcelImportWithLib($file['tmp_name']);
                } else {
                    $importResult = processExcelImportFallback($file['tmp_name']);
                }
            }
        }
    } else {
        $uploadError = "❌ Silakan pilih file terlebih dahulu.";
    }
}

// Smart column mapping with fuzzy matching
function fuzzyMatch($headerLower, $field) {
    $keywords = [
        'nama' => ['nama', 'name', 'namalengkap', 'nama_lengkap'],
        'nik' => ['nik', 'nomor identitas', 'no identitas'],
        'tempat_lahir' => ['tempat lahir', 'tempatlahir', 'tempat_lahir', 'birthplace'],
        'tanggal_lahir' => ['tanggal lahir', 'tgl lahir', 'tanggal_lahir', 'birthdate', 'tglahir'],
        'jenis_kelamin' => ['jenis kelamin', 'jeniskelamin', 'jenis_kelamin', 'gender', 'sex'],
        'alamat' => ['alamat', 'address'],
        'telepon' => ['telepon', 'telpon', 'phone', 'no hp', 'nomor hp', 'mobile'],
        'email' => ['email', 'e-mail'],
        'pekerjaan' => ['pekerjaan', 'profesi', 'occupation', 'job'],
        'tanggal_daftar' => ['tanggal daftar', 'tgl daftar', 'tanggal_daftar', 'register date']
    ];
    
    if (isset($keywords[$field])) {
        foreach ($keywords[$field] as $keyword) {
            if (strpos($headerLower, $keyword) !== false) {
                return true;
            }
        }
    }
    return false;
}

// Process CSV Import
function processCSVImport($filePath) {
    global $db;
    
    $f = fopen($filePath, 'r');
    if (!$f) return ['success' => false, 'message' => '❌ Gagal membaca file'];
    
    // Read header with auto-detect delimiter
    $headers = fgetcsv($f, 0, ',');
    if (!$headers) {
        rewind($f);
        $headers = fgetcsv($f, 0, ';');
    }
    if (!$headers) {
        rewind($f);
        $headers = fgetcsv($f, 0, "\t");
    }
    
    if (!$headers) {
        fclose($f);
        return ['success' => false, 'message' => '❌ File kosong atau format tidak valid'];
    }
    
    // Detect delimiter
    $delimiter = ',';
    if (count($headers) <= 1) {
        rewind($f);
        $test = fgetcsv($f, 0, ';');
        if (count($test) > count($headers)) {
            $delimiter = ';';
            $headers = $test;
        }
    }
    if (count($headers) <= 1) {
        rewind($f);
        $test = fgetcsv($f, 0, "\t");
        if (count($test) > count($headers)) {
            $delimiter = "\t";
            $headers = $test;
        }
    }
    
    // Clean BOM
    if (isset($headers[0])) {
        $headers[0] = preg_replace('/^\xEF\xBB\xBF/', '', $headers[0]);
    }
    
    // Map columns
    $mapping = [];
    $fields = ['nama', 'nik', 'tempat_lahir', 'tanggal_lahir', 'jenis_kelamin', 'alamat', 'telepon', 'email', 'pekerjaan', 'tanggal_daftar'];
    
    foreach ($headers as $idx => $header) {
        $headerLower = strtolower(trim($header));
        foreach ($fields as $field) {
            if (fuzzyMatch($headerLower, $field)) {
                $mapping[$field] = $idx;
                break;
            }
        }
    }
    
    // Check required field
    if (!isset($mapping['nama'])) {
        fclose($f);
        return ['success' => false, 'message' => '❌ Kolom NAMA tidak ditemukan. Silakan rename kolom menjadi "Nama"'];
    }
    
    // Import data
    $successCount = 0;
    $errorData = [];
    
    $db->beginTransaction();
    try {
        rewind($f);
        fgetcsv($f, 0, $delimiter); // Skip header
        
        $simpPokok = getSetting('simpanan_pokok', 100000);
        
        while (($row = fgetcsv($f, 0, $delimiter)) !== false) {
            if (empty(array_filter($row))) continue;
            
            $nama = isset($mapping['nama']) ? trim($row[$mapping['nama']] ?? '') : '';
            if (empty($nama)) {
                $errorData[] = 'Row: Data nama tidak lengkap';
                continue;
            }
            
            $no_anggota = generateNumber('AGT', 'anggota', 'no_anggota');
            
            try {
                $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,
                    $nama,
                    isset($mapping['nik']) ? trim($row[$mapping['nik']] ?? '') : '',
                    isset($mapping['tempat_lahir']) ? trim($row[$mapping['tempat_lahir']] ?? '') : '',
                    isset($mapping['tanggal_lahir']) ? cleanDate(trim($row[$mapping['tanggal_lahir']] ?? '')) : null,
                    isset($mapping['jenis_kelamin']) ? substr(strtoupper(trim($row[$mapping['jenis_kelamin']] ?? 'L')), 0, 1) : 'L',
                    isset($mapping['alamat']) ? trim($row[$mapping['alamat']] ?? '') : '',
                    isset($mapping['telepon']) ? trim($row[$mapping['telepon']] ?? '') : '',
                    isset($mapping['email']) ? trim($row[$mapping['email']] ?? '') : '',
                    isset($mapping['pekerjaan']) ? trim($row[$mapping['pekerjaan']] ?? '') : '',
                    isset($mapping['tanggal_daftar']) ? (cleanDate(trim($row[$mapping['tanggal_daftar']] ?? '')) ?: date('Y-m-d')) : date('Y-m-d')
                ]);
                
                $anggotaId = $db->lastInsertId();
                $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, date('Y-m-d'), $_SESSION['user_id']]);
                
                $successCount++;
            } catch (Exception $e) {
                $errorData[] = 'Baris ' . ($successCount + count($errorData) + 1) . ': ' . $e->getMessage();
            }
        }
        
        $db->commit();
        fclose($f);
        @unlink($filePath);
        
        $message = "✓ Berhasil import $successCount anggota";
        if (!empty($errorData)) {
            $message .= ". ⚠ " . count($errorData) . " baris gagal: " . implode("; ", array_slice($errorData, 0, 3));
        }
        
        return ['success' => true, 'message' => $message, 'count' => $successCount];
    } catch (Exception $e) {
        $db->rollBack();
        fclose($f);
        @unlink($filePath);
        return ['success' => false, 'message' => '❌ Error: ' . $e->getMessage()];
    }
}

// Process Excel with fallback (read XLSX using ZipArchive)
function processExcelImportFallback($filePath) {
    global $db;

    // Check if file is actually a ZIP archive (XLSX is ZIP format)
    $zip = new ZipArchive();
    if ($zip->open($filePath) !== true) {
        $zip->close();
        return ['success' => false, 'message' => '❌ File bukan format Excel (.xlsx) yang valid. Silakan konversi ke CSV terlebih dahulu'];
    }

    // Read [Content_Types].xml to verify it's an XLSX file
    $contentTypes = $zip->getFromName('[Content_Types].xml');
    if ($contentTypes === false || strpos($contentTypes, 'spreadsheetml') === false) {
        $zip->close();
        return ['success' => false, 'message' => '❌ File bukan format Excel (.xlsx) yang valid'];
    }

    // Read the first worksheet
    $worksheet = $zip->getFromName('xl/worksheets/sheet1.xml');
    if ($worksheet === false) {
        $zip->close();
        return ['success' => false, 'message' => '❌ Gagal membaca worksheet'];
    }

    // Parse XML
    try {
        $xml = simplexml_load_string($worksheet);
        if ($xml === false) {
            $zip->close();
            return ['success' => false, 'message' => '❌ Format Excel tidak valid'];
        }

        // Register namespace
        $xml->registerXPathNamespace('s', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');

        // Extract all rows
        $rows = [];
        foreach ($xml->xpath('//s:row') as $row) {
            $rowData = [];
            foreach ($row->c as $cell) {
                // Get column reference (e.g., A1, B2)
                $cellRef = (string) $cell['r'];
                $col = preg_replace('/[0-9]/', '', $cellRef);

                // Get value
                $value = '';
                if (isset($cell->v)) {
                    $value = (string) $cell->v;
                }

                // Handle shared strings
                $cellType = isset($cell['t']) ? (string) $cell['t'] : '';
                if ($cellType === 's') {
                    // Shared string - need to lookup in shared strings table
                    $value = getSharedString($zip, (int) $value);
                }

                // Convert column letter to index (A=0, B=1, etc.)
                $colIndex = convertColToIndex($col);
                $rowData[$colIndex] = $value;
            }

            if (!empty($rowData)) {
                // Normalize array to ensure sequential indices
                $maxIndex = max(array_keys($rowData));
                $normalizedRow = [];
                for ($i = 0; $i <= $maxIndex; $i++) {
                    $normalizedRow[$i] = isset($rowData[$i]) ? $rowData[$i] : '';
                }
                $rows[] = $normalizedRow;
            }
        }

        $zip->close();

        if (empty($rows)) {
            return ['success' => false, 'message' => '❌ File Excel kosong'];
        }

        // First row is header
        $headers = array_shift($rows);

        // Clean BOM from first header
        if (isset($headers[0])) {
            $headers[0] = preg_replace('/^\xEF\xBB\xBF/', '', $headers[0]);
        }

        // Map columns using fuzzy matching
        $mapping = [];
        $fields = ['nama', 'nik', 'tempat_lahir', 'tanggal_lahir', 'jenis_kelamin', 'alamat', 'telepon', 'email', 'pekerjaan', 'tanggal_daftar'];

        foreach ($headers as $idx => $header) {
            $headerLower = strtolower(trim($header));
            foreach ($fields as $field) {
                if (fuzzyMatch($headerLower, $field)) {
                    $mapping[$field] = $idx;
                    break;
                }
            }
        }

        // Check required field
        if (!isset($mapping['nama'])) {
            return ['success' => false, 'message' => '❌ Kolom NAMA tidak ditemukan. Silakan rename kolom menjadi "Nama"'];
        }

        // Import data
        $successCount = 0;
        $errorData = [];

        $db->beginTransaction();
        try {
            $simpPokok = getSetting('simpanan_pokok', 100000);

            foreach ($rows as $row) {
                if (empty(array_filter($row))) continue;

                $nama = isset($mapping['nama']) ? trim($row[$mapping['nama']] ?? '') : '';
                if (empty($nama)) {
                    $errorData[] = 'Row: Data nama tidak lengkap';
                    continue;
                }

                $no_anggota = generateNumber('AGT', 'anggota', 'no_anggota');

                try {
                    $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,
                        $nama,
                        isset($mapping['nik']) ? trim($row[$mapping['nik']] ?? '') : '',
                        isset($mapping['tempat_lahir']) ? trim($row[$mapping['tempat_lahir']] ?? '') : '',
                        isset($mapping['tanggal_lahir']) ? cleanDate(trim($row[$mapping['tanggal_lahir']] ?? '')) : null,
                        isset($mapping['jenis_kelamin']) ? substr(strtoupper(trim($row[$mapping['jenis_kelamin']] ?? 'L')), 0, 1) : 'L',
                        isset($mapping['alamat']) ? trim($row[$mapping['alamat']] ?? '') : '',
                        isset($mapping['telepon']) ? trim($row[$mapping['telepon']] ?? '') : '',
                        isset($mapping['email']) ? trim($row[$mapping['email']] ?? '') : '',
                        isset($mapping['pekerjaan']) ? trim($row[$mapping['pekerjaan']] ?? '') : '',
                        isset($mapping['tanggal_daftar']) ? (cleanDate(trim($row[$mapping['tanggal_daftar']] ?? '')) ?: date('Y-m-d')) : date('Y-m-d')
                    ]);

                    $anggotaId = $db->lastInsertId();
                    $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, date('Y-m-d'), $_SESSION['user_id']]);

                    $successCount++;
                } catch (Exception $e) {
                    $errorData[] = 'Baris ' . ($successCount + count($errorData) + 1) . ': ' . $e->getMessage();
                }
            }

            $db->commit();
            @unlink($filePath);

            $message = "✓ Berhasil import $successCount anggota";
            if (!empty($errorData)) {
                $message .= ". ⚠ " . count($errorData) . " baris gagal: " . implode("; ", array_slice($errorData, 0, 3));
            }

            return ['success' => true, 'message' => $message, 'count' => $successCount];
        } catch (Exception $e) {
            $db->rollBack();
            @unlink($filePath);
            return ['success' => false, 'message' => '❌ Error: ' . $e->getMessage()];
        }
    } catch (Exception $e) {
        $zip->close();
        return ['success' => false, 'message' => '❌ Error membaca file: ' . $e->getMessage()];
    }
}

// Helper: Convert column letter to index (A=0, B=1, AA=26, etc.)
function convertColToIndex($col) {
    $col = strtoupper($col);
    $len = strlen($col);
    $index = 0;
    for ($i = 0; $i < $len; $i++) {
        $index = $index * 26 + (ord($col[$i]) - ord('A') + 1);
    }
    return $index - 1;
}

// Helper: Get shared string from shared strings table
function getSharedString($zip, $index) {
    $sharedStrings = $zip->getFromName('xl/sharedStrings.xml');
    if ($sharedStrings === false) {
        return '';
    }

    try {
        $xml = simplexml_load_string($sharedStrings);
        if ($xml === false) {
            return '';
        }

        $ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
        $xml->registerXPathNamespace('s', $ns);
        
        // Get all <si> elements
        $siElements = $xml->children($ns)->si;
        if (!$siElements || count($siElements) <= $index) {
            return '';
        }

        // Get the specific si element by index
        $si = $siElements[$index];
        
        // Get text - could be in t or r elements
        $tElements = $si->children($ns)->t;
        if (!empty($tElements)) {
            return (string) $tElements[0];
        }

        // Check for rich text (r elements)
        $rElements = $si->children($ns)->r;
        if (!empty($rElements)) {
            $text = '';
            foreach ($rElements as $r) {
                $t = $r->children($ns)->t;
                if (!empty($t)) {
                    $text .= (string) $t[0];
                }
            }
            return $text;
        }

        return '';
    } catch (Exception $e) {
        return '';
    }
}

// Process Excel with library
function processExcelImportWithLib($filePath) {
    // Use PhpSpreadsheet to read XLSX
    global $db;

    try {
        require_once __DIR__ . '/../vendor/autoload.php';

        $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $spreadsheet = $reader->load($filePath);
        $worksheet = $spreadsheet->getActiveSheet();

        // Get all rows
        $rows = [];
        foreach ($worksheet->getRowIterator() as $row) {
            $rowData = [];
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);

            foreach ($cellIterator as $cell) {
                $value = $cell->getCalculatedValue();
                $rowData[] = $value;
            }

            if (!empty($rowData)) {
                $rows[] = $rowData;
            }
        }

        if (empty($rows)) {
            return ['success' => false, 'message' => '❌ File Excel kosong'];
        }

        // First row is header
        $headers = array_shift($rows);

        // Clean BOM from first header
        if (isset($headers[0])) {
            $headers[0] = preg_replace('/^\xEF\xBB\xBF/', '', $headers[0]);
        }

        // Map columns using fuzzy matching
        $mapping = [];
        $fields = ['nama', 'nik', 'tempat_lahir', 'tanggal_lahir', 'jenis_kelamin', 'alamat', 'telepon', 'email', 'pekerjaan', 'tanggal_daftar'];

        foreach ($headers as $idx => $header) {
            $headerLower = strtolower(trim($header));
            foreach ($fields as $field) {
                if (fuzzyMatch($headerLower, $field)) {
                    $mapping[$field] = $idx;
                    break;
                }
            }
        }

        // Check required field
        if (!isset($mapping['nama'])) {
            return ['success' => false, 'message' => '❌ Kolom NAMA tidak ditemukan. Silakan rename kolom menjadi "Nama"'];
        }

        // Import data
        $successCount = 0;
        $errorData = [];

        $db->beginTransaction();
        try {
            $simpPokok = getSetting('simpanan_pokok', 100000);

            foreach ($rows as $row) {
                if (empty(array_filter($row))) continue;

                $nama = isset($mapping['nama']) ? trim($row[$mapping['nama']] ?? '') : '';
                if (empty($nama)) {
                    $errorData[] = 'Row: Data nama tidak lengkap';
                    continue;
                }

                $no_anggota = generateNumber('AGT', 'anggota', 'no_anggota');

                try {
                    $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,
                        $nama,
                        isset($mapping['nik']) ? trim($row[$mapping['nik']] ?? '') : '',
                        isset($mapping['tempat_lahir']) ? trim($row[$mapping['tempat_lahir']] ?? '') : '',
                        isset($mapping['tanggal_lahir']) ? cleanDate(trim($row[$mapping['tanggal_lahir']] ?? '')) : null,
                        isset($mapping['jenis_kelamin']) ? substr(strtoupper(trim($row[$mapping['jenis_kelamin']] ?? 'L')), 0, 1) : 'L',
                        isset($mapping['alamat']) ? trim($row[$mapping['alamat']] ?? '') : '',
                        isset($mapping['telepon']) ? trim($row[$mapping['telepon']] ?? '') : '',
                        isset($mapping['email']) ? trim($row[$mapping['email']] ?? '') : '',
                        isset($mapping['pekerjaan']) ? trim($row[$mapping['pekerjaan']] ?? '') : '',
                        isset($mapping['tanggal_daftar']) ? (cleanDate(trim($row[$mapping['tanggal_daftar']] ?? '')) ?: date('Y-m-d')) : date('Y-m-d')
                    ]);

                    $anggotaId = $db->lastInsertId();
                    $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, date('Y-m-d'), $_SESSION['user_id']]);

                    $successCount++;
                } catch (Exception $e) {
                    $errorData[] = 'Baris ' . ($successCount + count($errorData) + 1) . ': ' . $e->getMessage();
                }
            }

            $db->commit();
            @unlink($filePath);

            $message = "✓ Berhasil import $successCount anggota";
            if (!empty($errorData)) {
                $message .= ". ⚠ " . count($errorData) . " baris gagal: " . implode("; ", array_slice($errorData, 0, 3));
            }

            return ['success' => true, 'message' => $message, 'count' => $successCount];
        } catch (Exception $e) {
            $db->rollBack();
            @unlink($filePath);
            return ['success' => false, 'message' => '❌ Error: ' . $e->getMessage()];
        }
    } catch (Exception $e) {
        // If PhpSpreadsheet fails, fallback to ZipArchive method
        return processExcelImportFallback($filePath);
    }
}

// Clean date format
function cleanDate($date) {
    if (empty($date)) return null;
    
    // Try to parse various date formats
    $date = trim($date);
    
    // If already in YYYY-MM-DD format
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        return $date;
    }
    
    // Try other formats
    try {
        $dt = new DateTime($date);
        return $dt->format('Y-m-d');
    } catch (Exception $e) {
        return null;
    }
}

include __DIR__ . '/../includes/header.php';
?>

<style>
    .import-container {
        max-width: 700px;
        margin: 0 auto;
    }
    
    .import-card {
        border: none;
        box-shadow: 0 2px 8px rgba(0,0,0,0.1);
        border-radius: 12px;
        overflow: hidden;
    }
    
    .import-header {
        background: linear-gradient(135deg, var(--primary-500), var(--primary-600));
        color: white;
        padding: 24px;
        display: flex;
        align-items: center;
        gap: 16px;
    }
    
    .import-header i {
        font-size: 2rem;
        opacity: 0.9;
    }
    
    .import-header h2 {
        margin: 0;
        font-size: 1.3rem;
        font-weight: 600;
    }
    
    .upload-zone {
        padding: 40px;
        text-align: center;
        border: 2px dashed var(--primary-300);
        border-radius: 12px;
        background: var(--primary-50);
        cursor: pointer;
        transition: all 0.3s;
        margin: 20px 0;
    }
    
    .upload-zone:hover {
        border-color: var(--primary-500);
        background: var(--primary-100);
    }
    
    .upload-zone i {
        font-size: 3rem;
        color: var(--primary-500);
        margin-bottom: 12px;
    }
    
    .upload-zone p {
        margin: 0;
        color: var(--gray-700);
    }
    
    .upload-zone .text-small {
        font-size: 0.85rem;
        color: var(--gray-600);
        margin-top: 8px;
    }
    
    .file-input {
        display: none;
    }
    
    .alert-box {
        padding: 16px;
        border-radius: 8px;
        margin-bottom: 20px;
        display: flex;
        gap: 12px;
        align-items: flex-start;
    }
    
    .alert-success {
        background: #d1fae5;
        border-left: 4px solid #10b981;
        color: #065f46;
    }
    
    .alert-danger {
        background: #fee2e2;
        border-left: 4px solid #ef4444;
        color: #7f1d1d;
    }
    
    .template-link {
        display: inline-flex;
        align-items: center;
        gap: 8px;
        color: var(--primary-600);
        text-decoration: none;
        font-weight: 500;
    }
    
    .template-link:hover {
        text-decoration: underline;
    }
</style>

<div class="import-container">
    <div class="import-card card">
        <!-- Header -->
        <div class="import-header">
            <i class="fas fa-file-import"></i>
            <div>
                <h2>Import Anggota</h2>
                <p style="margin: 0; font-size: 0.9rem; opacity: 0.9;">Upload file Excel atau CSV untuk menambah data anggota</p>
            </div>
        </div>
        
        <!-- Content -->
        <div class="card-body" style="padding: 30px;">
            
            <!-- Status Messages -->
            <?php if ($uploadError): ?>
                <div class="alert-box alert-danger">
                    <i class="fas fa-exclamation-circle" style="flex-shrink: 0; margin-top: 2px;"></i>
                    <div><?= $uploadError ?></div>
                </div>
            <?php endif; ?>
            
            <?php if ($importResult && $importResult['success']): ?>
                <div class="alert-box alert-success">
                    <i class="fas fa-check-circle" style="flex-shrink: 0; margin-top: 2px;"></i>
                    <div><?= $importResult['message'] ?></div>
                </div>
            <?php elseif ($importResult && !$importResult['success']): ?>
                <div class="alert-box alert-danger">
                    <i class="fas fa-times-circle" style="flex-shrink: 0; margin-top: 2px;"></i>
                    <div><?= $importResult['message'] ?></div>
                </div>
            <?php endif; ?>
            
            <!-- Upload Form -->
            <form method="POST" enctype="multipart/form-data" id="importForm">
                <div class="upload-zone" id="uploadZone">
                    <i class="fas fa-cloud-upload"></i>
                    <p><strong>Klik atau drag file ke sini</strong></p>
                    <p class="text-small">File Excel (.xlsx, .xls) atau CSV</p>
                </div>
                <input type="file" id="fileInput" name="file" class="file-input" accept=".xlsx,.xls,.csv" required>
                
                <div id="fileInfo" style="display: none; padding: 12px; background: var(--gray-50); border-radius: 8px; margin-bottom: 20px;">
                    <div style="font-weight: 600;">File dipilih: <span id="fileName"></span></div>
                    <div style="font-size: 0.85rem; color: var(--gray-600);">Ukuran: <span id="fileSize"></span></div>
                </div>
                
                <button type="submit" class="btn btn-primary" style="width: 100%; padding: 12px;">
                    <i class="fas fa-arrow-right"></i> Import Data
                </button>
            </form>
            
            <!-- Tips -->
            <div style="margin-top: 20px; padding: 16px; background: #fffbeb; border-left: 4px solid #f59e0b; border-radius: 6px;">
                <p style="margin: 0 0 10px 0; font-weight: 600; color: #92400e;">💡 Tips Menggunakan Import:</p>
                <ul style="margin: 0; padding-left: 20px; color: #78350f; font-size: 0.9rem;">
                    <li>File harus memiliki kolom <strong>"Nama"</strong></li>
                    <li>Kolom lain bersifat opsional (NIK, Telepon, Alamat, dll)</li>
                    <li>Gunakan format tanggal: <strong>YYYY-MM-DD</strong> (contoh: 2024-12-31)</li>
                    <li style="margin-top: 8px; padding-top: 8px; border-top: 1px solid #fcd34d;">
                        <a href="?download_template=anggota" class="template-link">
                            <i class="fas fa-download"></i> Download Template Excel
                        </a>
                    </li>
                </ul>
            </div>
        </div>
        
        <!-- Footer -->
        <div style="padding: 16px 24px; background: var(--gray-50); border-top: 1px solid var(--gray-200);">
            <a href="anggota.php" class="btn btn-outline">← Kembali ke Anggota</a>
        </div>
    </div>
</div>

<script>
const uploadZone = document.getElementById('uploadZone');
const fileInput = document.getElementById('fileInput');
const fileInfo = document.getElementById('fileInfo');

uploadZone.addEventListener('click', () => fileInput.click());

fileInput.addEventListener('change', (e) => {
    const file = e.target.files[0];
    if (file) {
        document.getElementById('fileName').textContent = file.name;
        document.getElementById('fileSize').textContent = (file.size / 1024 / 1024).toFixed(2) + ' MB';
        fileInfo.style.display = 'block';
    }
});

// Drag and drop
uploadZone.addEventListener('dragover', (e) => {
    e.preventDefault();
    uploadZone.style.borderColor = 'var(--primary-500)';
    uploadZone.style.background = 'var(--primary-100)';
});

uploadZone.addEventListener('dragleave', () => {
    uploadZone.style.borderColor = 'var(--primary-300)';
    uploadZone.style.background = 'var(--primary-50)';
});

uploadZone.addEventListener('drop', (e) => {
    e.preventDefault();
    uploadZone.style.borderColor = 'var(--primary-300)';
    uploadZone.style.background = 'var(--primary-50)';
    fileInput.files = e.dataTransfer.files;
    const event = new Event('change', { bubbles: true });
    fileInput.dispatchEvent(event);
});
</script>

<?php include __DIR__ . '/../includes/footer.php'; ?>
