jobtrackingapp/JobTrackerApi.Tests/CsvExportTests.cs

using System.Text;
using JobTrackerApi.Controllers;
using JobTrackerApi.Models;
using JobTrackerApi.Services;
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using JobTrackerApi.Tests.TestSupport;
using Microsoft.AspNetCore.Mvc;
using Microsoft.VisualBasic.FileIO;
using Xunit;
using Xunit.Abstractions;

namespace JobTrackerApi.Tests;

public sealed class CsvExportTests(ITestOutputHelper output)
{
    [Fact]
    public async Task Export_neutralizes_the_original_arithmetic_title_without_changing_storage()
    {
        await using var db = TestHostFactory.CreateInMemoryDb();
        var company = new Company { Name = "Ærlig Økonomi Ås", OwnerUserId = "user-1" };
        var job = new JobApplication { Company = company, OwnerUserId = "user-1", JobTitle = "=1+1" };
        db.JobApplications.Add(job);
        await db.SaveChangesAsync();
        var result = Assert.IsType<FileContentResult>(await new ExportController(db).ExportJobs("csv"));
        output.WriteLine("B05_CSV_SAMPLE:" + Convert.ToBase64String(result.FileContents));
        var rows = Parse(result.FileContents);
        Assert.Equal("'=1+1", rows[1][3]);
        Assert.Equal("Ærlig Økonomi Ås", rows[1][0]);
        Assert.Equal(new byte[] { 0xef, 0xbb, 0xbf }, result.FileContents[..3]);
        Assert.Equal("text/csv; charset=utf-8", result.ContentType);
        Assert.Matches(@"^job-tracker-export-\d{4}-\d{2}-\d{2}\.csv$", result.FileDownloadName);
        db.ChangeTracker.Clear();
        Assert.Equal("=1+1", (await db.JobApplications.FindAsync(job.Id))!.JobTitle);
        var json = Assert.IsType<FileContentResult>(await new ExportController(db).ExportJobs("json"));
        using var raw = JsonDocument.Parse(json.FileContents);
        Assert.Equal("=1+1", raw.RootElement[0].GetProperty("JobTitle").GetString());
        Assert.Equal(result.FileContents, Assert.IsType<FileContentResult>(await new ExportController(db).ExportJobs("CSV")).FileContents);
    }

    [Theory]
    [InlineData("normal text", "normal text")]
    [InlineData("=1+1", "'=1+1")]
    [InlineData("+SUM(A1:A2)", "'+SUM(A1:A2)")]
    [InlineData("-2+3", "'-2+3")]
    [InlineData("@SUM(A1:A2)", "'@SUM(A1:A2)")]
    [InlineData(" \t=1+1", "' \t=1+1")]
    [InlineData("\r\n=1+1", "'\r\n=1+1")]
    [InlineData("\0\u000b+1", "'\0\u000b+1")]
    [InlineData("\u00a0\u200b\ufeff=1+1", "'\u00a0\u200b\ufeff=1+1")]
    [InlineData("\U000E0001=1+1", "'\U000E0001=1+1")]
    [InlineData("'=1+1", "'=1+1")]
    [InlineData("  '=1+1", "  '=1+1")]
    [InlineData("a,b", "a,b")]
    [InlineData("a\"b", "a\"b")]
    [InlineData("a\r\nb\nc\rd", "a\r\nb\nc\rd")]
    [InlineData("=SUM(1,2)", "'=SUM(1,2)")]
    [InlineData("safe\",=1+1\r\n@SUM(A1:A2)", "safe\",=1+1\r\n@SUM(A1:A2)")]
    [InlineData("Ærlig, Økonomi og Ås — æøå 😀", "Ærlig, Økonomi og Ås — æøå 😀")]
    [InlineData("  ordinary text", "  ordinary text")]
    [InlineData("123.50", "123.50")]
    [InlineData("", "")]
    [InlineData(null, "")]
    public async Task Text_policy_is_idempotent_and_survives_independent_CSV_parsing(string? value, string expected)
    {
        Assert.Equal(expected, CsvCellSafety.SpreadsheetText(value));
        Assert.Equal(expected, CsvCellSafety.SpreadsheetText(CsvCellSafety.SpreadsheetText(value)));
        await using var db = TestHostFactory.CreateInMemoryDb();
        db.JobApplications.Add(new JobApplication { OwnerUserId = "user-1", Company = new Company { Name = "Synthetic", OwnerUserId = "user-1" }, Notes = value });
        await db.SaveChangesAsync();
        var result = Assert.IsType<FileContentResult>(await new ExportController(db).ExportJobs("csv"));
        var rows = Parse(result.FileContents);
        Assert.Equal(2, rows.Count);
        Assert.All(rows, row => Assert.Equal(17, row.Length));
        Assert.Equal(expected, rows[1][15]);
        Assert.Equal(value, (await db.JobApplications.AsNoTracking().SingleAsync()).Notes);
    }

    [Fact]
    public async Task Every_text_column_is_safe_while_typed_numbers_and_dates_keep_their_formats()
    {
        await using var db = TestHostFactory.CreateInMemoryDb();
        var date = new DateTime(2026, 9, 9, 12, 30, 0, DateTimeKind.Utc);
        db.JobApplications.Add(new JobApplication
        {
            OwnerUserId = "user-1",
            Company = new Company { Name = "=1+1", Location = " +SUM(1,2)", Source = "\t-2+3", OwnerUserId = "user-1" },
            JobTitle = "@SUM(A1:A2)", Status = "\r\n=1+1", Location = "\u200b=1+1", Salary = "-750",
            SalaryMin = -750.50m, SalaryMax = 1200.75m, SalaryCurrency = "+NOK", SalaryPeriod = "@year", NextAction = "=1+1",
            DateApplied = date, FollowUpAt = date.AddDays(1), JobUrl = "+1", Notes = "=SUM(1,2)\næøå", CoverLetterText = "=1+1\r\nÆØÅ",
        });
        await db.SaveChangesAsync();
        var result = Assert.IsType<FileContentResult>(await new ExportController(db).ExportJobs("csv"));
        output.WriteLine("B05_TYPED_SAMPLE:" + Convert.ToBase64String(result.FileContents));
        var cells = Parse(result.FileContents)[1];
        Assert.Equal(17, cells.Length);
        foreach (var index in new[] { 0, 1, 2, 3, 4, 6, 7, 10, 11, 12, 14, 15, 16 }) Assert.StartsWith("'", cells[index]);
        Assert.Equal("-750.50", cells[8]);
        Assert.Equal("1200.75", cells[9]);
        Assert.Equal(date.ToString("o"), cells[5]);
        Assert.Equal(date.AddDays(1).ToString("o"), cells[13]);
    }

    [Fact]
    public async Task Long_text_null_typed_cells_and_deleted_row_selection_remain_valid()
    {
        await using var db = TestHostFactory.CreateInMemoryDb();
        var longText = "=1+1,\"æøå\"\r\n" + new string('ø', 70_000);
        db.JobApplications.Add(new JobApplication { OwnerUserId = "user-1", Company = new Company { Name = "Synthetic", OwnerUserId = "user-1" }, Notes = longText, IsDeleted = true });
        await db.SaveChangesAsync();
        var controller = new ExportController(db);
        Assert.Single(Parse(Assert.IsType<FileContentResult>(await controller.ExportJobs("csv")).FileContents));
        var cells = Parse(Assert.IsType<FileContentResult>(await controller.ExportJobs("csv", includeDeleted: true)).FileContents)[1];
        Assert.Equal("'" + longText, cells[15]);
        foreach (var index in new[] { 5, 8, 9, 13 }) Assert.Equal("", cells[index]);
    }

    private static List<string[]> Parse(byte[] bytes)
    {
        using var parser = new TextFieldParser(new MemoryStream(bytes), Encoding.UTF8)
        {
            TextFieldType = FieldType.Delimited,
            HasFieldsEnclosedInQuotes = true,
            TrimWhiteSpace = false,
        };
        parser.SetDelimiters(",");
        var rows = new List<string[]>();
        while (!parser.EndOfData) rows.Add(parser.ReadFields()!);
        return rows;
    }
}