calorie-counter/foodservice.go

package main

import (
	"database/sql"
	"fmt"
	"log"
)

type (
	FoodService struct {
		db *DB
	}
	Food struct {
		Rowid      int64   `json:"rowid"`
		Date       string  `json:"date"`
		Food       string  `json:"food"`
		Descripton string  `json:"description"`
		Amount     float32 `json:"amount"`
		Per100     float32 `json:"per100"`
		Energy     float32 `json:"energy"`
	}
	FoodSearch struct {
		Food
		Score int64 `json:"score"`
	}
	AggregatedFood struct {
		Period    string  `json:"period"`
		Amount    float32 `json:"amount"`
		AvgPer100 float32 `json:"avgPer100"`
		Energy    float32 `json:"energy"`
	}
)

const foodColumns = "rowid, date, food, description, amount, per100, energy"
const foodAggregatedColumns = "period, amount, avgPer100, energy"

func (s *FoodService) GetRecent() ([]Food, error) {
	var res []Food = []Food{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get recent food, db is nil or is not ready")
	}

	row, err := s.db.readConn.Query(fmt.Sprintf("SELECT %s from foodView WHERE date > datetime('now', '-%d days') order by date desc;", foodColumns, Settings.FoodDaysLookback))
	if err != nil {
		log.Printf("error getting daily food: %v", err)
		return res, err
	}

	for row.Next() {
		var food Food
		err := row.Scan(&food.Rowid, &food.Date, &food.Food, &food.Descripton, &food.Amount, &food.Per100, &food.Energy)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}

		res = append(res, food)
	}

	return res, nil
}

func (s *FoodService) GetLastPer100(name string) ([]FoodSearch, error) {
	res := []FoodSearch{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get last per100, db is nil or is not ready")
	}

	query := fmt.Sprintf(`
with search_results as (
	select word, score, f.rowid, f.*,
		row_number() over (partition by f.food order by score asc, date desc) as rn
	from foodfix
	inner join food f on f.food == word
	where word MATCH ?
)
select %s, score
from search_results
where rn = 1
order by score asc, date desc
limit %d
		`, foodColumns, Settings.SearchLimit)
	// log.Printf("%#v", query)
	rows, err := s.db.readConn.Query(query, name)
	if err != nil {
		log.Printf("error getting last per100: %v", err)
		return res, err
	}

	for rows.Next() {
		var f FoodSearch
		err := rows.Scan(&f.Rowid, &f.Date, &f.Food.Food, &f.Descripton, &f.Amount, &f.Per100, &f.Energy, &f.Score)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}
		res = append(res, f)
	}

	if len(res) == 0 {
		return nil, fmt.Errorf("no results found for %s", name)
	}

	return res, nil
}

func (s *FoodService) Create(food Food) (Food, error) {
	if s.db == nil || !s.db.Ready {
		return food, fmt.Errorf("cannot create food, db is nil or is not ready")
	}
	if food.Food == "" {
		return food, fmt.Errorf("cannot create food, food is empty")
	}
	if food.Amount <= 0 {
		return food, fmt.Errorf("cannot create food, amount is less than or equal to 0")
	}

	var res sql.Result
	var err error
	if food.Per100 > 0 {
		res, err = s.db.writeConn.Exec("INSERT INTO food (food, description, amount, per100) VALUES (?, ?, ?, ?)", food.Food, food.Descripton, food.Amount, food.Per100)
		if err != nil {
			return food, fmt.Errorf("error inserting food: %v", err)
		}
	} else {
		res, err = s.db.writeConn.Exec("INSERT INTO food (food, description, amount) VALUES (?, ?, ?)", food.Food, food.Descripton, food.Amount)
		if err != nil {
			return food, fmt.Errorf("error inserting food: %v", err)
		}
	}

	rowid, err := res.LastInsertId()
	if err != nil {
		return food, fmt.Errorf("error getting last insert id: %v", err)
	}

	return s.GetByRowid(rowid)
}

func (s *FoodService) Update(food Food) (Food, error) {
	if s.db == nil || !s.db.Ready {
		return food, fmt.Errorf("cannot update food, db is nil or is not ready")
	}
	if food.Rowid <= 0 {
		return food, fmt.Errorf("cannot update food, rowid is less than or equal to 0")
	}
	if food.Food == "" {
		return food, fmt.Errorf("cannot update food, food is empty")
	}
	if food.Amount <= 0 {
		return food, fmt.Errorf("cannot update food, amount is less than or equal to 0")
	}

	if food.Per100 > 0 {
		_, err := s.db.writeConn.Exec("UPDATE food SET food = ?, description = ?, amount = ?, per100 = ? WHERE rowid = ?", food.Food, food.Descripton, food.Amount, food.Per100, food.Rowid)
		if err != nil {
			return food, fmt.Errorf("error updating food: %v", err)
		}
	} else {
		_, err := s.db.writeConn.Exec("UPDATE food SET food = ?, description = ?, amount = ? WHERE rowid = ?", food.Food, food.Descripton, food.Amount, food.Rowid)
		if err != nil {
			return food, fmt.Errorf("error updating food: %v", err)
		}
	}

	return s.GetByRowid(food.Rowid)
}

func (s *FoodService) GetByRowid(rowid int64) (Food, error) {
	var res Food
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get food by rowid, db is nil or is not ready")
	}

	row := s.db.readConn.QueryRow(fmt.Sprintf("SELECT %s from foodView WHERE rowid = ?", foodColumns), rowid)
	err := row.Scan(&res.Rowid, &res.Date, &res.Food, &res.Descripton, &res.Amount, &res.Per100, &res.Energy)
	if err != nil {
		return res, fmt.Errorf("error scanning row: %v", err)
	}

	return res, nil
}

// I could probably refactor this to be less of a disaster...
// But I think it'll work for now
func (s *FoodService) GetDaily() ([]AggregatedFood, error) {
	res := []AggregatedFood{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get daily food, db is nil or is not ready")
	}

	row, err := s.db.readConn.Query(fmt.Sprintf("SELECT %s from foodDaily LIMIT %d", foodAggregatedColumns, Settings.FoodDailyLookback))
	if err != nil {
		log.Printf("error getting daily food: %v", err)
		return res, err
	}

	for row.Next() {
		var food AggregatedFood
		err := row.Scan(&food.Period, &food.Amount, &food.AvgPer100, &food.Energy)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}

		res = append(res, food)
	}

	return res, nil
}

func (s *FoodService) GetWeekly() ([]AggregatedFood, error) {
	res := []AggregatedFood{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get weekly food, db is nil or is not ready")
	}

	row, err := s.db.readConn.Query(fmt.Sprintf("SELECT %s from foodWeekly LIMIT %d", foodAggregatedColumns, Settings.FoodWeeklyLookback))
	if err != nil {
		log.Printf("error getting weekly food: %v", err)
		return res, err
	}

	for row.Next() {
		var food AggregatedFood
		err := row.Scan(&food.Period, &food.Amount, &food.AvgPer100, &food.Energy)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}

		res = append(res, food)
	}

	return res, nil
}

func (s *FoodService) GetMonthly() ([]AggregatedFood, error) {
	res := []AggregatedFood{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get monthly food, db is nil or is not ready")
	}

	row, err := s.db.readConn.Query(fmt.Sprintf("SELECT %s from foodMonthly LIMIT %d", foodAggregatedColumns, Settings.FoodMonthlyLookback))
	if err != nil {
		log.Printf("error getting monthly food: %v", err)
		return res, err
	}

	for row.Next() {
		var food AggregatedFood
		err := row.Scan(&food.Period, &food.Amount, &food.AvgPer100, &food.Energy)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}

		res = append(res, food)
	}

	return res, nil
}

func (s *FoodService) GetYearly() ([]AggregatedFood, error) {
	res := []AggregatedFood{}
	if s.db == nil || !s.db.Ready {
		return res, fmt.Errorf("cannot get yearly food, db is nil or is not ready")
	}

	row, err := s.db.readConn.Query(fmt.Sprintf("SELECT %s from foodYearly LIMIT %d", foodAggregatedColumns, Settings.FoodYearlyLookback))
	if err != nil {
		log.Printf("error getting yearly food: %v", err)
		return res, err
	}

	for row.Next() {
		var food AggregatedFood
		err := row.Scan(&food.Period, &food.Amount, &food.AvgPer100, &food.Energy)
		if err != nil {
			log.Printf("error scanning row: %v", err)
			continue
		}

		res = append(res, food)
	}

	return res, nil
}