<?php
/**
 * GGM Commerce Subscriptions Class
 * Core subscription manager for recurring billing
 *
 * @package GGM_Commerce
 */

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

/**
 * Subscriptions class for managing recurring billing
 */
class GGM_Commerce_Subscriptions {

	/**
	 * Premium widget slug for license checking
	 */
	const PREMIUM_SLUG = 'subscription-billing';

	/**
	 * Subscription statuses
	 */
	const STATUS_PENDING        = 'pending';
	const STATUS_ACTIVE         = 'active';
	const STATUS_ON_HOLD        = 'on_hold';
	const STATUS_PAUSED         = 'paused';
	const STATUS_CANCELLED      = 'cancelled';
	const STATUS_EXPIRED        = 'expired';
	const STATUS_PENDING_CANCEL = 'pending_cancel';
	const STATUS_TRIAL          = 'trial';
	const STATUS_FAILED_PAYMENT = 'failed_payment';

	/**
	 * Billing periods
	 */
	const PERIOD_DAY   = 'day';
	const PERIOD_WEEK  = 'week';
	const PERIOD_MONTH = 'month';
	const PERIOD_YEAR  = 'year';

	/**
	 * Singleton instance
	 *
	 * @var GGM_Commerce_Subscriptions
	 */
	private static $instance = null;

	/**
	 * Table names
	 *
	 * @var string
	 */
	private $table;
	private $items_table;
	private $log_table;
	private $retries_table;

	/**
	 * Get singleton instance
	 *
	 * @return GGM_Commerce_Subscriptions
	 */
	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Alias for get_instance().
	 *
	 * @return GGM_Commerce_Subscriptions
	 */
	public static function instance() {
		return self::get_instance();
	}

	/**
	 * Constructor
	 */
	private function __construct() {
		global $wpdb;
		$this->table         = $wpdb->prefix . 'ggmc_subscriptions';
		$this->items_table   = $wpdb->prefix . 'ggmc_subscription_items';
		$this->log_table     = $wpdb->prefix . 'ggmc_subscription_log';
		$this->retries_table = $wpdb->prefix . 'ggmc_subscription_payment_retries';
	}

	/**
	 * Check if subscription feature is activated
	 *
	 * @return bool
	 */
	public static function is_activated() {
		if ( ! class_exists( 'GGM_Commerce_License' ) ) {
			return false;
		}
		return GGM_Commerce_License::instance()->is_widget_activated( self::PREMIUM_SLUG );
	}

	/**
	 * Get default subscription settings
	 *
	 * @return array
	 */
	public static function get_default_settings() {
		return array(
			'retry_schedule'                     => array( 1, 3, 5 ),
			'grace_period_days'                  => 7,
			'max_retries'                        => 3,
			'auto_cancel_on_exhausted_retries'   => true,
			'notify_customer_on_retry'           => true,
			'notify_admin_on_failure'            => true,
			'allow_pause'                        => true,
			'allow_cancel'                       => true,
			'allow_reactivate'                   => true,
			'allow_change_plan'                  => true,
			'allow_change_payment'               => true,
			'max_pause_duration_days'            => 90,
			'cancellation_behavior'              => 'end_of_period',
		);
	}

	/**
	 * Get subscription settings
	 *
	 * @return array
	 */
	public static function get_settings() {
		$defaults = self::get_default_settings();
		$saved    = get_option( 'ggm_commerce_subscription_settings', array() );
		return wp_parse_args( $saved, $defaults );
	}

	/**
	 * Generate a unique subscription number
	 *
	 * @return string
	 */
	public function generate_subscription_number() {
		global $wpdb;

		do {
			$number = 'SUB-' . strtoupper( wp_generate_password( 8, false ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$this->table} WHERE subscription_number = %s",
					$number
				)
			);
		} while ( $exists );

		return $number;
	}

	/**
	 * Create a new subscription
	 *
	 * @param array $args Subscription data.
	 * @return int|false Subscription ID or false on failure.
	 */
	public function create_subscription( $args ) {
		global $wpdb;

		$defaults = array(
			'subscription_number'    => $this->generate_subscription_number(),
			'customer_id'            => 0,
			'customer_email'         => '',
			'parent_order_id'        => 0,
			'product_id'             => 0,
			'variation_id'           => null,
			'quantity'               => 1,
			'billing_interval'       => 1,
			'billing_period'         => self::PERIOD_MONTH,
			'recurring_amount'       => 0.00,
			'tax_amount'             => 0.00,
			'shipping_amount'        => 0.00,
			'recurring_total'        => 0.00,
			'currency'               => 'GBP',
			'trial_length'           => 0,
			'trial_period'           => self::PERIOD_DAY,
			'trial_end_date'         => null,
			'payment_gateway'        => '',
			'gateway_subscription_id' => null,
			'gateway_customer_id'    => null,
			'payment_method_token'   => null,
			'start_date'             => current_time( 'mysql', true ),
			'next_payment_date'      => null,
			'end_date'               => null,
			'status'                 => self::STATUS_PENDING,
			'requires_manual_renewal' => 0,
		);

		$data = wp_parse_args( $args, $defaults );

		// Calculate trial end date if trial is set.
		if ( $data['trial_length'] > 0 && empty( $data['trial_end_date'] ) ) {
			$data['trial_end_date'] = $this->calculate_date_from_period(
				$data['start_date'],
				$data['trial_length'],
				$data['trial_period']
			);
			$data['status']            = self::STATUS_TRIAL;
			$data['next_payment_date'] = $data['trial_end_date'];
		} elseif ( empty( $data['next_payment_date'] ) ) {
			$data['next_payment_date'] = $this->calculate_date_from_period(
				$data['start_date'],
				$data['billing_interval'],
				$data['billing_period']
			);
		}

		// Calculate end date if subscription has a fixed length.
		$product_data = $this->get_product_subscription_data( $data['product_id'] );
		if ( $product_data && $product_data['subscription_length'] > 0 ) {
			$total_periods = $product_data['subscription_length'] * $data['billing_interval'];
			$data['end_date'] = $this->calculate_date_from_period(
				$data['start_date'],
				$total_periods,
				$data['billing_period']
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert( $this->table, $data );

		if ( false === $result ) {
			return false;
		}

		$subscription_id = $wpdb->insert_id;

		$this->log_event( $subscription_id, 'created', 'Subscription created.' );

		/**
		 * Fires after a subscription is created.
		 *
		 * @param int   $subscription_id The subscription ID.
		 * @param array $data            The subscription data.
		 */
		do_action( 'ggmc_subscription_created', $subscription_id, $data );

		return $subscription_id;
	}

	/**
	 * Get a subscription by ID
	 *
	 * @param int $id Subscription ID.
	 * @return object|null Subscription object or null.
	 */
	public function get_subscription( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d",
				$id
			)
		);
	}

	/**
	 * Get a subscription by subscription number
	 *
	 * @param string $number Subscription number.
	 * @return object|null
	 */
	public function get_subscription_by_number( $number ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE subscription_number = %s",
				$number
			)
		);
	}

	/**
	 * Get a subscription by gateway subscription ID
	 *
	 * @param string $gateway_id Gateway subscription ID.
	 * @return object|null
	 */
	public function get_subscription_by_gateway_id( $gateway_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE gateway_subscription_id = %s",
				$gateway_id
			)
		);
	}

	/**
	 * Update a subscription
	 *
	 * @param int   $id   Subscription ID.
	 * @param array $data Data to update.
	 * @return bool True on success.
	 */
	public function update_subscription( $id, $data ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$this->table,
			$data,
			array( 'id' => $id )
		);

		return false !== $result;
	}

	/**
	 * Activate a subscription
	 *
	 * @param int $id Subscription ID.
	 * @return bool
	 */
	public function activate_subscription( $id ) {
		$subscription = $this->get_subscription( $id );
		if ( ! $subscription ) {
			return false;
		}

		$update_data = array(
			'status' => self::STATUS_ACTIVE,
		);

		// If transitioning from trial, set the first real payment date.
		if ( self::STATUS_TRIAL === $subscription->status ) {
			$update_data['next_payment_date'] = $this->calculate_next_payment_date( $subscription );
		}

		$result = $this->update_subscription( $id, $update_data );

		if ( $result ) {
			$this->log_event( $id, 'activated', 'Subscription activated.' );
			do_action( 'ggmc_subscription_activated', $id );
		}

		return $result;
	}

	/**
	 * Pause a subscription
	 *
	 * @param int    $id           Subscription ID.
	 * @param string $initiated_by Who initiated (customer/admin/system).
	 * @return bool
	 */
	public function pause_subscription( $id, $initiated_by = 'customer' ) {
		$subscription = $this->get_subscription( $id );
		if ( ! $subscription || ! in_array( $subscription->status, array( self::STATUS_ACTIVE, self::STATUS_TRIAL ), true ) ) {
			return false;
		}

		$result = $this->update_subscription( $id, array(
			'status'    => self::STATUS_PAUSED,
			'paused_at' => current_time( 'mysql', true ),
		) );

		if ( $result ) {
			$this->log_event( $id, 'paused', 'Subscription paused.', null, null, $initiated_by );
			do_action( 'ggmc_subscription_paused', $id );
		}

		return $result;
	}

	/**
	 * Resume a paused subscription
	 *
	 * @param int    $id           Subscription ID.
	 * @param string $initiated_by Who initiated.
	 * @return bool
	 */
	public function resume_subscription( $id, $initiated_by = 'customer' ) {
		$subscription = $this->get_subscription( $id );
		if ( ! $subscription || self::STATUS_PAUSED !== $subscription->status ) {
			return false;
		}

		// Recalculate next payment date from now.
		$next_payment = $this->calculate_date_from_period(
			current_time( 'mysql', true ),
			(int) $subscription->billing_interval,
			$subscription->billing_period
		);

		$result = $this->update_subscription( $id, array(
			'status'            => self::STATUS_ACTIVE,
			'paused_at'         => null,
			'next_payment_date' => $next_payment,
		) );

		if ( $result ) {
			$this->log_event( $id, 'resumed', 'Subscription resumed.', null, null, $initiated_by );
			do_action( 'ggmc_subscription_resumed', $id );
		}

		return $result;
	}

	/**
	 * Cancel a subscription
	 *
	 * @param int    $id           Subscription ID.
	 * @param string $reason       Cancellation reason.
	 * @param string $initiated_by Who initiated.
	 * @return bool
	 */
	public function cancel_subscription( $id, $reason = '', $initiated_by = 'customer' ) {
		$subscription = $this->get_subscription( $id );
		if ( ! $subscription ) {
			return false;
		}

		$settings = self::get_settings();

		// Determine if cancellation is immediate or end-of-period.
		if ( 'end_of_period' === $settings['cancellation_behavior'] && self::STATUS_ACTIVE === $subscription->status ) {
			$status = self::STATUS_PENDING_CANCEL;
		} else {
			$status = self::STATUS_CANCELLED;
		}

		$result = $this->update_subscription( $id, array(
			'status'              => $status,
			'cancellation_reason' => $reason,
			'cancelled_at'        => current_time( 'mysql', true ),
		) );

		if ( $result ) {
			$this->log_event( $id, 'cancelled', 'Subscription cancelled. Reason: ' . $reason, null, null, $initiated_by );
			do_action( 'ggmc_subscription_cancelled', $id );
		}

		return $result;
	}

	/**
	 * Expire a subscription
	 *
	 * @param int $id Subscription ID.
	 * @return bool
	 */
	public function expire_subscription( $id ) {
		$result = $this->update_subscription( $id, array(
			'status' => self::STATUS_EXPIRED,
		) );

		if ( $result ) {
			$this->log_event( $id, 'expired', 'Subscription expired.' );
			do_action( 'ggmc_subscription_expired', $id );
		}

		return $result;
	}

	/**
	 * Put a subscription on hold (usually due to failed payment)
	 *
	 * @param int    $id     Subscription ID.
	 * @param string $reason Reason for hold.
	 * @return bool
	 */
	public function hold_subscription( $id, $reason = '' ) {
		$result = $this->update_subscription( $id, array(
			'status' => self::STATUS_ON_HOLD,
		) );

		if ( $result ) {
			$this->log_event( $id, 'on_hold', 'Subscription put on hold. ' . $reason );
			do_action( 'ggmc_subscription_on_hold', $id );
		}

		return $result;
	}

	/**
	 * Get customer subscriptions
	 *
	 * @param int    $customer_id Customer ID.
	 * @param string $status      Optional status filter.
	 * @return array
	 */
	public function get_customer_subscriptions( $customer_id, $status = '' ) {
		global $wpdb;

		if ( ! empty( $status ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$this->table} WHERE customer_id = %d AND status = %s ORDER BY created_at DESC",
					$customer_id,
					$status
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE customer_id = %d ORDER BY created_at DESC",
				$customer_id
			)
		);
	}

	/**
	 * Get customer subscriptions by email
	 *
	 * @param string $email  Customer email.
	 * @param string $status Optional status filter.
	 * @return array
	 */
	public function get_customer_subscriptions_by_email( $email, $status = '' ) {
		global $wpdb;

		if ( ! empty( $status ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$this->table} WHERE customer_email = %s AND status = %s ORDER BY created_at DESC",
					$email,
					$status
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE customer_email = %s ORDER BY created_at DESC",
				$email
			)
		);
	}

	/**
	 * Get subscriptions due for renewal
	 *
	 * @param string|null $date Date to check against (default: now).
	 * @return array
	 */
	public function get_subscriptions_due_for_renewal( $date = null ) {
		global $wpdb;

		if ( null === $date ) {
			$date = current_time( 'mysql', true );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE next_payment_date <= %s AND status = %s ORDER BY next_payment_date ASC",
				$date,
				self::STATUS_ACTIVE
			)
		);
	}

	/**
	 * Get subscriptions in grace period (on_hold status with retries pending)
	 *
	 * @return array
	 */
	public function get_subscriptions_in_grace_period() {
		global $wpdb;

		$settings     = self::get_settings();
		$grace_cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $settings['grace_period_days'] . ' days' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE status = %s AND last_payment_date < %s",
				self::STATUS_ON_HOLD,
				$grace_cutoff
			)
		);
	}

	/**
	 * Get subscription items
	 *
	 * @param int $subscription_id Subscription ID.
	 * @return array
	 */
	public function get_subscription_items( $subscription_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->items_table} WHERE subscription_id = %d",
				$subscription_id
			)
		);
	}

	/**
	 * Add an item to a subscription
	 *
	 * @param int   $subscription_id Subscription ID.
	 * @param array $item_data       Item data.
	 * @return int|false Item ID or false.
	 */
	public function add_subscription_item( $subscription_id, $item_data ) {
		global $wpdb;

		$item_data['subscription_id'] = $subscription_id;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert( $this->items_table, $item_data );

		return $result ? $wpdb->insert_id : false;
	}

	/**
	 * Get subscription log entries
	 *
	 * @param int $subscription_id Subscription ID.
	 * @param int $limit           Max entries.
	 * @return array
	 */
	public function get_subscription_log( $subscription_id, $limit = 50 ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->log_table} WHERE subscription_id = %d ORDER BY created_at DESC LIMIT %d",
				$subscription_id,
				$limit
			)
		);
	}

	/**
	 * Log a subscription event
	 *
	 * @param int         $subscription_id Subscription ID.
	 * @param string      $event_type      Event type.
	 * @param string      $description     Description.
	 * @param int|null    $order_id        Related order ID.
	 * @param float|null  $amount          Amount.
	 * @param string      $initiated_by    Who initiated (system/customer/admin/gateway).
	 * @param array|null  $metadata        Additional metadata.
	 */
	public function log_event( $subscription_id, $event_type, $description, $order_id = null, $amount = null, $initiated_by = 'system', $metadata = null ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$this->log_table,
			array(
				'subscription_id' => $subscription_id,
				'event_type'      => $event_type,
				'description'     => $description,
				'order_id'        => $order_id,
				'amount'          => $amount,
				'initiated_by'    => $initiated_by,
				'user_id'         => get_current_user_id() ?: null,
				'metadata'        => $metadata ? wp_json_encode( $metadata ) : null,
			)
		);
	}

	/**
	 * Check if a product is a subscription product
	 *
	 * @param int $product_id Product ID.
	 * @return bool
	 */
	public function is_product_subscription( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$is_sub = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT is_subscription FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			)
		);

		return (bool) $is_sub;
	}

	/**
	 * Get product subscription data
	 *
	 * @param int $product_id Product ID.
	 * @return array|null Subscription data or null if not a subscription product.
	 */
	public function get_product_subscription_data( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$product = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT is_subscription, subscription_period, subscription_interval,
				        subscription_trial_length, subscription_trial_period,
				        subscription_sign_up_fee, subscription_length, price, sale_price
				 FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			),
			ARRAY_A
		);

		if ( ! $product || ! (int) $product['is_subscription'] ) {
			return null;
		}

		return $product;
	}

	/**
	 * Calculate next payment date from a subscription
	 *
	 * @param object $subscription Subscription object.
	 * @return string MySQL datetime string.
	 */
	public function calculate_next_payment_date( $subscription ) {
		$from_date = ! empty( $subscription->last_payment_date )
			? $subscription->last_payment_date
			: $subscription->start_date;

		return $this->calculate_date_from_period(
			$from_date,
			(int) $subscription->billing_interval,
			$subscription->billing_period
		);
	}

	/**
	 * Calculate a future date based on a period
	 *
	 * @param string $from_date MySQL datetime string.
	 * @param int    $interval  Number of periods.
	 * @param string $period    Period type (day/week/month/year).
	 * @return string MySQL datetime string.
	 */
	public function calculate_date_from_period( $from_date, $interval, $period ) {
		$timestamp = strtotime( $from_date );

		switch ( $period ) {
			case self::PERIOD_DAY:
				$timestamp = strtotime( "+{$interval} days", $timestamp );
				break;
			case self::PERIOD_WEEK:
				$timestamp = strtotime( "+{$interval} weeks", $timestamp );
				break;
			case self::PERIOD_MONTH:
				$timestamp = strtotime( "+{$interval} months", $timestamp );
				break;
			case self::PERIOD_YEAR:
				$timestamp = strtotime( "+{$interval} years", $timestamp );
				break;
		}

		return gmdate( 'Y-m-d H:i:s', $timestamp );
	}

	/**
	 * Get all valid billing periods
	 *
	 * @return array
	 */
	public static function get_billing_periods() {
		return array(
			self::PERIOD_DAY   => __( 'day(s)', 'ggm-commerce' ),
			self::PERIOD_WEEK  => __( 'week(s)', 'ggm-commerce' ),
			self::PERIOD_MONTH => __( 'month(s)', 'ggm-commerce' ),
			self::PERIOD_YEAR  => __( 'year(s)', 'ggm-commerce' ),
		);
	}

	/**
	 * Get all subscription statuses
	 *
	 * @return array
	 */
	public static function get_statuses() {
		return array(
			self::STATUS_PENDING        => __( 'Pending', 'ggm-commerce' ),
			self::STATUS_ACTIVE         => __( 'Active', 'ggm-commerce' ),
			self::STATUS_ON_HOLD        => __( 'On Hold', 'ggm-commerce' ),
			self::STATUS_PAUSED         => __( 'Paused', 'ggm-commerce' ),
			self::STATUS_CANCELLED      => __( 'Cancelled', 'ggm-commerce' ),
			self::STATUS_EXPIRED        => __( 'Expired', 'ggm-commerce' ),
			self::STATUS_PENDING_CANCEL => __( 'Pending Cancellation', 'ggm-commerce' ),
			self::STATUS_TRIAL          => __( 'Trial', 'ggm-commerce' ),
			self::STATUS_FAILED_PAYMENT => __( 'Failed Payment', 'ggm-commerce' ),
		);
	}

	/**
	 * Get all subscriptions with optional filters
	 *
	 * @param array $args Query arguments.
	 * @return array Array with 'subscriptions' and 'total' keys.
	 */
	public function get_subscriptions( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'   => '',
			'search'   => '',
			'orderby'  => 'created_at',
			'order'    => 'DESC',
			'per_page' => 20,
			'page'     => 1,
		);

		$args = wp_parse_args( $args, $defaults );

		$where        = array( '1=1' );
		$where_values = array();

		if ( ! empty( $args['status'] ) ) {
			$where[]        = 'status = %s';
			$where_values[] = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where[]        = '(subscription_number LIKE %s OR customer_email LIKE %s)';
			$search_term    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where_values[] = $search_term;
			$where_values[] = $search_term;
		}

		$where_clause = implode( ' AND ', $where );

		$allowed_orderby = array( 'created_at', 'next_payment_date', 'recurring_total', 'status', 'subscription_number' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$offset = ( (int) $args['page'] - 1 ) * (int) $args['per_page'];

		// Get total count.
		$count_query = "SELECT COUNT(*) FROM {$this->table} WHERE {$where_clause}";
		if ( ! empty( $where_values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total = (int) $wpdb->get_var( $wpdb->prepare( $count_query, ...$where_values ) );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total = (int) $wpdb->get_var( $count_query );
		}

		// Get results.
		$query = "SELECT * FROM {$this->table} WHERE {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$query_values   = array_merge( $where_values, array( (int) $args['per_page'], $offset ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$subscriptions = $wpdb->get_results( $wpdb->prepare( $query, ...$query_values ) );

		return array(
			'subscriptions' => $subscriptions,
			'total'         => $total,
		);
	}

	/**
	 * Get related orders for a subscription
	 *
	 * @param int $subscription_id Subscription ID.
	 * @return array
	 */
	public function get_related_orders( $subscription_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_orders WHERE subscription_id = %d ORDER BY created_at DESC",
				$subscription_id
			)
		);
	}

	/**
	 * Schedule a payment retry
	 *
	 * @param int $subscription_id Subscription ID.
	 * @param int $retry_number    Which retry attempt this is.
	 * @return bool
	 */
	public function schedule_retry( $subscription_id, $retry_number ) {
		global $wpdb;

		$settings = self::get_settings();
		$schedule = $settings['retry_schedule'];

		if ( $retry_number > count( $schedule ) ) {
			return false;
		}

		$days_delay   = $schedule[ $retry_number - 1 ];
		$scheduled_at = gmdate( 'Y-m-d H:i:s', strtotime( "+{$days_delay} days" ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert(
			$this->retries_table,
			array(
				'subscription_id' => $subscription_id,
				'retry_number'    => $retry_number,
				'scheduled_at'    => $scheduled_at,
				'result'          => 'pending',
			)
		);

		if ( $result ) {
			$this->log_event(
				$subscription_id,
				'retry_scheduled',
				sprintf( 'Payment retry #%d scheduled for %s.', $retry_number, $scheduled_at )
			);

			do_action( 'ggmc_subscription_payment_retry', $subscription_id, array(
				'retry_number' => $retry_number,
				'scheduled_at' => $scheduled_at,
			) );
		}

		return (bool) $result;
	}

	/**
	 * Get pending retries due for processing
	 *
	 * @return array
	 */
	public function get_pending_retries() {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.*, s.payment_gateway, s.gateway_customer_id, s.payment_method_token,
				        s.recurring_total, s.currency
				 FROM {$this->retries_table} r
				 INNER JOIN {$this->table} s ON r.subscription_id = s.id
				 WHERE r.result = 'pending' AND r.scheduled_at <= %s
				 ORDER BY r.scheduled_at ASC",
				current_time( 'mysql', true )
			)
		);
	}

	/**
	 * Update a retry record
	 *
	 * @param int   $retry_id Retry ID.
	 * @param array $data     Data to update.
	 * @return bool
	 */
	public function update_retry( $retry_id, $data ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return false !== $wpdb->update(
			$this->retries_table,
			$data,
			array( 'id' => $retry_id )
		);
	}

	/**
	 * Get subscription dashboard stats
	 *
	 * @return array
	 */
	public function get_stats() {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$stats = $wpdb->get_row(
			"SELECT
				COUNT(*) as total,
				SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
				SUM(CASE WHEN status = 'trial' THEN 1 ELSE 0 END) as trial,
				SUM(CASE WHEN status = 'paused' THEN 1 ELSE 0 END) as paused,
				SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
				SUM(CASE WHEN status = 'on_hold' THEN 1 ELSE 0 END) as on_hold,
				SUM(CASE WHEN status IN ('active', 'trial') THEN recurring_total ELSE 0 END) as monthly_recurring
			 FROM {$this->table}",
			ARRAY_A
		);

		return $stats ?: array(
			'total'             => 0,
			'active'            => 0,
			'trial'             => 0,
			'paused'            => 0,
			'cancelled'         => 0,
			'on_hold'           => 0,
			'monthly_recurring' => 0,
		);
	}

	// -------------------------------------------------------------------------
	// Plan Change & Proration
	// -------------------------------------------------------------------------

	/**
	 * Calculate proration for a plan change.
	 *
	 * Works out the credit for unused days on the current plan and the charge
	 * for remaining days at the new plan's rate, then returns the net amount.
	 *
	 * @param int $subscription_id  Subscription ID.
	 * @param int $new_product_id   Target product ID (must be a subscription product).
	 * @return array|WP_Error {
	 *     @type float  $credit          Credit for unused days on current plan.
	 *     @type float  $charge          Charge for remaining days on new plan.
	 *     @type float  $proration_amount Net amount (charge - credit). Positive = charge customer.
	 *     @type int    $days_remaining   Days remaining in the current billing period.
	 *     @type int    $total_days       Total days in the current billing period.
	 *     @type float  $old_daily_rate   Daily rate of current plan.
	 *     @type float  $new_daily_rate   Daily rate of new plan.
	 *     @type string $new_recurring_total New plan's recurring total.
	 * }
	 */
	public function calculate_proration( $subscription_id, $new_product_id ) {
		$subscription = $this->get_subscription( $subscription_id );

		if ( ! $subscription ) {
			return new \WP_Error( 'invalid_subscription', __( 'Subscription not found.', 'ggm-commerce' ) );
		}

		if ( ! in_array( $subscription->status, array( 'active', 'trial' ), true ) ) {
			return new \WP_Error( 'invalid_status', __( 'Subscription must be active or in trial to change plans.', 'ggm-commerce' ) );
		}

		// Get new product subscription data.
		$new_product = $this->get_product_subscription_data( $new_product_id );

		if ( ! $new_product || empty( $new_product['is_subscription'] ) ) {
			return new \WP_Error( 'not_subscription', __( 'Target product is not a subscription product.', 'ggm-commerce' ) );
		}

		// Calculate daily rates for each plan.
		$old_total_days = $this->get_period_days( $subscription->billing_period, (int) $subscription->billing_interval );
		$new_total_days = $this->get_period_days( $new_product['subscription_period'], (int) $new_product['subscription_interval'] );

		if ( $old_total_days <= 0 || $new_total_days <= 0 ) {
			return new \WP_Error( 'invalid_period', __( 'Invalid billing period.', 'ggm-commerce' ) );
		}

		$old_daily_rate = (float) $subscription->recurring_total / $old_total_days;

		// New plan price from product.
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$new_price = $wpdb->get_var( $wpdb->prepare(
			"SELECT price FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
			$new_product_id
		) );
		$new_recurring_total = (float) $new_price + (float) $new_product['subscription_sign_up_fee'];
		// Sign-up fee is only for initial purchase, not plan changes.
		$new_recurring_total = (float) $new_price;
		$new_daily_rate      = $new_recurring_total / $new_total_days;

		// Calculate days remaining in the current billing period.
		$now              = time();
		$next_payment     = strtotime( $subscription->next_payment_date );
		$days_remaining   = max( 0, (int) ceil( ( $next_payment - $now ) / DAY_IN_SECONDS ) );

		// Credit for unused days on old plan.
		$credit = round( $old_daily_rate * $days_remaining, 2 );

		// Charge for remaining days at new plan rate.
		$charge = round( $new_daily_rate * $days_remaining, 2 );

		// Net proration: positive = customer owes, negative = customer gets credit.
		$proration_amount = round( $charge - $credit, 2 );

		return array(
			'credit'              => $credit,
			'charge'              => $charge,
			'proration_amount'    => $proration_amount,
			'days_remaining'      => $days_remaining,
			'total_days'          => $old_total_days,
			'old_daily_rate'      => $old_daily_rate,
			'new_daily_rate'      => $new_daily_rate,
			'new_recurring_total' => $new_recurring_total,
		);
	}

	/**
	 * Change a subscription's plan (product).
	 *
	 * @param int  $subscription_id Subscription ID.
	 * @param int  $new_product_id  New product ID.
	 * @param bool $apply_proration Whether to apply proration (charge/credit the difference).
	 * @return true|WP_Error
	 */
	public function change_plan( $subscription_id, $new_product_id, $apply_proration = true ) {
		$subscription = $this->get_subscription( $subscription_id );

		if ( ! $subscription ) {
			return new \WP_Error( 'invalid_subscription', __( 'Subscription not found.', 'ggm-commerce' ) );
		}

		$new_product_data = $this->get_product_subscription_data( $new_product_id );

		if ( ! $new_product_data || empty( $new_product_data['is_subscription'] ) ) {
			return new \WP_Error( 'not_subscription', __( 'Target product is not a subscription product.', 'ggm-commerce' ) );
		}

		// Get new product price.
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$new_product = $wpdb->get_row( $wpdb->prepare(
			"SELECT id, name, sku, price FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
			$new_product_id
		) );

		if ( ! $new_product ) {
			return new \WP_Error( 'product_not_found', __( 'Product not found.', 'ggm-commerce' ) );
		}

		$proration = null;

		if ( $apply_proration ) {
			$proration = $this->calculate_proration( $subscription_id, $new_product_id );

			if ( is_wp_error( $proration ) ) {
				return $proration;
			}

			// Process the proration payment/refund if amount is non-zero.
			if ( abs( $proration['proration_amount'] ) > 0.01 ) {
				$proration_result = $this->process_proration_payment( $subscription, $proration );

				if ( is_wp_error( $proration_result ) ) {
					return $proration_result;
				}
			}
		}

		// Update the subscription record.
		$update_data = array(
			'product_id'        => $new_product_id,
			'recurring_amount'  => (float) $new_product->price,
			'recurring_total'   => (float) $new_product->price,
			'billing_period'    => $new_product_data['subscription_period'],
			'billing_interval'  => (int) $new_product_data['subscription_interval'],
		);

		// If the billing cycle changed, recalculate next_payment_date.
		if (
			$subscription->billing_period !== $new_product_data['subscription_period'] ||
			(int) $subscription->billing_interval !== (int) $new_product_data['subscription_interval']
		) {
			$update_data['next_payment_date'] = $this->calculate_next_payment_date(
				$new_product_data['subscription_period'],
				(int) $new_product_data['subscription_interval']
			);
		}

		$this->update_subscription( $subscription_id, $update_data );

		// Update subscription items.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->update(
			$this->items_table,
			array(
				'product_id'      => $new_product_id,
				'product_name'    => $new_product->name,
				'product_sku'     => $new_product->sku ?? '',
				'recurring_price' => (float) $new_product->price,
				'recurring_total' => (float) $new_product->price,
			),
			array( 'subscription_id' => $subscription_id )
		);

		// Log the plan change.
		$proration_note = '';
		if ( $proration && abs( $proration['proration_amount'] ) > 0.01 ) {
			$proration_note = $proration['proration_amount'] > 0
				? sprintf( ' Prorated charge: %s.', ggm_commerce_format_price( $proration['proration_amount'] ) )
				: sprintf( ' Prorated credit: %s.', ggm_commerce_format_price( abs( $proration['proration_amount'] ) ) );
		}

		$this->log_event(
			$subscription_id,
			'plan_changed',
			sprintf(
				'Plan changed to %s (%s).%s',
				$new_product->name,
				ggm_commerce_format_price( $new_product->price ),
				$proration_note
			),
			null,
			$proration ? abs( $proration['proration_amount'] ) : null,
			'system'
		);

		do_action( 'ggmc_subscription_plan_changed', $subscription_id, $new_product_id, $proration );

		return true;
	}

	/**
	 * Process a proration payment (charge or refund).
	 *
	 * @param object $subscription Subscription object.
	 * @param array  $proration    Proration data from calculate_proration().
	 * @return true|WP_Error
	 */
	private function process_proration_payment( $subscription, $proration ) {
		$amount = $proration['proration_amount'];

		if ( 'stripe' === $subscription->payment_gateway ) {
			$gateway = ggm_commerce_gateways()->get_gateway( 'stripe' );

			if ( ! $gateway || ! $gateway->is_available() ) {
				return new \WP_Error( 'gateway_unavailable', __( 'Payment gateway not available.', 'ggm-commerce' ) );
			}

			if ( $amount > 0 ) {
				// Charge the customer for the upgrade difference.
				$result = $gateway->create_renewal_payment( $subscription, null, $amount );

				if ( empty( $result['success'] ) || empty( $result['data']['paid'] ) ) {
					return new \WP_Error( 'proration_charge_failed', __( 'Failed to process proration charge.', 'ggm-commerce' ) );
				}
			}
			// Negative proration (downgrade credit) is applied as a credit on the next invoice,
			// not as an immediate refund. The next renewal will be reduced accordingly.
		}

		// PayPal subscriptions: plan changes require creating a new PayPal subscription.
		// This is handled at the gateway level when the customer approves the new plan.
		// For now, we log the proration but the actual payment change happens via PayPal's flow.

		return true;
	}

	/**
	 * Convert a billing period + interval to approximate number of days.
	 *
	 * @param string $period   Billing period (day, week, month, year).
	 * @param int    $interval Billing interval.
	 * @return int
	 */
	private function get_period_days( $period, $interval ) {
		switch ( $period ) {
			case 'day':
				return $interval;
			case 'week':
				return $interval * 7;
			case 'month':
				return $interval * 30;
			case 'year':
				return $interval * 365;
			default:
				return $interval * 30;
		}
	}
}
