using Microsoft.EntityFrameworkCore;
using vendorApproverBackend.Entities;

namespace vendorApproverBackend.Data
{
    public class DataContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<FileModel> FileModels { get; set; }
        public DbSet<UserDevice> UserDevices { get; set; }
        public DbSet<Vendor> Vendors { get; set; }
        public DbSet<Product> Products { get; set; }
        public DbSet<PaymentDetail> PaymentDetail { get; set; }
        public DbSet<Certification> Certifications { get; set; }
        public DbSet<NotificationModel> NotificationModels { get; set; }
        public DbSet<AssessmentReport> AssessmentReports { get; set; }
        public DbSet<VisitRequest> VisitRequests { get; set; }
        public DbSet<WorkExperience> WorkExperiences { get; set; }
        public DbSet<Degree> Degrees { get; set; }
        public DbSet<EmployeeCertification> EmployeeCertifications { get; set; }
        public DbSet<CertificationPack> CertificationPacks { get; set; }
        public DbSet<PrequalificationRequest> PrequalificationRequests { get; set; }
        public DbSet<ApplicationCategory> ApplicationCategories { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<EmployeeApplication> EmployeeApplications { get; set; }
        public DbSet<RequestRenew> RequestRenews { get; set; }
        public DbSet<Signature> Signatures { get; set; }
        public DbSet<PerformanceLetter> PerformanceLetters { get; set; }
        public DbSet<VendorRequestLetter> VendorRequestLetters { get; set; }
        public DbSet<LetterProducts> LetterProducts { get; set; }
        public DbSet<EvaluationData> EvaluationDatas { get; set; }

        // NEW DBSETS
        public DbSet<UpdatedVendorData> UpdatedVendorDatas { get; set; }
        public DbSet<RenewalPart2Evaluation> RenewalPart2Evaluations { get; set; }
        public DbSet<RenewalPart3Decision> RenewalPart3Decisions { get; set; }
        public DbSet<RenewalFinalDecision> RenewalFinalDecisions { get; set; }
        public DbSet<RenewalClarification> RenewalClarifications { get; set; }
        public DbSet<VendorResponse> VendorResponses { get; set; }
        public DbSet<RenewalPayment> RenewalPayments { get; set; }

        public DbSet<RequestRenewClarification> RequestRenewClarifications { get; set; }
        public DataContext(DbContextOptions<DataContext> options) : base(options) { }
        public DbSet<PqRequestClarification> PqRequestClarifications { get; set; }

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

            // --- EXISTING USER CONFIG ---
            modelBuilder.Entity<User>(entity =>
            {
                entity.ToTable("users_table");
                entity.HasIndex(u => u.Username).IsUnique();
                entity.HasIndex(u => u.Email).IsUnique();
                entity.HasIndex(u => u.PhoneNumber).IsUnique();
                entity.HasOne(u => u.Image).WithOne().HasForeignKey<User>("users_profile_media").OnDelete(DeleteBehavior.Cascade);
                entity.HasOne(u => u.Device).WithOne().HasForeignKey<User>("user_device").OnDelete(DeleteBehavior.Cascade);
                entity.HasOne(u => u.Vendor).WithOne(v => v.User).HasForeignKey<Vendor>(v => v.UserId);
                entity.HasMany(u => u.InspectedPrequalificationRequests).WithMany(r => r.Inspectors).UsingEntity(j => j.ToTable("PqRequestInspector"));
                entity.HasMany(u => u.Degrees).WithMany().UsingEntity(j => j.ToTable("UserDegrees"));
                entity.HasMany(u => u.WorkExperiences).WithMany().UsingEntity(j => j.ToTable("UserWorkExperiences"));
                entity.HasMany(u => u.Certifications).WithMany().UsingEntity(j => j.ToTable("UserCertifications"));
                entity.HasMany(u => u.Categories).WithMany().UsingEntity(j => j.ToTable("UserApplicationCategories"));
            });

            // --- EXISTING VENDOR CONFIG ---
            modelBuilder.Entity<Vendor>(entity =>
            {
                entity.HasMany(v => v.PrequalificationRequests).WithOne(r => r.Vendor).HasForeignKey(r => r.VendorId);
                entity.HasMany(v => v.VendorRequestLetters).WithOne(l => l.Vendor).HasForeignKey(l => l.VendorId);
            });

            // *** UPDATED VendorRequestLetter CONFIG ***
            modelBuilder.Entity<VendorRequestLetter>(entity =>
            {
                entity.ToTable("vendor_request_letters");
                entity.HasMany(l => l.LetterProducts).WithOne(p => p.VendorRequestLetter).HasForeignKey(p => p.VendorRequestLetterId).OnDelete(DeleteBehavior.Cascade);
                entity.Property(l => l.LocalOwners).HasColumnName("local_owners").HasColumnType("jsonb");
                entity.Property(l => l.BoardOfDirectors).HasColumnName("board_of_directors").HasColumnType("jsonb");
                entity.Property(l => l.ContactPersons).HasColumnName("contact_persons").HasColumnType("jsonb");
                entity.Property(l => l.AuthorizedSignatories).HasColumnName("authorized_signatories").HasColumnType("jsonb");
                entity.Property(l => l.AssociatedCompanies).HasColumnName("associated_companies").HasColumnType("jsonb");
                entity.Property(l => l.CertificatesAndLicenses).HasColumnName("certificates_and_licenses").HasColumnType("jsonb");
            });

            modelBuilder.Entity<LetterProducts>(entity =>
            {
                entity.ToTable("letter_products");
            });

            modelBuilder.Entity<NotificationModel>().Property(n => n.Status).HasConversion<int>();

            // --- EXISTING PQ REQUEST CONFIG ---
            modelBuilder.Entity<PrequalificationRequest>(req =>
            {
                req.HasOne(pr => pr.AssignedTo).WithMany(u => u.AssignedPrequalificationRequests).HasForeignKey(pr => pr.AssignedToUserId).IsRequired(false).OnDelete(DeleteBehavior.Restrict);
                req.OwnsMany(r => r.AdminObservations, a => { a.ToJson(); });
                req.OwnsOne(r => r.GeneralInformation, gi => { gi.OwnsMany(d => d.LocalOwners); gi.OwnsMany(d => d.BoardOfDirectors); gi.OwnsMany(d => d.ContactPersons); gi.OwnsMany(d => d.AuthorizedSignatories); gi.OwnsMany(d => d.AssociatedCompanies); gi.OwnsOne(d => d.CertificatesAndLicenses, cl => { cl.OwnsOne(c => c.TradeLicense); cl.OwnsOne(c => c.ChamberMembership); cl.OwnsMany(c => c.IndustrialLicenses); }); gi.ToJson(); });
                req.OwnsOne(r => r.VendorResources, vr => { vr.OwnsMany(d => d.StaffDetails); vr.OwnsMany(d => d.QualifiedStaff); vr.OwnsMany(d => d.TechnicalItems); vr.OwnsMany(d => d.Applications); vr.OwnsMany(d => d.SoftwareList); vr.OwnsMany(d => d.ProductionQualifiedStaff); vr.OwnsMany(d => d.QualityDocuments); vr.OwnsMany(d => d.InternationalStandards); vr.OwnsMany(d => d.QualityQualifiedStaff); vr.ToJson(); });
                req.OwnsOne(r => r.FinancialStanding, fs => { fs.OwnsMany(d => d.Bankers); fs.ToJson(); });
                req.OwnsOne(r => r.ExperienceAndAfterSales, eas => { eas.OwnsMany(d => d.ClientReferences); eas.HasOne(e => e.WarrantyCertificateFile).WithOne().HasForeignKey<ExperienceAndAfterSales>(e => e.WarrantyCertificateFileId).IsRequired(false).OnDelete(DeleteBehavior.Restrict); });
                req.OwnsOne(r => r.QualityHSERisk, qhr => { qhr.OwnsMany(d => d.Certificates); qhr.OwnsMany(d => d.QaSummary); qhr.ToJson(); });
                req.OwnsOne(r => r.TypeTest, tt => { tt.ToJson(); });
                req.OwnsOne(r => r.EquipmentGeneralArrangement, ega => { ega.ToJson(); });
                req.OwnsOne(r => r.Compliance, c => { c.ToJson(); });
                req.OwnsOne(r => r.Declaration, d => { d.ToJson(); });
                req.HasOne(p => p.VisitRequest).WithOne().HasForeignKey<PrequalificationRequest>("VisitRequestId");
            });

            modelBuilder.Entity<PrequalificationRequest>().HasOne(p => p.PaymentDetail).WithOne(d => d.PrequalificationRequest).HasForeignKey<PrequalificationRequest>(p => p.PaymentDetailId).IsRequired(false).OnDelete(DeleteBehavior.SetNull);
            modelBuilder.Entity<EmployeeApplication>().HasOne(a => a.User).WithMany(u => u.EmployeeApplications).HasForeignKey(a => a.UserId);

            // --- EXISTING ASSESSMENT REPORT CONFIG ---
            modelBuilder.Entity<AssessmentReport>(report =>
            {
                report.HasOne(r => r.PrequalificationRequest).WithOne(pr => pr.AssessmentReport).HasForeignKey<AssessmentReport>(r => r.PrequalificationRequestId).IsRequired();
                report.HasOne(r => r.CreatedBy).WithMany(u => u.CreatedAssessmentReports).HasForeignKey(r => r.CreatedById).IsRequired().OnDelete(DeleteBehavior.Restrict);
                report.OwnsMany(r => r.DocumentationReview, dr => { dr.ToJson(); });
                report.OwnsMany(r => r.TechnicalFactoryAuditVisit, dr => { dr.ToJson(); });
                report.OwnsMany(r => r.AuditVisit, av => { av.ToJson(); });
                report.OwnsOne(r => r.FactoryAuditOutcome, a => { a.ToJson(); });
            });

            // --- EXISTING CERTIFICATION / RENEW CONFIG ---
            modelBuilder.Entity<Certification>().HasMany(c => c.RequestRenews).WithOne().HasForeignKey(r => r.CertificationId).OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<RequestRenew>(entity =>
            {
                entity.HasOne(r => r.ExpiredProductCertificateFile).WithOne().HasForeignKey<RequestRenew>(r => r.ExpiredProductCertificateFileId);
                entity.HasOne(r => r.ReferenceOrdersFile).WithOne().HasForeignKey<RequestRenew>(r => r.ReferenceOrdersFileId);
                entity.HasOne(r => r.ApprovedDesignFile).WithOne().HasForeignKey<RequestRenew>(r => r.ApprovedDesignFileId);
                entity.HasOne(r => r.UpdateTDSFile).WithOne().HasForeignKey<RequestRenew>(r => r.UpdateTDSFileId);
                entity.HasOne(r => r.Signature).WithOne().HasForeignKey<RequestRenew>(r => r.SignatureId);
                entity.HasMany(r => r.PerformanceLetters).WithOne().HasForeignKey(pl => pl.RequestRenewId).OnDelete(DeleteBehavior.Cascade);
                // entity.HasOne(r => r.AssignedTo).WithOne().HasForeignKey<RequestRenew>(r => r.AssignedToId);
                entity.HasOne(r => r.AssignedTo)
           .WithMany() // Leaving this empty makes it unidirectional
           .HasForeignKey(r => r.AssignedToId)
           .OnDelete(DeleteBehavior.Restrict);

                // RELATIONS
                entity.HasOne(r => r.Part2Evaluation).WithOne().HasForeignKey<RequestRenew>(r => r.Part2EvaluationId);
                entity.HasOne(r => r.Part3Decision).WithOne().HasForeignKey<RequestRenew>(r => r.Part3DecisionId);
                entity.HasOne(r => r.FinalDecision).WithOne().HasForeignKey<RequestRenew>(r => r.FinalDecisionId);
                entity.HasOne(r => r.Clarification).WithOne().HasForeignKey<RequestRenew>(r => r.ClarificationId);
                entity.HasOne(r => r.Payment).WithOne().HasForeignKey<RequestRenew>(r => r.PaymentId);

                // NEW CHANGE REQUEST RELATIONS
                entity.HasOne(r => r.UpdatedVendorData).WithOne().HasForeignKey<RequestRenew>(r => r.UpdatedVendorDataId);
                entity.HasOne(r => r.TdcDocument).WithOne().HasForeignKey<RequestRenew>(r => r.TdcDocumentId);
                entity.HasOne(r => r.OfficialLetterFile).WithOne().HasForeignKey<RequestRenew>(r => r.OfficialLetterFileId);
                entity.HasOne(r => r.ValidAgencyAgreementFile).WithOne().HasForeignKey<RequestRenew>(r => r.ValidAgencyAgreementFileId);

                entity.HasOne(r => r.OfficialLetterFileRemark)
                      .WithOne()
                      .HasForeignKey<RequestRenew>(r => r.OfficialLetterFileRemarkId);

                entity.HasOne(r => r.RenewRequestFile)
                      .WithOne()
                      .HasForeignKey<RequestRenew>(r => r.RenewRequestFileId);

                entity.HasOne(r => r.ManagerRenewFile).WithOne().HasForeignKey<RequestRenew>(r => r.ManagerRenewFileId);
            });

            modelBuilder.Entity<RequestRenewClarification>(entity =>
            {
                entity.HasOne(c => c.Sender).WithMany().HasForeignKey(c => c.SenderId);

                // Unidirectional relations:
                entity.HasOne<RequestRenew>()
                      .WithMany(r => r.Clarifications) // Requires the Clarifications property in RequestRenew
                      .HasForeignKey(c => c.RequestRenewId)
                      .OnDelete(DeleteBehavior.Cascade);

                entity.HasOne<PrequalificationRequest>()
                      .WithMany(p => p.Clarifications) // Requires the Clarifications property in PrequalificationRequest
                      .HasForeignKey(c => c.PrequalificationRequestId)
                      .OnDelete(DeleteBehavior.Cascade);

                entity.HasOne<VendorRequestLetter>()
                      .WithMany(l => l.Clarifications) // Requires the Clarifications property in VendorRequestLetter
                      .HasForeignKey(c => c.VendorRequestLetterId)
                      .OnDelete(DeleteBehavior.Cascade);
            });

            modelBuilder.Entity<PqRequestClarification>(entity =>
        {
            entity.ToTable("pq_request_clarifications");

            // Map the complex list to JSONB
            entity.Property(e => e.StepObservations)
                  .HasColumnType("jsonb");

            // Unidirectional/Internal relation
            entity.HasOne<PrequalificationRequest>()
                  .WithMany(p => p.PqClarifications)
                  .HasForeignKey(c => c.PrequalificationRequestId)
                  .OnDelete(DeleteBehavior.Cascade);

            // Enum Conversions
            entity.Property(e => e.Sender).HasConversion<int>();
            entity.Property(e => e.Receiver).HasConversion<int>();
        });

            modelBuilder.Entity<RenewalFinalDecision>(entity =>
            {
                entity.ToTable("renewal_final_decisions");
                entity.Property(e => e.Id).HasColumnName("id");
                entity.Property(e => e.Decision).HasColumnName("decision");
                entity.Property(e => e.Comments).HasColumnName("comments");
                entity.Property(e => e.ReviewDate).HasColumnName("review_date");

                // This forces EF to use snake_case for the FK
                entity.HasOne(d => d.SupportingDocument)
                      .WithMany()
                      .HasForeignKey(d => d.SupportingDocumentId);

                entity.Property(e => e.SupportingDocumentId).HasColumnName("supporting_document_id");
            });

            // --- NEW FEATURE JSON CONFIGS ---

            modelBuilder.Entity<RenewalPart2Evaluation>(entity =>
            {
                entity.OwnsOne(e => e.Criteria, c =>
                {
                    c.ToJson("criteria");
                    c.OwnsOne(x => x.ProductPerformance);
                    c.OwnsOne(x => x.ComplianceWithStandards);
                    c.OwnsOne(x => x.AfterSaleService);
                });
            });

            modelBuilder.Entity<RenewalPart3Decision>(entity =>
  {
      entity.ToTable("renewal_part3_decisions");
      entity.Property(e => e.Id).HasColumnName("id");
      entity.Property(e => e.Decision).HasColumnName("decision");
      entity.Property(e => e.Reason).HasColumnName("reason");
      entity.Property(e => e.DecidedBy).HasColumnName("decided_by");
      entity.Property(e => e.DecisionDate).HasColumnName("decision_date");

      // FIX: Use the property d.SupportingDocumentId instead of the string "SupportingDocumentId"
      entity.HasOne(d => d.SupportingDocument)
            .WithMany()
            .HasForeignKey(d => d.SupportingDocumentId);

      entity.Property(e => e.SupportingDocumentId).HasColumnName("supporting_document_id");
  });
            modelBuilder.Entity<RenewalClarification>(entity =>
            {
                entity.Property(e => e.RequiredDocuments).HasColumnType("jsonb");
            });

            modelBuilder.Entity<VendorResponse>(entity =>
            {
                entity.HasMany(v => v.Documents).WithMany().UsingEntity(j => j.ToTable("vendor_response_files"));
            });

            // EXISTING EVALUATION DATA
            modelBuilder.Entity<EvaluationData>(entity =>
            {
                entity.ToTable("evaluation_data");
                entity.OwnsOne(e => e.Evaluation, ev =>
                {
                    ev.ToJson();
                    ev.OwnsOne(x => x.CriteriaEvaluation, ce => { ce.OwnsOne(p => p.ProductPerformance); ce.OwnsOne(p => p.AfterSaleService); ce.OwnsOne(p => p.ComplianceWithStandards); });
                    ev.OwnsOne(x => x.FinalDecision); ev.OwnsOne(x => x.ManufacturerDetails); ev.OwnsOne(x => x.PolicyChecks);
                });
            });
        }
    }
}