<?php
session_start();

// Include access control functions
require_once '../includes/access_control.php';
require_once '../config/database.php';

// Check if user is logged in and is a teacher
require_teacher();

$user_role = $_SESSION['user_role'];
$user_id = $_SESSION['user_id'];
$user_name = $_SESSION['user_name'] ?? $_SESSION['username'] ?? 'শিক্ষক';

// Only teachers can access this page
if ($user_role !== 'teacher') {
    header('Location: ../index.php');
    exit();
}

// Get teacher ID
$teacher_id = null;
$stmt = $pdo->prepare("SELECT id FROM teachers WHERE user_id = ?");
$stmt->execute([$user_id]);
$teacher = $stmt->fetch();
if ($teacher) {
    $teacher_id = $teacher['id'];
} else {
    // Redirect if not a valid teacher
    header('Location: dashboard.php');
    exit();
}

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

// Get teacher's assigned classes
$assigned_classes = [];
if ($teacher_id) {
    // Get classes where this teacher is assigned as class teacher
    $stmt = $pdo->prepare("
        SELECT c.id, c.name, c.section
        FROM classes c
        WHERE c.class_teacher_id = ? AND c.status = 'active'
        ORDER BY c.name, c.section
    ");
    $stmt->execute([$teacher_id]);
    $assigned_classes = $stmt->fetchAll();
    
    // If no classes found through class teacher assignment, check if they teach subjects
    if (empty($assigned_classes)) {
        $stmt = $pdo->prepare("
            SELECT DISTINCT c.id, c.name, c.section
            FROM classes c
            JOIN class_subjects cs ON c.id = cs.class_id
            WHERE cs.teacher_id = ? AND c.status = 'active'
            ORDER BY c.name, c.section
        ");
        $stmt->execute([$teacher_id]);
        $assigned_classes = $stmt->fetchAll();
    }
}

// Get class IDs for filtering
$class_ids = array_column($assigned_classes, 'id');

// Get classes for dropdown (only assigned classes)
$classes = $assigned_classes;

// Get academic sessions
$stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
$sessions = $stmt->fetchAll();

// Get student groups
$stmt = $pdo->query("SELECT group_name, group_code, description FROM student_groups WHERE status = 'active' ORDER BY group_name");
$groups = $stmt->fetchAll();

// Process form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    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();
                
                // Read the CSV file
                if (($handle = fopen($file_tmp, "r")) !== FALSE) {
                    $rowNumber = 0;
                    $headers = fgetcsv($handle, 1000, ",");
                    $successful_rows = 0;
                    $failed_rows = 0;
                    
                    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                        $rowNumber++;
                        
                        try {
                            // Validate required fields
                            if (empty($data[0]) || empty($data[1])) {
                                throw new Exception("Row $rowNumber: প্রথম নাম এবং শেষ নাম আবশ্যক");
                            }
                            
                            // Prepare student data
                            $student_data = [
                                'first_name' => trim($data[0]),
                                'last_name' => trim($data[1]),
                                'father_name' => trim($data[2] ?? ''),
                                'mother_name' => trim($data[3] ?? ''),
                                'email' => trim($data[4] ?? ''),
                                'phone' => trim($data[5] ?? ''),
                                'date_of_birth' => trim($data[6] ?? ''),
                                'gender' => trim($data[7] ?? ''),
                                'address' => trim($data[8] ?? ''),
                                'guardian_name' => trim($data[9] ?? ''),
                                'guardian_phone' => trim($data[10] ?? ''),
                                'guardian_email' => trim($data[11] ?? ''),
                                'guardian_address' => trim($data[12] ?? ''),
                                'class_id' => !empty(trim($data[13] ?? '')) ? trim($data[13]) : null,
                                'roll_number' => trim($data[14] ?? ''),
                                'admission_date' => trim($data[15] ?? date('Y-m-d')),
                                'session' => trim($data[16] ?? date('Y')),
                                'group_name' => trim($data[17] ?? '')
                            ];
                            
                            // Validate class assignment
                            if (!$student_data['class_id'] || !in_array($student_data['class_id'], $class_ids)) {
                                throw new Exception("Row $rowNumber: আপনি এই ক্লাসে ছাত্র-ছাত্রী নিয়োগ করতে পারবেন না।");
                            }
                            
                            // 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} ইতিমধ্যে ব্যবহৃত হয়েছে।");
                            }
                            
                            // Validate email if provided
                            if (!empty($student_data['email']) && !filter_var($student_data['email'], FILTER_VALIDATE_EMAIL)) {
                                throw new Exception("Row $rowNumber: অবৈধ ইমেইল ফরম্যাট");
                            }
                            
                            // Validate date of birth if provided
                            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 ফরম্যাটে হতে হবে");
                            }
                            
                            // Validate admission date
                            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');
                            }
                            
                            // Generate student ID
                            $stmt = $pdo->query("SELECT MAX(CAST(SUBSTRING(student_id, 3) AS UNSIGNED)) as max_id FROM students WHERE student_id LIKE 'ST%'");
                            $result = $stmt->fetch();
                            $new_id = ($result['max_id'] ?? 0) + 1;
                            $student_id = 'ST' . str_pad($new_id, 4, '0', STR_PAD_LEFT);
                            
                            // Create user account for student
                            $username = 'student_' . $new_id;
                            $password = substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 8);
                            $hashed_password = password_hash($password, PASSWORD_DEFAULT);
                            
                            $stmt = $pdo->prepare("INSERT INTO users (username, password, email, role, created_at) VALUES (?, ?, ?, 'student', NOW())");
                            $stmt->execute([$username, $hashed_password, $student_data['email'] ?: null]);
                            $user_id = $pdo->lastInsertId();
                            
                            // Insert student
                            $stmt = $pdo->prepare("
                                INSERT INTO students 
                                (student_id, first_name, last_name, father_name, mother_name, email, phone, address,
                                date_of_birth, gender, class_id, session, group_name, roll_number,
                                admission_date, guardian_name, guardian_phone, guardian_email, guardian_address,
                                status, user_id, created_at, updated_at)
                                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, NOW(), NOW())
                            ");
                            
                            $stmt->execute([
                                $student_id, $student_data['first_name'], $student_data['last_name'],
                                $student_data['father_name'], $student_data['mother_name'], $student_data['email'],
                                $student_data['phone'], $student_data['address'], $student_data['date_of_birth'],
                                $student_data['gender'], $student_data['class_id'], $student_data['session'],
                                $student_data['group_name'] ?: null, $student_data['roll_number'],
                                $student_data['admission_date'], $student_data['guardian_name'],
                                $student_data['guardian_phone'], $student_data['guardian_email'],
                                $student_data['guardian_address'], $user_id
                            ]);
                            
                            $upload_results[] = [
                                'row' => $rowNumber,
                                'status' => 'success',
                                'name' => $student_data['first_name'] . ' ' . $student_data['last_name'],
                                'student_id' => $student_id,
                                'username' => $username,
                                'password' => $password
                            ];
                            
                            $successful_rows++;
                            
                        } catch (Exception $e) {
                            $upload_results[] = [
                                'row' => $rowNumber,
                                'status' => 'error',
                                'error' => $e->getMessage()
                            ];
                            $failed_rows++;
                        }
                    }
                    
                    fclose($handle);
                    
                    if ($failed_rows == 0) {
                        $pdo->commit();
                        $success_message = "সফলভাবে $successful_rows জন শিক্ষার্থী যোগ করা হয়েছে!";
                    } else {
                        $pdo->rollBack();
                        $error_message = "মোট $rowNumber টি রো থেকে $failed_rows টিতে ত্রুটি পাওয়া গেছে। কোন ডেটা যোগ করা হয়নি।";
                    }
                } else {
                    throw new Exception("CSV ফাইল পড়তে সমস্যা হয়েছে");
                }
                
            } 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/jessoreboard-theme.css" rel="stylesheet">
    <style>
        .form-section {
            background: #f8f9fa;
            padding: 20px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        .bengali-text {
            font-family: 'Kalpurush', 'SolaimanLipi', 'Nikosh', sans-serif;
        }
    </style>
</head>
<body class="bengali-text">
    <!-- Navigation -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
        <div class="container-fluid">
            <a class="navbar-brand" href="<?php echo ($user_role == 'teacher') ? 'dashboard.php' : '../index.php'; ?>">
                <i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম
            </a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="bulk_upload_students.php">
                    <i class="fas fa-upload"></i> বাল্ক আপলোড
                </a>
                <a class="nav-link" href="<?php echo ($user_role == 'teacher') ? 'my_students.php' : '../students/list.php'; ?>">
                    <i class="fas fa-arrow-left"></i> ছাত্র-ছাত্রী তালিকা
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4">
        <div class="row">
            <div class="col-md-12">
                <div class="card">
                    <div class="card-header">
                        <h5><i class="fas fa-upload"></i> শিক্ষার্থী বাল্ক আপলোড</h5>
                    </div>
                    <div class="card-body">
                        <?php if ($success_message): ?>
                            <div class="alert alert-success alert-dismissible fade show" role="alert">
                                <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" role="alert">
                                <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 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="d-grid gap-2 d-md-flex justify-content-md-end">
                                <a href="my_students.php" class="btn btn-secondary">
                                    <i class="fas fa-arrow-left"></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>
                                        <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'] ?? 'অজানা ত্রুটি'; ?>
                                            <?php endif; ?>
                                        </td>
                                        <td class="bengali-text">
                                            <?php if (isset($result['status']) && $result['status'] == 'success'): ?>
                                                ইউজারনেম: <?php echo $result['username']; ?>, পাসওয়ার্ড: <?php echo $result['password']; ?>
                                            <?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-file-csv"></i> CSV টেমপ্লেট এবং নির্দেশিকা</h5>
                    </div>
                    <div class="card-body">
                        <div class="alert alert-info">
                            <h6><i class="fas fa-info-circle"></i> আপনি শুধুমাত্র আপনার সংশ্লিষ্ঠ ক্লাসের শিক্ষার্থীদের আপলোড করতে পারবেন</h6>
                            <p>আপনার সংশ্লিষ্ঠ ক্লাসসমূহ:</p>
                            <ul>
                                <?php foreach ($assigned_classes as $class): ?>
                                    <li><?php echo htmlspecialchars($class['name'] . ' - ' . $class['section']); ?></li>
                                <?php endforeach; ?>
                            </ul>
                        </div>

                        <h6>১. টেমপ্লেট ডাউনলোড করুন</h6>
                        <div class="mb-3">
                            <a href="student_upload_template.csv" class="btn btn-primary" download>
                                <i class="fas fa-download"></i> শিক্ষার্থী আপলোড টেমপ্লেট ডাউনলোড
                            </a>
                        </div>

                        <h6>২. CSV ফরম্যাট গাইড</h6>
                        <div class="table-responsive mb-3">
                            <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>First Name (প্রথম নাম)</td><td class="text-danger">হ্যাঁ</td><td>মোহাম্মদ রহিম</td></tr>
                                    <tr><td>B</td><td>Last Name (শেষ নাম)</td><td class="text-danger">হ্যাঁ</td><td>উদ্দিন</td></tr>
                                    <tr><td>C</td><td>Father Name (পিতার নাম)</td><td class="text-success">না</td><td>আলীম উদ্দিন</td></tr>
                                    <tr><td>D</td><td>Mother Name (মাতার নাম)</td><td class="text-success">না</td><td>রাবেয়া খাতুন</td></tr>
                                    <tr><td>E</td><td>Email (ইমেইল)</td><td class="text-success">না</td><td>rahim@email.com</td></tr>
                                    <tr><td>F</td><td>Phone (ফোন)</td><td class="text-success">না</td><td>01712345678</td></tr>
                                    <tr><td>G</td><td>Date of Birth (জন্ম তারিখ)</td><td class="text-success">না</td><td>2010-01-15</td></tr>
                                    <tr><td>H</td><td>Gender (লিঙ্গ)</td><td class="text-success">না</td><td>male/female</td></tr>
                                    <tr><td>I</td><td>Address (ঠিকানা)</td><td class="text-success">না</td><td>ঢাকা, বাংলাদেশ</td></tr>
                                    <tr><td>J</td><td>Guardian Name (অভিভাবকের নাম)</td><td class="text-success">না</td><td>মোহাম্মদ করিম উদ্দিন</td></tr>
                                    <tr><td>K</td><td>Guardian Phone (অভিভাবকের ফোন)</td><td class="text-success">না</td><td>01812345678</td></tr>
                                    <tr><td>L</td><td>Guardian Email (অভিভাবকের ইমেইল)</td><td class="text-success">না</td><td>karim@email.com</td></tr>
                                    <tr><td>M</td><td>Guardian Address (অভিভাবকের ঠিকানা)</td><td class="text-success">না</td><td>ঢাকা, বাংলাদেশ</td></tr>
                                    <tr><td>N</td><td>Class ID (ক্লাস আইডি) *</td><td class="text-danger">হ্যাঁ</td><td><?php echo implode(', ', $class_ids); ?></td></tr>
                                    <tr><td>O</td><td>Roll Number (রোল নম্বর)</td><td class="text-danger">হ্যাঁ</td><td>01</td></tr>
                                    <tr><td>P</td><td>Admission Date (ভর্তি তারিখ)</td><td class="text-success">না</td><td>2024-01-01</td></tr>
                                    <tr><td>Q</td><td>Session (সেশন)</td><td class="text-warning">প্রয়োজনীয়</td><td>2024</td></tr>
                                    <tr><td>R</td><td>Group Name (গ্রুপ নাম)</td><td class="text-success">না</td><td>বিজ্ঞান</td></tr>
                                </tbody>
                            </table>
                        </div>

                        <h6>৩. নির্দেশিকা</h6>
                        <ol>
                            <li><strong>টেমপ্লেট ডাউনলোড</strong> করুন এবং Excel বা Google Sheets এ খুলুন</li>
                            <li><strong>শিক্ষার্থীদের তথ্য</strong> পূরণ করুন</li>
                            <li><strong>Class ID</strong> হিসেবে শুধুমাত্র আপনার সংশ্লিষ্ঠ ক্লাস আইডি ব্যবহার করুন: <?php echo implode(', ', $class_ids); ?></li>
                            <li><strong>CSV (UTF-8)</strong> হিসেবে ফাইলটি সেভ করুন</li>
                            <li>এই পেজে ফাইলটি <strong>আপলোড</strong> করুন</li>
                        </ol>

                        <div class="alert alert-warning">
                            <h6><i class="fas fa-exclamation-triangle"></i> গুরুত্বপূর্ণ তথ্য</h6>
                            <ul>
                                <li>প্রতিটি শিক্ষার্থীর জন্য স্বয়ংক্রিয়ভাবে লগইন অ্যাকাউন্ট তৈরি হবে</li>
                                <li>লগইন তথ্য (ইউজারনেম এবং পাসওয়ার্ড) আপলোড ফলাফলে দেখানো হবে</li>
                                <li>আপনি শুধুমাত্র আপনার সংশ্লিষ্ঠ ক্লাসের শিক্ষার্থীদের আপলোড করতে পারবেন</li>
                                <li>ডুপ্লিকেট রোল নম্বর গ্রহণ করা হবে না</li>
                            </ul>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Form validation
        (function () {
            'use strict'
            var forms = document.querySelectorAll('.needs-validation')
            Array.prototype.slice.call(forms)
                .forEach(function (form) {
                    form.addEventListener('submit', function (event) {
                        if (!form.checkValidity()) {
                            event.preventDefault()
                            event.stopPropagation()
                        }
                        form.classList.add('was-validated')
                    }, false)
                })
        })()
    </script>
</body>
</html>