00_wmt_stale_tips_payment_transactions.sql

select distinct
	pt.id,
	pt.gig_id,
	pt.amount,
	pt.status,
	pt.created_at
from payment_transactions pt
join gigs g on g.id = pt.gig_id 
join gig_tip_logs pre_tip_log on pre_tip_log.gig_id = g.id and pre_tip_log.operation = 0
left join gig_tip_logs tip_confirmed_log on tip_confirmed_log.gig_id = g.id and tip_confirmed_log.operation = 1
where 1=1
	and status = 'held'
	and g.profile_id = 409778
	and pt.created_at > '2021-09-28'
	and g.state = 'delivered'
	and pt.created_at < now() - interval '82 hours'
	and pt.category = 'net_tip'
	and tip_confirmed_log.id is null
order by pt.created_at asc

;

01_wmt_stale_tips_profiles.sql

select distinct
	pt.profile_id,
	sum(pt.amount) as tips_sum,
	string_agg(g.id::text, ', ') as gigs,
	count(pt.id) as tips_count,
	max(ge.created_at) as latest_delivery_at
from payment_transactions pt
join gigs g on g.id = pt.gig_id
join gig_events ge on ge.gig_id = g.id and ge.event_type = 12
join gig_line_items net_tip on net_tip.id = pt.gig_line_item_id and net_tip.category = 8
join gig_tip_logs pre_tip_log on pre_tip_log.gig_id = g.id and pre_tip_log.operation = 0
left join gig_tip_logs tip_confirmed_log on tip_confirmed_log.gig_id = g.id and tip_confirmed_log.operation = 1
where 1=1
	and tip_confirmed_log.id is null
	and pt.status = 'held'
	and g.profile_id = 409778
	and pt.created_at > '2021-09-28'
	and pt.created_at < now() - interval '82 hours'
	and pt.category = 'net_tip'
	and g.state = 'delivered'
group by pt.profile_id
order by pt.profile_id asc

;

02_helper_methods.rb

# Paste these into console
# Change `#create` to `#build` for dry runs in *2* locations

def create_instant_pay_credit_line_item(gig)
  gig.line_items.create(category: :promotional_credit, amount: 2, description: "Instant pay credit.")
end

def create_instant_pay_credit_transaction(gig, payment_account, gli)
  gig.payment_transactions.create(
      payment_account: payment_account,
      subject: gig,
      profile_id: payment_account.profile_id,
      gig_line_item: gli,
      category: gli.category,
      amount: gli.amount,
      description: 'Credit',
      transaction_type: :credit,
      itemization: {
        taxable_amount: gli.amount,
        nontaxable_amount: 0
      }
  )
end

03_load_data.rb

# accumulators and data to operate on
# generally speaking, paste this only once

pt_ids = [ 0, 0, 0 ] ; pt_ids.count # replace with real values
profile_ids = [ 0, 0, 0 ] ; profile_ids.count # replace with real values

pts_processed = []
pts_unprocessed = []

profiles_processed = []
profiles_unprocessed = []

profiles = Profile.where(id: profile_ids).order(:id); profiles.count

04_send_payments.rb

# send payments
# comment 2 indicated lines below for dry run
# change the `limit` as you see fit on the next line
profiles.limit(1).find_each do |p|
  if profiles_processed.include?(p.id)
    puts "Skipping already-processed profile #{p.id}"
    next
  end

  pts = p.transactions.where(id: pt_ids, status: :held)
  puts "Total tip transactions to be paid to profile #{p.id}: #{pts.count}"

  if pts.count == 0
    puts "Skipping profile #{p.id} - no payment_transactions found"
    profiles_unprocessed << p.id
    next
  end

  latest_pt = pts.order(id: :asc).last
  latest_gig = latest_pt.gig

  transactions_paid_count = 0

  pts.each do |pt|
    if pt.reload.status != 'held'
      puts "Skipping payment transaction #{pt.id} because status is NOT HELD."
      pts_unprocessed << pt.id

    elsif pt.reload.category != 'net_tip'
      puts "Skipping payment transaction #{pt.id} because category is NOT NET_TIP."
      pts_unprocessed << pt.id

    elsif pt.gig_line_item.amount != pt.amount
      puts "Skipping payment transaction #{pt.id} because AMT DOES NOT MATCH LINE ITEM AMT."
      pts_unprocessed << pt.id

    else
      # comment below for dry run
      V2::GigTipDisbursement.new(pt).process
      transactions_paid_count += 1
      pts_processed << pt.id
      gtl = GigTipLog.create(gig_id: pt.gig_id, operation: :manually_processed)
      puts "Tip log recorded for gig: #{pt.gig_id}."
    end
  end

  puts "Total transactions paid to driver #{p.id}: #{transactions_paid_count}."

  profiles_processed << p.id

  next unless transactions_paid_count > 0

  ip_gli = create_instant_pay_credit_line_item(latest_gig)
  ip_payment_transaction = create_instant_pay_credit_transaction(latest_gig, latest_pt.payment_account, ip_gli)

  # comment below for dry run
  PaymentEvent.send(ip_payment_transaction)

  puts "Instant pay credit submitted to driver #{p.id}."
end