<?php
/**
 * Reads the change log.
 *
 * @package StockAlibi
 */

declare( strict_types=1 );

namespace StockAlibi\Query;

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

defined( 'ABSPATH' ) || exit;

/**
 * Filtered, paged reads of the events table, for the screens and the export.
 *
 * Filters (all optional, unknown keys ignored):
 *
 * - `product`: product or variation ID. A variable product includes its
 *   variations.
 * - `fields`: field names (see Fields).
 * - `sources`: source keys, including names set by `stock_alibi_resolve_source`.
 * - `from`, `to`: UTC `Y-m-d H:i:s`, inclusive.
 * - `user`: user ID.
 * - `order`: order ID; matches every order source (sale, cancellation,
 *   refund, line edit) of that order.
 * - `request`: request ID.
 * - `search`: SKU or source reference, partial match.
 * - `ids`: event IDs (`events` in a query string, comma-separated).
 */
final class EventQuery {

	/**
	 * Sources whose `source_ref` is an order ID.
	 */
	public const ORDER_SOURCES = array(
		Context::SOURCE_ORDER,
		Context::SOURCE_ORDER_RESTORE,
		Context::SOURCE_REFUND_RESTOCK,
		Context::SOURCE_ORDER_EDIT,
	);

	/**
	 * Columns read for an event.
	 */
	private const COLUMNS = 'id, product_id, parent_id, sku, field, old_value, new_value, delta, source, source_ref, user_id, request_id, context, created_at';

	/**
	 * Most events returned by one call.
	 */
	public const MAX_LIMIT = 5000;

	/**
	 * Cleans raw filters (for example from a screen's query string) into the
	 * filter array this class understands.
	 *
	 * Dates in `from_date` / `to_date` are local site dates (`Y-m-d`) and are
	 * turned into a UTC range covering the whole days.
	 *
	 * @param array<string, mixed> $raw Raw input.
	 * @return array<string, mixed>
	 */
	public static function filters_from_request( array $raw ): array {
		$filters = array();

		if ( ! empty( $raw['product'] ) && is_numeric( $raw['product'] ) ) {
			$filters['product'] = absint( $raw['product'] );
		}

		foreach ( array(
			'field'  => 'fields',
			'source' => 'sources',
		) as $key => $target ) {
			if ( empty( $raw[ $key ] ) ) {
				continue;
			}
			$values = array_filter( array_map( 'sanitize_key', array_map( 'strval', (array) $raw[ $key ] ) ) );
			if ( 'field' === $key && in_array( 'stock', $values, true ) ) {
				$values = array_merge( array_diff( $values, array( 'stock' ) ), Fields::STOCK );
			}
			if ( 'field' === $key && in_array( 'prices', $values, true ) ) {
				$values = array_merge( array_diff( $values, array( 'prices' ) ), Fields::PRICES, array( Fields::PRICE ) );
			}
			if ( $values ) {
				$filters[ $target ] = array_values( array_unique( $values ) );
			}
		}

		$from = self::local_date( $raw['from_date'] ?? '' );
		if ( null !== $from ) {
			$filters['from'] = get_gmt_from_date( $from . ' 00:00:00' );
		}
		$to = self::local_date( $raw['to_date'] ?? '' );
		if ( null !== $to ) {
			$filters['to'] = get_gmt_from_date( $to . ' 23:59:59' );
		}

		foreach ( array( 'user', 'order' ) as $key ) {
			if ( ! empty( $raw[ $key ] ) && is_numeric( $raw[ $key ] ) ) {
				$filters[ $key ] = absint( $raw[ $key ] );
			}
		}

		if ( ! empty( $raw['request'] ) && is_string( $raw['request'] ) && preg_match( '/^[0-9a-f-]{36}$/', $raw['request'] ) ) {
			$filters['request'] = $raw['request'];
		}

		if ( ! empty( $raw['events'] ) && is_string( $raw['events'] ) ) {
			$ids = array_values( array_unique( array_filter( array_map( 'absint', explode( ',', $raw['events'] ) ) ) ) );
			if ( $ids ) {
				$filters['ids'] = array_slice( $ids, 0, 200 );
			}
		}

		if ( isset( $raw['s'] ) && is_string( $raw['s'] ) ) {
			$search = trim( sanitize_text_field( $raw['s'] ) );
			if ( '' !== $search ) {
				$filters['search'] = $search;
			}
		}

		return $filters;
	}

	/**
	 * Events matching the filters, newest first.
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @param int                  $limit   Most events returned.
	 * @param int                  $offset  Events skipped.
	 * @param string               $order   DESC (newest first) or ASC.
	 * @return array<int, array<string, mixed>> Rows, `context` decoded.
	 */
	public function find( array $filters, int $limit = 50, int $offset = 0, string $order = 'DESC' ): array {
		global $wpdb;

		list( $where, $values ) = $this->where( $filters );
		$order                  = 'ASC' === $order ? 'ASC' : 'DESC';
		$limit                  = max( 1, min( self::MAX_LIMIT, $limit ) );
		// Keyset paging (`after_id`) needs ID order: `created_at` of an `external` event is when it was first seen, before it was inserted.
		$order_by = isset( $filters['after_id'] ) ? "id {$order}" : "created_at {$order}, id {$order}";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin's own table; columns are a constant, the WHERE clause is built from fixed strings with placeholders (values spread), the order is built from constants.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT ' . self::COLUMNS . " FROM %i WHERE {$where} ORDER BY {$order_by} LIMIT %d OFFSET %d", Schema::events_table(), ...array_merge( $values, array( $limit, max( 0, $offset ) ) ) ), ARRAY_A );

		return array_map( array( self::class, 'decode' ), $rows ? $rows : array() );
	}

	/**
	 * Number of events matching the filters.
	 *
	 * @param array<string, mixed> $filters Filters.
	 */
	public function count( array $filters ): int {
		global $wpdb;

		list( $where, $values ) = $this->where( $filters );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin's own table; the WHERE clause is built from fixed strings with placeholders (values spread).
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", Schema::events_table(), ...$values ) );
	}

	/**
	 * Walks every matching event in batches, in ID order, without loading
	 * them all at once (for the CSV export).
	 *
	 * @param array<string, mixed> $filters      Filters.
	 * @param callable             $callback     Called with each row.
	 * @param int                  $batch        Rows per query.
	 * @param callable|null        $before_batch Called with the rows of each batch first (e.g. to prime caches).
	 * @return int Rows visited.
	 */
	public function each( array $filters, callable $callback, int $batch = 1000, ?callable $before_batch = null ): int {
		$visited = 0;
		$after   = 0;

		do {
			$rows = $this->find( array_merge( $filters, array( 'after_id' => $after ) ), $batch, 0, 'ASC' );
			$read = count( $rows );
			if ( $rows && $before_batch ) {
				$before_batch( $rows );
			}
			foreach ( $rows as $row ) {
				$callback( $row );
				$after = max( $after, (int) $row['id'] );
				++$visited;
			}
		} while ( $read === $batch );

		return $visited;
	}

	/**
	 * Distinct sources in the log, for the filter menu (includes names set by
	 * ERP bridges through the filter).
	 *
	 * @return string[]
	 */
	public function sources(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin's own table, uses the source_time index.
		$sources = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT source FROM %i ORDER BY source', Schema::events_table() ) );

		return array_map( 'strval', $sources ? $sources : array() );
	}

	/**
	 * The latest event of each field of a product, newest first.
	 *
	 * @param int $product_id Product or variation ID (exact match).
	 * @return array<string, array<string, mixed>> Keyed by field.
	 */
	public function latest_by_field( int $product_id ): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Plugin's own table; columns are a constant.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT ' . self::COLUMNS . ' FROM %i e WHERE product_id = %d AND id = ( SELECT MAX(id) FROM %i WHERE product_id = e.product_id AND field = e.field ) ORDER BY created_at DESC, id DESC', Schema::events_table(), $product_id, Schema::events_table() ), ARRAY_A );

		$latest = array();
		foreach ( $rows ? $rows : array() as $row ) {
			$latest[ (string) $row['field'] ] = self::decode( $row );
		}

		return $latest;
	}

	/**
	 * The stock event of a product just before another event, if any.
	 *
	 * @param array<string, mixed> $event Event.
	 * @return array<string, mixed>|null
	 */
	public function previous_stock_event( array $event ): ?array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Plugin's own table, uses the product_time index; columns are a constant.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT ' . self::COLUMNS . ' FROM %i WHERE product_id = %d AND field = %s AND ( created_at < %s OR ( created_at = %s AND id < %d ) ) ORDER BY created_at DESC, id DESC LIMIT 1', Schema::events_table(), (int) $event['product_id'], Fields::STOCK_QUANTITY, $event['created_at'], $event['created_at'], (int) $event['id'] ), ARRAY_A );

		return $row ? self::decode( $row ) : null;
	}

	/**
	 * Decodes a row: `context` as an array, IDs as integers, `delta` without
	 * trailing zeros.
	 *
	 * @param array<string, mixed> $row Raw row.
	 * @return array<string, mixed>
	 */
	public static function decode( array $row ): array {
		$context        = isset( $row['context'] ) && null !== $row['context'] ? json_decode( (string) $row['context'], true ) : array();
		$row['context'] = is_array( $context ) ? $context : array();

		foreach ( array( 'id', 'product_id', 'parent_id' ) as $key ) {
			if ( isset( $row[ $key ] ) ) {
				$row[ $key ] = (int) $row[ $key ];
			}
		}
		if ( isset( $row['delta'] ) ) {
			// DECIMAL(20,4) comes back as "-2.0000".
			$row['delta'] = Fields::number( $row['delta'] );
		}
		if ( array_key_exists( 'user_id', $row ) ) {
			$row['user_id'] = null === $row['user_id'] ? null : (int) $row['user_id'];
		}

		return $row;
	}

	/**
	 * WHERE clause and values for the filters.
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @return array{0: string, 1: array<int, mixed>}
	 */
	private function where( array $filters ): array {
		global $wpdb;

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

		if ( ! empty( $filters['product'] ) ) {
			$where[]  = '( product_id = %d OR parent_id = %d )';
			$values[] = (int) $filters['product'];
			$values[] = (int) $filters['product'];
		}

		foreach ( array(
			'fields'  => 'field',
			'sources' => 'source',
		) as $key => $column ) {
			if ( ! empty( $filters[ $key ] ) ) {
				$list    = array_values( array_map( 'strval', (array) $filters[ $key ] ) );
				$where[] = "{$column} IN (" . self::placeholders( $list, '%s' ) . ')';
				$values  = array_merge( $values, $list );
			}
		}

		if ( ! empty( $filters['from'] ) ) {
			$where[]  = 'created_at >= %s';
			$values[] = (string) $filters['from'];
		}
		if ( ! empty( $filters['to'] ) ) {
			$where[]  = 'created_at <= %s';
			$values[] = (string) $filters['to'];
		}

		if ( ! empty( $filters['user'] ) ) {
			$where[]  = 'user_id = %d';
			$values[] = (int) $filters['user'];
		}

		if ( ! empty( $filters['order'] ) ) {
			$where[]  = 'source_ref = %s AND source IN (' . self::placeholders( self::ORDER_SOURCES, '%s' ) . ')';
			$values[] = (string) (int) $filters['order'];
			$values   = array_merge( $values, self::ORDER_SOURCES );
		}

		if ( ! empty( $filters['request'] ) ) {
			$where[]  = 'request_id = %s';
			$values[] = (string) $filters['request'];
		}

		if ( ! empty( $filters['search'] ) ) {
			// Prefix match: both columns are indexed, a "contains" search would read every row.
			$like     = $wpdb->esc_like( (string) $filters['search'] ) . '%';
			$where[]  = '( sku LIKE %s OR source_ref LIKE %s )';
			$values[] = $like;
			$values[] = $like;
		}

		if ( ! empty( $filters['ids'] ) ) {
			$ids     = array_values( array_map( 'intval', (array) $filters['ids'] ) );
			$where[] = 'id IN (' . self::placeholders( $ids, '%d' ) . ')';
			$values  = array_merge( $values, $ids );
		}

		if ( ! empty( $filters['after_id'] ) ) {
			$where[]  = 'id > %d';
			$values[] = (int) $filters['after_id'];
		}

		return array( implode( ' AND ', $where ), $values );
	}

	/**
	 * A valid local date `Y-m-d`, or NULL.
	 *
	 * @param mixed $value Input.
	 */
	private static function local_date( mixed $value ): ?string {
		if ( ! is_string( $value ) || ! preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $value, $m ) ) {
			return null;
		}

		return checkdate( (int) $m[2], (int) $m[3], (int) $m[1] ) ? $value : null;
	}

	/**
	 * Comma-separated placeholders for a list.
	 *
	 * @param array  $items       Items.
	 * @param string $placeholder Placeholder.
	 */
	private static function placeholders( array $items, string $placeholder ): string {
		return implode( ',', array_fill( 0, count( $items ), $placeholder ) );
	}
}