Core_Be/Repos/CustomerSubscriptionRepository.cs

using Fitness_core_BE.Interfaces;
using Fitness_core_BE.Models;
using Microsoft.EntityFrameworkCore;

namespace Fitness_core_BE.Repos
{
    public class CustomerSubscriptionRepository : ICustomerSubscriptionRepository
    {
        private readonly TenantDbContext _context;
        private readonly CentralDbContext _central;

        public CustomerSubscriptionRepository(TenantDbContext context, CentralDbContext central)
        {
            _context = context;
            _central = central;
        }

        public async Task AddCustomerSubscriptionAsync(
            CustomerSubscription subscription,
            CustomerSubItem initialSubItem,
            SubscriptionMember primaryMember,
            IReadOnlyCollection<SubscriptionInstallment> installments,
            CancellationToken ct = default)
        {
            await _context.CustomerSubscriptions.AddAsync(subscription, ct);
            await _context.CustomerSubItems.AddAsync(initialSubItem, ct);
            await _context.SubscriptionMembers.AddAsync(primaryMember, ct);
            await _context.SubscriptionInstallments.AddRangeAsync(installments, ct);
        }

        public async Task<CustomerPlan?> GetPlanByIdAsync(
            Guid customerPlanId,
            CancellationToken ct = default)
        {
            return await _context.CustomerPlans.FindAsync(
                new object[] { customerPlanId }, ct);
        }

        public Task<bool> IsActiveCustomerAsync(
            Guid customerId,
            CancellationToken ct = default)
        {
            return _central.Customer.AnyAsync(
                customer => customer.CusId == customerId && customer.IsActive, ct);
        }

        public Task<List<CustomerSubItem>> GetSubItemsAsync(
            Guid subscriptionId,
            bool activeOnly,
            bool newestFirst,
            CancellationToken ct = default)
        {
            var query = _context.CustomerSubItems
                .AsNoTracking()
                .Where(item => item.FKCustomerSubscriptionId == subscriptionId);

            if (activeOnly)
                query = query.Where(item => item.IsActive);

            query = newestFirst
                ? query.OrderByDescending(item => item.CreatedAt)
                : query.OrderBy(item => item.CreatedAt);

            return query.ToListAsync(ct);
        }

        public async Task AddSubItemAsync(
            CustomerSubItem subItem,
            CancellationToken ct = default)
        {
            await _context.CustomerSubItems.AddAsync(subItem, ct);
        }

        public Task<CustomerInvoice?> GetLatestInvoiceAsync(
            Guid subscriptionId,
            CancellationToken ct = default)
        {
            // IsActive filter: once an unapproved renewal invoice exists for this subscription,
            // "latest" must still resolve to the real, still-collectible prior invoice -- not the
            // pending-approval one. Defaults true for every other invoice (no-op elsewhere).
            // RolledOver is excluded too: it's superseded by whatever invoice carried its balance
            // forward, so it's never "the current invoice" for display/payment purposes.
            return _context.CustomerInvoices
                .AsNoTracking()
                .Where(invoice =>
                    invoice.FKCustomerSubscriptionId == subscriptionId &&
                    invoice.Status != "Cancelled" &&
                    invoice.Status != "RolledOver" &&
                    invoice.IsActive)
                .OrderByDescending(invoice => invoice.CustomerInvoiceDate)
                .FirstOrDefaultAsync(ct);
        }

        public async Task<decimal> GetApprovedPaymentTotalAsync(
            Guid invoiceId,
            CancellationToken ct = default)
        {
            return await _context.PaymentTransactions
                .AsNoTracking()
                .Where(payment =>
                    payment.FK_CustomerInvoiceId == invoiceId &&
                    payment.IsApproved == true &&
                    payment.IsActive == true)
                .Select(payment => (decimal?)payment.PayAmount)
                .SumAsync(ct) ?? 0m;
        }

        public Task<CustomerCards?> GetCardByIdAsync(
            Guid cardId,
            CancellationToken ct = default)
        {
            return _context.CustomerCards
                .AsNoTracking()
                .FirstOrDefaultAsync(card => card.JobId == cardId, ct);
        }

        public async Task<CustomerSubscription?> GetCustomerSubscriptionById(Guid subscriptionId)
        {
            return await _context.CustomerSubscriptions
                .Include(s => s.TenantCustomerLink)
                .Include(s => s.CustomerPlan)
                .Include(s => s.SubscriptionMembers)
                    .ThenInclude(m => m.TenantCustomerLink)
                .Include(s => s.SubscriptionInstallments)
                .FirstOrDefaultAsync(s => s.SubscriptionId == subscriptionId);
        }

        public async Task<List<CustomerSubscription>> GetCustomerSubscriptionsByCustomerId(Guid customerId)
        {
            return await _context.CustomerSubscriptions
                .Include(s => s.CustomerPlan)
                .Where(s => s.FKCustomerId == customerId)
                .OrderByDescending(s => s.CreatedAt)
                .ToListAsync();
        }

        public async Task<CustomerSubscription?> GetActiveSubscriptionByCustomerId(Guid customerId)
        {
            return await _context.CustomerSubscriptions
                .Include(s => s.TenantCustomerLink)
                .Include(s => s.CustomerPlan)
                .Where(s => s.FKCustomerId == customerId && s.IsActive)
                .OrderByDescending(s => s.CreatedAt)
                .FirstOrDefaultAsync();
        }

        // SubscriptionMembers always has a row for the primary too (CustomerSubscriptionFactory
        // adds it alongside the CustomerSubscription itself), so this one query covers both
        // "primary somewhere" and "secondary/group member somewhere" in a single check.
        public Task<bool> HasAnyActiveSubscriptionAsync(Guid customerId, CancellationToken ct = default)
        {
            return _context.SubscriptionMembers.AnyAsync(m =>
                m.FKCustomerId == customerId && m.IsActive && m.Subscription.IsActive, ct);
        }

        // ---------- Group membership ----------

        public async Task<SubscriptionMember> AddSubscriptionMemberAsync(SubscriptionMember member)
        {
            member.SubscriptionMemberId = Guid.NewGuid();
            member.JoinedAt = DateTime.UtcNow;
            member.IsActive = true;
            _context.SubscriptionMembers.Add(member);

            return member;
        }

        public async Task<int> GetActiveMemberCountAsync(Guid subscriptionId)
        {
            // Unassigned members still hold their row (so they stay listed and can be put
            // back), but they aren't using the plan -- so they don't occupy a MemberLimit slot.
            return await _context.SubscriptionMembers
                .CountAsync(m => m.FKSubscriptionId == subscriptionId && m.IsActive && m.IsPlanAssigned);
        }

        public async Task<SubscriptionMember?> GetSubscriptionMemberAsync(Guid subscriptionMemberId)
        {
            return await _context.SubscriptionMembers
                .Include(m => m.Subscription)
                    .ThenInclude(s => s.CustomerPlan)
                .FirstOrDefaultAsync(m => m.SubscriptionMemberId == subscriptionMemberId);
        }

        public async Task<SubscriptionMember?> GetActiveSubscriptionMemberByCustomerAsync(Guid subscriptionId, Guid customerId)
        {
            return await _context.SubscriptionMembers
                .FirstOrDefaultAsync(m => m.FKSubscriptionId == subscriptionId && m.FKCustomerId == customerId && m.IsActive);
        }

        /// <summary>Subscriptions where the customer is an active member (primary OR group secondary).</summary>
        public async Task<List<CustomerSubscription>> GetSubscriptionsForCustomerAsync(Guid customerId)
        {
            return await _context.SubscriptionMembers
                .AsNoTracking()
                .Where(m => m.FKCustomerId == customerId && m.IsActive && m.Subscription.IsActive)
                .Include(m => m.Subscription).ThenInclude(s => s.CustomerPlan)
                .Include(m => m.Subscription).ThenInclude(s => s.SubscriptionMembers).ThenInclude(sm => sm.TenantCustomerLink)
                .Include(m => m.Subscription).ThenInclude(s => s.SubscriptionInstallments)
                .Select(m => m.Subscription)
                .OrderByDescending(s => s.OnboardDate)
                .ToListAsync();
        }

        public async Task<List<SubscriptionInstallment>> GetInstallmentsAsync(Guid subscriptionId)
        {
            return await _context.SubscriptionInstallments
                .Where(i => i.FKSubscriptionId == subscriptionId && i.IsActive)
                .OrderBy(i => i.InstallmentNo)
                .ToListAsync();
        }

        public async Task AdvanceRenewalDateAsync(Guid subscriptionId, DateTime newRenewalDate, CancellationToken ct = default)
        {
            var subscription = await _context.CustomerSubscriptions
                .FirstOrDefaultAsync(s => s.SubscriptionId == subscriptionId, ct);
            if (subscription == null)
                return;

            subscription.RenewalDate = newRenewalDate;
        }

        public async Task DeactivatePriorInstallmentsAsync(Guid subscriptionId, CancellationToken ct = default)
        {
            // Hard delete, not soft-deactivate: TenantDbContext has a unique index on
            // (FKSubscriptionId, InstallmentNo), so a soft-deactivated row would still occupy
            // its InstallmentNo and collide with the new cycle's schedule restarting at 1 on the
            // same subscription. The payment record of truth is PaymentTransactions (linked to
            // the invoice, not the installment), so this loses no financial history.
            var installments = await _context.SubscriptionInstallments
                .Where(i => i.FKSubscriptionId == subscriptionId)
                .ToListAsync(ct);
            _context.SubscriptionInstallments.RemoveRange(installments);
        }

        public async Task AddInstallmentsAsync(IReadOnlyCollection<SubscriptionInstallment> installments, CancellationToken ct = default)
        {
            await _context.SubscriptionInstallments.AddRangeAsync(installments, ct);
        }
    }
}