From 842502e82c4ddfea05a5f77c361aaa27f75afc42 Mon Sep 17 00:00:00 2001
From: ivar <i@oiee.no>
Date: Sun, 26 Oct 2025 22:57:28 +0100
Subject: Refactor db schema and add audits

---
 api/WhatApi/Database.cs | 112 +++++++++++++++++++++++++++++++++++++++++++-----
 1 file changed, 102 insertions(+), 10 deletions(-)

(limited to 'api/WhatApi/Database.cs')

diff --git a/api/WhatApi/Database.cs b/api/WhatApi/Database.cs
index 39de79a..8e8ed07 100644
--- a/api/WhatApi/Database.cs
+++ b/api/WhatApi/Database.cs
@@ -1,19 +1,111 @@
-using Microsoft.EntityFrameworkCore;
+using System.Security.Claims;
+using WhatApi.Tables;
 
 namespace WhatApi;
 
-public class Database(DbContextOptions<Database> options) : DbContext(options)
+public class Database(DbContextOptions<Database> options, IHttpContextAccessor httpContextAccessor) : DbContext(options)
 {
-    public DbSet<Tables.Content> Content { get; set; }
-    public DbSet<Tables.Place> Places { get; set; }
+    public DbSet<Content> Content => Set<Content>();
+    public DbSet<Place> Places => Set<Place>();
+    public DbSet<User>  Users => Set<User>();
+    public DbSet<AuditTrail> AuditTrails => Set<AuditTrail>();
+
     protected override void OnModelCreating(ModelBuilder b) {
         b.HasPostgresExtension("postgis");
-        b.Entity<Tables.Place>(e => {
-            e.Property(x => x.Location).HasColumnType($"geometry(point,{Constants.Wgs84SpatialReferenceId})");
-            e.HasIndex(x => x.Location).HasMethod("gist");
-            e.ToTable("Place");
-        });
-        b.Entity<Tables.Content>();
+        b.ApplyConfiguration(new AuditTrailConfiguration());
+        b.ApplyConfiguration(new PlaceConfiguration());
+        b.ApplyConfiguration(new UserConfiguration());
+        b.ApplyConfiguration(new ContentConfiguration());
         base.OnModelCreating(b);
     }
+
+    public override int SaveChanges() {
+        SetAuditableProperties();
+        var auditEntries = HandleAuditingBeforeSaveChanges();
+        if (auditEntries.Count != 0)
+            AuditTrails.AddRange(auditEntries);
+        return base.SaveChanges();
+    }
+
+    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default) {
+        SetAuditableProperties();
+        var auditEntries = HandleAuditingBeforeSaveChanges();
+        if (auditEntries.Count != 0)
+            await AuditTrails.AddRangeAsync(auditEntries, cancellationToken);
+        return await base.SaveChangesAsync(cancellationToken);
+    }
+
+    private List<AuditTrail> HandleAuditingBeforeSaveChanges() {
+        var userId = GetUserId();
+        var entries = ChangeTracker.Entries<IAuditableEntity>()
+            .Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted);
+
+        var auditTrails = new List<AuditTrail>();
+
+        foreach (var entry in entries) {
+            var audit = new AuditTrail {
+                Id = Guid.NewGuid(),
+                UserId = userId,
+                EntityName = entry.Entity.GetType().Name,
+                DateUtc = DateTimeOffset.UtcNow
+            };
+
+            foreach (var prop in entry.Properties) {
+                if (prop.Metadata.IsPrimaryKey()) {
+                    audit.PrimaryKey = prop.CurrentValue?.ToString();
+                    continue;
+                }
+
+                if (prop.Metadata.Name.Equals("Password")) continue;
+
+                var name = prop.Metadata.Name;
+
+                switch (entry.State) {
+                    case EntityState.Added:
+                        audit.TrailType = TrailType.Create;
+                        audit.NewValues[name] = prop.CurrentValue;
+                        break;
+                    case EntityState.Deleted:
+                        audit.TrailType = TrailType.Delete;
+                        audit.OldValues[name] = prop.OriginalValue;
+                        break;
+                    case EntityState.Modified:
+                        if (!Equals(prop.OriginalValue, prop.CurrentValue)) {
+                            audit.TrailType = TrailType.Update;
+                            audit.ChangedColumns.Add(name);
+                            audit.OldValues[name] = prop.OriginalValue;
+                            audit.NewValues[name] = prop.CurrentValue;
+                        }
+                        break;
+                }
+            }
+
+            if (audit.TrailType != TrailType.None)
+                auditTrails.Add(audit);
+        }
+
+        return auditTrails;
+    }
+
+    private Guid GetUserId() {
+        var system = new Guid("e87ab078-55bc-4655-86d9-c5b2ecad7162");
+        var userIdString = httpContextAccessor.HttpContext?.User.FindFirstValue(ClaimTypes.NameIdentifier);
+        return string.IsNullOrWhiteSpace(userIdString) ? system : new Guid(userIdString);
+    }
+
+    private void SetAuditableProperties() {
+        var actor = GetUserId();
+        foreach (var entry in ChangeTracker.Entries<IAuditableEntity>()) {
+            switch (entry.State) {
+                case EntityState.Added:
+                    entry.Entity.CreatedAtUtc = DateTimeOffset.UtcNow;
+                    entry.Entity.CreatedBy = actor;
+                    break;
+                case EntityState.Modified:
+                    entry.Entity.UpdatedAtUtc = DateTimeOffset.UtcNow;
+                    entry.Entity.UpdatedBy = actor;
+                    break;
+            }
+        }
+    }
 }
\ No newline at end of file
-- 
cgit v1.3