contra-payments-cli.ts

/* 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)
})();