package db

import (
	"time"
)

// TrendEventRecord represents a persisted trend state transition.
type TrendEventRecord struct {
	ID         int64   `json:"id"`
	Coin       string  `json:"coin"`
	PrevState  string  `json:"prev_state"`
	NewState   string  `json:"new_state"`
	Direction  string  `json:"direction"`
	ZScore     float64 `json:"z_score"`
	Volatility float64 `json:"volatility"`
	BGChange   float64 `json:"bg_change"`
	HLChange   float64 `json:"hl_change"`
	BNChange   float64 `json:"bn_change"`
	OKXChange  float64 `json:"okx_change"`
	ExAgree    int     `json:"ex_agree"`
	ExTotal    int     `json:"ex_total"`
	CreatedAt  string  `json:"created_at"`
}

// InsertTrendEvent saves a trend event to the database.
func (d *DB) InsertTrendEvent(coin, prevState, newState, direction string, zScore, volatility, bgChange, hlChange, bnChange, okxChange float64, exAgree, exTotal int) error {
	_, err := d.Exec(`
		INSERT INTO trend_events (coin, prev_state, new_state, direction, z_score, volatility, bg_change, hl_change, bn_change, okx_change, ex_agree, ex_total, created_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		coin, prevState, newState, direction, zScore, volatility, bgChange, hlChange, bnChange, okxChange, exAgree, exTotal, Now().Format(time.RFC3339))
	return err
}

// GetTrendEvents returns trend events ordered by creation time descending.
func (d *DB) GetTrendEvents(limit int) ([]TrendEventRecord, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := d.Query(`
		SELECT id, coin, prev_state, new_state, direction, z_score, volatility, bg_change, hl_change, bn_change, okx_change, ex_agree, ex_total, created_at
		FROM trend_events
		ORDER BY created_at DESC
		LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []TrendEventRecord
	for rows.Next() {
		var r TrendEventRecord
		if err := rows.Scan(&r.ID, &r.Coin, &r.PrevState, &r.NewState, &r.Direction,
			&r.ZScore, &r.Volatility, &r.BGChange, &r.HLChange, &r.BNChange, &r.OKXChange,
			&r.ExAgree, &r.ExTotal, &r.CreatedAt); err != nil {
			return nil, err
		}
		result = append(result, r)
	}
	return result, rows.Err()
}