<?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 subjects for reference
$stmt = $pdo->query("SELECT id, name, code FROM subjects WHERE status = 'active' ORDER BY name");
$subjects = $stmt->fetchAll();

if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    $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'];
        
        // Validate file type
        $allowed_types = ['text/csv', 'application/csv', 'application/vnd.ms-excel'];
        if (!in_array($file_type, $allowed_types)) {
            $error_message = 'শুধুমাত্র CSV ফাইল আপলোড করা যাবে';
        } else {
            try {
                // Set locale for proper CSV reading with Bengali characters
                setlocale(LC_ALL, 'en_US.UTF-8');
                
                $handle = fopen($file_tmp, 'r');
                if ($handle !== FALSE) {
                    // Skip header row
                    $header = fgetcsv($handle, 0, ',', '"', '"');
                    
                    $row_count = 0;
                    $success_count = 0;
                    $error_count = 0;
                    $errors = [];
                    
                    $pdo->beginTransaction();
                    
                    while (($data = fgetcsv($handle, 0, ',', '"', '"')) !== FALSE) {
                        // Convert encoding if needed
                        $data = array_map(function($item) {
                            // Check if string is not UTF-8 and convert
                            if (!mb_check_encoding($item, 'UTF-8')) {
                                return mb_convert_encoding($item, 'UTF-8', 'auto');
                            }
                            return $item;
                        }, $data);
                        $row_count++;
                        
                        try {
                            // Map CSV columns
                            $teacher_data = [
                                'teacher_id' => trim($data[0]),
                                'first_name' => trim($data[1]),
                                'last_name' => trim($data[2]),
                                'email' => trim($data[3]),
                                'phone' => trim($data[4]),
                                'date_of_birth' => trim($data[5]),
                                'gender' => trim($data[6]),
                                'address' => trim($data[7]),
                                'qualification' => trim($data[8]),
                                'experience' => trim($data[9]),
                                'subject_specialization' => trim($data[10]),
                                'joining_date' => trim($data[11]),
                                'salary' => trim($data[12]),
                                'designation' => trim($data[13])
                            ];
                            
                            // Validate required fields
                            if (empty($teacher_data['teacher_id']) || empty($teacher_data['first_name'])) {
                                throw new Exception("Row $row_count: Teacher ID এবং First Name আবশ্যক");
                            }
                            
                            // Check if employee_id already exists (map Teacher ID to employee_id)
                            $employee_id = $teacher_data['teacher_id'];
                            $stmt = $pdo->prepare("SELECT id FROM teachers WHERE employee_id = ?");
                            $stmt->execute([$employee_id]);
                            if ($stmt->fetch()) {
                                throw new Exception("Row $row_count: Employee ID '{$employee_id}' ইতিমধ্যে বিদ্যমান");
                            }
                            
                            // Validate email format
                            if (!empty($teacher_data['email']) && !filter_var($teacher_data['email'], FILTER_VALIDATE_EMAIL)) {
                                throw new Exception("Row $row_count: অবৈধ ইমেইল ফরম্যাট");
                            }
                            
                            // Validate date format
                            if (!empty($teacher_data['date_of_birth'])) {
                                $dt = DateTime::createFromFormat('m/d/Y', $teacher_data['date_of_birth']) ?: DateTime::createFromFormat('Y-m-d', $teacher_data['date_of_birth']);
                                if (!$dt) {
                                    throw new Exception("Row $row_count: জন্ম তারিখ YYYY-MM-DD বা MM/DD/YYYY ফরম্যাটে হতে হবে");
                                }
                                $teacher_data['date_of_birth'] = $dt->format('Y-m-d');
                            }
                            
                            if (!empty($teacher_data['joining_date'])) {
                                $dt = DateTime::createFromFormat('m/d/Y', $teacher_data['joining_date']) ?: DateTime::createFromFormat('Y-m-d', $teacher_data['joining_date']);
                                $teacher_data['joining_date'] = $dt ? $dt->format('Y-m-d') : date('Y-m-d');
                            } else {
                                $teacher_data['joining_date'] = date('Y-m-d');
                            }
                            
                            // Validate salary
                            if (!empty($teacher_data['salary']) && !is_numeric($teacher_data['salary'])) {
                                throw new Exception("Row $row_count: বেতন সংখ্যায় হতে হবে");
                            }
                            
                            // Map experience like "2 Years" -> 2 years
                            $experience_years = 0;
                            if (!empty($teacher_data['experience'])) {
                                if (preg_match('/(\d+(?:\.\d+)?)/', $teacher_data['experience'], $m)) {
                                    $experience_years = (int)$m[1];
                                }
                            }
                            
                            // Create user account if requested
                            $user_id = null;
                            if ($create_users) {
                                $username = strtolower($employee_id);
                                $password = password_hash($employee_id, PASSWORD_DEFAULT);
                                
                                $stmt = $pdo->prepare("
                                    INSERT INTO users (username, password, email, role, status) 
                                    VALUES (?, ?, ?, 'teacher', 'active')
                                ");
                                $stmt->execute([$username, $password, $teacher_data['email']]);
                                $user_id = $pdo->lastInsertId();
                            }
                            
                            // Insert teacher (match current schema)
                            $stmt = $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')
                            ");
                            
                            $stmt->execute([
                                $user_id,
                                $employee_id,
                                $teacher_data['first_name'],
                                $teacher_data['last_name'],
                                $teacher_data['email'],
                                $teacher_data['phone'],
                                $teacher_data['address'],
                                $teacher_data['date_of_birth'] ?: null,
                                $teacher_data['gender'] ?: null,
                                $teacher_data['qualification'] ?: null,
                                $experience_years,
                                $teacher_data['joining_date'],
                                $teacher_data['salary'] ?: 0,
                            ]);
                            
                            $success_count++;
                            $upload_results[] = [
                                'row' => $row_count,
                                'status' => 'success',
                                'name' => $teacher_data['first_name'] . ' ' . $teacher_data['last_name'],
                                'teacher_id' => $teacher_data['teacher_id']
                            ];
                            
                        } catch (Exception $e) {
                            $error_count++;
                            $errors[] = $e->getMessage();
                            $upload_results[] = [
                                'row' => $row_count,
                                'status' => 'error',
                                'message' => $e->getMessage()
                            ];
                        }
                    }
                    
                    fclose($handle);
                    
                    if ($error_count == 0) {
                        $pdo->commit();
                        $success_message = "সফলভাবে $success_count জন শিক্ষক যোগ করা হয়েছে!";
                    } else {
                        $pdo->rollBack();
                        $error_message = "মোট $row_count টি রো থেকে $error_count টিতে ত্রুটি পাওয়া গেছে। কোন ডেটা যোগ করা হয়নি।";
                    }
                } else {
                    $error_message = 'ফাইল পড়তে সমস্যা হয়েছে';
                }
            } 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>
    <!-- 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>
                            <ol class="small mb-0">
                                <li>টেমপ্লেট ডাউনলোড করুন</li>
                                <li>Excel/CSV এ তথ্য পূরণ করুন</li>
                                <li>CSV হিসেবে সেভ করুন</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-8">
                                    <!-- 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" required>
                                        <div class="invalid-feedback">অনুগ্রহ করে একটি CSV ফাইল নির্বাচন করুন</div>
                                        <small class="form-text text-muted">শুধুমাত্র CSV ফাইল সমর্থিত</small>
                                    </div>
                                </div>
                                
                                <div class="col-md-4">
                                    <!-- Options -->
                                    <div class="mb-3">
                                        <label class="form-label">অপশন</label>
                                        <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">
                                            চেক করলে প্রতিটি শিক্ষকের জন্য লগইন অ্যাকাউন্ট তৈরি হবে
                                        </small>
                                    </div>
                                </div>
                            </div>

                            <!-- Submit Button -->
                            <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-info">
                                    <i class="fas fa-upload"></i> আপলোড করুন
                                </button>
                            </div>
                        </form>
                    </div>
                </div>

                <!-- Upload Results -->
                <?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 ($result['status'] == 'success'): ?>
                                                    <span class="badge bg-success">সফল</span>
                                                <?php else: ?>
                                                    <span class="badge bg-danger">ব্যর্থ</span>
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <?php if ($result['status'] == 'success'): ?>
                                                    <?php echo $result['name'] . ' (' . $result['teacher_id'] . ')'; ?>
                                                <?php else: ?>
                                                    <?php echo $result['message']; ?>
                                                <?php endif; ?>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <?php endif; ?>

                <!-- CSV Format Guide -->
                <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>Teacher ID</td><td class="text-danger">হ্যাঁ</td><td>T001</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>Email</td><td class="text-success">না</td><td>ahmad@email.com</td></tr>
                                    <tr><td>E</td><td>Phone</td><td class="text-success">না</td><td>01712345678</td></tr>
                                    <tr><td>F</td><td>Date of Birth</td><td class="text-success">না</td><td>1985-01-15</td></tr>
                                    <tr><td>G</td><td>Gender</td><td class="text-success">না</td><td>male/female</td></tr>
                                    <tr><td>H</td><td>Address</td><td class="text-success">না</td><td>ঢাকা, বাংলাদেশ</td></tr>
                                    <tr><td>I</td><td>Qualification</td><td class="text-success">না</td><td>M.A in English</td></tr>
                                    <tr><td>J</td><td>Experience</td><td class="text-success">না</td><td>5 years</td></tr>
                                    <tr><td>K</td><td>Subject Specialization</td><td class="text-success">না</td><td>English</td></tr>
                                    <tr><td>L</td><td>Joining Date</td><td class="text-success">না</td><td>2024-01-01</td></tr>
                                    <tr><td>M</td><td>Salary</td><td class="text-success">না</td><td>25000</td></tr>
                                    <tr><td>N</td><td>Designation</td><td class="text-success">না</td><td>Assistant Teacher</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>
    <script>
        // Form validation
        (function() {
            'use strict';
            window.addEventListener('load', function() {
                var forms = document.getElementsByClassName('needs-validation');
                var validation = Array.prototype.filter.call(forms, function(form) {
                    form.addEventListener('submit', function(event) {
                        if (form.checkValidity() === false) {
                            event.preventDefault();
                            event.stopPropagation();
                        }
                        form.classList.add('was-validated');
                    }, false);
                });
            }, false);
        })();
    </script>
</body>
</html>