<?php
/**
 * $wpdb-backed click event repository.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository SQL uses table-name resolver output plus prepared values.

namespace Affilun\Infrastructure\Persistence;

use DateTimeImmutable;
use DateTimeZone;

/**
 * Persists click events and daily rollups in custom tables.
 */
final class WpdbClickEventRepository implements ClickEventRepository {

	/**
	 * WordPress database adapter.
	 */
	private object $wpdb;

	/**
	 * Table resolver.
	 */
	private TableNameResolver $tables;

	/**
	 * Construct the repository.
	 *
	 * @param object            $wpdb   WordPress database adapter.
	 * @param TableNameResolver $tables Table resolver.
	 */
	public function __construct( object $wpdb, TableNameResolver $tables ) {
		$this->wpdb   = $wpdb;
		$this->tables = $tables;
	}

	/**
	 * Record a sanitized click event.
	 *
	 * @param array<string,mixed> $event Sanitized event payload.
	 */
	public function record( array $event ): void {
		$createdAt = self::stringField( $event, 'created_at', gmdate( 'c' ) );
		$date      = substr( $createdAt, 0, 10 );
		$provider  = self::stringField( $event, 'provider', 'unknown' );
		$buttonKey = self::stringField( $event, 'button_key', 'unknown' );
		$variant   = self::stringField( $event, 'variant', '' );
		$productId = self::intField( $event, 'product_id' );
		$postId    = self::intField( $event, 'post_id' );
		$metadata  = isset( $event['metadata'] ) && is_array( $event['metadata'] ) ? $event['metadata'] : array();

		$this->wpdb->insert(
			$this->tables->resolve( 'affilun_click_events' ),
			array(
				'provider'        => $provider,
				'button_key'      => $buttonKey,
				'target_url_hash' => self::stringField( $event, 'target_url_hash', '' ),
				'product_id'      => $productId,
				'post_id'         => $postId,
				'variant'         => $variant,
				'user_agent_hash' => self::stringField( $metadata, 'user_agent_hash', '' ),
				'referer_host'    => self::stringField( $metadata, 'referer_host', '' ),
				'created_at'      => gmdate( 'Y-m-d H:i:s', strtotime( $createdAt ) ?: time() ),
			),
			array( '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s' )
		);

		$this->upsertRollup( $date, $provider, $buttonKey, $variant, $productId, $postId );
	}

	/**
	 * Return recent click events.
	 *
	 * @param int $limit Maximum rows.
	 *
	 * @return list<array<string,mixed>>
	 */
	public function recentEvents( int $limit = 500 ): array {
		$limit = max( 1, min( 5000, $limit ) );
		$table = $this->tables->resolve( 'affilun_click_events' );
		$sql   = 'SELECT created_at, provider, button_key, product_id, post_id, variant, target_url_hash '
			. "FROM {$table} ORDER BY created_at DESC, id DESC LIMIT %d";
		$rows  = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL contains only resolver-produced table name and placeholder; value bound immediately here.
			$this->wpdb->prepare( $sql, $limit ),
			ARRAY_A
		);
		if ( ! is_array( $rows ) ) {
			return array();
		}

		$events = array();
		foreach ( $rows as $row ) {
			if ( is_array( $row ) ) {
				$events[] = $row;
			}
		}
		return $events;
	}

	/**
	 * Count recent click events matching a client bucket.
	 *
	 * @param string            $userAgentHash User-agent hash.
	 * @param string            $refererHost   Referer host.
	 * @param DateTimeImmutable $since         Lower bound.
	 */
	public function countRecentMatching( string $userAgentHash, string $refererHost, DateTimeImmutable $since ): int {
		$table = $this->tables->resolve( 'affilun_click_events' );
		$since = $since->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
		$sql   = "SELECT COUNT(*) FROM {$table} WHERE created_at >= %s AND user_agent_hash = %s AND referer_host = %s";
		$count = $this->wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQL contains resolver-produced table name and placeholders; values bound immediately here.
			$this->wpdb->prepare( $sql, $since, $userAgentHash, $refererHost )
		);
		return is_numeric( $count ) ? max( 0, (int) $count ) : 0;
	}

	/**
	 * Delete click events and rollups older than cutoff.
	 *
	 * @param DateTimeImmutable $cutoff Cutoff instant.
	 */
	public function deleteOlderThan( DateTimeImmutable $cutoff ): int {
		$cutoff         = $cutoff->setTimezone( new DateTimeZone( 'UTC' ) );
		$eventTable     = $this->tables->resolve( 'affilun_click_events' );
		$rollupTable    = $this->tables->resolve( 'affilun_click_rollups' );
		$cutoffDate     = $cutoff->format( 'Y-m-d' );
		$cutoffDateTime = $cutoff->format( 'Y-m-d H:i:s' );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- SQL is prepared inline in this query call.
		$this->wpdb->query(
			$this->wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQL contains resolver-produced table name and one placeholder.
				"DELETE FROM {$eventTable} WHERE created_at < %s",
				$cutoffDateTime
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$deleted = isset( $this->wpdb->rows_affected ) ? max( 0, (int) $this->wpdb->rows_affected ) : 0;

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- SQL is prepared inline in this query call.
		$this->wpdb->query(
			$this->wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQL contains resolver-produced table name and one placeholder.
				"DELETE FROM {$rollupTable} WHERE rollup_date < %s",
				$cutoffDate
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared

		return $deleted;
	}

	/**
	 * Upsert the daily rollup row.
	 *
	 * @param string   $date      Rollup date.
	 * @param string   $provider  Provider token.
	 * @param string   $buttonKey Button key.
	 * @param string   $variant   Variant token.
	 * @param int|null $productId Product id.
	 * @param int|null $postId    Post id.
	 */
	private function upsertRollup( string $date, string $provider, string $buttonKey, string $variant, ?int $productId, ?int $postId ): void {
		$table = $this->tables->resolve( 'affilun_click_rollups' );
		$sql   = "INSERT INTO {$table} (rollup_date, provider, button_key, variant, product_id, post_id, clicks, updated_at) "
			. 'VALUES (%s, %s, %s, %s, %d, %d, 1, %s) '
			. 'ON DUPLICATE KEY UPDATE clicks = clicks + 1, updated_at = VALUES(updated_at)';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- SQL is prepared inline in this query call.
		$this->wpdb->query(
			$this->wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL contains only a resolver-produced table name and placeholders; values are bound immediately here.
				$sql,
				$date,
				$provider,
				$buttonKey,
				$variant,
				$productId ?? 0,
				$postId ?? 0,
				gmdate( 'Y-m-d H:i:s' )
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Read a string field.
	 *
	 * @param array<string,mixed> $event    Event or metadata.
	 * @param string              $field    Field name.
	 * @param string              $fallback Fallback value.
	 */
	private static function stringField( array $event, string $field, string $fallback ): string {
		$value = $event[ $field ] ?? null;
		return is_string( $value ) && trim( $value ) !== '' ? trim( $value ) : $fallback;
	}

	/**
	 * Read a positive integer field.
	 *
	 * @param array<string,mixed> $event Event.
	 * @param string              $field Field name.
	 */
	private static function intField( array $event, string $field ): ?int {
		$value = $event[ $field ] ?? null;
		return is_int( $value ) && $value > 0 ? $value : null;
	}
}