<?php

namespace ChargeForms\Includes;

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

/**
 * An entry's field values are stored as one JSON blob in `response` — cheap
 * to write and to read back as a whole. Only attributes list/report screens
 * actually filter/sort by (form_id, status, created_at) are real indexed
 * columns; `entry_meta` is a side channel for occasional per-entry extras
 * (e.g. a future payment transaction record), not the primary storage —
 * one row per field per submission doesn't scale once forms have many
 * fields or high submission volume.
 */
class Entry {

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'chargeforms_entries';
	}

	public static function meta_table() {
		global $wpdb;
		return $wpdb->prefix . 'chargeforms_entry_meta';
	}

	/**
	 * `created_at` is set explicitly here, in GMT (roadmap issue #27),
	 * rather than left to the `chargeforms_entries` table's own
	 * `DEFAULT CURRENT_TIMESTAMP` — that default's actual timezone
	 * depends on the MySQL server's session timezone, which is neither
	 * guaranteed to be UTC nor tied to the WordPress site's configured
	 * timezone. Every date-range filter this column is compared against
	 * (date_from/date_to args throughout this class) is built with
	 * gmdate(), so this column needs to reliably BE GMT, not whatever a
	 * given host's MySQL happens to default to.
	 */
	public static function create( $form_id, array $values ) {
		global $wpdb;

		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom plugin table, no core API exists for it.
			self::table(),
			[
				'form_id'    => $form_id,
				'status'     => 'unread',
				'response'   => wp_json_encode( $values ),
				'ip_address' => Settings::get( 'disable_ip_logging' ) ? '' : self::client_ip(),
				'user_agent' => isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '',
				'created_at' => current_time( 'mysql', true ),
			],
			[ '%d', '%s', '%s', '%s', '%s', '%s' ]
		);

		return $wpdb->insert_id;
	}

	/** Same as create() but lets an importer set the original submission date instead of "now". */
	public static function create_with_date( $form_id, array $values, $created_at ) {
		global $wpdb;

		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom plugin table, no core API exists for it.
			self::table(),
			[
				'form_id'    => $form_id,
				'status'     => 'unread',
				'response'   => wp_json_encode( $values ),
				'ip_address' => '',
				'user_agent' => 'Imported',
				'created_at' => $created_at,
			],
			[ '%d', '%s', '%s', '%s', '%s', '%s' ]
		);

		return $wpdb->insert_id;
	}

	public static function get( $entry_id ) {
		global $wpdb;
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), $entry_id ),
			ARRAY_A
		);

		return $row ? self::hydrate( $row ) : null;
	}

	public static function for_form( $form_id ) {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare( 'SELECT * FROM %i WHERE form_id = %d ORDER BY created_at DESC', self::table(), $form_id ),
			ARRAY_A
		);

		return array_map( [ __CLASS__, 'hydrate' ], $rows ?: [] );
	}

	/**
	 * Paginated equivalent of for_form() — roadmap issue #18 ("Entries
	 * Export/Import Does Not Scale for Large Datasets": "All-entries REST
	 * responses can be huge"). Mirrors the {items, total, page, per_page}
	 * shape Form::query()/Api_Log::query()/Notification_Log::query()
	 * already use, so a REST consumer paging through entries follows the
	 * same convention as every other paginated list this plugin exposes.
	 */
	public static function query_for_form( $form_id, array $args = [] ) {
		global $wpdb;
		$table = self::table();

		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );
		$per_page = max( 1, min( 200, (int) ( $args['per_page'] ?? 50 ) ) );
		$offset   = ( $page - 1 ) * $per_page;

		$total = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE form_id = %d', $table, $form_id ) );

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare(
				'SELECT * FROM %i WHERE form_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d',
				$table,
				$form_id,
				$per_page,
				$offset
			),
			ARRAY_A
		);

		return [
			'items'    => array_map( [ __CLASS__, 'hydrate' ], $rows ?: [] ),
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/** Kept for any caller that only wants one entry's values by id. */
	public static function values_for( $entry_id ) {
		$entry = self::get( $entry_id );
		return $entry ? $entry['values'] : [];
	}

	/**
	 * $exclude_pending — the limit-entries restriction shouldn't count
	 * unconfirmed double-opt-in submissions against the cap; the Forms list
	 * "Entries" column count should show everything, confirmed or not.
	 */
	public static function count_for_form( $form_id, $exclude_pending = false ) {
		global $wpdb;
		// Trashed is always excluded, independent of $exclude_pending — a
		// trashed entry represents something the site owner actively
		// removed, unlike "pending" (still awaiting double opt-in
		// confirmation), which the Forms list column deliberately still
		// counts (see this method's original doc comment above). Every
		// caller of this method (the entry-limit restriction check,
		// count_by_form() below) wants "real, current entries" — none want
		// trashed junk still counting against a cap or a total.
		if ( $exclude_pending ) {
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE form_id = %d AND status != 'trashed' AND status != 'pending'", self::table(), $form_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		}
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE form_id = %d AND status != 'trashed'", self::table(), $form_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	/** Trashed-entry count for one form — backs the Trashed quick-filter's count badge. */
	public static function count_trashed_for_form( $form_id ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE form_id = %d AND status = 'trashed'", self::table(), $form_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	/** Favorited (starred) entry count for one form, excluding trashed — backs the per-form Entries screen's stat-card row. */
	public static function count_starred_for_form( $form_id ) {
		global $wpdb;
		$entries = self::table();
		$meta    = self::meta_table();
		return (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			"SELECT COUNT(*) FROM %i e
			 INNER JOIN %i m ON m.entry_id = e.id
			 WHERE e.form_id = %d AND e.status != 'trashed' AND m.meta_key = 'starred' AND m.meta_value = '1'",
			$entries,
			$meta,
			$form_id
		) );
	}

	/** Site-wide trashed-entry count — backs the global Entries overview's Trashed quick-filter. */
	public static function count_trashed() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'trashed'", self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	public static function count_all() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	/**
	 * Submission counts for MULTIPLE forms in one GROUP BY query, instead
	 * of one Entry::query() COUNT(*) call per form (roadmap issue #36:
	 * "Reports May Become Expensive on Large Entry Tables" — the Reports
	 * page's "Top Forms" widget used to do exactly that, once per form on
	 * the site, to find the top 5). A form with zero matching entries
	 * simply has no key in the returned map — callers should treat a
	 * missing key as 0, not assume every requested id comes back.
	 *
	 * @return array<int,int> form_id => count
	 */
	public static function count_by_form( array $form_ids, $date_from = '', $date_to = '' ) {
		global $wpdb;
		$form_ids = array_values( array_unique( array_map( 'intval', $form_ids ) ) );
		if ( empty( $form_ids ) ) {
			return [];
		}

		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $form_ids ), '%d' ) );
		$from         = $date_from ? gmdate( 'Y-m-d 00:00:00', strtotime( $date_from ) ) : '';
		$to           = $date_to ? gmdate( 'Y-m-d 23:59:59', strtotime( $date_to ) ) : '';

		// Excludes trashed for the same reason count_for_form() does — a
		// site owner's per-form entry total/Reports figures/weekly summary
		// shouldn't still include what they've actively removed. Every
		// branch below is a genuine literal at the prepare() call site
		// (no query text held in a variable) — the live Plugin Check
		// scanner's PreparedSQL.NotPrepared check flags any variable
		// indirection here regardless of how safely it was built.
		if ( $from && $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT form_id, COUNT(*) AS total FROM %i WHERE form_id IN ({$placeholders}) AND status != 'trashed' AND created_at >= %s AND created_at <= %s GROUP BY form_id", array_merge( [ $table ], $form_ids, [ $from, $to ] ) ), ARRAY_A );
		} elseif ( $from ) {
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT form_id, COUNT(*) AS total FROM %i WHERE form_id IN ({$placeholders}) AND status != 'trashed' AND created_at >= %s GROUP BY form_id", array_merge( [ $table ], $form_ids, [ $from ] ) ), ARRAY_A );
		} elseif ( $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT form_id, COUNT(*) AS total FROM %i WHERE form_id IN ({$placeholders}) AND status != 'trashed' AND created_at <= %s GROUP BY form_id", array_merge( [ $table ], $form_ids, [ $to ] ) ), ARRAY_A );
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT form_id, COUNT(*) AS total FROM %i WHERE form_id IN ({$placeholders}) AND status != 'trashed' GROUP BY form_id", array_merge( [ $table ], $form_ids ) ), ARRAY_A );
		}

		$counts = [];
		foreach ( $rows ?: [] as $row ) {
			$counts[ (int) $row['form_id'] ] = (int) $row['total'];
		}
		return $counts;
	}

	/**
	 * Per-form unread counts in one GROUP BY query, same shape/pattern as
	 * count_by_form() — backs the Forms list "unread / total" entries
	 * column (the reference product parity: their AllForms.vue shows exactly this,
	 * with an info tooltip explaining the format; FormCraft's Vue Forms
	 * List previously showed only a bare total with no unread signal at
	 * all, a real information gap, not just a cosmetic one).
	 */
	public static function count_unread_by_form( array $form_ids ) {
		global $wpdb;
		$form_ids = array_values( array_unique( array_map( 'intval', $form_ids ) ) );
		if ( empty( $form_ids ) ) {
			return [];
		}

		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $form_ids ), '%d' ) );
		$rows         = $wpdb->get_results( $wpdb->prepare( "SELECT form_id, COUNT(*) AS total FROM %i WHERE status = 'unread' AND form_id IN ({$placeholders}) GROUP BY form_id", array_merge( [ $table ], $form_ids ) ), ARRAY_A );

		$counts = [];
		foreach ( $rows ?: [] as $row ) {
			$counts[ (int) $row['form_id'] ] = (int) $row['total'];
		}
		return $counts;
	}

	/** Backs the sidebar-menu and admin-bar unread badges — a plain equality check, not query()'s full filter set, since this only ever needs the one number. */
	public static function count_unread() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'unread'", self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	public static function count_since( $mysql_datetime ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s', self::table(), $mysql_datetime ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	/**
	 * Daily submission counts for the last $days days, oldest first.
	 * Always returns one row per day (zero-filled), keyed 'Y-m-d' => count.
	 */
	public static function counts_by_day( $days = 7 ) {
		global $wpdb;

		$since = gmdate( 'Y-m-d 00:00:00', strtotime( "-" . ( $days - 1 ) . " days" ) );

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare(
				'SELECT DATE(created_at) as day, COUNT(*) as total FROM %i WHERE created_at >= %s GROUP BY DATE(created_at)',
				self::table(),
				$since
			),
			ARRAY_A
		);

		$by_day = [];
		foreach ( $rows ?: [] as $row ) {
			$by_day[ $row['day'] ] = (int) $row['total'];
		}

		$series = [];
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$day            = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) );
			$series[ $day ] = $by_day[ $day ] ?? 0;
		}

		return $series;
	}

	/**
	 * Same daily series as counts_by_day(), broken out by real status
	 * (unread/read/pending) instead of one flat total — powers the
	 * Overview chart's multi-series legend. Only statuses ChargeForms
	 * actually has are included; there's no "Spam" or "Trashed" status in
	 * this schema (rejected submissions are never stored at all, and
	 * there's no soft-delete/trash concept), so those aren't fabricated
	 * here as always-empty series — Spam is already shown honestly as its
	 * own always-0 stat tile above the chart instead.
	 */
	public static function counts_by_day_and_status( $days = 7 ) {
		global $wpdb;

		$since   = gmdate( 'Y-m-d 00:00:00', strtotime( "-" . ( $days - 1 ) . " days" ) );
		$table   = self::table();
		$statuses = [ 'unread', 'read', 'pending' ];

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare(
				'SELECT DATE(created_at) as day, status, COUNT(*) as total FROM %i WHERE created_at >= %s GROUP BY DATE(created_at), status',
				$table,
				$since
			),
			ARRAY_A
		);

		$by_day = [];
		foreach ( $rows ?: [] as $row ) {
			$by_day[ $row['day'] ][ $row['status'] ] = (int) $row['total'];
		}

		$series = [ 'total' => [], 'unread' => [], 'read' => [], 'pending' => [] ];
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$day = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) );
			$day_total = 0;
			foreach ( $statuses as $status ) {
				$count = $by_day[ $day ][ $status ] ?? 0;
				$series[ $status ][ $day ] = $count;
				$day_total += $count;
			}
			$series['total'][ $day ] = $day_total;
		}

		return $series;
	}

	/**
	 * Percentage change between the current period's total and the
	 * immediately-preceding period of equal length — e.g. "this last 30
	 * days vs the 30 days before that". Returns null when there's no prior
	 * data to compare against (so the caller can show "—" honestly instead
	 * of a misleading "+100%" from a 0 baseline).
	 */
	public static function percent_change( $form_id, $date_from, $date_to ) {
		if ( ! $date_from || ! $date_to ) {
			return null;
		}

		$days          = max( 1, ( strtotime( $date_to ) - strtotime( $date_from ) ) / DAY_IN_SECONDS + 1 );
		$prior_to      = gmdate( 'Y-m-d', strtotime( $date_from . ' -1 day' ) );
		$prior_from    = gmdate( 'Y-m-d', strtotime( $prior_to . ' -' . ( $days - 1 ) . ' days' ) );

		$current_args = array_filter( [ 'form_id' => $form_id ?: null, 'date_from' => $date_from, 'date_to' => $date_to, 'per_page' => 1 ] );
		$prior_args   = array_filter( [ 'form_id' => $form_id ?: null, 'date_from' => $prior_from, 'date_to' => $prior_to, 'per_page' => 1 ] );

		$current = self::query( $current_args )['total'];
		$prior   = self::query( $prior_args )['total'];

		if ( 0 === $prior ) {
			return null;
		}

		return round( ( ( $current - $prior ) / $prior ) * 100 );
	}

	/**
	 * Value distribution for one field across every entry on a form — e.g.
	 * how many respondents picked each option on a Dropdown/Radio/Checkbox
	 * field. Sorted highest-count first. Only meaningful for choice-type
	 * fields; the caller (Reports_Page) is what actually restricts which
	 * field types this gets offered for.
	 */
	public static function field_value_counts( $form_id, $field_id ) {
		$entries = self::for_form( $form_id );
		$counts  = [];

		foreach ( $entries as $entry ) {
			$val = $entry['values'][ $field_id ] ?? '';
			$vals = is_array( $val ) ? $val : [ $val ];

			foreach ( $vals as $v ) {
				$v = trim( (string) $v );
				if ( '' === $v ) {
					continue;
				}
				$counts[ $v ] = ( $counts[ $v ] ?? 0 ) + 1;
			}
		}

		arsort( $counts );
		return $counts;
	}

	/**
	 * Same tally as field_value_counts(), for several fields on the same
	 * form at once — fetches the form's entries exactly once (for_form())
	 * and tallies every requested field from that one pass, instead of
	 * calling field_value_counts() once per field, which would each
	 * independently re-fetch every entry (the Field Analytics "show every
	 * chartable field at once" redesign needed this, or showing N fields
	 * would cost N full entry fetches for the same form — the same class
	 * of mistake already fixed elsewhere in this codebase, roadmap issue
	 * #36).
	 *
	 * @return array<string,array<string,int>> field_id => value => count, highest-count first
	 */
	public static function field_value_counts_multi( $form_id, array $field_ids ) {
		$entries = self::for_form( $form_id );
		$by_field = array_fill_keys( $field_ids, [] );

		foreach ( $entries as $entry ) {
			foreach ( $field_ids as $field_id ) {
				$val  = $entry['values'][ $field_id ] ?? '';
				$vals = is_array( $val ) ? $val : [ $val ];

				foreach ( $vals as $v ) {
					$v = trim( (string) $v );
					if ( '' === $v ) {
						continue;
					}
					$by_field[ $field_id ][ $v ] = ( $by_field[ $field_id ][ $v ] ?? 0 ) + 1;
				}
			}
		}

		foreach ( $by_field as &$counts ) {
			arsort( $counts );
		}
		unset( $counts ); // Break the reference — leaving it dangling would silently corrupt the last element on any later foreach reuse of this variable name in the same scope.

		return $by_field;
	}

	/**
	 * Submissions by country — honestly derived from each form's own
	 * Country List field answers, not IP geolocation (ChargeForms doesn't
	 * have a GeoIP database or service to call). $form_id = 0 aggregates
	 * across every form that has a Country field; a specific $form_id only
	 * looks at that form's own Country field, if it has one. Returns
	 * highest-count first, same shape as field_value_counts().
	 */
	public static function country_breakdown( $form_id = 0, $date_from = '', $date_to = '' ) {
		$forms = $form_id ? array_filter( [ Form::get( $form_id ) ] ) : Form::all();
		$counts = [];

		foreach ( $forms as $form ) {
			$country_fields = array_values( array_filter( $form['fields'], fn( $f ) => 'country' === $f['type'] ) );
			if ( empty( $country_fields ) ) {
				continue;
			}

			$args = array_filter( [
				'form_id'   => $form['id'],
				'date_from' => $date_from ?: null,
				'date_to'   => $date_to ?: null,
			] );
			$entries = self::query( array_merge( $args, [ 'per_page' => 100000 ] ) )['items'];

			foreach ( $entries as $entry ) {
				foreach ( $country_fields as $field ) {
					$val = trim( (string) ( $entry['values'][ $field['id'] ] ?? '' ) );
					if ( '' === $val ) {
						continue;
					}
					$counts[ $val ] = ( $counts[ $val ] ?? 0 ) + 1;
				}
			}
		}

		arsort( $counts );
		return $counts;
	}

	/**
	 * Device Breakdown — Desktop/Mobile/Tablet, classified from each
	 * entry's own stored user_agent (already captured on every submission
	 * for the double opt-in flow — no new tracking needed). A plain
	 * substring-based UA sniff is approximate by nature (this is true of
	 * any UA-based device detection, not a shortcut specific to this
	 * implementation) — good enough for a rough breakdown, not a claim of
	 * precise device fingerprinting.
	 */
	public static function device_breakdown( $form_id = 0, $date_from = '', $date_to = '' ) {
		$args = array_filter( [
			'form_id'   => $form_id ?: null,
			'date_from' => $date_from ?: null,
			'date_to'   => $date_to ?: null,
		] );
		$entries = self::query( array_merge( $args, [ 'per_page' => 100000 ] ) )['items'];

		$counts = [ 'Desktop' => 0, 'Mobile' => 0, 'Tablet' => 0 ];
		foreach ( $entries as $entry ) {
			$ua = (string) ( $entry['user_agent'] ?? '' );
			if ( '' === $ua ) {
				continue;
			}
			$counts[ self::classify_device( $ua ) ]++;
		}

		return array_filter( $counts );
	}

	/**
	 * Browser-family breakdown — same shape and same reasoning as
	 * device_breakdown() right above (the data — user_agent — is already
	 * captured on every entry, so this needed no new tracking either),
	 * reusing classify_browser() so this agrees with the Entries table's
	 * own Browser column rather than a second, possibly-drifting
	 * classification.
	 */
	public static function browser_breakdown( $form_id = 0, $date_from = '', $date_to = '' ) {
		$args = array_filter( [
			'form_id'   => $form_id ?: null,
			'date_from' => $date_from ?: null,
			'date_to'   => $date_to ?: null,
		] );
		$entries = self::query( array_merge( $args, [ 'per_page' => 100000 ] ) )['items'];

		$counts = [];
		foreach ( $entries as $entry ) {
			$ua = (string) ( $entry['user_agent'] ?? '' );
			if ( '' === $ua ) {
				continue;
			}
			$browser = self::classify_browser( $ua );
			if ( '' === $browser ) {
				continue;
			}
			$counts[ $browser ] = ( $counts[ $browser ] ?? 0 ) + 1;
		}

		return $counts;
	}

	/** Approximate device family from a raw user_agent string — shared by device_breakdown() and the Entries table's Browser column so both agree on the same classification. */
	public static function classify_device( $user_agent ) {
		if ( preg_match( '/iPad|Tablet(?!.*Mobile)/i', $user_agent ) ) {
			return 'Tablet';
		}
		if ( preg_match( '/Mobi|Android.*Mobile|iPhone|iPod/i', $user_agent ) ) {
			return 'Mobile';
		}
		return 'Desktop';
	}

	/**
	 * Hour-of-day x day-of-week submission counts, for the Timeline
	 * Patterns heatmap — derived entirely from entries.created_at, which
	 * every submission already has, so this needs no new tracking at all.
	 * Returns a 7x24 matrix: [ dow(0=Sun..6=Sat) ][ hour(0-23) ] => count.
	 */
	public static function submissions_by_hour_and_dow( $form_id = 0, $date_from = '', $date_to = '' ) {
		global $wpdb;
		$table = self::table();
		$from  = $date_from ? $date_from . ' 00:00:00' : '';
		$to    = $date_to ? $date_to . ' 23:59:59' : '';

		// Every branch below is a genuine, fully-inline literal at the
		// prepare() call site (no query text, or even a fragment of it,
		// held in a variable) — the live Plugin Check scanner's
		// PreparedSQL.NotPrepared check flags ANY variable indirection
		// here, even a concatenation of two variables each holding a
		// literal fragment, regardless of how safely it was built.
		if ( $form_id && $from && $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND form_id = %d AND created_at >= %s AND created_at <= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $form_id, $from, $to ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $form_id && $from ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND form_id = %d AND created_at >= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $form_id, $from ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $form_id && $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND form_id = %d AND created_at <= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $form_id, $to ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $form_id ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND form_id = %d GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $form_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $from && $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND created_at >= %s AND created_at <= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $from, $to ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $from ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND created_at >= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $from ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} elseif ( $to ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 AND created_at <= %s GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table, $to ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DAYOFWEEK(created_at) as dow, HOUR(created_at) as hour, COUNT(*) as total FROM %i WHERE 1=1 GROUP BY DAYOFWEEK(created_at), HOUR(created_at)', $table ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
		}

		$matrix = array_fill( 0, 7, array_fill( 0, 24, 0 ) );
		foreach ( $rows ?: [] as $row ) {
			// MySQL's DAYOFWEEK() is 1=Sunday..7=Saturday; normalize to 0-6.
			$dow = ( (int) $row['dow'] ) - 1;
			$matrix[ $dow ][ (int) $row['hour'] ] = (int) $row['total'];
		}

		return $matrix;
	}

	/** Recent entries across all forms, newest first — used by the global Entries overview. */
	public static function recent( $limit = 20 ) {
		global $wpdb;
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC LIMIT %d', self::table(), $limit ),
			ARRAY_A
		);

		return array_map( [ __CLASS__, 'hydrate' ], $rows ?: [] );
	}

	/**
	 * Flexible listing query for the global Entries overview: optional form
	 * filter, read/unread status filter, a search across submitted values
	 * (LIKE against the response JSON blob — fine at this scale; a form
	 * with high-volume search needs would move to a real search index, not
	 * a schema this simple regardless of storage shape), and real
	 * LIMIT/OFFSET pagination.
	 *
	 * @param array $args { form_id?: int, status?: 'all'|'unread'|'read', search?: string, page?: int, per_page?: int }
	 * @return array { items: array, total: int, pages: int, page: int, per_page: int }
	 */
	public static function query( array $args = [] ) {
		global $wpdb;
		$table = self::table();

		$where  = [];
		$params = [];

		if ( ! empty( $args['form_id'] ) ) {
			$where[]  = 'form_id = %d';
			$params[] = (int) $args['form_id'];
		}

		// 'all' means "every real entry" — trashed entries are excluded by
		// default the same way a soft-deleted row is everywhere else in this
		// codebase (Form::get(), etc.), not a literal "no filter". Viewing
		// the trash is an explicit, separate status='trashed' request.
		$status = $args['status'] ?? 'all';
		if ( 'all' === $status ) {
			$where[] = "status != 'trashed'";
		} else {
			$where[]  = 'status = %s';
			$params[] = sanitize_key( $status );
		}

		$search = trim( (string) ( $args['search'] ?? '' ) );
		if ( '' !== $search ) {
			$where[]  = 'response LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'created_at >= %s';
			$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( $args['date_from'] ) );
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'created_at <= %s';
			$params[] = gmdate( 'Y-m-d 23:59:59', strtotime( $args['date_to'] ) );
		}

		$where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';

		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i {$where_sql}", array_merge( [ $table ], $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		$per_page = max( 1, (int) ( $args['per_page'] ?? 10 ) );
		$pages    = max( 1, (int) ceil( $total / $per_page ) );
		$page     = max( 1, min( (int) ( $args['page'] ?? 1 ), $pages ) );
		$offset   = ( $page - 1 ) * $per_page;

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i {$where_sql} ORDER BY created_at DESC LIMIT %d OFFSET %d", array_merge( [ $table ], $params, [ $per_page, $offset ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		return [
			'items'    => array_map( [ __CLASS__, 'hydrate' ], $rows ?: [] ),
			'total'    => $total,
			'pages'    => $pages,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/**
	 * The per-form Entries screen's Advanced Filter — real filter groups,
	 * not the simple single-search-box query() above. Conditions inside a
	 * group are ANDed, groups are ORed, matching the reference product's own stated
	 * convention (and what the filter builder UI tells the user).
	 *
	 * Each condition: { field: 'input:{field_id}' | 'attr:{key}' | 'user:{key}', operator, value }.
	 * 'input:' reads the field's own value out of the response JSON blob via
	 * MySQL's JSON_EXTRACT (requires MySQL 5.7+/MariaDB 10.2+ — the same
	 * baseline current WordPress itself expects). 'attr:' covers real,
	 * actually-stored entry attributes (id/status/created_at/ip_address/
	 * source_url) — deliberately NOT "Submitter Browser/Device" (those are
	 * classified from user_agent at display time, not stored, so they can't
	 * be filtered in SQL without a schema change) or "Entry Unique ID" /
	 * "Submission Admin View URL" (concepts ChargeForms doesn't have). 'user:'
	 * covers the submitting WP user's own fields, via a subquery through the
	 * submitted_by_user_id entry_meta value.
	 *
	 * @param array $groups [ [ 'conditions' => [ [ 'field', 'operator', 'value' ], ... ] ], ... ]
	 */
	public static function advanced_query( $form_id, array $groups, array $args = [] ) {
		global $wpdb;
		$table      = self::table();
		$meta_table = self::meta_table();

		$group_clauses = [];
		$params        = [];

		foreach ( $groups as $group ) {
			if ( ! is_array( $group ) ) {
				continue;
			}
			$conditions = is_array( $group['conditions'] ?? null ) ? $group['conditions'] : [];
			$cond_sql   = [];

			foreach ( $conditions as $cond ) {
				if ( ! is_array( $cond ) ) {
					continue;
				}
				$built = self::build_condition( $cond, $meta_table, $params );
				if ( $built ) {
					$cond_sql[] = $built;
				}
			}

			if ( $cond_sql ) {
				$group_clauses[] = '(' . implode( ' AND ', $cond_sql ) . ')';
			}
		}

		$where   = [ 'e.form_id = %d' ];
		$where_p = [ (int) $form_id ];
		if ( $group_clauses ) {
			$where[]  = '(' . implode( ' OR ', $group_clauses ) . ')';
			$where_p  = array_merge( $where_p, $params );
		}

		// Same "'all' excludes trashed, an explicit status is a literal
		// filter" semantics as query()'s identical block above — a trashed
		// entry stays out of the normal table until the Trashed view
		// explicitly asks for status=trashed.
		$status = $args['status'] ?? 'all';
		if ( 'all' === $status ) {
			$where[] = "e.status != 'trashed'";
		} else {
			$where[]   = 'e.status = %s';
			$where_p[] = sanitize_key( $status );
		}

		$search = trim( (string) ( $args['search'] ?? '' ) );
		if ( '' !== $search ) {
			$where[]  = 'e.response LIKE %s';
			$where_p[] = '%' . $wpdb->esc_like( $search ) . '%';
		}
		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'e.created_at >= %s';
			$where_p[] = gmdate( 'Y-m-d 00:00:00', strtotime( $args['date_from'] ) );
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'e.created_at <= %s';
			$where_p[] = gmdate( 'Y-m-d 23:59:59', strtotime( $args['date_to'] ) );
		}

		$where_sql = 'WHERE ' . implode( ' AND ', $where );

		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i e {$where_sql}", array_merge( [ $table ], $where_p ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		$per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
		$pages    = max( 1, (int) ceil( $total / $per_page ) );
		$page     = max( 1, min( (int) ( $args['page'] ?? 1 ), $pages ) );
		$offset   = ( $page - 1 ) * $per_page;

		// Sortable by real columns only — input-field values live inside
		// the JSON response blob, and sorting by those isn't portable
		// without MySQL-only JSON functions (same reason build_input_condition()
		// uses LIKE instead of JSON_EXTRACT — see its own docblock). The
		// per-form table's dynamic field columns get client-side sort of
		// the current page instead; see admin.js.
		$sort_columns = [ 'id' => 'e.id', 'created_at' => 'e.created_at', 'status' => 'e.status' ];
		$sort_key     = $sort_columns[ $args['sort_by'] ?? 'created_at' ] ?? 'e.created_at';
		$sort_dir     = 'asc' === strtolower( (string) ( $args['sort_dir'] ?? 'desc' ) ) ? 'ASC' : 'DESC';

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT e.* FROM %i e {$where_sql} ORDER BY {$sort_key} {$sort_dir} LIMIT %d OFFSET %d", array_merge( [ $table ], $where_p, [ $per_page, $offset ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		return [
			'items'    => array_map( [ __CLASS__, 'hydrate' ], $rows ?: [] ),
			'total'    => $total,
			'pages'    => $pages,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/** Builds one condition's SQL fragment (with placeholders) and appends its bind values to &$params by reference. Returns '' for anything malformed rather than guessing. */
	private static function build_condition( array $cond, $meta_table, array &$params ) {
		global $wpdb;

		// filter_groups arrives as attacker-controlled JSON decoded straight
		// from a GET param — field/operator/value can each individually be
		// an array, object, null, etc. instead of the string a well-formed
		// condition would send. is_string() guards reject anything that
		// isn't, rather than relying on (string) casts, which would emit an
		// "Array to string conversion" PHP notice for a crafted array value
		// and pollute debug.log.
		if ( ! is_string( $cond['field'] ?? null ) || ! is_string( $cond['operator'] ?? null ) ) {
			return '';
		}
		$field    = $cond['field'];
		$operator = $cond['operator'];
		$value    = is_scalar( $cond['value'] ?? null ) ? (string) $cond['value'] : '';

		if ( 0 === strpos( $field, 'input:' ) ) {
			$field_id = substr( $field, 6 );
			if ( ! preg_match( '/^[a-zA-Z0-9_-]+$/', $field_id ) ) {
				return '';
			}
			return self::build_input_condition( $field_id, $operator, $value, $params );
		}

		$column_expr = null;
		$column_params = [];

		if ( 0 === strpos( $field, 'attr:' ) ) {
			$key = substr( $field, 5 );
			$attr_columns = [
				'id'         => 'e.id',
				'status'     => 'e.status',
				'created_at' => 'e.created_at',
				'ip_address' => 'e.ip_address',
			];
			if ( isset( $attr_columns[ $key ] ) ) {
				$column_expr = $attr_columns[ $key ];
			} elseif ( 'source_url' === $key ) {
				$column_expr     = '(SELECT meta_value FROM %i WHERE entry_id = e.id AND meta_key = %s LIMIT 1)';
				$column_params[] = $meta_table;
				$column_params[] = 'source_url';
			} elseif ( 'starred' === $key ) {
				// Same meta-subquery shape as source_url above — 'starred' is
				// a per-entry meta flag (Entry::set_meta()'s single-value
				// upsert, see the toggle-star admin-post handler), not a real
				// column, so Favorites is filterable through the general
				// Advanced Filter rather than needing its own bespoke
				// dropdown the way the reference product's fixed entry_type filter does.
				// Wrapped in COALESCE(..., '0'): an entry that was never
				// starred has NO meta row at all, so the bare subquery
				// returns SQL NULL — and `NULL = '0'` is false, not true, in
				// SQL. Without this, "Not Favorited" would silently match
				// almost nothing (only entries explicitly un-starred after
				// being starred), since the vast majority of entries that
				// were simply never touched would be excluded instead of
				// correctly counted as not-favorited.
				$column_expr     = "COALESCE((SELECT meta_value FROM %i WHERE entry_id = e.id AND meta_key = %s LIMIT 1), '0')";
				$column_params[] = $meta_table;
				$column_params[] = 'starred';
			} else {
				return '';
			}
		} elseif ( 0 === strpos( $field, 'user:' ) ) {
			$key = substr( $field, 5 );
			$user_columns = [
				'id'           => 'ID',
				'user_login'   => 'user_login',
				'display_name' => 'display_name',
				'user_email'   => 'user_email',
			];
			if ( ! isset( $user_columns[ $key ] ) ) {
				return '';
			}
			$column_expr = "(SELECT {$user_columns[ $key ]} FROM %i WHERE ID = (SELECT meta_value FROM %i WHERE entry_id = e.id AND meta_key = %s LIMIT 1))";
			$column_params[] = $wpdb->users;
			$column_params[] = $meta_table;
			$column_params[] = 'submitted_by_user_id';
		} else {
			return '';
		}

		switch ( $operator ) {
			case 'not_equal':
				$sql = "{$column_expr} != %s";
				$column_params[] = (string) $value;
				break;
			case 'contains':
				$sql = "{$column_expr} LIKE %s";
				$column_params[] = '%' . $wpdb->esc_like( (string) $value ) . '%';
				break;
			case 'not_contains':
				$sql = "( {$column_expr} IS NULL OR {$column_expr} NOT LIKE %s )";
				// column_expr appears twice in this branch, so its own bind
				// values (e.g. the JSON path / meta_key) need repeating too.
				$column_params = array_merge( $column_params, $column_params, [ '%' . $wpdb->esc_like( (string) $value ) . '%' ] );
				break;
			case 'starts_with':
				$sql = "{$column_expr} LIKE %s";
				$column_params[] = $wpdb->esc_like( (string) $value ) . '%';
				break;
			case 'ends_with':
				$sql = "{$column_expr} LIKE %s";
				$column_params[] = '%' . $wpdb->esc_like( (string) $value );
				break;
			case 'greater_than':
				$sql = "{$column_expr} > %s";
				$column_params[] = (string) $value;
				break;
			case 'less_than':
				$sql = "{$column_expr} < %s";
				$column_params[] = (string) $value;
				break;
			case 'is_empty':
				$sql = "( {$column_expr} IS NULL OR {$column_expr} = '' )";
				$column_params = array_merge( $column_params, $column_params );
				break;
			case 'is_not_empty':
				$sql = "( {$column_expr} IS NOT NULL AND {$column_expr} != '' )";
				$column_params = array_merge( $column_params, $column_params );
				break;
			case 'equal_in':
			case 'not_equal_in':
				$list = array_filter( array_map( 'trim', explode( ',', (string) $value ) ), fn( $v ) => '' !== $v );
				if ( ! $list ) {
					return '';
				}
				$placeholders = implode( ',', array_fill( 0, count( $list ), '%s' ) );
				$sql = "{$column_expr} " . ( 'not_equal_in' === $operator ? 'NOT IN' : 'IN' ) . " ({$placeholders})";
				$column_params = array_merge( $column_params, $list );
				break;
			case 'equal':
			default:
				$sql = "{$column_expr} = %s";
				$column_params[] = (string) $value;
				break;
		}

		$params = array_merge( $params, $column_params );
		return $sql;
	}

	/**
	 * Input-field conditions can't use JSON_EXTRACT()/JSON_UNQUOTE() like a
	 * real column would — those are MySQL 5.7+-only functions, and this
	 * plugin also needs to run on WordPress installs using the SQLite
	 * database integration (which has no JSON_EXTRACT at all — confirmed
	 * directly: an earlier version of this method that used JSON_EXTRACT
	 * threw "no such function: JSON_UNQUOTE" under WordPress Playground's
	 * own SQLite backend). Same LIKE-on-the-raw-JSON-text technique
	 * value_exists_for_field() already uses elsewhere in this file — an
	 * exact "field_id":"value" substring match, portable to any SQL
	 * backend at the cost of very rare false positives if a value happens
	 * to contain another field's exact key:value text (accepted the same
	 * way this file already accepts it for value_exists_for_field()).
	 * Numeric greater/less-than comparisons aren't supported for input
	 * fields for the same reason — there's no portable way to numerically
	 * compare a value still embedded in JSON text — so those return ''
	 * (no-op) here rather than silently mis-filtering.
	 */
	private static function build_input_condition( $field_id, $operator, $value, array &$params ) {
		global $wpdb;

		$key_needle = '"' . addslashes( $field_id ) . '":"';

		switch ( $operator ) {
			case 'not_equal':
				$needle = $key_needle . addslashes( $value ) . '"';
				$sql    = '( e.response IS NULL OR e.response NOT LIKE %s )';
				$params[] = '%' . $wpdb->esc_like( $needle ) . '%';
				break;
			case 'contains':
				$needle = $key_needle . addslashes( $value );
				$sql    = 'e.response LIKE %s';
				$params[] = '%' . $wpdb->esc_like( $needle ) . '%';
				break;
			case 'not_contains':
				$needle = $key_needle . addslashes( $value );
				$sql    = '( e.response IS NULL OR e.response NOT LIKE %s )';
				$params[] = '%' . $wpdb->esc_like( $needle ) . '%';
				break;
			case 'starts_with':
				$needle = $key_needle . addslashes( $value );
				$sql    = 'e.response LIKE %s';
				$params[] = '%' . $wpdb->esc_like( $needle ) . '%';
				break;
			case 'ends_with':
				$needle = addslashes( $value ) . '"';
				$sql    = 'e.response LIKE %s';
				$params[] = '%' . $wpdb->esc_like( $key_needle ) . '%' . $wpdb->esc_like( $needle ) . '%';
				break;
			case 'is_empty':
				$sql = '( e.response IS NULL OR e.response NOT LIKE %s OR e.response LIKE %s )';
				$params[] = '%' . $wpdb->esc_like( $key_needle ) . '%';
				$params[] = '%' . $wpdb->esc_like( $key_needle . '"' ) . '%';
				break;
			case 'is_not_empty':
				$sql = '( e.response LIKE %s AND e.response NOT LIKE %s )';
				$params[] = '%' . $wpdb->esc_like( $key_needle ) . '%';
				$params[] = '%' . $wpdb->esc_like( $key_needle . '"' ) . '%';
				break;
			case 'equal_in':
			case 'not_equal_in':
				$list = array_filter( array_map( 'trim', explode( ',', $value ) ), fn( $v ) => '' !== $v );
				if ( ! $list ) {
					return '';
				}
				$clauses = [];
				foreach ( $list as $item ) {
					$needle    = $key_needle . addslashes( $item ) . '"';
					$clauses[] = 'e.response LIKE %s';
					$params[]  = '%' . $wpdb->esc_like( $needle ) . '%';
				}
				$sql = 'not_equal_in' === $operator
					? '( e.response IS NULL OR NOT (' . implode( ' OR ', $clauses ) . ') )'
					: '(' . implode( ' OR ', $clauses ) . ')';
				break;
			case 'greater_than':
			case 'less_than':
				// Not supported for input fields — see the docblock above.
				return '';
			case 'equal':
			default:
				$needle = $key_needle . addslashes( $value ) . '"';
				$sql    = 'e.response LIKE %s';
				$params[] = '%' . $wpdb->esc_like( $needle ) . '%';
				break;
		}

		return $sql;
	}

	/**
	 * True if any other entry on this form already has this exact value for
	 * this field — backs the "Validate as Unique" field setting. Matches
	 * against the JSON blob directly (LIKE on the encoded "key":"value" pair)
	 * rather than decoding every row, same trade-off as the search filter in
	 * query() above: fine at this scale, a real index if it ever isn't.
	 */
	public static function value_exists_for_field( $form_id, $field_key, $value ) {
		global $wpdb;

		$needle = '"' . addslashes( $field_key ) . '":"' . addslashes( $value ) . '"';

		return (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			'SELECT COUNT(*) FROM %i WHERE form_id = %d AND response LIKE %s',
			self::table(),
			$form_id,
			'%' . $wpdb->esc_like( $needle ) . '%'
		) ) > 0;
	}

	/** Overwrites an entry's submitted values wholesale — the caller (Entries_Page::handle_update_entry) is responsible for merging with existing values first so fields not present in the edit form aren't wiped. */
	public static function update( $id, array $values ) {
		global $wpdb;
		$wpdb->update( self::table(), [ 'response' => wp_json_encode( $values ) ], [ 'id' => $id ], [ '%s' ], [ '%d' ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed; not worth an object-cache layer for these tables.
		return true;
	}

	public static function set_status( $id, $status ) {
		global $wpdb;
		$wpdb->update( self::table(), [ 'status' => sanitize_key( $status ) ], [ 'id' => $id ], [ '%s' ], [ '%d' ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed; not worth an object-cache layer for these tables.
		return true;
	}

	public static function delete( $id ) {
		global $wpdb;
		$wpdb->delete( self::meta_table(), [ 'entry_id' => $id ], [ '%d' ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed; not worth an object-cache layer for these tables.
		$wpdb->delete( self::table(), [ 'id' => $id ], [ '%d' ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed; not worth an object-cache layer for these tables.
		/* translators: %d: entry id */
		Activity_Log::log( 'entry_deleted', 'entry', $id, sprintf( __( 'Deleted entry #%d', 'chargeforms' ), $id ) );
		return true;
	}

	/** Deletes every entry (and its meta) belonging to a form — called when the form itself is deleted. */
	public static function delete_for_form( $form_id ) {
		global $wpdb;
		$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE form_id = %d', self::table(), $form_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		if ( $ids ) {
			// $placeholders always contains exactly count($ids) '%d' tokens.
			$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
			$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE entry_id IN ({$placeholders})", array_merge( [ self::meta_table() ], $ids ) ) );
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE form_id = %d', self::table(), $form_id ) );
		}

		return true;
	}

	/** Side-channel storage for occasional per-entry extras — not field values. */ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom plugin table, no core API exists for it.
	public static function add_meta( $entry_id, $key, $value ) {
		global $wpdb;
		$wpdb->insert(
			self::meta_table(), // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- querying this plugin's own meta table, not postmeta/usermeta.
			[ // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- querying this plugin's own meta table, not postmeta/usermeta.
				'entry_id'   => $entry_id,
				'meta_key'   => sanitize_key( $key ),
				'meta_value' => is_array( $value ) ? wp_json_encode( $value ) : $value,
				// GMT, explicit (roadmap issue #27) — matches chargeforms_entries'
				// created_at; entry notes' timestamps are displayed with the
				// same format_entry_date() helper as entries themselves.
				'created_at' => current_time( 'mysql', true ),
			],
			[ '%d', '%s', '%s', '%s' ]
		);
	}

	/** Reads back a single side-channel extra (e.g. 'created_post_id') — null if never set. */
	public static function get_meta( $entry_id, $key ) {
		global $wpdb;
		return $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			'SELECT meta_value FROM %i WHERE entry_id = %d AND meta_key = %s LIMIT 1',
			self::meta_table(),
			$entry_id,
			sanitize_key( $key )
		) );
	}

	/**
	 * Bulk version of get_meta() for one meta_key across many entries at
	 * once — an entries TABLE (row-per-page, up to 100 rows) rendering a
	 * per-row flag (e.g. the Favorite star toggle) must not call get_meta()
	 * once per row, the same N+1 query mistake already fixed elsewhere in
	 * this codebase (roadmap issue #36). Returns entry_id => meta_value for
	 * every row that actually has the key set; a missing key means "no
	 * entry" in the returned array, not an empty-string value.
	 */
	public static function get_meta_bulk( array $entry_ids, $key ) {
		global $wpdb;
		$entry_ids = array_values( array_filter( array_map( 'intval', $entry_ids ) ) );
		if ( ! $entry_ids ) {
			return [];
		}
		$placeholders = implode( ',', array_fill( 0, count( $entry_ids ), '%d' ) );
		$rows         = $wpdb->get_results( $wpdb->prepare( "SELECT entry_id, meta_value FROM %i WHERE meta_key = %s AND entry_id IN ({$placeholders})", array_merge( [ self::meta_table(), sanitize_key( $key ) ], $entry_ids ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		$out = [];
		foreach ( $rows ?: [] as $row ) {
			$out[ (int) $row['entry_id'] ] = $row['meta_value'];
		}
		return $out;
	}

	/** Upsert for single-value flags (e.g. 'starred') — unlike add_meta(), calling this twice for the same key updates the same logical value instead of accumulating rows. */ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- custom plugin table, no core API exists for it; per-request freshness needed; not worth an object-cache layer for these tables; querying this plugin's own meta table, not postmeta/usermeta.
	public static function set_meta( $entry_id, $key, $value ) {
		global $wpdb;
		$wpdb->delete( self::meta_table(), [ 'entry_id' => $entry_id, 'meta_key' => sanitize_key( $key ) ], [ '%d', '%s' ] );
		self::add_meta( $entry_id, $key, $value );
	}

	/** Every stored value for a repeatable meta key (e.g. notes, activity log entries), oldest first. */
	public static function get_meta_all( $entry_id, $key ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			'SELECT meta_value, created_at FROM %i WHERE entry_id = %d AND meta_key = %s ORDER BY id ASC',
			self::meta_table(),
			$entry_id,
			sanitize_key( $key )
		), ARRAY_A ) ?: [];
	}

	/** Appends one activity-log entry (status change, edit, etc.) as JSON — shown in the entry detail view's Submission Logs card. */
	public static function log_activity( $entry_id, $message, $user_id = null ) {
		$user_id = null !== $user_id ? $user_id : get_current_user_id();
		$user    = $user_id ? get_userdata( $user_id ) : false;
		self::add_meta( $entry_id, 'activity_log', wp_json_encode( [
			'message' => $message,
			'user'    => $user ? $user->display_name : __( 'System', 'chargeforms' ),
		] ) );
	}

	/** Approximate browser family from a raw user_agent string — a plain substring sniff, same caveat as classify_device(): good enough for a quick display label, not precise UA parsing. */
	public static function classify_browser( $user_agent ) {
		$ua = (string) $user_agent;
		if ( '' === $ua ) {
			return '';
		}
		if ( preg_match( '/Edg\//i', $ua ) ) {
			return 'Edge';
		}
		if ( preg_match( '/OPR\/|Opera/i', $ua ) ) {
			return 'Opera';
		}
		if ( preg_match( '/Firefox/i', $ua ) ) {
			return 'Firefox';
		}
		if ( preg_match( '/CriOS|Chrome/i', $ua ) ) {
			return 'Chrome';
		}
		if ( preg_match( '/Safari/i', $ua ) ) {
			return 'Safari';
		}
		if ( preg_match( '/MSIE|Trident/i', $ua ) ) {
			return 'Internet Explorer';
		}
		return __( 'Unknown', 'chargeforms' );
	}

	/** True if any entry already has this exact meta value for this key — used to stop a captured Stripe PaymentIntent id from being replayed onto a second, unpaid entry. */
	public static function meta_exists( $key, $value ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			'SELECT COUNT(*) FROM %i WHERE meta_key = %s AND meta_value = %s',
			self::meta_table(),
			sanitize_key( $key ),
			$value
		) ) > 0;
	}

	/**
	 * Every entry that has a captured payment (any gateway), newest first —
	 * backs the Payments admin screen. Reads the gateway-agnostic
	 * `payment_transaction_id`/`payment_gateway`/`payment_amount` keys every
	 * new transaction writes (both Stripe and PayPal, since PayPal support
	 * was added), COALESCEd against the legacy `stripe_payment_intent_id`/
	 * `stripe_amount` keys for entries created before those generic keys
	 * existed — a single LEFT JOIN + COALESCE query rather than a UNION, so
	 * an entry that (like every NEW Stripe transaction) has BOTH the legacy
	 * and generic keys written never shows up twice. No backfill migration
	 * needed for old entries; they just fall through to the legacy columns.
	 */
	public static function payment_transactions( array $args = [] ) {
		global $wpdb;
		$entries = self::table();
		$meta    = self::meta_table();

		$per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );

		$date_where  = '';
		$date_params = [];
		if ( ! empty( $args['date_from'] ) ) {
			$date_where   .= ' AND e.created_at >= %s';
			$date_params[] = gmdate( 'Y-m-d 00:00:00', strtotime( $args['date_from'] ) );
		}
		if ( ! empty( $args['date_to'] ) ) {
			$date_where   .= ' AND e.created_at <= %s';
			$date_params[] = gmdate( 'Y-m-d 23:59:59', strtotime( $args['date_to'] ) );
		}

		$gateway_where  = '';
		$gateway_params = [];
		if ( ! empty( $args['gateway'] ) ) {
			// Legacy rows have no `payment_gateway` meta at all — they're
			// always Stripe (PayPal didn't exist when they were written), so
			// COALESCE'd to 'stripe' the same way the SELECT below does.
			$gateway_where    = ' HAVING COALESCE(gw.meta_value, \'stripe\') = %s';
			$gateway_params[] = sanitize_key( $args['gateway'] );
		}

		// Six identifier placeholders below (%i), in the exact left-to-right
		// order they appear in $joins: entries table once, then meta table
		// once per LEFT JOIN alias (txn, legacy_pi, gw, amt, legacy_amt).
		// Every caller of $joins must prepend $join_identifiers to its own
		// params, in this same order, before any of its own %s/%d values.
		$join_identifiers = [ $entries, $meta, $meta, $meta, $meta, $meta ];
		$joins = "
			FROM %i e
			LEFT JOIN %i txn ON txn.entry_id = e.id AND txn.meta_key = 'payment_transaction_id'
			LEFT JOIN %i legacy_pi ON legacy_pi.entry_id = e.id AND legacy_pi.meta_key = 'stripe_payment_intent_id'
			LEFT JOIN %i gw ON gw.entry_id = e.id AND gw.meta_key = 'payment_gateway'
			LEFT JOIN %i amt ON amt.entry_id = e.id AND amt.meta_key = 'payment_amount'
			LEFT JOIN %i legacy_amt ON legacy_amt.entry_id = e.id AND legacy_amt.meta_key = 'stripe_amount'
			WHERE (txn.meta_value IS NOT NULL OR legacy_pi.meta_value IS NOT NULL) {$date_where}
		";

		// HAVING (not WHERE) for the gateway filter since it filters on the
		// COALESCE'd alias, which WHERE can't reference in standard SQL.
		$count_params = array_merge( $join_identifiers, $date_params, $gateway_params );
		$total        = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM ( SELECT e.id {$joins} GROUP BY e.id {$gateway_where} ) t", $count_params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		$pages  = max( 1, (int) ceil( $total / $per_page ) );
		$page   = min( max( 1, $page ), $pages );
		$offset = ( $page - 1 ) * $per_page;

		$rows = $wpdb->get_results( $wpdb->prepare( "
			SELECT e.id, e.form_id, e.created_at,
			       COALESCE(txn.meta_value, legacy_pi.meta_value) AS payment_intent_id,
			       COALESCE(gw.meta_value, 'stripe') AS payment_gateway,
			       COALESCE(amt.meta_value, legacy_amt.meta_value) AS amount
			{$joins}
			GROUP BY e.id
			{$gateway_where}
			ORDER BY e.created_at DESC
			LIMIT %d OFFSET %d
		", array_merge( $join_identifiers, $date_params, $gateway_params, [ $per_page, $offset ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.

		return [
			'items'    => $rows ?: [],
			'total'    => $total,
			'pages'    => $pages,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/**
	 * Sum of every captured payment amount. Optionally restricted to a date
	 * range and/or a single gateway. Same COALESCE-against-legacy-columns
	 * reasoning as payment_transactions().
	 *
	 * Real bug found auditing Reports (2026-08-18): the Reports "Total
	 * Collected" tile called this with no gateway filter, silently summing
	 * Stripe and PayPal amounts together, then labelled the number with the
	 * Stripe currency setting alone — wrong whenever the two gateways use
	 * different currencies (Stripe in USD + PayPal in EUR would report a
	 * meaningless combined figure as "USD"). `$gateway` lets a caller get a
	 * correct, single-currency total per gateway instead of a conflated one.
	 */
	public static function payment_total( $date_from = '', $date_to = '', $gateway = '' ) {
		global $wpdb;
		$meta    = self::meta_table();
		$entries = self::table();

		$where  = "(amt.meta_value IS NOT NULL OR legacy_amt.meta_value IS NOT NULL)";
		$params = [];
		if ( $date_from ) {
			$where   .= ' AND e.created_at >= %s';
			$params[] = gmdate( 'Y-m-d 00:00:00', strtotime( $date_from ) );
		}
		if ( $date_to ) {
			$where   .= ' AND e.created_at <= %s';
			$params[] = gmdate( 'Y-m-d 23:59:59', strtotime( $date_to ) );
		}

		// %i identifiers in the exact order they appear in $sql below: entries
		// (FROM), meta (amt JOIN), meta (legacy_amt JOIN), then meta again
		// (gw JOIN) only when the gateway filter is active — must precede
		// $params (which start with the WHERE clause's own %s values).
		$identifiers = [ $entries, $meta, $meta ];

		$gateway_join  = '';
		$gateway_where = '';
		if ( $gateway ) {
			// Legacy rows have no `payment_gateway` meta at all — they're always
			// Stripe (PayPal didn't exist when they were written), matching the
			// same COALESCE convention Entry::payment_transactions() already uses.
			$gateway_join  = "LEFT JOIN %i gw ON gw.entry_id = e.id AND gw.meta_key = 'payment_gateway'";
			$gateway_where = ' AND COALESCE(gw.meta_value, \'stripe\') = %s';
			$identifiers[] = $meta;
			$params[]      = sanitize_key( $gateway );
		}

		return (float) $wpdb->get_var( $wpdb->prepare( "
			SELECT SUM(CAST(COALESCE(amt.meta_value, legacy_amt.meta_value) AS DECIMAL(10,2)))
			FROM %i e
			LEFT JOIN %i amt ON amt.entry_id = e.id AND amt.meta_key = 'payment_amount'
			LEFT JOIN %i legacy_amt ON legacy_amt.entry_id = e.id AND legacy_amt.meta_key = 'stripe_amount'
			{$gateway_join}
			WHERE {$where}{$gateway_where}
		", array_merge( $identifiers, $params ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
	}

	/** Daily captured-payment totals for the last $days days, oldest first — mirrors counts_by_day() but sums the payment amount instead of counting rows. Same legacy-column COALESCE as payment_total(). */
	public static function payment_totals_by_day( $days = 14 ) {
		global $wpdb;
		$meta    = self::meta_table();
		$entries = self::table();
		$since   = gmdate( 'Y-m-d 00:00:00', strtotime( '-' . ( $days - 1 ) . ' days' ) );

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table, no core API exists for it; per-request freshness needed.
			$wpdb->prepare(
				"SELECT DATE(e.created_at) as day, SUM(CAST(COALESCE(amt.meta_value, legacy_amt.meta_value) AS DECIMAL(10,2))) as total
				 FROM %i e
				 LEFT JOIN %i amt ON amt.entry_id = e.id AND amt.meta_key = 'payment_amount'
				 LEFT JOIN %i legacy_amt ON legacy_amt.entry_id = e.id AND legacy_amt.meta_key = 'stripe_amount'
				 WHERE (amt.meta_value IS NOT NULL OR legacy_amt.meta_value IS NOT NULL) AND e.created_at >= %s
				 GROUP BY DATE(e.created_at)",
				$entries,
				$meta,
				$meta,
				$since
			),
			ARRAY_A
		);

		$by_day = [];
		foreach ( $rows ?: [] as $row ) {
			$by_day[ $row['day'] ] = (float) $row['total'];
		}

		$series = [];
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$day            = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) );
			$series[ $day ] = $by_day[ $day ] ?? 0.0;
		}

		return $series;
	}

	private static function hydrate( array $row ) {
		$row['values'] = json_decode( $row['response'] ?? '', true ) ?: [];
		unset( $row['response'] );
		return $row;
	}

	private static function client_ip() {
		if ( ! empty( $_SERVER['REMOTE_ADDR'] ) ) {
			return sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) );
		}
		return '';
	}
}