<?php
namespace Strikewise\History;

use Strikewise\Calculator\Interval;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- the plugin's own table has no WordPress API; reads go through ReferenceService's meta cache.

/**
 * Database access for the price history table.
 * One row per price change; valid_to = NULL marks the price that applies now.
 * All datetimes are stored in UTC ('Y-m-d H:i:s').
 */
final class Repository {

	const META_T0 = '_strikewise_t0';

	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'strikewise_history';
	}

	/**
	 * @return array<string,mixed>|null The row currently valid for this product and currency.
	 */
	public function get_open( int $product_id, string $currency ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE product_id = %d AND currency = %s AND valid_to IS NULL ORDER BY valid_from DESC, id DESC LIMIT 1',
				self::table(),
				$product_id,
				$currency
			),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Closes the open row and inserts the new price, both at $valid_from.
	 */
	public function insert_change( Change $change ): void {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET valid_to = %s WHERE product_id = %d AND currency = %s AND valid_to IS NULL',
				self::table(),
				$change->valid_from,
				$change->product_id,
				$change->currency
			)
		);

		$wpdb->insert(
			self::table(),
			array(
				'product_id'    => $change->product_id,
				'parent_id'     => $change->parent_id,
				'currency'      => $change->currency,
				'price'         => $change->price,
				'regular_price' => $change->regular_price,
				'is_reduced'    => $change->reduced ? 1 : 0,
				'valid_from'    => $change->valid_from,
				'valid_to'      => null,
				'source'        => $change->source,
				'user_id'       => $change->user_id,
			),
			// wpdb writes NULL for null values regardless of the format.
			array( '%d', '%d', '%s', '%f', '%f', '%d', '%s', '%s', '%s', '%d' )
		);
	}

	/**
	 * Writes a closed or open interval as is, e.g. for imported history.
	 */
	public function insert_interval( Change $change, ?string $valid_to ): void {
		global $wpdb;

		$wpdb->insert(
			self::table(),
			array(
				'product_id'    => $change->product_id,
				'parent_id'     => $change->parent_id,
				'currency'      => $change->currency,
				'price'         => $change->price,
				'regular_price' => $change->regular_price,
				'is_reduced'    => $change->reduced ? 1 : 0,
				'valid_from'    => $change->valid_from,
				'valid_to'      => $valid_to,
				'source'        => $change->source,
				'user_id'       => $change->user_id,
			),
			array( '%d', '%d', '%s', '%f', '%f', '%d', '%s', '%s', '%s', '%d' )
		);
	}

	/**
	 * Start of the oldest recorded price, or null if nothing is recorded.
	 */
	public function earliest_start( int $product_id, string $currency ): ?int {
		global $wpdb;

		$value = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT MIN(valid_from) FROM %i WHERE product_id = %d AND currency = %s',
				self::table(),
				$product_id,
				$currency
			)
		);

		return null === $value ? null : self::to_timestamp( $value );
	}

	public function has_source( int $product_id, string $source ): bool {
		global $wpdb;

		return (bool) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT 1 FROM %i WHERE product_id = %d AND source = %s LIMIT 1',
				self::table(),
				$product_id,
				$source
			)
		);
	}

	/**
	 * @return Interval[]
	 */
	public function get_intervals( int $product_id, string $currency ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT price, is_reduced, valid_from, valid_to FROM %i WHERE product_id = %d AND currency = %s ORDER BY valid_from ASC, id ASC',
				self::table(),
				$product_id,
				$currency
			),
			ARRAY_A
		);

		$intervals = array();
		foreach ( (array) $rows as $row ) {
			$intervals[] = new Interval(
				self::to_timestamp( $row['valid_from'] ),
				null === $row['valid_to'] ? null : self::to_timestamp( $row['valid_to'] ),
				(float) $row['price'],
				(bool) $row['is_reduced']
			);
		}

		return $intervals;
	}

	/**
	 * Latest rows for a product and its variations, newest first (admin view).
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_recent_for_product( int $product_id, int $limit = 25 ): array {
		global $wpdb;

		return (array) $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE product_id = %d OR parent_id = %d ORDER BY valid_from DESC, id DESC LIMIT %d',
				self::table(),
				$product_id,
				$product_id,
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Raw rows of one product and currency, oldest first.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_rows( int $product_id, string $currency ): array {
		global $wpdb;

		return (array) $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE product_id = %d AND currency = %s ORDER BY valid_from ASC, id ASC',
				self::table(),
				$product_id,
				$currency
			),
			ARRAY_A
		);
	}

	/**
	 * Complete history of a product and its variations, oldest first (export).
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_all_for_product( int $product_id ): array {
		global $wpdb;

		return (array) $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE product_id = %d OR parent_id = %d ORDER BY product_id ASC, valid_from ASC, id ASC',
				self::table(),
				$product_id,
				$product_id
			),
			ARRAY_A
		);
	}

	/**
	 * Deletes closed rows older than the retention period.
	 * Rows a running reduction still needs (its 30-day window) are kept.
	 *
	 * @return int Number of deleted rows.
	 */
	public function cleanup( int $retention_days, int $window_days ): int {
		global $wpdb;

		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $retention_days * DAY_IN_SECONDS );

		$deleted = $wpdb->query(
			$wpdb->prepare(
				"DELETE h FROM %i h
				LEFT JOIN %i m ON m.post_id = h.product_id AND m.meta_key = %s
				WHERE h.valid_to IS NOT NULL
				AND h.valid_to < %s
				AND ( m.meta_value IS NULL OR m.meta_value = '' OR h.valid_to < DATE_SUB( m.meta_value, INTERVAL %d DAY ) )",
				self::table(),
				$wpdb->postmeta,
				self::META_T0,
				$cutoff,
				$window_days
			)
		);

		return (int) $deleted;
	}

	public static function to_timestamp( string $utc_datetime ): int {
		return ( new \DateTimeImmutable( $utc_datetime, new \DateTimeZone( 'UTC' ) ) )->getTimestamp();
	}

	public static function to_datetime( int $timestamp ): string {
		return gmdate( 'Y-m-d H:i:s', $timestamp );
	}
}