<?php
// debug_get_attendance_aggregate.php

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

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

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    $academic_year = $_POST['academic_year'] ?? '';
    $view_type = $_POST['view_type'] ?? 'class';
    $selected_months = $_POST['months'] ?? [];

    $debug_info = [
        'academic_year' => $academic_year,
        'view_type' => $view_type,
        'selected_months' => $selected_months,
        'steps' => []
    ];

    if (empty($academic_year)) {
        throw new Exception('Academic year is required');
    }

    // If no months selected, use all months
    if (empty($selected_months)) {
        $selected_months = [
            'Baisakh', 'Jestha', 'Ashadh', 'Shrawan', 
            'Bhadra', 'Ashwin', 'Kartik', 'Mangsir', 
            'Poush', 'Magh', 'Falgun', 'Chaitra'
        ];
    }
    
    $debug_info['processed_months'] = $selected_months;

    // Test database connection
    $testStmt = $conn->query("SELECT 1");
    $debug_info['db_connection'] = 'OK';

    // Test if monthly_attendance table exists
    $tableCheck = $conn->query("SHOW TABLES LIKE 'monthly_attendance'");
    $debug_info['monthly_attendance_exists'] = ($tableCheck->rowCount() > 0) ? 'Yes' : 'No';

    // Count records in monthly_attendance
    $countStmt = $conn->query("SELECT COUNT(*) as total FROM monthly_attendance");
    $countRow = $countStmt->fetch(PDO::FETCH_ASSOC);
    $debug_info['monthly_attendance_records'] = $countRow['total'];

    // Get overall statistics with debugging
    $overall = getOverallStatsDebug($conn, $academic_year, $selected_months, $debug_info);
    $debug_info['overall'] = $overall;

    $response = [
        'status' => 'success',
        'debug_info' => $debug_info,
        'overall' => $overall,
        'class_data' => [],
        'monthly_data' => [],
        'student_data' => [],
        'trend_data' => []
    ];

    // Get data based on view type
    switch ($view_type) {
        case 'class':
            $response['class_data'] = getClassWiseStatsDebug($conn, $academic_year, $selected_months, $debug_info);
            break;
        case 'month':
            $response['monthly_data'] = getMonthlyStatsDebug($conn, $academic_year, $selected_months, $debug_info);
            break;
        case 'student':
            $response['student_data'] = getStudentWiseStatsDebug($conn, $academic_year, $selected_months, $debug_info);
            break;
        case 'trend':
            $response['trend_data'] = getTrendStatsDebug($conn, $academic_year, $selected_months, $debug_info);
            break;
    }

    echo json_encode($response, JSON_PRETTY_PRINT);

} catch (Exception $e) {
    echo json_encode([
        'status' => 'error',
        'message' => $e->getMessage(),
        'trace' => $e->getTraceAsString(),
        'debug_info' => $debug_info ?? []
    ]);
}

/**
 * Get overall statistics with debugging
 */
function getOverallStatsDebug($conn, $academic_year, $months, &$debug_info) {
    try {
        $monthPlaceholders = implode(',', array_fill(0, count($months), '?'));
        
        $sql = "SELECT 
                    COUNT(DISTINCT s.student_id) as total_students,
                    COALESCE(SUM(ma.total_working_days), 0) as total_days,
                    COALESCE(SUM(ma.days_present), 0) as total_present,
                    COALESCE(SUM(ma.days_absent), 0) as total_absent
                FROM students s
                INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_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
                    AND ma.nepali_month IN ($monthPlaceholders)
                WHERE scsd.academic_year = ?
                    AND s.status = 'active'
                    AND scsd.status = 'active'";
        
        $params = array_merge($months, [$academic_year]);
        
        $debug_info['overall_sql'] = $sql;
        $debug_info['overall_params'] = $params;
        
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $avgAttendance = ($row['total_days'] > 0) ? 
            round(($row['total_present'] / $row['total_days']) * 100, 2) : 0;
        
        return [
            'total_students' => $row['total_students'],
            'total_days' => $row['total_days'],
            'total_present' => $row['total_present'],
            'total_absent' => $row['total_absent'],
            'avg_attendance' => $avgAttendance
        ];
    } catch (Exception $e) {
        $debug_info['overall_error'] = $e->getMessage();
        return [
            'total_students' => 0,
            'total_days' => 0,
            'total_present' => 0,
            'total_absent' => 0,
            'avg_attendance' => 0,
            'error' => $e->getMessage()
        ];
    }
}

/**
 * Get class-wise statistics with debugging
 */
function getClassWiseStatsDebug($conn, $academic_year, $months, &$debug_info) {
    try {
        $monthPlaceholders = implode(',', array_fill(0, count($months), '?'));
        
        $sql = "SELECT 
                    c.class_id,
                    c.class as class_name,
                    COUNT(DISTINCT s.student_id) as student_count,
                    COALESCE(SUM(ma.total_working_days), 0) as total_days,
                    COALESCE(SUM(ma.days_present), 0) as present_days,
                    COALESCE(SUM(ma.days_absent), 0) as absent_days
                FROM classes c
                LEFT JOIN student_class_section_data scsd ON c.class_id = scsd.class_id AND scsd.academic_year = ?
                LEFT JOIN students s ON scsd.student_id = s.student_id AND s.status = 'active'
                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
                    AND ma.nepali_month IN ($monthPlaceholders)
                WHERE c.status = 1
                GROUP BY c.class_id, c.class
                ORDER BY c.class_numeric";
        
        $params = array_merge([$academic_year], $months);
        
        $debug_info['class_sql'] = $sql;
        $debug_info['class_params'] = $params;
        
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($results as &$row) {
            $row['percentage'] = ($row['total_days'] > 0) ? 
                round(($row['present_days'] / $row['total_days']) * 100, 2) : 0;
        }
        
        return $results;
    } catch (Exception $e) {
        $debug_info['class_error'] = $e->getMessage();
        return [];
    }
}

/**
 * Get monthly statistics with debugging
 */
function getMonthlyStatsDebug($conn, $academic_year, $months, &$debug_info) {
    try {
        $monthPlaceholders = implode(',', array_fill(0, count($months), '?'));
        
        $sql = "SELECT 
                    ma.nepali_month as month_name,
                    COUNT(DISTINCT ma.student_id) as student_count,
                    SUM(ma.total_working_days) as total_days,
                    SUM(ma.days_present) as present_days,
                    SUM(ma.days_absent) as absent_days
                FROM monthly_attendance ma
                INNER JOIN students s ON ma.student_id = s.student_id AND s.status = 'active'
                WHERE ma.academic_year = ?
                    AND ma.nepali_month IN ($monthPlaceholders)
                GROUP BY ma.nepali_month
                ORDER BY FIELD(ma.nepali_month, 
                    'Baisakh', 'Jestha', 'Ashadh', 'Shrawan', 
                    'Bhadra', 'Ashwin', 'Kartik', 'Mangsir', 
                    'Poush', 'Magh', 'Falgun', 'Chaitra')";
        
        $params = array_merge([$academic_year], $months);
        
        $debug_info['monthly_sql'] = $sql;
        $debug_info['monthly_params'] = $params;
        
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Calculate percentages and trends
        $prevPercentage = null;
        foreach ($results as &$row) {
            $row['percentage'] = ($row['total_days'] > 0) ? 
                round(($row['present_days'] / $row['total_days']) * 100, 2) : 0;
            
            if ($prevPercentage !== null) {
                $row['trend'] = round($row['percentage'] - $prevPercentage, 2);
            } else {
                $row['trend'] = 0;
            }
            $prevPercentage = $row['percentage'];
        }
        
        return $results;
    } catch (Exception $e) {
        $debug_info['monthly_error'] = $e->getMessage();
        return [];
    }
}

/**
 * Get student-wise statistics with debugging
 */
function getStudentWiseStatsDebug($conn, $academic_year, $months, &$debug_info) {
    try {
        $monthPlaceholders = implode(',', array_fill(0, count($months), '?'));
        
        $sql = "SELECT 
                    s.student_id,
                    s.name,
                    s.class as roll_no,
                    c.class as class_name,
                    sec.section_name
                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 scsd.academic_year = ?
                    AND s.status = 'active'
                    AND scsd.status = 'active'
                ORDER BY c.class_numeric, sec.section_name, CAST(s.class AS UNSIGNED)
                LIMIT 50"; // Limit for debugging
        
        $debug_info['student_sql'] = $sql;
        $debug_info['student_params'] = [$academic_year];
        
        $stmt = $conn->prepare($sql);
        $stmt->execute([$academic_year]);
        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($students as &$student) {
            $student['monthly'] = [];
            
            // Get monthly attendance for this student
            $monthSql = "SELECT 
                            nepali_month,
                            total_working_days as total,
                            days_present as present,
                            days_absent as absent
                        FROM monthly_attendance
                        WHERE student_id = ?
                            AND academic_year = ?
                            AND nepali_month IN ($monthPlaceholders)";
            
            $monthParams = array_merge([$student['student_id'], $academic_year], $months);
            $monthStmt = $conn->prepare($monthSql);
            $monthStmt->execute($monthParams);
            $monthlyData = $monthStmt->fetchAll(PDO::FETCH_ASSOC);
            
            foreach ($monthlyData as $month) {
                $month['percentage'] = ($month['total'] > 0) ? 
                    round(($month['present'] / $month['total']) * 100, 2) : 0;
                $student['monthly'][$month['nepali_month']] = $month;
            }
        }
        
        return $students;
    } catch (Exception $e) {
        $debug_info['student_error'] = $e->getMessage();
        return [];
    }
}

function getTrendStatsDebug($conn, $academic_year, $months, &$debug_info) {
    return getMonthlyStatsDebug($conn, $academic_year, $months, $debug_info);
}
?>