-- Executive summary report for church leadership
WITH current_stats AS (
    SELECT
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as active_members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Member' THEN person_id END) as full_members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Regular Attender' THEN person_id END) as regular_attenders,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Visitor' THEN person_id END) as visitors,
        COUNT(DISTINCT CASE WHEN created_at >= DATE_TRUNC('week', CURRENT_DATE) THEN person_id END) as new_this_week,
        COUNT(DISTINCT CASE WHEN child = true AND status = 'active' THEN person_id END) as children,
        COUNT(DISTINCT CASE WHEN graduation_year IS NOT NULL AND status = 'active' THEN person_id END) as students,
        COUNT(DISTINCT CASE WHEN birthdate IS NOT NULL AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 AND status = 'active' THEN person_id END) as seniors
    FROM planning_center.people_people
),
previous_week AS (
    SELECT
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as active_members
    FROM planning_center.people_people
    WHERE created_at < DATE_TRUNC('week', CURRENT_DATE)
),
household_stats AS (
    SELECT
        COUNT(DISTINCT h.household_id) as total_households,
        AVG(h.member_count) as avg_household_size
    FROM planning_center.people_households h
)
SELECT
    '=== WEEKLY MEMBERSHIP EXECUTIVE SUMMARY ===' as report_header,
    TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'Month DD, YYYY') as week_beginning,
    '' as blank1,
    '--- MEMBERSHIP METRICS ---' as section1,
    cs.active_members as total_active_members,
    pw.active_members as active_members_last_week,
    cs.active_members - pw.active_members as net_change,
    cs.new_this_week as new_people_this_week,
    '' as blank2,
    '--- MEMBERSHIP BREAKDOWN ---' as section2,
    cs.full_members as members,
    cs.regular_attenders as regular_attenders,
    cs.visitors as visitors,
    ROUND((cs.full_members::NUMERIC / NULLIF(cs.active_members, 0)) * 100, 1) as member_percentage,
    '' as blank3,
    '--- DEMOGRAPHICS ---' as section3,
    cs.children as children_count,
    cs.students as student_count,
    cs.seniors as senior_count,
    cs.active_members - cs.children - cs.students as adult_count,
    '' as blank4,
    '--- HOUSEHOLD METRICS ---' as section4,
    hs.total_households as households,
    ROUND(hs.avg_household_size, 1) as avg_household_size
FROM current_stats cs, previous_week pw, household_stats hs;

-- Comprehensive demographic breakdown for strategic planning
WITH age_demographics AS (
    SELECT
        CASE 
            WHEN child = true THEN '0-12 (Children)'
            WHEN graduation_year IS NOT NULL AND graduation_year >= EXTRACT(YEAR FROM CURRENT_DATE) THEN '13-18 (Students)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 18 AND 24 THEN '18-24 (College)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 25 AND 34 THEN '25-34 (Young Adult)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 35 AND 44 THEN '35-44 (Young Family)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 45 AND 54 THEN '45-54 (Middle Age)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) BETWEEN 55 AND 64 THEN '55-64 (Pre-Retirement)'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 THEN '65+ (Senior)'
            ELSE 'Age Unknown'
        END as age_group,
        gender,
        membership,
        COUNT(*) as count
    FROM planning_center.people_people
    WHERE status = 'active'
    GROUP BY age_group, gender, membership
),
demographic_summary AS (
    SELECT
        age_group,
        SUM(count) as total,
        SUM(CASE WHEN gender = 'Male' THEN count ELSE 0 END) as male_count,
        SUM(CASE WHEN gender = 'Female' THEN count ELSE 0 END) as female_count,
        SUM(CASE WHEN membership = 'Member' THEN count ELSE 0 END) as members,
        SUM(CASE WHEN membership = 'Regular Attender' THEN count ELSE 0 END) as regular_attenders,
        SUM(CASE WHEN membership = 'Visitor' THEN count ELSE 0 END) as visitors
    FROM age_demographics
    GROUP BY age_group
)
SELECT
    age_group,
    total,
    male_count,
    female_count,
    CASE 
        WHEN (male_count + female_count) > 0 
        THEN ROUND(male_count::NUMERIC / (male_count + female_count) * 100, 0)
        ELSE NULL
    END as male_percentage,
    members,
    regular_attenders,
    visitors,
    ROUND((total::NUMERIC / (SELECT SUM(total) FROM demographic_summary)) * 100, 1) as percent_of_church
FROM demographic_summary
ORDER BY 
    CASE 
        WHEN age_group LIKE '0-12%' THEN 1
        WHEN age_group LIKE '13-18%' THEN 2
        WHEN age_group LIKE '18-24%' THEN 3
        WHEN age_group LIKE '25-34%' THEN 4
        WHEN age_group LIKE '35-44%' THEN 5
        WHEN age_group LIKE '45-54%' THEN 6
        WHEN age_group LIKE '55-64%' THEN 7
        WHEN age_group LIKE '65+%' THEN 8
        ELSE 9
    END;

-- Upcoming birthdays and anniversaries for pastoral care
WITH upcoming_dates AS (
    SELECT
        person_id,
        first_name,
        last_name,
        birthdate,
        anniversary,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) + 1 as turning_age,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, anniversary)) + 1 as anniversary_years,
        -- Calculate next birthday
        CASE 
            WHEN DATE_PART('doy', birthdate) >= DATE_PART('doy', CURRENT_DATE)
            THEN DATE_TRUNC('year', CURRENT_DATE) + (birthdate - DATE_TRUNC('year', birthdate))
            ELSE DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' + (birthdate - DATE_TRUNC('year', birthdate))
        END as next_birthday,
        -- Calculate next anniversary
        CASE 
            WHEN anniversary IS NOT NULL AND DATE_PART('doy', anniversary) >= DATE_PART('doy', CURRENT_DATE)
            THEN DATE_TRUNC('year', CURRENT_DATE) + (anniversary - DATE_TRUNC('year', anniversary))
            ELSE DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year' + (anniversary - DATE_TRUNC('year', anniversary))
        END as next_anniversary
    FROM planning_center.people_people
    WHERE status = 'active'
        AND (birthdate IS NOT NULL OR anniversary IS NOT NULL)
),
combined_events AS (
    SELECT
        person_id,
        first_name,
        last_name,
        'Birthday' as event_type,
        next_birthday as event_date,
        turning_age::TEXT || ' years old' as detail
    FROM upcoming_dates
    WHERE birthdate IS NOT NULL
    
    UNION ALL
    
    SELECT
        person_id,
        first_name,
        last_name,
        'Anniversary' as event_type,
        next_anniversary as event_date,
        anniversary_years::TEXT || ' years' as detail
    FROM upcoming_dates
    WHERE anniversary IS NOT NULL
)
SELECT
    TO_CHAR(event_date, 'MM/DD') as date,
    TO_CHAR(event_date, 'Day') as day_of_week,
    first_name || ' ' || last_name as person,
    event_type,
    detail,
    event_date - CURRENT_DATE as days_until
FROM combined_events
WHERE event_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
ORDER BY event_date, last_name;

-- People requiring pastoral follow-up based on various criteria
WITH follow_up_needs AS (
    -- Recently inactive
    SELECT
        p.person_id,
        p.first_name || ' ' || p.last_name as name,
        'Recently Inactive' as reason,
        p.inactivated_at as trigger_date,
        ir.name as details,
        1 as priority
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships pr
        ON p.person_id = pr.person_id
        AND pr.relationship_type = 'InactiveReason'
    LEFT JOIN planning_center.people_inactive_reasons ir
        ON pr.relationship_id = ir.inactive_reason_id
    WHERE p.status = 'inactive'
        AND p.inactivated_at >= CURRENT_DATE - INTERVAL '30 days'
    
    UNION ALL
    
    -- First-time visitors (no membership status)
    SELECT
        person_id,
        first_name || ' ' || last_name,
        'First-Time Visitor',
        created_at,
        'Joined ' || TO_CHAR(created_at, 'MM/DD'),
        2
    FROM planning_center.people_people
    WHERE membership = 'Visitor'
        AND created_at >= CURRENT_DATE - INTERVAL '14 days'
        AND status = 'active'
    
    UNION ALL
    
    -- Background check expiring
    SELECT
        p.person_id,
        p.first_name || ' ' || p.last_name,
        'Background Check Expiring',
        bc.expires_on,
        'Expires ' || TO_CHAR(bc.expires_on, 'MM/DD'),
        3
    FROM planning_center.people_people p
    JOIN planning_center.people_background_checks bc
        ON p.person_id = bc.person_id
    WHERE bc.expires_on BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
        AND p.status = 'active'
)
SELECT
    name,
    reason,
    details,
    TO_CHAR(trigger_date, 'MM/DD/YYYY') as date,
    CURRENT_DATE - trigger_date::DATE as days_ago,
    CASE priority
        WHEN 1 THEN '🔴 High'
        WHEN 2 THEN '🟡 Medium'
        ELSE '🟢 Low'
    END as priority_level
FROM follow_up_needs
ORDER BY priority, trigger_date DESC;

-- Analyze household structures and family compositions
WITH household_analysis AS (
    SELECT
        h.household_id,
        h.name as household_name,
        h.member_count,
        COUNT(DISTINCT CASE WHEN p.child = true THEN p.person_id END) as children,
        COUNT(DISTINCT CASE WHEN p.child = false OR p.child IS NULL THEN p.person_id END) as adults,
        COUNT(DISTINCT CASE WHEN p.graduation_year IS NOT NULL THEN p.person_id END) as students,
        MIN(p.birthdate) as oldest_member_birthdate,
        MAX(p.birthdate) as youngest_member_birthdate,
        STRING_AGG(p.first_name, ', ' ORDER BY p.birthdate) as members
    FROM planning_center.people_households h
    JOIN planning_center.people_household_memberships hm
        ON h.household_id = hm.household_id
    JOIN planning_center.people_people p
        ON hm.person_id = p.person_id
        AND p.status = 'active'
    GROUP BY h.household_id, h.name, h.member_count
),
household_categories AS (
    SELECT
        household_name,
        member_count,
        adults,
        children,
        students,
        members,
        CASE
            WHEN adults = 1 AND children = 0 THEN 'Single Adult'
            WHEN adults = 2 AND children = 0 THEN 'Married No Children'
            WHEN adults = 1 AND children > 0 THEN 'Single Parent'
            WHEN adults = 2 AND children > 0 THEN 'Nuclear Family'
            WHEN adults > 2 THEN 'Multi-Generational'
            ELSE 'Other'
        END as family_type,
        CASE
            WHEN children > 0 AND children <= 2 THEN 'Small Family'
            WHEN children >= 3 THEN 'Large Family'
            WHEN students > 0 THEN 'Family with Teens'
            ELSE 'No Children'
        END as family_stage
    FROM household_analysis
)
SELECT
    family_type,
    COUNT(*) as household_count,
    SUM(member_count) as total_people,
    ROUND(AVG(member_count), 1) as avg_household_size,
    SUM(children) as total_children,
    SUM(adults) as total_adults,
    ROUND(AVG(children), 1) as avg_children_per_household,
    ROUND((COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM household_categories)) * 100, 1) as percent_of_households
FROM household_categories
GROUP BY family_type
ORDER BY household_count DESC;

-- Analyze member distribution across campuses
WITH campus_metrics AS (
    SELECT
        c.campus_id,
        c.name as campus_name,
        c.city,
        c.state,
        COUNT(DISTINCT pr.person_id) as total_members,
        COUNT(DISTINCT CASE WHEN p.membership = 'Member' THEN pr.person_id END) as full_members,
        COUNT(DISTINCT CASE WHEN p.child = true THEN pr.person_id END) as children,
        COUNT(DISTINCT CASE WHEN p.graduation_year IS NOT NULL THEN pr.person_id END) as students,
        COUNT(DISTINCT CASE WHEN p.created_at >= CURRENT_DATE - INTERVAL '90 days' THEN pr.person_id END) as new_in_90_days,
        AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate))) as avg_age
    FROM planning_center.people_campuses c
    LEFT JOIN planning_center.people_people_relationships pr
        ON c.campus_id = pr.relationship_id
        AND pr.relationship_type = 'Campus'
    LEFT JOIN planning_center.people_people p
        ON pr.person_id = p.person_id
        AND p.status = 'active'
    GROUP BY c.campus_id, c.name, c.city, c.state
),
campus_growth AS (
    SELECT
        c.name as campus_name,
        COUNT(DISTINCT CASE 
            WHEN p.created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' 
            THEN pr.person_id 
        END) as last_month,
        COUNT(DISTINCT CASE 
            WHEN p.created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '2 months'
            AND p.created_at < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
            THEN pr.person_id 
        END) as two_months_ago
    FROM planning_center.people_campuses c
    LEFT JOIN planning_center.people_people_relationships pr
        ON c.campus_id = pr.relationship_id
        AND pr.relationship_type = 'Campus'
    LEFT JOIN planning_center.people_people p
        ON pr.person_id = p.person_id
    GROUP BY c.name
)
SELECT
    cm.campus_name,
    cm.city || ', ' || cm.state as location,
    cm.total_members,
    cm.full_members,
    ROUND((cm.full_members::NUMERIC / NULLIF(cm.total_members, 0)) * 100, 1) as member_percentage,
    cm.children,
    cm.students,
    ROUND(cm.avg_age, 0) as avg_age,
    cm.new_in_90_days as new_members_90d,
    cg.last_month - cg.two_months_ago as monthly_growth,
    ROUND((cm.total_members::NUMERIC / (SELECT SUM(total_members) FROM campus_metrics)) * 100, 1) as percent_of_church
FROM campus_metrics cm
JOIN campus_growth cg ON cm.campus_name = cg.campus_name
WHERE cm.total_members > 0
ORDER BY cm.total_members DESC;

-- Analyze completeness of contact information for communication planning
WITH contact_completeness AS (
    SELECT
        p.person_id,
        p.first_name,
        p.last_name,
        p.status,
        p.membership,
        CASE WHEN e.email_id IS NOT NULL THEN 1 ELSE 0 END as has_email,
        CASE WHEN ph.phone_number_id IS NOT NULL THEN 1 ELSE 0 END as has_phone,
        CASE WHEN a.address_id IS NOT NULL THEN 1 ELSE 0 END as has_address,
        CASE WHEN e.blocked = true THEN 1 ELSE 0 END as email_blocked
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_emails e
        ON p.person_id = e.person_id
        AND e.is_primary = true
    LEFT JOIN planning_center.people_phone_numbers ph
        ON p.person_id = ph.person_id
        AND ph.is_primary = true
    LEFT JOIN planning_center.people_addresses a
        ON p.person_id = a.person_id
        AND a.is_primary = true
    WHERE p.status = 'active'
),
completeness_summary AS (
    SELECT
        membership,
        COUNT(*) as total_people,
        SUM(has_email) as with_email,
        SUM(has_phone) as with_phone,
        SUM(has_address) as with_address,
        SUM(CASE WHEN has_email = 1 AND has_phone = 1 THEN 1 ELSE 0 END) as email_and_phone,
        SUM(CASE WHEN has_email = 1 AND has_phone = 1 AND has_address = 1 THEN 1 ELSE 0 END) as complete_contact,
        SUM(CASE WHEN has_email = 0 AND has_phone = 0 THEN 1 ELSE 0 END) as no_contact,
        SUM(email_blocked) as emails_blocked
    FROM contact_completeness
    GROUP BY membership
)
SELECT
    COALESCE(membership, 'Unknown') as membership_level,
    total_people,
    with_email,
    ROUND((with_email::NUMERIC / total_people) * 100, 1) as email_percentage,
    with_phone,
    ROUND((with_phone::NUMERIC / total_people) * 100, 1) as phone_percentage,
    with_address,
    ROUND((with_address::NUMERIC / total_people) * 100, 1) as address_percentage,
    complete_contact,
    ROUND((complete_contact::NUMERIC / total_people) * 100, 1) as complete_percentage,
    no_contact,
    emails_blocked
FROM completeness_summary
ORDER BY 
    CASE membership
        WHEN 'Member' THEN 1
        WHEN 'Regular Attender' THEN 2
        WHEN 'Visitor' THEN 3
        ELSE 4
    END;

-- Track membership growth patterns over quarters
WITH quarterly_data AS (
    SELECT
        DATE_TRUNC('quarter', created_at) as quarter,
        COUNT(DISTINCT person_id) as new_people,
        COUNT(DISTINCT CASE WHEN membership = 'Member' THEN person_id END) as new_members,
        COUNT(DISTINCT CASE WHEN membership = 'Regular Attender' THEN person_id END) as new_regular,
        COUNT(DISTINCT CASE WHEN membership = 'Visitor' THEN person_id END) as new_visitors,
        COUNT(DISTINCT CASE WHEN child = true THEN person_id END) as new_children
    FROM planning_center.people_people
    WHERE created_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 year'
        AND status = 'active'
    GROUP BY DATE_TRUNC('quarter', created_at)
),
inactivated_data AS (
    SELECT
        DATE_TRUNC('quarter', inactivated_at) as quarter,
        COUNT(DISTINCT person_id) as people_inactivated
    FROM planning_center.people_people
    WHERE inactivated_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY DATE_TRUNC('quarter', inactivated_at)
),
combined_metrics AS (
    SELECT
        qd.quarter,
        TO_CHAR(qd.quarter, 'Q[Q] YYYY') as quarter_label,
        qd.new_people,
        qd.new_members,
        qd.new_regular,
        qd.new_visitors,
        qd.new_children,
        COALESCE(id.people_inactivated, 0) as people_lost,
        qd.new_people - COALESCE(id.people_inactivated, 0) as net_growth,
        LAG(qd.new_people, 1) OVER (ORDER BY qd.quarter) as prev_quarter_new
    FROM quarterly_data qd
    LEFT JOIN inactivated_data id ON qd.quarter = id.quarter
)
SELECT
    quarter_label,
    new_people,
    new_members,
    new_regular,
    new_visitors,
    people_lost,
    net_growth,
    CASE 
        WHEN prev_quarter_new > 0 THEN 
            ROUND(((new_people - prev_quarter_new)::NUMERIC / prev_quarter_new) * 100, 1)
        ELSE NULL
    END as growth_rate,
    ROUND((new_members::NUMERIC / NULLIF(new_people, 0)) * 100, 1) as member_conversion_rate
FROM combined_metrics
ORDER BY quarter DESC;

-- Comprehensive year-end census for annual reports
WITH yearly_stats AS (
    SELECT
        -- Total counts
        COUNT(DISTINCT CASE WHEN status = 'active' THEN person_id END) as total_active,
        COUNT(DISTINCT CASE WHEN status = 'inactive' THEN person_id END) as total_inactive,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Member' THEN person_id END) as members,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Regular Attender' THEN person_id END) as regular_attenders,
        COUNT(DISTINCT CASE WHEN status = 'active' AND membership = 'Visitor' THEN person_id END) as visitors,
        -- Demographics
        COUNT(DISTINCT CASE WHEN status = 'active' AND child = true THEN person_id END) as children,
        COUNT(DISTINCT CASE WHEN status = 'active' AND graduation_year IS NOT NULL THEN person_id END) as students,
        COUNT(DISTINCT CASE WHEN status = 'active' AND EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) >= 65 THEN person_id END) as seniors,
        -- New this year
        COUNT(DISTINCT CASE 
            WHEN created_at >= DATE_TRUNC('year', CURRENT_DATE) 
            AND status = 'active' 
            THEN person_id 
        END) as new_this_year,
        -- Lost this year
        COUNT(DISTINCT CASE 
            WHEN inactivated_at >= DATE_TRUNC('year', CURRENT_DATE) 
            THEN person_id 
        END) as lost_this_year,
        -- Gender breakdown
        COUNT(DISTINCT CASE WHEN status = 'active' AND gender = 'Male' THEN person_id END) as males,
        COUNT(DISTINCT CASE WHEN status = 'active' AND gender = 'Female' THEN person_id END) as females
    FROM planning_center.people_people
),
household_yearly AS (
    SELECT
        COUNT(DISTINCT household_id) as total_households,
        AVG(member_count) as avg_household_size,
        COUNT(DISTINCT CASE WHEN member_count = 1 THEN household_id END) as single_households,
        COUNT(DISTINCT CASE WHEN member_count >= 4 THEN household_id END) as large_households
    FROM planning_center.people_households
),
campus_count AS (
    SELECT COUNT(DISTINCT campus_id) as campus_count
    FROM planning_center.people_campuses
)
SELECT
    '===========================================' as divider1,
    TO_CHAR(DATE_TRUNC('year', CURRENT_DATE), 'YYYY') || ' ANNUAL CHURCH CENSUS REPORT' as report_title,
    '===========================================' as divider2,
    '' as blank1,
    '📊 MEMBERSHIP OVERVIEW' as section1,
    '-------------------------------------------' as divider3,
    ys.total_active as total_active_people,
    ys.members as full_members,
    ys.regular_attenders,
    ys.visitors,
    ys.new_this_year as people_added_this_year,
    ys.lost_this_year as people_lost_this_year,
    ys.new_this_year - ys.lost_this_year as net_growth,
    '' as blank2,
    '👥 DEMOGRAPHICS' as section2,
    '-------------------------------------------' as divider4,
    ys.children,
    ys.students,
    ys.total_active - ys.children - ys.students - ys.seniors as working_adults,
    ys.seniors,
    ys.males,
    ys.females,
    ROUND(ys.males::NUMERIC / NULLIF(ys.males + ys.females, 0) * 100, 0) as male_percentage,
    '' as blank3,
    '🏠 HOUSEHOLD STATISTICS' as section3,
    '-------------------------------------------' as divider5,
    hy.total_households,
    ROUND(hy.avg_household_size, 1) as avg_household_size,
    hy.single_households as single_person_households,
    hy.large_households as households_4_plus,
    '' as blank4,
    '📍 ORGANIZATIONAL' as section4,
    '-------------------------------------------' as divider6,
    cc.campus_count as number_of_campuses,
    ROUND(ys.total_active::NUMERIC / cc.campus_count, 0) as avg_per_campus,
    '' as blank5,
    '===========================================' as divider7
FROM yearly_stats ys, household_yearly hy, campus_count cc;