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

namespace PPL_AFW\Affiliates;

use PPL_AFW\Core\Repository;
use PPL_AFW\Core\Schema;

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 affiliates table.
 */
class Affiliate_Repository extends Repository {

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

	/**
	 * Finds the affiliate row for a WordPress user id.
	 *
	 * @since 1.0.0
	 * @param int $user_id User id.
	 * @return object|null
	 */
	public function find_by_user( $user_id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare(
				"SELECT * FROM %i WHERE user_id = %d",
				$this->table(),
				absint( $user_id
			) ) ) ?: null;
	}

	/**
	 * Finds an active affiliate by vanity slug or numeric id token.
	 *
	 * @since 1.0.0
	 * @param string $token Slug or numeric id from a referral URL.
	 * @return object|null
	 */
	public function find_by_token( $token ) {
		global $wpdb;
		$token = sanitize_key( $token );
		if ( '' === $token ) {
			return null;
		}
		if ( ctype_digit( $token ) ) {
			$row = $this->find( (int) $token );
			return ( $row && 'active' === $row->status ) ? $row : null;
		}
		return $wpdb->get_row( $wpdb->prepare(
				"SELECT * FROM %i WHERE slug = %s AND status = 'active'",
				$this->table(),
				$token
			) ) ?: null;
	}

	/**
	 * Whether a slug is already taken.
	 *
	 * @since 1.0.0
	 * @param string $slug       Candidate slug.
	 * @param int    $exclude_id Affiliate id to exclude.
	 * @return bool
	 */
	public function slug_exists( $slug, $exclude_id = 0 ) {
		global $wpdb;
		return (bool) $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM %i WHERE slug = %s AND id != %d",
				$this->table(),
				$slug, absint( $exclude_id
			) ) );
	}

	/**
	 * Paginated affiliate query for admin lists.
	 *
	 * @since 1.0.0
	 * @param array $args status, search, page, per_page.
	 * @return array{rows: object[], total: int}
	 */
	public function query( array $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'status'   => '',
				'search'   => '',
				'page'     => 1,
				'per_page' => 20,
			)
		);

		$where  = 'WHERE 1=1';
		$params = array();

		if ( '' !== $args['status'] ) {
			$where   .= ' AND a.status = %s';
			$params[] = $args['status'];
		}
		if ( '' !== $args['search'] ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where   .= ' AND (a.slug LIKE %s OR u.user_email LIKE %s OR u.display_name LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

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

		// Both table names are bound as identifiers, so the only thing joined
		// into these strings are the literal fragments built above.
		$from = '%i a LEFT JOIN %i u ON u.ID = a.user_id';
		$ids  = array( $this->table(), $wpdb->users );

		$list  = 'SELECT a.* FROM ' . $from . ' ' . $where . ' ORDER BY a.created_at DESC LIMIT %d OFFSET %d';
		$count = 'SELECT COUNT(*) FROM ' . $from . ' ' . $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, ...array_merge( $ids, $params, array( (int) $args['per_page'], $offset ) ) ) );
		// 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, ...array_merge( $ids, $params ) ) );

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

	/**
	 * Adjusts unpaid and lifetime earnings atomically.
	 *
	 * @since 1.0.0
	 * @param int   $id     Affiliate id.
	 * @param float $delta  Signed amount to apply to unpaid earnings.
	 * @param bool  $lifetime Also apply to lifetime earnings.
	 */
	public function adjust_earnings( $id, $delta, $lifetime = true ) {
		global $wpdb;

		if ( $lifetime ) {
			$wpdb->query(
				$wpdb->prepare(
					'UPDATE %i SET unpaid_earnings = unpaid_earnings + %f, lifetime_earnings = lifetime_earnings + %f WHERE id = %d',
					$this->table(),
					$delta,
					$delta,
					absint( $id )
				)
			);
		} else {
			$wpdb->query(
				$wpdb->prepare(
					'UPDATE %i SET unpaid_earnings = unpaid_earnings + %f WHERE id = %d',
					$this->table(),
					$delta,
					absint( $id )
				)
			);
		}

		wp_cache_delete( $this->table_key . ':' . absint( $id ), $this->cache_group );
	}

	/**
	 * Recent activity log entries for an affiliate.
	 *
	 * @since 1.0.0
	 * @param int $affiliate_id Affiliate id.
	 * @param int $limit        Row cap.
	 * @return object[]
	 */
	public function activity( $affiliate_id, $limit = 30 ) {
		global $wpdb;
		$table = Schema::table( 'activity_log' );
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE affiliate_id = %d ORDER BY id DESC LIMIT %d',
				$table,
				absint( $affiliate_id ),
				absint( $limit )
			)
		);
	}

	/**
	 * Counts affiliates per status for list screen views.
	 *
	 * @since 1.0.0
	 * @return array status => count.
	 */
	public function status_counts() {
		global $wpdb;
		$rows   = $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) AS total FROM %i GROUP BY status", $this->table() ) );
		$counts = array();
		foreach ( $rows as $row ) {
			$counts[ $row->status ] = (int) $row->total;
		}
		return $counts;
	}

	/**
	 * Records an activity log entry for an affiliate.
	 *
	 * @since 1.0.0
	 * @param int    $affiliate_id Affiliate id.
	 * @param string $action       Machine action key.
	 * @param string $note         Human readable note.
	 * @param array  $meta         Extra context.
	 */
	public function log_activity( $affiliate_id, $action, $note = '', array $meta = array() ) {
		global $wpdb;
		$wpdb->insert(
			Schema::table( 'activity_log' ),
			array(
				'affiliate_id' => absint( $affiliate_id ),
				'actor_id'     => get_current_user_id(),
				'action'       => sanitize_key( $action ),
				'note'         => sanitize_text_field( $note ),
				'meta'         => wp_json_encode( $meta ),
				'created_at'   => current_time( 'mysql', true ),
			)
		);
	}
}