< Summary

Information
Class: Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource
Assembly: Orchestrator
File(s): /home/runner/work/KicktippAi/KicktippAi/src/Orchestrator/Commands/Operations/CollectContext/Wm26LineupSource.cs
Line coverage
91%
Covered lines: 408
Uncovered lines: 39
Coverable lines: 447
Total lines: 848
Line coverage: 91.2%
Branch coverage
74%
Covered branches: 188
Total branches: 254
Branch coverage: 74%
Method coverage

Feature is only available for sponsors

Upgrade to PRO version

Metrics

MethodBranch coverage Crap Score Cyclomatic complexity Line coverage
.cctor()100%11100%
.ctor(...)100%11100%
CollectAsync()100%88100%
ResolvePath(...)100%11100%
GetSlugFromDocumentName(...)100%11100%
ReadTeamManifest(...)56.25%181679.17%
ReadSeedRows(...)60%101092.59%
CreateReader(...)100%11100%
ValidateColumns(...)50%4483.33%
GetTrimmedField(...)50%22100%
GetOptionalField(...)75%44100%
ValidateSeedRow(...)57.14%151480.77%
ValidateOutputRow(...)70%101083.33%
IsValidRole(...)100%22100%
ValidateAge(...)75%9880%
ValidateMarketValue(...)91.67%131280%
EnrichRows(...)100%88100%
EnrichCoachRow(...)75%8886.67%
EnrichPlayerRow(...)77.78%1818100%
ResolvePlayer(...)90%101092.86%
GetPlayerById(...)100%22100%
GetPlayersByNationalTeamId(...)100%22100%
GetCoachName(...)50%1010100%
ReadPlayer(...)54.55%2222100%
ProvidedValue(...)100%44100%
CalculateAgeOrMissing(...)50%1362442.11%
FormatMarketValueOrMissing(...)80%111080%
GroupRowsByManifest(...)87.5%9871.43%
ValidateCoaches(...)83.33%7666.67%
BuildMissingSourceData(...)100%88100%
RenderCsv(...)100%44100%
FormatMarketValueForOutput(...)83.33%66100%
NormalizeName(...)100%44100%
.ctor(...)100%11100%
.ctor(...)100%11100%
.ctor(...)100%11100%
.ctor(...)100%11100%

File(s)

/home/runner/work/KicktippAi/KicktippAi/src/Orchestrator/Commands/Operations/CollectContext/Wm26LineupSource.cs

#LineLine coverage
 1using System.Globalization;
 2using System.Text;
 3using System.Text.RegularExpressions;
 4using CsvHelper;
 5using CsvHelper.Configuration;
 6using DuckDB.NET.Data;
 7using EHonda.KicktippAi.Core;
 8using Microsoft.Extensions.Logging;
 9
 10namespace Orchestrator.Commands.Operations.CollectContext;
 11
 12public interface IWm26LineupSource
 13{
 14    Task<Wm26LineupCollection> CollectAsync(
 15        Wm26LineupSourceRequest request,
 16        CancellationToken cancellationToken = default);
 17}
 18
 19internal interface IWm26TransfermarktDuckDbProvider
 20{
 21    Task<string> GetDatabasePathAsync(
 22        string? configuredPath,
 23        CancellationToken cancellationToken = default);
 24}
 25
 26internal sealed class Wm26LineupSource : IWm26LineupSource
 27{
 28    private const string MissingValue = "N/A";
 29
 130    private static readonly IReadOnlyList<string> RequiredSeedColumns =
 131    [
 132        "Team_Slug",
 133        "Team",
 134        "Data_Collected_At",
 135        "Role",
 136        "Name",
 137        "Transfermarkt_National_Team_Id",
 138        "Transfermarkt_Player_Id"
 139    ];
 40
 141    private static readonly IReadOnlyList<string> OutputColumns =
 142    [
 143        "Team",
 144        "Data_Collected_At",
 145        "Role",
 146        "Name",
 147        "Age",
 148        "Position",
 149        "Market_Value_EUR"
 150    ];
 51
 152    private static readonly Regex NonAlphanumericRegex = new("[^a-z0-9]+", RegexOptions.Compiled);
 53
 54    private readonly IWm26TransfermarktDuckDbProvider _duckDbProvider;
 55
 156    public Wm26LineupSource(IWm26TransfermarktDuckDbProvider duckDbProvider)
 57    {
 158        _duckDbProvider = duckDbProvider;
 159    }
 60
 61    public async Task<Wm26LineupCollection> CollectAsync(
 62        Wm26LineupSourceRequest request,
 63        CancellationToken cancellationToken = default)
 64    {
 165        ArgumentNullException.ThrowIfNull(request);
 66
 167        var seedPath = ResolvePath(request.SeedPath);
 168        var teamsPath = ResolvePath(request.TeamsPath);
 169        var teams = ReadTeamManifest(teamsPath);
 170        var seedRows = ReadSeedRows(seedPath);
 171        var databasePath = await _duckDbProvider.GetDatabasePathAsync(request.DuckDbPath, cancellationToken);
 72
 173        var enrichedRows = EnrichRows(seedRows, databasePath);
 174        var groupedRows = GroupRowsByManifest(teams, enrichedRows);
 175        ValidateCoaches(groupedRows);
 76
 177        var contextDocuments = groupedRows
 178            .Select(entry => new Wm26LineupDocument(
 179                $"lineup-{entry.Team.Slug}.csv",
 180                RenderCsv(entry.Rows),
 181                entry.Team.Name,
 182                entry.Rows.Count(row => string.Equals(row.Role, "Player", StringComparison.Ordinal)),
 183                entry.Rows.Count == 0))
 184            .ToList();
 85
 186        var aggregateRows = groupedRows.SelectMany(entry => entry.Rows);
 187        var kpiContent = RenderCsv(aggregateRows);
 188        var headerOnlyTeams = contextDocuments
 189            .Where(document => document.IsHeaderOnly)
 190            .Select(document => new Wm26LineupTeam(GetSlugFromDocumentName(document.DocumentName), document.TeamName))
 191            .ToList();
 192        var missingSourceData = BuildMissingSourceData(enrichedRows);
 93
 194        return new Wm26LineupCollection(
 195            seedPath,
 196            teamsPath,
 197            databasePath,
 198            seedRows.Count,
 199            enrichedRows.Count,
 1100            contextDocuments,
 1101            kpiContent,
 1102            headerOnlyTeams,
 1103            missingSourceData);
 1104    }
 105
 106    private static string ResolvePath(string value)
 107    {
 1108        ArgumentException.ThrowIfNullOrWhiteSpace(value);
 1109        return Path.GetFullPath(value);
 110    }
 111
 112    private static string GetSlugFromDocumentName(string documentName)
 113    {
 1114        return documentName["lineup-".Length..^4];
 115    }
 116
 117    private static IReadOnlyList<Wm26LineupTeam> ReadTeamManifest(string teamsPath)
 118    {
 1119        if (!File.Exists(teamsPath))
 120        {
 0121            throw new FileNotFoundException($"Team manifest CSV not found: {teamsPath}", teamsPath);
 122        }
 123
 1124        using var reader = new StreamReader(teamsPath, Encoding.UTF8, detectEncodingFromByteOrderMarks: true);
 1125        using var csv = CreateReader(reader);
 1126        csv.Read();
 1127        csv.ReadHeader();
 1128        ValidateColumns(csv, ["Team_Slug", "Team"], "Team manifest CSV");
 129
 1130        var teams = new List<Wm26LineupTeam>();
 1131        var slugs = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
 1132        while (csv.Read())
 133        {
 1134            var lineNumber = csv.Context?.Parser?.Row ?? 0;
 1135            var slug = GetTrimmedField(csv, "Team_Slug");
 1136            var team = GetTrimmedField(csv, "Team");
 137
 1138            if (string.IsNullOrWhiteSpace(slug))
 139            {
 0140                throw new InvalidOperationException($"Team manifest line {lineNumber}: missing Team_Slug");
 141            }
 142
 1143            if (string.IsNullOrWhiteSpace(team))
 144            {
 0145                throw new InvalidOperationException($"Team manifest line {lineNumber}: missing Team");
 146            }
 147
 1148            if (!slugs.Add(slug))
 149            {
 0150                throw new InvalidOperationException($"Team manifest line {lineNumber}: duplicate Team_Slug {slug}");
 151            }
 152
 1153            teams.Add(new Wm26LineupTeam(slug, team));
 154        }
 155
 1156        if (teams.Count == 0)
 157        {
 0158            throw new InvalidOperationException("Team manifest CSV has no team rows");
 159        }
 160
 1161        return teams;
 1162    }
 163
 164    private static List<Wm26LineupSeedRow> ReadSeedRows(string seedPath)
 165    {
 1166        if (!File.Exists(seedPath))
 167        {
 0168            throw new FileNotFoundException($"Lineup seed CSV not found: {seedPath}", seedPath);
 169        }
 170
 1171        using var reader = new StreamReader(seedPath, Encoding.UTF8, detectEncodingFromByteOrderMarks: true);
 1172        using var csv = CreateReader(reader);
 1173        csv.Read();
 1174        csv.ReadHeader();
 1175        ValidateColumns(csv, RequiredSeedColumns, "Lineup seed CSV");
 176
 1177        var rows = new List<Wm26LineupSeedRow>();
 1178        while (csv.Read())
 179        {
 1180            var lineNumber = csv.Context?.Parser?.Row ?? 0;
 1181            var row = new Wm26LineupSeedRow(
 1182                GetTrimmedField(csv, "Team_Slug"),
 1183                GetTrimmedField(csv, "Team"),
 1184                GetTrimmedField(csv, "Data_Collected_At"),
 1185                GetTrimmedField(csv, "Role"),
 1186                GetTrimmedField(csv, "Name"),
 1187                GetTrimmedField(csv, "Transfermarkt_National_Team_Id"),
 1188                GetTrimmedField(csv, "Transfermarkt_Player_Id"),
 1189                GetOptionalField(csv, "Age"),
 1190                GetOptionalField(csv, "Position"),
 1191                GetOptionalField(csv, "Market_Value_EUR"));
 192
 1193            ValidateSeedRow(row, lineNumber);
 1194            rows.Add(row);
 195        }
 196
 1197        if (rows.Count == 0)
 198        {
 0199            throw new InvalidOperationException("Lineup seed CSV has no lineup rows");
 200        }
 201
 1202        return rows;
 1203    }
 204
 205    private static CsvReader CreateReader(TextReader reader)
 206    {
 1207        return new CsvReader(
 1208            reader,
 1209            new CsvConfiguration(CultureInfo.InvariantCulture)
 1210            {
 1211                BadDataFound = null,
 1212                MissingFieldFound = null,
 1213                TrimOptions = TrimOptions.Trim
 1214            });
 215    }
 216
 217    private static void ValidateColumns(CsvReader csv, IReadOnlyList<string> requiredColumns, string label)
 218    {
 1219        var headers = csv.HeaderRecord ?? [];
 1220        var missing = requiredColumns
 1221            .Where(column => !headers.Contains(column, StringComparer.Ordinal))
 1222            .ToList();
 223
 1224        if (missing.Count > 0)
 225        {
 0226            throw new InvalidOperationException($"{label} is missing required column(s): {string.Join(", ", missing)}");
 227        }
 1228    }
 229
 230    private static string GetTrimmedField(CsvReader csv, string column)
 231    {
 1232        return (csv.GetField(column) ?? string.Empty).Trim();
 233    }
 234
 235    private static string GetOptionalField(CsvReader csv, string column)
 236    {
 1237        var headers = csv.HeaderRecord ?? [];
 1238        return headers.Contains(column, StringComparer.Ordinal)
 1239            ? GetTrimmedField(csv, column)
 1240            : string.Empty;
 241    }
 242
 243    private static void ValidateSeedRow(Wm26LineupSeedRow row, int lineNumber)
 244    {
 1245        foreach (var (column, value) in new[]
 1246                 {
 1247                     ("Team_Slug", row.TeamSlug),
 1248                     ("Team", row.Team),
 1249                     ("Data_Collected_At", row.DataCollectedAt),
 1250                     ("Role", row.Role)
 1251                 })
 252        {
 1253            if (string.IsNullOrWhiteSpace(value))
 254            {
 0255                throw new InvalidOperationException($"Line {lineNumber}: missing {column}");
 256            }
 257        }
 258
 1259        if (!IsValidRole(row.Role))
 260        {
 0261            throw new InvalidOperationException($"Line {lineNumber}: unsupported Role {row.Role}");
 262        }
 263
 1264        if (string.Equals(row.Role, "Player", StringComparison.Ordinal)
 1265            && string.IsNullOrWhiteSpace(row.Name)
 1266            && string.IsNullOrWhiteSpace(row.TransfermarktPlayerId))
 267        {
 0268            throw new InvalidOperationException($"Line {lineNumber}: Player row needs Name or Transfermarkt_Player_Id");
 269        }
 270
 1271        ValidateAge(row.Age, lineNumber);
 1272        ValidateMarketValue(row.MarketValueEur, row.Role, lineNumber);
 273
 1274        if (!DateOnly.TryParseExact(
 1275                row.DataCollectedAt,
 1276                "yyyy-MM-dd",
 1277                CultureInfo.InvariantCulture,
 1278                DateTimeStyles.None,
 1279                out _))
 280        {
 0281            throw new InvalidOperationException(
 0282                $"Line {lineNumber}: Data_Collected_At must use YYYY-MM-DD, got {row.DataCollectedAt}");
 283        }
 1284    }
 285
 286    private static void ValidateOutputRow(Wm26LineupOutputRow row, int lineNumber)
 287    {
 1288        foreach (var (column, value) in new[]
 1289                 {
 1290                     ("Team", row.Team),
 1291                     ("Data_Collected_At", row.DataCollectedAt),
 1292                     ("Role", row.Role),
 1293                     ("Name", row.Name),
 1294                     ("Position", row.Position)
 1295                 })
 296        {
 1297            if (string.IsNullOrWhiteSpace(value))
 298            {
 0299                throw new InvalidOperationException($"Line {lineNumber}: missing {column}");
 300            }
 301        }
 302
 1303        if (string.Equals(row.Role, "Player", StringComparison.Ordinal)
 1304            && string.IsNullOrWhiteSpace(row.Age))
 305        {
 0306            throw new InvalidOperationException($"Line {lineNumber}: missing Age");
 307        }
 308
 1309        if (!IsValidRole(row.Role))
 310        {
 0311            throw new InvalidOperationException($"Line {lineNumber}: unsupported Role {row.Role}");
 312        }
 313
 1314        ValidateAge(row.Age, lineNumber);
 1315        ValidateMarketValue(row.MarketValueEur, row.Role, lineNumber);
 1316    }
 317
 318    private static bool IsValidRole(string role)
 319    {
 1320        return string.Equals(role, "Player", StringComparison.Ordinal)
 1321               || string.Equals(role, "Coach", StringComparison.Ordinal);
 322    }
 323
 324    private static void ValidateAge(string age, int lineNumber)
 325    {
 1326        if (!string.IsNullOrWhiteSpace(age)
 1327            && !string.Equals(age, MissingValue, StringComparison.OrdinalIgnoreCase)
 1328            && !age.All(char.IsDigit))
 329        {
 0330            throw new InvalidOperationException($"Line {lineNumber}: Age must be numeric or N/A when provided");
 331        }
 1332    }
 333
 334    private static void ValidateMarketValue(string marketValue, string role, int lineNumber)
 335    {
 1336        var normalized = marketValue.Replace(".", string.Empty, StringComparison.Ordinal);
 1337        if (!string.IsNullOrWhiteSpace(marketValue)
 1338            && !string.Equals(marketValue, MissingValue, StringComparison.OrdinalIgnoreCase)
 1339            && !normalized.All(char.IsDigit))
 340        {
 0341            throw new InvalidOperationException(
 0342                $"Line {lineNumber}: Market_Value_EUR must be numeric, N/A, or empty");
 343        }
 344
 1345        if (string.Equals(role, "Player", StringComparison.Ordinal) && normalized == "0")
 346        {
 1347            throw new InvalidOperationException(
 1348                $"Line {lineNumber}: Market_Value_EUR must use N/A instead of 0 when unavailable");
 349        }
 1350    }
 351
 352    private static List<Wm26LineupOutputRow> EnrichRows(
 353        IReadOnlyList<Wm26LineupSeedRow> seedRows,
 354        string databasePath)
 355    {
 1356        using var connection = new DuckDBConnection($"Data Source={databasePath}");
 1357        connection.Open();
 358
 1359        var enrichedRows = new List<Wm26LineupOutputRow>();
 1360        var errors = new List<string>();
 1361        for (var index = 0; index < seedRows.Count; index++)
 362        {
 1363            var lineNumber = index + 2;
 1364            var seedRow = seedRows[index];
 365            try
 366            {
 1367                var row = string.Equals(seedRow.Role, "Coach", StringComparison.Ordinal)
 1368                    ? EnrichCoachRow(connection, seedRow)
 1369                    : EnrichPlayerRow(connection, seedRow);
 1370                ValidateOutputRow(row, lineNumber);
 1371                enrichedRows.Add(row);
 1372            }
 1373            catch (Exception ex) when (ex is InvalidOperationException or FormatException)
 374            {
 1375                errors.Add($"Line {lineNumber}: {ex.Message}");
 1376            }
 377        }
 378
 1379        if (errors.Count > 0)
 380        {
 1381            throw new InvalidOperationException(
 1382                "Lineup enrichment failed:" + Environment.NewLine +
 1383                string.Join(Environment.NewLine, errors.Select(error => $"- {error}")));
 384        }
 385
 1386        return enrichedRows;
 1387    }
 388
 389    private static Wm26LineupOutputRow EnrichCoachRow(
 390        DuckDBConnection connection,
 391        Wm26LineupSeedRow row)
 392    {
 1393        var coachName = row.Name;
 1394        if (string.IsNullOrWhiteSpace(coachName) && !string.IsNullOrWhiteSpace(row.TransfermarktNationalTeamId))
 395        {
 1396            coachName = GetCoachName(connection, row.TransfermarktNationalTeamId);
 397        }
 398
 1399        if (string.IsNullOrWhiteSpace(coachName))
 400        {
 0401            throw new InvalidOperationException(
 0402                "Coach row needs Name or Transfermarkt_National_Team_Id with national_teams.coach_name");
 403        }
 404
 1405        return new Wm26LineupOutputRow(
 1406            row.TeamSlug,
 1407            row.Team,
 1408            row.DataCollectedAt,
 1409            "Coach",
 1410            coachName,
 1411            row.Age,
 1412            string.IsNullOrWhiteSpace(row.Position) ? "Coach" : row.Position,
 1413            string.Empty);
 414    }
 415
 416    private static Wm26LineupOutputRow EnrichPlayerRow(
 417        DuckDBConnection connection,
 418        Wm26LineupSeedRow row)
 419    {
 1420        var player = ResolvePlayer(connection, row);
 1421        if (player is null)
 422        {
 1423            return new Wm26LineupOutputRow(
 1424                row.TeamSlug,
 1425                row.Team,
 1426                row.DataCollectedAt,
 1427                "Player",
 1428                row.Name,
 1429                ProvidedValue(row.Age) ?? MissingValue,
 1430                ProvidedValue(row.Position) ?? MissingValue,
 1431                ProvidedValue(row.MarketValueEur) ?? MissingValue);
 432        }
 433
 1434        var collectedAt = DateOnly.ParseExact(row.DataCollectedAt, "yyyy-MM-dd", CultureInfo.InvariantCulture);
 1435        return new Wm26LineupOutputRow(
 1436            row.TeamSlug,
 1437            row.Team,
 1438            row.DataCollectedAt,
 1439            "Player",
 1440            string.IsNullOrWhiteSpace(row.Name) ? player.Name : row.Name,
 1441            ProvidedValue(row.Age) ?? CalculateAgeOrMissing(player.DateOfBirth, collectedAt),
 1442            string.IsNullOrWhiteSpace(player.Position) ? ProvidedValue(row.Position) ?? MissingValue : player.Position,
 1443            ProvidedValue(row.MarketValueEur) ?? FormatMarketValueOrMissing(player.MarketValueInEur));
 444    }
 445
 446    private static Wm26LineupPlayerRecord? ResolvePlayer(
 447        DuckDBConnection connection,
 448        Wm26LineupSeedRow row)
 449    {
 1450        if (!string.IsNullOrWhiteSpace(row.TransfermarktPlayerId))
 451        {
 1452            return GetPlayerById(connection, row.TransfermarktPlayerId);
 453        }
 454
 1455        if (string.IsNullOrWhiteSpace(row.TransfermarktNationalTeamId))
 456        {
 0457            return null;
 458        }
 459
 1460        var candidates = GetPlayersByNationalTeamId(connection, row.TransfermarktNationalTeamId);
 1461        var normalizedName = NormalizeName(row.Name);
 1462        var matches = candidates
 1463            .Where(candidate => NormalizeName(candidate.Name) == normalizedName)
 1464            .ToList();
 465
 1466        return matches.Count switch
 1467        {
 1468            0 => null,
 1469            1 => matches[0],
 1470            _ => throw new InvalidOperationException(
 1471                $"Player {row.Name} matched multiple Transfermarkt players: {string.Join(", ", matches.Select(match => m
 1472        };
 473    }
 474
 475    private static Wm26LineupPlayerRecord? GetPlayerById(DuckDBConnection connection, string playerId)
 476    {
 1477        using var command = connection.CreateCommand();
 1478        command.CommandText =
 1479            """
 1480            select
 1481                player_id,
 1482                name,
 1483                date_of_birth,
 1484                position,
 1485                market_value_in_eur,
 1486                current_national_team_id
 1487            from players
 1488            where cast(player_id as varchar) = $player_id
 1489            """;
 1490        command.Parameters.Add(new DuckDBParameter("player_id", playerId));
 491
 1492        using var reader = command.ExecuteReader();
 1493        return reader.Read() ? ReadPlayer(reader) : null;
 1494    }
 495
 496    private static IReadOnlyList<Wm26LineupPlayerRecord> GetPlayersByNationalTeamId(
 497        DuckDBConnection connection,
 498        string nationalTeamId)
 499    {
 1500        using var command = connection.CreateCommand();
 1501        command.CommandText =
 1502            """
 1503            select
 1504                player_id,
 1505                name,
 1506                date_of_birth,
 1507                position,
 1508                market_value_in_eur,
 1509                current_national_team_id
 1510            from players
 1511            where cast(current_national_team_id as varchar) = $national_team_id
 1512            """;
 1513        command.Parameters.Add(new DuckDBParameter("national_team_id", nationalTeamId));
 514
 1515        using var reader = command.ExecuteReader();
 1516        var players = new List<Wm26LineupPlayerRecord>();
 1517        while (reader.Read())
 518        {
 1519            players.Add(ReadPlayer(reader));
 520        }
 521
 1522        return players;
 1523    }
 524
 525    private static string GetCoachName(DuckDBConnection connection, string nationalTeamId)
 526    {
 1527        using var command = connection.CreateCommand();
 1528        command.CommandText =
 1529            """
 1530            select coach_name
 1531            from national_teams
 1532            where cast(national_team_id as varchar) = $national_team_id
 1533            """;
 1534        command.Parameters.Add(new DuckDBParameter("national_team_id", nationalTeamId));
 535
 1536        var value = command.ExecuteScalar();
 1537        return value is null or DBNull ? string.Empty : Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim() ??
 1538    }
 539
 540    private static Wm26LineupPlayerRecord ReadPlayer(System.Data.Common.DbDataReader reader)
 541    {
 1542        return new Wm26LineupPlayerRecord(
 1543            Convert.ToString(reader.GetValue(0), CultureInfo.InvariantCulture) ?? string.Empty,
 1544            Convert.ToString(reader.GetValue(1), CultureInfo.InvariantCulture)?.Trim() ?? string.Empty,
 1545            reader.IsDBNull(2) ? null : reader.GetValue(2),
 1546            reader.IsDBNull(3) ? string.Empty : Convert.ToString(reader.GetValue(3), CultureInfo.InvariantCulture)?.Trim
 1547            reader.IsDBNull(4) ? null : reader.GetValue(4),
 1548            reader.IsDBNull(5) ? string.Empty : Convert.ToString(reader.GetValue(5), CultureInfo.InvariantCulture)?.Trim
 549    }
 550
 551    private static string? ProvidedValue(string value)
 552    {
 1553        return string.IsNullOrWhiteSpace(value) || string.Equals(value, MissingValue, StringComparison.OrdinalIgnoreCase
 1554            ? null
 1555            : value;
 556    }
 557
 558    private static string CalculateAgeOrMissing(object? dateOfBirth, DateOnly collectedAt)
 559    {
 1560        if (dateOfBirth is null or DBNull)
 561        {
 0562            return MissingValue;
 563        }
 564
 565        DateOnly born;
 1566        if (dateOfBirth is DateTime dateTime)
 567        {
 0568            born = DateOnly.FromDateTime(dateTime);
 569        }
 1570        else if (dateOfBirth is DateOnly dateOnly)
 571        {
 1572            born = dateOnly;
 573        }
 574        else
 575        {
 0576            var text = Convert.ToString(dateOfBirth, CultureInfo.InvariantCulture)?.Trim();
 0577            if (string.IsNullOrWhiteSpace(text)
 0578                || !DateOnly.TryParseExact(
 0579                    text[..Math.Min(text.Length, 10)],
 0580                    "yyyy-MM-dd",
 0581                    CultureInfo.InvariantCulture,
 0582                    DateTimeStyles.None,
 0583                    out born))
 584            {
 0585                return MissingValue;
 586            }
 587        }
 588
 1589        var age = collectedAt.Year - born.Year;
 1590        if (collectedAt.Month < born.Month || (collectedAt.Month == born.Month && collectedAt.Day < born.Day))
 591        {
 1592            age--;
 593        }
 594
 1595        return age < 0 ? MissingValue : age.ToString(CultureInfo.InvariantCulture);
 596    }
 597
 598    private static string FormatMarketValueOrMissing(object? value)
 599    {
 1600        if (value is null or DBNull)
 601        {
 1602            return MissingValue;
 603        }
 604
 1605        if (!long.TryParse(Convert.ToString(value, CultureInfo.InvariantCulture), NumberStyles.Integer, CultureInfo.Inva
 606        {
 0607            return MissingValue;
 608        }
 609
 1610        return marketValue == 0 ? MissingValue : marketValue.ToString(CultureInfo.InvariantCulture);
 611    }
 612
 613    private static IReadOnlyList<Wm26GroupedLineupRows> GroupRowsByManifest(
 614        IReadOnlyList<Wm26LineupTeam> teams,
 615        IReadOnlyList<Wm26LineupOutputRow> rows)
 616    {
 1617        var grouped = teams
 1618            .Select(team => new Wm26GroupedLineupRows(team, []))
 1619            .ToDictionary(entry => entry.Team.Slug, StringComparer.OrdinalIgnoreCase);
 620
 1621        foreach (var row in rows)
 622        {
 1623            if (!grouped.TryGetValue(row.TeamSlug, out var group))
 624            {
 0625                group = new Wm26GroupedLineupRows(new Wm26LineupTeam(row.TeamSlug, row.Team), []);
 0626                grouped[row.TeamSlug] = group;
 627            }
 628
 1629            group.Rows.Add(row);
 630        }
 631
 1632        return grouped.Values.ToList();
 633    }
 634
 635    private static void ValidateCoaches(IReadOnlyList<Wm26GroupedLineupRows> groupedRows)
 636    {
 1637        var teamsWithoutCoach = groupedRows
 1638            .Where(group => group.Rows.Count > 0
 1639                            && group.Rows.All(row => !string.Equals(row.Role, "Coach", StringComparison.Ordinal)))
 0640            .Select(group => group.Team.Slug)
 1641            .ToList();
 642
 1643        if (teamsWithoutCoach.Count > 0)
 644        {
 0645            throw new InvalidOperationException(
 0646                $"Lineup source has teams without Coach rows: {string.Join(", ", teamsWithoutCoach)}");
 647        }
 1648    }
 649
 650    private static IReadOnlyList<Wm26LineupMissingData> BuildMissingSourceData(
 651        IReadOnlyList<Wm26LineupOutputRow> rows)
 652    {
 1653        return rows
 1654            .Where(row => string.Equals(row.Role, "Player", StringComparison.Ordinal))
 1655            .Select(row => new
 1656            {
 1657                Row = row,
 1658                Fields = new[] { ("Age", row.Age), ("Position", row.Position), ("Market_Value_EUR", row.MarketValueEur) 
 1659                    .Where(field => string.Equals(field.Item2, MissingValue, StringComparison.OrdinalIgnoreCase))
 1660                    .Select(field => field.Item1)
 1661                    .ToList()
 1662            })
 1663            .Where(entry => entry.Fields.Count > 0)
 1664            .Select(entry => new Wm26LineupMissingData(
 1665                entry.Row.TeamSlug,
 1666                entry.Row.Team,
 1667                entry.Row.Name,
 1668                entry.Fields))
 1669            .ToList();
 670    }
 671
 672    private static string RenderCsv(IEnumerable<Wm26LineupOutputRow> rows)
 673    {
 1674        using var writer = new StringWriter(CultureInfo.InvariantCulture);
 1675        using var csv = new CsvWriter(
 1676            writer,
 1677            new CsvConfiguration(CultureInfo.InvariantCulture)
 1678            {
 1679                NewLine = "\r\n"
 1680            });
 681
 1682        foreach (var column in OutputColumns)
 683        {
 1684            csv.WriteField(column);
 685        }
 686
 1687        csv.NextRecord();
 688
 1689        foreach (var row in rows)
 690        {
 1691            csv.WriteField(row.Team);
 1692            csv.WriteField(row.DataCollectedAt);
 1693            csv.WriteField(row.Role);
 1694            csv.WriteField(row.Name);
 1695            csv.WriteField(row.Age);
 1696            csv.WriteField(row.Position);
 1697            csv.WriteField(FormatMarketValueForOutput(row.MarketValueEur));
 1698            csv.NextRecord();
 699        }
 700
 1701        return writer.ToString();
 1702    }
 703
 704    private static string FormatMarketValueForOutput(string value)
 705    {
 1706        if (string.IsNullOrWhiteSpace(value) || string.Equals(value, MissingValue, StringComparison.OrdinalIgnoreCase))
 707        {
 1708            return value;
 709        }
 710
 1711        var digits = value.Replace(".", string.Empty, StringComparison.Ordinal);
 1712        return long.TryParse(digits, NumberStyles.Integer, CultureInfo.InvariantCulture, out var marketValue)
 1713            ? marketValue.ToString("N0", CultureInfo.InvariantCulture).Replace(",", ".", StringComparison.Ordinal)
 1714            : value;
 715    }
 716
 717    private static string NormalizeName(string value)
 718    {
 1719        var normalized = value.Normalize(NormalizationForm.FormKD);
 1720        var builder = new StringBuilder();
 721
 1722        foreach (var character in normalized)
 723        {
 1724            var category = CharUnicodeInfo.GetUnicodeCategory(character);
 1725            if (category != UnicodeCategory.NonSpacingMark)
 726            {
 1727                builder.Append(char.ToLowerInvariant(character));
 728            }
 729        }
 730
 1731        return NonAlphanumericRegex.Replace(builder.ToString(), " ").Trim();
 732    }
 733
 1734    private sealed record Wm26LineupSeedRow(
 1735        string TeamSlug,
 1736        string Team,
 1737        string DataCollectedAt,
 1738        string Role,
 1739        string Name,
 1740        string TransfermarktNationalTeamId,
 1741        string TransfermarktPlayerId,
 1742        string Age,
 1743        string Position,
 1744        string MarketValueEur);
 745
 1746    private sealed record Wm26LineupOutputRow(
 1747        string TeamSlug,
 1748        string Team,
 1749        string DataCollectedAt,
 1750        string Role,
 1751        string Name,
 1752        string Age,
 1753        string Position,
 1754        string MarketValueEur);
 755
 1756    private sealed record Wm26LineupPlayerRecord(
 1757        string PlayerId,
 1758        string Name,
 1759        object? DateOfBirth,
 1760        string Position,
 1761        object? MarketValueInEur,
 1762        string CurrentNationalTeamId);
 763
 1764    private sealed record Wm26GroupedLineupRows(Wm26LineupTeam Team, List<Wm26LineupOutputRow> Rows);
 765}
 766
 767internal sealed class Wm26TransfermarktDuckDbProvider : IWm26TransfermarktDuckDbProvider
 768{
 769    public const string DefaultDuckDbUrl =
 770        "https://pub-e682421888d945d684bcae8890b0ec20.r2.dev/data/transfermarkt-datasets.duckdb";
 771
 772    public const string DefaultCachePath = "data/wm26/lineups/private/data/transfermarkt-datasets.duckdb";
 773
 774    private readonly HttpClient _httpClient;
 775    private readonly ILogger<Wm26TransfermarktDuckDbProvider> _logger;
 776
 777    public Wm26TransfermarktDuckDbProvider(
 778        HttpClient httpClient,
 779        ILogger<Wm26TransfermarktDuckDbProvider> logger)
 780    {
 781        _httpClient = httpClient;
 782        _logger = logger;
 783    }
 784
 785    public async Task<string> GetDatabasePathAsync(
 786        string? configuredPath,
 787        CancellationToken cancellationToken = default)
 788    {
 789        if (!string.IsNullOrWhiteSpace(configuredPath))
 790        {
 791            var path = Path.GetFullPath(configuredPath);
 792            if (!File.Exists(path))
 793            {
 794                throw new FileNotFoundException($"DuckDB database not found: {path}", path);
 795            }
 796
 797            return path;
 798        }
 799
 800        var cachePath = Path.GetFullPath(DefaultCachePath);
 801        Directory.CreateDirectory(Path.GetDirectoryName(cachePath)!);
 802        var tempPath = $"{cachePath}.download";
 803
 804        _logger.LogInformation("Refreshing Transfermarkt DuckDB snapshot from {Url}", DefaultDuckDbUrl);
 805        using var response = await _httpClient.GetAsync(DefaultDuckDbUrl, HttpCompletionOption.ResponseHeadersRead, canc
 806        response.EnsureSuccessStatusCode();
 807
 808        await using (var source = await response.Content.ReadAsStreamAsync(cancellationToken))
 809        await using (var target = new FileStream(tempPath, FileMode.Create, FileAccess.Write, FileShare.None))
 810        {
 811            await source.CopyToAsync(target, cancellationToken);
 812        }
 813
 814        File.Move(tempPath, cachePath, overwrite: true);
 815        return cachePath;
 816    }
 817}
 818
 819public sealed record Wm26LineupSourceRequest(
 820    string SeedPath,
 821    string TeamsPath,
 822    string? DuckDbPath);
 823
 824public sealed record Wm26LineupCollection(
 825    string SeedPath,
 826    string TeamsPath,
 827    string DuckDbPath,
 828    int SeedRowCount,
 829    int EnrichedRowCount,
 830    IReadOnlyList<Wm26LineupDocument> ContextDocuments,
 831    string KpiContent,
 832    IReadOnlyList<Wm26LineupTeam> HeaderOnlyTeams,
 833    IReadOnlyList<Wm26LineupMissingData> MissingSourceData);
 834
 835public sealed record Wm26LineupDocument(
 836    string DocumentName,
 837    string Content,
 838    string TeamName,
 839    int PlayerCount,
 840    bool IsHeaderOnly);
 841
 842public sealed record Wm26LineupTeam(string Slug, string Name);
 843
 844public sealed record Wm26LineupMissingData(
 845    string TeamSlug,
 846    string TeamName,
 847    string PlayerName,
 848    IReadOnlyList<string> Fields);
Generated by: ReportGenerator 5.5.10.0
06/15/2026 - 23:01:29
GitHub | reportgenerator.io

Methods/Properties

.cctor()
.ctor(Orchestrator.Commands.Operations.CollectContext.IWm26TransfermarktDuckDbProvider)
CollectAsync()
ResolvePath(string)
GetSlugFromDocumentName(string)
ReadTeamManifest(string)
ReadSeedRows(string)
CreateReader(System.IO.TextReader)
ValidateColumns(CsvHelper.CsvReader, System.Collections.Generic.IReadOnlyList<string>, string)
GetTrimmedField(CsvHelper.CsvReader, string)
GetOptionalField(CsvHelper.CsvReader, string)
ValidateSeedRow(Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupSeedRow, int)
ValidateOutputRow(Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupOutputRow, int)
IsValidRole(string)
ValidateAge(string, int)
ValidateMarketValue(string, string, int)
EnrichRows(System.Collections.Generic.IReadOnlyList<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupSeedRow>, string)
EnrichCoachRow(DuckDB.NET.Data.DuckDBConnection, Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupSeedRow)
EnrichPlayerRow(DuckDB.NET.Data.DuckDBConnection, Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupSeedRow)
ResolvePlayer(DuckDB.NET.Data.DuckDBConnection, Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupSeedRow)
GetPlayerById(DuckDB.NET.Data.DuckDBConnection, string)
GetPlayersByNationalTeamId(DuckDB.NET.Data.DuckDBConnection, string)
GetCoachName(DuckDB.NET.Data.DuckDBConnection, string)
ReadPlayer(System.Data.Common.DbDataReader)
ProvidedValue(string)
CalculateAgeOrMissing(object, System.DateOnly)
FormatMarketValueOrMissing(object)
GroupRowsByManifest(System.Collections.Generic.IReadOnlyList<Orchestrator.Commands.Operations.CollectContext.Wm26LineupTeam>, System.Collections.Generic.IReadOnlyList<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupOutputRow>)
ValidateCoaches(System.Collections.Generic.IReadOnlyList<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26GroupedLineupRows>)
BuildMissingSourceData(System.Collections.Generic.IReadOnlyList<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupOutputRow>)
RenderCsv(System.Collections.Generic.IEnumerable<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupOutputRow>)
FormatMarketValueForOutput(string)
NormalizeName(string)
.ctor(string, string, string, string, string, string, string, string, string, string)
.ctor(string, string, string, string, string, string, string, string)
.ctor(string, string, object, string, object, string)
.ctor(Orchestrator.Commands.Operations.CollectContext.Wm26LineupTeam, System.Collections.Generic.List<Orchestrator.Commands.Operations.CollectContext.Wm26LineupSource.Wm26LineupOutputRow>)