package db

import "time"

// TrendSignalRecord mirrors the trend_signals table row.
type TrendSignalRecord struct {
	ID            int64
	Coin          string
	Type          string // "enter" or "exit"
	SignalScore   *float64
	Price         *float64
	EMA52         *float64
	EMASlope      *float64
	VolumeRatio   *float64
	Range24h      *float64
	VolBaseline   *float64
	PriceAboveEMA bool
	State         *string
	CreatedAt     time.Time
}

// SaveTrendSignal inserts a new trend signal record.
func (d *DB) SaveTrendSignal(s *TrendSignalRecord) (int64, error) {
	pa := 0
	if s.PriceAboveEMA {
		pa = 1
	}
	res, err := d.Exec(`INSERT INTO trend_signals
		(coin, type, signal_score, price, ema_52, ema_slope, volume_ratio,
		 range_24h, vol_baseline, price_above_ema, state, created_at)
		VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,
		s.Coin, s.Type, s.SignalScore, s.Price, s.EMA52, s.EMASlope, s.VolumeRatio,
		s.Range24h, s.VolBaseline, pa, s.State, s.CreatedAt,
	)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// GetTrendSignals returns the most recent N trend signal records.
func (d *DB) GetTrendSignals(limit int) ([]TrendSignalRecord, error) {
	rows, err := d.Query(`SELECT id, coin, type, signal_score, price, ema_52, ema_slope,
		volume_ratio, range_24h, vol_baseline, price_above_ema, state, created_at
		FROM trend_signals ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var signals []TrendSignalRecord
	for rows.Next() {
		var s TrendSignalRecord
		var pa int
		if err := rows.Scan(&s.ID, &s.Coin, &s.Type, &s.SignalScore, &s.Price,
			&s.EMA52, &s.EMASlope, &s.VolumeRatio, &s.Range24h, &s.VolBaseline,
			&pa, &s.State, &s.CreatedAt); err != nil {
			return nil, err
		}
		s.PriceAboveEMA = pa == 1
		signals = append(signals, s)
	}
	return signals, rows.Err()
}