<commit_before>package mpdb

import (
	"database/sql"
	"github.com/kierdavis/mealplanner/mpdata"
)

const SearchTextFunc = "CONCAT(meal.name, ' ', meal.recipe, ' ', IFNULL((SELECT GROUP_CONCAT(tag.tag SEPARATOR ' ') FROM tag WHERE tag.mealid = meal.id), ''))"

// SQL statement for listing meals.
const ListMealsSQL = "SELECT meal.id, meal.name, meal.recipe, meal.favourite FROM meal"

// SQL statement for listing meals sorted by name.
const ListMealsByNameSQL = ListMealsSQL + " ORDER BY meal.name"

const CreateSearchPatternsTableSQL = "CREATE TEMPORARY TABLE search_patterns (pattern VARCHAR(255))"

const InsertSearchPatternSQL = "INSERT INTO search_patterns VALUES (?)"

const SearchMealsSQL = "SELECT meal.id, meal.name, meal.recipe, meal.favourite FROM meal INNER JOIN search_patterns ON ((meal.name LIKE search_patterns.pattern) OR (meal.recipe LIKE search_patterns.pattern))"

const SearchMealsByNameSQL = SearchMealsSQL + " ORDER BY meal.name"

const DropSearchPatternsTableSQL = "DROP TABLE search_patterns"

// SQL statement for fetching information about a meal.
const GetMealSQL = "SELECT meal.name, meal.recipe, meal.favourite FROM meal WHERE meal.id = ?"

// SQL statement for fetching tags associated with a meal.
const GetMealTagsSQL = "SELECT tag.tag FROM tag WHERE tag.mealid = ?"

const UpdateSearchTextSQL = "UPDATE meal SET meal.searchtext = " + SearchTextFunc + " WHERE meal.id = ?"

// SQL statement for adding a meal.
const AddMealSQL = "INSERT INTO meal VALUES (NULL, ?, ?, ?, '')"

// SQL statement for updating the information about a meal.
const UpdateMealSQL = "UPDATE meal SET meal.name = ?, meal.recipe = ?, meal.favourite = ? WHERE meal.id = ?"

// SQL statement for deleting all tags associated with a meal.
const DeleteMealTagsSQL = "DELETE FROM tag WHERE tag.mealid = ?"

// SQL statement for adding a tag to a meal.
const AddMealTagSQL = "INSERT INTO tag VALUES (?, ?)"

// SQL statement for testing whether a meal is marked as a favourite.
const IsFavouriteSQL = "SELECT meal.favourite FROM meal WHERE meal.id = ?"

// SQL statement to set the "favourite" status of a meal.
const SetFavouriteSQL = "UPDATE meal SET meal.favourite = ? WHERE meal.id = ?"

// SQL statement to delete a meal.
const DeleteMealSQL = "DELETE FROM meal WHERE meal.id = ?"

// SQL statement to list all tags in the database.
const ListAllTagsSQL = "SELECT DISTINCT tag.tag FROM tag"

// SQL statement to list all tags in the database sorted by name.
const ListAllTagsByNameSQL = "SELECT DISTINCT tag.tag FROM tag ORDER BY tag.tag ASC"

// ListMeals fetches and returns a list of all meals in the database. If the
// parameter 'sortByName' is true, the meals are sorted in alphabetical order
// by name.
func ListMeals(q Queryable, sortByName bool) (meals []*mpdata.Meal, err error) {
	var query string
	if sortByName {
		query = ListMealsByNameSQL
	} else {
		query = ListMealsSQL
	}
	
	return readMeals(q, query)
}

// ListMealsWithTags fetches and returns a list of all meals in the database
// with their associated tags. If the parameter 'sortByName' is true, the meals
// are sorted in alphabetical order by name.
func ListMealsWithTags(q Queryable, sortByName bool) (mts []mpdata.MealWithTags, err error) {
	meals, err := ListMeals(q, sortByName)
	if err != nil {
		return nil, err
	}
	
	return AttachMealTags(q, meals)
}

func insertSearchWords(q Queryable, words []string) (err error) {
	stmt, err := q.Prepare(InsertSearchPatternSQL)
	if err != nil {
		return err
	}
	defer stmt.Close()
	
	for _, word := range words {
		_, err = stmt.Exec("%" + word + "%")
		if err != nil {
			return err
		}
	}
	
	return nil
}

func SearchMeals(q Queryable, words []string, sortByName bool) (meals []*mpdata.Meal, err error) {
	_, err = q.Exec(CreateSearchPatternsTableSQL)
	if err != nil {
		return nil, err
	}
	
	err = insertSearchWords(q, words)
	if err != nil {
		return nil, err
	}
	
	var query string
	if sortByName {
		query = SearchMealsByNameSQL
	} else {
		query = SearchMealsSQL
	}
	
	meals, err = readMeals(q, query)
	if err != nil {
		return nil, err
	}
	
	_, err = q.Exec(DropSearchPatternsTableSQL)
	if err != nil {
		return nil, err
	}
	
	return meals, nil
}

func SearchMealsWithTags(q Queryable, words []string, sortByName bool) (mts []mpdata.MealWithTags, err error) {
	meals, err := SearchMeals(q, words, sortByName)
	if err != nil {
		return nil, err
	}
	
	return AttachMealTags(q, meals)
}

func readMeals(q Queryable, query string) (meals []*mpdata.Meal, err error) {
	rows, err := q.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	for rows.Next() {
		meal := &mpdata.Meal{}

		err = rows.Scan(&meal.ID, &meal.Name, &meal.RecipeURL, &meal.Favourite)
		if err != nil {
			return nil, err
		}

		meals = append(meals, meal)
	}

	err = rows.Err()
	if err != nil {
		return nil, err
	}

	return meals, nil
}

func AttachMealTags(q Queryable, meals []*mpdata.Meal) (mts []mpdata.MealWithTags, err error) {
	getTagsStmt, err := q.Prepare(GetMealTagsSQL)
	if err != nil {
		return nil, err
	}
	defer getTagsStmt.Close()

	for _, meal := range meals {
		tags, err := getMealTagsPrepared(getTagsStmt, meal.ID)
		if err != nil {
			return nil, err
		}<commit_msg>Change search to be logical AND of words instead of OR<commit_after>package mpdb

import (
	"database/sql"
	"github.com/kierdavis/mealplanner/mpdata"
)

const SearchTextFunc = "CONCAT(meal.name, ' ', meal.recipe, ' ', IFNULL((SELECT GROUP_CONCAT(tag.tag SEPARATOR ' ') FROM tag WHERE tag.mealid = meal.id), ''))"

// SQL statement for listing meals.
const ListMealsSQL = "SELECT meal.id, meal.name, meal.recipe, meal.favourite FROM meal"

// SQL statement for listing meals sorted by name.
const ListMealsByNameSQL = ListMealsSQL + " ORDER BY meal.name"

const CreateSearchPatternsTableSQL = "CREATE TEMPORARY TABLE search_patterns (pattern VARCHAR(255))"

const InsertSearchPatternSQL = "INSERT INTO search_patterns VALUES (?)"

const DropSearchPatternsTableSQL = "DROP TABLE search_patterns"

// SQL statement for fetching information about a meal.
const GetMealSQL = "SELECT meal.name, meal.recipe, meal.favourite FROM meal WHERE meal.id = ?"

// SQL statement for fetching tags associated with a meal.
const GetMealTagsSQL = "SELECT tag.tag FROM tag WHERE tag.mealid = ?"

const UpdateSearchTextSQL = "UPDATE meal SET meal.searchtext = " + SearchTextFunc + " WHERE meal.id = ?"

// SQL statement for adding a meal.
const AddMealSQL = "INSERT INTO meal VALUES (NULL, ?, ?, ?, '')"

// SQL statement for updating the information about a meal.
const UpdateMealSQL = "UPDATE meal SET meal.name = ?, meal.recipe = ?, meal.favourite = ? WHERE meal.id = ?"

// SQL statement for deleting all tags associated with a meal.
const DeleteMealTagsSQL = "DELETE FROM tag WHERE tag.mealid = ?"

// SQL statement for adding a tag to a meal.
const AddMealTagSQL = "INSERT INTO tag VALUES (?, ?)"

// SQL statement for testing whether a meal is marked as a favourite.
const IsFavouriteSQL = "SELECT meal.favourite FROM meal WHERE meal.id = ?"

// SQL statement to set the "favourite" status of a meal.
const SetFavouriteSQL = "UPDATE meal SET meal.favourite = ? WHERE meal.id = ?"

// SQL statement to delete a meal.
const DeleteMealSQL = "DELETE FROM meal WHERE meal.id = ?"

// SQL statement to list all tags in the database.
const ListAllTagsSQL = "SELECT DISTINCT tag.tag FROM tag"

// SQL statement to list all tags in the database sorted by name.
const ListAllTagsByNameSQL = "SELECT DISTINCT tag.tag FROM tag ORDER BY tag.tag ASC"

// ListMeals fetches and returns a list of all meals in the database. If the
// parameter 'sortByName' is true, the meals are sorted in alphabetical order
// by name.
func ListMeals(q Queryable, sortByName bool) (meals []*mpdata.Meal, err error) {
	var query string
	if sortByName {
		query = ListMealsByNameSQL
	} else {
		query = ListMealsSQL
	}
	
	rows, err := q.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	return readMeals(rows)
}

// ListMealsWithTags fetches and returns a list of all meals in the database
// with their associated tags. If the parameter 'sortByName' is true, the meals
// are sorted in alphabetical order by name.
func ListMealsWithTags(q Queryable, sortByName bool) (mts []mpdata.MealWithTags, err error) {
	meals, err := ListMeals(q, sortByName)
	if err != nil {
		return nil, err
	}
	
	return AttachMealTags(q, meals)
}

func SearchMeals(q Queryable, words []string, sortByName bool) (meals []*mpdata.Meal, err error) {
	query := "SELECT meal.id, meal.name, meal.recipe, meal.favourite FROM meal"
	conjuctive := "WHERE"
	args := make([]interface{}, len(words))
	for i, word := range words {
		query += " " + conjuctive + " meal.searchtext LIKE ?"
		args[i] = "%" + word + "%"
		conjuctive = "AND"
	}
	
	if sortByName {
		query += " ORDER BY meal.name"
	}
	
	rows, err := q.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	
	return readMeals(rows)
}

func SearchMealsWithTags(q Queryable, words []string, sortByName bool) (mts []mpdata.MealWithTags, err error) {
	meals, err := SearchMeals(q, words, sortByName)
	if err != nil {
		return nil, err
	}
	
	return AttachMealTags(q, meals)
}

func readMeals(rows *sql.Rows) (meals []*mpdata.Meal, err error) {
	for rows.Next() {
		meal := &mpdata.Meal{}

		err = rows.Scan(&meal.ID, &meal.Name, &meal.RecipeURL, &meal.Favourite)
		if err != nil {
			return nil, err
		}

		meals = append(meals, meal)
	}

	err = rows.Err()
	if err != nil {
		return nil, err
	}

	return meals, nil
}

func AttachMealTags(q Queryable, meals []*mpdata.Meal) (mts []mpdata.MealWithTags, err error) {
	getTagsStmt, err := q.Prepare(GetMealTagsSQL)
	if err != nil {
		return nil, err
	}
	defer getTagsStmt.Close()

	for _, meal := range meals {
		tags, err := getMealTagsPrepared(getTagsStmt, meal.ID)
		if err != nil {
			return nil, err
		}
{'fraction_non_alphanumeric': 0.11071789686552073, 'fraction_numerical': 0.0006066734074823053, 'mean_word_length': 3.9013875123885033, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4756370', 'n_tokens_mistral': 3448, 'n_tokens_neox': 3265, 'n_words': 1124}