<?php
/**
 * Subscription repository.
 *
 * @package Lucrativeit_Subscription_Recurring_Billing
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Data access for subscriptions.
 */
class LUCRSURE_Subscription_Repository extends LUCRSURE_Repository {

	/**
	 * Table base name.
	 *
	 * @var string
	 */
	protected $table = 'subscriptions';

	/**
	 * Column formats.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array(
		'customer_id'             => '%d',
		'plan_id'                 => '%d',
		'status'                  => '%s',
		'quantity'                => '%d',
		'mrr'                     => '%f',
		'start_date'              => '%s',
		'trial_ends'              => '%s',
		'current_period_start'    => '%s',
		'current_period_end'      => '%s',
		'canceled_at'             => '%s',
		'gateway'                 => '%s',
		'gateway_subscription_id' => '%s',
		'auto_renew'              => '%d',
		'wc_subscription_id'      => '%d',
		'created_at'              => '%s',
		'updated_at'              => '%s',
	);

	/**
	 * Query subscriptions joined with customer and plan info.
	 *
	 * @param array $args { search, status, limit, offset }.
	 * @return array[]
	 */
	public function query( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'search' => '',
				'status' => '',
				'limit'  => 50,
				'offset' => 0,
			)
		);

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

		if ( '' !== $args['search'] ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = 'c.name LIKE %s';
			$params[] = $like;
		}
		if ( '' !== $args['status'] ) {
			$where[]  = 's.status = %s';
			$params[] = $args['status'];
		}

		$sql = 'SELECT s.*, c.name AS customer_name, c.email AS customer_email, p.name AS plan_name, p.currency AS plan_currency
			FROM %i s
			LEFT JOIN %i c ON c.id = s.customer_id
			LEFT JOIN %i p ON p.id = s.plan_id
			WHERE ' . implode( ' AND ', $where ) . ' ORDER BY s.created_at DESC LIMIT %d OFFSET %d';

		$prepare_args = array_merge(
			array( lucrsure_table( 'subscriptions' ), lucrsure_table( 'customers' ), lucrsure_table( 'plans' ) ),
			$params,
			array( (int) $args['limit'], (int) $args['offset'] )
		);

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query prepared below; WHERE uses placeholders only.
			$wpdb->prepare( $sql, ...$prepare_args ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);
	}

	/**
	 * Find a subscription by its gateway subscription id (e.g. Stripe sub id).
	 *
	 * @param string $gateway_sub_id Gateway subscription id.
	 * @return array|null
	 */
	public function find_by_gateway_subscription( $gateway_sub_id ) {
		global $wpdb;
		if ( '' === $gateway_sub_id ) {
			return null;
		}
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT * FROM %i WHERE gateway_subscription_id = %s', $this->table(), $gateway_sub_id ),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Sum of MRR across active subscriptions.
	 *
	 * @return float
	 */
	public function total_mrr() {
		global $wpdb;
		return (float) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT COALESCE(SUM(mrr),0) FROM %i WHERE status IN ('active','trialing')",
				$this->table()
			)
		);
	}

	/**
	 * Count subscriptions by status.
	 *
	 * @param string $status Status.
	 * @return int
	 */
	public function count_status( $status ) {
		return $this->count( 'status', $status );
	}

	/**
	 * Sum MRR for subscriptions that were active at a point in time.
	 *
	 * @param string $datetime MySQL datetime.
	 * @return float
	 */
	public function mrr_as_of( $datetime ) {
		global $wpdb;

		return (float) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT COALESCE(SUM(mrr),0) FROM %i WHERE start_date <= %s AND (canceled_at IS NULL OR canceled_at > %s)',
				$this->table(),
				$datetime,
				$datetime
			)
		);
	}

	/**
	 * Count subscriptions that were active at a point in time.
	 *
	 * @param string $datetime MySQL datetime.
	 * @return int
	 */
	public function active_count_as_of( $datetime ) {
		global $wpdb;

		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE start_date <= %s AND (canceled_at IS NULL OR canceled_at > %s)',
				$this->table(),
				$datetime,
				$datetime
			)
		);
	}

	/**
	 * Count subscriptions canceled on or before a datetime.
	 *
	 * @param string $datetime MySQL datetime.
	 * @return int
	 */
	public function canceled_count_as_of( $datetime ) {
		global $wpdb;

		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE canceled_at IS NOT NULL AND canceled_at <= %s',
				$this->table(),
				$datetime
			)
		);
	}

	/**
	 * Churn rate snapshot: canceled / (active + canceled) at a datetime.
	 *
	 * @param string $datetime MySQL datetime.
	 * @return float
	 */
	public function churn_rate_as_of( $datetime ) {
		$active   = $this->active_count_as_of( $datetime );
		$canceled = $this->canceled_count_as_of( $datetime );
		$total    = max( 1, $active + $canceled );

		return round( ( $canceled / $total ) * 100, 1 );
	}

	/**
	 * Subscriptions due for renewal on/before a datetime.
	 *
	 * @param string $datetime MySQL datetime.
	 * @return array[]
	 */
	public function due_for_renewal( $datetime ) {
		global $wpdb;
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				"SELECT * FROM %i WHERE status IN ('active','trialing') AND auto_renew = 1 AND current_period_end IS NOT NULL AND current_period_end <= %s",
				$this->table(),
				$datetime
			),
			ARRAY_A
		);
	}
}
