<?php

declare(strict_types=1);

if (! defined('ABSPATH')) {
	exit;
}

final class CRToday_Raw_Snapshot_Repository {
	/**
	 * Persist snapshot rates into a single rates table.
	 *
	 * @param array<string,mixed> $payload Raw decoded API payload.
	 */
	public static function save_remote_payload(
		string $source_id,
		string $deal,
		string $source_base,
		array $payload,
		int $fetch_ts,
		int $source_ts,
		int $interval_min
	): void {
		global $wpdb;

		$table = CRToday_Storage_Installer::rates_table();
		if (! self::table_exists($table)) {
			return;
		}

		$source = strtoupper(trim($source_id));
		$deal = strtoupper(trim($deal));
		$base = strtoupper(trim($source_base));
		if ($source === '' || $base === '' || $interval_min <= 0) {
			return;
		}

		$fetch_ts = max(1, $fetch_ts);
		$source_ts = max(1, $source_ts);
		$rate_day = gmdate('Y-m-d', $source_ts);

		$rates = $payload['rates'] ?? null;
		if (! is_array($rates) || $rates === []) {
			return;
		}

		foreach ($rates as $code => $entry) {
			$currency = strtoupper((string) $code);
			if ($currency === '' || $currency === $base) {
				continue;
			}

			$mid = null;
			$bid = null;
			$ask = null;

			if (is_array($entry)) {
				$mid = self::normalize_rate($entry['mid'] ?? null);
				$bid = self::normalize_rate($entry['bid'] ?? null);
				$ask = self::normalize_rate($entry['ask'] ?? null);
				if ($mid === null && $bid !== null && $ask !== null) {
					$mid = ($bid + $ask) / 2;
				}
			} elseif (is_numeric($entry)) {
				$mid = self::normalize_rate($entry);
			}

			if ($mid === null && $bid === null && $ask === null) {
				continue;
			}

			$snapshot_hash = hash('sha256', implode('|', [
				$source,
				$deal,
				$base,
				$currency,
				(string) $interval_min,
				$rate_day,
				$mid !== null ? (string) $mid : '-',
				$bid !== null ? (string) $bid : '-',
				$ask !== null ? (string) $ask : '-',
			]));

			$source_ts_sql = gmdate('Y-m-d H:i:s', $source_ts);
			$fetch_ts_sql = gmdate('Y-m-d H:i:s', $fetch_ts);
			$created_at_sql = $fetch_ts_sql;

			$bid_value = ($bid === null) ? '' : sprintf('%.10F', $bid);
			$ask_value = ($ask === null) ? '' : sprintf('%.10F', $ask);
			$mid_value = ($mid === null) ? '' : sprintf('%.10F', $mid);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deterministic insert on value identity; skip if hash already stored.
			$wpdb->query(
				$wpdb->prepare(
					"INSERT IGNORE INTO %i
					(source, deal, base, code, interval_min, rate_day_utc, source_ts_utc, fetch_ts_utc, bid, ask, mid, snapshot_hash, created_at_utc)
					VALUES (%s, %s, %s, %s, %d, %s, %s, %s, NULLIF(%s, ''), NULLIF(%s, ''), NULLIF(%s, ''), %s, %s)",
					$table,
					$source,
					$deal,
					$base,
					$currency,
					$interval_min,
					$rate_day,
					$source_ts_sql,
					$fetch_ts_sql,
					$bid_value,
					$ask_value,
					$mid_value,
					$snapshot_hash,
					$created_at_sql
				)
			);
		}

		self::apply_retention_policy($table, $source, $deal, $interval_min);
	}

	/**
	 * @return array{payload:array<string,mixed>,source_ts:int,fetch_ts:int}|null
	 */
	public static function get_latest_payload(string $source_id, string $deal, int $interval_min = 0): ?array {
		return self::query_latest_payload($source_id, $deal, $interval_min, 0, true);
	}

	/**
	 * Resolve reference snapshot for change calculation.
	 *
	 * Strategy: find the snapshot whose source_ts_utc is closest to
	 * (current_ts − target_window_seconds), searching both directions
	 * from that target. The current snapshot is excluded to avoid
	 * self-comparison.
	 *
	 * @return array{payload:array<string,mixed>,source_ts:int,fetch_ts:int}|null
	 */
	public static function get_change_reference_payload(
		string $source_id,
		string $deal,
		int $interval_min,
		int $current_ts,
		int $target_window_seconds
	): ?array {
		$current_ts  = max(1, $current_ts);
		$interval_min = max(1, $interval_min);
		$target_ts   = max(1, $current_ts - max(1, $target_window_seconds));

		$table = CRToday_Storage_Installer::rates_table();
		if (! self::table_exists($table)) {
			return null;
		}

		$source = strtoupper(trim($source_id));
		$deal_u = strtoupper(trim($deal));
		if ($source === '' || $deal_u === '') {
			return null;
		}

		$nearest_ts = self::find_nearest_source_ts(
			$table,
			$source,
			$deal_u,
			$interval_min,
			$target_ts,
			$current_ts
		);

		if ($nearest_ts === null) {
			return null;
		}

		return self::query_latest_payload($source_id, $deal, $interval_min, $nearest_ts, true);
	}

	/**
	 * Find the distinct source_ts_utc nearest to $target_ts,
	 * excluding the current snapshot ($exclude_ts) to avoid self-comparison.
	 */
	private static function find_nearest_source_ts(
		string $table,
		string $source,
		string $deal,
		int $interval_min,
		int $target_ts,
		int $exclude_ts
	): ?int {
		global $wpdb;

		$target_dt  = gmdate('Y-m-d H:i:s', $target_ts);
		$exclude_dt = gmdate('Y-m-d H:i:s', $exclude_ts);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Lightweight single-value lookup for nearest reference timestamp.
		$below = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT source_ts_utc FROM %i
				 WHERE source = %s AND deal = %s AND interval_min = %d
				   AND source_ts_utc <= %s AND source_ts_utc <> %s
				 ORDER BY source_ts_utc DESC LIMIT 1",
				$table,
				$source,
				$deal,
				$interval_min,
				$target_dt,
				$exclude_dt
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Lightweight single-value lookup for nearest reference timestamp.
		$above = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT source_ts_utc FROM %i
				 WHERE source = %s AND deal = %s AND interval_min = %d
				   AND source_ts_utc > %s AND source_ts_utc <> %s
				 ORDER BY source_ts_utc ASC LIMIT 1",
				$table,
				$source,
				$deal,
				$interval_min,
				$target_dt,
				$exclude_dt
			)
		);

		$below_ts = is_string($below) ? self::parse_utc_datetime_to_unix($below) : 0;
		$above_ts = is_string($above) ? self::parse_utc_datetime_to_unix($above) : 0;

		if ($below_ts <= 0 && $above_ts <= 0) {
			return null;
		}
		if ($below_ts > 0 && $above_ts <= 0) {
			return $below_ts;
		}
		if ($below_ts <= 0) {
			return $above_ts;
		}

		return abs($target_ts - $below_ts) <= abs($above_ts - $target_ts)
			? $below_ts
			: $above_ts;
	}

	/**
	 * @return array{payload:array<string,mixed>,source_ts:int,fetch_ts:int}|null
	 */
	private static function query_latest_payload(
		string $source_id,
		string $deal,
		int $interval_min,
		int $upper_ts,
		bool $inclusive
	): ?array {
		global $wpdb;

		$table = CRToday_Storage_Installer::rates_table();
		if (! self::table_exists($table)) {
			return null;
		}

		$source = strtoupper(trim($source_id));
		$deal = strtoupper(trim($deal));
		if ($source === '' || $deal === '') {
			return null;
		}

		$rows = [];
		if ($upper_ts > 0) {
			$upper_dt = gmdate('Y-m-d H:i:s', $upper_ts);

			if ($interval_min > 0) {
				if ($inclusive) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
					$rows = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
							 FROM %i
							 WHERE source = %s AND deal = %s AND interval_min = %d AND source_ts_utc <= %s
							 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
							$table,
							$source,
							$deal,
							$interval_min,
							$upper_dt
						),
						ARRAY_A
					);
				} else {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
					$rows = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
							 FROM %i
							 WHERE source = %s AND deal = %s AND interval_min = %d AND source_ts_utc < %s
							 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
							$table,
							$source,
							$deal,
							$interval_min,
							$upper_dt
						),
						ARRAY_A
					);
				}
			} else {
				if ($inclusive) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
					$rows = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
							 FROM %i
							 WHERE source = %s AND deal = %s AND source_ts_utc <= %s
							 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
							$table,
							$source,
							$deal,
							$upper_dt
						),
						ARRAY_A
					);
				} else {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
					$rows = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
							 FROM %i
							 WHERE source = %s AND deal = %s AND source_ts_utc < %s
							 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
							$table,
							$source,
							$deal,
							$upper_dt
						),
						ARRAY_A
					);
				}
			}
		} else {
			if ($interval_min > 0) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
						 FROM %i
						 WHERE source = %s AND deal = %s AND interval_min = %d
						 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
						$table,
						$source,
						$deal,
						$interval_min
					),
					ARRAY_A
				);
			} else {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository read path for latest deterministic rows.
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT base, code, bid, ask, mid, source_ts_utc, fetch_ts_utc
						 FROM %i
						 WHERE source = %s AND deal = %s
						 ORDER BY code ASC, source_ts_utc DESC, fetch_ts_utc DESC, id DESC",
						$table,
						$source,
						$deal
					),
					ARRAY_A
				);
			}
		}

		if (! is_array($rows) || $rows === []) {
			return null;
		}

		$latest_by_code = [];
		foreach ($rows as $row) {
			if (! is_array($row)) {
				continue;
			}

			$code = strtoupper((string) ($row['code'] ?? ''));
			if ($code === '' || isset($latest_by_code[$code])) {
				continue;
			}

			$latest_by_code[$code] = $row;
		}

		if ($latest_by_code === []) {
			return null;
		}

		$payload_rates = [];
		$base = '';
		$source_ts = 0;
		$fetch_ts = 0;

		foreach ($latest_by_code as $code => $row) {
			if ($base === '') {
				$base = strtoupper((string) ($row['base'] ?? 'USD'));
			}

			$rate_entry = [];
			$mid = self::normalize_rate($row['mid'] ?? null);
			$bid = self::normalize_rate($row['bid'] ?? null);
			$ask = self::normalize_rate($row['ask'] ?? null);

			if ($mid !== null) {
				$rate_entry['mid'] = $mid;
			}
			if ($bid !== null) {
				$rate_entry['bid'] = $bid;
			}
			if ($ask !== null) {
				$rate_entry['ask'] = $ask;
			}
			if ($rate_entry === [] && $mid !== null) {
				$rate_entry['mid'] = $mid;
			}
			if ($rate_entry === []) {
				continue;
			}

			$payload_rates[$code] = $rate_entry;

			$row_source_ts = self::parse_utc_datetime_to_unix((string) ($row['source_ts_utc'] ?? ''));
			$row_fetch_ts = self::parse_utc_datetime_to_unix((string) ($row['fetch_ts_utc'] ?? ''));
			$source_ts = max($source_ts, $row_source_ts);
			$fetch_ts = max($fetch_ts, $row_fetch_ts);
		}

		if ($payload_rates === []) {
			return null;
		}

		return [
			'payload' => [
				'base' => ($base !== '' ? $base : 'USD'),
				'ts' => max(1, $source_ts),
				'rates' => $payload_rates,
			],
			'source_ts' => max(1, $source_ts),
			'fetch_ts' => max(1, $fetch_ts),
		];
	}

	private static function apply_retention_policy(string $table, string $source, string $deal, int $interval_min): void {
		global $wpdb;

		$config = CRToday_Plugin_Settings::get_config();
		$retention_days = max(2, (int) ($config['retention'] ?? 2));
		$now = time();
		$dense_cutoff_dt = gmdate('Y-m-d H:i:s', $now - DAY_IN_SECONDS);
		$retention_cutoff_date = gmdate('Y-m-d', $now - ($retention_days * DAY_IN_SECONDS));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Retention cleanup is performed in-place for bounded storage.
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM %i
				 WHERE source = %s AND deal = %s AND interval_min = %d AND rate_day_utc < %s",
				$table,
				$source,
				$deal,
				$interval_min,
				$retention_cutoff_date
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Daily compaction keeps one latest record per day for older windows.
		$wpdb->query(
			$wpdb->prepare(
				"DELETE target
				 FROM %i AS target
				 LEFT JOIN (
				 	SELECT MAX(id) AS keep_id
				 	FROM %i
				 	WHERE source = %s AND deal = %s AND interval_min = %d AND rate_day_utc >= %s AND source_ts_utc < %s
				 	GROUP BY source, deal, base, code, interval_min, rate_day_utc
				 ) AS keepers ON target.id = keepers.keep_id
				 WHERE target.source = %s AND target.deal = %s AND target.interval_min = %d
				 AND target.rate_day_utc >= %s AND target.source_ts_utc < %s
				 AND keepers.keep_id IS NULL",
				$table,
				$table,
				$source,
				$deal,
				$interval_min,
				$retention_cutoff_date,
				$dense_cutoff_dt,
				$source,
				$deal,
				$interval_min,
				$retention_cutoff_date,
				$dense_cutoff_dt
			)
		);
	}

	private static function normalize_rate($value): ?float {
		if (! is_numeric($value)) {
			return null;
		}

		$rate = (float) $value;
		if (! is_finite($rate) || $rate <= 0) {
			return null;
		}

		return $rate;
	}

	private static function parse_utc_datetime_to_unix(string $value): int {
		if ($value === '') {
			return 0;
		}

		$dt = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $value, new DateTimeZone('UTC'));
		if (! ($dt instanceof DateTimeImmutable)) {
			return 0;
		}

		return (int) $dt->getTimestamp();
	}

	private static function table_exists(string $table): bool {
		return CRToday_Storage_Installer::table_exists($table);
	}
}
