﻿using Microsoft.EntityFrameworkCore;
using WarehouseAppSHIPS.Models;

namespace WarehouseAppSHIPS.Data
{
    /// <summary>
    /// Entity Framework Core database context for the Warehouse Management System.
    /// Configures entity mappings, relationships, and constraints.
    /// </summary>
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }

        // ==================== DbSet Properties ====================

        /// <summary>Application users with authentication and roles.</summary>
        public DbSet<AppUser> AppUsers { get; set; }

        /// <summary>Materials/inventory items in the warehouse.</summary>
        public DbSet<Material> Materials { get; set; }

        /// <summary>Inbound goods receipt transactions.</summary>
        public DbSet<InboundTransaction> InboundTransactions { get; set; }

        /// <summary>Material requests with approval workflow.</summary>
        public DbSet<MaterialRequest> MaterialRequests { get; set; }

        /// <summary>Stock opname (physical inventory audit) records.</summary>
        public DbSet<StockOpname> StockOpnames { get; set; }

        /// <summary>Legacy Barang table for backward compatibility.</summary>
        public DbSet<Barang> DataBarang { get; set; }

        // ==================== Model Configuration ====================

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

            // ========== AppUser Configuration ==========
            modelBuilder.Entity<AppUser>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.Username)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.PasswordHash)
                    .IsRequired();

                entity.Property(e => e.FullName)
                    .IsRequired()
                    .HasMaxLength(100);

                entity.Property(e => e.Role)
                    .IsRequired()
                    .HasConversion<int>();

                // Unique constraint on Username
                entity.HasIndex(e => e.Username)
                    .IsUnique()
                    .HasDatabaseName("IX_AppUser_Username_Unique");

                // Navigation: InboundTransactions
                entity.HasMany(e => e.InboundTransactions)
                    .WithOne(t => t.User)
                    .HasForeignKey(t => t.UserId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_InboundTransaction_AppUser_UserId");

                // Navigation: RequestsAsRequester (Material Requests created by user)
                entity.HasMany(e => e.RequestsAsRequester)
                    .WithOne(mr => mr.Requester)
                    .HasForeignKey(mr => mr.RequesterId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_AppUser_RequesterId");

                // Navigation: RequestsAsApprover (Material Requests approved by user)
                entity.HasMany(e => e.RequestsAsApprover)
                    .WithOne(mr => mr.Approver)
                    .HasForeignKey(mr => mr.ApproverId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_AppUser_ApproverId");

                // Navigation: StockOpnames
                entity.HasMany(e => e.StockOpnames)
                    .WithOne(so => so.Auditor)
                    .HasForeignKey(so => so.AuditorId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_StockOpname_AppUser_AuditorId");
            });

            // ========== Material Configuration ==========
            modelBuilder.Entity<Material>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.AssetCode)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(100);

                entity.Property(e => e.Category)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.Property(e => e.Stock)
                    .HasDefaultValue(0);

                entity.Property(e => e.MinStock)
                    .HasDefaultValue(0);

                entity.Property(e => e.LeadTime)
                    .HasDefaultValue(0);

                // Unique constraint on AssetCode
                entity.HasIndex(e => e.AssetCode)
                    .IsUnique()
                    .HasDatabaseName("IX_Material_AssetCode_Unique");

                // Navigation: InboundTransactions
                entity.HasMany(e => e.InboundTransactions)
                    .WithOne(t => t.Material)
                    .HasForeignKey(t => t.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_InboundTransaction_Material_MaterialId");

                // Navigation: MaterialRequests
                entity.HasMany(e => e.MaterialRequests)
                    .WithOne(mr => mr.Material)
                    .HasForeignKey(mr => mr.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_Material_MaterialId");

                // Navigation: StockOpnames
                entity.HasMany(e => e.StockOpnames)
                    .WithOne(so => so.Material)
                    .HasForeignKey(so => so.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_StockOpname_Material_MaterialId");
            });

            // ========== InboundTransaction Configuration ==========
            modelBuilder.Entity<InboundTransaction>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.TransactionDate)
                    .IsRequired()
                    .HasDefaultValueSql("GETUTCDATE()");

                entity.Property(e => e.MaterialId)
                    .IsRequired();

                entity.Property(e => e.Qty)
                    .IsRequired();

                entity.Property(e => e.UserId)
                    .IsRequired();

                // Foreign Key: Material
                entity.HasOne(e => e.Material)
                    .WithMany(m => m.InboundTransactions)
                    .HasForeignKey(e => e.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_InboundTransaction_Material_MaterialId");

                // Foreign Key: User (AppUser)
                entity.HasOne(e => e.User)
                    .WithMany(u => u.InboundTransactions)
                    .HasForeignKey(e => e.UserId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_InboundTransaction_AppUser_UserId");

                // Composite Index for MaterialId and TransactionDate
                entity.HasIndex(e => new { e.MaterialId, e.TransactionDate })
                    .HasDatabaseName("IX_InboundTransaction_MaterialId_TransactionDate");
            });

            // ========== MaterialRequest Configuration ==========
            modelBuilder.Entity<MaterialRequest>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.RequestDate)
                    .IsRequired()
                    .HasDefaultValueSql("GETUTCDATE()");

                entity.Property(e => e.RequesterId)
                    .IsRequired();

                entity.Property(e => e.MaterialId)
                    .IsRequired();

                entity.Property(e => e.Qty)
                    .IsRequired();

                entity.Property(e => e.Purpose)
                    .IsRequired()
                    .HasMaxLength(255);

                entity.Property(e => e.Status)
                    .IsRequired()
                    .HasConversion<int>()
                    .HasDefaultValue(MaterialRequestStatus.Pending);

                entity.Property(e => e.ApproverId)
                    .IsRequired(false); // Nullable

                entity.Property(e => e.ApprovalNote)
                    .HasMaxLength(255)
                    .IsRequired(false); // Nullable

                // Foreign Key: Material
                entity.HasOne(e => e.Material)
                    .WithMany(m => m.MaterialRequests)
                    .HasForeignKey(e => e.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_Material_MaterialId");

                // Foreign Key: Requester (AppUser)
                // CRITICAL: Using DeleteBehavior.Restrict to prevent cascading delete issues
                entity.HasOne(e => e.Requester)
                    .WithMany(u => u.RequestsAsRequester)
                    .HasForeignKey(e => e.RequesterId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_AppUser_RequesterId");

                // Foreign Key: Approver (AppUser)
                // CRITICAL: Using DeleteBehavior.Restrict to prevent multiple cascading delete paths
                entity.HasOne(e => e.Approver)
                    .WithMany(u => u.RequestsAsApprover)
                    .HasForeignKey(e => e.ApproverId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_MaterialRequest_AppUser_ApproverId");

                // Composite Index for RequesterId and Status
                entity.HasIndex(e => new { e.RequesterId, e.Status })
                    .HasDatabaseName("IX_MaterialRequest_RequesterId_Status");

                // Index for ApproverId and Status
                entity.HasIndex(e => new { e.ApproverId, e.Status })
                    .HasDatabaseName("IX_MaterialRequest_ApproverId_Status");
            });

            // ========== StockOpname Configuration ==========
            modelBuilder.Entity<StockOpname>(entity =>
            {
                entity.HasKey(e => e.Id);

                entity.Property(e => e.AuditDate)
                    .IsRequired()
                    .HasDefaultValueSql("GETUTCDATE()");

                entity.Property(e => e.MaterialId)
                    .IsRequired();

                entity.Property(e => e.PhysicalQty)
                    .IsRequired();

                entity.Property(e => e.SystemQty)
                    .IsRequired();

                entity.Property(e => e.Difference)
                    .IsRequired();
                    // Difference will be calculated as PhysicalQty - SystemQty

                entity.Property(e => e.AuditorId)
                    .IsRequired();

                // Foreign Key: Material
                entity.HasOne(e => e.Material)
                    .WithMany(m => m.StockOpnames)
                    .HasForeignKey(e => e.MaterialId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_StockOpname_Material_MaterialId");

                // Foreign Key: Auditor (AppUser)
                entity.HasOne(e => e.Auditor)
                    .WithMany(u => u.StockOpnames)
                    .HasForeignKey(e => e.AuditorId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_StockOpname_AppUser_AuditorId");

                // Composite Index for MaterialId and AuditDate
                entity.HasIndex(e => new { e.MaterialId, e.AuditDate })
                    .HasDatabaseName("IX_StockOpname_MaterialId_AuditDate");
            });

            // ========== Barang Configuration (Legacy) ==========
            modelBuilder.Entity<Barang>(entity =>
            {
                entity.HasKey(e => e.Id);
                entity.ToTable("DataBarang");

                entity.Property(e => e.NamaBarang)
                    .HasMaxLength(100);

                entity.Property(e => e.KodeAsset)
                    .HasMaxLength(50);

                entity.Property(e => e.Kategori)
                    .HasMaxLength(50);
            });
        }
    }
}
