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

// Function to generate unique username for students
function generateUniqueStudentUsername($pdo, $base_username) {
    $counter = 1;
    $username = $base_username;
    
    // Keep trying until we find a unique username
    while (true) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE username = :username");
        $stmt->execute([':username' => $username]);
        $count = $stmt->fetchColumn();
        
        if ($count == 0) {
            return $username; // Found a unique username
        }
        
        // Try next username with counter
        $username = $base_username . $counter;
        $counter++;
    }
}

// Function to generate strong random password
function generateStrongPassword($length = 12) {
    $chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*';
    return substr(str_shuffle($chars), 0, $length);
}

// Function to create temporary password notifications table
function createTempPasswordNotificationsTable($pdo) {
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS temp_password_notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL,
            temp_password VARCHAR(255) NOT NULL,
            expires_at DATETIME NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_username (username)
        )"
    );
}

// Check if user is logged in and is admin
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] != 'admin') {
    header('Location: ../login.php');
    exit();
}

$user_role = $_SESSION['user_role'];
$user_name = $_SESSION['user_name'];
$error_message = '';
$success_message = '';

// Handle application status update
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['update_status'])) {
    try {
        $application_id = (int)$_POST['application_id'];
        $status = $_POST['status'];
        $reviewed_by = $_SESSION['user_id'];
        $admin_remarks = isset($_POST['admin_remarks']) ? trim($_POST['admin_remarks']) : '';
        
        // Update status based on the new status value
        if ($status == 'approved') {
            $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = :approved_by, approved_at = :approved_at, admin_remarks = :admin_remarks WHERE id = :id");
            $result = $stmt->execute([
                ':status' => $status,
                ':approved_by' => $reviewed_by,
                ':approved_at' => date('Y-m-d H:i:s'),
                ':admin_remarks' => $admin_remarks,
                ':id' => $application_id
            ]);
        } else {
            // For rejected/cancelled, set approved_by and approved_at to NULL
            $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = NULL, approved_at = NULL, admin_remarks = :admin_remarks WHERE id = :id");
            $result = $stmt->execute([
                ':status' => $status,
                ':admin_remarks' => $admin_remarks,
                ':id' => $application_id
            ]);
        }
        
        if (!$result) {
            throw new Exception("Failed to update application status");
        }
        
        // If approved, create student record
        if ($status == 'approved') {
            // Get application details
            $stmt = $pdo->prepare("SELECT * FROM admission_applications WHERE id = :id");
            $stmt->execute([':id' => $application_id]);
            $application = $stmt->fetch();
            
            if ($application) {
                // Check if a student record already exists for this application
                $stmt = $pdo->prepare("SELECT id, student_id FROM students WHERE application_no = :application_no");
                $stmt->execute([':application_no' => $application['application_no']]);
                $existing_student = $stmt->fetch();
                
                if ($existing_student) {
                    // Student record already exists, use the existing one
                    $student_db_id = $existing_student['id'];
                    $student_id = $existing_student['student_id'];
                    
                    // Update the existing student record with current application data
                    $stmt = $pdo->prepare(
                        "UPDATE students SET " .
                        "first_name = :first_name, last_name = :last_name, father_name = :father_name, " .
                        "mother_name = :mother_name, date_of_birth = :date_of_birth, gender = :gender, " .
                        "phone = :phone, email = :email, address = :address, class_id = :class_id, " .
                        "session = :session, group_name = :group_name, admission_date = :admission_date, " .
                        "guardian_name = :guardian_name, guardian_phone = :guardian_phone, " .
                        "photo = :photo, selected_subjects = :selected_subjects, status = 'active', " .
                        "updated_at = :updated_at WHERE id = :student_db_id"
                    );
                    $result = $stmt->execute([
                        ':first_name' => $application['first_name'],
                        ':last_name' => $application['last_name'],
                        ':father_name' => $application['father_name'],
                        ':mother_name' => $application['mother_name'],
                        ':date_of_birth' => $application['date_of_birth'],
                        ':gender' => $application['gender'],
                        ':phone' => $application['phone'],
                        ':email' => $application['email'],
                        ':address' => $application['present_address'] ?? '',
                        ':class_id' => $application['applying_for_class_id'],
                        ':session' => $application['applying_for_session'] ?? '',
                        ':group_name' => $application['applying_for_group'] ?? '',
                        ':admission_date' => date('Y-m-d'),
                        ':guardian_name' => $application['father_name'],
                        ':guardian_phone' => $application['father_phone'] ?? '',
                        ':photo' => $application['photo_path'] ?? '',
                        ':selected_subjects' => $application['selected_subjects'] ?? null,
                        ':updated_at' => date('Y-m-d H:i:s'),
                        ':student_db_id' => $student_db_id
                    ]);
                    
                    if (!$result) {
                        throw new Exception("Failed to update existing student record");
                    }
                } else {
                    // Create new student ID
                    $student_id = 'STU' . date('Y') . str_pad($application_id, 4, '0', STR_PAD_LEFT);
                    
                    // Check if this student ID already exists (shouldn't happen with proper logic, but good to be safe)
                    $stmt = $pdo->prepare("SELECT COUNT(*) FROM students WHERE student_id = :student_id");
                    $stmt->execute([':student_id' => $student_id]);
                    if ($stmt->fetchColumn() > 0) {
                        // Generate a unique ID by appending a counter
                        $counter = 1;
                        do {
                            $student_id = 'STU' . date('Y') . str_pad($application_id, 4, '0', STR_PAD_LEFT) . 'X' . $counter;
                            $stmt = $pdo->prepare("SELECT COUNT(*) FROM students WHERE student_id = :student_id");
                            $stmt->execute([':student_id' => $student_id]);
                            $counter++;
                        } while ($stmt->fetchColumn() > 0 && $counter < 1000); // Safety limit
                        
                        if ($counter >= 1000) {
                            throw new Exception("Could not generate unique student ID after 1000 attempts");
                        }
                    }
                    
                    // Insert into students table
                    // First, get the next available roll number for this class
                    $next_roll_number = 1;
                    try {
                        $stmt = $pdo->prepare("SELECT MAX(roll_number) as max_roll FROM students WHERE class_id = :class_id AND status = 'active'");
                        $stmt->execute([':class_id' => $application['applying_for_class_id']]);
                        $result = $stmt->fetch();
                        if ($result && $result['max_roll']) {
                            $next_roll_number = $result['max_roll'] + 1;
                        }
                    } catch (Exception $e) {
                        // If there's an error getting the max roll number, use default
                        error_log("Error getting next roll number: " . $e->getMessage());
                    }
                                    
                    $stmt = $pdo->prepare(
                        "INSERT INTO students " .
                        "(student_id, application_no, first_name, last_name, father_name, mother_name, date_of_birth, gender, " .
                        "phone, email, address, class_id, roll_number, session, group_name, admission_date, guardian_name, " .
                        "guardian_phone, status, photo, scholarship, selected_subjects, created_at) " .
                        "VALUES (:student_id, :application_no, :first_name, :last_name, :father_name, :mother_name, " .
                        ":date_of_birth, :gender, :phone, :email, :address, :class_id, :roll_number, :session, :group_name, " .
                        ":admission_date, :guardian_name, :guardian_phone, :status, :photo, :scholarship, :selected_subjects, :created_at)"
                    );
                    $result = $stmt->execute([
                        ':student_id' => $student_id,
                        ':application_no' => $application['application_no'],
                        ':first_name' => $application['first_name'],
                        ':last_name' => $application['last_name'],
                        ':father_name' => $application['father_name'],
                        ':mother_name' => $application['mother_name'],
                        ':date_of_birth' => $application['date_of_birth'],
                        ':gender' => $application['gender'],
                        ':phone' => $application['phone'],
                        ':email' => $application['email'],
                        ':address' => $application['present_address'] ?? '', // Use present_address as address
                        ':class_id' => $application['applying_for_class_id'],
                        ':roll_number' => $next_roll_number, // Auto assigned roll number
                        ':session' => $application['applying_for_session'] ?? '',
                        ':group_name' => $application['applying_for_group'] ?? '',
                        ':admission_date' => date('Y-m-d'), // admission_date
                        ':guardian_name' => $application['father_name'], // guardian_name (using father's name as default)
                        ':guardian_phone' => $application['father_phone'] ?? '', // guardian phone
                        ':status' => 'active',
                        ':photo' => $application['photo_path'] ?? '',
                        ':scholarship' => 'no', // Default to no scholarship for new students
                        ':selected_subjects' => $application['selected_subjects'] ?? null, // Include selected subjects from application
                        ':created_at' => date('Y-m-d H:i:s')
                    ]);
                    
                    if (!$result) {
                        throw new Exception("Failed to create student record");
                    }
                    
                    $student_db_id = $pdo->lastInsertId();
                }
                
                // Create user account for student with unique credentials
                $base_username = strtolower($student_id); // Use student ID as base username (converted to lowercase)
                $username = generateUniqueStudentUsername($pdo, $base_username);
                $raw_password = generateStrongPassword(12); // Generate a strong, unique password
                $password = password_hash($raw_password, PASSWORD_DEFAULT);
                
                $stmt = $pdo->prepare("INSERT INTO users (username, password, email, role, status) VALUES (:username, :password, :email, 'student', 'active')");
                $result = $stmt->execute([
                    ':username' => $username,
                    ':password' => $password,
                    ':email' => $application['email']
                ]);
                
                if (!$result) {
                    throw new Exception("Failed to create user account");
                }
                
                $user_id = $pdo->lastInsertId();
                
                // Store the temporary password in session for notification to admin
                $_SESSION['temp_student_credentials'] = [
                    'username' => $username,
                    'password' => $raw_password,
                    'student_name' => $application['first_name'] . ' ' . $application['last_name'],
                    'email' => $application['email']
                ];
                
                // Store temporary password in database for student first-time login notification
                // Create a temporary password notification record
                try {
                    $current_time = date('Y-m-d H:i:s');
                    $expires_time = date('Y-m-d H:i:s', strtotime('+24 hours'));
                    $stmt = $pdo->prepare("INSERT INTO temp_password_notifications (username, temp_password, expires_at, created_at) VALUES (:username, :temp_password, :expires_at, :created_at) ON DUPLICATE KEY UPDATE temp_password = VALUES(temp_password), expires_at = VALUES(expires_at), created_at = VALUES(created_at)");
                    $stmt->execute([
                        ':username' => $username,
                        ':temp_password' => $raw_password,
                        ':expires_at' => $expires_time,
                        ':created_at' => $current_time
                    ]);
                } catch (Exception $e) {
                    // If table doesn't exist, create it first
                    createTempPasswordNotificationsTable($pdo);
                    $current_time = date('Y-m-d H:i:s');
                    $expires_time = date('Y-m-d H:i:s', strtotime('+24 hours'));
                    $stmt = $pdo->prepare("INSERT INTO temp_password_notifications (username, temp_password, expires_at, created_at) VALUES (:username, :temp_password, :expires_at, :created_at) ON DUPLICATE KEY UPDATE temp_password = VALUES(temp_password), expires_at = VALUES(expires_at), created_at = VALUES(created_at)");
                    $stmt->execute([
                        ':username' => $username,
                        ':temp_password' => $raw_password,
                        ':expires_at' => $expires_time,
                        ':created_at' => $current_time
                    ]);
                }
                
                // Link student to user
                $stmt = $pdo->prepare("UPDATE students SET user_id = :user_id WHERE id = :id");
                $result = $stmt->execute([
                    ':user_id' => $user_id,
                    ':id' => $student_db_id
                ]);
                
                if (!$result) {
                    throw new Exception("Failed to link student to user");
                }
                
                // Assign all class fees to the newly approved student
                try {
                    // Check if the student has scholarship status from the students table
                    $scholarship_stmt = $pdo->prepare("SELECT scholarship FROM students WHERE id = :student_id");
                    $scholarship_stmt->execute([':student_id' => $student_db_id]);
                    $student_record = $scholarship_stmt->fetch();
                    $scholarship = $student_record['scholarship'] ?? 'no'; // Default to 'no' if not set
                                    
                    if ($scholarship === 'no') {
                        // Get all distinct fee types for the class (not individual student fees)
                        // We need to find all fees that have been assigned to any student in the same class
                        $stmt = $pdo->prepare(
                            "SELECT DISTINCT f.fee_type, f.amount, f.due_date, f.academic_year, f.month, f.remarks 
                            FROM fees f 
                            JOIN students s ON f.student_id = s.id 
                            WHERE s.class_id = :class_id AND f.status = 'pending'"
                        );
                        $stmt->execute([':class_id' => $application['applying_for_class_id']]);
                        $class_fees = $stmt->fetchAll();
                                        
                        // Assign each fee type to the new student
                        if (!empty($class_fees)) {
                            $fee_insert_stmt = $pdo->prepare(
                                "INSERT INTO fees (student_id, fee_type, amount, due_date, academic_year, month, remarks, status) 
                                VALUES (:student_id, :fee_type, :amount, :due_date, :academic_year, :month, :remarks, 'pending')"
                            );
                                            
                            foreach ($class_fees as $class_fee) {
                                $fee_insert_stmt->execute([
                                    ':student_id' => $student_db_id,
                                    ':fee_type' => $class_fee['fee_type'],
                                    ':amount' => $class_fee['amount'],
                                    ':due_date' => $class_fee['due_date'],
                                    ':academic_year' => $class_fee['academic_year'],
                                    ':month' => $class_fee['month'],
                                    ':remarks' => $class_fee['remarks']
                                ]);
                            }
                        }
                    } else {
                        // If student has scholarship, no fees need to be assigned
                        error_log("Student has scholarship - no fees assigned");
                    }
                } catch (Exception $e) {
                    error_log("Error assigning class fees to approved student: " . $e->getMessage());
                    // Don't throw exception here as the student has already been created successfully
                }
                
                // Handle selected subjects for the approved student
                try {
                    if (!empty($application['selected_subjects'])) {
                        // Get the selected subjects from the application
                        $selected_subjects = json_decode($application['selected_subjects'], true);
                        
                        if (is_array($selected_subjects) && !empty($selected_subjects)) {
                            // Update the student record with selected subjects
                            $stmt = $pdo->prepare("UPDATE students SET selected_subjects = :selected_subjects WHERE id = :student_id");
                            $stmt->execute([
                                ':selected_subjects' => $application['selected_subjects'],
                                ':student_id' => $student_db_id
                            ]);
                        }
                    }
                } catch (Exception $e) {
                    error_log("Error handling selected subjects for approved student: " . $e->getMessage());
                    // Don't throw exception here as it's not critical for student creation
                }
            }
        }
    } catch (Exception $e) {
        error_log("Admission application approval error: " . $e->getMessage());
        $error_message = 'স্ট্যাটাস আপডেট করতে সমস্যা হয়েছে: ' . $e->getMessage();
    }
    
    // Display temporary credentials to admin after successful approval
    if (isset($_SESSION['temp_student_credentials'])) {
        $creds = $_SESSION['temp_student_credentials'];
        $success_message = 'শিক্ষার্থী সফলভাবে অনুমোদিত হয়েছে। নতুন ব্যবহারকারী তথ্য:<br>' .
                         'ইউজারনেম: ' . htmlspecialchars($creds['username']) . '<br>' .
                         'পাসওয়ার্ড: ' . htmlspecialchars($creds['password']) . '<br>' .
                         'শিক্ষার্থীর নাম: ' . htmlspecialchars($creds['student_name']) . '<br>' .
                         'ইমেইল: ' . htmlspecialchars($creds['email']);
        
        // Clear the temporary credentials
        unset($_SESSION['temp_student_credentials']);
    }
}

// Handle application deletion for rejected and cancelled applications
if (isset($_GET['delete_id']) && is_numeric($_GET['delete_id'])) {
    try {
        $delete_id = (int)$_GET['delete_id'];
        
        // First, verify that the application is either rejected or cancelled
        $stmt = $pdo->prepare("SELECT status FROM admission_applications WHERE id = ?");
        $stmt->execute([$delete_id]);
        $application = $stmt->fetch();
        
        if ($application && ($application['status'] == 'rejected' || $application['status'] == 'cancelled')) {
            // Delete the application
            $stmt = $pdo->prepare("DELETE FROM admission_applications WHERE id = ?");
            $result = $stmt->execute([$delete_id]);
            
            if ($result) {
                $success_message = 'আবেদনটি সফলভাবে মুছে ফেলা হয়েছে';
            } else {
                throw new Exception("Failed to delete application");
            }
        } else {
            $error_message = 'শুধুমাত্র প্রত্যাখ্যাত বা বাতিল আবেদনগুলো মুছে ফেলা যাবে';
        }
    } catch (Exception $e) {
        error_log("Admission application deletion error: " . $e->getMessage());
        $error_message = 'আবেদন মুছে ফেলতে সমস্যা হয়েছে: ' . $e->getMessage();
    }
}

// Handle removal of approved student
if (isset($_GET['remove_approved_id']) && is_numeric($_GET['remove_approved_id'])) {
    try {
        $remove_id = (int)$_GET['remove_approved_id'];
        
        // First, verify that the application is approved
        $stmt = $pdo->prepare("SELECT status, application_no FROM admission_applications WHERE id = ?");
        $stmt->execute([$remove_id]);
        $application = $stmt->fetch();
        
        if ($application && $application['status'] == 'approved') {
            // Get the student record associated with this application
            $stmt = $pdo->prepare("SELECT id, student_id FROM students WHERE application_no = ?");
            $stmt->execute([$application['application_no']]);
            $student = $stmt->fetch();
            
            if ($student) {
                // Start transaction
                $pdo->beginTransaction();
                
                // Delete the student record
                $stmt = $pdo->prepare("DELETE FROM students WHERE id = ?");
                $result = $stmt->execute([$student['id']]);
                
                if ($result) {
                    // Delete the associated user account
                    $stmt = $pdo->prepare("DELETE u FROM users u JOIN students s ON u.id = s.user_id WHERE s.id = ?");
                    $stmt->execute([$student['id']]);
                    
                    // Delete associated fees
                    $stmt = $pdo->prepare("DELETE FROM fees WHERE student_id = ?");
                    $stmt->execute([$student['id']]);
                    
                    // Change application status to cancelled instead of deleting
                    $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = :approved_by, approved_at = :approved_at WHERE id = :id");
                    $stmt->execute([
                        ':status' => 'cancelled',
                        ':approved_by' => null,
                        ':approved_at' => null,
                        ':id' => $remove_id
                    ]);
                    
                    $pdo->commit();
                    $success_message = 'অনুমোদিত শিক্ষার্থী সফলভাবে সরানো হয়েছে';
                } else {
                    $pdo->rollBack();
                    throw new Exception("Failed to remove student");
                }
            } else {
                // If no student record found, just update the application status
                $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = :approved_by, approved_at = :approved_at WHERE id = :id");
                $stmt->execute([
                    ':status' => 'cancelled',
                    ':approved_by' => null,
                    ':approved_at' => null,
                    ':id' => $remove_id
                ]);
                $success_message = 'আবেদনটি বাতিল করা হয়েছে';
            }
        } else {
            $error_message = 'শুধুমাত্র অনুমোদিত আবেদনগুলো সরানো যাবে';
        }
    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        error_log("Approved student removal error: " . $e->getMessage());
        $error_message = 'অনুমোদিত শিক্ষার্থী সরাতে সমস্যা হয়েছে: ' . $e->getMessage();
    }
}

// Handle bulk removal of approved students
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['bulk_remove']) && isset($_POST['selected_applications'])) {
    try {
        $selected_ids = $_POST['selected_applications'];
        
        if (!empty($selected_ids) && is_array($selected_ids)) {
            $pdo->beginTransaction();
            
            $success_count = 0;
            $error_count = 0;
            
            foreach ($selected_ids as $app_id) {
                $app_id = (int)$app_id;
                
                // Verify that the application is approved
                $stmt = $pdo->prepare("SELECT status, application_no FROM admission_applications WHERE id = ?");
                $stmt->execute([$app_id]);
                $application = $stmt->fetch();
                
                if ($application && $application['status'] == 'approved') {
                    // Get the student record associated with this application
                    $stmt = $pdo->prepare("SELECT id, student_id FROM students WHERE application_no = ?");
                    $stmt->execute([$application['application_no']]);
                    $student = $stmt->fetch();
                    
                    if ($student) {
                        // Delete the student record
                        $stmt = $pdo->prepare("DELETE FROM students WHERE id = ?");
                        $result = $stmt->execute([$student['id']]);
                        
                        if ($result) {
                            // Delete the associated user account
                            $stmt = $pdo->prepare("DELETE u FROM users u JOIN students s ON u.id = s.user_id WHERE s.id = ?");
                            $stmt->execute([$student['id']]);
                            
                            // Delete associated fees
                            $stmt = $pdo->prepare("DELETE FROM fees WHERE student_id = ?");
                            $stmt->execute([$student['id']]);
                            
                            // Change application status to cancelled instead of deleting
                            $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = :approved_by, approved_at = :approved_at WHERE id = :id");
                            $stmt->execute([
                                ':status' => 'cancelled',
                                ':approved_by' => null,
                                ':approved_at' => null,
                                ':id' => $app_id
                            ]);
                            
                            $success_count++;
                        } else {
                            $error_count++;
                        }
                    } else {
                        // If no student record found, just update the application status
                        $stmt = $pdo->prepare("UPDATE admission_applications SET status = :status, approved_by = :approved_by, approved_at = :approved_at WHERE id = :id");
                        $stmt->execute([
                            ':status' => 'cancelled',
                            ':approved_by' => null,
                            ':approved_at' => null,
                            ':id' => $app_id
                        ]);
                        $success_count++;
                    }
                } else {
                    $error_count++;
                }
            }
            
            $pdo->commit();
            
            if ($error_count == 0) {
                $success_message = "$success_countটি অনুমোদিত শিক্ষার্থী সফলভাবে সরানো হয়েছে";
            } elseif ($success_count > 0) {
                $success_message = "$success_countটি অনুমোদিত শিক্ষার্থী সফলভাবে সরানো হয়েছে, $error_countটি সরানো যায়নি";
            } else {
                $error_message = "$error_countটি অনুমোদিত শিক্ষার্থী সরানো যায়নি";
            }
        } else {
            $error_message = 'কোন শিক্ষার্থী নির্বাচন করা হয়নি';
        }
    } catch (Exception $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        error_log("Bulk approved student removal error: " . $e->getMessage());
        $error_message = 'অনুমোদিত শিক্ষার্থী বাল্ক সরাতে সমস্যা হয়েছে: ' . $e->getMessage();
    }
}

// Get filter parameters
$filter_status = isset($_GET['status']) ? $_GET['status'] : 'all';
$search_application_no = isset($_GET['application_no']) ? trim($_GET['application_no']) : '';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 10;
$offset = ($page - 1) * $limit;

// Build query based on filters
$where_conditions = [];
$params = [];
$param_counter = 1;

if ($filter_status != 'all') {
    $param_name = ":status" . $param_counter++;
    $where_conditions[] = "aa.status = $param_name";
    $params[$param_name] = $filter_status;
}

if (!empty($search_application_no)) {
    $param_name = ":appno" . $param_counter++;
    $where_conditions[] = "aa.application_no LIKE $param_name";
    $params[$param_name] = "%$search_application_no%";
}

$where_clause = '';
if (!empty($where_conditions)) {
    $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
}

// Get total count
$count_sql = "SELECT COUNT(*) FROM admission_applications aa $where_clause";
$count_stmt = $pdo->prepare($count_sql);

// Bind parameters for count query
foreach ($params as $key => $value) {
    $count_stmt->bindValue($key, $value);
}

$count_stmt->execute();
$total_applications = $count_stmt->fetchColumn();
$total_pages = ceil($total_applications / $limit);

// Get applications with pagination
$sql = "
    SELECT aa.*, c.name as class_name, c.section, u.username as reviewed_by_name
    FROM admission_applications aa
    LEFT JOIN classes c ON aa.applying_for_class_id = c.id
    LEFT JOIN users u ON aa.approved_by = u.id
    $where_clause
    ORDER BY aa.created_at DESC
    LIMIT :limit OFFSET :offset
";

$stmt = $pdo->prepare($sql);

// Bind WHERE clause parameters
foreach ($params as $key => $value) {
    $stmt->bindValue($key, $value);
}

// Bind LIMIT and OFFSET parameters
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

$stmt->execute();
$applications = $stmt->fetchAll();

// Get classes for filter
$classes = [];
try {
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name ASC");
    $classes = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    $classes = [];
}
?>

<!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="https://fonts.googleapis.com/css2?family=Hind+Siliguri:wght@300;400;500;600;700&display=swap" rel="stylesheet">
    <link href="../assets/css/jessoreboard-theme.css" rel="stylesheet">
    <link href="../assets/css/sidebar.css" rel="stylesheet">
    <style>
        body {
            font-family: 'Hind Siliguri', sans-serif;
            background-color: #f8f9fa;
        }
        
        .applications-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 2rem 0;
            margin-bottom: 2rem;
        }
        
        .application-card {
            background: white;
            border-radius: 10px;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            transition: all 0.3s ease;
            margin-bottom: 1.5rem;
            border-left: 4px solid #667eea;
        }
        
        .application-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.15);
        }
        
        .application-card.pending {
            border-left-color: #ffc107;
        }
        
        .application-card.approved {
            border-left-color: #28a745;
        }
        
        .application-card.rejected {
            border-left-color: #dc3545;
        }
        
        .status-badge {
            font-size: 0.85rem;
            padding: 0.4em 0.8em;
        }
        
        .filter-card {
            background: white;
            border-radius: 10px;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            padding: 1.5rem;
            margin-bottom: 1.5rem;
        }
        
        .stats-card {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
            color: white;
            border-radius: 10px;
            padding: 1.5rem;
            margin-bottom: 1.5rem;
        }
        
        .pagination .page-link {
            color: #667eea;
        }
        
        .pagination .page-item.active .page-link {
            background-color: #667eea;
            border-color: #667eea;
        }
        
        .application-details {
            font-size: 0.9rem;
        }
    </style>
</head>
<body>
    <div class="sidebar-container">
        <?php echo renderSidebar('index_with_sidebar.php', $user_role, $user_name); ?>
        <div class="main-content">
            <!-- Header -->
            <div class="applications-header">
                <div class="container">
                    <div class="row align-items-center">
                        <div class="col-md-8">
                            <h1><i class="fas fa-user-plus"></i> ভর্তি আবেদন পর্যালোচনা</h1>
                            <p class="mb-0">ছাত্র/ছাত্রীদের ভর্তি আবেদন পর্যালোচনা ও অনুমোদন</p>
                        </div>
                        <div class="col-md-4 text-md-end">
                            <a href="../index.php" class="btn btn-light">
                                <i class="fas fa-arrow-left"></i> ড্যাশবোর্ডে ফিরে যান
                            </a>
                        </div>
                    </div>
                </div>
            </div>

            <div class="container">
                <?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; ?>
                
                <?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; ?>

                <!-- Statistics Cards -->
                <div class="row">
                    <div class="col-md-4">
                        <div class="stats-card">
                            <div class="d-flex justify-content-between align-items-center">
                                <div>
                                    <h3><?php 
                                        $stmt = $pdo->query("SELECT COUNT(*) FROM admission_applications WHERE status = 'pending'");
                                        echo $stmt->fetchColumn();
                                    ?></h3>
                                    <p class="mb-0">মুলতুবি</p>
                                </div>
                                <i class="fas fa-clock fa-2x"></i>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-md-4">
                        <div class="stats-card" style="background: linear-gradient(135deg, #28a745 0%, #20c997 100%);">
                            <div class="d-flex justify-content-between align-items-center">
                                <div>
                                    <h3><?php 
                                        $stmt = $pdo->query("SELECT COUNT(*) FROM admission_applications WHERE status = 'approved'");
                                        echo $stmt->fetchColumn();
                                    ?></h3>
                                    <p class="mb-0">অনুমোদিত</p>
                                </div>
                                <i class="fas fa-check-circle fa-2x"></i>
                            </div>
                        </div>
                    </div>
                    
                    <div class="col-md-4">
                        <div class="stats-card" style="background: linear-gradient(135deg, #dc3545 0%, #fd7e14 100%);">
                            <div class="d-flex justify-content-between align-items-center">
                                <div>
                                    <h3><?php 
                                        $stmt = $pdo->query("SELECT COUNT(*) FROM admission_applications WHERE status = 'rejected' OR status = 'cancelled'");
                                        echo $stmt->fetchColumn();
                                    ?></h3>
                                    <p class="mb-0">প্রত্যাখ্যাত</p>
                                </div>
                                <i class="fas fa-times-circle fa-2x"></i>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Filter Section -->
                <div class="filter-card">
                    <h5><i class="fas fa-filter"></i> ফিল্টার এবং সার্চ</h5>
                    <form method="GET" class="row g-3">
                        <div class="col-md-4">
                            <label for="status" class="form-label">অবস্থা</label>
                            <select name="status" id="status" class="form-select">
                                <option value="all" <?php echo ($filter_status == 'all') ? 'selected' : ''; ?>>সব অবস্থা</option>
                                <option value="pending" <?php echo ($filter_status == 'pending') ? 'selected' : ''; ?>>মুলতুবি</option>
                                <option value="approved" <?php echo ($filter_status == 'approved') ? 'selected' : ''; ?>>অনুমোদিত</option>
                                <option value="rejected" <?php echo ($filter_status == 'rejected') ? 'selected' : ''; ?>>প্রত্যাখ্যাত</option>
                                <option value="cancelled" <?php echo ($filter_status == 'cancelled') ? 'selected' : ''; ?>>বাতিল</option>
                            </select>
                        </div>
                        
                        <div class="col-md-4">
                            <label for="application_no" class="form-label">আবেদন নং</label>
                            <input type="text" name="application_no" id="application_no" class="form-control" value="<?php echo htmlspecialchars($search_application_no); ?>" placeholder="আবেদন নং দিয়ে সার্চ করুন">
                        </div>
                        
                        <div class="col-md-4 d-flex align-items-end">
                            <button type="submit" class="btn btn-primary me-2">
                                <i class="fas fa-search"></i> সার্চ
                            </button>
                            <a href="admission_applications.php" class="btn btn-secondary">
                                <i class="fas fa-redo"></i> রিসেট
                            </a>
                        </div>
                    </form>
                </div>

                <!-- Bulk Remove Form for Approved Applications -->
                <?php if ($filter_status == 'approved' || $filter_status == 'all'): ?>
                <form method="POST" id="bulkRemoveForm">
                <?php endif; ?>
                
                <?php if ($filter_status == 'approved' || $filter_status == 'all'): ?>
                <div class="card mb-4">
                    <div class="card-header bg-warning text-dark">
                        <div class="d-flex justify-content-between align-items-center">
                            <h5 class="mb-0"><i class="fas fa-users-slash"></i> বাল্ক শিক্ষার্থী সরান</h5>
                            <div>
                                <button type="button" class="btn btn-sm btn-outline-dark" onclick="toggleCheckboxes(true)">সব সিলেক্ট করুন</button>
                                <button type="button" class="btn btn-sm btn-outline-dark ms-1" onclick="toggleCheckboxes(false)">সব আনসিলেক্ট করুন</button>
                                <button type="submit" name="bulk_remove" class="btn btn-sm btn-danger ms-1" onclick="return confirm('আপনি কি নিশ্চিত যে নির্বাচিত অনুমোদিত শিক্ষার্থীদের সরাতে চান? এটি স্টুডেন্ট রেকর্ড এবং সংশ্লিষ্ট তথ্যগুলো মুছে ফেলবে।')">
                                    <i class="fas fa-trash"></i> নির্বাচিত শিক্ষার্থীদের সরান
                                </button>
                            </div>
                        </div>
                    </div>
                </div>
                <?php endif; ?>
                
                <script>
                function toggleCheckboxes(select) {
                    const checkboxes = document.querySelectorAll('input[name="selected_applications[]"]');
                    checkboxes.forEach(checkbox => {
                        checkbox.checked = select;
                    });
                }
                </script>
                
                <!-- Applications List -->
                <?php if (empty($applications)): ?>
                    <div class="text-center py-5">
                        <i class="fas fa-inbox fa-5x text-muted mb-3"></i>
                        <h4>কোন আবেদন পাওয়া যায়নি</h4>
                        <p class="text-muted">এখন পর্যন্ত কোন ভর্তি আবেদন জমা হয়নি।</p>
                    </div>
                <?php else: ?>
                    <?php foreach ($applications as $app): ?>
                        <div class="application-card <?php echo $app['status']; ?>">
                            <div class="card-body">
                                <div class="row">
                                    <div class="col-md-9">
                                        <h5 class="card-title">
                                            <?php if ($app['status'] == 'approved'): ?>
                                                <input type="checkbox" name="selected_applications[]" value="<?php echo $app['id']; ?>" class="me-2">
                                            <?php endif; ?>
                                            <?php echo htmlspecialchars($app['first_name'] . ' ' . $app['last_name']); ?>
                                            <span class="badge bg-<?php 
                                                echo ($app['status'] == 'pending') ? 'warning' : 
                                                    (($app['status'] == 'approved') ? 'success' : 'danger'); 
                                            ?> status-badge">
                                                <?php 
                                                    echo ($app['status'] == 'pending') ? 'মুলতুবি' : 
                                                        (($app['status'] == 'approved') ? 'অনুমোদিত' : 
                                                        (($app['status'] == 'rejected') ? 'প্রত্যাখ্যাত' : 'বাতিল')); 
                                                ?>
                                            </span>
                                        </h5>
                                        
                                        <div class="row application-details">
                                            <div class="col-md-6">
                                                <p class="mb-1"><strong>আবেদন নং:</strong> <?php echo htmlspecialchars($app['application_no']); ?></p>
                                                <p class="mb-1"><strong>পিতার নাম:</strong> <?php echo htmlspecialchars($app['father_name']); ?></p>
                                                <p class="mb-1"><strong>মাতার নাম:</strong> <?php echo htmlspecialchars($app['mother_name']); ?></p>
                                                <p class="mb-1"><strong>জন্ম তারিখ:</strong> <?php echo date('d/m/Y', strtotime($app['date_of_birth'])); ?></p>
                                                <p class="mb-1"><strong>লিঙ্গ:</strong> 
                                                    <?php 
                                                        echo ($app['gender'] == 'male') ? 'পুরুষ' : 
                                                            (($app['gender'] == 'female') ? 'মহিলা' : 'অন্যান্য'); 
                                                    ?>
                                                </p>
                                            </div>
                                            
                                            <div class="col-md-6">
                                                <p class="mb-1"><strong>ফোন:</strong> <?php echo htmlspecialchars($app['phone']); ?></p>
                                                <p class="mb-1"><strong>ইমেইল:</strong> <?php echo htmlspecialchars($app['email'] ?? 'N/A'); ?></p>
                                                <p class="mb-1"><strong>আবেদন করা ক্লাস:</strong> <?php echo htmlspecialchars($app['class_name'] ?? 'N/A'); ?></p>
                                                <p class="mb-1"><strong>আবেদনের তারিখ:</strong> <?php echo date('d/m/Y H:i', strtotime($app['created_at'])); ?></p>
                                                
                                                <!-- Student Photo Thumbnail -->
                                                <?php if (!empty($app['photo_path']) && file_exists($app['photo_path'])): ?>
                                                    <p class="mb-1">
                                                        <strong>ছবি:</strong>
                                                        <img src="<?php echo htmlspecialchars($app['photo_path']); ?>" alt="Student Photo" style="max-width: 80px; max-height: 100px; border: none; border-radius: 3px; box-shadow: 0 2px 4px rgba(0,0,0,0.1);">
                                                    </p>
                                                <?php endif; ?>
                                            </div>
                                        </div>
                                        
                                        <?php if (!empty($app['present_address'])): ?>
                                            <p class="mb-1 application-details"><strong>ঠিকানা:</strong> <?php echo htmlspecialchars($app['present_address']); ?></p>
                                        <?php endif; ?>
                                        
                                        <?php if (!empty($app['selected_subjects'])): ?>
                                            <?php 
                                                $selected_subjects = json_decode($app['selected_subjects'], true);
                                                if (is_array($selected_subjects) && !empty($selected_subjects)) {
                                                    // Get subject names from database
                                                    $placeholders = str_repeat('?,', count($selected_subjects) - 1) . '?';
                                                    $stmt = $pdo->prepare("SELECT name FROM subjects WHERE id IN ($placeholders) AND status = 'active'");
                                                    $stmt->execute($selected_subjects);
                                                    $subject_names = $stmt->fetchAll(PDO::FETCH_COLUMN);
                                                }
                                            ?>
                                            <p class="mb-1 application-details">
                                                <strong>নির্বাচিত বিষয়সমূহ:</strong> 
                                                <?php 
                                                    if (!empty($subject_names)) {
                                                        echo htmlspecialchars(implode(', ', $subject_names));
                                                    } else {
                                                        echo 'বিষয় তথ্য পাওয়া যায়নি';
                                                    }
                                                ?>
                                            </p>
                                        <?php endif; ?>
                                        
                                        <?php if (!empty($app['previous_school']) || !empty($app['previous_class'])): ?>
                                            <p class="mb-1 application-details">
                                                <strong>পূর্ববর্তী শিক্ষা:</strong> 
                                                <?php 
                                                    echo htmlspecialchars($app['previous_class'] ?? '');
                                                    if (!empty($app['previous_school'])) {
                                                        echo ' - ' . htmlspecialchars($app['previous_school']);
                                                    }
                                                ?>
                                            </p>
                                        <?php endif; ?>
                                        
                                        <?php if (!empty($app['admin_remarks'])): ?>
                                            <p class="mb-1 application-details">
                                                <strong>প্রশাসনের মন্তব্য:</strong> 
                                                <span class="text-muted"><?php echo htmlspecialchars($app['admin_remarks']); ?></span>
                                            </p>
                                        <?php endif; ?>
                                        
                                        <?php if ($app['status'] != 'pending' && !empty($app['reviewed_by_name'])): ?>
                                            <p class="mb-0 application-details">
                                                <strong>পর্যালোচনা করেছেন:</strong> 
                                                <?php echo htmlspecialchars($app['reviewed_by_name']); ?> 
                                                (<?php echo date('d/m/Y H:i', strtotime($app['approved_at'])); ?>)
                                            </p>
                                        <?php endif; ?>
                                    </div>
                                    
                                    <div class="col-md-3 d-flex align-items-center justify-content-center">
                                        <div class="w-100">
                                            <?php if ($app['status'] == 'pending'): ?>
                                                <form method="POST" class="mb-2">
                                                    <input type="hidden" name="application_id" value="<?php echo $app['id']; ?>">
                                                    <div class="mb-2">
                                                        <select name="status" class="form-select form-select-sm" required>
                                                            <option value="">অবস্থা নির্বাচন করুন</option>
                                                            <option value="approved">অনুমোদন করুন</option>
                                                            <option value="rejected">প্রত্যাখ্যান করুন</option>
                                                            <option value="cancelled">বাতিল করুন</option>
                                                        </select>
                                                    </div>
                                                    <div class="mb-2">
                                                        <textarea name="admin_remarks" class="form-control form-control-sm" placeholder="মন্তব্য (ঐচ্ছিক)" rows="2"></textarea>
                                                    </div>
                                                    <button type="submit" name="update_status" class="btn btn-sm btn-primary w-100">
                                                        <i class="fas fa-save"></i> আপডেট
                                                    </button>
                                                </form>
                                            <?php else: ?>
                                                <div class="text-center mb-2">
                                                    <i class="fas fa-<?php echo ($app['status'] == 'approved') ? 'check-circle text-success' : 'times-circle text-danger'; ?> fa-2x mb-2"></i>
                                                    <div>
                                                        <?php if ($app['status'] == 'approved'): ?>
                                                            <span class="badge bg-success">অনুমোদিত</span>
                                                        <?php elseif ($app['status'] == 'rejected'): ?>
                                                            <span class="badge bg-danger">প্রত্যাখ্যাত</span>
                                                        <?php else: ?>
                                                            <span class="badge bg-secondary">বাতিল</span>
                                                        <?php endif; ?>
                                                    </div>
                                                </div>
                                                
                                                <!-- Delete button for rejected and cancelled applications -->
                                                <?php if ($app['status'] == 'rejected' || $app['status'] == 'cancelled'): ?>
                                                    <a href="?delete_id=<?php echo $app['id']; ?>" 
                                                       class="btn btn-sm btn-outline-danger w-100 mb-2" 
                                                       onclick="return confirm('আপনি কি নিশ্চিত যে এই আবেদনটি মুছে ফেলতে চান? এটি পুনরুদ্ধার করা যাবে না।')">
                                                        <i class="fas fa-trash"></i> মুছে ফেলুন
                                                    </a>
                                                <?php endif; ?>
                                                
                                                <!-- Remove button for approved applications -->
                                                <?php if ($app['status'] == 'approved'): ?>
                                                    <a href="?remove_approved_id=<?php echo $app['id']; ?>" 
                                                       class="btn btn-sm btn-outline-warning w-100 mb-2" 
                                                       onclick="return confirm('আপনি কি নিশ্চিত যে এই অনুমোদিত শিক্ষার্থীটি সরাতে চান? এটি স্টুডেন্ট রেকর্ড এবং সংশ্লিষ্ট তথ্যগুলো মুছে ফেলবে।')">
                                                        <i class="fas fa-user-times"></i> শিক্ষার্থী সরান
                                                    </a>
                                                <?php endif; ?>
                                            <?php endif; ?>
                                            
                                            <!-- View Receipt Button -->
                                            <a href="../admission_receipt.php?application_no=<?php echo urlencode($app['application_no']); ?>" target="_blank" class="btn btn-sm btn-outline-primary w-100">
                                                <i class="fas fa-receipt"></i> রশিদ দেখুন
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    <?php endforeach; ?>
                    
                    <!-- Close the bulk remove form if it was opened -->
                    <?php if ($filter_status == 'approved' || $filter_status == 'all'): ?>
                    </form>
                    <?php endif; ?>
                    
                    <!-- Pagination -->
                    <?php if ($total_pages > 1): ?>
                        <nav aria-label="Page navigation">
                            <ul class="pagination justify-content-center">
                                <?php if ($page > 1): ?>
                                    <li class="page-item">
                                        <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $page - 1])); ?>" aria-label="Previous">
                                            <span aria-hidden="true">&laquo;</span>
                                        </a>
                                    </li>
                                <?php endif; ?>
                                
                                <?php for ($i = 1; $i <= $total_pages; $i++): ?>
                                    <li class="page-item <?php echo ($i == $page) ? 'active' : ''; ?>">
                                        <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $i])); ?>"><?php echo $i; ?></a>
                                    </li>
                                <?php endfor; ?>
                                
                                <?php if ($page < $total_pages): ?>
                                    <li class="page-item">
                                        <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $page + 1])); ?>" aria-label="Next">
                                            <span aria-hidden="true">&raquo;</span>
                                        </a>
                                    </li>
                                <?php endif; ?>
                            </ul>
                        </nav>
                    <?php endif; ?>
                <?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>