Refactor db

7 files changed, 247 insertions, 0 deletions

diff --git a/api/WhatApi/Database/AppDatabase.cs b/api/WhatApi/Database/AppDatabase.cs
new file mode 100644
index 0000000..64c138f
--- /dev/null
+++ b/api/WhatApi/Database/AppDatabase.cs
@@ -0,0 +1,113 @@
+using System.Security.Claims;
+
+namespace WhatApi.Database;
+
+public class AppDatabase(DbContextOptions<AppDatabase> options, IHttpContextAccessor httpContextAccessor, IConfiguration configuration) : DbContext(options)
+{
+    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.ApplyConfiguration(new AuditTrailConfiguration());
+        b.ApplyConfiguration(new PlaceConfiguration());
+        b.ApplyConfiguration(new UserConfiguration());
+        b.ApplyConfiguration(new ContentConfiguration());
+        base.OnModelCreating(b);
+    }
+
+    public override int SaveChanges() {
+        if (configuration.GetValue<bool>("DISABLE_AUDIT_TRAILS")) return base.SaveChanges();
+        SetAuditableProperties();
+        var auditEntries = GetActiveAuditTrails();
+        if (auditEntries.Count != 0)
+            AuditTrails.AddRange(auditEntries);
+        return base.SaveChanges();
+    }
+
+    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default) {
+        if (configuration.GetValue<bool>("DISABLE_AUDIT_TRAILS")) return await base.SaveChangesAsync(cancellationToken);
+        SetAuditableProperties();
+        var auditEntries = GetActiveAuditTrails();
+        if (auditEntries.Count != 0)
+            await AuditTrails.AddRangeAsync(auditEntries, cancellationToken);
+        return await base.SaveChangesAsync(cancellationToken);
+    }
+
+    private List<AuditTrail> GetActiveAuditTrails() {
+        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.PropertyInfo?.CustomAttributes.FirstOrDefault(c => c.AttributeType == typeof(AuditTrailIgnoreAttribute)) != null)
+                    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
diff --git a/api/WhatApi/Database/Tables/AuditTrail.cs b/api/WhatApi/Database/Tables/AuditTrail.cs
new file mode 100644
index 0000000..4ded46c
--- /dev/null
+++ b/api/WhatApi/Database/Tables/AuditTrail.cs
@@ -0,0 +1,39 @@
+namespace WhatApi.Database.Tables;
+
+public class AuditTrail
+{
+    public Guid Id { get; init; }
+    public Guid? UserId { get; init; }
+    public required string EntityName { get; init; }
+    public string? PrimaryKey { get; set; }
+    public TrailType TrailType { get; set; }
+    public DateTimeOffset DateUtc { get; init; }
+
+    public Dictionary<string, object?> OldValues { get; init; } = [];
+    public Dictionary<string, object?> NewValues { get; init; } = [];
+    public List<string> ChangedColumns { get; init; } = [];
+}
+
+public class AuditTrailConfiguration : IEntityTypeConfiguration<AuditTrail>
+{
+    public void Configure(EntityTypeBuilder<AuditTrail> builder)
+    {
+        builder.ToTable("audit_trails");
+        builder.HasIndex(e => e.EntityName);
+        builder.Property(e => e.EntityName).HasMaxLength(100).IsRequired();
+        builder.Property(e => e.PrimaryKey).HasMaxLength(100);
+        builder.Property(e => e.DateUtc).IsRequired();
+        builder.Property(e => e.TrailType).HasConversion<string>();
+        builder.Property(e => e.OldValues).HasColumnType("jsonb");
+        builder.Property(e => e.NewValues).HasColumnType("jsonb");
+        builder.Property(e => e.ChangedColumns).HasColumnType("jsonb");
+    }
+}
+
+public enum TrailType : byte
+{
+    None = 0,
+    Create = 1,
+    Update = 2,
+    Delete = 3
+}
\ No newline at end of file
diff --git a/api/WhatApi/Database/Tables/BaseAuditableEntity.cs b/api/WhatApi/Database/Tables/BaseAuditableEntity.cs
new file mode 100644
index 0000000..25fb3fa
--- /dev/null
+++ b/api/WhatApi/Database/Tables/BaseAuditableEntity.cs
@@ -0,0 +1,18 @@
+namespace WhatApi.Database.Tables;
+
+public class BaseAuditableEntity : IAuditableEntity
+{
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+
+    public void SetCreated(Guid createdBy) {
+        CreatedBy = createdBy;
+        CreatedAtUtc = DateTimeOffset.UtcNow;
+    }
+    public void SetUpdated(Guid updatedBy) {
+        UpdatedBy = updatedBy;
+        UpdatedAtUtc = DateTimeOffset.UtcNow;
+    }
+}
\ No newline at end of file
diff --git a/api/WhatApi/Database/Tables/Content.cs b/api/WhatApi/Database/Tables/Content.cs
new file mode 100644
index 0000000..8148f81
--- /dev/null
+++ b/api/WhatApi/Database/Tables/Content.cs
@@ -0,0 +1,20 @@
+using System.Net;
+
+namespace WhatApi.Database.Tables;
+
+public class Content : BaseAuditableEntity
+{
+    public Guid Id { get; set; }
+    public required string Mime { get; set; }
+    public Guid BlobId { get; set; }
+    public required IPAddress Ip { get; set; }
+}
+
+public class ContentConfiguration : IEntityTypeConfiguration<Content>
+{
+    public void Configure(EntityTypeBuilder<Content> builder) {
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Mime).HasMaxLength(100).IsRequired();
+        builder.ToTable("content");
+    }
+}
\ No newline at end of file
diff --git a/api/WhatApi/Database/Tables/IAuditableEntity.cs b/api/WhatApi/Database/Tables/IAuditableEntity.cs
new file mode 100644
index 0000000..61d64fd
--- /dev/null
+++ b/api/WhatApi/Database/Tables/IAuditableEntity.cs
@@ -0,0 +1,9 @@
+namespace WhatApi.Database.Tables;
+
+public interface IAuditableEntity
+{
+    public DateTimeOffset CreatedAtUtc { get; set; }
+    public DateTimeOffset? UpdatedAtUtc { get; set; }
+    public Guid CreatedBy { get; set; }
+    public Guid? UpdatedBy { get; set; }
+}
\ No newline at end of file
diff --git a/api/WhatApi/Database/Tables/Place.cs b/api/WhatApi/Database/Tables/Place.cs
new file mode 100644
index 0000000..2914aa7
--- /dev/null
+++ b/api/WhatApi/Database/Tables/Place.cs
@@ -0,0 +1,21 @@
+namespace WhatApi.Database.Tables;
+
+public class Place : BaseAuditableEntity
+{
+    public Guid Id { get; set; }
+    public Guid ContentId { get; set; }
+    public Content Content { get; set; } = null!;
+    public required Point Location { get; set; }
+}
+
+public class PlaceConfiguration : IEntityTypeConfiguration<Place>
+{
+    public void Configure(EntityTypeBuilder<Place> builder) {
+        builder.ToTable("place");
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Location).IsRequired();
+        builder.HasOne(x => x.Content);
+        builder.Property(x => x.Location).HasColumnType($"geometry(point,{Constants.Wgs84SpatialReferenceId})");
+        builder.HasIndex(x => x.Location).HasMethod("gist");
+    }
+}
diff --git a/api/WhatApi/Database/Tables/User.cs b/api/WhatApi/Database/Tables/User.cs
new file mode 100644
index 0000000..bfcdb50
--- /dev/null
+++ b/api/WhatApi/Database/Tables/User.cs
@@ -0,0 +1,27 @@
+namespace WhatApi.Database.Tables;
+
+public class User : BaseAuditableEntity
+{
+    public Guid Id { get; set; }
+    public required string Name { get; set; }
+    public required string Email { get; set; }
+    public required string PasswordHash { get; set; }
+    public DateTimeOffset? LastSeen { get; set; }
+    public IEnumerable<Place> Places { get; set; } = null!;
+
+    public void SetLastSeen() {
+        LastSeen = DateTimeOffset.UtcNow;
+    }
+}
+
+public class UserConfiguration : IEntityTypeConfiguration<User>
+{
+    public void Configure(EntityTypeBuilder<User> builder) {
+        builder.HasKey(x => x.Id);
+        builder.Property(x => x.Name).HasMaxLength(50);
+        builder.Property(x => x.Email).HasMaxLength(100);
+        builder.Property(x => x.PasswordHash).HasMaxLength(100);
+        builder.HasMany(x => x.Places);
+        builder.ToTable("user");
+    }
+}
\ No newline at end of file