-- See the 10 most recent check-ins
SELECT
    c.check_in_id,
    c.first_name,
    c.last_name,
    c.kind,  -- 'Regular', 'Guest', 'Volunteer'
    c.security_code,
    c.created_at as checked_in_at,
    c.checked_out_at
FROM planning_center.checkins_checkins c
WHERE c.created_at >= CURRENT_DATE
ORDER BY c.created_at DESC
LIMIT 10;

-- Get check-ins grouped by location for today
SELECT
    l.name as location_name,
    l.kind as location_type,
    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,
    COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as volunteer_checkins
FROM planning_center.checkins_checkins c
JOIN planning_center.checkins_checkin_relationships cr
    ON c.check_in_id = cr.checkin_id
    AND cr.relationship_type = 'Location'
JOIN planning_center.checkins_locations l
    ON cr.relationship_id = l.location_id
WHERE DATE(c.created_at) = CURRENT_DATE
GROUP BY l.name, l.kind
ORDER BY total_checkins DESC;

-- Check volunteer-to-child ratios by location
SELECT
    l.name as location,
    l.attendees_per_volunteer as required_ratio,
    COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END) as children,
    COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) as volunteers,
    CASE 
        WHEN COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END) > 0
        THEN ROUND(COUNT(DISTINCT CASE WHEN c.kind = 'Regular' THEN c.check_in_id END)::NUMERIC / 
                   COUNT(DISTINCT CASE WHEN c.kind = 'Volunteer' THEN c.check_in_id END), 1)
        ELSE NULL
    END as actual_ratio
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 DATE(c.created_at) = CURRENT_DATE
    AND c.checked_out_at IS NULL  -- Only currently checked in
WHERE l.kind = 'Folder'  -- Physical locations
    AND l.child_or_adult = 'child'
GROUP BY l.name, l.attendees_per_volunteer
ORDER BY l.name;

SELECT first_name, last_name, security_code, created_at
FROM planning_center.checkins_checkins
WHERE checked_out_at IS NULL
  AND DATE(created_at) = CURRENT_DATE
ORDER BY created_at DESC;

SELECT COUNT(*) as first_time_guests
FROM planning_center.checkins_checkins
WHERE one_time_guest = true
  AND DATE(created_at) >= CURRENT_DATE - INTERVAL '7 days';