pretium-ramp/queries.sql at master · grassrootseconomics/pretium-ramp · GitHub

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- Non-custodial link queries

--name: insert-non-custodial-link
-- $1: public_key
-- $2: phone_number
INSERT INTO non_custodial_link(
    public_key,
    phone_number
) VALUES($1, $2) ON CONFLICT DO NOTHING;

--name: get-non-custodial-link-by-public-key
-- $1: public_key
SELECT * FROM non_custodial_link WHERE public_key = $1 AND active = true;

--name: get-non-custodial-link-by-phone
-- $1: phone_number
SELECT * FROM non_custodial_link WHERE phone_number = $1 AND active = true;

--name: get-non-custodial-links-by-phone
-- $1: phone_number
SELECT * FROM non_custodial_link WHERE phone_number = $1 AND active = true ORDER BY created_at DESC;

--name: deactivate-non-custodial-link
-- $1: phone_number
UPDATE non_custodial_link SET active = false WHERE phone_number = $1;


-- Offramp queries

--name: insert-offramp
-- $1: pretium_id
-- $2: phone_number
-- $3: amount_usd
-- $4: amount_kes
-- $5: tx_hash
-- $6: token_address
INSERT INTO offramp(
    pretium_id,
    phone_number,
    amount_usd,
    amount_kes,
    tx_hash,
    token_address
) VALUES($1, $2, $3, $4, $5, $6);

--name: get-offramp-by-pretium-id
-- $1: pretium_id
SELECT * FROM offramp WHERE pretium_id = $1;

--name: get-offramp-by-tx-hash
-- $1: tx_hash
SELECT * FROM offramp WHERE tx_hash = $1;

--name: get-offramp-by-phone
-- $1: phone_number
SELECT * FROM offramp WHERE phone_number = $1 ORDER BY created_at DESC;

--name: update-offramp-status
-- $1: pretium_status
-- $2: pretium_id
UPDATE offramp SET pretium_status = $1 WHERE pretium_id = $2;

--name: update-offramp-mpesa-confirmation
-- $1: mpesa_confirmation
-- $2: pretium_status
-- $3: pretium_id
UPDATE offramp SET mpesa_confirmation = $1, pretium_status = $2 WHERE pretium_id = $3;

--name: get-stale-offramps
SELECT * FROM offramp
WHERE mpesa_confirmation IS NULL
  AND created_at < NOW() - INTERVAL '1 minute'
ORDER BY created_at ASC
LIMIT 100;

--name: get-recent-offramps
SELECT * FROM offramp
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

--name: insert-onramp
-- $1: pretium_id
-- $2: phone_number
-- $3: amount_usd
-- $4: amount_kes
-- $5: tx_hash
-- $6: token_address
INSERT INTO onramp(
    pretium_id,
    phone_number,
    amount_usd,
    amount_kes,
    tx_hash,
    token_address
) VALUES($1, $2, $3, $4, $5, $6);

--name: get-onramp-by-pretium-id
-- $1: pretium_id
SELECT * FROM onramp WHERE pretium_id = $1;

--name: get-onramp-by-tx-hash
-- $1: tx_hash
SELECT * FROM onramp WHERE tx_hash = $1;

--name: get-onramp-by-phone
-- $1: phone_number
SELECT * FROM onramp WHERE phone_number = $1 ORDER BY created_at DESC;

--name: update-onramp-status
-- $1: pretium_status
-- $2: pretium_id
UPDATE onramp SET pretium_status = $1 WHERE pretium_id = $2;

--name: update-onramp-mpesa-confirmation
-- $1: mpesa_confirmation
-- $2: pretium_status
-- $3: pretium_id
UPDATE onramp SET mpesa_confirmation = $1, pretium_status = $2 WHERE pretium_id = $3;

--name: get-stale-onramps
SELECT * FROM onramp
WHERE mpesa_confirmation IS NULL
  AND created_at < NOW() - INTERVAL '1 minute'
ORDER BY created_at ASC
LIMIT 100;

--name: get-recent-onramps
SELECT * FROM onramp
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;