1#!/usr/bin/ruby
 2# frozen_string_literal: true
 3
 4require "bigdecimal"
 5require "dhall"
 6require "pg"
 7
 8PLANS =
 9	Dhall::Coder
10	.new(safe: Dhall::Coder::JSON_LIKE + [Symbol])
11	.load(<<-DHALL, transform_keys: ->(k) { k&.to_sym })
12		let Quota = < unlimited | limited: { included: Natural, price: Natural } >
13		let Currency = < CAD | USD >
14		in
15		(#{ARGV[0]}) : List {
16			name: Text,
17			currency: Currency,
18			monthly_price: Natural,
19			minutes: Quota,
20			messages: Quota
21		}
22DHALL
23
24db = PG.connect(dbname: "jmp")
25db.type_map_for_results = PG::BasicTypeMapForResults.new(db)
26db.type_map_for_queries = PG::BasicTypeMapForQueries.new(db)
27
28QUERY = <<~SQL
29	SELECT code::TEXT, creator_id, plan_name
30	FROM
31		invites
32		INNER JOIN transactions ON used_by_id = transactions.customer_id
33		INNER JOIN customer_plans ON creator_id = customer_plans.customer_id
34	WHERE rewarded_at IS NULL AND used_by_id IS NOT NULL AND amount > 0
35	GROUP BY used_by_id, code, creator_id, plan_name
36	HAVING
37		MIN(transactions.created_at) < LOCALTIMESTAMP - '90 days'::interval
38		AND
39		SUM(transactions.amount) >= 15
40SQL
41
42db.transaction do
43	db.exec(QUERY).each do |row|
44		puts row["code"]
45		plan = PLANS.find { |p| p[:name] == row["plan_name"] }
46		price = BigDecimal(plan[:monthly_price]) / 10000
47		db.exec(<<~SQL, [row["creator_id"], row["code"], price])
48			INSERT INTO transactions
49				(customer_id, transaction_id, settled_after, amount, note)
50			VALUES
51				($1, 'reward_' || $1 || '_for_' || $2, LOCALTIMESTAMP, $3, 'Reward for referral ' || $2)
52		SQL
53		db.exec(<<~SQL, [row["creator_id"]])
54			INSERT INTO invites (creator_id) VALUES ($1)
55		SQL
56		db.exec(<<~SQL, [row["code"]])
57			UPDATE invites
58			SET rewarded_at=LOCALTIMESTAMP
59			WHERE code=$1
60		SQL
61	end
62end