Skip to content

Instantly share code, notes, and snippets.

@HOllarves
Created March 24, 2022 13:43
Show Gist options
  • Save HOllarves/78367bfed9ff5a22aa2d2f22b8ee5f71 to your computer and use it in GitHub Desktop.
Save HOllarves/78367bfed9ff5a22aa2d2f22b8ee5f71 to your computer and use it in GitHub Desktop.
/* eslint-disable complexity */
/* eslint-disable node/no-process-exit */
/* eslint-disable @typescript-eslint/naming-convention */
/* eslint-disable no-console */
import type { DatabasePoolConnection } from 'slonik';
import { createPool, sql } from "slonik";
import yargs from "yargs";
import { Logger } from "./Logger";
// import { createSlonikInterceptors } from "./factories";
const getBill = async (billId: number, pool: DatabasePoolConnection) => {
const bill = await pool.maybeOne(sql`
SELECT *
FROM bill
WHERE id = ${billId}
`);
if (!bill) {
return null
}
return bill;
}
(async () => {
const log = Logger.child({
namespace: 'contra-payments',
});
const argv = yargs
.env('CPC')
.help()
.options({
'add-payment-provider': {
demand: false,
type: 'string',
},
'bill-amount': {
demand: false,
type: 'number',
},
'bill-creator-id': {
demand: false,
type: 'string',
},
'bill-currency': {
demand: false,
type: 'string',
},
'bill-payer-id': {
demand: false,
type: 'string',
},
'bill-release-mechanism': {
demand: false,
type: 'string',
},
'create-balance-accounts': {
demand: false,
type: 'number',
},
'create-bill': {
demand: false
},
'execute-bill-id': {
demand: false,
},
'get-balance-id': {
demand: false,
type: 'number',
},
'get-transaction-history-id': {
demand: false,
type: 'number',
},
'pay-bill-id': {
demand: false,
type: 'number',
},
'pay-bill-payment-service-provider': {
demand: false,
type: 'string',
},
'postgres-dsn': {
demand: true,
describe: 'Postgres DSN',
type: 'string',
},
'release-bill-id': {
demand: false,
type: 'number'
}
})
.parse()
const pool = createPool(
argv["postgres-dsn"],
{
captureStackTrace: false,
connectionTimeout: 60 * 1_000,
idleInTransactionSessionTimeout: 'DISABLE_TIMEOUT',
// interceptors: [...createSlonikInterceptors()],
maximumPoolSize: 10,
statementTimeout: 'DISABLE_TIMEOUT',
}
);
const currentBalanceAccounts = await pool.anyFirst(sql`
SELECT id
FROM balance_account
`)
if (currentBalanceAccounts.length === 0 && !argv["create-balance-accounts"]) {
const errorMessage = 'No balance accounts found and no starting balance provided'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
if (argv["create-balance-accounts"]) {
const balanceAccountIds = []
for (let i = 0; i < argv["create-balance-accounts"]; i++) {
const newBalanceAccounts = await pool.anyFirst(sql`
INSERT INTO balance_account
(user_account_id, payment_service_provider_id)
SELECT
${i + 1},
psp.id
FROM payment_service_provider psp
RETURNING
id
`)
balanceAccountIds.push(...newBalanceAccounts)
}
console.log({ balanceAccountIds })
}
if (argv["create-bill"]) {
if (!argv["bill-amount"] || !argv["bill-currency"] || !argv["bill-payer-id"] || !argv["bill-creator-id"] || !argv["bill-release-mechanism"]) {
const errorMessage = 'Missing required arguments to create bill'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
const newBillId = await pool.oneFirst(sql`
INSERT INTO bill
(
amount,
currency_id,
release_mechanism_id,
payer_user_account_id,
creator_user_account_id
)
VALUES
(
${argv["bill-amount"]},
(SELECT c.id FROM currency c WHERE c.code = ${argv["bill-currency"] as string}),
(SELECT rm.id FROM release_mechanism rm WHERE rm.nid = ${argv["bill-release-mechanism"] as string}),
${argv["bill-payer-id"] as string},
${argv["bill-creator-id"] as string}
)
RETURNING
id
`)
console.log('Created Bill ID:', newBillId)
}
if (argv["pay-bill-id"] && argv["pay-bill-payment-service-provider"]) {
const billExists = await pool.exists(sql`
SELECT *
FROM bill b
WHERE
b.id = ${argv["pay-bill-id"] as number}
`)
if (!billExists) {
const errorMessage = 'Bill does not exist'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
const paymentServiceProviderExists = await pool.exists(sql`
SELECT *
FROM payment_service_provider psp
WHERE
psp.name = ${argv["pay-bill-payment-service-provider"] as string}
`)
if (!paymentServiceProviderExists) {
const errorMessage = 'Payment Service Provider does not exist'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
const { id, amount, currency_id, payer_user_account_id, creator_user_account_id } = await pool.one(sql`
SELECT
b.id,
b.amount,
b.currency_id,
b.payer_user_account_id,
b.creator_user_account_id
FROM bill b
WHERE
b.id = ${argv["pay-bill-id"] as number}
`)
const payerBalanceTransaction = await pool.oneFirst(sql`
INSERT INTO balance_transaction
(
balance_account_id,
bill_id,
amount,
currency_id,
detail_record_id,
detail_table_name,
created_at
)
VALUES
(
(SELECT ba.id FROM balance_account ba WHERE ba.user_account_id = ${payer_user_account_id} AND ba.payment_service_provider_id = (SELECT psp.id FROM payment_service_provider psp WHERE psp.name = ${argv["pay-bill-payment-service-provider"] as string})),
${id},
${amount},
${currency_id},
1,
'whater-table-name-or-type',
NOW()
)
RETURNING
id
`
)
const creatorBalanceTransaction = await pool.oneFirst(sql`
INSERT INTO balance_transaction
(
balance_account_id,
bill_id,
amount,
currency_id,
detail_record_id,
detail_table_name,
created_at
)
VALUES
(
(SELECT ba.id FROM balance_account ba WHERE ba.user_account_id = ${creator_user_account_id} AND ba.payment_service_provider_id = (SELECT psp.id FROM payment_service_provider psp WHERE psp.name = ${argv["pay-bill-payment-service-provider"] as string})),
${id},
${amount},
${currency_id},
1,
'whater-table-name-or-type',
NOW()
)
RETURNING
id
`
)
console.log({ creatorBalanceTransaction, payerBalanceTransaction })
}
if (argv["execute-bill-id"]) {
const bill = await getBill(argv["execute-bill-id"] as number, pool)
if (!bill) {
const errorMessage = 'Bill does not exist'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
const { id: billId, payer_user_account_id, creator_user_account_id } = bill
const payerBalanceTransaction = await pool.oneFirst(sql`
UPDATE balance_transaction
SET
processed_at = NOW(),
completed_at = NOW()
WHERE
id = (
SELECT bt.id
FROM balance_transaction bt
INNER JOIN bill b ON b.id = bt.bill_id
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id AND ba.user_account_id = ${payer_user_account_id}
WHERE
b.id = ${billId} AND
b.payer_user_account_id = ${payer_user_account_id}
)
RETURNING
id
`)
const creatorBalanceTransaction = await pool.oneFirst(sql`
UPDATE balance_transaction
SET
processed_at = NOW()
WHERE
id = (
SELECT bt.id
FROM balance_transaction bt
INNER JOIN bill b ON b.id = bt.bill_id
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id AND ba.user_account_id = ${creator_user_account_id}
WHERE
b.id = ${billId} AND
b.creator_user_account_id = ${creator_user_account_id}
)
RETURNING
id
`)
console.log({ creatorBalanceTransaction, payerBalanceTransaction })
}
if (argv["get-balance-account-id"]) {
const income = await pool.oneFirst<number>(sql`
SELECT
coalesce(SUM(bt.amount), 0)
FROM balance_transaction bt
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id
INNER JOIN bill b ON b.id = bt.bill_id
WHERE
b.creator_user_account_id = ${argv["get-balance-account-id"] as number} AND
ba.user_account_id = ${argv["get-balance-account-id"] as number} AND
bt.completed_at IS NOT NULL
`)
const heldIncome = await pool.oneFirst<number>(sql`
SELECT
coalesce(SUM(bt.amount), 0)
FROM balance_transaction bt
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id
INNER JOIN bill b ON b.id = bt.bill_id
WHERE
b.creator_user_account_id = ${argv["get-balance-account-id"] as number} AND
ba.user_account_id = ${argv["get-balance-account-id"] as number} AND
bt.completed_at IS NULL
`)
const expenses = await pool.oneFirst<number>(sql`
SELECT
coalesce(SUM(bt.amount), 0)
FROM balance_transaction bt
INNER JOIN bill b ON b.id = bt.bill_id
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id
WHERE
b.payer_user_account_id = ${argv["get-balance-account-id"] as number} AND
ba.user_account_id = ${argv["get-balance-account-id"] as number} AND
bt.completed_at IS NOT NULL
`)
console.log({ balance: Math.max(income - expenses, 0), expenses, heldIncome, income })
}
if (argv["get-transaction-history-account-id"]) {
const transactions = await pool.any(sql`
SELECT
CASE
WHEN b.creator_user_account_id = ${argv["get-transaction-history-account-id"] as number} THEN 'income'
ELSE 'expense'
END as type,
coalesce(bt.completed_at IS NOT NULL, false) available,
bt.id,
bt.amount,
bt.currency_id,
bt.created_at,
bt.processed_at,
bt.completed_at,
bt.detail_record_id,
bt.detail_table_name,
bt.bill_id,
bt.balance_account_id
FROM balance_transaction bt
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id
INNER JOIN bill b ON b.id = bt.bill_id
WHERE
ba.user_account_id = ${argv["get-transaction-history-account-id"] as number}
`)
console.log({ transactions })
}
if (argv['release-bill-id']) {
const bill = await getBill(argv["release-bill-id"] as number, pool)
if (!bill) {
const errorMessage = 'Bill does not exist'
const error = new Error(errorMessage)
log.error(
{
error: errorMessage,
},
errorMessage
);
throw error;
}
const { id: billId, creator_user_account_id } = bill
const creatorBalanceTransaction = await pool.oneFirst(sql`
UPDATE balance_transaction
SET completed_at = NOW()
WHERE
id = (
SELECT bt.id
FROM balance_transaction bt
INNER JOIN balance_account ba ON ba.id = bt.balance_account_id AND ba.user_account_id = ${creator_user_account_id}
INNER JOIN bill b ON b.id = bt.bill_id
WHERE
b.id = ${billId} AND
b.creator_user_account_id = ${creator_user_account_id}
)
RETURNING
id
`)
console.log({ creatorBalanceTransaction })
}
console.log('currentBalanceAccounts', currentBalanceAccounts)
process.exit(0)
})();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment