<?php
ini_set('display_errors', 1);

// Include required files
require_once '../../../resources/base_path_include.php';
require_once $base_path_to_learning_resources . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

// Validation files
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning . 'functions/common_functions.php';

// DB access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

// Decode JSON post data
$postdata = json_decode(file_get_contents("php://input"), true);
checkIfNoPhpContentsReceive();

// Check if required fields are present
$required_fields = ['institution_id', 'class_id', 'section_id', 'branch_id', 'api_signature', 'function_type', 'jwt_token', 'min_date', 'max_date', 'subject_id'];
foreach ($required_fields as $field) {
    if (!isset($postdata[$field]) || !checkIfIssetAndNotEmpty($postdata[$field])) {
        errorreturn("All * fields are required.");
    }
}

// Validate API signature
validateApiSignature($postdata['api_signature']);

// Authenticate JWT token
$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);

// Extract data from JWT token
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;

// Validate user access
$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id,['superadmin','admin'],null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
}

$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);

require_once $base_path_to_learning . 'functions/get_branch_functions.php';

// Extract and validate input data
$institution_id = trim($postdata['institution_id']);
$class_id = trim($postdata['class_id']);
$branch_id = trim($postdata['branch_id']);
$section_id = trim($postdata['section_id']);
$subject_id = trim($postdata['subject_id']);
$min_date = $postdata['min_date'];
$max_date = $postdata['max_date'];

$academic_id = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
if ($academic_id == false) {
    errorreturn("Access denied. Academic session not active.");
}

// Validate class
if ($class_id != "all") {
    $sql_class = $conn_rjllearning_specific->prepare("SELECT * FROM class_info WHERE id = ? AND institution_id = ?");
    $sql_class->execute([$class_id, $institution_id]);
    if ($sql_class->rowCount() < 1) {
        errorreturn("Class info does not exist.");
    }
}

// Validate section

if ($section_id != "all") {
    $sql_section = $conn_rjllearning_specific->prepare("SELECT * FROM section_info WHERE id = ? AND institution_id = ?");
    $sql_section->execute([$section_id, $institution_id]);
    if ($sql_section->rowCount() < 1) {
        errorreturn("Section info does not exist.");
    }
}

// Validate branch
// $sql_branch = $conn_rjllearning_specific->prepare("SELECT * FROM branch_info WHERE id = ? AND institution_id = ?");

if ($branch_id != "all") {
    $sql_branch = $conn_rjllearning_specific->prepare("SELECT * FROM branch WHERE id = ? AND institution_id = ?");
    $sql_branch->execute([$branch_id, $institution_id]);
    if ($sql_branch->rowCount() < 1) {
        errorreturn("branch info does not exist.");
    }
}

$function_type = $postdata['function_type'];
if ($function_type === "subjectwise_attendance_stats") {
    
    // Get total students
    // $sql_total_students = "SELECT COUNT(*) AS total_students FROM students WHERE institution_id = ?";
    // $array_params = [$institution_id];

    // if ($branch_id != 'all') {
    //     $sql_total_students .= " AND branch_id = ?";
    //     $array_params[] = $branch_id;
    // }

    // if ($class_id != 'all') {
    //     $sql_total_students .= " AND class_id = ?";
    //     $array_params[] = $class_id;
    // }

    // if ($section_id != 'all') {
    //     $sql_total_students .= " AND section_id = ?";
    //     $array_params[] = $section_id;
    // }

    // // Add subject_id condition
    // // if ($subject_id != 'all') {
    // //     $sql_total_students .= " AND subject_id = ?";
    // //     $array_params[] = $subject_id;
    // // }

    // $stmt = $conn_rjllearning_specific->prepare($sql_total_students);
    // $stmt->execute($array_params);
    // $total_students_result = $stmt->fetch();
    // $total_students = $total_students_result['total_students'];
    
    if ($min_date == date("Y-m-d")) {
        $sql_total_students = "SELECT COUNT(*) AS total_students FROM students WHERE institution_id = ?  and (date_left IS NULL or date_left > ?)  and can_activate = 1";
        $array_params = [$institution_id, $min_date];

        if ($branch_id != 'all') {
            $sql_total_students .= " AND branch_id = ?";
            $array_params[] = $branch_id;
        }

        if ($class_id != 'all') {
            $sql_total_students .= " AND class_id = ?";
            $array_params[] = $class_id;
        }

        if ($section_id != 'all') {
            $sql_total_students .= " AND section_id = ?";
            $array_params[] = $section_id;
        }
      
         // Add subject_id condition
        // if ($subject_id != 'all') {
        //     $sql_total_students .= " AND subject_id = ?";
        //     $array_params[] = $subject_id;
        // }
     
        $stmt = $conn_rjllearning_specific->prepare($sql_total_students);
        $stmt->execute($array_params);
        $total_students_result = $stmt->fetch();
        $total_students = $total_students_result['total_students'];
    } else {
        $sql_total_students = "SELECT count(*) as total_students from cache_academic_students_data where institution_id = ?   and student_id IN (SELECT id FROM students WHERE institution_id = ? and date_admission <= ?   AND can_activate = 1 and (date_left IS NULL or date_left > ?))";
        $array_params = [$institution_id, $institution_id, $min_date, $min_date];

        if ($branch_id != 'all') {
            $sql_total_students .= " AND branch_id = ?";
            $array_params[] = $branch_id;
        }

        if ($class_id != 'all') {
            $sql_total_students .= " AND class_id = ?";
            $array_params[] = $class_id;
        }

        if ($section_id != 'all') {
            $sql_total_students .= " AND section_id = ?";
            $array_params[] = $section_id;
        }
        
        // Add subject_id condition
        // if ($subject_id != 'all') {
        //     $sql_total_students .= " AND subject_id = ?";
        //     $array_params[] = $subject_id;
        // }

        $stmt = $conn_rjllearning_specific->prepare($sql_total_students);
        $stmt->execute($array_params);
        $total_students_result = $stmt->fetch();
        $total_students = $total_students_result['total_students'];
    }

    // Get attendance summary
    $possible_values = ['H', 'P', 'A', 'L'];

    // Query to get attendance summary
    $sql_attendance_summary = "SELECT date, value, COUNT(*) AS attendance_summary 
                               FROM student_attendance_subjectwise 
                               WHERE institution_id = ?";
    $array_params = [$institution_id];

    // Append conditions for branch, class, and section if they are not 'all'
    if ($branch_id != 'all') {
        $sql_attendance_summary .= " AND branch_id = ?";
        $array_params[] = $branch_id;
    }

    if ($class_id != 'all') {
        $sql_attendance_summary .= " AND class_id = ?";
        $array_params[] = $class_id;
    }

    if ($section_id != 'all') {
        $sql_attendance_summary .= " AND section_id = ?";
        $array_params[] = $section_id;
    }

    // Append subject_id condition
    if ($subject_id != 'all') {
        $sql_attendance_summary .= " AND subject_id = ?";
        $array_params[] = $subject_id;
    }

    // Append date condition based on whether min_date and max_date are the same
    if ($min_date === $max_date) {
        $sql_attendance_summary .= " AND date = ?";
        $array_params[] = $min_date;
    } else {
        $sql_attendance_summary .= " AND date BETWEEN ? AND ?";
        $array_params[] = $min_date;
        $array_params[] = $max_date;
    }

    $sql_attendance_summary .= " GROUP BY date, value";

    // Prepare and execute the query
    $stmt = $conn_rjllearning_specific->prepare($sql_attendance_summary);
    $stmt->execute($array_params);
    $attendance_summary_results = $stmt->fetchAll();

    // Initialize arrays to store attendance summary
    $attendance_summary = [];
    $attendance_counts_separate = [];

    // Initialize attendance_summary with zero counts for all possible values
    $attendance_summary = array_fill_keys($possible_values, 0);

    // Iterate through the results to populate attendance_summary and attendance_counts_separate
    // Iterate through the results to populate attendance_summary and attendance_counts_separate
foreach ($attendance_summary_results as $row) {
    // Extract data from the row
    $date = $row['date'];
    $value = $row['value'];
    $count = $row['attendance_summary'];

    // Populate attendance_summary with counts for each value
    if (isset($attendance_summary[$value])) {
        $attendance_summary[$value] += $count;
    }

    // Populate attendance_counts_separate for each date and value
    if (!isset($attendance_counts_separate[$date])) {
        $attendance_counts_separate[$date] = array_fill_keys($possible_values, 0);
    }
    $attendance_counts_separate[$date][$value] = $count;
}

// Remove dates where no attendance was marked
$attendance_counts_separate = array_filter($attendance_counts_separate, function ($counts) {
    return array_sum($counts) > 0;
});

// Ensure that all dates within the range are represented
$current_date = strtotime($min_date);
$end_date = strtotime($max_date);

while ($current_date <= $end_date) {
    $date = date('Y-m-d', $current_date);
    if (!isset($attendance_counts_separate[$date])) {
        // Initialize with all possible values set to 0 for dates not present in the attendance data
        $attendance_counts_separate[$date] = array_fill_keys($possible_values, 0);
    }
    $current_date = strtotime('+1 day', $current_date);
}

// Remove dates where no attendance was marked
$attendance_counts_separate = array_filter($attendance_counts_separate, function ($counts) {
    return array_sum($counts) > 0;
});

// Calculate total marked days
$sql_total_marked_days = "SELECT COUNT(DISTINCT date) AS total_marked_days FROM student_attendance_subjectwise WHERE institution_id = ?";
$array_params = [$institution_id];

if ($branch_id != 'all') {
    $sql_total_marked_days .= " AND branch_id = ?";
    $array_params[] = $branch_id;
}

if ($class_id != 'all') {
    $sql_total_marked_days .= " AND class_id = ?";
    $array_params[] = $class_id;
}

if ($section_id != 'all') {
    $sql_total_marked_days .= " AND section_id = ?";
    $array_params[] = $section_id;
}

// Add subject_id condition
if ($subject_id != 'all') {
    $sql_total_marked_days .= " AND subject_id = ?";
    $array_params[] = $subject_id;
}

$sql_total_marked_days .= " AND date BETWEEN ? AND ?";
$array_params[] = $min_date;
$array_params[] = $max_date;

$stmt = $conn_rjllearning_specific->prepare($sql_total_marked_days);
$stmt->execute($array_params);
$total_marked_days_result = $stmt->fetch();
$total_marked_days = $total_marked_days_result['total_marked_days'];

// Get total marked attendance
$sql_total_marked_attendance = "SELECT COUNT(*) AS total_marked_attendance FROM student_attendance_subjectwise WHERE institution_id = ?";
$array_params = [$institution_id];

if ($branch_id != 'all') {
    $sql_total_marked_attendance .= " AND branch_id = ?";
    $array_params[] = $branch_id;
}

if ($class_id != 'all') {
    $sql_total_marked_attendance .= " AND class_id = ?";
    $array_params[] = $class_id;
}

if ($section_id != 'all') {
    $sql_total_marked_attendance .= " AND section_id = ?";
    $array_params[] = $section_id;
}

// Add subject_id condition
if ($subject_id != 'all') {
    $sql_total_marked_attendance .= " AND subject_id = ?";
    $array_params[] = $subject_id;
}

$sql_total_marked_attendance .= " AND date BETWEEN ? AND ?";
$array_params[] = $min_date;
$array_params[] = $max_date;

$stmt = $conn_rjllearning_specific->prepare($sql_total_marked_attendance);
$stmt->execute($array_params);
$total_marked_attendance_result = $stmt->fetch();
$total_marked_attendance = $total_marked_attendance_result['total_marked_attendance'];

// Initialize variables for total present, late, holiday, and absent
$total_present = isset($attendance_summary['P']) ? $attendance_summary['P'] : 0;
$total_late = isset($attendance_summary['L']) ? $attendance_summary['L'] : 0;
$total_half_day = isset($attendance_summary['H']) ? $attendance_summary['H'] : 0;
$total_absent = isset($attendance_summary['A']) ? $attendance_summary['A'] : 0;

// Calculate percentages if total marked attendance is not zero
$attendance_percentages = [];
if ($total_marked_attendance != 0) {
    $attendance_percentages['P'] = round(($total_present / $total_marked_attendance) * 100);
    $attendance_percentages['L'] = round(($total_late / $total_marked_attendance) * 100);
    $attendance_percentages['H'] = round(($total_half_day / $total_marked_attendance) * 100);
    $attendance_percentages['A'] = round(($total_absent / $total_marked_attendance) * 100);
} else {
    $attendance_percentages['P'] = 0;
    $attendance_percentages['L'] = 0;
    $attendance_percentages['H'] = 0;
    $attendance_percentages['A'] = 0;
}

// Calculate marked students
// Calculate marked students
$total_marked_students = $total_present + $total_late + $total_half_day + $total_absent;

// Calculate unmarked students
$unmarked_students = $total_students > $total_marked_students ? $total_students - $total_marked_students : 0;

// Get total attendance record
$sql_total_attendance_record = "SELECT COUNT(*) AS total_attendance FROM student_attendance_subjectwise WHERE institution_id = ?";
$array_params = [$institution_id];

if ($branch_id != 'all') {
    $sql_total_attendance_record .= " AND branch_id = ?";
    $array_params[] = $branch_id;
}

if ($class_id != 'all') {
    $sql_total_attendance_record .= " AND class_id = ?";
    $array_params[] = $class_id;
}

if ($section_id != 'all') {
    $sql_total_attendance_record .= " AND section_id = ?";
    $array_params[] = $section_id;
}

// Add subject_id condition
if ($subject_id != 'all') {
    $sql_total_attendance_record .= " AND subject_id = ?";
    $array_params[] = $subject_id;
}

$sql_total_attendance_record .= " AND date BETWEEN ? AND ?";
$array_params[] = $min_date;
$array_params[] = $max_date;

$stmt = $conn_rjllearning_specific->prepare($sql_total_attendance_record);
$stmt->execute($array_params);
$total_attendance_record_result = $stmt->fetch();
$total_attendance_record = $total_attendance_record_result['total_attendance'];

// Construct response data
$response_data = [
    'min_date' => $min_date,
    'max_date' => $max_date,
    'marked_students' => intval($total_marked_students),
    'unmarked_students' => intval($unmarked_students),
    'total_students' => intval($total_students),
    'total_attendance_records' => intval($total_attendance_record),
    'attendance_counts' => $attendance_summary,
    'attendance_counts_separate' => $attendance_counts_separate,
    'attendance_percentages' => $attendance_percentages,
];

// Wrap attendance counts arrays inside another array
$response_data['attendance_counts'] = [$response_data['attendance_counts']];
$response_data['attendance_counts_separate'] = [$response_data['attendance_counts_separate']];

// Prepare success response with the response data
$success['data'] = $response_data;
successreturn($success);
}
