<?php
/**
 * SQL for the admin Reports screen (keeps queries out of the view template).
 *
 * @package CartCue\Admin
 */

namespace CartCue\Admin;

use CartCue\DB;

defined( 'ABSPATH' ) || exit;

final class ReportsRepository {

	/**
	 * @return array{
	 *   totals: array<string, int|string>,
	 *   month_success: int,
	 *   week_success: int,
	 *   top_workflows: array<int, array<string, mixed>>,
	 *   recent_errors: array<int, array<string, mixed>>,
	 *   daily_rows: array<int, array<string, mixed>>
	 * }
	 */
	public static function fetch(): array {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$totals = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) AS total,
					SUM(status = 'success') AS success_count,
					SUM(status = 'skipped') AS skipped_count,
					SUM(status = 'error')   AS error_count
				 FROM %i",
				DB::logs()
			),
			ARRAY_A
		) ?: [ 'total' => 0, 'success_count' => 0, 'skipped_count' => 0, 'error_count' => 0 ];

		$month_success = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE status = 'success' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)",
				DB::logs()
			)
		);

		$week_success = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE status = 'success' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)",
				DB::logs()
			)
		);

		$top_workflows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT w.id, w.name, w.trigger_slug, w.run_count, w.status
				 FROM %i w
				 ORDER BY w.run_count DESC
				 LIMIT 10',
				DB::workflows()
			),
			ARRAY_A
		) ?: [];

		$recent_errors = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT l.workflow_name, l.trigger_slug, l.customer_email, l.message, l.created_at
				 FROM %i l
				 WHERE l.status = 'error'
				 ORDER BY l.created_at DESC
				 LIMIT 10",
				DB::logs()
			),
			ARRAY_A
		) ?: [];

		$daily_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(created_at) AS day, COUNT(*) AS runs,
					SUM(status = 'success') AS success,
					SUM(status = 'error')   AS errors
				 FROM %i
				 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
				 GROUP BY DATE(created_at)
				 ORDER BY day ASC",
				DB::logs()
			),
			ARRAY_A
		) ?: [];
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return [
			'totals'        => $totals,
			'month_success' => $month_success,
			'week_success'  => $week_success,
			'top_workflows' => $top_workflows,
			'recent_errors' => $recent_errors,
			'daily_rows'    => $daily_rows,
		];
	}

	/**
	 * Per-play 30-day metrics for the reports table.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	/**
	 * Latest log entry per play for list cards.
	 *
	 * @return array<int, array{ last_run_at: string, last_status: string }>
	 */
	public static function fetch_play_last_activity(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT l.workflow_id,
				        MAX(l.created_at) AS last_run_at,
				        SUBSTRING_INDEX(GROUP_CONCAT(l.status ORDER BY l.created_at DESC SEPARATOR \',\'), \',\', 1) AS last_status,
				        SUBSTRING_INDEX(GROUP_CONCAT(l.customer_email ORDER BY l.created_at DESC SEPARATOR \',\'), \',\', 1) AS last_customer_email
				 FROM %i l
				 GROUP BY l.workflow_id',
				DB::logs()
			),
			ARRAY_A
		) ?: [];

		$map = [];
		foreach ( $rows as $row ) {
			$map[ (int) $row['workflow_id'] ] = [
				'last_run_at'          => (string) ( $row['last_run_at'] ?? '' ),
				'last_status'          => (string) ( $row['last_status'] ?? '' ),
				'last_customer_email'  => (string) ( $row['last_customer_email'] ?? '' ),
			];
		}
		return $map;
	}

	public static function fetch_play_metrics(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT w.id, w.name,
				        (SELECT COUNT(*) FROM %i l WHERE l.workflow_id = w.id AND l.status = %s AND l.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS runs,
				        (SELECT COUNT(*) FROM %i et WHERE et.workflow_id = w.id AND et.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS sent,
				        (SELECT SUM( COALESCE( et2.opens_count, 0 ) ) FROM %i et2 WHERE et2.workflow_id = w.id AND et2.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS opens,
				        (SELECT COUNT(*) FROM %i c WHERE c.workflow_id = w.id AND c.converted_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS conversions,
				        (SELECT SUM(c2.order_total) FROM %i c2 WHERE c2.workflow_id = w.id AND c2.converted_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS revenue
				 FROM %i w ORDER BY runs DESC',
				DB::logs(),
				'success',
				DB::email_tracking(),
				DB::email_tracking(),
				DB::conversions(),
				DB::conversions(),
				DB::workflows()
			),
			ARRAY_A
		) ?: [];
	}

	/**
	 * Stream a CSV export of recent logs and exit.
	 */
	public static function export_logs_csv(): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT w.name AS play, l.customer_email, l.status, l.message, l.created_at
				 FROM %i l
				 LEFT JOIN %i w ON w.id = l.workflow_id
				 ORDER BY l.created_at DESC
				 LIMIT 5000',
				DB::logs(),
				DB::workflows()
			),
			ARRAY_A
		) ?: [];

		header( 'Content-Type: text/csv' );
		header( 'Content-Disposition: attachment; filename="cartcue-logs-' . gmdate( 'Y-m-d' ) . '.csv"' );

		$out = fopen( 'php://output', 'w' );
		fputcsv( $out, [ 'Play', 'Customer email', 'Status', 'Message', 'Date' ] );
		foreach ( $rows as $r ) {
			fputcsv( $out, array_values( $r ) );
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- php://output stream.
		fclose( $out );
		exit;
	}
}
