Hard delete (DELETE FROM):
  - Row is gone permanently
  - Foreign key constraints may block deletion
  - No recovery without a backup
  - Audit trail is lost

Soft delete (UPDATE ... SET IsDeleted = true):
  - Row stays in the database
  - Can be restored
  - Full audit trail preserved
  - Referential integrity maintained
  - GDPR note: soft-deleting PII is not the same as erasing it — you may still need hard delete for compliance

// Soft delete marker
public interface ISoftDeletable
{
    bool      IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
    string?   DeletedBy { get; set; }
}

// Audit trail
public interface IAuditable
{
    DateTime  CreatedAt  { get; set; }
    string    CreatedBy  { get; set; }
    DateTime? UpdatedAt  { get; set; }
    string?   UpdatedBy  { get; set; }
}

// Base entity combining both
public abstract class BaseEntity : IAuditable, ISoftDeletable
{
    public int      Id        { get; set; }
    public DateTime CreatedAt { get; set; }
    public string   CreatedBy { get; set; } = "";
    public DateTime? UpdatedAt { get; set; }
    public string?   UpdatedBy { get; set; }
    public bool      IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
    public string?   DeletedBy { get; set; }
}

public class Order : BaseEntity
{
    public int     CustomerId { get; set; }
    public decimal Total      { get; set; }
    public string  Status     { get; set; } = "";
}

protected override void OnModelCreating(ModelBuilder model)
{
    // Apply soft delete filter to every entity that implements ISoftDeletable
    foreach (var entityType in model.Model.GetEntityTypes())
    {
        if (!typeof(ISoftDeletable).IsAssignableFrom(entityType.ClrType)) continue;

        var param  = Expression.Parameter(entityType.ClrType, "e");
        var prop   = Expression.Property(param, nameof(ISoftDeletable.IsDeleted));
        var filter = Expression.Lambda(Expression.Not(prop), param);

        entityType.SetQueryFilter(filter);

        // Add index on IsDeleted — WHERE IsDeleted = false is on every query
        entityType.AddIndex(
            entityType.FindProperty(nameof(ISoftDeletable.IsDeleted))!);
    }
}

// All queries now automatically exclude soft-deleted rows
var orders = await context.Orders.ToListAsync();
// SQL: SELECT * FROM Orders WHERE IsDeleted = 0

// To include deleted rows (admin, audit):
var allOrders = await context.Orders.IgnoreQueryFilters().ToListAsync();

// Override SaveChanges to intercept Delete operations
public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, InterceptionResult<int> result)
    {
        SoftDeleteEntities(eventData.Context!);
        return base.SavingChanges(eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default)
    {
        SoftDeleteEntities(eventData.Context!);
        return base.SavingChangesAsync(eventData, result, ct);
    }

    private static void SoftDeleteEntities(DbContext context)
    {
        var deletedEntities = context.ChangeTracker
            .Entries<ISoftDeletable>()
            .Where(e => e.State == EntityState.Deleted);

        foreach (var entry in deletedEntities)
        {
            entry.State = EntityState.Modified;   // change from Delete to Update
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
        }
    }
}

public class AuditInterceptor(ICurrentUserService currentUser) : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, InterceptionResult<int> result)
    {
        AuditEntities(eventData.Context!);
        return base.SavingChanges(eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default)
    {
        AuditEntities(eventData.Context!);
        return base.SavingChangesAsync(eventData, result, ct);
    }

    private void AuditEntities(DbContext context)
    {
        var now  = DateTime.UtcNow;
        var user = currentUser.UserId ?? "system";

        foreach (var entry in context.ChangeTracker.Entries<IAuditable>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = now;
                    entry.Entity.CreatedBy = user;
                    break;

                case EntityState.Modified:
                    entry.Entity.UpdatedAt = now;
                    entry.Entity.UpdatedBy = user;
                    break;

                case EntityState.Deleted when entry.Entity is ISoftDeletable softDelete:
                    softDelete.DeletedAt = now;
                    softDelete.DeletedBy = user;
                    break;
            }
        }
    }
}

// Register interceptors in DbContext configuration
builder.Services.AddDbContext<AppDbContext>((sp, opts) =>
{
    opts.UseNpgsql(connectionString)
        .AddInterceptors(
            sp.GetRequiredService<SoftDeleteInterceptor>(),
            sp.GetRequiredService<AuditInterceptor>());
});

builder.Services.AddScoped<SoftDeleteInterceptor>();
builder.Services.AddScoped<AuditInterceptor>();

public interface ICurrentUserService
{
    string? UserId   { get; }
    string? UserName { get; }
}

public class HttpCurrentUserService(IHttpContextAccessor accessor) : ICurrentUserService
{
    public string? UserId   => accessor.HttpContext?.User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
    public string? UserName => accessor.HttpContext?.User.FindFirst(ClaimTypes.Name)?.Value;
}

builder.Services.AddScoped<ICurrentUserService, HttpCurrentUserService>();
builder.Services.AddHttpContextAccessor();

// Restore — must bypass the global filter to find the deleted record
public async Task RestoreOrderAsync(int orderId, CancellationToken ct)
{
    var order = await context.Orders
        .IgnoreQueryFilters()
        .FirstOrDefaultAsync(o => o.Id == orderId && o.IsDeleted, ct);

    if (order is null) throw new NotFoundException(nameof(Order), orderId);

    order.IsDeleted = false;
    order.DeletedAt = null;
    order.DeletedBy = null;

    await context.SaveChangesAsync(ct);
}

// For full history: use a separate history table via interceptor
// Every change creates a new row in OrderHistory

public class OrderHistory
{
    public int      Id          { get; set; }
    public int      OrderId     { get; set; }
    public string   FieldName   { get; set; } = "";
    public string?  OldValue    { get; set; }
    public string?  NewValue    { get; set; }
    public DateTime ChangedAt   { get; set; }
    public string   ChangedBy   { get; set; } = "";
    public string   Operation   { get; set; } = "";   // Created, Updated, Deleted
}

// Or use a library: Audit.NET, EntityFramework.Exceptions, or Z.EntityFramework.Plus

// Soft delete preserves PII — may not satisfy GDPR right-to-erasure
// For GDPR: anonymise instead of delete

public async Task AnonymiseCustomerAsync(int customerId, CancellationToken ct)
{
    await context.Customers
        .Where(c => c.Id == customerId)
        .ExecuteUpdateAsync(s =>
            s.SetProperty(c => c.Name,  "DELETED")
             .SetProperty(c => c.Email, $"deleted-{customerId}@deleted.invalid")
             .SetProperty(c => c.Phone, null)
             .SetProperty(c => c.IsDeleted, true)
             .SetProperty(c => c.DeletedAt, DateTime.UtcNow),
        ct);
}