<?php
/**
 * Data access for cached forecasts.
 *
 * @package ProfitEngine
 */

declare( strict_types=1 );

namespace ProfitEngine\Forecasting;

use wpdb;

defined( 'ABSPATH' ) || exit;

/**
 * Repository for wp_pe_forecasts (one row per purchasable unit).
 */
class ForecastRepository {

	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_forecasts';
	}

	/**
	 * Insert or update a forecast keyed on product_id.
	 *
	 * @param int                 $product_id Purchasable-unit ID.
	 * @param array<string,mixed> $data       Column data.
	 */
	public function upsert( int $product_id, array $data ): void {
		$data['product_id']  = $product_id;
		$data['computed_at'] = gmdate( 'Y-m-d H:i:s' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$exists = (int) $this->db->get_var(
			$this->db->prepare( "SELECT id FROM {$this->table} WHERE product_id = %d", $product_id )
		);

		if ( $exists > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->db->update( $this->table, $data, array( 'product_id' => $product_id ) );
			return;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->insert( $this->table, $data );
	}

	/**
	 * Get one forecast.
	 *
	 * @param int $product_id Purchasable-unit ID.
	 * @return array<string,mixed>|null
	 */
	public function get( int $product_id ): ?array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$row = $this->db->get_row(
			$this->db->prepare( "SELECT * FROM {$this->table} WHERE product_id = %d", $product_id ),
			ARRAY_A
		);
		return $row ?: null;
	}

	/**
	 * Query forecasts by severity.
	 *
	 * @param string[] $severities Severities to include (empty = all).
	 * @return array<int,array<string,mixed>>
	 */
	public function by_severity( array $severities ): array {
		if ( empty( $severities ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$rows = $this->db->get_results( "SELECT * FROM {$this->table} ORDER BY days_remaining ASC", ARRAY_A );
			return $rows ?: array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $severities ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT * FROM {$this->table} WHERE severity IN ({$placeholders}) ORDER BY days_remaining ASC",
				$severities
			),
			ARRAY_A
		);
		return $rows ?: array();
	}

	/**
	 * Count forecasts grouped by severity.
	 *
	 * @return array<string,int>
	 */
	public function severity_counts(): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $this->db->get_results( "SELECT severity, COUNT(*) AS c FROM {$this->table} GROUP BY severity", ARRAY_A );
		$out  = array();
		foreach ( $rows ?: array() as $row ) {
			$out[ (string) $row['severity'] ] = (int) $row['c'];
		}
		return $out;
	}

	/**
	 * Total number of forecast rows (i.e. products with a computed forecast).
	 */
	public function count_all(): int {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return (int) $this->db->get_var( "SELECT COUNT(*) FROM {$this->table}" );
	}

	/**
	 * Number of forecasts that have measurable sales velocity (> 0/day).
	 *
	 * Reorder suggestions need a non-zero velocity, so this distinguishes
	 * "no forecast yet" from "forecasts exist but there's no sales history".
	 */
	public function count_with_velocity(): int {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return (int) $this->db->get_var( "SELECT COUNT(*) FROM {$this->table} WHERE avg_daily_sales > 0" );
	}

	/**
	 * Remove a forecast (e.g. when a product is disabled/deleted).
	 *
	 * @param int $product_id Purchasable-unit ID.
	 */
	public function delete( int $product_id ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->delete( $this->table, array( 'product_id' => $product_id ) );
	}
}