<?php
function createSpeechTask($conn_rjllearning,$conn_rjllearning_specific,$workspace_id,$institution_id,$branch_id,$academic_id,$class_id,$section_id,$subject,$min_age,$max_age,$username,$title,$description,$allowed_type,$max_duration,$date_of_publish,$last_date,$last_modified_by,$last_modified_at,$s3_learning_bucket,$learning_bucketName,$role,$description_attachment = null,$description_attachment_type = null,$max_points=null) 
{
    // 1. Validate mandatory fields
    if (empty($title) || empty($description) || empty($allowed_type) || empty($max_duration) || empty($date_of_publish) || empty($last_date)) {
        errorreturn("All mandatory fields must be provided.");
    }

    $privileged_roles = ['principal', 'faculty','teacher','admin'];
    if (in_array($role, $privileged_roles)) {
        if (empty($workspace_id) || empty($institution_id) || empty($branch_id) || empty($subject)) {
            errorreturn("Workspace, Institution, Branch, and Subject are mandatory for your role.");
        }
    }

    // 1.a Validate min_age and max_age if provided
    if ($min_age !== null && $min_age !== '' && !is_numeric($min_age)) {
        errorreturn("Min Age must be a valid number.");
    }
    if ($max_age !== null && $max_age !== '' && !is_numeric($max_age)) {
        errorreturn("Max Age must be a valid number.");
    }
    if ($min_age !== null && $min_age !== '' && $max_age !== null && $max_age !== '' && $min_age > $max_age) {
        errorreturn("Min Age cannot be greater than Max Age.");
    }

    // Validate institution_id in main DB
    if (!empty($institution_id)) {
        $stmt = $conn_rjllearning->prepare("SELECT COUNT(*) as cnt FROM institutions WHERE id = ?");
        $stmt->execute([$institution_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row || $row['cnt'] == 0) errorreturn("Institution ID is invalid.");
    }

    // Validate subject
    $allowed_subjects = ['English'];
    if (!in_array($subject, $allowed_subjects)) {
        errorreturn("Invalid subject. Only English is allowed.");
    }

    // -------------------------
    // Normalize class_id and section_id
    // -------------------------
    function normalize_ids($val) {
        if ($val === 'all') return 'all';
        if (is_array($val)) {
            return count($val) === 1 ? intval($val[0]) : json_encode(array_map('intval', $val));
        } elseif (is_string($val) && str_starts_with($val, '[')) {
            $arr = json_decode($val, true);
            return count($arr) === 1 ? intval($arr[0]) : json_encode(array_map('intval', $arr));
        } else {
            return intval($val);
        }
    }

    $class_ids_db = normalize_ids($class_id);
    $section_ids_db = normalize_ids($section_id);

    // 3. Either class/section or age range must be provided
    $has_class_section = !empty($class_ids_db) && !empty($section_ids_db) && $section_ids_db !== 'all';
    $has_age_range = !empty($min_age) && !empty($max_age);
    if (!$has_class_section && !$has_age_range) errorreturn("Either Class & Section or Min/Max Age must be provided.");
    if ($has_class_section && $has_age_range) errorreturn("Cannot provide both Class/Section and Min/Max Age together.");

    // -------------------------
    // Handle attachment if any
    // -------------------------
    $attachment_path = '';
    if ($description_attachment != null && isset($description_attachment['tmp_name']) && !empty($description_attachment['tmp_name'])) {
        $currentYear = date('Y');
        $currentMonth = date('m');

        $directoryPathMain = "user_uploads/{$workspace_id}/{$institution_id}/speech_tasks/{$currentYear}/{$currentMonth}/";
        $baseUploadPath    = "/home/rejoicebilldesk/public_html/rjllearning_development/" . $directoryPathMain;

        $file_name_new = "speech_task_" . time() . "_" . rand(1000, 9999) . "." .
                         strtolower(pathinfo($description_attachment['name'], PATHINFO_EXTENSION));

        $attachment_path = $directoryPathMain . $file_name_new;
        $localFilePath   = $baseUploadPath . $file_name_new;

        if (!is_dir($baseUploadPath)) {
            mkdir($baseUploadPath, 0777, true);
        }

        if (!move_uploaded_file($description_attachment['tmp_name'], $localFilePath)) {
            errorreturn("Failed to upload attachment.");
        }

        $description_attachment['tmp_name'] = $localFilePath;

        $s3_upload_result = awsS3UserUploadInsert(
            $s3_learning_bucket,
            $learning_bucketName,
            $description_attachment,
            $attachment_path
        );
    }

    // Duplication check for single row
    $sql = "SELECT COUNT(*) as cnt FROM speech_tasks WHERE title = ? AND class_id = ? AND section_id = ?";
    $stmt = $conn_rjllearning->prepare($sql);
    $stmt->execute([$title, $class_ids_db, $section_ids_db]);
    $row = $stmt->fetch();
    if ($row && $row['cnt'] > 0) errorreturn("A Speech Task with this configuration already exists.");

    // Insert single row
    $created_at = date('Y-m-d H:i:s');
    $sql = "INSERT INTO speech_tasks (
        workspace_id, institution_id, branch_id, academic_id, class_id, section_id,
        subject, min_age, max_age, title, description, allowed_type, max_duration,
        date_of_publish, last_date, description_attachment, description_attachment_type, max_points,
        created_by, created_at, last_modified_by, last_modified_at, status
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    $stmt = $conn_rjllearning->prepare($sql);
    $stmt->execute([
        $workspace_id, $institution_id, $branch_id, $academic_id, $class_ids_db, $section_ids_db,
        $subject, $min_age, $max_age, $title, $description, $allowed_type, $max_duration,
        $date_of_publish, $last_date, $attachment_path, $description_attachment_type,$max_points,
        $username, $created_at, $last_modified_by, $last_modified_at, 1
    ]);

    return $conn_rjllearning->lastInsertId(); 
}


function deleteSpeechTask($conn_rjllearning, $workspace_id, $institution_id, $task_id, $username, $role) 
{
    // 1. Fetch task
    $stmt = $conn_rjllearning->prepare("
        SELECT id, branch_id, class_id, section_id, status 
        FROM speech_tasks 
        WHERE id = ? AND workspace_id = ? AND institution_id = ? AND status = 1
    ");
    $stmt->execute([$task_id, $workspace_id, $institution_id]);
    $task = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$task) {
        errorreturn('Task not found or already deleted.');
    }

    $branch_id  = $task['branch_id'];
    $class_id   = $task['class_id'];
    $section_id = $task['section_id'];
    $role = strtolower($role);

    // 2. Role-based conditions
    if ($branch_id === 'all') {
        if (!in_array($role, ['admin','superadmin'])) {
            errorreturn('Unauthorized: only admin/superadmin can delete this task.');
        }
    } else {
        if (!in_array($role, ['superadmin','principal','teacher'])) {
            errorreturn('Unauthorized: insufficient role to delete this task.');
        }
    }

    // 3. Soft delete (status = 0)
    $update = $conn_rjllearning->prepare("
        UPDATE speech_tasks 
        SET status = 0, last_modified_by = ?, last_modified_at = NOW() 
        WHERE id = ?
    ");
    $update->execute([$username, $task_id]);

    return 'Task deleted successfully.';
}

function fetchSpeechTasks($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $filters, $limit, $offset) 
{
     // This ensures your existing code works without changing the request
    $filters = [
        'class'   => isset($filters['class_id']) ? trim($filters['class_id']) : '',
        'section' => isset($filters['section_id']) ? trim($filters['section_id']) : '',
        'subject' => isset($filters['subject']) ? trim($filters['subject']) : '',
        'branch'  => isset($filters['branch_id']) ? trim($filters['branch_id']) : ''
    ];
    
    if (($filters['class'] !== '' || $filters['section'] !== '') && $filters['subject'] === '') {
        errorreturn('Subject is required when class or section is selected.');
    }

    // Build WHERE clause
    $sql_where = " WHERE institution_id = ?";
    $params = [$institution_id];

     if ($filters['branch'] !== '') {
        $sql_where .= " AND branch_id = ?";
        $params[] = $filters['branch'];
    }

    if ($filters['class'] !== '') {
    $sql_where .= " AND FIND_IN_SET(?, REPLACE(class_id, \"'\", \"\")) > 0";
    $params[] = $filters['class'];
    }
    
    if ($filters['section'] !== '') {
        $sql_where .= " AND FIND_IN_SET(?, REPLACE(section_id, \"'\", \"\")) > 0";
        $params[] = $filters['section'];
    }

if ($filters['subject'] !== '') {
    $sql_where .= " AND LOWER(subject) = LOWER(?)";
    $params[] = $filters['subject'];
}

    // --- STEP 4: Count total rows ---
    $sql_count = "SELECT COUNT(*) as total_rows FROM speech_tasks" . $sql_where;
    $stmt = $conn_rjllearning->prepare($sql_count); // use specific DB
    $stmt->execute($params);
    $result_total = $stmt->fetch(PDO::FETCH_ASSOC);
    $totalrows = $result_total['total_rows'] ?? 0;

    // --- STEP 5: Fetch tasks with or without limit ---
    if ($limit === 'all') {
        $sql_tasks = "SELECT * FROM speech_tasks" . $sql_where . " ORDER BY created_at DESC";
        $stmt = $conn_rjllearning->prepare($sql_tasks);
        $stmt->execute($params);
    } else {
        $offset = (int)$offset;
        $limit  = (int)$limit;
        $sql_tasks = "SELECT * FROM speech_tasks" . $sql_where . " ORDER BY created_at DESC LIMIT $offset, $limit";
        $stmt = $conn_rjllearning->prepare($sql_tasks);
        $stmt->execute($params);
    }

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

    // --- STEP 6: Return result in expected structure ---
    return [
        'total_rows' => $totalrows,
        'speech_tasks' => $task_rows,
        'maxreach' => count($task_rows) < 50 ? 1 : 0
    ];
}

/*
-> fetch specific
    basic details of the task
    overall rating of students attepmted, rating , phoneme, 
    student_submission_fetch (independent function)
    
-> mark_speech_task_submission -> student_id, speech_task_id,  submission_id -> remarks, rating (phoneme, intonatino, stress), another_atempt_request -> 
*/

// student_pending_speech_Asessment -> fetch all -> workspace_id = null, institution_id , branch_id, acaddemic == null min_age, max_age , w, i, b , a != null (class, section), (min max age) -> 
// no submissino for the student or re attempt requested

// student submission
/*
 -> speech_task_id, submission (can be audio or video file), if video file -> thumbnail -> 5 sec -> 
    if any previous entry by the same student exists for the same speech_task_id order by submitted_at desc limit 1, if no rows found store it, then simply store it
    if any previous entry by the same student exists for the same speech_task_id order by submitted_at desc limit 1, if 1 row found, then check if that row has another_attempt_requested == 1 only then store it, then simply store it, else dont proceed
*/

// view_student_specific_speech_Assessment speech_Assessment_id_teacher
    // speech_task_id, student_id [], limit, page => if submission exists then show all submissions for students
    
// mark attempt 
    

?>