<?php
/**
 * Data function get_dead_stock.
 *
 * @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 DateTimeImmutable;

defined( 'ABSPATH' ) || exit;

/**
 * Stock that isn't selling, and what all stock on hand is worth.
 *
 * Reads products and variations with managed stock above zero. Value at cost uses
 * WooCommerce's Cost of Goods Sold field where a cost is set; value at price uses
 * the current selling price.
 */
final class DeadStock implements Tool {

	/** Most stocked products and variations read. */
	private const MAX_ITEMS = 5000;

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

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

	/** {@inheritDoc} */
	public function schema(): array {
		return Schema::object(
			array(
				'days'  => array(
					'type'    => 'integer',
					'minimum' => 14,
					'maximum' => 365,
					'default' => 60,
				),
				'limit' => array(
					'type'    => 'integer',
					'minimum' => 1,
					'maximum' => 50,
					'default' => 10,
				),
			)
		);
	}

	/**
	 * Runs the data function.
	 *
	 * @param array<string, mixed> $args Validated arguments.
	 * @return array<string, mixed>
	 */
	public function run( array $args ): array {
		$days  = (int) ( $args['days'] ?? 60 );
		$limit = (int) ( $args['limit'] ?? 10 );

		$today  = new DateTimeImmutable( 'today', wp_timezone() );
		$period = Period::from_dates( $today->modify( '-' . ( $days - 1 ) . ' days' )->format( 'Y-m-d' ), $today->format( 'Y-m-d' ) );

		$items = self::stocked_items();
		$sold  = self::sold_ids( $period );
		$cogs  = Orders::cogs_enabled();

		$inventory = self::empty_totals();
		$dead      = array();
		$dead_tot  = self::empty_totals();
		foreach ( $items as $item ) {
			self::add( $inventory, $item, $cogs );
			$is_variation = $item['parent_id'] > 0;
			$did_sell     = $is_variation ? isset( $sold['variations'][ $item['id'] ] ) : isset( $sold['products'][ $item['id'] ] );
			// Products added during the period haven't had the chance to sell.
			$is_new = $item['created'] >= $period->start->format( 'Y-m-d H:i:s' );
			if ( ! $did_sell && ! $is_new ) {
				self::add( $dead_tot, $item, $cogs );
				$dead[] = $item;
			}
		}

		// Most money tied up first.
		usort(
			$dead,
			static fn( array $a, array $b ): int => self::value( $b, $cogs ) <=> self::value( $a, $cogs )
		);
		$dead_count = count( $dead );
		$dead       = array_slice( $dead, 0, $limit );
		$last_sold  = self::last_sold( array_column( $dead, 'id' ) );

		$rows = array();
		foreach ( $dead as $item ) {
			$product = wc_get_product( $item['id'] );
			$rows[]  = array(
				'product_id'     => $item['id'],
				'name'           => $product ? wp_strip_all_tags( $product->get_name() ) : '#' . $item['id'],
				'sku'            => $product ? (string) $product->get_sku() : '',
				'stock'          => $item['stock'],
				'value_at_cost'  => $cogs && null !== $item['cost'] ? Analytics::money( $item['stock'] * $item['cost'] ) : null,
				'value_at_price' => Analytics::money( $item['stock'] * $item['price'] ),
				'last_sold'      => $last_sold[ $item['id'] ] ?? null,
			);
		}

		return array(
			'days_without_sales' => $days,
			'since'              => $period->to_array()['start_date'],
			'cogs_enabled'       => $cogs,
			'inventory'          => self::round_totals( $inventory, $cogs ),
			'dead_stock'         => self::round_totals( $dead_tot, $cogs ),
			'dead_stock_items'   => $rows,
			'dead_stock_count'   => $dead_count,
			'capped'             => count( $items ) >= self::MAX_ITEMS,
			'rows_read'          => count( $items ),
			'notes'              => array(
				'Only products and variations with managed stock above zero are counted. Dead stock had no sales in the period.',
				$cogs
					? 'value_at_cost uses each product\'s cost from Cost of Goods Sold; items without a cost are left out of it and counted in items_without_cost.'
					: 'Cost of Goods Sold is off, so stock is valued at selling price only. Turn it on in WooCommerce › Settings › Advanced › Features to value stock at cost.',
			),
			'chart'              => array(
				'type'   => 'bar',
				'title'  => __( 'Stock not selling, by value', 'askshelf' ),
				'format' => 'money',
				'points' => array_map(
					static fn( array $r ): array => array(
						'label' => $r['name'],
						'value' => $r['value_at_cost'] ?? $r['value_at_price'],
					),
					$rows
				),
			),
		);
	}

	/**
	 * Products and variations with managed stock above zero.
	 *
	 * @return list<array{id: int, parent_id: int, stock: int, price: float, cost: float|null, created: string}>
	 */
	private static function stocked_items(): array {
		global $wpdb;
		// A variation without its own cost uses its parent's.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only query on WooCommerce's product lookup table.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.ID AS id, p.post_parent AS parent_id, p.post_date AS created, l.stock_quantity AS stock,
					COALESCE(NULLIF(pr.meta_value, ''), 0) AS price,
					COALESCE(NULLIF(c.meta_value, ''), NULLIF(pc.meta_value, '')) AS cost
				FROM %i l
				INNER JOIN %i p ON p.ID = l.product_id AND p.post_type IN ('product', 'product_variation') AND p.post_status IN ('publish', 'private')
				INNER JOIN %i ms ON ms.post_id = p.ID AND ms.meta_key = '_manage_stock' AND ms.meta_value = 'yes'
				LEFT JOIN %i pr ON pr.post_id = p.ID AND pr.meta_key = '_price'
				LEFT JOIN %i c ON c.post_id = p.ID AND c.meta_key = '_cogs_total_value'
				LEFT JOIN %i pc ON pc.post_id = p.post_parent AND p.post_parent > 0 AND pc.meta_key = '_cogs_total_value'
				WHERE l.stock_quantity > 0
				ORDER BY l.stock_quantity DESC
				LIMIT %d",
				$wpdb->prefix . 'wc_product_meta_lookup',
				$wpdb->posts,
				$wpdb->postmeta,
				$wpdb->postmeta,
				$wpdb->postmeta,
				$wpdb->postmeta,
				self::MAX_ITEMS
			),
			ARRAY_A
		);
		// phpcs:enable

		$items = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$cost    = null === $row['cost'] ? null : (float) $row['cost'];
			$items[] = array(
				'id'        => (int) $row['id'],
				'parent_id' => (int) $row['parent_id'],
				'stock'     => (int) $row['stock'],
				'price'     => (float) $row['price'],
				'cost'      => null !== $cost && $cost > 0 ? $cost : null,
				'created'   => (string) $row['created'],
			);
		}
		return $items;
	}

	/**
	 * Products and variations with at least one counted sale in a period.
	 *
	 * @param Period $period Period.
	 * @return array{products: array<int, true>, variations: array<int, true>}
	 */
	private static function sold_ids( Period $period ): array {
		global $wpdb;
		[ $from, $to ] = Orders::bounds( $period );
		$column        = Analytics::date_column();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only report query on WooCommerce's Analytics tables.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT DISTINCT l.product_id, l.variation_id
				FROM %i l
				INNER JOIN %i s ON s.order_id = l.order_id
				WHERE l.product_qty > 0 AND s.parent_id = 0 AND s.%i >= %s AND s.%i <= %s AND FIND_IN_SET(s.status, %s) = 0',
				$wpdb->prefix . 'wc_order_product_lookup',
				Orders::stats_table(),
				$column,
				$from,
				$column,
				$to,
				Orders::excluded_csv()
			),
			ARRAY_A
		);
		// phpcs:enable

		$out = array(
			'products'   => array(),
			'variations' => array(),
		);
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$out['products'][ (int) $row['product_id'] ] = true;
			if ( (int) $row['variation_id'] > 0 ) {
				$out['variations'][ (int) $row['variation_id'] ] = true;
			}
		}
		return $out;
	}

	/**
	 * Date each product or variation last sold, ever.
	 *
	 * @param int[] $ids Product or variation ids.
	 * @return array<int, string> YYYY-MM-DD by id.
	 */
	private static function last_sold( array $ids ): array {
		global $wpdb;
		if ( ! $ids ) {
			return array();
		}
		$csv = implode( ',', array_map( 'intval', $ids ) );
		// 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 IF(FIND_IN_SET(l.variation_id, %s) > 0, l.variation_id, l.product_id) AS id, MAX(l.date_created) AS last_sold
				FROM %i l
				WHERE l.product_qty > 0 AND (FIND_IN_SET(l.product_id, %s) > 0 OR FIND_IN_SET(l.variation_id, %s) > 0)
				GROUP BY id',
				$csv,
				$wpdb->prefix . 'wc_order_product_lookup',
				$csv,
				$csv
			),
			ARRAY_A
		);
		// phpcs:enable
		$out = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$out[ (int) $row['id'] ] = substr( (string) $row['last_sold'], 0, 10 );
		}
		return $out;
	}

	/**
	 * Zeroed totals.
	 *
	 * @return array{items: int, units: int, value_at_cost: float, value_at_price: float, items_without_cost: int}
	 */
	private static function empty_totals(): array {
		return array(
			'items'              => 0,
			'units'              => 0,
			'value_at_cost'      => 0.0,
			'value_at_price'     => 0.0,
			'items_without_cost' => 0,
		);
	}

	/**
	 * Adds an item to totals.
	 *
	 * @param array<string, int|float>                                                                    $totals Totals.
	 * @param array{id: int, parent_id: int, stock: int, price: float, cost: float|null, created: string} $item   Item.
	 * @param bool                                                                                        $cogs   Whether costs are on.
	 */
	private static function add( array &$totals, array $item, bool $cogs ): void {
		++$totals['items'];
		$totals['units']          += $item['stock'];
		$totals['value_at_price'] += $item['stock'] * $item['price'];
		if ( $cogs && null !== $item['cost'] ) {
			$totals['value_at_cost'] += $item['stock'] * $item['cost'];
		} else {
			++$totals['items_without_cost'];
		}
	}

	/**
	 * Money rounding, and no cost figures when costs are off.
	 *
	 * @param array<string, int|float> $totals Totals.
	 * @param bool                     $cogs   Whether costs are on.
	 * @return array<string, int|float|null>
	 */
	private static function round_totals( array $totals, bool $cogs ): array {
		$totals['value_at_price'] = Analytics::money( $totals['value_at_price'] );
		$totals['value_at_cost']  = $cogs ? Analytics::money( $totals['value_at_cost'] ) : null;
		if ( ! $cogs ) {
			unset( $totals['items_without_cost'] );
		}
		return $totals;
	}

	/**
	 * Value used for sorting: at cost where known, else at price.
	 *
	 * @param array{id: int, parent_id: int, stock: int, price: float, cost: float|null, created: string} $item Item.
	 * @param bool                                                                                        $cogs Whether costs are on.
	 */
	private static function value( array $item, bool $cogs ): float {
		return $item['stock'] * ( $cogs && null !== $item['cost'] ? $item['cost'] : $item['price'] );
	}
}