<?php
/**
 * Data function get_customer_retention.
 *
 * @package Askshelf
 */

declare( strict_types = 1 );

namespace Askshelf\Tools;

use Askshelf\Data\Analytics;
use Askshelf\Data\Orders;
use Askshelf\Data\Period;
use Askshelf\Settings;

defined( 'ABSPATH' ) || exit;

/**
 * New and returning customers, repeat purchase rate and lifetime spend. Counts and
 * averages only: never names, emails or other contact details.
 */
final class CustomerRetention implements Tool {

	/** {@inheritDoc} */
	public function name(): string {
		return 'get_customer_retention';
	}

	/** {@inheritDoc} */
	public function scopes(): array {
		return array( Settings::SCOPE_ORDERS );
	}

	/** {@inheritDoc} */
	public function schema(): array {
		return Schema::object(
			array(
				'start_date' => Schema::date(),
				'end_date'   => Schema::date(),
				'compare'    => Schema::compare(),
			),
			array( 'start_date', 'end_date' )
		);
	}

	/**
	 * Runs the data function.
	 *
	 * @param array<string, mixed> $args Validated arguments.
	 * @return array<string, mixed>
	 */
	public function run( array $args ): array {
		$period   = Schema::period( $args );
		$current  = self::period_stats( $period );
		$all_time = self::all_time();

		$result = array(
			'period'    => $period->to_array(),
			'customers' => $current,
			'all_time'  => $all_time,
			'rows_read' => $current['orders'] + $all_time['customers'],
			'notes'     => array(
				'New customers placed their first ever order in the period; returning customers had ordered before it. Guests are counted by their customer record.',
				'all_time covers every customer with at least one counted order. repeat_rate_pct is the share who ordered two or more times.',
				'Counts and averages only. Askshelf never returns customer names or contact details.',
			),
		);

		$compare = (string) ( $args['compare'] ?? 'none' );
		if ( 'none' !== $compare ) {
			$previous_period      = $period->comparison( $compare );
			$previous             = self::period_stats( $previous_period );
			$result['comparison'] = array(
				'period'    => $previous_period->to_array(),
				'customers' => $previous,
				'change'    => SalesSummary::change(
					array_intersect_key( $current, array_flip( array( 'customers', 'new_customers', 'returning_customers', 'new_customer_sales', 'returning_customer_sales' ) ) ),
					$previous
				),
			);
			$result['rows_read'] += $previous['orders'];
		}

		$result['chart'] = array(
			'type'   => 'column',
			'title'  => __( 'New and returning customers', 'askshelf' ),
			'format' => 'number',
			'points' => array(
				array(
					'label' => __( 'New', 'askshelf' ),
					'value' => $current['new_customers'],
				),
				array(
					'label' => __( 'Returning', 'askshelf' ),
					'value' => $current['returning_customers'],
				),
			),
		);
		return $result;
	}

	/**
	 * Customers who ordered in a period, split by whether it was their first order.
	 *
	 * @param Period $period Period.
	 * @return array{customers: int, new_customers: int, returning_customers: int, new_customer_sales: float, returning_customer_sales: float, returning_share_pct: float|null, orders: int}
	 */
	public static function period_stats( Period $period ): array {
		global $wpdb;
		$stats         = Orders::stats_table();
		$column        = Analytics::date_column();
		$excluded      = Orders::excluded_csv();
		[ $from, $to ] = Orders::bounds( $period );

		// Each customer's first counted order, then their orders in the period. The alias must not be
		// "returning_customer": wc_order_stats has a column of that name, and GROUP BY would use it.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only report query on WooCommerce's Analytics table.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT (f.first_order < %s) AS ordered_before,
					COUNT(DISTINCT s.customer_id) AS customers,
					COUNT(*) AS orders,
					SUM(s.net_total) AS net_sales
				FROM %i s
				INNER JOIN (
					SELECT customer_id, MIN(%i) AS first_order
					FROM %i
					WHERE parent_id = 0 AND customer_id > 0 AND %i IS NOT NULL AND FIND_IN_SET(status, %s) = 0
					GROUP BY customer_id
				) f ON f.customer_id = s.customer_id
				WHERE s.parent_id = 0 AND s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0
				GROUP BY ordered_before',
				$from,
				$stats,
				$column,
				$stats,
				$column,
				$excluded,
				$column,
				$from,
				$column,
				$to,
				$excluded
			),
			ARRAY_A
		);
		// phpcs:enable

		$out = array(
			'customers'                => 0,
			'new_customers'            => 0,
			'returning_customers'      => 0,
			'new_customer_sales'       => 0.0,
			'returning_customer_sales' => 0.0,
			'returning_share_pct'      => null,
			'orders'                   => 0,
		);
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$key                             = (int) $row['ordered_before'] ? 'returning' : 'new';
			$out[ $key . '_customers' ]      = (int) $row['customers'];
			$out[ $key . '_customer_sales' ] = Analytics::money( $row['net_sales'] );
			$out['orders']                  += (int) $row['orders'];
		}
		$out['customers']           = $out['new_customers'] + $out['returning_customers'];
		$out['returning_share_pct'] = $out['customers'] > 0 ? round( $out['returning_customers'] / $out['customers'] * 100, 1 ) : null;
		return $out;
	}

	/**
	 * Repeat purchase rate and lifetime averages, from Analytics › Customers.
	 *
	 * @return array{customers: int, repeat_customers: int, repeat_rate_pct: float|null, avg_orders_per_customer: float, avg_lifetime_spend: float, avg_order_value: float}
	 */
	public static function all_time(): array {
		// orders_count_min is exclusive: 0 means at least one order, 1 means two or more.
		$all    = self::customer_stats( array( 'orders_count_min' => 0 ) );
		$repeat = self::customer_stats( array( 'orders_count_min' => 1 ) );
		return array(
			'customers'               => (int) ( $all['customers_count'] ?? 0 ),
			'repeat_customers'        => (int) ( $repeat['customers_count'] ?? 0 ),
			'repeat_rate_pct'         => ( (int) ( $all['customers_count'] ?? 0 ) ) > 0 ? round( (int) ( $repeat['customers_count'] ?? 0 ) / (int) $all['customers_count'] * 100, 1 ) : null,
			'avg_orders_per_customer' => round( (float) ( $all['avg_orders_count'] ?? 0 ), 2 ),
			'avg_lifetime_spend'      => Analytics::money( $all['avg_total_spend'] ?? 0 ),
			'avg_order_value'         => Analytics::money( $all['avg_avg_order_value'] ?? 0 ),
		);
	}

	/**
	 * Customer totals for a filter.
	 *
	 * @param array<string, mixed> $filter Analytics customer filters.
	 * @return array<string, mixed>
	 */
	private static function customer_stats( array $filter ): array {
		$response = Analytics::get( '/reports/customers/stats', $filter );
		return is_array( $response['data'] ) && is_array( $response['data']['totals'] ?? null ) ? $response['data']['totals'] : array();
	}
}