-- See your 10 most recent event signups
SELECT 
    s.signup_id,
    s.name as event_name,
    s.description,
    s.open_at,
    s.close_at,
    COUNT(DISTINCT a.attendee_id) as attendee_count,
    COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlist_count
FROM planning_center.registrations_signups s
LEFT JOIN planning_center.registrations_attendees a 
    ON EXISTS (
        SELECT 1 FROM planning_center.registrations_attendee_relationships ar
        WHERE ar.attendee_id = a.attendee_id
        AND ar.relationship_type = 'signup'
        AND ar.relationship_id = s.signup_id
    )
WHERE s.archived = false
GROUP BY s.signup_id, s.name, s.description, s.open_at, s.close_at
ORDER BY s.created_at DESC
LIMIT 10;

-- Events with waitlists and conversion potential
SELECT 
    s.name as event_name,
    st.starts_at as event_date,
    COUNT(CASE WHEN a.active = true THEN 1 END) as registered_count,
    COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlist_count,
    sl.name as location_name
FROM planning_center.registrations_signups s
LEFT JOIN planning_center.registrations_signup_relationships sr 
    ON sr.signup_id = s.signup_id 
    AND sr.relationship_type = 'signup_time'
LEFT JOIN planning_center.registrations_signup_times st 
    ON st.signup_time_id = sr.relationship_id
LEFT JOIN planning_center.registrations_signup_relationships slr 
    ON slr.signup_id = s.signup_id 
    AND slr.relationship_type = 'signup_location'
LEFT JOIN planning_center.registrations_signup_locations sl 
    ON sl.signup_location_id = slr.relationship_id
LEFT JOIN planning_center.registrations_attendees a 
    ON EXISTS (
        SELECT 1 FROM planning_center.registrations_attendee_relationships ar
        WHERE ar.attendee_id = a.attendee_id
        AND ar.relationship_type = 'signup'
        AND ar.relationship_id = s.signup_id
    )
WHERE s.archived = false
  AND st.starts_at >= CURRENT_DATE
GROUP BY s.name, st.starts_at, sl.name
HAVING COUNT(CASE WHEN a.waitlisted = true THEN 1 END) > 0
ORDER BY st.starts_at;

-- Registration patterns by days before event
WITH registration_timeline AS (
    SELECT 
        s.name as event_name,
        st.starts_at as event_date,
        a.created_at as registration_date,
        DATE_PART('day', st.starts_at - a.created_at) as days_before_event
    FROM planning_center.registrations_attendees a
    JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.attendee_id = a.attendee_id 
        AND ar.relationship_type = 'signup'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar.relationship_id
    JOIN planning_center.registrations_signup_relationships sr 
        ON sr.signup_id = s.signup_id 
        AND sr.relationship_type = 'signup_time'
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr.relationship_id
    WHERE a.active = true
)
SELECT 
    CASE 
        WHEN days_before_event >= 30 THEN '30+ days'
        WHEN days_before_event >= 14 THEN '14-29 days'
        WHEN days_before_event >= 7 THEN '7-13 days'
        WHEN days_before_event >= 1 THEN '1-6 days'
        ELSE 'Day of event'
    END as registration_window,
    COUNT(*) as registration_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM registration_timeline
WHERE days_before_event >= 0
GROUP BY 1
ORDER BY 
    CASE registration_window
        WHEN '30+ days' THEN 1
        WHEN '14-29 days' THEN 2
        WHEN '7-13 days' THEN 3
        WHEN '1-6 days' THEN 4
        WHEN 'Day of event' THEN 5
    END;