package database import ( "context" "database/sql" "errors" "fmt" "github.com/ansg191/ibd-trader-backend/internal/analyzer" "github.com/ansg191/ibd-trader-backend/internal/utils" "github.com/Rhymond/go-money" ) var ErrStockNotFound = errors.New("stock not found") 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 errors.Is(err, sql.ErrNoRows) { return Stock{}, ErrStockNotFound } return Stock{}, err } return stock, nil } 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 AddRanking(ctx context.Context, exec Executor, symbol string, ibd50, cap20 int) error { if ibd50 > 0 { _, 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 := exec.ExecContext(ctx, ` INSERT INTO stock_rank (symbol, rank_type, rank) VALUES ($1, $2, $3)`, symbol, "cap20", cap20) if err != nil { return err } } return nil } func AddStockInfo(ctx context.Context, exec TransactionExecutor, info *StockInfo) (string, error) { tx, err := exec.BeginTx(ctx, nil) if err != nil { return "", err } defer func(tx *sql.Tx) { _ = tx.Rollback() }(tx) // Add raw chart analysis 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 { return "", err } // Add stock info 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, info.Ratings.RelStr, info.Ratings.GroupRelStr, info.Ratings.SMR, info.Ratings.AccDis, chartAnalysisID, info.Price.Display(), ) var ratingsID string if err = row.Scan(&ratingsID); err != nil { return "", err } return ratingsID, tx.Commit() } 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( &info.Symbol, &info.Name, &info.ChartAnalysis, &info.Ratings.Composite, &info.Ratings.EPS, &info.Ratings.RelStr, &info.Ratings.GroupRelStr, &info.Ratings.SMR, &info.Ratings.AccDis, &priceStr, ) if err != nil { return nil, err } info.Price, err = utils.ParseMoney(priceStr) if err != nil { return nil, err } return &info, nil } 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(), analysis.Reason, analysis.Confidence, ) return err } type Stock struct { Symbol string Name string IBDUrl string } type StockInfo struct { Symbol string Name string ChartAnalysis string Ratings Ratings Price *money.Money } type Ratings struct { Composite uint8 EPS uint8 RelStr uint8 GroupRelStr LetterRating SMR LetterRating AccDis LetterRating } type LetterRating uint8 const ( LetterRatingE LetterRating = iota LetterRatingEPlus LetterRatingDMinus LetterRatingD LetterRatingDPlus LetterRatingCMinus LetterRatingC LetterRatingCPlus LetterRatingBMinus LetterRatingB LetterRatingBPlus LetterRatingAMinus LetterRatingA LetterRatingAPlus ) func (r LetterRating) String() string { switch r { case LetterRatingE: return "E" case LetterRatingEPlus: return "E+" case LetterRatingDMinus: return "D-" case LetterRatingD: return "D" case LetterRatingDPlus: return "D+" case LetterRatingCMinus: return "C-" case LetterRatingC: return "C" case LetterRatingCPlus: return "C+" case LetterRatingBMinus: return "B-" case LetterRatingB: return "B" case LetterRatingBPlus: return "B+" case LetterRatingAMinus: return "A-" case LetterRatingA: return "A" case LetterRatingAPlus: return "A+" default: return "Unknown" } } func LetterRatingFromString(str string) (LetterRating, error) { switch str { case "N/A": fallthrough case "E": return LetterRatingE, nil case "E+": return LetterRatingEPlus, nil case "D-": return LetterRatingDMinus, nil case "D": return LetterRatingD, nil case "D+": return LetterRatingDPlus, nil case "C-": return LetterRatingCMinus, nil case "C": return LetterRatingC, nil case "C+": return LetterRatingCPlus, nil case "B-": return LetterRatingBMinus, nil case "B": return LetterRatingB, nil case "B+": return LetterRatingBPlus, nil case "A-": return LetterRatingAMinus, nil case "A": return LetterRatingA, nil case "A+": return LetterRatingAPlus, nil default: return 0, fmt.Errorf("unknown rating: %s", str) } }