-- Executive summary report for leadership meetings
WITH current_week AS (
    SELECT
        COUNT(DISTINCT check_in_id) as total_checkins,
        COUNT(DISTINCT CASE WHEN kind = 'Regular' THEN check_in_id END) as regular_attendees,
        COUNT(DISTINCT CASE WHEN kind = 'Guest' THEN check_in_id END) as guests,
        COUNT(DISTINCT CASE WHEN kind = 'Volunteer' THEN check_in_id END) as volunteers,
        COUNT(DISTINCT CASE WHEN one_time_guest = true THEN check_in_id END) as first_time_guests
    FROM planning_center.checkins_checkins
    WHERE created_at >= DATE_TRUNC('week', CURRENT_DATE)
        AND created_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'
),
previous_week AS (
    SELECT
        COUNT(DISTINCT check_in_id) as total_checkins,
        COUNT(DISTINCT CASE WHEN kind = 'Regular' THEN check_in_id END) as regular_attendees,
        COUNT(DISTINCT CASE WHEN kind = 'Guest' THEN check_in_id END) as guests
    FROM planning_center.checkins_checkins
    WHERE created_at >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week'
        AND created_at < DATE_TRUNC('week', CURRENT_DATE)
),
four_week_avg AS (
    SELECT
        AVG(weekly_count) as avg_attendance
    FROM (
        SELECT
            DATE_TRUNC('week', created_at) as week,
            COUNT(DISTINCT check_in_id) as weekly_count
        FROM planning_center.checkins_checkins
        WHERE created_at >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '4 weeks'
            AND created_at < DATE_TRUNC('week', CURRENT_DATE)
        GROUP BY DATE_TRUNC('week', created_at)
    ) w
)
SELECT
    '=== WEEKLY EXECUTIVE SUMMARY ===' as report_header,
    TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'Month DD, YYYY') as week_beginning,
    '' as blank1,
    '--- ATTENDANCE METRICS ---' as section1,
    cw.total_checkins as total_attendance_this_week,
    pw.total_checkins as total_attendance_last_week,
    cw.total_checkins - pw.total_checkins as week_over_week_change,
    ROUND(((cw.total_checkins - pw.total_checkins)::NUMERIC / NULLIF(pw.total_checkins, 0)) * 100, 1) as percent_change,
    ROUND(fwa.avg_attendance, 0) as four_week_average,
    '' as blank2,
    '--- ATTENDANCE BREAKDOWN ---' as section2,
    cw.regular_attendees as regular_attendees,
    cw.guests as total_guests,
    cw.first_time_guests as first_time_guests,
    cw.volunteers as volunteers_serving,
    ROUND((cw.guests::NUMERIC / NULLIF(cw.total_checkins, 0)) * 100, 1) as guest_percentage,
    '' as blank3,
    '--- COMPARISON TO LAST WEEK ---' as section3,
    cw.regular_attendees - pw.regular_attendees as regular_change,
    cw.guests - pw.guests as guest_change
FROM current_week cw, previous_week pw, four_week_avg fwa;

-- Monthly attendance trend report with year-over-year comparison
WITH monthly_data AS (
    SELECT
        DATE_TRUNC('month', created_at) as month,
        COUNT(DISTINCT check_in_id) as total_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Regular' THEN check_in_id END) as regular_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Guest' THEN check_in_id END) as guest_attendance,
        COUNT(DISTINCT CASE WHEN kind = 'Volunteer' THEN check_in_id END) as volunteer_attendance,
        COUNT(DISTINCT DATE(created_at)) as service_days
    FROM planning_center.checkins_checkins
    WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY DATE_TRUNC('month', created_at)
),
monthly_comparison AS (
    SELECT
        TO_CHAR(month, 'YYYY-MM') as month_year,
        TO_CHAR(month, 'Month') as month_name,
        total_attendance,
        regular_attendance,
        guest_attendance,
        volunteer_attendance,
        service_days,
        ROUND(total_attendance::NUMERIC / NULLIF(service_days, 0), 0) as avg_per_service,
        LAG(total_attendance, 12) OVER (ORDER BY month) as same_month_last_year,
        LAG(total_attendance, 1) OVER (ORDER BY month) as previous_month
    FROM monthly_data
)
SELECT
    month_name,
    month_year,
    total_attendance,
    regular_attendance,
    guest_attendance,
    volunteer_attendance,
    avg_per_service,
    CASE 
        WHEN same_month_last_year IS NOT NULL THEN
            total_attendance - same_month_last_year
        ELSE NULL
    END as yoy_change,
    CASE 
        WHEN same_month_last_year IS NOT NULL AND same_month_last_year > 0 THEN
            ROUND(((total_attendance - same_month_last_year)::NUMERIC / same_month_last_year) * 100, 1)
        ELSE NULL
    END as yoy_percent_change,
    CASE 
        WHEN previous_month IS NOT NULL THEN
            total_attendance - previous_month
        ELSE NULL
    END as month_over_month_change
FROM monthly_comparison
WHERE month >= DATE_TRUNC('year', CURRENT_DATE)
ORDER BY month_year DESC;

-- Comprehensive children's ministry report with safety metrics
WITH children_locations AS (
    SELECT 
        location_id,
        name,
        age_min_in_months,
        age_max_in_months,
        max_occupancy,
        attendees_per_volunteer,
        min_volunteers
    FROM planning_center.checkins_locations
    WHERE child_or_adult = 'child'
        AND kind = 'Folder'
),
current_week_stats AS (
    SELECT
        cl.name as classroom,
        cl.age_min_in_months,
        cl.age_max_in_months,
        cl.max_occupancy,
        cl.attendees_per_volunteer as required_ratio,
        cl.min_volunteers,
        COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as total_children,
        COUNT(DISTINCT CASE WHEN c.kind = 'Guest' THEN c.check_in_id END) as guest_children,
        COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as volunteers,
        COUNT(DISTINCT c.security_code) as unique_security_codes,
        COUNT(DISTINCT DATE(c.created_at)) as days_with_checkins
    FROM children_locations cl
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON cl.location_id = cr.relationship_id
        AND cr.relationship_type = 'Location'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
        AND c.created_at >= DATE_TRUNC('week', CURRENT_DATE)
    GROUP BY cl.name, cl.age_min_in_months, cl.age_max_in_months, 
             cl.max_occupancy, cl.attendees_per_volunteer, cl.min_volunteers
)
SELECT
    classroom,
    CASE 
        WHEN age_min_in_months IS NOT NULL AND age_max_in_months IS NOT NULL THEN
            (age_min_in_months / 12)::TEXT || '-' || (age_max_in_months / 12)::TEXT || ' years'
        ELSE 'All ages'
    END as age_range,
    total_children,
    guest_children,
    volunteers,
    CASE 
        WHEN volunteers > 0 THEN ROUND(total_children::NUMERIC / volunteers, 1)
        ELSE NULL
    END as actual_ratio,
    required_ratio,
    CASE 
        WHEN max_occupancy IS NOT NULL THEN max_occupancy::TEXT
        ELSE 'No limit'
    END as room_capacity,
    CASE 
        WHEN max_occupancy IS NOT NULL AND total_children > 0 THEN
            ROUND((total_children::NUMERIC / max_occupancy) * 100, 0)::TEXT || '%'
        ELSE '-'
    END as capacity_used,
    CASE 
        WHEN volunteers < COALESCE(min_volunteers, 1) THEN '⚠️ Under minimum volunteers'
        WHEN required_ratio IS NOT NULL AND volunteers > 0 
             AND (total_children::NUMERIC / volunteers) > required_ratio THEN '⚠️ Ratio exceeded'
        WHEN max_occupancy IS NOT NULL AND total_children >= max_occupancy THEN '⚠️ At capacity'
        WHEN total_children = 0 THEN 'No attendance'
        ELSE '✓ OK'
    END as status
FROM current_week_stats
ORDER BY 
    CASE 
        WHEN volunteers < COALESCE(min_volunteers, 1) AND total_children > 0 THEN 1
        WHEN required_ratio IS NOT NULL AND volunteers > 0 
             AND (total_children::NUMERIC / volunteers) > required_ratio THEN 2
        ELSE 3
    END,
    age_min_in_months NULLS LAST,
    classroom;

-- Analyze age distribution for curriculum and volunteer planning
WITH age_groups AS (
    SELECT
        l.name as location_name,
        l.age_min_in_months,
        l.age_max_in_months,
        CASE 
            WHEN l.age_max_in_months <= 24 THEN 'Nursery (0-2)'
            WHEN l.age_max_in_months <= 60 THEN 'Preschool (2-5)'
            WHEN l.age_max_in_months <= 144 THEN 'Elementary (5-12)'
            WHEN l.age_max_in_months <= 216 THEN 'Youth (12-18)'
            ELSE 'Adult'
        END as age_category,
        COUNT(DISTINCT c.check_in_id) as total_checkins,
        COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as regular_checkins,
        COUNT(DISTINCT CASE WHEN c.kind = 'Guest' THEN c.check_in_id END) as guest_checkins
    FROM planning_center.checkins_locations l
    LEFT JOIN planning_center.checkins_checkin_relationships cr
        ON l.location_id = cr.relationship_id
        AND cr.relationship_type = 'Location'
    LEFT JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
        AND c.created_at >= DATE_TRUNC('month', CURRENT_DATE)
    WHERE l.child_or_adult = 'child'
        AND l.kind = 'Folder'
    GROUP BY l.name, l.age_min_in_months, l.age_max_in_months
),
age_summary AS (
    SELECT
        age_category,
        SUM(total_checkins) as total,
        SUM(regular_checkins) as regulars,
        SUM(guest_checkins) as guests,
        COUNT(DISTINCT location_name) as num_locations
    FROM age_groups
    GROUP BY age_category
)
SELECT
    age_category,
    total as total_attendance,
    regulars as regular_children,
    guests as guest_children,
    num_locations as classrooms,
    ROUND(total::NUMERIC / NULLIF(num_locations, 0), 0) as avg_per_classroom,
    ROUND((total::NUMERIC / NULLIF((SELECT SUM(total) FROM age_summary), 0)) * 100, 1) as percent_of_total
FROM age_summary
WHERE total > 0
ORDER BY 
    CASE age_category
        WHEN 'Nursery (0-2)' THEN 1
        WHEN 'Preschool (2-5)' THEN 2
        WHEN 'Elementary (5-12)' THEN 3
        WHEN 'Youth (12-18)' THEN 4
        ELSE 5
    END;

-- Comprehensive volunteer service tracking report
WITH volunteer_service AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        COUNT(DISTINCT DATE(c.created_at)) as days_served,
        COUNT(DISTINCT DATE_TRUNC('week', c.created_at)) as weeks_served,
        MIN(c.created_at) as first_service,
        MAX(c.created_at) as last_service,
        COUNT(DISTINCT l.location_id) as locations_served,
        STRING_AGG(DISTINCT l.name, ', ' ORDER BY l.name) as service_areas
    FROM planning_center.checkins_people p
    JOIN planning_center.checkins_checkin_relationships cr_person
        ON p.person_id = cr_person.relationship_id
        AND cr_person.relationship_type = 'Person'
    JOIN planning_center.checkins_checkins c
        ON cr_person.checkin_id = c.check_in_id
        AND c.kind = 'Volunteer'
        AND c.created_at >= DATE_TRUNC('quarter', CURRENT_DATE)
    LEFT JOIN planning_center.checkins_checkin_relationships cr_location
        ON c.check_in_id = cr_location.checkin_id
        AND cr_location.relationship_type = 'Location'
    LEFT JOIN planning_center.checkins_locations l
        ON cr_location.relationship_id = l.location_id
    GROUP BY p.person_id, p.first_name, p.last_name
),
volunteer_categories AS (
    SELECT
        person_id,
        first_name,
        last_name,
        days_served,
        weeks_served,
        TO_CHAR(first_service, 'MM/DD/YY') as first_service_date,
        TO_CHAR(last_service, 'MM/DD/YY') as last_service_date,
        locations_served,
        service_areas,
        CURRENT_DATE - DATE(last_service) as days_since_last_service,
        CASE 
            WHEN weeks_served >= 10 THEN 'Core Volunteer'
            WHEN weeks_served >= 5 THEN 'Regular Volunteer'
            WHEN weeks_served >= 2 THEN 'Occasional Volunteer'
            ELSE 'New Volunteer'
        END as volunteer_category,
        CASE 
            WHEN CURRENT_DATE - DATE(last_service) > 30 THEN 'Inactive'
            WHEN CURRENT_DATE - DATE(last_service) > 14 THEN 'Missing'
            ELSE 'Active'
        END as status
    FROM volunteer_service
)
SELECT
    first_name || ' ' || last_name as volunteer_name,
    volunteer_category,
    status,
    days_served,
    weeks_served as weeks_served_this_quarter,
    first_service_date,
    last_service_date,
    days_since_last_service,
    locations_served as num_areas_served,
    service_areas
FROM volunteer_categories
ORDER BY 
    CASE volunteer_category
        WHEN 'Core Volunteer' THEN 1
        WHEN 'Regular Volunteer' THEN 2
        WHEN 'Occasional Volunteer' THEN 3
        ELSE 4
    END,
    days_served DESC;

-- Identify areas needing additional volunteers
WITH location_requirements AS (
    SELECT
        l.location_id,
        l.name as location,
        l.attendees_per_volunteer as ratio_requirement,
        l.min_volunteers as minimum_volunteers,
        l.max_occupancy
    FROM planning_center.checkins_locations l
    WHERE l.child_or_adult = 'child'
        AND l.kind = 'Folder'
        AND (l.attendees_per_volunteer IS NOT NULL OR l.min_volunteers IS NOT NULL)
),
recent_attendance AS (
    SELECT
        lr.location,
        lr.ratio_requirement,
        lr.minimum_volunteers,
        AVG(daily_children) as avg_children,
        AVG(daily_volunteers) as avg_volunteers,
        MAX(daily_children) as peak_children,
        MIN(daily_volunteers) as min_volunteers_actual
    FROM location_requirements lr
    LEFT JOIN LATERAL (
        SELECT
            DATE(c.created_at) as service_date,
            COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as daily_children,
            COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as daily_volunteers
        FROM planning_center.checkins_checkin_relationships cr
        JOIN planning_center.checkins_checkins c
            ON cr.checkin_id = c.check_in_id
        WHERE cr.relationship_id = lr.location_id
            AND cr.relationship_type = 'Location'
            AND c.created_at >= CURRENT_DATE - INTERVAL '4 weeks'
        GROUP BY DATE(c.created_at)
    ) daily_stats ON true
    GROUP BY lr.location, lr.ratio_requirement, lr.minimum_volunteers
)
SELECT
    location,
    ROUND(avg_children, 0) as avg_children,
    peak_children,
    ROUND(avg_volunteers, 0) as current_avg_volunteers,
    CASE 
        WHEN ratio_requirement IS NOT NULL AND avg_children > 0 THEN
            CEIL(avg_children / ratio_requirement)
        ELSE minimum_volunteers
    END as volunteers_needed,
    CASE 
        WHEN ratio_requirement IS NOT NULL AND peak_children > 0 THEN
            CEIL(peak_children / ratio_requirement)
        ELSE minimum_volunteers
    END as peak_volunteers_needed,
    CASE 
        WHEN ratio_requirement IS NOT NULL AND avg_children > 0 THEN
            GREATEST(0, CEIL(avg_children / ratio_requirement) - ROUND(avg_volunteers, 0))
        ELSE GREATEST(0, COALESCE(minimum_volunteers, 0) - ROUND(avg_volunteers, 0))
    END as additional_volunteers_needed,
    CASE 
        WHEN min_volunteers_actual < COALESCE(minimum_volunteers, 1) THEN '🚨 Critical - Below minimum'
        WHEN ratio_requirement IS NOT NULL AND avg_volunteers > 0 
             AND (avg_children / avg_volunteers) > ratio_requirement * 1.2 THEN '⚠️ Often over ratio'
        WHEN ratio_requirement IS NOT NULL AND avg_volunteers > 0 
             AND (avg_children / avg_volunteers) > ratio_requirement THEN '⚠️ At ratio limit'
        ELSE '✓ Adequately staffed'
    END as staffing_status
FROM recent_attendance
WHERE avg_children > 0 OR avg_volunteers > 0
ORDER BY 
    additional_volunteers_needed DESC,
    location;

-- Generate follow-up list for guest services team
WITH guest_visits AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        p.gender,
        p.birthdate,
        c.check_in_id,
        c.created_at as visit_date,
        c.one_time_guest,
        c.emergency_contact_name,
        c.emergency_contact_phone_number
    FROM planning_center.checkins_people p
    JOIN planning_center.checkins_checkin_relationships cr
        ON p.person_id = cr.relationship_id
        AND cr.relationship_type = 'Person'
    JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    WHERE c.kind = 'Guest'
        AND c.created_at >= DATE_TRUNC('week', CURRENT_DATE)
),
guest_summary AS (
    SELECT
        person_id,
        first_name,
        last_name,
        gender,
        CASE 
            WHEN birthdate IS NOT NULL THEN 
                EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate))::TEXT || ' years'
            ELSE 'Age unknown'
        END as age,
        COUNT(*) as visits_this_week,
        MIN(visit_date) as first_visit_this_week,
        BOOL_OR(one_time_guest) as is_first_time_guest,
        STRING_AGG(DISTINCT emergency_contact_name || ' (' || emergency_contact_phone_number || ')', '; ') 
            FILTER (WHERE emergency_contact_name IS NOT NULL) as emergency_contacts
    FROM guest_visits
    GROUP BY person_id, first_name, last_name, gender, birthdate
)
SELECT
    ROW_NUMBER() OVER (ORDER BY first_visit_this_week) as follow_up_priority,
    first_name || ' ' || last_name as guest_name,
    gender,
    age,
    TO_CHAR(first_visit_this_week, 'Day, MM/DD') as visit_day,
    visits_this_week,
    CASE 
        WHEN is_first_time_guest THEN 'First-Time Guest'
        ELSE 'Returning Guest'
    END as guest_type,
    emergency_contacts,
    CASE 
        WHEN is_first_time_guest THEN 'Send welcome packet'
        WHEN visits_this_week > 1 THEN 'Multiple visits - high interest'
        ELSE 'Standard follow-up'
    END as follow_up_action
FROM guest_summary
ORDER BY 
    is_first_time_guest DESC,
    first_visit_this_week;

-- Analyze attendance patterns to optimize service times
WITH service_patterns AS (
    SELECT
        TO_CHAR(created_at, 'Day') as day_of_week,
        TO_CHAR(created_at, 'HH12:00 AM') as service_hour,
        DATE(created_at) as service_date,
        COUNT(DISTINCT check_in_id) as attendance
    FROM planning_center.checkins_checkins
    WHERE created_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY TO_CHAR(created_at, 'Day'), TO_CHAR(created_at, 'HH12:00 AM'), 
             DATE(created_at), EXTRACT(DOW FROM created_at), EXTRACT(HOUR FROM created_at)
),
service_analysis AS (
    SELECT
        day_of_week,
        service_hour,
        COUNT(DISTINCT service_date) as num_services,
        ROUND(AVG(attendance), 0) as avg_attendance,
        MIN(attendance) as min_attendance,
        MAX(attendance) as max_attendance,
        ROUND(STDDEV(attendance), 1) as attendance_stddev
    FROM service_patterns
    GROUP BY day_of_week, service_hour, 
             EXTRACT(DOW FROM service_date), EXTRACT(HOUR FROM service_date)
    HAVING COUNT(DISTINCT service_date) >= 3
)
SELECT
    day_of_week,
    service_hour,
    num_services as services_held,
    avg_attendance,
    min_attendance,
    max_attendance,
    attendance_stddev as variance,
    ROUND((max_attendance - min_attendance)::NUMERIC / NULLIF(avg_attendance, 0) * 100, 0) as volatility_pct,
    CASE 
        WHEN avg_attendance < 30 THEN 'Consider combining with another service'
        WHEN attendance_stddev > avg_attendance * 0.5 THEN 'High variance - investigate causes'
        WHEN max_attendance > avg_attendance * 1.4 THEN 'Occasional overflow - plan accordingly'
        ELSE 'Stable attendance pattern'
    END as recommendation
FROM service_analysis
ORDER BY 
    CASE day_of_week
        WHEN 'Sunday' THEN 1
        WHEN 'Saturday' THEN 2
        WHEN 'Wednesday' THEN 3
        WHEN 'Thursday' THEN 4
        WHEN 'Friday' THEN 5
        WHEN 'Tuesday' THEN 6
        WHEN 'Monday' THEN 7
    END,
    service_hour;

-- Comprehensive year-end summary for annual reports
WITH yearly_stats AS (
    SELECT
        COUNT(DISTINCT check_in_id) as total_checkins,
        COUNT(DISTINCT CASE WHEN kind = 'Regular' THEN check_in_id END) as regular_checkins,
        COUNT(DISTINCT CASE WHEN kind = 'Guest' THEN check_in_id END) as guest_checkins,
        COUNT(DISTINCT CASE WHEN kind = 'Volunteer' THEN check_in_id END) as volunteer_checkins,
        COUNT(DISTINCT CASE WHEN one_time_guest = true THEN check_in_id END) as first_time_guests,
        COUNT(DISTINCT DATE(created_at)) as service_days,
        COUNT(DISTINCT DATE_TRUNC('week', created_at)) as weeks_with_services
    FROM planning_center.checkins_checkins
    WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND created_at < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
),
unique_people AS (
    SELECT
        COUNT(DISTINCT p.person_id) as unique_attendees,
        COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN p.person_id END) as unique_volunteers
    FROM planning_center.checkins_people p
    JOIN planning_center.checkins_checkin_relationships cr
        ON p.person_id = cr.relationship_id
        AND cr.relationship_type = 'Person'
    JOIN planning_center.checkins_checkins c
        ON cr.checkin_id = c.check_in_id
    WHERE c.created_at >= DATE_TRUNC('year', CURRENT_DATE)
),
peak_attendance AS (
    SELECT
        DATE(created_at) as peak_date,
        COUNT(DISTINCT check_in_id) as peak_count
    FROM planning_center.checkins_checkins
    WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY DATE(created_at)
    ORDER BY COUNT(DISTINCT check_in_id) DESC
    LIMIT 1
)
SELECT
    '===========================================' as divider1,
    TO_CHAR(DATE_TRUNC('year', CURRENT_DATE), 'YYYY') || ' ANNUAL MINISTRY IMPACT REPORT' as report_title,
    '===========================================' as divider2,
    '' as blank1,
    '📊 ATTENDANCE OVERVIEW' as section1,
    '-------------------------------------------' as divider3,
    ys.total_checkins as total_annual_checkins,
    up.unique_attendees as unique_individuals,
    ys.service_days as days_with_services,
    ys.weeks_with_services as weeks_with_services,
    ROUND(ys.total_checkins::NUMERIC / NULLIF(ys.service_days, 0), 0) as avg_daily_attendance,
    ROUND(ys.total_checkins::NUMERIC / NULLIF(ys.weeks_with_services, 0), 0) as avg_weekly_attendance,
    '' as blank2,
    '👥 ATTENDANCE BREAKDOWN' as section2,
    '-------------------------------------------' as divider4,
    ys.regular_checkins as regular_attendee_checkins,
    ys.guest_checkins as guest_checkins,
    ys.first_time_guests as first_time_visitors,
    ROUND((ys.guest_checkins::NUMERIC / NULLIF(ys.total_checkins, 0)) * 100, 1) as guest_percentage,
    '' as blank3,
    '🙋 VOLUNTEER IMPACT' as section3,
    '-------------------------------------------' as divider5,
    ys.volunteer_checkins as total_volunteer_checkins,
    up.unique_volunteers as unique_volunteers,
    ROUND(ys.volunteer_checkins::NUMERIC / NULLIF(ys.weeks_with_services, 0), 0) as avg_volunteers_per_week,
    '' as blank4,
    '🏆 PEAK ATTENDANCE' as section4,
    '-------------------------------------------' as divider6,
    TO_CHAR(pa.peak_date, 'Month DD, YYYY') as highest_attendance_date,
    pa.peak_count as highest_attendance_count,
    '' as blank5,
    '===========================================' as divider7
FROM yearly_stats ys, unique_people up, peak_attendance pa;