datastore/internal/dal/impl.go

package dal

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	"git.rosemyrtle.work/personal-finance/server/internal/entity"
	"github.com/doug-martin/goqu/v9"
	"github.com/shopspring/decimal"
)

type DalImpl struct {
	Db      *sql.DB
	Dialect goqu.DialectWrapper
}

// Table aliases
var (
	schema = goqu.S("pfbudget")
	t      = schema.Table("transactions")
	tc     = schema.Table("transactions_categorized")
	b      = schema.Table("banks")
	bn     = schema.Table("banks_nordigen")
	c      = schema.Table("categories")
)

func (dal *DalImpl) Transaction(transactionId int64) (*entity.Transaction, error) {
	log.Printf("DAL::Transaction(%v)", transactionId)

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(
		t.Col("id"),
		t.Col("date"),
		t.Col("description"),
		t.Col("amount"),
		tc.Col("name"),
	).
		From(t).
		LeftJoin(tc, goqu.On(t.Col("id").Eq(tc.Col("id")))).
		Where(t.Col("id").Eq(transactionId))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		log.Panic(err)
	}

	rows, err := dal.Db.Query(sqlStr, args...)
	if err != nil {
		return nil, err
	}

	transactions := convert[entity.Transaction](rows)
	if len(transactions) == 0 {
		return nil, nil
	}

	return &transactions[0], nil
}

func (dal *DalImpl) Transactions(limit, offset int, description *string, category *string) (entity.Transactions, uint, error) {
	log.Print("DAL::Transactions", "limit", limit, "offset", offset, "description", description, "category", category)

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(
		t.Col("id"),
		t.Col("date"),
		t.Col("description"),
		t.Col("amount"),
		tc.Col("name"),
		goqu.L("count(*) OVER() AS total_count"),
	).
		From(t).
		LeftJoin(tc, goqu.On(t.Col("id").Eq(tc.Col("id")))).
		Order(t.Col("date").Desc()).
		Limit(uint(limit)).
		Offset(uint(offset))

	if description != nil {
		ds = ds.Where(t.Col("description").ILike("%" + *description + "%"))
	}

	if category != nil {
		ds = ds.Where(tc.Col("name").ILike("%" + *category + "%"))
	}

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return nil, 0, err
	}

	rows, err := dal.Db.Query(sqlStr, args...)
	if err != nil {
		return nil, 0, err
	}

	type TransactionWithCount struct {
		Id          entity.TransactionId
		Date        time.Time
		Description string
		Value       decimal.Decimal
		Category    *entity.CategoryName
		TotalCount  uint
	}

	transactions_with_count := convert[TransactionWithCount](rows)
	if len(transactions_with_count) == 0 {
		return nil, 0, nil
	}

	var ans entity.Transactions
	for _, twc := range transactions_with_count {
		tx := entity.Transaction{Id: twc.Id, Date: twc.Date, Description: twc.Description, Value: twc.Value, Category: twc.Category}
		ans = append(ans, tx)
	}
	return ans, transactions_with_count[0].TotalCount, nil
}

func (dal *DalImpl) InsertTransaction(tx entity.Transaction) (entity.Transaction, error) {
	log.Print("DAL::InsertTransaction")

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Insert(t).Rows(
		goqu.Record{
			"date":        tx.Date,
			"description": tx.Description,
			"amount":      tx.Value,
		}).
		Returning(t.Col("id"))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return entity.Transaction{}, err
	}

	id := new(uint64)
	if err := dal.Db.QueryRow(sqlStr, args...).Scan(id); err != nil {
		return entity.Transaction{}, err
	}

	tx.Id = *id
	return tx, nil
}

func (dal *DalImpl) UpdateTransaction(id entity.TransactionId, category *entity.CategoryName) (bool, error) {
	log.Print("DAL::UpdateTransaction")

	if dal.Db == nil {
		log.Panic("database not available")
	}

	if category == nil {
		return false, errors.New("missing category")
	}

	// TODO(43): implement upsert logic
	ds := dal.Dialect.Update(tc).
		Set(goqu.Record{"name": *category}).
		Where(tc.Col("id").Eq(id))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return false, err
	}

	result, err := dal.Db.Exec(sqlStr, args...)
	if err != nil {
		return false, err
	}

	nAffected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}

	// TODO: find if this value can be different than 1, otherwise the func return can only be error
	if nAffected != 1 {
		return false, fmt.Errorf("%d rows affected", nAffected)
	}

	return true, nil
}

func (dal *DalImpl) TransactionExists(id uint64) (bool, error) {
	log.Print("DAL::TransactionExists")

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(goqu.L("EXISTS(SELECT 1 FROM pfbudget.transactions WHERE id = ?)", id))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return false, err
	}

	exists := new(bool)
	if err := dal.Db.QueryRow(sqlStr, args...).Scan(exists); err != nil {
		return false, err
	}

	return *exists, nil
}

func (dal *DalImpl) Bank(bankId string) (*entity.Bank, error) {
	log.Printf("DAL::Bank(%v)", bankId)

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(
		b.Col("name"),
		b.Col("name"),
		bn.Col("requisition_id"),
	).
		From(b).
		Join(bn, goqu.On(b.Col("name").Eq(bn.Col("name")))).
		Where(b.Col("name").Eq(bankId))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return nil, err
	}

	rows, err := dal.Db.Query(sqlStr, args...)
	if err != nil {
		return nil, err
	}

	banks := convert[entity.Bank](rows)
	if len(banks) == 0 {
		return nil, nil
	}

	return &banks[0], nil
}

func (dal *DalImpl) Banks() (entity.Banks, error) {
	log.Print("DAL::Banks")

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(
		b.Col("name"),
		b.Col("name"),
		bn.Col("requisition_id"),
	).
		From(b).
		Join(bn, goqu.On(b.Col("name").Eq(bn.Col("name"))))

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return entity.Banks{}, err
	}

	rows, err := dal.Db.Query(sqlStr, args...)
	if err != nil {
		return entity.Banks{}, err
	}

	return convert[entity.Bank](rows), nil
}

func (dal *DalImpl) Categories() (entity.Categories, error) {
	log.Print("DAL::Categories")

	if dal.Db == nil {
		log.Panic("database not available")
	}

	ds := dal.Dialect.Select(
		c.Col("name"),
		c.Col("group"),
	).
		From(c)

	sqlStr, args, err := ds.Prepared(true).ToSQL()
	if err != nil {
		return []entity.Category{}, err
	}

	rows, err := dal.Db.Query(sqlStr, args...)
	if err != nil {
		return []entity.Category{}, err
	}

	return convert[entity.Category](rows), nil
}