trade/web/backend/internal/store/ai.go

package store

import (
	"database/sql"
	"encoding/json"
	"fmt"
)

// AnalysisContext 汇总一次 AI 分析所需的全部数据。
type AnalysisContext struct {
	Score        Score    `json:"score"`
	Candles      []Candle `json:"candles"`
	RecentScores []Score  `json:"recent_scores"`
}

// GetAnalysisContext 拉取指定合约某日的 score + 近 60 日 K 线 + 近 10 日 scores。
func (s *FuturesStore) GetAnalysisContext(tsCode, tradeDate string) (*AnalysisContext, error) {
	ctx := &AnalysisContext{}

	// 1) 目标日 score（含 detail_json）
	row := s.db.QueryRow(`SELECT id, ts_code, trade_date, close, oi, oi_chg, short_term, medium_term,
		long_term, composite, signal, detail_json, created_at FROM scores
		WHERE ts_code = $1 AND trade_date = $2`, tsCode, tradeDate)
	var detail sql.NullString
	if err := row.Scan(&ctx.Score.ID, &ctx.Score.TsCode, &ctx.Score.TradeDate,
		&ctx.Score.Close, &ctx.Score.OI, &ctx.Score.OIChg,
		&ctx.Score.ShortTerm, &ctx.Score.MediumTerm, &ctx.Score.LongTerm,
		&ctx.Score.Composite, &ctx.Score.Signal, &detail, &ctx.Score.CreatedAt); err != nil {
		return nil, fmt.Errorf("score not found: %w", err)
	}
	if detail.Valid {
		ctx.Score.Detail = json.RawMessage(detail.String)
	}

	// 2) 近 60 日 K 线
	rows, err := s.db.Query(`SELECT ts_code, trade_date,
		COALESCE(NULLIF(open, 'NaN'::real), 0), COALESCE(NULLIF(high, 'NaN'::real), 0),
		COALESCE(NULLIF(low, 'NaN'::real), 0), COALESCE(NULLIF(close, 'NaN'::real), 0),
		COALESCE(NULLIF(vol, 'NaN'::real), 0), COALESCE(NULLIF(amount, 'NaN'::real), 0),
		COALESCE(NULLIF(oi, 'NaN'::real), 0), COALESCE(NULLIF(oi_chg, 'NaN'::real), 0),
		COALESCE(NULLIF(pre_close, 'NaN'::real), 0)
		FROM candles WHERE ts_code = $1 AND trade_date <= $2
		ORDER BY trade_date DESC LIMIT 60`, tsCode, tradeDate)
	if err != nil {
		return nil, fmt.Errorf("candles: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var c Candle
		if err := rows.Scan(&c.TsCode, &c.TradeDate, &c.Open, &c.High, &c.Low, &c.Close,
			&c.Vol, &c.Amount, &c.OI, &c.OIChg, &c.PreClose); err != nil {
			return nil, err
		}
		ctx.Candles = append(ctx.Candles, c)
	}
	// 反转回升序
	for i, j := 0, len(ctx.Candles)-1; i < j; i, j = i+1, j-1 {
		ctx.Candles[i], ctx.Candles[j] = ctx.Candles[j], ctx.Candles[i]
	}

	// 3) 近 10 日 scores（不含 detail_json 以减体积）
	scoreRows, err := s.db.Query(`SELECT id, ts_code, trade_date, close, oi, oi_chg, short_term, medium_term,
		long_term, composite, signal, created_at FROM scores
		WHERE ts_code = $1 AND trade_date <= $2
		ORDER BY trade_date DESC LIMIT 10`, tsCode, tradeDate)
	if err != nil {
		return nil, fmt.Errorf("recent scores: %w", err)
	}
	defer scoreRows.Close()
	for scoreRows.Next() {
		var sc Score
		if err := scoreRows.Scan(&sc.ID, &sc.TsCode, &sc.TradeDate, &sc.Close, &sc.OI, &sc.OIChg,
			&sc.ShortTerm, &sc.MediumTerm, &sc.LongTerm, &sc.Composite, &sc.Signal, &sc.CreatedAt); err != nil {
			return nil, err
		}
		ctx.RecentScores = append(ctx.RecentScores, sc)
	}
	// 反转回升序
	for i, j := 0, len(ctx.RecentScores)-1; i < j; i, j = i+1, j-1 {
		ctx.RecentScores[i], ctx.RecentScores[j] = ctx.RecentScores[j], ctx.RecentScores[i]
	}

	return ctx, nil
}

// LLMConfig 数据库中的 LLM 配置单例。
type LLMConfig struct {
	BaseURL string `json:"base_url"`
	APIKey  string `json:"api_key"`
	Model   string `json:"model"`
}

// EnsureLLMConfigTable 建 llm_config 表（幂等）。
func (s *FuturesStore) EnsureLLMConfigTable() error {
	_, err := s.db.Exec(`
		CREATE TABLE IF NOT EXISTS llm_config (
			id       INTEGER PRIMARY KEY DEFAULT 1 CHECK(id = 1),
			base_url TEXT NOT NULL DEFAULT 'https://api.deepseek.com/v1',
			api_key  TEXT NOT NULL DEFAULT '',
			model    TEXT NOT NULL DEFAULT 'deepseek-chat',
			updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		);
		INSERT INTO llm_config (id) VALUES (1) ON CONFLICT DO NOTHING;
	`)
	return err
}

// GetLLMConfig 读取 LLM 配置，无记录返回零值。
func (s *FuturesStore) GetLLMConfig() (*LLMConfig, error) {
	cfg := &LLMConfig{}
	err := s.db.QueryRow(`SELECT base_url, api_key, model FROM llm_config WHERE id = 1`).
		Scan(&cfg.BaseURL, &cfg.APIKey, &cfg.Model)
	if err != nil {
		if err.Error() == "sql: no rows in result set" {
			return cfg, nil
		}
		return nil, err
	}
	return cfg, nil
}

// SaveLLMConfig 写入 LLM 配置（upsert）。
func (s *FuturesStore) SaveLLMConfig(cfg *LLMConfig) error {
	_, err := s.db.Exec(`
		INSERT INTO llm_config (id, base_url, api_key, model, updated_at)
		VALUES (1, $1, $2, $3, CURRENT_TIMESTAMP)
		ON CONFLICT (id) DO UPDATE SET
			base_url = EXCLUDED.base_url,
			api_key  = EXCLUDED.api_key,
			model    = EXCLUDED.model,
			updated_at = CURRENT_TIMESTAMP
	`, cfg.BaseURL, cfg.APIKey, cfg.Model)
	return err
}