Program.cs

using System.Text.Json;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<QuoteDbContext>(options =>
    options.UseSqlite("Data Source=quotes.db"));

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

const int MaxPageSize = 100;

app.UseHttpsRedirection();

app.MapGet("/quotes", async (QuoteDbContext db, int pageNumber = 1, int pageSize = 10) =>
{
    if (pageNumber < 1) pageNumber = 1;
    if (pageSize < 1) pageSize = 10;
    pageSize = Math.Min(pageSize, MaxPageSize); // Limit pageSize to MaxPageSize

    var quotes = await db.Quotes
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return Results.Ok(quotes);
});

app.MapGet("/quotes/{id}", async (int id, QuoteDbContext db) =>
    await db.Quotes.FindAsync(id) is Quote quote
        ? Results.Ok(quote)
        : Results.NotFound("Quote not found"));

app.MapPost("/quotes", async (Quote newQuote, QuoteDbContext db) =>
{
    db.Quotes.Add(newQuote);
    await db.SaveChangesAsync();
    return Results.Created($"/quotes/{newQuote.Id}", newQuote);
});

app.MapPut("/quotes/{id}", async (int id, Quote updatedQuote, QuoteDbContext db) =>
{
    var quote = await db.Quotes.FindAsync(id);
    if (quote is null) return Results.NotFound("Quote not found");

    quote.Author = updatedQuote.Author;
    quote.QuoteText = updatedQuote.QuoteText;
    quote.Source = updatedQuote.Source;
    quote.Book = updatedQuote.Book;
    quote.Categories = updatedQuote.Categories;
    quote.Url = updatedQuote.Url;
    quote.Isbn = updatedQuote.Isbn;
    quote.Language = updatedQuote.Language;
    quote.OriginalLanguage = updatedQuote.OriginalLanguage;
    
    await db.SaveChangesAsync();
    return Results.NoContent();
});

app.MapDelete("/quotes/{id}", async (int id, QuoteDbContext db) =>
{
    var quote = await db.Quotes.FindAsync(id);
    if (quote is null) return Results.NotFound("Quote not found");

    db.Quotes.Remove(quote);
    await db.SaveChangesAsync();
    return Results.NoContent();
});

// Search quotes by author, categories, book, or quoteText with pagination
app.MapGet("/quotes/search", async (string search, QuoteDbContext db, int pageNumber = 1, int pageSize = 10) =>
{
    var query = db.Quotes.AsQueryable();

    if (!string.IsNullOrWhiteSpace(search))
        query = query.Where(q => q.Author.Contains(search));

    if (!string.IsNullOrWhiteSpace(search))
        query = query.Where(q => q.Categories.Contains(search));

    if (!string.IsNullOrWhiteSpace(search))
        query = query.Where(q => q.Book.Contains(search));

    if (!string.IsNullOrWhiteSpace(search))
        query = query.Where(q => q.QuoteText.Contains(search));

    if (pageNumber < 1) pageNumber = 1;
    if (pageSize < 1) pageSize = 10;
    pageSize = Math.Min(pageSize, MaxPageSize); // Limit pageSize to MaxPageSize

    var paginatedQuotes = await query
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return paginatedQuotes.Any() ? Results.Ok(paginatedQuotes) : Results.NotFound("No matching quotes found.");
});

async Task SaveSource1Async(string jsonFilePath, QuoteDbContext db)
{
    var path = Path.Combine(Directory.GetCurrentDirectory(), "data", jsonFilePath);

    if (!File.Exists(path))
        throw new FileNotFoundException("The JSON file for seeding is missing.");

    // var jsonString = await File.ReadAllTextAsync(path);

    // // Deserialize JSON to a list of dictionaries
    // var dictionaryList = JsonSerializer.Deserialize<List<Dictionary<string, object>>>(jsonString);

    // if (dictionaryList == null)
    //     throw new InvalidOperationException("Failed to deserialize JSON into dictionary list.");

    // Fields
    // ==========
    // Quote (string)
    // Author (string)
    // Tags (list<string>)
    // Category (string)

    // foreach (var dictionary in dictionaryList)
    // {
    //     var tags = "";
    //     if(dictionary.TryGetValue("Tags", out var categories)) {
    //         var list = JsonSerializer.Deserialize<List<string>>(categories.ToString());
    //         tags = String.Join(",", list.Select(x=> x.Trim().Trim('"')));
    //     }
    //     var quote = new Quote
    //     {
    //         Author = dictionary.TryGetValue("Author", out var author) ? author.ToString() : null,
    //         QuoteText = dictionary.TryGetValue("Quote", out var quoteText) ? quoteText.ToString() : null,
    //         Categories = tags,
    //     };
    //     db.Quotes.Add(quote);
    // }

    // TODO: import data and sanitize relevant fields: Trim and Trim('"')
    
    //save for each seed file
    await db.SaveChangesAsync();
}

async Task SaveSource2Async(string jsonFile, QuoteDbContext db)
{
    var path = Path.Combine(Directory.GetCurrentDirectory(), "data", jsonFile);

    if (!File.Exists(path))
        throw new FileNotFoundException("The JSON file for seeding is missing.");

    // Fields
    // ==========
    // content (string)
    // author (string)
    // tags (list<string>)

    // TODO: import data and sanitize relevant fields: Trim and Trim('"')
    
    //save for each seed file
    await db.SaveChangesAsync();
}

async Task SaveSource3Async(string csvFile, QuoteDbContext db)
{
    var path = Path.Combine(Directory.GetCurrentDirectory(), "data", csvFile);

    if (!File.Exists(path))
        throw new FileNotFoundException("The CSV file for seeding is missing.");

    // Fields
    // ==========
    // quote (string)
    // author (string)
    // category (string)

    // TODO: import data and sanitize relevant fields: Trim and Trim('"')

    //save for each seed file
    await db.SaveChangesAsync();
}

// Seed database
async Task SeedDatabase(QuoteDbContext db)
{
    if (await db.Quotes.AnyAsync())
        return; // Database is already seeded

    await SaveSource1Async("source1.json", db);
    await SaveSource2Async("source2.json", db);
    await SaveSource3Async("source3.csv", db);
}

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<QuoteDbContext>();
    db.Database.EnsureCreated();
    await SeedDatabase(db);
}

app.Run();