<?php
/**
 * Batch aggregates for the built-in forms overview.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Forms;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- The overview reads plugin-owned custom tables and deliberately batches all aggregates.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table names are plugin-owned $wpdb->prefix values; dynamic clauses use allowlists and placeholders.

/**
 * Provides a query-bounded, presentation-ready overview of built-in forms.
 */
class FormsOverviewRepository {
	const PROVIDER = 'submission_inbox_form';

	/**
	 * WordPress database adapter.
	 *
	 * @var \wpdb|object
	 */
	private $database;

	/**
	 * Plugin settings repository.
	 *
	 * @var \SUBIN_SettingsRepository|object
	 */
	private $settings;

	/**
	 * Constructor.
	 *
	 * The optional arguments keep the aggregation layer independently testable.
	 *
	 * @param object|null $database Database adapter.
	 * @param object|null $settings Settings repository.
	 */
	public function __construct( $database = null, $settings = null ) {
		global $wpdb;

		$this->database = $database ? $database : $wpdb;
		$this->settings = $settings ? $settings : new \SUBIN_SettingsRepository();
	}

	/**
	 * Load forms and all overview aggregates.
	 *
	 * With the default arguments every built-in form is returned. Setting
	 * per_page adds pagination without changing the fixed number of aggregate
	 * queries for that page.
	 *
	 * @param array<string,mixed> $args Query arguments.
	 * @return array<string,mixed>
	 */
	public function get_overview( array $args = array() ) {
		$query_args = $this->sanitize_query_args( $args );
		$forms      = $this->load_forms( $query_args );
		$total      = $query_args['per_page'] > 0 ? $this->count_forms( $query_args ) : count( $forms );
		$settings   = $this->plugin_settings();
		$items      = $this->hydrate( $forms, $settings, $query_args['period_days'] );
		$global     = $this->global_health( $settings );
		$summary    = $this->summarize( $items, $total );

		$summary['global_issue_count'] = count( $global['issues'] );

		return array(
			'forms'         => $items,
			'summary'       => $summary,
			'global_facts'  => $global['facts'],
			'global_issues' => $global['issues'],
			'pagination'    => array(
				'total'       => $total,
				'per_page'    => $query_args['per_page'],
				'offset'      => $query_args['offset'],
				'has_more'    => $query_args['per_page'] > 0 && ( $query_args['offset'] + count( $items ) ) < $total,
				'period_days' => $query_args['period_days'],
			),
		);
	}

	/**
	 * Batch-hydrate form rows that were loaded by another caller.
	 *
	 * This method performs three queries regardless of the number of forms:
	 * one for fields, one for leads, and one for auto-reply rules/templates.
	 *
	 * @param array<int,array<string,mixed>> $forms Form rows.
	 * @param array<string,mixed>|null       $plugin_settings Plugin settings, or null to load them.
	 * @param int                            $period_days Comparison period.
	 * @return array<int,array<string,mixed>>
	 */
	public function hydrate( array $forms, $plugin_settings = null, $period_days = 7 ) {
		$form_ids = $this->form_ids( $forms );
		if ( empty( $form_ids ) ) {
			return array();
		}

		$plugin_settings = is_array( $plugin_settings ) ? $plugin_settings : $this->plugin_settings();
		$period_days      = min( 365, max( 1, absint( $period_days ) ) );
		$fields           = $this->load_field_summaries( $form_ids );
		$leads            = $this->load_lead_summaries( $form_ids, $period_days );
		$auto_replies     = $this->load_auto_reply_summaries( $form_ids );
		$items            = array();

		foreach ( $forms as $form ) {
			$form_id = absint( $form['id'] ?? 0 );
			if ( $form_id <= 0 || ! in_array( $form_id, $form_ids, true ) ) {
				continue;
			}

			$field_summary = $fields[ $form_id ] ?? $this->empty_field_summary();
			$lead_summary  = $leads[ $form_id ] ?? $this->empty_lead_summary();
			$auto_reply    = $this->normalize_auto_reply(
				$auto_replies[ $form_id ] ?? null,
				$field_summary
			);
			$notification  = $this->notification_summary( $form, $plugin_settings );
			$publishing    = $this->publishing_summary( $form );
			$health        = $this->form_health(
				$form,
				$field_summary,
				$lead_summary,
				$auto_reply,
				$notification,
				$publishing,
				$period_days
			);
			$clean_form    = $form;
			unset( $clean_form['settings_json'] );

			$items[] = array_merge(
				$clean_form,
				array(
					'id'            => $form_id,
					'is_active'     => FormRepository::STATUS_ACTIVE === sanitize_key( $form['status'] ?? '' ),
					'fields'        => $field_summary,
					'leads'         => $lead_summary['leads'],
					'email'         => $lead_summary['email'],
					'auto_reply'    => $auto_reply,
					'notification'  => $notification,
					'publishing'    => $publishing,
					'health'        => $health,
				)
			);
		}

		return $items;
	}

	/**
	 * Build global facts and issues that must not be repeated per form.
	 *
	 * @param array<string,mixed>|null $plugin_settings Plugin settings, or null to load them.
	 * @return array{facts:array<string,mixed>,issues:array<int,array<string,mixed>>}
	 */
	public function global_health( $plugin_settings = null ) {
		$plugin_settings = is_array( $plugin_settings ) ? $plugin_settings : $this->plugin_settings();
		$smtp_enabled    = ! empty( $plugin_settings['enable_smtp'] );
		$smtp_host       = trim( (string) ( $plugin_settings['smtp_host'] ?? '' ) );
		$smtp_auth       = ! empty( $plugin_settings['smtp_auth'] );
		$smtp_username   = trim( (string) ( $plugin_settings['smtp_username'] ?? '' ) );
		$host_ready      = '' !== $smtp_host;
		$auth_ready      = ! $smtp_auth || '' !== $smtp_username;
		$configured      = $smtp_enabled && $host_ready && $auth_ready;
		$issues          = array();

		if ( ! $smtp_enabled ) {
			$issues[] = $this->issue(
				'smtp_not_configured',
				'warning',
				'global',
				__( 'SMTP is not configured', 'submission-inbox' ),
				__( 'WordPress is using its default mail method. Configure SMTP for observable, authenticated delivery.', 'submission-inbox' ),
				'configure_smtp',
				__( 'Configure SMTP', 'submission-inbox' )
			);
		} elseif ( ! $host_ready ) {
			$issues[] = $this->issue(
				'smtp_host_missing',
				'critical',
				'global',
				__( 'SMTP host is missing', 'submission-inbox' ),
				__( 'SMTP is enabled but no server host is configured.', 'submission-inbox' ),
				'configure_smtp',
				__( 'Complete SMTP setup', 'submission-inbox' )
			);
		} elseif ( ! $auth_ready ) {
			$issues[] = $this->issue(
				'smtp_username_missing',
				'critical',
				'global',
				__( 'SMTP authentication is incomplete', 'submission-inbox' ),
				__( 'Authentication is enabled but no SMTP username is configured.', 'submission-inbox' ),
				'configure_smtp',
				__( 'Complete SMTP setup', 'submission-inbox' )
			);
		}

		return array(
			'facts'  => array(
				'smtp' => array(
					'enabled'             => $smtp_enabled,
					'configured'          => $configured,
					'host_configured'     => $host_ready,
					'auth_required'       => $smtp_auth,
					'username_configured' => '' !== $smtp_username,
				),
			),
			'issues' => $issues,
		);
	}

	/**
	 * Sanitize list query arguments.
	 *
	 * @param array<string,mixed> $args Raw arguments.
	 * @return array<string,mixed>
	 */
	private function sanitize_query_args( array $args ) {
		$status = sanitize_key( $args['status'] ?? '' );
		if ( ! in_array( $status, array( FormRepository::STATUS_ACTIVE, FormRepository::STATUS_INACTIVE ), true ) ) {
			$status = '';
		}

		$orderby = sanitize_key( $args['orderby'] ?? 'updated_at' );
		if ( ! in_array( $orderby, array( 'id', 'title', 'slug', 'status', 'created_at', 'updated_at' ), true ) ) {
			$orderby = 'updated_at';
		}

		$per_page = array_key_exists( 'per_page', $args ) ? min( 200, max( 1, absint( $args['per_page'] ) ) ) : 0;

		return array(
			'search'      => sanitize_text_field( $args['search'] ?? '' ),
			'status'      => $status,
			'orderby'     => $orderby,
			'order'       => 'ASC' === strtoupper( (string) ( $args['order'] ?? 'DESC' ) ) ? 'ASC' : 'DESC',
			'per_page'    => $per_page,
			'offset'      => max( 0, absint( $args['offset'] ?? 0 ) ),
			'period_days' => min( 365, max( 1, absint( $args['period_days'] ?? 7 ) ) ),
		);
	}

	/**
	 * Load built-in forms.
	 *
	 * @param array<string,mixed> $args Sanitized query arguments.
	 * @return array<int,array<string,mixed>>
	 */
	private function load_forms( array $args ) {
		$table  = $this->database->prefix . 'subin_forms';
		$where  = $this->forms_where( $args );
		$sql    = "SELECT * FROM {$table}{$where['sql']} ORDER BY {$args['orderby']} {$args['order']}, id DESC";
		$params = $where['params'];

		if ( $args['per_page'] > 0 ) {
			$sql      .= ' LIMIT %d OFFSET %d';
			$params[] = $args['per_page'];
			$params[] = $args['offset'];
		}

		$rows = $this->database->get_results( $this->prepare( $sql, $params ), ARRAY_A );

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Count filtered forms.
	 *
	 * @param array<string,mixed> $args Sanitized query arguments.
	 * @return int
	 */
	private function count_forms( array $args ) {
		$table = $this->database->prefix . 'subin_forms';
		$where = $this->forms_where( $args );

		return absint(
			$this->database->get_var(
				$this->prepare( "SELECT COUNT(*) FROM {$table}{$where['sql']}", $where['params'] )
			)
		);
	}

	/**
	 * Build filters for forms queries.
	 *
	 * @param array<string,mixed> $args Query arguments.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function forms_where( array $args ) {
		$where  = array();
		$params = array();

		if ( '' !== $args['status'] ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( '' !== $args['search'] ) {
			$like     = '%' . $this->database->esc_like( $args['search'] ) . '%';
			$where[]  = '(title LIKE %s OR slug LIKE %s OR CAST(id AS CHAR) = %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = (string) absint( $args['search'] );
		}

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

	/**
	 * Load fields for all requested forms in one query and summarize in PHP.
	 *
	 * @param array<int,int> $form_ids Form ids.
	 * @return array<int,array<string,mixed>>
	 */
	private function load_field_summaries( array $form_ids ) {
		$table        = $this->database->prefix . 'subin_form_fields';
		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$sql          = "SELECT form_id, field_key, field_type, field_options, is_required
			FROM {$table}
			WHERE form_id IN ({$placeholders})
			ORDER BY form_id ASC, sort_order ASC, id ASC";
		$rows         = $this->database->get_results( $this->prepare( $sql, $form_ids ), ARRAY_A );
		$summaries    = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$form_id = absint( $row['form_id'] ?? 0 );
			if ( ! isset( $summaries[ $form_id ] ) ) {
				$summaries[ $form_id ] = $this->empty_field_summary();
			}

			++$summaries[ $form_id ]['total'];
			if ( ! empty( $row['is_required'] ) ) {
				++$summaries[ $form_id ]['required_count'];
			}

			$field_key  = sanitize_key( $row['field_key'] ?? '' );
			$field_type = $this->condition_field_type( $row['field_type'] ?? '' );
			if ( '' !== $field_key && '' !== $field_type ) {
				$summaries[ $form_id ]['field_types'][ $field_key ] = $field_type;
				$summaries[ $form_id ]['schema'][] = array(
					'key'     => $field_key,
					'type'    => $field_type,
					'options' => $row['field_options'] ?? array(),
				);
			}

			if ( FormFieldRepository::TYPE_EMAIL === sanitize_key( $row['field_type'] ?? '' ) ) {
				++$summaries[ $form_id ]['email_count'];
				$summaries[ $form_id ]['email_keys'][] = $field_key;
			}
		}

		foreach ( $summaries as &$summary ) {
			$summary['email_keys'] = array_values( array_filter( array_unique( $summary['email_keys'] ) ) );
			$summary['has_email']  = $summary['email_count'] > 0;
		}
		unset( $summary );

		return $summaries;
	}

	/**
	 * Load lead, workflow, and delivery aggregates in one query.
	 *
	 * @param array<int,int> $form_ids Form ids.
	 * @param int            $period_days Comparison period.
	 * @return array<int,array<string,mixed>>
	 */
	private function load_lead_summaries( array $form_ids, $period_days ) {
		$table          = $this->database->prefix . 'subin_leads';
		$placeholders   = implode( ', ', array_fill( 0, count( $form_ids ), '%s' ) );
		$now            = (int) current_time( 'timestamp' );
		$period_start   = gmdate( 'Y-m-d H:i:s', $now - ( DAY_IN_SECONDS * $period_days ) );
		$previous_start = gmdate( 'Y-m-d H:i:s', $now - ( 2 * DAY_IN_SECONDS * $period_days ) );
		$sql            = "SELECT form_id,
			COUNT(*) AS total,
			SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) AS new_count,
			SUM(CASE WHEN status = 'seen' THEN 1 ELSE 0 END) AS seen_count,
			SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived_count,
			SUM(CASE WHEN workflow_status = 'open' THEN 1 ELSE 0 END) AS workflow_open,
			SUM(CASE WHEN workflow_status = 'contacted' THEN 1 ELSE 0 END) AS workflow_contacted,
			SUM(CASE WHEN workflow_status = 'qualified' THEN 1 ELSE 0 END) AS workflow_qualified,
			SUM(CASE WHEN workflow_status = 'won' THEN 1 ELSE 0 END) AS workflow_won,
			SUM(CASE WHEN workflow_status = 'lost' THEN 1 ELSE 0 END) AS workflow_lost,
			SUM(CASE WHEN workflow_status NOT IN ('won', 'lost') THEN 1 ELSE 0 END) AS open_pipeline,
			SUM(CASE WHEN assigned_user_id IS NOT NULL AND assigned_user_id > 0 THEN 1 ELSE 0 END) AS assigned_count,
			SUM(CASE WHEN assigned_user_id IS NULL OR assigned_user_id = 0 THEN 1 ELSE 0 END) AS unassigned_count,
			SUM(CASE WHEN priority = 'high' THEN 1 ELSE 0 END) AS `high_priority`,
			SUM(CASE WHEN priority = 'urgent' THEN 1 ELSE 0 END) AS `urgent_priority`,
			SUM(CASE WHEN follow_up_at IS NOT NULL AND follow_up_at < %s AND workflow_status NOT IN ('won', 'lost') THEN 1 ELSE 0 END) AS overdue_follow_up,
			SUM(CASE WHEN submitted_at >= %s THEN 1 ELSE 0 END) AS period_count,
			SUM(CASE WHEN submitted_at >= %s AND submitted_at < %s THEN 1 ELSE 0 END) AS previous_period_count,
			MAX(submitted_at) AS last_submission,
			SUM(CASE WHEN email_status = 'sent' THEN 1 ELSE 0 END) AS email_sent,
			SUM(CASE WHEN email_status = 'failed' THEN 1 ELSE 0 END) AS email_failed,
			SUM(CASE WHEN email_status = 'no_email_generated' THEN 1 ELSE 0 END) AS email_not_generated,
			SUM(CASE WHEN email_status IS NULL OR email_status NOT IN ('sent', 'failed', 'no_email_generated') THEN 1 ELSE 0 END) AS email_unknown,
			SUM(CASE WHEN email_status = 'sent' AND submitted_at >= %s THEN 1 ELSE 0 END) AS email_sent_period,
			SUM(CASE WHEN email_status = 'failed' AND submitted_at >= %s THEN 1 ELSE 0 END) AS email_failed_period,
			SUM(CASE WHEN email_status = 'no_email_generated' AND submitted_at >= %s THEN 1 ELSE 0 END) AS email_not_generated_period,
			SUM(CASE WHEN submitted_at >= %s AND (email_status IS NULL OR email_status NOT IN ('sent', 'failed', 'no_email_generated')) THEN 1 ELSE 0 END) AS email_unknown_period
			FROM {$table}
			WHERE provider = %s AND form_id IN ({$placeholders})
			GROUP BY form_id";
		$params         = array(
			gmdate( 'Y-m-d H:i:s', $now ),
			$period_start,
			$previous_start,
			$period_start,
			$period_start,
			$period_start,
			$period_start,
			$period_start,
			self::PROVIDER,
		);
		$params         = array_merge( $params, array_map( 'strval', $form_ids ) );
		$rows           = $this->database->get_results( $this->prepare( $sql, $params ), ARRAY_A );
		$summaries      = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$form_id  = absint( $row['form_id'] ?? 0 );
			$summary  = $this->empty_lead_summary();
			$lead     = &$summary['leads'];
			$email    = &$summary['email'];
			$int_keys = array(
				'total', 'new_count', 'seen_count', 'archived_count', 'workflow_open',
				'workflow_contacted', 'workflow_qualified', 'workflow_won', 'workflow_lost',
				'open_pipeline', 'assigned_count', 'unassigned_count', 'high_priority',
				'urgent_priority', 'overdue_follow_up', 'period_count', 'previous_period_count',
			);

			foreach ( $int_keys as $key ) {
				$lead[ $key ] = absint( $row[ $key ] ?? 0 );
			}
			$lead['unread']           = $lead['new_count'];
			$lead['last_submission']  = ! empty( $row['last_submission'] ) ? (string) $row['last_submission'] : null;
			$lead['trend_delta']      = $lead['period_count'] - $lead['previous_period_count'];
			$lead['trend_percent']    = $this->trend_percent( $lead['period_count'], $lead['previous_period_count'] );
			$email['sent']            = absint( $row['email_sent'] ?? 0 );
			$email['failed']          = absint( $row['email_failed'] ?? 0 );
			$email['no_email_generated'] = absint( $row['email_not_generated'] ?? 0 );
			$email['unknown']         = absint( $row['email_unknown'] ?? 0 );
			$email['total']           = $email['sent'] + $email['failed'] + $email['no_email_generated'] + $email['unknown'];
			$email['failure_rate']    = $this->failure_rate( $email['sent'], $email['failed'] );
			$email['period_sent']     = absint( $row['email_sent_period'] ?? 0 );
			$email['period_failed']   = absint( $row['email_failed_period'] ?? 0 );
			$email['period_no_email_generated'] = absint( $row['email_not_generated_period'] ?? 0 );
			$email['period_unknown']  = absint( $row['email_unknown_period'] ?? 0 );
			$email['period_total']    = $email['period_sent'] + $email['period_failed'] + $email['period_no_email_generated'] + $email['period_unknown'];
			$email['period_failure_rate'] = $this->failure_rate( $email['period_sent'], $email['period_failed'] );
			$email['failed_period']   = $email['period_failed'];

			unset( $lead, $email );
			$summaries[ $form_id ] = $summary;
		}

		return $summaries;
	}

	/**
	 * Load every auto-reply rule and its optional template for each form in one query.
	 *
	 * @param array<int,int> $form_ids Form ids.
	 * @return array<int,array<string,mixed>>
	 */
	private function load_auto_reply_summaries( array $form_ids ) {
		$rules_table     = $this->database->prefix . 'subin_auto_reply_rules';
		$templates_table = $this->database->prefix . 'subin_auto_reply_templates';
		$placeholders    = implode( ', ', array_fill( 0, count( $form_ids ), '%s' ) );
		$sql             = "SELECT 'rule' AS row_kind,
			r.form_id, r.id AS rule_id, r.enabled, r.status, r.priority,
			r.visitor_email_field, r.visitor_email_field_label, r.template_id,
			r.condition_match, r.conditions_json, r.subject, r.body,
			r.updated_at AS rule_updated_at,
			NULL AS resolved_template_id, NULL AS template_name, NULL AS template_status,
			NULL AS template_subject, NULL AS template_body, NULL AS template_rendered_html,
			NULL AS template_updated_at
			FROM {$rules_table} r
			WHERE r.provider = %s AND r.form_id IN ({$placeholders})
			UNION ALL
			SELECT 'template' AS row_kind,
				'' AS form_id, 0 AS rule_id, 0 AS enabled, '' AS status, 0 AS priority,
				'' AS visitor_email_field, '' AS visitor_email_field_label, t.id AS template_id,
				'' AS condition_match, '' AS conditions_json, '' AS subject, '' AS body,
				NULL AS rule_updated_at,
				t.id AS resolved_template_id, t.name AS template_name, t.status AS template_status,
				t.subject AS template_subject, t.body AS template_body, t.rendered_html AS template_rendered_html,
				t.updated_at AS template_updated_at
			FROM {$templates_table} t
			INNER JOIN (
				SELECT DISTINCT r2.template_id
				FROM {$rules_table} r2
				WHERE r2.provider = %s
					AND r2.form_id IN ({$placeholders})
					AND r2.template_id > 0
			) selected_templates ON selected_templates.template_id = t.id";
		$filter          = array_merge( array( self::PROVIDER ), array_map( 'strval', $form_ids ) );
		$params          = array_merge( $filter, $filter );
		$rows            = $this->database->get_results( $this->prepare( $sql, $params ), ARRAY_A );
		$templates       = array();
		$rules           = array();
		$summaries       = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			if ( 'template' === ( $row['row_kind'] ?? '' ) ) {
				$template_id = absint( $row['resolved_template_id'] ?? 0 );
				if ( $template_id > 0 ) {
					$templates[ $template_id ] = array(
						'resolved_template_id'   => $template_id,
						'template_name'          => $row['template_name'] ?? '',
						'template_status'        => $row['template_status'] ?? '',
						'template_subject'       => $row['template_subject'] ?? '',
						'template_body'          => $row['template_body'] ?? '',
						'template_rendered_html' => $row['template_rendered_html'] ?? null,
						'template_updated_at'    => $row['template_updated_at'] ?? '',
					);
				}
				continue;
			}
			$rules[] = $row;
		}

		foreach ( $rules as $row ) {
			$form_id = absint( $row['form_id'] ?? 0 );
			if ( $form_id > 0 ) {
				$template_id = absint( $row['template_id'] ?? 0 );
				$template    = $templates[ $template_id ] ?? array(
					'resolved_template_id'   => 0,
					'template_name'          => '',
					'template_status'        => '',
					'template_subject'       => '',
					'template_body'          => '',
					'template_rendered_html' => null,
					'template_updated_at'    => '',
				);
				$row = array_merge( $row, $template );
				unset( $row['row_kind'] );
				if ( ! isset( $summaries[ $form_id ] ) ) {
					$summaries[ $form_id ] = array();
				}
				$summaries[ $form_id ][] = $row;
			}
		}

		foreach ( $summaries as &$form_rules ) {
			usort(
				$form_rules,
				static function ( $left, $right ) {
					$left_status  = (string) ( $left['status'] ?? '' );
					$right_status = (string) ( $right['status'] ?? '' );
					$left_rank    = 'published' === $left_status ? ( ! empty( $left['enabled'] ) ? 0 : 1 ) : 2;
					$right_rank   = 'published' === $right_status ? ( ! empty( $right['enabled'] ) ? 0 : 1 ) : 2;
					if ( $left_rank !== $right_rank ) {
						return $left_rank <=> $right_rank;
					}
					$priority = absint( $left['priority'] ?? 0 ) <=> absint( $right['priority'] ?? 0 );
					return 0 !== $priority ? $priority : absint( $left['rule_id'] ?? 0 ) <=> absint( $right['rule_id'] ?? 0 );
				}
			);
		}
		unset( $form_rules );

		return $summaries;
	}

	/**
	 * Normalize auto-reply readiness.
	 *
	 * @param array<int,array<string,mixed>>|null $rows Rule/template rows in delivery order.
	 * @param array<string,mixed>      $fields Field summary.
	 * @return array<string,mixed>
	 */
	private function normalize_auto_reply( $rows, array $fields ) {
		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return array(
				'configured'       => false,
				'enabled'          => false,
				'ready'            => false,
				'state'            => 'not_configured',
				'total_count'      => 0,
				'active_count'     => 0,
				'conditional_count'=> 0,
				'draft_count'      => 0,
				'attention_count'  => 0,
				'rule_id'          => 0,
				'template_id'      => 0,
				'template_exists'  => false,
				'content_ready'    => false,
				'conditions_valid' => true,
				'active_email_fields_valid' => true,
				'active_content_ready'       => true,
				'active_conditions_valid'    => true,
				'template_name'    => '',
				'template_status'  => '',
				'email_field_key'  => '',
				'email_field_valid'=> false,
			);
		}

		$primary             = reset( $rows );
		$total_count         = 0;
		$active_count        = 0;
		$conditional_count   = 0;
		$draft_count         = 0;
		$attention_count     = 0;
		$invalid_email_count = 0;
		$invalid_content_count = 0;
		$invalid_conditions_count = 0;
		$readiness           = new \SUBIN\AutoReplies\AutoReplyRuleReadiness();

		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			++$total_count;
			$status = sanitize_key( $row['status'] ?? 'published' );
			if ( \SUBIN\AutoReplies\AutoReplyRuleRepository::STATUS_DRAFT === $status ) {
				++$draft_count;
			}

			if ( $this->stored_auto_reply_condition_count( $row ) > 0 ) {
				++$conditional_count;
			}

			$is_active = \SUBIN\AutoReplies\AutoReplyRuleRepository::STATUS_PUBLISHED === $status && ! empty( $row['enabled'] );
			if ( ! $is_active ) {
				continue;
			}
			++$active_count;
			$template = absint( $row['resolved_template_id'] ?? 0 ) > 0
				? array(
					'subject'       => (string) ( $row['template_subject'] ?? '' ),
					'body'          => (string) ( $row['template_body'] ?? '' ),
					'rendered_html' => $row['template_rendered_html'] ?? null,
				)
				: null;
			$analysis = $readiness->analyze( $row, (array) ( $fields['schema'] ?? array() ), true, $template );

			if ( empty( $analysis['email_field_valid'] ) ) {
				++$invalid_email_count;
			}
			if ( empty( $analysis['content_ready'] ) ) {
				++$invalid_content_count;
			}
			if ( empty( $analysis['conditions_valid'] ) ) {
				++$invalid_conditions_count;
			}
			if ( empty( $analysis['ready'] ) ) {
				++$attention_count;
			}
		}

		$primary             = is_array( $primary ) ? $primary : array();
		$primary_template    = absint( $primary['resolved_template_id'] ?? 0 ) > 0
			? array(
				'subject'       => (string) ( $primary['template_subject'] ?? '' ),
				'body'          => (string) ( $primary['template_body'] ?? '' ),
				'rendered_html' => $primary['template_rendered_html'] ?? null,
			)
			: null;
		$primary_analysis    = $readiness->analyze( $primary, (array) ( $fields['schema'] ?? array() ), true, $primary_template );
		$enabled             = $active_count > 0;
		$template_id         = absint( $primary['template_id'] ?? 0 );
		$template_exists     = absint( $primary['resolved_template_id'] ?? 0 ) > 0;
		$email_field_key     = sanitize_key( $primary['visitor_email_field'] ?? '' );
		$email_field_valid   = ! empty( $primary_analysis['email_field_valid'] );
		$content_ready       = ! empty( $primary_analysis['content_ready'] );
		$conditions_valid    = ! empty( $primary_analysis['conditions_valid'] );
		$ready               = $enabled && 0 === $attention_count;
		$state               = 'disabled';
		if ( $attention_count > 0 ) {
			$state = 'needs_attention';
		} elseif ( $active_count > 0 ) {
			$state = 'active';
		} elseif ( $draft_count > 0 ) {
			$state = 'draft';
		}

		return array(
			'configured'        => true,
			'enabled'           => $enabled,
			'ready'             => $ready,
			'state'             => $state,
			'total_count'       => $total_count,
			'active_count'      => $active_count,
			'conditional_count' => $conditional_count,
			'draft_count'       => $draft_count,
			'attention_count'   => $attention_count,
			'rule_id'           => absint( $primary['rule_id'] ?? 0 ),
			'template_id'       => $template_id,
			'template_exists'   => $template_exists,
			'content_ready'     => $content_ready,
			'conditions_valid'  => $conditions_valid,
			'active_email_fields_valid' => 0 === $invalid_email_count,
			'active_content_ready'       => 0 === $invalid_content_count,
			'active_conditions_valid'    => 0 === $invalid_conditions_count,
			'template_name'     => sanitize_text_field( $primary['template_name'] ?? '' ),
			'template_status'   => sanitize_key( $primary['template_status'] ?? '' ),
			'email_field_key'   => $email_field_key,
			'email_field_valid' => $email_field_valid,
			'updated_at'        => sanitize_text_field( $primary['rule_updated_at'] ?? '' ),
		);
	}

	/** Count conditions in the raw overview projection without rendering inactive rules. */
	private function stored_auto_reply_condition_count( array $row ) {
		$document = json_decode( (string) ( $row['conditions_json'] ?? '' ), true );
		if ( ! is_array( $document ) ) {
			return 0;
		}
		$conditions = array_key_exists( 'conditions', $document ) ? $document['conditions'] : $document;

		return is_array( $conditions ) ? count( $conditions ) : 0;
	}

	/**
	 * Resolve effective notification settings.
	 *
	 * @param array<string,mixed> $form Form.
	 * @param array<string,mixed> $plugin_settings Plugin settings.
	 * @return array<string,mixed>
	 */
	private function notification_summary( array $form, array $plugin_settings ) {
		$enabled    = ! empty( $form['notification_enabled'] );
		$form_email = sanitize_email( $form['notification_email'] ?? '' );
		$site_email = sanitize_email( $plugin_settings['notification_recipient_email'] ?? '' );
		$recipient  = '' !== $form_email ? $form_email : $site_email;

		return array(
			'enabled'          => $enabled,
			'ready'            => $enabled && is_email( $recipient ),
			'recipient'        => $recipient,
			'recipient_source' => '' !== $form_email ? 'form' : ( '' !== $site_email ? 'global' : 'missing' ),
		);
	}

	/**
	 * Derive lightweight publishing state without returning project JSON.
	 *
	 * @param array<string,mixed> $form Form.
	 * @return array<string,mixed>
	 */
	private function publishing_summary( array $form ) {
		$settings      = $this->decode_settings( $form['settings_json'] ?? '' );
		$design_studio = is_array( $settings['design_studio'] ?? null ) ? $settings['design_studio'] : array();
		$published     = is_array( $design_studio['published'] ?? null ) ? $design_studio['published'] : array();
		$draft         = is_array( $design_studio['draft'] ?? null ) ? $design_studio['draft'] : array();
		$legacy        = is_array( $design_studio['grapesjs_project'] ?? null ) ? $design_studio['grapesjs_project'] : array();
		$has_draft     = is_array( $draft['project'] ?? null ) || array_key_exists( 'revision', $draft );
		$published_project = is_array( $published['project'] ?? null ) ? $published['project'] : $legacy;
		$draft_project     = is_array( $draft['project'] ?? null ) ? $draft['project'] : $published_project;
		$published_hash    = sanitize_text_field( $published['hash'] ?? '' );
		$draft_hash        = sanitize_text_field( $draft['hash'] ?? '' );

		if ( '' === $published_hash ) {
			$published_hash = $this->project_hash( $published_project );
		}
		if ( '' === $draft_hash ) {
			$draft_hash = $this->project_hash( $draft_project );
		}

		return array(
			'has_draft'          => $has_draft,
			'is_dirty'           => $has_draft && $draft_hash !== $published_hash,
			'draft_revision'     => $has_draft ? max( 0, (int) ( $draft['revision'] ?? 0 ) ) : 0,
			'published_revision' => array_key_exists( 'revision', $published ) ? max( 0, (int) $published['revision'] ) : max( 0, (int) ( $design_studio['project_revision'] ?? 0 ) ),
			'draft_updated_at'   => sanitize_text_field( $draft['updated_at'] ?? '' ),
			'published_at'       => sanitize_text_field( $published['published_at'] ?? '' ),
		);
	}

	/**
	 * Build actionable, form-scoped health facts.
	 *
	 * Global transport issues are intentionally excluded here.
	 *
	 * @param array<string,mixed> $form Form.
	 * @param array<string,mixed> $fields Fields summary.
	 * @param array<string,mixed> $leads Lead/email summary.
	 * @param array<string,mixed> $auto_reply Auto-reply summary.
	 * @param array<string,mixed> $notification Notification summary.
	 * @param array<string,mixed> $publishing Publishing summary.
	 * @param int                 $period_days Reporting period.
	 * @return array<string,mixed>
	 */
	private function form_health( array $form, array $fields, array $leads, array $auto_reply, array $notification, array $publishing, $period_days ) {
		$issues = array();

		if ( 0 === $fields['total'] ) {
			$issues[] = $this->issue(
				'form_has_no_fields',
				'critical',
				'form',
				__( 'Form has no fields', 'submission-inbox' ),
				__( 'Add at least one field before collecting submissions.', 'submission-inbox' ),
				'edit_fields',
				__( 'Add fields', 'submission-inbox' )
			);
		}

		if ( empty( $form['save_leads'] ) ) {
			$issues[] = $this->issue(
				'lead_storage_disabled',
				'warning',
				'form',
				__( 'Lead storage is disabled', 'submission-inbox' ),
				__( 'Submissions from this form are not protected in the Inbox.', 'submission-inbox' ),
				'edit_settings',
				__( 'Enable lead storage', 'submission-inbox' )
			);
		}

		if ( ! empty( $form['notification_enabled'] ) && empty( $notification['ready'] ) ) {
			$issues[] = $this->issue(
				'notification_recipient_missing',
				'critical',
				'form',
				__( 'Notification recipient is missing', 'submission-inbox' ),
				__( 'Admin notifications are enabled but have no valid recipient.', 'submission-inbox' ),
				'edit_notification',
				__( 'Add recipient', 'submission-inbox' )
			);
		}

		if ( empty( $fields['has_email'] ) ) {
			$issues[] = $this->issue(
				'email_field_missing',
				! empty( $auto_reply['enabled'] ) ? 'critical' : 'warning',
				'form',
				__( 'Email field is missing', 'submission-inbox' ),
				__( 'Add an email field to support Reply-To and visitor auto-replies.', 'submission-inbox' ),
				'edit_fields',
				__( 'Add email field', 'submission-inbox' )
			);
		}

		if ( ! empty( $auto_reply['enabled'] ) && empty( $auto_reply['active_email_fields_valid'] ) && ! empty( $fields['has_email'] ) ) {
			$issues[] = $this->issue(
				'auto_reply_email_field_invalid',
				'critical',
				'form',
				__( 'Auto-reply email field is invalid', 'submission-inbox' ),
				__( 'The auto-reply rule references a missing or non-email field.', 'submission-inbox' ),
				'manage_auto_reply',
				__( 'Repair rule', 'submission-inbox' )
			);
		}

		if ( ! empty( $auto_reply['enabled'] ) && empty( $auto_reply['active_content_ready'] ) ) {
			$issues[] = $this->issue(
				'auto_reply_content_invalid',
				'critical',
				'form',
				__( 'Auto-reply content is not delivery-ready', 'submission-inbox' ),
				__( 'Add usable rule or template content within the supported email size limits.', 'submission-inbox' ),
				'manage_auto_reply',
				__( 'Add content', 'submission-inbox' )
			);
		}

		if ( ! empty( $auto_reply['enabled'] ) && empty( $auto_reply['active_conditions_valid'] ) ) {
			$issues[] = $this->issue(
				'auto_reply_conditions_invalid',
				'critical',
				'form',
				__( 'Auto-reply conditions are invalid', 'submission-inbox' ),
				__( 'The affected rule will fail closed until its conditions are repaired.', 'submission-inbox' ),
				'manage_auto_reply',
				__( 'Repair conditions', 'submission-inbox' )
			);
		}

		if ( ! empty( $publishing['is_dirty'] ) ) {
			$issues[] = $this->issue(
				'unpublished_changes',
				'warning',
				'form',
				__( 'Form has unpublished changes', 'submission-inbox' ),
				__( 'The saved draft differs from the version visitors currently see.', 'submission-inbox' ),
				'review_draft',
				__( 'Review and publish', 'submission-inbox' )
			);
		}

		if ( $leads['email']['failed_period'] > 0 ) {
			$issue = $this->issue(
				'recent_email_failures',
				'critical',
				'form',
				__( 'Recent email delivery failures', 'submission-inbox' ),
				sprintf(
					/* translators: 1: Number of failed emails, 2: Number of days. */
					__( '%1$d submissions had a failed email status in the last %2$d days.', 'submission-inbox' ),
					$leads['email']['failed_period'],
					$period_days
				),
				'view_email_log',
				__( 'View failed emails', 'submission-inbox' )
			);
			$issue['data'] = array( 'count' => $leads['email']['failed_period'], 'period_days' => $period_days );
			$issues[]      = $issue;
		}

		$critical_count = 0;
		$warning_count  = 0;
		foreach ( $issues as $issue ) {
			if ( 'critical' === $issue['severity'] ) {
				++$critical_count;
			} elseif ( 'warning' === $issue['severity'] ) {
				++$warning_count;
			}
		}

		return array(
			'status'         => $critical_count > 0 ? 'critical' : ( $warning_count > 0 ? 'attention' : 'healthy' ),
			'issue_count'    => $critical_count + $warning_count,
			'critical_count' => $critical_count,
			'warning_count'  => $warning_count,
			'issues'         => $issues,
		);
	}

	/**
	 * Summarize visible forms.
	 *
	 * @param array<int,array<string,mixed>> $items Overview items.
	 * @param int                            $total Total filtered forms.
	 * @return array<string,int|float|null>
	 */
	private function summarize( array $items, $total ) {
		$summary = array(
			'total_forms'        => absint( $total ),
			'visible_forms'      => count( $items ),
			'active_forms'       => 0,
			'inactive_forms'     => 0,
			'needs_attention'    => 0,
			'critical_forms'     => 0,
			'dirty_drafts'       => 0,
			'lead_total'         => 0,
			'unread_total'       => 0,
			'period_lead_total'  => 0,
			'failed_email_total' => 0,
			'failed_email_period'=> 0,
			'period_email_total' => 0,
			'period_email_sent'  => 0,
			'period_email_failed'=> 0,
			'period_email_failure_rate' => null,
		);

		foreach ( $items as $item ) {
			$key = ! empty( $item['is_active'] ) ? 'active_forms' : 'inactive_forms';
			++$summary[ $key ];
			if ( 'healthy' !== ( $item['health']['status'] ?? 'healthy' ) ) {
				++$summary['needs_attention'];
			}
			if ( 'critical' === ( $item['health']['status'] ?? '' ) ) {
				++$summary['critical_forms'];
			}
			if ( ! empty( $item['publishing']['is_dirty'] ) ) {
				++$summary['dirty_drafts'];
			}
			$summary['lead_total']          += absint( $item['leads']['total'] ?? 0 );
			$summary['unread_total']        += absint( $item['leads']['unread'] ?? 0 );
			$summary['period_lead_total']   += absint( $item['leads']['period_count'] ?? 0 );
			$summary['failed_email_total']  += absint( $item['email']['failed'] ?? 0 );
			$summary['failed_email_period'] += absint( $item['email']['failed_period'] ?? 0 );
			$summary['period_email_total']  += absint( $item['email']['period_total'] ?? 0 );
			$summary['period_email_sent']   += absint( $item['email']['period_sent'] ?? 0 );
			$summary['period_email_failed'] += absint( $item['email']['period_failed'] ?? 0 );
		}
		$summary['period_email_failure_rate'] = $this->failure_rate( $summary['period_email_sent'], $summary['period_email_failed'] );

		return $summary;
	}

	/**
	 * Extract unique valid form ids.
	 *
	 * @param array<int,array<string,mixed>> $forms Forms.
	 * @return array<int,int>
	 */
	private function form_ids( array $forms ) {
		$ids = array();
		foreach ( $forms as $form ) {
			$form_id = absint( $form['id'] ?? 0 );
			if ( $form_id > 0 ) {
				$ids[ $form_id ] = $form_id;
			}
		}

		return array_values( $ids );
	}

	/**
	 * Empty field summary.
	 *
	 * @return array<string,mixed>
	 */
	private function empty_field_summary() {
		return array(
			'total'          => 0,
			'required_count' => 0,
			'email_count'    => 0,
			'has_email'      => false,
			'email_keys'     => array(),
			'field_types'    => array(),
			'schema'         => array(),
		);
	}

	/** Normalize a built-in field type to the condition-engine vocabulary. */
	private function condition_field_type( $type ) {
		$type = sanitize_key( (string) $type );
		if ( FormFieldRepository::TYPE_PHONE === $type ) {
			return 'tel';
		}
		$supported = array( 'text', 'email', 'date', 'textarea', 'select', 'radio', 'checkbox', 'consent' );

		return in_array( $type, $supported, true ) ? $type : '';
	}

	/**
	 * Empty lead and delivery summary.
	 *
	 * @return array<string,array<string,mixed>>
	 */
	private function empty_lead_summary() {
		return array(
			'leads' => array(
				'total'                 => 0,
				'new_count'             => 0,
				'unread'                => 0,
				'seen_count'            => 0,
				'archived_count'        => 0,
				'workflow_open'         => 0,
				'workflow_contacted'    => 0,
				'workflow_qualified'    => 0,
				'workflow_won'          => 0,
				'workflow_lost'         => 0,
				'open_pipeline'         => 0,
				'assigned_count'        => 0,
				'unassigned_count'      => 0,
				'high_priority'         => 0,
				'urgent_priority'       => 0,
				'overdue_follow_up'     => 0,
				'period_count'          => 0,
				'previous_period_count' => 0,
				'trend_delta'           => 0,
				'trend_percent'         => null,
				'last_submission'       => null,
			),
			'email' => array(
				'total'              => 0,
				'sent'               => 0,
				'failed'             => 0,
				'no_email_generated' => 0,
				'unknown'            => 0,
				'failed_period'      => 0,
				'failure_rate'       => null,
				'period_total'       => 0,
				'period_sent'        => 0,
				'period_failed'      => 0,
				'period_no_email_generated' => 0,
				'period_unknown'     => 0,
				'period_failure_rate'=> null,
			),
		);
	}

	/**
	 * Build a structured actionable issue.
	 *
	 * @param string $code Issue code.
	 * @param string $severity Severity.
	 * @param string $scope Scope.
	 * @param string $title Title.
	 * @param string $message Message.
	 * @param string $action_key Action key.
	 * @param string $action_label Action label.
	 * @return array<string,mixed>
	 */
	private function issue( $code, $severity, $scope, $title, $message, $action_key, $action_label ) {
		return array(
			'code'     => sanitize_key( $code ),
			'severity' => in_array( $severity, array( 'critical', 'warning', 'info' ), true ) ? $severity : 'info',
			'scope'    => 'global' === $scope ? 'global' : 'form',
			'title'    => $title,
			'message'  => $message,
			'action'   => array(
				'key'   => sanitize_key( $action_key ),
				'label' => $action_label,
			),
		);
	}

	/**
	 * Decode stored settings safely.
	 *
	 * @param mixed $settings Settings JSON or array.
	 * @return array<string,mixed>
	 */
	private function decode_settings( $settings ) {
		if ( is_array( $settings ) ) {
			return $settings;
		}

		$decoded = json_decode( (string) $settings, true );

		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Hash project JSON like FormRepository.
	 *
	 * @param array<string,mixed> $project Project.
	 * @return string
	 */
	private function project_hash( array $project ) {
		$json = wp_json_encode( $project );

		return hash( 'sha256', is_string( $json ) ? $json : '' );
	}

	/**
	 * Calculate percent trend, retaining null when there is no baseline.
	 *
	 * @param int $current Current value.
	 * @param int $previous Previous value.
	 * @return float|null
	 */
	private function trend_percent( $current, $previous ) {
		$current  = absint( $current );
		$previous = absint( $previous );
		if ( 0 === $previous ) {
			return $current > 0 ? 100.0 : null;
		}

		return round( ( ( $current - $previous ) / $previous ) * 100, 1 );
	}

	/**
	 * Calculate failed share among attempted deliveries.
	 *
	 * @param int $sent Sent count.
	 * @param int $failed Failed count.
	 * @return float|null
	 */
	private function failure_rate( $sent, $failed ) {
		$sent     = absint( $sent );
		$failed   = absint( $failed );
		$attempts = $sent + $failed;

		return $attempts > 0 ? round( ( $failed / $attempts ) * 100, 1 ) : null;
	}

	/**
	 * Read plugin settings safely.
	 *
	 * @return array<string,mixed>
	 */
	private function plugin_settings() {
		if ( is_object( $this->settings ) && is_callable( array( $this->settings, 'all' ) ) ) {
			$settings = $this->settings->all();
			return is_array( $settings ) ? $settings : array();
		}

		return array();
	}

	/**
	 * Prepare dynamic SQL only when parameters are present.
	 *
	 * @param string           $sql SQL.
	 * @param array<int,mixed> $params Parameters.
	 * @return string
	 */
	private function prepare( $sql, array $params ) {
		return empty( $params ) ? $sql : $this->database->prepare( $sql, $params );
	}
}
