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

try {
    $student_id = (int)($_POST['student_id'] ?? 0);
    $academic_year = trim($_POST['academic_year'] ?? '');
    $semester = (int)($_POST['semester'] ?? 0);
    $course_id = (int)($_POST['course_id'] ?? 0);
    $regenerate = ($_POST['regenerate'] ?? 'false') === 'true';

    // Validation
    if ($student_id <= 0) { throw new Exception('Valid Student ID is required.'); }
    if (empty($academic_year)) { throw new Exception('Academic Year is required.'); }
    if ($semester <= 0) { throw new Exception('Valid Semester is required.'); }
    if ($course_id <= 0) { throw new Exception('Valid Course ID is required.'); }

    // Process academic year format (same as report API)
    // Support AY already including Odd/Even or compute from semester
    // Also handle cases like "1997-1998 Odd Odd" by cleaning up duplicate terms
    $ay_term = (preg_match('/\b(Odd|Even)\b$/', $academic_year)) ? $academic_year : ($academic_year . ' ' . (($semester % 2)===1 ? 'Odd' : 'Even'));
    // Clean up duplicate terms like "1997-1998 Odd Odd" -> "1997-1998 Odd"
    $ay_term = preg_replace('/\s+(Odd|Even)\s+\1$/', ' $1', $ay_term);

    // Check if marksheet already exists
    $stmt = $pdo->prepare("SELECT * FROM student_marksheets WHERE student_id = ? AND academic_year = ? AND semester = ? AND course_id = ?");
    $stmt->execute([$student_id, $academic_year, $semester, $course_id]);
    $existing = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($existing && !$regenerate) {
        // Get the marksheet data for existing marksheet
        $marksheet_data = json_decode($existing['marksheet_data'], true);
        
        echo json_encode([
            'status' => 'success',
            'message' => 'Marksheet already exists',
            'folio_number' => $existing['folio_number'],
            'marksheet_id' => $existing['marksheet_id'],
            'marksheet_data' => $marksheet_data,
            'existing' => true
        ]);
        exit;
    }

    // Get student details
    $stmt = $pdo->prepare("
        SELECT s.*, c.course_name, c.course_code, col.college_name, col.college_code
        FROM students s 
        LEFT JOIN courses c ON s.course_id = c.course_id
        LEFT JOIN colleges col ON s.college_id = col.college_id
        WHERE s.student_id = ?
    ");
    $stmt->execute([$student_id]);
    $student = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$student) { throw new Exception('Student not found.'); }

    // Generate folio number
    $folio_number = generateFolioNumber($pdo, $academic_year, $course_id, $student);

    // Get student results for the semester
    $results = getStudentResults($pdo, $student_id, $academic_year, $semester, $course_id, $ay_term);

    // Prepare marksheet data
    $marksheet_data = [
        'student' => $student,
        'academic_year' => $academic_year,
        'semester' => $semester,
        'course' => [
            'course_id' => $course_id,
            'course_name' => $student['course_name'],
            'course_code' => $student['course_code']
        ],
        'college' => [
            'college_name' => $student['college_name'],
            'college_code' => $student['college_code']
        ],
        'results' => $results,
        'generated_date' => date('Y-m-d H:i:s'),
        'generated_by' => $_SESSION['staff_id'] ?? null
    ];

    // Save or update marksheet
    if ($existing) {
        $stmt = $pdo->prepare("
            UPDATE student_marksheets 
            SET folio_number = ?, marksheet_data = ?, status = 'Generated', updated_at = CURRENT_TIMESTAMP
            WHERE marksheet_id = ?
        ");
        $stmt->execute([$folio_number, json_encode($marksheet_data), $existing['marksheet_id']]);
        $marksheet_id = $existing['marksheet_id'];
    } else {
        $stmt = $pdo->prepare("
            INSERT INTO student_marksheets (folio_number, student_id, academic_year, semester, course_id, generated_by, marksheet_data)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $folio_number, $student_id, $academic_year, $semester, $course_id,
            $_SESSION['staff_id'] ?? null, json_encode($marksheet_data)
        ]);
        $marksheet_id = $pdo->lastInsertId();
    }

    // Log the action
    write_audit_log($pdo, [
        'module' => 'Marksheet',
        'action' => $existing ? 'regenerate' : 'generate',
        'entity_table' => 'student_marksheets',
        'entity_id' => $marksheet_id,
        'after_data' => [
            'folio_number' => $folio_number,
            'student_id' => $student_id,
            'academic_year' => $academic_year,
            'semester' => $semester
        ],
        'message' => 'Marksheet ' . ($existing ? 'regenerated' : 'generated')
    ]);

    echo json_encode([
        'status' => 'success',
        'message' => 'Marksheet generated successfully',
        'folio_number' => $folio_number,
        'marksheet_id' => $marksheet_id,
        'marksheet_data' => $marksheet_data
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}

function generateFolioNumber($pdo, $academic_year, $course_id, $student) {
    // Get or create folio sequence
    $stmt = $pdo->prepare("
        INSERT INTO folio_sequence (academic_year, course_id, last_folio_number) 
        VALUES (?, ?, 0) 
        ON DUPLICATE KEY UPDATE last_folio_number = last_folio_number
    ");
    $stmt->execute([$academic_year, $course_id]);

    // Get next folio number
    $stmt = $pdo->prepare("
        SELECT last_folio_number FROM folio_sequence 
        WHERE academic_year = ? AND course_id = ? FOR UPDATE
    ");
    $stmt->execute([$academic_year, $course_id]);
    $seq = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $next_number = $seq['last_folio_number'] + 1;
    
    // Update sequence
    $stmt = $pdo->prepare("
        UPDATE folio_sequence 
        SET last_folio_number = ? 
        WHERE academic_year = ? AND course_id = ?
    ");
    $stmt->execute([$next_number, $academic_year, $course_id]);

    // Generate folio number format: U-{YEAR}{COURSE_CODE}{SEQUENCE}
    $year = substr($academic_year, 0, 4);
    $course_code = $student['course_code'] ?? 'COURSE';
    $folio_number = sprintf('U-%s%s%06d', $year, $course_code, $next_number);
    
    return $folio_number;
}

function getStudentResults($pdo, $student_id, $academic_year, $semester, $course_id, $ay_term) {
    // Get subjects for the semester
    $stmt = $pdo->prepare("
        SELECT DISTINCT s.subject_id, s.subject_name, s.subject_code, s.credits, s.semester
        FROM subjects s
        INNER JOIN exam_subjects es ON s.subject_id = es.subject_id
        INNER JOIN exams e ON es.exam_id = e.exam_id
        WHERE e.academic_year = ? AND s.course_id = ? AND s.semester = ?
        ORDER BY s.subject_code
    ");
    $stmt->execute([$academic_year, $course_id, $semester]);
    $subjects = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $results = [];
    foreach ($subjects as $subject) {
        // Get CI settings for calculation method
        $stmt = $pdo->prepare("SELECT calculation_method FROM ci_assessment_settings WHERE course_id=? AND exam_type='Internal' ORDER BY setting_id DESC LIMIT 1");
        $stmt->execute([$course_id]);
        $ciSettings = $stmt->fetch(PDO::FETCH_ASSOC);
        $calculationMethod = $ciSettings['calculation_method'] ?? 'best2_avg';

        // Get internal marks based on calculation method from CI1, CI2, CI3
        // First, get all CI marks for this subject
        $stmt = $pdo->prepare("
            SELECT im.marks_obtained, et.type_name
            FROM internal_marks im
            INNER JOIN internal_exams ie ON im.internal_exam_id = ie.internal_exam_id
            INNER JOIN exam_types et ON ie.exam_type_id = et.exam_type_id
            WHERE im.student_id = ? AND ie.subject_id = ? AND ie.academic_year = ?
            AND et.type_name IN ('CI1', 'CI2', 'CI3')
        ");
        $stmt->execute([$student_id, $subject['subject_id'], $ay_term]);
        $ciMarks = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $ciMarks[] = (float)$row['marks_obtained'];
        }

        // Calculate internal marks based on calculation method
        $internal_marks = 0;
        if (count($ciMarks) >= 1) {
            if ($calculationMethod === 'best1') {
                // Best of 1
                $internal_marks = max($ciMarks);
            } else if ($calculationMethod === 'avg3' && count($ciMarks) >= 3) {
                // Average of 3
                $internal_marks = array_sum($ciMarks) / count($ciMarks);
            } else if ($calculationMethod === 'best2_avg' && count($ciMarks) >= 2) {
                // Best 2 average (default behavior)
                rsort($ciMarks);
                $best2 = array_slice($ciMarks, 0, 2);
                $internal_marks = array_sum($best2) / count($best2);
            } else if (count($ciMarks) >= 2) {
                // Fallback to best 2 average if not enough marks for selected method
                rsort($ciMarks);
                $best2 = array_slice($ciMarks, 0, 2);
                $internal_marks = array_sum($best2) / count($best2);
            } else {
                // Just use the single mark if only one exists
                $internal_marks = $ciMarks[0];
            }
            $internal_marks = round($internal_marks, 1);
        }

        // Get external marks from subject_results (same as report)
        $stmt = $pdo->prepare("
            SELECT sr.external_marks, sr.internal_marks, sr.result
            FROM subject_results sr
            INNER JOIN exams e ON sr.exam_id = e.exam_id
            WHERE sr.student_id = ? AND sr.subject_id = ? AND e.academic_year = ?
        ");
        $stmt->execute([$student_id, $subject['subject_id'], $ay_term]);
        $external_result = $stmt->fetch(PDO::FETCH_ASSOC);
        $external_marks = $external_result ? $external_result['external_marks'] : 0;
        
        
        // If no external marks from subject_results, try valuation_marks as fallback
        if ($external_marks == 0) {
            $stmt = $pdo->prepare("
                SELECT vm.total_mark as external_marks
                FROM valuation_marks vm
                INNER JOIN student_registrations sr ON vm.registration_id = sr.registration_id
                INNER JOIN exam_subjects es ON vm.exam_subject_id = es.exam_subject_id
                INNER JOIN exams e ON es.exam_id = e.exam_id
                WHERE sr.student_id = ? AND es.subject_id = ? AND e.academic_year = ?
            ");
            $stmt->execute([$student_id, $subject['subject_id'], $ay_term]);
            $vm_result = $stmt->fetch(PDO::FETCH_ASSOC);
            $external_marks = $vm_result ? $vm_result['external_marks'] : 0;
            
        }

        $total_marks = $internal_marks + $external_marks;

        // Get grade based on external marks (same logic as report)
        $grade = getGradeForMarks($pdo, $course_id, $external_marks);
        
        // Determine result based on grading system
        $result = 'FAIL';
        if ($external_marks > 0 && $grade && $grade['is_pass']) {
            $result = 'PASS';
        } elseif ($external_marks == 0 && $internal_marks == 0) {
            $result = 'Absent';
        }
        
        $results[] = [
            'subject_id' => $subject['subject_id'],
            'subject_name' => $subject['subject_name'],
            'subject_code' => $subject['subject_code'],
            'credits' => $subject['credits'],
            'internal_marks' => $internal_marks,
            'external_marks' => $external_marks,
            'total_marks' => $total_marks,
            'grade' => $grade ? $grade['grade_name'] : 'F',
            'grade_point' => $grade ? $grade['grade_point'] : 0,
            'result' => $result
        ];
    }

    return $results;
}

function getGradeForMarks($pdo, $course_id, $marks) {
    // Get grading system for course
    $stmt = $pdo->prepare("
        SELECT g.grade_name, g.grade_point, g.is_pass
        FROM grades g
        INNER JOIN grading_systems gs ON g.grading_system_id = gs.grading_system_id
        WHERE gs.course_id = ? AND gs.is_active = 1
        AND ? BETWEEN g.min_marks AND g.max_marks
        ORDER BY g.min_marks ASC
        LIMIT 1
    ");
    $stmt->execute([$course_id, $marks]);
    $grade = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$grade) {
        // Fallback to any active grading system
        $stmt = $pdo->prepare("
            SELECT g.grade_name, g.grade_point, g.is_pass
            FROM grades g
            INNER JOIN grading_systems gs ON g.grading_system_id = gs.grading_system_id
            WHERE gs.is_active = 1
            AND ? BETWEEN g.min_marks AND g.max_marks
            ORDER BY g.min_marks ASC
            LIMIT 1
        ");
        $stmt->execute([$marks]);
        $grade = $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    return $grade ?: ['grade_name' => 'F', 'grade_point' => 0, 'is_pass' => 0];
}
?>