<?php
defined( 'ABSPATH' ) || exit;

class YMMHERO_DB {

	/**
	 * Above this vehicle count the preloaded YMM tree is skipped and the
	 * frontend falls back to the AJAX endpoints, to avoid shipping a
	 * multi-megabyte payload on every widget page.
	 */
	const TREE_PRELOAD_LIMIT = 5000;

	/**
	 * Above this vehicle count the Product Assignments screen stops shipping the
	 * whole list to the browser. The client renders what it got and tells the
	 * user the view is partial rather than silently filtering a subset.
	 */
	const ASSIGNMENT_ROWS_LIMIT = 10000;

	private static function table() {
		global $wpdb;
		return $wpdb->prefix . 'ymmhero_vehicles';
	}

	private static function pivot_table() {
		global $wpdb;
		return $wpdb->prefix . 'ymmhero_vehicle_products';
	}

	public static function get_product_ids_by_vehicle( $vehicle_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return array_map( 'absint', $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
				"SELECT product_id FROM " . self::pivot_table() . " WHERE vehicle_id = %d",
				absint( $vehicle_id )
			)
		) );
	}

	public static function get_product_ids_by_vehicles( $vehicle_ids ) {
		global $wpdb;

		$vehicle_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $vehicle_ids ) ) ) );

		if ( empty( $vehicle_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $vehicle_ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return array_map( 'absint', $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input; placeholders are built dynamically from a counted array and bound in the same prepare() call.
				"SELECT DISTINCT product_id FROM " . self::pivot_table() . " WHERE vehicle_id IN ({$placeholders})",
				$vehicle_ids
			)
		) );
	}

	public static function get_vehicle_ids_by_product( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return array_map( 'absint', $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
				"SELECT vehicle_id FROM " . self::pivot_table() . " WHERE product_id = %d",
				absint( $product_id )
			)
		) );
	}

	public static function set_vehicle_products( $vehicle_id, $product_ids ) {
		global $wpdb;

		$vehicle_id  = absint( $vehicle_id );
		$product_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $product_ids ) ) ) );

		if ( ! $vehicle_id ) {
			return;
		}

		$current = self::get_product_ids_by_vehicle( $vehicle_id );
		$added   = array_diff( $product_ids, $current );
		$removed = array_diff( $current, $product_ids );

		foreach ( $added as $product_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
					"INSERT IGNORE INTO " . self::pivot_table() . " (vehicle_id, product_id) VALUES (%d, %d)",
					$vehicle_id,
					$product_id
				)
			);
		}

		foreach ( $removed as $product_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
			$wpdb->delete(
				self::pivot_table(),
				array( 'vehicle_id' => $vehicle_id, 'product_id' => $product_id ),
				array( '%d', '%d' )
			);
		}

		foreach ( array_merge( $added, $removed ) as $product_id ) {
			self::sync_product_meta( $product_id );
		}

		self::flush_cache();
	}

	/**
	 * Assignment from the product side: writes the pivot, then mirrors it back to
	 * post meta so both representations always agree.
	 */
	public static function sync_product_vehicles( $product_id, $vehicle_ids ) {
		global $wpdb;

		$product_id  = absint( $product_id );
		$vehicle_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $vehicle_ids ) ) ) );

		if ( ! $product_id ) {
			return;
		}

		$current = self::get_vehicle_ids_by_product( $product_id );

		foreach ( array_diff( $vehicle_ids, $current ) as $vehicle_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
					"INSERT IGNORE INTO " . self::pivot_table() . " (vehicle_id, product_id) VALUES (%d, %d)",
					$vehicle_id,
					$product_id
				)
			);
		}

		foreach ( array_diff( $current, $vehicle_ids ) as $vehicle_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
			$wpdb->delete(
				self::pivot_table(),
				array( 'vehicle_id' => $vehicle_id, 'product_id' => $product_id ),
				array( '%d', '%d' )
			);
		}

		self::sync_product_meta( $product_id );
		self::flush_cache();
	}

	private static function sync_product_meta( $product_id ) {
		$vehicle_ids = self::get_vehicle_ids_by_product( $product_id );
		update_post_meta( absint( $product_id ), '_ymmhero_compatible_vehicles', $vehicle_ids );
	}

	/**
	 * Rewrites a product's meta mirror from the pivot. For bulk callers that
	 * write many pivot rows first and reconcile meta once per product after.
	 */
	public static function resync_product_meta( $product_id ) {
		self::sync_product_meta( $product_id );
	}

	/**
	 * Append a single assignment. Returns true when a row was actually created,
	 * false when the pair already existed — INSERT IGNORE against the composite
	 * primary key answers "already assigned?" without a second query.
	 */
	public static function add_assignment( $vehicle_id, $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
				"INSERT IGNORE INTO " . self::pivot_table() . " (vehicle_id, product_id) VALUES (%d, %d)",
				absint( $vehicle_id ),
				absint( $product_id )
			)
		);

		if ( ! $wpdb->rows_affected ) {
			return false;
		}

		self::flush_cache();

		return true;
	}

	public static function get_or_create_vehicle( $year, $make, $model, $submodel = '' ) {
		$existing = self::get_vehicle_id_by_ymm( $make, $model, $year, $submodel );

		if ( $existing ) {
			return $existing;
		}

		global $wpdb;

		if ( false === self::insert_vehicle( $year, $make, $model, $submodel ) ) {
			return 0;
		}

		return absint( $wpdb->insert_id );
	}

	public static function delete_product_assignments( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
		$wpdb->delete( self::pivot_table(), array( 'product_id' => absint( $product_id ) ), array( '%d' ) );
		self::flush_cache();
	}

	public static function count_products_for_vehicles( $vehicle_ids ) {
		global $wpdb;

		$vehicle_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $vehicle_ids ) ) ) );

		if ( empty( $vehicle_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $vehicle_ids ), '%d' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name is $wpdb->prefix plus a hardcoded literal; {$placeholders} is a generated list of %d tokens whose values are bound by this same prepare() call. plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT vehicle_id, COUNT(*) AS total FROM " . self::pivot_table() . "
				 WHERE vehicle_id IN ({$placeholders}) GROUP BY vehicle_id",
				$vehicle_ids
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$counts = array_fill_keys( $vehicle_ids, 0 );

		foreach ( $rows as $row ) {
			$counts[ absint( $row['vehicle_id'] ) ] = absint( $row['total'] );
		}

		return $counts;
	}

	/**
	 * Every vehicle plus its assigned-product count, as compact positional rows
	 * ([id, year, make, model, submodel, count]) for the Product Assignments screen, which
	 * filters/sorts/pages client-side. Positional keeps the JSON payload roughly
	 * a third the size of keyed objects; the JS reads the same offsets.
	 */
	public static function get_assignment_rows() {
		// Keyed _v2 because the row shape gained a sub-model column: a cached payload
		// from the previous shape would feed the JS a count from the wrong offset.
		$cached = get_transient( 'ymmhero_assignment_rows_v2' );

		if ( is_array( $cached ) ) {
			return $cached;
		}

		global $wpdb;

		$table = self::table();
		$pivot = self::pivot_table();

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table and $pivot are $wpdb->prefix plus hardcoded literals; the only value is bound via prepare(). plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT v.id, v.year, v.make, v.model, v.submodel, COUNT(p.product_id) AS total
				 FROM {$table} v
				 LEFT JOIN {$pivot} p ON p.vehicle_id = v.id
				 GROUP BY v.id, v.year, v.make, v.model, v.submodel
				 ORDER BY v.year DESC, v.make ASC, v.model ASC, v.submodel ASC
				 LIMIT %d",
				self::ASSIGNMENT_ROWS_LIMIT + 1
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$truncated = count( $rows ) > self::ASSIGNMENT_ROWS_LIMIT;

		if ( $truncated ) {
			array_pop( $rows );
		}

		$result = array(
			'rows'      => array_map(
				function ( $row ) {
					return array(
						absint( $row['id'] ),
						absint( $row['year'] ),
						(string) $row['make'],
						(string) $row['model'],
						isset( $row['submodel'] ) ? (string) $row['submodel'] : '',
						absint( $row['total'] ),
					);
				},
				$rows
			),
			'truncated' => $truncated,
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input; identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
			'total'     => $truncated ? (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ) : count( $rows ),
		);

		set_transient( 'ymmhero_assignment_rows_v2', $result, HOUR_IN_SECONDS );

		return $result;
	}

	/**
	 * Every vehicle as [id, year, make, model, submodel], for the Add Assignment
	 * picker. Separate from get_assignment_rows() because that one carries product
	 * counts and no sub-model, and is shaped for a table rather than a dropdown
	 * chain. Same positional-row and limit reasoning applies.
	 */
	public static function get_vehicle_picker_rows() {
		$cached = get_transient( 'ymmhero_vehicle_picker_rows' );

		if ( is_array( $cached ) ) {
			return $cached;
		}

		global $wpdb;

		$table = self::table();

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is $wpdb->prefix plus a hardcoded literal; the only value is bound via prepare(). plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, year, make, model, submodel
				 FROM {$table}
				 ORDER BY year DESC, make ASC, model ASC, submodel ASC
				 LIMIT %d",
				self::ASSIGNMENT_ROWS_LIMIT + 1
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$truncated = count( $rows ) > self::ASSIGNMENT_ROWS_LIMIT;

		if ( $truncated ) {
			array_pop( $rows );
		}

		$result = array(
			'rows'      => array_map(
				function ( $row ) {
					return array(
						absint( $row['id'] ),
						absint( $row['year'] ),
						(string) $row['make'],
						(string) $row['model'],
						isset( $row['submodel'] ) ? (string) $row['submodel'] : '',
					);
				},
				$rows
			),
			'truncated' => $truncated,
		);

		set_transient( 'ymmhero_vehicle_picker_rows', $result, HOUR_IN_SECONDS );

		return $result;
	}

	public static function count_vehicles_by_assignment() {
		global $wpdb;

		$table = self::table();
		$pivot = self::pivot_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input; identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$total    = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table and $pivot are $wpdb->prefix plus hardcoded literals; the query takes no user input. plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$assigned = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$table} v WHERE EXISTS (
				SELECT 1 FROM {$pivot} p WHERE p.vehicle_id = v.id
			)"
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array(
			'all'          => $total,
			'assigned'     => $assigned,
			'not_assigned' => $total - $assigned,
		);
	}

	/**
	 * Shared WHERE builder for the vehicle list/count queries. Returns the clause
	 * (including the leading " WHERE", or '' when unfiltered) and its params, so
	 * get_vehicles() and count_vehicles() can never drift apart on filtering.
	 */
	private static function build_vehicle_where( $search = '', $status = '' ) {
		global $wpdb;

		$table  = self::table();
		$pivot  = self::pivot_table();
		$where  = array();
		$params = array();
		$search = trim( (string) $search );

		if ( $search !== '' ) {
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]  = '(year LIKE %s OR make LIKE %s OR model LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		if ( 'assigned' === $status || 'not_assigned' === $status ) {
			$operator = 'assigned' === $status ? 'EXISTS' : 'NOT EXISTS';
			$where[]  = "{$operator} ( SELECT 1 FROM {$pivot} p WHERE p.vehicle_id = {$table}.id )";
		}

		$clause = empty( $where ) ? '' : ' WHERE ' . implode( ' AND ', $where );

		return array( $clause, $params );
	}

	public static function get_vehicles( $args = array() ) {
		global $wpdb;

		$allowed_orderby = array( 'year', 'make', 'model', 'submodel' );
		$orderby = isset( $args['orderby'] ) && in_array( $args['orderby'], $allowed_orderby, true )
			? $args['orderby']
			: 'year';
		$order   = isset( $args['order'] ) && $args['order'] === 'ASC' ? 'ASC' : 'DESC';
		$limit   = isset( $args['limit'] ) ? absint( $args['limit'] ) : 50;
		$offset  = isset( $args['offset'] ) ? absint( $args['offset'] ) : 0;
		$search  = isset( $args['search'] ) ? $args['search'] : '';
		$status  = isset( $args['status'] ) ? $args['status'] : '';

		$table = self::table();

		list( $where, $params ) = self::build_vehicle_where( $search, $status );

		$sql = "SELECT * FROM {$table}{$where} ORDER BY {$orderby} {$order}";

		if ( $limit > 0 ) {
			$sql     .= ' LIMIT %d OFFSET %d';
			$params[] = $limit;
			$params[] = $offset;
		}

		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
			$sql = $wpdb->prepare( $sql, $params );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return $wpdb->get_results( $sql, ARRAY_A );
	}

	public static function get_vehicle( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return $wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
			$wpdb->prepare( "SELECT * FROM " . self::table() . " WHERE id = %d", absint( $id ) ),
			ARRAY_A
		);
	}

	public static function insert_vehicle( $year, $make, $model, $submodel = '' ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them.
		$result = $wpdb->insert(
			self::table(),
			array(
				'year'     => absint( $year ),
				'make'     => sanitize_text_field( $make ),
				'model'    => sanitize_text_field( $model ),
				'submodel' => self::normalize_submodel( $submodel ),
			),
			array( '%d', '%s', '%s', '%s' )
		);
		self::flush_cache();

		return $result;
	}

	/**
	 * $submodel === null leaves the stored value untouched. Callers that know
	 * nothing about sub-models (the vehicle form, quick edit) must not blank a
	 * value they never rendered, so null — not '' — is the default.
	 */
	public static function update_vehicle( $id, $year, $make, $model, $submodel = null ) {
		global $wpdb;

		$data    = array(
			'year'  => absint( $year ),
			'make'  => sanitize_text_field( $make ),
			'model' => sanitize_text_field( $model ),
		);
		$formats = array( '%d', '%s', '%s' );

		if ( null !== $submodel ) {
			$data['submodel'] = self::normalize_submodel( $submodel );
			$formats[]        = '%s';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
		$result = $wpdb->update(
			self::table(),
			$data,
			array( 'id' => absint( $id ) ),
			$formats,
			array( '%d' )
		);
		self::flush_cache();

		return $result;
	}

	private static function normalize_submodel( $submodel ) {
		return substr( sanitize_text_field( (string) $submodel ), 0, 100 );
	}

	/**
	 * Human-readable "2018 Toyota Corolla" for a vehicle row. Free never adds
	 * the sub-model itself — the add-on filters it in, so the label stays
	 * correct when the add-on is deactivated.
	 *
	 * @param array  $vehicle Row from get_vehicles()/get_vehicle().
	 * @param string $context Where the label is rendered, e.g. 'meta_box'.
	 */
	public static function vehicle_label( $vehicle, $context = '' ) {
		$parts = array(
			isset( $vehicle['year'] ) ? (string) $vehicle['year'] : '',
			isset( $vehicle['make'] ) ? (string) $vehicle['make'] : '',
			isset( $vehicle['model'] ) ? (string) $vehicle['model'] : '',
		);

		$label = trim( implode( ' ', array_filter( $parts ) ) );

		return (string) apply_filters( 'ymmhero_vehicle_label', $label, $vehicle, $context );
	}

	public static function delete_vehicle( $id ) {
		global $wpdb;

		$id          = absint( $id );
		$product_ids = self::get_product_ids_by_vehicle( $id );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
		$result = $wpdb->delete(
			self::table(),
			array( 'id' => $id ),
			array( '%d' )
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache().
		$wpdb->delete( self::pivot_table(), array( 'vehicle_id' => $id ), array( '%d' ) );

		foreach ( $product_ids as $product_id ) {
			self::sync_product_meta( $product_id );
		}

		self::flush_cache();

		return $result;
	}

	public static function vehicle_exists( $year, $make, $model, $submodel = '' ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return (bool) $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
				"SELECT id FROM " . self::table() . " WHERE year = %d AND make = %s AND model = %s AND submodel = %s LIMIT 1",
				absint( $year ),
				sanitize_text_field( $make ),
				sanitize_text_field( $model ),
				self::normalize_submodel( $submodel )
			)
		);
	}

	/**
	 * When "only assigned vehicles" is on, the frontend dropdown sources join the
	 * pivot so a vehicle with no published product never reaches the widget —
	 * otherwise customers can pick a vehicle and always land on "no products
	 * found". Every caller of this must SELECT DISTINCT: the join multiplies a
	 * vehicle row by its product count.
	 */
	private static function assigned_only_join() {
		if ( ! YMMHERO_Settings::get_display( 'only_assigned_vehicles' ) ) {
			return '';
		}

		global $wpdb;

		$table = self::table();
		$pivot = self::pivot_table();

		return " INNER JOIN {$pivot} ymmhero_p ON ymmhero_p.vehicle_id = {$table}.id"
			. " INNER JOIN {$wpdb->posts} ymmhero_posts ON ymmhero_posts.ID = ymmhero_p.product_id"
			. " AND ymmhero_posts.post_status = 'publish'";
	}

	public static function get_all_makes() {
		$cached = get_transient( 'ymmhero_makes' );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$makes = $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
			"SELECT DISTINCT make FROM " . self::table() . self::assigned_only_join() . " ORDER BY make ASC"
		);
		set_transient( 'ymmhero_makes', $makes, HOUR_IN_SECONDS );

		return $makes;
	}

	public static function get_all_years() {
		$cached = get_transient( 'ymmhero_years' );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		$years = $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
			"SELECT DISTINCT year FROM " . self::table() . self::assigned_only_join() . " ORDER BY year DESC"
		);
		set_transient( 'ymmhero_years', $years, HOUR_IN_SECONDS );

		return $years;
	}

	public static function get_ymmhero_tree() {
		$cached = get_transient( 'ymmhero_tree' );
		if ( false !== $cached ) {
			return $cached;
		}

		$tree = array();

		if ( self::count_vehicles() <= self::TREE_PRELOAD_LIMIT ) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
			$rows = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
				"SELECT DISTINCT year, make, model FROM " . self::table() . self::assigned_only_join()
					. " ORDER BY year DESC, make ASC, model ASC",
				ARRAY_A
			);

			foreach ( $rows as $row ) {
				$tree[ $row['year'] ][ $row['make'] ][] = $row['model'];
			}
		}

		set_transient( 'ymmhero_tree', $tree, HOUR_IN_SECONDS );

		return $tree;
	}

	private static $defer_flush   = false;
	private static $flush_pending = false;

	/**
	 * Lets a bulk caller (the CSV importers) collapse thousands of per-row
	 * flushes into one at the end. Pass false to release and flush if anything
	 * asked to during the deferred window.
	 */
	public static function defer_flush( $defer = true ) {
		self::$defer_flush = (bool) $defer;

		if ( ! $defer && self::$flush_pending ) {
			self::$flush_pending = false;
			self::flush_cache();
		}
	}

	public static function flush_cache() {
		if ( self::$defer_flush ) {
			self::$flush_pending = true;
			return;
		}

		delete_transient( 'ymmhero_makes' );
		delete_transient( 'ymmhero_years' );
		delete_transient( 'ymmhero_tree' );
		delete_transient( 'ymmhero_assignment_rows' );
		delete_transient( 'ymmhero_assignment_rows_v2' );
		delete_transient( 'ymmhero_vehicle_picker_rows' );

		// Add-ons caching their own derived data invalidate it here, so they
		// inherit every trigger free already wires up (vehicle CRUD, CSV import,
		// assignment save, product delete, settings change) instead of
		// duplicating them. Deferred flushes fire this once, not per row.
		do_action( 'ymmhero_flush_cache' );
	}

	public static function get_makes_by_year( $year ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input.
				"SELECT DISTINCT make FROM {$table}" . self::assigned_only_join()
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input.
					. " WHERE {$table}.year = %d ORDER BY make ASC",
				absint( $year )
			)
		);
	}

	public static function get_models_by_year_make( $year, $make ) {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return $wpdb->get_col(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input.
				"SELECT DISTINCT model FROM {$table}" . self::assigned_only_join()
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input.
					. " WHERE {$table}.year = %d AND {$table}.make = %s ORDER BY model ASC",
				absint( $year ),
				sanitize_text_field( $make )
			)
		);
	}

	/**
	 * $submodel of null matches every sub-model of the given Y/M/M; a string
	 * matches that one exactly, and an array matches any value in it (which is
	 * how a caller asks for "this sub-model or the '' wildcard row").
	 */
	public static function get_vehicle_ids_by_ymm( $make = '', $model = '', $year = 0, $submodel = null ) {
		global $wpdb;

		$where  = array();
		$params = array();

		if ( $make !== '' ) {
			$where[]  = 'make = %s';
			$params[] = sanitize_text_field( $make );
		}

		if ( $model !== '' ) {
			$where[]  = 'model = %s';
			$params[] = sanitize_text_field( $model );
		}

		if ( $year ) {
			$where[]  = 'year = %d';
			$params[] = absint( $year );
		}

		if ( empty( $where ) ) {
			return array();
		}

		if ( null !== $submodel ) {
			$values = array_map( array( __CLASS__, 'normalize_submodel' ), (array) $submodel );

			if ( empty( $values ) ) {
				return array();
			}

			$where[] = 'submodel IN (' . implode( ',', array_fill( 0, count( $values ), '%s' ) ) . ')';
			$params  = array_merge( $params, $values );
		}

		$sql = $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); placeholders are built dynamically from a counted array and bound in the same prepare() call.
			"SELECT id FROM " . self::table() . " WHERE " . implode( ' AND ', $where ),
			$params
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return array_map( 'absint', $wpdb->get_col( $sql ) );
	}

	public static function get_vehicle_id_by_ymm( $make, $model, $year, $submodel = null ) {
		$ids = self::get_vehicle_ids_by_ymm( $make, $model, $year, $submodel );
		return ! empty( $ids ) ? $ids[0] : null;
	}

	public static function get_vehicles_by_ids( $ids ) {
		global $wpdb;

		$ids = array_filter( array_map( 'absint', (array) $ids ) );

		if ( empty( $ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		$sql = $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); interpolated part is a generated %d/%s placeholder list or a prefix-derived table name, never user input; placeholders are built dynamically from a counted array and bound in the same prepare() call.
			"SELECT * FROM " . self::table() . " WHERE id IN ({$placeholders}) ORDER BY make ASC, year DESC",
			$ids
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return $wpdb->get_results( $sql, ARRAY_A );
	}

	public static function count_vehicles( $search = '', $status = '' ) {
		global $wpdb;

		$table = self::table();

		list( $where, $params ) = self::build_vehicle_where( $search, $status );

		$sql = "SELECT COUNT(*) FROM {$table}{$where}";

		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare().
			$sql = $wpdb->prepare( $sql, $params );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- plugin owns the ymmhero_vehicles/ymmhero_vehicle_products tables; there is no core API for them; results are cached in the YMMHERO_DB object-cache layer and invalidated by YMMHERO_DB::flush_cache(); table name comes from $wpdb->prefix plus a hardcoded literal; all values are bound via prepare(); identifier is $wpdb->prefix plus a hardcoded literal; values are bound via prepare().
		return (int) $wpdb->get_var( $sql );
	}
}