<?php

namespace WPPilot;

defined( 'ABSPATH' ) || exit;

/**
 * Elementor Pro's own forms on this site: what each does with an enquiry, and
 * what Elementor → Submissions has kept of it. Read-only.
 *
 * Live, alaskaholiday.co, 2026-10-12: a booking form emailed its enquiries and
 * kept none, and the owner was told they were stored in a screen that does
 * not exist. When an owner asks about leads, the answer must come from the
 * form's saved settings and from the store itself:
 *
 *   settings    — every Elementor document holding a `form` widget: the
 *                 form's name, its after-submit actions (Collect Submissions
 *                 is `save-to-database`) and its recipient.
 *   submissions — counts per form from Elementor's own tables
 *                 (`{prefix}e_submissions`, and its actions log for whether
 *                 each stored enquiry's email was sent). Counts only: a
 *                 visitor's details never leave the site.
 *
 * Every column is checked before it is used: an Elementor Pro version whose
 * tables differ yields unknown counts, never a wrong one.
 */
class Elementor_Forms {

	/** At most this many documents holding a form are read. */
	public const MAX_DOCUMENTS = 40;
	/** At most this many forms are reported. */
	public const MAX_FORMS = 60;
	/** Elementor Pro's "Collect Submissions" action. */
	public const STORE_ACTION = 'save-to-database';

	/**
	 * The report, or null when Elementor Pro is not active.
	 *
	 * @return array{submissions_table: bool, forms: array<int, array<string, mixed>>, truncated: bool}|null
	 */
	public static function report( int $now ): ?array {
		if ( ! defined( 'ELEMENTOR_PRO_VERSION' ) ) {
			return null;
		}
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- a read on demand for the backend; at most MAX_DOCUMENTS + 1 rows.
			$wpdb->prepare(
				"SELECT p.ID AS post_id, p.post_title, p.post_type, p.post_status, pm.meta_value
				FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				WHERE pm.meta_key = '_elementor_data'
				AND pm.meta_value LIKE %s
				AND p.post_type <> 'revision'
				AND p.post_status IN ('publish', 'private', 'draft', 'future', 'pending')
				ORDER BY p.ID ASC
				LIMIT %d",
				'%' . $wpdb->esc_like( '"widgetType":"form"' ) . '%',
				self::MAX_DOCUMENTS + 1
			),
			ARRAY_A
		);
		$rows      = is_array( $rows ) ? $rows : array();
		$truncated = count( $rows ) > self::MAX_DOCUMENTS;
		$rows      = array_slice( $rows, 0, self::MAX_DOCUMENTS );

		$forms = array();
		foreach ( $rows as $row ) {
			$tree = json_decode( (string) $row['meta_value'], true );
			if ( ! is_array( $tree ) ) {
				continue;
			}
			foreach ( self::forms_in( $tree ) as $form ) {
				if ( count( $forms ) >= self::MAX_FORMS ) {
					$truncated = true;
					break 2;
				}
				$forms[] = array_merge(
					array(
						'post_id'     => (int) $row['post_id'],
						'post_title'  => sanitize_text_field( (string) $row['post_title'] ),
						'post_type'   => (string) $row['post_type'],
						'post_status' => (string) $row['post_status'],
					),
					$form
				);
			}
		}

		$table = self::submissions_columns();
		$stats = null === $table ? array() : self::submission_stats( $table, $now );
		foreach ( $forms as &$form ) {
			$key = $form['post_id'] . ':' . $form['element_id'];
			if ( null === $table ) {
				// No table: nothing was ever kept, or this Elementor Pro has no submissions store.
				$form += array( 'submissions_total' => null, 'submissions_last_30d' => null, 'last_submission_at' => null );
				continue;
			}
			$form += $stats[ $key ] ?? array(
				'submissions_total'    => 0,
				'submissions_last_30d' => 0,
				'last_submission_at'   => null,
				'email_sent_30d'       => isset( $table['log'] ) ? 0 : null,
				'email_failed_30d'     => isset( $table['log'] ) ? 0 : null,
			);
		}
		unset( $form );

		return array(
			'submissions_table' => null !== $table,
			'forms'             => $forms,
			'truncated'         => $truncated,
		);
	}

	/**
	 * Every Elementor Pro form widget in a document tree, with its settings.
	 *
	 * @param array<int|string, mixed> $nodes Elementor elements.
	 * @return array<int, array<string, mixed>>
	 */
	public static function forms_in( array $nodes ): array {
		$out = array();
		foreach ( $nodes as $node ) {
			if ( ! is_array( $node ) ) {
				continue;
			}
			if ( 'widget' === ( $node['elType'] ?? null ) && 'form' === ( $node['widgetType'] ?? null ) && is_string( $node['id'] ?? null ) ) {
				$settings = is_array( $node['settings'] ?? null ) ? $node['settings'] : array();
				$actions  = null;
				if ( isset( $settings['submit_actions'] ) && is_array( $settings['submit_actions'] ) ) {
					$actions = array_values( array_filter( array_map( 'strval', $settings['submit_actions'] ), static fn( string $a ): bool => '' !== $a ) );
				}
				$to    = is_string( $settings['email_to'] ?? null ) ? trim( $settings['email_to'] ) : '';
				$name  = is_string( $settings['form_name'] ?? null ) ? trim( $settings['form_name'] ) : '';
				$out[] = array(
					'element_id' => substr( (string) $node['id'], 0, 40 ),
					'form_name'  => '' === $name ? null : sanitize_text_field( $name ),
					// Null: none saved, so the form runs Elementor's defaults.
					'actions'    => null === $actions ? null : array_slice( $actions, 0, 30 ),
					'email_to'   => '' === $to ? null : sanitize_text_field( $to ),
				);
			}
			if ( isset( $node['elements'] ) && is_array( $node['elements'] ) ) {
				$out = array_merge( $out, self::forms_in( $node['elements'] ) );
			}
		}
		return $out;
	}

	/**
	 * The submissions table's name and the columns this read needs, or null
	 * when there is no table or it lacks them. `log` is set when the actions
	 * log can say whether each enquiry's email was sent.
	 *
	 * @return array{table: string, status: bool, log?: string}|null
	 */
	private static function submissions_columns(): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'e_submissions';
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- schema probe.
		if ( $found !== $table ) {
			return null;
		}
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- schema probe; the name is the prefix plus a constant.
		$columns = is_array( $columns ) ? $columns : array();
		foreach ( array( 'id', 'post_id', 'element_id', 'created_at_gmt' ) as $needed ) {
			if ( ! in_array( $needed, $columns, true ) ) {
				return null;
			}
		}
		$out = array( 'table' => $table, 'status' => in_array( 'status', $columns, true ) );

		$log   = $wpdb->prefix . 'e_submissions_actions_log';
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $log ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- schema probe.
		if ( $found === $log ) {
			$log_columns = $wpdb->get_col( "SHOW COLUMNS FROM `{$log}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- schema probe.
			$log_columns = is_array( $log_columns ) ? $log_columns : array();
			if ( ! array_diff( array( 'submission_id', 'action_name', 'status' ), $log_columns ) ) {
				$out['log'] = $log;
			}
		}
		return $out;
	}

	/**
	 * Counts per form, keyed "post_id:element_id". Trashed submissions are
	 * not counted.
	 *
	 * @param array{table: string, status: bool, log?: string} $table Columns.
	 * @return array<string, array<string, mixed>>
	 */
	private static function submission_stats( array $table, int $now ): array {
		global $wpdb;
		$since  = gmdate( 'Y-m-d H:i:s', $now - 30 * DAY_IN_SECONDS );
		$name   = $table['table'];
		$live   = $table['status'] ? "AND s.status <> 'trash'" : '';
		$counts = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- counts on demand.
			$wpdb->prepare(
				"SELECT s.post_id, s.element_id, COUNT(*) AS total, SUM(s.created_at_gmt >= %s) AS recent, MAX(s.created_at_gmt) AS latest FROM `{$name}` s WHERE 1=1 {$live} GROUP BY s.post_id, s.element_id", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name and a constant clause.
				$since
			),
			ARRAY_A
		);
		$out = array();
		foreach ( is_array( $counts ) ? $counts : array() as $row ) {
			$stamp = is_string( $row['latest'] ?? null ) ? strtotime( $row['latest'] . ' UTC' ) : false;
			$out[ (int) $row['post_id'] . ':' . (string) $row['element_id'] ] = array(
				'submissions_total'    => (int) $row['total'],
				'submissions_last_30d' => (int) $row['recent'],
				'last_submission_at'   => false === $stamp ? null : gmdate( 'c', $stamp ),
				'email_sent_30d'       => isset( $table['log'] ) ? 0 : null,
				'email_failed_30d'     => isset( $table['log'] ) ? 0 : null,
			);
		}
		if ( ! isset( $table['log'] ) ) {
			return $out;
		}
		$log    = $table['log'];
		$emails = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- counts on demand.
			$wpdb->prepare(
				"SELECT s.post_id, s.element_id, SUM(l.status = 'success') AS sent, SUM(l.status = 'failed') AS failed FROM `{$log}` l INNER JOIN `{$name}` s ON s.id = l.submission_id WHERE l.action_name = 'email' AND s.created_at_gmt >= %s {$live} GROUP BY s.post_id, s.element_id", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names and a constant clause.
				$since
			),
			ARRAY_A
		);
		foreach ( is_array( $emails ) ? $emails : array() as $row ) {
			$key = (int) $row['post_id'] . ':' . (string) $row['element_id'];
			if ( isset( $out[ $key ] ) ) {
				$out[ $key ]['email_sent_30d']   = (int) $row['sent'];
				$out[ $key ]['email_failed_30d'] = (int) $row['failed'];
			}
		}
		return $out;
	}
}