<?php
/**
 * Recovery email repository helpers.
 *
 * @package EDD\Pro\CartRecovery\Emails
 * @copyright Copyright (c) 2026, Sandhills Development, LLC
 * @license http://www.gnu.org/licenses/gpl-2.0.html GPL-2.0-or-later
 * @since 3.6.5
 */

namespace EDD\Pro\CartRecovery\Emails;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit; // @codeCoverageIgnore

use EDD\Pro\CartRecovery\Database\Queries\RecoveryEmail as Query;
use EDD\Pro\CartRecovery\Traits\Debug;
use wpdb;

/**
 * Provides paginated and aggregate access to recovery email records.
 *
 * @since 3.6.5
 */
final class Repository {
	use Debug;

	/**
	 * Table name including WordPress prefix.
	 *
	 * @since 3.6.5
	 * @var string
	 */
	private $table_name;

	/**
	 * Database handler.
	 *
	 * @var wpdb|null
	 */
	private $wpdb;

	/**
	 * Constructor.
	 *
	 * @since 3.6.5
	 */
	public function __construct() {
		global $wpdb;

		$this->wpdb       = $wpdb;
		$this->table_name = $this->wpdb->prefix . 'edd_acr_emails';
	}

	/**
	 * Retrieves recovery emails for admin listing with pagination and filtering.
	 *
	 * @since 3.6.5
	 * @param array $args {
	 *     Optional. Query parameters.
	 *
	 *     @type int    $number       Number of results to retrieve. Defaults to 20.
	 *     @type int    $offset       Number of results to offset (for pagination). Defaults to 0.
	 *     @type string $status       Filter by delivery_status value.
	 *     @type int    $cart_id      Filter by related cart.
	 *     @type string $template_id  Filter by template identifier.
	 *     @type string $date_from    Inclusive start date (Y-m-d or Y-m-d H:i:s).
	 *     @type string $date_to      Inclusive end date (Y-m-d or Y-m-d H:i:s).
	 *     @type string $orderby      Order field. Defaults to date_sent.
	 *     @type string $order        Order direction ASC/DESC.
	 * }
	 * @return array {
	 *     @type array $items Recovery email items.
	 *     @type int   $total Total rows matching filters.
	 *     @type int   $pages Total pages available.
	 * }
	 */
	public function get_emails_for_admin( array $args = array() ): array {
		$defaults = array(
			'number'      => 20,
			'offset'      => 0,
			'status'      => '',
			'cart_id'     => 0,
			'template_id' => '',
			'date_from'   => '',
			'date_to'     => '',
			'orderby'     => 'date_sent',
			'order'       => 'DESC',
		);

		$parsed = wp_parse_args( $args, $defaults );

		$query_args = array(
			'number'  => max( 1, (int) $parsed['number'] ),
			'offset'  => max( 0, (int) $parsed['offset'] ),
			'orderby' => $parsed['orderby'],
			'order'   => $parsed['order'],
		);

		if ( ! empty( $parsed['delivery_status'] ) ) {
			$query_args['delivery_status'] = $parsed['delivery_status'];
		}

		if ( ! empty( $parsed['cart_id'] ) ) {
			$query_args['cart_id'] = (int) $parsed['cart_id'];
		}

		if ( ! empty( $parsed['template_id'] ) ) {
			$query_args['template_id'] = $parsed['template_id'];
		}

		if ( ! empty( $parsed['date_from'] ) || ! empty( $parsed['date_to'] ) ) {
			$date_range = array( 'column' => 'date_sent' );

			if ( ! empty( $parsed['date_from'] ) ) {
				$date_range['after'] = $this->normalise_boundary( $parsed['date_from'] );
			}

			if ( ! empty( $parsed['date_to'] ) ) {
				$date_range['before'] = $this->normalise_boundary( $parsed['date_to'] );
			}

			$query_args['date_query'] = array( $date_range );
		}

		$query = new Query();
		$items = $query->query( $query_args );

		$total_args = $query_args;
		unset( $total_args['number'], $total_args['offset'] );
		$total_args['count']         = true;
		$total_args['no_found_rows'] = false;

		$total_query = new Query();
		$total_count = (int) $total_query->query( $total_args );

		return array(
			'items' => $items,
			'total' => $total_count,
			'pages' => (int) ceil( $total_count / $query_args['number'] ),
		);
	}

	/**
	 * Gets counts of recovery emails grouped by delivery status.
	 *
	 * @since 3.6.5
	 * @return array Associative array keyed by delivery_status.
	 */
	public function get_email_status_counts(): array {
		$results = $this->wpdb->get_results(
			"SELECT delivery_status, COUNT(*) AS count
			 FROM {$this->table_name}
			 GROUP BY delivery_status"
		);

		$error = isset( $this->wpdb->last_error ) ? trim( (string) $this->wpdb->last_error ) : '';
		if ( '' !== $error ) {
			$this->log( 'Database error while retrieving email status counts: ' . $error, true );
			return array();
		}

		$counts = array();
		foreach ( (array) $results as $row ) {
			$counts[ $row->delivery_status ] = (int) $row->count;
		}

		return $counts;
	}

	/**
	 * Counts how many recovery emails have been recorded for a cart.
	 *
	 * @since 3.6.5
	 * @param int $cart_id Cart identifier.
	 * @return int Number of email attempts.
	 */
	public function get_count_by_cart_id( int $cart_id ): int {
		if ( $cart_id <= 0 ) {
			return 0;
		}

		$query = new Query();

		return (int) $query->query(
			array(
				'cart_id' => $cart_id,
				'count'   => true,
			)
		);
	}

	/**
	 * Retrieves all emails associated with a given cart ID.
	 *
	 * @since 3.6.5
	 * @param int $cart_id Cart identifier.
	 * @return array List of email rows.
	 */
	public function get_emails_by_cart_id( int $cart_id ): array {
		if ( $cart_id <= 0 ) {
			return array();
		}

		$query = new Query();

		return (array) $query->query(
			array(
				'cart_id' => $cart_id,
				'orderby' => 'date_sent',
				'order'   => 'DESC',
				'number'  => 9999,
			)
		);
	}

	/**
	 * Normalises a boundary string into a MySQL datetime in the site timezone.
	 *
	 * @since 3.6.5
	 * @param mixed $boundary Raw boundary value.
	 *
	 * @return string|null
	 */
	private function normalise_boundary( $boundary ): ?string {
		if ( ! is_string( $boundary ) ) {
			return null;
		}

		$trimmed = trim( $boundary );
		if ( '' === $trimmed ) {
			return null;
		}

		try {
			$datetime = EDD()->utils->date( $trimmed, edd_get_timezone_id(), true );
		} catch ( \Exception $exception ) {
			return null;
		}

		return $datetime->format( 'Y-m-d H:i:s' );
	}
}
