<?php
/**
 * Logger reporting helpers.
 *
 * @package MailDoctor
 */

namespace MailDoctor;

use DateTimeImmutable;
use DateTimeZone;

trait Logger_Report_Trait {


	/**
	 * Retrieve aggregate metrics for recent activity.
	 *
	 * @param int $days Time window in days.
	 * @return array<string, mixed>
	 */
	public static function get_metrics( int $days ): array {
		$days      = max( 1, $days );
		$cache_key = sprintf( 'mail_doctor_metrics_%d', $days );
		$cached    = get_transient( $cache_key );

		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;

		$table = self::table_name();
		$start = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$counts = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT status, COUNT(*) AS total FROM {$table} WHERE created_at >= %s GROUP BY status",
				$start
			),
			ARRAY_A
		);

		$metrics = array(
			'sent'    => 0,
			'failed'  => 0,
			'skipped' => 0,
			'queued'  => 0,
		);

		foreach ( (array) $counts as $row ) {
			$status = isset( $row['status'] ) ? sanitize_key( $row['status'] ) : '';
			$total  = isset( $row['total'] ) ? (int) $row['total'] : 0;

			if ( array_key_exists( $status, $metrics ) ) {
				$metrics[ $status ] = $total;
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$duration_rows = $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT duration_ms FROM {$table} WHERE created_at >= %s AND duration_ms IS NOT NULL AND duration_ms > 0",
				$start
			)
		);

		$durations                     = array_map( 'intval', $duration_rows );
		$has_durations                 = ! empty( $durations );
		$metrics['median_duration_ms'] = $has_durations ? self::calculate_median( $durations ) : 0.0;
		$metrics['avg_duration_ms']    = $has_durations ? array_sum( $durations ) / count( $durations ) : 0.0;

		$total = (int) $metrics['sent'] + (int) $metrics['failed'];

		$metrics['total']          = $total;
		$metrics['success_rate']   = $total > 0 ? ( ( (float) $metrics['sent'] / (float) $total ) * 100 ) : null;
		$metrics['median_latency'] = $metrics['median_duration_ms'];
		$metrics['avg_latency']    = $metrics['avg_duration_ms'];

		set_transient( $cache_key, $metrics, HOUR_IN_SECONDS );

		return $metrics;
	}

	/**
	 * Retrieve a timeseries roll-up of sent/failed counts.
	 *
	 * @param int    $hours    Lookback window in hours.
	 * @param string $interval Interval grouping ("hour" or "day").
	 * @return array<string, mixed>
	 */
	public static function get_metrics_series( int $hours = 24, string $interval = 'hour' ): array {
		global $wpdb;

		$interval  = ( 'day' === strtolower( $interval ) ) ? 'day' : 'hour';
		$max_hours = ( 'day' === $interval ) ? 24 * 30 : 168; // cap hourly window to 7 days.
		$hours     = max( 1, min( $hours, $max_hours ) );

		$bucket_interval = ( 'day' === $interval ) ? DAY_IN_SECONDS : HOUR_IN_SECONDS;
		$start_gmt       = gmdate( 'Y-m-d H:i:s', time() - ( $hours * HOUR_IN_SECONDS ) );
		$bucket_format   = ( 'day' === $interval ) ? 'M j' : 'M j, H:i';

		$table = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT FLOOR(UNIX_TIMESTAMP(created_at) / %d) * %d AS bucket,
					SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END)   AS sent,
					SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
					COUNT(*)                                          AS total
				FROM {$table}
				WHERE created_at >= %s
				GROUP BY bucket
				ORDER BY bucket ASC",
				// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				$bucket_interval,
				$bucket_interval,
				$start_gmt
			),
			ARRAY_A
		);

		$series     = array();
		$timezone   = wp_timezone();
		$now        = time();
		$total_sum  = 0;
		$sent_sum   = 0;
		$failed_sum = 0;

		foreach ( (array) $rows as $row ) {
			$bucket_ts = isset( $row['bucket'] ) ? (int) $row['bucket'] : 0;

			if ( $bucket_ts <= 0 ) {
				continue;
			}

			switch ( $interval ) {
				case 'day':
					$bucket_ts = (int) $bucket_ts;
					break;
				default:
					$bucket_ts = (int) $bucket_ts;
			}

			$sent   = (int) ( $row['sent'] ?? 0 );
			$failed = (int) ( $row['failed'] ?? 0 );
			$total  = (int) ( $row['total'] ?? ( $sent + $failed ) );

			$total_sum  += $total;
			$sent_sum   += $sent;
			$failed_sum += $failed;

			$success_rate = $total > 0 ? ( (float) $sent / (float) $total ) * 100 : null;

			$series[] = array(
				'timestamp'    => $bucket_ts,
				'label'        => wp_date( $bucket_format, $bucket_ts, $timezone ),
				'sent'         => $sent,
				'failed'       => $failed,
				'total'        => $total,
				'success_rate' => $success_rate,
				'age_seconds'  => max( 0, $now - $bucket_ts ),
			);
		}

		return array(
			'interval' => $interval,
			'series'   => $series,
			'summary'  => array(
				'total'  => $total_sum,
				'sent'   => $sent_sum,
				'failed' => $failed_sum,
			),
		);
	}

	/**
	 * Produce an error/failure breakdown for recent activity.
	 *
	 * @param int $hours Lookback window in hours.
	 * @param int $limit Maximum groups to return.
	 * @return array<int, array<string, mixed>>
	 */
	public static function get_failure_breakdown( int $hours = 24, int $limit = 5 ): array {
		global $wpdb;

		$hours = max( 1, min( $hours, 168 ) );
		$limit = max( 1, min( $limit, 10 ) );

		$table     = self::table_name();
		$start_gmt = gmdate( 'Y-m-d H:i:s', time() - ( $hours * HOUR_IN_SECONDS ) );
		$fetch     = $limit * 40; // pull extra rows for grouping.

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT provider_code, error FROM {$table}
				WHERE status = 'failed' AND created_at >= %s
				ORDER BY id DESC
				LIMIT %d",
				$start_gmt,
				$fetch
			),
			ARRAY_A
		);

		$groups = array();
		foreach ( (array) $rows as $row ) {
			$error_raw = $row['error'] ?? '';
			$info      = self::normalize_error_info( $error_raw );
			$provider  = $info['provider_code'] ? $info['provider_code'] : (string) ( $row['provider_code'] ?? '' );
			$provider  = sanitize_text_field( $provider );
			if ( '' === $provider ) {
				$provider = __( 'Unknown', 'mail-doctor' );
			}

			$raw_message = $info['message'];
			$hint        = '';
			if ( preg_match( '/\sHint:\s(.+)$/', $raw_message, $matches ) ) {
				$hint        = trim( $matches[1] );
				$raw_message = trim( substr( $raw_message, 0, -strlen( $matches[0] ) ) );
			}

			$message = sanitize_text_field( self::shorten_text( $raw_message, 180 ) );
			$hint    = $hint ? sanitize_text_field( self::shorten_text( $hint, 160 ) ) : '';
			$key     = md5( strtolower( $provider . '|' . $message ) );

			if ( ! isset( $groups[ $key ] ) ) {
				$severity = 'error';
				if ( preg_match( '/^(4\d{2})/', $provider ) ) {
					$severity = 'warning';
				} elseif ( preg_match( '/^(2\d{2})/', $provider ) ) {
					$severity = 'info';
				} elseif ( preg_match( '/^(5\d{2})/', $provider ) ) {
					$severity = 'critical';
				}

				$groups[ $key ] = array(
					'provider' => $provider,
					'count'    => 0,
					'message'  => $message,
					'hint'     => $hint ? sanitize_text_field( $hint ) : '',
					'severity' => $severity,
				);
			}

			++$groups[ $key ]['count'];
		}

		usort(
			$groups,
			static function ( array $a, array $b ): int {
				return (int) $b['count'] <=> (int) $a['count'];
			}
		);

		$groups = array_slice( array_values( $groups ), 0, $limit );
		$total  = array_reduce(
			$groups,
			static function ( $carry, $item ) {
				return $carry + (int) ( $item['count'] ?? 0 );
			},
			0
		);

		if ( $total > 0 ) {
			foreach ( $groups as &$group ) {
				$group['share'] = round( ( (int) $group['count'] / $total ) * 100, 1 );
			}
			unset( $group );
		}

		return $groups;
	}

	/**
	 * Summarize provider performance for recent logs.
	 *
	 * @param int $hours Lookback window in hours.
	 * @param int $limit Maximum providers to surface.
	 * @return array<int, array<string, mixed>>
	 */
	public static function get_provider_performance( int $hours = 24, int $limit = 5 ): array {
		global $wpdb;

		$hours = max( 1, min( $hours, 168 ) );
		$limit = max( 1, min( $limit, 10 ) );

		$table     = self::table_name();
		$start_gmt = gmdate( 'Y-m-d H:i:s', time() - ( $hours * HOUR_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT id, status, duration_ms, payload
				FROM {$table}
				WHERE created_at >= %s
				ORDER BY id DESC
				LIMIT %d",
				// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				$start_gmt,
				$limit * 200
			),
			ARRAY_A
		);

		$providers = array();
		$timezone  = wp_timezone();

		foreach ( (array) $rows as $row ) {
			$payload = json_decode( (string) ( $row['payload'] ?? '' ), true );
			$meta    = is_array( $payload ) && isset( $payload['meta'] ) && is_array( $payload['meta'] ) ? $payload['meta'] : array();

			$transport_label = sanitize_text_field( $meta['transport_label'] ?? '' );
			$transport_key   = sanitize_key( $meta['transport'] ?? '' );
			$profile_label   = sanitize_text_field( $meta['smtp_profile_label'] ?? '' );

			$name_parts = array_filter( array( $transport_label ?: '', $profile_label ?: '' ) );
			if ( empty( $name_parts ) && $transport_key ) {
				$name_parts[] = strtoupper( str_replace( '_', ' ', $transport_key ) );
			}
			if ( empty( $name_parts ) ) {
				$name_parts[] = __( 'Default Transport', 'mail-doctor' );
			}

			$label = implode( ' — ', $name_parts );
			$key   = md5( strtolower( $label ) );

			if ( ! isset( $providers[ $key ] ) ) {
				$providers[ $key ] = array(
					'label'         => $label,
					'sent'          => 0,
					'failed'        => 0,
					'total'         => 0,
					'latencies'     => array(),
					'last_activity' => null,
				);
			}

			$status = sanitize_key( $row['status'] ?? '' );
			if ( 'failed' === $status ) {
				++$providers[ $key ]['failed'];
			} elseif ( 'sent' === $status ) {
				++$providers[ $key ]['sent'];
			}
			++$providers[ $key ]['total'];

			$duration = isset( $row['duration_ms'] ) ? (int) $row['duration_ms'] : 0;
			if ( $duration > 0 ) {
				$providers[ $key ]['latencies'][] = $duration;
			}

			if ( isset( $payload['created_at'] ) ) {
				$created_ts = self::gmt_to_timestamp( $payload['created_at'] );
				if ( $created_ts ) {
					$providers[ $key ]['last_activity'] = max( (int) ( $providers[ $key ]['last_activity'] ?? 0 ), $created_ts );
				}
			}
		}

		$results = array();
		foreach ( $providers as $provider ) {
			$total          = max( 1, (int) $provider['total'] );
			$sent           = (int) $provider['sent'];
			$failed         = (int) $provider['failed'];
			$median_latency = ! empty( $provider['latencies'] ) ? self::calculate_median( $provider['latencies'] ) : 0;
			$success_rate   = ( $sent / $total ) * 100;

			$results[] = array(
				'label'         => $provider['label'],
				'success_rate'  => round( $success_rate, 1 ),
				'sent'          => $sent,
				'failed'        => $failed,
				'latency'       => (int) round( $median_latency ),
				'last_activity' => $provider['last_activity'] ? wp_date( 'M j, H:i', $provider['last_activity'], $timezone ) : '',
			);
		}

		usort(
			$results,
			static function ( array $a, array $b ): int {
				// Sort by success rate desc, then sent volume desc.
				$rate_compare = (float) $b['success_rate'] <=> (float) $a['success_rate'];
				if ( 0 !== $rate_compare ) {
					return $rate_compare;
				}
				return (int) $b['sent'] <=> (int) $a['sent'];
			}
		);

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

	/**
	 * Provide queue health roll-up for the retry timeline.
	 *
	 * @param int $limit Timeline window to inspect.
	 * @return array<string, mixed>
	 */
	public static function summarize_retry_queue( int $limit = 50 ): array {
		$timeline = self::get_retry_timeline( $limit );
		$now      = time();

		if ( empty( $timeline ) ) {
			return array(
				'avg_attempts'              => 0,
				'oldest_queued_seconds'     => 0,
				'oldest_quarantine_seconds' => 0,
				'next_retry_seconds'        => 0,
				'queued_count'              => 0,
				'quarantine_count'          => 0,
			);
		}

		$attempts_total    = 0;
		$queued_count      = 0;
		$quarantine_count  = 0;
		$oldest_queued     = null;
		$oldest_quarantine = null;
		$next_retry        = null;

		foreach ( $timeline as $entry ) {
			$attempts_total += (int) ( $entry['attempts'] ?? 0 );

			if ( ! empty( $entry['quarantined'] ) ) {
				++$quarantine_count;
				$quarantine_at = isset( $entry['quarantine_at_ts'] ) ? (int) $entry['quarantine_at_ts'] : null;
				if ( $quarantine_at ) {
					$oldest_quarantine = null === $oldest_quarantine ? $quarantine_at : min( $oldest_quarantine, $quarantine_at );
				}
				continue;
			}

			$status = $entry['status'] ?? '';
			if ( 'queued' === $status ) {
				++$queued_count;
				$created = isset( $entry['created_at_ts'] ) ? (int) $entry['created_at_ts'] : null;
				if ( $created ) {
					$oldest_queued = null === $oldest_queued ? $created : min( $oldest_queued, $created );
				}
			}

			$next_attempt = isset( $entry['next_attempt_ts'] ) ? (int) $entry['next_attempt_ts'] : null;
			if ( $next_attempt && $next_attempt >= $now ) {
				$next_retry = null === $next_retry ? $next_attempt : min( $next_retry, $next_attempt );
			}
		}

		$total_examined = max( 1, count( $timeline ) );
		$avg_attempts   = $attempts_total / $total_examined;

		return array(
			'avg_attempts'              => round( $avg_attempts, 1 ),
			'oldest_queued_seconds'     => $oldest_queued ? max( 0, $now - $oldest_queued ) : 0,
			'oldest_quarantine_seconds' => $oldest_quarantine ? max( 0, $now - $oldest_quarantine ) : 0,
			'next_retry_seconds'        => $next_retry ? max( 0, $next_retry - $now ) : 0,
			'queued_count'              => $queued_count,
			'quarantine_count'          => $quarantine_count,
		);
	}

	/**
	 * Shorten long text for summaries.
	 *
	 * @param string $text Text to shorten.
	 * @param int    $limit Character limit.
	 * @return string
	 */
	protected static function shorten_text( string $text, int $limit ): string {
		$text = trim( preg_replace( '/\s+/', ' ', wp_strip_all_tags( $text ) ) );
		if ( strlen( $text ) <= $limit ) {
			return $text;
		}

		return substr( $text, 0, $limit - 1 ) . '…';
	}

	/**
	 * Retrieve retry timeline data.
	 *
	 * @param int $limit Number of records to include.
	 * @return array<int, array<string, mixed>>
	 */
	public static function get_retry_timeline( int $limit = 50 ): array {
		global $wpdb;

		$limit = max( 1, $limit );

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT id, created_at, subject, to_email, status, payload FROM {$table} WHERE status IN ('failed','queued') ORDER BY created_at ASC LIMIT %d",
				$limit
			),
			ARRAY_A
		);

		$timeline = array();

		foreach ( (array) $rows as $row ) {
			$payload = json_decode( $row['payload'] ?? '', true );

			if ( empty( $payload ) || empty( $payload['retry'] ) || ! is_array( $payload['retry'] ) ) {
				continue;
			}

			$retry   = $payload['retry'];
			$history = array();
			$entries = isset( $retry['history'] ) && is_array( $retry['history'] ) ? $retry['history'] : array();

			foreach ( $entries as $entry ) {
				if ( ! is_array( $entry ) ) {
					continue;
				}

				$entry['occurred_at_ts']  = self::gmt_to_timestamp( $entry['occurred_at_gmt'] ?? null );
				$entry['next_attempt_ts'] = self::gmt_to_timestamp( $entry['next_attempt_gmt'] ?? null );
				$history[]                = $entry;
			}

			$next_attempt_gmt = $retry['next_attempt_gmt'] ?? null;

			$timeline[] = array(
				'id'                => (int) $row['id'],
				'created_at_gmt'    => $row['created_at'],
				'created_at_ts'     => self::gmt_to_timestamp( $row['created_at'] ),
				'subject'           => $row['subject'],
				'to_email'          => $row['to_email'],
				'status'            => $row['status'],
				'attempts'          => (int) ( $retry['attempts'] ?? 0 ),
				'next_attempt_gmt'  => $next_attempt_gmt,
				'next_attempt_ts'   => self::gmt_to_timestamp( $next_attempt_gmt ),
				'history'           => $history,
				'quarantined'       => ! empty( $retry['quarantined'] ),
				'quarantine_reason' => $retry['quarantine_reason'] ?? '',
				'quarantine_at_gmt' => $retry['quarantine_at'] ?? null,
				'quarantine_at_ts'  => self::gmt_to_timestamp( $retry['quarantine_at'] ?? null ),
				'idempotency_key'   => isset( $payload['meta']['idempotency_key'] ) ? $payload['meta']['idempotency_key'] : null,
				'smtp_profile'      => isset( $payload['meta']['smtp_profile'] ) ? $payload['meta']['smtp_profile'] : null,
				'recipients_hash'   => isset( $payload['meta']['recipients_hash'] ) && is_array( $payload['meta']['recipients_hash'] ) ? $payload['meta']['recipients_hash'] : array(),
				'recipients_plain'  => isset( $payload['meta']['recipients_plain'] ) && is_array( $payload['meta']['recipients_plain'] ) ? $payload['meta']['recipients_plain'] : array(),
				'privacy_mode'      => ! empty( $payload['meta']['privacy_mode'] ),
			);
		}

		usort(
			$timeline,
			static function ( $a, $b ) {
				$a_ts = ! empty( $a['quarantined'] ) ? PHP_INT_MAX : ( $a['next_attempt_ts'] ?? PHP_INT_MAX - 1 );
				$b_ts = ! empty( $b['quarantined'] ) ? PHP_INT_MAX : ( $b['next_attempt_ts'] ?? PHP_INT_MAX - 1 );

				if ( $a_ts === $b_ts ) {
					return ( $a['created_at_ts'] ?? 0 ) <=> ( $b['created_at_ts'] ?? 0 );
				}

				return $a_ts <=> $b_ts;
			}
		);

		return $timeline;
	}
}
