<?php
/**
 * Syncs vehicle data to the custom specs table.
 *
 * Listens to `save_post_trx_ad_listing` and upserts taxonomy terms
 * plus meta-box field values into `{prefix}trx_ad_vehicle_specs`.
 *
 * @package ThemeREX\AutoDealer
 */

declare(strict_types=1);

namespace ThemeREX\AutoDealer\Database;

defined( 'ABSPATH' ) || exit;
use ThemeREX\AutoDealer\PostTypes\VehiclePostType;

class VehicleSpecsSync {

	/**
	 * Map taxonomy slugs to specs table column names.
	 *
	 * @var array<string, string>
	 */
	private const TAXONOMY_COLUMN_MAP = array(
		'trx_ad_make'         => 'make',
		'trx_ad_model'        => 'model',
		'trx_ad_body_style'   => 'body_style',
		'trx_ad_fuel_type'    => 'fuel_type',
		'trx_ad_transmission' => 'transmission',
		'trx_ad_drivetrain'   => 'drivetrain',
		'trx_ad_color'        => 'color_ext',
		'trx_ad_condition'    => 'condition_type',
		'trx_ad_vehicle_type' => 'vehicle_type',
	);

	/**
	 * Spec fields that come from $_POST (meta box inputs).
	 * Maps POST field name (with _trx_ad_ prefix) to column name.
	 *
	 * @var array<string, array{column: string, type: string}>
	 */
	private const POST_FIELD_MAP = array(
		'_trx_ad_vin'                   => array(
			'column' => 'vin',
			'type'   => 'string',
		),
		'_trx_ad_stock_number'          => array(
			'column' => 'stock_number',
			'type'   => 'string',
		),
		'_trx_ad_status'                => array(
			'column' => 'status',
			'type'   => 'enum',
		),
		'_trx_ad_year'                  => array(
			'column' => 'year',
			'type'   => 'int',
		),
		'_trx_ad_trim'                  => array(
			'column' => 'trim',
			'type'   => 'string',
		),
		'_trx_ad_mileage'               => array(
			'column' => 'mileage',
			'type'   => 'int',
		),
		'_trx_ad_price'                 => array(
			'column' => 'price',
			'type'   => 'decimal',
		),
		'_trx_ad_sale_price'            => array(
			'column' => 'sale_price',
			'type'   => 'decimal',
		),
		'_trx_ad_price_on_request'      => array(
			'column' => 'price_on_request',
			'type'   => 'bool',
		),
		'_trx_ad_color_int'             => array(
			'column' => 'color_int',
			'type'   => 'string',
		),
		'_trx_ad_doors'                 => array(
			'column' => 'doors',
			'type'   => 'int',
		),
		'_trx_ad_seats'                 => array(
			'column' => 'seats',
			'type'   => 'int',
		),
		'_trx_ad_engine_size'           => array(
			'column' => 'engine_size',
			'type'   => 'decimal',
		),
		'_trx_ad_cylinders'             => array(
			'column' => 'cylinders',
			'type'   => 'int',
		),
		'_trx_ad_horsepower'            => array(
			'column' => 'horsepower',
			'type'   => 'int',
		),
		'_trx_ad_torque_nm'             => array(
			'column' => 'torque_nm',
			'type'   => 'int',
		),
		'_trx_ad_fuel_economy_city'     => array(
			'column' => 'fuel_economy_city',
			'type'   => 'decimal',
		),
		'_trx_ad_fuel_economy_hwy'      => array(
			'column' => 'fuel_economy_hwy',
			'type'   => 'decimal',
		),
		'_trx_ad_fuel_economy_combined' => array(
			'column' => 'fuel_economy_combined',
			'type'   => 'decimal',
		),
		'_trx_ad_ev_range_km'           => array(
			'column' => 'ev_range_km',
			'type'   => 'int',
		),
		'_trx_ad_ev_battery_kwh'        => array(
			'column' => 'ev_battery_kwh',
			'type'   => 'decimal',
		),
		'_trx_ad_ev_charging_type'      => array(
			'column' => 'ev_charging_type',
			'type'   => 'string',
		),
		'_trx_ad_ev_charge_time_min'    => array(
			'column' => 'ev_charge_time_min',
			'type'   => 'int',
		),
		'_trx_ad_co2_emissions'         => array(
			'column' => 'co2_emissions',
			'type'   => 'int',
		),
		'_trx_ad_registration_date'     => array(
			'column' => 'registration_date',
			'type'   => 'date',
		),
		'_trx_ad_warranty_expiry'       => array(
			'column' => 'warranty_expiry',
			'type'   => 'date',
		),
		'_trx_ad_weight_kg'             => array(
			'column' => 'weight_kg',
			'type'   => 'int',
		),
		'_trx_ad_cargo_volume_l'        => array(
			'column' => 'cargo_volume_l',
			'type'   => 'int',
		),
		'_trx_ad_engine_hours'          => array(
			'column' => 'engine_hours',
			'type'   => 'int',
		),
		'_trx_ad_hull_material'         => array(
			'column' => 'hull_material',
			'type'   => 'string',
		),
		'_trx_ad_prev_owners'           => array(
			'column' => 'prev_owners',
			'type'   => 'int',
		),
		'_trx_ad_accident_free'         => array(
			'column' => 'accident_free',
			'type'   => 'bool',
		),
		'_trx_ad_featured'              => array(
			'column' => 'featured',
			'type'   => 'bool',
		),
		'_trx_ad_dealer_id'             => array(
			'column' => 'dealer_id',
			'type'   => 'int',
		),
		'_trx_ad_lat'                   => array(
			'column' => 'lat',
			'type'   => 'decimal',
		),
		'_trx_ad_lng'                   => array(
			'column' => 'lng',
			'type'   => 'decimal',
		),
		'_trx_ad_expiry_date'           => array(
			'column' => 'expiry_date',
			'type'   => 'date',
		),
	);

	/**
	 * Register hooks.
	 *
	 * @return void
	 */
	public function register(): void {
		add_action( 'save_post_' . VehiclePostType::POST_TYPE, array( $this, 'syncOnSave' ), 20, 1 );
		add_action( 'trx_ad_vehicle_specs_vin_conflict', array( $this, 'rememberVinConflict' ), 10, 2 );
		add_action( 'before_delete_post', array( $this, 'onDeletePost' ), 10, 1 );

		// Belt-and-suspenders: also re-sync on `wp_after_insert_post` (which
		// fires AFTER all save_post handlers + classification/term saves —
		// useful when a meta box wrote terms/meta after our priority-20
		// hook already ran).
		add_action( 'wp_after_insert_post', array( $this, 'syncOnAfterInsert' ), 10, 4 );
	}

	/**
	 * @param int   $postId
	 * @param mixed $post
	 * @param bool  $update
	 * @param mixed $postBefore
	 */
	public function syncOnAfterInsert( int $postId, $post, $update, $postBefore ): void {
		if ( ! ( $post instanceof \WP_Post ) ) {
			return;
		}
		if ( $post->post_type !== VehiclePostType::POST_TYPE ) {
			return;
		}
		$this->syncOnSave( $postId );
	}

	/**
	 * Cascade-delete the specs row when its listing post is permanently deleted.
	 *
	 * @param int $postId Post ID being deleted.
	 * @return void
	 */
	public function onDeletePost( int $postId ): void {
		if ( get_post_type( $postId ) !== VehiclePostType::POST_TYPE ) {
			return;
		}
		$this->deleteSpecs( $postId );
	}

	/**
	 * Record a VIN conflict in post meta so the per-listing admin notice
	 * can surface it the next time the user views that listing's edit screen.
	 *
	 * @param int $postId         Listing whose VIN was nulled during sync.
	 * @param int $conflictPostId Listing already holding the same VIN.
	 */
	public function rememberVinConflict( int $postId, int $conflictPostId ): void {
		update_post_meta( $postId, '_trx_ad_vin_conflict_with', $conflictPostId );

		$conflictTitle = (string) ( get_the_title( $conflictPostId ) ?: '' );
		self::recordSyncError(
			$postId,
			'vin_conflict',
			sprintf(
				/* translators: %s: title of the listing already using this VIN */
				__( 'VIN conflict — same VIN already used by "%s". The rest of this listing synced normally.', 'trx-auto-dealer' ),
				$conflictTitle !== '' ? $conflictTitle : '#' . $conflictPostId
			),
			array( 'conflict_with' => $conflictPostId )
		);
	}

	/**
	 * Persist a sync error so it can be surfaced by `showSyncErrorNotices` and
	 * the list-table "Sync errors" filter. Skip codes (autosave/revision/
	 * non-vehicle saves) are silently ignored — those aren't real errors.
	 *
	 * @param array<string,mixed> $extra Extra payload (e.g. conflict_with).
	 */
	public static function recordSyncError( int $postId, string $code, string $message, array $extra = array() ): void {
		// Skip codes that aren't user-actionable: `trx_ad_sync_skip` (autosave/
		// revision/non-vehicle) and `trx_ad_sync_no_data` (empty collectData()
		// on a fresh auto-draft before terms/meta are saved — happens on
		// every "Add new", harmless, would generate notice noise).
		if ( in_array( $code, array( 'trx_ad_sync_skip', 'trx_ad_sync_no_data' ), true ) ) {
			return;
		}
		update_post_meta(
			$postId,
			'_trx_ad_sync_last_error',
			array_merge(
				array(
					'code'      => $code,
					'message'   => $message,
					'timestamp' => time(),
				),
				$extra
			)
		);
	}

	/**
	 * Clear the persisted sync error. Called after a successful sync so a
	 * resolved problem stops appearing in the admin notice + list filter.
	 * Also clears the legacy `_trx_ad_vin_conflict_with` key when the stored
	 * error was a VIN conflict that just got resolved.
	 */
	public static function clearSyncError( int $postId ): void {
		$prev = get_post_meta( $postId, '_trx_ad_sync_last_error', true );
		if ( is_array( $prev ) && ( $prev['code'] ?? '' ) === 'vin_conflict' ) {
			delete_post_meta( $postId, '_trx_ad_vin_conflict_with' );
		}
		delete_post_meta( $postId, '_trx_ad_sync_last_error' );
	}

	/**
	 * Sync vehicle data to specs table on post save.
	 *
	 * @param int $postId Post ID.
	 * @return true|\WP_Error
	 */
	public function syncOnSave( int $postId ): bool|\WP_Error {
		// Skip autosaves.
		if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
			return new \WP_Error( 'trx_ad_sync_skip', 'Skipping autosave.' );
		}

		// Skip revisions.
		if ( wp_is_post_revision( $postId ) ) {
			return new \WP_Error( 'trx_ad_sync_skip', 'Skipping revision.' );
		}

		// Verify this is actually our post type.
		if ( get_post_type( $postId ) !== VehiclePostType::POST_TYPE ) {
			return new \WP_Error( 'trx_ad_sync_skip', 'Not a vehicle post.' );
		}

		$data = $this->collectData( $postId );

		$result = $this->upsert( $postId, $data );

		// Persist (or clear) the per-listing sync error so admin_notices +
		// the list-table filter can surface unresolved problems. VIN
		// conflicts are written by `rememberVinConflict()` from the dedicated
		// `trx_ad_vehicle_specs_vin_conflict` action — don't overwrite that
		// richer payload here on the soft-success path.
		if ( is_wp_error( $result ) ) {
			self::recordSyncError( $postId, (string) $result->get_error_code(), (string) $result->get_error_message() );
		} else {
			// Don't clobber the richer payload that `rememberVinConflict()`
			// just wrote for THIS sync — but DO clear a stale `vin_conflict`
			// from a previous sync if the conflict is now resolved. Freshness
			// gate via the meta `timestamp`: `rememberVinConflict()` set
			// `timestamp = time()` a moment ago, so a real just-written
			// conflict is ≤2 seconds old; anything older is stale and safe
			// to clear.
			$prev        = get_post_meta( $postId, '_trx_ad_sync_last_error', true );
			$prevCode    = is_array( $prev ) ? (string) ( $prev['code'] ?? '' ) : '';
			$prevStamp   = is_array( $prev ) ? (int) ( $prev['timestamp'] ?? 0 ) : 0;
			$justWritten = 'vin_conflict' === $prevCode && $prevStamp >= time() - 2;
			if ( ! $justWritten ) {
				self::clearSyncError( $postId );
			}
		}

		/**
		 * Fires after the vehicle specs row has been synced.
		 *
		 * @param int                $postId Post ID.
		 * @param array<string,mixed> $data   Column data that was synced.
		 * @param true|\WP_Error     $result  Result of the upsert.
		 */
		do_action( 'trx_ad_vehicle_specs_synced', $postId, $data, $result );

		return $result;
	}

	/**
	 * Collect all column data from taxonomies and $_POST.
	 *
	 * Public so diagnostic tooling (`Activator::resyncAllListingSpecsWithReport`,
	 * the Settings → Maintenance "Re-sync vehicle specs" handler) can surface
	 * what `upsert()` is about to write per listing without going through
	 * reflection. (Plan T2-4.)
	 *
	 * @param int $postId Post ID.
	 * @return array<string, mixed>
	 */
	public function collectData( int $postId ): array {
		$data = array();

		// Taxonomy-sourced columns.
		foreach ( self::TAXONOMY_COLUMN_MAP as $taxonomy => $column ) {
			$terms = wp_get_object_terms( $postId, $taxonomy, array( 'fields' => 'slugs' ) );

			if ( is_wp_error( $terms ) || empty( $terms ) ) {
				continue;
			}

			$value = $terms[0];

			// For condition_type, map the slug to the ENUM value.
			if ( $column === 'condition_type' ) {
				$allowed = array( 'new', 'used', 'cpo' );
				$value   = in_array( $value, $allowed, true ) ? $value : null;
			}

			if ( $value !== null ) {
				$data[ $column ] = $value;
			}
		}

		// Meta-sourced columns. By the time syncOnSave runs (priority 20),
		// VehicleDataMetaBox::save (priority 12) has already written every
		// $_POST value to post meta — so post meta is the canonical source
		// and works for non-form save paths too (REST writes, programmatic
		// imports, etc.). $_POST is consulted as a fallback only when the
		// matching meta key isn't set yet.
		// phpcs:disable WordPress.Security.NonceVerification.Missing -- nonce verified in meta box save handler.
		foreach ( self::POST_FIELD_MAP as $postKey => $config ) {
			$raw   = null;
			$value = get_post_meta( $postId, $postKey, true );

			if ( '' !== $value && null !== $value ) {
				$raw = $value;
			} elseif ( isset( $_POST[ $postKey ] ) ) {
				// Sanitized at the read; sanitizeValue() below then applies
				// the type-specific cast (int/decimal/bool/date/enum).
				$raw = sanitize_text_field( wp_unslash( (string) $_POST[ $postKey ] ) );
			}

			if ( null === $raw ) {
				continue;
			}

			$data[ $config['column'] ] = $this->sanitizeValue( $raw, $config['type'] );
		}
		// phpcs:enable WordPress.Security.NonceVerification.Missing

		return $data;
	}

	/**
	 * Sanitize a value based on its type.
	 *
	 * @param mixed  $raw  Raw value.
	 * @param string $type Type identifier.
	 * @return mixed Sanitized value.
	 */
	private function sanitizeValue( mixed $raw, string $type ): mixed {
		return match ( $type ) {
			'int'     => is_string( $raw ) && $raw !== '' ? (int) $raw : null,
			'decimal' => is_string( $raw ) && $raw !== '' ? (float) $raw : null,
			'bool'    => ! empty( $raw ) ? 1 : 0,
			'date'    => is_string( $raw ) ? sanitize_text_field( $raw ) : null,
			'enum'    => is_string( $raw ) ? sanitize_key( $raw ) : null,
			default   => is_string( $raw ) ? sanitize_text_field( $raw ) : null,
		};
	}

	/**
	 * Upsert a row in the vehicle specs table.
	 *
	 * @param int                 $postId Post ID.
	 * @param array<string,mixed> $data   Column => value pairs.
	 * @return true|\WP_Error
	 */
	public function upsert( int $postId, array $data ): bool|\WP_Error {
		global $wpdb;

		$table = $wpdb->prefix . 'trx_ad_vehicle_specs';

		// Whitelist columns and build format strings for $wpdb->insert / ->update.
		$row     = array( 'post_id' => $postId );
		$formats = array( $this->getFormat( 'post_id' ) );

		foreach ( $data as $column => $value ) {
			if ( $column === 'post_id' || ! $this->isValidColumn( $column ) ) {
				continue;
			}
			$row[ $column ] = $value;
			$formats[]      = $this->getFormat( $column );
		}

		if ( count( $row ) <= 1 ) {
			return new \WP_Error( 'trx_ad_sync_no_data', 'No data to sync.' );
		}

		// Check whether the row already exists, then INSERT or UPDATE.
		// Avoids MySQL's `ON DUPLICATE KEY UPDATE` which the SQLite adapter
		// cannot translate correctly when the table has multiple separate
		// unique constraints (PK on post_id + UNIQUE on vin).
		$exists = (bool) $wpdb->get_var(
			$wpdb->prepare( "SELECT 1 FROM `{$table}` WHERE post_id = %d", $postId )
		);

		if ( $exists ) {
			$update = $row;
			unset( $update['post_id'] );
			$updateFormats = array_slice( $formats, 1 );
			$result        = $wpdb->update( $table, $update, array( 'post_id' => $postId ), $updateFormats, array( '%d' ) );
		} else {
			$result = $wpdb->insert( $table, $row, $formats );
		}

		// Duplicate VIN handling. The `vin` column has a UNIQUE index. If
		// another row holds the same VIN — usually an orphaned row left
		// behind when a listing was deleted before the before_delete_post
		// hook existed — the INSERT/UPDATE fails. Detect both error
		// dialects (MySQL/MariaDB: "Duplicate entry '...' for key 'vin'";
		// SQLite via the SQLite Database Integration drop-in:
		// "UNIQUE constraint failed: ...vin"), then attempt to free the VIN
		// and retry once.
		$last_error  = (string) $wpdb->last_error;
		$err_has_vin = '' !== $last_error && false !== stripos( $last_error, 'vin' );
		$err_is_dup  = $err_has_vin
			&& ( false !== stripos( $last_error, 'duplicate' )
				|| false !== stripos( $last_error, 'unique constraint' ) );
		if ( $result === false && $err_is_dup ) {
			$conflictPostId = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT post_id FROM `{$table}` WHERE vin = %s AND post_id <> %d LIMIT 1",
					(string) ( $row['vin'] ?? '' ),
					$postId
				)
			);

			$conflictPost     = $conflictPostId > 0 ? get_post( $conflictPostId ) : null;
			$conflictIsOrphan = $conflictPostId > 0
				&& ( null === $conflictPost
					|| 'trx_ad_listing' !== $conflictPost->post_type
					|| 'trash' === $conflictPost->post_status );

			if ( $conflictIsOrphan ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->delete( $table, array( 'post_id' => $conflictPostId ), array( '%d' ) );
				// Retry with the original semantics (insert if no row, update if there is).
				if ( $exists ) {
					$update = $row;
					unset( $update['post_id'] );
					$updateFormats = array_slice( $formats, 1 );
					$result        = $wpdb->update( $table, $update, array( 'post_id' => $postId ), $updateFormats, array( '%d' ) );
				} else {
					$result = $wpdb->insert( $table, $row, $formats );
				}
			} elseif ( $conflictPostId > 0 ) {
				// VIN legitimately collides with another live listing. Sync
				// the listing's other data with VIN=NULL so the compare
				// page, vehicle cards, and search still work — the admin
				// gets a `trx_ad_vehicle_specs_vin_conflict` action they can
				// surface as an in-product warning. Preserve the original
				// VIN string so the action handler can report what was
				// attempted (the row itself goes in with VIN=NULL).
				$conflictingVin = (string) ( $row['vin'] ?? '' );
				$row['vin']     = null;
				if ( $exists ) {
					$update = $row;
					unset( $update['post_id'] );
					$updateFormats = array_slice( $formats, 1 );
					$result        = $wpdb->update( $table, $update, array( 'post_id' => $postId ), $updateFormats, array( '%d' ) );
				} else {
					$result = $wpdb->insert( $table, $row, $formats );
				}

				do_action( 'trx_ad_vehicle_specs_vin_conflict', $postId, $conflictPostId, $conflictingVin );
			}
		}

		if ( $result === false ) {
			return new \WP_Error(
				'trx_ad_sync_failed',
				sprintf( 'Failed to sync vehicle specs: %s', $wpdb->last_error )
			);
		}

		return true;
	}

	/**
	 * Get the row for a given post (object shape).
	 *
	 * @param int $postId Post ID.
	 * @return object|null Row object or null.
	 */
	public function getSpecs( int $postId ): ?object {
		global $wpdb;

		$table = $wpdb->prefix . 'trx_ad_vehicle_specs';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE post_id = %d", $postId ) );

		return $row ?: null;
	}

	/**
	 * Get the row for a given post (associative-array shape).
	 *
	 * Canonical reader for the specs row. Replaces the namespaced
	 * `\ThemeREX\AutoDealer\getVehicleSpecs()` helper, which is kept as a
	 * thin deprecated shim. (Plan T2-4.)
	 *
	 * @param int $postId Post ID.
	 * @return array<string,mixed>|null
	 */
	public static function get( int $postId ): ?array {
		global $wpdb;

		$table = $wpdb->prefix . 'trx_ad_vehicle_specs';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE post_id = %d LIMIT 1", $postId ), ARRAY_A );

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

	/**
	 * Resolve the denormalized specs-table column that mirrors a given
	 * post-meta key. Readers (the Vehicle Data meta box, the marketplace
	 * feeds) use this to fall back to the specs row when post meta is empty —
	 * e.g. for listings whose data was written specs-first (seed scripts,
	 * direct DB writes) rather than through the editor form.
	 *
	 * @param string $metaKey Post-meta key, with the `_trx_ad_` prefix.
	 * @return string|null Specs column name, or null if the key isn't mirrored.
	 */
	public static function columnForMeta( string $metaKey ): ?string {
		if ( isset( self::POST_FIELD_MAP[ $metaKey ] ) ) {
			return self::POST_FIELD_MAP[ $metaKey ]['column'];
		}
		if ( isset( self::TAXONOMY_COLUMN_MAP[ $metaKey ] ) ) {
			return self::TAXONOMY_COLUMN_MAP[ $metaKey ];
		}
		return null;
	}

	/**
	 * Delete the specs row for a post.
	 *
	 * @param int $postId Post ID.
	 * @return true|\WP_Error
	 */
	public function deleteSpecs( int $postId ): bool|\WP_Error {
		global $wpdb;

		$table  = $wpdb->prefix . 'trx_ad_vehicle_specs';
		$result = $wpdb->delete( $table, array( 'post_id' => $postId ), array( '%d' ) );

		if ( $result === false ) {
			return new \WP_Error( 'trx_ad_delete_failed', $wpdb->last_error );
		}

		return true;
	}

	/**
	 * Check if a column name is valid (exists in our schema).
	 *
	 * @param string $column Column name.
	 * @return bool
	 */
	private function isValidColumn( string $column ): bool {
		static $validColumns = array(
			'post_id',
			'vin',
			'stock_number',
			'condition_type',
			'status',
			'vehicle_type',
			'year',
			'make',
			'model',
			'trim',
			'body_style',
			'fuel_type',
			'transmission',
			'drivetrain',
			'mileage',
			'price',
			'sale_price',
			'price_on_request',
			'color_ext',
			'color_int',
			'doors',
			'seats',
			'engine_size',
			'cylinders',
			'horsepower',
			'torque_nm',
			'fuel_economy_city',
			'fuel_economy_hwy',
			'fuel_economy_combined',
			'ev_range_km',
			'ev_battery_kwh',
			'ev_charging_type',
			'ev_charge_time_min',
			'co2_emissions',
			'registration_date',
			'warranty_expiry',
			'weight_kg',
			'cargo_volume_l',
			'engine_hours',
			'hull_material',
			'prev_owners',
			'accident_free',
			'featured',
			'dealer_id',
			'lat',
			'lng',
			'expiry_date',
			'price_reduced',
			'price_reduced_at',
			'price_original',
			'market_value_badge',
			'view_count',
			'call_count',
			'whatsapp_count',
			'inquiry_count',
			'favorites_count',
			'listing_quality',
			'created_at',
			'updated_at',
		);

		return in_array( $column, $validColumns, true );
	}

	/**
	 * Get the wpdb format specifier for a column (used by $wpdb->insert / ->update).
	 *
	 * @param string $column Column name.
	 * @return string One of '%d', '%f', '%s'.
	 */
	private function getFormat( string $column ): string {
		static $intColumns = array(
			'post_id',
			'year',
			'mileage',
			'doors',
			'seats',
			'cylinders',
			'horsepower',
			'torque_nm',
			'ev_range_km',
			'ev_charge_time_min',
			'co2_emissions',
			'weight_kg',
			'cargo_volume_l',
			'engine_hours',
			'prev_owners',
			'price_on_request',
			'accident_free',
			'featured',
			'dealer_id',
			'price_reduced',
			'view_count',
			'call_count',
			'whatsapp_count',
			'inquiry_count',
			'favorites_count',
			'listing_quality',
		);

		static $floatColumns = array(
			'price',
			'sale_price',
			'engine_size',
			'fuel_economy_city',
			'fuel_economy_hwy',
			'fuel_economy_combined',
			'ev_battery_kwh',
			'lat',
			'lng',
			'price_original',
		);

		if ( in_array( $column, $intColumns, true ) ) {
			return '%d';
		}

		if ( in_array( $column, $floatColumns, true ) ) {
			return '%f';
		}

		return '%s';
	}
}
