~singpolyma/jmp-pay

ref: 7ee5681369e6212adbb4394678bad9e273ca8625 jmp-pay/bin/process_invite_rewards -rwxr-xr-x 1.7 KiB
7ee56813Stephen Paul Weber Merge branch 'invite-rewards' 8 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#!/usr/bin/ruby
# frozen_string_literal: true

require "bigdecimal"
require "dhall"
require "pg"

PLANS =
	Dhall::Coder
	.new(safe: Dhall::Coder::JSON_LIKE + [Symbol])
	.load(<<-DHALL, transform_keys: ->(k) { k&.to_sym })
		let Quota = < unlimited | limited: { included: Natural, price: Natural } >
		let Currency = < CAD | USD >
		in
		(#{ARGV[0]}) : List {
			name: Text,
			currency: Currency,
			monthly_price: Natural,
			minutes: Quota,
			messages: Quota
		}
DHALL

db = PG.connect(dbname: "jmp")
db.type_map_for_results = PG::BasicTypeMapForResults.new(db)
db.type_map_for_queries = PG::BasicTypeMapForQueries.new(db)

QUERY = <<~SQL
	SELECT code::TEXT, creator_id, plan_name
	FROM
		invites
		INNER JOIN transactions ON used_by_id = transactions.customer_id
		INNER JOIN customer_plans ON creator_id = customer_plans.customer_id
	WHERE rewarded_at IS NULL AND used_by_id IS NOT NULL AND amount > 0
	GROUP BY used_by_id, code, creator_id, plan_name
	HAVING
		MIN(transactions.created_at) < LOCALTIMESTAMP - '90 days'::interval
		AND
		SUM(transactions.amount) >= 15
SQL

db.transaction do
	db.exec(QUERY).each do |row|
		puts row["code"]
		plan = PLANS.find { |p| p[:name] == row["plan_name"] }
		price = BigDecimal(plan[:monthly_price]) / 10000
		db.exec(<<~SQL, [row["creator_id"], row["code"], price])
			INSERT INTO transactions
				(customer_id, transaction_id, amount, note)
			VALUES
				($1, 'reward_' || $1 || '_for_' || $2, $3, 'Reward for referral ' || $2)
		SQL
		db.exec(<<~SQL, [row["creator_id"]])
			INSERT INTO invites (creator_id) VALUES ($1)
		SQL
		db.exec(<<~SQL, [row["code"]])
			UPDATE invites
			SET rewarded_at=LOCALTIMESTAMP
			WHERE code=$1
		SQL
	end
end