<?php
// get_attendance_report_data.php

include('session_validator.php');
include('includes/db_pdo.php');

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

try {
    // Get filter parameters
    $academic_year = $_POST['academic_year'] ?? '';
    $class_id = $_POST['class_id'] ?? '';
    $section_ids = $_POST['section_ids'] ?? [];
    $nepali_months = $_POST['nepali_months'] ?? [];
    $min_attendance = $_POST['min_attendance'] ?? '';
    $max_attendance = $_POST['max_attendance'] ?? '';
    $status_filter = $_POST['status_filter'] ?? '';

    // Build the query using only named parameters
    $sql = "SELECT 
                s.student_id,
                s.name as student_name,
                s.class as roll_no,
                c.class as class_name,
                sec.section_name,
                COALESCE(SUM(ma.total_working_days), 0) as total_days,
                COALESCE(SUM(ma.days_present), 0) as days_present,
                COALESCE(SUM(ma.days_absent), 0) as days_absent
            FROM students s
            INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
            INNER JOIN classes c ON scsd.class_id = c.class_id
            INNER JOIN sections sec ON scsd.section_id = sec.section_id
            LEFT JOIN monthly_attendance ma ON s.student_id = ma.student_id 
                AND ma.class_id = scsd.class_id 
                AND ma.section_id = scsd.section_id
                AND ma.academic_year = scsd.academic_year
            WHERE s.status = 'active' 
                AND scsd.status = 'active'";

    $params = [];

    // Add filters using named parameters
    if (!empty($academic_year)) {
        $sql .= " AND scsd.academic_year = :academic_year";
        $params[':academic_year'] = $academic_year;
    }

    if (!empty($class_id)) {
        $sql .= " AND scsd.class_id = :class_id";
        $params[':class_id'] = $class_id;
    }

    if (!empty($section_ids)) {
        $section_placeholders = [];
        foreach ($section_ids as $index => $id) {
            $param_name = ":section_id_" . $index;
            $section_placeholders[] = $param_name;
            $params[$param_name] = $id;
        }
        $sql .= " AND scsd.section_id IN (" . implode(',', $section_placeholders) . ")";
    }

    if (!empty($nepali_months)) {
        $month_placeholders = [];
        foreach ($nepali_months as $index => $month) {
            $param_name = ":month_" . $index;
            $month_placeholders[] = $param_name;
            $params[$param_name] = $month;
        }
        $sql .= " AND ma.nepali_month IN (" . implode(',', $month_placeholders) . ")";
    }

    $sql .= " GROUP BY s.student_id, s.name, s.class, c.class, sec.section_name";

    // Prepare and execute with named parameters
    $stmt = $conn->prepare($sql);
    
    // Bind all parameters
    foreach ($params as $key => &$value) {
        $stmt->bindParam($key, $value);
    }
    
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Calculate percentages and apply additional filters
    $filteredResults = [];
    $summary = [
        'total_students' => 0,
        'total_days' => 0,
        'total_present' => 0,
        'total_absent' => 0,
        'high_attendance' => 0,
        'low_attendance' => 0,
        'avg_attendance' => 0
    ];

    foreach ($results as $row) {
        $totalDays = $row['total_days'];
        $daysPresent = $row['days_present'];
        
        if ($totalDays > 0) {
            $percentage = round(($daysPresent / $totalDays) * 100, 2);
        } else {
            $percentage = 0;
        }

        // Apply percentage filters
        if (!empty($min_attendance) && $percentage < $min_attendance) {
            continue;
        }
        if (!empty($max_attendance) && $percentage > $max_attendance) {
            continue;
        }

        // Apply status filter
        if (!empty($status_filter)) {
            if ($status_filter == 'high' && $percentage < 75) continue;
            if ($status_filter == 'medium' && ($percentage < 60 || $percentage >= 75)) continue;
            if ($status_filter == 'low' && $percentage >= 60) continue;
        }

        $row['percentage'] = $percentage;
        $filteredResults[] = $row;

        // Update summary
        $summary['total_students']++;
        $summary['total_days'] += $totalDays;
        $summary['total_present'] += $daysPresent;
        $summary['total_absent'] += ($totalDays - $daysPresent);
        
        if ($percentage >= 75) {
            $summary['high_attendance']++;
        } elseif ($percentage < 60) {
            $summary['low_attendance']++;
        }
    }

    // Calculate average attendance
    if ($summary['total_days'] > 0) {
        $summary['avg_attendance'] = round(($summary['total_present'] / $summary['total_days']) * 100, 2);
    } else {
        $summary['avg_attendance'] = 0;
    }

    echo json_encode([
        'status' => 'success',
        'data' => $filteredResults,
        'summary' => $summary
    ]);

} catch (Exception $e) {
    echo json_encode([
        'status' => 'error',
        'message' => 'Error generating report: ' . $e->getMessage()
    ]);
}
?>