<?php
/**
 * Data access for stock movements (audit trail).
 *
 * @package ProfitEngine
 */

declare( strict_types=1 );

namespace ProfitEngine\Costing;

use ProfitEngine\Support\Money;
use wpdb;

defined( 'ABSPATH' ) || exit;

/**
 * Repository for wp_pe_stock_movements.
 *
 * Records every stock change PPE causes (PO receipts, adjustments, returns) so
 * there is an auditable history independent of WooCommerce's own stock number.
 */
class StockMovementRepository {

	private wpdb $db;

	private string $table;

	/**
	 * Constructor.
	 *
	 * @param wpdb $db WordPress database.
	 */
	public function __construct( wpdb $db ) {
		$this->db    = $db;
		$this->table = $db->prefix . 'profit_engine_stock_movements';
	}

	/**
	 * Record a movement.
	 *
	 * @param array{product_id:int,type:string,qty:float,unit_cost?:float,reference_type?:string,reference_id?:int,note?:string} $data Movement.
	 */
	public function record( array $data ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->insert(
			$this->table,
			array(
				'product_id'     => (int) $data['product_id'],
				'type'           => sanitize_key( (string) $data['type'] ),
				'qty'            => Money::store( $data['qty'] ),
				'unit_cost'      => Money::store( $data['unit_cost'] ?? 0 ),
				'reference_type' => sanitize_key( (string) ( $data['reference_type'] ?? '' ) ),
				'reference_id'   => (int) ( $data['reference_id'] ?? 0 ),
				'note'           => sanitize_text_field( (string) ( $data['note'] ?? '' ) ),
				'created_at'     => gmdate( 'Y-m-d H:i:s' ),
			)
		);
	}

	/**
	 * Movements for a given reference (e.g. a purchase order).
	 *
	 * @param string $type Reference type.
	 * @param int    $id   Reference ID.
	 * @return array<int,array<string,mixed>>
	 */
	/**
	 * Most recent movements for a set of products (newest first).
	 *
	 * @param int[] $product_ids Product/variation IDs.
	 * @param int   $limit       Max rows.
	 * @return array<int,array<string,mixed>>
	 */
	public function recent_for_products( array $product_ids, int $limit = 10 ): array {
		$product_ids = array_values( array_filter( array_map( 'intval', $product_ids ) ) );
		if ( ! $product_ids ) {
			return array();
		}
		$placeholders = implode( ', ', array_fill( 0, count( $product_ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $this->db->get_results(
			$this->db->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- placeholders built above.
				"SELECT * FROM {$this->table} WHERE product_id IN ({$placeholders}) ORDER BY created_at DESC, id DESC LIMIT %d",
				array_merge( $product_ids, array( $limit ) )
			),
			ARRAY_A
		);
		return $rows ?: array();
	}

	public function for_reference( string $type, int $id ): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT * FROM {$this->table} WHERE reference_type = %s AND reference_id = %d ORDER BY created_at ASC",
				sanitize_key( $type ),
				$id
			),
			ARRAY_A
		);
		return $rows ?: array();
	}
}