<?php
/**
 * Store report.
 *
 * @package StockAlibi
 */

declare( strict_types=1 );

namespace StockAlibi\Query;

use StockAlibi\Database\Schema;
use StockAlibi\Recorder\Fields;

defined( 'ABSPATH' ) || exit;

/**
 * Aggregates of the change log for a period: by source, by day, by product.
 *
 * Stock changes are `stock_quantity` events; price changes are events of the
 * price fields (regular, sale, sale dates, active price). Other stock fields
 * (status, manage stock, backorders) count in the total only.
 */
final class Report {

	/**
	 * Most products listed as "most changed".
	 */
	public const TOP_PRODUCTS = 10;

	/**
	 * The report for a period.
	 *
	 * @param string $from UTC start `Y-m-d H:i:s`.
	 * @param string $to   UTC end `Y-m-d H:i:s`.
	 * @return array{
	 *     from: string,
	 *     to: string,
	 *     totals: array{all: int, stock: int, price: int, products: int},
	 *     sources: array<int, array{source: string, all: int, stock: int, price: int, stock_share: float, all_share: float}>,
	 *     days: array<int, array{day: string, all: int, stock: int, price: int}>,
	 *     products: array<int, array{product_id: int, parent_id: int, sku: string, all: int, stock: int, price: int}>
	 * }
	 */
	public function build( string $from, string $to ): array {
		global $wpdb;

		$table  = Schema::events_table();
		$prices = array_merge( Fields::PRICES, array( Fields::PRICE ) );
		$in     = implode( ',', array_fill( 0, count( $prices ), '%s' ) );
		$sums   = $wpdb->prepare( "COUNT(*) AS all_changes, SUM( field = %s ) AS stock, SUM( field IN ({$in}) ) AS price", Fields::STOCK_QUANTITY, ...$prices ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders only (one per price field, values spread).

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin's own table; $sums is prepared above.
		$by_source = $wpdb->get_results( $wpdb->prepare( "SELECT source, {$sums} FROM %i WHERE created_at BETWEEN %s AND %s GROUP BY source", $table, $from, $to ), ARRAY_A );

		// Days in the site's time zone: group on the UTC time shifted by the current offset.
		$offset  = (int) round( (float) get_option( 'gmt_offset', 0 ) * HOUR_IN_SECONDS );
		$by_day  = $wpdb->get_results( $wpdb->prepare( "SELECT DATE( DATE_ADD( created_at, INTERVAL %d SECOND ) ) AS day, {$sums} FROM %i WHERE created_at BETWEEN %s AND %s GROUP BY day ORDER BY day", $offset, $table, $from, $to ), ARRAY_A );
		$by_item = $wpdb->get_results( $wpdb->prepare( "SELECT product_id, {$sums} FROM %i WHERE created_at BETWEEN %s AND %s GROUP BY product_id ORDER BY all_changes DESC, product_id LIMIT %d", $table, $from, $to, self::TOP_PRODUCTS ), ARRAY_A );
		$items   = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT( DISTINCT product_id ) FROM %i WHERE created_at BETWEEN %s AND %s', $table, $from, $to ) );
		// phpcs:enable

		$totals = array(
			'all'      => 0,
			'stock'    => 0,
			'price'    => 0,
			'products' => $items,
		);
		foreach ( $by_source ? $by_source : array() as $row ) {
			$totals['all']   += (int) $row['all_changes'];
			$totals['stock'] += (int) $row['stock'];
			$totals['price'] += (int) $row['price'];
		}

		$sources = array();
		foreach ( $by_source ? $by_source : array() as $row ) {
			$sources[] = array(
				'source'      => (string) $row['source'],
				'all'         => (int) $row['all_changes'],
				'stock'       => (int) $row['stock'],
				'price'       => (int) $row['price'],
				'stock_share' => $totals['stock'] ? round( 100 * (int) $row['stock'] / $totals['stock'], 1 ) : 0.0,
				'all_share'   => $totals['all'] ? round( 100 * (int) $row['all_changes'] / $totals['all'], 1 ) : 0.0,
			);
		}
		usort( $sources, static fn( array $a, array $b ): int => array( $b['stock'], $b['all'], $a['source'] ) <=> array( $a['stock'], $a['all'], $b['source'] ) );

		$days = array();
		foreach ( $by_day ? $by_day : array() as $row ) {
			$days[] = array(
				'day'   => (string) $row['day'],
				'all'   => (int) $row['all_changes'],
				'stock' => (int) $row['stock'],
				'price' => (int) $row['price'],
			);
		}

		// Parent and SKU of the top products: read separately, for ten products, so that the
		// aggregates above need only the index.
		$latest = array();
		if ( $by_item ) {
			$ids = array_map( static fn( array $row ): int => (int) $row['product_id'], $by_item );
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Plugin's own table; one %d per ID.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT product_id, MAX( parent_id ) AS parent_id, MAX( sku ) AS sku FROM %i WHERE product_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') AND created_at BETWEEN %s AND %s GROUP BY product_id',
					...array_merge( array( $table ), $ids, array( $from, $to ) )
				),
				ARRAY_A
			);
			// phpcs:enable
			foreach ( $rows as $row ) {
				$latest[ (int) $row['product_id'] ] = $row;
			}
		}

		$products = array();
		foreach ( $by_item ? $by_item : array() as $row ) {
			$id         = (int) $row['product_id'];
			$products[] = array(
				'product_id' => $id,
				'parent_id'  => (int) ( $latest[ $id ]['parent_id'] ?? 0 ),
				'sku'        => (string) ( $latest[ $id ]['sku'] ?? '' ),
				'all'        => (int) $row['all_changes'],
				'stock'      => (int) $row['stock'],
				'price'      => (int) $row['price'],
			);
		}

		return array(
			'from'     => $from,
			'to'       => $to,
			'totals'   => $totals,
			'sources'  => $sources,
			'days'     => $days,
			'products' => $products,
		);
	}
}