<?php
namespace Advently\Data;

defined( 'ABSPATH' ) || exit;

/**
 * Subscriber listing for the admin: one page at a time, or all rows for CSV.
 */
final class SubscriberList {

	/** @var string */
	private $start;

	/**
	 * @param string $start Calendar start (Y-m-d): doors opened are counted for this run.
	 */
	public function __construct( string $start ) {
		$this->start = $start;
	}

	/**
	 * @return array{items: array[], total: int}
	 */
	public function page( int $page, int $perPage, string $search ): array {
		global $wpdb;
		$like   = '%' . $wpdb->esc_like( $search ) . '%';
		$offset = max( 0, ( $page - 1 ) * $perPage );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom tables, admin listing.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->prefix}advently_subscribers WHERE email LIKE %s", $like )
		);
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT s.id, s.email, s.user_id, s.created_at, s.consent_at,
					( SELECT COUNT(*) FROM {$wpdb->prefix}advently_opens o WHERE o.subscriber_id = s.id AND o.calendar_start = %s ) AS opened
				FROM {$wpdb->prefix}advently_subscribers s
				WHERE s.email LIKE %s
				ORDER BY s.id DESC
				LIMIT %d OFFSET %d",
				$this->start,
				$like,
				$perPage,
				$offset
			),
			ARRAY_A
		);
		// phpcs:enable

		return [
			'items' => array_map( [ self::class, 'shape' ], is_array( $rows ) ? $rows : [] ),
			'total' => $total,
		];
	}

	/**
	 * Every subscriber, in batches, for the CSV export.
	 *
	 * @param callable $each Called with each shaped row.
	 */
	public function each( callable $each ): void {
		$page = 1;
		do {
			$items = $this->page( $page, 500, '' )['items'];
			$count = count( $items );
			foreach ( $items as $row ) {
				$each( $row );
			}
			++$page;
		} while ( 500 === $count );
	}

	private static function shape( array $row ): array {
		return [
			'id'      => (int) $row['id'],
			'email'   => (string) $row['email'],
			'user_id' => (int) $row['user_id'],
			'joined'  => self::local( (string) $row['created_at'] ),
			'consent' => self::local( (string) $row['consent_at'] ),
			'opened'  => (int) $row['opened'],
		];
	}

	/**
	 * Stored times are UTC; shown in the site's format and timezone.
	 */
	private static function local( string $utc ): string {
		if ( '' === $utc || '0000-00-00 00:00:00' === $utc ) {
			return '';
		}
		return (string) wp_date( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), strtotime( $utc . ' UTC' ) );
	}
}