tipper/model/model.go

package model

import (
	"fmt"
	"math"
	"sort"
	"strings"

	"gordenko.dev/dima/qx"
)

func New(qxdb *qx.Db) *Model {
	if qxdb == nil {
		panic("Param is required")
	}

	s := new(Model)
	s.qx = qxdb
	return s
}

type Model struct {
	qx *qx.Db
}

func (s *Model) AddTip(tip Tip) (tipID int64, err error) {
	/*
		if req.Name == "" {
			err = api.Err{
				Code:  EmptyValue,
				Field: "name",
			}
			return
		}
	*/

	tx, err := s.qx.Tx()
	if err != nil {
		return
	}
	defer tx.Close(&err)

	tipID, err = tx.Insert(tip)
	if err != nil {
		return
	}
	err = tx.Commit()
	return
}

func (s *Model) GetTip(tipID int64) (_ *Tip, err error) {
	tip := Tip{
		TipID: tipID,
	}
	found, err := s.qx.One(&tip)
	if err != nil {
		return
	}

	if found {
		return &tip, nil
	}
	return
}

func (s *Model) HasTip(matchID string, strategyID int64) (has bool, err error) {
	has, err = s.qx.Has("tips", "WHERE matchID=? AND strategyID=?", matchID, strategyID)
	return
}

func (s *Model) ListSportTips(sportID int) (tips []Tip, err error) {
	err = s.qx.ListBy(&tips, "WHERE sportID=? ORDER BY tipTime DESC", sportID)
	return
}

func (s *Model) ListTips() (tips []Tip, err error) {
	err = s.qx.ListBy(&tips, "ORDER BY tipTime DESC")
	return
}

func (s *Model) ListWaitingTips() (tips []Tip, err error) {
	err = s.qx.ListBy(&tips, "WHERE status=? ORDER BY tipTime DESC", Waiting)
	return
}

func (s *Model) ListStrategyTips(strategyID int) (tips []Tip, err error) {
	err = s.qx.ListBy(&tips, "WHERE strategyID=? ORDER BY tipTime DESC", strategyID)
	return
}

// ListMatchTips - возвращает прогнозы с неизвестным результатом
func (s *Model) ListMatchTips(matchID string) (tips []Tip, err error) {
	err = s.qx.ListBy(&tips, "WHERE matchID=? AND status=0", matchID)
	return
}

func (s *Model) SetTipResult(req SetTipResultReq) (err error) {
	switch req.Status {
	case Waiting, Won, Lost, Void:
		// pass

	default:
		err = fix.Field(WrongValue, "status")
		return
	}

	req.Result = strings.TrimSpace(req.Result)

	if req.Result == "" {
		err = fix.Field(EmptyValue, "result")
		return
	}

	err = s.qx.Exec(`UPDATE tips SET result=?, status=?, notes=? WHERE tipID=?`,
		req.Result, req.Status, req.Notes, req.TipID)
	return
}

type StrategyPoint struct {
	Date   int64   `json:"date"`
	Profit float64 `json:"profit"`
}

func (s *Model) GetStrategyHistory(strategyID int64) (list []StrategyPoint, err error) {
	var (
		tips   []Tip
		profit float64
	)

	err = s.qx.ListBy(&tips, "WHERE strategyID=? AND status IN (1, 2) ORDER BY tipTime ASC",
		strategyID)
	if err != nil {
		return
	}

	for _, tip := range tips {
		if tip.Status == Won {
			profit += (tip.Price - 1) * 10
		} else {
			profit -= 10
		}

		list = append(list, StrategyPoint{
			Date:   tip.TipTime,
			Profit: profit,
		})
	}

	return
}

func (s *Model) GetStrategyRatedHistory(strategyID int64) (list []StrategyPoint, err error) {
	var (
		tips   []Tip
		profit float64
	)

	err = s.qx.ListBy(&tips, "WHERE strategyID=? AND status IN (1, 2) AND isRated=true ORDER BY tipTime ASC",
		strategyID)
	if err != nil {
		return
	}

	for _, tip := range tips {
		if tip.Status == Won {
			profit += (tip.Price - 1) * 10
		} else {
			profit -= 10
		}

		list = append(list, StrategyPoint{
			Date:   tip.TipTime,
			Profit: profit,
		})
	}

	return
}

func (s *Model) ListStrategiesReports(sportID int) (list []StrategyReport, err error) {
	var (
		tips    []Tip
		reports = make(map[int64]*StrategyReport)
	)

	err = s.qx.ListBy(&tips, "WHERE sportID=? AND status IN (1, 2) ORDER BY tipTime ASC", sportID)
	if err != nil {
		return
	}

	for _, tip := range tips {
		report, ok := reports[tip.StrategyID]
		if !ok {
			report = &StrategyReport{
				StrategyID: tip.StrategyID,
			}
			reports[tip.StrategyID] = report
		}

		report.Turnover += 10

		if tip.Status == Won {
			report.Profit += (tip.Price - 1) * 10
			report.WonTips++
		} else {
			report.Profit -= 10
			report.LostTips++
		}

		if report.Profit < report.Drawdown {
			report.Drawdown = report.Profit
		}
	}

	var strategies []Strategy

	err = s.qx.ListQuery(&strategies,
		"SELECT strategyID, name FROM strategies WHERE sportID=?", sportID)
	if err != nil {
		return
	}

	for _, strategy := range strategies {
		report, ok := reports[strategy.StrategyID]
		if ok {
			report.Strategy = strategy.Name
		}
	}

	for _, report := range reports {
		// turnover = 100%
		// profit   = x (ROI)
		report.ROI = (report.Profit * 100) / report.Turnover

		// won + lost = 100%
		// won        = x (Accuracy)
		report.Accuracy = (float64(report.WonTips) * 100) / float64(report.WonTips+report.LostTips)

		list = append(list, *report)
	}

	sort.Slice(list, func(i, j int) bool {
		return list[i].StrategyID < list[j].StrategyID
	})

	return
}

func (s *Model) GetSportRatedReport(sportID int64) (report SportReport, err error) {
	var tips []Tip

	report.SportID = sportID

	err = s.qx.ListBy(&tips, "WHERE sportID=? AND status IN (1, 2) AND isRated=true ORDER BY tipTime ASC", sportID)
	if err != nil {
		return
	}

	for _, tip := range tips {

		report.Turnover += 10

		if tip.Status == Won {
			report.Profit += (tip.Price - 1) * 10
			report.WonTips++
		} else {
			report.Profit -= 10
			report.LostTips++
		}

		if report.Profit < report.Drawdown {
			report.Drawdown = report.Profit
		}
	}

	// turnover = 100%
	// profit   = x (ROI)
	report.ROI = (report.Profit * 100) / report.Turnover

	// won + lost = 100%
	// won        = x (Accuracy)
	report.Accuracy = (float64(report.WonTips) * 100) / float64(report.WonTips+report.LostTips)

	return
}

func getAccuracy(won, lost int) (accuracy float64) {
	// won + lost = 100%
	// won        = x%
	// x = (won*100)  / (won+lost)

	total := won + lost
	if total == 0 {
		return
	}

	return float64(won*100) / float64(total)
}

type StrategyChampStats struct {
	//StrategyID int64   `json:"strategyID"`
	ZoneID    string  `json:"zoneID" qx:"table=tips"`
	ZoneName  string  `json:"zoneName"`
	ChampID   string  `json:"champID"`
	ChampName string  `json:"champName"`
	Won       int     `json:"won"`
	Lost      int     `json:"lost"`
	AvgPrice  float64 `json:"avgPrice"`
}

// Статистика по лигам
func (s *Model) ListStrategyChampStats(strategyID int64) (list []StrategyChampStats, err error) {
	err = s.qx.ListQuery(&list, `
	SELECT z.zoneID, z.name, t.champID, c.name, COUNT(IF(t.status=1,1,NULL)) as won, 
	COUNT(IF(t.status=2,1,NULL)) as lost, AVG(t.price) 
	FROM tips t 
		INNER JOIN teamChamps c USING(champID) 
		INNER JOIN teamZones z USING(zoneID)
	WHERE t.strategyID=? AND t.status IN (1,2)
	GROUP BY t.champID
	ORDER BY c.name ASC`, strategyID)
	if err != nil {
		return
	}

	eps := 0.001

	sort.Slice(list, func(i, j int) bool {
		// Сортируем по точности прогнозов

		a := getAccuracy(list[i].Won, list[i].Lost)
		b := getAccuracy(list[j].Won, list[j].Lost)

		dif := math.Abs(a - b)

		if dif < eps {
			if list[i].Won == list[j].Won {
				if list[i].ZoneName == list[j].ZoneName {
					return list[i].ChampName < list[j].ChampName
				}
				return list[i].ZoneName < list[j].ZoneName
			}
			return list[i].Won > list[j].Won
		}

		return a > b
	})

	return
}

// Статистика по лигам
func (s *Model) ListStrategyRatedChampStats(strategyID int64) (list []StrategyChampStats, err error) {
	err = s.qx.ListQuery(&list, `
	SELECT z.zoneID, z.name, t.champID, c.name, COUNT(IF(t.status=1,1,NULL)) as won, 
	COUNT(IF(t.status=2,1,NULL)) as lost, AVG(t.price) 
	FROM tips t 
		INNER JOIN teamChamps c USING(champID) 
		INNER JOIN teamZones z USING(zoneID)
	WHERE t.strategyID=? AND t.status IN (1,2) AND t.isRated=true
	GROUP BY t.champID
	ORDER BY c.name ASC`, strategyID)
	if err != nil {
		return
	}

	eps := 0.001

	sort.Slice(list, func(i, j int) bool {
		// Сортируем по точности прогнозов

		a := getAccuracy(list[i].Won, list[i].Lost)
		b := getAccuracy(list[j].Won, list[j].Lost)

		dif := math.Abs(a - b)

		if dif < eps {
			if list[i].Won == list[j].Won {
				if list[i].ZoneName == list[j].ZoneName {
					return list[i].ChampName < list[j].ChampName
				}
				return list[i].ZoneName < list[j].ZoneName
			}
			return list[i].Won > list[j].Won
		}

		return a > b
	})

	return
}

type Strategy struct {
	StrategyID int64  `json:"strategyID" qx:"table=strategies; pk"`
	Name       string `json:"name"`
}

// Статистика по лигам
func (s *Model) ListStrategies() (list []Strategy, err error) {
	tmp := []struct {
		StrategyID int64
		Name       string
		SportID    int64
		SportName  string
	}{}

	err = s.qx.ListQuery(&tmp, `
	SELECT s.strategyID, s.name, x.sportID, x.name
	FROM strategies s 
		INNER JOIN sports x USING(sportID)
	ORDER BY x.sportID ASC, s.strategyID ASC`)
	if err != nil {
		return
	}

	for _, x := range tmp {
		list = append(list, Strategy{
			StrategyID: x.StrategyID,
			Name:       fmt.Sprintf("#%d %s. %s", x.StrategyID, x.SportName, x.Name),
		})
	}
	return
	/*
		return []Strategy{
			{
				StrategyID: 1,
				Name:       "#1 Алгоритм 1 (Футбол, гол в 1м тайме)",
			},
			{
				StrategyID: 2,
				Name:       "#2 Алгоритм 2 (Футбол, гол в матче)",
			},
			{
				StrategyID: 3,
				Name:       "#3 Алгоритм 3 (Футбол, 2й гол в матче)",
			},
			{
				StrategyID: 4,
				Name:       "#4 Алгоритм ТБ (-9) (Гандбол, ТБ в матче)",
			},
			{
				StrategyID: 5,
				Name:       "#5 Алгоритм ТМ (+8) (Гандбол, ТМ в матче)",
			},
			{
				StrategyID: 6,
				Name:       "#6 Алгоритм 1 модифицированный (Футбол, гол в 1м тайме)",
			},
			{
				StrategyID: 7,
				Name:       "#7 Алгоритм 2 модифицированный (Футбол, гол в матче)",
			},
			{
				StrategyID: 8,
				Name:       "#8 Алгоритм 3 модифицированный (Футбол, 2й гол в матче)",
			},
			{
				StrategyID: 11,
				Name:       "#11 Алгоритм ТБ (-6) (Гандбол, ТБ в 1м тайме)",
			},
			{
				StrategyID: 12,
				Name:       "#12 Алгоритм ТМ (+5) (Гандбол, ТМ в 1м тайме)",
			},


		}, nil
	*/
}