<?php
/**
 * Built-in form field repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Forms;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom form field tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes fields for built-in Submission Inbox forms.
 */
class FormFieldRepository {
	const TYPE_TEXT     = 'text';
	const TYPE_EMAIL    = 'email';
	const TYPE_PHONE    = 'phone';
	const TYPE_DATE     = 'date';
	const TYPE_TEXTAREA = 'textarea';
	const TYPE_SELECT   = 'select';
	const TYPE_RADIO    = 'radio';
	const TYPE_CHECKBOX = 'checkbox';
	const TYPE_CONSENT  = 'consent';
	const TYPE_HIDDEN   = 'hidden';

	/**
	 * Create a field.
	 *
	 * @param int                 $form_id Form id.
	 * @param array<string,mixed> $data Field data.
	 * @return int
	 */
	public function create( $form_id, array $data ) {
		global $wpdb;

		$form_id = absint( $form_id );
		if ( $form_id <= 0 ) {
			return 0;
		}

		$table = $wpdb->prefix . 'subin_form_fields';
		$now   = current_time( 'mysql' );
		$data  = $this->sanitize_data( $data );

		if ( '' === $data['field_key'] ) {
			$data['field_key'] = 'field_' . absint( $data['sort_order'] );
		}
		$data['field_key'] = $this->generate_unique_field_key( $form_id, $data['field_key'] );

		$inserted = $wpdb->insert(
			$table,
			array(
				'form_id'       => $form_id,
				'field_type'    => $data['field_type'],
				'field_key'     => $data['field_key'],
				'field_label'   => $data['field_label'],
				'placeholder'   => $data['placeholder'],
				'help_text'     => $data['help_text'],
				'is_required'   => $data['is_required'],
				'field_options' => $data['field_options'],
				'sort_order'    => $data['sort_order'],
				'created_at'    => $now,
				'updated_at'    => $now,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s', '%s' )
		);

		return $inserted ? absint( $wpdb->insert_id ) : 0;
	}

	/**
	 * Update a field.
	 *
	 * @param int                 $field_id Field id.
	 * @param array<string,mixed> $data Field data.
	 * @return bool
	 */
	public function update( $field_id, array $data ) {
		$field_id = absint( $field_id );
		if ( $field_id <= 0 ) {
			return false;
		}

		$field = $this->get( $field_id );
		if ( ! $field ) {
			return false;
		}

		return $this->update_for_form( absint( $field['form_id'] ), $field_id, $data );
	}

	/**
	 * Update a field only when it belongs to the expected form.
	 *
	 * @param int                 $form_id Form id.
	 * @param int                 $field_id Field id.
	 * @param array<string,mixed> $data Field data.
	 * @return bool
	 */
	public function update_for_form( $form_id, $field_id, array $data ) {
		global $wpdb;

		$form_id  = absint( $form_id );
		$field_id = absint( $field_id );
		if ( $form_id <= 0 || $field_id <= 0 ) {
			return false;
		}

		$field = $this->get_for_form( $form_id, $field_id );
		if ( ! $field ) {
			return false;
		}

		$data = $this->sanitize_data( $data );
		if ( '' === $data['field_key'] ) {
			return false;
		}
		$data['field_key'] = $this->generate_unique_field_key( $form_id, $data['field_key'], $field_id );

		$updated = $wpdb->update(
			$wpdb->prefix . 'subin_form_fields',
			array(
				'field_type'    => $data['field_type'],
				'field_key'     => $data['field_key'],
				'field_label'   => $data['field_label'],
				'placeholder'   => $data['placeholder'],
				'help_text'     => $data['help_text'],
				'is_required'   => $data['is_required'],
				'field_options' => $data['field_options'],
				'sort_order'    => $data['sort_order'],
				'updated_at'    => current_time( 'mysql' ),
			),
			array(
				'id'      => $field_id,
				'form_id' => $form_id,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%d', '%s' ),
			array( '%d', '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Get a field by id.
	 *
	 * @param int $field_id Field id.
	 * @return array<string,mixed>|null
	 */
	public function get( $field_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_form_fields';
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $field_id ) ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Get a field only when it belongs to the expected form.
	 *
	 * @param int $form_id Form id.
	 * @param int $field_id Field id.
	 * @return array<string,mixed>|null
	 */
	public function get_for_form( $form_id, $field_id ) {
		global $wpdb;

		$form_id  = absint( $form_id );
		$field_id = absint( $field_id );
		if ( $form_id <= 0 || $field_id <= 0 ) {
			return null;
		}

		$table = $wpdb->prefix . 'subin_form_fields';
		$row   = $wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is built only from the trusted WordPress prefix; IDs use placeholders.
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The concatenated identifier is internal; both IDs remain placeholders.
				'SELECT * FROM ' . $table . ' WHERE id = %d AND form_id = %d',
				$field_id,
				$form_id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Get fields by form id.
	 *
	 * @param int $form_id Form id.
	 * @return array<int,array<string,mixed>>
	 */
	public function get_by_form_id( $form_id ) {
		global $wpdb;

		$table   = $wpdb->prefix . 'subin_form_fields';
		$results = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE form_id = %d ORDER BY sort_order ASC, id ASC", absint( $form_id ) ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

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

	/**
	 * Get and lock all fields for a form in the active transaction.
	 *
	 * @param int $form_id Form id.
	 * @return array<int,array<string,mixed>>|\WP_Error
	 */
	public function get_by_form_id_for_update( $form_id ) {
		global $wpdb;

		$form_id = absint( $form_id );
		if ( $form_id <= 0 ) {
			return array();
		}

		$table   = $wpdb->prefix . 'subin_form_fields';
		$results = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The table name is built only from the trusted WordPress prefix; the form ID uses a placeholder.
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The concatenated identifier is internal; the form ID remains a placeholder.
				'SELECT * FROM ' . $table . ' WHERE form_id = %d ORDER BY sort_order ASC, id ASC FOR UPDATE',
				$form_id
			),
			ARRAY_A
		);

		if ( '' !== (string) $wpdb->last_error ) {
			return new \WP_Error(
				'subin_form_field_lock_failed',
				__( 'Form fields could not be locked.', 'submission-inbox' ),
				array( 'status' => 500 )
			);
		}

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

	/**
	 * Replace all fields for a form.
	 *
	 * @param int              $form_id Form id.
	 * @param array<int,array> $fields Fields.
	 * @return bool
	 */
	public function replace_for_form( $form_id, array $fields ) {
		global $wpdb;

		$form_id = absint( $form_id );
		if ( $form_id <= 0 ) {
			return false;
		}

		if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
			return false;
		}

		if ( ! $this->delete_by_form_id( $form_id ) ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		foreach ( array_values( $fields ) as $index => $field ) {
			if ( ! isset( $field['sort_order'] ) ) {
				$field['sort_order'] = $index;
			}

			if ( 0 === $this->create( $form_id, $field ) ) {
				$wpdb->query( 'ROLLBACK' );
				return false;
			}
		}

		if ( false === $wpdb->query( 'COMMIT' ) ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		return true;
	}

	/**
	 * Delete a field.
	 *
	 * @param int $field_id Field id.
	 * @return bool
	 */
	public function delete( $field_id ) {
		global $wpdb;

		return false !== $wpdb->delete( $wpdb->prefix . 'subin_form_fields', array( 'id' => absint( $field_id ) ), array( '%d' ) );
	}

	/**
	 * Delete a field only when it belongs to the expected form.
	 *
	 * @param int $form_id Form id.
	 * @param int $field_id Field id.
	 * @return bool
	 */
	public function delete_for_form( $form_id, $field_id ) {
		global $wpdb;

		$form_id  = absint( $form_id );
		$field_id = absint( $field_id );
		if ( $form_id <= 0 || $field_id <= 0 ) {
			return false;
		}

		return false !== $wpdb->delete(
			$wpdb->prefix . 'subin_form_fields',
			array(
				'id'      => $field_id,
				'form_id' => $form_id,
			),
			array( '%d', '%d' )
		);
	}

	/**
	 * Delete fields by form id.
	 *
	 * @param int $form_id Form id.
	 * @return bool
	 */
	public function delete_by_form_id( $form_id ) {
		global $wpdb;

		return false !== $wpdb->delete( $wpdb->prefix . 'subin_form_fields', array( 'form_id' => absint( $form_id ) ), array( '%d' ) );
	}

	/**
	 * Copy fields from one form to another.
	 *
	 * @param int $source_form_id Source form id.
	 * @param int $target_form_id Target form id.
	 * @return bool
	 */
	public function copy_fields( $source_form_id, $target_form_id ) {
		return ! is_wp_error( $this->copy_fields_with_map( $source_form_id, $target_form_id ) );
	}

	/**
	 * Copy fields and return their old-to-new id map.
	 *
	 * The caller owns the surrounding transaction.
	 *
	 * @param int $source_form_id Source form id.
	 * @param int $target_form_id Target form id.
	 * @return array<int,int>|\WP_Error
	 */
	public function copy_fields_with_map( $source_form_id, $target_form_id ) {
		$source_form_id = absint( $source_form_id );
		$target_form_id = absint( $target_form_id );

		if ( $source_form_id <= 0 || $target_form_id <= 0 ) {
			return new \WP_Error( 'subin_form_field_copy_invalid', __( 'Form fields could not be copied.', 'submission-inbox' ) );
		}

		$fields = $this->get_by_form_id_for_update( $source_form_id );
		if ( is_wp_error( $fields ) ) {
			return $fields;
		}
		if ( empty( $fields ) ) {
			return array();
		}

		$id_map = array();
		foreach ( $fields as $field ) {
			$source_id = absint( $field['id'] ?? 0 );
			$new_id    = $this->create( $target_form_id, $field );
			if ( $source_id <= 0 || $new_id <= 0 ) {
				return new \WP_Error( 'subin_form_field_copy_failed', __( 'Form fields could not be copied.', 'submission-inbox' ) );
			}
			$id_map[ $source_id ] = $new_id;
		}

		return $id_map;
	}

	/**
	 * Duplicate a field after the original.
	 *
	 * @param int $field_id Field id.
	 * @return int
	 */
	public function duplicate_field( $field_id ) {
		global $wpdb;

		$field = $this->get( $field_id );
		if ( ! $field ) {
			return 0;
		}

		$field['field_label'] = sprintf(
			/* translators: %s: Original field label. */
			__( '%s Copy', 'submission-inbox' ),
			$field['field_label']
		);
		$field['field_key']  = $this->generate_unique_field_key( absint( $field['form_id'] ), $field['field_key'] . '_copy' );
		$field['sort_order'] = absint( $field['sort_order'] ) + 1;

		if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
			return 0;
		}

		if ( false === $this->shift_sort_orders( absint( $field['form_id'] ), absint( $field['sort_order'] ) ) ) {
			$wpdb->query( 'ROLLBACK' );
			return 0;
		}

		$new_field_id = $this->create( absint( $field['form_id'] ), $field );
		if ( $new_field_id <= 0 || false === $wpdb->query( 'COMMIT' ) ) {
			$wpdb->query( 'ROLLBACK' );
			return 0;
		}

		return $new_field_id;
	}

	/**
	 * Move field up or down.
	 *
	 * @param int    $field_id Field id.
	 * @param string $direction Direction: up or down.
	 * @return bool
	 */
	public function move_field( $field_id, $direction ) {
		global $wpdb;

		$field = $this->get( $field_id );
		if ( ! $field ) {
			return false;
		}

		$direction = sanitize_key( $direction );
		if ( ! in_array( $direction, array( 'up', 'down' ), true ) ) {
			return false;
		}

		$operator = 'up' === $direction ? '<' : '>';
		$order    = 'up' === $direction ? 'DESC' : 'ASC';
		$table    = $wpdb->prefix . 'subin_form_fields';
		$neighbor = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE form_id = %d AND sort_order {$operator} %d ORDER BY sort_order {$order}, id {$order} LIMIT 1",
				absint( $field['form_id'] ),
				absint( $field['sort_order'] )
			),
			ARRAY_A
		);

		if ( ! is_array( $neighbor ) ) {
			return true;
		}

		if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
			return false;
		}

		$first = $wpdb->update(
			$table,
			array( 'sort_order' => absint( $neighbor['sort_order'] ) ),
			array( 'id' => absint( $field['id'] ) ),
			array( '%d' ),
			array( '%d' )
		);
		if ( false === $first ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		$second = $wpdb->update(
			$table,
			array( 'sort_order' => absint( $field['sort_order'] ) ),
			array( 'id' => absint( $neighbor['id'] ) ),
			array( '%d' ),
			array( '%d' )
		);
		if ( false === $second || false === $wpdb->query( 'COMMIT' ) ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		return true;
	}

	/**
	 * Reorder fields for a form.
	 *
	 * @param int        $form_id Form id.
	 * @param array<int> $field_ids Ordered field ids.
	 * @return bool
	 */
	public function reorder_fields( $form_id, array $field_ids ) {
		global $wpdb;

		$form_id = absint( $form_id );
		if ( $form_id <= 0 || empty( $field_ids ) ) {
			return false;
		}

		$existing = $this->get_by_form_id( $form_id );
		$lookup   = array();
		foreach ( $existing as $field ) {
			$lookup[ absint( $field['id'] ?? 0 ) ] = $field;
		}

		$ordered = array();
		foreach ( $field_ids as $field_id ) {
			$field_id = absint( $field_id );
			if ( isset( $lookup[ $field_id ] ) ) {
				$ordered[] = $field_id;
				unset( $lookup[ $field_id ] );
			}
		}

		foreach ( $lookup as $field_id => $field ) {
			$ordered[] = absint( $field_id );
		}

		if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
			return false;
		}

		$table = $wpdb->prefix . 'subin_form_fields';
		foreach ( $ordered as $index => $field_id ) {
			$updated = $wpdb->update(
				$table,
				array(
					'sort_order' => ( $index + 1 ) * 10,
					'updated_at' => current_time( 'mysql' ),
				),
				array(
					'id'      => absint( $field_id ),
					'form_id' => $form_id,
				),
				array( '%d', '%s' ),
				array( '%d', '%d' )
			);
			if ( false === $updated ) {
				$wpdb->query( 'ROLLBACK' );
				return false;
			}
		}

		if ( false === $wpdb->query( 'COMMIT' ) ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		return true;
	}

	/**
	 * Generate a unique field key within a form.
	 *
	 * @param int    $form_id Form id.
	 * @param string $field_key Base key.
	 * @param int    $exclude_id Field id to exclude.
	 * @return string
	 */
	public function generate_unique_field_key( $form_id, $field_key, $exclude_id = 0 ) {
		$base_key   = sanitize_key( $field_key );
		$base_key   = $base_key ? $base_key : 'field';
		$key        = $base_key;
		$suffix     = 2;
		$form_id    = absint( $form_id );
		$exclude_id = absint( $exclude_id );

		while ( $this->field_key_exists( $form_id, $key, $exclude_id ) ) {
			$key = $base_key . '_' . $suffix;
			++$suffix;
		}

		return $key;
	}

	/**
	 * Check whether a field key exists within a form.
	 *
	 * @param int    $form_id Form id.
	 * @param string $field_key Field key.
	 * @param int    $exclude_id Field id to exclude.
	 * @return bool
	 */
	public function field_key_exists( $form_id, $field_key, $exclude_id = 0 ) {
		global $wpdb;

		$table      = $wpdb->prefix . 'subin_form_fields';
		$form_id    = absint( $form_id );
		$field_key  = sanitize_key( $field_key );
		$exclude_id = absint( $exclude_id );

		if ( $exclude_id > 0 ) {
			$count = $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND field_key = %s AND id != %d", $form_id, $field_key, $exclude_id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			);
		} else {
			$count = $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND field_key = %s", $form_id, $field_key ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			);
		}

		return absint( $count ) > 0;
	}

	/**
	 * Sanitize field type.
	 *
	 * @param string $type Field type.
	 * @return string
	 */
	public function sanitize_field_type( $type ) {
		$type = sanitize_key( $type );

		return in_array( $type, $this->get_field_types(), true ) ? $type : self::TYPE_TEXT;
	}

	/**
	 * Get supported field types.
	 *
	 * @return array<int,string>
	 */
	public function get_field_types() {
		return array(
			self::TYPE_TEXT,
			self::TYPE_EMAIL,
			self::TYPE_PHONE,
			self::TYPE_DATE,
			self::TYPE_TEXTAREA,
			self::TYPE_SELECT,
			self::TYPE_RADIO,
			self::TYPE_CHECKBOX,
			self::TYPE_CONSENT,
			self::TYPE_HIDDEN,
		);
	}

	/**
	 * Sanitize field data.
	 *
	 * @param array<string,mixed> $data Raw data.
	 * @return array<string,mixed>
	 */
	private function sanitize_data( array $data ) {
		$field_label = sanitize_text_field( $data['field_label'] ?? $data['label'] ?? '' );
		$field_key   = sanitize_key( $data['field_key'] ?? $data['key'] ?? '' );

		if ( '' === $field_key && '' !== $field_label ) {
			$field_key = sanitize_key( $field_label );
		}

		return array(
			'field_type'    => $this->sanitize_field_type( $data['field_type'] ?? $data['type'] ?? self::TYPE_TEXT ),
			'field_key'     => $field_key,
			'field_label'   => $field_label,
			'placeholder'   => sanitize_text_field( $data['placeholder'] ?? '' ),
			'help_text'     => sanitize_textarea_field( $data['help_text'] ?? '' ),
			'is_required'   => ! empty( $data['is_required'] ) || ! empty( $data['required'] ) ? 1 : 0,
			'field_options' => $this->sanitize_options( $data['field_options'] ?? $data['options'] ?? array() ),
			'sort_order'    => absint( $data['sort_order'] ?? 0 ),
		);
	}

	/**
	 * Sanitize selectable field options.
	 *
	 * @param mixed $options Options.
	 * @return string|null
	 */
	private function sanitize_options( $options ) {
		if ( is_string( $options ) ) {
			$decoded = json_decode( $options, true );
			$options = is_array( $decoded ) ? $decoded : array();
		}

		if ( ! is_array( $options ) || empty( $options ) ) {
			return null;
		}

		$sanitized = array();
		foreach ( $options as $option ) {
			if ( is_scalar( $option ) ) {
				$label = sanitize_text_field( (string) $option );
				$value = sanitize_key( $label );
			} elseif ( is_array( $option ) ) {
				$label = sanitize_text_field( $option['label'] ?? '' );
				$value = sanitize_key( $option['value'] ?? $label );
			} else {
				continue;
			}

			if ( '' === $label || '' === $value ) {
				continue;
			}

			$sanitized[] = array(
				'label' => $label,
				'value' => $value,
			);
		}

		if ( empty( $sanitized ) ) {
			return null;
		}

		$encoded = wp_json_encode( $sanitized );

		return is_string( $encoded ) ? $encoded : null;
	}

	/**
	 * Shift sort orders at or after an insertion point.
	 *
	 * @param int $form_id Form id.
	 * @param int $from_order Sort order.
	 * @return void
	 */
	private function shift_sort_orders( $form_id, $from_order ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_form_fields';
		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET sort_order = sort_order + 1 WHERE form_id = %d AND sort_order >= %d",
				absint( $form_id ),
				absint( $from_order )
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}
}
