<?php

declare(strict_types=1);

namespace Australcode\Rewards\Infrastructure\Wallet;

use Australcode\Rewards\Domain\Wallet\LedgerAnalytics;
use Australcode\Rewards\Domain\Wallet\TransactionStatus;
use Australcode\Rewards\Infrastructure\Database\Schema;

defined( 'ABSPATH' ) || exit;

/**
 * wp_acrwd_transactions-backed analytics: per-day net series (sparkline / trend)
 * and top movers (most active customers) over a time window. Active entries only;
 * windows are half-open [$from, $to) on `created_at` (UTC).
 */
final class CustomTableLedgerAnalytics implements LedgerAnalytics {

	public function daily_series( string $from, string $to ): array {
		global $wpdb;
		$table = Schema::table_name( Schema::TABLE_TRANSACTIONS );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table from internal Schema constant; values bound.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(created_at) AS day, COALESCE(SUM(points_delta), 0) AS net
				 FROM `{$table}`
				 WHERE status = %s AND created_at >= %s AND created_at < %s
				 GROUP BY DATE(created_at)
				 ORDER BY day ASC",
				TransactionStatus::Active->value,
				$from,
				$to
			),
			ARRAY_A
		);

		$series = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( is_array( $row ) && isset( $row['day'] ) ) {
					$series[ (string) $row['day'] ] = (int) $row['net'];
				}
			}
		}

		return $series;
	}

	public function top_movers( string $from, string $to, int $limit ): array {
		$limit = max( 1, $limit );

		global $wpdb;
		$table = Schema::table_name( Schema::TABLE_TRANSACTIONS );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table from internal Schema constant; values bound.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT user_id, SUM(ABS(points_delta)) AS volume, SUM(points_delta) AS net
				 FROM `{$table}`
				 WHERE status = %s AND created_at >= %s AND created_at < %s
				 GROUP BY user_id
				 HAVING volume > 0
				 ORDER BY volume DESC, user_id ASC
				 LIMIT %d",
				TransactionStatus::Active->value,
				$from,
				$to,
				$limit
			),
			ARRAY_A
		);

		$movers = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( is_array( $row ) ) {
					$movers[] = array(
						'user_id' => (int) ( $row['user_id'] ?? 0 ),
						'volume'  => (int) ( $row['volume'] ?? 0 ),
						'net'     => (int) ( $row['net'] ?? 0 ),
					);
				}
			}
		}

		return $movers;
	}
}
