Core_Be/Repos/ClientRepository.cs

using Fitness_core_BE.Domain;
using Fitness_core_BE.DTOs.Customer;
using Fitness_core_BE.DTOs.CustomerPlan;
using Fitness_core_BE.DTOs.Meal;
using Fitness_core_BE.DTOs.Workout;
using Fitness_core_BE.Interfaces;
using Fitness_core_BE.Models;
using Microsoft.EntityFrameworkCore;

namespace Fitness_core_BE.Repos
{

    public class ClientRepository : IClientRepository
    {
        // Tenant-scoped operational data (staff, memberships, invoices, access control, ...).
        private readonly TenantDbContext _dbContext;
        // Central identity/registry data (Customer, Client, ClientBusiness, admin templates,
        // platform billing).
        private readonly CentralDbContext _central;

        public ClientRepository(TenantDbContext dbContext, CentralDbContext central)
        {
            _dbContext = dbContext;
            _central = central;
        }


        public async Task<List<Customer>> GetAllCustomers()
        {
            return await _central.Customer
                .Where(c => c.IsActive == true)
                .ToListAsync();
        }

        public async Task<Customer?> GetCustomerById(Guid customerId)
        {
            var customer = await _central.Customer
                .AsNoTracking()
                .Include(c => c.User)
                .Include(c => c.CustomerTargets.Where(ct => ct.IsActive))
                    .ThenInclude(ct => ct.WeeklyProgresses.Where(wp => wp.IsActive))
                .FirstOrDefaultAsync(c => c.CusId == customerId && c.IsActive);
            if (customer == null)
                return null;

            // CustomerSubscriptions/CustomerWorkouts/CustomerMeals are tenant-only -- load
            // this tenant's slice separately and attach in memory (mirrors what a single
            // cross-database Include could no longer do).
            customer.CustomerSubscriptions = await _dbContext.CustomerSubscriptions
                .AsNoTracking()
                .Include(cs => cs.CustomerPlan)
                .Where(cs => cs.FKCustomerId == customerId)
                .ToListAsync();
            customer.CustomerWorkouts = await _dbContext.CustomerWorkouts
                .AsNoTracking()
                .Where(cw => cw.FKCustomerId == customerId && cw.IsActive)
                .ToListAsync();
            customer.CustomerMeals = await _dbContext.CustomerMeals
                .AsNoTracking()
                .Where(cm => cm.FKCustomerId == customerId && cm.IsActive)
                .ToListAsync();

            return customer;
        }



        public async Task<List<Client>> GetAllClientsAsync(CancellationToken ct = default)
        {
            return await _central.Client
                .AsNoTracking()
                .Include(c => c.User)
                .ToListAsync(ct);
        }

        public async Task<Client?> GetClientByIdAsync(Guid clientId, CancellationToken ct = default)
        {
            return await _central.Client
                .AsNoTracking()
                // Keep the profile read aligned with the same primary active
                // business row that UpdateGymProfile mutates.
                .Include(c => c.ClientBusinesses
                    .Where(b => b.IsActive)
                    .OrderBy(b => b.createdAt))
                .FirstOrDefaultAsync(c => c.ClientId == clientId, ct);
        }

        public async Task<List<Customer>> GetClientListAsync(Guid clientId, CancellationToken ct = default)
        {
            var customers = await _central.Customer

                .Where(c => c.FKUserId == clientId)
                .ToListAsync(ct);

            return customers;
        }

        public async Task<object> RegisterClientAsync(Client client, CancellationToken ct = default)
        {
            await _central.Client.AddAsync(client, ct);
            return new { client.ClientId };
        }


        public async Task<List<MemberListRowDto>> GetCustomersByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            // One row per member via SubscriptionMembers so group secondaries appear
            // alongside primaries, each pointing at the shared subscription.
            var memberships = await _dbContext.SubscriptionMembers
                .AsNoTracking()
                .Where(m => m.IsActive
                            && m.TenantCustomerLink.IsActiveAtThisClient
                            && m.Subscription.IsActive
                            && m.Subscription.CustomerPlan.FKClientId == clientId)
                .Include(m => m.TenantCustomerLink)
                .Include(m => m.Subscription)
                    .ThenInclude(s => s.CustomerPlan)
                .OrderByDescending(m => m.Subscription.OnboardDate)
                .ToListAsync(ct);

            // Latest subscription per member (mirrors the previous one-row-per-member semantic).
            var latestPerMember = memberships
                .GroupBy(m => m.FKCustomerId)
                .Select(g => g.First())
                .ToList();

            var subscriptionIds = latestPerMember.Select(m => m.FKSubscriptionId).Distinct().ToList();

            // Batch: latest current invoice per subscription. IsActive excludes an unapproved
            // renewal invoice awaiting staff review; RolledOver excludes one already superseded
            // by a newer invoice that carried its balance forward -- without both, this list
            // could show either a not-yet-billed amount or a double-counted balance.
            var invoices = await _dbContext.CustomerInvoices
                .AsNoTracking()
                .Where(i => subscriptionIds.Contains(i.FKCustomerSubscriptionId)
                         && i.IsActive && i.Status != "Cancelled" && i.Status != "RolledOver")
                .GroupBy(i => i.FKCustomerSubscriptionId)
                .Select(g => g.OrderByDescending(i => i.CustomerInvoiceDate).First())
                .ToListAsync(ct);
            var invoiceBySub = invoices.ToDictionary(i => i.FKCustomerSubscriptionId);

            // Batch: approved paid sums per invoice.
            var invoiceIds = invoices.Select(i => i.CustomerInvoiceId).ToList();
            var paidSums = await _dbContext.PaymentTransactions
                .AsNoTracking()
                .Where(p => p.FK_CustomerInvoiceId != null
                            && invoiceIds.Contains(p.FK_CustomerInvoiceId.Value)
                            && p.IsApproved == true
                            && p.IsActive == true)
                .GroupBy(p => p.FK_CustomerInvoiceId!.Value)
                .Select(g => new { InvoiceId = g.Key, Paid = g.Sum(p => p.PayAmount) })
                .ToDictionaryAsync(x => x.InvoiceId, x => x.Paid, ct);

            // Batch: active member counts per subscription.
            var memberCounts = await _dbContext.SubscriptionMembers
                .AsNoTracking()
                .Where(m => subscriptionIds.Contains(m.FKSubscriptionId) && m.IsActive && m.IsPlanAssigned)
                .GroupBy(m => m.FKSubscriptionId)
                .Select(g => new { SubscriptionId = g.Key, Count = g.Count() })
                .ToDictionaryAsync(x => x.SubscriptionId, x => x.Count, ct);

            // Batch: tenant-scoped access profiles and their terminal bindings.
            var customerUserIds = latestPerMember
                .Where(m => m.TenantCustomerLink != null)
                .Select(m => m.TenantCustomerLink.CentralUserId)
                .Distinct()
                .ToList();
            var accessByUser = await _dbContext.AccessProfiles
                .AsNoTracking()
                .Include(p => p.DeviceBindings)
                    .ThenInclude(b => b.AccessDevice)
                .Where(p => p.FKClientId == clientId && customerUserIds.Contains(p.FKUserId))
                .ToDictionaryAsync(p => p.FKUserId, p => p, ct);

            var rows = latestPerMember.Select(m =>
            {
                var subscription = m.Subscription;
                var plan = subscription.CustomerPlan;
                invoiceBySub.TryGetValue(subscription.SubscriptionId, out var invoice);

                // Legacy subscriptions without an invoice still show the plan price as owed.
                var invoiceAmount = invoice?.Amount ?? plan?.PlanPrice ?? 0m;
                var paid = invoice != null && paidSums.TryGetValue(invoice.CustomerInvoiceId, out var sum) ? sum : 0m;
                var balance = invoiceAmount - paid;
                accessByUser.TryGetValue(m.TenantCustomerLink.CentralUserId, out var access);
                var bindings = access?.DeviceBindings
                    .Where(b => b.AccessDevice.IsActive && b.AccessDevice.FKAgentId != null)
                    .ToList() ?? new List<AccessDeviceBinding>();
                var provision = access == null
                    ? "NotConfigured"
                    : bindings.Count == 0 ? "NoDevice"
                    : bindings.Any(b => b.ProvisionStatus == "Failed") ? "Failed"
                    : bindings.All(b => b.ProvisionStatus == "Synced") ? "Synced" : "Pending";
                var actual = bindings.Count == 0 ? "Unknown"
                    : bindings.All(b => b.ActualAccessState == access!.DesiredAccessState)
                        ? access.DesiredAccessState
                        : bindings.All(b => b.ActualAccessState == "Unknown")
                            ? "Unknown"
                            : "Mixed";

                return new MemberListRowDto
                {
                    FingerprintId = access != null && access.AccessProfileId <= int.MaxValue
                        ? (int)access.AccessProfileId : null,
                    FingerprintName = access?.DisplayName,
                    AccessProfileId = access?.AccessProfileId,
                    AccessNumber = bindings.FirstOrDefault()?.DeviceEmployeeNo
                        ?? (access == null ? null : $"HF{access.AccessProfileId}"),
                    AccessProvisionStatus = provision,
                    ActualAccessState = actual,
                    DesiredAccessState = access?.DesiredAccessState ?? "Enabled",
                    FingerprintCount = bindings.Count == 0 ? 0 : bindings.Max(b => b.FingerprintCount),
                    AccessLastError = bindings.FirstOrDefault(b => !string.IsNullOrWhiteSpace(b.LastError))?.LastError,
                    IsLocked = access?.DesiredAccessState == "Disabled",
                    LockReason = access?.LockReason,
                    HasFacePhoto = access?.HasFacePhoto ?? false,
                    FacePhotoStatus = access?.FacePhotoStatus ?? "None",
                    FacePhotoError = access?.FacePhotoError,
                    SubscriptionId = subscription.SubscriptionId,
                    FKCustomerPlanId = subscription.FKCustomerPlanId,
                    FKCustomerId = m.FKCustomerId,
                    OnboardDate = subscription.OnboardDate,
                    ExpiryDate = subscription.ExpiryDate,
                    RenewalDate = subscription.RenewalDate,
                    IsActive = subscription.IsActive,
                    CreatedAt = subscription.CreatedAt,
                    Customer = BuildDisplayCustomer(m.TenantCustomerLink),
                    IsPrimary = m.IsPrimary,
                    IsPlanAssigned = m.IsPlanAssigned,
                    PlanName = plan?.PlanName,
                    PlanScope = plan?.PlanScope ?? "Individual",
                    MemberCount = memberCounts.TryGetValue(subscription.SubscriptionId, out var count) ? count : 1,
                    MemberLimit = plan?.MemberLimit ?? 1,
                    InvoiceAmount = invoiceAmount,
                    PaidAmount = paid,
                    Balance = balance,
                    PaymentStatus = paid >= invoiceAmount && invoiceAmount > 0
                        ? "Paid"
                        : paid > 0 ? "Partial" : "Pending",
                    AllowSplitPayments = plan?.AllowSplitPayments ?? false
                };
            }).ToList();

            // Customers created without a plan yet have no SubscriptionMember row, so they'd
            // otherwise be invisible here — they're still this gym's members (linked via the
            // AccessProfile created at creation time, same as everyone else) and need to show
            // up so staff can find them and assign a plan (as primary or as a group member).
            var linkedCustomerIds = rows.Select(r => r.FKCustomerId).ToHashSet();
            var planlessLinkIds = await _dbContext.AccessProfiles
                .AsNoTracking()
                .Where(p => p.FKClientId == clientId && p.PersonType == "Customer")
                .Join(_dbContext.TenantCustomerLink.Where(l => l.IsActiveAtThisClient),
                    p => p.FKUserId, l => l.CentralUserId,
                    (p, l) => l.TenantCustomerLinkId)
                .Where(cusId => !linkedCustomerIds.Contains(cusId))
                .Distinct()
                .ToListAsync(ct);

            if (planlessLinkIds.Count > 0)
            {
                // Same tenant-only cache as the main list above -- no central round-trip.
                var planlessLinks = await _dbContext.TenantCustomerLink
                    .AsNoTracking()
                    .Where(l => planlessLinkIds.Contains(l.TenantCustomerLinkId) && l.IsActiveAtThisClient)
                    .ToListAsync(ct);

                rows.AddRange(planlessLinks.Select(l => new MemberListRowDto
                {
                    SubscriptionId = Guid.Empty,
                    FKCustomerPlanId = Guid.Empty,
                    FKCustomerId = l.TenantCustomerLinkId,
                    OnboardDate = l.CreatedAt,
                    ExpiryDate = null,
                    RenewalDate = null,
                    IsActive = l.IsActiveAtThisClient,
                    CreatedAt = l.CreatedAt,
                    Customer = BuildDisplayCustomer(l),
                    IsPrimary = true,
                    PlanName = null,
                    PlanScope = "None",
                    MemberCount = 0,
                    MemberLimit = 0,
                    InvoiceAmount = 0,
                    PaidAmount = 0,
                    Balance = 0,
                    PaymentStatus = "NoPlan",
                    AllowSplitPayments = false
                }));
            }

            return rows.OrderByDescending(r => r.CreatedAt).ToList();
        }

        // Builds a display-only Customer for the member list from this gym's own cached copy
        // (TenantCustomerLink) instead of a central-DB round-trip. Only name/phone/email/active
        // are cached here -- gender/DOB/NIC/login-username are central-only and are fetched by
        // GetMemberById (client/customers/{id}) when a single member's full profile is needed
        // (e.g. opening the edit form), not for every row in the list.
        private static Models.Customer BuildDisplayCustomer(TenantCustomerLink link) => new()
        {
            CusId = link.TenantCustomerLinkId,
            CusName = link.DisplayName,
            PrimaryPH = link.PrimaryPhoneCached,
            Email = link.EmailCached,
            FKUserId = link.CentralUserId,
            IsActive = link.IsActiveAtThisClient,
            CreatedAt = link.CreatedAt,
        };

        public async Task<bool> CustomerBelongsToClientAsync(Guid customerId, Guid clientId, CancellationToken ct = default)
        {
            var hasSubscription = await _dbContext.SubscriptionMembers
                .AsNoTracking()
                .AnyAsync(member => member.FKCustomerId == customerId
                    && member.IsActive
                    && member.Subscription.IsActive
                    && member.Subscription.CustomerPlan.FKClientId == clientId, ct);
            if (hasSubscription)
                return true;

            // Plan-less customers have no SubscriptionMember row yet — fall back to the
            // AccessProfile link created at member-creation time (see GetCustomersByClientIdAsync).
            // Central lookup for the customer's shared login UserId, then a tenant-scoped check.
            var userId = await _central.Customer
                .AsNoTracking()
                .Where(c => c.CusId == customerId)
                .Select(c => (Guid?)c.FKUserId)
                .FirstOrDefaultAsync(ct);
            if (userId == null)
                return false;

            return await _dbContext.AccessProfiles
                .AsNoTracking()
                .AnyAsync(p => p.FKClientId == clientId && p.PersonType == "Customer" && p.FKUserId == userId.Value, ct);
        }

        public async Task<List<CustomerPlan>> GetCustomerPlansByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.CustomerPlans
                .Where(p => p.FKClientId == clientId && p.IsActive)
                .AsNoTracking()
                .OrderByDescending(p => p.CreatedAt)
                .ToListAsync(ct);
        }

        public async Task<CustomerPlan?> GetCustomerPlanEntityForClientAsync(Guid customerPlanId, Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.CustomerPlans
                .AsNoTracking()
                .Where(p => p.CustomerPlanId == customerPlanId && p.FKClientId == clientId && p.IsActive)
                .FirstOrDefaultAsync(ct);
        }

        // Unlike CustomerSubscription.FKCustomerId (which only ever points at the primary
        // member), this resolves the caller's own SubscriptionMember row whether they're
        // the primary or a secondary member of a group subscription.
        public async Task<SubscriptionMember?> GetActiveMembershipForCustomerAsync(Guid customerId, CancellationToken ct = default)
        {
            return await _dbContext.SubscriptionMembers
                .Include(m => m.Subscription)
                    .ThenInclude(s => s.CustomerPlan)
                .Where(m => m.FKCustomerId == customerId && m.IsActive && m.Subscription.IsActive)
                .OrderByDescending(m => m.Subscription.CreatedAt)
                .FirstOrDefaultAsync(ct);
        }

        public async Task DeactivateSubscriptionAsync(Guid subscriptionId, CancellationToken ct = default)
        {
            var subscription = await _dbContext.CustomerSubscriptions
                .FirstOrDefaultAsync(cs => cs.SubscriptionId == subscriptionId, ct);
            if (subscription == null)
                return;

            subscription.IsActive = false;

            var members = await _dbContext.SubscriptionMembers
                .Where(m => m.FKSubscriptionId == subscriptionId && m.IsActive)
                .ToListAsync(ct);
            foreach (var member in members)
                member.IsActive = false;

            var subItems = await _dbContext.CustomerSubItems
                .Where(si => si.FKCustomerSubscriptionId == subscriptionId && si.IsActive)
                .ToListAsync(ct);
            foreach (var item in subItems)
                item.IsActive = false;

            var installments = await _dbContext.SubscriptionInstallments
                .Where(i => i.FKSubscriptionId == subscriptionId && i.IsActive)
                .ToListAsync(ct);
            foreach (var installment in installments)
                installment.IsActive = false;
        }

        public async Task DeactivateSubscriptionMemberAsync(Guid subscriptionMemberId, CancellationToken ct = default)
        {
            var member = await _dbContext.SubscriptionMembers
                .FirstOrDefaultAsync(m => m.SubscriptionMemberId == subscriptionMemberId && m.IsActive, ct);
            if (member != null)
                member.IsActive = false;
        }

        // Unassign/re-assign only moves IsPlanAssigned. IsActive is left alone so the member
        // stays in the gym's member list (and keeps their device enrollment) while sitting
        // without a plan, and so the same button can put them straight back on it.
        public async Task SetMembershipPlanAssignedAsync(
            Guid subscriptionId, bool assigned, bool wholeSubscription, Guid subscriptionMemberId,
            CancellationToken ct = default)
        {
            var members = wholeSubscription
                ? await _dbContext.SubscriptionMembers
                    .Where(m => m.FKSubscriptionId == subscriptionId && m.IsActive)
                    .ToListAsync(ct)
                : await _dbContext.SubscriptionMembers
                    .Where(m => m.SubscriptionMemberId == subscriptionMemberId && m.IsActive)
                    .ToListAsync(ct);

            foreach (var member in members)
                member.IsPlanAssigned = assigned;
        }

        // The membership to act on for assign/unassign, ignoring IsPlanAssigned so an
        // already-unassigned member can be found again and put back on their plan.
        public async Task<SubscriptionMember?> GetMembershipForCustomerAsync(
            Guid customerId, CancellationToken ct = default)
        {
            return await _dbContext.SubscriptionMembers
                .Include(m => m.Subscription)
                    .ThenInclude(s => s.CustomerPlan)
                .Where(m => m.FKCustomerId == customerId && m.IsActive && m.Subscription.IsActive)
                .OrderByDescending(m => m.Subscription.CreatedAt)
                .FirstOrDefaultAsync(ct);
        }

        public async Task ReplaceCustomerSubscriptionAsync(SubscriptionMember? oldMembership, CustomerSubscriptionAggregate newAggregate, CancellationToken ct = default)
        {
            if (oldMembership != null)
            {
                if (oldMembership.IsPrimary)
                    await DeactivateSubscriptionAsync(oldMembership.FKSubscriptionId, ct);
                else
                    await DeactivateSubscriptionMemberAsync(oldMembership.SubscriptionMemberId, ct);
            }

            await _dbContext.CustomerSubscriptions.AddAsync(newAggregate.Subscription, ct);
            await _dbContext.CustomerSubItems.AddAsync(newAggregate.InitialSubItem, ct);
            await _dbContext.SubscriptionMembers.AddAsync(newAggregate.PrimaryMember, ct);
            await _dbContext.SubscriptionInstallments.AddRangeAsync(newAggregate.Installments, ct);
        }

        public async Task<CustomerPlanDto?> GetCustomerPlanByIdForClientAsync(Guid customerPlanId, Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.CustomerPlans
                .AsNoTracking()
                .Where(p => p.CustomerPlanId == customerPlanId && p.FKClientId == clientId && p.IsActive)
                .Select(p => new CustomerPlanDto
                {
                    CustomerPlanId = p.CustomerPlanId,
                    FKClientId = p.FKClientId,
                    ClientName = null,
                    PlanName = p.PlanName,
                    PlanDescription = p.PlanDescription,
                    PlanPrice = p.PlanPrice,
                    DurationInDays = p.DurationInDays,
                    IsActive = p.IsActive,
                    CreatedAt = p.CreatedAt
                })
                .FirstOrDefaultAsync(ct);
        }

        public async Task RegisterBussinessDetailAsync(ClientBusiness clientBusiness, CancellationToken ct = default)
        {
            await _central.ClientBusiness.AddAsync(clientBusiness, ct);
        }

        public async Task<ClientBusiness?> GetPrimaryBusinessByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            // Tracked query — the profile editor mutates this entity and persists via UoW.
            return await _central.ClientBusiness
                .Where(b => b.FKClientId == clientId && b.IsActive)
                .OrderBy(b => b.createdAt)
                .FirstOrDefaultAsync(ct);
        }

        public async Task<Client?> GetClientEntityByIdAsync(Guid clientId, CancellationToken ct = default)
        {
            // Tracked — used to mirror contact-detail changes back onto the Client record.
            return await _central.Client.FirstOrDefaultAsync(c => c.ClientId == clientId, ct);
        }

        public async Task<List<FollowedPlanDto>> GetFollowedWorkoutPlansAsync(Guid customerId, CancellationToken ct = default)
        {
            var follows = await _dbContext.CustomerWorkouts
                .AsNoTracking()
                .Where(cw => cw.FKCustomerId == customerId && cw.IsActive)
                .Select(cw => new { cw.FKWorkoutPlanId, cw.IsFollowing, cw.CompletedDate })
                .ToListAsync(ct);
            if (follows.Count == 0)
                return new List<FollowedPlanDto>();

            var planIds = follows.Select(f => f.FKWorkoutPlanId).Distinct().ToList();
            var names = await ResolveWorkoutPlanNamesAsync(planIds, ct);

            return follows
                .Select(f => new FollowedPlanDto(f.FKWorkoutPlanId, names.GetValueOrDefault(f.FKWorkoutPlanId), f.IsFollowing, f.CompletedDate))
                .ToList();
        }

        public async Task<List<FollowedPlanDto>> GetFollowedMealPlansAsync(Guid customerId, CancellationToken ct = default)
        {
            var follows = await _dbContext.CustomerMeals
                .AsNoTracking()
                .Where(cm => cm.FKCustomerId == customerId && cm.IsActive)
                .Select(cm => new { cm.FKMealPlanId, cm.IsFollowing, cm.CompletedDate })
                .ToListAsync(ct);
            if (follows.Count == 0)
                return new List<FollowedPlanDto>();

            var planIds = follows.Select(f => f.FKMealPlanId).Distinct().ToList();
            var names = await ResolveMealPlanNamesAsync(planIds, ct);

            return follows
                .Select(f => new FollowedPlanDto(f.FKMealPlanId, names.GetValueOrDefault(f.FKMealPlanId), f.IsFollowing, f.CompletedDate))
                .ToList();
        }

        // A followed WorkoutPlan/MealPlan id could be an admin template (central) or this
        // gym's own custom plan (tenant) -- resolve names from both buckets and merge.
        private async Task<Dictionary<Guid, string?>> ResolveWorkoutPlanNamesAsync(List<Guid> planIds, CancellationToken ct)
        {
            var central = await _central.WorkoutPlans
                .AsNoTracking()
                .Where(wp => planIds.Contains(wp.WorkoutPlanId))
                .ToDictionaryAsync(wp => wp.WorkoutPlanId, wp => (string?)wp.WorkoutPlanName, ct);
            var missing = planIds.Except(central.Keys).ToList();
            if (missing.Count > 0)
            {
                var tenant = await _dbContext.WorkoutPlans
                    .AsNoTracking()
                    .Where(wp => missing.Contains(wp.WorkoutPlanId))
                    .ToDictionaryAsync(wp => wp.WorkoutPlanId, wp => (string?)wp.WorkoutPlanName, ct);
                foreach (var kv in tenant)
                    central[kv.Key] = kv.Value;
            }
            return central;
        }

        private async Task<Dictionary<Guid, string?>> ResolveMealPlanNamesAsync(List<Guid> planIds, CancellationToken ct)
        {
            var central = await _central.MealPlans
                .AsNoTracking()
                .Where(mp => planIds.Contains(mp.MealPlanId))
                .ToDictionaryAsync(mp => mp.MealPlanId, mp => (string?)mp.Name, ct);
            var missing = planIds.Except(central.Keys).ToList();
            if (missing.Count > 0)
            {
                var tenant = await _dbContext.MealPlans
                    .AsNoTracking()
                    .Where(mp => missing.Contains(mp.MealPlanId))
                    .ToDictionaryAsync(mp => mp.MealPlanId, mp => (string?)mp.Name, ct);
                foreach (var kv in tenant)
                    central[kv.Key] = kv.Value;
            }
            return central;
        }

        public async Task<CustomerHealth?> GetCustomerHealthAsync(Guid customerId, CancellationToken ct = default)
        {
            return await _central.CustomerHealth
                .AsNoTracking()
                .Where(h => h.FKCusId == customerId && h.IsActive)
                .OrderByDescending(h => h.CreatedAt)
                .FirstOrDefaultAsync(ct);
        }

        // Client Package operations
        public async Task<List<ClientPackage>> GetPackageListAsync(CancellationToken ct = default)
        {
            return await _central.ClientPackages
                .AsNoTracking()
                .Where(cp => cp.IsActive)
                .OrderBy(cp => cp.PackageName)
                .ToListAsync(ct);
        }

        public async Task<ClientPackage?> GetPackageByIdAsync(Guid id, CancellationToken ct = default)
        {
            return await _central.ClientPackages
                .AsNoTracking()
                .FirstOrDefaultAsync(cp => cp.ClientPackageId == id && cp.IsActive, ct);
        }

        public async Task<List<ClientBusiness>> GetActiveGymsAsync(CancellationToken ct = default)
        {
            return await _central.ClientBusiness
                .Where(x => x.IsActive == true
                            && x.Latitude != null
                            && x.Longitude != null)
                .ToListAsync(ct);
        }

        public async Task<List<WorkoutPlanDto>> GetWorkoutPlansByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.WorkoutPlans
                .AsNoTracking()
                .Include(wp => wp.WorkoutCategory)
                .Include(wp => wp.CustomerWorkouts.Where(cw => cw.IsActive))
                    .ThenInclude(cw => cw.TenantCustomerLink)
                .Where(wp => wp.IsActive && wp.FKClientId == clientId)
                .OrderByDescending(wp => wp.CreateDate)
                .Select(wp => new WorkoutPlanDto
                {
                    WorkoutPlanId = wp.WorkoutPlanId,
                    Name = wp.WorkoutPlanName,
                    Description = wp.Description,
                    IsTemplate = wp.IsTemplate,
                    WorkoutCategoryId = wp.FKWorkoutCategoryId,
                    WorkoutCategoryName = wp.WorkoutCategory != null ? wp.WorkoutCategory.WorkoutCategoryName : "",
                    ClientId = wp.FKClientId,
                    ClientName = null,
                    IsActive = wp.IsActive,
                    CreateDate = wp.CreateDate,
                    AssignedCustomers = wp.CustomerWorkouts
                        .Where(cw => cw.IsActive)
                        .Select(cw => new CustomerWorkoutDto
                        {
                            CustomerWorkoutId = cw.CustomerWorkoutId,
                            CustomerId = cw.FKCustomerId,
                            CustomerName = cw.TenantCustomerLink != null ? cw.TenantCustomerLink.DisplayName : null,
                            WorkoutPlanId = cw.FKWorkoutPlanId,
                            IsActive = cw.IsActive,
                            IsFollowing = cw.IsFollowing,
                            AssignedDate = cw.AssignedDate,
                            CompletedDate = cw.CompletedDate,
                            Notes = cw.Notes
                        }).ToList()
                })
                .ToListAsync(ct);
        }

        public async Task<WorkoutPlanDetailDto?> GetWorkoutPlanByIdForClientAsync(Guid workoutPlanId, Guid clientId, CancellationToken ct = default)
        {
            var workoutPlan = await _dbContext.WorkoutPlans
                .AsNoTracking()
                .Include(wp => wp.WorkoutCategory)
                .Include(wp => wp.CustomerWorkouts.Where(cw => cw.IsActive))
                    .ThenInclude(cw => cw.TenantCustomerLink)
                .Include(wp => wp.WorkoutItems)
                .FirstOrDefaultAsync(wp => wp.WorkoutPlanId == workoutPlanId && wp.FKClientId == clientId && wp.IsActive, ct);

            if (workoutPlan == null)
                return null;

            return new WorkoutPlanDetailDto
            {
                WorkoutPlanId = workoutPlan.WorkoutPlanId,
                Name = workoutPlan.WorkoutPlanName,
                Description = workoutPlan.Description,
                IsTemplate = workoutPlan.IsTemplate,
                WorkoutCategoryId = workoutPlan.FKWorkoutCategoryId,
                WorkoutCategoryName = workoutPlan.WorkoutCategory != null ? workoutPlan.WorkoutCategory.WorkoutCategoryName : "",
                ClientId = workoutPlan.FKClientId,
                ClientName = null,
                IsActive = workoutPlan.IsActive,
                CreateDate = workoutPlan.CreateDate,
                WorkoutItems = workoutPlan.WorkoutItems
                    .Where(wi => wi.IsActive)
                    .Select(wi => new WorkoutItemDto
                    {
                        WorkoutItemId = wi.WorkoutItemId,
                        ExerciseDay = wi.ExerciseDay,
                        Description = wi.Description,
                        Exercises = wi.Exercises,
                        HowToDo = wi.HowToDo,
                        DoURL = wi.DoURL,
                        Duration = wi.Duration,
                        WormUp = wi.WormUp,
                        Cooldown = wi.Cooldown,
                        WorkoutPlanId = wi.FKWorkoutPlanId,
                        IsActive = wi.IsActive,
                        CreateDate = wi.CreateDate,
                        UpdatedAt = wi.UpdatedAt
                    })
                    .ToList(),
                AssignedCustomers = workoutPlan.CustomerWorkouts
                    .Where(cw => cw.IsActive)
                    .Select(cw => new CustomerWorkoutDto
                    {
                        CustomerWorkoutId = cw.CustomerWorkoutId,
                        CustomerId = cw.FKCustomerId,
                        CustomerName = cw.TenantCustomerLink != null ? cw.TenantCustomerLink.DisplayName : null,
                        WorkoutPlanId = cw.FKWorkoutPlanId,
                        IsActive = cw.IsActive,
                        IsFollowing = cw.IsFollowing,
                        AssignedDate = cw.AssignedDate,
                        CompletedDate = cw.CompletedDate,
                        Notes = cw.Notes
                    }).ToList()
            };
        }

        public async Task<List<MealPlanDto>> GetMealPlansByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.MealPlans
                .AsNoTracking()
                .Include(mp => mp.MealCategory)
                .Include(mp => mp.CustomerMeals.Where(cm => cm.IsActive))
                    .ThenInclude(cm => cm.TenantCustomerLink)
                .Where(mp => mp.IsActive && mp.FKClientId == clientId)
                .OrderByDescending(mp => mp.CreateDate)
                .Select(mp => new MealPlanDto
                {
                    MealPlanId = mp.MealPlanId,
                    Name = mp.Name,
                    Description = mp.Description,
                    IsTemplate = mp.IsTemplate,
                    MealCategoryId = mp.FKMealCategoryId,
                    MealCategoryName = mp.MealCategory != null ? mp.MealCategory.Name : "",
                    ClientId = mp.FKClientId,
                    ClientName = null,
                    IsActive = mp.IsActive,
                    CreateDate = mp.CreateDate,
                    AssignedCustomers = mp.CustomerMeals
                        .Where(cm => cm.IsActive)
                        .Select(cm => new CustomerMealDto
                        {
                            CustomerMealId = cm.CustomerMealId,
                            CustomerId = cm.FKCustomerId,
                            CustomerName = cm.TenantCustomerLink != null ? cm.TenantCustomerLink.DisplayName : null,
                            MealPlanId = cm.FKMealPlanId,
                            IsActive = cm.IsActive,
                            IsFollowing = cm.IsFollowing,
                            AssignedDate = cm.AssignedDate,
                            CompletedDate = cm.CompletedDate,
                            Notes = cm.Notes
                        }).ToList()
                })
                .ToListAsync(ct);
        }

        public async Task<MealPlanDetailDto?> GetMealPlanByIdForClientAsync(Guid mealPlanId, Guid clientId, CancellationToken ct = default)
        {
            var mealPlan = await _dbContext.MealPlans
                .AsNoTracking()
                .Include(mp => mp.MealCategory)
                .Include(mp => mp.CustomerMeals.Where(cm => cm.IsActive))
                    .ThenInclude(cm => cm.TenantCustomerLink)
                .Include(mp => mp.MealItems)
                .FirstOrDefaultAsync(mp => mp.MealPlanId == mealPlanId && mp.FKClientId == clientId && mp.IsActive, ct);

            if (mealPlan == null)
                return null;

            var mealItems = mealPlan.MealItems
                .Where(mi => mi.IsActive)
                .Select(mi => new MealDto
                {
                    MealId = mi.MealId,
                    Name = mi.Name,
                    Description = mi.Description,
                    Calories = mi.Calories,
                    Protein = mi.Protein,
                    Carbohydrates = mi.Carbohydrates,
                    Fats = mi.Fats,
                    ServingSize = mi.ServingSize,
                    MealItems = mi.MealItems,
                    PreparationNotes = mi.PreparationNotes,
                    MealPlanId = mi.FKMealPlanId,
                    IsActive = mi.IsActive,
                    CreateDate = mi.CreateDate
                })
                .ToList();

            return new MealPlanDetailDto
            {
                MealPlanId = mealPlan.MealPlanId,
                Name = mealPlan.Name,
                Description = mealPlan.Description,
                IsTemplate = mealPlan.IsTemplate,
                MealCategoryId = mealPlan.FKMealCategoryId,
                MealCategoryName = mealPlan.MealCategory != null ? mealPlan.MealCategory.Name : "",
                ClientId = mealPlan.FKClientId,
                ClientName = null,
                IsActive = mealPlan.IsActive,
                CreateDate = mealPlan.CreateDate,
                MealItems = mealItems,
                AssignedCustomers = mealPlan.CustomerMeals
                    .Where(cm => cm.IsActive)
                    .Select(cm => new CustomerMealDto
                    {
                        CustomerMealId = cm.CustomerMealId,
                        CustomerId = cm.FKCustomerId,
                        CustomerName = cm.TenantCustomerLink != null ? cm.TenantCustomerLink.DisplayName : null,
                        MealPlanId = cm.FKMealPlanId,
                        IsActive = cm.IsActive,
                        IsFollowing = cm.IsFollowing,
                        AssignedDate = cm.AssignedDate,
                        CompletedDate = cm.CompletedDate,
                        Notes = cm.Notes
                    }).ToList(),
                TotalCalories = mealItems.Sum(mi => mi.Calories ?? 0),
                TotalProtein = mealItems.Sum(mi => mi.Protein ?? 0),
                TotalCarbohydrates = mealItems.Sum(mi => mi.Carbohydrates ?? 0),
                TotalFats = mealItems.Sum(mi => mi.Fats ?? 0)
            };
        }

        public async Task<List<CustomerCards>> GetCardsByClientIdAsync(Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.CustomerCards
                .AsNoTracking()
                .Where(c => c.FK_ClientId == clientId && c.IsActive)
                .ToListAsync(ct);
        }

        // Scoped to a single member, and to the caller's own gym — used by the dashboard to show
        // whether a member has a consented card before offering the auto-charge toggle.
        public async Task<List<CustomerCards>> GetCardsByCustomerForClientAsync(Guid customerId, Guid clientId, CancellationToken ct = default)
        {
            return await _dbContext.CustomerCards
                .AsNoTracking()
                .Where(c => c.FK_CustomerId == customerId && c.FK_ClientId == clientId && c.IsActive)
                .ToListAsync(ct);
        }
    }
}