using ClassNotify.Server.Domain.Entities; using ClassNotify.Shared.Models; using Microsoft.EntityFrameworkCore; namespace ClassNotify.Server.Data; public class ClassNotifyDbContext : DbContext { public ClassNotifyDbContext(DbContextOptions options) : base(options) { } public DbSet Tenants => Set(); public DbSet Classrooms => Set(); public DbSet Users => Set(); public DbSet Devices => Set(); public DbSet DeviceCredentials => Set(); public DbSet ActivationCodes => Set(); public DbSet Notifications => Set(); public DbSet NotificationReceipts => Set(); public DbSet MigrationMarkers => Set(); public DbSet DeviceCommands => Set(); public DbSet AdminAuditEvents => Set(); public DbSet DeviceSelfChecks => Set(); public DbSet DeviceSelfCheckBatches => Set(); public DbSet DeviceSupportBundles => Set(); public DbSet UpdateReleases => Set(); public DbSet DeviceUpdateRuns => Set(); public DbSet DeviceUpdateBatches => Set(); public DbSet DeviceLicenses => Set(); public DbSet ActivationCodeAssignments => Set(); public DbSet DeviceTrialLedgers => Set(); public DbSet PasswordResetTokens => Set(); public DbSet NotificationHistoryDismissals => Set(); public DbSet TeacherDeviceBindings => Set(); public DbSet Orders => Set(); public DbSet SubscriptionVouchers => Set(); protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); // 1. Tenant modelBuilder.Entity(entity => { entity.HasKey(t => t.Id); entity.Property(t => t.Name).IsRequired().HasMaxLength(200); }); // 2. Classroom (Composite key for multi-tenant isolation) modelBuilder.Entity(entity => { entity.HasKey(c => new { c.TenantId, c.Id }); entity.Property(c => c.DisplayName).IsRequired().HasMaxLength(100); entity.Property(c => c.Grade).HasMaxLength(50); entity.Property(c => c.RoomNumber).HasMaxLength(50); entity.HasOne(c => c.Tenant) .WithMany(t => t.Classrooms) .HasForeignKey(c => c.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasIndex(c => new { c.TenantId, c.Id }); }); // 3. User modelBuilder.Entity(entity => { entity.HasKey(u => u.Id); entity.Property(u => u.Username).IsRequired().HasMaxLength(100); entity.Property(u => u.PasswordHash).IsRequired(); entity.Property(u => u.FullName).IsRequired().HasMaxLength(100); entity.Property(u => u.Role).IsRequired().HasMaxLength(50); entity.Property(u => u.RecoveryEmail).HasMaxLength(128); entity.Property(u => u.RecoveryKeyHash).HasMaxLength(128); entity.HasIndex(u => u.Username).IsUnique(); entity.HasIndex(u => u.TenantId); entity.HasOne(u => u.Tenant) .WithMany(t => t.Users) .HasForeignKey(u => u.TenantId) .OnDelete(DeleteBehavior.Restrict); }); // 4. Device modelBuilder.Entity(entity => { entity.HasKey(d => d.Id); entity.Property(d => d.DeviceTokenHash).IsRequired(); entity.Property(d => d.InstallationId).HasMaxLength(100); entity.Property(d => d.Hostname).HasMaxLength(200); entity.HasIndex(d => d.Id).IsUnique(); entity.HasIndex(d => d.InstallationId).IsUnique().HasFilter("\"InstallationId\" IS NOT NULL"); entity.HasIndex(d => new { d.TenantId, d.ClassroomId }); entity.HasIndex(d => d.TenantId); entity.HasOne(d => d.Tenant) .WithMany(t => t.Devices) .HasForeignKey(d => d.TenantId) .OnDelete(DeleteBehavior.Restrict); entity.HasOne(d => d.Classroom) .WithMany(c => c.Devices) .HasForeignKey(d => new { d.TenantId, d.ClassroomId }) .OnDelete(DeleteBehavior.Restrict); }); // 5. DeviceCredentialRecord modelBuilder.Entity(entity => { entity.HasKey(c => c.DeviceId); entity.Property(c => c.InstallationId).IsRequired().HasMaxLength(100); entity.Property(c => c.DeviceTokenHash).IsRequired(); entity.Property(c => c.EncryptedRecoverableToken).IsRequired(); entity.HasIndex(c => c.DeviceId).IsUnique(); entity.HasIndex(c => c.InstallationId).IsUnique(); }); // 6. ActivationCode modelBuilder.Entity(entity => { entity.HasKey(a => a.Code); entity.Property(a => a.Code).HasMaxLength(50); entity.Property(a => a.ClassroomId).IsRequired().HasMaxLength(50); entity.Property(a => a.InstallationId).HasMaxLength(100); entity.Property(a => a.Notes).HasMaxLength(255); entity.Property(a => a.TargetClassNameAtIssue).HasMaxLength(100); entity.Property(a => a.AssignedTeacherId).HasMaxLength(100); entity.Property(a => a.AssignedTeacherName).HasMaxLength(100); entity.Property(a => a.DistributionMethod).HasMaxLength(50); entity.Property(a => a.AssignedByUserId).HasMaxLength(100); entity.Property(a => a.SecretExposedAtUtc); entity.HasIndex(a => a.Code).IsUnique(); entity.HasIndex(a => a.TenantId); entity.HasIndex(a => a.ClassroomId); entity.HasIndex(a => a.InstallationId); entity.HasIndex(a => new { a.TenantId, a.AssignedTeacherId }); entity.HasIndex(a => new { a.TenantId, a.IsSignupTrial }) .IsUnique() .HasFilter("\"IsSignupTrial\" = true"); entity.HasOne(a => a.Tenant) .WithMany(t => t.ActivationCodes) .HasForeignKey(a => a.TenantId) .OnDelete(DeleteBehavior.Restrict); }); // 7. Notification modelBuilder.Entity(entity => { entity.HasKey(n => n.Id); entity.Property(n => n.Id).HasMaxLength(100); entity.Property(n => n.ClassroomId).IsRequired().HasMaxLength(50); entity.Property(n => n.SenderName).IsRequired().HasMaxLength(100); entity.Property(n => n.Message).IsRequired(); entity.Property(n => n.Title).IsRequired().HasMaxLength(200); entity.Property(n => n.Priority).IsRequired().HasMaxLength(50); entity.Property(n => n.CorrelationId).HasMaxLength(32); entity.HasIndex(n => n.CorrelationId); entity.HasIndex(n => new { n.TenantId, n.CreatedAt }).IsDescending(false, true); entity.HasIndex(n => new { n.ClassroomId, n.CreatedAt }).IsDescending(false, true); entity.HasIndex(n => new { n.TenantId, n.ClassroomId, n.ExpiresAt }); entity.HasOne(n => n.Tenant) .WithMany(t => t.Notifications) .HasForeignKey(n => n.TenantId) .OnDelete(DeleteBehavior.Cascade); }); // 8. NotificationReceipt (per-device ACK state isolation) modelBuilder.Entity(entity => { entity.HasKey(r => r.Id); entity.Property(r => r.Id).HasMaxLength(100); entity.Property(r => r.NotificationId).IsRequired().HasMaxLength(100); entity.Property(r => r.DeviceId).IsRequired().HasMaxLength(100); entity.Property(r => r.Status).IsRequired().HasMaxLength(50); entity.Property(r => r.FailureReason).HasMaxLength(500); entity.Property(r => r.VisualReceiptState).IsRequired().HasMaxLength(32).HasDefaultValue("None"); entity.Property(r => r.VisualReceiptPath).HasMaxLength(500); entity.Property(r => r.VisualReceiptSha256).HasMaxLength(64); entity.HasIndex(r => new { r.NotificationId, r.DeviceId }).IsUnique(); entity.HasIndex(r => new { r.DeviceId, r.UpdatedAt }).IsDescending(false, true); entity.HasIndex(r => new { r.DeviceId, r.Status }); entity.HasIndex(r => r.VisualReceiptExpiresAt); entity.HasOne(r => r.Notification) .WithMany(n => n.Receipts) .HasForeignKey(r => r.NotificationId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(r => r.Device) .WithMany(d => d.Receipts) .HasForeignKey(r => r.DeviceId) .OnDelete(DeleteBehavior.Cascade); }); // 9. MigrationMarker modelBuilder.Entity(entity => { entity.HasKey(m => m.Id); entity.Property(m => m.Id).HasMaxLength(100); }); // 10. DeviceCommand modelBuilder.Entity(entity => { entity.HasKey(c => c.Id); entity.Property(c => c.Id).HasMaxLength(100); entity.Property(c => c.TenantId).IsRequired().HasMaxLength(100); entity.Property(c => c.DeviceId).IsRequired().HasMaxLength(100); entity.Property(c => c.CommandType).IsRequired().HasMaxLength(50); entity.Property(c => c.CreatedBy).IsRequired().HasMaxLength(100); entity.Property(c => c.Status).IsRequired().HasMaxLength(50); entity.Property(c => c.DurationMs); entity.Property(c => c.ResultMessage).HasMaxLength(1000); entity.HasIndex(c => new { c.TenantId, c.DeviceId, c.CreatedAt }).IsDescending(false, false, true); entity.HasIndex(c => new { c.DeviceId, c.Status }); entity.HasIndex(c => new { c.Status, c.ExpiresAt }); entity.HasOne(c => c.Tenant) .WithMany() .HasForeignKey(c => c.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(c => c.Device) .WithMany() .HasForeignKey(c => c.DeviceId) .OnDelete(DeleteBehavior.Cascade); }); // 11. DeviceSelfCheckRun modelBuilder.Entity(entity => { entity.HasKey(sc => sc.Id); entity.Property(sc => sc.Id).HasMaxLength(100); entity.Property(sc => sc.BatchId).HasMaxLength(100); entity.Property(sc => sc.TenantId).IsRequired().HasMaxLength(100); entity.Property(sc => sc.DeviceId).IsRequired().HasMaxLength(100); entity.Property(sc => sc.CorrelationId).HasMaxLength(32); entity.Property(sc => sc.Status).IsRequired().HasMaxLength(50); entity.Property(sc => sc.RunState).IsRequired().HasMaxLength(32).HasDefaultValue(ClassNotify.Shared.Models.SelfCheckRunStates.PendingDispatch); entity.Property(sc => sc.OverallStatus).HasMaxLength(32); entity.Property(sc => sc.ResultJson).HasMaxLength(32768); entity.Property(sc => sc.RequestedByUserId).HasMaxLength(100); entity.HasIndex(sc => new { sc.DeviceId, sc.RequestedAtUtc }).IsDescending(false, true); entity.HasIndex(sc => sc.CorrelationId); entity.HasIndex(sc => new { sc.TenantId, sc.Status }); entity.HasIndex(sc => new { sc.BatchId, sc.RunState }); entity.HasIndex(sc => new { sc.BatchId, sc.OverallStatus }); entity.HasIndex(sc => new { sc.BatchId, sc.DeviceId }).IsUnique().HasFilter("\"BatchId\" IS NOT NULL"); entity.HasOne(sc => sc.Tenant) .WithMany() .HasForeignKey(sc => sc.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(sc => sc.Device) .WithMany() .HasForeignKey(sc => sc.DeviceId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(sc => sc.Batch) .WithMany() .HasForeignKey(sc => sc.BatchId) .OnDelete(DeleteBehavior.Restrict); }); // 12. DeviceSelfCheckBatch (P2.5 RC1-2) modelBuilder.Entity(entity => { entity.HasKey(x => x.Id); entity.Property(x => x.Id).HasMaxLength(100); entity.Property(x => x.CorrelationId).HasMaxLength(32).IsRequired(); entity.Property(x => x.ClientRequestId).HasMaxLength(64).IsRequired(); entity.Property(x => x.CreatedByUserId).HasMaxLength(100).IsRequired(); entity.Property(x => x.Status).HasMaxLength(32).IsRequired(); entity.HasIndex(x => x.CreatedAtUtc); entity.HasIndex(x => new { x.CreatedByUserId, x.ClientRequestId }).IsUnique(); }); // 13. DeviceSupportBundle (P2.5 RC1-3 Support Bundle) modelBuilder.Entity(entity => { entity.ToTable("DeviceSupportBundles", t => { t.HasCheckConstraint("CK_DeviceSupportBundles_Status", "\"Status\" IN ('PendingDispatch', 'Collecting', 'Available', 'Failed', 'TimedOut', 'Expired')"); }); entity.HasKey(sb => sb.Id); entity.Property(sb => sb.Id).HasMaxLength(100); entity.Property(sb => sb.ClientRequestId).HasMaxLength(64).IsRequired(); entity.Property(sb => sb.TenantId).IsRequired().HasMaxLength(100); entity.Property(sb => sb.DeviceId).IsRequired().HasMaxLength(100); entity.Property(sb => sb.CorrelationId).HasMaxLength(32).IsRequired(); entity.Property(sb => sb.RequestedByUserId).HasMaxLength(100).IsRequired(); entity.Property(sb => sb.Status).IsRequired().HasMaxLength(32); entity.Property(sb => sb.StoragePath).HasMaxLength(500); entity.Property(sb => sb.Sha256).HasMaxLength(64); entity.Property(sb => sb.ErrorCode).HasMaxLength(100); // 1. (RequestedByUserId, ClientRequestId) UNIQUE entity.HasIndex(sb => new { sb.RequestedByUserId, sb.ClientRequestId }).IsUnique(); // 2. Partial Unique Index: DeviceId where Status IN ('PendingDispatch', 'Collecting') entity.HasIndex(sb => sb.DeviceId) .IsUnique() .HasDatabaseName("UX_DeviceSupportBundles_Device_Active") .HasFilter("\"Status\" IN ('PendingDispatch', 'Collecting')"); // 3. (Status, NextDispatchAttemptAtUtc) entity.HasIndex(sb => new { sb.Status, sb.NextDispatchAttemptAtUtc }); // 4. (TenantId, CreatedAtUtc) entity.HasIndex(sb => new { sb.TenantId, sb.CreatedAtUtc }); // 5. (DeviceId, CreatedAtUtc) entity.HasIndex(sb => new { sb.DeviceId, sb.CreatedAtUtc }); // 6. CorrelationId entity.HasIndex(sb => sb.CorrelationId); // 7. ExpiresAtUtc entity.HasIndex(sb => sb.ExpiresAtUtc); entity.HasOne(sb => sb.Tenant) .WithMany() .HasForeignKey(sb => sb.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(sb => sb.Device) .WithMany() .HasForeignKey(sb => sb.DeviceId) .OnDelete(DeleteBehavior.Cascade); }); // 13. UpdateRelease (P2.5 RC1-4) modelBuilder.Entity(entity => { entity.HasKey(r => r.Id); entity.Property(r => r.Id).HasMaxLength(64); entity.Property(r => r.Version).IsRequired().HasMaxLength(50); entity.Property(r => r.Architecture).IsRequired().HasMaxLength(32); entity.Property(r => r.PackageRelativePath).IsRequired().HasMaxLength(500); entity.Property(r => r.PackageSha256).IsRequired().HasMaxLength(64); entity.Property(r => r.ManifestSignature).IsRequired().HasMaxLength(512); entity.HasIndex(r => new { r.Version, r.Architecture }).IsUnique(); entity.HasIndex(r => r.IsEnabled); }); // 14. DeviceUpdateRun (P2.5 RC1-4) modelBuilder.Entity(entity => { entity.HasKey(u => u.Id); entity.Property(u => u.Id).HasMaxLength(64); entity.Property(u => u.TenantId).IsRequired().HasMaxLength(50); entity.Property(u => u.DeviceId).IsRequired().HasMaxLength(50); entity.Property(u => u.ReleaseId).IsRequired().HasMaxLength(64); entity.Property(u => u.CorrelationId).IsRequired().HasMaxLength(64); entity.Property(u => u.RequestedByUserId).IsRequired().HasMaxLength(100); entity.Property(u => u.FromVersion).HasMaxLength(50); entity.Property(u => u.TargetVersion).IsRequired().HasMaxLength(50); entity.Property(u => u.Status).IsRequired().HasMaxLength(32); entity.Property(u => u.ErrorCode).HasMaxLength(100); entity.Property(u => u.ErrorMessage).HasMaxLength(500); // Partial Unique: DeviceId where Status is active entity.HasIndex(u => u.DeviceId) .IsUnique() .HasDatabaseName("UX_DeviceUpdateRuns_Device_Active") .HasFilter("\"Status\" IN ('PendingDispatch', 'Downloading', 'Verified', 'Installing', 'HealthChecking', 'RollingBack')"); entity.HasIndex(u => new { u.Status, u.NextDispatchAttemptAtUtc }); entity.HasIndex(u => new { u.DeviceId, u.CreatedAtUtc }); entity.HasIndex(u => new { u.TenantId, u.CreatedAtUtc }); entity.HasIndex(u => u.CorrelationId); entity.Property(u => u.BatchId).HasMaxLength(64); entity.Property(u => u.DispatchGate).IsRequired().HasMaxLength(32).HasDefaultValue(UpdateDispatchGates.Released); entity.HasIndex(u => u.BatchId); entity.HasOne(u => u.Tenant) .WithMany() .HasForeignKey(u => u.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(u => u.Device) .WithMany() .HasForeignKey(u => u.DeviceId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(u => u.Release) .WithMany() .HasForeignKey(u => u.ReleaseId) .OnDelete(DeleteBehavior.Restrict); }); // 15. DeviceUpdateBatch (P2.5 RC1-4 Phase 3) modelBuilder.Entity(entity => { entity.HasKey(b => b.Id); entity.Property(b => b.Id).HasMaxLength(64); entity.Property(b => b.TenantId).IsRequired().HasMaxLength(50); entity.Property(b => b.ReleaseId).IsRequired().HasMaxLength(64); entity.Property(b => b.CanaryDeviceId).IsRequired().HasMaxLength(50); entity.Property(b => b.RequestedByUserId).IsRequired().HasMaxLength(100); entity.Property(b => b.ClientRequestId).HasMaxLength(100); entity.Property(b => b.Status).IsRequired().HasMaxLength(32); entity.HasIndex(b => new { b.RequestedByUserId, b.ClientRequestId }) .IsUnique() .HasFilter("\"ClientRequestId\" IS NOT NULL"); entity.HasIndex(b => new { b.TenantId, b.CreatedAtUtc }); entity.HasIndex(b => b.Status); entity.HasOne(b => b.Tenant) .WithMany() .HasForeignKey(b => b.TenantId) .OnDelete(DeleteBehavior.Cascade); entity.HasOne(b => b.Release) .WithMany() .HasForeignKey(b => b.ReleaseId) .OnDelete(DeleteBehavior.Restrict); entity.HasMany(b => b.Runs) .WithOne(r => r.Batch) .HasForeignKey(r => r.BatchId) .OnDelete(DeleteBehavior.Cascade); }); // 16. DeviceLicenseRecord (P3 License Lifecycle) modelBuilder.Entity(entity => { entity.HasKey(l => l.Id); entity.Property(l => l.Id).HasMaxLength(100); entity.Property(l => l.TenantId).IsRequired().HasMaxLength(100); entity.Property(l => l.DeviceId).IsRequired().HasMaxLength(100); entity.Property(l => l.ClassroomId).IsRequired().HasMaxLength(100); entity.Property(l => l.InstallationId).HasMaxLength(100); entity.Property(l => l.Action).IsRequired().HasMaxLength(50); entity.Property(l => l.LicenseTier).IsRequired().HasMaxLength(50); entity.Property(l => l.SourceActivationCode).HasMaxLength(100); entity.Property(l => l.OperatorUsername).HasMaxLength(100); entity.Property(l => l.Reason).HasMaxLength(250); entity.HasIndex(l => new { l.TenantId, l.DeviceId }); entity.HasIndex(l => l.InstallationId); entity.HasIndex(l => l.SourceActivationCode) .IsUnique() .HasFilter("\"SourceActivationCode\" IS NOT NULL"); entity.HasIndex(l => l.CreatedAt); }); // 18. DeviceTrialLedger (Server-Authoritative Device Trial Eligibility) modelBuilder.Entity(entity => { entity.HasKey(d => d.Id); entity.Property(d => d.Id).HasMaxLength(64); entity.Property(d => d.DeviceTrialKey).IsRequired().HasMaxLength(128); entity.Property(d => d.OriginalDeviceId).HasMaxLength(64); entity.Property(d => d.OriginalTenantId).HasMaxLength(64); entity.Property(d => d.OriginalUserId).HasMaxLength(64); entity.Property(d => d.Status).IsRequired().HasMaxLength(32); entity.Property(d => d.OverrideReason).HasMaxLength(255); entity.Property(d => d.OverriddenByUserId).HasMaxLength(64); entity.Property(d => d.SourceActivationCodeId).HasMaxLength(64); entity.HasIndex(d => d.DeviceTrialKey).IsUnique(); entity.HasIndex(d => d.OriginalTenantId); entity.HasIndex(d => d.Status); }); // 19. PasswordResetToken (One-time, 15-min expiry) modelBuilder.Entity(entity => { entity.HasKey(p => p.Id); entity.Property(p => p.Id).HasMaxLength(64); entity.Property(p => p.UserId).IsRequired().HasMaxLength(64); entity.Property(p => p.TokenHash).IsRequired().HasMaxLength(128); entity.Property(p => p.TokenType).IsRequired().HasMaxLength(32); entity.Property(p => p.IssuedByUserId).HasMaxLength(64); entity.HasIndex(p => p.TokenHash); entity.HasIndex(p => p.UserId); }); // 20. NotificationHistoryDismissal (Per-user history dismissal) modelBuilder.Entity(entity => { entity.HasKey(d => d.Id); entity.Property(d => d.Id).HasMaxLength(100); entity.Property(d => d.TenantId).IsRequired().HasMaxLength(100); entity.Property(d => d.UserId).IsRequired().HasMaxLength(100); entity.Property(d => d.NotificationId).IsRequired().HasMaxLength(100); entity.HasIndex(d => new { d.UserId, d.NotificationId }).IsUnique(); entity.HasIndex(d => new { d.TenantId, d.UserId }); entity.HasOne(d => d.Notification) .WithMany() .HasForeignKey(d => d.NotificationId) .OnDelete(DeleteBehavior.Cascade); }); } }