micw/server/repository/transaction_repo.go

package repository

import (
	"context"
	"errors"
	pgUtils "git.mic.pp.ua/anderson/nettools/pg"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	e "mic-wallet/server/repository/entities"
	"time"
)

const (
	// GetTrxSql parameters:
	// 1 // tx hash (string);
	GetTrxSql = `SELECT * FROM transactions WHERE tx_hash = $1`
	// GetLastTrxHashSql has no parameters
	GetLastTrxHashSql = `SELECT tx_hash FROM transactions ORDER BY approved_at DESC LIMIT 1`
	// GetUserIncomingTrxsSql parameters:
	// 1 // user public key (string); 2 // order (ACL/DESC); 3 // limit (int); 4 // offset (int);
	GetUserIncomingTrxsSql = `SELECT * FROM transactions WHERE receiver_public_key = $1 
		ORDER BY approved_at $2 LIMIT $3 OFFSET $4`
	// GetUserOutcomingTrxsSql parameters:
	// 1 // user public key (string); 2 // order (ACL/DESC); 3 // limit (int); 4 // offset (int);
	GetUserOutcomingTrxsSql = `SELECT * FROM transactions WHERE sender_public_key = $1 
		ORDER BY approved_at $2 LIMIT $3 OFFSET $4`
	// GetUserTrxsSql parameters:
	// 1 // user public key (string); 2 // order (ACL/DESC); 3 // limit (int); 4 // offset (int);
	GetUserTrxsSql = `SELECT * FROM transactions WHERE sender_public_key = $1 
		ORDER BY approved_at $2 LIMIT $3 OFFSET $4`
	// GetTrxsSql parameters:
	// 1 // order (ACL/DESC); 2 // limit (int); 3 // offset (int);
	GetTrxsSql = `SELECT * FROM transactions ORDER BY approved_at $1 LIMIT $2 OFFSET $3`

	AddTrxSql = `INSERT INTO transactions (
		hash, block_id, sender_public_key, receiver_public_key, is_reward,
		amount, amount_burned, message, signature, created_at, added_at
	) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`
)

type TransactionRepository struct {
	ITransactionsRepository
	DB *pgxpool.Pool
}

func ScanTxRow(row pgx.Row, tx *e.Transaction) (err error) {
	return row.Scan(&tx.Hash, &tx.BlockId, &tx.SenderPublicKey, &tx.ReceiverPublicKey,
		&tx.IsReward, &tx.Amount, &tx.AmountBurned, &tx.Message, &tx.Signature, &tx.CreatedAt, &tx.AddedAt)
}

func (repo TransactionRepository) GetLastTransactionHash(ctx context.Context) ([]byte, error) {
	lastTxHash := make([]byte, 64)
	err := repo.DB.QueryRow(ctx, GetLastTrxHashSql).Scan(&lastTxHash)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	return lastTxHash, err
}

// AddTransaction may be not secure in multi-threading
// because new transaction depends on a previous one.
// Consider using with a transaction queue or a lock
func (repo TransactionRepository) AddTransaction(ctx context.Context, opts e.NewTransactionOpts) (transaction *e.Transaction, err error) {
	_, err = repo.DB.Exec(ctx, AddTrxSql,
		opts.Hash, opts.BlockId, opts.ReceiverPublicKey, opts.ReceiverPublicKey,
		opts.IsReward, opts.Amount, opts.AmountBurned, opts.Message, opts.Signature, opts.CreatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &e.Transaction{
		Hash:              opts.Hash,
		BlockId:           opts.BlockId,
		SenderPublicKey:   opts.ReceiverPublicKey,
		ReceiverPublicKey: opts.ReceiverPublicKey,
		IsReward:          opts.IsReward,
		Amount:            opts.Amount,
		AmountBurned:      opts.AmountBurned,
		Message:           opts.Message,
		Signature:         opts.Signature,
		CreatedAt:         opts.CreatedAt,
		AddedAt:           time.Now(),
	}, err
}

func (repo TransactionRepository) GetTransaction(ctx context.Context, txID int64) (tx e.Transaction, err error) {
	return tx, ScanTxRow(repo.DB.QueryRow(ctx, GetTrxSql, txID), &tx)
}
func (repo TransactionRepository) GetUserIncomingTransactions(
	ctx context.Context, userPubKey string, orderAsc bool, limit int, offset int) ([]e.Transaction, error) {
	return pgUtils.Query[e.Transaction](
		ctx, repo.DB, GetUserIncomingTrxsSql, userPubKey, pgUtils.SqlOrder(orderAsc), limit, offset)
}
func (repo TransactionRepository) GetUserOutcomingTransactions(
	ctx context.Context, userPubKey string, orderAsc bool, limit int, offset int) ([]e.Transaction, error) {
	return pgUtils.Query[e.Transaction](
		ctx, repo.DB, GetUserOutcomingTrxsSql, userPubKey, pgUtils.SqlOrder(orderAsc), limit, offset)
}
func (repo TransactionRepository) GetUserTransactions(
	ctx context.Context, userPubKey string, orderAsc bool, limit int, offset int) ([]e.Transaction, error) {
	return pgUtils.Query[e.Transaction](
		ctx, repo.DB, GetUserTrxsSql, userPubKey, pgUtils.SqlOrder(orderAsc), limit, offset)
}
func (repo TransactionRepository) GetTransactions(
	ctx context.Context, orderAsc bool, limit int, offset int) ([]e.Transaction, error) {
	return pgUtils.Query[e.Transaction](ctx, repo.DB, GetTrxsSql, pgUtils.SqlOrder(orderAsc), limit, offset)
}