<?php

function isUserValid($conn_rjllearning_specific, $user_id)
{
    $sql = "SELECT COUNT(*) FROM staff_details WHERE id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$user_id]);
    $count = $stmt->fetchColumn();
    return $count > 0;
}

function specificStaffAttendance($institution_id, $branch_id, $user_id, $conn_rjllearning_specific, $start_date, $end_date)
{
    $result = [];
    $overall_attendance_count = [
        'Present' => 0,
        'Absent' => 0,
        'Leave' => 0,
        'Half-day' => 0
    ];

    function generateDailyDateRanges($start_date, $end_date)
    {
        $date_ranges = [];
        $current_date = new DateTime($start_date);
        $end_date = new DateTime($end_date);

        while ($current_date <= $end_date) {
            $date_ranges[] = [
                'start_date' => $current_date->format('Y-m-d'),
                'end_date' => $current_date->format('Y-m-d')
            ];
            $current_date->modify('+1 day');
        }
        return $date_ranges;
    }

    $date_ranges = generateDailyDateRanges($start_date, $end_date);

    $sql = "SELECT date, value as attendance_type, count(*) as attendance_count 
    FROM staff_attendance_daywise 
    WHERE institution_id = ? AND branch_id = ? AND user_id = ? AND date >= ? AND date <= ? 
    GROUP BY date, value";

    $stmt = $conn_rjllearning_specific->prepare($sql);

    foreach ($date_ranges as $temp) {
        $stmt->execute([$institution_id, $branch_id, $user_id, $temp['start_date'], $temp['end_date']]);
        $daily_attendance = $stmt->fetchAll();

        foreach ($daily_attendance as $attendance) {
            $attendance_type = $attendance['attendance_type'];
            $attendance_count = intval($attendance['attendance_count']);
            $date = $attendance['date'];

            $result[] = [
                'date' => $date,
                'attendance' => $attendance_type
            ];
            if ($attendance_type == 'P') {
                $overall_attendance_count['Present'] += $attendance_count;
            } else if ($attendance_type == 'A') {
                $overall_attendance_count['Absent'] += $attendance_count;
            } else if ($attendance_type == 'L') {
                $overall_attendance_count['Leave'] += $attendance_count;
            } else if ($attendance_type == 'H') {
                $overall_attendance_count['Half-day'] += $attendance_count;
            }
        }
    }
    return [
        'date_wise_info' => $result,
        'attendance_summary' => $overall_attendance_count
    ];
}

function fetchMonthly_Attendance($institution_id, $branch_id, $user_id, $conn_rjllearning_specific, $start_date, $end_date)
    {
        $result = [];
        $overall_total_attendance = 0;
        $status_total = [
            'Present' => 0,
            'Absent' => 0,
            'Leave' => 0,
            'Half-day' => 0,
        ];

        function generateMonthly_DateRanges($start_date, $end_date)
        {
            $date_ranges = [];
            $current_start_date = new DateTime($start_date);
            $end_date = new DateTime($end_date);

            while ($current_start_date <= $end_date) {
                $current_end_date = clone $current_start_date;
                $current_end_date->modify('last day of this month');


                // If the end of the month is greater than the max end date, use the max end date
                if ($current_end_date > $end_date) {
                    $current_end_date = $end_date;
                }

                $date_ranges[] = [
                    'start' => $current_start_date->format('Y-m-d'),
                    'end' => $current_end_date->format('Y-m-d')
                ];
                $current_start_date = clone $current_end_date;
                $current_start_date->modify('first day of next month');
            }
            return $date_ranges;
        }

        $date_ranges = generateMonthly_DateRanges($start_date, $end_date);

        $sql = "SELECT DATE_FORMAT(date, '%Y-%m') as month, value as attendance_type, COUNT(*) as attendance_count 
        FROM staff_attendance_daywise 
        WHERE institution_id = ? AND branch_id = ? AND user_id = ? AND date >= ? AND date <= ? 
        GROUP BY month, attendance_type";

        $stmt = $conn_rjllearning_specific->prepare($sql);

        foreach ($date_ranges as $temp) {
            $array_params = [$institution_id, $branch_id, $user_id, $temp['start'], $temp['end']];
            $stmt->execute($array_params);
            $monthly_attendance = $stmt->fetchAll();

            $temp_date_data_array = [
                'date' => $temp['start'] . ' - ' . $temp['end'],
                'statuses' => [
                    'Present' => 0,
                    'Absent' => 0,
                    'Leave' => 0,
                    'Half-day' => 0
                ],
                'total_attendance' => 0
            ];
            foreach ($monthly_attendance as $attendance) {
                $attendance_type = $attendance['attendance_type'];
                $attendance_count = intval($attendance['attendance_count']);

                $overall_total_attendance += $attendance_count;
                $temp_date_data_array['total_attendance'] += $attendance_count;

                if ($attendance_type == 'P') {
                    $temp_date_data_array['statuses']['Present'] = $attendance_count;
                    $status_total['Present'] += $attendance_count;
                } else if ($attendance_type == 'A') {
                    $temp_date_data_array['statuses']['Absent'] = $attendance_count;
                    $status_total['Absent'] += $attendance_count;
                } else if ($attendance_type == 'L') {
                    $temp_date_data_array['statuses']['Leave'] = $attendance_count;
                    $status_total['Leave'] += $attendance_count;
                } else if ($attendance_type == 'H') {
                    $temp_date_data_array['statuses']['Half-day'] = $attendance_count;
                    $status_total['Half-day'] += $attendance_count;
                }
            }

            $result[] = $temp_date_data_array;
        }
        return [
            'date_wise_info' => $result,
            'total_attendance' => $overall_total_attendance,
            'stats_info' => $status_total
        ];
    }



