<?php
/**
 * Email health reporting service.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Mail;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Service reads plugin-owned mail event tables for admin analytics.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are plugin-owned $wpdb->prefix values.

/**
 * Provides reusable email health aggregates.
 */
class EmailHealthService {
	/**
	 * Email error classifier.
	 *
	 * @var EmailErrorClassifier
	 */
	private $error_classifier;

	/**
	 * Constructor.
	 *
	 * @param EmailErrorClassifier|null $error_classifier Classifier.
	 */
	public function __construct( ?EmailErrorClassifier $error_classifier = null ) {
		$this->error_classifier = $error_classifier ? $error_classifier : new EmailErrorClassifier();
	}

	/**
	 * Summary counts.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,int>
	 */
	public function summary_counts( array $filters = array() ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_mail_events';
		$lead_table = $wpdb->prefix . 'subin_leads';
		$where      = $this->where_for_filters( $filters );
		$sql        = "SELECT
				COUNT(*) AS total,
				SUM(CASE WHEN e.status = 'sent' THEN 1 ELSE 0 END) AS sent,
				SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END) AS failed,
				SUM(CASE WHEN e.status = 'no_email_generated' THEN 1 ELSE 0 END) AS no_email_generated,
				SUM(CASE WHEN e.status IN ('unknown','pending') THEN 1 ELSE 0 END) AS unknown
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}";
		$row        = $wpdb->get_row( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A );

		return array_map( 'absint', is_array( $row ) ? $row : array() );
	}

	/**
	 * Summary counts grouped by Email Health source.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array<string,array<string,mixed>>
	 */
	public function source_summary( array $filters = array() ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'subin_mail_events';
		$lead_table  = $wpdb->prefix . 'subin_leads';
		$where       = $this->where_for_filters( $filters );
		$source_expr = $this->source_group_sql();
		$sql         = "SELECT {$source_expr} AS source_group,
				SUM(CASE WHEN e.status = 'sent' THEN 1 ELSE 0 END) AS sent,
				SUM(CASE WHEN e.status = 'failed' THEN 1 ELSE 0 END) AS failed,
				SUM(CASE WHEN e.status = 'no_email_generated' THEN 1 ELSE 0 END) AS no_email_generated,
				SUM(CASE WHEN e.status IN ('unknown','pending') THEN 1 ELSE 0 END) AS unknown
			FROM {$table} e
			LEFT JOIN {$lead_table} leads ON leads.id = e.lead_id
			{$where['sql']}
			GROUP BY source_group";
		$rows        = $wpdb->get_results( $this->prepare_sql( $sql, $where['params'] ), ARRAY_A );
		$summary     = array();

		foreach ( $this->source_labels() as $key => $label ) {
			$summary[ $key ] = array(
				'name'               => $label,
				'sent'               => 0,
				'failed'             => 0,
				'no_email_generated' => 0,
				'unknown'            => 0,
			);
		}

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$key = sanitize_key( $row['source_group'] ?? 'unknown' );
			if ( ! isset( $summary[ $key ] ) ) {
				$key = 'unknown';
			}

			$summary[ $key ]['sent']               = absint( $row['sent'] ?? 0 );
			$summary[ $key ]['failed']             = absint( $row['failed'] ?? 0 );
			$summary[ $key ]['no_email_generated'] = absint( $row['no_email_generated'] ?? 0 );
			$summary[ $key ]['unknown']            = absint( $row['unknown'] ?? 0 );
		}

		return $summary;
	}

	/**
	 * Build date/source filters.
	 *
	 * @param array<string,string> $filters Filters.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	public function where_for_filters( array $filters ) {
		$where  = array();
		$params = array();

		$source = sanitize_key( $filters['source'] ?? '' );
		if ( '' !== $source ) {
			$where[]  = $this->source_group_sql() . ' = %s';
			$params[] = $source;
		}

		$date_range = (string) ( $filters['date_range'] ?? '' );
		if ( in_array( $date_range, array( '7', '30' ), true ) ) {
			$where[]  = 'e.created_at >= %s';
			$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( '-' . absint( $date_range ) . ' days' ) );
		}

		return array(
			'sql'    => ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '',
			'params' => $params,
		);
	}

	/**
	 * Prepare SQL when needed.
	 *
	 * @param string           $sql SQL.
	 * @param array<int,mixed> $params Params.
	 * @return string
	 */
	public function prepare_sql( $sql, array $params ) {
		global $wpdb;

		return empty( $params ) ? $sql : $wpdb->prepare( $sql, $params );
	}

	/**
	 * Classify an error.
	 *
	 * @param string $error Error text.
	 * @return array<string,string>
	 */
	public function classify_error( $error ) {
		return $this->error_classifier->classify( $error );
	}

	/**
	 * Source grouping SQL.
	 *
	 * @return string
	 */
	private function source_group_sql() {
		return "CASE
			WHEN leads.provider = 'submission_inbox_form' OR e.source = 'submission_inbox_form' OR (e.source = 'submission_inbox_auto_reply' AND (leads.provider IS NULL OR leads.provider = '' OR leads.provider = 'submission_inbox_form')) THEN 'submission_inbox_forms'
			WHEN leads.provider = 'contact_form_7' OR e.source = 'contact_form_7' THEN 'contact_form_7'
			WHEN leads.provider = 'wpforms' OR e.source = 'wpforms' THEN 'wpforms'
			WHEN leads.provider = 'fluent_forms' OR e.source = 'fluent_forms' THEN 'fluent_forms'
			WHEN leads.provider = 'forminator' OR e.source = 'forminator' THEN 'forminator'
			WHEN leads.provider = 'ninja_forms' OR e.source = 'ninja_forms' THEN 'ninja_forms'
			WHEN e.source IN ('wp_mail','wordpress') THEN 'wordpress_core'
			WHEN e.source = 'plugin' THEN 'other_plugins'
			WHEN e.source IS NOT NULL AND e.source != '' AND e.source != 'unknown' THEN 'other_plugins'
			ELSE 'unknown'
		END";
	}

	/**
	 * Source labels used by Email Health.
	 *
	 * @return array<string,string>
	 */
	private function source_labels() {
		return array(
			'submission_inbox_forms' => __( 'InboxMend Forms', 'submission-inbox' ),
			'contact_form_7'         => __( 'Contact Form 7', 'submission-inbox' ),
			'wpforms'                => __( 'WPForms', 'submission-inbox' ),
			'fluent_forms'           => __( 'Fluent Forms', 'submission-inbox' ),
			'forminator'             => __( 'Forminator', 'submission-inbox' ),
			'ninja_forms'            => __( 'Ninja Forms', 'submission-inbox' ),
			'wordpress_core'         => __( 'WordPress Core', 'submission-inbox' ),
			'other_plugins'          => __( 'Other plugins', 'submission-inbox' ),
			'unknown'                => __( 'Unknown', 'submission-inbox' ),
		);
	}
}
