<?php
session_start();

// Validasi session
if (!isset($_SESSION['login']) || $_SESSION['login'] !== true) {
    header("Location: ../../index.html?error=session");
    exit;
}

$userRole = strtoupper(trim($_SESSION['role'] ?? ''));
if ($userRole !== 'M' && $userRole !== 'A') {
    header("Location: ../../index.html?error=forbidden");
    exit;
}

require_once __DIR__ . '/../../DataBase/koneksi.php';

$errors = [];
$success = false;
$importResults = [
    'total' => 0,
    'success' => 0,
    'failed' => 0,
    'details' => []
];

// Handle file upload
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    $file = $_FILES['excel_file'];
    
    // Validate file
    if ($file['error'] !== UPLOAD_ERR_OK) {
        $errors[] = "Error uploading file";
    } else {
        $allowed = ['xls', 'xlsx', 'csv'];
        $filename = $file['name'];
        $ext = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
        
        if (!in_array($ext, $allowed)) {
            $errors[] = "File harus berformat Excel (.xls, .xlsx) atau CSV";
        } else {
            // Process the file
            require_once __DIR__ . '/../../vendor/autoload.php'; // Pastikan PhpSpreadsheet terinstall
            
            try {
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
                $worksheet = $spreadsheet->getActiveSheet();
                $rows = $worksheet->toArray();
                
                // Skip header row
                $header = array_shift($rows);
                
                $importResults['total'] = count($rows);
                
                foreach ($rows as $index => $row) {
                    $rowNumber = $index + 2; // +2 karena index mulai dari 0 dan ada header
                    
                    // Skip empty rows
                    if (empty(array_filter($row))) {
                        continue;
                    }
                    
                    // Map columns (sesuaikan dengan urutan kolom di Excel)
                    $nip = mysqli_real_escape_string($conn, trim($row[0] ?? ''));
                    $nama = mysqli_real_escape_string($conn, trim($row[1] ?? ''));
                    $jenis_kelamin = mysqli_real_escape_string($conn, trim($row[2] ?? ''));
                    $tempat_lahir = mysqli_real_escape_string($conn, trim($row[3] ?? ''));
                    $tanggal_lahir = mysqli_real_escape_string($conn, trim($row[4] ?? ''));
                    $alamat = mysqli_real_escape_string($conn, trim($row[5] ?? ''));
                    $no_hp = mysqli_real_escape_string($conn, trim($row[6] ?? ''));
                    $email = mysqli_real_escape_string($conn, trim($row[7] ?? ''));
                    $jabatan = mysqli_real_escape_string($conn, trim($row[8] ?? ''));
                    $kantor = mysqli_real_escape_string($conn, trim($row[9] ?? ''));
                    $tanggal_masuk = mysqli_real_escape_string($conn, trim($row[10] ?? ''));
                    $status = mysqli_real_escape_string($conn, trim($row[11] ?? 'Aktif'));
                    $pendidikan = mysqli_real_escape_string($conn, trim($row[12] ?? ''));
                    $jurusan = mysqli_real_escape_string($conn, trim($row[13] ?? ''));
                    
                    // Validate required field
                    if (empty($nama)) {
                        $importResults['failed']++;
                        $importResults['details'][] = "Baris $rowNumber: Nama tidak boleh kosong";
                        continue;
                    }
                    
                    // Check duplicate NIP
                    if (!empty($nip)) {
                        $checkNip = mysqli_query($conn, "SELECT id FROM sdm WHERE nip = '$nip'");
                        if (mysqli_num_rows($checkNip) > 0) {
                            $importResults['failed']++;
                            $importResults['details'][] = "Baris $rowNumber: NIP $nip sudah terdaftar";
                            continue;
                        }
                    }
                    
                    // Check duplicate email
                    if (!empty($email)) {
                        $checkEmail = mysqli_query($conn, "SELECT id FROM sdm WHERE email = '$email'");
                        if (mysqli_num_rows($checkEmail) > 0) {
                            $importResults['failed']++;
                            $importResults['details'][] = "Baris $rowNumber: Email $email sudah terdaftar";
                            continue;
                        }
                    }
                    
                    // Convert date format if needed
                    if (!empty($tanggal_lahir)) {
                        try {
                            $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($tanggal_lahir);
                            $tanggal_lahir = $date->format('Y-m-d');
                        } catch (Exception $e) {
                            // If not Excel date, try to parse as string
                            $tanggal_lahir = date('Y-m-d', strtotime($tanggal_lahir));
                        }
                    }
                    
                    if (!empty($tanggal_masuk)) {
                        try {
                            $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($tanggal_masuk);
                            $tanggal_masuk = $date->format('Y-m-d');
                        } catch (Exception $e) {
                            $tanggal_masuk = date('Y-m-d', strtotime($tanggal_masuk));
                        }
                    }
                    
                    // Insert data
                    $queryInsert = "INSERT INTO sdm (
                        nip, nama, jenis_kelamin, tempat_lahir, tanggal_lahir, 
                        alamat, no_hp, email, jabatan, kantor, 
                        tanggal_masuk, status, pendidikan, jurusan, created_at
                    ) VALUES (
                        '$nip', '$nama', '$jenis_kelamin', '$tempat_lahir', " . (empty($tanggal_lahir) ? "NULL" : "'$tanggal_lahir'") . ",
                        '$alamat', '$no_hp', '$email', '$jabatan', '$kantor',
                        " . (empty($tanggal_masuk) ? "NULL" : "'$tanggal_masuk'") . ", '$status', '$pendidikan', '$jurusan', NOW()
                    )";
                    
                    if (mysqli_query($conn, $queryInsert)) {
                        $importResults['success']++;
                    } else {
                        $importResults['failed']++;
                        $importResults['details'][] = "Baris $rowNumber ($nama): " . mysqli_error($conn);
                    }
                }
                
                // Log activity
                $username = $_SESSION['username'] ?? 'Unknown';
                $logQuery = "INSERT INTO activity_log (user, action, description, timestamp) 
                             VALUES ('$username', 'IMPORT', 'Import {$importResults['success']} data SDM dari Excel', NOW())";
                @mysqli_query($conn, $logQuery);
                
                $success = true;
                
            } catch (Exception $e) {
                $errors[] = "Error memproses file: " . $e->getMessage();
            }
        }
    }
}

mysqli_close($conn);
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import Data SDM - ACF EDU HUB</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700;800&display=swap" rel="stylesheet">
    <style>
        :root {
            --primary-teal: #7AB2B2;
            --secondary-teal: #4D869C;
            --light-mint: #CDE8E5;
            --lightest-mint: #EEF7FF;
            --bg-light: #f8fafc;
            --text-dark: #1e293b;
            --text-medium: #475569;
            --text-light: #64748b;
            --border-color: #e2e8f0;
            --purple: #6366f1;
        }

        * { margin: 0; padding: 0; box-sizing: border-box; }

        body {
            font-family: 'Inter', sans-serif;
            background: var(--bg-light);
            color: var(--text-dark);
            padding: 24px;
            line-height: 1.6;
        }

        .container { max-width: 900px; margin: 0 auto; }

        .page-header {
            margin-bottom: 24px;
        }

        .header-title h1 {
            font-size: 28px;
            font-weight: 800;
            color: var(--text-dark);
            margin-bottom: 8px;
            display: flex;
            align-items: center;
            gap: 12px;
        }

        .breadcrumb {
            display: flex;
            align-items: center;
            gap: 8px;
            font-size: 14px;
            color: var(--text-light);
            margin-top: 8px;
        }

        .breadcrumb a {
            color: var(--primary-teal);
            text-decoration: none;
            font-weight: 600;
        }

        .breadcrumb a:hover {
            text-decoration: underline;
        }

        .main-card {
            background: white;
            border-radius: 16px;
            border: 1px solid var(--border-color);
            overflow: hidden;
            margin-bottom: 24px;
        }

        .card-header {
            padding: 24px;
            border-bottom: 1px solid var(--border-color);
            background: var(--bg-light);
        }

        .card-title {
            font-size: 18px;
            font-weight: 700;
            color: var(--text-dark);
            display: flex;
            align-items: center;
            gap: 10px;
        }

        .card-body {
            padding: 32px;
        }

        .upload-area {
            border: 2px dashed var(--border-color);
            border-radius: 12px;
            padding: 48px 32px;
            text-align: center;
            background: var(--bg-light);
            transition: all 0.3s ease;
            cursor: pointer;
        }

        .upload-area:hover {
            border-color: var(--purple);
            background: var(--lightest-mint);
        }

        .upload-area.dragover {
            border-color: var(--purple);
            background: var(--lightest-mint);
        }

        .upload-icon {
            font-size: 64px;
            color: var(--primary-teal);
            margin-bottom: 16px;
        }

        .upload-text h3 {
            font-size: 18px;
            font-weight: 700;
            color: var(--text-dark);
            margin-bottom: 8px;
        }

        .upload-text p {
            font-size: 14px;
            color: var(--text-light);
        }

        .file-input {
            display: none;
        }

        .selected-file {
            margin-top: 16px;
            padding: 12px 16px;
            background: white;
            border: 1px solid var(--border-color);
            border-radius: 8px;
            display: flex;
            align-items: center;
            gap: 12px;
        }

        .selected-file i {
            font-size: 24px;
            color: var(--green);
        }

        .file-info {
            flex: 1;
        }

        .file-name {
            font-weight: 600;
            color: var(--text-dark);
        }

        .file-size {
            font-size: 12px;
            color: var(--text-light);
        }

        .btn-remove {
            background: none;
            border: none;
            color: #dc2626;
            cursor: pointer;
            padding: 8px;
            border-radius: 6px;
            transition: all 0.2s ease;
        }

        .btn-remove:hover {
            background: #fee2e2;
        }

        .alert {
            padding: 16px 20px;
            border-radius: 10px;
            margin-bottom: 20px;
            display: flex;
            align-items: flex-start;
            gap: 12px;
        }

        .alert-success {
            background: #d1fae5;
            border: 1px solid #6ee7b7;
        }

        .alert-error {
            background: #fee2e2;
            border: 1px solid #fecaca;
        }

        .alert-info {
            background: #dbeafe;
            border: 1px solid #93c5fd;
        }

        .alert i {
            font-size: 20px;
            margin-top: 2px;
        }

        .alert-success i { color: #065f46; }
        .alert-error i { color: #991b1b; }
        .alert-info i { color: #1e40af; }

        .alert-content {
            flex: 1;
        }

        .alert-content strong {
            display: block;
            margin-bottom: 4px;
            font-size: 14px;
        }

        .alert-success strong { color: #065f46; }
        .alert-error strong { color: #991b1b; }
        .alert-info strong { color: #1e40af; }

        .result-stats {
            display: grid;
            grid-template-columns: repeat(3, 1fr);
            gap: 16px;
            margin: 20px 0;
        }

        .stat-box {
            padding: 16px;
            border-radius: 10px;
            text-align: center;
        }

        .stat-box.total {
            background: #dbeafe;
        }

        .stat-box.success {
            background: #d1fae5;
        }

        .stat-box.failed {
            background: #fee2e2;
        }

        .stat-number {
            font-size: 32px;
            font-weight: 800;
            margin-bottom: 4px;
        }

        .stat-box.total .stat-number { color: #1e40af; }
        .stat-box.success .stat-number { color: #065f46; }
        .stat-box.failed .stat-number { color: #991b1b; }

        .stat-label {
            font-size: 12px;
            font-weight: 600;
            text-transform: uppercase;
        }

        .error-list {
            margin-top: 12px;
            padding-left: 20px;
            max-height: 200px;
            overflow-y: auto;
        }

        .error-list li {
            font-size: 13px;
            margin-bottom: 6px;
            color: #991b1b;
        }

        .btn {
            padding: 12px 24px;
            border-radius: 10px;
            font-weight: 700;
            font-size: 14px;
            display: inline-flex;
            align-items: center;
            gap: 8px;
            cursor: pointer;
            border: none;
            transition: all 0.3s ease;
            text-decoration: none;
        }

        .btn-primary {
            background: var(--purple);
            color: white;
        }

        .btn-primary:hover {
            background: #4f46e5;
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(99, 102, 241, 0.4);
        }

        .btn-outline {
            background: white;
            color: var(--text-dark);
            border: 2px solid var(--border-color);
        }

        .btn-outline:hover {
            background: var(--bg-light);
            border-color: var(--primary-teal);
        }

        .btn-success {
            background: #16a34a;
            color: white;
        }

        .btn-success:hover {
            background: #15803d;
        }

        .form-actions {
            display: flex;
            gap: 12px;
            justify-content: space-between;
            margin-top: 24px;
        }

        .info-box {
            background: var(--lightest-mint);
            border: 1px solid var(--light-mint);
            border-radius: 10px;
            padding: 16px;
            margin-bottom: 24px;
        }

        .info-box h4 {
            font-size: 14px;
            font-weight: 700;
            color: var(--text-dark);
            margin-bottom: 12px;
            display: flex;
            align-items: center;
            gap: 8px;
        }

        .info-box ul {
            padding-left: 20px;
            font-size: 13px;
            color: var(--text-medium);
        }

        .info-box li {
            margin-bottom: 6px;
        }

        @media (max-width: 768px) {
            body { padding: 12px; }
            .card-body { padding: 20px; }
            .result-stats { grid-template-columns: 1fr; }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="page-header">
            <h1>
                <i class="fas fa-file-import" style="color: var(--purple);"></i>
                Import Data SDM
            </h1>
            <div class="breadcrumb">
                <a href="javascript:history.back()"><i class="fas fa-arrow-left"></i> Kembali</a>
                <span>/</span>
                <span>Import</span>
            </div>
        </div>

        <?php if ($success): ?>
        <div class="alert alert-success">
            <i class="fas fa-check-circle"></i>
            <div class="alert-content">
                <strong>Import Selesai!</strong>
                <div class="result-stats">
                    <div class="stat-box total">
                        <div class="stat-number"><?= $importResults['total'] ?></div>
                        <div class="stat-label">Total Baris</div>
                    </div>
                    <div class="stat-box success">
                        <div class="stat-number"><?= $importResults['success'] ?></div>
                        <div class="stat-label">Berhasil</div>
                    </div>
                    <div class="stat-box failed">
                        <div class="stat-number"><?= $importResults['failed'] ?></div>
                        <div class="stat-label">Gagal</div>
                    </div>
                </div>
                <?php if (!empty($importResults['details'])): ?>
                <strong style="margin-top: 12px;">Detail Error:</strong>
                <ul class="error-list">
                    <?php foreach ($importResults['details'] as $detail): ?>
                    <li><?= htmlspecialchars($detail) ?></li>
                    <?php endforeach; ?>
                </ul>
                <?php endif; ?>
            </div>
        </div>
        <?php endif; ?>

        <?php if (!empty($errors)): ?>
        <div class="alert alert-error">
            <i class="fas fa-exclamation-circle"></i>
            <div class="alert-content">
                <strong>Terjadi Kesalahan:</strong>
                <ul>
                    <?php foreach ($errors as $error): ?>
                    <li><?= htmlspecialchars($error) ?></li>
                    <?php endforeach; ?>
                </ul>
            </div>
        </div>
        <?php endif; ?>

        <div class="main-card">
            <div class="card-header">
                <div class="card-title">
                    <i class="fas fa-upload"></i>
                    Upload File Excel
                </div>
            </div>
            <div class="card-body">
                <div class="info-box">
                    <h4><i class="fas fa-info-circle"></i> Format File Excel:</h4>
                    <ul>
                        <li><strong>Kolom 1:</strong> NIP</li>
                        <li><strong>Kolom 2:</strong> Nama Lengkap (Required)</li>
                        <li><strong>Kolom 3:</strong> Jenis Kelamin (Laki-laki/Perempuan)</li>
                        <li><strong>Kolom 4:</strong> Tempat Lahir</li>
                        <li><strong>Kolom 5:</strong> Tanggal Lahir (YYYY-MM-DD)</li>
                        <li><strong>Kolom 6:</strong> Alamat</li>
                        <li><strong>Kolom 7:</strong> No HP</li>
                        <li><strong>Kolom 8:</strong> Email</li>
                        <li><strong>Kolom 9:</strong> Jabatan</li>
                        <li><strong>Kolom 10:</strong> Kantor</li>
                        <li><strong>Kolom 11:</strong> Tanggal Masuk (YYYY-MM-DD)</li>
                        <li><strong>Kolom 12:</strong> Status (Aktif/Non-Aktif)</li>
                        <li><strong>Kolom 13:</strong> Pendidikan</li>
                        <li><strong>Kolom 14:</strong> Jurusan</li>
                    </ul>
                </div>

                <form method="POST" enctype="multipart/form-data" id="uploadForm">
                    <div class="upload-area" id="uploadArea" onclick="document.getElementById('fileInput').click()">
                        <div class="upload-icon">
                            <i class="fas fa-cloud-upload-alt"></i>
                        </div>
                        <div class="upload-text">
                            <h3>Klik atau Drag & Drop File Excel</h3>
                            <p>Format: .xls, .xlsx, .csv (Maks. 10MB)</p>
                        </div>
                    </div>
                    
                    <input type="file" 
                           id="fileInput" 
                           name="excel_file" 
                           class="file-input" 
                           accept=".xls,.xlsx,.csv"
                           onchange="handleFileSelect(event)">
                    
                    <div id="selectedFile" class="selected-file" style="display: none;">
                        <i class="fas fa-file-excel"></i>
                        <div class="file-info">
                            <div class="file-name" id="fileName"></div>
                            <div class="file-size" id="fileSize"></div>
                        </div>
                        <button type="button" class="btn-remove" onclick="removeFile()">
                            <i class="fas fa-times"></i>
                        </button>
                    </div>

                    <div class="form-actions">
                        <a href="javascript:history.back()" class="btn btn-outline">
                            <i class="fas fa-arrow-left"></i>
                            Kembali
                        </a>
                        <div style="display: flex; gap: 12px;">
                            <a href="template_import_sdm.xlsx" class="btn btn-outline" download>
                                <i class="fas fa-download"></i>
                                Download Template
                            </a>
                            <button type="submit" class="btn btn-primary" id="btnSubmit" disabled>
                                <i class="fas fa-upload"></i>
                                Import Data
                            </button>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <script>
        const uploadArea = document.getElementById('uploadArea');
        const fileInput = document.getElementById('fileInput');
        const selectedFile = document.getElementById('selectedFile');
        const btnSubmit = document.getElementById('btnSubmit');

        // Drag & Drop
        uploadArea.addEventListener('dragover', (e) => {
            e.preventDefault();
            uploadArea.classList.add('dragover');
        });

        uploadArea.addEventListener('dragleave', () => {
            uploadArea.classList.remove('dragover');
        });

        uploadArea.addEventListener('drop', (e) => {
            e.preventDefault();
            uploadArea.classList.remove('dragover');
            
            const files = e.dataTransfer.files;
            if (files.length > 0) {
                fileInput.files = files;
                handleFileSelect({ target: fileInput });
            }
        });

        function handleFileSelect(event) {
            const file = event.target.files[0];
            if (file) {
                const fileName = file.name;
                const fileSize = (file.size / 1024).toFixed(2) + ' KB';
                
                document.getElementById('fileName').textContent = fileName;
                document.getElementById('fileSize').textContent = fileSize;
                selectedFile.style.display = 'flex';
                uploadArea.style.display = 'none';
                btnSubmit.disabled = false;
            }
        }

        function removeFile() {
            fileInput.value = '';
            selectedFile.style.display = 'none';
            uploadArea.style.display = 'block';
            btnSubmit.disabled = true;
        }
    </script>
</body>
</html>