AShareView/server/repository/stock_repo.go

package repository

import (
	"ashareview-server/model"

	"gorm.io/gorm"
)

type StockRepo struct {
	db *gorm.DB
}

func NewStockRepo(db *gorm.DB) *StockRepo {
	return &StockRepo{db: db}
}

func (r *StockRepo) GetLatestTradeDate() (string, error) {
	var result struct {
		MaxDate string
	}
	err := r.db.Model(&model.StockDaily{}).
		Select("MAX(date) as max_date").
		Scan(&result).Error
	return result.MaxDate, err
}

func (r *StockRepo) GetSummaryByDate(date string) (*model.DashboardSummary, error) {
	summary := &model.DashboardSummary{}

	r.db.Model(&model.StockDaily{}).
		Where("date = ?", date).
		Count(&summary.TotalStocks)

	r.db.Model(&model.StockDaily{}).
		Where("date = ? AND pct_change > 0", date).
		Count(&summary.UpCount)

	r.db.Model(&model.StockDaily{}).
		Where("date = ? AND pct_change < 0", date).
		Count(&summary.DownCount)

	r.db.Model(&model.StockDaily{}).
		Where("date = ? AND pct_change = 0", date).
		Count(&summary.FlatCount)

	var amtResult struct {
		Total float64
		Avg   float64
	}
	r.db.Model(&model.StockDaily{}).
		Where("date = ?", date).
		Select("COALESCE(SUM(turnover), 0) as total, COALESCE(AVG(pct_change), 0) as avg").
		Scan(&amtResult)
	summary.TotalTurnover = amtResult.Total
	summary.AvgPctChange = amtResult.Avg

	r.db.Table("stock_daily as sd").
		Select("sd.code, si.name, sd.pct_change, sd.close").
		Joins("LEFT JOIN stock_info si ON sd.code = si.code").
		Where("sd.date = ? AND sd.pct_change >= 9.5", date).
		Order("sd.pct_change DESC").
		Find(&summary.LimitUpList)

	r.db.Table("stock_daily as sd").
		Select("sd.code, si.name, sd.pct_change, sd.close").
		Joins("LEFT JOIN stock_info si ON sd.code = si.code").
		Where("sd.date = ? AND sd.pct_change <= -9.5", date).
		Order("sd.pct_change ASC").
		Find(&summary.LimitDownList)

	r.db.Raw(`
		SELECT sd.code, si.name, sd.pct_change, sd.close
		FROM stock_daily sd
		LEFT JOIN stock_info si ON sd.code = si.code
		WHERE sd.date = ?
		  AND sd.close = (
		    SELECT MAX(sd2.close)
		    FROM stock_daily sd2
		    WHERE sd2.code = sd.code
		      AND sd2.date <= sd.date
		      AND sd2.date >= DATE_SUB(sd.date, INTERVAL 150 DAY)
		  )
		  AND sd.close > 0
		ORDER BY sd.pct_change DESC
	`, date).Scan(&summary.High100List)

	return summary, nil
}

func (r *StockRepo) GetDailySummary(days int) ([]model.DailySummary, error) {
	var result []model.DailySummary

	sql := `SELECT
		date,
		COUNT(*) as total_stocks,
		SUM(CASE WHEN pct_change > 0 THEN 1 ELSE 0 END) as up_count,
		SUM(CASE WHEN pct_change < 0 THEN 1 ELSE 0 END) as down_count,
		SUM(CASE WHEN pct_change = 0 OR pct_change IS NULL THEN 1 ELSE 0 END) as flat_count,
		COALESCE(SUM(turnover), 0) as total_turnover,
		COALESCE(AVG(pct_change), 0) as avg_pct_change,
		SUM(CASE WHEN pct_change >= 9.5 AND pct_change < 19.5 THEN 1 ELSE 0 END) as limit_up_10,
		SUM(CASE WHEN pct_change <= -9.5 AND pct_change > -19.5 THEN 1 ELSE 0 END) as limit_down_10,
		SUM(CASE WHEN pct_change >= 19.5 THEN 1 ELSE 0 END) as limit_up_20,
		SUM(CASE WHEN pct_change <= -19.5 THEN 1 ELSE 0 END) as limit_down_20
	FROM stock_daily
	WHERE date >= (SELECT MAX(date) FROM stock_daily) - INTERVAL ? DAY
	GROUP BY date
	ORDER BY date ASC`

	err := r.db.Raw(sql, days-1).Scan(&result).Error

	return result, err
}

func (r *StockRepo) GetConsecutiveLimits() ([]model.ConsecutiveLimit, error) {
	// Get latest trade date from stock_daily
	type dateResult struct {
		D string `gorm:"column:d"`
	}
	var dr dateResult
	r.db.Raw("SELECT MAX(DATE(date)) as d FROM stock_daily").Scan(&dr)
	latest := dr.D
	if latest == "" {
		return nil, nil
	}

	// Get recent trading dates up to latest
	var dates []string
	r.db.Raw(`SELECT DATE(date) as d FROM stock_daily WHERE date <= ? GROUP BY date ORDER BY date DESC LIMIT 20`, latest).Scan(&dates)
	if len(dates) == 0 {
		return nil, nil
	}
	earliest := dates[len(dates)-1]

	// Get all limit-up records for recent dates
	type limitRow struct {
		Code string
		D    string
	}
	var rows []limitRow
	r.db.Raw(`SELECT code, DATE(date) as d FROM stock_daily WHERE pct_change >= 9.5 AND date >= ?`, earliest).Scan(&rows)

	// Build set of (code, date) for limit-up
	limitSet := make(map[string]bool)
	for _, row := range rows {
		limitSet[row.Code+"_"+row.D] = true
	}

	// Get today's limit-up stocks with info
	type stockInfo struct {
		Code      string  `gorm:"column:code"`
		Name      string  `gorm:"column:name"`
		Close     float64 `gorm:"column:close"`
		PctChange float64 `gorm:"column:pct_change"`
	}
	var todayLimit []stockInfo
	r.db.Raw(`
		SELECT sd.code, si.name, sd.close, sd.pct_change
		FROM stock_daily sd
		LEFT JOIN stock_info si ON sd.code = si.code
		WHERE sd.date = ? AND sd.pct_change >= 9.5
		ORDER BY sd.pct_change DESC
	`, latest).Scan(&todayLimit)

	// For each stock, count consecutive limit-up days backwards from latest
	var result []model.ConsecutiveLimit
	for _, s := range todayLimit {
		streak := 0
		for _, d := range dates {
			if limitSet[s.Code+"_"+d] {
				streak++
			} else {
				break
			}
		}
		result = append(result, model.ConsecutiveLimit{
			Code:      s.Code,
			Name:      s.Name,
			Streak:    streak,
			Close:     s.Close,
			PctChange: s.PctChange,
		})
	}

	// Sort by streak desc
	for i := 0; i < len(result); i++ {
		for j := i + 1; j < len(result); j++ {
			if result[j].Streak > result[i].Streak || (result[j].Streak == result[i].Streak && result[j].PctChange > result[i].PctChange) {
				result[i], result[j] = result[j], result[i]
			}
		}
	}

	return result, nil
}