<?php
require_once '../../config/config.php';
header('Content-Type: application/json');

try {
    $academicYear = $_POST['academic_year'] ?? '';
    $examId = $_POST['exam_id'] ?? '';
    $courseId = $_POST['course_id'] ?? '';
    
    if (!$academicYear && !$examId && !$courseId) {
        echo json_encode(['success' => false, 'message' => 'At least one filter (Academic Year, Exam, or Course) is required']);
        exit;
    }
    
    // Build exam filter query
    $examWhere = "WHERE e.status IN ('Draft', 'Announced', 'In Progress')";
    $examParams = [];
    
    if ($academicYear) {
        $examWhere .= " AND e.academic_year = ?";
        $examParams[] = $academicYear;
    }
    
    if ($examId) {
        $examWhere .= " AND e.exam_id = ?";
        $examParams[] = $examId;
    }
    
    // Get exams based on filters
    $examsSql = "SELECT e.exam_id, e.exam_name, e.academic_year, e.semester
                 FROM exams e
                 $examWhere
                 ORDER BY e.academic_year, e.semester";
    
    $examsStmt = $pdo->prepare($examsSql);
    $examsStmt->execute($examParams);
    $exams = $examsStmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($exams)) {
        // Debug: Let's check what exams exist
        $debugSql = "SELECT exam_id, exam_name, academic_year, semester, status FROM exams ORDER BY exam_id DESC LIMIT 10";
        $debugStmt = $pdo->prepare($debugSql);
        $debugStmt->execute();
        $debugExams = $debugStmt->fetchAll(PDO::FETCH_ASSOC);
        
        $debugInfo = [
            'query' => $examsSql,
            'params' => $examParams,
            'recent_exams' => $debugExams
        ];
        
        echo json_encode([
            'success' => false, 
            'message' => 'No exams found matching the criteria',
            'debug' => $debugInfo
        ]);
        exit;
    }
    
    $totalSynced = 0;
    $examsProcessed = 0;
    $errors = [];
    
    foreach ($exams as $exam) {
        // Build subjects query based on filters
        $subjectsWhere = "WHERE 1=1";
        $subjectsParams = [];
        
        // If course is specified, filter by course
        if ($courseId) {
            $subjectsWhere .= " AND s.course_id = ?";
            $subjectsParams[] = $courseId;
        } else {
            // If no course specified, try to match by semester
            $subjectsWhere .= " AND s.semester = ?";
            $subjectsParams[] = $exam['semester'];
        }
        
        // Get subjects for this course (or semester if no course specified)
        $subjectsSql = "SELECT s.subject_id, s.subject_name, s.subject_code, s.semester, c.course_id, c.course_name
                        FROM subjects s
                        LEFT JOIN courses c ON c.course_id = s.course_id
                        $subjectsWhere
                        ORDER BY c.course_name, s.semester, s.subject_name";
        
        $subjectsStmt = $pdo->prepare($subjectsSql);
        $subjectsStmt->execute($subjectsParams);
        $subjects = $subjectsStmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (empty($subjects)) {
            // Debug: Check what subjects exist for this course
            $debugSubjectsSql = "SELECT s.subject_id, s.subject_name, s.subject_code, s.semester, c.course_name
                                FROM subjects s
                                LEFT JOIN courses c ON c.course_id = s.course_id
                                WHERE s.course_id = ? OR ? = 0
                                ORDER BY s.semester, s.subject_name";
            $debugSubjectsStmt = $pdo->prepare($debugSubjectsSql);
            $debugSubjectsStmt->execute([$courseId, $courseId]);
            $debugSubjects = $debugSubjectsStmt->fetchAll(PDO::FETCH_ASSOC);
            
            $courseFilter = $courseId ? " for course ID $courseId" : "";
            $errors[] = "No subjects found for exam '{$exam['exam_name']}' (Semester: {$exam['semester']}$courseFilter) - skipped. Available subjects: " . json_encode($debugSubjects);
            continue;
        }
        
        // Check existing exam subjects for this exam
        $existingSql = "SELECT subject_id FROM exam_subjects WHERE exam_id = ?";
        $existingStmt = $pdo->prepare($existingSql);
        $existingStmt->execute([$exam['exam_id']]);
        $existingSubjects = $existingStmt->fetchAll(PDO::FETCH_COLUMN);
        
        $examSyncedCount = 0;
        
        // Insert subjects that don't already exist
        $syncedSubjects = [];
        $skippedSubjects = [];
        
        foreach ($subjects as $subject) {
            if (in_array($subject['subject_id'], $existingSubjects)) {
                $skippedSubjects[] = $subject['subject_name'] . " (already exists)";
                continue; // Skip existing subjects
            }
            
            try {
                $insertSql = "INSERT INTO exam_subjects (exam_id, subject_id, max_marks, pass_marks) 
                             VALUES (?, ?, 100, 40)";
                
                $insertStmt = $pdo->prepare($insertSql);
                $insertStmt->execute([$exam['exam_id'], $subject['subject_id']]);
                $examSyncedCount++;
                $totalSynced++;
                $syncedSubjects[] = $subject['subject_name'];
                
            } catch (Exception $e) {
                $errors[] = "Failed to insert subject '{$subject['subject_name']}' for exam '{$exam['exam_name']}': " . $e->getMessage();
            }
        }
        
        // Add detailed logging
        if ($examSyncedCount > 0 || count($skippedSubjects) > 0) {
            $details[] = [
                'exam_name' => $exam['exam_name'],
                'course_name' => $subjects[0]['course_name'] ?? 'Unknown',
                'semester' => $exam['semester'],
                'total_subjects_found' => count($subjects),
                'synced' => $examSyncedCount,
                'skipped' => count($skippedSubjects),
                'synced_subjects' => $syncedSubjects,
                'skipped_subjects' => $skippedSubjects
            ];
        }
        
        if ($examSyncedCount > 0) {
            $examsProcessed++;
        }
    }
    
    // Prepare response
    $response = [
        'success' => true,
        'total_synced' => $totalSynced,
        'exams_processed' => $examsProcessed,
        'total_exams' => count($exams),
        'filters' => [
            'academic_year' => $academicYear,
            'course_id' => $courseId
        ]
    ];
    
    if (!empty($errors)) {
        $response['warnings'] = $errors;
    }
    
    echo json_encode($response);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
}
?>