<?php

declare(strict_types=1);

namespace Australcode\Rewards\Infrastructure\Wallet;

use Australcode\Rewards\Domain\Wallet\Balance;
use Australcode\Rewards\Domain\Wallet\BalanceDirectory;
use Australcode\Rewards\Infrastructure\Database\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * wp_acrwd_balances-backed read-model for the admin Customers list. Read-only:
 * a paginated, balance-descending listing, a search (joins wp_users), and counts.
 */
final class CustomTableBalanceDirectory implements BalanceDirectory {

	public function paginate( int $limit, int $offset ): array {
		global $wpdb;
		$table  = Schema::table_name( Schema::TABLE_BALANCES );
		$limit  = max( 1, $limit );
		$offset = max( 0, $offset );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from internal Schema constant; values bound.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM `{$table}` ORDER BY points_balance DESC, user_id ASC LIMIT %d OFFSET %d",
				$limit,
				$offset
			),
			ARRAY_A
		);

		return $this->map_rows( $rows );
	}

	public function search( string $term, int $limit, int $offset ): array {
		global $wpdb;
		$table  = Schema::table_name( Schema::TABLE_BALANCES );
		$users  = $wpdb->users;
		$limit  = max( 1, $limit );
		$offset = max( 0, $offset );
		$like   = '%' . $wpdb->esc_like( $term ) . '%';
		$id     = ctype_digit( $term ) ? (int) $term : 0;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table/users identifiers from Schema/$wpdb; values bound.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.* FROM `{$table}` b INNER JOIN `{$users}` u ON b.user_id = u.ID
				 WHERE u.user_email LIKE %s OR u.display_name LIKE %s OR u.user_login LIKE %s OR b.user_id = %d
				 ORDER BY b.points_balance DESC, b.user_id ASC LIMIT %d OFFSET %d",
				$like,
				$like,
				$like,
				$id,
				$limit,
				$offset
			),
			ARRAY_A
		);

		return $this->map_rows( $rows );
	}

	public function count(): int {
		global $wpdb;
		$table = Schema::table_name( Schema::TABLE_BALANCES );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from internal Schema constant; no user input.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
	}

	public function count_search( string $term ): int {
		global $wpdb;
		$table = Schema::table_name( Schema::TABLE_BALANCES );
		$users = $wpdb->users;
		$like  = '%' . $wpdb->esc_like( $term ) . '%';
		$id    = ctype_digit( $term ) ? (int) $term : 0;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table/users identifiers from Schema/$wpdb; values bound.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM `{$table}` b INNER JOIN `{$users}` u ON b.user_id = u.ID
				 WHERE u.user_email LIKE %s OR u.display_name LIKE %s OR u.user_login LIKE %s OR b.user_id = %d",
				$like,
				$like,
				$like,
				$id
			)
		);
	}

	public function totals(): array {
		global $wpdb;
		$table = Schema::table_name( Schema::TABLE_BALANCES );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from internal Schema constant; no user input.
		$row = $wpdb->get_row(
			"SELECT COUNT(*) AS members, COALESCE(SUM(points_balance),0) AS outstanding, COALESCE(SUM(lifetime_earned),0) AS earned, COALESCE(SUM(lifetime_redeemed),0) AS redeemed FROM `{$table}`",
			ARRAY_A
		);

		return array(
			'members'           => is_array( $row ) ? (int) ( $row['members'] ?? 0 ) : 0,
			'outstanding'       => is_array( $row ) ? (int) ( $row['outstanding'] ?? 0 ) : 0,
			'lifetime_earned'   => is_array( $row ) ? (int) ( $row['earned'] ?? 0 ) : 0,
			'lifetime_redeemed' => is_array( $row ) ? (int) ( $row['redeemed'] ?? 0 ) : 0,
		);
	}

	/**
	 * Maps raw $wpdb rows to Balance VOs, skipping any non-array row.
	 *
	 * @param mixed $rows
	 * @return Balance[]
	 */
	private function map_rows( $rows ): array {
		$balances = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( is_array( $row ) ) {
					$balances[] = Balance::from_row( $row );
				}
			}
		}

		return $balances;
	}
}
