<?php
require_once 'config/connection.php';

// Include PhpSpreadsheet library (make sure to install via Composer)
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$message = '';
$messageType = '';
$importResults = [];

// Handle file upload and import
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['excel_file'])) {
    try {
        // Validate file upload
        if ($_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
            throw new Exception('Error uploading file');
        }
        
        $allowedExtensions = ['xlsx', 'xls'];
        $fileExtension = strtolower(pathinfo($_FILES['excel_file']['name'], PATHINFO_EXTENSION));
        
        if (!in_array($fileExtension, $allowedExtensions)) {
            throw new Exception('File harus berformat .xlsx atau .xls');
        }
        
        // Load the Excel file
        $spreadsheet = IOFactory::load($_FILES['excel_file']['tmp_name']);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();
        
        if ($highestRow < 2) {
            throw new Exception('File Excel tidak memiliki data yang valid');
        }
        
        // Start transaction
        $pdo->beginTransaction();
        
        $successCount = 0;
        $skipCount = 0;
        $errorCount = 0;
        $duplicateCount = 0;
        
        // Default password (SHA256 hash of "password123")
        $defaultPassword = hash('sha256', 'password123');
        
        // Process each row (skip header row)
        for ($row = 2; $row <= $highestRow; $row++) {
            try {
                // Read data from Excel columns
                $employee_id = trim($worksheet->getCell('A' . $row)->getValue());
                $full_name = trim($worksheet->getCell('B' . $row)->getValue());
                $branch_name = trim($worksheet->getCell('C' . $row)->getValue());
                $organization = trim($worksheet->getCell('D' . $row)->getValue());
                $jabatan = trim($worksheet->getCell('E' . $row)->getValue());
                
                // Skip empty rows
                if (empty($employee_id) && empty($full_name)) {
                    $skipCount++;
                    continue;
                }
                
                // Validate required fields
                if (empty($employee_id) || empty($full_name) || empty($organization)) {
                    $importResults[] = [
                        'row' => $row,
                        'employee_id' => $employee_id,
                        'full_name' => $full_name,
                        'status' => 'error',
                        'message' => 'Employee ID, Nama Lengkap, dan Organisasi wajib diisi'
                    ];
                    $errorCount++;
                    continue;
                }
                
                // Set default values for optional fields
                if (empty($branch_name)) {
                    $branch_name = 'Main Branch';
                }
                if (empty($jabatan)) {
                    $jabatan = 'Staff';
                }
                
                // Check if employee_id already exists
                $stmt = $pdo->prepare("SELECT id FROM borrowers WHERE employee_id = ?");
                $stmt->execute([$employee_id]);
                
                if ($stmt->fetch()) {
                    $importResults[] = [
                        'row' => $row,
                        'employee_id' => $employee_id,
                        'full_name' => $full_name,
                        'status' => 'duplicate',
                        'message' => 'Employee ID sudah ada dalam database'
                    ];
                    $duplicateCount++;
                    continue;
                }
                
                // Insert new borrower
                $stmt = $pdo->prepare("
                    INSERT INTO borrowers 
                    (employee_id, password, full_name, branch_name, organization, jabatan, is_active, created_at, updated_at) 
                    VALUES (?, ?, ?, ?, ?, ?, 1, NOW(), NOW())
                ");
                
                $stmt->execute([
                    $employee_id,
                    $defaultPassword,
                    $full_name,
                    $branch_name,
                    $organization,
                    $jabatan
                ]);
                
                $importResults[] = [
                    'row' => $row,
                    'employee_id' => $employee_id,
                    'full_name' => $full_name,
                    'status' => 'success',
                    'message' => 'Berhasil diimport'
                ];
                $successCount++;
                
            } catch (Exception $e) {
                $importResults[] = [
                    'row' => $row,
                    'employee_id' => $employee_id ?? '',
                    'full_name' => $full_name ?? '',
                    'status' => 'error',
                    'message' => 'Error: ' . $e->getMessage()
                ];
                $errorCount++;
            }
        }
        
        // Commit transaction if there are successful imports
        if ($successCount > 0) {
            $pdo->commit();
            $message = "Import selesai! Berhasil: {$successCount}, Duplikat: {$duplicateCount}, Error: {$errorCount}, Dilewati: {$skipCount}";
            $messageType = 'success';
        } else {
            $pdo->rollback();
            $message = "Import gagal! Tidak ada data yang berhasil diimport. Error: {$errorCount}, Duplikat: {$duplicateCount}, Dilewati: {$skipCount}";
            $messageType = 'error';
        }
        
    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollback();
        }
        $message = 'Error: ' . $e->getMessage();
        $messageType = 'error';
    }
}

// Get current borrowers count
$stmt = $pdo->prepare("SELECT COUNT(*) as total FROM borrowers WHERE is_active = 1");
$stmt->execute();
$totalBorrowers = $stmt->fetch()['total'];

// Get recent borrowers (last 10)
$stmt = $pdo->prepare("
    SELECT employee_id, full_name, branch_name, organization, jabatan, created_at 
    FROM borrowers 
    WHERE is_active = 1 
    ORDER BY created_at DESC 
    LIMIT 10
");
$stmt->execute();
$recentBorrowers = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Data Borrowers - Asset Management System</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }
        
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            padding: 20px;
        }
        
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        
        h1, h2, h3 {
            color: #333;
            margin-bottom: 20px;
        }
        
        h1 {
            border-bottom: 2px solid #2196F3;
            padding-bottom: 10px;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        
        .user-info {
            background: #e7f3ff;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            border-left: 4px solid #2196F3;
        }
        
        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 20px;
            margin-bottom: 30px;
        }
        
        .stats-card {
            background: linear-gradient(135deg, #2196F3, #1976D2);
            color: white;
            padding: 20px;
            border-radius: 8px;
            text-align: center;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        
        .stats-card h3 {
            font-size: 2em;
            margin-bottom: 10px;
        }
        
        .alert {
            padding: 15px;
            margin-bottom: 20px;
            border-radius: 4px;
            font-weight: bold;
        }
        
        .alert-success {
            background: #d4edda;
            color: #155724;
            border: 1px solid #c3e6cb;
        }
        
        .alert-error {
            background: #f8d7da;
            color: #721c24;
            border: 1px solid #f5c6cb;
        }
        
        .alert-warning {
            background: #fff3cd;
            color: #856404;
            border: 1px solid #ffeaa7;
        }
        
        .section {
            margin-bottom: 40px;
            background: #f8f9fa;
            padding: 20px;
            border-radius: 5px;
        }
        
        .form-group {
            margin-bottom: 20px;
        }
        
        label {
            display: block;
            margin-bottom: 8px;
            font-weight: bold;
            color: #555;
        }
        
        input[type="file"] {
            width: 100%;
            padding: 12px;
            border: 2px dashed #ddd;
            border-radius: 4px;
            background: #fafafa;
            font-size: 14px;
        }
        
        input[type="file"]:hover {
            border-color: #2196F3;
            background: #f0f8ff;
        }
        
        button {
            background: #2196F3;
            color: white;
            padding: 12px 25px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-size: 14px;
            margin-right: 10px;
        }
        
        button:hover {
            background: #1976D2;
        }
        
        .btn-success {
            background: #28a745;
        }
        
        .btn-success:hover {
            background: #218838;
        }
        
        .btn-warning {
            background: #ffc107;
            color: #212529;
        }
        
        .btn-warning:hover {
            background: #e0a800;
        }
        
        .btn-danger {
            background: #dc3545;
        }
        
        .btn-danger:hover {
            background: #c82333;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        
        th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        
        th {
            background-color: #2196F3;
            color: white;
            font-weight: bold;
        }
        
        tr:hover {
            background-color: #f5f5f5;
        }
        
        .status-badge {
            padding: 4px 8px;
            border-radius: 3px;
            font-size: 12px;
            font-weight: bold;
            display: inline-block;
        }
        
        .status-success {
            background: #d4edda;
            color: #155724;
        }
        
        .status-error {
            background: #f8d7da;
            color: #721c24;
        }
        
        .status-duplicate {
            background: #fff3cd;
            color: #856404;
        }
        
        .requirements {
            background: #e3f2fd;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            border-left: 4px solid #2196F3;
        }
        
        .requirements ul {
            margin-left: 20px;
            margin-top: 10px;
        }
        
        .requirements li {
            margin-bottom: 5px;
        }
        
        .download-template {
            background: #e8f5e8;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            border-left: 4px solid #28a745;
        }
        
        .empty-state {
            text-align: center;
            color: #666;
            font-style: italic;
            padding: 40px;
        }
        
        .results-container {
            max-height: 400px;
            overflow-y: auto;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        
        @media (max-width: 768px) {
            .container {
                padding: 15px;
            }
            
            .stats-grid {
                grid-template-columns: 1fr;
            }
            
            table {
                font-size: 12px;
            }
            
            th, td {
                padding: 8px 4px;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>
            Import Data Borrowers
            <div style="font-size: 0.6em; color: #666;">
                <?php echo date('d/m/Y H:i:s'); ?>
            </div>
        </h1>
        
        <div class="user-info">
            <strong>Import Data Borrowers - Asset Management System</strong><br>
            <strong>Waktu:</strong> <?php echo date('d/m/Y H:i:s'); ?> WIB
        </div>
        
        <!-- Statistics -->
        <div class="stats-grid">
            <div class="stats-card">
                <h3><?php echo $totalBorrowers; ?></h3>
                <p>Total Borrowers Aktif</p>
            </div>
            <div class="stats-card" style="background: linear-gradient(135deg, #28a745, #1e7e34);">
                <h3><?php echo count($importResults); ?></h3>
                <p>Data Terproses</p>
            </div>
        </div>
        
        <?php if ($message): ?>
            <div class="alert alert-<?php echo $messageType; ?>">
                <?php echo htmlspecialchars($message); ?>
            </div>
        <?php endif; ?>
        
        <!-- Upload Section -->
        <div class="section">
            <h2>Upload File Excel</h2>
            
            <!-- Download Template -->
            <div class="download-template">
                <h4>📋 Template Excel</h4>
                <p>Download template Excel yang sesuai dengan format yang dibutuhkan:</p>
                <button onclick="downloadTemplate()" class="btn-success">📥 Download Template</button>
            </div>
            
            <!-- Requirements -->
            <div class="requirements">
                <h4>📋 Format File Excel:</h4>
                <ul>
                    <li><strong>Kolom A:</strong> Employee ID (wajib, unik)</li>
                    <li><strong>Kolom B:</strong> Nama Lengkap (wajib)</li>
                    <li><strong>Kolom C:</strong> Nama Cabang (opsional, default: "Main Branch")</li>
                    <li><strong>Kolom D:</strong> Organisasi (wajib)</li>
                    <li><strong>Kolom E:</strong> Jabatan (opsional, default: "Staff")</li>
                </ul>
                <p><strong>Catatan:</strong></p>
                <ul>
                    <li>File harus berformat .xlsx atau .xls</li>
                    <li>Baris pertama adalah header (akan dilewati)</li>
                    <li>Employee ID harus unik, jika duplikat akan diabaikan</li>
                    <li>Password default untuk semua user: "password123"</li>
                </ul>
            </div>
            
            <!-- Upload Form -->
            <form method="POST" enctype="multipart/form-data" onsubmit="return validateUpload()">
                <div class="form-group">
                    <label for="excel_file">Pilih File Excel:</label>
                    <input type="file" name="excel_file" id="excel_file" accept=".xlsx,.xls" required>
                </div>
                
                <button type="submit" class="btn-success">📤 Upload dan Import</button>
                <button type="button" onclick="location.reload()" class="btn-warning">🔄 Reset</button>
            </form>
        </div>
        
        <!-- Import Results -->
        <?php if (!empty($importResults)): ?>
        <div class="section">
            <h2>Hasil Import (<?php echo count($importResults); ?> data)</h2>
            
            <div class="results-container">
                <table>
                    <thead>
                        <tr>
                            <th>Baris</th>
                            <th>Employee ID</th>
                            <th>Nama Lengkap</th>
                            <th>Status</th>
                            <th>Keterangan</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($importResults as $result): ?>
                        <tr>
                            <td><?php echo $result['row']; ?></td>
                            <td><?php echo htmlspecialchars($result['employee_id']); ?></td>
                            <td><?php echo htmlspecialchars($result['full_name']); ?></td>
                            <td>
                                <span class="status-badge status-<?php echo $result['status']; ?>">
                                    <?php 
                                    switch($result['status']) {
                                        case 'success': echo '✅ Berhasil'; break;
                                        case 'error': echo '❌ Error'; break;
                                        case 'duplicate': echo '⚠️ Duplikat'; break;
                                        default: echo ucfirst($result['status']);
                                    }
                                    ?>
                                </span>
                            </td>
                            <td><?php echo htmlspecialchars($result['message']); ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
        <?php endif; ?>
        
        <!-- Recent Borrowers -->
        <div class="section">
            <h2>Borrowers Terbaru (10 Terakhir)</h2>
            
            <?php if (empty($recentBorrowers)): ?>
                <div class="empty-state">
                    Belum ada data borrowers
                </div>
            <?php else: ?>
                <table>
                    <thead>
                        <tr>
                            <th>Employee ID</th>
                            <th>Nama Lengkap</th>
                            <th>Cabang</th>
                            <th>Organisasi</th>
                            <th>Jabatan</th>
                            <th>Tanggal Dibuat</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($recentBorrowers as $borrower): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($borrower['employee_id']); ?></td>
                            <td><?php echo htmlspecialchars($borrower['full_name']); ?></td>
                            <td><?php echo htmlspecialchars($borrower['branch_name']); ?></td>
                            <td><?php echo htmlspecialchars($borrower['organization']); ?></td>
                            <td><?php echo htmlspecialchars($borrower['jabatan']); ?></td>
                            <td><?php echo date('d/m/Y H:i', strtotime($borrower['created_at'])); ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php endif; ?>
        </div>
    </div>

    <script>
        function validateUpload() {
            const fileInput = document.getElementById('excel_file');
            const file = fileInput.files[0];
            
            if (!file) {
                alert('Pilih file Excel terlebih dahulu!');
                return false;
            }
            
            const allowedExtensions = ['.xlsx', '.xls'];
            const fileName = file.name.toLowerCase();
            const isValidExtension = allowedExtensions.some(ext => fileName.endsWith(ext));
            
            if (!isValidExtension) {
                alert('File harus berformat .xlsx atau .xls!');
                return false;
            }
            
            const maxSize = 5 * 1024 * 1024; // 5MB
            if (file.size > maxSize) {
                alert('Ukuran file maksimal 5MB!');
                return false;
            }
            
            // Show loading
            const submitButton = document.querySelector('button[type="submit"]');
            submitButton.textContent = '⏳ Mengimport...';
            submitButton.disabled = true;
            
            return confirm('Yakin ingin mengimport data dari file "' + file.name + '"?\n\nPastikan format file sudah sesuai dengan template.');
        }
        
        function downloadTemplate() {
            // Create a simple CSV template
            const template = `Employee ID,Nama Lengkap,Nama Cabang,Organisasi,Jabatan
BR001,John Doe,Jakarta Branch,PT. Example,Manager
BR002,Jane Smith,Surabaya Branch,PT. Example,Staff
BR003,Ahmad Rahman,Bandung Branch,PT. Example,Technician`;
            
            const blob = new Blob([template], { type: 'text/csv;charset=utf-8;' });
            const link = document.createElement('a');
            
            if (link.download !== undefined) {
                const url = URL.createObjectURL(blob);
                link.setAttribute('href', url);
                link.setAttribute('download', 'template_borrowers.csv');
                link.style.visibility = 'hidden';
                document.body.appendChild(link);
                link.click();
                document.body.removeChild(link);
            } else {
                alert('Browser Anda tidak mendukung download otomatis. Silakan copy data template secara manual.');
            }
        }
        
        // Auto-refresh page after successful import
        <?php if ($messageType == 'success' && !empty($importResults)): ?>
        setTimeout(() => {
            if (confirm('Import berhasil! Refresh halaman untuk melihat data terbaru?')) {
                location.reload();
            }
        }, 3000);
        <?php endif; ?>
        
        // File input enhancement
        document.addEventListener('DOMContentLoaded', function() {
            const fileInput = document.getElementById('excel_file');
            const originalText = fileInput.parentElement.querySelector('label').textContent;
            
            fileInput.addEventListener('change', function() {
                const fileName = this.files[0] ? this.files[0].name : '';
                const label = this.parentElement.querySelector('label');
                
                if (fileName) {
                    label.textContent = originalText + ' - ' + fileName;
                    label.style.color = '#28a745';
                } else {
                    label.textContent = originalText;
                    label.style.color = '#555';
                }
            });
            
            // Drag and drop functionality
            fileInput.addEventListener('dragover', function(e) {
                e.preventDefault();
                this.style.borderColor = '#2196F3';
                this.style.backgroundColor = '#f0f8ff';
            });
            
            fileInput.addEventListener('dragleave', function(e) {
                e.preventDefault();
                this.style.borderColor = '#ddd';
                this.style.backgroundColor = '#fafafa';
            });
            
            fileInput.addEventListener('drop', function(e) {
                e.preventDefault();
                this.style.borderColor = '#ddd';
                this.style.backgroundColor = '#fafafa';
                
                if (e.dataTransfer.files.length > 0) {
                    this.files = e.dataTransfer.files;
                    this.dispatchEvent(new Event('change'));
                }
            });
        });
        
        // Progress indicator for large files
        function showProgress() {
            const progressBar = document.createElement('div');
            progressBar.style.cssText = `
                position: fixed;
                top: 0;
                left: 0;
                width: 100%;
                height: 4px;
                background: #e0e0e0;
                z-index: 9999;
            `;
            
            const progress = document.createElement('div');
            progress.style.cssText = `
                height: 100%;
                background: #2196F3;
                width: 0%;
                transition: width 0.3s ease;
            `;
            
            progressBar.appendChild(progress);
            document.body.appendChild(progressBar);
            
            let width = 0;
            const interval = setInterval(() => {
                width += Math.random() * 15;
                if (width >= 90) {
                    clearInterval(interval);
                }
                progress.style.width = Math.min(width, 90) + '%';
            }, 100);
            
            return () => {
                progress.style.width = '100%';
                setTimeout(() => {
                    if (progressBar.parentNode) {
                        progressBar.parentNode.removeChild(progressBar);
                    }
                }, 200);
            };
        }
        
        // Enhanced form submission
        document.querySelector('form').addEventListener('submit', function(e) {
            const finishProgress = showProgress();
            
            // Finish progress when page reloads or after 10 seconds
            setTimeout(finishProgress, 10000);
        });
    </script>
</body>
</html>