<?php
session_start();
include 'includes/database.php';
include 'includes/functions.php';

//header('Content-Type: application/json');

$conn = new Functions();

try {
    // Validate required fields
    $required_fields = ['template_id', 'class', 'term', 'section', 'subject', 'student_name', 'reg_no', 'scores'];
    foreach ($required_fields as $field) {
        if (empty($_POST[$field])) {
            throw new Exception("$field is required");
        }
    }

    $template_id = (int)$_POST['template_id'];
    $class = $_POST['class'];
    $term = $_POST['term'];
    $section = $_POST['section'];
    $subject = $_POST['subject'];
    $exam_year = $_POST['exam_year'];
    $current_school_session = $_POST['current_school_session'];

    $student_names = $_POST['student_name'];
    $reg_nos = $_POST['reg_no'];
    $scores = $_POST['scores'];

    // Get template components
    $sql = "SELECT id, component_key, max_marks FROM assessment_components 
            WHERE template_id = :template_id ORDER BY display_order";
    $conn->query($sql);
    $conn->bind(':template_id', $template_id);
    $components = $conn->fetchMultiple();

    if (empty($components)) {
        throw new Exception("No components found for the selected template");
    }

    // Begin transaction
    $conn->query("START TRANSACTION");
    $conn->execute();

    foreach ($student_names as $index => $student_name) {
        $reg_no = $reg_nos[$index];
        $student_scores = $scores[$index + 1] ?? []; // scores array is 1-indexed

        // Calculate total score and percentage
        $total_score = 0;
        $component_scores = [];

        foreach ($components as $component) {
            $score = floatval($student_scores[$component->id] ?? 0);
            if ($score > $component->max_marks) {
                throw new Exception("Score $score exceeds maximum {$component->max_marks} for student $student_name");
            }
            $total_score += $score;
            $component_scores[$component->id] = $score;
        }

        $percentage = ($total_score / 100) * 100; // Assuming total is always out of 100

        // Get or determine grade
        $sql = "SELECT grade_name FROM grade WHERE :percentage BETWEEN mark_from AND mark_upto";
        $conn->query($sql);
        $conn->bind(':percentage', $percentage);
        $grade_result = $conn->fetchSingle();
        $grade = $grade_result ? $grade_result->grade_name : '';

        // Check if student result already exists
        $sql = "SELECT id FROM student_results 
                WHERE student_name = :student_name 
                AND class = :class 
                AND term = :term 
                AND section = :section 
                AND subject = :subject 
                AND current_school_session = :school_session";

        $conn->query($sql);
        $conn->bind(':student_name', $student_name);
        $conn->bind(':class', $class);
        $conn->bind(':term', $term);
        $conn->bind(':section', $section);
        $conn->bind(':subject', $subject);
        $conn->bind(':school_session', $current_school_session);

        $existing_result = $conn->fetchSingle();

        if ($existing_result) {
            // Update existing result
            $result_id = $existing_result->id;

            $sql = "UPDATE student_results 
                    SET total_score = :total_score, percentage = :percentage, 
                        grade = :grade, template_id = :template_id 
                    WHERE id = :result_id";
            $conn->query($sql);
            $conn->bind(':total_score', $total_score);
            $conn->bind(':percentage', $percentage);
            $conn->bind(':grade', $grade);
            $conn->bind(':template_id', $template_id);
            $conn->bind(':result_id', $result_id);
            $conn->execute();

            // Delete existing component scores
            $sql = "DELETE FROM student_assessment_scores WHERE result_id = :result_id";
            $conn->query($sql);
            $conn->bind(':result_id', $result_id);
            $conn->execute();

        } else {
            // Insert new result
            $sql = "INSERT INTO student_results 
                    (student_name, reg_no, class, section, subject, term, school_year, 
                     current_school_session, template_id, total_score, percentage, grade) 
                    VALUES (:student_name, :reg_no, :class, :section, :subject, :term, 
                            :school_year, :current_school_session, :template_id, :total_score, 
                            :percentage, :grade)";

            $conn->query($sql);
            $conn->bind(':student_name', $student_name);
            $conn->bind(':reg_no', $reg_no);
            $conn->bind(':class', $class);
            $conn->bind(':section', $section);
            $conn->bind(':subject', $subject);
            $conn->bind(':term', $term);
            $conn->bind(':school_year', $exam_year);
            $conn->bind(':current_school_session', $current_school_session);
            $conn->bind(':template_id', $template_id);
            $conn->bind(':total_score', $total_score);
            $conn->bind(':percentage', $percentage);
            $conn->bind(':grade', $grade);
            $conn->execute();

            $result_id = $conn->lastInserted();
        }

        // Insert component scores
        foreach ($component_scores as $component_id => $score) {
            $sql = "INSERT INTO student_assessment_scores 
                    (result_id, component_id, score) 
                    VALUES (:result_id, :component_id, :score)";
            $conn->query($sql);
            $conn->bind(':result_id', $result_id);
            $conn->bind(':component_id', $component_id);
            $conn->bind(':score', $score);
            $conn->execute();
        }
    }

    // Update positions (class and subject positions)
    updateStudentPositions($conn, $class, $term, $section, $current_school_session);
    updateSubjectPositions($conn, $class, $term, $section, $subject, $current_school_session);

    // Commit transaction
    $conn->query("COMMIT");
    $conn->execute();

    echo json_encode(['success' => true, 'message' => 'Marks saved successfully']);

} catch (Exception $e) {
    // Rollback transaction
    $conn->query("ROLLBACK");
    $conn->execute();

    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}

function updateStudentPositions($conn, $class, $term, $section, $school_session) {
    try {
        // Get all students with their total scores for ranking
        $sql = "SELECT student_name, reg_no, SUM(total_score) as grand_total 
                FROM student_results 
                WHERE class = :class AND term = :term AND section = :section 
                AND current_school_session = :school_session 
                GROUP BY student_name, reg_no 
                ORDER BY grand_total DESC";

        $conn->query($sql);
        $conn->bind(':class', $class);
        $conn->bind(':term', $term);
        $conn->bind(':section', $section);
        $conn->bind(':school_session', $school_session);
        $results = $conn->fetchMultiple();

        $position = 1;
        $prev_score = null;
        $actual_position = 1;

        foreach ($results as $student) {
            if ($prev_score !== null && $student->grand_total < $prev_score) {
                $position = $actual_position;
            }

            // Format position with ordinal suffix
            $suffix = getOrdinalSuffix($position);
            $position_text = $position . $suffix;

            // Check if position record already exists
            $sql = "SELECT id FROM student_class_positions 
                    WHERE name = :name AND class = :class AND section = :section 
                    AND exam = :exam AND exam_year = :exam_year";

            $conn->query($sql);
            $conn->bind(':name', $student->student_name);
            $conn->bind(':class', $class);
            $conn->bind(':section', $section);
            $conn->bind(':exam', $term);
            $conn->bind(':exam_year', date('Y'));
            $existing_position = $conn->fetchSingle();

            if ($existing_position) {
                // Update existing position record
                $sql = "UPDATE student_class_positions 
                        SET reg_no = :reg_no, marks_obtained = :marks, position = :position 
                        WHERE id = :id";

                $conn->query($sql);
                $conn->bind(':reg_no', $student->reg_no);
                $conn->bind(':marks', $student->grand_total);
                $conn->bind(':position', $position_text);
                $conn->bind(':id', $existing_position->id);
                $conn->execute();
            } else {
                // Insert new position record
                $sql = "INSERT INTO student_class_positions 
                        (name, reg_no, class, section, exam, exam_year, marks_obtained, position) 
                        VALUES (:name, :reg_no, :class, :section, :exam, :exam_year, :marks, :position)";

                $conn->query($sql);
                $conn->bind(':name', $student->student_name);
                $conn->bind(':reg_no', $student->reg_no);
                $conn->bind(':class', $class);
                $conn->bind(':section', $section);
                $conn->bind(':exam', $term);
                $conn->bind(':exam_year', date('Y'));
                $conn->bind(':marks', $student->grand_total);
                $conn->bind(':position', $position_text);
                $conn->execute();
            }

            $prev_score = $student->grand_total;
            $actual_position++;
        }

    } catch (Exception $e) {
        // Log error but don't throw to avoid breaking the main transaction
        error_log("Position update error: " . $e->getMessage());
    }
}

function updateSubjectPositions($conn, $class, $term, $section, $subject, $school_session) {
    try {
        // Get all students with their subject scores for ranking
        $sql = "SELECT student_name, reg_no, total_score 
                FROM student_results 
                WHERE class = :class AND term = :term AND section = :section 
                AND subject = :subject AND current_school_session = :school_session 
                ORDER BY total_score DESC";

        $conn->query($sql);
        $conn->bind(':class', $class);
        $conn->bind(':term', $term);
        $conn->bind(':section', $section);
        $conn->bind(':subject', $subject);
        $conn->bind(':school_session', $school_session);
        $results = $conn->fetchMultiple();

        $position = 1;
        $prev_score = null;
        $actual_position = 1;

        foreach ($results as $student) {
            if ($prev_score !== null && $student->total_score < $prev_score) {
                $position = $actual_position;
            }

            // Format position with ordinal suffix
            $suffix = getOrdinalSuffix($position);
            $position_text = $position . $suffix;

            // Check if subject position record already exists
            $sql = "SELECT id FROM subject_positions 
                    WHERE name = :name AND subject = :subject AND class = :class 
                    AND section = :section AND exam = :exam AND exam_year = :exam_year";

            $conn->query($sql);
            $conn->bind(':name', $student->student_name);
            $conn->bind(':subject', $subject);
            $conn->bind(':class', $class);
            $conn->bind(':section', $section);
            $conn->bind(':exam', $term);
            $conn->bind(':exam_year', date('Y'));
            $existing_position = $conn->fetchSingle();

            if ($existing_position) {
                // Update existing subject position record
                $sql = "UPDATE subject_positions 
                        SET reg_no = :reg_no, marks_obtained = :marks, position = :position 
                        WHERE id = :id";

                $conn->query($sql);
                $conn->bind(':reg_no', $student->reg_no);
                $conn->bind(':marks', $student->total_score);
                $conn->bind(':position', $position_text);
                $conn->bind(':id', $existing_position->id);
                $conn->execute();
            } else {
                // Insert new subject position record
                $sql = "INSERT INTO subject_positions 
                        (name, reg_no, subject, class, section, exam, exam_year, marks_obtained, position) 
                        VALUES (:name, :reg_no, :subject, :class, :section, :exam, :exam_year, :marks, :position)";

                $conn->query($sql);
                $conn->bind(':name', $student->student_name);
                $conn->bind(':reg_no', $student->reg_no);
                $conn->bind(':subject', $subject);
                $conn->bind(':class', $class);
                $conn->bind(':section', $section);
                $conn->bind(':exam', $term);
                $conn->bind(':exam_year', date('Y'));
                $conn->bind(':marks', $student->total_score);
                $conn->bind(':position', $position_text);
                $conn->execute();
            }

            $prev_score = $student->total_score;
            $actual_position++;
        }

    } catch (Exception $e) {
        // Log error but don't throw to avoid breaking the main transaction
        error_log("Subject position update error: " . $e->getMessage());
    }
}

function getOrdinalSuffix($number) {
    $ones = $number % 10;
    $tens = floor($number / 10) % 10;

    if ($tens == 1) {
        return "th";
    } else {
        switch ($ones) {
            case 1: return "st";
            case 2: return "nd";
            case 3: return "rd";
            default: return "th";
        }
    }
}