| | | 1 | | using System.Globalization; |
| | | 2 | | using CsvHelper; |
| | | 3 | | |
| | | 4 | | namespace EHonda.KicktippAi.Core; |
| | | 5 | | |
| | 1 | 6 | | public sealed record HistoryDateMapEntry( |
| | 1 | 7 | | string DocumentName, |
| | 1 | 8 | | string Competition, |
| | 1 | 9 | | string HomeTeam, |
| | 1 | 10 | | string AwayTeam, |
| | 1 | 11 | | string Score, |
| | 1 | 12 | | string Annotation, |
| | 1 | 13 | | string PlayedAt, |
| | 1 | 14 | | string SourceName, |
| | 1 | 15 | | string SourceUrl, |
| | 1 | 16 | | string VerifiedAt, |
| | 1 | 17 | | string Notes); |
| | | 18 | | |
| | | 19 | | public sealed record HistoryDateMapApplyResult |
| | | 20 | | { |
| | | 21 | | public HistoryDateMapApplyResult( |
| | | 22 | | string Content, |
| | | 23 | | int RowCount, |
| | | 24 | | int UpdatedRowCount, |
| | | 25 | | IReadOnlyList<HistoryDateMapEntry> MissingEntries, |
| | | 26 | | int PreservedRowCount = 0, |
| | | 27 | | int SkippedRowCount = 0, |
| | | 28 | | IReadOnlyList<HistoryDateMapEntry>? MissingPredictionEntries = null) |
| | | 29 | | { |
| | | 30 | | this.Content = Content; |
| | | 31 | | this.RowCount = RowCount; |
| | | 32 | | this.UpdatedRowCount = UpdatedRowCount; |
| | | 33 | | this.MissingEntries = MissingEntries; |
| | | 34 | | this.PreservedRowCount = PreservedRowCount; |
| | | 35 | | this.SkippedRowCount = SkippedRowCount; |
| | | 36 | | this.MissingPredictionEntries = MissingPredictionEntries ?? Array.Empty<HistoryDateMapEntry>(); |
| | | 37 | | } |
| | | 38 | | |
| | | 39 | | public string Content { get; init; } |
| | | 40 | | public int RowCount { get; init; } |
| | | 41 | | public int UpdatedRowCount { get; init; } |
| | | 42 | | public IReadOnlyList<HistoryDateMapEntry> MissingEntries { get; init; } |
| | | 43 | | public int PreservedRowCount { get; init; } |
| | | 44 | | public int SkippedRowCount { get; init; } |
| | | 45 | | public IReadOnlyList<HistoryDateMapEntry> MissingPredictionEntries { get; init; } |
| | | 46 | | } |
| | | 47 | | |
| | | 48 | | public sealed record HistoryDateMapApplyOptions( |
| | | 49 | | bool ApplyKnownOnly = false, |
| | | 50 | | DateOnly? PreserveCollectedOnOrAfter = null, |
| | | 51 | | IReadOnlyList<HistoryDateMapEntry>? PredictionDateEntries = null) |
| | | 52 | | { |
| | | 53 | | public static HistoryDateMapApplyOptions Strict { get; } = new(); |
| | | 54 | | } |
| | | 55 | | |
| | | 56 | | /// <summary> |
| | | 57 | | /// Utility class for handling date columns in history CSV documents. |
| | | 58 | | /// </summary> |
| | | 59 | | public static class HistoryCsvUtility |
| | | 60 | | { |
| | | 61 | | public const string DataCollectedAtColumnName = "Data_Collected_At"; |
| | | 62 | | public const string PlayedAtColumnName = "Played_At"; |
| | | 63 | | |
| | | 64 | | private static readonly string[] DateMapHeaders = |
| | | 65 | | [ |
| | | 66 | | "DocumentName", |
| | | 67 | | "Competition", |
| | | 68 | | "Home_Team", |
| | | 69 | | "Away_Team", |
| | | 70 | | "Score", |
| | | 71 | | "Annotation", |
| | | 72 | | PlayedAtColumnName, |
| | | 73 | | "Source_Name", |
| | | 74 | | "Source_Url", |
| | | 75 | | "Verified_At", |
| | | 76 | | "Notes" |
| | | 77 | | ]; |
| | | 78 | | |
| | | 79 | | private static readonly string[] PlayedAtTimestampFormats = |
| | | 80 | | [ |
| | | 81 | | "yyyy-MM-dd'T'HH:mm:sszzz", |
| | | 82 | | "yyyy-MM-dd'T'HH:mm:ss.FFFFFFFzzz" |
| | | 83 | | ]; |
| | | 84 | | |
| | | 85 | | private enum ExistingDateTreatment |
| | | 86 | | { |
| | | 87 | | None, |
| | | 88 | | ReplaceFromPrediction, |
| | | 89 | | PreserveExistingTimestamp |
| | | 90 | | } |
| | | 91 | | |
| | | 92 | | /// <summary> |
| | | 93 | | /// Adds or updates the Data_Collected_At column in a history CSV document. |
| | | 94 | | /// </summary> |
| | | 95 | | /// <param name="csvContent">The original CSV content.</param> |
| | | 96 | | /// <param name="previousCsvContent">The previous version of the CSV content (null if this is the first version).</p |
| | | 97 | | /// <param name="collectedDate">The date when the data was collected (e.g., "2025-08-30").</param> |
| | | 98 | | /// <returns>The updated CSV content with Data_Collected_At column.</returns> |
| | | 99 | | public static string AddDataCollectedAtColumn(string csvContent, string? previousCsvContent, string collectedDate) |
| | | 100 | | { |
| | | 101 | | // Check if the CSV already has a history date column. |
| | | 102 | | if (HasHistoryDateColumn(csvContent)) |
| | | 103 | | { |
| | | 104 | | return csvContent; // Already has the column |
| | | 105 | | } |
| | | 106 | | |
| | | 107 | | // Extract matches from previous version to get their collection dates |
| | | 108 | | var previousMatches = previousCsvContent != null |
| | | 109 | | ? ExtractMatchesWithCollectionDates(previousCsvContent) |
| | | 110 | | : new Dictionary<string, string>(); |
| | | 111 | | |
| | | 112 | | // Extract current matches |
| | | 113 | | var currentMatches = ExtractMatches(csvContent); |
| | | 114 | | |
| | | 115 | | // Build the new CSV with Data_Collected_At column |
| | | 116 | | var updatedCsvContent = BuildCsvWithDataCollectedAt(csvContent, currentMatches, previousMatches, collectedDate); |
| | | 117 | | |
| | | 118 | | // When the previous version already used Played_At and the rebuilt rows plus dates |
| | | 119 | | // are otherwise identical, keep the previous payload unchanged to avoid transient |
| | | 120 | | // version churn in WM26 collect-context -> date-map workflows. |
| | | 121 | | if (HasPlayedAtColumn(previousCsvContent)) |
| | | 122 | | { |
| | | 123 | | var playedAtVariant = ReplaceDataCollectedAtHeaderWithPlayedAt(updatedCsvContent); |
| | | 124 | | if (string.Equals( |
| | | 125 | | NormalizeLineEndings(playedAtVariant), |
| | | 126 | | NormalizeLineEndings(previousCsvContent!), |
| | | 127 | | StringComparison.Ordinal)) |
| | | 128 | | { |
| | | 129 | | return previousCsvContent; |
| | | 130 | | } |
| | | 131 | | } |
| | | 132 | | |
| | | 133 | | return updatedCsvContent; |
| | | 134 | | } |
| | | 135 | | |
| | | 136 | | public static IReadOnlyList<HistoryDateMapEntry> ReadDateMapEntries(string csvContent) |
| | | 137 | | { |
| | | 138 | | var entries = new List<HistoryDateMapEntry>(); |
| | | 139 | | if (string.IsNullOrWhiteSpace(csvContent)) |
| | | 140 | | { |
| | | 141 | | return entries.AsReadOnly(); |
| | | 142 | | } |
| | | 143 | | |
| | | 144 | | using var reader = new StringReader(csvContent); |
| | | 145 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 146 | | |
| | | 147 | | try |
| | | 148 | | { |
| | | 149 | | csv.Read(); |
| | | 150 | | csv.ReadHeader(); |
| | | 151 | | |
| | | 152 | | while (csv.Read()) |
| | | 153 | | { |
| | | 154 | | entries.Add(new HistoryDateMapEntry( |
| | | 155 | | GetOptionalField(csv, "DocumentName"), |
| | | 156 | | GetOptionalField(csv, "Competition"), |
| | | 157 | | GetOptionalField(csv, "Home_Team"), |
| | | 158 | | GetOptionalField(csv, "Away_Team"), |
| | | 159 | | GetOptionalField(csv, "Score"), |
| | | 160 | | GetOptionalField(csv, "Annotation"), |
| | | 161 | | GetOptionalField(csv, PlayedAtColumnName), |
| | | 162 | | GetOptionalField(csv, "Source_Name"), |
| | | 163 | | GetOptionalField(csv, "Source_Url"), |
| | | 164 | | GetOptionalField(csv, "Verified_At"), |
| | | 165 | | GetOptionalField(csv, "Notes"))); |
| | | 166 | | } |
| | | 167 | | } |
| | | 168 | | catch (Exception) |
| | | 169 | | { |
| | | 170 | | return Array.Empty<HistoryDateMapEntry>(); |
| | | 171 | | } |
| | | 172 | | |
| | | 173 | | return entries.AsReadOnly(); |
| | | 174 | | } |
| | | 175 | | |
| | | 176 | | public static string WriteDateMapEntries(IEnumerable<HistoryDateMapEntry> entries) |
| | | 177 | | { |
| | | 178 | | using var writer = new StringWriter(); |
| | | 179 | | using var csvWriter = new CsvWriter(writer, CultureInfo.InvariantCulture); |
| | | 180 | | |
| | | 181 | | foreach (var header in DateMapHeaders) |
| | | 182 | | { |
| | | 183 | | csvWriter.WriteField(header); |
| | | 184 | | } |
| | | 185 | | |
| | | 186 | | csvWriter.NextRecord(); |
| | | 187 | | |
| | | 188 | | foreach (var entry in entries) |
| | | 189 | | { |
| | | 190 | | csvWriter.WriteField(entry.DocumentName); |
| | | 191 | | csvWriter.WriteField(entry.Competition); |
| | | 192 | | csvWriter.WriteField(entry.HomeTeam); |
| | | 193 | | csvWriter.WriteField(entry.AwayTeam); |
| | | 194 | | csvWriter.WriteField(entry.Score); |
| | | 195 | | csvWriter.WriteField(entry.Annotation); |
| | | 196 | | csvWriter.WriteField(entry.PlayedAt); |
| | | 197 | | csvWriter.WriteField(entry.SourceName); |
| | | 198 | | csvWriter.WriteField(entry.SourceUrl); |
| | | 199 | | csvWriter.WriteField(entry.VerifiedAt); |
| | | 200 | | csvWriter.WriteField(entry.Notes); |
| | | 201 | | csvWriter.NextRecord(); |
| | | 202 | | } |
| | | 203 | | |
| | | 204 | | return writer.ToString(); |
| | | 205 | | } |
| | | 206 | | |
| | | 207 | | public static IReadOnlyList<HistoryDateMapEntry> ExtractDateMapEntries( |
| | | 208 | | string documentName, |
| | | 209 | | string csvContent, |
| | | 210 | | bool includeExistingDataCollectedAt = false) |
| | | 211 | | { |
| | | 212 | | var entries = new List<HistoryDateMapEntry>(); |
| | | 213 | | if (string.IsNullOrWhiteSpace(csvContent)) |
| | | 214 | | { |
| | | 215 | | return entries.AsReadOnly(); |
| | | 216 | | } |
| | | 217 | | |
| | | 218 | | using var reader = new StringReader(csvContent); |
| | | 219 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 220 | | |
| | | 221 | | try |
| | | 222 | | { |
| | | 223 | | csv.Read(); |
| | | 224 | | csv.ReadHeader(); |
| | | 225 | | |
| | | 226 | | while (csv.Read()) |
| | | 227 | | { |
| | | 228 | | var playedAt = includeExistingDataCollectedAt |
| | | 229 | | ? GetHistoryDateField(csv) |
| | | 230 | | : ""; |
| | | 231 | | |
| | | 232 | | entries.Add(new HistoryDateMapEntry( |
| | | 233 | | documentName, |
| | | 234 | | GetOptionalField(csv, "Competition"), |
| | | 235 | | GetOptionalField(csv, "Home_Team"), |
| | | 236 | | GetOptionalField(csv, "Away_Team"), |
| | | 237 | | GetOptionalField(csv, "Score"), |
| | | 238 | | GetOptionalField(csv, "Annotation"), |
| | | 239 | | playedAt, |
| | | 240 | | SourceName: "", |
| | | 241 | | SourceUrl: "", |
| | | 242 | | VerifiedAt: "", |
| | | 243 | | Notes: "")); |
| | | 244 | | } |
| | | 245 | | } |
| | | 246 | | catch (Exception) |
| | | 247 | | { |
| | | 248 | | return Array.Empty<HistoryDateMapEntry>(); |
| | | 249 | | } |
| | | 250 | | |
| | | 251 | | return entries.AsReadOnly(); |
| | | 252 | | } |
| | | 253 | | |
| | | 254 | | public static IReadOnlyList<HistoryDateMapEntry> ExtractRowsRequiringPredictionPlayedAt( |
| | | 255 | | string documentName, |
| | | 256 | | string csvContent, |
| | | 257 | | DateOnly preserveCollectedOnOrAfter) |
| | | 258 | | { |
| | | 259 | | var entries = new List<HistoryDateMapEntry>(); |
| | | 260 | | if (string.IsNullOrWhiteSpace(csvContent)) |
| | | 261 | | { |
| | | 262 | | return entries.AsReadOnly(); |
| | | 263 | | } |
| | | 264 | | |
| | | 265 | | using var reader = new StringReader(csvContent); |
| | | 266 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 267 | | |
| | | 268 | | try |
| | | 269 | | { |
| | | 270 | | csv.Read(); |
| | | 271 | | csv.ReadHeader(); |
| | | 272 | | |
| | | 273 | | while (csv.Read()) |
| | | 274 | | { |
| | | 275 | | var row = new HistoryDateMapEntry( |
| | | 276 | | documentName, |
| | | 277 | | GetOptionalField(csv, "Competition"), |
| | | 278 | | GetOptionalField(csv, "Home_Team"), |
| | | 279 | | GetOptionalField(csv, "Away_Team"), |
| | | 280 | | GetOptionalField(csv, "Score"), |
| | | 281 | | GetOptionalField(csv, "Annotation"), |
| | | 282 | | PlayedAt: GetHistoryDateField(csv), |
| | | 283 | | SourceName: "", |
| | | 284 | | SourceUrl: "", |
| | | 285 | | VerifiedAt: "", |
| | | 286 | | Notes: ""); |
| | | 287 | | |
| | | 288 | | if (IsWorldCupTournamentRow(row) && |
| | | 289 | | GetExistingDateTreatment(row.PlayedAt, preserveCollectedOnOrAfter) == ExistingDateTreatment.ReplaceF |
| | | 290 | | { |
| | | 291 | | entries.Add(row); |
| | | 292 | | } |
| | | 293 | | } |
| | | 294 | | } |
| | | 295 | | catch (Exception) |
| | | 296 | | { |
| | | 297 | | return Array.Empty<HistoryDateMapEntry>(); |
| | | 298 | | } |
| | | 299 | | |
| | | 300 | | return entries.AsReadOnly(); |
| | | 301 | | } |
| | | 302 | | |
| | | 303 | | public static HistoryDateMapApplyResult ApplyDateMap( |
| | | 304 | | string documentName, |
| | | 305 | | string csvContent, |
| | | 306 | | IReadOnlyList<HistoryDateMapEntry> dateMapEntries, |
| | | 307 | | HistoryDateMapApplyOptions? options = null) |
| | | 308 | | { |
| | | 309 | | options ??= HistoryDateMapApplyOptions.Strict; |
| | | 310 | | |
| | | 311 | | var dateMap = dateMapEntries |
| | | 312 | | .Where(entry => string.Equals(entry.DocumentName, documentName, StringComparison.OrdinalIgnoreCase)) |
| | | 313 | | .GroupBy(CreateDateMapKey, StringComparer.OrdinalIgnoreCase) |
| | | 314 | | .ToDictionary( |
| | | 315 | | group => group.Key, |
| | | 316 | | group => new Queue<HistoryDateMapEntry>(group), |
| | | 317 | | StringComparer.OrdinalIgnoreCase); |
| | | 318 | | var predictionDateMap = (options.PredictionDateEntries ?? Array.Empty<HistoryDateMapEntry>()) |
| | | 319 | | .Where(entry => string.Equals(entry.DocumentName, documentName, StringComparison.OrdinalIgnoreCase)) |
| | | 320 | | .GroupBy(CreateDateMapKey, StringComparer.OrdinalIgnoreCase) |
| | | 321 | | .ToDictionary( |
| | | 322 | | group => group.Key, |
| | | 323 | | group => group.Last().PlayedAt.Trim(), |
| | | 324 | | StringComparer.OrdinalIgnoreCase); |
| | | 325 | | |
| | | 326 | | var missingEntries = new List<HistoryDateMapEntry>(); |
| | | 327 | | var missingPredictionEntries = new List<HistoryDateMapEntry>(); |
| | | 328 | | var rows = new List<HistoryDateMapEntry>(); |
| | | 329 | | var updatedRowCount = 0; |
| | | 330 | | var preservedRowCount = 0; |
| | | 331 | | var skippedRowCount = 0; |
| | | 332 | | |
| | | 333 | | if (string.IsNullOrWhiteSpace(csvContent)) |
| | | 334 | | { |
| | | 335 | | return new HistoryDateMapApplyResult(csvContent, RowCount: 0, UpdatedRowCount: 0, missingEntries); |
| | | 336 | | } |
| | | 337 | | |
| | | 338 | | using var reader = new StringReader(csvContent); |
| | | 339 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 340 | | |
| | | 341 | | try |
| | | 342 | | { |
| | | 343 | | csv.Read(); |
| | | 344 | | csv.ReadHeader(); |
| | | 345 | | |
| | | 346 | | while (csv.Read()) |
| | | 347 | | { |
| | | 348 | | var row = new HistoryDateMapEntry( |
| | | 349 | | documentName, |
| | | 350 | | GetOptionalField(csv, "Competition"), |
| | | 351 | | GetOptionalField(csv, "Home_Team"), |
| | | 352 | | GetOptionalField(csv, "Away_Team"), |
| | | 353 | | GetOptionalField(csv, "Score"), |
| | | 354 | | GetOptionalField(csv, "Annotation"), |
| | | 355 | | PlayedAt: GetHistoryDateField(csv), |
| | | 356 | | SourceName: "", |
| | | 357 | | SourceUrl: "", |
| | | 358 | | VerifiedAt: "", |
| | | 359 | | Notes: ""); |
| | | 360 | | |
| | | 361 | | if (options.PreserveCollectedOnOrAfter.HasValue) |
| | | 362 | | { |
| | | 363 | | var existingDateTreatment = GetExistingDateTreatment( |
| | | 364 | | row.PlayedAt, |
| | | 365 | | options.PreserveCollectedOnOrAfter.Value); |
| | | 366 | | |
| | | 367 | | if (existingDateTreatment == ExistingDateTreatment.PreserveExistingTimestamp) |
| | | 368 | | { |
| | | 369 | | preservedRowCount++; |
| | | 370 | | rows.Add(row); |
| | | 371 | | continue; |
| | | 372 | | } |
| | | 373 | | |
| | | 374 | | if (IsWorldCupTournamentRow(row) && |
| | | 375 | | existingDateTreatment == ExistingDateTreatment.ReplaceFromPrediction) |
| | | 376 | | { |
| | | 377 | | if (predictionDateMap.TryGetValue(CreateDateMapKey(row), out var predictedPlayedAt) && |
| | | 378 | | IsExactTimestamp(predictedPlayedAt)) |
| | | 379 | | { |
| | | 380 | | rows.Add(row with { PlayedAt = predictedPlayedAt }); |
| | | 381 | | if (!string.Equals(row.PlayedAt, predictedPlayedAt, StringComparison.Ordinal)) |
| | | 382 | | { |
| | | 383 | | updatedRowCount++; |
| | | 384 | | } |
| | | 385 | | |
| | | 386 | | continue; |
| | | 387 | | } |
| | | 388 | | |
| | | 389 | | if (dateMap.TryGetValue(CreateDateMapKey(row), out var predictionFallbackEntries) && |
| | | 390 | | predictionFallbackEntries.Count > 0 && |
| | | 391 | | IsExactDate(predictionFallbackEntries.Peek().PlayedAt)) |
| | | 392 | | { |
| | | 393 | | var fallbackDateMapEntry = predictionFallbackEntries.Dequeue(); |
| | | 394 | | var fallbackPlayedAt = fallbackDateMapEntry.PlayedAt.Trim(); |
| | | 395 | | rows.Add(row with { PlayedAt = fallbackPlayedAt }); |
| | | 396 | | if (!string.Equals(row.PlayedAt, fallbackPlayedAt, StringComparison.Ordinal)) |
| | | 397 | | { |
| | | 398 | | updatedRowCount++; |
| | | 399 | | } |
| | | 400 | | |
| | | 401 | | continue; |
| | | 402 | | } |
| | | 403 | | |
| | | 404 | | missingPredictionEntries.Add(row); |
| | | 405 | | rows.Add(row); |
| | | 406 | | continue; |
| | | 407 | | } |
| | | 408 | | } |
| | | 409 | | |
| | | 410 | | if (!dateMap.TryGetValue(CreateDateMapKey(row), out var dateMapEntriesForRow) || |
| | | 411 | | dateMapEntriesForRow.Count == 0) |
| | | 412 | | { |
| | | 413 | | if (!options.ApplyKnownOnly) |
| | | 414 | | { |
| | | 415 | | missingEntries.Add(row); |
| | | 416 | | } |
| | | 417 | | else |
| | | 418 | | { |
| | | 419 | | skippedRowCount++; |
| | | 420 | | } |
| | | 421 | | |
| | | 422 | | rows.Add(row); |
| | | 423 | | continue; |
| | | 424 | | } |
| | | 425 | | |
| | | 426 | | if (!IsExactDate(dateMapEntriesForRow.Peek().PlayedAt)) |
| | | 427 | | { |
| | | 428 | | var skippedDateMapEntry = dateMapEntriesForRow.Dequeue(); |
| | | 429 | | if (!options.ApplyKnownOnly) |
| | | 430 | | { |
| | | 431 | | missingEntries.Add(skippedDateMapEntry); |
| | | 432 | | } |
| | | 433 | | else |
| | | 434 | | { |
| | | 435 | | skippedRowCount++; |
| | | 436 | | } |
| | | 437 | | |
| | | 438 | | rows.Add(row); |
| | | 439 | | continue; |
| | | 440 | | } |
| | | 441 | | |
| | | 442 | | var dateMapEntry = dateMapEntriesForRow.Dequeue(); |
| | | 443 | | var playedAt = dateMapEntry.PlayedAt.Trim(); |
| | | 444 | | rows.Add(row with { PlayedAt = playedAt }); |
| | | 445 | | if (!string.Equals(row.PlayedAt, playedAt, StringComparison.Ordinal)) |
| | | 446 | | { |
| | | 447 | | updatedRowCount++; |
| | | 448 | | } |
| | | 449 | | } |
| | | 450 | | } |
| | | 451 | | catch (Exception) |
| | | 452 | | { |
| | | 453 | | return new HistoryDateMapApplyResult(csvContent, RowCount: 0, UpdatedRowCount: 0, missingEntries); |
| | | 454 | | } |
| | | 455 | | |
| | | 456 | | if (missingEntries.Count > 0 || missingPredictionEntries.Count > 0) |
| | | 457 | | { |
| | | 458 | | return new HistoryDateMapApplyResult( |
| | | 459 | | csvContent, |
| | | 460 | | rows.Count, |
| | | 461 | | UpdatedRowCount: 0, |
| | | 462 | | missingEntries, |
| | | 463 | | MissingPredictionEntries: missingPredictionEntries); |
| | | 464 | | } |
| | | 465 | | |
| | | 466 | | using var writer = new StringWriter(); |
| | | 467 | | using var csvWriter = new CsvWriter(writer, CultureInfo.InvariantCulture); |
| | | 468 | | |
| | | 469 | | csvWriter.WriteField("Competition"); |
| | | 470 | | csvWriter.WriteField(PlayedAtColumnName); |
| | | 471 | | csvWriter.WriteField("Home_Team"); |
| | | 472 | | csvWriter.WriteField("Away_Team"); |
| | | 473 | | csvWriter.WriteField("Score"); |
| | | 474 | | csvWriter.WriteField("Annotation"); |
| | | 475 | | csvWriter.NextRecord(); |
| | | 476 | | |
| | | 477 | | foreach (var row in rows) |
| | | 478 | | { |
| | | 479 | | csvWriter.WriteField(row.Competition); |
| | | 480 | | csvWriter.WriteField(row.PlayedAt); |
| | | 481 | | csvWriter.WriteField(row.HomeTeam); |
| | | 482 | | csvWriter.WriteField(row.AwayTeam); |
| | | 483 | | csvWriter.WriteField(row.Score); |
| | | 484 | | csvWriter.WriteField(row.Annotation); |
| | | 485 | | csvWriter.NextRecord(); |
| | | 486 | | } |
| | | 487 | | |
| | | 488 | | return new HistoryDateMapApplyResult( |
| | | 489 | | writer.ToString(), |
| | | 490 | | rows.Count, |
| | | 491 | | updatedRowCount, |
| | | 492 | | missingEntries, |
| | | 493 | | preservedRowCount, |
| | | 494 | | skippedRowCount, |
| | | 495 | | missingPredictionEntries); |
| | | 496 | | } |
| | | 497 | | |
| | | 498 | | /// <summary> |
| | | 499 | | /// Checks if the CSV content already has a recognized history date column. |
| | | 500 | | /// </summary> |
| | | 501 | | private static bool HasHistoryDateColumn(string csvContent) |
| | | 502 | | { |
| | | 503 | | var lines = csvContent.Split('\n', StringSplitOptions.RemoveEmptyEntries); |
| | | 504 | | if (lines.Length == 0) |
| | | 505 | | { |
| | | 506 | | return false; |
| | | 507 | | } |
| | | 508 | | |
| | | 509 | | var header = lines[0]; |
| | | 510 | | return header.Contains(DataCollectedAtColumnName, StringComparison.OrdinalIgnoreCase) |
| | | 511 | | || header.Contains(PlayedAtColumnName, StringComparison.OrdinalIgnoreCase); |
| | | 512 | | } |
| | | 513 | | |
| | | 514 | | private static bool HasPlayedAtColumn(string? csvContent) |
| | | 515 | | { |
| | | 516 | | if (string.IsNullOrWhiteSpace(csvContent)) |
| | | 517 | | { |
| | | 518 | | return false; |
| | | 519 | | } |
| | | 520 | | |
| | | 521 | | var lines = csvContent.Split('\n', StringSplitOptions.RemoveEmptyEntries); |
| | | 522 | | if (lines.Length == 0) |
| | | 523 | | { |
| | | 524 | | return false; |
| | | 525 | | } |
| | | 526 | | |
| | | 527 | | return lines[0].Contains(PlayedAtColumnName, StringComparison.OrdinalIgnoreCase); |
| | | 528 | | } |
| | | 529 | | |
| | | 530 | | private static string ReplaceDataCollectedAtHeaderWithPlayedAt(string csvContent) |
| | | 531 | | { |
| | | 532 | | return csvContent.Replace( |
| | | 533 | | DataCollectedAtColumnName, |
| | | 534 | | PlayedAtColumnName, |
| | | 535 | | StringComparison.Ordinal); |
| | | 536 | | } |
| | | 537 | | |
| | | 538 | | private static string NormalizeLineEndings(string value) |
| | | 539 | | { |
| | | 540 | | return value |
| | | 541 | | .Replace("\r\n", "\n", StringComparison.Ordinal) |
| | | 542 | | .Replace("\r", "\n", StringComparison.Ordinal); |
| | | 543 | | } |
| | | 544 | | |
| | | 545 | | /// <summary> |
| | | 546 | | /// Extracts matches from CSV content without a history date column. |
| | | 547 | | /// </summary> |
| | | 548 | | private static HashSet<string> ExtractMatches(string csvContent) |
| | | 549 | | { |
| | | 550 | | var matches = new HashSet<string>(); |
| | | 551 | | |
| | | 552 | | using var reader = new StringReader(csvContent); |
| | | 553 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 554 | | |
| | | 555 | | try |
| | | 556 | | { |
| | | 557 | | csv.Read(); |
| | | 558 | | csv.ReadHeader(); |
| | | 559 | | |
| | | 560 | | while (csv.Read()) |
| | | 561 | | { |
| | | 562 | | var competition = csv.GetField("Competition") ?? ""; |
| | | 563 | | var homeTeam = csv.GetField("Home_Team") ?? ""; |
| | | 564 | | var awayTeam = csv.GetField("Away_Team") ?? ""; |
| | | 565 | | var score = csv.GetField("Score") ?? ""; |
| | | 566 | | var annotation = (csv.TryGetField<string>("Annotation", out var ann) ? ann : null) ?? ""; |
| | | 567 | | |
| | | 568 | | var matchKey = CreateMatchKey(competition, homeTeam, awayTeam, score, annotation); |
| | | 569 | | matches.Add(matchKey); |
| | | 570 | | } |
| | | 571 | | } |
| | | 572 | | catch (Exception) |
| | | 573 | | { |
| | | 574 | | // If CSV parsing fails, return empty set |
| | | 575 | | } |
| | | 576 | | |
| | | 577 | | return matches; |
| | | 578 | | } |
| | | 579 | | |
| | | 580 | | /// <summary> |
| | | 581 | | /// Extracts matches with their collection dates from CSV content that has a history date column. |
| | | 582 | | /// </summary> |
| | | 583 | | private static Dictionary<string, string> ExtractMatchesWithCollectionDates(string csvContent) |
| | | 584 | | { |
| | | 585 | | var matches = new Dictionary<string, string>(); |
| | | 586 | | |
| | | 587 | | if (!HasHistoryDateColumn(csvContent)) |
| | | 588 | | { |
| | | 589 | | return matches; |
| | | 590 | | } |
| | | 591 | | |
| | | 592 | | using var reader = new StringReader(csvContent); |
| | | 593 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 594 | | |
| | | 595 | | try |
| | | 596 | | { |
| | | 597 | | csv.Read(); |
| | | 598 | | csv.ReadHeader(); |
| | | 599 | | |
| | | 600 | | while (csv.Read()) |
| | | 601 | | { |
| | | 602 | | var competition = csv.GetField("Competition") ?? ""; |
| | | 603 | | var historyDate = GetHistoryDateField(csv); |
| | | 604 | | var homeTeam = csv.GetField("Home_Team") ?? ""; |
| | | 605 | | var awayTeam = csv.GetField("Away_Team") ?? ""; |
| | | 606 | | var score = csv.GetField("Score") ?? ""; |
| | | 607 | | var annotation = (csv.TryGetField<string>("Annotation", out var ann) ? ann : null) ?? ""; |
| | | 608 | | |
| | | 609 | | var matchKey = CreateMatchKey(competition, homeTeam, awayTeam, score, annotation); |
| | | 610 | | matches[matchKey] = historyDate; |
| | | 611 | | } |
| | | 612 | | } |
| | | 613 | | catch (Exception) |
| | | 614 | | { |
| | | 615 | | // If CSV parsing fails, return empty dictionary |
| | | 616 | | } |
| | | 617 | | |
| | | 618 | | return matches; |
| | | 619 | | } |
| | | 620 | | |
| | | 621 | | /// <summary> |
| | | 622 | | /// Builds a new CSV with the Data_Collected_At column. |
| | | 623 | | /// </summary> |
| | | 624 | | private static string BuildCsvWithDataCollectedAt( |
| | | 625 | | string originalCsvContent, |
| | | 626 | | HashSet<string> currentMatches, |
| | | 627 | | Dictionary<string, string> previousMatches, |
| | | 628 | | string collectedDate) |
| | | 629 | | { |
| | | 630 | | using var reader = new StringReader(originalCsvContent); |
| | | 631 | | using var csv = new CsvReader(reader, CultureInfo.InvariantCulture); |
| | | 632 | | |
| | | 633 | | using var writer = new StringWriter(); |
| | | 634 | | using var csvWriter = new CsvWriter(writer, CultureInfo.InvariantCulture); |
| | | 635 | | |
| | | 636 | | try |
| | | 637 | | { |
| | | 638 | | csv.Read(); |
| | | 639 | | csv.ReadHeader(); |
| | | 640 | | |
| | | 641 | | // Write new header with Data_Collected_At after Competition |
| | | 642 | | csvWriter.WriteField("Competition"); |
| | | 643 | | csvWriter.WriteField(DataCollectedAtColumnName); |
| | | 644 | | csvWriter.WriteField("Home_Team"); |
| | | 645 | | csvWriter.WriteField("Away_Team"); |
| | | 646 | | csvWriter.WriteField("Score"); |
| | | 647 | | csvWriter.WriteField("Annotation"); |
| | | 648 | | csvWriter.NextRecord(); |
| | | 649 | | |
| | | 650 | | while (csv.Read()) |
| | | 651 | | { |
| | | 652 | | var competition = csv.GetField("Competition") ?? ""; |
| | | 653 | | var homeTeam = csv.GetField("Home_Team") ?? ""; |
| | | 654 | | var awayTeam = csv.GetField("Away_Team") ?? ""; |
| | | 655 | | var score = csv.GetField("Score") ?? ""; |
| | | 656 | | var annotation = (csv.TryGetField<string>("Annotation", out var ann) ? ann : null) ?? ""; |
| | | 657 | | |
| | | 658 | | var matchKey = CreateMatchKey(competition, homeTeam, awayTeam, score, annotation); |
| | | 659 | | |
| | | 660 | | // Determine the collection date for this match |
| | | 661 | | string dataCollectedAt; |
| | | 662 | | if (previousMatches.TryGetValue(matchKey, out var existingDate)) |
| | | 663 | | { |
| | | 664 | | // Match existed in previous version, use its existing date |
| | | 665 | | dataCollectedAt = existingDate; |
| | | 666 | | } |
| | | 667 | | else |
| | | 668 | | { |
| | | 669 | | // New match, use current collection date |
| | | 670 | | dataCollectedAt = collectedDate; |
| | | 671 | | } |
| | | 672 | | |
| | | 673 | | csvWriter.WriteField(competition); |
| | | 674 | | csvWriter.WriteField(dataCollectedAt); |
| | | 675 | | csvWriter.WriteField(homeTeam); |
| | | 676 | | csvWriter.WriteField(awayTeam); |
| | | 677 | | csvWriter.WriteField(score); |
| | | 678 | | csvWriter.WriteField(annotation); |
| | | 679 | | csvWriter.NextRecord(); |
| | | 680 | | } |
| | | 681 | | } |
| | | 682 | | catch (Exception) |
| | | 683 | | { |
| | | 684 | | // If parsing fails, return original content |
| | | 685 | | return originalCsvContent; |
| | | 686 | | } |
| | | 687 | | |
| | | 688 | | return writer.ToString(); |
| | | 689 | | } |
| | | 690 | | |
| | | 691 | | /// <summary> |
| | | 692 | | /// Creates a unique key for a match. |
| | | 693 | | /// </summary> |
| | | 694 | | private static string CreateMatchKey(string competition, string homeTeam, string awayTeam, string score, string anno |
| | | 695 | | { |
| | | 696 | | return $"{competition}|{homeTeam}|{awayTeam}|{score}|{annotation}"; |
| | | 697 | | } |
| | | 698 | | |
| | | 699 | | private static string CreateDateMapKey(HistoryDateMapEntry entry) |
| | | 700 | | { |
| | | 701 | | return CreateMatchKey( |
| | | 702 | | NormalizeKeyPart(entry.Competition), |
| | | 703 | | NormalizeKeyPart(entry.HomeTeam), |
| | | 704 | | NormalizeKeyPart(entry.AwayTeam), |
| | | 705 | | NormalizeKeyPart(entry.Score), |
| | | 706 | | NormalizeKeyPart(entry.Annotation)); |
| | | 707 | | } |
| | | 708 | | |
| | | 709 | | private static bool IsWorldCupTournamentRow(HistoryDateMapEntry entry) |
| | | 710 | | { |
| | | 711 | | return string.Equals( |
| | | 712 | | NormalizeKeyPart(entry.Competition), |
| | | 713 | | "WM", |
| | | 714 | | StringComparison.OrdinalIgnoreCase); |
| | | 715 | | } |
| | | 716 | | |
| | | 717 | | private static string NormalizeKeyPart(string? value) |
| | | 718 | | { |
| | | 719 | | return value?.Trim() ?? ""; |
| | | 720 | | } |
| | | 721 | | |
| | | 722 | | private static bool IsExactDate(string value) |
| | | 723 | | { |
| | | 724 | | return DateOnly.TryParseExact( |
| | | 725 | | value.Trim(), |
| | | 726 | | "yyyy-MM-dd", |
| | | 727 | | CultureInfo.InvariantCulture, |
| | | 728 | | DateTimeStyles.None, |
| | | 729 | | out _); |
| | | 730 | | } |
| | | 731 | | |
| | | 732 | | private static bool IsExactTimestamp(string value) |
| | | 733 | | { |
| | | 734 | | return DateTimeOffset.TryParseExact( |
| | | 735 | | value.Trim(), |
| | | 736 | | PlayedAtTimestampFormats, |
| | | 737 | | CultureInfo.InvariantCulture, |
| | | 738 | | DateTimeStyles.None, |
| | | 739 | | out _); |
| | | 740 | | } |
| | | 741 | | |
| | | 742 | | private static ExistingDateTreatment GetExistingDateTreatment(string value, DateOnly preserveCollectedOnOrAfter) |
| | | 743 | | { |
| | | 744 | | if (TryParseExactDate(value, out var collectedAt)) |
| | | 745 | | { |
| | | 746 | | return collectedAt >= preserveCollectedOnOrAfter |
| | | 747 | | ? ExistingDateTreatment.ReplaceFromPrediction |
| | | 748 | | : ExistingDateTreatment.None; |
| | | 749 | | } |
| | | 750 | | |
| | | 751 | | if (TryParseExactTimestampDate(value, out var playedAtDate) && playedAtDate >= preserveCollectedOnOrAfter) |
| | | 752 | | { |
| | | 753 | | return ExistingDateTreatment.PreserveExistingTimestamp; |
| | | 754 | | } |
| | | 755 | | |
| | | 756 | | return ExistingDateTreatment.None; |
| | | 757 | | } |
| | | 758 | | |
| | | 759 | | private static bool TryParseExactDate(string value, out DateOnly date) |
| | | 760 | | { |
| | | 761 | | return DateOnly.TryParseExact( |
| | | 762 | | value.Trim(), |
| | | 763 | | "yyyy-MM-dd", |
| | | 764 | | CultureInfo.InvariantCulture, |
| | | 765 | | DateTimeStyles.None, |
| | | 766 | | out date); |
| | | 767 | | } |
| | | 768 | | |
| | | 769 | | private static bool TryParseExactTimestampDate(string value, out DateOnly date) |
| | | 770 | | { |
| | | 771 | | if (DateTimeOffset.TryParseExact( |
| | | 772 | | value.Trim(), |
| | | 773 | | PlayedAtTimestampFormats, |
| | | 774 | | CultureInfo.InvariantCulture, |
| | | 775 | | DateTimeStyles.None, |
| | | 776 | | out var timestamp)) |
| | | 777 | | { |
| | | 778 | | date = DateOnly.FromDateTime(timestamp.DateTime); |
| | | 779 | | return true; |
| | | 780 | | } |
| | | 781 | | |
| | | 782 | | date = default; |
| | | 783 | | return false; |
| | | 784 | | } |
| | | 785 | | |
| | | 786 | | private static string GetHistoryDateField(CsvReader csv) |
| | | 787 | | { |
| | | 788 | | var playedAt = GetOptionalField(csv, PlayedAtColumnName); |
| | | 789 | | return string.IsNullOrWhiteSpace(playedAt) |
| | | 790 | | ? GetOptionalField(csv, DataCollectedAtColumnName) |
| | | 791 | | : playedAt; |
| | | 792 | | } |
| | | 793 | | |
| | | 794 | | private static string GetOptionalField(CsvReader csv, string fieldName) |
| | | 795 | | { |
| | | 796 | | return (csv.TryGetField<string>(fieldName, out var value) ? value : null) ?? ""; |
| | | 797 | | } |
| | | 798 | | } |