| | | 1 | | using Chronicis.Shared.Models; |
| | | 2 | | using Microsoft.EntityFrameworkCore; |
| | | 3 | | |
| | | 4 | | namespace Chronicis.Api.Data; |
| | | 5 | | |
| | | 6 | | /// <summary> |
| | | 7 | | /// Database context for Chronicis application. |
| | | 8 | | /// </summary> |
| | | 9 | | public class ChronicisDbContext : DbContext |
| | | 10 | | { |
| | | 11 | | // ===== Core Entities ===== |
| | 553 | 12 | | public DbSet<User> Users { get; set; } = null!; |
| | 478 | 13 | | public DbSet<World> Worlds { get; set; } = null!; |
| | 593 | 14 | | public DbSet<WorldMember> WorldMembers { get; set; } = null!; |
| | 276 | 15 | | public DbSet<WorldInvitation> WorldInvitations { get; set; } = null!; |
| | 397 | 16 | | public DbSet<Campaign> Campaigns { get; set; } = null!; |
| | 390 | 17 | | public DbSet<Arc> Arcs { get; set; } = null!; |
| | 533 | 18 | | public DbSet<Article> Articles { get; set; } = null!; |
| | 228 | 19 | | public DbSet<ArticleAlias> ArticleAliases { get; set; } = null!; |
| | 271 | 20 | | public DbSet<ArticleLink> ArticleLinks { get; set; } = null!; |
| | 228 | 21 | | public DbSet<ArticleExternalLink> ArticleExternalLinks { get; set; } = null!; |
| | 228 | 22 | | public DbSet<WorldLink> WorldLinks { get; set; } = null!; |
| | 228 | 23 | | public DbSet<WorldDocument> WorldDocuments { get; set; } = null!; |
| | 228 | 24 | | public DbSet<SummaryTemplate> SummaryTemplates { get; set; } = null!; |
| | 228 | 25 | | public DbSet<ResourceProvider> ResourceProviders { get; set; } = null!; |
| | 228 | 26 | | public DbSet<WorldResourceProvider> WorldResourceProviders { get; set; } = null!; |
| | 293 | 27 | | public DbSet<Quest> Quests { get; set; } = null!; |
| | 268 | 28 | | public DbSet<QuestUpdate> QuestUpdates { get; set; } = null!; |
| | | 29 | | |
| | | 30 | | |
| | | 31 | | public ChronicisDbContext(DbContextOptions<ChronicisDbContext> options) |
| | 228 | 32 | | : base(options) |
| | | 33 | | { |
| | 228 | 34 | | } |
| | | 35 | | |
| | | 36 | | protected override void OnModelCreating(ModelBuilder modelBuilder) |
| | | 37 | | { |
| | 1 | 38 | | ConfigureUser(modelBuilder); |
| | 1 | 39 | | ConfigureWorld(modelBuilder); |
| | 1 | 40 | | ConfigureWorldMember(modelBuilder); |
| | 1 | 41 | | ConfigureWorldInvitation(modelBuilder); |
| | 1 | 42 | | ConfigureCampaign(modelBuilder); |
| | 1 | 43 | | ConfigureArc(modelBuilder); |
| | 1 | 44 | | ConfigureArticle(modelBuilder); |
| | 1 | 45 | | ConfigureArticleAlias(modelBuilder); |
| | 1 | 46 | | ConfigureArticleLink(modelBuilder); |
| | 1 | 47 | | ConfigureArticleExternalLink(modelBuilder); |
| | 1 | 48 | | ConfigureWorldLink(modelBuilder); |
| | 1 | 49 | | ConfigureWorldDocument(modelBuilder); |
| | 1 | 50 | | ConfigureSummaryTemplate(modelBuilder); |
| | 1 | 51 | | ConfigureResourceProvider(modelBuilder); |
| | 1 | 52 | | ConfigureWorldResourceProvider(modelBuilder); |
| | 1 | 53 | | ConfigureQuest(modelBuilder); |
| | 1 | 54 | | ConfigureQuestUpdate(modelBuilder); |
| | 1 | 55 | | } |
| | | 56 | | |
| | | 57 | | private static void ConfigureUser(ModelBuilder modelBuilder) |
| | | 58 | | { |
| | 1 | 59 | | modelBuilder.Entity<User>(entity => |
| | 1 | 60 | | { |
| | 1 | 61 | | entity.HasKey(u => u.Id); |
| | 1 | 62 | | |
| | 1 | 63 | | entity.Property(u => u.Auth0UserId) |
| | 1 | 64 | | .HasMaxLength(256) |
| | 1 | 65 | | .IsRequired(); |
| | 1 | 66 | | |
| | 1 | 67 | | entity.Property(u => u.Email) |
| | 1 | 68 | | .HasMaxLength(256) |
| | 1 | 69 | | .IsRequired(); |
| | 1 | 70 | | |
| | 1 | 71 | | entity.Property(u => u.DisplayName) |
| | 1 | 72 | | .HasMaxLength(100) |
| | 1 | 73 | | .IsRequired(); |
| | 1 | 74 | | |
| | 1 | 75 | | entity.Property(u => u.AvatarUrl) |
| | 1 | 76 | | .HasMaxLength(500); |
| | 1 | 77 | | |
| | 1 | 78 | | entity.HasIndex(u => u.Auth0UserId) |
| | 1 | 79 | | .IsUnique(); |
| | 1 | 80 | | |
| | 1 | 81 | | entity.HasIndex(u => u.Email); |
| | 2 | 82 | | }); |
| | 1 | 83 | | } |
| | | 84 | | |
| | | 85 | | private static void ConfigureWorld(ModelBuilder modelBuilder) |
| | | 86 | | { |
| | 1 | 87 | | modelBuilder.Entity<World>(entity => |
| | 1 | 88 | | { |
| | 1 | 89 | | entity.HasKey(w => w.Id); |
| | 1 | 90 | | |
| | 1 | 91 | | entity.Property(w => w.Name) |
| | 1 | 92 | | .HasMaxLength(200) |
| | 1 | 93 | | .IsRequired(); |
| | 1 | 94 | | |
| | 1 | 95 | | entity.Property(w => w.Slug) |
| | 1 | 96 | | .HasMaxLength(200) |
| | 1 | 97 | | .IsRequired(); |
| | 1 | 98 | | |
| | 1 | 99 | | entity.Property(w => w.Description) |
| | 1 | 100 | | .HasMaxLength(1000); |
| | 1 | 101 | | |
| | 1 | 102 | | // Public access fields |
| | 1 | 103 | | entity.Property(w => w.IsPublic) |
| | 1 | 104 | | .HasDefaultValue(false); |
| | 1 | 105 | | |
| | 1 | 106 | | entity.Property(w => w.PublicSlug) |
| | 1 | 107 | | .HasMaxLength(100); |
| | 1 | 108 | | |
| | 1 | 109 | | // World -> Owner (User) |
| | 1 | 110 | | entity.HasOne(w => w.Owner) |
| | 1 | 111 | | .WithMany(u => u.OwnedWorlds) |
| | 1 | 112 | | .HasForeignKey(w => w.OwnerId) |
| | 1 | 113 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 114 | | |
| | 1 | 115 | | entity.HasIndex(w => w.OwnerId); |
| | 1 | 116 | | |
| | 1 | 117 | | // Unique constraint: Slug must be unique per owner |
| | 1 | 118 | | entity.HasIndex(w => new { w.OwnerId, w.Slug }) |
| | 1 | 119 | | .IsUnique() |
| | 1 | 120 | | .HasDatabaseName("IX_Worlds_OwnerId_Slug"); |
| | 1 | 121 | | |
| | 1 | 122 | | // Unique constraint: PublicSlug must be globally unique (when not null) |
| | 1 | 123 | | entity.HasIndex(w => w.PublicSlug) |
| | 1 | 124 | | .IsUnique() |
| | 1 | 125 | | .HasFilter("[PublicSlug] IS NOT NULL") |
| | 1 | 126 | | .HasDatabaseName("IX_Worlds_PublicSlug"); |
| | 2 | 127 | | }); |
| | 1 | 128 | | } |
| | | 129 | | |
| | | 130 | | private static void ConfigureCampaign(ModelBuilder modelBuilder) |
| | | 131 | | { |
| | 1 | 132 | | modelBuilder.Entity<Campaign>(entity => |
| | 1 | 133 | | { |
| | 1 | 134 | | entity.HasKey(c => c.Id); |
| | 1 | 135 | | |
| | 1 | 136 | | entity.Property(c => c.Name) |
| | 1 | 137 | | .HasMaxLength(200) |
| | 1 | 138 | | .IsRequired(); |
| | 1 | 139 | | |
| | 1 | 140 | | entity.Property(c => c.Description) |
| | 1 | 141 | | .HasMaxLength(1000); |
| | 1 | 142 | | |
| | 1 | 143 | | // Campaign -> World |
| | 1 | 144 | | entity.HasOne(c => c.World) |
| | 1 | 145 | | .WithMany(w => w.Campaigns) |
| | 1 | 146 | | .HasForeignKey(c => c.WorldId) |
| | 1 | 147 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 148 | | |
| | 1 | 149 | | // Campaign -> Owner (User) |
| | 1 | 150 | | entity.HasOne(c => c.Owner) |
| | 1 | 151 | | .WithMany(u => u.OwnedCampaigns) |
| | 1 | 152 | | .HasForeignKey(c => c.OwnerId) |
| | 1 | 153 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 154 | | |
| | 1 | 155 | | entity.HasIndex(c => c.WorldId); |
| | 1 | 156 | | entity.HasIndex(c => c.OwnerId); |
| | 2 | 157 | | }); |
| | 1 | 158 | | } |
| | | 159 | | |
| | | 160 | | private static void ConfigureWorldMember(ModelBuilder modelBuilder) |
| | | 161 | | { |
| | 1 | 162 | | modelBuilder.Entity<WorldMember>(entity => |
| | 1 | 163 | | { |
| | 1 | 164 | | entity.HasKey(wm => wm.Id); |
| | 1 | 165 | | |
| | 1 | 166 | | // WorldMember -> World |
| | 1 | 167 | | entity.HasOne(wm => wm.World) |
| | 1 | 168 | | .WithMany(w => w.Members) |
| | 1 | 169 | | .HasForeignKey(wm => wm.WorldId) |
| | 1 | 170 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 171 | | |
| | 1 | 172 | | // WorldMember -> User |
| | 1 | 173 | | entity.HasOne(wm => wm.User) |
| | 1 | 174 | | .WithMany(u => u.WorldMemberships) |
| | 1 | 175 | | .HasForeignKey(wm => wm.UserId) |
| | 1 | 176 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 177 | | |
| | 1 | 178 | | // WorldMember -> Inviter (User) |
| | 1 | 179 | | entity.HasOne(wm => wm.Inviter) |
| | 1 | 180 | | .WithMany(u => u.InvitedMembers) |
| | 1 | 181 | | .HasForeignKey(wm => wm.InvitedBy) |
| | 1 | 182 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 183 | | |
| | 1 | 184 | | // Unique constraint: One membership per user per world |
| | 1 | 185 | | entity.HasIndex(wm => new { wm.WorldId, wm.UserId }) |
| | 1 | 186 | | .IsUnique(); |
| | 2 | 187 | | }); |
| | 1 | 188 | | } |
| | | 189 | | |
| | | 190 | | private static void ConfigureWorldInvitation(ModelBuilder modelBuilder) |
| | | 191 | | { |
| | 1 | 192 | | modelBuilder.Entity<WorldInvitation>(entity => |
| | 1 | 193 | | { |
| | 1 | 194 | | entity.HasKey(wi => wi.Id); |
| | 1 | 195 | | |
| | 1 | 196 | | entity.Property(wi => wi.Code) |
| | 1 | 197 | | .HasMaxLength(9) // XXXX-XXXX format |
| | 1 | 198 | | .IsRequired(); |
| | 1 | 199 | | |
| | 1 | 200 | | // WorldInvitation -> World |
| | 1 | 201 | | entity.HasOne(wi => wi.World) |
| | 1 | 202 | | .WithMany(w => w.Invitations) |
| | 1 | 203 | | .HasForeignKey(wi => wi.WorldId) |
| | 1 | 204 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 205 | | |
| | 1 | 206 | | // WorldInvitation -> Creator (User) |
| | 1 | 207 | | entity.HasOne(wi => wi.Creator) |
| | 1 | 208 | | .WithMany(u => u.CreatedInvitations) |
| | 1 | 209 | | .HasForeignKey(wi => wi.CreatedBy) |
| | 1 | 210 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 211 | | |
| | 1 | 212 | | // Unique constraint: Invitation codes must be globally unique |
| | 1 | 213 | | entity.HasIndex(wi => wi.Code) |
| | 1 | 214 | | .IsUnique(); |
| | 1 | 215 | | |
| | 1 | 216 | | // Index for looking up active invitations by world |
| | 1 | 217 | | entity.HasIndex(wi => new { wi.WorldId, wi.IsActive }); |
| | 2 | 218 | | }); |
| | 1 | 219 | | } |
| | | 220 | | |
| | | 221 | | private static void ConfigureArc(ModelBuilder modelBuilder) |
| | | 222 | | { |
| | 1 | 223 | | modelBuilder.Entity<Arc>(entity => |
| | 1 | 224 | | { |
| | 1 | 225 | | entity.HasKey(a => a.Id); |
| | 1 | 226 | | |
| | 1 | 227 | | entity.Property(a => a.Name) |
| | 1 | 228 | | .HasMaxLength(200) |
| | 1 | 229 | | .IsRequired(); |
| | 1 | 230 | | |
| | 1 | 231 | | entity.Property(a => a.Description) |
| | 1 | 232 | | .HasMaxLength(1000); |
| | 1 | 233 | | |
| | 1 | 234 | | // Arc -> Campaign |
| | 1 | 235 | | entity.HasOne(a => a.Campaign) |
| | 1 | 236 | | .WithMany(c => c.Arcs) |
| | 1 | 237 | | .HasForeignKey(a => a.CampaignId) |
| | 1 | 238 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 239 | | |
| | 1 | 240 | | // Arc -> Creator (User) |
| | 1 | 241 | | entity.HasOne(a => a.Creator) |
| | 1 | 242 | | .WithMany(u => u.CreatedArcs) |
| | 1 | 243 | | .HasForeignKey(a => a.CreatedBy) |
| | 1 | 244 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 245 | | |
| | 1 | 246 | | // Indexes |
| | 1 | 247 | | entity.HasIndex(a => a.CampaignId); |
| | 1 | 248 | | entity.HasIndex(a => a.CreatedBy); |
| | 1 | 249 | | entity.HasIndex(a => new { a.CampaignId, a.SortOrder }); |
| | 2 | 250 | | }); |
| | 1 | 251 | | } |
| | | 252 | | |
| | | 253 | | private static void ConfigureArticle(ModelBuilder modelBuilder) |
| | | 254 | | { |
| | 1 | 255 | | modelBuilder.Entity<Article>(entity => |
| | 1 | 256 | | { |
| | 1 | 257 | | entity.HasKey(a => a.Id); |
| | 1 | 258 | | |
| | 1 | 259 | | // Content fields |
| | 1 | 260 | | entity.Property(a => a.Title) |
| | 1 | 261 | | .HasMaxLength(500); |
| | 1 | 262 | | |
| | 1 | 263 | | entity.Property(a => a.Slug) |
| | 1 | 264 | | .HasMaxLength(200) |
| | 1 | 265 | | .IsRequired(); |
| | 1 | 266 | | |
| | 1 | 267 | | entity.Property(a => a.IconEmoji) |
| | 1 | 268 | | .HasMaxLength(50); |
| | 1 | 269 | | |
| | 1 | 270 | | entity.Property(a => a.InGameDate) |
| | 1 | 271 | | .HasMaxLength(100); |
| | 1 | 272 | | |
| | 1 | 273 | | // Self-referencing hierarchy |
| | 1 | 274 | | entity.HasOne(a => a.Parent) |
| | 1 | 275 | | .WithMany(a => a.Children) |
| | 1 | 276 | | .HasForeignKey(a => a.ParentId) |
| | 1 | 277 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 278 | | |
| | 1 | 279 | | // Article -> World |
| | 1 | 280 | | entity.HasOne(a => a.World) |
| | 1 | 281 | | .WithMany(w => w.Articles) |
| | 1 | 282 | | .HasForeignKey(a => a.WorldId) |
| | 1 | 283 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 284 | | |
| | 1 | 285 | | // Article -> Campaign |
| | 1 | 286 | | entity.HasOne(a => a.Campaign) |
| | 1 | 287 | | .WithMany(c => c.Articles) |
| | 1 | 288 | | .HasForeignKey(a => a.CampaignId) |
| | 1 | 289 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 290 | | |
| | 1 | 291 | | // Article -> Arc (for Session articles) |
| | 1 | 292 | | entity.HasOne(a => a.Arc) |
| | 1 | 293 | | .WithMany(arc => arc.Sessions) |
| | 1 | 294 | | .HasForeignKey(a => a.ArcId) |
| | 1 | 295 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 296 | | |
| | 1 | 297 | | // Article -> Creator (User) |
| | 1 | 298 | | entity.HasOne(a => a.Creator) |
| | 1 | 299 | | .WithMany(u => u.CreatedArticles) |
| | 1 | 300 | | .HasForeignKey(a => a.CreatedBy) |
| | 1 | 301 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 302 | | |
| | 1 | 303 | | // Article -> Modifier (User) |
| | 1 | 304 | | entity.HasOne(a => a.Modifier) |
| | 1 | 305 | | .WithMany(u => u.ModifiedArticles) |
| | 1 | 306 | | .HasForeignKey(a => a.LastModifiedBy) |
| | 1 | 307 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 308 | | |
| | 1 | 309 | | // Article -> Player (User) for Character ownership |
| | 1 | 310 | | entity.HasOne(a => a.Player) |
| | 1 | 311 | | .WithMany(u => u.OwnedCharacters) |
| | 1 | 312 | | .HasForeignKey(a => a.PlayerId) |
| | 1 | 313 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 314 | | |
| | 1 | 315 | | // Indexes |
| | 1 | 316 | | entity.HasIndex(a => a.ParentId); |
| | 1 | 317 | | entity.HasIndex(a => a.WorldId); |
| | 1 | 318 | | entity.HasIndex(a => a.CampaignId); |
| | 1 | 319 | | entity.HasIndex(a => a.CreatedBy); |
| | 1 | 320 | | entity.HasIndex(a => a.Type); |
| | 1 | 321 | | entity.HasIndex(a => a.Title); |
| | 1 | 322 | | |
| | 1 | 323 | | // Unique constraint: Slug must be unique among siblings |
| | 1 | 324 | | // For root articles (ParentId is null), scope by WorldId |
| | 1 | 325 | | entity.HasIndex(a => new { a.WorldId, a.Slug }) |
| | 1 | 326 | | .IsUnique() |
| | 1 | 327 | | .HasFilter("[ParentId] IS NULL") |
| | 1 | 328 | | .HasDatabaseName("IX_Articles_WorldId_Slug_Root"); |
| | 1 | 329 | | |
| | 1 | 330 | | // For child articles (ParentId is not null) |
| | 1 | 331 | | entity.HasIndex(a => new { a.ParentId, a.Slug }) |
| | 1 | 332 | | .IsUnique() |
| | 1 | 333 | | .HasFilter("[ParentId] IS NOT NULL") |
| | 1 | 334 | | .HasDatabaseName("IX_Articles_ParentId_Slug"); |
| | 2 | 335 | | }); |
| | 1 | 336 | | } |
| | | 337 | | |
| | | 338 | | private static void ConfigureArticleAlias(ModelBuilder modelBuilder) |
| | | 339 | | { |
| | 1 | 340 | | modelBuilder.Entity<ArticleAlias>(entity => |
| | 1 | 341 | | { |
| | 1 | 342 | | entity.HasKey(aa => aa.Id); |
| | 1 | 343 | | |
| | 1 | 344 | | // AliasText is required, max 200 characters |
| | 1 | 345 | | entity.Property(aa => aa.AliasText) |
| | 1 | 346 | | .HasMaxLength(200) |
| | 1 | 347 | | .IsRequired(); |
| | 1 | 348 | | |
| | 1 | 349 | | // AliasType is optional (for future use) |
| | 1 | 350 | | entity.Property(aa => aa.AliasType) |
| | 1 | 351 | | .HasMaxLength(50); |
| | 1 | 352 | | |
| | 1 | 353 | | // ArticleAlias -> Article (CASCADE delete - when article is deleted, remove its aliases) |
| | 1 | 354 | | entity.HasOne(aa => aa.Article) |
| | 1 | 355 | | .WithMany(a => a.Aliases) |
| | 1 | 356 | | .HasForeignKey(aa => aa.ArticleId) |
| | 1 | 357 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 358 | | |
| | 1 | 359 | | // Index for looking up aliases by article |
| | 1 | 360 | | entity.HasIndex(aa => aa.ArticleId); |
| | 1 | 361 | | |
| | 1 | 362 | | // Index for searching aliases (case-insensitive search will be handled in queries) |
| | 1 | 363 | | entity.HasIndex(aa => aa.AliasText); |
| | 1 | 364 | | |
| | 1 | 365 | | // Unique constraint: No duplicate aliases on the same article |
| | 1 | 366 | | entity.HasIndex(aa => new { aa.ArticleId, aa.AliasText }) |
| | 1 | 367 | | .IsUnique() |
| | 1 | 368 | | .HasDatabaseName("IX_ArticleAliases_ArticleId_AliasText"); |
| | 2 | 369 | | }); |
| | 1 | 370 | | } |
| | | 371 | | |
| | | 372 | | private static void ConfigureArticleLink(ModelBuilder modelBuilder) |
| | | 373 | | { |
| | 1 | 374 | | modelBuilder.Entity<ArticleLink>(entity => |
| | 1 | 375 | | { |
| | 1 | 376 | | entity.HasKey(al => al.Id); |
| | 1 | 377 | | |
| | 1 | 378 | | // DisplayText max length |
| | 1 | 379 | | entity.Property(al => al.DisplayText) |
| | 1 | 380 | | .HasMaxLength(500); |
| | 1 | 381 | | |
| | 1 | 382 | | // ArticleLink -> SourceArticle (CASCADE delete - when source is deleted, remove its links) |
| | 1 | 383 | | entity.HasOne(al => al.SourceArticle) |
| | 1 | 384 | | .WithMany(a => a.OutgoingLinks) |
| | 1 | 385 | | .HasForeignKey(al => al.SourceArticleId) |
| | 1 | 386 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 387 | | |
| | 1 | 388 | | // ArticleLink -> TargetArticle (NO ACTION - SQL Server limitation with multiple cascade paths) |
| | 1 | 389 | | // When target article is deleted, links must be cleaned up manually or via triggers |
| | 1 | 390 | | entity.HasOne(al => al.TargetArticle) |
| | 1 | 391 | | .WithMany(a => a.IncomingLinks) |
| | 1 | 392 | | .HasForeignKey(al => al.TargetArticleId) |
| | 1 | 393 | | .OnDelete(DeleteBehavior.NoAction); |
| | 1 | 394 | | |
| | 1 | 395 | | // Indexes for query performance |
| | 1 | 396 | | entity.HasIndex(al => al.SourceArticleId); |
| | 1 | 397 | | entity.HasIndex(al => al.TargetArticleId); |
| | 1 | 398 | | |
| | 1 | 399 | | // Unique constraint: Prevent duplicate links at same position |
| | 1 | 400 | | entity.HasIndex(al => new { al.SourceArticleId, al.TargetArticleId, al.Position }) |
| | 1 | 401 | | .IsUnique(); |
| | 2 | 402 | | }); |
| | 1 | 403 | | } |
| | | 404 | | |
| | | 405 | | private static void ConfigureArticleExternalLink(ModelBuilder modelBuilder) |
| | | 406 | | { |
| | 1 | 407 | | modelBuilder.Entity<ArticleExternalLink>(entity => |
| | 1 | 408 | | { |
| | 1 | 409 | | entity.HasKey(ael => ael.Id); |
| | 1 | 410 | | |
| | 1 | 411 | | // String field max lengths |
| | 1 | 412 | | entity.Property(ael => ael.Source) |
| | 1 | 413 | | .HasMaxLength(50) |
| | 1 | 414 | | .IsRequired(); |
| | 1 | 415 | | |
| | 1 | 416 | | entity.Property(ael => ael.ExternalId) |
| | 1 | 417 | | .HasMaxLength(200) |
| | 1 | 418 | | .IsRequired(); |
| | 1 | 419 | | |
| | 1 | 420 | | entity.Property(ael => ael.DisplayTitle) |
| | 1 | 421 | | .HasMaxLength(500) |
| | 1 | 422 | | .IsRequired(); |
| | 1 | 423 | | |
| | 1 | 424 | | // ArticleExternalLink -> Article (CASCADE delete - when article is deleted, remove its external links) |
| | 1 | 425 | | entity.HasOne(ael => ael.Article) |
| | 1 | 426 | | .WithMany(a => a.ExternalLinks) |
| | 1 | 427 | | .HasForeignKey(ael => ael.ArticleId) |
| | 1 | 428 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 429 | | |
| | 1 | 430 | | // Index for query performance (lookup all external links for an article) |
| | 1 | 431 | | entity.HasIndex(ael => ael.ArticleId); |
| | 1 | 432 | | |
| | 1 | 433 | | // Composite index for uniqueness and query performance |
| | 1 | 434 | | // An article should not have duplicate references to the same external resource |
| | 1 | 435 | | entity.HasIndex(ael => new { ael.ArticleId, ael.Source, ael.ExternalId }) |
| | 1 | 436 | | .IsUnique() |
| | 1 | 437 | | .HasDatabaseName("IX_ArticleExternalLinks_ArticleId_Source_ExternalId"); |
| | 2 | 438 | | }); |
| | 1 | 439 | | } |
| | | 440 | | |
| | | 441 | | private static void ConfigureWorldLink(ModelBuilder modelBuilder) |
| | | 442 | | { |
| | 1 | 443 | | modelBuilder.Entity<WorldLink>(entity => |
| | 1 | 444 | | { |
| | 1 | 445 | | entity.HasKey(wl => wl.Id); |
| | 1 | 446 | | |
| | 1 | 447 | | entity.Property(wl => wl.Url) |
| | 1 | 448 | | .HasMaxLength(2048) |
| | 1 | 449 | | .IsRequired(); |
| | 1 | 450 | | |
| | 1 | 451 | | entity.Property(wl => wl.Title) |
| | 1 | 452 | | .HasMaxLength(200) |
| | 1 | 453 | | .IsRequired(); |
| | 1 | 454 | | |
| | 1 | 455 | | entity.Property(wl => wl.Description) |
| | 1 | 456 | | .HasMaxLength(500); |
| | 1 | 457 | | |
| | 1 | 458 | | // WorldLink -> World (CASCADE delete - when world is deleted, remove its links) |
| | 1 | 459 | | entity.HasOne(wl => wl.World) |
| | 1 | 460 | | .WithMany(w => w.Links) |
| | 1 | 461 | | .HasForeignKey(wl => wl.WorldId) |
| | 1 | 462 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 463 | | |
| | 1 | 464 | | // Index for query performance |
| | 1 | 465 | | entity.HasIndex(wl => wl.WorldId); |
| | 2 | 466 | | }); |
| | 1 | 467 | | } |
| | | 468 | | |
| | | 469 | | private static void ConfigureWorldDocument(ModelBuilder modelBuilder) |
| | | 470 | | { |
| | 1 | 471 | | modelBuilder.Entity<WorldDocument>(entity => |
| | 1 | 472 | | { |
| | 1 | 473 | | entity.HasKey(wd => wd.Id); |
| | 1 | 474 | | |
| | 1 | 475 | | entity.Property(wd => wd.FileName) |
| | 1 | 476 | | .HasMaxLength(255) |
| | 1 | 477 | | .IsRequired(); |
| | 1 | 478 | | |
| | 1 | 479 | | entity.Property(wd => wd.Title) |
| | 1 | 480 | | .HasMaxLength(200) |
| | 1 | 481 | | .IsRequired(); |
| | 1 | 482 | | |
| | 1 | 483 | | entity.Property(wd => wd.BlobPath) |
| | 1 | 484 | | .HasMaxLength(1024) |
| | 1 | 485 | | .IsRequired(); |
| | 1 | 486 | | |
| | 1 | 487 | | entity.Property(wd => wd.ContentType) |
| | 1 | 488 | | .HasMaxLength(100) |
| | 1 | 489 | | .IsRequired(); |
| | 1 | 490 | | |
| | 1 | 491 | | entity.Property(wd => wd.Description) |
| | 1 | 492 | | .HasMaxLength(500); |
| | 1 | 493 | | |
| | 1 | 494 | | // WorldDocument -> World (CASCADE delete - when world is deleted, remove its documents) |
| | 1 | 495 | | entity.HasOne(wd => wd.World) |
| | 1 | 496 | | .WithMany(w => w.Documents) |
| | 1 | 497 | | .HasForeignKey(wd => wd.WorldId) |
| | 1 | 498 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 499 | | |
| | 1 | 500 | | // WorldDocument -> Article (SET NULL - when article is deleted, preserve document but clear reference) |
| | 1 | 501 | | entity.HasOne(wd => wd.Article) |
| | 1 | 502 | | .WithMany(a => a.Images) |
| | 1 | 503 | | .HasForeignKey(wd => wd.ArticleId) |
| | 1 | 504 | | .OnDelete(DeleteBehavior.SetNull); |
| | 1 | 505 | | |
| | 1 | 506 | | // WorldDocument -> UploadedBy (User) |
| | 1 | 507 | | entity.HasOne(wd => wd.UploadedBy) |
| | 1 | 508 | | .WithMany(u => u.UploadedDocuments) |
| | 1 | 509 | | .HasForeignKey(wd => wd.UploadedById) |
| | 1 | 510 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 511 | | |
| | 1 | 512 | | // Index for query performance |
| | 1 | 513 | | entity.HasIndex(wd => wd.WorldId); |
| | 1 | 514 | | entity.HasIndex(wd => wd.UploadedById); |
| | 1 | 515 | | entity.HasIndex(wd => wd.ArticleId) |
| | 1 | 516 | | .HasFilter("[ArticleId] IS NOT NULL") |
| | 1 | 517 | | .HasDatabaseName("IX_WorldDocuments_ArticleId"); |
| | 2 | 518 | | }); |
| | 1 | 519 | | } |
| | | 520 | | |
| | | 521 | | private static void ConfigureSummaryTemplate(ModelBuilder modelBuilder) |
| | | 522 | | { |
| | 1 | 523 | | modelBuilder.Entity<SummaryTemplate>(entity => |
| | 1 | 524 | | { |
| | 1 | 525 | | entity.HasKey(st => st.Id); |
| | 1 | 526 | | |
| | 1 | 527 | | entity.Property(st => st.Name) |
| | 1 | 528 | | .HasMaxLength(200) |
| | 1 | 529 | | .IsRequired(); |
| | 1 | 530 | | |
| | 1 | 531 | | entity.Property(st => st.Description) |
| | 1 | 532 | | .HasMaxLength(500); |
| | 1 | 533 | | |
| | 1 | 534 | | entity.Property(st => st.PromptTemplate) |
| | 1 | 535 | | .IsRequired(); |
| | 1 | 536 | | |
| | 1 | 537 | | // SummaryTemplate -> World (optional, for future world-specific templates) |
| | 1 | 538 | | entity.HasOne(st => st.World) |
| | 1 | 539 | | .WithMany() |
| | 1 | 540 | | .HasForeignKey(st => st.WorldId) |
| | 1 | 541 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 542 | | |
| | 1 | 543 | | // SummaryTemplate -> Creator (optional, for future user-created templates) |
| | 1 | 544 | | entity.HasOne(st => st.Creator) |
| | 1 | 545 | | .WithMany() |
| | 1 | 546 | | .HasForeignKey(st => st.CreatedBy) |
| | 1 | 547 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 548 | | |
| | 1 | 549 | | // Indexes |
| | 1 | 550 | | entity.HasIndex(st => st.WorldId); |
| | 1 | 551 | | entity.HasIndex(st => st.IsSystem); |
| | 2 | 552 | | }); |
| | | 553 | | |
| | | 554 | | // Article -> SummaryTemplate relationship |
| | 1 | 555 | | modelBuilder.Entity<Article>(entity => |
| | 1 | 556 | | { |
| | 1 | 557 | | entity.HasOne(a => a.SummaryTemplate) |
| | 1 | 558 | | .WithMany() |
| | 1 | 559 | | .HasForeignKey(a => a.SummaryTemplateId) |
| | 1 | 560 | | .OnDelete(DeleteBehavior.SetNull); |
| | 2 | 561 | | }); |
| | | 562 | | |
| | | 563 | | // Campaign -> SummaryTemplate relationship |
| | 1 | 564 | | modelBuilder.Entity<Campaign>(entity => |
| | 1 | 565 | | { |
| | 1 | 566 | | entity.HasOne(c => c.SummaryTemplate) |
| | 1 | 567 | | .WithMany() |
| | 1 | 568 | | .HasForeignKey(c => c.SummaryTemplateId) |
| | 1 | 569 | | .OnDelete(DeleteBehavior.SetNull); |
| | 2 | 570 | | }); |
| | | 571 | | |
| | | 572 | | // Arc -> SummaryTemplate relationship |
| | 1 | 573 | | modelBuilder.Entity<Arc>(entity => |
| | 1 | 574 | | { |
| | 1 | 575 | | entity.HasOne(a => a.SummaryTemplate) |
| | 1 | 576 | | .WithMany() |
| | 1 | 577 | | .HasForeignKey(a => a.SummaryTemplateId) |
| | 1 | 578 | | .OnDelete(DeleteBehavior.SetNull); |
| | 2 | 579 | | }); |
| | 1 | 580 | | } |
| | | 581 | | |
| | | 582 | | private static void ConfigureResourceProvider(ModelBuilder modelBuilder) |
| | | 583 | | { |
| | 1 | 584 | | modelBuilder.Entity<ResourceProvider>(entity => |
| | 1 | 585 | | { |
| | 1 | 586 | | // Primary key is Code (string) |
| | 1 | 587 | | entity.HasKey(rp => rp.Code); |
| | 1 | 588 | | |
| | 1 | 589 | | entity.Property(rp => rp.Code) |
| | 1 | 590 | | .HasMaxLength(20) |
| | 1 | 591 | | .IsRequired(); |
| | 1 | 592 | | |
| | 1 | 593 | | entity.Property(rp => rp.Name) |
| | 1 | 594 | | .HasMaxLength(200) |
| | 1 | 595 | | .IsRequired(); |
| | 1 | 596 | | |
| | 1 | 597 | | entity.Property(rp => rp.Description) |
| | 1 | 598 | | .HasMaxLength(500) |
| | 1 | 599 | | .IsRequired(); |
| | 1 | 600 | | |
| | 1 | 601 | | entity.Property(rp => rp.DocumentationLink) |
| | 1 | 602 | | .HasMaxLength(500) |
| | 1 | 603 | | .IsRequired(); |
| | 1 | 604 | | |
| | 1 | 605 | | entity.Property(rp => rp.License) |
| | 1 | 606 | | .HasMaxLength(500) |
| | 1 | 607 | | .IsRequired(); |
| | 1 | 608 | | |
| | 1 | 609 | | entity.Property(rp => rp.IsActive) |
| | 1 | 610 | | .HasDefaultValue(true); |
| | 1 | 611 | | |
| | 1 | 612 | | entity.Property(rp => rp.CreatedAt) |
| | 1 | 613 | | .IsRequired(); |
| | 1 | 614 | | |
| | 1 | 615 | | // Seed initial providers |
| | 1 | 616 | | entity.HasData( |
| | 1 | 617 | | new ResourceProvider |
| | 1 | 618 | | { |
| | 1 | 619 | | Code = "srd", |
| | 1 | 620 | | Name = "Open 5e API", |
| | 1 | 621 | | Description = "System Reference Document for D&D 5th Edition", |
| | 1 | 622 | | DocumentationLink = "https://open5e.com/api-docs", |
| | 1 | 623 | | License = "https://open5e.com/legal", |
| | 1 | 624 | | IsActive = true, |
| | 1 | 625 | | CreatedAt = new DateTimeOffset(2025, 1, 1, 0, 0, 0, TimeSpan.Zero) |
| | 1 | 626 | | }, |
| | 1 | 627 | | new ResourceProvider |
| | 1 | 628 | | { |
| | 1 | 629 | | Code = "srd14", |
| | 1 | 630 | | Name = "SRD 2014", |
| | 1 | 631 | | Description = "System Reference Document 5.1", |
| | 1 | 632 | | DocumentationLink = "https://www.dndbeyond.com/srd?srsltid=AfmBOooZgD0uD_hbmyYkHEvFJtDJzktTdIa_J_N2G |
| | 1 | 633 | | License = "https://opengamingfoundation.org/ogl.html", |
| | 1 | 634 | | IsActive = true, |
| | 1 | 635 | | CreatedAt = new DateTimeOffset(2025, 1, 1, 0, 0, 0, TimeSpan.Zero) |
| | 1 | 636 | | }, |
| | 1 | 637 | | new ResourceProvider |
| | 1 | 638 | | { |
| | 1 | 639 | | Code = "srd24", |
| | 1 | 640 | | Name = "SRD 2024", |
| | 1 | 641 | | Description = "System Reference Document 5.2.1", |
| | 1 | 642 | | DocumentationLink = "https://www.dndbeyond.com/srd?srsltid=AfmBOooZgD0uD_hbmyYkHEvFJtDJzktTdIa_J_N2G |
| | 1 | 643 | | License = "https://creativecommons.org/licenses/by/4.0/", |
| | 1 | 644 | | IsActive = true, |
| | 1 | 645 | | CreatedAt = new DateTimeOffset(2025, 1, 1, 0, 0, 0, TimeSpan.Zero) |
| | 1 | 646 | | }, |
| | 1 | 647 | | new ResourceProvider |
| | 1 | 648 | | { |
| | 1 | 649 | | Code = "ros", |
| | 1 | 650 | | Name = "Ruins of Symbaroum", |
| | 1 | 651 | | Description = "Ruins of Symbaroum source material", |
| | 1 | 652 | | DocumentationLink = "https://freeleaguepublishing.com/games/ruins-of-symbaroum/", |
| | 1 | 653 | | License = "https://opengamingfoundation.org/ogl.html", |
| | 1 | 654 | | IsActive = true, |
| | 1 | 655 | | CreatedAt = new DateTimeOffset(2025, 1, 1, 0, 0, 0, TimeSpan.Zero) |
| | 1 | 656 | | } |
| | 1 | 657 | | ); |
| | 2 | 658 | | }); |
| | 1 | 659 | | } |
| | | 660 | | |
| | | 661 | | private static void ConfigureWorldResourceProvider(ModelBuilder modelBuilder) |
| | | 662 | | { |
| | 1 | 663 | | modelBuilder.Entity<WorldResourceProvider>(entity => |
| | 1 | 664 | | { |
| | 1 | 665 | | // Composite primary key |
| | 1 | 666 | | entity.HasKey(wrp => new { wrp.WorldId, wrp.ResourceProviderCode }); |
| | 1 | 667 | | |
| | 1 | 668 | | entity.Property(wrp => wrp.IsEnabled) |
| | 1 | 669 | | .IsRequired(); |
| | 1 | 670 | | |
| | 1 | 671 | | entity.Property(wrp => wrp.ModifiedAt) |
| | 1 | 672 | | .IsRequired(); |
| | 1 | 673 | | |
| | 1 | 674 | | entity.Property(wrp => wrp.ModifiedByUserId) |
| | 1 | 675 | | .IsRequired(); |
| | 1 | 676 | | |
| | 1 | 677 | | // WorldResourceProvider -> World (CASCADE delete - when world is deleted, remove provider associations) |
| | 1 | 678 | | entity.HasOne(wrp => wrp.World) |
| | 1 | 679 | | .WithMany(w => w.WorldResourceProviders) |
| | 1 | 680 | | .HasForeignKey(wrp => wrp.WorldId) |
| | 1 | 681 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 682 | | |
| | 1 | 683 | | // WorldResourceProvider -> ResourceProvider (RESTRICT - don't allow provider deletion if in use) |
| | 1 | 684 | | entity.HasOne(wrp => wrp.ResourceProvider) |
| | 1 | 685 | | .WithMany(rp => rp.WorldResourceProviders) |
| | 1 | 686 | | .HasForeignKey(wrp => wrp.ResourceProviderCode) |
| | 1 | 687 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 688 | | |
| | 1 | 689 | | // Index for querying providers by world |
| | 1 | 690 | | entity.HasIndex(wrp => wrp.WorldId); |
| | 1 | 691 | | |
| | 1 | 692 | | // Index for querying worlds by provider |
| | 1 | 693 | | entity.HasIndex(wrp => wrp.ResourceProviderCode); |
| | 2 | 694 | | }); |
| | 1 | 695 | | } |
| | | 696 | | |
| | | 697 | | private static void ConfigureQuest(ModelBuilder modelBuilder) |
| | | 698 | | { |
| | 1 | 699 | | modelBuilder.Entity<Quest>(entity => |
| | 1 | 700 | | { |
| | 1 | 701 | | entity.HasKey(q => q.Id); |
| | 1 | 702 | | |
| | 1 | 703 | | // Title is required, max 300 characters |
| | 1 | 704 | | entity.Property(q => q.Title) |
| | 1 | 705 | | .HasMaxLength(300) |
| | 1 | 706 | | .IsRequired(); |
| | 1 | 707 | | |
| | 1 | 708 | | // Description is HTML from TipTap (nullable) |
| | 1 | 709 | | entity.Property(q => q.Description); |
| | 1 | 710 | | |
| | 1 | 711 | | // Status enum |
| | 1 | 712 | | entity.Property(q => q.Status) |
| | 1 | 713 | | .IsRequired(); |
| | 1 | 714 | | |
| | 1 | 715 | | // IsGmOnly flag |
| | 1 | 716 | | entity.Property(q => q.IsGmOnly) |
| | 1 | 717 | | .HasDefaultValue(false); |
| | 1 | 718 | | |
| | 1 | 719 | | // SortOrder for display ordering |
| | 1 | 720 | | entity.Property(q => q.SortOrder) |
| | 1 | 721 | | .HasDefaultValue(0); |
| | 1 | 722 | | |
| | 1 | 723 | | // Timestamps |
| | 1 | 724 | | entity.Property(q => q.CreatedAt) |
| | 1 | 725 | | .IsRequired(); |
| | 1 | 726 | | |
| | 1 | 727 | | entity.Property(q => q.UpdatedAt) |
| | 1 | 728 | | .IsRequired(); |
| | 1 | 729 | | |
| | 1 | 730 | | // RowVersion for optimistic concurrency |
| | 1 | 731 | | entity.Property(q => q.RowVersion) |
| | 1 | 732 | | .IsRowVersion(); |
| | 1 | 733 | | |
| | 1 | 734 | | // Quest -> Arc (CASCADE delete - when arc is deleted, remove its quests) |
| | 1 | 735 | | entity.HasOne(q => q.Arc) |
| | 1 | 736 | | .WithMany() |
| | 1 | 737 | | .HasForeignKey(q => q.ArcId) |
| | 1 | 738 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 739 | | |
| | 1 | 740 | | // Quest -> Creator (User) (RESTRICT - don't allow user deletion if they created quests) |
| | 1 | 741 | | entity.HasOne(q => q.Creator) |
| | 1 | 742 | | .WithMany(u => u.CreatedQuests) |
| | 1 | 743 | | .HasForeignKey(q => q.CreatedBy) |
| | 1 | 744 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 745 | | |
| | 1 | 746 | | // Indexes per architecture spec |
| | 1 | 747 | | entity.HasIndex(q => q.ArcId) |
| | 1 | 748 | | .HasDatabaseName("IX_Quest_ArcId"); |
| | 1 | 749 | | |
| | 1 | 750 | | entity.HasIndex(q => new { q.ArcId, q.Status }) |
| | 1 | 751 | | .HasDatabaseName("IX_Quest_ArcId_Status"); |
| | 1 | 752 | | |
| | 1 | 753 | | entity.HasIndex(q => new { q.ArcId, q.UpdatedAt }) |
| | 1 | 754 | | .HasDatabaseName("IX_Quest_ArcId_UpdatedAt"); |
| | 2 | 755 | | }); |
| | 1 | 756 | | } |
| | | 757 | | |
| | | 758 | | private static void ConfigureQuestUpdate(ModelBuilder modelBuilder) |
| | | 759 | | { |
| | 1 | 760 | | modelBuilder.Entity<QuestUpdate>(entity => |
| | 1 | 761 | | { |
| | 1 | 762 | | entity.HasKey(qu => qu.Id); |
| | 1 | 763 | | |
| | 1 | 764 | | // Body is HTML from TipTap (required, non-empty) |
| | 1 | 765 | | entity.Property(qu => qu.Body) |
| | 1 | 766 | | .IsRequired(); |
| | 1 | 767 | | |
| | 1 | 768 | | // Timestamp |
| | 1 | 769 | | entity.Property(qu => qu.CreatedAt) |
| | 1 | 770 | | .IsRequired(); |
| | 1 | 771 | | |
| | 1 | 772 | | // QuestUpdate -> Quest (CASCADE delete - when quest is deleted, remove its updates) |
| | 1 | 773 | | entity.HasOne(qu => qu.Quest) |
| | 1 | 774 | | .WithMany(q => q.Updates) |
| | 1 | 775 | | .HasForeignKey(qu => qu.QuestId) |
| | 1 | 776 | | .OnDelete(DeleteBehavior.Cascade); |
| | 1 | 777 | | |
| | 1 | 778 | | // QuestUpdate -> Session (Article) (SET NULL - when session is deleted, preserve the update but clear the r |
| | 1 | 779 | | entity.HasOne(qu => qu.Session) |
| | 1 | 780 | | .WithMany() |
| | 1 | 781 | | .HasForeignKey(qu => qu.SessionId) |
| | 1 | 782 | | .OnDelete(DeleteBehavior.SetNull); |
| | 1 | 783 | | |
| | 1 | 784 | | // QuestUpdate -> Creator (User) (RESTRICT - don't allow user deletion if they created updates) |
| | 1 | 785 | | entity.HasOne(qu => qu.Creator) |
| | 1 | 786 | | .WithMany(u => u.CreatedQuestUpdates) |
| | 1 | 787 | | .HasForeignKey(qu => qu.CreatedBy) |
| | 1 | 788 | | .OnDelete(DeleteBehavior.Restrict); |
| | 1 | 789 | | |
| | 1 | 790 | | // Indexes per architecture spec |
| | 1 | 791 | | entity.HasIndex(qu => new { qu.QuestId, qu.CreatedAt }) |
| | 1 | 792 | | .HasDatabaseName("IX_QuestUpdate_QuestId_CreatedAt"); |
| | 1 | 793 | | |
| | 1 | 794 | | entity.HasIndex(qu => qu.SessionId) |
| | 1 | 795 | | .HasDatabaseName("IX_QuestUpdate_SessionId"); |
| | 2 | 796 | | }); |
| | 1 | 797 | | } |
| | | 798 | | } |