<?php
session_start();
require_once '../config/database.php';
require_once '../includes/site_settings.php';
require_once '../includes/functions.php'; // Include functions for grading calculations

// Get site settings
$settings = get_all_settings($pdo);
$site_name = $settings['site_name'] ?? 'বিদ্যা নিকেতন';
$site_address = $settings['site_address'] ?? 'ঠিকানা: গ্রাম/থানা, জেলা, বাংলাদেশ';
$logo_image = $settings['logo_image'] ?? null;
$logo_size = isset($settings['logo_size']) ? intval($settings['logo_size']) : 100;

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

// Get teacher's assigned classes if user is a teacher
$teacher_id = null;
$assigned_class_ids = [];
if ($_SESSION['user_role'] == 'teacher') {
    // Get teacher ID
    $stmt = $pdo->prepare("SELECT id FROM teachers WHERE user_id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $teacher = $stmt->fetch();
    if ($teacher) {
        $teacher_id = $teacher['id'];
        
        // Get assigned class IDs
        $stmt = $pdo->prepare("SELECT DISTINCT c.id FROM classes c WHERE (c.class_teacher_id = ? OR c.id IN (SELECT class_id FROM class_subjects WHERE teacher_id = ?)) AND c.status = 'active'");
        $stmt->execute([$teacher_id, $teacher_id]);
        $assigned_classes = $stmt->fetchAll();
        $assigned_class_ids = array_column($assigned_classes, 'id');
    }
}

// Get search parameters
$subject_code = $_GET['subject_code'] ?? '253_254'; // Default to 253_254
$class_id = $_GET['class_id'] ?? '';
$session = $_GET['session'] ?? date('Y');
$exam_type = $_GET['exam_type'] ?? '';

$subject_info = null;
$students = [];
$results = [];

// Process subject code range (e.g., 253_254)
function parseSubjectCodeRange($code) {
    if (strpos($code, '_') !== false) {
        $parts = explode('_', $code);
        if (count($parts) == 2) {
            $start = (int)$parts[0];
            $end = (int)$parts[1];
            $codes = [];
            for ($i = $start; $i <= $end; $i++) {
                $codes[] = (string)$i;
            }
            return $codes;
        }
    }
    return [$code];
}

// Function to auto-create subjects if they don't exist
function createSubjectIfNotExists($pdo, $subject_code) {
    $stmt = $pdo->prepare("SELECT id FROM subjects WHERE code = ?");
    $stmt->execute([$subject_code]);
    $existing_subject = $stmt->fetch();
    
    if ($existing_subject) {
        return $existing_subject['id'];
    }
    
    // Generate a default name for the subject based on the code
    $default_name = "Auto-created subject with code: $subject_code";
    
    // Insert the new subject
    $insert_stmt = $pdo->prepare(
        "INSERT INTO subjects (name, code, description, status, created_at, updated_at) 
         VALUES (?, ?, ?, 'active', NOW(), NOW())"
    );
    $insert_stmt->execute([$default_name, $subject_code, "Auto-created subject with code: $subject_code"]);
    
    // Return the ID of the newly created subject
    return $pdo->lastInsertId();
}

$subject_codes = parseSubjectCodeRange($subject_code);

// Auto-create subjects if they don't exist
foreach ($subject_codes as $code) {
    createSubjectIfNotExists($pdo, $code);
}

if (!empty($subject_codes) && $class_id && $session && $exam_type) {
    // Get subject info for the codes
    $placeholders = str_repeat('?,', count($subject_codes) - 1) . '?';
    $stmt = $pdo->prepare("SELECT * FROM subjects WHERE code IN ($placeholders)");
    $stmt->execute($subject_codes);
    $subjects = $stmt->fetchAll();
    
    if (!empty($subjects)) {
        $subject_info = $subjects[0]; // Use first subject as main info
        
        // Get students in this class
        $stmt = $pdo->prepare("
            SELECT s.* FROM students s 
            WHERE s.class_id = ? AND s.status = 'active'
            ORDER BY s.student_id
        ");
        $stmt->execute([$class_id]);
        $students = $stmt->fetchAll();
        
        // Get results for these subjects
        $subject_ids = array_column($subjects, 'id');
        $subject_placeholders = str_repeat('?,', count($subject_ids) - 1) . '?';
        
        $stmt = $pdo->prepare("
            SELECT r.*, s.student_id as roll_number, s.first_name, s.last_name
            FROM results r
            JOIN students s ON r.student_id = s.id
            WHERE s.class_id = ? AND r.session = ? AND r.exam_type = ? AND r.subject_id IN ($subject_placeholders)
        ");
        $params = array_merge([$class_id, $session, $exam_type], $subject_ids);
        $stmt->execute($params);
        $results = $stmt->fetchAll();
    }
}

// Get all classes for dropdown
if ($_SESSION['user_role'] == 'teacher' && !empty($assigned_class_ids)) {
    // Teacher: only show assigned classes
    $placeholders = str_repeat('?,', count($assigned_class_ids) - 1) . '?';
    $stmt = $pdo->prepare("SELECT * FROM classes WHERE id IN ($placeholders) AND status = 'active' ORDER BY name");
    $stmt->execute($assigned_class_ids);
    $all_classes = $stmt->fetchAll();
} else {
    // Admin: show all classes
    $stmt = $pdo->query("SELECT * FROM classes WHERE status = 'active' ORDER BY name");
    $all_classes = $stmt->fetchAll();
}

// Get available sessions
$stmt = $pdo->query("SELECT DISTINCT session FROM results ORDER BY session DESC");
$sessions = $stmt->fetchAll();

// Get available exam types
$stmt = $pdo->query("SELECT DISTINCT exam_type FROM results ORDER BY exam_type");
$exam_types = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>অটো-ক্রিয়েটেড বিষয় রিপোর্ট - <?php echo htmlspecialchars($site_name); ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="../assets/css/style.css" rel="stylesheet">
    <style>
        @media print {
            .no-print { display: none !important; }
            .container-fluid { max-width: 100% !important; }
            body { font-size: 10px; }
            .report-sheet { page-break-inside: avoid; }
            .table { font-size: 9px; }
        }
        .report-header {
            text-align: center;
            border-bottom: 2px solid #333;
            padding-bottom: 15px;
            margin-bottom: 20px;
        }
        .subject-range {
            background-color: #e7f3ff;
            padding: 10px;
            border-radius: 5px;
            margin-bottom: 15px;
        }
    </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="../index.php">
                <i class="fas fa-graduation-cap"></i> <?php echo htmlspecialchars($site_name); ?>
            </a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="../logout.php">
                    <i class="fas fa-sign-out-alt"></i> লগ আউট
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4">
        <!-- Search Form -->
        <div class="card no-print mb-4">
            <div class="card-header bg-primary text-white">
                <h5><i class="fas fa-table"></i> অটো-ক্রিয়েটেড বিষয় রিপোর্ট তৈরি করুন</h5>
            </div>
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label class="form-label">বিষয় কোড</label>
                        <input type="text" name="subject_code" class="form-control" value="<?php echo htmlspecialchars($subject_code); ?>" placeholder="যেমন: 253_254">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">ক্লাস নির্বাচন করুন</label>
                        <select name="class_id" class="form-select" required onchange="this.form.submit()">
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($all_classes as $c): ?>
                                <option value="<?php echo $c['id']; ?>" <?php echo $class_id == $c['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($c['name']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">সেশন</label>
                        <select name="session" class="form-select" required onchange="this.form.submit()">
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($sessions as $s): ?>
                                <option value="<?php echo $s['session']; ?>" <?php echo $session == $s['session'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($s['session']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">পরীক্ষার ধরন</label>
                        <select name="exam_type" class="form-select" required onchange="this.form.submit()">
                            <option value="">নির্বাচন করুন</option>
                            <?php foreach ($exam_types as $e): ?>
                                <option value="<?php echo $e['exam_type']; ?>" <?php echo $exam_type == $e['exam_type'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($e['exam_type']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                </form>
            </div>
        </div>

        <?php if ($subject_info && !empty($students)): ?>
        <!-- Print Controls -->
        <div class="text-center mb-3 no-print">
            <button onclick="window.print()" class="btn btn-success btn-lg me-2">
                <i class="fas fa-print"></i> প্রিন্ট করুন
            </button>
        </div>

        <!-- Report Sheet -->
        <div class="card report-sheet">
            <div class="card-header bg-light">
                <div class="report-header">
                    <?php if ($logo_image): ?>
                        <img src="../<?php echo htmlspecialchars($logo_image); ?>" alt="Logo" style="height: <?php echo $logo_size; ?>px; margin-bottom: 10px;">
                    <?php endif; ?>
                    <h3><?php echo htmlspecialchars($site_name); ?></h3>
                    <p><?php echo htmlspecialchars($site_address); ?></p>
                    <h4>অটো-ক্রিয়েটেড বিষয় রিপোর্ট</h4>
                    <div class="subject-range">
                        <strong>বিষয় কোড:</strong> <?php echo htmlspecialchars($subject_code); ?>
                        <?php if (count($subject_codes) > 1): ?>
                            (<?php echo implode(', ', $subject_codes); ?>)
                        <?php endif; ?>
                    </div>
                    <p><strong>ক্লাস:</strong> <?php echo htmlspecialchars($class_info['name'] ?? 'N/A'); ?> | 
                       <strong>সেশন:</strong> <?php echo htmlspecialchars($session); ?> | 
                       <strong>পরীক্ষা:</strong> <?php echo htmlspecialchars($exam_type); ?></p>
                </div>
            </div>
            
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead class="table-dark">
                            <tr>
                                <th>রোল</th>
                                <th>নাম</th>
                                <?php foreach ($subjects as $subject): ?>
                                    <th><?php echo htmlspecialchars($subject['name'] . ' (' . $subject['code'] . ')'); ?></th>
                                <?php endforeach; ?>
                                <th>গ্রেড</th>
                                <th>মেধা অবস্থান</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($students as $student): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($student['student_id'] ?? 'N/A'); ?></td>
                                <td><?php echo htmlspecialchars($student['first_name'] . ' ' . $student['last_name']); ?></td>
                                
                                <?php foreach ($subjects as $subject): ?>
                                <td>
                                    <?php
                                    $student_results = array_filter($results, function($r) use ($student, $subject) {
                                        return $r['student_id'] == $student['id'] && $r['subject_id'] == $subject['id'];
                                    });
                                    
                                    if (!empty($student_results)) {
                                        $result = array_values($student_results)[0];
                                        echo htmlspecialchars($result['grade'] ?? 'N/A');
                                    } else {
                                        echo 'N/A';
                                    }
                                    ?>
                                </td>
                                <?php endforeach; ?>
                                
                                <td>
                                    <?php
                                    // Calculate overall grade for the student
                                    $student_results = array_filter($results, function($r) use ($student) {
                                        return $r['student_id'] == $student['id'];
                                    });
                                    
                                    if (!empty($student_results)) {
                                        $grades = array_column($student_results, 'grade');
                                        // Simple implementation - you may want to enhance this
                                        $overall_grade = implode(', ', array_unique($grades));
                                        echo htmlspecialchars($overall_grade);
                                    } else {
                                        echo 'N/A';
                                    }
                                    ?>
                                </td>
                                <td>
                                    <?php
                                    // Calculate merit position based on grades
                                    $student_results = array_filter($results, function($r) use ($student) {
                                        return $r['student_id'] == $student['id'];
                                    });
                                    
                                    if (!empty($student_results)) {
                                        // Calculate GPA for this student
                                        $total_grade_points = 0;
                                        $subject_count = 0;
                                        $failed = false;
                                        
                                        foreach ($student_results as $result) {
                                            if ($result['grade'] == 'F' || $result['grade'] == 'Fails') {
                                                $failed = true;
                                                break;
                                            } else {
                                                // Convert grade to grade point
                                                $grade_point = 0;
                                                switch($result['grade']) {
                                                    case 'A+': $grade_point = 5.00; break;
                                                    case 'A': $grade_point = 4.00; break;
                                                    case 'A-': $grade_point = 3.70; break;
                                                    case 'B': $grade_point = 3.00; break;
                                                    case 'C': $grade_point = 2.00; break;
                                                    case 'D': $grade_point = 1.00; break;
                                                    default: $grade_point = 0.00; break;
                                                }
                                                $total_grade_points += $grade_point;
                                                $subject_count++;
                                            }
                                        }
                                        
                                        if ($failed) {
                                            echo 'F';
                                        } else if ($subject_count > 0) {
                                            $gpa = $total_grade_points / $subject_count;
                                            echo number_format($gpa, 2);
                                        } else {
                                            echo 'N/A';
                                        }
                                    } else {
                                        echo 'N/A';
                                    }
                                    ?>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
                
                <div class="mt-4">
                    <h5>বিস্তারিত তথ্য:</h5>
                    <ul>
                        <li>এই রিপোর্টটি অটো-ক্রিয়েটেড বিষয় কোড <?php echo htmlspecialchars($subject_code); ?> এর জন্য তৈরি করা হয়েছে</li>
                        <li>বিষয়টি স্বয়ংক্রিয়ভাবে তৈরি করা হয়েছে এবং রিপোর্ট আকারে প্রদর্শিত হচ্ছে</li>
                        <?php if (count($subject_codes) > 1): ?>
                            <li>বিষয় কোড রেঞ্জ: <?php echo implode(' - ', $subject_codes); ?></li>
                        <?php endif; ?>
                    </ul>
                </div>
            </div>
        </div>

        <!-- Print Controls -->
        <div class="text-center mt-3 no-print">
            <button onclick="window.print()" class="btn btn-success btn-lg me-2">
                <i class="fas fa-print"></i> প্রিন্ট করুন
            </button>
        </div>
        <?php elseif ($subject_code): ?>
        <div class="alert alert-warning">
            <h5><i class="fas fa-exclamation-triangle"></i> কোন ডেটা পাওয়া যায়নি</h5>
            <p>বিষয় কোড <?php echo htmlspecialchars($subject_code); ?> এর জন্য কোন ডেটা পাওয়া যায়নি।</p>
        </div>
        <?php endif; ?>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        function exportToExcel() {
            // This would require additional implementation
            alert('Excel export functionality would be implemented here');
        }
    </script>
</body>
</html>