using System.Globalization; using ExpenseTracker.Api.Data; using ExpenseTracker.Api.DTOs; using ExpenseTracker.Api.Models; using Microsoft.EntityFrameworkCore; namespace ExpenseTracker.Api.Services; /// /// Parses a bank-export CSV (semicolon-separated, French locale) and imports /// the rows as records for the given month. /// /// Expected columns (1-based): /// 1 Date de comptabilisation → EntryDate (dd/MM/yyyy) /// 2 Libelle simplifie → (ignored) /// 3 Libelle operation → Label /// 4 Reference → (ignored) /// 5 Informations complementaires → (ignored) /// 6 Type operation → (ignored) /// 7 Categorie → (ignored) /// 8 Sous categorie → Category name (optional lookup) /// 9 Debit → Amount when negative → EntryType.EXPENSE /// 10 Credit → Amount when present → EntryType.INCOME /// 11+ (remaining columns ignored) /// /// Amount values use a comma as the decimal separator (French locale). /// Rows whose Debit and Credit are both empty are silently skipped. /// public class CsvImportService { private const int ColDate = 0; // Date de comptabilisation private const int ColLabel = 2; // Libelle operation private const int ColSousCategorie = 7; // Sous categorie private const int ColDebit = 8; // Debit (negative value or empty) private const int ColCredit = 9; // Credit (positive value or empty) private const int MinColumns = 10; private static readonly CultureInfo FrenchCulture = CultureInfo.GetCultureInfo("fr-FR"); private readonly ILogger _logger; public CsvImportService(ILogger logger) { _logger = logger; } public async Task ImportAsync(Guid monthId, Stream csvStream, AppDbContext db) { var errors = new List(); var validEntries = new List(); // Load all categories once for case-insensitive lookup. // "Autre" is the fallback category used when no Sous categorie matches. var categories = await db.Categories .AsNoTracking() .ToListAsync(); var fallbackCategory = categories.FirstOrDefault(c => c.Name.Equals("Autre", StringComparison.OrdinalIgnoreCase)); using var reader = new StreamReader(csvStream); // Skip header line var header = await reader.ReadLineAsync(); if (header is null) return new ImportResultDto(0, 0, errors); int lineNumber = 1; // header is line 1 string? line; while ((line = await reader.ReadLineAsync()) is not null) { lineNumber++; // Skip blank lines and lines that contain only semicolons (bank file padding) if (string.IsNullOrWhiteSpace(line) || line.Replace(";", "").Trim().Length == 0) continue; var columns = line.Split(';'); if (columns.Length < MinColumns) { errors.Add(new ImportErrorDto(lineNumber, $"La ligne ne contient pas les {MinColumns} colonnes attendues.")); continue; } var dateRaw = columns[ColDate].Trim(); var labelRaw = columns[ColLabel].Trim(); var sousCategorieRaw = columns[ColSousCategorie].Trim(); var debitRaw = columns[ColDebit].Trim(); var creditRaw = columns[ColCredit].Trim(); // Determine type and raw amount string bool hasDebit = !string.IsNullOrEmpty(debitRaw); bool hasCredit = !string.IsNullOrEmpty(creditRaw); if (!hasDebit && !hasCredit) { // Neither column filled — silently skip (padding row) continue; } var entryType = hasCredit ? EntryType.INCOME : EntryType.EXPENSE; var amountRaw = hasCredit ? creditRaw : debitRaw; // Parse date (dd/MM/yyyy) if (!DateOnly.TryParseExact(dateRaw, "dd/MM/yyyy", FrenchCulture, DateTimeStyles.None, out var entryDate)) { errors.Add(new ImportErrorDto(lineNumber, $"Date invalide '{dateRaw}'. Format attendu : JJ/MM/AAAA.")); continue; } // Parse amount — French locale uses comma as decimal separator. // Debit values may carry a leading minus sign; we store the absolute value. if (!decimal.TryParse(amountRaw, NumberStyles.Number, FrenchCulture, out var amount)) { errors.Add(new ImportErrorDto(lineNumber, $"Montant invalide '{amountRaw}'.")); continue; } amount = Math.Abs(amount); if (amount <= 0) { errors.Add(new ImportErrorDto(lineNumber, $"Le montant doit être supérieur à zéro (valeur lue : '{amountRaw}').")); continue; } // Validate label if (string.IsNullOrWhiteSpace(labelRaw)) { errors.Add(new ImportErrorDto(lineNumber, "Le libellé de l'opération est vide.")); continue; } if (labelRaw.Length > 200) { errors.Add(new ImportErrorDto(lineNumber, "Le libellé dépasse 200 caractères.")); continue; } // Category lookup — the CSV "Sous categorie" value (e.g. "Banque et assurance - autre") // is matched against stored category names (e.g. "Banque et assurance") using a // Contains check: the stored name must appear as a substring of the CSV value. // This handles the " - " suffixes present in bank exports. // If no category matches, the entry falls back to the "Autre" category. Guid? categoryId = null; if (!string.IsNullOrWhiteSpace(sousCategorieRaw)) { var category = categories.FirstOrDefault(c => !c.Name.Equals("Autre", StringComparison.OrdinalIgnoreCase) && sousCategorieRaw.Contains(c.Name, StringComparison.OrdinalIgnoreCase)); categoryId = category?.Id ?? fallbackCategory?.Id; } else { categoryId = fallbackCategory?.Id; } validEntries.Add(new Entry { Id = Guid.NewGuid(), MonthId = monthId, Type = entryType, Amount = amount, Label = labelRaw, CategoryId = categoryId, EntryDate = entryDate, CreatedAt = DateTime.UtcNow }); } if (validEntries.Count > 0) { db.Entries.AddRange(validEntries); await db.SaveChangesAsync(); _logger.LogInformation( "Imported {Count} entries for month {MonthId}.", validEntries.Count, monthId); } return new ImportResultDto(validEntries.Count, errors.Count, errors); } }