<?php

function fetchMailSmsStats($institution_id, $branch_id, $conn_rjllearning, $start_date, $end_date)
{
    $result = [
        'data' => [],
    ];

    // Function to generate date ranges based on the period
    function generateDateRanges($start_date, $end_date)
    {
        $date_ranges = [];
        
        // Use 'now' if $start_date or $end_date is null
        $start_date = $start_date ?? 'now';
        $end_date = $end_date ?? 'now';
        
        // Initialize DateTime objects
        $current_start_date = new DateTime($start_date);
        $end_date = new DateTime($end_date);
        
        $date_diff = $current_start_date->diff($end_date)->days;

        if ($date_diff === 0) {
            
            $date_ranges[] = [
                'start' => $current_start_date->format('Y-m-d'),
                'end' => $current_start_date->format('Y-m-d')
            ];
        } elseif ($date_diff <= 7) {
            
            while ($current_start_date <= $end_date) {
                $date_ranges[] = [
                    'start' => $current_start_date->format('Y-m-d'),
                    'end' => $current_start_date->format('Y-m-d')
                ];
                $current_start_date->modify('+1 day');
            }
        } elseif ($date_diff <= 31) {
            
            while ($current_start_date <= $end_date) {
                $current_end_date = clone $current_start_date;
                $current_end_date->modify('+6 days');
                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->modify('+7 days');
            }
        } elseif ($date_diff <= 365) {
            
            while ($current_start_date <= $end_date) {
                $current_end_date = clone $current_start_date;
                $current_end_date->modify('last day of this month');
                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->modify('first day of next month');
            }
        } else {
            // More than 365 days, return year-wise ranges
            
            while ($current_start_date <= $end_date) {
                $current_end_date = clone $current_start_date;
                $current_end_date->modify('last day of December');  // End of the current year
                if ($current_end_date > $end_date) {
                    $current_end_date = $end_date;  // Adjust if it's beyond the specified end date
                }
                $date_ranges[] = [
                    'start' => $current_start_date->format('Y-m-d'),
                    'end' => $current_end_date->format('Y-m-d')
                ];
                $current_start_date->modify('first day of January next year');  // Move to next year
            }
        }

        return $date_ranges;
    }

    // Generate the date ranges
    $date_ranges = generateDateRanges($start_date, $end_date);

    $sql = "SELECT ";

    $date_diff = (new DateTime($start_date))->diff(new DateTime($end_date))->days;
    
    $group_by = ""; // Initialize $group_by to avoid undefined variable errors
    
    if ($date_diff <= 7) {
        // Group by week
        $sql .= "YEAR(last_mail_time) AS year, WEEK(last_mail_time) AS week, ";
        $group_by = "YEAR(last_mail_time), WEEK(last_mail_time)";
    } elseif ($date_diff <= 31) {
        // Group by month
        $sql .= "YEAR(last_mail_time) AS year, MONTH(last_mail_time) AS month, ";
        $group_by = "YEAR(last_mail_time), MONTH(last_mail_time)";
    } elseif ($date_diff >= 31) {
        // Group by year
        $sql .= "YEAR(last_mail_time) AS year, ";
        $group_by = "YEAR(last_mail_time)";
    } else {
        errorreturn("Date range is invalid or exceeds the maximum allowed range.");
    }
    
    // Continue SQL query to aggregate data
    $sql .= "SUM(mails_sent) AS total_mails_sent, 
             SUM(sms_sent) AS total_sms_sent,  
             SUM(notification_sent) AS total_notifications_sent 
             FROM mail_sms_counter 
             WHERE DATE(last_mail_time) >= ? AND DATE(last_mail_time) <= ?";
    
    if ($institution_id !== 'all') {
        $sql .= " AND institution_id = ?";
    }
    
    if ($branch_id !== 'all') {
        $sql .= " AND branch_id = ?";
    }
    
    if (!empty($group_by)) {
        $sql .= " GROUP BY $group_by";
    } else {
        errorreturn("Grouping criteria is not defined. Please check your date range logic.");
    }
    
    $stmt = $conn_rjllearning->prepare($sql);
    
    // Process the results based on date ranges
    foreach ($date_ranges as $range) {
        $array_params = [
            date('Y-m-d', strtotime($range['start'])),
            date('Y-m-d', strtotime($range['end']))
        ];
        if ($institution_id !== 'all') {
            $array_params[] = $institution_id;
        }
        if ($branch_id !== 'all') {
            $array_params[] = $branch_id;
        }
    
        $stmt->execute($array_params);
        $stats = $stmt->fetchAll(PDO::FETCH_ASSOC);


        $temp_date_data = [
            'date' => ($range['start'] === $range['end']) ? $range['start'] : $range['start'] . ' - ' . $range['end'],
            'mails' => 0,
            'sms' => 0,
            'notifications' => 0
        ];

        foreach ($stats as $stat) {
            if ($date_diff <= 7) {
                $temp_date_data['mails'] += intval($stat['total_mails_sent']);
                $temp_date_data['sms'] += intval($stat['total_sms_sent']);
                $temp_date_data['notifications'] += intval($stat['total_notifications_sent']);
            } elseif ($date_diff <= 31) {
                $temp_date_data['mails'] += intval($stat['total_mails_sent']);
                $temp_date_data['sms'] += intval($stat['total_sms_sent']);
                $temp_date_data['notifications'] += intval($stat['total_notifications_sent']);
            } elseif ($date_diff <= 365) {
                $temp_date_data['mails'] += intval($stat['total_mails_sent']);
                $temp_date_data['sms'] += intval($stat['total_sms_sent']);
                $temp_date_data['notifications'] += intval($stat['total_notifications_sent']);
            }
        }

        $result['data'][] = $temp_date_data;
    }

    // Add overall totals
    $overall_totals = [
        'total_mails_sent' => 0,
        'total_sms_sent' => 0,
        'total_notification_sent' => 0
    ];

    foreach ($result['data'] as $data) {
        $overall_totals['total_mails_sent'] += $data['mails'];
        $overall_totals['total_sms_sent'] += $data['sms'];
        $overall_totals['total_notification_sent'] += $data['notifications'];
    }

    $result['total'] = $overall_totals;

    return $result;
}

?>
