Skip to content

armgit / lyftr

Public Imported from GitHub
0

Self-hosted fitness tracker. Log workouts, track nutrition, and monitor progress. Docker deployment, mobile-friendly.

29.4 KB
Raw Blame
package stores

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"net"
	"sort"
	"strings"
	"sync"
	"sync/atomic"
	"time"

	"github.com/Cawlumm/lyftr-backend/models"
	"github.com/Cawlumm/lyftr-backend/oedb"
	"github.com/Cawlumm/lyftr-backend/utils"
)

// ExerciseStore owns all SQL for the (global, read-only) exercises catalog: the
// local cache of oedb rows, the scheduled sync that fills it, and the admin
// sync endpoint.
type ExerciseStore struct {
	db      *sql.DB
	catalog *oedb.Client
	// syncing is held for the length of a catalog pass, so the daily timer and the
	// admin endpoint can never run two imports against the one SQLite connection.
	syncing atomic.Bool
	// status is how recent passes went. syncing above stays the one running flag.
	statusMu sync.Mutex
	status   models.ExerciseCacheStatus
	// searchDownUntil (unix nanos, 0 = up) is when Search may next ask the catalog. See
	// searchAllowed.
	searchDownUntil atomic.Int64
	// searchTimeout bounds one live search call. A healthy catalog answers in well under
	// a second; a picker that hangs is worse than one that falls back.
	searchTimeout time.Duration
}

func NewExerciseStore(db *sql.DB) *ExerciseStore {
	return &ExerciseStore{db: db, searchTimeout: defaultSearchTimeout}
}

// ExerciseFilter holds the optional list filters (empty string = no filter).
type ExerciseFilter struct {
	Query, MuscleGroup, Category, Equipment string
	// Locale is the reader's language. Empty and malformed values mean English.
	Locale string
	Limit  int
	// Page is 1-based; zero and one both mean the first page. Paging is what lets
	// the pickers stop downloading the catalog to filter it in the browser.
	Page int
}

// offset converts the 1-based page into a SQL offset.
func (f ExerciseFilter) offset() int {
	if f.Page < 2 {
		return 0
	}
	return (f.Page - 1) * f.Limit
}

const defaultLocale = "en"

// NormalizeLocale is the one spelling of a locale: language lowercase, everything
// after the first '-' uppercase, as oedb's normalizeLocale writes it, because the
// rows stored here carry oedb's spelling. Empty or malformed input is English, the
// documented meaning of "no usable locale" rather than an error.
func NormalizeLocale(raw string) string {
	raw = strings.TrimSpace(raw)
	if len(raw) > 35 {
		return defaultLocale
	}
	subtags := strings.Split(raw, "-")
	for i, tag := range subtags {
		if tag == "" {
			return defaultLocale
		}
		for j := 0; j < len(tag); j++ {
			c := tag[j]
			isLetter := c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z'
			if !isLetter && (i == 0 || c < '0' || c > '9') {
				return defaultLocale
			}
		}
	}
	if n := len(subtags[0]); n < 2 || n > 8 {
		return defaultLocale
	}
	subtags[0] = strings.ToLower(subtags[0])
	for i := 1; i < len(subtags); i++ {
		subtags[i] = strings.ToUpper(subtags[i])
	}
	return strings.Join(subtags, "-")
}

// BaseLanguage is the language subtag of a normalized locale: es for es-MX.
func BaseLanguage(locale string) string {
	if i := strings.IndexByte(locale, '-'); i > 0 {
		return locale[:i]
	}
	return locale
}

// Which text an exercise shows is decided here and nowhere else: the reader's
// locale, then its base language, then the English base row, field by field. Every
// read that returns exercise text joins through translationJoin and selects
// localizedName / localizedDescription.
const (
	localizedName        = `COALESCE(NULLIF(tr.name, ''), e.name)`
	localizedDescription = `COALESCE(NULLIF(tr.description, ''), e.description)`
)

// translationJoin returns a LEFT JOIN, aliased tr, onto the single best translation
// row for each exercise e. English reads join nothing: the base row is English.
func translationJoin(locale string) (string, []any) {
	l := NormalizeLocale(locale)
	if l == defaultLocale {
		return ` LEFT JOIN exercise_translations tr ON 1 = 0`, nil
	}
	return ` LEFT JOIN exercise_translations tr ON tr.id = (
		SELECT t.id FROM exercise_translations t
		WHERE t.exercise_id = e.id AND t.locale IN (?, ?)
		ORDER BY t.locale = ? DESC LIMIT 1)`, []any{l, BaseLanguage(l), l}
}

func exerciseSelect(locale string) (string, []any) {
	join, args := translationJoin(locale)
	return `SELECT e.id, ` + localizedName + `, e.muscle_group, e.secondary_muscles, e.category, e.equipment, ` +
		localizedDescription + `, e.image_url FROM exercises e` + join, args
}

type scanner interface{ Scan(dest ...any) error }

func scanExercise(row scanner, e *models.Exercise) error {
	var secondaryRaw string
	if err := row.Scan(&e.ID, &e.Name, &e.MuscleGroup, &secondaryRaw, &e.Category, &e.Equipment, &e.Description, &e.ImageURL); err != nil {
		return err
	}
	json.Unmarshal([]byte(secondaryRaw), &e.SecondaryMuscles)
	if e.SecondaryMuscles == nil {
		e.SecondaryMuscles = []string{}
	}
	return nil
}

func (s *ExerciseStore) List(f ExerciseFilter) ([]models.Exercise, error) {
	locale := NormalizeLocale(f.Locale)
	sel, args := exerciseSelect(locale)
	q := sel + ` WHERE e.upstream_missing_at IS NULL`
	if f.Query != "" {
		like := "%" + f.Query + "%"
		q += " AND (e.name LIKE ? OR EXISTS (SELECT 1 FROM exercise_translations t WHERE t.exercise_id = e.id AND t.locale IN (?, ?) AND t.name LIKE ?))"
		args = append(args, like, locale, BaseLanguage(locale), like)
	}
	if f.MuscleGroup != "" {
		q += " AND e.muscle_group = ?"
		args = append(args, f.MuscleGroup)
	}
	if f.Category != "" {
		q += " AND e.category = ?"
		args = append(args, f.Category)
	}
	if f.Equipment != "" {
		q += " AND e.equipment = ?"
		args = append(args, f.Equipment)
	}
	q += " ORDER BY " + localizedName + " LIMIT ? OFFSET ?"
	args = append(args, f.Limit, f.offset())

	rows, err := s.db.Query(q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	exercises := []models.Exercise{}
	for rows.Next() {
		var e models.Exercise
		if err := scanExercise(rows, &e); err != nil {
			return nil, err
		}
		exercises = append(exercises, e)
	}
	return exercises, rows.Err()
}

// Get returns one exercise, or sql.ErrNoRows if not found.
func (s *ExerciseStore) Get(id int64, locale string) (models.Exercise, error) {
	var e models.Exercise
	sel, args := exerciseSelect(locale)
	if err := scanExercise(s.db.QueryRow(sel+` WHERE e.id = ?`, append(args, id)...), &e); err != nil {
		return models.Exercise{}, err
	}
	return e, nil
}

func (s *ExerciseStore) Count() (int, error) {
	var n int
	err := s.db.QueryRow(`SELECT COUNT(*) FROM exercises`).Scan(&n)
	return n, err
}

// Materialize records upstream catalog rows in the local exercises table and
// returns them as Lyftr exercises, each carrying its local integer id.
//
// This is what lets Lyftr query oedb live while keeping referential integrity.
// workout_exercises and program_exercises FK into exercises(id) with no cascade,
// so an exercise a user can see in the picker must already exist locally by the
// time they add it to a workout — otherwise the insert fails, or worse, succeeds
// against an id that means something else. Materializing on read closes that gap
// without a second round trip when the user picks.
//
// Rows are matched by oedb_id first and name second. The order matters: name is
// the only key legacy rows have (they predate oedb entirely and were seeded from
// free-exercise-db), while oedb_id is the only key that survives an upstream
// rename. Trying oedb_id first means a renamed exercise updates the row it
// already owns instead of inserting a duplicate under the new name.
//
// The base row is English and stays English. Each field oedb served in English
// goes into it; each field served in another language goes into that locale's
// exercise_translations row instead, so one user's Spanish search never rewrites
// what an English reader sees. locale is the language that was asked for, and the
// language the returned rows are shown in.
//
// Name matching only adopts a row that no upstream id owns yet, or whose upstream
// exercise has since been removed (wger's replaced_by: the history follows the
// exercise that replaced it). An upstream
// exercise whose name is already owned by another id, or that arrives translated
// with no English row to attach to, is skipped and left out of the result, because oedb names are not unique and re-pointing the row would re-point
// every workout that references it.
func (s *ExerciseStore) Materialize(items []oedb.Exercise, imageBase, locale string) ([]models.Exercise, error) {
	if len(items) == 0 {
		return []models.Exercise{}, nil
	}

	tx, err := s.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	locale = NormalizeLocale(locale)
	sel, selArgs := exerciseSelect(locale)

	byOedbID, err := tx.Prepare(`
		UPDATE exercises SET
		  name = COALESCE(?, name), muscle_group = ?, secondary_muscles = ?, category = ?,
		  equipment = ?, description = COALESCE(?, description), image_url = ?, slug = ?,
		  upstream_missing_at = NULL
		WHERE oedb_id = ?`)
	if err != nil {
		return nil, err
	}
	defer byOedbID.Close()

	byName, err := tx.Prepare(`
		INSERT INTO exercises (name, muscle_group, secondary_muscles, category, equipment, description, image_url, oedb_id, slug)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(name) DO UPDATE SET
		  muscle_group      = excluded.muscle_group,
		  secondary_muscles = excluded.secondary_muscles,
		  category          = excluded.category,
		  equipment         = excluded.equipment,
		  description       = COALESCE(?, description),
		  image_url         = excluded.image_url,
		  oedb_id           = excluded.oedb_id,
		  slug              = excluded.slug,
		  upstream_missing_at = NULL
		WHERE exercises.oedb_id IS NULL OR exercises.upstream_missing_at IS NOT NULL`)
	if err != nil {
		return nil, err
	}
	defer byName.Close()

	out := make([]models.Exercise, 0, len(items))
	for _, it := range items {
		secondary := it.SecondaryMuscles
		if secondary == nil {
			secondary = []string{}
		}
		secondaryJSON, err := json.Marshal(secondary)
		if err != nil {
			return nil, err
		}

		name, desc := it.Name(), it.Description()
		nameEN := servedInEnglish(it.Translation.NameLocale)
		descEN := servedInEnglish(it.Translation.InstructionsLocale)
		// nil leaves the base column alone: a field served in another language is
		// not the English text this column holds.
		var nameEnglish, descEnglish any
		if nameEN {
			nameEnglish = name
		}
		if descEN {
			descEnglish = desc
		}

		args := []any{
			nameEnglish, it.MuscleGroup, string(secondaryJSON), it.Category,
			it.Equipment, descEnglish, it.ImageURL(imageBase), it.Slug, it.ID,
		}
		res, err := byOedbID.Exec(args...)
		if utils.IsUniqueViolation(err) {
			// Moving the name would mean merging two rows that workouts point at.
			holderID, holderOedb, herr := nameHolder(tx, name)
			if herr != nil {
				return nil, herr
			}
			log.Printf("exercises: upstream %s renamed to %q, already held by local exercise %d (oedb_id %q); keeping its current name", it.ID, name, holderID, holderOedb)
			args[0] = nil
			res, err = byOedbID.Exec(args...)
		}
		if err != nil {
			return nil, err
		}
		n, err := res.RowsAffected()
		if err != nil {
			return nil, err
		}
		if n == 0 {
			if !nameEN {
				// No English row to hang the translation on, and none to invent: either
				// the English read skipped this exercise (its name is held by another
				// id) or oedb did not return it. Leave it out; the rest of the batch stands.
				log.Printf("exercises: skipping upstream %s: no English base row for its %q translation", it.ID, locale)
				continue
			}
			baseDesc := ""
			if descEN {
				baseDesc = desc
			}
			nameRes, err := byName.Exec(
				name, it.MuscleGroup, string(secondaryJSON), it.Category,
				it.Equipment, baseDesc, it.ImageURL(imageBase), it.ID, it.Slug,
				descEnglish,
			)
			if err != nil {
				return nil, err
			}
			adopted, err := nameRes.RowsAffected()
			if err != nil {
				return nil, err
			}
			if adopted == 0 {
				holderID, holderOedb, herr := nameHolder(tx, name)
				if herr != nil {
					return nil, herr
				}
				log.Printf("exercises: skipping upstream %s %q: name already held by local exercise %d (oedb_id %q)", it.ID, name, holderID, holderOedb)
				continue
			}
		}

		if locale != defaultLocale {
			if err := replaceTranslation(tx, it, locale, nameEN, descEN); err != nil {
				return nil, err
			}
		}

		var e models.Exercise
		row := tx.QueryRow(sel+` WHERE e.oedb_id = ?`, append(append([]any{}, selArgs...), it.ID)...)
		if err := scanExercise(row, &e); err != nil {
			return nil, err
		}
		out = append(out, e)
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}
	return out, nil
}

// nameHolder reports which local row holds name, for the log line when an upstream exercise cannot take it.
func nameHolder(tx *sql.Tx, name string) (id int64, oedbID string, err error) {
	err = tx.QueryRow(`SELECT id, COALESCE(oedb_id, '') FROM exercises WHERE name = ?`, name).Scan(&id, &oedbID)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, "", nil
	}
	return
}

// servedInEnglish reports whether a field oedb served is the English text. An empty
// locale is an oedb that predates name_locale, which only ever served English.
func servedInEnglish(served string) bool { return NormalizeLocale(served) == defaultLocale }

// replaceTranslation records what oedb served for the requested locale. It drops
// the rows for the requested locale and its base language first, so a translation
// upstream has since dropped (a new revision does that) stops being shown, then
// writes one row if any field really was served in another language. A field that
// fell back to English is stored empty so the read falls through to the base row.
func replaceTranslation(tx *sql.Tx, it oedb.Exercise, locale string, nameEN, descEN bool) error {
	var exerciseID int64
	if err := tx.QueryRow(`SELECT id FROM exercises WHERE oedb_id = ?`, it.ID).Scan(&exerciseID); err != nil {
		return err
	}
	if _, err := tx.Exec(
		`DELETE FROM exercise_translations WHERE exercise_id = ? AND locale IN (?, ?)`,
		exerciseID, locale, BaseLanguage(locale),
	); err != nil {
		return err
	}
	if nameEN && descEN {
		return nil
	}

	served, name, desc, origin := NormalizeLocale(it.Translation.NameLocale), "", "", "human"
	if nameEN {
		served = NormalizeLocale(it.Translation.InstructionsLocale)
	} else {
		name = it.Name()
		if it.Translation.NameMachineOrigin {
			origin = "machine"
		}
	}
	if !descEN {
		desc = it.Description()
		if it.Translation.InstructionsMachineOrigin {
			origin = "machine"
		}
	}
	_, err := tx.Exec(
		`INSERT INTO exercise_translations (exercise_id, locale, name, description, origin)
		 VALUES (?, ?, ?, ?, ?)
		 ON CONFLICT(exercise_id, locale) DO UPDATE SET
		   name = excluded.name, description = excluded.description, origin = excluded.origin`,
		exerciseID, served, name, desc, origin,
	)
	return err
}

// UseCatalog points this store at an upstream open-exercise-db instance.
//
// Optional by design: with no catalog the store answers from the local table
// alone, which is both the pre-oedb behaviour and the behaviour every test
// exercises. Nothing in Lyftr requires the network to work.
func (s *ExerciseStore) UseCatalog(c *oedb.Client) { s.catalog = c }

const (
	defaultSearchTimeout = 3 * time.Second
	// searchBackoff is how long Search stops asking a catalog that did not answer, so
	// one dead upstream costs one slow request, not one per keystroke.
	searchBackoff = time.Minute
)

// searchAllowed reports whether this search may call the catalog. After a failed call
// the answer is no until searchBackoff has passed. The first caller after that claims
// the retry, holding the rest off for as long as its own call can take, so a catalog
// that is still down is probed once, not by every request that arrives meanwhile.
func (s *ExerciseStore) searchAllowed() bool {
	until := s.searchDownUntil.Load()
	if until == 0 {
		return true
	}
	now := time.Now()
	if now.UnixNano() < until {
		return false
	}
	return s.searchDownUntil.CompareAndSwap(until, now.Add(s.searchTimeout+time.Second).UnixNano())
}

// searchFailed backs Search off the catalog when it gave no answer: a timeout or a
// connection that never completed. An answer that says no (a 4xx for one bad query,
// a 5xx) arrives fast, costs nothing to repeat and says nothing about the next query,
// and a caller that gave up on its own request says nothing about the catalog.
func (s *ExerciseStore) searchFailed(ctx context.Context, err error) {
	var netErr net.Error
	if ctx.Err() != nil || !(errors.Is(err, context.DeadlineExceeded) || errors.As(err, &netErr)) {
		s.searchDownUntil.Store(0)
		return
	}
	s.searchDownUntil.Store(time.Now().Add(searchBackoff).UnixNano())
	log.Printf("exercises: catalog is not answering, serving the local copy for %s: %v", searchBackoff, err)
}

// Search answers an exercise query from the upstream catalog, recording what it
// returns locally, and falls back to the local table when the catalog cannot be
// reached. A catalog that does not answer is left alone for searchBackoff, so the
// fallback is immediate rather than a timeout on every request.
//
// The fallback is not a nicety. oedb being down, slow, or rate-limiting must
// never stop someone logging a workout, and the local table already holds every
// exercise this instance has shown before — which, for an established install,
// is most of the ones its users search for.
func (s *ExerciseStore) Search(ctx context.Context, f ExerciseFilter) ([]models.Exercise, error) {
	if s.catalog == nil {
		return s.List(f)
	}

	if !s.searchAllowed() {
		return s.List(f)
	}

	items, err := s.fetchCatalog(ctx, f)
	if err != nil {
		s.searchFailed(ctx, err)
		log.Printf("exercises: catalog query failed, serving local snapshot: %v", err)
		return s.List(f)
	}

	if err := s.ensureEnglishBase(ctx, items); err != nil {
		s.searchFailed(ctx, err)
		log.Printf("exercises: could not fetch English base rows, serving local snapshot: %v", err)
		return s.List(f)
	}
	s.searchDownUntil.Store(0)

	out, err := s.Materialize(items, s.catalog.BaseURL(), f.Locale)
	if err != nil {
		log.Printf("exercises: could not record catalog results, serving local snapshot: %v", err)
		return s.List(f)
	}
	return out, nil
}

// ensureEnglishBase records the English row of any exercise that arrived with a
// translated name and is not held locally yet. Materialize refuses to invent an
// English base from translated text, so this has to happen first: the exercises are
// re-read by id with no locale, which is the only way to learn their English name.
func (s *ExerciseStore) ensureEnglishBase(ctx context.Context, items []oedb.Exercise) error {
	var missing []string
	for _, it := range items {
		if servedInEnglish(it.Translation.NameLocale) {
			continue
		}
		var held int
		err := s.db.QueryRow(`SELECT COUNT(*) FROM exercises WHERE oedb_id = ?`, it.ID).Scan(&held)
		if err != nil {
			return err
		}
		if held == 0 {
			missing = append(missing, it.ID)
		}
	}
	if len(missing) == 0 {
		return nil
	}

	ctx, cancel := context.WithTimeout(ctx, s.searchTimeout)
	defer cancel()
	for len(missing) > 0 {
		batch := missing[:min(len(missing), 100)]
		missing = missing[len(batch):]
		res, err := s.catalog.List(ctx, oedb.ListParams{IDs: batch, PerPage: len(batch)})
		if err != nil {
			return err
		}
		wanted := make(map[string]bool, len(batch))
		for _, id := range batch {
			wanted[id] = true
		}
		english := res.Exercises[:0]
		for _, it := range res.Exercises {
			if wanted[it.ID] {
				english = append(english, it)
			}
		}
		if _, err := s.Materialize(english, s.catalog.BaseURL(), defaultLocale); err != nil {
			return err
		}
	}
	return nil
}

// fetchCatalog picks between the paged endpoint and the bulk export.
//
// The picker asks for the whole catalog (limit=1000) to build its client-side
// cache, and oedb rejects per_page above 100 outright rather than clamping it.
// Rather than issue nine paged requests for that case, an unfiltered bulk read
// goes to the export endpoint, which returns everything at once. Anything
// filtered stays on List, where the filter is what keeps the response small.
func (s *ExerciseStore) fetchCatalog(ctx context.Context, f ExerciseFilter) ([]oedb.Exercise, error) {
	filtered := f.Query != "" || f.MuscleGroup != "" || f.Category != "" || f.Equipment != ""
	// The export takes no locale, so only an English read may use it. English is
	// sent as "" so its upstream URLs, and cache entries, are the same as before.
	locale := NormalizeLocale(f.Locale)
	if locale == defaultLocale {
		locale = ""
	}
	if !filtered && f.Limit > 100 && f.Page < 2 && locale == "" {
		// The export is ~300 KB and measured around 14s against the hosted
		// instance, so it gets a deadline sized for that. It is also cached
		// upstream-style for 900s, so only the first caller after a cold start
		// waits at all.
		ctx, cancel := context.WithTimeout(ctx, 45*time.Second)
		defer cancel()
		return s.catalog.Export(ctx)
	}

	// An interactive search gets a short deadline: a picker that hangs is worse
	// than one that falls back to the local snapshot immediately.
	ctx, cancel := context.WithTimeout(ctx, s.searchTimeout)
	defer cancel()

	// A filtered request for more than one page walks the pages. oedb caps
	// per_page at 100, so without this a caller asking for 1000 dumbbell
	// exercises would silently receive the first 100 and no indication that more
	// existed — which is what the local table used to answer in full.
	first := f.Page
	if first < 1 {
		first = 1
	}
	var out []oedb.Exercise
	for page := first; ; page++ {
		res, err := s.catalog.List(ctx, oedb.ListParams{
			Query:       f.Query,
			MuscleGroup: f.MuscleGroup,
			Category:    f.Category,
			Equipment:   f.Equipment,
			Locale:      locale,
			Page:        page,
			PerPage:     f.Limit - len(out),
		})
		if err != nil {
			return nil, err
		}
		out = append(out, res.Exercises...)
		if len(out) >= f.Limit || len(res.Exercises) == 0 {
			return out, nil
		}
		// res.Total counts the whole result set, not the remainder, so it only
		// terminates the walk once this page's offset has consumed it.
		if (page-first+1)*len(res.Exercises) >= res.Total {
			return out, nil
		}
	}
}

// ErrNoCatalog is returned by the cache-management operations on an instance with
// no upstream configured.
var ErrNoCatalog = errors.New("This server has no exercise catalog configured.")

// ErrCatalogUnreachable marks a refresh that failed reading the upstream catalog, so a
// caller can tell an oedb outage from a local database failure.
var ErrCatalogUnreachable = errors.New("exercise catalog unreachable")

// ErrSyncRunning is returned when a catalog pass is already in progress.
var ErrSyncRunning = errors.New("exercise sync already running")

// ErrCatalogShrank marks an English export that lists under half of the visible held
// exercises. It is indistinguishable from a truncated read, so the scheduled pass refuses it;
// an operator who knows the removal is real accepts it with a confirmed sync.
var ErrCatalogShrank = errors.New("exercise catalog shrank")

// CacheStatus reports how many catalog rows this instance currently holds and how the
// sync passes are going.
//
// After the first scheduled pass the table holds the whole catalog plus any legacy
// rows; before it, only what reads have had reason to record. A fresh install reports
// zero and is fully functional.
func (s *ExerciseStore) CacheStatus() (models.ExerciseCacheStatus, error) {
	n, err := s.Count()
	if err != nil {
		return models.ExerciseCacheStatus{}, err
	}
	// Flag first: recordPass runs before it is released, so a reader that sees it down
	// is guaranteed to read the finished pass's outcome below.
	syncing := s.syncing.Load()
	s.statusMu.Lock()
	st := s.status
	s.statusMu.Unlock()
	st.Count = n
	st.Syncing = syncing
	st.LastFailed = st.LastFailure != nil
	return st, nil
}

// RefreshCached is the scheduled catalog pass run on demand: it imports every upstream
// exercise in English and re-reads every held one in each other language. The int is
// the number of exercises the English pass recorded. The pass outlives ctx: a caller that
// gives up (a proxy timing out at 60s) must not abort a half-written import and record a
// false "unreachable".
func (s *ExerciseStore) RefreshCached(ctx context.Context) (int, error) {
	return s.syncCatalog(context.WithoutCancel(ctx))
}

// RefreshCachedConfirmed is RefreshCached for an operator who has checked that the upstream
// catalog really did lose most of its exercises: this one pass accepts a shrunken export
// instead of failing with ErrCatalogShrank. An empty export is still refused.
func (s *ExerciseStore) RefreshCachedConfirmed(ctx context.Context) (int, error) {
	return s.syncCatalogWith(context.WithoutCancel(ctx), true)
}

// refresh brings one language up to date. English imports every exercise in the bulk
// export, oedb.MaxPerPage rows per transaction so a pass never holds the single
// connection for long. Any other language re-reads every held row by id in oedb's
// batch size, because the export is English-only and a list call is the only way to
// ask for a translation.
//
// It stops at the first failed batch and reports how many rows it did apply: the
// rows written so far are valid, and the caller decides whether a partial pass counts.
//
// It reads through Revalidating, because a re-read that a cache can answer re-applies
// what this instance already holds.
func (s *ExerciseStore) refresh(ctx context.Context, locale string, acceptShrink bool) (int, error) {
	if s.catalog == nil {
		return 0, ErrNoCatalog
	}
	cat := s.catalog.Revalidating()

	if servedInEnglish(locale) {
		items, err := cat.Export(ctx)
		if err != nil {
			return 0, fmt.Errorf("%w: %w", ErrCatalogUnreachable, err)
		}
		done := 0
		for start := 0; start < len(items); start += oedb.MaxPerPage {
			out, err := s.Materialize(items[start:min(start+oedb.MaxPerPage, len(items))], s.catalog.BaseURL(), defaultLocale)
			if err != nil {
				return done, err
			}
			done += len(out)
		}
		// Only after the whole export imported: a half-read export must not hide anything.
		if err := s.markUpstreamMissing(items, acceptShrink); err != nil {
			return done, err
		}
		return done, nil
	}

	held, err := s.cachedOedbIDs()
	if err != nil {
		return 0, err
	}

	ids := make([]string, 0, len(held))
	for id := range held {
		ids = append(ids, id)
	}
	sort.Strings(ids)

	done := 0
	for start := 0; start < len(ids); start += oedb.MaxPerPage {
		end := min(start+oedb.MaxPerPage, len(ids))
		res, err := cat.List(ctx, oedb.ListParams{IDs: ids[start:end], Locale: locale, PerPage: oedb.MaxPerPage})
		if err != nil {
			return done, fmt.Errorf("%w: %w", ErrCatalogUnreachable, err)
		}
		out, err := s.Materialize(res.Exercises, s.catalog.BaseURL(), locale)
		if err != nil {
			return done, err
		}
		done += len(out)
	}
	return done, nil
}

func (s *ExerciseStore) cachedOedbIDs() (map[string]bool, error) {
	rows, err := s.db.Query(`SELECT oedb_id FROM exercises WHERE oedb_id IS NOT NULL AND oedb_id != '' AND upstream_missing_at IS NULL`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	held := map[string]bool{}
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		held[id] = true
	}
	return held, rows.Err()
}

// markUpstreamMissing hides every held upstream row the complete English export no longer
// lists. oedb has no deletion log, so absence from a whole export is the only signal, and
// a truncated export looks exactly like a mass removal: when the export holds under half
// as many exercises as the visible held rows, nothing is marked and the pass fails with
// ErrCatalogShrank so it is retried rather than trusted, unless acceptShrink says an
// operator confirmed the removal. An empty export is never accepted.
// Rows are never deleted, because workout_exercises and
// program_exercises reference them with no cascade. One statement in one transaction, so
// the write lock is held for a single scan of the table.
func (s *ExerciseStore) markUpstreamMissing(items []oedb.Exercise, acceptShrink bool) error {
	ids := make([]string, len(items))
	for i, it := range items {
		ids[i] = it.ID
	}
	idsJSON, err := json.Marshal(ids)
	if err != nil {
		return err
	}
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	var held int
	if err := tx.QueryRow(`SELECT COUNT(*) FROM exercises WHERE oedb_id IS NOT NULL AND oedb_id != '' AND upstream_missing_at IS NULL`).Scan(&held); err != nil {
		return err
	}
	if held > 0 && (len(ids) == 0 || (2*len(ids) < held && !acceptShrink)) {
		log.Printf("exercises: upstream export lists %d exercises against %d held; not marking any as removed", len(ids), held)
		if len(ids) == 0 {
			return fmt.Errorf("%w: export is empty against %d held; marking none as removed", ErrCatalogUnreachable, held)
		}
		return fmt.Errorf("%w: export lists %d exercises against %d held; marking none as removed", ErrCatalogShrank, len(ids), held)
	}
	res, err := tx.Exec(`UPDATE exercises SET upstream_missing_at = CURRENT_TIMESTAMP
		WHERE oedb_id IS NOT NULL AND oedb_id != '' AND upstream_missing_at IS NULL
		  AND oedb_id NOT IN (SELECT value FROM json_each(?))`, string(idsJSON))
	if err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n > 0 {
		log.Printf("exercises: %d exercises no longer in the upstream catalog; hidden from search, kept for history", n)
		// A cached search answer still lists them, and recording it would un-hide them
		// until the next pass.
		s.catalog.DropCache()
	}
	return nil
}

// PrimeQuery materializes whatever the catalog returns for one search term.
//
// This is how a caller that needs specific exercises to exist locally — demo data
// building a routine, say — gets them, so it does not wait for the scheduled pass.
func (s *ExerciseStore) PrimeQuery(ctx context.Context, query string) error {
	if s.catalog == nil {
		return ErrNoCatalog
	}
	_, err := s.Search(ctx, ExerciseFilter{Query: query, Limit: 25})
	return err
}