<?php
/**
 * Customer journey timeline for admin.
 *
 * @package CartCue
 */

namespace CartCue;

defined( 'ABSPATH' ) || exit;

final class CustomerJourney {

	/**
	 * @return array<int, array{at: string, type: string, label: string, workflow_id: int}>
	 */
	public static function get_timeline( string $email, int $limit = 100 ): array {
		$email = strtolower( trim( sanitize_email( $email ) ) );
		if ( ! is_email( $email ) ) {
			return [];
		}

		global $wpdb;
		$events = [];

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$journey = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT workflow_id, event_type, detail, created_at FROM %i
				 WHERE customer_email = %s ORDER BY created_at DESC LIMIT %d',
				DB::journey_events(),
				$email,
				$limit
			),
			ARRAY_A
		) ?: [];

		foreach ( $journey as $row ) {
			$events[] = [
				'at'          => (string) $row['created_at'],
				'type'        => (string) $row['event_type'],
				'label'       => (string) $row['detail'],
				'workflow_id' => (int) $row['workflow_id'],
			];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$emails = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT workflow_id, subject, sent_at, opened_at, clicks FROM %i
				 WHERE customer_email = %s ORDER BY sent_at DESC LIMIT %d',
				DB::email_tracking(),
				$email,
				$limit
			),
			ARRAY_A
		) ?: [];

		foreach ( $emails as $row ) {
			$label = sprintf(
				/* translators: 1: email subject */
				__( 'Email sent: %s', 'cartcue-sales-automation' ),
				(string) $row['subject']
			);
			if ( ! empty( $row['opened_at'] ) ) {
				$label .= ' · ' . __( 'opened', 'cartcue-sales-automation' );
			}
			if ( (int) $row['clicks'] > 0 ) {
				$label .= ' · ' . sprintf(
					/* translators: %d: click count */
					_n( '%d click', '%d clicks', (int) $row['clicks'], 'cartcue-sales-automation' ),
					(int) $row['clicks']
				);
			}
			$events[] = [
				'at'          => (string) $row['sent_at'],
				'type'        => 'email_sent',
				'label'       => $label,
				'workflow_id' => (int) $row['workflow_id'],
			];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$runs = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT pr.workflow_id, pr.fired_at, w.name FROM %i pr
				 LEFT JOIN %i w ON w.id = pr.workflow_id
				 WHERE pr.customer_email = %s ORDER BY pr.fired_at DESC LIMIT %d',
				DB::play_runs(),
				DB::workflows(),
				$email,
				$limit
			),
			ARRAY_A
		) ?: [];

		foreach ( $runs as $row ) {
			$name = (string) ( $row['name'] ?? '' );
			$events[] = [
				'at'          => (string) $row['fired_at'],
				'type'        => 'play_run',
				'label'       => $name
					? sprintf(
						/* translators: %s: automation name */
						__( 'Automation run: %s', 'cartcue-sales-automation' ),
						$name
					)
					: __( 'Automation run', 'cartcue-sales-automation' ),
				'workflow_id' => (int) $row['workflow_id'],
			];
		}

		$reason = UnsubscribeReasons::get_for_email( $email );
		if ( $reason ) {
			$events[] = [
				'at'          => (string) $reason['created_at'],
				'type'        => 'unsubscribed',
				'label'       => UnsubscribeReasons::format_label( $reason ),
				'workflow_id' => 0,
			];
		}

		usort(
			$events,
			static function ( array $a, array $b ): int {
				return strcmp( $b['at'], $a['at'] );
			}
		);

		return array_slice( $events, 0, $limit );
	}
}
