<?php
/**
 * Form data-access model.
 *
 * Static CRUD methods for forms, fields, submissions, and submission values.
 *
 * @package GoValid_QR
 */

defined( 'ABSPATH' ) || exit;

class GoValid_Form_Model {

	/* ------------------------------------------------------------------
	 * Table helpers
	 * ----------------------------------------------------------------*/

	public static function forms_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'govalid_forms';
	}

	public static function fields_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'govalid_form_fields';
	}

	public static function submissions_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'govalid_form_submissions';
	}

	public static function values_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'govalid_form_submission_values';
	}

	/* ------------------------------------------------------------------
	 * Schema creation (called from activator)
	 * ----------------------------------------------------------------*/

	public static function create_tables(): void {
		global $wpdb;
		$charset = $wpdb->get_charset_collate();

		$forms = self::forms_table();
		$fields = self::fields_table();
		$submissions = self::submissions_table();
		$values = self::values_table();

		$sql = "CREATE TABLE {$forms} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			title varchar(255) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'draft',
			settings longtext DEFAULT NULL,
			author_id bigint(20) unsigned NOT NULL DEFAULT 0,
			submission_count bigint(20) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY status (status),
			KEY author_id (author_id)
		) {$charset};

		CREATE TABLE {$fields} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id bigint(20) unsigned NOT NULL,
			type varchar(50) NOT NULL DEFAULT 'text',
			label varchar(255) NOT NULL DEFAULT '',
			placeholder varchar(255) DEFAULT '',
			options longtext DEFAULT NULL,
			validation longtext DEFAULT NULL,
			conditional_logic longtext DEFAULT NULL,
			field_order int(11) NOT NULL DEFAULT 0,
			required tinyint(1) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY form_id (form_id),
			KEY field_order (field_order)
		) {$charset};

		CREATE TABLE {$submissions} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			form_id bigint(20) unsigned NOT NULL,
			user_id bigint(20) unsigned DEFAULT NULL,
			ip_address varchar(45) DEFAULT '',
			user_agent text DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY form_id (form_id),
			KEY created_at (created_at)
		) {$charset};

		CREATE TABLE {$values} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			submission_id bigint(20) unsigned NOT NULL,
			field_id bigint(20) unsigned NOT NULL DEFAULT 0,
			field_label varchar(255) NOT NULL DEFAULT '',
			field_value longtext DEFAULT NULL,
			PRIMARY KEY (id),
			KEY submission_id (submission_id),
			KEY field_id (field_id)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Drop all plugin tables (used during uninstall).
	 */
	public static function drop_tables(): void {
		global $wpdb;
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}govalid_form_submission_values" ); // phpcs:ignore
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}govalid_form_submissions" ); // phpcs:ignore
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}govalid_form_fields" ); // phpcs:ignore
		$wpdb->query( "DROP TABLE IF EXISTS {$wpdb->prefix}govalid_forms" ); // phpcs:ignore
	}

	/* ------------------------------------------------------------------
	 * Forms CRUD
	 * ----------------------------------------------------------------*/

	/**
	 * List forms with pagination.
	 */
	public static function list_forms( int $page = 1, int $per_page = 20, string $status = '' ): array {
		global $wpdb;
		$table  = self::forms_table();
		$where  = '1=1';
		$params = array();

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

		$offset  = ( $page - 1 ) * $per_page;
		$params[] = $per_page;
		$params[] = $offset;

		$sql   = "SELECT * FROM {$table} WHERE {$where} ORDER BY updated_at DESC LIMIT %d OFFSET %d";
		$items = $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore

		$count_sql = "SELECT COUNT(*) FROM {$table} WHERE " . ( $status ? $wpdb->prepare( 'status = %s', $status ) : '1=1' );
		$total     = (int) $wpdb->get_var( $count_sql ); // phpcs:ignore

		return array(
			'items'    => $items ?: array(),
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
			'pages'    => (int) ceil( $total / max( $per_page, 1 ) ),
		);
	}

	/**
	 * Get a single form by ID.
	 */
	public static function get_form( int $id ) {
		global $wpdb;
		$table = self::forms_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$table} WHERE id = %d",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Count total forms.
	 */
	public static function count_forms(): int {
		global $wpdb;
		$table = esc_sql( self::forms_table() );
		// Table name is always $wpdb->prefix + a hardcoded suffix — no user input involved.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Create a form. Returns the new form ID.
	 */
	public static function create_form( array $data ): int {
		global $wpdb;
		$wpdb->insert( self::forms_table(), array( // phpcs:ignore
			'title'     => sanitize_text_field( $data['title'] ?? '' ),
			'status'    => sanitize_key( $data['status'] ?? 'draft' ),
			'settings'  => wp_json_encode( $data['settings'] ?? new \stdClass() ),
			'author_id' => get_current_user_id(),
		) );
		return (int) $wpdb->insert_id;
	}

	/**
	 * Update a form.
	 */
	public static function update_form( int $id, array $data ): bool {
		global $wpdb;
		$update = array( 'updated_at' => current_time( 'mysql' ) );

		if ( isset( $data['title'] ) ) {
			$update['title'] = sanitize_text_field( $data['title'] );
		}
		if ( isset( $data['status'] ) ) {
			$update['status'] = sanitize_key( $data['status'] );
		}
		if ( isset( $data['settings'] ) ) {
			$update['settings'] = wp_json_encode( $data['settings'] );
		}

		return false !== $wpdb->update( self::forms_table(), $update, array( 'id' => $id ) ); // phpcs:ignore
	}

	/**
	 * Delete a form and all its fields / submissions.
	 */
	public static function delete_form( int $id ): bool {
		global $wpdb;
		$val_table = self::values_table();
		$sub_table = self::submissions_table();

		// Delete submission values via subquery.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare(
			"DELETE v FROM {$val_table} v INNER JOIN {$sub_table} s ON v.submission_id = s.id WHERE s.form_id = %d",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Delete submissions.
		$wpdb->delete( self::submissions_table(), array( 'form_id' => $id ) ); // phpcs:ignore

		// Delete fields.
		$wpdb->delete( self::fields_table(), array( 'form_id' => $id ) ); // phpcs:ignore

		// Delete form.
		return false !== $wpdb->delete( self::forms_table(), array( 'id' => $id ) ); // phpcs:ignore
	}

	/**
	 * Duplicate a form and all its fields. Returns the new form ID, or 0 on failure.
	 */
	public static function duplicate_form( int $id ): int {
		global $wpdb;

		$form = self::get_form( $id );
		if ( ! $form ) {
			return 0;
		}

		$settings = json_decode( $form->settings ?? '{}', true ) ?: array();

		$new_id = self::create_form( array(
			'title'    => sprintf(
				/* translators: %s: original form title */
				__( 'Copy of %s', 'govalid-qr-validator' ),
				$form->title
			),
			'status'   => 'draft',
			'settings' => $settings,
		) );

		if ( ! $new_id ) {
			return 0;
		}

		$fields = self::get_fields( $id );
		$order  = 0;
		foreach ( $fields as $field ) {
			$wpdb->insert( self::fields_table(), array( // phpcs:ignore
				'form_id'           => $new_id,
				'type'              => $field->type,
				'label'             => $field->label,
				'placeholder'       => $field->placeholder,
				'options'           => $field->options,
				'validation'        => $field->validation,
				'conditional_logic' => $field->conditional_logic,
				'field_order'       => $order,
				'required'          => $field->required,
			) );
			$order++;
		}

		return $new_id;
	}

	/* ------------------------------------------------------------------
	 * Fields CRUD
	 * ----------------------------------------------------------------*/

	/**
	 * Get all fields for a form (ordered).
	 */
	public static function get_fields( int $form_id ): array {
		global $wpdb;
		$table = self::fields_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} WHERE form_id = %d ORDER BY field_order ASC",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $rows ?: array();
	}

	/**
	 * Bulk save fields for a form. Replaces all existing fields.
	 *
	 * @param int   $form_id Form ID.
	 * @param array $fields  Array of field data arrays.
	 */
	public static function save_fields( int $form_id, array $fields ): void {
		global $wpdb;

		// Delete existing fields.
		$wpdb->delete( self::fields_table(), array( 'form_id' => $form_id ) ); // phpcs:ignore

		$order = 0;
		foreach ( $fields as $field ) {
			$wpdb->insert( self::fields_table(), array( // phpcs:ignore
				'form_id'           => $form_id,
				'type'              => sanitize_key( $field['type'] ?? 'text' ),
				'label'             => sanitize_text_field( $field['label'] ?? '' ),
				'placeholder'       => sanitize_text_field( $field['placeholder'] ?? '' ),
				'options'           => wp_json_encode( $field['options'] ?? null ),
				'validation'        => wp_json_encode( $field['validation'] ?? null ),
				'conditional_logic' => wp_json_encode( $field['conditional_logic'] ?? null ),
				'field_order'       => $order,
				'required'          => ! empty( $field['required'] ) ? 1 : 0,
			) );
			$order++;
		}
	}

	/* ------------------------------------------------------------------
	 * Submissions CRUD
	 * ----------------------------------------------------------------*/

	/**
	 * List submissions for a form.
	 */
	public static function list_submissions( int $form_id, int $page = 1, int $per_page = 20 ): array {
		global $wpdb;
		$table  = self::submissions_table();
		$offset = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$items = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} WHERE form_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d",
			$form_id,
			$per_page,
			$offset
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE form_id = %d",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array(
			'items'    => $items ?: array(),
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
			'pages'    => (int) ceil( $total / max( $per_page, 1 ) ),
		);
	}

	/**
	 * Get a single submission with its values.
	 */
	public static function get_submission( int $id ) {
		global $wpdb;

		$sub_table = self::submissions_table();
		$val_table = self::values_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$submission = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$sub_table} WHERE id = %d",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! $submission ) {
			return null;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$submission->values = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$val_table} WHERE submission_id = %d ORDER BY id ASC",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $submission;
	}

	/**
	 * Create a submission with values. Returns submission ID.
	 */
	public static function create_submission( int $form_id, array $values_data, array $meta = array() ): int {
		global $wpdb;

		$wpdb->insert( self::submissions_table(), array( // phpcs:ignore
			'form_id'    => $form_id,
			'user_id'    => $meta['user_id'] ?? null,
			'ip_address' => $meta['ip_address'] ?? '',
			'user_agent' => $meta['user_agent'] ?? '',
		) );

		$submission_id = (int) $wpdb->insert_id;

		foreach ( $values_data as $val ) {
			$wpdb->insert( self::values_table(), array( // phpcs:ignore
				'submission_id' => $submission_id,
				'field_id'      => (int) ( $val['field_id'] ?? 0 ),
				'field_label'   => sanitize_text_field( $val['field_label'] ?? '' ),
				'field_value'   => sanitize_text_field( $val['field_value'] ?? '' ),
			) );
		}

		// Increment submission count on form.
		$forms_table = self::forms_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare(
			"UPDATE {$forms_table} SET submission_count = submission_count + 1 WHERE id = %d",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $submission_id;
	}

	/**
	 * Delete a submission and its values.
	 */
	public static function delete_submission( int $id ): bool {
		global $wpdb;

		$sub_table = self::submissions_table();

		// Get form_id first to decrement counter.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$submission = $wpdb->get_row( $wpdb->prepare(
			"SELECT form_id FROM {$sub_table} WHERE id = %d",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Delete values.
		$wpdb->delete( self::values_table(), array( 'submission_id' => $id ) ); // phpcs:ignore

		// Delete submission.
		$result = $wpdb->delete( self::submissions_table(), array( 'id' => $id ) ); // phpcs:ignore

		// Decrement counter.
		if ( $result && $submission ) {
			$forms_table = self::forms_table();
			// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( $wpdb->prepare(
				"UPDATE {$forms_table} SET submission_count = GREATEST(submission_count - 1, 0) WHERE id = %d",
				(int) $submission->form_id
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		return false !== $result;
	}

	/**
	 * Get comprehensive statistics for a form.
	 *
	 * @param int $form_id Form ID.
	 * @return array Statistics data.
	 */
	public static function get_statistics( int $form_id ): array {
		global $wpdb;

		$sub_table = self::submissions_table();
		$val_table = self::values_table();
		$fld_table = self::fields_table();

		// --- Summary counts ---
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$sub_table} WHERE form_id = %d",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$today = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$sub_table} WHERE form_id = %d AND DATE(created_at) = CURDATE()",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$this_week = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$sub_table} WHERE form_id = %d AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$this_month = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$sub_table} WHERE form_id = %d AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// --- Timeline (daily counts, last 30 days) ---
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$timeline_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT DATE(created_at) AS day, COUNT(*) AS count
			 FROM {$sub_table} WHERE form_id = %d AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
			 GROUP BY DATE(created_at) ORDER BY day ASC",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Fill in missing days with 0.
		$timeline = array();
		$start    = new \DateTime( '-29 days' );
		$end      = new \DateTime( 'tomorrow' );
		$counts   = array();
		foreach ( $timeline_rows as $row ) {
			$counts[ $row->day ] = (int) $row->count;
		}
		$period = new \DatePeriod( $start, new \DateInterval( 'P1D' ), $end );
		foreach ( $period as $date ) {
			$d = $date->format( 'Y-m-d' );
			$timeline[] = array(
				'date'  => $d,
				'count' => $counts[ $d ] ?? 0,
			);
		}

		// --- Field breakdowns (categorical fields) ---
		$categorical_types = array( 'select', 'radio', 'checkbox', 'gender', 'rating' );
		$fields = self::get_fields( $form_id );

		$field_stats = array();
		foreach ( $fields as $field ) {
			if ( ! in_array( $field->type, $categorical_types, true ) ) {
				continue;
			}

			// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$distribution = $wpdb->get_results( $wpdb->prepare(
				"SELECT v.field_value AS val, COUNT(*) AS count
				 FROM {$val_table} v
				 INNER JOIN {$sub_table} s ON v.submission_id = s.id
				 WHERE s.form_id = %d AND v.field_id = %d AND v.field_value != ''
				 GROUP BY v.field_value ORDER BY count DESC LIMIT 20",
				$form_id, $field->id
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( $distribution ) {
				$field_stats[] = array(
					'field_id'    => (int) $field->id,
					'field_label' => $field->label,
					'field_type'  => $field->type,
					'values'      => array_map( function ( $r ) {
						return array( 'label' => $r->val, 'count' => (int) $r->count );
					}, $distribution ),
				);
			}
		}

		// --- Top email domains (if email field exists) ---
		$email_stats = array();
		foreach ( $fields as $field ) {
			if ( 'email' !== $field->type ) {
				continue;
			}
			$like_email = '%' . $wpdb->esc_like( '@' ) . '%';
			// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$domains = $wpdb->get_results( $wpdb->prepare(
				"SELECT SUBSTRING_INDEX(v.field_value, '@', -1) AS domain, COUNT(*) AS count
				 FROM {$val_table} v
				 INNER JOIN {$sub_table} s ON v.submission_id = s.id
				 WHERE s.form_id = %d AND v.field_id = %d AND v.field_value LIKE %s
				 GROUP BY domain ORDER BY count DESC LIMIT 10",
				$form_id, $field->id, $like_email
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( $domains ) {
				$email_stats[] = array(
					'field_label' => $field->label,
					'domains'     => array_map( function ( $r ) {
						return array( 'label' => $r->domain, 'count' => (int) $r->count );
					}, $domains ),
				);
			}
			break; // Only first email field.
		}

		// --- Device / Browser stats (from user_agent) ---
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$agents = $wpdb->get_results( $wpdb->prepare(
			"SELECT user_agent, COUNT(*) AS count
			 FROM {$sub_table} WHERE form_id = %d AND user_agent IS NOT NULL AND user_agent != ''
			 GROUP BY user_agent ORDER BY count DESC",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$browsers = array();
		$devices  = array();
		foreach ( $agents as $row ) {
			$ua    = $row->user_agent;
			$cnt   = (int) $row->count;
			$bname = self::parse_browser( $ua );
			$dtype = self::parse_device( $ua );

			$browsers[ $bname ] = ( $browsers[ $bname ] ?? 0 ) + $cnt;
			$devices[ $dtype ]  = ( $devices[ $dtype ] ?? 0 ) + $cnt;
		}

		arsort( $browsers );
		arsort( $devices );

		$browser_stats = array();
		foreach ( array_slice( $browsers, 0, 8, true ) as $name => $cnt ) {
			$browser_stats[] = array( 'label' => $name, 'count' => $cnt );
		}

		$device_stats = array();
		foreach ( array_slice( $devices, 0, 5, true ) as $name => $cnt ) {
			$device_stats[] = array( 'label' => $name, 'count' => $cnt );
		}

		// --- Hourly distribution (peak hours) ---
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$hourly = $wpdb->get_results( $wpdb->prepare(
			"SELECT HOUR(created_at) AS hour, COUNT(*) AS count
			 FROM {$sub_table} WHERE form_id = %d
			 GROUP BY HOUR(created_at) ORDER BY hour ASC",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$hourly_map = array_fill( 0, 24, 0 );
		foreach ( $hourly as $row ) {
			$hourly_map[ (int) $row->hour ] = (int) $row->count;
		}
		$hourly_stats = array();
		for ( $h = 0; $h < 24; $h++ ) {
			$hourly_stats[] = array(
				'hour'  => $h,
				'label' => sprintf( '%02d:00', $h ),
				'count' => $hourly_map[ $h ],
			);
		}

		return array(
			'summary'       => array(
				'total'      => $total,
				'today'      => $today,
				'this_week'  => $this_week,
				'this_month' => $this_month,
			),
			'timeline'      => $timeline,
			'field_stats'   => $field_stats,
			'email_stats'   => $email_stats,
			'browsers'      => $browser_stats,
			'devices'       => $device_stats,
			'hourly'        => $hourly_stats,
		);
	}

	/**
	 * Parse browser name from user agent string.
	 */
	private static function parse_browser( string $ua ): string {
		if ( preg_match( '/Edg[e\/]/i', $ua ) )   return 'Edge';
		if ( preg_match( '/OPR|Opera/i', $ua ) )   return 'Opera';
		if ( preg_match( '/Chrome/i', $ua ) )       return 'Chrome';
		if ( preg_match( '/Firefox/i', $ua ) )      return 'Firefox';
		if ( preg_match( '/Safari/i', $ua ) )       return 'Safari';
		if ( preg_match( '/MSIE|Trident/i', $ua ) ) return 'IE';
		return 'Other';
	}

	/**
	 * Parse device type from user agent string.
	 */
	private static function parse_device( string $ua ): string {
		if ( preg_match( '/Mobile|Android.*Mobile|iPhone|iPod/i', $ua ) ) return 'Mobile';
		if ( preg_match( '/iPad|Android(?!.*Mobile)|Tablet/i', $ua ) )   return 'Tablet';
		return 'Desktop';
	}

	/**
	 * Export submissions as array (for CSV).
	 */
	public static function export_submissions( int $form_id ): array {
		global $wpdb;

		$fields = self::get_fields( $form_id );
		$sub_table = self::submissions_table();
		$val_table = self::values_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$submissions = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$sub_table} WHERE form_id = %d ORDER BY created_at ASC",
			$form_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! $submissions ) {
			return array();
		}

		$rows = array();
		foreach ( $submissions as $sub ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$values = $wpdb->get_results( $wpdb->prepare(
				"SELECT field_label, field_value FROM {$val_table} WHERE submission_id = %d ORDER BY id ASC",
				$sub->id
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			$row = array(
				'id'         => $sub->id,
				'created_at' => $sub->created_at,
				'ip_address' => $sub->ip_address,
			);
			foreach ( $values as $v ) {
				$row[ $v->field_label ] = $v->field_value;
			}
			$rows[] = $row;
		}

		return $rows;
	}
}
