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

declare( strict_types = 1 );

namespace Askshelf\Tools;

use Askshelf\Data\Analytics;
use Askshelf\Data\Orders;
use Askshelf\Data\Period;
use Askshelf\Settings;
use Automattic\WooCommerce\Utilities\OrderUtil;

defined( 'ABSPATH' ) || exit;

/**
 * Orders and net sales split by category, country, weekday, hour or payment method:
 * the splits WooCommerce Analytics has no report for (category aside).
 */
final class SalesBreakdown implements Tool {

	private const GROUPS = array( 'category', 'country', 'weekday', 'hour', 'payment_method' );

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

	/** {@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(),
				'group_by'   => array(
					'type' => 'string',
					'enum' => self::GROUPS,
				),
				'compare'    => Schema::compare(),
			),
			array( 'start_date', 'end_date', 'group_by' )
		);
	}

	/**
	 * 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 );
		$group_by = (string) $args['group_by'];
		$current  = self::query( $period, $group_by );

		$result = array(
			'period'    => $period->to_array(),
			'group_by'  => $group_by,
			'groups'    => $current['groups'],
			'rows_read' => $current['rows'],
			'notes'     => array( self::note( $group_by ) ),
		);

		$compare = (string) ( $args['compare'] ?? 'none' );
		if ( 'none' !== $compare ) {
			$previous_period = $period->comparison( $compare );
			$previous        = self::query( $previous_period, $group_by );
			$by_group        = array_column( $previous['groups'], null, 'group' );
			foreach ( $result['groups'] as &$group ) {
				$before              = $by_group[ $group['group'] ] ?? array(
					'orders'    => 0,
					'net_sales' => 0.0,
				);
				$group['previous']   = array(
					'orders'    => $before['orders'],
					'net_sales' => $before['net_sales'],
				);
				$group['change_pct'] = SalesSummary::change(
					array(
						'orders'    => $group['orders'],
						'net_sales' => $group['net_sales'],
					),
					$group['previous']
				);
			}
			unset( $group );
			$result['comparison_period'] = $previous_period->to_array();
			$result['rows_read']        += $previous['rows'];
		}//end if

		$time            = in_array( $group_by, array( 'weekday', 'hour' ), true );
		$result['chart'] = array(
			'type'   => $time ? 'column' : 'bar',
			'title'  => self::title( $group_by ),
			'format' => 'money',
			'points' => array_map(
				static fn( array $g ): array => array(
					'label' => $g['group'],
					'value' => $g['net_sales'],
				),
				array_slice( $result['groups'], 0, $time ? 24 : 15 )
			),
		);
		return $result;
	}

	/**
	 * Groups for one period.
	 *
	 * @param Period $period   Period.
	 * @param string $group_by Grouping.
	 * @return array{groups: list<array{group: string, orders: int, net_sales: float, items_sold?: int}>, rows: int}
	 */
	public static function query( Period $period, string $group_by ): array {
		if ( 'category' === $group_by ) {
			return self::categories( $period );
		}
		$rows = self::sql( $period, $group_by );

		$groups = array();
		$orders = 0;
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			$groups[ (string) $row['grp'] ] = array(
				'group'     => (string) $row['grp'],
				'orders'    => (int) $row['orders'],
				'net_sales' => Analytics::money( $row['net_sales'] ),
			);
			$orders                        += (int) $row['orders'];
		}

		if ( 'weekday' === $group_by ) {
			$groups = self::label_weekdays( $groups );
		} elseif ( 'hour' === $group_by ) {
			$groups = self::label_hours( $groups );
		} else {
			if ( 'country' === $group_by ) {
				$groups = self::label_countries( $groups );
			}
			$groups = array_values( $groups );
			usort( $groups, static fn( array $a, array $b ): int => $b['net_sales'] <=> $a['net_sales'] );
		}

		return array(
			'groups' => $groups,
			'rows'   => $orders,
		);
	}

	/**
	 * Direct query for country, weekday, hour and payment method.
	 *
	 * @param Period $period   Period.
	 * @param string $group_by Grouping.
	 * @return array<int, mixed>
	 */
	private static function sql( Period $period, string $group_by ): array {
		global $wpdb;
		$stats         = Orders::stats_table();
		$column        = Analytics::date_column();
		[ $from, $to ] = Orders::bounds( $period );
		$excluded      = Orders::excluded_csv();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only report queries on WooCommerce's Analytics tables.
		switch ( $group_by ) {
			case 'weekday':
			case 'hour':
				// When orders were placed: orders only, before refunds, so a late refund
				// doesn't move sales to another hour.
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT IF(%s = 'hour', HOUR(s.%i), DAYOFWEEK(s.%i)) AS grp, COUNT(*) AS orders, SUM(s.net_total) AS net_sales
						FROM %i s
						WHERE s.parent_id = 0 AND s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0
						GROUP BY grp",
						$group_by,
						$column,
						$column,
						$stats,
						$column,
						$from,
						$column,
						$to,
						$excluded
					),
					ARRAY_A
				);
				break;

			case 'country':
				// Only the country is read from the customer lookup, never the rest of the address.
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT COALESCE(NULLIF(c.country, ''), '') AS grp,
							SUM(CASE WHEN s.parent_id = 0 THEN 1 ELSE 0 END) AS orders,
							SUM(s.net_total) AS net_sales
						FROM %i s
						LEFT JOIN %i c ON c.customer_id = s.customer_id
						WHERE s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0
						GROUP BY grp",
						$stats,
						$wpdb->prefix . 'wc_customer_lookup',
						$column,
						$from,
						$column,
						$to,
						$excluded
					),
					ARRAY_A
				);
				break;

			default:
				// Payment method title, from the order (HPOS column or posts meta).
				$hpos = class_exists( OrderUtil::class ) && OrderUtil::custom_orders_table_usage_is_enabled();
				$rows = $hpos
					? $wpdb->get_results(
						$wpdb->prepare(
							"SELECT COALESCE(NULLIF(o.payment_method_title, ''), NULLIF(o.payment_method, ''), '(none)') AS grp,
								SUM(CASE WHEN s.parent_id = 0 THEN 1 ELSE 0 END) AS orders,
								SUM(s.net_total) AS net_sales
							FROM %i s
							LEFT JOIN %i o ON o.id = IF(s.parent_id > 0, s.parent_id, s.order_id)
							WHERE s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0
							GROUP BY grp",
							$stats,
							$wpdb->prefix . 'wc_orders',
							$column,
							$from,
							$column,
							$to,
							$excluded
						),
						ARRAY_A
					)
					: $wpdb->get_results(
						$wpdb->prepare(
							"SELECT COALESCE(NULLIF(m.meta_value, ''), '(none)') AS grp,
								SUM(CASE WHEN s.parent_id = 0 THEN 1 ELSE 0 END) AS orders,
								SUM(s.net_total) AS net_sales
							FROM %i s
							LEFT JOIN %i m ON m.post_id = IF(s.parent_id > 0, s.parent_id, s.order_id) AND m.meta_key = %s
							WHERE s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0
							GROUP BY grp",
							$stats,
							$wpdb->postmeta,
							'_payment_method_title',
							$column,
							$from,
							$column,
							$to,
							$excluded
						),
						ARRAY_A
					);
		}//end switch
		// phpcs:enable

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Categories, from Analytics › Categories.
	 *
	 * @param Period $period Period.
	 * @return array{groups: list<array{group: string, orders: int, net_sales: float, items_sold: int}>, rows: int}
	 */
	private static function categories( Period $period ): array {
		$response = Analytics::get(
			'/reports/categories',
			array(
				'after'         => $period->after(),
				'before'        => $period->before(),
				'orderby'       => 'net_revenue',
				'order'         => 'desc',
				'per_page'      => 100,
				'extended_info' => true,
			)
		);
		$groups   = array();
		$rows     = 0;
		foreach ( is_array( $response['data'] ) ? $response['data'] : array() as $row ) {
			$name     = (string) ( $row['extended_info']['name'] ?? '' );
			$groups[] = array(
				'group'      => '' !== $name ? wp_strip_all_tags( html_entity_decode( $name, ENT_QUOTES, 'UTF-8' ) ) : '#' . (int) ( $row['category_id'] ?? 0 ),
				'orders'     => (int) ( $row['orders_count'] ?? 0 ),
				'net_sales'  => Analytics::money( $row['net_revenue'] ?? 0 ),
				'items_sold' => (int) ( $row['items_sold'] ?? 0 ),
			);
			$rows    += (int) ( $row['orders_count'] ?? 0 );
		}
		return array(
			'groups' => $groups,
			'rows'   => $rows,
		);
	}

	/**
	 * Every weekday in store week order, with names.
	 *
	 * @param array<string, array{group: string, orders: int, net_sales: float}> $groups Keyed by MySQL DAYOFWEEK (1 = Sunday).
	 * @return list<array{group: string, orders: int, net_sales: float}>
	 */
	private static function label_weekdays( array $groups ): array {
		global $wp_locale;
		$start = (int) get_option( 'start_of_week', 1 );
		$out   = array();
		for ( $i = 0; $i < 7; $i++ ) {
			$day   = ( $start + $i ) % 7;
			$row   = $groups[ (string) ( $day + 1 ) ] ?? array(
				'orders'    => 0,
				'net_sales' => 0.0,
			);
			$out[] = array(
				'group'     => $wp_locale ? (string) $wp_locale->get_weekday( $day ) : (string) $day,
				'orders'    => $row['orders'],
				'net_sales' => $row['net_sales'],
			);
		}
		return $out;
	}

	/**
	 * All 24 hours, as "09:00".
	 *
	 * @param array<string, array{group: string, orders: int, net_sales: float}> $groups Keyed by hour.
	 * @return list<array{group: string, orders: int, net_sales: float}>
	 */
	private static function label_hours( array $groups ): array {
		$out = array();
		for ( $h = 0; $h < 24; $h++ ) {
			$row   = $groups[ (string) $h ] ?? array(
				'orders'    => 0,
				'net_sales' => 0.0,
			);
			$out[] = array(
				'group'     => sprintf( '%02d:00', $h ),
				'orders'    => $row['orders'],
				'net_sales' => $row['net_sales'],
			);
		}
		return $out;
	}

	/**
	 * Country names instead of codes.
	 *
	 * @param array<string, array{group: string, orders: int, net_sales: float}> $groups Keyed by country code.
	 * @return array<string, array{group: string, orders: int, net_sales: float}>
	 */
	private static function label_countries( array $groups ): array {
		$names = function_exists( 'WC' ) && WC()->countries ? WC()->countries->get_countries() : array();
		foreach ( $groups as $code => &$group ) {
			$group['group'] = '' === $code ? '(unknown)' : html_entity_decode( (string) ( $names[ $code ] ?? $code ), ENT_QUOTES, 'UTF-8' );
		}
		unset( $group );
		return $groups;
	}

	/**
	 * Chart title.
	 *
	 * @param string $group_by Grouping.
	 */
	private static function title( string $group_by ): string {
		switch ( $group_by ) {
			case 'category':
				return __( 'Net sales by category', 'askshelf' );
			case 'country':
				return __( 'Net sales by country', 'askshelf' );
			case 'weekday':
				return __( 'Net sales by weekday', 'askshelf' );
			case 'hour':
				return __( 'Net sales by hour', 'askshelf' );
			default:
				return __( 'Net sales by payment method', 'askshelf' );
		}
	}

	/**
	 * What the numbers mean for a grouping.
	 *
	 * @param string $group_by Grouping.
	 */
	private static function note( string $group_by ): string {
		switch ( $group_by ) {
			case 'category':
				return 'From WooCommerce Analytics › Categories, which counts like Analytics › Products and can differ slightly from the Revenue totals. A product in two categories counts in both.';
			case 'country':
				return "Country is the customer's billing country. Net sales take refunds out.";
			case 'weekday':
			case 'hour':
				return "When orders were placed, in the store's time zone. Net sales here are before refunds.";
			default:
				return 'Payment method as named at checkout. Net sales take refunds out.';
		}
	}
}