package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	_ "modernc.org/sqlite" // registers the "sqlite" driver, CGO-free

	"go-places-api/internal/domain"
)

// SQLStore is the SQLite-backed implementation of Store.
type SQLStore struct {
	db *sql.DB
}

var _ Store = (*SQLStore)(nil)

// OpenSQL opens the SQLite database at dsn and applies the schema. The dsn is a
// SQLite connection string such as "file:go-places.db?_pragma=foreign_keys(1)".
func OpenSQL(dsn string) (*SQLStore, error) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("store: open database: %w", err)
	}

	// SQLite serialises writers. A single connection avoids "database is
	// locked" errors under concurrent uploads without needing a write mutex.
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(time.Hour)

	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("store: ping database: %w", err)
	}

	s := &SQLStore{db: db}
	if err := s.migrate(context.Background()); err != nil {
		db.Close()
		return nil, err
	}
	return s, nil
}

// Close releases the underlying database handle.
func (s *SQLStore) Close() error { return s.db.Close() }

// DB exposes the handle for health checks.
func (s *SQLStore) DB() *sql.DB { return s.db }

const schema = `
CREATE TABLE IF NOT EXISTS users (
    id         TEXT PRIMARY KEY,
    username   TEXT NOT NULL DEFAULT '',
    email      TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS places (
    id                 TEXT PRIMARY KEY,
    title              TEXT NOT NULL,
    description        TEXT NOT NULL DEFAULT '',
    latitude           REAL,
    longitude          REAL,
    link_radius_meters REAL NOT NULL DEFAULT 150,
    creator_id         TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at         TEXT NOT NULL,
    updated_at         TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_places_creator ON places(creator_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_places_geo    ON places(latitude, longitude);

CREATE TABLE IF NOT EXISTS images (
    id              TEXT PRIMARY KEY,
    place_id        TEXT NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    storage_key     TEXT NOT NULL,
    content_type    TEXT NOT NULL,
    byte_size       INTEGER NOT NULL,
    width           INTEGER NOT NULL DEFAULT 0,
    height          INTEGER NOT NULL DEFAULT 0,
    caption         TEXT NOT NULL DEFAULT '',
    latitude        REAL,
    longitude       REAL,
    taken_at        TEXT,
    camera_make     TEXT NOT NULL DEFAULT '',
    camera_model    TEXT NOT NULL DEFAULT '',
    analysis_status TEXT NOT NULL DEFAULT 'pending',
    analysis_error  TEXT NOT NULL DEFAULT '',
    created_at      TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_images_place    ON images(place_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_images_created  ON images(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_images_geo      ON images(latitude, longitude);

CREATE TABLE IF NOT EXISTS badges (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id   TEXT NOT NULL REFERENCES images(id) ON DELETE CASCADE,
    label      TEXT NOT NULL,
    display    TEXT NOT NULL DEFAULT '',
    confidence REAL NOT NULL DEFAULT 0,
    source     TEXT NOT NULL DEFAULT '',
    UNIQUE(image_id, label, source)
);
CREATE INDEX IF NOT EXISTS idx_badges_image ON badges(image_id);

CREATE TABLE IF NOT EXISTS comments (
    id         TEXT PRIMARY KEY,
    place_id   TEXT NOT NULL REFERENCES places(id) ON DELETE CASCADE,
    content    TEXT NOT NULL,
    creator_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_comments_place ON comments(place_id, created_at);

-- Evidence that two pictures were taken at the same place. Both directions are
-- stored so a link is discoverable from either picture.
CREATE TABLE IF NOT EXISTS image_links (
    image_id        TEXT NOT NULL REFERENCES images(id) ON DELETE CASCADE,
    related_image_id TEXT NOT NULL REFERENCES images(id) ON DELETE CASCADE,
    distance_meters REAL NOT NULL DEFAULT 0,
    reason          TEXT NOT NULL DEFAULT '',
    created_at      TEXT NOT NULL,
    PRIMARY KEY (image_id, related_image_id)
);
CREATE INDEX IF NOT EXISTS idx_image_links_related ON image_links(related_image_id);
`

func (s *SQLStore) migrate(ctx context.Context) error {
	if _, err := s.db.ExecContext(ctx, schema); err != nil {
		return fmt.Errorf("store: apply schema: %w", err)
	}
	return nil
}

// ---------------------------------------------------------------- places ---

func (s *SQLStore) CreatePlace(ctx context.Context, p domain.Place) (domain.Place, error) {
	if p.ID == "" {
		p.ID = newID()
	}
	now := time.Now().UTC()
	p.CreatedAt = now
	// Timestamps are stored as RFC3339 so the schema stays portable to engines
	// without a native timestamp type.
	_, err := s.db.ExecContext(ctx, `
        INSERT INTO places (id, title, description, latitude, longitude,
                            link_radius_meters, creator_id, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		p.ID, p.Title, p.Description,
		nullableFloat(p.Coordinate, func(c domain.Coordinate) float64 { return c.Latitude }),
		nullableFloat(p.Coordinate, func(c domain.Coordinate) float64 { return c.Longitude }),
		p.LinkRadiusMeters, p.Creator.ID,
		formatTime(p.CreatedAt), formatTime(now),
	)
	if err != nil {
		return domain.Place{}, wrapWrite(err, "place")
	}
	return s.GetPlace(ctx, p.ID)
}

func (s *SQLStore) GetPlace(ctx context.Context, id string) (domain.Place, error) {
	row := s.db.QueryRowContext(ctx, `
        SELECT p.id, p.title, p.description, p.latitude, p.longitude,
               p.link_radius_meters, p.created_at,
               u.id, u.username, u.email
        FROM places p
        JOIN users u ON u.id = p.creator_id
        WHERE p.id = ?`, id)

	p, err := scanPlace(row)
	if err != nil {
		return domain.Place{}, err
	}

	images, err := s.ListImages(ctx, ImageFilter{PlaceID: id})
	if err != nil {
		return domain.Place{}, err
	}
	comments, err := s.ListComments(ctx, id)
	if err != nil {
		return domain.Place{}, err
	}
	p.Images = images
	p.Comments = comments
	return p, nil
}

func (s *SQLStore) UpdatePlace(ctx context.Context, p domain.Place) (domain.Place, error) {
	res, err := s.db.ExecContext(ctx, `
        UPDATE places
        SET title = ?, description = ?, latitude = ?, longitude = ?,
            link_radius_meters = ?, updated_at = ?
        WHERE id = ?`,
		p.Title, p.Description,
		nullableFloat(p.Coordinate, func(c domain.Coordinate) float64 { return c.Latitude }),
		nullableFloat(p.Coordinate, func(c domain.Coordinate) float64 { return c.Longitude }),
		p.LinkRadiusMeters, formatTime(time.Now().UTC()), p.ID,
	)
	if err != nil {
		return domain.Place{}, wrapWrite(err, "place")
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return domain.Place{}, ErrNotFound
	}
	return s.GetPlace(ctx, p.ID)
}

func (s *SQLStore) ListPlaces(ctx context.Context, limit, offset int) ([]domain.Place, error) {
	query := `
        SELECT p.id, p.title, p.description, p.latitude, p.longitude,
               p.link_radius_meters, p.created_at,
               u.id, u.username, u.email
        FROM places p
        JOIN users u ON u.id = p.creator_id
        ORDER BY p.created_at DESC`
	args := []any{}
	if limit > 0 {
		query += " LIMIT ?"
		args = append(args, limit)
		if offset > 0 {
			query += " OFFSET ?"
			args = append(args, offset)
		}
	}

	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("store: list places: %w", err)
	}
	defer rows.Close()

	var out []domain.Place
	for rows.Next() {
		p, err := scanPlace(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

func (s *SQLStore) DeletePlace(ctx context.Context, id string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM places WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("store: delete place: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// ---------------------------------------------------------------- images ---

func (s *SQLStore) CreateImage(ctx context.Context, img domain.Image) (domain.Image, error) {
	if img.ID == "" {
		img.ID = newID()
	}
	if img.AnalysisStatus == "" {
		img.AnalysisStatus = domain.AnalysisPending
	}
	if img.CreatedAt.IsZero() {
		img.CreatedAt = time.Now().UTC()
	}

	_, err := s.db.ExecContext(ctx, `
        INSERT INTO images (id, place_id, storage_key, content_type, byte_size,
                            width, height, caption, latitude, longitude, taken_at,
                            camera_make, camera_model, analysis_status, analysis_error,
                            created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		img.ID, img.PlaceID, img.StorageKey, img.ContentType, img.ByteSize,
		img.Width, img.Height, img.Caption,
		nullableFloat(img.Coordinate, func(c domain.Coordinate) float64 { return c.Latitude }),
		nullableFloat(img.Coordinate, func(c domain.Coordinate) float64 { return c.Longitude }),
		nullableTime(img.TakenAt),
		img.CameraMake, img.CameraModel, string(img.AnalysisStatus), img.AnalysisError,
		formatTime(img.CreatedAt),
	)
	if err != nil {
		return domain.Image{}, wrapWrite(err, "image")
	}

	if len(img.Badges) > 0 {
		if err := s.ReplaceBadges(ctx, img.ID, img.Badges); err != nil {
			return domain.Image{}, err
		}
	}
	return s.GetImage(ctx, img.ID)
}

func (s *SQLStore) GetImage(ctx context.Context, id string) (domain.Image, error) {
	row := s.db.QueryRowContext(ctx, imageSelect+` WHERE i.id = ?`, id)
	img, err := scanImage(row)
	if err != nil {
		return domain.Image{}, err
	}
	badges, err := s.badgesFor(ctx, []string{id})
	if err != nil {
		return domain.Image{}, err
	}
	img.Badges = badges[id]
	return img, nil
}

func (s *SQLStore) GetImageByStorageKey(ctx context.Context, key string) (domain.Image, error) {
	row := s.db.QueryRowContext(ctx, imageSelect+` WHERE i.storage_key = ? LIMIT 1`, key)
	img, err := scanImage(row)
	if err != nil {
		return domain.Image{}, err
	}
	badges, err := s.badgesFor(ctx, []string{img.ID})
	if err != nil {
		return domain.Image{}, err
	}
	img.Badges = badges[img.ID]
	return img, nil
}

// Ping verifies the database is reachable. It backs the readiness probe, which
// reports the service as unavailable without restarting it.
func (s *SQLStore) Ping(ctx context.Context) error { return s.db.PingContext(ctx) }

// PingContext is an alias for Ping kept for callers that name the method after
// database/sql's own convention.
func (s *SQLStore) PingContext(ctx context.Context) error { return s.db.PingContext(ctx) }

func (s *SQLStore) UpdateImage(ctx context.Context, img domain.Image) (domain.Image, error) {
	res, err := s.db.ExecContext(ctx, `
        UPDATE images
        SET caption = ?, latitude = ?, longitude = ?, taken_at = ?,
            camera_make = ?, camera_model = ?,
            analysis_status = ?, analysis_error = ?
        WHERE id = ?`,
		img.Caption,
		nullableFloat(img.Coordinate, func(c domain.Coordinate) float64 { return c.Latitude }),
		nullableFloat(img.Coordinate, func(c domain.Coordinate) float64 { return c.Longitude }),
		nullableTime(img.TakenAt),
		img.CameraMake, img.CameraModel,
		string(img.AnalysisStatus), img.AnalysisError, img.ID,
	)
	if err != nil {
		return domain.Image{}, wrapWrite(err, "image")
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return domain.Image{}, ErrNotFound
	}
	return s.GetImage(ctx, img.ID)
}

func (s *SQLStore) ListImages(ctx context.Context, f ImageFilter) ([]domain.Image, error) {
	var (
		where []string
		args  []any
	)
	if f.PlaceID != "" {
		where = append(where, "i.place_id = ?")
		args = append(args, f.PlaceID)
	}
	if f.CreatorID != "" {
		where = append(where, "p.creator_id = ?")
		args = append(args, f.CreatorID)
	}
	if f.Within != nil {
		// Pre-filter with the bounding box; the exact haversine test is applied
		// by the service layer afterwards.
		b := f.Within.BoundingBox()
		where = append(where,
			"i.latitude IS NOT NULL", "i.longitude IS NOT NULL",
			"i.latitude BETWEEN ? AND ?", "i.longitude BETWEEN ? AND ?")
		args = append(args, b.MinLat, b.MaxLat, b.MinLon, b.MaxLon)
	}

	query := imageSelect
	if len(where) > 0 {
		query += " WHERE " + strings.Join(where, " AND ")
	}
	query += " ORDER BY i.created_at DESC"
	if f.Limit > 0 {
		query += " LIMIT ?"
		args = append(args, f.Limit)
		if f.Offset > 0 {
			query += " OFFSET ?"
			args = append(args, f.Offset)
		}
	}

	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("store: list images: %w", err)
	}
	defer rows.Close()

	var out []domain.Image
	var ids []string
	for rows.Next() {
		img, err := scanImage(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, img)
		ids = append(ids, img.ID)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	badges, err := s.badgesFor(ctx, ids)
	if err != nil {
		return nil, err
	}
	for i := range out {
		out[i].Badges = badges[out[i].ID]
	}
	return out, nil
}

func (s *SQLStore) ImagesWithin(ctx context.Context, q NearbyQuery) ([]domain.Image, error) {
	return s.ListImages(ctx, ImageFilter{Within: &q})
}

func (s *SQLStore) ReplaceBadges(ctx context.Context, imageID string, badges []domain.Badge) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("store: begin badge transaction: %w", err)
	}
	defer tx.Rollback()

	if _, err := tx.ExecContext(ctx, `DELETE FROM badges WHERE image_id = ?`, imageID); err != nil {
		return fmt.Errorf("store: clear badges: %w", err)
	}
	for _, b := range badges {
		if _, err := tx.ExecContext(ctx, `
            INSERT OR REPLACE INTO badges (image_id, label, display, confidence, source)
            VALUES (?, ?, ?, ?, ?)`,
			imageID, b.Label, b.Display, b.Confidence, b.Source,
		); err != nil {
			return fmt.Errorf("store: insert badge: %w", err)
		}
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("store: commit badges: %w", err)
	}
	return nil
}

func (s *SQLStore) SetAnalysisState(ctx context.Context, imageID string, status domain.AnalysisStatus, failure string) error {
	res, err := s.db.ExecContext(ctx,
		`UPDATE images SET analysis_status = ?, analysis_error = ? WHERE id = ?`,
		string(status), failure, imageID)
	if err != nil {
		return fmt.Errorf("store: set analysis state: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s *SQLStore) DeleteImage(ctx context.Context, id string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("store: delete image: %w", err)
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

// -------------------------------------------------------------- image links ---

func (s *SQLStore) CreateImageLink(ctx context.Context, imageID, relatedImageID string, distanceMeters float64, reason string) error {
	if imageID == relatedImageID {
		return fmt.Errorf("%w: a picture cannot be linked to itself", ErrInvalidInput)
	}
	_, err := s.db.ExecContext(ctx, `
        INSERT INTO image_links (image_id, related_image_id, distance_meters, reason, created_at)
        VALUES (?, ?, ?, ?, ?)
        ON CONFLICT(image_id, related_image_id) DO UPDATE SET
            distance_meters = excluded.distance_meters,
            reason          = excluded.reason`,
		imageID, relatedImageID, distanceMeters, reason, formatTime(time.Now().UTC()))
	if err != nil {
		return wrapWrite(err, "image link")
	}
	return nil
}

func (s *SQLStore) ListImageLinks(ctx context.Context, imageID string) ([]domain.Link, error) {
	// The stored rows record the direction they were written in, so this query
	// flips the orientation for rows where the picture is the related end.
	rows, err := s.db.QueryContext(ctx, `
        SELECT image_id, related_image_id, distance_meters, reason,
               CASE WHEN image_id = ? THEN 0 ELSE 1 END AS direction
        FROM image_links
        WHERE image_id = ? OR related_image_id = ?
        ORDER BY distance_meters ASC, related_image_id ASC`, imageID, imageID, imageID)
	if err != nil {
		return nil, fmt.Errorf("store: list image links: %w", err)
	}
	defer rows.Close()

	var out []domain.Link
	for rows.Next() {
		var (
			l         domain.Link
			storedA   string
			storedB   string
			direction int
		)
		if err := rows.Scan(&storedA, &storedB, &l.DistanceMeters, &l.Reason, &direction); err != nil {
			return nil, fmt.Errorf("store: scan image link: %w", err)
		}
		if direction == 0 {
			l.ImageID, l.RelatedImageID = storedA, storedB
		} else {
			l.ImageID, l.RelatedImageID = storedB, storedA
		}
		l.SamePlace = l.Reason == "same-place"
		out = append(out, l)
	}
	return out, rows.Err()
}

// -------------------------------------------------------------- comments ---

func (s *SQLStore) AddComment(ctx context.Context, c domain.Comment) (domain.Comment, error) {
	if c.ID == "" {
		c.ID = newID()
	}
	if c.CreatedAt.IsZero() {
		c.CreatedAt = time.Now().UTC()
	}
	_, err := s.db.ExecContext(ctx, `
        INSERT INTO comments (id, place_id, content, creator_id, created_at)
        VALUES (?, ?, ?, ?, ?)`,
		c.ID, c.PlaceID, c.Content, c.Creator.ID, formatTime(c.CreatedAt))
	if err != nil {
		return domain.Comment{}, wrapWrite(err, "comment")
	}
	return c, nil
}

func (s *SQLStore) ListComments(ctx context.Context, placeID string) ([]domain.Comment, error) {
	rows, err := s.db.QueryContext(ctx, `
        SELECT c.id, c.place_id, c.content, c.created_at,
               u.id, u.username, u.email
        FROM comments c
        JOIN users u ON u.id = c.creator_id
        WHERE c.place_id = ?
        ORDER BY c.created_at`, placeID)
	if err != nil {
		return nil, fmt.Errorf("store: list comments: %w", err)
	}
	defer rows.Close()

	var out []domain.Comment
	for rows.Next() {
		var (
			c       domain.Comment
			created timeText
		)
		err := rows.Scan(&c.ID, &c.PlaceID, &c.Content, &created, &c.Creator.ID, &c.Creator.Username, &c.Creator.Email)
		if err != nil {
			return nil, fmt.Errorf("store: scan comment: %w", err)
		}
		c.CreatedAt = created.Time
		out = append(out, c)
	}
	return out, rows.Err()
}

// ----------------------------------------------------------------- users ---

func (s *SQLStore) UpsertUser(ctx context.Context, u domain.User) error {
	_, err := s.db.ExecContext(ctx, `
        INSERT INTO users (id, username, email, created_at)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            username = CASE WHEN excluded.username = '' THEN users.username ELSE excluded.username END,
            email    = CASE WHEN excluded.email    = '' THEN users.email    ELSE excluded.email    END`,
		u.ID, u.Username, u.Email, formatTime(time.Now().UTC()))
	if err != nil {
		return wrapWrite(err, "user")
	}
	return nil
}

func (s *SQLStore) GetUser(ctx context.Context, id string) (domain.User, error) {
	var u domain.User
	err := s.db.QueryRowContext(ctx,
		`SELECT id, username, email FROM users WHERE id = ?`, id).
		Scan(&u.ID, &u.Username, &u.Email)
	if errors.Is(err, sql.ErrNoRows) {
		return domain.User{}, ErrNotFound
	}
	if err != nil {
		return domain.User{}, fmt.Errorf("store: get user: %w", err)
	}
	return u, nil
}

// --------------------------------------------------------------- helpers ---

const imageSelect = `
    SELECT i.id, i.place_id, i.storage_key, i.content_type, i.byte_size,
           i.width, i.height, i.caption, i.latitude, i.longitude, i.taken_at,
           i.camera_make, i.camera_model, i.analysis_status, i.analysis_error,
           i.created_at, p.creator_id
    FROM images i
    JOIN places p ON p.id = i.place_id`

// scanner is satisfied by both *sql.Row and *sql.Rows.
type scanner interface{ Scan(dest ...any) error }

func scanPlace(sc scanner) (domain.Place, error) {
	var (
		p       domain.Place
		lat     sql.NullFloat64
		lon     sql.NullFloat64
		created timeText
	)
	err := sc.Scan(&p.ID, &p.Title, &p.Description, &lat, &lon,
		&p.LinkRadiusMeters, &created,
		&p.Creator.ID, &p.Creator.Username, &p.Creator.Email)
	if errors.Is(err, sql.ErrNoRows) {
		return domain.Place{}, ErrNotFound
	}
	if err != nil {
		return domain.Place{}, fmt.Errorf("store: scan place: %w", err)
	}
	if lat.Valid && lon.Valid {
		p.Coordinate = &domain.Coordinate{Latitude: lat.Float64, Longitude: lon.Float64}
	}
	p.CreatedAt = created.Time
	return p, nil
}

func scanImage(sc scanner) (domain.Image, error) {
	var (
		img       domain.Image
		lat       sql.NullFloat64
		lon       sql.NullFloat64
		takenAt   nullTimeText
		createdAt timeText
		status    string
		creatorID string
	)
	err := sc.Scan(&img.ID, &img.PlaceID, &img.StorageKey, &img.ContentType, &img.ByteSize,
		&img.Width, &img.Height, &img.Caption, &lat, &lon, &takenAt,
		&img.CameraMake, &img.CameraModel, &status, &img.AnalysisError, &createdAt, &creatorID)
	if errors.Is(err, sql.ErrNoRows) {
		return domain.Image{}, ErrNotFound
	}
	if err != nil {
		return domain.Image{}, fmt.Errorf("store: scan image: %w", err)
	}
	if lat.Valid && lon.Valid {
		img.Coordinate = &domain.Coordinate{Latitude: lat.Float64, Longitude: lon.Float64}
	}
	img.TakenAt = takenAt.Time
	img.CreatedAt = createdAt.Time
	img.AnalysisStatus = domain.AnalysisStatus(status)
	return img, nil
}

// badgesFor loads badges for many pictures in one round trip.
func (s *SQLStore) badgesFor(ctx context.Context, imageIDs []string) (map[string][]domain.Badge, error) {
	out := map[string][]domain.Badge{}
	if len(imageIDs) == 0 {
		return out, nil
	}

	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(imageIDs)), ",")
	args := make([]any, len(imageIDs))
	for i, id := range imageIDs {
		args[i] = id
	}

	rows, err := s.db.QueryContext(ctx, `
        SELECT image_id, label, display, confidence, source
        FROM badges
        WHERE image_id IN (`+placeholders+`)
        ORDER BY confidence DESC, label ASC`, args...)
	if err != nil {
		return nil, fmt.Errorf("store: load badges: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var (
			imageID string
			b       domain.Badge
		)
		if err := rows.Scan(&imageID, &b.Label, &b.Display, &b.Confidence, &b.Source); err != nil {
			return nil, fmt.Errorf("store: scan badge: %w", err)
		}
		out[imageID] = append(out[imageID], b)
	}
	return out, rows.Err()
}

func newID() string { return uuid.NewString() }

func formatTime(t time.Time) string { return t.UTC().Format(time.RFC3339Nano) }

func nullableFloat(c *domain.Coordinate, get func(domain.Coordinate) float64) any {
	if c == nil {
		return nil
	}
	return get(*c)
}

func nullableTime(t *time.Time) any {
	if t == nil {
		return nil
	}
	return formatTime(*t)
}

// timeText scans an RFC3339 timestamp column into a time.Time.
type timeText struct{ Time time.Time }

// Scan implements sql.Scanner.
func (t *timeText) Scan(src any) error {
	var s string
	switch v := src.(type) {
	case string:
		s = v
	case []byte:
		s = string(v)
	case time.Time:
		t.Time = v.UTC()
		return nil
	case nil:
		return errors.New("store: unexpected NULL timestamp")
	default:
		return fmt.Errorf("store: cannot scan %T into time", src)
	}
	// Try RFC3339Nano first, then date-only format YYYY-MM-DD
	parsedTime := time.Time{}
	if parsed, err := time.Parse(time.RFC3339Nano, s); err == nil {
		parsedTime = parsed
	} else if parsed, err := time.Parse("2006-01-02", s); err == nil {
		parsedTime = parsed
	} else {
		return fmt.Errorf("store: parse timestamp %q: %w", s, nil)
	}
	t.Time = parsedTime.UTC()
	return nil
}

// nullTimeText scans a nullable RFC3339 timestamp column.
type nullTimeText struct{ Time *time.Time }

// Scan implements sql.Scanner.
func (t *nullTimeText) Scan(src any) error {
	if src == nil {
		t.Time = nil
		return nil
	}
	var inner timeText
	if err := inner.Scan(src); err != nil {
		return err
	}
	v := inner.Time
	t.Time = &v
	return nil
}

// wrapWrite converts driver constraint failures into the package sentinel
// errors so callers can branch on them with errors.Is.
func wrapWrite(err error, entity string) error {
	if err == nil {
		return nil
	}
	msg := strings.ToLower(err.Error())
	switch {
	case strings.Contains(msg, "unique constraint failed"),
		strings.Contains(msg, "constraint failed"):
		return fmt.Errorf("%w: duplicate %s: %v", ErrConflict, entity, err)
	case errors.Is(err, sql.ErrNoRows):
		return ErrNotFound
	default:
		return fmt.Errorf("store: write %s: %w", entity, err)
	}
}