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

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

// Get filters
$search_class = isset($_GET['search_class']) ? (int)$_GET['search_class'] : 0;
$search_section = isset($_GET['search_section']) ? $_GET['search_section'] : '';
$search_session = isset($_GET['search_session']) ? $_GET['search_session'] : date('Y');
$search_subject = isset($_GET['search_subject']) ? (int)$_GET['search_subject'] : 0;
$date_range = isset($_GET['date_range']) ? $_GET['date_range'] : date('Y-m');

// Get data for dropdowns
$classes = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section")->fetchAll();
$sessions = $pdo->query("SELECT DISTINCT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC")->fetchAll();
if (empty($sessions)) {
    // Fallback to get sessions from students table if academic_sessions table doesn't exist
    $sessions = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session DESC")->fetchAll(PDO::FETCH_COLUMN);
    $formatted_sessions = [];
    foreach ($sessions as $session) {
        $formatted_sessions[] = ['session_name' => $session];
    }
    $sessions = $formatted_sessions;
}
$subjects = $pdo->query("SELECT id, name, code FROM subjects WHERE status = 'active' ORDER BY name")->fetchAll();

// Get students based on filters
$students = [];
if ($search_class || $search_session) {
    $sql = "SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.class_id, 
                   c.name as class_name, c.section
            FROM students s
            JOIN classes c ON s.class_id = c.id
            WHERE s.status = 'active'";
    
    $params = [];
    if ($search_class) {
        $sql .= " AND s.class_id = ?";
        $params[] = $search_class;
    }
    if ($search_session) {
        $sql .= " AND s.session = ?";
        $params[] = $search_session;
    }
    if ($search_section) {
        $sql .= " AND c.section = ?";
        $params[] = $search_section;
    }
    
    $sql .= " ORDER BY c.name, c.section, s.roll_number, s.first_name";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll();
}

// Get subject name if subject is selected
$subject_name = '';
if ($search_subject) {
    $stmt = $pdo->prepare("SELECT name FROM subjects WHERE id = ?");
    $stmt->execute([$search_subject]);
    $subject_name = $stmt->fetchColumn();
}
?>

<!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.3.0-alpha1/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">
    <style>
        @media print {
            body {
                background-color: white;
                padding: 0;
                margin: 0;
            }
            .no-print {
                display: none !important;
            }
            .print-container {
                box-shadow: none !important;
                margin: 0 !important;
                padding: 0 !important;
            }
        }
        
        .print-container {
            max-width: 21cm; /* A4 width */
            margin: 20px auto;
            padding: 20px;
            background: white;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
            min-height: 29.7cm; /* A4 height */
        }
        
        .attendance-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        
        .attendance-table th, .attendance-table td {
            border: 1px solid #000;
            padding: 8px;
            text-align: center;
            vertical-align: middle;
        }
        
        .attendance-table th {
            background-color: #f8f9fa;
            font-weight: bold;
        }
        
        .student-name-cell {
            text-align: left;
            min-width: 150px;
        }
        
        .signature-cell {
            height: 50px;
        }
        
        .header-info {
            margin-bottom: 20px;
        }
        
        .school-name {
            font-size: 24px;
            font-weight: bold;
            text-align: center;
            margin-bottom: 5px;
        }
        
        .report-title {
            font-size: 20px;
            font-weight: bold;
            text-align: center;
            margin-bottom: 15px;
            text-decoration: underline;
        }
        
        .filter-info {
            text-align: center;
            margin-bottom: 15px;
            font-size: 16px;
        }
        
        .responsive-table {
            overflow-x: auto;
        }
        
        .fourth-subject-cell {
            background-color: #fff3cd;
            font-weight: bold;
        }
    </style>
</head>
<body class="bg-light">
    <div class="print-container">
        <div class="header-info">
            <div class="school-name">বিদ্যা মন্দির উচ্চ বিদ্যালয়</div>
            <div class="report-title">উপস্থিতি শীট</div>
            <div class="filter-info">
                <?php if ($search_class): ?>
                    <?php 
                    $class_info = null;
                    foreach ($classes as $class) {
                        if ($class['id'] == $search_class) {
                            $class_info = $class;
                            break;
                        }
                    }
                    if ($class_info) echo "ক্লাস: " . $class_info['name'] . ' - ' . $class_info['section'];
                    ?>
                <?php endif; ?>
                
                <?php if ($search_session): ?>
                    | সেশন: <?php echo $search_session; ?>
                <?php endif; ?>
                
                <?php if ($search_subject && $subject_name): ?>
                    | বিষয়: <?php echo $subject_name; ?>
                <?php endif; ?>
            </div>
        </div>
        
        <div class="responsive-table">
            <?php if (!empty($students)): ?>
                <?php if ($search_class && $search_session): ?>
                <table class="attendance-table">
                    <thead>
                        <tr>
                            <th rowspan="2">ক্রমিক নং</th>
                            <th rowspan="2" class="student-name-cell">ছাত্র/ছাত্রীর নাম</th>
                            <th rowspan="2">রোল</th>
                            <th colspan="31" class="text-center">তারিখ (মাস: <?php echo date('F Y', mktime(0, 0, 0, substr($date_range ?? date('Y-m'), 5, 2), 1, substr($date_range ?? date('Y-m'), 0, 4))); ?>)</th>
                            <th rowspan="2">সর্বমোট<br>উপস্থিতি</th>
                            <th rowspan="2">সর্বমোট<br>অনুপস্থিতি</th>
                            <th rowspan="2">উপস্থিতি<br>হার (%)</th>
                            <th rowspan="2">৪র্থ বিষয়</th>
                        </tr>
                        <tr>
                            <?php 
                            $year_month = $date_range ?? date('Y-m');
                            $year = substr($year_month, 0, 4);
                            $month = substr($year_month, 5, 2);
                            $days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);
                            for ($day = 1; $day <= 31; $day++): 
                            ?>
                                <th><?php echo $day <= $days_in_month ? $day : ''; ?></th>
                            <?php endfor; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($students as $index => $student): 
                            // Get attendance data for this student for the selected month
                            $monthly_attendance = [];
                            $present_count = 0;
                            $absent_count = 0;
                            
                            for ($day = 1; $day <= $days_in_month; $day++) {
                                $date_str = sprintf('%04d-%02d-%02d', $year, $month, $day);
                                $stmt = $pdo->prepare("SELECT status FROM attendance WHERE student_id = ? AND date = ?");
                                $stmt->execute([$student['id'], $date_str]);
                                $attendance = $stmt->fetch();
                                
                                if ($attendance) {
                                    $monthly_attendance[$day] = $attendance['status'];
                                    if ($attendance['status'] == 'present') {
                                        $present_count++;
                                    } elseif ($attendance['status'] == 'absent') {
                                        $absent_count++;
                                    }
                                } else {
                                    $monthly_attendance[$day] = '';
                                }
                            }
                            
                            $total_days = $present_count + $absent_count;
                            $attendance_rate = $total_days > 0 ? round(($present_count * 100) / $total_days, 2) : 0;
                            
                            // Check if this student has a 4th subject
                            $stmt_check = $pdo->prepare("SELECT COUNT(*) FROM student_subjects WHERE student_id = ? AND is_fourth_subject = 1");
                            $stmt_check->execute([$student['id']]);
                            $has_fourth_subject = $stmt_check->fetchColumn() > 0;
                        ?>
                            <tr>
                                <td><?php echo $index + 1; ?></td>
                                <td class="student-name-cell"><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                <td><?php echo $student['roll_number']; ?></td>
                                <?php for ($day = 1; $day <= 31; $day++): ?>
                                    <td><?php echo ($day <= $days_in_month && isset($monthly_attendance[$day])) ? strtoupper(substr($monthly_attendance[$day], 0, 1)) : ''; ?></td>
                                <?php endfor; ?>
                                <td><?php echo $present_count; ?></td>
                                <td><?php echo $absent_count; ?></td>
                                <td><?php echo $attendance_rate; ?>%</td>
                                <td class="<?php echo $has_fourth_subject ? 'fourth-subject-cell' : ''; ?>">
                                    <?php
                                    if ($has_fourth_subject) {
                                        echo '<span class="badge bg-warning text-dark">৪র্থ</span>';
                                    } else {
                                        echo 'নেই';
                                    }
                                    ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <?php else: ?>
                <div class="alert alert-warning text-center">
                    <i class="fas fa-exclamation-triangle fs-1 mb-3"></i>
                    <h5>অনুগ্রহ করে ক্লাস এবং সেশন নির্বাচন করুন</h5>
                    <p class="mb-0">উপস্থিতি শীট দেখার জন্য কমপক্ষে ক্লাস এবং সেশন নির্বাচন করা আবশ্যিক</p>
                </div>
                <?php endif; ?>
                
                <!-- Signature section -->
                <div class="mt-5" style="margin-top: 100px;">
                    <div class="row">
                        <div class="col-4 text-center">
                            <div class="signature-cell">&nbsp;</div>
                            <div>শিক্ষকের স্বাক্ষর</div>
                        </div>
                        <div class="col-4 text-center">
                            <div class="signature-cell">&nbsp;</div>
                            <div>প্রধান শিক্ষকের স্বাক্ষর</div>
                        </div>
                        <div class="col-4 text-center">
                            <div class="signature-cell">&nbsp;</div>
                            <div>অভিভাবকের স্বাক্ষর</div>
                        </div>
                    </div>
                </div>
            <?php else: ?>
                <div class="alert alert-info text-center">
                    <i class="fas fa-info-circle fs-1 mb-3"></i>
                    <h5>কোন ছাত্র-ছাত্রী পাওয়া যায়নি</h5>
                    <p class="mb-0">ফিল্টার পরিবর্তন করে আবার চেষ্টা করুন</p>
                </div>
            <?php endif; ?>
        </div>
    </div>

    <!-- Filter Panel -->
    <div class="container-fluid fixed-bottom bg-white shadow no-print p-3 border-top">
        <div class="row g-3 align-items-end">
            <div class="col-md-2">
                <label class="form-label">ক্লাস</label>
                <select class="form-select" id="search_class" name="search_class" onchange="updateFilters()">
                    <option value="">সব ক্লাস</option>
                    <?php foreach ($classes as $class): ?>
                        <option value="<?php echo $class['id']; ?>" <?php echo ($search_class == $class['id']) ? 'selected' : ''; ?>>
                            <?php echo $class['name'] . ' - ' . $class['section']; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            
            <div class="col-md-2">
                <label class="form-label">সেশন</label>
                <select class="form-select" id="search_session" name="search_session" onchange="updateFilters()">
                    <option value="">সব সেশন</option>
                    <?php foreach ($sessions as $session): ?>
                        <option value="<?php echo $session['session_name']; ?>" <?php echo ($search_session == $session['session_name']) ? 'selected' : ''; ?>>
                            <?php echo $session['session_name']; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            
            <div class="col-md-3">
                <label class="form-label">বিষয়</label>
                <select class="form-select" id="search_subject" name="search_subject" onchange="updateFilters()">
                    <option value="">সব বিষয়</option>
                    <?php foreach ($subjects as $subject): ?>
                        <option value="<?php echo $subject['id']; ?>" <?php echo ($search_subject == $subject['id']) ? 'selected' : ''; ?>>
                            <?php echo $subject['name'] . ' (' . $subject['code'] . ')'; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            
            <div class="col-md-3">
                <label class="form-label">তারিখ রেঞ্জ</label>
                <input type="month" class="form-control" id="date_range" name="date_range" value="<?php echo $date_range; ?>">
            </div>
            
            <div class="col-md-2">
                <button class="btn btn-primary w-100" onclick="window.print()">
                    <i class="fas fa-print me-1"></i> প্রিন্ট
                </button>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function updateFilters() {
            const classId = document.getElementById('search_class').value;
            const session = document.getElementById('search_session').value;
            const subject = document.getElementById('search_subject').value;
            const dateRange = document.getElementById('date_range').value;
            
            let params = new URLSearchParams();
            if (classId) params.append('search_class', classId);
            if (session) params.append('search_session', session);
            if (subject) params.append('search_subject', subject);
            if (dateRange) params.append('date_range', dateRange);
            
            window.location.href = '?' + params.toString();
        }
        
        // Initialize the page with filters
        document.addEventListener('DOMContentLoaded', function() {
            // Set the date range to current month if not set
            const dateRangeInput = document.getElementById('date_range');
            if (!dateRangeInput.value) {
                dateRangeInput.value = new Date().toISOString().slice(0, 7);
            }
        });
    </script>
</body>
</html>