<?php
/**
 * Visit data access.
 *
 * @package PPL_AFW
 */

namespace PPL_AFW\Tracking;

use PPL_AFW\Core\Repository;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This plugin stores affiliate data in its own custom tables, which the WordPress data APIs cannot read or write, so direct queries are required. Every query is assembled from string literals and executed through $wpdb->prepare(), with %i for table names and %s/%d/%f for values, so nothing is ever interpolated into SQL.

/**
 * Repository for the visits table.
 */
class Visit_Repository extends Repository {

	/**
	 * Table key.
	 *
	 * @var string
	 */
	protected $table_key = 'visits';

	/**
	 * Finds the most recent unconverted visit matching a fingerprint hash.
	 *
	 * @since 1.0.0
	 * @param string $fingerprint_hash Hashed fingerprint.
	 * @param int    $max_age_days     Attribution window in days.
	 * @return object|null
	 */
	public function find_by_fingerprint( $fingerprint_hash, $max_age_days = 30 ) {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE fingerprint_hash = %s AND converted = 0 AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) ORDER BY created_at DESC LIMIT 1",
				$this->table(),
				$fingerprint_hash,
				absint( $max_age_days
			)
			)
		) ?: null;
	}

	/**
	 * Marks a visit converted against an order.
	 *
	 * @since 1.0.0
	 * @param int $visit_id Visit id.
	 * @param int $order_id Order id.
	 */
	public function mark_converted( $visit_id, $order_id ) {
		$this->update(
			$visit_id,
			array(
				'converted' => 1,
				'order_id'  => absint( $order_id ),
			)
		);

		/**
		 * Fires after a visit is marked converted.
		 *
		 * @since 1.3.0
		 * @param int $visit_id Visit id.
		 * @param int $order_id Order id.
		 */
		do_action( 'ppl_afw_visit_converted', (int) $visit_id, (int) $order_id );
	}

	/**
	 * Paginated visits for an affiliate.
	 *
	 * @since 1.0.0
	 * @param int $affiliate_id Affiliate id.
	 * @param int $page         Page number.
	 * @param int $per_page     Rows per page.
	 * @return object[]
	 */
	public function for_affiliate( $affiliate_id, $page = 1, $per_page = 20 ) {
		global $wpdb;
		$offset = ( max( 1, (int) $page ) - 1 ) * (int) $per_page;
		return $wpdb->get_results( $wpdb->prepare(
				"SELECT * FROM %i WHERE affiliate_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d",
				$this->table(),
				absint( $affiliate_id
			), (int) $per_page, $offset ) );
	}

	/**
	 * Paginated visits with filters for the admin list.
	 *
	 * @since 1.0.0
	 * @param array $args affiliate_id, converted, page, per_page.
	 * @return array{rows: object[], total: int}
	 */
	public function query( array $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'affiliate_id' => 0,
				'converted'    => '',
				'page'         => 1,
				'per_page'     => 20,
			)
		);

		$where  = 'WHERE 1=1';
		$params = array();
		if ( $args['affiliate_id'] ) {
			$where   .= ' AND affiliate_id = %d';
			$params[] = absint( $args['affiliate_id'] );
		}
		if ( '' !== $args['converted'] ) {
			$where   .= ' AND converted = %d';
			$params[] = (int) (bool) $args['converted'];
		}

		$offset = ( max( 1, (int) $args['page'] ) - 1 ) * (int) $args['per_page'];
		$p      = array_merge( $params, array( (int) $args['per_page'], $offset ) );

		$list  = 'SELECT * FROM %i ' . $where . ' ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d';
		$count = 'SELECT COUNT(*) FROM %i ' . $where;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is concatenated from the string literals above; the table name and every value are bound by prepare() below.
		$rows = $wpdb->get_results( $wpdb->prepare( $list, $this->table(), ...$p ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Same query fragments, bound the same way.
		$total = (int) $wpdb->get_var( $wpdb->prepare( $count, $this->table(), ...$params ) );

		return array(
			'rows'  => $rows,
			'total' => $total,
		);
	}

	/**
	 * Daily aggregate of visits and conversions for stats.
	 *
	 * @since 1.0.0
	 * @param string $date Y-m-d UTC day.
	 * @return object[] Rows of affiliate_id, visit_count, conversion_count.
	 */
	public function daily_totals( $date ) {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT affiliate_id, COUNT(*) AS visit_count, SUM(converted) AS conversion_count FROM %i WHERE created_at >= %s AND created_at < %s + INTERVAL 1 DAY GROUP BY affiliate_id",
				$this->table(),
				$date . ' 00:00:00',
				$date . ' 00:00:00'
			)
		);
	}

	/**
	 * Counts visits within a window for velocity checks.
	 *
	 * @since 1.0.0
	 * @param int $affiliate_id Affiliate id.
	 * @param int $minutes      Window minutes.
	 * @return int
	 */
	public function recent_count( $affiliate_id, $minutes = 10 ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE affiliate_id = %d AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d MINUTE)",
				$this->table(),
				absint( $affiliate_id
			), absint( $minutes ) ) );
	}
}