db/recipe-sql.go

package db

import (
	"database/sql"
	"errors"
	"fmt"

	"github.com/chadweimer/gomp/models"
	"github.com/jmoiron/sqlx"
)

type sqlRecipeDriverAdapter interface {
	GetSearchFields(filterFields []models.SearchField, query string) (string, []any)
}

type sqlRecipeDriver struct {
	Db      *sqlx.DB
	adapter sqlRecipeDriverAdapter
}

var supportedSearchFields = [...]models.SearchField{
	models.SearchFieldName,
	models.SearchFieldIngredients,
	models.SearchFieldDirections,
	models.SearchFieldStorageInstructions,
	models.SearchFieldNutrition,
}

func (d *sqlRecipeDriver) Create(recipe *models.Recipe) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		return d.createImpl(recipe, db)
	})
}

func (d *sqlRecipeDriver) createImpl(recipe *models.Recipe, db sqlx.Ext) error {
	stmt := "INSERT INTO recipe (name, serving_size, nutrition_info, ingredients, directions, storage_instructions, source_url, recipe_time) " +
		"VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id"

	err := sqlx.Get(db, recipe, stmt,
		recipe.Name, recipe.ServingSize, recipe.NutritionInfo, recipe.Ingredients, recipe.Directions, recipe.StorageInstructions, recipe.SourceURL, recipe.Time)
	if err != nil {
		return fmt.Errorf("creating recipe: %w", err)
	}

	for _, tag := range recipe.Tags {
		if err := d.createTagImpl(*recipe.ID, tag, db); err != nil {
			return fmt.Errorf("adding tags to new recipe: %w", err)
		}
	}

	return nil
}

func (d *sqlRecipeDriver) Read(id int64) (*models.Recipe, error) {
	return get(d.Db, func(q sqlx.Queryer) (*models.Recipe, error) {
		stmt := "SELECT id, name, serving_size, nutrition_info, ingredients, directions, storage_instructions, source_url, recipe_time, current_state, created_at, modified_at " +
			"FROM recipe WHERE id = $1"
		recipe := new(models.Recipe)
		if err := sqlx.Get(q, recipe, stmt, id); err != nil {
			return nil, err
		}

		tags, err := d.ListTags(id)
		if err != nil {
			return nil, fmt.Errorf("reading tags for recipe: %w", err)
		}
		recipe.Tags = *tags

		return recipe, nil
	})
}

func (d *sqlRecipeDriver) Update(recipe *models.Recipe) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		return d.updateImpl(recipe, db)
	})
}

func (d *sqlRecipeDriver) updateImpl(recipe *models.Recipe, db sqlx.Execer) error {
	if recipe.ID == nil {
		return ErrMissingID
	}

	_, err := db.Exec(
		"UPDATE recipe "+
			"SET name = $1, serving_size = $2, nutrition_info = $3, ingredients = $4, directions = $5, storage_instructions = $6, source_url = $7, recipe_time = $8 "+
			"WHERE id = $9",
		recipe.Name, recipe.ServingSize, recipe.NutritionInfo, recipe.Ingredients, recipe.Directions, recipe.StorageInstructions, recipe.SourceURL, recipe.Time, recipe.ID)
	if err != nil {
		return fmt.Errorf("updating recipe: %w", err)
	}

	// Deleting and recreating seems inefficient. Maybe make this smarter.
	if err = d.deleteAllTagsImpl(*recipe.ID, db); err != nil {
		return fmt.Errorf("deleting tags before updating on recipe: %w", err)
	}
	for _, tag := range recipe.Tags {
		if err = d.createTagImpl(*recipe.ID, tag, db); err != nil {
			return fmt.Errorf("updating tags on recipe: %w", err)
		}
	}

	return nil
}

func (d *sqlRecipeDriver) Delete(id int64) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		return d.deleteImpl(id, db)
	})
}

func (*sqlRecipeDriver) deleteImpl(id int64, db sqlx.Execer) error {
	if _, err := db.Exec("DELETE FROM recipe WHERE id = $1", id); err != nil {
		return fmt.Errorf("deleting recipe: %w", err)
	}

	return nil
}

func (d *sqlRecipeDriver) GetRating(id int64) (*float32, error) {
	return get(d.Db, func(db sqlx.Queryer) (*float32, error) {
		var rating float32
		err := sqlx.Get(db, &rating,
			"SELECT COALESCE(g.rating, 0) AS avg_rating FROM recipe AS r "+
				"LEFT OUTER JOIN recipe_rating as g ON r.id = g.recipe_id "+
				"WHERE r.id = $1", id)
		if err != nil {
			return nil, fmt.Errorf("updating recipe state: %w", err)
		}

		return &rating, nil
	})
}

func (d *sqlRecipeDriver) SetRating(id int64, rating float32) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		count := -1
		err := sqlx.Get(db, &count, "SELECT count(*) FROM recipe_rating WHERE recipe_id = $1", id)

		if errors.Is(err, sql.ErrNoRows) || count == 0 {
			_, err = db.Exec(
				"INSERT INTO recipe_rating (recipe_id, rating) VALUES ($1, $2)", id, rating)
			if err != nil {
				return fmt.Errorf("creating recipe rating: %w", err)
			}
		} else if err == nil {
			_, err = db.Exec(
				"UPDATE recipe_rating SET rating = $1 WHERE recipe_id = $2", rating, id)
		}

		if err != nil {
			return fmt.Errorf("updating recipe rating: %w", err)
		}

		return nil
	})
}

func (d *sqlRecipeDriver) SetState(id int64, state models.RecipeState) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		_, err := db.Exec(
			"UPDATE recipe SET current_state = $1 WHERE id = $2", state, id)
		if err != nil {
			return fmt.Errorf("updating recipe state: %w", err)
		}

		return nil
	})
}

func (d *sqlRecipeDriver) Find(filter *models.SearchFilter, page int64, count int64) (*[]models.RecipeCompact, int64, error) {
	states := filter.States
	if len(states) == 0 {
		states = append(states, "active")
	}

	whereStmt, whereArgs, err := sqlx.In("WHERE r.current_state IN (?)", states)
	if err != nil {
		return nil, 0, err
	}

	const appendFmtStr = " AND (%s)"

	if fieldsStmt, fieldsArgs := getFieldsStmt(filter.Query, filter.Fields, d.adapter); fieldsStmt != "" {
		whereStmt += fmt.Sprintf(appendFmtStr, fieldsStmt)
		whereArgs = append(whereArgs, fieldsArgs...)
	}

	tagsStmt, tagsArgs, err := getTagsStmt(filter.Tags)
	if err != nil {
		return nil, 0, err
	}
	if tagsStmt != "" {
		whereStmt += fmt.Sprintf(appendFmtStr, tagsStmt)
		whereArgs = append(whereArgs, tagsArgs...)
	}

	if picturesStmt := getPicturesStmt(filter.WithPictures); picturesStmt != "" {
		whereStmt += fmt.Sprintf(appendFmtStr, picturesStmt)
	}

	var total int64
	countStmt := d.Db.Rebind("SELECT count(r.id) FROM recipe AS r " + whereStmt)
	if err := sqlx.Get(d.Db, &total, countStmt, whereArgs...); err != nil {
		return nil, 0, err
	}

	orderStmt := getOrderStmt(filter.SortBy, filter.SortDir)

	// Build the offset and limit
	limitStmt := ""
	limitArgs := make([]any, 0)
	if count > 0 {
		limitStmt = "LIMIT ? OFFSET ?"
		limitArgs = append(limitArgs, count, count*(page-1))
	}

	combinedStr :=
		"SELECT r.id, r.name, r.current_state, r.created_at, r.modified_at, COALESCE(g.rating, 0) AS avg_rating, COALESCE(i.thumbnail_url, '') AS thumbnail_url " +
			"FROM recipe AS r " +
			"LEFT OUTER JOIN recipe_rating as g ON r.id = g.recipe_id " +
			"LEFT OUTER JOIN recipe_image as i ON r.image_id = i.id " +
			fmt.Sprintf("%s %s %s", whereStmt, orderStmt, limitStmt)

	selectArgs := append(whereArgs, limitArgs...)
	selectStmt := d.Db.Rebind(combinedStr)

	recipes := make([]models.RecipeCompact, 0)
	if err = sqlx.Select(d.Db, &recipes, selectStmt, selectArgs...); err != nil {
		return nil, 0, err
	}

	return &recipes, total, nil
}

func getFieldsStmt(query string, fields []models.SearchField, adapter sqlRecipeDriverAdapter) (string, []any) {
	if query == "" {
		return "", nil
	}
	
	// If the filter didn't specify the fields to search on, use all of them
	filterFields := fields
	if len(filterFields) == 0 {
		filterFields = supportedSearchFields[:]
	}

	return adapter.GetSearchFields(filterFields, query)
}

func getTagsStmt(tags []string) (string, []any, error) {
	if len(tags) == 0 {
		return "", nil, nil
	}
	
	return sqlx.In("EXISTS (SELECT 1 FROM recipe_tag AS t WHERE t.recipe_id = r.id AND t.tag IN (?))", tags)
}

func getPicturesStmt(withPictures *bool) string {
	if withPictures == nil {
		return ""
	}

	prefix := ""
	if !*withPictures {
		prefix = "NOT "
	}
	return prefix + "EXISTS (SELECT 1 FROM recipe_image AS t WHERE t.recipe_id = r.id)"
}

func getOrderStmt(sortBy models.SortBy, sortDir models.SortDir) string {
	stmt := "ORDER BY "
	switch sortBy {
	case models.SortByID:
		stmt += "r.id"
	case models.SortByCreated:
		stmt += "r.created_at"
	case models.SortByModified:
		stmt += "r.modified_at"
	case models.SortByRating:
		stmt += "avg_rating"
	case models.SortByRandom:
		stmt += "RANDOM()"
	case models.SortByName:
		fallthrough
	default:
		stmt += "r.name"
	}
	if sortDir == models.Desc {
		stmt += " DESC"
	}

	// Need a special case for rating, since the way the execution plan works can
	// cause uncertain results due to many recipes having the same rating (ties).
	// By adding an additional sort to show recently modified recipes first,
	// this ensures a consistent result.
	if sortBy == models.SortByRating {
		stmt += ", r.modified_at DESC"
	}

	return stmt
}

func (d *sqlRecipeDriver) CreateTag(recipeID int64, tag string) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		return d.createTagImpl(recipeID, tag, db)
	})
}

func (*sqlRecipeDriver) createTagImpl(recipeID int64, tag string, db sqlx.Execer) error {
	_, err := db.Exec(
		"INSERT INTO recipe_tag (recipe_id, tag) VALUES ($1, $2)",
		recipeID, tag)
	return err
}

func (d *sqlRecipeDriver) DeleteAllTags(recipeID int64) error {
	return tx(d.Db, func(db sqlx.Ext) error {
		return d.deleteAllTagsImpl(recipeID, db)
	})
}

func (*sqlRecipeDriver) deleteAllTagsImpl(recipeID int64, db sqlx.Execer) error {
	_, err := db.Exec(
		"DELETE FROM recipe_tag WHERE recipe_id = $1",
		recipeID)
	return err
}

func (d *sqlRecipeDriver) ListTags(recipeID int64) (*[]string, error) {
	return get(d.Db, func(db sqlx.Queryer) (*[]string, error) {
		tags := make([]string, 0)
		if err := sqlx.Select(db, &tags, "SELECT tag FROM recipe_tag WHERE recipe_id = $1", recipeID); err != nil {
			return nil, err
		}

		return &tags, nil
	})
}