Refactor DB to remove restrictive query system

1 files changed, 60 insertions, 39 deletions

diff --git a/backend/internal/database/stocks.go b/backend/internal/database/stocks.go
index f74e4e8..865aec4 100644
--- a/backend/internal/database/stocks.go
+++ b/backend/internal/database/stocks.go
@@ -14,23 +14,15 @@ import (
 
 var ErrStockNotFound = errors.New("stock not found")
 
-type StockStore interface {
-	GetStock(ctx context.Context, symbol string) (Stock, error)
-	AddStock(ctx context.Context, stock Stock) error
-	AddRanking(ctx context.Context, symbol string, ibd50, cap20 int) error
-	AddStockInfo(ctx context.Context, info *StockInfo) (string, error)
-	GetStockInfo(ctx context.Context, id string) (*StockInfo, error)
-	AddAnalysis(ctx context.Context, ratingId string, analysis *analyzer.Analysis) error
-}
-
-func (d *database) GetStock(ctx context.Context, symbol string) (Stock, error) {
-	row, err := d.queryRow(ctx, d.db, "stocks/get_stock", symbol)
-	if err != nil {
-		return Stock{}, err
-	}
+func GetStock(ctx context.Context, exec Executor, symbol string) (Stock, error) {
+	row := exec.QueryRowContext(ctx, `
+SELECT symbol, name, ibd_url
+FROM stocks
+WHERE symbol = $1;
+`, symbol)
 
 	var stock Stock
-	if err = row.Scan(&stock.Symbol, &stock.Name, &stock.IBDUrl); err != nil {
+	if err := row.Scan(&stock.Symbol, &stock.Name, &stock.IBDUrl); err != nil {
 		if errors.Is(err, sql.ErrNoRows) {
 			return Stock{}, ErrStockNotFound
 		}
@@ -40,20 +32,29 @@ func (d *database) GetStock(ctx context.Context, symbol string) (Stock, error) {
 	return stock, nil
 }
 
-func (d *database) AddStock(ctx context.Context, stock Stock) error {
-	_, err := d.exec(ctx, d.db, "stocks/add_stock", stock.Symbol, stock.Name, stock.IBDUrl)
+func AddStock(ctx context.Context, exec Executor, stock Stock) error {
+	_, err := exec.ExecContext(ctx, `
+INSERT INTO stocks (symbol, name, ibd_url)
+VALUES ($1, $2, $3)
+ON CONFLICT (symbol)
+    DO UPDATE SET name    = $2,
+                  ibd_url = $3;`, stock.Symbol, stock.Name, stock.IBDUrl)
 	return err
 }
 
-func (d *database) AddRanking(ctx context.Context, symbol string, ibd50, cap20 int) error {
+func AddRanking(ctx context.Context, exec Executor, symbol string, ibd50, cap20 int) error {
 	if ibd50 > 0 {
-		_, err := d.exec(ctx, d.db, "stocks/add_rank", symbol, "ibd50", ibd50)
+		_, err := exec.ExecContext(ctx, `
+INSERT INTO stock_rank (symbol, rank_type, rank)
+VALUES ($1, $2, $3)`, symbol, "ibd50", ibd50)
 		if err != nil {
 			return err
 		}
 	}
 	if cap20 > 0 {
-		_, err := d.exec(ctx, d.db, "stocks/add_rank", symbol, "cap20", cap20)
+		_, err := exec.ExecContext(ctx, `
+INSERT INTO stock_rank (symbol, rank_type, rank)
+VALUES ($1, $2, $3)`, symbol, "cap20", cap20)
 		if err != nil {
 			return err
 		}
@@ -61,8 +62,8 @@ func (d *database) AddRanking(ctx context.Context, symbol string, ibd50, cap20 i
 	return nil
 }
 
-func (d *database) AddStockInfo(ctx context.Context, info *StockInfo) (string, error) {
-	tx, err := d.db.BeginTx(ctx, nil)
+func AddStockInfo(ctx context.Context, exec TransactionExecutor, info *StockInfo) (string, error) {
+	tx, err := exec.BeginTx(ctx, nil)
 	if err != nil {
 		return "", err
 	}
@@ -71,10 +72,10 @@ func (d *database) AddStockInfo(ctx context.Context, info *StockInfo) (string, e
 	}(tx)
 
 	// Add raw chart analysis
-	row, err := d.queryRow(ctx, tx, "stocks/add_raw_chart_analysis", info.ChartAnalysis)
-	if err != nil {
-		return "", err
-	}
+	row := tx.QueryRowContext(ctx, `
+INSERT INTO chart_analysis (raw_analysis)
+VALUES ($1)
+RETURNING id;`, info.ChartAnalysis)
 
 	var chartAnalysisID string
 	if err = row.Scan(&chartAnalysisID); err != nil {
@@ -82,8 +83,11 @@ func (d *database) AddStockInfo(ctx context.Context, info *StockInfo) (string, e
 	}
 
 	// Add stock info
-	row, err = d.queryRow(ctx, tx,
-		"stocks/add_rating",
+	row = tx.QueryRowContext(ctx,
+		`
+INSERT INTO ratings (symbol, composite, eps, rel_str, group_rel_str, smr, acc_dis, chart_analysis, price)
+VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
+RETURNING id;`,
 		info.Symbol,
 		info.Ratings.Composite,
 		info.Ratings.EPS,
@@ -94,9 +98,6 @@ func (d *database) AddStockInfo(ctx context.Context, info *StockInfo) (string, e
 		chartAnalysisID,
 		info.Price.Display(),
 	)
-	if err != nil {
-		return "", err
-	}
 
 	var ratingsID string
 	if err = row.Scan(&ratingsID); err != nil {
@@ -106,15 +107,26 @@ func (d *database) AddStockInfo(ctx context.Context, info *StockInfo) (string, e
 	return ratingsID, tx.Commit()
 }
 
-func (d *database) GetStockInfo(ctx context.Context, id string) (*StockInfo, error) {
-	row, err := d.queryRow(ctx, d.db, "stocks/get_stock_info", id)
-	if err != nil {
-		return nil, err
-	}
+func GetStockInfo(ctx context.Context, exec Executor, id string) (*StockInfo, error) {
+	row := exec.QueryRowContext(ctx, `
+SELECT r.symbol,
+       s.name,
+       ca.raw_analysis,
+       r.composite,
+       r.eps,
+       r.rel_str,
+       r.group_rel_str,
+       r.smr,
+       r.acc_dis,
+       r.price
+FROM ratings r
+         INNER JOIN stocks s on r.symbol = s.symbol
+         INNER JOIN chart_analysis ca on r.chart_analysis = ca.id
+WHERE r.id = $1;`, id)
 
 	var info StockInfo
 	var priceStr string
-	err = row.Scan(
+	err := row.Scan(
 		&info.Symbol,
 		&info.Name,
 		&info.ChartAnalysis,
@@ -138,8 +150,17 @@ func (d *database) GetStockInfo(ctx context.Context, id string) (*StockInfo, err
 	return &info, nil
 }
 
-func (d *database) AddAnalysis(ctx context.Context, ratingId string, analysis *analyzer.Analysis) error {
-	_, err := d.exec(ctx, d.db, "stocks/add_analysis",
+func AddAnalysis(ctx context.Context, exec Executor, ratingId string, analysis *analyzer.Analysis) error {
+	_, err := exec.ExecContext(ctx, `
+UPDATE chart_analysis ca
+SET processed  = true,
+    action     = $2,
+    price      = $3,
+    reason     = $4,
+    confidence = $5
+FROM ratings r
+WHERE r.id = $1
+  AND r.chart_analysis = ca.id;`,
 		ratingId,
 		analysis.Action,
 		analysis.Price.Display(),