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

function convert_to_utf8($string) {
    return EncodingHelper::cleanBengaliText($string);
}

if (!isset($_SESSION['user_id']) || !in_array($_SESSION['user_role'], ['admin', 'teacher'])) {
    header('Location: ../login.php');
    exit();
}

$upload_summary = null;
$error_message = '';
$success_message = '';

function get_class_id_by_name($pdo, $class_name) {
    $stmt = $pdo->prepare("SELECT id FROM classes WHERE name = ?");
    $stmt->execute([$class_name]);
    $result = $stmt->fetchColumn();
    error_log("Class lookup for '{$class_name}': " . ($result ?: 'Not Found'));
    return $result;
}

function get_subject_ids_by_codes($pdo, $subject_codes_str) {
    error_log("DEBUG: Processing subject codes string: " . $subject_codes_str);
    
    if (empty($subject_codes_str)) return [];
    
    // Split by comma to get individual code segments
    $segments = array_map('trim', explode(',', $subject_codes_str));
    error_log("DEBUG: Segments after comma split: " . json_encode($segments));
    
    // Process each segment to handle both individual codes and ranges
    $all_codes = [];
    foreach ($segments as $segment) {
        error_log("DEBUG: Processing segment: " . $segment);
        
        // First check if the exact segment exists as a subject code
        $stmt_check = $pdo->prepare("SELECT id FROM subjects WHERE code = ?");
        $stmt_check->execute([$segment]);
        $exact_match = $stmt_check->fetchColumn();
        
        if ($exact_match) {
            // If exact match found, use it directly
            error_log("DEBUG: Exact match found for segment: " . $segment);
            $all_codes[] = $segment;
        } else if (strpos($segment, '_') !== false) {
            error_log("DEBUG: Found underscore, treating as range");
            // Handle range notation (e.g., "101_102" or "253_254")
            $parts = explode('_', $segment);
            error_log("DEBUG: Parts after underscore split: " . json_encode($parts));
            if (count($parts) == 2) {
                $start = (int)$parts[0];
                $end = (int)$parts[1];
                error_log("DEBUG: Range from $start to $end");
                // Add all codes in the range
                for ($i = $start; $i <= $end; $i++) {
                    $all_codes[] = (string)$i;
                    error_log("DEBUG: Adding code to all_codes: " . (string)$i);
                }
            } else {
                // If not a proper range, treat as individual code
                error_log("DEBUG: Not a proper range, treating as individual code");
                $all_codes[] = $segment;
            }
        } else {
            error_log("DEBUG: No underscore, treating as individual code");
            // Individual code
            $all_codes[] = $segment;
        }
    }
    
    // Remove duplicates
    $all_codes = array_unique($all_codes);
    error_log("DEBUG: All codes after deduplication: " . json_encode($all_codes));
    
    if (empty($all_codes)) return [];
    
    $placeholders = rtrim(str_repeat('?,', count($all_codes)), ',');
    
    $stmt = $pdo->prepare("SELECT id FROM subjects WHERE code IN ({$placeholders})");
    $stmt->execute($all_codes);
    $results = $stmt->fetchAll(PDO::FETCH_COLUMN);
    error_log("DEBUG: Subject lookup results: " . json_encode($results));
    return $results;
}

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['student_file'])) {
    $file = $_FILES['student_file'];

    if ($file['error'] !== UPLOAD_ERR_OK) {
        $error_message = "ফাইল আপলোডে ত্রুটি হয়েছে।";
    } elseif (pathinfo($file['name'], PATHINFO_EXTENSION) != 'csv') {
        $error_message = "শুধুমাত্র CSV ফাইল আপলোড করা যাবে।";
    } else {
        $file_path = $file['tmp_name'];
        
        $total_rows = 0;
        $successful_imports = 0;
        $failed_imports = 0;
        $errors = [];

        if (($handle = fopen($file_path, "r")) !== FALSE) {
            // Detect encoding and skip BOM
            $bom = fread($handle, 3);
            if ($bom !== '\xEF\xBB\xBF') {
                rewind($handle);
            }

            $header = fgetcsv($handle);
            if ($header === FALSE) {
                $error_message = "CSV ফাইলটি খালি বা সঠিক ফরম্যাটে নেই।";
                fclose($handle);
            } else {
                $header = array_map('trim', $header);
                $expected_headers = [
                    'first_name', 'last_name', 'father_name', 'mother_name', 'email', 'phone', 'address',
                    'date_of_birth', 'gender', 'religion', 'class_name', 'session', 'group_name', 'roll_number',
                    'admission_date', 'guardian_name', 'guardian_phone', 'guardian_email', 'guardian_address', 'subject_codes'
                ];

                if (count(array_diff($expected_headers, $header)) > 0) {
                    $error_message = "CSV ফাইলের হেডার সঠিক নয়। অনুগ্রহ করে টেমপ্লেট অনুসরণ করুন।";
                    fclose($handle);
                } else {
                    $header_map = array_flip($header);
                    
                    try {
                        $pdo->beginTransaction();
                        
                        while (($data = fgetcsv($handle)) !== FALSE) {
                            $total_rows++;
                            $row_data = array_map(function($value) {
                                return convert_to_utf8($value);
                            }, $data);

                            $student = [];
                            foreach ($header_map as $key => $index) {
                                $student[$key] = $row_data[$index] ?? null;
                            }

                            // Basic validation
                            if (empty($student['first_name']) || empty($student['class_name']) || empty($student['session']) || empty($student['roll_number'])) {
                                $failed_imports++;
                                $errors[] = "Row {$total_rows}: নাম, ক্লাস, সেশন এবং রোল নম্বর আবশ্যক।";
                                continue;
                            }

                            // Get Class ID
                            $class_id = get_class_id_by_name($pdo, $student['class_name']);
                            if (!$class_id) {
                                $failed_imports++;
                                $errors[] = "Row {$total_rows}: ক্লাস ‘{$student['class_name']}’ খুঁজে পাওয়া যায়নি।";
                                continue;
                            }

                            // Check for duplicate roll number
                            $stmt_check = $pdo->prepare("SELECT COUNT(*) FROM students WHERE class_id = ? AND session = ? AND COALESCE(group_name, '') = ? AND roll_number = ? AND status = 'active'");
                            $stmt_check->execute([$class_id, $student['session'], $student['group_name'] ?: '', $student['roll_number']]);
                            if ($stmt_check->fetchColumn() > 0) {
                                $failed_imports++;
                                $errors[] = "Row {$total_rows}: রোল নম্বর ‘{$student['roll_number']}’ এই ক্লাস ও সেশনে ব্যবহৃত হয়েছে।";
                                continue;
                            }
                            
                            // Generate student ID with collision avoidance
                            $year = date('Y');
                            $attempt = 1;
                            do {
                                $student_id_generated = $year . str_pad($attempt, 4, '0', STR_PAD_LEFT);
                                $stmt_check_id = $pdo->prepare("SELECT COUNT(*) FROM students WHERE student_id = ?");
                                $stmt_check_id->execute([$student_id_generated]);
                                $exists = $stmt_check_id->fetchColumn() > 0;
                                $attempt++;
                                // Safety check to prevent infinite loop
                                if ($attempt > 9999) {
                                    throw new Exception("Unable to generate unique student ID after 9999 attempts");
                                }
                            } while ($exists);

                            // Insert student
                            $stmt_insert = $pdo->prepare("
                                INSERT INTO students (
                                    student_id, first_name, last_name, father_name, mother_name, email, phone, address,
                                    date_of_birth, gender, religion, class_id, session, group_name, roll_number, admission_date,
                                    guardian_name, guardian_phone, guardian_email, guardian_address, status
                                ) VALUES (
                                    :student_id, :first_name, :last_name, :father_name, :mother_name, :email, :phone, :address,
                                    :date_of_birth, :gender, :religion, :class_id, :session, :group_name, :roll_number, :admission_date,
                                    :guardian_name, :guardian_phone, :guardian_email, :guardian_address, 'active'
                                )
                            ");
                            
                            $stmt_insert->execute([
                                ':student_id' => $student_id_generated,
                                ':first_name' => $student['first_name'],
                                ':last_name' => $student['last_name'],
                                ':father_name' => $student['father_name'],
                                ':mother_name' => $student['mother_name'],
                                ':email' => $student['email'],
                                ':phone' => $student['phone'],
                                ':address' => $student['address'],
                                ':date_of_birth' => !empty($student['date_of_birth']) ? $student['date_of_birth'] : null,
                                ':gender' => $student['gender'],
                                ':religion' => !empty($student['religion']) ? $student['religion'] : null,
                                ':class_id' => $class_id,
                                ':session' => $student['session'],
                                ':group_name' => $student['group_name'],
                                ':roll_number' => $student['roll_number'],
                                ':admission_date' => !empty($student['admission_date']) ? $student['admission_date'] : date('Y-m-d'),
                                ':guardian_name' => $student['guardian_name'],
                                ':guardian_phone' => $student['guardian_phone'],
                                ':guardian_email' => $student['guardian_email'],
                                ':guardian_address' => $student['guardian_address']
                            ]);
                            
                            $student_db_id = $pdo->lastInsertId();

                            // Get and insert subjects
                            $subject_ids = get_subject_ids_by_codes($pdo, $student['subject_codes']);
                            if (!empty($subject_ids)) {
                                $stmt_subj = $pdo->prepare("INSERT INTO student_subjects (student_id, subject_id, is_fourth_subject) VALUES (?, ?, ?)");
                                foreach ($subject_ids as $subject_id) {
                                    try {
                                        $stmt_subj->execute([$student_db_id, $subject_id, 0]);
                                    } catch (PDOException $e) {
                                        // Ignore duplicate subject error
                                        if (isset($e->errorInfo[1]) && $e->errorInfo[1] != 1062) {
                                            throw $e;
                                        }
                                    }
                                }
                            }
                            
                            $successful_imports++;
                        }
                        
                        $pdo->commit();
                        $success_message = "ফাইল সফলভাবে প্রসেস করা হয়েছে।";
                        
                    } catch (Exception $e) {
                        $pdo->rollBack();
                        $error_message = "ডাটাবেস ত্রুটি: " . $e->getMessage();
                        $failed_imports = $total_rows - $successful_imports;
                    }
                    
                    fclose($handle);
                    
                    $upload_summary = [
                        'total_rows' => $total_rows,
                        'successful_imports' => $successful_imports,
                        'failed_imports' => $failed_imports,
                        'errors' => $errors
                    ];
                }
            }
        } else {
            $error_message = "ফাইলটি খোলা যায়নি।";
        }
    }
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <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>
    <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="card">
            <div class="card-header">
                <h3>ছাত্র-ছাত্রী ও তাদের বিষয় আপলোড করুন</h3>
            </div>
            <div class="card-body">
                <?php if ($error_message): ?>
                    <div class="alert alert-danger"><?php echo $error_message; ?></div>
                <?php endif; ?>
                <?php if ($success_message): ?>
                    <div class="alert alert-success"><?php echo $success_message; ?></div>
                <?php endif; ?>

                <div class="mb-4 p-3 border rounded bg-light">
                    <h5>নির্দেশনা:</h5>
                    <ol>
                        <li>প্রথমে নিচের বোতামে ক্লিক করে CSV টেমপ্লেট ফাইলটি ডাউনলোড করুন।</li>
                        <li>ডাউনলোড করা ফাইলে ছাত্র-ছাত্রীদের তথ্য পূরণ করুন। প্রতিটি ছাত্র-ছাত্রীর জন্য একটি সারি ব্যবহার করুন।</li>
                        <li><code>subject_codes</code> কলামে কমা (,) দিয়ে একাধিক বিষয়ের কোড লিখুন (যেমন: 101,107,265_266)। কিছু বিষয়ের জন্য কোড হিসেবে রেঞ্জ ব্যবহার করা হয় (যেমন: 101_102) যা একটি বিষয়কে নির্দেশ করে।</li>
                        <li><code>session</code> কলামে সেশনের নাম লিখুন (যেমন: 2025)।</li>
                        <li><code>class_name</code> কলামে ক্লাসের নাম লিখুন (যেমন: ONE, দশম শ্রেণী)।</li>
                        <li>ফাইলটি UTF-8 এনকোডিং-এ সংরক্ষণ করুন।</li>
                        <li>নিচের ফর্ম ব্যবহার করে আপনার পূরণ করা CSV ফাইলটি আপলোড করুন।</li>
                    </ol>
                    <div class="alert alert-info mt-3">
                        <strong>গুরুত্বপূর্ণ:</strong> 
                        <ul class="mb-0">
                            <li>রোল নম্বর ক্লাস, সেশন ও গ্রুপের ভিত্তিতে অনন্য হতে হবে</li>
                            <li>বিষয়ের কোডগুলি সিস্টেমে বিদ্যমান হতে হবে</li>
                            <li>ক্লাসের নাম সিস্টেমে বিদ্যমান হতে হবে</li>
                        </ul>
                    </div>
                    <a href="templates/students_with_subjects_template.csv" class="btn btn-primary" download>
                        <i class="fas fa-download"></i> CSV টেমপ্লেট ডাউনলোড করুন
                    </a>
                </div>

                <form action="" method="post" enctype="multipart/form-data">
                    <div class="mb-3">
                        <label for="student_file" class="form-label">CSV ফাইল নির্বাচন করুন</label>
                        <input type="file" class="form-control" id="student_file" name="student_file" accept=".csv" required>
                    </div>
                    <button type="submit" class="btn btn-success">
                        <i class="fas fa-upload"></i> আপলোড ও প্রসেস করুন
                    </button>
                </form>

                <?php if ($upload_summary): ?>
                <div class="mt-4">
                    <h4>আপলোড সারাংশ</h4>
                    <table class="table table-bordered">
                        <tbody>
                            <tr>
                                <th>মোট সারি</th>
                                <td><?php echo $upload_summary['total_rows']; ?></td>
                            </tr>
                            <tr>
                                <th class="text-success">সফলভাবে ইম্পোর্ট হয়েছে</th>
                                <td class="text-success"><?php echo $upload_summary['successful_imports']; ?></td>
                            </tr>
                            <tr>
                                <th class="text-danger">ব্যর্থ হয়েছে</th>
                                <td class="text-danger"><?php echo $upload_summary['failed_imports']; ?></td>
                            </tr>
                        </tbody>
                    </table>

                    <?php if (!empty($upload_summary['errors'])): ?>
                        <h5>ত্রুটির বিবরণ:</h5>
                        <div class="alert alert-warning">
                            <ul>
                                <?php foreach ($upload_summary['errors'] as $err): ?>
                                    <li><?php echo htmlspecialchars($err); ?></li>
                                <?php endforeach; ?>
                            </ul>
                        </div>
                    <?php endif; ?>
                </div>
                <?php endif; ?>
            </div>
        </div>
    </div>

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