-- Executive Monthly Giving Summary
WITH current_month AS (
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE) as month_start,
        DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day' as month_end
),
last_month AS (
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') as month_start,
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day' as month_end
),
current_month_stats AS (
    SELECT 
        COUNT(DISTINCT d.donation_id) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount,
        AVG(d.amount_cents) / 100.0 as avg_donation,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.amount_cents) / 100.0 as median_donation
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    CROSS JOIN current_month cm
    WHERE d.received_at >= cm.month_start
        AND d.received_at <= cm.month_end
        AND d.refunded = false
),
last_month_stats AS (
    SELECT 
        COUNT(DISTINCT d.donation_id) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    CROSS JOIN last_month lm
    WHERE d.received_at >= lm.month_start
        AND d.received_at <= lm.month_end
        AND d.refunded = false
),
ytd_stats AS (
    SELECT 
        SUM(amount_cents) / 100.0 as ytd_total,
        COUNT(DISTINCT dr.relationship_id) as ytd_unique_donors
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND d.refunded = false
)
SELECT 
    TO_CHAR(CURRENT_DATE, 'Month YYYY') as report_month,
    cms.donation_count as donations_this_month,
    cms.unique_donors as unique_donors_this_month,
    cms.total_amount as total_this_month,
    cms.avg_donation as avg_donation_this_month,
    cms.median_donation as median_donation_this_month,
    ROUND(((cms.total_amount - lms.total_amount) / NULLIF(lms.total_amount, 0)) * 100, 2) as month_over_month_change_pct,
    ROUND(((cms.unique_donors - lms.unique_donors) / NULLIF(lms.unique_donors::numeric, 0)) * 100, 2) as donor_change_pct,
    ytd.ytd_total as year_to_date_total,
    ytd.ytd_unique_donors as year_to_date_unique_donors
FROM current_month_stats cms
CROSS JOIN last_month_stats lms
CROSS JOIN ytd_stats ytd;

-- Weekly Giving Snapshot with Comparisons
WITH weeks AS (
    SELECT 
        DATE_TRUNC('week', received_at) as week_start,
        COUNT(*) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount,
        SUM(CASE WHEN d.payment_method = 'cash' THEN d.amount_cents ELSE 0 END) / 100.0 as cash_total,
        SUM(CASE WHEN d.payment_method = 'check' THEN d.amount_cents ELSE 0 END) / 100.0 as check_total,
        SUM(CASE WHEN d.payment_method IN ('card', 'ach') THEN d.amount_cents ELSE 0 END) / 100.0 as electronic_total
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.received_at >= CURRENT_DATE - INTERVAL '8 weeks'
        AND d.refunded = false
    GROUP BY DATE_TRUNC('week', received_at)
)
SELECT 
    TO_CHAR(week_start, 'MM/DD/YYYY') as week_beginning,
    donation_count,
    unique_donors,
    total_amount,
    cash_total,
    check_total,
    electronic_total,
    LAG(total_amount, 1) OVER (ORDER BY week_start) as previous_week,
    ROUND(((total_amount - LAG(total_amount, 1) OVER (ORDER BY week_start)) / 
           NULLIF(LAG(total_amount, 1) OVER (ORDER BY week_start), 0)) * 100, 2) as week_over_week_change_pct
FROM weeks
ORDER BY week_start DESC;

-- Annual Giving Statement for a Specific Donor
-- Replace 'DONOR_ID_HERE' with actual person_id
WITH donor_info AS (
    SELECT 
        person_id,
        first_name,
        last_name,
        donor_number
    FROM planning_center.giving_people
    WHERE person_id = 'DONOR_ID_HERE'  -- Replace with actual ID
),
donation_details AS (
    SELECT 
        d.donation_id,
        d.received_at,
        d.amount_cents / 100.0 as amount,
        d.payment_method,
        d.payment_check_number,
        d.refunded,
        STRING_AGG(
            f.name || ': $' || (des.amount_cents / 100.0)::text, 
            ', ' 
            ORDER BY des.amount_cents DESC
        ) as fund_breakdown
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    JOIN donor_info di ON dr.relationship_id = di.person_id
    LEFT JOIN planning_center.giving_designation_relationships desr
        ON d.donation_id = desr.designation_id
        AND desr.relationship_type = 'Donation'
    LEFT JOIN planning_center.giving_designations des
        ON desr.relationship_id = des.designation_id
    LEFT JOIN planning_center.giving_funds f
        ON des.fund_id = f.fund_id
    WHERE EXTRACT(YEAR FROM d.received_at) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY d.donation_id, d.received_at, d.amount_cents, 
             d.payment_method, d.payment_check_number, d.refunded
),
summary AS (
    SELECT 
        COUNT(*) FILTER (WHERE NOT refunded) as total_donations,
        SUM(amount) FILTER (WHERE NOT refunded) as total_given,
        SUM(amount) FILTER (WHERE refunded) as total_refunded
    FROM donation_details
)
SELECT 
    di.first_name || ' ' || di.last_name as donor_name,
    di.donor_number,
    EXTRACT(YEAR FROM CURRENT_DATE) as tax_year,
    dd.received_at as donation_date,
    dd.amount,
    dd.payment_method,
    dd.payment_check_number as check_number,
    dd.fund_breakdown,
    CASE WHEN dd.refunded THEN 'REFUNDED' ELSE '' END as status,
    s.total_donations,
    s.total_given as year_total,
    s.total_refunded as year_refunded,
    s.total_given - COALESCE(s.total_refunded, 0) as net_contributions
FROM donation_details dd
CROSS JOIN donor_info di
CROSS JOIN summary s
ORDER BY dd.received_at;

-- Quarterly Giving Statements for All Active Donors
WITH quarter_dates AS (
    SELECT 
        DATE_TRUNC('quarter', CURRENT_DATE) as quarter_start,
        DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months' - INTERVAL '1 day' as quarter_end,
        'Q' || EXTRACT(QUARTER FROM CURRENT_DATE) || ' ' || 
        EXTRACT(YEAR FROM CURRENT_DATE) as quarter_label
),
donor_quarterly_summary AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        p.donor_number,
        COUNT(d.donation_id) as donation_count,
        SUM(d.amount_cents) / 100.0 as total_given,
        MIN(d.received_at) as first_donation,
        MAX(d.received_at) as last_donation,
        STRING_AGG(
            DISTINCT f.name,
            ', '
            ORDER BY f.name
        ) as funds_supported
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    JOIN planning_center.giving_designation_relationships desr
        ON d.donation_id = desr.designation_id
        AND desr.relationship_type = 'Donation'
    JOIN planning_center.giving_designations des
        ON desr.relationship_id = des.designation_id
    JOIN planning_center.giving_funds f
        ON des.fund_id = f.fund_id
    CROSS JOIN quarter_dates qd
    WHERE d.received_at >= qd.quarter_start
        AND d.received_at <= qd.quarter_end
        AND d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name, p.donor_number
)
SELECT 
    qd.quarter_label,
    dqs.donor_number,
    dqs.first_name || ' ' || dqs.last_name as donor_name,
    dqs.donation_count,
    dqs.total_given,
    dqs.funds_supported,
    TO_CHAR(dqs.first_donation, 'MM/DD/YYYY') as first_donation_date,
    TO_CHAR(dqs.last_donation, 'MM/DD/YYYY') as last_donation_date
FROM donor_quarterly_summary dqs
CROSS JOIN quarter_dates qd
ORDER BY dqs.total_given DESC;

-- Fund Performance Against Goals
WITH fund_goals AS (
    -- Define your fund goals here (could come from another table)
    SELECT * FROM (VALUES
        ('General Fund', 50000.00),
        ('Building Fund', 25000.00),
        ('Missions', 15000.00)
    ) AS goals(fund_name, monthly_goal)
),
current_month_giving AS (
    SELECT 
        f.fund_id,
        f.name as fund_name,
        COUNT(DISTINCT des.designation_id) as designation_count,
        COUNT(DISTINCT d.donation_id) as donation_count,
        SUM(des.amount_cents) / 100.0 as amount_received
    FROM planning_center.giving_funds f
    LEFT JOIN planning_center.giving_designations des ON f.fund_id = des.fund_id
    LEFT JOIN planning_center.giving_designation_relationships desr
        ON des.designation_id = desr.designation_id
        AND desr.relationship_type = 'Donation'
    LEFT JOIN planning_center.giving_donations d 
        ON desr.relationship_id = d.donation_id
        AND d.received_at >= DATE_TRUNC('month', CURRENT_DATE)
        AND d.received_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
        AND d.refunded = false
    GROUP BY f.fund_id, f.name
),
ytd_giving AS (
    SELECT 
        f.fund_id,
        SUM(des.amount_cents) / 100.0 as ytd_amount
    FROM planning_center.giving_funds f
    LEFT JOIN planning_center.giving_designations des ON f.fund_id = des.fund_id
    LEFT JOIN planning_center.giving_designation_relationships desr
        ON des.designation_id = desr.designation_id
        AND desr.relationship_type = 'Donation'
    LEFT JOIN planning_center.giving_donations d 
        ON desr.relationship_id = d.donation_id
        AND d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND d.refunded = false
    GROUP BY f.fund_id
)
SELECT 
    cmg.fund_name,
    COALESCE(cmg.amount_received, 0) as month_to_date,
    COALESCE(fg.monthly_goal, 0) as monthly_goal,
    COALESCE(cmg.amount_received, 0) - COALESCE(fg.monthly_goal, 0) as variance,
    CASE 
        WHEN fg.monthly_goal > 0 THEN 
            ROUND((cmg.amount_received / fg.monthly_goal) * 100, 2)
        ELSE NULL 
    END as percent_of_goal,
    COALESCE(ytd.ytd_amount, 0) as year_to_date,
    COALESCE(fg.monthly_goal * EXTRACT(MONTH FROM CURRENT_DATE), 0) as ytd_goal,
    cmg.donation_count as donations_this_month
FROM current_month_giving cmg
LEFT JOIN fund_goals fg ON cmg.fund_name = fg.fund_name
LEFT JOIN ytd_giving ytd ON cmg.fund_id = ytd.fund_id
ORDER BY COALESCE(cmg.amount_received, 0) DESC;

-- Restricted vs Unrestricted Fund Analysis
WITH fund_categories AS (
    SELECT 
        fund_id,
        name,
        CASE 
            WHEN name ILIKE '%general%' OR is_default = true THEN 'Unrestricted'
            WHEN name ILIKE '%building%' OR name ILIKE '%capital%' THEN 'Capital'
            WHEN name ILIKE '%mission%' OR name ILIKE '%outreach%' THEN 'Missions'
            WHEN name ILIKE '%benevolence%' OR name ILIKE '%compassion%' THEN 'Benevolence'
            ELSE 'Other Restricted'
        END as fund_category
    FROM planning_center.giving_funds
),
monthly_by_category AS (
    SELECT 
        fc.fund_category,
        DATE_TRUNC('month', d.received_at) as month,
        SUM(des.amount_cents) / 100.0 as amount
    FROM fund_categories fc
    JOIN planning_center.giving_designations des ON fc.fund_id = des.fund_id
    JOIN planning_center.giving_designation_relationships desr
        ON des.designation_id = desr.designation_id
        AND desr.relationship_type = 'Donation'
    JOIN planning_center.giving_donations d
        ON desr.relationship_id = d.donation_id
    WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND d.refunded = false
    GROUP BY fc.fund_category, DATE_TRUNC('month', d.received_at)
)
SELECT 
    TO_CHAR(month, 'Month') as month_name,
    SUM(CASE WHEN fund_category = 'Unrestricted' THEN amount ELSE 0 END) as unrestricted,
    SUM(CASE WHEN fund_category = 'Capital' THEN amount ELSE 0 END) as capital,
    SUM(CASE WHEN fund_category = 'Missions' THEN amount ELSE 0 END) as missions,
    SUM(CASE WHEN fund_category = 'Benevolence' THEN amount ELSE 0 END) as benevolence,
    SUM(CASE WHEN fund_category = 'Other Restricted' THEN amount ELSE 0 END) as other_restricted,
    SUM(amount) as total_month
FROM monthly_by_category
GROUP BY month, TO_CHAR(month, 'Month')
ORDER BY month;

-- Capital Campaign Progress Dashboard
WITH campaign_summary AS (
    SELECT 
        pc.pledge_campaign_id,
        pc.name as campaign_name,
        pc.description,
        pc.goal_cents / 100.0 as campaign_goal,
        pc.pledges_total_cents / 100.0 as total_pledged,
        pc.received_total_cents / 100.0 as total_received,
        pc.created_at as campaign_start,
        pc.ends_at as campaign_end
    FROM planning_center.giving_pledge_campaigns pc
    WHERE pc.name ILIKE '%capital%' OR pc.name ILIKE '%building%'  -- Adjust as needed
),
pledge_details AS (
    SELECT 
        p.pledge_campaign_id,
        COUNT(DISTINCT p.pledge_id) as pledge_count,
        COUNT(DISTINCT pr.relationship_id) as pledger_count,
        AVG(p.amount_cents / 100.0) as avg_pledge,
        MAX(p.amount_cents / 100.0) as largest_pledge,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p.amount_cents) / 100.0 as median_pledge
    FROM planning_center.giving_pledges p
    JOIN planning_center.giving_pledge_relationships pr
        ON p.pledge_id = pr.pledge_id
        AND pr.relationship_type = 'Person'
    GROUP BY p.pledge_campaign_id
),
recent_activity AS (
    SELECT 
        p.pledge_campaign_id,
        SUM(CASE 
            WHEN p.created_at >= CURRENT_DATE - INTERVAL '30 days' 
            THEN p.amount_cents / 100.0 
            ELSE 0 
        END) as pledges_last_30_days,
        COUNT(CASE 
            WHEN p.created_at >= CURRENT_DATE - INTERVAL '7 days' 
            THEN p.pledge_id 
        END) as new_pledges_this_week
    FROM planning_center.giving_pledges p
    GROUP BY p.pledge_campaign_id
)
SELECT 
    cs.campaign_name,
    cs.campaign_goal,
    cs.total_pledged,
    cs.total_received,
    ROUND((cs.total_pledged / NULLIF(cs.campaign_goal, 0)) * 100, 2) as percent_pledged,
    ROUND((cs.total_received / NULLIF(cs.total_pledged, 0)) * 100, 2) as fulfillment_rate,
    cs.campaign_goal - cs.total_received as remaining_to_goal,
    pd.pledge_count,
    pd.pledger_count,
    pd.avg_pledge,
    pd.median_pledge,
    pd.largest_pledge,
    ra.pledges_last_30_days,
    ra.new_pledges_this_week,
    CASE 
        WHEN cs.campaign_end IS NOT NULL THEN
            cs.campaign_end - CURRENT_DATE
        ELSE NULL
    END as days_remaining
FROM campaign_summary cs
LEFT JOIN pledge_details pd ON cs.pledge_campaign_id = pd.pledge_campaign_id
LEFT JOIN recent_activity ra ON cs.pledge_campaign_id = ra.pledge_campaign_id
ORDER BY cs.campaign_goal DESC;

-- Major Donors Report for IRS Form 990
WITH annual_giving AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        p.donor_number,
        SUM(d.amount_cents) / 100.0 as total_given,
        COUNT(d.donation_id) as donation_count,
        MIN(d.received_at) as first_donation,
        MAX(d.received_at) as last_donation
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE EXTRACT(YEAR FROM d.received_at) = EXTRACT(YEAR FROM CURRENT_DATE)
        AND d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name, p.donor_number
    HAVING SUM(d.amount_cents) >= 500000  -- $5,000 in cents
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY total_given DESC) as rank,
    donor_number,
    -- For privacy, only show initials in reports
    SUBSTRING(first_name, 1, 1) || '.' || SUBSTRING(last_name, 1, 1) || '.' as donor_initials,
    total_given,
    donation_count,
    TO_CHAR(first_donation, 'MM/DD/YYYY') as first_gift_date,
    TO_CHAR(last_donation, 'MM/DD/YYYY') as last_gift_date,
    ROUND(total_given * 100.0 / SUM(total_given) OVER (), 2) as percent_of_major_gifts
FROM annual_giving
ORDER BY total_given DESC;

-- Non-Cash Contributions Summary
SELECT 
    DATE_TRUNC('month', received_at) as month,
    COUNT(*) as non_cash_donation_count,
    SUM(amount_cents) / 100.0 as total_non_cash_value,
    STRING_AGG(DISTINCT payment_method, ', ') as contribution_types
FROM planning_center.giving_donations
WHERE payment_method NOT IN ('cash', 'check', 'card', 'ach')
    AND received_at >= DATE_TRUNC('year', CURRENT_DATE)
    AND refunded = false
GROUP BY DATE_TRUNC('month', received_at)
ORDER BY month DESC;

-- 13-Month Rolling Giving Trend
WITH monthly_stats AS (
    SELECT 
        DATE_TRUNC('month', received_at) as month,
        TO_CHAR(received_at, 'Mon YY') as month_label,
        COUNT(DISTINCT d.donation_id) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount,
        AVG(d.amount_cents) / 100.0 as avg_donation
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.received_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '13 months')
        AND d.refunded = false
    GROUP BY DATE_TRUNC('month', received_at), TO_CHAR(received_at, 'Mon YY')
),
with_calculations AS (
    SELECT 
        *,
        LAG(total_amount, 1) OVER (ORDER BY month) as prev_month,
        LAG(total_amount, 12) OVER (ORDER BY month) as same_month_last_year,
        AVG(total_amount) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_month_avg
    FROM monthly_stats
)
SELECT 
    month_label,
    donation_count,
    unique_donors,
    total_amount,
    avg_donation,
    ROUND(((total_amount - prev_month) / NULLIF(prev_month, 0)) * 100, 2) as month_over_month_pct,
    ROUND(((total_amount - same_month_last_year) / NULLIF(same_month_last_year, 0)) * 100, 2) as year_over_year_pct,
    ROUND(three_month_avg, 2) as three_month_rolling_avg
FROM with_calculations
ORDER BY month DESC
LIMIT 13;

-- Donor Retention Cohort Analysis
WITH donor_first_gift AS (
    SELECT 
        dr.relationship_id as person_id,
        DATE_TRUNC('month', MIN(d.received_at)) as cohort_month
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.refunded = false
    GROUP BY dr.relationship_id
),
donor_activity AS (
    SELECT 
        dfg.person_id,
        dfg.cohort_month,
        DATE_TRUNC('month', d.received_at) as activity_month,
        EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', d.received_at), dfg.cohort_month)) * 12 +
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', d.received_at), dfg.cohort_month)) as months_since_first
    FROM donor_first_gift dfg
    JOIN planning_center.giving_donation_relationships dr
        ON dfg.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.refunded = false
),
cohort_retention AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT CASE WHEN months_since_first = 0 THEN person_id END) as month_0,
        COUNT(DISTINCT CASE WHEN months_since_first = 1 THEN person_id END) as month_1,
        COUNT(DISTINCT CASE WHEN months_since_first = 2 THEN person_id END) as month_2,
        COUNT(DISTINCT CASE WHEN months_since_first = 3 THEN person_id END) as month_3,
        COUNT(DISTINCT CASE WHEN months_since_first = 6 THEN person_id END) as month_6,
        COUNT(DISTINCT CASE WHEN months_since_first = 12 THEN person_id END) as month_12
    FROM donor_activity
    WHERE cohort_month >= CURRENT_DATE - INTERVAL '13 months'
    GROUP BY cohort_month
)
SELECT 
    TO_CHAR(cohort_month, 'Mon YYYY') as cohort,
    month_0 as new_donors,
    ROUND(month_1 * 100.0 / NULLIF(month_0, 0), 2) as month_1_retention_pct,
    ROUND(month_2 * 100.0 / NULLIF(month_0, 0), 2) as month_2_retention_pct,
    ROUND(month_3 * 100.0 / NULLIF(month_0, 0), 2) as month_3_retention_pct,
    ROUND(month_6 * 100.0 / NULLIF(month_0, 0), 2) as month_6_retention_pct,
    ROUND(month_12 * 100.0 / NULLIF(month_0, 0), 2) as month_12_retention_pct
FROM cohort_retention
ORDER BY cohort_month DESC;

-- Donor List for Mail Merge Export
SELECT 
    p.donor_number,
    p.first_name,
    p.last_name,
    SUM(d.amount_cents) / 100.0 as total_given_ytd,
    MAX(d.received_at) as last_gift_date,
    COUNT(d.donation_id) as gift_count,
    -- Add any address fields if available in your system
    'Thank you for your generous support!' as merge_message
FROM planning_center.giving_people p
JOIN planning_center.giving_donation_relationships dr
    ON p.person_id = dr.relationship_id
    AND dr.relationship_type = 'Person'
JOIN planning_center.giving_donations d
    ON dr.donation_id = d.donation_id
WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
    AND d.refunded = false
GROUP BY p.person_id, p.donor_number, p.first_name, p.last_name
HAVING SUM(d.amount_cents) > 0
ORDER BY p.last_name, p.first_name;