sentinela-go/internal/db/market.go

package db

import (
	"database/sql"
	"fmt"
)

type SelicRecord struct {
	ID         int64    `json:"id"`
	Date       string   `json:"date"`
	DailyRate  float64  `json:"daily_rate"`
	AnnualRate *float64 `json:"annual_rate,omitempty"`
	TargetRate *float64 `json:"target_rate,omitempty"`
}

type CDIRecord struct {
	ID         int64    `json:"id"`
	Date       string   `json:"date"`
	DailyRate  float64  `json:"daily_rate"`
	AnnualRate *float64 `json:"annual_rate,omitempty"`
}

type IPCARecord struct {
	ID              int64    `json:"id"`
	Date            string   `json:"date"`
	MonthlyRate     float64  `json:"monthly_rate"`
	Accumulated12m  *float64 `json:"accumulated_12m,omitempty"`
}

type FXRecord struct {
	ID   int64   `json:"id"`
	Date string  `json:"date"`
	Pair string  `json:"pair"`
	Rate float64 `json:"rate"`
}

// Selic
func (d *DB) InsertSelic(date string, daily float64, annual, target *float64) error {
	_, err := d.Conn.Exec(`INSERT OR IGNORE INTO selic_history (date, daily_rate, annual_rate, target_rate) VALUES (?,?,?,?)`,
		date, daily, annual, target)
	return err
}

func (d *DB) ListSelic(limit int, from, to string) ([]SelicRecord, error) {
	where, args := marketWhere(from, to)
	query := fmt.Sprintf("SELECT id, date, daily_rate, annual_rate, target_rate FROM selic_history %s ORDER BY date DESC LIMIT ?", where)
	args = append(args, limit)
	rows, err := d.Conn.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SelicRecord
	for rows.Next() {
		var r SelicRecord
		rows.Scan(&r.ID, &r.Date, &r.DailyRate, &r.AnnualRate, &r.TargetRate)
		out = append(out, r)
	}
	return out, nil
}

func (d *DB) CurrentSelic() (*SelicRecord, error) {
	r := &SelicRecord{}
	err := d.Conn.QueryRow("SELECT id, date, daily_rate, annual_rate, target_rate FROM selic_history ORDER BY date DESC LIMIT 1").
		Scan(&r.ID, &r.Date, &r.DailyRate, &r.AnnualRate, &r.TargetRate)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return r, err
}

// CDI
func (d *DB) InsertCDI(date string, daily float64, annual *float64) error {
	_, err := d.Conn.Exec(`INSERT OR IGNORE INTO cdi_history (date, daily_rate, annual_rate) VALUES (?,?,?)`,
		date, daily, annual)
	return err
}

func (d *DB) ListCDI(limit int, from, to string) ([]CDIRecord, error) {
	where, args := marketWhere(from, to)
	query := fmt.Sprintf("SELECT id, date, daily_rate, annual_rate FROM cdi_history %s ORDER BY date DESC LIMIT ?", where)
	args = append(args, limit)
	rows, err := d.Conn.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []CDIRecord
	for rows.Next() {
		var r CDIRecord
		rows.Scan(&r.ID, &r.Date, &r.DailyRate, &r.AnnualRate)
		out = append(out, r)
	}
	return out, nil
}

func (d *DB) CurrentCDI() (*CDIRecord, error) {
	r := &CDIRecord{}
	err := d.Conn.QueryRow("SELECT id, date, daily_rate, annual_rate FROM cdi_history ORDER BY date DESC LIMIT 1").
		Scan(&r.ID, &r.Date, &r.DailyRate, &r.AnnualRate)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return r, err
}

// IPCA
func (d *DB) InsertIPCA(date string, monthly float64, acc12m *float64) error {
	_, err := d.Conn.Exec(`INSERT OR IGNORE INTO ipca_history (date, monthly_rate, accumulated_12m) VALUES (?,?,?)`,
		date, monthly, acc12m)
	return err
}

func (d *DB) ListIPCA(limit int, from, to string) ([]IPCARecord, error) {
	where, args := marketWhere(from, to)
	query := fmt.Sprintf("SELECT id, date, monthly_rate, accumulated_12m FROM ipca_history %s ORDER BY date DESC LIMIT ?", where)
	args = append(args, limit)
	rows, err := d.Conn.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []IPCARecord
	for rows.Next() {
		var r IPCARecord
		rows.Scan(&r.ID, &r.Date, &r.MonthlyRate, &r.Accumulated12m)
		out = append(out, r)
	}
	return out, nil
}

func (d *DB) CurrentIPCA() (*IPCARecord, error) {
	r := &IPCARecord{}
	err := d.Conn.QueryRow("SELECT id, date, monthly_rate, accumulated_12m FROM ipca_history ORDER BY date DESC LIMIT 1").
		Scan(&r.ID, &r.Date, &r.MonthlyRate, &r.Accumulated12m)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return r, err
}

// FX
func (d *DB) InsertFX(date, pair string, rate float64) error {
	_, err := d.Conn.Exec(`INSERT OR IGNORE INTO fx_rates (date, pair, rate) VALUES (?,?,?)`, date, pair, rate)
	return err
}

func (d *DB) ListFX(limit int, pair, from, to string) ([]FXRecord, error) {
	where := "WHERE 1=1"
	args := []any{}
	if pair != "" {
		where += " AND pair = ?"
		args = append(args, pair)
	}
	if from != "" {
		where += " AND date >= ?"
		args = append(args, from)
	}
	if to != "" {
		where += " AND date <= ?"
		args = append(args, to)
	}
	query := fmt.Sprintf("SELECT id, date, pair, rate FROM fx_rates %s ORDER BY date DESC LIMIT ?", where)
	args = append(args, limit)
	rows, err := d.Conn.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []FXRecord
	for rows.Next() {
		var r FXRecord
		rows.Scan(&r.ID, &r.Date, &r.Pair, &r.Rate)
		out = append(out, r)
	}
	return out, nil
}

func (d *DB) CurrentFX() ([]FXRecord, error) {
	rows, err := d.Conn.Query(`SELECT DISTINCT pair FROM fx_rates`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var pairs []string
	for rows.Next() {
		var p string
		rows.Scan(&p)
		pairs = append(pairs, p)
	}
	var out []FXRecord
	for _, p := range pairs {
		var r FXRecord
		err := d.Conn.QueryRow("SELECT id, date, pair, rate FROM fx_rates WHERE pair = ? ORDER BY date DESC LIMIT 1", p).
			Scan(&r.ID, &r.Date, &r.Pair, &r.Rate)
		if err == nil {
			out = append(out, r)
		}
	}
	return out, nil
}

func marketWhere(from, to string) (string, []any) {
	where := "WHERE 1=1"
	args := []any{}
	if from != "" {
		where += " AND date >= ?"
		args = append(args, from)
	}
	if to != "" {
		where += " AND date <= ?"
		args = append(args, to)
	}
	return where, args
}