WITH recurring_gift_summaries AS (
    select
        ("Donation"->'Donor'->>'ConsId')::bigint as constituent_id,
        ("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint as original_transaction_id,
        MAX(("Donation"->'Payment'->>'PaymentDate')::timestamp) as last_payment_date,
        MAX(("Donation"->'RecurringPayment'->>'NumberOfPayments')::bigint) AS number_of_payments
    FROM donations
    WHERE
        UPPER("Donation"->'RecurringPayment'->>'Type') = 'SUSTAINING'
    GROUP BY 
        ("Donation"->'Donor'->>'ConsId')::bigint,
        ("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint
    order by ("Donation"->'RecurringPayment'->>'OriginalTransactionId')::bigint
)

select
    rgs.*,
    c."Constituent"->'ConsName'->>'FirstName' as first_name,
    c."Constituent"->'ConsName'->>'LastName' as last_name,
    c."Constituent"->>'PrimaryEmail' as primary_email,
    (c."Constituent"->>'AcceptsEmail')::boolean as accepts_email,
    c."Constituent"->'HomeAddress'->>'Street1' as home_address_street1,
    c."Constituent"->'HomeAddress'->>'Street2' as home_address_street2,
    c."Constituent"->'HomeAddress'->>'City' as home_address_city,
    c."Constituent"->'HomeAddress'->>'State' as home_address_state,
    c."Constituent"->'HomeAddress'->>'Zip' as home_address_zip
from constituents c 
join recurring_gift_summaries rgs on (c."Constituent"->>'ConsId')::bigint = rgs.constituent_id

WITH simple_donations AS (
SELECT
 (donations."Donation"->'Payment'->>'Amount')::numeric AS donation_amount,
 (donations."Donation"->'Donor'->>'ConsId')::bigint AS constituent_id,
 (donations."Donation"->'Payment'->>'CreationDate')::timestamp without time zone AS donation_date
FROM donations
), simple_constituents AS (
SELECT
 constituents.dmi_id,
 constituents.dmi_status,
 constituents.dmi_deleted,
 constituents."ConsId"::bigint AS consid,
 constituents."Constituent"->>'PrimaryEmail' AS email,
 constituents."Constituent"->'ConsName'->>'LastName' AS lastname,
 constituents."Constituent"->'ConsName'->>'FirstName' AS firstname,
 constituents."Constituent"->'HomeAddress'->>'Zip' AS zipcode,
 constituents."Constituent"->'HomeAddress'->>'City' AS city,
 constituents."Constituent"->'HomeAddress'->>'State' AS state,
 constituents."Constituent"->'HomeAddress'->>'Street1' AS street1,
 constituents."Constituent"->'HomeAddress'->>'Street2' AS street2,
 constituents."Constituent"->>'AcceptsEmail' AS acceptsemail,
 constituents."Constituent"->>'EmailStatus' AS emailstatus
FROM constituents
), donor_giving_summaries AS (
SELECT
SUM((donations."Donation"->'Payment'->>'Amount')::numeric) AS total_amount,
MIN((donations."Donation"->'Payment'->>'PaymentDate')::date) AS first_gift_date,
MAX((donations."Donation"->'Payment'->>'PaymentDate')::date) AS last_gift_date,
 (donations."Donation"->'Donor'->>'ConsId')::bigint AS constituent_id,
MAX((donations."Donation"->'Payment'->>'Amount')::numeric) AS largest_amount
FROM donations
GROUP BY
 (donations."Donation"->'Donor'->>'ConsId')::bigint
), donor_consecutive_giving AS (
SELECT t2.constituent_id,
MAX(t2.yr_cnt) AS max_consecutive_years
FROM (
SELECT
 t.constituent_id,
 t.grp,
COUNT(t.seqnum) AS yr_cnt
FROM (
SELECT
 t_1.constituent_id,
 t_1.donation_date,
 t_1.seqnum,
dense_rank() OVER (
PARTITION BY t_1.constituent_id
ORDER BY (t_1.donation_date + t_1.seqnum) DESC
 ) AS grp
FROM (
SELECT
 t_2.constituent_id,
 t_2.donation_date,
dense_rank() OVER (
PARTITION BY t_2.constituent_id
ORDER BY t_2.donation_date DESC
 ) AS seqnum
FROM (
SELECT
 simple_donations.constituent_id,
 (date_part('year'::text, simple_donations.donation_date))::bigint AS donation_date
FROM simple_donations
GROUP BY
 simple_donations.constituent_id,
 (date_part('year'::text, simple_donations.donation_date))
 ) t_2
 ) t_1
 ) t
GROUP BY
 t.constituent_id,
 t.grp
 ) t2
GROUP BY
 t2.constituent_id
), donors_year_before_last AS (
SELECT
 simple_donations.constituent_id
FROM simple_donations
WHERE
 (date_part('year'::text, simple_donations.donation_date) = (((date_part('year'::text, CURRENT_DATE))::bigint - 2))::double precision)
GROUP BY
 simple_donations.constituent_id
), donors_last_year AS (
SELECT
 simple_donations.constituent_id
FROM simple_donations
WHERE
 (date_part('year'::text, simple_donations.donation_date) = (((date_part('year'::text, CURRENT_DATE))::bigint - 1))::double precision)
GROUP BY
 simple_donations.constituent_id
), donors_this_year AS (
SELECT
 simple_donations.constituent_id
FROM simple_donations simple_donations
WHERE
 (date_part('year'::text, simple_donations.donation_date) = date_part('year'::text, CURRENT_DATE))
GROUP BY
 simple_donations.constituent_id
)
SELECT dgs.total_amount, dgs.first_gift_date, dgs.last_gift_date, dgs.constituent_id, dgs.largest_amount, sc.email, sc.acceptsemail, sc.emailstatus, sc.firstname, sc.lastname, sc.city, sc.state, sc.zipcode, sc.street1, sc.street2, dcg.max_consecutive_years AS consecutive_years, CASE WHEN (dybl.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_year_before_last, CASE WHEN (dly.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_last_year, CASE WHEN (dty.constituent_id IS NOT NULL) THEN true ELSE false END AS gave_this_year, case WHEN (rec.constituent_id IS NOT NULL) THEN true ELSE false END AS sustainer, rec.last_recurring_gift_dateFROM donor_giving_summaries dgsJOIN simple_constituents sc ON dgs.constituent_id = sc.considJOIN donor_consecutive_giving dcg ON dcg.constituent_id = dgs.constituent_idLEFT JOIN donors_year_before_last dybl ON dgs.constituent_id = dybl.constituent_idLEFT JOIN donors_last_year dly ON dgs.constituent_id = dly.constituent_idLEFT JOIN donors_this_year dty ON dgs.constituent_id = dty.constituent_idLEFT JOIN ( SELECT constituent_id, MAX(last_payment_date) as last_recurring_gift_date FROM byt_recurring_donation_summaries GROUP BY constituent_id) AS rec ON dgs.constituent_id = rec.constituent_id