<?php
// get_attendance_report_data_advanced.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 = isset($_POST['min_attendance']) && $_POST['min_attendance'] !== '' ? floatval($_POST['min_attendance']) : null;
    $max_attendance = isset($_POST['max_attendance']) && $_POST['max_attendance'] !== '' ? floatval($_POST['max_attendance']) : null;
    $view_type = $_POST['view_type'] ?? 'monthly';
    $data_display = $_POST['data_display'] ?? 'percentage';

    // Get all students based on filters
    $sql = "SELECT DISTINCT 
                s.student_id,
                s.name as student_name,
                s.class as roll_no,
                c.class as class_name,
                c.class_id,
                sec.section_name,
                sec.section_id
            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
            WHERE s.status = 'active' 
                AND scsd.status = 'active'";

    $params = [];

    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) . ")";
    }

    $sql .= " ORDER BY c.class_numeric, sec.section_name, s.name";

    $stmt = $conn->prepare($sql);
    foreach ($params as $key => &$value) {
        $stmt->bindParam($key, $value);
    }
    $stmt->execute();
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($students)) {
        echo json_encode([
            'status' => 'success',
            'data' => [],
            'summary' => [
                'total_students' => 0,
                'total_days' => 0,
                'total_present' => 0,
                'total_absent' => 0,
                'high_attendance' => 0,
                'low_attendance' => 0,
                'avg_attendance' => 0
            ],
            'months' => $nepali_months
        ]);
        exit;
    }

    // Get attendance data for selected months
    $attendanceData = [];
    $monthlyStats = [];
    
    // Initialize monthly stats array
    foreach ($nepali_months as $month) {
        $monthlyStats[$month] = [
            'total_present' => 0,
            'total_students' => 0,
            'total_days' => 0
        ];
    }

    foreach ($students as $student) {
        // Get attendance for each selected month
        $monthlyAttendance = [];
        $totalDays = 0;
        $totalPresent = 0;
        
        foreach ($nepali_months as $month) {
            $attSql = "SELECT total_working_days, days_present 
                      FROM monthly_attendance 
                      WHERE student_id = :student_id 
                      AND class_id = :class_id 
                      AND section_id = :section_id 
                      AND nepali_month = :month 
                      AND academic_year = :academic_year";
            
            $attStmt = $conn->prepare($attSql);
            $attStmt->bindParam(':student_id', $student['student_id']);
            $attStmt->bindParam(':class_id', $student['class_id']);
            $attStmt->bindParam(':section_id', $student['section_id']);
            $attStmt->bindParam(':month', $month);
            $attStmt->bindParam(':academic_year', $academic_year);
            $attStmt->execute();
            
            $attendance = $attStmt->fetch(PDO::FETCH_ASSOC);
            
            if ($attendance) {
                $totalDays += $attendance['total_working_days'];
                $totalPresent += $attendance['days_present'];
                
                $monthlyAttendance[$month] = [
                    'present' => $attendance['days_present'],
                    'total' => $attendance['total_working_days'],
                    'percentage' => $attendance['total_working_days'] > 0 ? 
                        round(($attendance['days_present'] / $attendance['total_working_days']) * 100, 2) : 0
                ];
                
                // Update monthly stats
                $monthlyStats[$month]['total_present'] += $attendance['days_present'];
                $monthlyStats[$month]['total_students']++;
                $monthlyStats[$month]['total_days'] += $attendance['total_working_days'];
            } else {
                $monthlyAttendance[$month] = [
                    'present' => 0,
                    'total' => 0,
                    'percentage' => 0
                ];
            }
        }
        
        $avgPercentage = $totalDays > 0 ? round(($totalPresent / $totalDays) * 100, 2) : 0;
        
        // Apply percentage filters
        if ($min_attendance !== null && $avgPercentage < $min_attendance) continue;
        if ($max_attendance !== null && $avgPercentage > $max_attendance) continue;
        
        $studentData = [
            'student_id' => $student['student_id'],
            'student_name' => $student['student_name'],
            'roll_no' => $student['roll_no'],
            'class_name' => $student['class_name'],
            'section_name' => $student['section_name'],
            'monthly_data' => $monthlyAttendance,
            'total_days' => $totalDays,
            'days_present' => $totalPresent,
            'days_absent' => $totalDays - $totalPresent,
            'avg_percentage' => $avgPercentage
        ];
        
        $attendanceData[] = $studentData;
    }

    // Calculate summary statistics
    $summary = [
        'total_students' => count($attendanceData),
        'total_days' => 0,
        'total_present' => 0,
        'total_absent' => 0,
        'high_attendance' => 0,
        'low_attendance' => 0,
        'avg_attendance' => 0,
        'monthly_stats' => $monthlyStats
    ];

    foreach ($attendanceData as $data) {
        $summary['total_days'] += $data['total_days'];
        $summary['total_present'] += $data['days_present'];
        $summary['total_absent'] += $data['days_absent'];
        
        if ($data['avg_percentage'] >= 75) {
            $summary['high_attendance']++;
        } elseif ($data['avg_percentage'] < 60) {
            $summary['low_attendance']++;
        }
    }

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

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

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