package assets

import (
	"context"

	"vibetree/internal/models"
)

// Search performs a global, case-insensitive substring search across asset
// ID, name, notes, folder name, item type name, assigned username, and
// custom field values, returning fully-loaded assets (with folder path and
// custom fields) ready for display with breadcrumbs.
func (s *Store) Search(ctx context.Context, query string, limit int) ([]models.Asset, error) {
	if query == "" {
		return nil, nil
	}
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	like := "%" + query + "%"

	rows, err := s.db.QueryContext(ctx, `
		SELECT DISTINCT a.id
		FROM assets a
		LEFT JOIN folders fo ON fo.id = a.folder_id
		LEFT JOIN users au ON au.id = a.assigned_user_id
		JOIN item_types it ON it.id = a.item_type_id
		WHERE a.asset_id LIKE ? COLLATE NOCASE
		   OR a.name LIKE ? COLLATE NOCASE
		   OR a.notes LIKE ? COLLATE NOCASE
		   OR fo.name LIKE ? COLLATE NOCASE
		   OR it.name LIKE ? COLLATE NOCASE
		   OR au.username LIKE ? COLLATE NOCASE
		   OR EXISTS (SELECT 1 FROM custom_field_values v WHERE v.asset_id = a.id AND v.value LIKE ? COLLATE NOCASE)
		ORDER BY a.name COLLATE NOCASE ASC
		LIMIT ?`,
		like, like, like, like, like, like, like, limit)
	if err != nil {
		return nil, err
	}
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return nil, err
		}
		ids = append(ids, id)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return nil, err
	}

	var out []models.Asset
	for _, id := range ids {
		a, err := s.Get(ctx, id)
		if err != nil {
			return nil, err
		}
		out = append(out, *a)
	}
	return out, nil
}