<?php

// ✅ Fetch specific speech task details + ratings summary
function fetchSpecificSpeechTask($conn, $speech_task_id) {
    // Task details
    $sql = "SELECT id, title, description, subject, allowed_type, max_duration, 
                   date_of_publish, last_date 
            FROM speech_tasks 
            WHERE id = :task_id";
    $stmt = $conn->prepare($sql);
    $stmt->execute([':task_id' => $speech_task_id]);
    $task = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$task) return null;

    // Ratings summary
    $sql = "SELECT 
                AVG(overall_rating) as avg_rating,
                AVG(phoneme_rating) as avg_phoneme,
                AVG(intonation_rating) as avg_intonation,
                AVG(stress_rating) as avg_stress,
                COUNT(*) as total_submissions
            FROM speech_submissions
            WHERE speech_task_id = :task_id";
    $stmt = $conn->prepare($sql);
    $stmt->execute([':task_id' => $speech_task_id]);
    $ratings = $stmt->fetch(PDO::FETCH_ASSOC);

    $task['ratings'] = $ratings;
    return $task;
}

// ✅ Fetch submissions (optionally for a single student)
function fetchStudentSubmissions($conn, $conn_rjllearning_specific, $institution_id, $speech_task_id, $student_id = null, $limit = 20, $offset = 0) {
    // Base SQL
    $sql = "SELECT 
                id, 
                student_id, 
                branch_id,
                submission, 
                thumbnail, 
                remarks, 
                rating, 
                phoneme, 
                stress, 
                intonation, 
                submitted_at
            FROM speech_tasks_submission 
            WHERE task_id = ?";

    $params = [$speech_task_id];

    if ($student_id) {
        $sql .= " AND student_id = ?";
        $params[] = $student_id;
    }

    // Safe integer handling for pagination
    $offset = (int)$offset;
    $limit  = (int)$limit;
    $sql .= " ORDER BY submitted_at DESC LIMIT $offset, $limit";

    // Execute with positional params
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Attach branch info
    foreach ($results as &$row) {
        $branch_id = $row['branch_id'];
        $row['branch_info'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id);
    }

    return $results;
}


// ✅ Mark teacher evaluation
function markSpeechTaskSubmission(
    $conn,
    $student_id,
    $speech_task_id,
    $submission_id,
    $remarks,
    $rating,
    $another_attempt,
    $teacher_username
) {
    // Defensive checks
    if (!is_array($rating) || 
        !isset($rating['phoneme'], $rating['intonation'], $rating['stress'], $rating['overall'])) {
        return false; // Invalid rating format
    }

    $sql = "UPDATE speech_submissions 
            SET remarks = ?, 
                phoneme_rating = ?, 
                intonation_rating = ?, 
                stress_rating = ?, 
                overall_rating = ?, 
                another_attempt_requested = ?, 
                evaluated_by = ?, 
                evaluated_at = NOW()
            WHERE id = ? AND student_id = ? AND speech_task_id = ?";

    $stmt = $conn->prepare($sql);

    return $stmt->execute([
        $remarks,
        $rating['phoneme'],
        $rating['intonation'],
        $rating['stress'],
        $rating['overall'],
        (int)$another_attempt, // store 0 or 1
        $teacher_username,
        $submission_id,
        $student_id,
        $speech_task_id
    ]);
}

// ✅ Pending tasks for a student
function fetchPendingSpeechTasks(
    $conn,
    $conn_rjllearning_specific,
    $institution_id,
    $branch_id,
    $student_id,
    $class_id = null,
    $section_id = null,
    $min_age = null,
    $max_age = null
) {
    // Base query
    $sql = "SELECT t.id, t.title, t.subject, t.date_of_publish, t.last_date, t.branch_id
            FROM speech_tasks t
            WHERE t.institution_id = ? 
              AND t.branch_id = ?";
    
    $params = [$institution_id, $branch_id];

    // Apply filters
    if ($class_id && $section_id) {
        $sql .= " AND t.class_id = ? AND t.section_id = ?";
        $params[] = $class_id;
        $params[] = $section_id;
    } elseif ($min_age && $max_age) {
        $sql .= " AND t.min_age >= ? AND t.max_age <= ?";
        $params[] = $min_age;
        $params[] = $max_age;
    }

    // Exclude already submitted tasks (unless another attempt requested)
    $sql .= " AND NOT EXISTS (
                SELECT 1 FROM speech_tasks_submission s
                WHERE s.task_id = t.id
                  AND s.student_id = ?
                  AND s.another_attempt_requested = 0
              )";
    $params[] = $student_id;

    // Prepare and execute
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Attach branch info to each result
    foreach ($results as &$row) {
        $branch_id = $row['branch_id'];
        $row['branch_info'] = getBranchInfo($conn_rjllearning_specific, $institution_id, $branch_id);
    }

    return $results;
}

// ✅ Student submission (audio/video)
function saveStudentSubmission($conn, $speech_task_id, $student_id, $file, $username) {
    // Check if submission exists
    $sql = "SELECT id, another_attempt_requested 
            FROM speech_submissions 
            WHERE student_id = :student_id AND speech_task_id = :task_id
            ORDER BY submitted_at DESC LIMIT 1";
    $stmt = $conn->prepare($sql);
    $stmt->execute([':student_id' => $student_id, ':task_id' => $speech_task_id]);
    $last = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($last && $last['another_attempt_requested'] == 0) {
        return false; // cannot submit again
    }

    // Upload file (dummy path, replace with S3/local upload)
    $filePath = '/uploads/speech/' . time() . '_' . basename($file['name']);
    move_uploaded_file($file['tmp_name'], __DIR__ . $filePath);

    $sql = "INSERT INTO speech_submissions 
            (speech_task_id, student_id, file_url, file_type, submitted_at, created_by)
            VALUES (:task_id, :student_id, :file_url, :file_type, NOW(), :created_by)";
    $stmt = $conn->prepare($sql);
    return $stmt->execute([
        ':task_id' => $speech_task_id,
        ':student_id' => $student_id,
        ':file_url' => $filePath,
        ':file_type' => $file['type'],
        ':created_by' => $username
    ]);
}

// ✅ View specific student assessments
function viewStudentSpecificSpeechAssessment($conn, $speech_task_id, $student_ids, $limit, $offset) {
    $inQuery = implode(',', array_fill(0, count($student_ids), '?'));

    $sql = "SELECT s.id, s.student_id, s.file_url, s.overall_rating, s.remarks, s.submitted_at
            FROM speech_submissions s
            WHERE s.speech_task_id = ? 
              AND s.student_id IN ($inQuery)
            ORDER BY s.submitted_at DESC
            LIMIT ? OFFSET ?";
    $stmt = $conn->prepare($sql);

    $params = array_merge([$speech_task_id], $student_ids, [$limit, $offset]);
    $stmt->execute($params);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// ✅ Quick mark attempt
function markAttempt($conn, $submission_id, $remarks, $rating, $teacher_username) {
    $sql = "UPDATE speech_submissions 
            SET remarks = :remarks,
                phoneme_rating = :phoneme,
                intonation_rating = :intonation,
                stress_rating = :stress,
                overall_rating = :overall,
                evaluated_by = :teacher,
                evaluated_at = NOW()
            WHERE id = :submission_id";
    $stmt = $conn->prepare($sql);
    return $stmt->execute([
        ':remarks' => $remarks,
        ':phoneme' => $rating['phoneme'],
        ':intonation' => $rating['intonation'],
        ':stress' => $rating['stress'],
        ':overall' => $rating['overall'],
        ':teacher' => $teacher_username,
        ':submission_id' => $submission_id
    ]);
}

?>
