import { InvoicePdfService } from "@api/shared/invoice/invoice-pdf.service";
import { SystemLogService } from "@api/shared/services";
import {
  localStripeCancelAtPeriodEndFromSubscription,
  StripeService,
} from "@api/shared/stripe/services/stripe.service";
import { StripePaymentService } from "@api/shared/stripe/services/stripe-payment.service";
import { optionalEnv, requireEnv } from "@bish-nest/core";
import {
  BadRequestException,
  Injectable,
  Logger,
  NotFoundException,
  ServiceUnavailableException,
} from "@nestjs/common";
import {
  InvoiceStatus,
  InvoiceType,
  Package,
  PackageType,
  SubscriptionStatus,
  SystemLogEntityType,
} from "@prisma/client";
import { Decimal } from "@prisma/client/runtime/library";
import { plainToInstance } from "class-transformer";
import Stripe from "stripe";
import {
  buildInvoiceBillingAmounts,
  invoiceBillingAmountsToDbFields,
} from "../../../config/billing.config";
import {
  addBillingDays,
  calculateNextBillingDateFromAnchor,
  calculateTrialNextBillingDate,
  computeMidCycleSamePackageLicenseIncreaseCharge,
  getBillingCycleMultiplier,
  getBillingCyclePeriodDays,
  inferPaidPeriodStartFromNextBilling,
  nextBillingAfterImmediatePeriodStart,
  startOfUtcDay,
} from "../../../config/billing-cycle";
import { CLBaseService } from "../../shared/services/base.service";
import {
  SubscriptionBillingHistoryResponseDto,
  SubscriptionCancelAtPeriodEndResponseDto,
  SubscriptionConfirmRequestDto,
  SubscriptionConfirmResponseDto,
  SubscriptionOverviewResponseDto,
  SubscriptionPackageDto,
  SubscriptionPaymentMethodResponseDto,
  SubscriptionPreviewRequestDto,
  SubscriptionPreviewResponseDto,
  SubscriptionRetryPaymentResponseDto,
  SubscriptionStripeBillingCreateResponseDto,
} from "./dto";

@Injectable()
export class CLSubscriptionService extends CLBaseService {
  private readonly logger = new Logger(CLSubscriptionService.name);
  private readonly portalReturnUrl = (() => {
    const portal = optionalEnv("RECALLASSESS_PORTAL_URL", "");
    if (portal !== "") {
      return portal;
    }
    return requireEnv("FRONTEND_URL");
  })();

  constructor(
    private stripeService: StripeService,
    private stripePaymentService: StripePaymentService,
    private readonly invoicePdfService: InvoicePdfService,
    private readonly systemLogService: SystemLogService,
  ) {
    super();
  }

  private async getStripeSubscriptionOverviewFields(stripeSubscriptionId: string | null): Promise<{
    stripe_subscription_id: string | null;
    stripe_subscription_status: string | null;
    stripe_current_period_start: string | null;
    stripe_current_period_end: string | null;
    stripe_trial_end: string | null;
    stripe_next_charge_date: string | null;
    stripe_cancel_at_period_end: boolean | null;
    stripe_collection_method: string | null;
    stripe_next_invoice_amount: number | null;
    stripe_next_invoice_currency: string | null;
    /**
     * True iff the customer has paid a real (non-zero) invoice for the current
     * subscription period. This is the DEFINITIVE signal that the customer is
     * a paying Active subscriber, not on a trial. Frontend uses this in place
     * of duration-based heuristics so the UI is correct regardless of how
     * trial_end was set (signup, expired-recovery, or admin-edited).
     *
     * Computed by checking subscription.latest_invoice.amount_paid > 0.
     * Null when subscription / Stripe lookup fails — frontend falls back to
     * the duration heuristic in that case.
     */
    has_paid_current_period: boolean | null;
  }> {
    const base = {
      stripe_subscription_id: stripeSubscriptionId,
      stripe_subscription_status: null as string | null,
      stripe_current_period_start: null as string | null,
      stripe_current_period_end: null as string | null,
      stripe_trial_end: null as string | null,
      stripe_next_charge_date: null as string | null,
      stripe_cancel_at_period_end: null as boolean | null,
      stripe_collection_method: null as string | null,
      stripe_next_invoice_amount: null as number | null,
      stripe_next_invoice_currency: null as string | null,
      has_paid_current_period: null as boolean | null,
    };
    if (!stripeSubscriptionId || !this.stripeService.isConfigured()) {
      return base;
    }
    try {
      // Stripe typings for API version may omit period fields; runtime always includes them.
      // We also pull `trial_end` so the API can correctly report the actual next-charge
      // date for trialing subscriptions (where the next charge happens at trial_end,
      // not at current_period_end + interval).
      // `latest_invoice` is expanded so we can definitively detect paid periods —
      // amount_paid > 0 means the customer has actually paid, regardless of what
      // status string Stripe reports. This is what powers `has_paid_current_period`.
      const sub = (await this.stripeService.retrieveSubscriptionWithLatestInvoice(
        stripeSubscriptionId,
      )) as Stripe.Subscription & {
        current_period_start?: number;
        current_period_end?: number;
      };
      const upcoming = await this.stripeService.retrieveUpcomingInvoiceAmountForSubscription(sub.id);

      const trialEndIso = sub.trial_end ? new Date(sub.trial_end * 1000).toISOString() : null;
      const periodEndIso = sub.current_period_end ? new Date(sub.current_period_end * 1000).toISOString() : null;

      // The "next charge date" is the date the customer's card will next be charged.
      // For trialing subscriptions this is trial_end (Stripe charges at trial cutoff).
      // For active subscriptions it's current_period_end (start of next billing period).
      // Frontend should prefer this over current_period_end for "next renewal" labelling.
      const isTrialing = sub.status === "trialing";
      const trialEndUnix = sub.trial_end ?? 0;
      const nowUnix = Math.floor(Date.now() / 1000);
      const trialIsInFuture = trialEndUnix > nowUnix;
      const nextChargeDateIso = isTrialing && trialIsInFuture && trialEndIso ? trialEndIso : periodEndIso;

      // ── DEFINITIVE PAID DETECTION ────────────────────────────────────────────
      // If latest_invoice exists AND amount_paid > 0, the customer has paid for
      // this period. This is true even when status == "trialing" (the backend's
      // expired-recovery flow uses trialing as a "paid through this date" flag).
      //
      // Falls back to null if invoice can't be loaded — frontend then uses the
      // duration heuristic so UI doesn't go blank.
      let hasPaidCurrentPeriod: boolean | null = null;
      const latest = sub.latest_invoice;
      if (latest && typeof latest === "object") {
        const inv = latest as Stripe.Invoice;
        const amountPaid = inv.amount_paid ?? 0;
        const invoiceStatus = inv.status ?? null;
        // `paid` status OR amount_paid > 0 — both signal a real payment
        hasPaidCurrentPeriod = amountPaid > 0 || invoiceStatus === "paid";
        this.logger.debug(
          `getStripeSubscriptionOverviewFields: sub=${sub.id} status=${sub.status} ` +
            `latest_invoice=${inv.id} amount_paid=${amountPaid} status=${invoiceStatus} ` +
            `→ has_paid_current_period=${hasPaidCurrentPeriod}`,
        );
      } else {
        this.logger.debug(
          `getStripeSubscriptionOverviewFields: sub=${sub.id} has no expanded latest_invoice; ` +
            `has_paid_current_period=null (frontend will use duration heuristic)`,
        );
      }

      return {
        stripe_subscription_id: sub.id,
        stripe_subscription_status: sub.status,
        stripe_current_period_start: sub.current_period_start
          ? new Date(sub.current_period_start * 1000).toISOString()
          : null,
        stripe_current_period_end: periodEndIso,
        stripe_trial_end: trialEndIso,
        stripe_next_charge_date: nextChargeDateIso,
        stripe_cancel_at_period_end: localStripeCancelAtPeriodEndFromSubscription(sub),
        stripe_collection_method: sub.collection_method ?? null,
        stripe_next_invoice_amount: upcoming.amount,
        stripe_next_invoice_currency: upcoming.currency,
        has_paid_current_period: hasPaidCurrentPeriod,
      };
    } catch (error) {
      this.logger.warn(
        `Overview: could not load Stripe subscription ${stripeSubscriptionId}: ${
          error instanceof Error ? error.message : String(error)
        }`,
      );
      return base;
    }
  }

  /**
   * Get current subscription overview for a company
   */
  async getSubscriptionOverview(companyId: number): Promise<SubscriptionOverviewResponseDto> {
    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: {
        plan: true,
        country: true,
        email: true,
        stripe_customer_id: true,
        stripe_subscription_id: true,
        is_subscription_expiry: true,
      },
    });

    if (!company) {
      throw new NotFoundException("Company not found");
    }

    let subscription = await this.prisma.client.subscription.findFirst({
      where: {
        company_id: companyId,
        is_current: true,
      },
      include: {
        package: true,
        previousSubscription: { include: { package: true } },
      },
    });

    const stripeSubFields = await this.getStripeSubscriptionOverviewFields(company.stripe_subscription_id);

    // Expiry job marks overdue subs EXPIRED and clears is_current — there is then no "current" row.
    // Use the latest subscription row so trial→Startup pricing and license floors match recovery charging.
    if (!subscription?.package) {
      subscription = await this.prisma.client.subscription.findFirst({
        where: { company_id: companyId },
        orderBy: { id: "desc" },
        include: { package: true, previousSubscription: { include: { package: true } } },
      });
      if (subscription?.package) {
        this.logger.debug(
          `Overview for company ${companyId}: using latest subscription ${subscription.id} (no is_current row).`,
        );
      }
    }

    if (!subscription?.package) {
      this.logger.warn(
        `No local subscription row found for company ${companyId}; returning fallback overview from company/Stripe data.`,
      );

      let paymentMethodLast4: string | null = null;
      let paymentMethodBrand: string | null = null;
      let paymentMethodExpMonth: number | null = null;
      let paymentMethodExpYear: number | null = null;
      const nextBillingDateIso: string | null = stripeSubFields.stripe_current_period_end;

      if (company.stripe_customer_id && this.stripeService.isConfigured()) {
        try {
          const paymentMethod = await this.stripeService.getCustomerDefaultPaymentMethod(
            company.stripe_customer_id,
            { companyId, email: company.email },
          );
          paymentMethodLast4 = paymentMethod.last4;
          paymentMethodBrand = paymentMethod.brand;
          paymentMethodExpMonth = paymentMethod.exp_month;
          paymentMethodExpYear = paymentMethod.exp_year;
        } catch (error) {
          this.logger.warn(
            `Fallback overview: unable to retrieve Stripe customer details for company ${companyId}: ${
              error instanceof Error ? error.message : String(error)
            }`,
          );
        }
      }

      const startingPlan = company.plan ?? null;
      const fallbackColor =
        startingPlan === "PRIVATE_VIP_TRIAL" || startingPlan === "FREE_TRIAL" ? "purple" : "orange";
      const fallbackIcon =
        startingPlan === "PRIVATE_VIP_TRIAL" ? "pi-star" : startingPlan === "FREE_TRIAL" ? "pi-clock" : "pi-box";

      return plainToInstance(SubscriptionOverviewResponseDto, {
        plan_type: startingPlan,
        package_name: startingPlan ?? "Subscription",
        license_count: 0,
        price_per_license: null,
        monthly_subtotal: 0,
        next_billing_date: nextBillingDateIso,
        next_billing_amount: null,
        company_country: company.country ?? null,
        payment_method_last4: paymentMethodLast4,
        payment_method_brand: paymentMethodBrand,
        payment_method_exp_month: paymentMethodExpMonth,
        payment_method_exp_year: paymentMethodExpYear,
        color: fallbackColor,
        icon: fallbackIcon,
        billing_cycle: "QUARTERLY",
        is_subscription_expiry: company.is_subscription_expiry ?? false,
        has_stripe_customer: !!company.stripe_customer_id,
        ...stripeSubFields,
      });
    }

    let effectivePricePerLicense = this.toNumber(subscription.package.price_per_licence);
    let effectiveLicenseCount = subscription.license_count;
    let effectiveBillingCycle: string | null = subscription.billing_cycle;

    // For free-signup trial views, show projected paid billing values (Startup catalog price + license floor),
    // aligned with {@link StripePaymentService.runImmediateExpiredRecoveryCharge} trial-to-paid fallback.
    const isTrialLikePackage =
      !!subscription.package.is_trial_package ||
      subscription.package.package_type === "FREE_TRIAL" ||
      subscription.package.package_type === "PRIVATE_VIP_TRIAL";
    if (isTrialLikePackage) {
      const startupPackage = await this.prisma.client.package.findFirst({
        where: {
          package_type: "STARTUP",
          is_active: true,
          OR: [{ special_slug: null }, { special_slug: "" }],
        },
        select: {
          price_per_licence: true,
          minimum_license_required: true,
        },
      });
      const startupPricePerLicense = this.toNumber(startupPackage?.price_per_licence ?? null);
      if (startupPricePerLicense !== null && startupPricePerLicense > 0) {
        effectivePricePerLicense = startupPricePerLicense;
      }
      effectiveLicenseCount = Math.max(effectiveLicenseCount, startupPackage?.minimum_license_required ?? 1, 1);
      // Free-trial signups default to quarterly billing for projected post-trial charges.
      effectiveBillingCycle = "QUARTERLY";
    }

    const monthlySubtotal =
      effectivePricePerLicense !== null ? effectivePricePerLicense * effectiveLicenseCount : 0;

    let nextBillingAmount: number | null = null;
    if (effectiveBillingCycle) {
      const billingCycleMultiplier = getBillingCycleMultiplier(effectiveBillingCycle);
      nextBillingAmount = monthlySubtotal * billingCycleMultiplier;
    }

    let paymentMethodLast4: string | null = null;
    let paymentMethodBrand: string | null = null;
    let paymentMethodExpMonth: number | null = null;
    let paymentMethodExpYear: number | null = null;

    if (company.stripe_customer_id && this.stripeService.isConfigured()) {
      try {
        const paymentMethod = await this.stripeService.getCustomerDefaultPaymentMethod(
          company.stripe_customer_id,
          { companyId, email: company.email },
        );
        paymentMethodLast4 = paymentMethod.last4;
        paymentMethodBrand = paymentMethod.brand;
        paymentMethodExpMonth = paymentMethod.exp_month;
        paymentMethodExpYear = paymentMethod.exp_year;

        // Orphan-recovery: if the card was found on a different Stripe customer
        // than the one configured on the company row, repoint company.stripe_customer_id
        // so future Portal sessions / charges target the correct customer. Best-effort
        // — failure here doesn't block the response.
        if (
          paymentMethod.effective_customer_id &&
          paymentMethod.effective_customer_id !== company.stripe_customer_id
        ) {
          this.prisma.client.company
            .update({
              where: { id: companyId },
              data: { stripe_customer_id: paymentMethod.effective_customer_id },
            })
            .then(() => {
              this.logger.warn(
                `Reconciled stripe_customer_id for company ${companyId}: ` +
                  `${company.stripe_customer_id} → ${paymentMethod.effective_customer_id} ` +
                  `(card found via orphan-recovery email match)`,
              );
            })
            .catch((reconErr: unknown) => {
              this.logger.error(
                `Failed to reconcile stripe_customer_id for company ${companyId}: ` +
                  (reconErr instanceof Error ? reconErr.message : String(reconErr)),
              );
            });
        }

        this.logger.debug(
          `Payment method retrieved for company ${companyId}: ` +
            `last4=${paymentMethodLast4}, brand=${paymentMethodBrand}, ` +
            `exp_month=${paymentMethodExpMonth}, exp_year=${paymentMethodExpYear}`,
        );
      } catch (error) {
        this.logger.warn(
          `Unable to retrieve payment method/invoice settings for company ${companyId}: ${String(
            (error as Error)?.message ?? error,
          )}`,
        );
        // Log the full error for debugging
        this.logger.debug(`Payment method retrieval error details:`, error);
      }
    }

    const packageType = subscription.package.package_type;
    const colorMap: Record<PackageType, string> = {
      FREE_TRIAL: "purple",
      STARTUP: "orange",
      GROWTH: "blue",
      ENTERPRISE: "green",
      PRIVATE_VIP_TRIAL: "purple",
    };
    const iconMap: Record<PackageType, string> = {
      FREE_TRIAL: "pi-clock",
      STARTUP: "pi-users",
      GROWTH: "pi-chart-line",
      ENTERPRISE: "pi-star",
      PRIVATE_VIP_TRIAL: "pi-star",
    };
    const color = colorMap[packageType] ?? "orange";
    const icon = iconMap[packageType] ?? "pi-box";

    if (
      company.stripe_subscription_id &&
      stripeSubFields.stripe_cancel_at_period_end !== null &&
      (!subscription.stripe_subscription_id ||
        subscription.stripe_subscription_id === company.stripe_subscription_id)
    ) {
      const nextCancel = stripeSubFields.stripe_cancel_at_period_end === true;
      if (subscription.stripe_cancel_at_period_end !== nextCancel) {
        await this.prisma.client.subscription.update({
          where: { id: subscription.id },
          data: { stripe_cancel_at_period_end: nextCancel },
        });
      }
    }

    const nextBillingDateIso = subscription.next_billing_date
      ? subscription.next_billing_date.toISOString()
      : null;

    // VIP offer applies to the first Stripe invoice after a VIP trial ends.
    // This can be:
    // - The current local row is PRIVATE_VIP_TRIAL (trial will convert to paid pricing after `next_billing_date`)
    // - Or the current row is paid and the previous row was PRIVATE_VIP_TRIAL (trial -> paid upgrade row)
    // Align with Stripe sync logic (firstInvoiceAmountOffCents) so portal "Billing invoice" shows the discount line.
    const vipFirstInvoiceOfferApplies =
      !!subscription.next_billing_date &&
      (subscription.package.package_type === PackageType.PRIVATE_VIP_TRIAL ||
        subscription.previousSubscription?.package?.package_type === PackageType.PRIVATE_VIP_TRIAL);

    // Boolean API field (client UI can still display N/A when Stripe sub is missing).
    // When Stripe is connected, auto-payment is enabled iff cancel_at_period_end is false.
    const autoPaymentEnabled = company.stripe_subscription_id
      ? stripeSubFields.stripe_cancel_at_period_end === true
        ? false
        : true
      : false;

    return plainToInstance(SubscriptionOverviewResponseDto, {
      plan_type: subscription.package.package_type ?? null,
      package_name: subscription.package.name ?? null,
      license_count: effectiveLicenseCount,
      price_per_license: effectivePricePerLicense,
      monthly_subtotal: monthlySubtotal,
      next_billing_date: nextBillingDateIso,
      next_billing_amount: nextBillingAmount,
      company_country: company.country ?? null,
      payment_method_last4: paymentMethodLast4,
      payment_method_brand: paymentMethodBrand,
      payment_method_exp_month: paymentMethodExpMonth,
      payment_method_exp_year: paymentMethodExpYear,
      auto_payment_enabled: autoPaymentEnabled,
      color,
      icon,
      billing_cycle: effectiveBillingCycle,
      is_subscription_expiry: company.is_subscription_expiry ?? false,
      has_stripe_customer: !!company.stripe_customer_id,
      vip_first_invoice_offer_applies: vipFirstInvoiceOfferApplies,
      ...stripeSubFields,
    });
  }

  /**
   * Get all active packages with current plan highlighted
   */
  async getAvailablePackages(companyId: number): Promise<SubscriptionPackageDto[]> {
    const currentSubscription = await this.prisma.client.subscription.findFirst({
      where: {
        company_id: companyId,
        is_current: true,
      },
      select: {
        package_id: true,
      },
    });

    const packages = await this.prisma.client.package.findMany({
      where: {
        is_active: true,
        OR: [{ special_slug: null }, { special_slug: "" }],
      },
      orderBy: [
        {
          sort_order: "asc",
        },
        {
          license_count_start: "asc",
        },
      ],
    });

    return this.toDtoArray(
      packages.map((pkg) => this.enrichPackage(pkg, currentSubscription?.package_id ?? null)),
      SubscriptionPackageDto,
    );
  }

  /**
   * Preview the financial impact of changing subscription
   * (Currently a simplified calculation until Stripe integration is implemented)
   */
  async previewSubscriptionChange(
    companyId: number,
    dto: SubscriptionPreviewRequestDto,
  ): Promise<SubscriptionPreviewResponseDto> {
    const [currentSubscription, targetPackage, companyRow] = await Promise.all([
      this.prisma.client.subscription.findFirst({
        where: { company_id: companyId, is_current: true },
        include: { package: true },
      }),
      this.prisma.client.package.findUnique({
        where: { id: dto.package_id },
      }),
      this.prisma.client.company.findUnique({
        where: { id: companyId },
        select: { country: true },
      }),
    ]);

    if (!targetPackage || !targetPackage.is_active) {
      throw new NotFoundException("Target package not available");
    }

    this.validateLicenseCount(
      dto.license_count,
      targetPackage.license_count_start,
      targetPackage.license_count_end,
    );

    /** No current row or missing package: first paid (or trial) subscription — mirror {@link confirmSubscriptionChange} initial path. */
    if (!currentSubscription?.package) {
      const billingCycle = dto.billing_cycle || "QUARTERLY";
      const billingCycleMultiplier = getBillingCycleMultiplier(billingCycle);
      const targetPricePerLicense = this.toNumber(targetPackage.price_per_licence) ?? 0;
      const newMonthlySubtotal = dto.license_count * targetPricePerLicense;
      const isTargetTrialPackage =
        !!targetPackage.is_trial_package ||
        targetPackage.package_type === "FREE_TRIAL" ||
        targetPackage.package_type === "PRIVATE_VIP_TRIAL";
      const fullCycleCharge = isTargetTrialPackage ? 0 : newMonthlySubtotal * billingCycleMultiplier;
      const difference = fullCycleCharge;
      const differenceType =
        isTargetTrialPackage || difference > 0
          ? ("UPGRADE" as const)
          : difference === 0
            ? ("NO_CHANGE" as const)
            : ("DOWNGRADE" as const);
      const newNextBillingDate = isTargetTrialPackage
        ? calculateTrialNextBillingDate(targetPackage.trial_duration_days)
        : nextBillingAfterImmediatePeriodStart(billingCycle);

      return plainToInstance(SubscriptionPreviewResponseDto, {
        current_monthly_subtotal: 0,
        new_monthly_subtotal: newMonthlySubtotal,
        difference_amount: Number(difference.toFixed(2)),
        difference_type: differenceType,
        is_same_plan: false,
        current_package_name: null,
        current_package_features: null,
        new_package_features: targetPackage.features,
        current_next_billing_date: null,
        current_subscription_start_date: null,
        new_next_billing_date: newNextBillingDate.toISOString(),
        current_license_count: 0,
        new_license_count: dto.license_count,
        current_billing_cycle: null,
        new_billing_cycle: billingCycle,
        is_private_vip_trial_upgrade: false,
        is_trial_upgrade_before_end: false,
        discount_breakdown: null,
        gross_new_term_amount: isTargetTrialPackage ? null : Number(fullCycleCharge.toFixed(2)),
        remainder_credit_amount: null,
        mid_cycle_pricing_detail: null,
        mid_cycle_old_term_amount: null,
        mid_cycle_used_value_amount: null,
        mid_cycle_period_start_iso: null,
        mid_cycle_period_end_inclusive_iso: null,
        mid_cycle_upgrade_date_iso: null,
        company_country: companyRow?.country ?? null,
      });
    }

    const currentPricePerLicense = this.toNumber(currentSubscription.package.price_per_licence) ?? 0;
    const targetPricePerLicense = this.toNumber(targetPackage.price_per_licence) ?? 0;

    // Get billing cycle multipliers
    // Legacy rows may have null billing_cycle; treat as quarterly for calculations
    const billingCycle = dto.billing_cycle || currentSubscription.billing_cycle || "QUARTERLY";
    const billingCycleMultiplier = getBillingCycleMultiplier(billingCycle);
    const currentBillingCycleMultiplier = getBillingCycleMultiplier(
      currentSubscription.billing_cycle || "QUARTERLY",
    );

    // Calculate monthly subtotals (base monthly price)
    const currentMonthlySubtotal = currentSubscription.license_count * currentPricePerLicense;
    const newMonthlySubtotal = dto.license_count * targetPricePerLicense;

    // Check if it's the same plan (only license count change)
    const isSamePlan = currentSubscription.package_id === dto.package_id;
    const isBillingCycleChange = (currentSubscription.billing_cycle ?? "QUARTERLY") !== billingCycle;

    // Check for trial upgrade paths
    const isCurrentTrialPackage =
      !!currentSubscription.package.is_trial_package ||
      currentSubscription.package.package_type === "FREE_TRIAL" ||
      currentSubscription.package.package_type === "PRIVATE_VIP_TRIAL";
    const isTargetTrialPackage =
      !!targetPackage.is_trial_package ||
      targetPackage.package_type === "FREE_TRIAL" ||
      targetPackage.package_type === "PRIVATE_VIP_TRIAL";
    const isUpgradeFromAnyTrial = isCurrentTrialPackage && !isTargetTrialPackage;

    if (!isUpgradeFromAnyTrial) {
      this.assertNoPaidProductTierDowngrade(currentSubscription.package.package_type, targetPackage.package_type);
    }

    // Check for PRIVATE_VIP_TRIAL upgrade special pricing
    const isUpgradeFromPrivateVipTrial =
      currentSubscription.package.package_type === "PRIVATE_VIP_TRIAL" &&
      targetPackage.package_type !== "PRIVATE_VIP_TRIAL";

    let difference = 0;
    let discountBreakdown: string | null = null;
    let grossNewTermAmount: number | null = null;
    let remainderCreditAmount: number | null = null;
    let midCyclePricingDetail: string | null = null;
    let midCycleOldTermAmount: number | null = null;
    let midCycleUsedValueAmount: number | null = null;
    let midCyclePeriodStartIso: string | null = null;
    let midCyclePeriodEndInclusiveIso: string | null = null;
    let midCycleUpgradeDateIso: string | null = null;

    if (isUpgradeFromPrivateVipTrial) {
      // Apply PRIVATE_VIP_TRIAL upgrade discount: free trial cancelled on upgrade.
      // Same offer in all cases: Month 1 & 2 at 50% off, Month 3+ full price. Next billing from upgrade date.
      const monthlyPrice = newMonthlySubtotal;
      const multiplier = billingCycleMultiplier;

      let totalCharge = 0;
      const breakdownParts: string[] = [];

      totalCharge += monthlyPrice * 0.5; // Month 1: 50% off
      breakdownParts.push(`Month 1: 50% off ($${(monthlyPrice * 0.5).toFixed(2)})`);

      totalCharge += monthlyPrice * 0.5; // Month 2: 50% off
      breakdownParts.push(`Month 2: 50% off ($${(monthlyPrice * 0.5).toFixed(2)})`);

      if (multiplier > 2) {
        const remainingMonths = multiplier - 2;
        totalCharge += monthlyPrice * remainingMonths;
        breakdownParts.push(`Months 3-${multiplier}: Full price ($${monthlyPrice.toFixed(2)} each)`);
      }

      difference = totalCharge;
      discountBreakdown = breakdownParts.join(", ");

      this.logger.log(
        `Preview: PRIVATE_VIP_TRIAL upgrade - totalCharge=${totalCharge}, breakdown=${discountBreakdown}`,
      );
    } else {
      const now = new Date();
      const currentChargeAmount = currentMonthlySubtotal * currentBillingCycleMultiplier;
      const newChargeAmount = newMonthlySubtotal * billingCycleMultiplier;
      const isLicenseIncrease = dto.license_count > currentSubscription.license_count;
      const isPaidMidCycleLicenseBump =
        isSamePlan && !isBillingCycleChange && isLicenseIncrease && !isCurrentTrialPackage;

      if (isPaidMidCycleLicenseBump) {
        const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
          currentNextBilling: currentSubscription.next_billing_date,
          billingCycle,
          asOf: now,
          oldLicenseCount: currentSubscription.license_count,
          newLicenseCount: dto.license_count,
          pricePerLicense: targetPricePerLicense,
        });
        difference = mid.net;
        grossNewTermAmount = mid.grossNewTerm;
        remainderCreditAmount = mid.credit;
        midCyclePricingDetail =
          `Full new term $${mid.grossNewTerm.toFixed(2)} − unused credit $${mid.credit.toFixed(2)} ` +
          `(${mid.remainderDays}/${mid.periodDays} days left in term; credit vs ${mid.creditBasisDays}-day paid basis at ${currentSubscription.license_count} licenses) = $${mid.net.toFixed(2)}`;
        if (currentSubscription.next_billing_date) {
          const periodStartRaw = inferPaidPeriodStartFromNextBilling(
            currentSubscription.next_billing_date,
            billingCycle,
          );
          const periodStart = startOfUtcDay(periodStartRaw);
          const periodEndInclusive = startOfUtcDay(addBillingDays(periodStart, mid.periodDays - 1));
          const upgradeDay = startOfUtcDay(now);
          midCycleOldTermAmount = Number(mid.amountPaidOldTerm.toFixed(2));
          midCycleUsedValueAmount = Number(Math.max(0, mid.amountPaidOldTerm - mid.credit).toFixed(2));
          midCyclePeriodStartIso = periodStart.toISOString();
          midCyclePeriodEndInclusiveIso = periodEndInclusive.toISOString();
          midCycleUpgradeDateIso = upgradeDay.toISOString();
        }
      } else if (isSamePlan && isBillingCycleChange && isLicenseIncrease && !isCurrentTrialPackage) {
        const currentCycle = currentSubscription.billing_cycle || "QUARTERLY";
        const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
          currentNextBilling: currentSubscription.next_billing_date,
          billingCycle: currentCycle,
          newBillingCycle: billingCycle,
          asOf: now,
          oldLicenseCount: currentSubscription.license_count,
          newLicenseCount: dto.license_count,
          pricePerLicense: targetPricePerLicense,
        });
        difference = mid.net;
        grossNewTermAmount = mid.grossNewTerm;
        remainderCreditAmount = mid.credit;
        midCyclePricingDetail =
          `Full new term $${mid.grossNewTerm.toFixed(2)} − unused credit $${mid.credit.toFixed(2)} ` +
          `(${mid.remainderDays}/${mid.periodDays} days left in term; credit vs ${mid.creditBasisDays}-day paid basis at ${currentSubscription.license_count} licenses) = $${mid.net.toFixed(2)}`;
        if (currentSubscription.next_billing_date) {
          const periodStartRaw = inferPaidPeriodStartFromNextBilling(
            currentSubscription.next_billing_date,
            currentCycle,
          );
          const periodStart = startOfUtcDay(periodStartRaw);
          const periodEndInclusive = startOfUtcDay(addBillingDays(periodStart, mid.periodDays - 1));
          const upgradeDay = startOfUtcDay(now);
          midCycleOldTermAmount = Number(mid.amountPaidOldTerm.toFixed(2));
          midCycleUsedValueAmount = Number(Math.max(0, mid.amountPaidOldTerm - mid.credit).toFixed(2));
          midCyclePeriodStartIso = periodStart.toISOString();
          midCyclePeriodEndInclusiveIso = periodEndInclusive.toISOString();
          midCycleUpgradeDateIso = upgradeDay.toISOString();
        }
      } else if (!isSamePlan || isBillingCycleChange) {
        if (isSamePlan && isBillingCycleChange && !isCurrentTrialPackage) {
          const currentCycleOnly = currentSubscription.billing_cycle || "QUARTERLY";
          const midSnap = computeMidCycleSamePackageLicenseIncreaseCharge({
            currentNextBilling: currentSubscription.next_billing_date,
            billingCycle: currentCycleOnly,
            newBillingCycle: billingCycle,
            asOf: now,
            oldLicenseCount: currentSubscription.license_count,
            newLicenseCount: dto.license_count,
            pricePerLicense: targetPricePerLicense,
          });
          difference = midSnap.net;
          grossNewTermAmount = Number(midSnap.grossNewTerm.toFixed(2));
          remainderCreditAmount = midSnap.credit;
          midCyclePricingDetail =
            `Full new term $${midSnap.grossNewTerm.toFixed(2)} − unused credit $${midSnap.credit.toFixed(2)} ` +
            `(${midSnap.remainderDays}/${midSnap.periodDays} days left in term; credit vs ${midSnap.creditBasisDays}-day paid basis at ${currentSubscription.license_count} licenses) = $${midSnap.net.toFixed(2)}`;

          if (currentSubscription.next_billing_date) {
            const periodStartRaw = inferPaidPeriodStartFromNextBilling(
              currentSubscription.next_billing_date,
              currentCycleOnly,
            );
            const periodStart = startOfUtcDay(periodStartRaw);
            const periodEndInclusive = startOfUtcDay(addBillingDays(periodStart, midSnap.periodDays - 1));
            const upgradeDay = startOfUtcDay(now);
            midCycleOldTermAmount = Number(midSnap.amountPaidOldTerm.toFixed(2));
            midCycleUsedValueAmount = Number(Math.max(0, midSnap.amountPaidOldTerm - midSnap.credit).toFixed(2));
            midCyclePeriodStartIso = periodStart.toISOString();
            midCyclePeriodEndInclusiveIso = periodEndInclusive.toISOString();
            midCycleUpgradeDateIso = upgradeDay.toISOString();
          }
        } else {
          if (!isSamePlan && !isCurrentTrialPackage) {
            const currentCycleForCredit = currentSubscription.billing_cycle || "QUARTERLY";
            const midPlan = computeMidCycleSamePackageLicenseIncreaseCharge({
              currentNextBilling: currentSubscription.next_billing_date,
              billingCycle: currentCycleForCredit,
              newBillingCycle: billingCycle,
              asOf: now,
              oldLicenseCount: currentSubscription.license_count,
              newLicenseCount: dto.license_count,
              pricePerLicense: targetPricePerLicense,
              oldPricePerLicense: currentPricePerLicense,
            });
            difference = midPlan.net;
            grossNewTermAmount = Number(midPlan.grossNewTerm.toFixed(2));
            remainderCreditAmount = midPlan.credit;
            midCyclePricingDetail =
              `Full new term $${midPlan.grossNewTerm.toFixed(2)} − unused credit (old plan) $${midPlan.credit.toFixed(2)} ` +
              `(${midPlan.remainderDays}/${midPlan.periodDays} days left in current term; credit vs ${midPlan.creditBasisDays}-day paid basis at ${currentSubscription.license_count} licenses) = $${midPlan.net.toFixed(2)}`;

            if (currentSubscription.next_billing_date) {
              const periodStartRaw = inferPaidPeriodStartFromNextBilling(
                currentSubscription.next_billing_date,
                currentCycleForCredit,
              );
              const periodStart = startOfUtcDay(periodStartRaw);
              const periodEndInclusive = startOfUtcDay(addBillingDays(periodStart, midPlan.periodDays - 1));
              const upgradeDay = startOfUtcDay(now);
              midCycleOldTermAmount = Number(midPlan.amountPaidOldTerm.toFixed(2));
              midCycleUsedValueAmount = Number(Math.max(0, midPlan.amountPaidOldTerm - midPlan.credit).toFixed(2));
              midCyclePeriodStartIso = periodStart.toISOString();
              midCyclePeriodEndInclusiveIso = periodEndInclusive.toISOString();
              midCycleUpgradeDateIso = upgradeDay.toISOString();
            }
          } else {
            difference = newChargeAmount;
          }
        }
      } else {
        difference = newChargeAmount - currentChargeAmount;
      }
    }

    const differenceType =
      difference === 0 ? "NO_CHANGE" : difference > 0 ? ("UPGRADE" as const) : ("DOWNGRADE" as const);

    const currentNextBillingDate = currentSubscription.next_billing_date;
    const isTrialUpgradeBeforeEnd =
      isUpgradeFromAnyTrial && !!currentNextBillingDate && currentNextBillingDate.getTime() > Date.now();

    const now = new Date();
    const isLicenseIncreasePreview = dto.license_count > currentSubscription.license_count;
    const isPaidMidCycleLicenseBumpPreview =
      !isUpgradeFromPrivateVipTrial &&
      isSamePlan &&
      !isBillingCycleChange &&
      isLicenseIncreasePreview &&
      !isCurrentTrialPackage;

    const isPaidMidCyclePlanPackageUpgradePreview =
      !isUpgradeFromPrivateVipTrial && !isSamePlan && !isCurrentTrialPackage;

    const baseDateForNewBilling = isTrialUpgradeBeforeEnd
      ? undefined
      : isBillingCycleChange && currentNextBillingDate
        ? currentNextBillingDate
        : undefined;
    const newNextBillingDate =
      isPaidMidCycleLicenseBumpPreview || isPaidMidCyclePlanPackageUpgradePreview
        ? addBillingDays(now, getBillingCyclePeriodDays(billingCycle))
        : calculateNextBillingDateFromAnchor(billingCycle, baseDateForNewBilling);

    return this.toDto(
      {
        current_monthly_subtotal: currentMonthlySubtotal,
        new_monthly_subtotal: newMonthlySubtotal,
        difference_amount: Number(difference.toFixed(2)),
        difference_type: differenceType,
        is_same_plan: isSamePlan,
        current_package_name: currentSubscription.package.name ?? null,
        current_package_features: currentSubscription.package.features,
        new_package_features: targetPackage.features,
        current_next_billing_date: currentNextBillingDate?.toISOString() || null,
        current_subscription_start_date: currentSubscription.created_at?.toISOString() || null,
        new_next_billing_date: newNextBillingDate.toISOString(),
        current_license_count: currentSubscription.license_count,
        new_license_count: dto.license_count,
        current_billing_cycle: currentSubscription.billing_cycle,
        new_billing_cycle: billingCycle,
        is_private_vip_trial_upgrade: isUpgradeFromPrivateVipTrial,
        is_trial_upgrade_before_end: isTrialUpgradeBeforeEnd,
        discount_breakdown: discountBreakdown,
        gross_new_term_amount: grossNewTermAmount,
        remainder_credit_amount: remainderCreditAmount,
        mid_cycle_pricing_detail: midCyclePricingDetail,
        mid_cycle_old_term_amount: midCycleOldTermAmount,
        mid_cycle_used_value_amount: midCycleUsedValueAmount,
        mid_cycle_period_start_iso: midCyclePeriodStartIso,
        mid_cycle_period_end_inclusive_iso: midCyclePeriodEndInclusiveIso,
        mid_cycle_upgrade_date_iso: midCycleUpgradeDateIso,
        company_country: companyRow?.country ?? null,
      },
      SubscriptionPreviewResponseDto,
    );
  }

  /**
   * Generate unique sequential invoice number
   */
  private async generateInvoiceNumber(): Promise<string> {
    const today = new Date();
    const year = today.getFullYear();
    const month = String(today.getMonth() + 1).padStart(2, "0");
    const day = String(today.getDate()).padStart(2, "0");
    const datePrefix = `${year}${month}${day}`;

    // Find the highest invoice number for today
    const todayInvoices = await this.prisma.client.invoice.findMany({
      where: {
        invoice_number: {
          startsWith: `INV-${datePrefix}-`,
        },
      },
      orderBy: {
        invoice_number: "desc",
      },
      take: 1,
    });

    let sequence = 1;
    if (todayInvoices.length > 0) {
      const lastInvoice = todayInvoices[0];
      const lastSequence = parseInt(lastInvoice.invoice_number.split("-").pop() ?? "0", 10);
      sequence = lastSequence + 1;
    }

    return `INV-${datePrefix}-${String(sequence).padStart(4, "0")}`;
  }

  /**
   * Confirm subscription change and create payment checkout session
   */
  async confirmSubscriptionChange(
    companyId: number,
    dto: SubscriptionConfirmRequestDto,
  ): Promise<SubscriptionConfirmResponseDto> {
    // 1) Validate target package
    const pkg = await this.prisma.client.package.findUnique({
      where: { id: dto.package_id },
    });
    if (!pkg) {
      throw new NotFoundException("Package not found.");
    }

    // 2) Validate license count within allowed range for the package
    const minLicenses = Math.max(pkg.minimum_license_required, pkg.license_count_start);
    const maxLicenses = pkg.license_count_end === 0 ? Number.MAX_SAFE_INTEGER : pkg.license_count_end;

    if (dto.license_count < minLicenses) {
      throw new BadRequestException(`License count must be at least ${minLicenses}.`);
    }
    if (dto.license_count > maxLicenses) {
      throw new BadRequestException(`License count cannot exceed ${maxLicenses}.`);
    }

    // 3) Get current subscription (if any)
    const current = await this.prisma.client.subscription.findFirst({
      where: {
        company_id: companyId,
        is_current: true,
      },
      include: {
        package: true,
      },
      orderBy: { created_at: "desc" },
    });

    // Log current subscription info for debugging
    if (current) {
      this.logger.log(
        `Current subscription: package_id=${current.package_id}, package_type=${current.package?.package_type}, is_trial=${current.package?.is_trial_package}`,
      );
    }

    // 4) Ensure we are not reducing below consumed licenses
    const licensesConsumed = current?.licenses_consumed ?? 0;
    if (dto.license_count < licensesConsumed) {
      throw new BadRequestException(
        `License count cannot be less than already consumed licenses (${licensesConsumed}).`,
      );
    }

    if (current?.package) {
      const isCurrentTrialPackage =
        !!current.package.is_trial_package ||
        current.package.package_type === PackageType.FREE_TRIAL ||
        current.package.package_type === PackageType.PRIVATE_VIP_TRIAL;
      const isTargetTrialPackage =
        !!pkg.is_trial_package ||
        pkg.package_type === PackageType.FREE_TRIAL ||
        pkg.package_type === PackageType.PRIVATE_VIP_TRIAL;
      const isUpgradeFromAnyTrial = isCurrentTrialPackage && !isTargetTrialPackage;
      if (!isUpgradeFromAnyTrial) {
        this.assertNoPaidProductTierDowngrade(current.package.package_type, pkg.package_type);
      }
    }

    // 5) Get company info for Stripe customer (and country for VAT)
    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: {
        id: true,
        stripe_customer_id: true,
        name: true,
        country: true,
      },
    });

    if (!company) {
      throw new NotFoundException("Company not found");
    }

    // 6) Calculate pricing
    const pricePerLicense = this.toNumber(pkg.price_per_licence) ?? 0;
    const newMonthlySubtotal = pricePerLicense * dto.license_count;

    // Stored cadence defaults to quarterly; initial trial checkout still charges $0 (multiplier 0).
    const isInitialTrialPackage =
      !current &&
      (pkg.is_trial_package || pkg.package_type === "FREE_TRIAL" || pkg.package_type === "PRIVATE_VIP_TRIAL");
    const billingCycle = dto.billing_cycle || current?.billing_cycle || "QUARTERLY";
    const billingCycleMultiplier = isInitialTrialPackage ? 0 : getBillingCycleMultiplier(billingCycle);

    // 6.1) Check if this is a downgrade and prevent it, and check for no price change
    // Also calculate the difference amount for plan changes
    let currentPricePerLicense = 0;
    let currentMonthlySubtotal = 0;
    let currentBillingCycleMultiplier = 3; // Default to QUARTERLY
    let differenceAmount = newMonthlySubtotal * billingCycleMultiplier; // For initial subscriptions, charge full amount
    let subtotalAmount = newMonthlySubtotal * billingCycleMultiplier; // Default to full amount
    let billingReferenceNotes: string | null = null;

    if (current) {
      currentPricePerLicense = this.toNumber(current.package.price_per_licence) ?? 0;
      currentMonthlySubtotal = current.license_count * currentPricePerLicense;
      currentBillingCycleMultiplier = getBillingCycleMultiplier(current.billing_cycle ?? "QUARTERLY");

      // Calculate actual charge amounts with billing cycle multipliers
      const currentChargeAmount = currentMonthlySubtotal * currentBillingCycleMultiplier;
      const newChargeAmount = newMonthlySubtotal * billingCycleMultiplier;
      const difference = newChargeAmount - currentChargeAmount;
      const isDowngrade = difference < -0.01;

      this.logger.log(
        `Subscription change check: currentMonthly=${currentMonthlySubtotal}, newMonthly=${newMonthlySubtotal}, currentCharge=${currentChargeAmount}, newCharge=${newChargeAmount}, difference=${difference}, currentBillingCycle=${current.billing_cycle}, newBillingCycle=${billingCycle}, currentPricePerLicense=${currentPricePerLicense}, newPricePerLicense=${pricePerLicense}, currentLicenseCount=${current.license_count}, newLicenseCount=${dto.license_count}, isDowngrade=${isDowngrade}`,
      );

      if (isDowngrade) {
        throw new BadRequestException(
          "Plan downgrades are not allowed. You can only upgrade to a higher plan or increase your license count.",
        );
      }

      // If there's no price change (same total cost), reject the change request
      if (difference < 0.01 && difference > -0.01 && newChargeAmount > 0) {
        throw new BadRequestException(
          "No change detected. The selected plan has the same total cost as your current plan. Please select a different plan or adjust the license count.",
        );
      }

      // Determine charge amount based on change type:
      // - License increase only (same plan + same billing cycle): charge difference
      // - Billing cycle change only: charge full new billing cycle amount
      // - Billing cycle change + license increase: charge pro-rated extra licenses for remaining old cycle + full new cycle
      // - Plan change: charge full new amount
      const isSamePlan = current.package_id === pkg.id;
      const isBillingCycleChange = (current.billing_cycle ?? "QUARTERLY") !== billingCycle;
      const isLicenseIncrease = current.license_count < dto.license_count;
      const isLicenseIncreaseOnly = isSamePlan && !isBillingCycleChange && isLicenseIncrease;
      const isBillingCycleChangeWithLicenseIncrease = isSamePlan && isBillingCycleChange && isLicenseIncrease;

      if (isLicenseIncreaseOnly) {
        const now = new Date();
        const isCurrentPaidTrialLike =
          current.package.is_trial_package ||
          current.package.package_type === "FREE_TRIAL" ||
          current.package.package_type === "PRIVATE_VIP_TRIAL";
        if (isCurrentPaidTrialLike) {
          differenceAmount = Math.max(0, difference);
          subtotalAmount = differenceAmount;
          this.logger.log(`License increase (trial-like package): charging simple delta ${subtotalAmount}`);
        } else {
          const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
            currentNextBilling: current.next_billing_date,
            billingCycle: billingCycle ?? "QUARTERLY",
            asOf: now,
            oldLicenseCount: current.license_count,
            newLicenseCount: dto.license_count,
            pricePerLicense,
          });
          differenceAmount = mid.net;
          subtotalAmount = mid.net;
          billingReferenceNotes = this.formatMidCycleBillingReferenceNotes(
            mid,
            "same_package",
            current.license_count,
          );
          this.logger.log(
            `License increase (30-day cycle): gross=${mid.grossNewTerm}, credit=${mid.credit}, net=${mid.net}`,
          );
        }
      } else if (isBillingCycleChangeWithLicenseIncrease) {
        const now = new Date();
        const isCurrentPaidTrialLike =
          current.package.is_trial_package ||
          current.package.package_type === "FREE_TRIAL" ||
          current.package.package_type === "PRIVATE_VIP_TRIAL";
        if (isCurrentPaidTrialLike) {
          differenceAmount = newChargeAmount;
          subtotalAmount = newChargeAmount;
          this.logger.log(
            `Billing cycle change + license increase (trial-like): full new cycle amount ${subtotalAmount}`,
          );
        } else {
          const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
            currentNextBilling: current.next_billing_date,
            billingCycle: current.billing_cycle || "QUARTERLY",
            newBillingCycle: billingCycle ?? "QUARTERLY",
            asOf: now,
            oldLicenseCount: current.license_count,
            newLicenseCount: dto.license_count,
            pricePerLicense,
          });
          differenceAmount = mid.net;
          subtotalAmount = mid.net;
          billingReferenceNotes = this.formatMidCycleBillingReferenceNotes(
            mid,
            "same_package",
            current.license_count,
          );
          this.logger.log(
            `Billing cycle change + license increase: gross=${mid.grossNewTerm}, credit=${mid.credit}, net=${mid.net}`,
          );
        }
      } else if (isSamePlan && isBillingCycleChange && !isLicenseIncrease) {
        const now = new Date();
        const isCurrentPaidTrialLike =
          current.package.is_trial_package ||
          current.package.package_type === "FREE_TRIAL" ||
          current.package.package_type === "PRIVATE_VIP_TRIAL";
        if (isCurrentPaidTrialLike) {
          differenceAmount = newChargeAmount;
          subtotalAmount = newChargeAmount;
          this.logger.log(`Billing cycle change only (trial-like): full new cycle amount ${subtotalAmount}`);
        } else {
          const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
            currentNextBilling: current.next_billing_date,
            billingCycle: current.billing_cycle || "QUARTERLY",
            newBillingCycle: billingCycle ?? "QUARTERLY",
            asOf: now,
            oldLicenseCount: current.license_count,
            newLicenseCount: dto.license_count,
            pricePerLicense,
          });
          differenceAmount = mid.net;
          subtotalAmount = mid.net;
          billingReferenceNotes = this.formatMidCycleBillingReferenceNotes(
            mid,
            "same_package",
            current.license_count,
          );
          this.logger.log(
            `Billing cycle change only: gross=${mid.grossNewTerm}, credit=${mid.credit}, net=${mid.net}`,
          );
        }
      } else {
        // Plan change (or other): charge full new amount
        // Special handling for Private_vip_trial upgrades
        const isUpgradeFromPrivateVipTrial =
          current.package.package_type === "PRIVATE_VIP_TRIAL" && pkg.package_type !== "PRIVATE_VIP_TRIAL";

        this.logger.log(
          `Upgrade check: current_package_type=${current.package.package_type}, new_package_type=${pkg.package_type}, isUpgradeFromPrivateVipTrial=${isUpgradeFromPrivateVipTrial}`,
        );

        const isCurrentPaidTrialLike =
          current.package.is_trial_package ||
          current.package.package_type === "FREE_TRIAL" ||
          current.package.package_type === "PRIVATE_VIP_TRIAL";

        if (isUpgradeFromPrivateVipTrial) {
          // Private_vip_trial upgrade: free trial cancelled on upgrade. Same offer always:
          // Month 1 & 2: 50% off, Month 3+: full price. Next billing from upgrade date.
          const monthlyPrice = newMonthlySubtotal;
          const multiplier = billingCycleMultiplier;

          let totalCharge = 0;
          totalCharge += monthlyPrice * 0.5; // Month 1: 50% off
          totalCharge += monthlyPrice * 0.5; // Month 2: 50% off
          if (multiplier > 2) {
            const remainingMonths = multiplier - 2;
            totalCharge += monthlyPrice * remainingMonths;
          }

          differenceAmount = totalCharge;
          subtotalAmount = totalCharge;

          this.logger.log(
            `Private_vip_trial upgrade: billing cycle=${billingCycle}, multiplier=${multiplier}, monthlyPrice=${monthlyPrice}, totalCharge=${totalCharge} (Months 1&2: 50% off, Month 3+: full)`,
          );
        } else if (!isSamePlan && !isCurrentPaidTrialLike) {
          const now = new Date();
          const mid = computeMidCycleSamePackageLicenseIncreaseCharge({
            currentNextBilling: current.next_billing_date,
            billingCycle: current.billing_cycle || "QUARTERLY",
            newBillingCycle: billingCycle ?? "QUARTERLY",
            asOf: now,
            oldLicenseCount: current.license_count,
            newLicenseCount: dto.license_count,
            pricePerLicense,
            oldPricePerLicense: currentPricePerLicense,
          });
          differenceAmount = mid.net;
          subtotalAmount = mid.net;
          billingReferenceNotes = this.formatMidCycleBillingReferenceNotes(
            mid,
            "plan_change",
            current.license_count,
          );
          this.logger.log(`Plan package upgrade: gross=${mid.grossNewTerm}, credit=${mid.credit}, net=${mid.net}`);
        } else {
          differenceAmount = newChargeAmount;
          subtotalAmount = newChargeAmount;
          this.logger.log(
            `${isBillingCycleChange ? "Billing cycle change" : "Plan change"}: charging full new amount ${subtotalAmount} (new plan: ${pkg.name}, billing cycle: ${billingCycle})`,
          );
        }
      }
    }

    // Skip payment for free trial packages
    if (pkg.is_trial_package || subtotalAmount === 0) {
      // For free packages, directly apply the change
      const licensesAvailable = Math.max(0, dto.license_count - licensesConsumed);

      // Calculate next billing date for trial packages
      const isInitialTrialSubscription =
        !current &&
        (pkg.is_trial_package || pkg.package_type === "FREE_TRIAL" || pkg.package_type === "PRIVATE_VIP_TRIAL");
      let trialNextBillingDate: Date | undefined;

      if (isInitialTrialSubscription) {
        // For initial trial subscriptions, use trial_duration_days
        trialNextBillingDate = calculateTrialNextBillingDate(pkg.trial_duration_days);
        this.logger.log(
          `Free trial subscription: setting next_billing_date based on trial_duration_days=${pkg.trial_duration_days}`,
        );
      } else {
        // For upgrades or non-trial free packages, use billing cycle
        // Ensure billingCycle is not null
        const effectiveBillingCycle = billingCycle || "QUARTERLY";
        const isBillingCycleChange = current && (current.billing_cycle ?? "QUARTERLY") !== effectiveBillingCycle;
        const baseDateForNextBilling =
          isBillingCycleChange && current?.next_billing_date ? current.next_billing_date : undefined;
        trialNextBillingDate = calculateNextBillingDateFromAnchor(effectiveBillingCycle, baseDateForNextBilling);
      }

      // Mark old subscription as not current
      if (current) {
        await this.prisma.client.subscription.update({
          where: { id: current.id },
          data: {
            is_current: false,
            status: SubscriptionStatus.CANCELLED,
            end_date: new Date(),
            stripe_subscription_id: null,
            stripe_cancel_at_period_end: false,
          },
        });
      }

      // Build subscription data
      const subscriptionData: any = {
        company_id: companyId,
        package_id: pkg.id,
        license_count: dto.license_count,
        licenses_available: licensesAvailable,
        licenses_consumed: licensesConsumed,
        status: "ACTIVE",
        is_current: true,
        subscription_type: current ? "UPGRADE" : "INITIAL",
        start_date: new Date(),
        end_date: trialNextBillingDate,
        next_billing_date: trialNextBillingDate,
      };

      const effectiveBillingCycle = billingCycle || "QUARTERLY";
      subscriptionData.billing_cycle = effectiveBillingCycle as any;

      // Create new subscription
      const newSubscription = await this.prisma.client.subscription.create({
        data: subscriptionData,
      });

      // Create invoice for free trial. We snapshot `billing_cycle` directly on
      // the invoice row so the PDF renders historically accurate data even if
      // the subscription's live cycle is later changed.
      const invoiceNumber = await this.generateInvoiceNumber();
      await this.prisma.client.invoice.create({
        data: {
          company_id: companyId,
          subscription_id: newSubscription.id,
          invoice_number: invoiceNumber,
          unit_price_per_license: new Decimal(0),
          license_quantity: dto.license_count,
          package_type: pkg.package_type,
          billing_cycle: effectiveBillingCycle as any,
          status: InvoiceStatus.PAID,
          invoice_type: current ? InvoiceType.RENEWAL : InvoiceType.INITIAL_SUBSCRIPTION,
          paid_date: new Date(),
          ...invoiceBillingAmountsToDbFields(buildInvoiceBillingAmounts({ grossLicenseAmount: 0 })),
        },
      });

      this.logger.log(
        `Free subscription change applied: companyId=${companyId}, package_id=${dto.package_id}, license_count=${dto.license_count}`,
      );

      await this.clearCompanySubscriptionExpiryWhenRenewalIsAhead(companyId);

      // Return success URL for free packages
      return this.toDto(
        {
          checkout_url: `${this.portalReturnUrl}/portal/settings/subscription?tab=subscription&success=true`,
        },
        SubscriptionConfirmResponseDto,
      );
    }

    // 7) Check if Stripe is configured
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Payment processing is not available");
    }

    // 8) Create or get Stripe customer
    let stripeCustomerId = company.stripe_customer_id;
    if (stripeCustomerId) {
      const exists = await this.stripeService.customerExists(stripeCustomerId);
      if (!exists) {
        this.logger.warn(
          `Stored stripe_customer_id ${stripeCustomerId} not found in current Stripe account/mode for company ${companyId}; recreating customer.`,
        );
        stripeCustomerId = null;
      }
    }
    if (!stripeCustomerId) {
      const customer = await this.stripeService.createCustomer({
        email: "", // Will be set during checkout
        name: company.name ?? undefined,
        metadata: { company_id: companyId.toString() },
      });
      stripeCustomerId = customer.id;

      // Update company with Stripe customer ID
      await this.prisma.client.company.update({
        where: { id: companyId },
        data: { stripe_customer_id: stripeCustomerId },
      });
    }

    // 9) Determine the type of change:
    // - isSamePlan: same package_id
    // - isBillingCycleChange: billing cycle changed
    // - isLicenseIncrease: only license count increased (same plan + same billing cycle)
    const isSamePlan = current && current.package_id === pkg.id;
    const isBillingCycleChange = current && current.billing_cycle !== billingCycle;
    const isLicenseIncreaseOnly = isSamePlan && !isBillingCycleChange && current.license_count < dto.license_count;

    // 9.1) Determine invoice type — and, for amendments on top of an already
    // paid subscription, the `parent_invoice_id` that links the new invoice
    // back to the paid row it is amending. Paid invoices are never mutated:
    // every mid-cycle change produces a NEW row with the appropriate type.
    //
    //   * No current sub                 → INITIAL_SUBSCRIPTION
    //   * Same plan, same cycle, same
    //     licenses (paying for next
    //     cycle)                         → RENEWAL
    //   * Same plan, same licenses,
    //     cycle changed (Q→A, etc)       → CYCLE_CHANGE (parent = last paid)
    //   * Plan change OR license
    //     increase                       → UPGRADE_PRORATION (parent = last paid)
    //
    // Downgrades are currently rejected earlier in this method via
    // assertNoPaidProductTierDowngrade, so DOWNGRADE_CREDIT rows are only
    // produced by the Stripe refund webhook (stripe-payment.service.ts).
    let invoiceType: InvoiceType;
    let parentInvoiceId: number | null = null;

    if (!current) {
      invoiceType = InvoiceType.INITIAL_SUBSCRIPTION;
    } else {
      const isSamePackage = current.package_id === pkg.id;
      const isSameLicenseCount = current.license_count === dto.license_count;
      const isSameCycle = !isBillingCycleChange;

      if (isSamePackage && isSameLicenseCount && isSameCycle) {
        invoiceType = InvoiceType.RENEWAL;
      } else if (isSamePackage && isSameLicenseCount && !isSameCycle) {
        invoiceType = InvoiceType.CYCLE_CHANGE;
      } else {
        invoiceType = InvoiceType.UPGRADE_PRORATION;
      }

      // Look up the most recent PAID invoice for this subscription to use as
      // the amendment's parent. Skip REFUND / DOWNGRADE_CREDIT rows (those are
      // themselves amendments, not parent-eligible originals). If none exists
      // yet (rare: the old subscription paid via a legacy path without a
      // local invoice row) we leave parent_invoice_id null.
      if (invoiceType === InvoiceType.UPGRADE_PRORATION || invoiceType === InvoiceType.CYCLE_CHANGE) {
        const lastPaidInvoice = await this.prisma.client.invoice.findFirst({
          where: {
            subscription_id: current.id,
            status: InvoiceStatus.PAID,
            invoice_type: {
              notIn: [InvoiceType.REFUND, InvoiceType.DOWNGRADE_CREDIT],
            },
          },
          orderBy: [{ paid_date: "desc" }, { id: "desc" }],
          select: { id: true },
        });
        parentInvoiceId = lastPaidInvoice?.id ?? null;
        this.logger.log(
          `Amendment invoice: type=${invoiceType}, parent_invoice_id=${parentInvoiceId ?? "none"} (subscription=${current.id})`,
        );
      }
    }

    // 10) Subscription row for the pending invoice + checkout metadata
    // After payment, checkout.session.completed always takes the "create new row" path
    // (should_update_existing is never "true" from this flow): old row superseded, new row with new license_count,
    // then syncStripeSubscriptionForLocalSubscription cancels Stripe subscription(s) for the customer and creates
    // a new Stripe subscription priced for the full cycle at the new seat count.
    let subscriptionForInvoice = current;

    if (isLicenseIncreaseOnly) {
      this.logger.log(
        `License increase: will create new subscription after checkout, superseding ${current.id} (${current.license_count} → ${dto.license_count} licenses)`,
      );
    } else {
      if (!subscriptionForInvoice) {
        // Initial subscription - create temporary subscription for invoice
        const licensesConsumed = 0;
        const licensesAvailable = dto.license_count;

        // Initial trial: trial end from package; billing_cycle = quarterly (post-trial cadence).
        const isInitialTrialSubscription =
          pkg.is_trial_package || pkg.package_type === "FREE_TRIAL" || pkg.package_type === "PRIVATE_VIP_TRIAL";
        let initialNextBillingDate: Date | undefined;
        let subscriptionBillingCycle: string;

        if (isInitialTrialSubscription) {
          initialNextBillingDate = calculateTrialNextBillingDate(pkg.trial_duration_days);
          subscriptionBillingCycle = "QUARTERLY";
          this.logger.log(
            `Creating initial trial subscription: next_billing_date from trial_duration_days=${pkg.trial_duration_days}, billing_cycle=${subscriptionBillingCycle}`,
          );
        } else {
          subscriptionBillingCycle = billingCycle;
        }

        // Build subscription data
        const subscriptionData: any = {
          company_id: companyId,
          package_id: pkg.id,
          license_count: dto.license_count,
          licenses_available: licensesAvailable,
          licenses_consumed: licensesConsumed,
          status: "ACTIVE",
          is_current: true,
          subscription_type: "INITIAL",
          start_date: new Date(),
          end_date: initialNextBillingDate,
          next_billing_date: initialNextBillingDate,
          billing_cycle: subscriptionBillingCycle,
        };

        subscriptionForInvoice = await this.prisma.client.subscription.create({
          data: subscriptionData,
          include: {
            package: true,
          },
        });
      } else {
        // Plan change or billing cycle change - will create new subscription in webhook
        if (current) {
          this.logger.log(
            `Plan/billing cycle change: will create new subscription, current package_id=${current.package_id}, new package_id=${pkg.id}, billing cycle changed=${isBillingCycleChange}`,
          );
        } else {
          this.logger.log(`Plan change: will create new subscription, new package_id=${pkg.id}`);
        }
      }
    }

    // Ensure subscriptionForInvoice is not null (TypeScript guard)
    if (!subscriptionForInvoice) {
      throw new BadRequestException("Unable to create subscription for invoice");
    }

    // Next billing dates are set when checkout completes (webhook) using the same 30-day-month rules.

    // 11) Generate invoice number and create pending invoice
    const invoiceNumber = await this.generateInvoiceNumber();

    // 11.1) Persist full billing breakdown (license, fees, pre-VAT, total).
    const billingAmounts = buildInvoiceBillingAmounts({
      grossLicenseAmount: subtotalAmount,
      adminCountry: company.country ?? undefined,
    });
    const totalAmount = billingAmounts.total_amount;

    this.logger.log(
      `Fees: gross_license=${billingAmounts.gross_license_amount}, subtotal=${billingAmounts.subtotal_amount}, processing_fee=${billingAmounts.processing_fee} (${billingAmounts.processing_fee_percentage}%), vat_fee=${billingAmounts.vat_fee} (${billingAmounts.vat_fee_percentage}%), pre_vat=${billingAmounts.pre_vat_total_amount}, total=${totalAmount}`,
    );

    // Create pending invoice (subscription will be created/updated after payment)
    // For upgrades, invoice shows the difference amount charged, but includes full subscription details.
    //
    // `billing_cycle` is snapshotted onto the invoice row so the PDF never
    // reflects a later plan/cycle change — paid invoices stay historically
    // accurate, and PENDING invoices reflect the current draft until checkout.
    // The snapshot follows the same precedence as the subscription row:
    // DTO-supplied cycle first, else the existing subscription's cycle.
    const invoiceBillingCycleSnapshot = (billingCycle as any) ?? subscriptionForInvoice.billing_cycle ?? null;
    const invoice = await this.prisma.client.invoice.create({
      data: {
        company_id: companyId,
        subscription_id: subscriptionForInvoice.id,
        invoice_number: invoiceNumber,
        unit_price_per_license: new Decimal(pricePerLicense),
        license_quantity: dto.license_count,
        package_type: pkg.package_type,
        billing_cycle: invoiceBillingCycleSnapshot,
        // parent_invoice_id is set for UPGRADE_PRORATION / CYCLE_CHANGE rows so
        // the PDF and admin UI can link this amendment back to the paid
        // invoice it is amending. null for INITIAL_SUBSCRIPTION and RENEWAL.
        parent_invoice_id: parentInvoiceId ?? undefined,
        status: InvoiceStatus.PENDING,
        invoice_type: invoiceType,
        billing_reference_notes: billingReferenceNotes,
        ...invoiceBillingAmountsToDbFields(billingAmounts),
      },
    });

    // 13) Create Stripe checkout session
    const checkoutMetadata: Record<string, string> = {
      company_id: companyId.toString(),
      invoice_id: invoice.id.toString(),
      package_id: dto.package_id.toString(),
      license_count: dto.license_count.toString(),
      invoice_type: invoiceType,
      billing_cycle: billingCycle,
      should_update_existing: "false",
      is_billing_cycle_change: isBillingCycleChange ? "true" : "false", // Flag: billing cycle changed
    };

    this.logger.log(`=== CREATING CHECKOUT SESSION ===`);
    this.logger.log(`New monthly subtotal: ${newMonthlySubtotal}`);
    this.logger.log(`Current monthly subtotal: ${current ? currentMonthlySubtotal : 0}`);
    this.logger.log(
      `Amount to charge (subtotal + processing + VAT): ${totalAmount} (${Math.round(totalAmount * 100)} cents)`,
    );
    this.logger.log(`Metadata: ${JSON.stringify(checkoutMetadata)}`);

    // For upgrades, create a line item showing the difference
    // For initial subscriptions, show the full subscription details
    const lineItemDescription = current
      ? `Plan upgrade: ${pkg.name} (${dto.license_count} licenses) - $${subtotalAmount.toFixed(2)} + fees (total $${totalAmount.toFixed(2)})`
      : `${pkg.name} - ${dto.license_count} ${dto.license_count === 1 ? "License" : "Licenses"} (includes processing fee and VAT)`;

    // Single line item with total amount (subtotal + processing fee + VAT) so Stripe charges correct total
    const lineItems = [
      {
        price_data: {
          currency: "usd",
          product_data: {
            name: current
              ? `Plan Upgrade - ${pkg.name}`
              : `${pkg.name} - ${dto.license_count} ${dto.license_count === 1 ? "License" : "Licenses"}`,
            description: lineItemDescription,
          },
          unit_amount: Math.round(totalAmount * 100),
        },
        quantity: 1,
      },
    ];

    const checkoutSession = await this.stripeService.createCheckoutSession({
      amount: Math.round(totalAmount * 100), // Total including processing fee and VAT
      currency: "usd",
      successUrl: `${this.portalReturnUrl}/portal/settings/subscription?tab=subscription&success=true`,
      cancelUrl: `${this.portalReturnUrl}/portal/settings/subscription?tab=subscription&canceled=true`,
      customerId: stripeCustomerId, // Pass customer ID so payment method is saved
      metadata: checkoutMetadata,
      lineItems,
    });

    // Update invoice with checkout session ID
    await this.prisma.client.invoice.update({
      where: { id: invoice.id },
      data: { stripe_checkout_session_id: checkoutSession.id },
    });

    this.logger.log(`=== CHECKOUT SESSION CREATED ===`);
    this.logger.log(`Company ID: ${companyId}`);
    this.logger.log(`Invoice ID: ${invoice.id}`);
    this.logger.log(`Invoice Number: ${invoiceNumber}`);
    this.logger.log(`Checkout Session ID: ${checkoutSession.id}`);
    this.logger.log(`Checkout URL: ${checkoutSession.url}`);
    this.logger.log(`Metadata sent to Stripe: ${JSON.stringify(checkoutMetadata)}`);
    this.logger.log(`=== IMPORTANT: Webhook must be configured at: /api/stripe/webhook ===`);
    this.logger.log(`=== Webhook should listen for: checkout.session.completed event ===`);
    this.logger.log(
      `=== For local testing, use: stripe listen --forward-to localhost:3001/api/stripe/webhook ===`,
    );

    return this.toDto(
      {
        checkout_url: checkoutSession.url ?? "",
      },
      SubscriptionConfirmResponseDto,
    );
  }

  /**
   * Get billing history for a company
   */
  async getBillingHistory(companyId: number): Promise<SubscriptionBillingHistoryResponseDto> {
    // Automatically refresh pending invoices when billing history is requested
    // This ensures the page shows the most up-to-date status without manual refresh
    await this.refreshPendingInvoicesForCompany(companyId);

    const invoices = await this.prisma.client.invoice.findMany({
      where: {
        company_id: companyId,
      },
      include: {
        subscription: {
          include: {
            package: true,
          },
        },
      },
      orderBy: {
        created_at: "desc",
      },
    });

    const items = invoices.map((invoice) => {
      const totalAmount = this.toNumber(invoice.total_amount) ?? 0;
      const formattedAmount = `$${totalAmount.toFixed(2)}`;

      // Format date as DD/MM/YYYY
      const date = invoice.paid_date ? invoice.paid_date : invoice.created_at;
      const formattedDate = date.toLocaleDateString("en-GB", {
        day: "2-digit",
        month: "2-digit",
        year: "numeric",
      });

      // Build description from subscription details
      const licenseCount = invoice.license_quantity;
      const packageName = invoice.subscription.package.name ?? "Subscription";
      const description = `${packageName} - ${licenseCount} ${licenseCount === 1 ? "license" : "licenses"}`;

      // Map invoice status to frontend status
      let status: "Paid" | "Pending" | "Failed";
      switch (invoice.status) {
        case "PAID":
          status = "Paid";
          break;
        case "FAILED":
          status = "Failed";
          break;
        case "PENDING":
        case "CANCELLED":
        default:
          status = "Pending";
          break;
      }

      return {
        id: invoice.id,
        invoice_number: invoice.invoice_number,
        date: formattedDate,
        description,
        amount: formattedAmount,
        status,
        invoice_type: this.formatBillingHistoryInvoiceTypeLabel(invoice.invoice_type),
      };
    });

    return this.toDto({ items }, SubscriptionBillingHistoryResponseDto);
  }

  private formatBillingHistoryInvoiceTypeLabel(type: InvoiceType | string | null | undefined): string {
    if (type == null || type === "") {
      return "—";
    }
    const key = String(type);
    // Labels are kept consistent with the shared PDF builder's
    // `classifyInvoiceType` so the portal Billing History column and the PDF
    // header use the same wording.
    const labels: Record<string, string> = {
      INITIAL_SUBSCRIPTION: "Initial subscription",
      RENEWAL: "Renewal",
      UPGRADE_PRORATION: "Upgrade – proration",
      DOWNGRADE_CREDIT: "Downgrade credit",
      CYCLE_CHANGE: "Billing cycle change",
      // Legacy values (rows created before the amendment refactor).
      UPGRADE: "Upgrade",
      DOWNGRADE: "Downgrade",
      REFUND: "Refund",
    };
    return labels[key] ?? key.replace(/_/g, " ").replace(/\b\w/g, (c) => c.toUpperCase());
  }

  /**
   * Pull missing paid invoices and refund rows from Stripe for this company (same engine as admin
   * "Import from Stripe"). Scoped to @ClientAuth company only.
   */
  async syncBillingHistoryFromStripe(companyId: number): Promise<{
    success: boolean;
    message: string;
    invoices_created: number;
    refunds_created: number;
  }> {
    return this.stripePaymentService.syncCompanyInvoicesAndRefundsFromStripe(companyId);
  }

  /**
   * Automatically refresh pending invoices for a company when billing history is requested.
   * This ensures users see the most up-to-date payment status without manual refresh.
   */
  private async refreshPendingInvoicesForCompany(companyId: number): Promise<void> {
    try {
      // Get all pending invoices for this company
      const pendingInvoices = await this.prisma.client.invoice.findMany({
        where: {
          company_id: companyId,
          status: "PENDING",
        },
        select: {
          id: true,
        },
        orderBy: {
          created_at: "asc",
        },
      });

      if (pendingInvoices.length === 0) {
        return; // No pending invoices to refresh
      }

      this.logger.log(`Auto-refreshing ${pendingInvoices.length} pending invoices for company ${companyId}`);

      // Refresh each pending invoice (with some concurrency control)
      const refreshPromises = pendingInvoices.map(async (invoice) => {
        try {
          await this.stripePaymentService.getInvoiceStatus(invoice.id);
        } catch (error) {
          // Log error but don't fail the entire billing history request
          this.logger.warn(
            `Failed to refresh invoice ${invoice.id}: ${error instanceof Error ? error.message : String(error)}`,
          );
        }
      });

      // Process in batches of 5 to avoid overwhelming Stripe API
      const batchSize = 5;
      for (let i = 0; i < refreshPromises.length; i += batchSize) {
        const batch = refreshPromises.slice(i, i + batchSize);
        await Promise.allSettled(batch);

        // Small delay between batches to be respectful to Stripe API
        if (i + batchSize < refreshPromises.length) {
          await new Promise((resolve) => setTimeout(resolve, 500));
        }
      }

      this.logger.log(`Completed auto-refresh of pending invoices for company ${companyId}`);
    } catch (error) {
      // Log error but don't fail the billing history request
      this.logger.warn(
        `Failed to auto-refresh pending invoices for company ${companyId}: ${error instanceof Error ? error.message : String(error)}`,
      );
    }
  }

  /**
   * Get invoice download URL (signed) for a given invoice number, scoped to the
   * authenticated company. Delegates to {@link InvoicePdfService}, which is
   * the same service the admin Invoices tab uses — so the portal Billing
   * History and the admin Invoices tab always return the identical document,
   * including the billing-cycle snapshot and any proration / credit-note
   * details carried on the invoice row.
   *
   * We no longer fall back to Stripe's hosted invoice URL or charge receipt:
   * those render Stripe's own (non-RECALL) document and do not carry our
   * `billing_cycle`, which was the root cause of the empty-cycle bug on the
   * portal download. Stripe identifiers remain on the invoice row for
   * reconciliation but are no longer surfaced as the customer-facing PDF.
   */
  async getInvoiceDownloadUrl(companyId: number, invoiceNumber: string, authorization?: string): Promise<string> {
    return this.invoicePdfService.generateSignedUrlForCompany(companyId, invoiceNumber, authorization);
  }

  /**
   * Stream-download variant used by the portal `.../invoice/:invoiceNumber/download-pdf`
   * endpoint, which serves the PDF through our API instead of redirecting the
   * browser to an S3 URL. Delegates to the same shared service the admin uses
   * so both sides produce the identical document.
   */
  async getInvoicePdfBuffer(
    companyId: number,
    invoiceNumber: string,
    authorization?: string,
  ): Promise<{ buffer: Buffer; filename: string }> {
    return this.invoicePdfService.getPdfBufferForCompany(companyId, invoiceNumber, authorization);
  }

  /**
   * Create Stripe billing portal session to update payment method
   */
  async createPaymentMethodPortal(companyId: number): Promise<SubscriptionPaymentMethodResponseDto> {
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Stripe is not configured");
    }

    // Ensure a Stripe customer exists before creating the portal session.
    // Without this, accounts that have never added a card (no stripe_customer_id)
    // would get "Failed to create billing portal session" — Stripe's billingPortal
    // requires an existing customer. This helper creates one on-demand if missing.
    await this.ensureStripeCustomerForBilling(companyId);

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: {
        stripe_customer_id: true,
      },
    });

    if (!company || !company.stripe_customer_id) {
      // ensureStripeCustomerForBilling should have populated this; if it didn't,
      // there's a deeper issue (e.g. Stripe rejected customer creation).
      throw new BadRequestException("Stripe customer ID not found for company");
    }

    const session = await this.stripeService.createBillingPortalSession({
      customerId: company.stripe_customer_id,
      returnUrl: `${this.portalReturnUrl}/portal/settings/subscription?tab=subscription`,
    });

    return this.toDto(
      {
        redirect_url: session.url,
      },
      SubscriptionPaymentMethodResponseDto,
    );
  }

  /**
   * Creates or repairs `company.stripe_customer_id` when missing or invalid in the current Stripe mode.
   * Free / trial companies often had no customer until first checkout; Activate billing still needs a customer.
   */
  private async ensureStripeCustomerForBilling(companyId: number): Promise<void> {
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Payment processing is not available");
    }

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: { id: true, name: true, email: true, stripe_customer_id: true },
    });
    if (!company) {
      throw new NotFoundException("Company not found");
    }

    let stripeCustomerId = company.stripe_customer_id;
    if (stripeCustomerId) {
      const exists = await this.stripeService.customerExists(stripeCustomerId);
      if (!exists) {
        this.logger.warn(
          `ensureStripeCustomerForBilling: company ${companyId} customer ${stripeCustomerId} missing in Stripe; will create a new customer.`,
        );
        stripeCustomerId = null;
      }
    }

    if (!stripeCustomerId) {
      const customer = await this.stripeService.createCustomer({
        email: company.email || undefined,
        name: company.name ?? undefined,
        metadata: { company_id: companyId.toString() },
      });
      await this.prisma.client.company.update({
        where: { id: companyId },
        data: { stripe_customer_id: customer.id },
      });
      this.logger.log(
        `ensureStripeCustomerForBilling: created Stripe customer ${customer.id} for company ${companyId}`,
      );
    }
  }

  /**
   * Portal: create or replace Stripe Billing subscription from local paid subscription rows
   * (same logic as admin manual create; scoped to the authenticated company).
   * When the company is marked expired, refreshes the subscription row to the active catalog package
   * for the same tier (current prices) before syncing; license count stays on the subscription row.
   */
  async createStripeBillingSubscription(companyId: number): Promise<SubscriptionStripeBillingCreateResponseDto> {
    await this.ensureStripeCustomerForBilling(companyId);

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: { is_subscription_expiry: true },
    });
    if (company?.is_subscription_expiry) {
      // Expired: collect one full Startup-quarterly period now, then set up the future-renewal subscription.
      const immediate = await this.stripePaymentService.runImmediateExpiredRecoveryCharge(companyId, {
        forceRecreateStripeSubscription: true,
      });
      // Map to portal response shape (next renewal will be reflected in overview after webhook updates).
      const stripeSubId = immediate.company?.stripe_subscription_id ?? null;
      let nextPeriodEndIso: string | null = null;
      if (stripeSubId) {
        try {
          const sub = await this.stripeService.retrieveSubscription(stripeSubId);
          const end = (sub as Stripe.Subscription & { current_period_end?: number }).current_period_end;
          if (typeof end === "number") {
            nextPeriodEndIso = new Date(end * 1000).toISOString();
          }
        } catch {
          // ignore
        }
      }
      return plainToInstance(SubscriptionStripeBillingCreateResponseDto, {
        success: immediate.success,
        message: immediate.message,
        next_period_end_iso: nextPeriodEndIso,
      });
    }

    // Not expired: link/create Stripe billing subscription without charging immediately (align to local next billing).
    const result = await this.stripePaymentService.runAdminCreateStripeSubscription(companyId);
    return this.toDto(
      {
        ...result,
        next_period_end_iso: result.next_period_end_iso ?? null,
      },
      SubscriptionStripeBillingCreateResponseDto,
    );
  }

  /**
   * Portal: undo “cancel at period end” on the linked Stripe subscription (same subscription,
   * next renewal unchanged). Not for expired companies — use {@link createStripeBillingSubscription}.
   */
  async reactivateStripeSubscriptionAfterScheduledCancel(
    companyId: number,
  ): Promise<SubscriptionCancelAtPeriodEndResponseDto> {
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Stripe is not configured");
    }

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: { stripe_subscription_id: true, is_subscription_expiry: true },
    });

    if (!company?.stripe_subscription_id) {
      throw new BadRequestException("No Stripe subscription is linked to this account.");
    }

    if (company.is_subscription_expiry) {
      throw new BadRequestException(
        "Your access period has ended. Use Activate billing to set up payment with your current plan.",
      );
    }

    let stripeSub: Stripe.Subscription;
    try {
      stripeSub = await this.stripeService.retrieveSubscription(company.stripe_subscription_id);
    } catch {
      throw new BadRequestException("Could not load your Stripe subscription. Try again or contact support.");
    }

    if (stripeSub.cancel_at_period_end !== true) {
      const periodEndSec = (stripeSub as Stripe.Subscription & { current_period_end?: number }).current_period_end;
      const endLabel =
        typeof periodEndSec === "number"
          ? new Date(periodEndSec * 1000).toLocaleDateString("en-US", {
              month: "short",
              day: "numeric",
              year: "numeric",
              timeZone: "UTC",
            })
          : null;
      return this.toDto(
        {
          stripe_cancel_at_period_end: false,
          message: endLabel
            ? `Your subscription is already set to renew. Next period ends ${endLabel} (UTC).`
            : "Your subscription is already set to renew.",
        },
        SubscriptionCancelAtPeriodEndResponseDto,
      );
    }

    const updated = await this.stripeService.setSubscriptionCancelAtPeriodEnd(
      company.stripe_subscription_id,
      false,
    );

    const localSub = await this.prisma.client.subscription.findFirst({
      where: { company_id: companyId, is_current: true },
      select: { id: true, stripe_subscription_id: true, stripe_cancel_at_period_end: true },
    });

    if (localSub?.stripe_subscription_id && localSub.stripe_subscription_id !== company.stripe_subscription_id) {
      throw new BadRequestException(
        "Your current plan subscription does not match the active Stripe subscription. Please contact support.",
      );
    }

    // Local DB flag is reconciled by Stripe webhook `customer.subscription.updated`.

    await this.stripePaymentService.sendBillingSubscriptionResumeEmail(companyId, updated, {
      triggeredBy: "portal_resume_subscription",
      subscriptionRowId: localSub?.id,
    });

    if (localSub?.id) {
      const oldVal = localSub.stripe_cancel_at_period_end === true;
      const newVal = false;
      await this.systemLogService.logUpdate(
        SystemLogEntityType.SUBSCRIPTION,
        localSub.id,
        { stripe_cancel_at_period_end: oldVal, stripe_subscription_id: localSub.stripe_subscription_id } as Record<
          string,
          unknown
        >,
        {
          stripe_cancel_at_period_end: newVal,
          stripe_subscription_id: company.stripe_subscription_id,
          source: "portal",
          action: "reactivate_subscription",
        } as Record<string, unknown>,
        {
          stripe_cancel_at_period_end: { old: oldVal, new: newVal },
        } as Record<string, unknown>,
        { company_id: companyId },
      );
    }

    const periodEndSec = (updated as Stripe.Subscription & { current_period_end?: number }).current_period_end;
    const endLabel =
      typeof periodEndSec === "number"
        ? new Date(periodEndSec * 1000).toLocaleDateString("en-US", {
            month: "short",
            day: "numeric",
            year: "numeric",
            timeZone: "UTC",
          })
        : null;

    this.logger.log(
      `Reactivated Stripe subscription (cleared cancel_at_period_end) for company ${companyId} (${company.stripe_subscription_id})`,
    );

    return this.toDto(
      {
        stripe_cancel_at_period_end: false,
        message: endLabel
          ? `Your subscription will renew automatically. Current billing period ends ${endLabel} (UTC).`
          : "Your subscription will renew automatically.",
      },
      SubscriptionCancelAtPeriodEndResponseDto,
    );
  }

  /**
   * Portal: retry payment for the latest Stripe invoice of the existing subscription (past_due/unpaid).
   * Uses the customer's invoice default payment method (off-session).
   */
  async retryStripeSubscriptionPayment(companyId: number): Promise<SubscriptionRetryPaymentResponseDto> {
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Stripe is not configured");
    }

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: { stripe_customer_id: true, stripe_subscription_id: true },
    });

    if (!company?.stripe_customer_id) {
      throw new BadRequestException("No Stripe customer is linked to this account.");
    }
    if (!company.stripe_subscription_id) {
      throw new BadRequestException("No Stripe subscription is linked to this account.");
    }

    await this.stripeService.ensureDefaultPaymentMethodFromSavedCards(company.stripe_customer_id);

    const res = await this.stripeService.attemptPayLatestSubscriptionInvoice(company.stripe_subscription_id);

    return plainToInstance(SubscriptionRetryPaymentResponseDto, {
      success: res.paid === true,
      paid: res.paid === true,
      stripe_invoice_id: res.invoiceId,
      stripe_invoice_status: res.status,
      message: res.paid
        ? "Payment succeeded. Your subscription will remain active."
        : `Payment attempt did not complete (status: ${res.status ?? "unknown"}). Update your card in Payment method and try again.`,
    });
  }

  /**
   * Schedule Stripe subscription cancellation at the end of the current billing period.
   * Local flag is persisted by Stripe webhook reconciliation.
   */
  async scheduleCancelSubscriptionAtPeriodEnd(
    companyId: number,
  ): Promise<SubscriptionCancelAtPeriodEndResponseDto> {
    if (!this.stripeService.isConfigured()) {
      throw new ServiceUnavailableException("Stripe is not configured");
    }

    const company = await this.prisma.client.company.findUnique({
      where: { id: companyId },
      select: { stripe_subscription_id: true },
    });

    if (!company?.stripe_subscription_id) {
      throw new BadRequestException(
        "No Stripe subscription is linked to this account. Cancellation is only available for billed Stripe subscriptions.",
      );
    }

    const localSub = await this.prisma.client.subscription.findFirst({
      where: { company_id: companyId, is_current: true },
      select: { id: true, stripe_subscription_id: true, stripe_cancel_at_period_end: true },
    });

    if (localSub?.stripe_subscription_id && localSub.stripe_subscription_id !== company.stripe_subscription_id) {
      throw new BadRequestException(
        "Your current plan subscription does not match the active Stripe subscription. Please contact support.",
      );
    }

    const updatedStripeSub = await this.stripeService.setSubscriptionCancelAtPeriodEnd(
      company.stripe_subscription_id,
      true,
    );

    // Local DB flag is reconciled by Stripe webhook `customer.subscription.updated`.

    await this.stripePaymentService.sendBillingSubscriptionScheduledCancelEmail(companyId, updatedStripeSub, {
      triggeredBy: "portal_schedule_cancel_at_period_end",
      subscriptionRowId: localSub?.id,
    });

    if (localSub?.id) {
      const oldVal = localSub.stripe_cancel_at_period_end === true;
      const newVal = true;
      await this.systemLogService.logUpdate(
        SystemLogEntityType.SUBSCRIPTION,
        localSub.id,
        { stripe_cancel_at_period_end: oldVal, stripe_subscription_id: localSub.stripe_subscription_id } as Record<
          string,
          unknown
        >,
        {
          stripe_cancel_at_period_end: newVal,
          stripe_subscription_id: company.stripe_subscription_id,
          source: "portal",
          action: "schedule_cancel_at_period_end",
        } as Record<string, unknown>,
        {
          stripe_cancel_at_period_end: { old: oldVal, new: newVal },
        } as Record<string, unknown>,
        { company_id: companyId },
      );
    }

    this.logger.log(
      `Scheduled Stripe subscription cancel at period end for company ${companyId} (${company.stripe_subscription_id})`,
    );

    return this.toDto(
      {
        stripe_cancel_at_period_end: true,
        message:
          "Your subscription will stay active until the end of the current billing period, then it will end. You can reactivate before then in Stripe if needed.",
      },
      SubscriptionCancelAtPeriodEndResponseDto,
    );
  }

  /**
   * Clear subscription-expiry flag when the company has a current ACTIVE subscription and
   * `next_billing_date` is set and still in the future. Used after upgrade, paid checkout, or auto-payment changes.
   */
  private async clearCompanySubscriptionExpiryWhenRenewalIsAhead(companyId: number): Promise<void> {
    const now = new Date();
    const viable = await this.prisma.client.subscription.findFirst({
      where: {
        company_id: companyId,
        is_current: true,
        status: SubscriptionStatus.ACTIVE,
        next_billing_date: { gt: now },
      },
      select: { id: true },
    });
    if (!viable) {
      return;
    }
    await this.prisma.client.company.update({
      where: { id: companyId },
      data: { is_subscription_expiry: false },
    });
  }

  private toNumber(value: Decimal | number | null | undefined): number | null {
    if (value === null || value === undefined) {
      return null;
    }

    if (value instanceof Decimal) {
      return Number(value.toString());
    }

    return Number(value);
  }

  /**
   * Paid product ladder: Startup → Growth → Enterprise. Trial/free types are not ranked here.
   */
  private paidProductTierRank(packageType: PackageType): number | null {
    switch (packageType) {
      case PackageType.STARTUP:
        return 1;
      case PackageType.GROWTH:
        return 2;
      case PackageType.ENTERPRISE:
        return 3;
      default:
        return null;
    }
  }

  /** Blocks Growth→Startup, Enterprise→Growth, Enterprise→Startup, etc. (independent of license totals). */
  private assertNoPaidProductTierDowngrade(fromType: PackageType, toType: PackageType): void {
    const fromRank = this.paidProductTierRank(fromType);
    const toRank = this.paidProductTierRank(toType);
    if (fromRank !== null && toRank !== null && toRank < fromRank) {
      throw new BadRequestException(
        "Downgrading to a lower product tier is not supported (for example Growth to Startup, or Enterprise to Growth or Startup). Contact support if you need to reduce your plan.",
      );
    }
  }

  private validateLicenseCount(licenseCount: number, min: number, max: number): void {
    if (licenseCount < min) {
      throw new BadRequestException(`Minimum license count for this package is ${min}`);
    }

    if (max > 0 && licenseCount > max) {
      throw new BadRequestException(`Maximum license count for this package is ${max}`);
    }
  }

  private enrichPackage(pkg: Package, currentPackageId: number | null) {
    const colorMap: Record<PackageType, string> = {
      FREE_TRIAL: "purple",
      STARTUP: "orange",
      GROWTH: "blue",
      ENTERPRISE: "green",
      PRIVATE_VIP_TRIAL: "purple",
    };

    const iconMap: Record<PackageType, string> = {
      FREE_TRIAL: "pi-clock",
      STARTUP: "pi-users",
      GROWTH: "pi-chart-line",
      ENTERPRISE: "pi-star",
      PRIVATE_VIP_TRIAL: "pi-star",
    };

    const features =
      pkg.features
        ?.split("\n")
        .map((feature) => feature.trim())
        .filter((feature) => feature.length > 0) ?? [];

    let badge: string;
    if (pkg.is_trial_package) {
      const trialDays = pkg.trial_duration_days ?? 7;
      badge = `${trialDays}-day trial`;
    } else if (pkg.license_count_end === 0 || pkg.license_count_end >= 999999) {
      badge = `${pkg.license_count_start}+ licenses`;
    } else {
      badge = `${pkg.license_count_start}-${pkg.license_count_end} licenses`;
    }

    const pricePerLicence = this.toNumber(pkg.price_per_licence);

    let priceDisplay: string;
    let pricePrefix = "";
    let priceSuffix = "";

    if (pkg.is_trial_package) {
      priceDisplay = "FREE";
    } else if (pricePerLicence !== null) {
      priceDisplay = pricePerLicence.toString();
      pricePrefix = "$";
      priceSuffix = "/license/month";
    } else {
      priceDisplay = "Contact Us";
    }

    const buttonLabel = pkg.is_trial_package ? "Try Now" : "Select Plan";

    const color = colorMap[pkg.package_type] ?? "orange";
    const icon = iconMap[pkg.package_type] ?? "pi-box";

    return {
      ...pkg,
      price_per_licence: pricePerLicence,
      is_current: pkg.id === currentPackageId,
      color,
      icon,
      badge,
      price_display: priceDisplay,
      price_prefix: pricePrefix,
      price_suffix: priceSuffix,
      button_label: buttonLabel,
      features,
    };
  }

  /** Matches portal preview {@link mid_cycle_pricing_detail} wording for storage on the invoice row. */
  private formatMidCycleBillingReferenceNotes(
    mid: {
      grossNewTerm: number;
      credit: number;
      net: number;
      remainderDays: number;
      periodDays: number;
      creditBasisDays: number;
    },
    variant: "same_package" | "plan_change",
    oldLicenseCount: number,
  ): string {
    if (variant === "plan_change") {
      return (
        `Full new term $${mid.grossNewTerm.toFixed(2)} − unused credit (old plan) $${mid.credit.toFixed(2)} ` +
        `(${mid.remainderDays}/${mid.periodDays} days left in current term; credit vs ${mid.creditBasisDays}-day paid basis at ${oldLicenseCount} licenses) = $${mid.net.toFixed(2)}`
      );
    }
    return (
      `Full new term $${mid.grossNewTerm.toFixed(2)} − unused credit $${mid.credit.toFixed(2)} ` +
      `(${mid.remainderDays}/${mid.periodDays} days left in term; credit vs ${mid.creditBasisDays}-day paid basis at ${oldLicenseCount} licenses) = $${mid.net.toFixed(2)}`
    );
  }
}