<?php
// Ensure database connection is active
if (!isset($connection)) {
    require_once("../public/connection.php");
}

$import_results = null;

// Fetch school initials for auto-generating admission numbers if blank
$school_initial = "EDM";
$admin_query = mysqli_query($connection, "SELECT adm_no_initials FROM administratives LIMIT 1");
if ($admin_query && mysqli_num_rows($admin_query) > 0) {
    $admin_row = mysqli_fetch_assoc($admin_query);
    if (!empty($admin_row['adm_no_initials'])) {
        $school_initial = trim($admin_row['adm_no_initials']);
    }
}

// Fetch all valid classes and their levels into a map
$classes_map = [];
$cls_res = mysqli_query($connection, "SELECT `classes`, `level` FROM `classes`");
if ($cls_res) {
    while ($cr = mysqli_fetch_assoc($cls_res)) {
        $classes_map[trim(strtolower($cr['classes']))] = [
            'name' => trim($cr['classes']),
            'level' => trim($cr['level'])
        ];
    }
}

// Handle Form Submission
if (isset($_POST['import_students_btn'])) {
    $target_class_override = trim($_POST['target_class'] ?? '');
    $password_policy = trim($_POST['password_policy'] ?? 'default_pin'); // 'default_pin' or 'surname'
    
    if (empty($_FILES['student_csv']['tmp_name'])) {
        $import_results = [
            'status' => 'error',
            'message' => 'Please select a valid CSV file to upload.'
        ];
    } else {
        $file_name = $_FILES['student_csv']['name'];
        $file_ext = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));

        if ($file_ext !== 'csv' && $file_ext !== 'txt') {
            $import_results = [
                'status' => 'error',
                'message' => 'Invalid file format. Please upload a standard comma-separated (.csv) file.'
            ];
        } else {
            $tmp_path = $_FILES['student_csv']['tmp_name'];
            $file_handle = fopen($tmp_path, 'r');

            if (!$file_handle) {
                $import_results = [
                    'status' => 'error',
                    'message' => 'Could not open uploaded file. Please try again.'
                ];
            } else {
                $total_rows = 0;
                $inserted_count = 0;
                $skipped_count = 0;
                $errors = [];
                $successes = [];

                // Prepare insert statement
                $insert_stmt = $connection->prepare("
                    INSERT INTO students (
                        gender, firstname, othername, lastname, dob, height, weight, club, 
                        health_conditions, address, guardian_name, guardian_occupation, 
                        guardian_phone, state, lga, nationality, class, level, reg_number, 
                        role, gen_password, passport, date_of_reg, status
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ");

                // Check duplicate statement
                $check_stmt = $connection->prepare("SELECT id FROM students WHERE reg_number = ? LIMIT 1");

                $row_idx = 0;
                $current_year = date('y');

                while (($row_data = fgetcsv($file_handle, 5000, ",")) !== FALSE) {
                    $row_idx++;

                    // Strip potential UTF-8 BOM from first element on row 1
                    if ($row_idx === 1 && !empty($row_data[0])) {
                        $row_data[0] = preg_replace('/^\xEF\xBB\xBF/', '', $row_data[0]);
                    }

                    // Skip empty rows
                    if (empty($row_data) || (count($row_data) === 1 && trim($row_data[0]) === '')) {
                        continue;
                    }

                    // Skip header row if detected
                    $col0 = strtolower(trim($row_data[0] ?? ''));
                    $col1 = strtolower(trim($row_data[1] ?? ''));
                    $col3 = strtolower(trim($row_data[3] ?? ''));
                    if ($row_idx === 1 && (
                        strpos($col0, 'admission') !== false || 
                        strpos($col0, 'reg') !== false || 
                        strpos($col1, 'first') !== false || 
                        strpos($col3, 'last') !== false ||
                        strpos($col1, 'name') !== false
                    )) {
                        continue;
                    }

                    $total_rows++;

                    // Extract and sanitize columns from CSV
                    $raw_reg_number = trim($row_data[0] ?? '');
                    $firstname = ucwords(strtolower(trim($row_data[1] ?? '')));
                    $othername = ucwords(strtolower(trim($row_data[2] ?? '')));
                    $lastname  = ucwords(strtolower(trim($row_data[3] ?? '')));
                    $raw_gender = trim($row_data[4] ?? '');
                    $raw_class  = trim($row_data[5] ?? '');
                    $dob        = trim($row_data[6] ?? '');
                    $address    = trim($row_data[7] ?? '');
                    $guardian_name = ucwords(strtolower(trim($row_data[8] ?? '')));
                    $guardian_phone = trim($row_data[9] ?? '');
                    $guardian_occupation = ucwords(strtolower(trim($row_data[10] ?? '')));
                    $state      = trim($row_data[11] ?? '');
                    $lga        = trim($row_data[12] ?? '');
                    $height     = !empty(trim($row_data[13] ?? '')) ? trim($row_data[13]) : 'Average';
                    $weight     = !empty(trim($row_data[14] ?? '')) ? trim($row_data[14]) : 'Average';
                    $club       = !empty(trim($row_data[15] ?? '')) ? trim($row_data[15]) : 'None';
                    $health     = !empty(trim($row_data[16] ?? '')) ? trim($row_data[16]) : 'None';
                    $nationality= !empty(trim($row_data[17] ?? '')) ? trim($row_data[17]) : 'Nigerian';

                    // 1. Validation: First name and Last name are required
                    if (empty($firstname) || empty($lastname)) {
                        $skipped_count++;
                        $errors[] = "Row #{$row_idx}: Missing First Name or Last Name.";
                        continue;
                    }

                    // 2. Gender normalization
                    $gender = 'Male';
                    if (stripos($raw_gender, 'f') === 0) {
                        $gender = 'Female';
                    } elseif (stripos($raw_gender, 'm') === 0) {
                        $gender = 'Male';
                    }

                    // 3. Determine Class & Level
                    $effective_class = '';
                    $effective_level = '';

                    if (!empty($target_class_override) && $target_class_override !== 'AUTO') {
                        $class_key = strtolower($target_class_override);
                        if (isset($classes_map[$class_key])) {
                            $effective_class = $classes_map[$class_key]['name'];
                            $effective_level = $classes_map[$class_key]['level'];
                        } else {
                            $effective_class = $target_class_override;
                            $effective_level = 'PRIMARY';
                        }
                    } else {
                        // Use class specified in row
                        $class_key = strtolower($raw_class);
                        if (isset($classes_map[$class_key])) {
                            $effective_class = $classes_map[$class_key]['name'];
                            $effective_level = $classes_map[$class_key]['level'];
                        } elseif (!empty($raw_class)) {
                            // Check for partial match (e.g. 'JSS1' -> 'JSS 1')
                            $found = false;
                            foreach ($classes_map as $ck => $cinfo) {
                                if (str_replace(' ', '', $ck) === str_replace(' ', '', $class_key)) {
                                    $effective_class = $cinfo['name'];
                                    $effective_level = $cinfo['level'];
                                    $found = true;
                                    break;
                                }
                            }
                            if (!$found) {
                                $skipped_count++;
                                $errors[] = "Row #{$row_idx} ({$firstname} {$lastname}): Class '{$raw_class}' is not registered in the system.";
                                continue;
                            }
                        } else {
                            $skipped_count++;
                            $errors[] = "Row #{$row_idx} ({$firstname} {$lastname}): No class specified.";
                            continue;
                        }
                    }

                    // 4. Admission / Registration Number Handling
                    $reg_number = $raw_reg_number;
                    if (empty($reg_number)) {
                        // Auto-generate unique registration number: EDM/YY/XXXXXX
                        $is_unique = false;
                        $attempts = 0;
                        while (!$is_unique && $attempts < 10) {
                            $attempts++;
                            $rand_num = str_pad(mt_rand(1, 999999), 6, '0', STR_PAD_LEFT);
                            $candidate_reg = "{$school_initial}/{$current_year}/{$rand_num}";
                            
                            $check_stmt->bind_param("s", $candidate_reg);
                            $check_stmt->execute();
                            $check_stmt->store_result();
                            if ($check_stmt->num_rows === 0) {
                                $reg_number = $candidate_reg;
                                $is_unique = true;
                            }
                        }
                    } else {
                        // Check if provided reg_number already exists
                        $check_stmt->bind_param("s", $reg_number);
                        $check_stmt->execute();
                        $check_stmt->store_result();
                        if ($check_stmt->num_rows > 0) {
                            $skipped_count++;
                            $errors[] = "Row #{$row_idx} ({$firstname} {$lastname}): Admission Number '{$reg_number}' already exists in database.";
                            continue;
                        }
                    }

                    // 5. Password Generation
                    $raw_password = '1234';
                    if ($password_policy === 'surname' && !empty($lastname)) {
                        $raw_password = strtolower($lastname);
                    }
                    $hashed_password = password_hash($raw_password, PASSWORD_DEFAULT);

                    // 6. Defaults
                    $role_id = 12; // Student Role
                    $passport_default = 'uploads/students/none.jpg';
                    $reg_date = date('Y-m-d');
                    $student_status = 'active';

                    // 7. Insert Student
                    $insert_stmt->bind_param(
                        "sssssssssssssssssssissss",
                        $gender,
                        $firstname,
                        $othername,
                        $lastname,
                        $dob,
                        $height,
                        $weight,
                        $club,
                        $health,
                        $address,
                        $guardian_name,
                        $guardian_occupation,
                        $guardian_phone,
                        $state,
                        $lga,
                        $nationality,
                        $effective_class,
                        $effective_level,
                        $reg_number,
                        $role_id,
                        $hashed_password,
                        $passport_default,
                        $reg_date,
                        $student_status
                    );

                    if ($insert_stmt->execute()) {
                        $inserted_count++;
                        $successes[] = "{$firstname} {$lastname} ({$effective_class}) - Reg: {$reg_number}";
                    } else {
                        $skipped_count++;
                        $errors[] = "Row #{$row_idx} ({$firstname} {$lastname}): DB Error - " . $insert_stmt->error;
                    }
                }

                fclose($file_handle);
                $insert_stmt->close();
                $check_stmt->close();

                $import_results = [
                    'status' => ($inserted_count > 0) ? 'success' : 'warning',
                    'total_rows' => $total_rows,
                    'inserted_count' => $inserted_count,
                    'skipped_count' => $skipped_count,
                    'errors' => $errors,
                    'successes' => $successes
                ];
            }
        }
    }
}
?>

<div class='page-title'>
  <div>
    <h1><i class='fa fa-graduation-cap'></i> Student Management</h1>
    <p>Mass Student Onboarding & CSV Bulk Import</p>
  </div>
  <div>
    <ul class='breadcrumb'>
      <li><a href='dashboard'><i class='fa fa-home fa-lg'></i></a></li>
      <li><a href='view_students'>Student Management</a></li>
      <li><a href='#'>Import Students</a></li>
    </ul>
  </div>
</div>

<?php if ($import_results): ?>
    <?php if (isset($import_results['status']) && $import_results['status'] === 'error'): ?>
        <div class='alert alert-danger alert-dismissible' style='border-radius: 8px;'>
            <button type='button' class='close' data-dismiss='alert'>&times;</button>
            <h4><i class='fa fa-exclamation-triangle'></i> Import Failed</h4>
            <p><?= htmlspecialchars($import_results['message']) ?></p>
        </div>
    <?php else: ?>
        <div class='card mb-4' style='border-left: 5px solid <?= ($import_results['inserted_count'] > 0) ? '#28a745' : '#ffc107' ?>;'>
            <div class='card-body'>
                <h3 class='text-<?= ($import_results['inserted_count'] > 0) ? 'success' : 'warning' ?>'>
                    <i class='fa fa-check-circle'></i> Import Process Completed
                </h3>
                <div class='row text-center mt-3'>
                    <div class='col-md-4'>
                        <div class='well' style='background: #f8f9fa; border-radius: 8px;'>
                            <h2 style='margin:0; font-weight:bold;'><?= $import_results['total_rows'] ?></h2>
                            <span class='text-muted'>Total Rows Processed</span>
                        </div>
                    </div>
                    <div class='col-md-4'>
                        <div class='well' style='background: #e8f5e9; border-radius: 8px;'>
                            <h2 class='text-success' style='margin:0; font-weight:bold;'><?= $import_results['inserted_count'] ?></h2>
                            <span class='text-success'><b>Successfully Added</b></span>
                        </div>
                    </div>
                    <div class='col-md-4'>
                        <div class='well' style='background: #fff3e0; border-radius: 8px;'>
                            <h2 class='text-warning' style='margin:0; font-weight:bold;'><?= $import_results['skipped_count'] ?></h2>
                            <span class='text-warning'><b>Skipped / Errors</b></span>
                        </div>
                    </div>
                </div>

                <?php if (!empty($import_results['errors'])): ?>
                    <div class='alert alert-warning mt-3'>
                        <h5><i class='fa fa-exclamation-circle'></i> Notes & Skipped Rows:</h5>
                        <ul style='margin-bottom:0; max-height: 200px; overflow-y: auto;'>
                            <?php foreach ($import_results['errors'] as $err): ?>
                                <li><?= htmlspecialchars($err) ?></li>
                            <?php endforeach; ?>
                        </ul>
                    </div>
                <?php endif; ?>

                <div class='text-right mt-3'>
                    <a href='view_students' class='btn btn-success'><i class='fa fa-users'></i> View All Students</a>
                    <a href='import_students' class='btn btn-default'><i class='fa fa-refresh'></i> Import Another File</a>
                </div>
            </div>
        </div>
    <?php endif; ?>
<?php endif; ?>

<div class='row'>
    <!-- Step 1: Download Template -->
    <div class='col-md-5'>
        <div class='card' style='height: 100%;'>
            <div class='card-title-w-btn'>
                <h3 class='title'><i class='fa fa-download text-primary'></i> Step 1: Get CSV Template</h3>
            </div>
            <div class='card-body'>
                <p class='text-muted'>
                    Download our official CSV template formatted for Edumacflo student onboarding. You can open it in Microsoft Excel, enter student records, and save as CSV.
                </p>

                <div class='form-group'>
                    <label><b>Choose Class Format:</b></label>
                    <select id='template_class_select' class='form-control'>
                        <option value=''>★ General Template (Multi-Class Intake)</option>
                        <option disabled>────────── Pre-fill Class ──────────</option>
                        <?php
                        foreach ($classes_map as $cinfo) {
                            $cname = htmlspecialchars($cinfo['name']);
                            $clevel = htmlspecialchars($cinfo['level']);
                            echo "<option value='{$cname}'>{$cname} ({$clevel})</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class='text-center mt-3'>
                    <button type='button' class='btn btn-outline-primary btn-block btn-lg' onclick='downloadSelectedTemplate();' style='border-radius: 8px;'>
                        <i class='fa fa-file-excel-o'></i> Download Student CSV Template
                    </button>
                </div>

                <hr>
                <h5><i class='fa fa-info-circle text-info'></i> Import Guidelines:</h5>
                <ul class='text-muted' style='font-size: 13px; padding-left: 20px; line-height: 1.6;'>
                    <li><b>Admission Number:</b> Leave empty to let Edumacflo auto-generate sequential IDs (e.g., <code><?= $school_initial ?>/<?= date('y') ?>/XXXXXX</code>).</li>
                    <li><b>First Name & Last Name:</b> Required for every student.</li>
                    <li><b>Class:</b> Must match a class registered in the portal (e.g. <code>Basic 1</code>, <code>JSS 1</code>).</li>
                    <li><b>Level:</b> Automatically assigned based on the class.</li>
                    <li><b>Default Password:</b> Initial password will be hashed and set to <code>1234</code>.</li>
                </ul>
            </div>
        </div>
    </div>

    <!-- Step 2: Upload and Import -->
    <div class='col-md-7'>
        <div class='card'>
            <div class='card-title-w-btn'>
                <h3 class='title'><i class='fa fa-upload text-success'></i> Step 2: Upload Completed CSV</h3>
                <p><a class='btn btn-info btn-sm' href='export_students'><i class='fa fa-download'></i> Class Export</a></p>
            </div>
            <div class='card-body'>
                <form method='POST' action='' enctype='multipart/form-data'>
                    <div class='form-group'>
                        <label for='target_class'><b>Class Assignment Mode:</b></label>
                        <select name='target_class' id='target_class' class='form-control' required>
                            <option value='AUTO' selected>★ Auto-Detect Class from CSV Rows (Multi-Class Bulk Intake)</option>
                            <option disabled>────────── Force Single Class ──────────</option>
                            <?php
                            foreach ($classes_map as $cinfo) {
                                $cname = htmlspecialchars($cinfo['name']);
                                $clevel = htmlspecialchars($cinfo['level']);
                                echo "<option value='{$cname}'>Assign All Rows to: {$cname} ({$clevel})</option>";
                            }
                            ?>
                        </select>
                        <small class='form-text text-muted'>Select 'Auto-Detect' if your CSV spreadsheet contains students for different classes.</small>
                    </div>

                    <div class='form-group'>
                        <label for='password_policy'><b>Initial Password Policy:</b></label>
                        <select name='password_policy' id='password_policy' class='form-control'>
                            <option value='default_pin' selected>Standard Default PIN (1234)</option>
                            <option value='surname'>Student's Surname (Lowercase)</option>
                        </select>
                    </div>

                    <div class='form-group'>
                        <label for='student_csv'><b>Select CSV File (.csv):</b></label>
                        <input type='file' name='student_csv' id='student_csv' class='form-control' accept='.csv, text/csv' required>
                    </div>

                    <hr>

                    <button type='submit' name='import_students_btn' class='btn btn-success btn-block btn-lg' style='border-radius: 8px;'>
                        <i class='fa fa-cloud-upload'></i> Start Mass Student Import
                    </button>
                </form>
            </div>
        </div>
    </div>
</div>

<script>
function downloadSelectedTemplate() {
    var cls = document.getElementById('template_class_select').value;
    var url = 'download_student_template.php';
    if (cls) {
        url += '?class=' + encodeURIComponent(cls);
    }
    window.location.href = url;
}
</script>
