osdb-1-claude-opusplan/backend/internal/repository/fruit_repo.go

package repository

import (
	"context"
	"errors"
	"fmt"
	"strings"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"osdb/internal/domain"
	"osdb/internal/handler"
	"osdb/internal/imaging"
)

type FruitRepo struct {
	pool *pgxpool.Pool
}

func NewFruitRepo(pool *pgxpool.Pool) *FruitRepo {
	return &FruitRepo{pool: pool}
}

func imageURL(fruitID, imageID int) string {
	return fmt.Sprintf("/api/v1/fruits/%d/images/%d", fruitID, imageID)
}

func thumbnailURL(fruitID, imageID int) string {
	return fmt.Sprintf("/api/v1/fruits/%d/images/%d/thumbnail", fruitID, imageID)
}

func mapPgError(err error) error {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) && pgErr.Code == "23505" {
		return handler.ErrDuplicateOSDBNumber
	}
	return err
}

func escapeLike(s string) string {
	s = strings.ReplaceAll(s, `\`, `\\`)
	s = strings.ReplaceAll(s, `%`, `\%`)
	s = strings.ReplaceAll(s, `_`, `\_`)
	return s
}

func (r *FruitRepo) List(ctx context.Context, limit, offset int, name string, types []string) ([]domain.Fruit, int, error) {
	escaped := escapeLike(name)
	countRow := r.pool.QueryRow(ctx,
		`SELECT COUNT(DISTINCT f.id)
		 FROM fruits f
		 LEFT JOIN fruit_synonyms fs ON fs.fruit_id = f.id
		 WHERE ($1 = '' OR f.name ILIKE '%' || $1 || '%' ESCAPE '\' OR fs.synonym ILIKE '%' || $1 || '%' ESCAPE '\')
		   AND ($2::fruit_type[] IS NULL OR f.fruit_type = ANY($2::fruit_type[]))`,
		escaped, types)
	var total int
	if err := countRow.Scan(&total); err != nil {
		return nil, 0, err
	}

	rows, err := r.pool.Query(ctx,
		`SELECT DISTINCT f.id, f.name, f.osdb_number, f.comment, f.fruit_type, f.created_at, f.updated_at,
		        COALESCE((SELECT array_agg(fs2.synonym ORDER BY fs2.id) FROM fruit_synonyms fs2 WHERE fs2.fruit_id = f.id), '{}') AS synonyms,
		        COALESCE(
		          (SELECT '/api/v1/fruits/' || f.id || '/images/' || fi.id || '/thumbnail'
		           FROM fruit_images fi WHERE fi.fruit_id = f.id AND fi.image_type = 'fruit' ORDER BY fi.id LIMIT 1),
		          (SELECT '/api/v1/publications/' || pfi.publication_id || '/fruit-images/' || pfi.id || '/thumbnail'
		           FROM publication_fruit_images pfi WHERE pfi.fruit_id = f.id ORDER BY pfi.id LIMIT 1)
		        ) AS thumbnail_url
		 FROM fruits f
		 LEFT JOIN fruit_synonyms fs ON fs.fruit_id = f.id
		 WHERE ($1 = '' OR f.name ILIKE '%' || $1 || '%' ESCAPE '\' OR fs.synonym ILIKE '%' || $1 || '%' ESCAPE '\')
		   AND ($2::fruit_type[] IS NULL OR f.fruit_type = ANY($2::fruit_type[]))
		 ORDER BY f.name, f.id LIMIT $3 OFFSET $4`,
		escaped, types, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	fruits := []domain.Fruit{}
	for rows.Next() {
		var f domain.Fruit
		if err := rows.Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt,
			&f.Synonyms, &f.ThumbnailURL); err != nil {
			return nil, 0, err
		}
		if f.Synonyms == nil {
			f.Synonyms = []string{}
		}
		f.Images = []domain.FruitImage{}
		fruits = append(fruits, f)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}
	return fruits, total, nil
}

func (r *FruitRepo) Get(ctx context.Context, id int) (domain.Fruit, error) {
	var f domain.Fruit
	err := r.pool.QueryRow(ctx,
		`SELECT id, name, osdb_number, comment, fruit_type, created_at, updated_at
		 FROM fruits WHERE id = $1`, id).
		Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.Fruit{}, handler.ErrNotFound
	}
	if err != nil {
		return domain.Fruit{}, err
	}

	// load synonyms
	synRows, err := r.pool.Query(ctx, `SELECT synonym FROM fruit_synonyms WHERE fruit_id = $1 ORDER BY id`, id)
	if err != nil {
		return domain.Fruit{}, err
	}
	defer synRows.Close()
	f.Synonyms = []string{}
	for synRows.Next() {
		var s string
		if err := synRows.Scan(&s); err != nil {
			return domain.Fruit{}, err
		}
		f.Synonyms = append(f.Synonyms, s)
	}
	if err := synRows.Err(); err != nil {
		return domain.Fruit{}, err
	}

	// load image metadata (no binary)
	imgRows, err := r.pool.Query(ctx,
		`SELECT id, fruit_id, filename, image_type, title, created_at
		 FROM fruit_images WHERE fruit_id = $1 ORDER BY id`, id)
	if err != nil {
		return domain.Fruit{}, err
	}
	defer imgRows.Close()
	f.Images = []domain.FruitImage{}
	for imgRows.Next() {
		var img domain.FruitImage
		if err := imgRows.Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt); err != nil {
			return domain.Fruit{}, err
		}
		img.URL = imageURL(img.FruitID, img.ID)
		f.Images = append(f.Images, img)
	}
	if err := imgRows.Err(); err != nil {
		return domain.Fruit{}, err
	}

	return f, nil
}

func (r *FruitRepo) Create(ctx context.Context, dto domain.FruitWriteDTO) (domain.Fruit, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return domain.Fruit{}, err
	}
	defer tx.Rollback(ctx) //nolint:errcheck

	var f domain.Fruit
	err = tx.QueryRow(ctx,
		`INSERT INTO fruits (name, osdb_number, comment, fruit_type)
		 VALUES ($1, $2, $3, $4)
		 RETURNING id, name, osdb_number, comment, fruit_type, created_at, updated_at`,
		dto.Name, dto.OSDBNumber, dto.Comment, dto.FruitType).
		Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
	if err != nil {
		return domain.Fruit{}, mapPgError(err)
	}

	syns := dto.Synonyms
	if syns == nil {
		syns = []string{}
	}
	for _, s := range syns {
		if _, err := tx.Exec(ctx,
			`INSERT INTO fruit_synonyms (fruit_id, synonym) VALUES ($1, $2)`, f.ID, s); err != nil {
			return domain.Fruit{}, err
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return domain.Fruit{}, err
	}
	f.Synonyms = syns
	f.Images = []domain.FruitImage{}
	return f, nil
}

func (r *FruitRepo) Update(ctx context.Context, id int, dto domain.FruitWriteDTO) (domain.Fruit, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return domain.Fruit{}, err
	}
	defer tx.Rollback(ctx) //nolint:errcheck

	var f domain.Fruit
	err = tx.QueryRow(ctx,
		`UPDATE fruits SET name=$1, osdb_number=$2, comment=$3, fruit_type=$4, updated_at=NOW()
		 WHERE id=$5
		 RETURNING id, name, osdb_number, comment, fruit_type, created_at, updated_at`,
		dto.Name, dto.OSDBNumber, dto.Comment, dto.FruitType, id).
		Scan(&f.ID, &f.Name, &f.OSDBNumber, &f.Comment, &f.FruitType, &f.CreatedAt, &f.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.Fruit{}, handler.ErrNotFound
	}
	if err != nil {
		return domain.Fruit{}, mapPgError(err)
	}

	if _, err := tx.Exec(ctx, `DELETE FROM fruit_synonyms WHERE fruit_id=$1`, id); err != nil {
		return domain.Fruit{}, err
	}
	syns := dto.Synonyms
	if syns == nil {
		syns = []string{}
	}
	for _, s := range syns {
		if _, err := tx.Exec(ctx,
			`INSERT INTO fruit_synonyms (fruit_id, synonym) VALUES ($1, $2)`, id, s); err != nil {
			return domain.Fruit{}, err
		}
	}

	if err := tx.Commit(ctx); err != nil {
		return domain.Fruit{}, err
	}
	f.Synonyms = syns
	f.Images = []domain.FruitImage{}
	return f, nil
}

func (r *FruitRepo) Delete(ctx context.Context, id int) error {
	tag, err := r.pool.Exec(ctx, `DELETE FROM fruits WHERE id=$1`, id)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return handler.ErrNotFound
	}
	return nil
}

func (r *FruitRepo) ListImages(ctx context.Context, fruitID int) ([]domain.FruitImage, error) {
	// verify fruit exists
	var exists bool
	if err := r.pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM fruits WHERE id=$1)`, fruitID).Scan(&exists); err != nil {
		return nil, err
	}
	if !exists {
		return nil, handler.ErrNotFound
	}

	rows, err := r.pool.Query(ctx,
		`SELECT id, fruit_id, filename, image_type, title, created_at
		 FROM fruit_images WHERE fruit_id=$1 ORDER BY id`, fruitID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	images := []domain.FruitImage{}
	for rows.Next() {
		var img domain.FruitImage
		if err := rows.Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt); err != nil {
			return nil, err
		}
		img.URL = imageURL(img.FruitID, img.ID)
		images = append(images, img)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return images, nil
}

func (r *FruitRepo) AddImage(ctx context.Context, fruitID int, filename *string, data []byte, imageType string, title *string) (domain.FruitImage, error) {
	var exists bool
	if err := r.pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM fruits WHERE id=$1)`, fruitID).Scan(&exists); err != nil {
		return domain.FruitImage{}, err
	}
	if !exists {
		return domain.FruitImage{}, handler.ErrNotFound
	}

	thumbnailData, _ := imaging.CropToContent(data)

	var img domain.FruitImage
	err := r.pool.QueryRow(ctx,
		`INSERT INTO fruit_images (fruit_id, filename, data, thumbnail_data, image_type, title)
		 VALUES ($1, $2, $3, $4, $5, $6)
		 RETURNING id, fruit_id, filename, image_type, title, created_at`,
		fruitID, filename, data, thumbnailData, imageType, title).
		Scan(&img.ID, &img.FruitID, &img.Filename, &img.ImageType, &img.Title, &img.CreatedAt)
	if err != nil {
		return domain.FruitImage{}, err
	}
	img.URL = imageURL(img.FruitID, img.ID)
	return img, nil
}

func (r *FruitRepo) GetImageData(ctx context.Context, fruitID, imageID int) ([]byte, error) {
	var data []byte
	err := r.pool.QueryRow(ctx,
		`SELECT data FROM fruit_images WHERE id=$1 AND fruit_id=$2`, imageID, fruitID).Scan(&data)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, handler.ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	return data, nil
}

// GetThumbnailData returns thumbnail_data if present, falls back to data.
func (r *FruitRepo) GetThumbnailData(ctx context.Context, fruitID, imageID int) ([]byte, error) {
	var thumbnailData, data []byte
	err := r.pool.QueryRow(ctx,
		`SELECT thumbnail_data, data FROM fruit_images WHERE id=$1 AND fruit_id=$2`, imageID, fruitID).
		Scan(&thumbnailData, &data)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, handler.ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	if len(thumbnailData) > 0 {
		return thumbnailData, nil
	}
	return data, nil
}

func (r *FruitRepo) DeleteImage(ctx context.Context, fruitID, imageID int) error {
	tag, err := r.pool.Exec(ctx, `DELETE FROM fruit_images WHERE id=$1 AND fruit_id=$2`, imageID, fruitID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return handler.ErrNotFound
	}
	return nil
}

// BackfillThumbnails generates thumbnail_data for all fruit_images rows missing it.
// Returns count of rows processed.
func (r *FruitRepo) BackfillThumbnails(ctx context.Context) (int, error) {
	rows, err := r.pool.Query(ctx, `SELECT id, fruit_id, data FROM fruit_images WHERE thumbnail_data IS NULL`)
	if err != nil {
		return 0, err
	}
	defer rows.Close()

	type row struct {
		id, fruitID int
		data        []byte
	}
	var pending []row
	for rows.Next() {
		var rw row
		if err := rows.Scan(&rw.id, &rw.fruitID, &rw.data); err != nil {
			return 0, err
		}
		pending = append(pending, rw)
	}
	if err := rows.Err(); err != nil {
		return 0, err
	}

	count := 0
	for _, rw := range pending {
		thumb, _ := imaging.CropToContent(rw.data)
		if _, err := r.pool.Exec(ctx,
			`UPDATE fruit_images SET thumbnail_data=$1 WHERE id=$2`, thumb, rw.id); err != nil {
			return count, err
		}
		count++
	}
	return count, nil
}