<?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 = [];

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    $create_users = isset($_POST['create_users']);

    if ($_FILES['csv_file']['error'] == 0) {
        $file_tmp = $_FILES['csv_file']['tmp_name'];
        $file_type = $_FILES['csv_file']['type'];

        // Allow common CSV MIME types (and text/plain from Excel exports)
        $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();

                // Get file encoding info for message
                $encodingInfo = EncodingHelper::getFileEncodingInfo($file_tmp);

                // Process CSV with Bengali support
                $csvResult = processCsvWithBengaliSupport($file_tmp, function($data, $rowNumber, $headers) use ($pdo, $create_users) {
                    // Map CSV columns with Bengali text cleaning
                    // Columns expected (same order as teachers_template.csv):
                    // 0: Teacher ID, 1: First Name, 2: Last Name, 3: Email, 4: Phone,
                    // 5: Date of Birth, 6: Gender, 7: Address, 8: Qualification,
                    // 9: Experience, 10: Subject Specialization, 11: Joining Date, 12: Salary, 13: Designation
                    $teacher = [
                        'teacher_id' => EncodingHelper::cleanBengaliText($data[0] ?? ''),
                        'first_name' => EncodingHelper::cleanBengaliText($data[1] ?? ''),
                        'last_name' => EncodingHelper::cleanBengaliText($data[2] ?? ''),
                        'email' => trim($data[3] ?? ''),
                        'phone' => trim($data[4] ?? ''),
                        'date_of_birth' => trim($data[5] ?? ''),
                        'gender' => trim($data[6] ?? ''),
                        'address' => EncodingHelper::cleanBengaliText($data[7] ?? ''),
                        'qualification' => EncodingHelper::cleanBengaliText($data[8] ?? ''),
                        'experience' => EncodingHelper::cleanBengaliText($data[9] ?? ''),
                        'subject_specialization' => EncodingHelper::cleanBengaliText($data[10] ?? ''),
                        'joining_date' => trim($data[11] ?? ''),
                        'salary' => trim($data[12] ?? ''),
                        'designation' => EncodingHelper::cleanBengaliText($data[13] ?? '')
                    ];

                    // Basic validation
                    if (empty($teacher['teacher_id']) || empty($teacher['first_name'])) {
                        throw new Exception("Row $rowNumber: Teacher ID এবং First Name আবশ্যক");
                    }

                    // Unique employee_id (mapped from Teacher ID column)
                    $employee_id = $teacher['teacher_id'];
                    $chk = $pdo->prepare('SELECT id FROM teachers WHERE employee_id = ?');
                    $chk->execute([$employee_id]);
                    if ($chk->fetch()) {
                        throw new Exception("Row $rowNumber: Employee ID '{$employee_id}' ইতিমধ্যে বিদ্যমান");
                    }

                    // Email format
                    if (!empty($teacher['email']) && !filter_var($teacher['email'], FILTER_VALIDATE_EMAIL)) {
                        throw new Exception("Row $rowNumber: অবৈধ ইমেইল ফরম্যাট");
                    }

                    // Dates validation (support MM/DD/YYYY from template)
                    if (!empty($teacher['date_of_birth'])) {
                        $dob = DateTime::createFromFormat('m/d/Y', $teacher['date_of_birth']) ?: DateTime::createFromFormat('Y-m-d', $teacher['date_of_birth']);
                        if (!$dob) {
                            throw new Exception("Row $rowNumber: জন্ম তারিখ YYYY-MM-DD বা MM/DD/YYYY ফরম্যাটে হতে হবে");
                        }
                        $teacher['date_of_birth'] = $dob->format('Y-m-d');
                    }
                    if (!empty($teacher['joining_date'])) {
                        $jd = DateTime::createFromFormat('m/d/Y', $teacher['joining_date']) ?: DateTime::createFromFormat('Y-m-d', $teacher['joining_date']);
                        $teacher['joining_date'] = $jd ? $jd->format('Y-m-d') : date('Y-m-d');
                    } else {
                        $teacher['joining_date'] = date('Y-m-d');
                    }

                    // Salary numeric
                    if (!empty($teacher['salary']) && !is_numeric($teacher['salary'])) {
                        throw new Exception("Row $rowNumber: বেতন সংখ্যায় হতে হবে");
                    }

                    // Map experience string to years (e.g., "2 Years" -> 2)
                    $experience_years = 0;
                    if (!empty($teacher['experience'])) {
                        if (preg_match('/(\d+(?:\.\d+)?)/', $teacher['experience'], $m)) {
                            $experience_years = (int)$m[1];
                        }
                    }

                    // Optional user account
                    $user_id = null;
                    if ($create_users) {
                        $username = strtolower($employee_id);
                        $password = password_hash($employee_id, PASSWORD_DEFAULT);
                        $insU = $pdo->prepare('INSERT INTO users (username, password, email, role, status) VALUES (?, ?, ?, "teacher", "active")');
                        $insU->execute([$username, $password, $teacher['email']]);
                        $user_id = $pdo->lastInsertId();
                    }

                    // Insert teacher (match current schema)
                    $insT = $pdo->prepare('INSERT INTO teachers (user_id, employee_id, first_name, last_name, email, phone, address, date_of_birth, gender, qualification, experience_years, joining_date, salary, photo, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, "active")');
                    $insT->execute([
                        $user_id,
                        $employee_id,
                        $teacher['first_name'],
                        $teacher['last_name'],
                        $teacher['email'],
                        $teacher['phone'],
                        $teacher['address'],
                        $teacher['date_of_birth'] ?: null,
                        $teacher['gender'] ?: null,
                        $teacher['qualification'] ?: null,
                        $experience_years,
                        $teacher['joining_date'],
                        $teacher['salary'] ?: 0,
                    ]);

                    return [
                        'row' => $rowNumber,
                        'status' => 'success',
                        'name' => $teacher['first_name'] . ' ' . $teacher['last_name'],
                        'teacher_id' => $teacher['teacher_id']
                    ];
                });

                if ($csvResult['success']) {
                    $success_count = $csvResult['successful_rows'];
                    $error_count = $csvResult['failed_rows'];
                    // Merge detailed results for display
                    $upload_results = array_merge($csvResult['results'], $csvResult['errors']);

                    if ($error_count == 0) {
                        $pdo->commit();
                        $success_message = "সফলভাবে $success_count জন শিক্ষক যোগ করা হয়েছে! (এনকোডিং: " . ($encodingInfo['detected_encoding'] ?? 'unknown') . ")";
                    } 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">
    <!-- Navigation -->
    <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">
            <!-- Sidebar -->
            <div class="col-md-3">
                <div class="card">
                    <div class="card-header">
                        <h5><i class="fas fa-chalkboard-teacher"></i> শিক্ষক আপলোড</h5>
                    </div>
                    <div class="card-body">
                        <div class="d-grid gap-2">
                            <a href="templates/teachers_template.csv" class="btn btn-info">
                                <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 (UTF-8) সেভ করুন</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>

            <!-- Main Content -->
            <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-7">
                                    <!-- File Upload -->
                                    <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-5">
                                    <div class="form-check mt-4 pt-2">
                                        <input class="form-check-input" type="checkbox" id="create_users" name="create_users">
                                        <label class="form-check-label" for="create_users">ব্যবহারকারী একাউন্ট তৈরি করুন (username/password = Teacher ID)</label>
                                    </div>
                                </div>
                            </div>

                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-upload"></i> আপলোড করুন
                            </button>
                        </form>

                        <?php if (!empty($upload_results)): ?>
                        <hr>
                        <h5><i class="fas fa-list"></i> আপলোড ফলাফল</h5>
                        <div class="table-responsive">
                            <table class="table table-sm table-bordered align-middle">
                                <thead class="table-light">
                                    <tr>
                                        <th>রো</th>
                                        <th>স্ট্যাটাস</th>
                                        <th>তথ্য/বার্তা</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($upload_results as $r): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($r['row']); ?></td>
                                            <td>
                                                <?php if (($r['status'] ?? '') === 'success'): ?>
                                                    <span class="badge bg-success">সফল</span>
                                                <?php else: ?>
                                                    <span class="badge bg-danger">ত্রুটি</span>
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <?php if (($r['status'] ?? '') === 'success'): ?>
                                                    <?php echo htmlspecialchars(($r['name'] ?? '') . ' (' . ($r['teacher_id'] ?? '') . ')'); ?>
                                                <?php else: ?>
                                                    <?php echo htmlspecialchars($r['error'] ?? $r['message'] ?? ''); ?>
                                                <?php endif; ?>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>
    // Bootstrap 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>