<?php
session_start();

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

$user_role = $_SESSION['user_role'];

require_once 'config/database.php';

// Get all parameters
$search_customer = isset($_GET['search_customer']) ? trim($_GET['search_customer']) : '';
$search_student_id = isset($_GET['search_student_id']) ? trim($_GET['search_student_id']) : '';
$search_name = isset($_GET['search_name']) ? trim($_GET['search_name']) : '';
$session_filter = isset($_GET['session']) ? trim($_GET['session']) : '';
$class_filter = isset($_GET['class_id']) ? (int)$_GET['class_id'] : 0;
$group_filter = isset($_GET['group_name']) ? trim($_GET['group_name']) : '';

// Get available data for filters
try {
    // Get sessions - try multiple approaches
    $sessions = [];
    try {
        // Try academic_sessions table first
        $stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
        $sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    } catch (Exception $e) {
        // Fallback: look for session-like patterns in class names
        $stmt = $pdo->query("SELECT DISTINCT name FROM classes WHERE name REGEXP '[0-9]{4}' ORDER BY name DESC");
        $class_names = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Extract potential session years from class names
        foreach ($class_names as $name) {
            preg_match('/(\d{4})/', $name, $matches);
            if (!empty($matches[1])) {
                $sessions[] = $matches[1];
            }
        }
        $sessions = array_unique($sessions);
        rsort($sessions);
    }
    
    // Get classes
    $stmt = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section");
    $classes = $stmt->fetchAll();
    
    // Get groups
    $stmt = $pdo->query("SELECT DISTINCT group_name FROM students WHERE group_name IS NOT NULL AND group_name != '' ORDER BY group_name");
    $groups = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
} catch (Exception $e) {
    error_log("Filter data loading error: " . $e->getMessage());
    $sessions = [];
    $classes = [];
    $groups = [];
}

// Build query with proper error handling
$sql = "SELECT id, student_id, application_no, customer_number, first_name, last_name, class_id, roll_number, photo, group_name FROM students WHERE 1=1";
$params = [];

try {
    // Add session filter - safer approach
    if (!empty($session_filter)) {
        // Look for classes that contain the session year
        $sql .= " AND class_id IN (SELECT id FROM classes WHERE name LIKE ?)";
        $params[] = '%' . $session_filter . '%';
    }
    
    // Add class filter
    if ($class_filter > 0) {
        $sql .= " AND class_id = ?";
        $params[] = $class_filter;
    }
    
    // Add group filter
    if (!empty($group_filter)) {
        $sql .= " AND group_name = ?";
        $params[] = $group_filter;
    }
    
    // Add search filters
    if (!empty($search_customer)) {
        $sql .= " AND customer_number LIKE ?";
        $params[] = '%' . $search_customer . '%';
    }
    
    if (!empty($search_student_id)) {
        $sql .= " AND student_id LIKE ?";
        $params[] = '%' . $search_student_id . '%';
    }
    
    if (!empty($search_name)) {
        $sql .= " AND (first_name LIKE ? OR last_name LIKE ?)";
        $params[] = '%' . $search_name . '%';
        $params[] = '%' . $search_name . '%';
    }
    
    $sql .= " ORDER BY customer_number ASC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll();
    $error_message = '';
    
} catch (Exception $e) {
    error_log("Student query error: " . $e->getMessage());
    $students = [];
    $error_message = "ডেটা লোড করতে সমস্যা হয়েছে: " . $e->getMessage();
}
?>

<!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">
    <style>
        body {
            font-family: 'Hind Siliguri', sans-serif;
        }
        
        @media print {
            .no-print {
                display: none !important;
            }
            
            .table {
                font-size: 12px;
            }
            
            .customer-number {
                font-size: 14px !important;
                font-weight: bold;
            }
        }
        
        .student-photo {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            object-fit: cover;
            border: 2px solid #dee2e6;
        }
        
        .student-photo-placeholder {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            background-color: #e9ecef;
            display: flex;
            align-items: center;
            justify-content: center;
            color: #6c757d;
            font-size: 14px;
            border: 2px solid #dee2e6;
        }
        
        .customer-list-card {
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            border: 1px solid rgba(0, 0, 0, 0.125);
        }
        
        .search-box {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        
        .customer-number {
            font-weight: bold;
            color: #007bff;
            font-size: 1.1em;
        }
        
        .student-id {
            font-family: monospace;
            background-color: #e9ecef;
            padding: 2px 5px;
            border-radius: 3px;
        }
    </style>
</head>
<body>
    <!-- Navigation -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary no-print">
        <div class="container-fluid">
            <a class="navbar-brand" href="<?php echo ($user_role == 'teacher') ? 'teachers/dashboard.php' : 'index.php'; ?>">
                <i class="fas fa-graduation-cap"></i> স্কুল ম্যানেজমেন্ট সিস্টেম
            </a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="<?php echo ($user_role == 'teacher') ? 'teachers/dashboard.php' : 'index.php'; ?>">
                    <i class="fas fa-home"></i> হোম
                </a>
                <a class="nav-link" href="logout.php">
                    <i class="fas fa-sign-out-alt"></i> লগআউট
                </a>
            </div>
        </div>
    </nav>

    <!-- Print Header -->
    <div class="container-fluid d-none d-print-block mb-4">
        <div class="text-center">
            <h2>ছাত্র-ছাত্রীদের গ্রাহক নম্বর তালিকা</h2>
            <p>প্রিন্ট তারিখ: <?php echo date('d/m/Y'); ?></p>
            <?php 
            $all_filters = [];
            if ($session_filter) $all_filters[] = "সেশন: " . htmlspecialchars($session_filter);
            if ($class_filter > 0) {
                $class_name = '';
                foreach ($classes as $class) {
                    if ($class['id'] == $class_filter) {
                        $class_name = $class['name'] . ' (' . $class['section'] . ')';
                        break;
                    }
                }
                $all_filters[] = "ক্লাস: " . htmlspecialchars($class_name);
            }
            if ($group_filter) $all_filters[] = "গ্রুপ: " . htmlspecialchars($group_filter);
            if ($search_customer) $all_filters[] = "গ্রাহক নম্বর: " . htmlspecialchars($search_customer);
            if ($search_student_id) $all_filters[] = "ছাত্র আইডি: " . htmlspecialchars($search_student_id);
            if ($search_name) $all_filters[] = "নাম: " . htmlspecialchars($search_name);
            
            if (!empty($all_filters)): ?>
                <p>ফিল্টার: <?php echo implode(', ', $all_filters); ?></p>
            <?php endif; ?>
        </div>
    </div>

    <div class="container-fluid mt-4">
        <div class="row">
            <div class="col-md-12">
                <div class="card customer-list-card">
                    <div class="card-header bg-primary text-white">
                        <h5 class="mb-0">
                            <i class="fas fa-list"></i> ছাত্র-ছাত্রীদের গ্রাহক নম্বর তালিকা
                        </h5>
                    </div>
                    <div class="card-body">
                        <?php if ($error_message): ?>
                            <div class="alert alert-danger">
                                <i class="fas fa-exclamation-triangle"></i> <?php echo $error_message; ?>
                            </div>
                        <?php endif; ?>
                        
                        <!-- Search Filters -->
                        <div class="search-box no-print">
                            <form method="GET" class="row g-3">
                                <div class="col-md-3">
                                    <label for="session" class="form-label">সেশন</label>
                                    <select class="form-select" id="session" name="session">
                                        <option value="">সকল সেশন</option>
                                        <?php foreach ($sessions as $session): ?>
                                            <option value="<?php echo htmlspecialchars($session); ?>" 
                                                    <?php echo ($session_filter == $session) ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars($session); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label for="class_id" class="form-label">ক্লাস</label>
                                    <select class="form-select" id="class_id" name="class_id">
                                        <option value="0">সকল ক্লাস</option>
                                        <?php foreach ($classes as $class): ?>
                                            <option value="<?php echo $class['id']; ?>" 
                                                    <?php echo ($class_filter == $class['id']) ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars($class['name'] . ' (' . $class['section'] . ')'); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label for="group_name" class="form-label">গ্রুপ</label>
                                    <select class="form-select" id="group_name" name="group_name">
                                        <option value="">সকল গ্রুপ</option>
                                        <?php foreach ($groups as $group): ?>
                                            <option value="<?php echo htmlspecialchars($group); ?>" 
                                                    <?php echo ($group_filter == $group) ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars($group); ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label for="search_customer" class="form-label">গ্রাহক নম্বর</label>
                                    <input type="text" class="form-control" id="search_customer" name="search_customer" 
                                           value="<?php echo htmlspecialchars($search_customer); ?>" 
                                           placeholder="গ্রাহক নম্বর লিখুন...">
                                </div>
                                <div class="col-md-3">
                                    <label for="search_student_id" class="form-label">ছাত্র আইডি</label>
                                    <input type="text" class="form-control" id="search_student_id" name="search_student_id" 
                                           value="<?php echo htmlspecialchars($search_student_id); ?>" 
                                           placeholder="ছাত্র আইডি লিখুন...">
                                </div>
                                <div class="col-md-3">
                                    <label for="search_name" class="form-label">নাম</label>
                                    <input type="text" class="form-control" id="search_name" name="search_name" 
                                           value="<?php echo htmlspecialchars($search_name); ?>" 
                                           placeholder="ছাত্রের নাম লিখুন...">
                                </div>
                                <div class="col-12">
                                    <button type="submit" class="btn btn-primary">
                                        <i class="fas fa-search"></i> খুঁজুন
                                    </button>
                                    <a href="fixed_student_list.php" class="btn btn-secondary">
                                        <i class="fas fa-sync"></i> রিসেট
                                    </a>
                                    <button type="button" class="btn btn-success" onclick="window.print()">
                                        <i class="fas fa-print"></i> প্রিন্ট
                                    </button>
                                </div>
                            </form>
                        </div>

                        <!-- Customer Numbers Table -->
                        <div class="table-responsive">
                            <table class="table table-striped table-hover">
                                <thead class="table-dark">
                                    <tr>
                                        <th scope="col">#</th>
                                        <th scope="col">ছবি</th>
                                        <th scope="col">গ্রাহক নম্বর</th>
                                        <th scope="col">ছাত্র আইডি</th>
                                        <th scope="col">আবেদন নম্বর</th>
                                        <th scope="col">ছাত্রের নাম</th>
                                        <th scope="col">ক্লাস</th>
                                        <th scope="col">গ্রুপ</th>
                                        <th scope="col">রোল নম্বর</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php if (count($students) > 0): ?>
                                        <?php $counter = 1; ?>
                                        <?php foreach ($students as $student): ?>
                                            <tr>
                                                <td><?php echo $counter++; ?></td>
                                                <td>
                                                    <?php 
                                                    $photo_path = '';
                                                    if (!empty($student['photo'])) {
                                                        $possible_paths = [
                                                            'uploads/students/' . $student['photo'],
                                                            '../uploads/students/' . $student['photo'],
                                                            'uploads/students/' . basename($student['photo']),
                                                            '../uploads/students/' . basename($student['photo'])
                                                        ];
                                                        
                                                        foreach ($possible_paths as $path) {
                                                            if (file_exists($path)) {
                                                                $photo_path = $path;
                                                                break;
                                                            }
                                                        }
                                                    }
                                                    
                                                    if ($photo_path): ?>
                                                        <img src="<?php echo htmlspecialchars($photo_path); ?>" 
                                                             alt="Photo" class="student-photo">
                                                    <?php else: ?>
                                                        <div class="student-photo-placeholder">
                                                            <i class="fas fa-user"></i>
                                                        </div>
                                                    <?php endif; ?>
                                                </td>
                                                <td class="customer-number"><?php echo htmlspecialchars($student['customer_number']); ?></td>
                                                <td><span class="student-id"><?php echo htmlspecialchars($student['student_id']); ?></span></td>
                                                <td><?php echo htmlspecialchars($student['application_no']); ?></td>
                                                <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                                <td>
                                                    <?php
                                                    $class_stmt = $pdo->prepare("SELECT name, section FROM classes WHERE id = ?");
                                                    $class_stmt->execute([$student['class_id']]);
                                                    $class = $class_stmt->fetch();
                                                    echo $class ? htmlspecialchars($class['name'] . ' (' . $class['section'] . ')') : 'N/A';
                                                    ?>
                                                </td>
                                                <td>
                                                    <?php echo !empty($student['group_name']) ? htmlspecialchars($student['group_name']) : '-'; ?>
                                                </td>
                                                <td><?php echo htmlspecialchars($student['roll_number']); ?></td>
                                            </tr>
                                        <?php endforeach; ?>
                                    <?php else: ?>
                                        <tr>
                                            <td colspan="9" class="text-center">
                                                <i class="fas fa-info-circle text-info"></i> 
                                                কোন ছাত্র-ছাত্রী পাওয়া যায়নি
                                            </td>
                                        </tr>
                                    <?php endif; ?>
                                </tbody>
                            </table>
                        </div>

                        <?php if (count($students) > 0): ?>
                            <div class="mt-3">
                                <p class="text-muted">
                                    <i class="fas fa-info-circle"></i> 
                                    মোট ছাত্র-ছাত্রী: <?php echo count($students); ?>
                                </p>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

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