using LSFE.Domain.Contracts.Doctor;
using LSFE.Infrastructure.Database;
using LSFE.Infrastructure.Dto.Doctor;
using LSFE.Infrastructure.Entities.Doctor; 
using LSFE.Infrastructure.Model;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace LSFE.Domain.Services.Doctor
{
    public class DoctorRepo : GenericRepository<Infrastructure.Entities.Doctor.Doctor>, IDoctorRepo
    {
        private readonly LSFEDbContext _context;
        public DoctorRepo(LSFEDbContext context) : base(context)
        {
            _context = context;
        }

        public async Task<Response> SoftDeleteAsync(int doctorId)
        {
            var response = new Response();

            try
            {
                var existingDoctor = await GetByIdAsync(doctorId);

                if (existingDoctor == null)
                {
                    response.Success = false;
                    response.Message = $"Doctor with ID {doctorId} not found.";
                    response.MessCode = 0;
                    return response;
                }

                existingDoctor.IsActive = false;

                Update(existingDoctor);
                await _context.SaveChangesAsync();

                response.Success = true;
                response.Data = existingDoctor;
                response.Message = "Doctor deactivated successfully.";
                response.MessCode = 3; // code for delete/soft delete
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.Message = ex.Message;
                response.MessCode = 0;
            }

            return response;
        }
        public async Task<List<Infrastructure.Entities.Doctor.Doctor>> GetAllDoctor(DoctorDto dto)
        {
            var allItems = await _context.Doctors
                     .FromSqlRaw("EXEC GetAllDoctor @UserId",
                     new SqlParameter("@UserId", dto.UserId))
                     .ToListAsync();

            return allItems ?? new List<Infrastructure.Entities.Doctor.Doctor>();
        }
        public async Task<List<DoctorDto>> GetDoctor(DoctorDto dto)
        {
            var query =
                from d in _context.Doctors
                join i in _context.Institutions
                    on d.InstitutionId equals i.InstitutionId into di
                from i in di.DefaultIfEmpty()
                join s in _context.Specializations
                on d.SpecializationId equals s.SpecializationId into si
                from s in si.DefaultIfEmpty()
                join dis in _context.Districts
                    on d.DistrictId equals dis.DistrictId into dist
                from dis in dist.DefaultIfEmpty()
                join fda in _context.ForDoctorApprovals
                    on d.DoctorId equals fda.DoctorId
                join u in _context.Users
                    on d.UserId equals u.Id
                where d.UserId == dto.UserId
                    && (dto.DoctorId == 0 || d.DoctorId == dto.DoctorId)
                select new DoctorDto
                {
                    DoctorId = d.DoctorId,
                    FirstName = d.FirstName,
                    MiddleInitial = d.MiddleInitial,
                    LastName = d.LastName,
                    EmailAddress = d.EmailAddress,
                    PhoneNo = d.PhoneNo,
                    SpecializationId = d.SpecializationId,
                    LicenseNo = d.LicenseNo,
                    Address = d.Address,
                    BirthDate = d.BirthDate,
                    MaxVisit = d.MaxVisit,
                    MedRepName=u.UserName,
                    Status = fda.Status,
                    IsTerritorial= d.IsTerritorial,
                    InstitutionId = fda.InstitutionId,
                    InstitutionName = i != null ? i.InstitutionName : null,
                    SpecializationName = s != null ? s.SpecializationName : null,
                    DistrictId = fda != null ? dis.DistrictId : 0,
                    DistrictName = dis != null ? dis.DistrictName : null
                };

            return await query.ToListAsync();
        }

        public async Task<List<DoctorDto>> GetMyDoctor(DoctorDto dto)
        {
            var query =
                from d in _context.Doctors
                join i in _context.Institutions
                    on d.InstitutionId equals i.InstitutionId into di
                from i in di.DefaultIfEmpty()
                join s in _context.Specializations
                   on d.SpecializationId equals s.SpecializationId into si
                from s in si.DefaultIfEmpty()
                join dis in _context.Districts
                 on d.DistrictId equals dis.DistrictId into dist
                from dis in dist.DefaultIfEmpty()
                join u in _context.Users
                  on d.UserId equals u.Id
                where d.UserId == dto.UserId
                select new DoctorDto
                {
                    DoctorId = d.DoctorId,
                    FirstName = d.FirstName,
                    MiddleInitial = d.MiddleInitial,
                    LastName = d.LastName,
                    EmailAddress = d.EmailAddress,
                    PhoneNo = d.PhoneNo,
                    SpecializationId = d.SpecializationId,
                    LicenseNo = d.LicenseNo,
                    Address = d.Address,
                    BirthDate = d.BirthDate,
                    MaxVisit = d.MaxVisit,
                    Status = d.Status,
                    MedRepName = u.UserName,
                    IsTerritorial = d.IsTerritorial,
                    InstitutionId = d.InstitutionId,
                    InstitutionName = i != null ? i.InstitutionName : null,
                    SpecializationName = s != null ?  s.SpecializationName : null,
                    DistrictId = dis != null ? dis.DistrictId : 0,
                    DistrictName = dis != null ? dis.DistrictName : null
                };

            return await query.ToListAsync();
        }
        public async Task<Response> PostPutDoctor(DoctorDto dto)
        {
            var response = new Response();

            try
            {
                Infrastructure.Entities.Doctor.Doctor? existingDoctor = null;
                ForDoctorApproval? existingApproval = null;

                // Check if DoctorId exists
                if (dto.DoctorId > 0)
                {
                    existingDoctor = await GetByIdAsync(dto.DoctorId);
                    existingApproval = await _context.ForDoctorApprovals
                        .FirstOrDefaultAsync(x => x.DoctorId == dto.DoctorId);
                }

                int doctorId = dto.DoctorId;

                // Step 1: Insert/Update in main Doctors table first to get DoctorId
                if (existingDoctor == null)
                {
                    // CREATE new doctor in main table with Status = 1 (For DSM Approval)
                    var newDoctor = MapToNewDoctor(dto);
                    newDoctor.Status = 1; // Always start with "For DSM Approval"
                    newDoctor.IsActive = false; // Not active until approved

                    await AddAsync(newDoctor);
                    await _context.SaveChangesAsync(); // Save to get DoctorId

                    doctorId = newDoctor.DoctorId; // Get the auto-generated DoctorId
                }
                else
                {
                    // UPDATE existing doctor - keep current status, don't change it here
                    // The doctor table will only be updated when status = 3 (Approved)
                    // For now, we just ensure it exists
                    doctorId = existingDoctor.DoctorId;
                }

                // Step 2: Insert/Update in ForDoctorApproval staging table
                if (existingApproval == null)
                {
                    // INSERT into ForDoctorApproval
                    var newApproval = new ForDoctorApproval
                    {
                        ForDoctorApprovalId = Guid.NewGuid(),
                        DoctorId = doctorId, // Use the DoctorId from main table
                        FirstName = dto.FirstName,
                        MiddleInitial = dto.MiddleInitial,
                        LastName = dto.LastName,
                        MaxVisit = dto.MaxVisit,
                        SpecializationId = dto.SpecializationId,
                        UserId = dto.UserId,
                        StageLevelId = dto.StageLevelId,
                        DistrictId = dto.DistrictId,
                        BirthDate = dto.BirthDate,
                        EmailAddress = dto.EmailAddress,
                        Address = dto.Address,
                        PhoneNo = dto.PhoneNo,
                        LicenseNo = dto.LicenseNo,
                        InstitutionId = dto.InstitutionId,
                        IsTerritorial = dto.IsTerritorial,
                        Status = 1, // Always "For DSM Approval" when submitted
                        CreatedDate = DateTime.UtcNow,
                        UpdatedDate = DateTime.UtcNow,
                        CreatedBy = dto.UserName
                    };

                    await _context.ForDoctorApprovals.AddAsync(newApproval);
                    await _context.SaveChangesAsync();

                    return new Response
                    {
                        Success = true,
                        Data = new { DoctorId = doctorId, Approval = newApproval },
                        Message = "Doctor submitted for approval successfully.",
                        MessCode = 1
                    };
                }
                else
                {
                    // UPDATE existing ForDoctorApproval
                    existingApproval.FirstName = dto.FirstName;
                    existingApproval.MiddleInitial = dto.MiddleInitial;
                    existingApproval.LastName = dto.LastName;
                    existingApproval.MaxVisit = dto.MaxVisit;
                    existingApproval.DistrictId = dto.DistrictId;
                    existingApproval.SpecializationId = dto.SpecializationId;
                    existingApproval.BirthDate = dto.BirthDate;
                    existingApproval.EmailAddress = dto.EmailAddress;
                    existingApproval.Address = dto.Address;
                    existingApproval.PhoneNo = dto.PhoneNo;
                    existingApproval.LicenseNo = dto.LicenseNo;
                    existingApproval.InstitutionId = dto.InstitutionId;
                    existingApproval.IsTerritorial = dto.IsTerritorial;
                    existingApproval.UserId = dto.UserId;
                    existingApproval.StageLevelId = dto.StageLevelId;
                    existingApproval.Status = 1; // Reset to "For DSM Approval"
                    existingApproval.UpdatedDate = DateTime.UtcNow;
                    existingApproval.UpdatedBy = dto.UpdatedBy;

                    _context.ForDoctorApprovals.Update(existingApproval);
                    await _context.SaveChangesAsync();

                    return new Response
                    {
                        Success = true,
                        Data = new { DoctorId = doctorId, Approval = existingApproval },
                        Message = "Doctor information updated and resubmitted for approval.",
                        MessCode = 2
                    };
                }
            }
            catch (Exception ex)
            {
                return new Response
                {
                    Success = false,
                    Message = ex.Message,
                    MessCode = 0
                };
            }
        }

        public async Task<Response> ApproveDoctor(int doctorId, byte newStatus, string updatedBy)
        {
            var response = new Response();

            try
            {
                // Get the approval record from staging table
                var approval = await _context.ForDoctorApprovals
                    .FirstOrDefaultAsync(x => x.DoctorId == doctorId);

                if (approval == null)
                {
                    return new Response
                    {
                        Success = false,
                        Message = "Approval record not found for this doctor.",
                        MessCode = 0
                    };
                }

                // Get the doctor from main table
                var doctor = await GetByIdAsync(doctorId);

                if (doctor == null)
                {
                    return new Response
                    {
                        Success = false,
                        Message = "Doctor not found in main table.",
                        MessCode = 0
                    };
                }

                // Validate status progression
                // Status flow: 1 (For DSM) -> 2 (For NSM) -> 3 (Approved) or 0 (Denied at any step)
                if (newStatus != 0 && newStatus != 2 && newStatus != 3)
                {
                    return new Response
                    {
                        Success = false,
                        Message = "Invalid status. Use 0 (Denied), 2 (For NSM Approval), or 3 (Approved).",
                        MessCode = 0
                    };
                }

                // Update status in ForDoctorApproval staging table
                approval.Status = newStatus;
                approval.UpdatedDate = DateTime.UtcNow;
                _context.ForDoctorApprovals.Update(approval);

                // Handle different status scenarios
                if (newStatus==3) // APPROVED - Copy from staging to main table
                {
                    // Apply all changes from ForDoctorApproval to Doctors table
                    doctor.FirstName = approval.FirstName;
                    doctor.MiddleInitial = approval.MiddleInitial;
                    doctor.LastName = approval.LastName;
                    doctor.MaxVisit = approval.MaxVisit;
                    doctor.DistrictId = approval.DistrictId;
                    doctor.SpecializationId = approval.SpecializationId;
                    doctor.StageLevelId = approval.StageLevelId;
                    doctor.BirthDate = approval.BirthDate;
                    doctor.EmailAddress = approval.EmailAddress;
                    doctor.Address = approval.Address;
                    doctor.PhoneNo = approval.PhoneNo;
                    doctor.LicenseNo = approval.LicenseNo;
                    doctor.InstitutionId = approval.InstitutionId;
                    doctor.IsTerritorial = approval.IsTerritorial;
                    doctor.Status = 3;
                    doctor.IsActive = true; // Activate the doctor
                    doctor.UpdatedDate = DateTime.UtcNow;
                    doctor.UpdatedBy = updatedBy;

                    Update(doctor);

                    // Optionally, you can delete or archive the approval record
                    // _context.ForDoctorApprovals.Remove(approval);
                }
                else if (newStatus == 0) // DENIED
                {
                    // Update doctor status to denied
                    doctor.Status = 0;
                    doctor.IsActive = false;
                    doctor.UpdatedDate= DateTime.UtcNow;
                    doctor.UpdatedBy = updatedBy;
                    Update(doctor);
                }
                else if (newStatus == 2) // FOR NSM APPROVAL
                {
                    // Just update the staging table status, keep doctor status as is
                    doctor.Status = 2; // Update main table to show it's in NSM review
                    doctor.UpdatedDate = DateTime.UtcNow;
                    doctor.UpdatedBy = updatedBy;
                    Update(doctor);
                }

                await _context.SaveChangesAsync();

                string statusMessage = newStatus switch
                {
                    0 => "denied",
                    2 => "forwarded to NSM for approval",
                    3 => "approved and activated in the system",
                    _ => "updated"
                };

                return new Response
                {
                    Success = true,
                    Data = new { Doctor = doctor, Approval = approval },
                    Message = $"Doctor has been {statusMessage}.",
                    MessCode = 2
                };
            }
            catch (Exception ex)
            {
                return new Response
                {
                    Success = false,
                    Message = ex.Message,
                    MessCode = 0
                };
            }
        }

        public async Task<List<ForDoctorApprovalDto>> GetPendingApprovals(DoctorDto dto)
        {
            var query =
                from fda in _context.ForDoctorApprovals
                join d in _context.Doctors
                    on fda.DoctorId equals d.DoctorId into fdaDoc
                from d in fdaDoc.DefaultIfEmpty()
                join i in _context.Institutions
                    on fda.InstitutionId equals i.InstitutionId into fdai
                from i in fdai.DefaultIfEmpty()
                join dis in _context.Districts
                    on fda.DistrictId equals dis.DistrictId into fdaDist
                from dis in fdaDist.DefaultIfEmpty()
                join u in _context.Users
                    on d.UserId equals u.Id into user
                from u in user.DefaultIfEmpty()
                where fda.Status == 1 || u.Company == dto.Company
                select new ForDoctorApprovalDto
                {
                    ForDoctorApprovalId = fda.ForDoctorApprovalId,
                    DoctorId = fda.DoctorId,
                    FirstName = fda.FirstName,
                    MiddleInitial = fda.MiddleInitial,
                    LastName = fda.LastName,
                    EmailAddress = fda.EmailAddress,
                    PhoneNo = fda.PhoneNo,
                    SpecializationId = fda.SpecializationId,
                    LicenseNo = fda.LicenseNo,
                    Address = fda.Address,
                    BirthDate = fda.BirthDate,
                    MaxVisit = fda.MaxVisit,
                    MedRepName=u.UserName,
                    Status = fda.Status,
                    InstitutionId = fda.InstitutionId,
                    InstitutionName = i != null ? i.InstitutionName : null,
                    DistrictId = fda.DistrictId,
                    DistrictName = dis != null ? dis.DistrictName : null,
                    UserId = fda.UserId,
                    StageLevelId = fda.StageLevelId,
                    IsTerritorial = fda.IsTerritorial
                };

            return await query.ToListAsync();
        }

        private Infrastructure.Entities.Doctor.Doctor MapToNewDoctor(DoctorDto dto)
        {
            return new Infrastructure.Entities.Doctor.Doctor
            {
                FirstName = dto.FirstName,
                MiddleInitial = dto.MiddleInitial,
                LastName = dto.LastName,
                MaxVisit = dto.MaxVisit,
                SpecializationId = dto.SpecializationId,
                UserId = dto.UserId,
                StageLevelId = dto.StageLevelId,
                DistrictId = dto.DistrictId,
                BirthDate = dto.BirthDate,
                EmailAddress = dto.EmailAddress,
                Address = dto.Address,
                PhoneNo = dto.PhoneNo,
                LicenseNo = dto.LicenseNo,
                InstitutionId = dto.InstitutionId,
                IsTerritorial = dto.IsTerritorial,
                Status = dto.Status,
                IsActive = true,
                CreatedDate = DateTime.UtcNow,
                UpdatedDate = DateTime.UtcNow,
                CreatedBy = dto.UserName,
                UpdatedBy = dto.UserName,
            };
        }

        public async Task<List<DoctorCoverageToday>> GetDoctorCoverageToday(DoctorDto dto)
        {
            var allItems = await _context.DoctorCoverageTodays
                  .FromSqlRaw("EXEC GetDoctorCoverageToday @UserId,@MRPlanDate",
                  new SqlParameter("@UserId", dto.UserId),
                  new SqlParameter("@MRPlanDate", dto.MRPlanDate))
                  .ToListAsync();

            return allItems ?? new List<DoctorCoverageToday>();
        }
    }
}