/*
 * File: ShadowedRealms.Data/Contexts/GameDbContext.cs
 * Created: 2025-10-19
 * Last Modified: 2025-11-01
 * Description: Main Entity Framework database context for Shadowed Realms. Updated with Admin management tables.
 * Last Edit Notes: Added AdminPlayerMessage, AdminActionAudit, AdminSession tables and entities for admin dashboard functionality
 */

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using ShadowedRealms.Core.Models.Alliance;
using ShadowedRealms.Core.Models.Kingdom;
using ShadowedRealms.Core.Models.Player;
using ShadowedRealms.Core.Models.Combat;
using ShadowedRealms.Core.Models.Purchase;
using ShadowedRealms.Core.Models.Admin;
using System.Linq.Expressions;

namespace ShadowedRealms.Data.Contexts
{
    public class GameDbContext : IdentityDbContext<ApplicationUser, IdentityRole<int>, int>
    {
        private readonly ILogger<GameDbContext> _logger;
        private int? _currentKingdomId;

        public GameDbContext(DbContextOptions<GameDbContext> options, ILogger<GameDbContext> logger)
            : base(options)
        {
            _logger = logger;
        }

        // Core Game Entities
        public DbSet<Player> Players { get; set; }
        public DbSet<Alliance> Alliances { get; set; }
        public DbSet<Kingdom> Kingdoms { get; set; }
        public DbSet<CombatLog> CombatLogs { get; set; }
        public DbSet<PurchaseLog> PurchaseLogs { get; set; }

        // Admin Management Entities
        public DbSet<AdminPlayerMessage> AdminPlayerMessages { get; set; }
        public DbSet<AdminActionAudit> AdminActionAudits { get; set; }
        public DbSet<AdminSession> AdminSessions { get; set; }

        // Kingdom Context Management
        public void SetKingdomContext(int kingdomId)
        {
            _currentKingdomId = kingdomId;
            _logger.LogInformation("Kingdom context set to {KingdomId}", kingdomId);
        }

        public int? GetCurrentKingdomId()
        {
            return _currentKingdomId;
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            try
            {
                // Configure Identity tables with custom names
                ConfigureIdentityTables(modelBuilder);

                // Configure core game entities
                ConfigurePlayerEntity(modelBuilder);
                ConfigureAllianceEntity(modelBuilder);
                ConfigureKingdomEntity(modelBuilder);
                ConfigureCombatLogEntity(modelBuilder);
                ConfigurePurchaseLogEntity(modelBuilder);

                // Configure admin management entities
                ConfigureAdminPlayerMessageEntity(modelBuilder);
                ConfigureAdminActionAuditEntity(modelBuilder);
                ConfigureAdminSessionEntity(modelBuilder);

                // Configure related entities (fixes Alliance global query filter warnings)
                ConfigureAllianceRelatedEntities(modelBuilder);

                // Apply kingdom-scoped global query filters
                ApplyKingdomScopedFilters(modelBuilder);

                // Configure indexes for performance
                ConfigureIndexes(modelBuilder);

                _logger.LogInformation("Database model configuration completed successfully");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to configure database model");
                throw;
            }
        }

        private void ConfigureIdentityTables(ModelBuilder modelBuilder)
        {
            // Rename Identity tables to match our naming convention
            modelBuilder.Entity<ApplicationUser>().ToTable("Players_Auth");
            modelBuilder.Entity<IdentityRole<int>>().ToTable("Roles");
            modelBuilder.Entity<IdentityUserRole<int>>().ToTable("Player_Roles");
            modelBuilder.Entity<IdentityUserClaim<int>>().ToTable("Player_Claims");
            modelBuilder.Entity<IdentityUserLogin<int>>().ToTable("Player_Logins");
            modelBuilder.Entity<IdentityRoleClaim<int>>().ToTable("Role_Claims");
            modelBuilder.Entity<IdentityUserToken<int>>().ToTable("Player_Tokens");
        }

        private void ConfigurePlayerEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Player>(entity =>
            {
                entity.HasKey(p => p.Id);
                entity.Property(p => p.Name).IsRequired().HasMaxLength(50);
                entity.Property(p => p.CastleLevel).IsRequired().HasDefaultValue(1);
                entity.Property(p => p.Power).IsRequired().HasDefaultValue(0);
                entity.Property(p => p.KingdomId).IsRequired();
                entity.Property(p => p.CreatedAt).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(p => p.LastActiveAt).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(p => p.IsActive).IsRequired().HasDefaultValue(true);

                // String properties with max lengths
                entity.Property(p => p.Biography).HasMaxLength(500);

                // Relationships - Use navigation properties from your actual Player model
                entity.HasOne(p => p.Kingdom)
                      .WithMany(k => k.Players)
                      .HasForeignKey(p => p.KingdomId)
                      .HasConstraintName("FK_Players_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne(p => p.Alliance)
                      .WithMany(a => a.Members)
                      .HasForeignKey(p => p.AllianceId)
                      .HasConstraintName("FK_Players_Alliances_AllianceId")
                      .OnDelete(DeleteBehavior.SetNull);

                // Indexes
                entity.HasIndex(p => p.KingdomId).HasDatabaseName("IX_Players_KingdomId");
                entity.HasIndex(p => p.AllianceId).HasDatabaseName("IX_Players_AllianceId");
                entity.HasIndex(p => p.Name).HasDatabaseName("IX_Players_Name");
                entity.HasIndex(p => p.Power).HasDatabaseName("IX_Players_Power");
            });
        }

        private void ConfigureAllianceEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Alliance>(entity =>
            {
                entity.HasKey(a => a.Id);
                entity.Property(a => a.Name).IsRequired().HasMaxLength(50);
                entity.Property(a => a.Tag).IsRequired().HasMaxLength(5);
                entity.Property(a => a.Level).IsRequired().HasDefaultValue(1);
                entity.Property(a => a.Power).IsRequired().HasDefaultValue(0);
                entity.Property(a => a.KingdomId).IsRequired();
                entity.Property(a => a.CreatedAt).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(a => a.IsActive).IsRequired().HasDefaultValue(true);
                entity.Property(a => a.MaxMembers).IsRequired().HasDefaultValue(50);

                // String properties with max lengths
                entity.Property(a => a.Description).HasMaxLength(1000);
                entity.Property(a => a.WelcomeMessage).HasMaxLength(200);

                // Relationships - Use navigation properties from your actual Alliance model
                entity.HasOne(a => a.Kingdom)
                      .WithMany(k => k.Alliances)
                      .HasForeignKey(a => a.KingdomId)
                      .HasConstraintName("FK_Alliances_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne(a => a.Leader)
                      .WithOne()
                      .HasForeignKey<Alliance>(a => a.LeaderId)
                      .HasConstraintName("FK_Alliances_Players_LeaderId")
                      .OnDelete(DeleteBehavior.Restrict);

                // Indexes
                entity.HasIndex(a => a.KingdomId).HasDatabaseName("IX_Alliances_KingdomId");
                entity.HasIndex(a => a.Name).HasDatabaseName("IX_Alliances_Name");
                entity.HasIndex(a => a.Tag).HasDatabaseName("IX_Alliances_Tag");
                entity.HasIndex(a => a.Power).HasDatabaseName("IX_Alliances_Power");
            });
        }

        private void ConfigureKingdomEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Kingdom>(entity =>
            {
                entity.HasKey(k => k.Id);
                entity.Property(k => k.Name).IsRequired().HasMaxLength(50);
                entity.Property(k => k.Number).IsRequired();
                entity.Property(k => k.CreatedAt).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(k => k.IsActive).IsRequired().HasDefaultValue(true);
                entity.Property(k => k.MaxPopulation).IsRequired().HasDefaultValue(1500);
                entity.Property(k => k.CurrentPopulation).IsRequired().HasDefaultValue(0);

                // String properties with max lengths
                entity.Property(k => k.Description).HasMaxLength(500);
                entity.Property(k => k.WelcomeMessage).HasMaxLength(200);

                // Numeric properties with proper types
                entity.Property(k => k.TaxRate).HasColumnType("decimal(5,4)");
                entity.Property(k => k.TotalTaxCollected).HasColumnType("decimal(18,2)");

                // Indexes
                entity.HasIndex(k => k.Number).IsUnique().HasDatabaseName("IX_Kingdoms_Number");
                entity.HasIndex(k => k.Name).HasDatabaseName("IX_Kingdoms_Name");
            });
        }

        private void ConfigureCombatLogEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CombatLog>(entity =>
            {
                entity.HasKey(c => c.Id);
                entity.Property(c => c.AttackerPlayerId).IsRequired();
                entity.Property(c => c.DefenderPlayerId).IsRequired();
                entity.Property(c => c.KingdomId).IsRequired();
                entity.Property(c => c.CombatType).IsRequired();
                entity.Property(c => c.Result).IsRequired();
                entity.Property(c => c.Timestamp).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");

                // String properties with max lengths
                entity.Property(c => c.AttackerDragonSkillsUsed).HasMaxLength(100);
                entity.Property(c => c.DefenderDragonSkillsUsed).HasMaxLength(100);
                entity.Property(c => c.DetailedBattleReport).HasMaxLength(4000);
                entity.Property(c => c.BattleNotes).HasMaxLength(1000);

                // CRITICAL FIX: Map navigation properties to specific foreign keys
                // This prevents EF from creating shadow properties

                // NO navigation properties configured here to avoid conflicts
                // Use foreign key relationships only since your CombatLog model 
                // doesn't have navigation properties defined

                // Simple foreign key relationships without navigation
                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(c => c.AttackerPlayerId)
                      .HasConstraintName("FK_CombatLogs_Players_AttackerPlayerId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(c => c.DefenderPlayerId)
                      .HasConstraintName("FK_CombatLogs_Players_DefenderPlayerId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(c => c.InterceptorPlayerId)
                      .HasConstraintName("FK_CombatLogs_Players_InterceptorPlayerId")
                      .OnDelete(DeleteBehavior.Restrict)
                      .IsRequired(false);

                entity.HasOne<Kingdom>()
                      .WithMany()
                      .HasForeignKey(c => c.KingdomId)
                      .HasConstraintName("FK_CombatLogs_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Alliance>()
                      .WithMany()
                      .HasForeignKey(c => c.AttackerAllianceId)
                      .HasConstraintName("FK_CombatLogs_Alliances_AttackerAllianceId")
                      .OnDelete(DeleteBehavior.SetNull)
                      .IsRequired(false);

                entity.HasOne<Alliance>()
                      .WithMany()
                      .HasForeignKey(c => c.DefenderAllianceId)
                      .HasConstraintName("FK_CombatLogs_Alliances_DefenderAllianceId")
                      .OnDelete(DeleteBehavior.SetNull)
                      .IsRequired(false);

                // Indexes with explicit names
                entity.HasIndex(c => c.KingdomId).HasDatabaseName("IX_CombatLogs_KingdomId");
                entity.HasIndex(c => c.AttackerPlayerId).HasDatabaseName("IX_CombatLogs_AttackerPlayerId");
                entity.HasIndex(c => c.DefenderPlayerId).HasDatabaseName("IX_CombatLogs_DefenderPlayerId");
                entity.HasIndex(c => c.InterceptorPlayerId).HasDatabaseName("IX_CombatLogs_InterceptorPlayerId");
                entity.HasIndex(c => c.AttackerAllianceId).HasDatabaseName("IX_CombatLogs_AttackerAllianceId");
                entity.HasIndex(c => c.DefenderAllianceId).HasDatabaseName("IX_CombatLogs_DefenderAllianceId");
                entity.HasIndex(c => c.Timestamp).HasDatabaseName("IX_CombatLogs_Timestamp");
            });
        }

        private void ConfigurePurchaseLogEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<PurchaseLog>(entity =>
            {
                entity.HasKey(p => p.Id);
                entity.Property(p => p.PlayerId).IsRequired();
                entity.Property(p => p.KingdomId).IsRequired();
                entity.Property(p => p.ProductId).IsRequired().HasMaxLength(100);
                entity.Property(p => p.ProductName).IsRequired().HasMaxLength(200);
                entity.Property(p => p.Amount).IsRequired().HasColumnType("decimal(18,2)");
                entity.Property(p => p.Currency).IsRequired().HasMaxLength(10);
                entity.Property(p => p.PurchaseDate).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(p => p.TransactionId).IsRequired().HasMaxLength(200);
                entity.Property(p => p.PlatformTransactionId).IsRequired().HasMaxLength(200);

                // String properties with max lengths
                entity.Property(p => p.PlatformReceiptData).HasMaxLength(500);
                entity.Property(p => p.Platform).IsRequired().HasMaxLength(20);
                entity.Property(p => p.PlatformUserId).HasMaxLength(100);
                entity.Property(p => p.DeviceInfo).HasMaxLength(50);
                entity.Property(p => p.IPAddress).HasMaxLength(100);
                entity.Property(p => p.UserAgent).HasMaxLength(200);
                entity.Property(p => p.ChargebackReason).HasMaxLength(500);
                entity.Property(p => p.FraudReason).HasMaxLength(1000);
                entity.Property(p => p.RefundReason).HasMaxLength(500);
                entity.Property(p => p.Notes).HasMaxLength(1000);
                entity.Property(p => p.VipMilestoneReached).HasMaxLength(100);
                entity.Property(p => p.DragonType).HasMaxLength(50);
                entity.Property(p => p.DragonSkillsUnlocked).HasMaxLength(200);
                entity.Property(p => p.StealthType).HasMaxLength(50);
                entity.Property(p => p.SkillAlternativeDescription).HasMaxLength(200);
                entity.Property(p => p.BundleContents).HasMaxLength(1000);
                entity.Property(p => p.DetailedPurchaseData).HasMaxLength(2000);
                entity.Property(p => p.PurchaseNotes).HasMaxLength(1000);
                entity.Property(p => p.CampaignId).HasMaxLength(100);
                entity.Property(p => p.PromotionCode).HasMaxLength(100);
                entity.Property(p => p.FraudNotes).HasMaxLength(500);

                // Decimal properties
                entity.Property(p => p.PlatformFee).HasColumnType("decimal(18,2)");
                entity.Property(p => p.NetRevenue).HasColumnType("decimal(18,2)");
                entity.Property(p => p.TaxAmount).HasColumnType("decimal(18,2)");
                entity.Property(p => p.RefundAmount).HasColumnType("decimal(18,2)");
                entity.Property(p => p.ChargebackPenalty).HasColumnType("decimal(18,2)");
                entity.Property(p => p.ChargebackFee).HasColumnType("decimal(18,2)");
                entity.Property(p => p.MonthlySpendingBefore).HasColumnType("decimal(18,2)");
                entity.Property(p => p.MonthlySpendingAfter).HasColumnType("decimal(18,2)");
                entity.Property(p => p.LifetimeSpendingBefore).HasColumnType("decimal(18,2)");
                entity.Property(p => p.LifetimeSpendingAfter).HasColumnType("decimal(18,2)");
                entity.Property(p => p.SpendingInLast24Hours).HasColumnType("decimal(18,2)");
                entity.Property(p => p.TimeInvestmentHours).HasColumnType("decimal(8,2)");

                // Simple foreign key relationships without navigation
                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(p => p.PlayerId)
                      .HasConstraintName("FK_PurchaseLogs_Players_PlayerId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Kingdom>()
                      .WithMany()
                      .HasForeignKey(p => p.KingdomId)
                      .HasConstraintName("FK_PurchaseLogs_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Alliance>()
                      .WithMany()
                      .HasForeignKey(p => p.AllianceId)
                      .HasConstraintName("FK_PurchaseLogs_Alliances_AllianceId")
                      .OnDelete(DeleteBehavior.SetNull)
                      .IsRequired(false);

                // Indexes with explicit names
                entity.HasIndex(p => p.PlayerId).HasDatabaseName("IX_PurchaseLogs_PlayerId");
                entity.HasIndex(p => p.KingdomId).HasDatabaseName("IX_PurchaseLogs_KingdomId");
                entity.HasIndex(p => p.AllianceId).HasDatabaseName("IX_PurchaseLogs_AllianceId");
                entity.HasIndex(p => p.PurchaseDate).HasDatabaseName("IX_PurchaseLogs_PurchaseDate");
                entity.HasIndex(p => p.TransactionId).HasDatabaseName("IX_PurchaseLogs_TransactionId");
            });
        }

        private void ConfigureAdminPlayerMessageEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AdminPlayerMessage>(entity =>
            {
                entity.HasKey(m => m.Id);
                entity.Property(m => m.PlayerId).IsRequired();
                entity.Property(m => m.AdminUserId).IsRequired().HasMaxLength(450);
                entity.Property(m => m.Message).IsRequired();
                entity.Property(m => m.MessageType).IsRequired().HasMaxLength(50);
                entity.Property(m => m.SentAt).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(m => m.IsRead).IsRequired().HasDefaultValue(false);
                entity.Property(m => m.KingdomId).IsRequired();

                // Optional properties
                entity.Property(m => m.PlayerResponse);
                entity.Property(m => m.AdminNotes).HasMaxLength(1000);

                // Foreign key relationships
                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(m => m.PlayerId)
                      .HasConstraintName("FK_AdminPlayerMessages_Players_PlayerId")
                      .OnDelete(DeleteBehavior.Cascade);

                entity.HasOne<ApplicationUser>()
                      .WithMany()
                      .HasForeignKey(m => m.AdminUserId)
                      .HasConstraintName("FK_AdminPlayerMessages_Users_AdminUserId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Kingdom>()
                      .WithMany()
                      .HasForeignKey(m => m.KingdomId)
                      .HasConstraintName("FK_AdminPlayerMessages_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                // Indexes
                entity.HasIndex(m => m.PlayerId).HasDatabaseName("IX_AdminPlayerMessages_PlayerId");
                entity.HasIndex(m => m.AdminUserId).HasDatabaseName("IX_AdminPlayerMessages_AdminUserId");
                entity.HasIndex(m => m.KingdomId).HasDatabaseName("IX_AdminPlayerMessages_KingdomId");
                entity.HasIndex(m => m.SentAt).HasDatabaseName("IX_AdminPlayerMessages_SentAt");
                entity.HasIndex(m => new { m.PlayerId, m.SentAt }).HasDatabaseName("IX_AdminPlayerMessages_Player_Time");
            });
        }

        private void ConfigureAdminActionAuditEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AdminActionAudit>(entity =>
            {
                entity.HasKey(a => a.Id);
                entity.Property(a => a.AdminUserId).IsRequired().HasMaxLength(450);
                entity.Property(a => a.ActionType).IsRequired().HasMaxLength(100);
                entity.Property(a => a.ActionDetails);
                entity.Property(a => a.Reason).HasMaxLength(500);
                entity.Property(a => a.ActionData);
                entity.Property(a => a.Timestamp).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(a => a.KingdomId).IsRequired();
                entity.Property(a => a.IpAddress).HasMaxLength(45);

                // Optional player reference
                entity.Property(a => a.PlayerId).IsRequired(false);

                // Foreign key relationships
                entity.HasOne<Player>()
                      .WithMany()
                      .HasForeignKey(a => a.PlayerId)
                      .HasConstraintName("FK_AdminActionAudit_Players_PlayerId")
                      .OnDelete(DeleteBehavior.SetNull)
                      .IsRequired(false);

                entity.HasOne<ApplicationUser>()
                      .WithMany()
                      .HasForeignKey(a => a.AdminUserId)
                      .HasConstraintName("FK_AdminActionAudit_Users_AdminUserId")
                      .OnDelete(DeleteBehavior.Restrict);

                entity.HasOne<Kingdom>()
                      .WithMany()
                      .HasForeignKey(a => a.KingdomId)
                      .HasConstraintName("FK_AdminActionAudit_Kingdoms_KingdomId")
                      .OnDelete(DeleteBehavior.Restrict);

                // Indexes
                entity.HasIndex(a => a.AdminUserId).HasDatabaseName("IX_AdminActionAudit_AdminUserId");
                entity.HasIndex(a => a.PlayerId).HasDatabaseName("IX_AdminActionAudit_PlayerId");
                entity.HasIndex(a => a.KingdomId).HasDatabaseName("IX_AdminActionAudit_KingdomId");
                entity.HasIndex(a => a.Timestamp).HasDatabaseName("IX_AdminActionAudit_Timestamp");
                entity.HasIndex(a => a.ActionType).HasDatabaseName("IX_AdminActionAudit_ActionType");
            });
        }

        private void ConfigureAdminSessionEntity(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AdminSession>(entity =>
            {
                entity.HasKey(s => s.Id);
                entity.Property(s => s.AdminUserId).IsRequired().HasMaxLength(450);
                entity.Property(s => s.LoginTime).IsRequired().HasDefaultValueSql("CURRENT_TIMESTAMP");
                entity.Property(s => s.IpAddress).HasMaxLength(45);
                entity.Property(s => s.UserAgent).HasMaxLength(500);
                entity.Property(s => s.SessionData);

                // Optional logout time
                entity.Property(s => s.LogoutTime).IsRequired(false);

                // Foreign key relationships
                entity.HasOne<ApplicationUser>()
                      .WithMany()
                      .HasForeignKey(s => s.AdminUserId)
                      .HasConstraintName("FK_AdminSessions_Users_AdminUserId")
                      .OnDelete(DeleteBehavior.Cascade);

                // Indexes
                entity.HasIndex(s => s.AdminUserId).HasDatabaseName("IX_AdminSessions_AdminUserId");
                entity.HasIndex(s => s.LoginTime).HasDatabaseName("IX_AdminSessions_LoginTime");
                entity.HasIndex(s => new { s.AdminUserId, s.LoginTime }).HasDatabaseName("IX_AdminSessions_User_LoginTime");
            });
        }

        private void ConfigureAllianceRelatedEntities(ModelBuilder modelBuilder)
        {
            // Configure AllianceInvitation entity - EXPLICIT NAVIGATION MAPPING
            modelBuilder.Entity<AllianceInvitation>(entity =>
            {
                entity.HasKey(ai => ai.Id);
                entity.Property(ai => ai.AllianceId).IsRequired();
                entity.Property(ai => ai.PlayerId).IsRequired();
                entity.Property(ai => ai.InvitedById).IsRequired();
                entity.Property(ai => ai.CreatedAt).IsRequired();
                entity.Property(ai => ai.ExpiresAt).IsRequired();

                // CRITICAL: Map navigation properties to specific foreign keys
                // This is what fixes the shadow property warnings

                // Alliance navigation
                entity.HasOne(ai => ai.Alliance)
                      .WithMany(a => a.PendingInvitations)
                      .HasForeignKey(ai => ai.AllianceId)
                      .HasConstraintName("FK_AllianceInvitation_Alliances_AllianceId")
                      .OnDelete(DeleteBehavior.Cascade);

                // Player being invited navigation
                entity.HasOne(ai => ai.Player)
                      .WithMany()
                      .HasForeignKey(ai => ai.PlayerId)
                      .HasConstraintName("FK_AllianceInvitation_Players_PlayerId")
                      .OnDelete(DeleteBehavior.Cascade);

                // Player who sent invite navigation
                entity.HasOne(ai => ai.InvitedBy)
                      .WithMany()
                      .HasForeignKey(ai => ai.InvitedById)
                      .HasConstraintName("FK_AllianceInvitation_Players_InvitedById")
                      .OnDelete(DeleteBehavior.Cascade);

                // Indexes
                entity.HasIndex(ai => ai.AllianceId).HasDatabaseName("IX_AllianceInvitation_AllianceId");
                entity.HasIndex(ai => ai.PlayerId).HasDatabaseName("IX_AllianceInvitation_PlayerId");
                entity.HasIndex(ai => ai.InvitedById).HasDatabaseName("IX_AllianceInvitation_InvitedById");
            });

            // Configure AllianceRole entity - EXPLICIT NAVIGATION MAPPING
            modelBuilder.Entity<AllianceRole>(entity =>
            {
                entity.HasKey(ar => ar.Id);
                entity.Property(ar => ar.AllianceId).IsRequired();
                entity.Property(ar => ar.PlayerId).IsRequired();
                entity.Property(ar => ar.Rank).IsRequired();
                entity.Property(ar => ar.AssignedAt).IsRequired();
                entity.Property(ar => ar.AssignedById).IsRequired();

                // CRITICAL: Map navigation properties to specific foreign keys
                // This is what fixes the shadow property warnings

                // Alliance navigation
                entity.HasOne(ar => ar.Alliance)
                      .WithMany(a => a.Roles)
                      .HasForeignKey(ar => ar.AllianceId)
                      .HasConstraintName("FK_AllianceRole_Alliances_AllianceId")
                      .OnDelete(DeleteBehavior.Cascade);

                // Player with role navigation
                entity.HasOne(ar => ar.Player)
                      .WithMany()
                      .HasForeignKey(ar => ar.PlayerId)
                      .HasConstraintName("FK_AllianceRole_Players_PlayerId")
                      .OnDelete(DeleteBehavior.Cascade);

                // Player who assigned role navigation  
                entity.HasOne(ar => ar.AssignedBy)
                      .WithMany()
                      .HasForeignKey(ar => ar.AssignedById)
                      .HasConstraintName("FK_AllianceRole_Players_AssignedById")
                      .OnDelete(DeleteBehavior.Cascade);

                // Indexes
                entity.HasIndex(ar => ar.AllianceId).HasDatabaseName("IX_AllianceRole_AllianceId");
                entity.HasIndex(ar => ar.PlayerId).HasDatabaseName("IX_AllianceRole_PlayerId");
                entity.HasIndex(ar => ar.AssignedById).HasDatabaseName("IX_AllianceRole_AssignedById");
            });
        }

        private void ApplyKingdomScopedFilters(ModelBuilder modelBuilder)
        {
            // Apply global query filters to ensure kingdom-scoped data access
            // Players filter
            modelBuilder.Entity<Player>()
                .HasQueryFilter(p => _currentKingdomId == null || p.KingdomId == _currentKingdomId);

            // Alliances filter
            modelBuilder.Entity<Alliance>()
                .HasQueryFilter(a => _currentKingdomId == null || a.KingdomId == _currentKingdomId);

            // Combat logs filter
            modelBuilder.Entity<CombatLog>()
                .HasQueryFilter(c => _currentKingdomId == null || c.KingdomId == _currentKingdomId);

            // Purchase logs filter
            modelBuilder.Entity<PurchaseLog>()
                .HasQueryFilter(p => _currentKingdomId == null || p.KingdomId == _currentKingdomId);

            // Admin messages filter (kingdom-scoped)
            modelBuilder.Entity<AdminPlayerMessage>()
                .HasQueryFilter(m => _currentKingdomId == null || m.KingdomId == _currentKingdomId);

            // Admin actions filter (kingdom-scoped)
            modelBuilder.Entity<AdminActionAudit>()
                .HasQueryFilter(a => _currentKingdomId == null || a.KingdomId == _currentKingdomId);

            // Admin sessions are NOT kingdom-scoped - admins can access multiple kingdoms
            // Apply matching filters for related entities to resolve Alliance warning
            // Since AllianceInvitation and AllianceRole relate to Alliance, they inherit kingdom scoping
            // through their alliance relationship - no additional filters needed
        }

        private void ConfigureIndexes(ModelBuilder modelBuilder)
        {
            // Composite indexes for common query patterns
            modelBuilder.Entity<Player>()
                .HasIndex(p => new { p.KingdomId, p.Power })
                .HasDatabaseName("IX_Players_Kingdom_Power");

            modelBuilder.Entity<Player>()
                .HasIndex(p => new { p.KingdomId, p.AllianceId })
                .HasDatabaseName("IX_Players_Kingdom_Alliance");

            modelBuilder.Entity<Alliance>()
                .HasIndex(a => new { a.KingdomId, a.Power })
                .HasDatabaseName("IX_Alliances_Kingdom_Power");

            modelBuilder.Entity<CombatLog>()
                .HasIndex(c => new { c.KingdomId, c.Timestamp })
                .HasDatabaseName("IX_CombatLogs_Kingdom_Time");

            modelBuilder.Entity<PurchaseLog>()
                .HasIndex(p => new { p.KingdomId, p.PurchaseDate })
                .HasDatabaseName("IX_PurchaseLogs_Kingdom_Time");

            // Admin-specific indexes
            modelBuilder.Entity<AdminPlayerMessage>()
                .HasIndex(m => new { m.KingdomId, m.SentAt })
                .HasDatabaseName("IX_AdminPlayerMessages_Kingdom_Time");

            modelBuilder.Entity<AdminActionAudit>()
                .HasIndex(a => new { a.KingdomId, a.Timestamp })
                .HasDatabaseName("IX_AdminActionAudit_Kingdom_Time");

            modelBuilder.Entity<AdminActionAudit>()
                .HasIndex(a => new { a.AdminUserId, a.Timestamp })
                .HasDatabaseName("IX_AdminActionAudit_Admin_Time");
        }

        public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
        {
            try
            {
                var result = await base.SaveChangesAsync(cancellationToken);

                if (result > 0)
                {
                    _logger.LogInformation("Successfully saved {ChangeCount} changes to database", result);
                }

                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to save changes to database");
                throw;
            }
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                _logger.LogWarning("DbContext is not configured - this should only happen during design-time operations");
            }
        }
    }

    // Custom ApplicationUser for Identity integration
    public class ApplicationUser : IdentityUser<int>
    {
        public int? PlayerId { get; set; }
        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
        public DateTime LastLoginAt { get; set; } = DateTime.UtcNow;
    }
}