<?php
session_start();
require_once '../config/database.php';
require_once 'encoding_helper.php';

// Check if user is logged in and is admin
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] != 'admin') {
    header('Location: ../login.php');
    exit();
}

$success_message = '';
$error_message = '';
$upload_results = [];

// Get classes for dropdown
$stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
$classes = $stmt->fetchAll();

// Fetch all valid class IDs for validation
$stmt_classes = $pdo->query("SELECT id FROM classes");
$valid_class_ids = $stmt_classes->fetchAll(PDO::FETCH_COLUMN);

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    $default_class_id = $_POST['default_class_id'];
    $create_users = isset($_POST['create_users']) ? true : false;
    
    if ($_FILES['csv_file']['error'] == 0) {
        $file_tmp = $_FILES['csv_file']['tmp_name'];
        $file_type = $_FILES['csv_file']['type'];
        
        $allowed_types = ['text/csv', 'application/csv', 'application/vnd.ms-excel', 'text/plain'];
        if (!in_array($file_type, $allowed_types)) {
            $error_message = 'শুধুমাত্র CSV ফাইল আপলোড করা যাবে';
        } else {
            try {
                $pdo->beginTransaction();
                
                $encodingInfo = EncodingHelper::getFileEncodingInfo($file_tmp);
                
                $csvResult = processCsvWithBengaliSupport($file_tmp, function($data, $rowNumber, $headers) use ($pdo, $default_class_id, $create_users, $valid_class_ids) {
                    
                    $student_data = [
                        'student_id' => EncodingHelper::cleanBengaliText($data[0] ?? ''),
                        'first_name' => EncodingHelper::cleanBengaliText($data[1] ?? ''),
                        'last_name' => EncodingHelper::cleanBengaliText($data[2] ?? ''),
                        'father_name' => EncodingHelper::cleanBengaliText($data[3] ?? ''),
                        'mother_name' => EncodingHelper::cleanBengaliText($data[4] ?? ''),
                        'email' => trim($data[5] ?? ''),
                        'phone' => trim($data[6] ?? ''),
                        'date_of_birth' => trim($data[7] ?? ''),
                        'gender' => trim($data[8] ?? ''),
                        'address' => EncodingHelper::cleanBengaliText($data[9] ?? ''),
                        'guardian_name' => EncodingHelper::cleanBengaliText($data[10] ?? ''),
                        'guardian_phone' => trim($data[11] ?? ''),
                        'guardian_email' => trim($data[12] ?? ''),
                        'guardian_address' => EncodingHelper::cleanBengaliText($data[13] ?? ''),
                        'class_id' => !empty(trim($data[14] ?? '')) ? trim($data[14]) : $default_class_id,
                        'roll_number' => trim($data[15] ?? ''),
                        'admission_date' => trim($data[16] ?? ''),
                        'session' => trim($data[17] ?? '') ?: date('Y'),
                        'group_name' => EncodingHelper::cleanBengaliText($data[18] ?? '')
                    ];
                    
                    // Ensure empty values are properly handled for non-required fields
                    $student_data = array_map(function($value) {
                        return is_string($value) ? trim($value) : $value;
                    }, $student_data);
                    
                    // Set null for empty string values in non-required fields
                    $nullable_fields = ['last_name', 'father_name', 'mother_name', 'email', 'phone', 'date_of_birth', 'gender', 'address', 'guardian_name', 'guardian_phone', 'guardian_email', 'guardian_address', 'admission_date', 'group_name'];
                    foreach ($nullable_fields as $field) {
                        if (isset($student_data[$field]) && $student_data[$field] === '') {
                            $student_data[$field] = null;
                        }
                    }
                    
                    if (empty($student_data['student_id']) || empty($student_data['first_name'])) {
                        throw new Exception("Row $rowNumber: Student ID এবং First Name আবশ্যক");
                    }

                    if (!in_array($student_data['class_id'], $valid_class_ids)) {
                        throw new Exception("Row $rowNumber: অবৈধ ক্লাস আইডি '{$student_data['class_id']}'। এই ক্লাসটি সিস্টেমে নেই।");
                    }

                    // Check for duplicate roll number
                    $stmt_roll = $pdo->prepare("SELECT COUNT(*) FROM students WHERE class_id = ? AND session = ? AND COALESCE(group_name, '') = ? AND roll_number = ? AND status = 'active'");
                    $stmt_roll->execute([
                        $student_data['class_id'], 
                        $student_data['session'], 
                        $student_data['group_name'] ?: '', 
                        $student_data['roll_number']
                    ]);
                    if ($stmt_roll->fetchColumn() > 0) {
                        $group_text = $student_data['group_name'] ? " ({$student_data['group_name']} গ্রুপে)" : '';
                        throw new Exception("Row $rowNumber: রোল নম্বর '{$student_data['roll_number']}' এই ক্লাস ও সেশনে{$group_text} ইতিমধ্যে ব্যবহৃত হয়েছে।");
                    }
                    
                    $stmt = $pdo->prepare("SELECT id FROM students WHERE student_id = ?");
                    $stmt->execute([$student_data['student_id']]);
                    if ($stmt->fetch()) {
                        throw new Exception("Row $rowNumber: Student ID '{$student_data['student_id']}' ইতিমধ্যে বিদ্যমান");
                    }
                    
                    if (!empty($student_data['email']) && !filter_var($student_data['email'], FILTER_VALIDATE_EMAIL)) {
                        throw new Exception("Row $rowNumber: অবৈধ ইমেইল ফরম্যাট");
                    }
                    
                    if (!empty($student_data['date_of_birth'])) {
                        $date = DateTime::createFromFormat('Y-m-d', $student_data['date_of_birth']);
                        if (!$date) throw new Exception("Row $rowNumber: জন্ম তারিখ YYYY-MM-DD ফরম্যাটে হতে হবে");
                    }
                    
                    if (!empty($student_data['admission_date'])) {
                        $date = DateTime::createFromFormat('Y-m-d', $student_data['admission_date']);
                        if (!$date) $student_data['admission_date'] = date('Y-m-d');
                    } else {
                        $student_data['admission_date'] = date('Y-m-d');
                    }
                    
                    $user_id = null;
                    if ($create_users) {
                        $username = strtolower($student_data['student_id']);
                        $original_username = $username;
                        $counter = 1;

                        $stmt_check = $pdo->prepare("SELECT id FROM users WHERE username = ?");
                        $stmt_check->execute([$username]);
                        while ($stmt_check->fetch()) {
                            $username = $original_username . $counter;
                            $counter++;
                            $stmt_check->execute([$username]);
                        }

                        $password = password_hash($student_data['student_id'], PASSWORD_DEFAULT);
                        
                        $stmt = $pdo->prepare("
                            INSERT INTO users (username, password, email, role, status) 
                            VALUES (?, ?, ?, 'student', 'active')
                        ");
                        // Use null for empty email values
                        $user_email = !empty($student_data['email']) ? $student_data['email'] : null;
                        $stmt->execute([$username, $password, $user_email]);
                        $user_id = $pdo->lastInsertId();
                    }
                    
                    $stmt = $pdo->prepare(" 
                        INSERT INTO students (
                            student_id, first_name, last_name, father_name, mother_name, email, phone, date_of_birth, 
                            gender, address, guardian_name, guardian_phone, guardian_email, guardian_address, 
                            class_id, session, group_name, roll_number, admission_date, user_id, status
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active')
                    ");
                    
                    // Prepare values array, converting empty strings to null where appropriate
                    $values = [
                        $student_data['student_id'],
                        $student_data['first_name'],
                        $student_data['last_name'] ?? null,
                        $student_data['father_name'] ?? null,
                        $student_data['mother_name'] ?? null,
                        $student_data['email'] ?? null,
                        $student_data['phone'] ?? null,
                        $student_data['date_of_birth'] ?? null,
                        $student_data['gender'] ?? null,
                        $student_data['address'] ?? null,
                        $student_data['guardian_name'] ?? null,
                        $student_data['guardian_phone'] ?? null,
                        $student_data['guardian_email'] ?? null,
                        $student_data['guardian_address'] ?? null,
                        $student_data['class_id'],
                        $student_data['session'],
                        $student_data['group_name'] ?? null,
                        $student_data['roll_number'],
                        $student_data['admission_date'] ?? null,
                        $user_id
                    ];
                    
                    $stmt->execute($values);
                    
                    return [
                        'row' => $rowNumber, 'status' => 'success',
                        'name' => $student_data['first_name'] . ' ' . $student_data['last_name'],
                        'student_id' => $student_data['student_id']
                    ];
                });
                
                if ($csvResult['success']) {
                    $success_count = $csvResult['successful_rows'];
                    $error_count = $csvResult['failed_rows'];
                    $upload_results = array_merge($csvResult['results'], $csvResult['errors']);
                    
                    if ($error_count == 0) {
                        $pdo->commit();
                        $success_message = "সফলভাবে $success_count জন শিক্ষার্থী যোগ করা হয়েছে! (এনকোডিং: {$csvResult['encoding_info']['detected_encoding']})";
                    } else {
                        $pdo->rollBack();
                        $error_message = "মোট {$csvResult['total_rows']} টি রো থেকে $error_count টিতে ত্রুটি পাওয়া গেছে। কোন ডেটা যোগ করা হয়নি।";
                    }
                } else {
                    $pdo->rollBack();
                    $error_message = $csvResult['error'];
                }
                
            } catch (Exception $e) {
                $pdo->rollBack();
                $error_message = 'ডাটাবেস ত্রুটি: ' . $e->getMessage();
            }
        }
    } else {
        $error_message = 'ফাইল আপলোড করতে সমস্যা হয়েছে';
    }
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>শিক্ষার্থী বাল্ক আপলোড (বাংলা সাপোর্ট) - স্কুল ম্যানেজমেন্ট সিস্টেম</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="../assets/css/style.css" rel="stylesheet">
</head>
<body class="bengali-text">
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
        <div class="container-fluid"><a class="navbar-brand" href="../index.php"><i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম</a><div class="navbar-nav ms-auto"><a class="nav-link" href="index.php"><i class="fas fa-arrow-left"></i> বাল্ক আপলোড হোম</a></div></div>
    </nav>

    <div class="container mt-4">
        <div class="row">
            <div class="col-md-3">
                <div class="card">
                    <div class="card-header"><h5><i class="fas fa-user-graduate"></i> শিক্ষার্থী আপলোড</h5></div>
                    <div class="card-body">
                        <div class="d-grid gap-2">
                            <a href="templates/students_template.csv" class="btn btn-success"><i class="fas fa-download"></i> নতুন টেমপ্লেট ডাউনলোড</a>
                            <a href="index.php" class="btn btn-outline-primary"><i class="fas fa-home"></i> হোম</a>
                        </div>
                        <hr>
                        <div class="alert alert-info"><h6><i class="fas fa-info-circle"></i> বাংলা সাপোর্ট:</h6><ul class="small mb-0"><li>বাংলা নাম ও ঠিকানা সাপোর্ট</li><li>UTF-8 এনকোডিং অটো কনভার্ট</li><li>Excel থেকে CSV সেভ করুন</li><li>বাংলা ফন্ট প্রদর্শন</li></ul></div>
                        <div class="alert alert-warning"><h6><i class="fas fa-exclamation-triangle"></i> নির্দেশনা:</h6><ol class="small mb-0"><li>টেমপ্লেট ডাউনলোড করুন</li><li>Excel/CSV এ তথ্য পূরণ করুন</li><li>CSV (UTF-8) হিসেবে সেভ করুন</li><li>ফাইল আপলোড করুন</li></ol></div>
                    </div>
                </div>
            </div>
            
            <div class="col-md-9">
                <div class="card">
                    <div class="card-header"><h4><i class="fas fa-upload"></i> শিক্ষার্থী বাল্ক আপলোড (বাংলা সাপোর্ট)</h4></div>
                    <div class="card-body">
                        <?php if ($success_message): ?><div class="alert alert-success alert-dismissible fade show"><i class="fas fa-check-circle"></i> <?php echo $success_message; ?><button type="button" class="btn-close" data-bs-dismiss="alert"></button></div><?php endif; ?>
                        <?php if ($error_message): ?><div class="alert alert-danger alert-dismissible fade show"><i class="fas fa-exclamation-triangle"></i> <?php echo $error_message; ?><button type="button" class="btn-close" data-bs-dismiss="alert"></button></div><?php endif; ?>

                        <form method="POST" action="" enctype="multipart/form-data" class="needs-validation" novalidate>
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="mb-3"><label for="csv_file" class="form-label">CSV ফাইল নির্বাচন করুন <span class="text-danger">*</span></label><input type="file" class="form-control" id="csv_file" name="csv_file" accept=".csv,.txt" required><div class="invalid-feedback">অনুগ্রহ করে একটি CSV ফাইল নির্বাচন করুন</div><small class="form-text text-muted">CSV ফাইল (UTF-8 এনকোডিং প্রস্তাবিত)</small></div>
                                </div>
                                <div class="col-md-6">
                                    <div class="mb-3"><label for="default_class_id" class="form-label">ডিফল্ট ক্লাস</label><select class="form-select" id="default_class_id" name="default_class_id"><option value="">নির্বাচন করুন</option><?php foreach ($classes as $class): ?><option value="<?php echo $class['id']; ?>"><?php echo $class['name'] . ' - ' . $class['section']; ?></option><?php endforeach; ?></select><small class="form-text text-muted">CSV এ ক্লাস উল্লেখ না থাকলে এই ক্লাস ব্যবহার হবে</small></div>
                                </div>
                            </div>
                            <div class="mb-3"><div class="form-check"><input class="form-check-input" type="checkbox" id="create_users" name="create_users" checked><label class="form-check-label" for="create_users"><i class="fas fa-user-plus"></i> শিক্ষার্থীদের জন্য ইউজার অ্যাকাউন্ট তৈরি করুন</label></div><small class="form-text text-muted">চেক করলে প্রতিটি শিক্ষার্থীর জন্য লগইন অ্যাকাউন্ট তৈরি হবে (Username: Student ID, Password: Student ID)</small></div>
                            <div class="d-grid gap-2 d-md-flex justify-content-md-end"><a href="index.php" class="btn btn-secondary"><i class="fas fa-times"></i> বাতিল</a><button type="submit" class="btn btn-success"><i class="fas fa-upload"></i> আপলোড করুন</button></div>
                        </form>
                    </div>
                </div>

                <?php if (!empty($upload_results)): ?>
                <div class="card mt-4">
                    <div class="card-header"><h5><i class="fas fa-list"></i> আপলোড ফলাফল</h5></div>
                    <div class="card-body"><div class="table-responsive"><table class="table table-sm"><thead><tr><th>রো</th><th>স্ট্যাটাস</th><th>বিবরণ</th></tr></thead><tbody><?php foreach ($upload_results as $result): ?><tr><td><?php echo $result['row']; ?></td><td><?php if (isset($result['status']) && $result['status'] == 'success'): ?><span class="badge bg-success">সফল</span><?php else: ?><span class="badge bg-danger">ব্যর্থ</span><?php endif; ?></td><td class="bengali-text"><?php if (isset($result['status']) && $result['status'] == 'success'): ?><?php echo $result['name'] . ' (' . $result['student_id'] . ')'; ?><?php else: ?><?php echo $result['error'] ?? $result['message'] ?? 'অজানা ত্রুটি'; ?><?php endif; ?></td></tr><?php endforeach; ?></tbody></table></div></div>
                </div>
                <?php endif; ?>

                <div class="card mt-4">
                    <div class="card-header"><h5><i class="fas fa-table"></i> নতুন CSV ফরম্যাট গাইড</h5></div>
                    <div class="card-body">
                        <div class="table-responsive">
                            <table class="table table-bordered table-sm">
                                <thead class="table-dark"><tr><th>কলাম</th><th>ফিল্ড নাম</th><th>আবশ্যক</th><th>বাংলা উদাহরণ</th></tr></thead>
                                <tbody>
                                    <tr><td>A</td><td>Student ID</td><td class="text-danger">হ্যাঁ</td><td>2024001</td></tr>
                                    <tr><td>B</td><td>First Name</td><td class="text-danger">হ্যাঁ</td><td>মোহাম্মদ রহিম</td></tr>
                                    <tr><td>C</td><td>Last Name</td><td class="text-success">না</td><td>উদ্দিন</td></tr>
                                    <tr><td>D</td><td>Father Name</td><td class="text-success">না</td><td>আলীম উদ্দিন</td></tr>
                                    <tr><td>E</td><td>Mother Name</td><td class="text-success">না</td><td>রাবেয়া খাতুন</td></tr>
                                    <tr><td>F</td><td>Email</td><td class="text-success">না</td><td>rahim@email.com</td></tr>
                                    <tr><td>G</td><td>Phone</td><td class="text-success">না</td><td>01712345678</td></tr>
                                    <tr><td>H</td><td>Date of Birth</td><td class="text-success">না</td><td>2010-01-15</td></tr>
                                    <tr><td>I</td><td>Gender</td><td class="text-success">না</td><td>male/female</td></tr>
                                    <tr><td>J</td><td>Address</td><td class="text-success">না</td><td>ঢাকা, বাংলাদেশ</td></tr>
                                    <tr><td>K</td><td>Guardian Name</td><td class="text-success">না</td><td>মোহাম্মদ করিম উদ্দিন</td></tr>
                                    <tr><td>L</td><td>Guardian Phone</td><td class="text-success">না</td><td>01812345678</td></tr>
                                    <tr><td>M</td><td>Guardian Email</td><td class="text-success">না</td><td>karim@email.com</td></tr>
                                    <tr><td>N</td><td>Guardian Address</td><td class="text-success">না</td><td>ঢাকা, বাংলাদেশ</td></tr>
                                    <tr><td>O</td><td>Class ID</td><td class="text-success">না</td><td>6 (ঐচ্ছিক, বৈধ ID: 6,7,8,9,10)</td></tr>
                                    <tr><td>P</td><td>Roll Number</td><td class="text-danger">হ্যাঁ</td><td>1</td></tr>
                                    <tr><td>Q</td><td>Admission Date</td><td class="text-success">না</td><td>2024-01-01</td></tr>
                                    <tr><td>R</td><td>Session</td><td class="text-warning">প্রয়োজনীয়</td><td>2024</td></tr>
                                    <tr><td>S</td><td>Group Name</td><td class="text-success">না</td><td>Science</td></tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
