MTG_CardCollectionTool/MTG_CollectionVerwaltung/Helpers/CardSearcher.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Primitives;
using MTG_CollectionVerwaltung.Data.MTGCollection;

namespace MTG_CollectionVerwaltung.Helpers
{
    public class CardSearcher
    {
        public static List<CardPrinting>? SearchCards(
        MTGContext db, string query, int maxResults = 100)
        {
            // Schritt 1: FTS MATCH für schnelle Eingrenzung
            List<CardPrinting>? candidates = GetNames(db, query + "*", maxResults * 20);

            if (candidates == null) return null;

            if (candidates.Count == 0 && query.Length >= 3)
            {
                List<string> tokens = Grammize(query);
                foreach(string token in tokens)
                {
                    candidates.AddRange(GetNames(db, token, maxResults * 20));
                    candidates.DistinctBy(c=> c.Id);
                }
            }

            if (candidates.Count == 0) return null;

            // Schritt 3: Levenshtein im Speicher anwenden
            List<(CardPrinting card, int distance)> ranked = RankResults(db, candidates, query, maxResults);

            if (ranked[0].distance == 0)
            {
                return ranked.Where(c => c.distance == 0).Select(x=>x.card).ToList();
            }

            return ranked.Select(x=>x.card).ToList();
        }

        private static List<string> Grammize(string query)
        {
            List<string> results = new List<string>();
            query = query.ToLower();
            query = new string(query.Where(char.IsLetterOrDigit).ToArray());
            if (query.Length <= 3)
            {
                string[] substrings = [query.Substring(0, query.Length - 1), query.Substring(1, query.Length - 1)];
                for (char c = 'a'; c <= 'z'; c++)
                {
                    results.Add(substrings[0] + c);
                    results.Add(c + substrings[1]);
                }
                return results;
            }

            for (int i = 0; i < query.Length - 2; i++)
            {
                results.Add(query.Substring(i, 3));
            }

            return results;
        }

        private static List<CardPrinting> GetNames(MTGContext db, string query, int maxResults)
        {
            return db.CardPrintings
                .FromSqlRaw(@"SELECT cp.*
                        FROM CardPrintings cp
                        JOIN CardPrintingsFTS fts
                        ON cp.Id = fts.rowid
                        WHERE fts.rowid IN (
                            SELECT MIN(rowid)
                            FROM CardPrintingsFTS
                            WHERE Name MATCH {0}
                            GROUP BY Name
                        )
                ", query)
                .Take(maxResults)
                .Include(cp => cp.Card)
                .ToList();
        }

        private static List<(CardPrinting card, int distance)> RankResults(MTGContext db, List<CardPrinting> candidates,string query, int maxResults)
        {
            return candidates
                .DistinctBy(c => c.Id)
                .Select(c => (card: c, dist: MTGContext.LevenshteinMinWordDistanceByLength(c.Name.ToLower(), query.ToLower())))
                .Where(x => x.dist <= 3)
                .DistinctBy(c => c.card.Name)
                .OrderBy(x => x.dist)
                .Take(maxResults)
                .ToList();
        }

    }
}