<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date;

function importGtkFromExcel($file_path, $file_extension, $conn) {
    // Variabel untuk menandai transaction status
    $in_transaction = false;
    
    try {
        // Load file Excel
        $reader = IOFactory::createReader(ucfirst($file_extension));
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file_path);
        $worksheet = $spreadsheet->getActiveSheet();
        $rows = $worksheet->toArray();
        
        // Konfigurasi
        $skip_header = isset($_POST['skip_header']) && $_POST['skip_header'] == 1;
        $update_existing = isset($_POST['update_existing']) && $_POST['update_existing'] == 1;
        
        // Ambil tahun ajaran aktif
        $query_tahun_aktif = "SELECT * FROM bind_tahunajaran WHERE aktif = 1 LIMIT 1";
        $result_tahun_aktif = mysqli_query($conn, $query_tahun_aktif);
        $tahun_aktif = mysqli_fetch_assoc($result_tahun_aktif);
        
        if (!$tahun_aktif) {
            return [
                'success' => false,
                'message' => 'Tidak ada tahun ajaran yang aktif. Silakan hubungi administrator.'
            ];
        }
        
        $id_tahunajaran = $tahun_aktif['id_tahunajaran'];
        
        // Mulai dari baris 1 atau 2 tergantung header
        $start_row = $skip_header ? 1 : 0;
        $total_rows = count($rows) - $start_row;
        
        if ($total_rows <= 0) {
            return [
                'success' => false,
                'message' => 'Tidak ada data yang ditemukan dalam file.'
            ];
        }
        
        $stats = [
            'total' => $total_rows,
            'success' => 0,
            'failed' => 0,
            'errors' => []
        ];
        
        // Mulai transaction (gunakan cara yang kompatibel)
        $conn->autocommit(false);
        $in_transaction = true;
        
        for ($i = $start_row; $i < count($rows); $i++) {
            $row = $rows[$i];
            $row_number = $i + 1;
            
            // Skip baris kosong
            if (empty(array_filter($row))) {
                continue;
            }
            
            // Mapping kolom Excel ke field database
            $data = mapExcelRowToGtkData($row, $row_number);
            
            if (isset($data['error'])) {
                $stats['failed']++;
                $stats['errors'][] = "Baris $row_number: " . $data['error'];
                continue;
            }
            
            // Tambahkan id_tahunajaran ke data
            $data['id_tahunajaran'] = $id_tahunajaran;
            
            // Cek apakah data sudah ada
            $existing_id = checkExistingGtk($data, $conn);
            
            if ($existing_id && $update_existing) {
                // Update data existing
                $result = updateGtk($existing_id, $data, $conn, $row_number);
            } else if (!$existing_id) {
                // Insert data baru
                $result = insertGtk($data, $conn, $row_number);
            } else {
                // Data sudah ada dan tidak diupdate
                $stats['failed']++;
                $stats['errors'][] = "Baris $row_number: Data sudah ada (NIP: {$data['nip']}, NUPTK: {$data['nuptk']}, NIK: {$data['nik']})";
                continue;
            }
            
            if ($result['success']) {
                $stats['success']++;
            } else {
                $stats['failed']++;
                $stats['errors'][] = "Baris $row_number: " . $result['error'];
            }
        }
        
        // Commit transaction
        $conn->commit();
        $in_transaction = false;
        
        // Hapus file temporary
        if (file_exists($file_path)) {
            unlink($file_path);
        }
        
        return [
            'success' => true,
            'message' => "Proses import selesai.",
            'stats' => $stats
        ];
        
    } catch (Exception $e) {
        // Rollback transaction jika error dan sedang dalam transaction
        if ($in_transaction) {
            $conn->rollback();
        }
        
        // Hapus file temporary
        if (file_exists($file_path)) {
            unlink($file_path);
        }
        
        return [
            'success' => false,
            'message' => 'Terjadi kesalahan: ' . $e->getMessage()
        ];
    } finally {
        // Pastikan autocommit dikembalikan ke true
        $conn->autocommit(true);
    }
}

function mapExcelRowToGtkData($row, $row_number) {
    // Validasi minimal required fields
    if (empty($row[3])) { // Nama
        return ['error' => 'Nama tidak boleh kosong'];
    }
    
    if (empty($row[2])) { // NIK
        return ['error' => 'NIK tidak boleh kosong'];
    }
    
    $data = [
        'nip' => !empty($row[0]) ? $row[0] : '-',
        'nuptk' => !empty($row[1]) ? $row[1] : '-',
        'nik' => $row[2],
        'nama' => $row[3],
        'tempat_lahir' => $row[4] ?? '',
        'tgl_lahir' => formatExcelDate($row[5] ?? null),
        'jenis_kelamin' => formatJenisKelamin($row[6] ?? ''),
        'agama' => formatAgama($row[7] ?? ''),
        'status_pernikahan' => formatStatusPernikahan($row[8] ?? ''),
        'alamat' => $row[9] ?? '',
        'no_hp' => $row[10] ?? '',
        'email' => $row[11] ?? '',
        'sumber_gaji' => formatSumberGaji($row[12] ?? ''),
        'jenis_gtk' => formatJenisGtk($row[13] ?? ''),
        'status_kepegawaian' => formatStatusKepegawaian($row[14] ?? ''),
        'pendidikan_terakhir' => formatPendidikan($row[15] ?? ''),
        'mata_pelajaran' => $row[16] ?? '',
        'tunjangan_sertifikasi' => !empty($row[17]) ? 1 : 0,
        'jabatan_fungsional' => $row[18] ?? '',
        'golongan' => $row[19] ?? '',
        'tmt_kerja' => formatExcelDate($row[20] ?? null),
        'status_aktif' => isset($row[21]) ? (($row[21] == 0) ? 0 : 1) : 1
    ];
    
    // Validasi data
    $validation = validateGtkData($data, $row_number);
    if (!$validation['valid']) {
        return ['error' => $validation['message']];
    }
    
    return $data;
}

function formatExcelDate($excelDate) {
    if (empty($excelDate)) {
        return null;
    }
    
    // Jika sudah format string tanggal
    if (is_string($excelDate)) {
        // Coba parse berbagai format tanggal
        $formats = ['Y-m-d', 'd/m/Y', 'm/d/Y', 'd-m-Y', 'm-d-Y'];
        
        foreach ($formats as $format) {
            $date = DateTime::createFromFormat($format, $excelDate);
            if ($date !== false) {
                return $date->format('Y-m-d');
            }
        }
        
        // Jika tidak bisa diparse, return null
        return null;
    }
    
    // Jika numeric (format Excel)
    if (is_numeric($excelDate)) {
        try {
            return Date::excelToDateTimeObject($excelDate)->format('Y-m-d');
        } catch (Exception $e) {
            return null;
        }
    }
    
    return null;
}

function formatJenisKelamin($value) {
    $value = strtoupper(trim($value));
    if (in_array($value, ['L', 'LAKI-LAKI', 'LAKI', 'PRIA'])) {
        return 'L';
    } elseif (in_array($value, ['P', 'PEREMPUAN', 'WANITA'])) {
        return 'P';
    }
    return '';
}

function formatAgama($value) {
    $value = ucfirst(strtolower(trim($value)));
    $agama_list = ['Islam', 'Kristen', 'Katolik', 'Hindu', 'Budha', 'Khonghucu', 'Lainnya'];
    
    foreach ($agama_list as $agama) {
        if (strpos($agama, $value) === 0 || strpos($value, $agama) === 0) {
            return $agama;
        }
    }
    
    return 'Lainnya';
}

function formatStatusPernikahan($value) {
    $value = ucwords(strtolower(trim($value)));
    $status_list = ['Belum Kawin', 'Kawin', 'Cerai Hidup', 'Cerai Mati'];
    
    foreach ($status_list as $status) {
        if (strpos($status, $value) === 0 || strpos($value, $status) === 0) {
            return $status;
        }
    }
    
    return 'Belum Kawin';
}

function formatSumberGaji($value) {
    $value = ucfirst(strtolower(trim($value)));
    $sumber_list = ['Apbn', 'Apbd', 'Sekolah', 'Yayasan', 'Lainnya'];
    
    foreach ($sumber_list as $sumber) {
        if (strpos($sumber, $value) === 0 || strpos($value, $sumber) === 0) {
            return $sumber;
        }
    }
    
    return 'Lainnya';
}

function formatJenisGtk($value) {
    $value = ucwords(strtolower(trim($value)));
    $jenis_list = ['Guru Mapel', 'Guru Bk', 'Kepala Sekolah', 'Tendik', 'Lainnya'];
    
    foreach ($jenis_list as $jenis) {
        if (strpos($jenis, $value) === 0 || strpos($value, $jenis) === 0) {
            return $jenis;
        }
    }
    
    return 'Lainnya';
}

function formatStatusKepegawaian($value) {
    $value = strtoupper(trim($value));
    $status_list = ['PNS', 'PPPK', 'GTT', 'PTT'];
    
    foreach ($status_list as $status) {
        if (strpos($status, $value) === 0 || strpos($value, $status) === 0) {
            return $status;
        }
    }
    
    return 'PTT'; // Default value
}

function formatPendidikan($value) {
    $value = strtoupper(trim($value));
    $pendidikan_list = ['SMA', 'D3', 'S1', 'S2', 'S3'];
    
    foreach ($pendidikan_list as $pendidikan) {
        if (strpos($pendidikan, $value) === 0 || strpos($value, $pendidikan) === 0) {
            return $pendidikan;
        }
    }
    
    return 'SMA'; // Default value
}

function validateGtkData($data, $row_number) {
    // Validasi NIP, NUPTK, NIK tidak boleh sama dengan yang sudah ada kecuali '-'
    if ($data['nip'] != '-' && !empty($data['nip'])) {
        if (!preg_match('/^[0-9-]+$/', $data['nip'])) {
            return [
                'valid' => false,
                'message' => 'Format NIP tidak valid (hanya angka dan tanda minus)'
            ];
        }
    }
    
    if ($data['nuptk'] != '-' && !empty($data['nuptk'])) {
        if (!preg_match('/^[0-9-]+$/', $data['nuptk'])) {
            return [
                'valid' => false,
                'message' => 'Format NUPTK tidak valid (hanya angka dan tanda minus)'
            ];
        }
    }
    
    if (!preg_match('/^[0-9-]+$/', $data['nik'])) {
        return [
            'valid' => false,
            'message' => 'Format NIK tidak valid (hanya angka dan tanda minus)'
        ];
    }
    
    // Validasi email
    if (!empty($data['email']) && !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
        return [
            'valid' => false,
            'message' => 'Format email tidak valid'
        ];
    }
    
    // Validasi tanggal
    if (!empty($data['tgl_lahir']) && !validateDate($data['tgl_lahir'])) {
        return [
            'valid' => false,
            'message' => 'Format tanggal lahir tidak valid (harus YYYY-MM-DD)'
        ];
    }
    
    if (!empty($data['tmt_kerja']) && !validateDate($data['tmt_kerja'])) {
        return [
            'valid' => false,
            'message' => 'Format TMT kerja tidak valid (harus YYYY-MM-DD)'
        ];
    }
    
    return ['valid' => true];
}

function validateDate($date, $format = 'Y-m-d') {
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}

function checkExistingGtk($data, $conn) {
    $conditions = [];
    $params = [];
    $types = '';
    
    // Cek berdasarkan NIP (jika tidak kosong dan bukan '-')
    if (!empty($data['nip']) && $data['nip'] != '-') {
        $conditions[] = "nip = ?";
        $params[] = $data['nip'];
        $types .= 's';
    }
    
    // Cek berdasarkan NUPTK (jika tidak kosong dan bukan '-')
    if (!empty($data['nuptk']) && $data['nuptk'] != '-') {
        $conditions[] = "nuptk = ?";
        $params[] = $data['nuptk'];
        $types .= 's';
    }
    
    // Selalu cek berdasarkan NIK
    $conditions[] = "nik = ?";
    $params[] = $data['nik'];
    $types .= 's';
    
    // Tambahkan kondisi tahun ajaran
    $conditions[] = "id_tahunajaran = ?";
    $params[] = $data['id_tahunajaran'];
    $types .= 'i';
    
    if (empty($conditions)) {
        return false;
    }
    
    $sql = "SELECT id_gtk FROM bind_gtk WHERE (" . implode(" OR ", array_slice($conditions, 0, -1)) . ") AND " . end($conditions) . " LIMIT 1";
    $stmt = $conn->prepare($sql);
    
    if ($stmt) {
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return $row['id_gtk'];
        }
    }
    
    return false;
}

function insertGtk($data, $conn, $row_number) {
    $sql = "INSERT INTO bind_gtk (
        id_tahunajaran, nip, nuptk, nik, nama, tempat_lahir, tgl_lahir, jenis_kelamin, 
        agama, status_pernikahan, alamat, no_hp, email, sumber_gaji, jenis_gtk, 
        status_kepegawaian, pendidikan_terakhir, mata_pelajaran, tunjangan_sertifikasi, 
        jabatan_fungsional, golongan, tmt_kerja, status_aktif
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    $stmt = $conn->prepare($sql);
    
    if (!$stmt) {
        return [
            'success' => false,
            'error' => 'Prepare statement failed: ' . $conn->error
        ];
    }
    
    $stmt->bind_param(
        "issssssssssssssssisssss", 
        $data['id_tahunajaran'], $data['nip'], $data['nuptk'], $data['nik'], $data['nama'], 
        $data['tempat_lahir'], $data['tgl_lahir'], $data['jenis_kelamin'],
        $data['agama'], $data['status_pernikahan'], $data['alamat'], 
        $data['no_hp'], $data['email'], $data['sumber_gaji'], $data['jenis_gtk'],
        $data['status_kepegawaian'], $data['pendidikan_terakhir'], $data['mata_pelajaran'],
        $data['tunjangan_sertifikasi'], $data['jabatan_fungsional'], $data['golongan'],
        $data['tmt_kerja'], $data['status_aktif']
    );
    
    if ($stmt->execute()) {
        return ['success' => true];
    } else {
        return [
            'success' => false,
            'error' => 'Execute failed: ' . $stmt->error
        ];
    }
}

function updateGtk($id, $data, $conn, $row_number) {
    $sql = "UPDATE bind_gtk SET
        id_tahunajaran = ?, nip = ?, nuptk = ?, nik = ?, nama = ?, tempat_lahir = ?, tgl_lahir = ?, 
        jenis_kelamin = ?, agama = ?, status_pernikahan = ?, alamat = ?, no_hp = ?, 
        email = ?, sumber_gaji = ?, jenis_gtk = ?, status_kepegawaian = ?, 
        pendidikan_terakhir = ?, mata_pelajaran = ?, tunjangan_sertifikasi = ?, 
        jabatan_fungsional = ?, golongan = ?, tmt_kerja = ?, status_aktif = ?,
        updated_at = CURRENT_TIMESTAMP
        WHERE id_gtk = ?";
    
    $stmt = $conn->prepare($sql);
    
    if (!$stmt) {
        return [
            'success' => false,
            'error' => 'Prepare statement failed: ' . $conn->error
        ];
    }
    
    $stmt->bind_param(
        "issssssssssssssssisssssi", 
        $data['id_tahunajaran'], $data['nip'], $data['nuptk'], $data['nik'], $data['nama'], 
        $data['tempat_lahir'], $data['tgl_lahir'], $data['jenis_kelamin'],
        $data['agama'], $data['status_pernikahan'], $data['alamat'], 
        $data['no_hp'], $data['email'], $data['sumber_gaji'], $data['jenis_gtk'],
        $data['status_kepegawaian'], $data['pendidikan_terakhir'], $data['mata_pelajaran'],
        $data['tunjangan_sertifikasi'], $data['jabatan_fungsional'], $data['golongan'],
        $data['tmt_kerja'], $data['status_aktif'], $id
    );
    
    if ($stmt->execute()) {
        return ['success' => true];
    } else {
        return [
            'success' => false,
            'error' => 'Execute failed: ' . $stmt->error
        ];
    }
}
?>