← All changes | app/Repositories/TripRepository.php +710 -89 3.0.2.6 → 3.0.16 View file →

		@@ -18,8 +18,9 @@
18	18	use Yatra\Utils\Cache;
19	19	use Yatra\Utils\QueryCache;
20	20	use Yatra\Database\Tables\TripAvailabilityDatesTable;
21	21	use Yatra\Database\Tables\TripAvailabilityRulesTable;
	22	+use Yatra\Database\Tables\DeparturesTable;
22	23	use Yatra\Constants\ClassificationTypes;
23	24
24	25	/**
25	26	* Trip Repository
		@@ -33,8 +34,66 @@
33	34	*/
34	35	class TripRepository extends BaseRepository
35	36	{
36	37	/**
	38	+ * Get bookings count map for given trip IDs.
	39	+ *
	40	+ * The trips table has a `bookings_count` column but it is not reliably maintained.
	41	+ * For list views, compute counts from the bookings table in one grouped query.
	42	+ *
	43	+ * @param int[] $tripIds
	44	+ * @param string[]|null $excludeStatuses
	45	+ * @return array<int,int> map trip_id => count
	46	+ */
	47	+ public function getBookingsCountMap(array $tripIds, ?array $excludeStatuses = null): array
	48	+ {
	49	+ $tripIds = array_values(array_filter(array_map('intval', $tripIds)));
	50	+ if (empty($tripIds)) {
	51	+ return [];
	52	+ }
	53	+
	54	+ // Default: ignore cancelled/failed bookings in counts (can be overridden)
	55	+ $excludeStatuses = $excludeStatuses ?? apply_filters(
	56	+ 'yatra_trip_bookings_count_exclude_statuses',
	57	+ ['cancelled', 'failed'],
	58	+ $tripIds
	59	+ );
	60	+ $excludeStatuses = is_array($excludeStatuses) ? array_values(array_filter(array_map('strval', $excludeStatuses))) : [];
	61	+
	62	+ $bookingsTable = BookingsTable::getTableName();
	63	+
	64	+ $idPlaceholders = implode(',', array_fill(0, count($tripIds), '%d'));
	65	+ $where = "trip_id IN ({$idPlaceholders})";
	66	+ $params = $tripIds;
	67	+
	68	+ if (!empty($excludeStatuses)) {
	69	+ $stPlaceholders = implode(',', array_fill(0, count($excludeStatuses), '%s'));
	70	+ $where .= " AND status NOT IN ({$stPlaceholders})";
	71	+ $params = array_merge($params, $excludeStatuses);
	72	+ }
	73	+
	74	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- uses $wpdb->prepare with placeholders
	75	+ $sql = $this->wpdb->prepare(
	76	+ "SELECT trip_id, COUNT(*) AS cnt
	77	+ FROM {$bookingsTable}
	78	+ WHERE {$where}
	79	+ GROUP BY trip_id",
	80	+ $params
	81	+ );
	82	+
	83	+ $rows = $this->wpdb->get_results($sql);
	84	+ $map = [];
	85	+ foreach ((array) $rows as $row) {
	86	+ $tId = (int) ($row->trip_id ?? 0);
	87	+ if ($tId > 0) {
	88	+ $map[$tId] = (int) ($row->cnt ?? 0);
	89	+ }
	90	+ }
	91	+
	92	+ return $map;
	93	+ }
	94	+
	95	+ /**
37	96	* Cache for table existence checks to avoid repeated SHOW TABLES queries
38	97	*/
39	98	private static array $tableExistsCache = [];
40	99
		@@ -212,8 +271,9 @@
212	271	* Create trip row; {@see afterWrite} handles cache; fires action `yatra_trip_created` once per insert.
213	272	*/
214	273	public function create(array $data): int
215	274	{
	275	+ $data = $this->dropUnsupportedDurationHours($data);
216	276	$id = parent::create($data);
217	277	do_action('yatra_trip_created', $id);
218	278
219	279	return $id;
		@@ -219,12 +279,32 @@
219	279	return $id;
220	280	}
221	281
222	282	/**
	283	+ * Defensive: if the `duration_hours` column is somehow missing (a failed or
	284	+ * pending column-add migration on a restrictive host), drop it from the
	285	+ * write set so a normal trip save still succeeds instead of erroring on an
	286	+ * unknown column. The hour-based feature simply stays off until the column
	287	+ * exists — it never breaks saving.
	288	+ *
	289	+ * @param array<string, mixed> $data
	290	+ * @return array<string, mixed>
	291	+ */
	292	+ protected function dropUnsupportedDurationHours(array $data): array
	293	+ {
	294	+ if (array_key_exists('duration_hours', $data) && !$this->tripTableHasColumn('duration_hours')) {
	295	+ unset($data['duration_hours']);
	296	+ }
	297	+
	298	+ return $data;
	299	+ }
	300	+
	301	+ /**
223	302	* Override update method to provide proper field formats
224	303	*/
225	304	public function update(int $id, array $data): bool
226	305	{
	306	+ $data = $this->dropUnsupportedDurationHours($data);
227	307	$data = $this->sanitizeData($data);
228	308	$data['updated_at'] = current_time('mysql');
229	309
230	310	// Build format array based on field types
		@@ -235,9 +315,9 @@
235	315	} elseif (in_array($key, ['created_at', 'updated_at'], true)) {
236	316	$formats[] = '%s';
237	317	} elseif (in_array($key, ['original_price', 'discounted_price', 'sale_price', 'deposit_amount', 'deposit_percentage', 'avg_rating', 'revenue_total', 'conversion_rate'], true)) {
238	318	$formats[] = '%f';
239		- } elseif (in_array($key, ['transportation_included', 'is_featured', 'seasonal_auto_enable'], true)) {
	319	+ } elseif (in_array($key, ['transportation_included', 'is_featured', 'seasonal_auto_enable', 'has_default_time_slots'], true)) {
240	320	$formats[] = '%d'; // boolean as integer
241	321	} else {
242	322	$formats[] = '%s'; // default to string
243	323	}
		@@ -342,8 +422,21 @@
342	422	return self::$tripColumnExistsCache[$column];
343	423	}
344	424
345	425	/**
	426	+ * Public, cached column-existence check for the trips table.
	427	+ *
	428	+ * Lets raw SELECTs outside this repository (booking confirmation join,
	429	+ * similar-trips query) add columns introduced by later upgrades — e.g.
	430	+ * `duration_hours` — without failing on an install whose ALTER has not
	431	+ * run yet (see InstallerService::maybeAddTripDurationHoursColumn()).
	432	+ */
	433	+ public function hasTripColumn(string $column): bool
	434	+ {
	435	+ return $this->tripTableHasColumn($column);
	436	+ }
	437	+
	438	+ /**
346	439	* SQL expression for trip "current" list price (matches TripPricingService::resolveRegularCurrentPrice).
347	440	*/
348	441	protected function sqlTripEffectiveListPrice(): string
349	442	{
		@@ -423,8 +516,21 @@
423	516	$params[] = $filters['trip_type'];
424	517	}
425	518	}
426	519
	520	+ // Featured Priority (column on yatra_trips, indexed by idx_featured_priority).
	521	+ // Admin form's Featured Priority dropdown is the single source of truth.
	522	+ // Legacy `is_featured` shortcode/block flag is normalised upstream into featured_priority='featured'.
	523	+ if (
	524	+ !empty($filters['featured_priority'])
	525	+ && is_string($filters['featured_priority'])
	526	+ && in_array($filters['featured_priority'], ['featured', 'new', 'limited'], true)
	527	+ && $this->tripTableHasColumn('featured_priority')
	528	+ ) {
	529	+ $wheres[] = 't.featured_priority = %s';
	530	+ $params[] = $filters['featured_priority'];
	531	+ }
	532	+
427	533	// Destination: checkbox classification IDs (OR) or single slug
428	534	if (!empty($filters['destination_ids']) && is_array($filters['destination_ids'])) {
429	535	$ids = array_values(array_filter(array_map('intval', $filters['destination_ids']), static fn (int $id): bool => $id > 0));
430	536	if ($ids !== []) {
		@@ -434,13 +540,23 @@
434	540	$params[] = ClassificationTypes::DESTINATION;
435	541	$params = array_merge($params, $ids);
436	542	}
437	543	} elseif (!empty($filters['destination'])) {
438		- $joins[] = "LEFT JOIN {$tripClassificationsTable} tcd ON tcd.trip_id = t.id";
439		- $joins[] = "LEFT JOIN {$classificationsTable} dest ON dest.id = tcd.classification_id";
440		- $wheres[] = 'dest.type = %s AND dest.slug = %s';
441		- $params[] = ClassificationTypes::DESTINATION;
442		- $params[] = $filters['destination'];
	544	+ if (is_array($filters['destination'])) {
	545	+ $slugs = array_values(array_filter(array_map('sanitize_title', $filters['destination'])));
	546	+ if ($slugs !== []) {
	547	+ $placeholders = implode(',', array_fill(0, count($slugs), '%s'));
	548	+ $wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcdx INNER JOIN {$classificationsTable} destx ON destx.id = tcdx.classification_id AND destx.type = %s WHERE tcdx.trip_id = t.id AND tcdx.is_active = 1 AND destx.slug IN ({$placeholders}))";
	549	+ $params[] = ClassificationTypes::DESTINATION;
	550	+ $params = array_merge($params, $slugs);
	551	+ }
	552	+ } elseif (is_string($filters['destination']) && $filters['destination'] !== '') {
	553	+ $joins[] = "LEFT JOIN {$tripClassificationsTable} tcd ON tcd.trip_id = t.id";
	554	+ $joins[] = "LEFT JOIN {$classificationsTable} dest ON dest.id = tcd.classification_id";
	555	+ $wheres[] = 'dest.type = %s AND dest.slug = %s';
	556	+ $params[] = ClassificationTypes::DESTINATION;
	557	+ $params[] = $filters['destination'];
	558	+ }
443	559	}
444	560
445	561	// Activity: IDs or slug
446	562	if (!empty($filters['activity_ids']) && is_array($filters['activity_ids'])) {
		@@ -451,13 +567,23 @@
451	567	$params[] = ClassificationTypes::ACTIVITY;
452	568	$params = array_merge($params, $ids);
453	569	}
454	570	} elseif (!empty($filters['activity'])) {
455		- $joins[] = "LEFT JOIN {$tripClassificationsTable} tca ON tca.trip_id = t.id";
456		- $joins[] = "LEFT JOIN {$classificationsTable} act ON act.id = tca.classification_id";
457		- $wheres[] = 'act.type = %s AND act.slug = %s';
458		- $params[] = ClassificationTypes::ACTIVITY;
459		- $params[] = $filters['activity'];
	571	+ if (is_array($filters['activity'])) {
	572	+ $slugs = array_values(array_filter(array_map('sanitize_title', $filters['activity'])));
	573	+ if ($slugs !== []) {
	574	+ $placeholders = implode(',', array_fill(0, count($slugs), '%s'));
	575	+ $wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tcax INNER JOIN {$classificationsTable} actx ON actx.id = tcax.classification_id AND actx.type = %s WHERE tcax.trip_id = t.id AND tcax.is_active = 1 AND actx.slug IN ({$placeholders}))";
	576	+ $params[] = ClassificationTypes::ACTIVITY;
	577	+ $params = array_merge($params, $slugs);
	578	+ }
	579	+ } elseif (is_string($filters['activity']) && $filters['activity'] !== '') {
	580	+ $joins[] = "LEFT JOIN {$tripClassificationsTable} tca ON tca.trip_id = t.id";
	581	+ $joins[] = "LEFT JOIN {$classificationsTable} act ON act.id = tca.classification_id";
	582	+ $wheres[] = 'act.type = %s AND act.slug = %s';
	583	+ $params[] = ClassificationTypes::ACTIVITY;
	584	+ $params[] = $filters['activity'];
	585	+ }
460	586	}
461	587
462	588	// Category: IDs or slug
463	589	if (!empty($filters['category_ids']) && is_array($filters['category_ids'])) {
		@@ -468,13 +594,23 @@
468	594	$params[] = ClassificationTypes::CATEGORY;
469	595	$params = array_merge($params, $ids);
470	596	}
471	597	} elseif (!empty($filters['trip_category'])) {
472		- $joins[] = "LEFT JOIN {$tripClassificationsTable} tcc ON tcc.trip_id = t.id";
473		- $joins[] = "LEFT JOIN {$classificationsTable} cat ON cat.id = tcc.classification_id";
474		- $wheres[] = 'cat.type = %s AND cat.slug = %s';
475		- $params[] = ClassificationTypes::CATEGORY;
476		- $params[] = $filters['trip_category'];
	598	+ if (is_array($filters['trip_category'])) {
	599	+ $slugs = array_values(array_filter(array_map('sanitize_title', $filters['trip_category'])));
	600	+ if ($slugs !== []) {
	601	+ $placeholders = implode(',', array_fill(0, count($slugs), '%s'));
	602	+ $wheres[] = "EXISTS (SELECT 1 FROM {$tripClassificationsTable} tccx INNER JOIN {$classificationsTable} catx ON catx.id = tccx.classification_id AND catx.type = %s WHERE tccx.trip_id = t.id AND tccx.is_active = 1 AND catx.slug IN ({$placeholders}))";
	603	+ $params[] = ClassificationTypes::CATEGORY;
	604	+ $params = array_merge($params, $slugs);
	605	+ }
	606	+ } elseif (is_string($filters['trip_category']) && $filters['trip_category'] !== '') {
	607	+ $joins[] = "LEFT JOIN {$tripClassificationsTable} tcc ON tcc.trip_id = t.id";
	608	+ $joins[] = "LEFT JOIN {$classificationsTable} cat ON cat.id = tcc.classification_id";
	609	+ $wheres[] = 'cat.type = %s AND cat.slug = %s';
	610	+ $params[] = ClassificationTypes::CATEGORY;
	611	+ $params[] = $filters['trip_category'];
	612	+ }
477	613	}
478	614
479	615	// Special offers (OR within group)
480	616	if (!empty($filters['special_offers']) && is_array($filters['special_offers'])) {
		@@ -634,18 +770,71 @@
634	770	}
635	771	}
636	772	}
637	773
638		- // Price range filter (effective list price, not original-only)
	774	+ // Price range filter.
	775	+ //
	776	+ // The previous version compared a single legacy "effective"
	777	+ // price (discounted → sale → original column) against the
	778	+ // range. That excluded traveler-based trips whose real
	779	+ // displayed price lives in the `price_types` JSON — so eg. a
	780	+ // trip costing €3755 (Adult category) never appeared in search
	781	+ // when the user set max=€3755, because its legacy
	782	+ // original_price was €0 / stale.
	783	+ //
	784	+ // New approach: match a trip if EITHER the legacy effective
	785	+ // price OR ANY per-category price in the JSON falls in the
	786	+ // requested range. Numeric values in the `price_types` JSON
	787	+ // are extracted with a regex on the raw text — works in all
	788	+ // MySQL 5.7+ builds without needing JSON_VALUE / JSON_TABLE
	789	+ // (which are inconsistent across MariaDB / older MySQL).
639	790	$effPrice = $this->sqlTripEffectiveListPrice();
640		- if (!empty($filters['price_min']) && $filters['price_min'] > 0) {
641		- $wheres[] = "{$effPrice} >= %f";
642		- $params[] = $filters['price_min'];
643		- }
	791	+ $priceMin = !empty($filters['price_min']) && $filters['price_min'] > 0 ? (float) $filters['price_min'] : null;
	792	+ $priceMax = !empty($filters['price_max']) && $filters['price_max'] > 0 ? (float) $filters['price_max'] : null;
644	793
645		- if (!empty($filters['price_max']) && $filters['price_max'] > 0) {
646		- $wheres[] = "{$effPrice} <= %f";
647		- $params[] = $filters['price_max'];
	794	+ if ($priceMin !== null || $priceMax !== null) {
	795	+ $legacyCond = [];
	796	+ if ($priceMin !== null) { $legacyCond[] = "{$effPrice} >= %f"; $params[] = $priceMin; }
	797	+ if ($priceMax !== null) { $legacyCond[] = "{$effPrice} <= %f"; $params[] = $priceMax; }
	798	+ $legacyClause = implode(' AND ', $legacyCond);
	799	+
	800	+ // For per-category pricing, find ANY price in `price_types`
	801	+ // JSON that falls in the requested range. This subquery
	802	+ // creates an ad-hoc number sequence (n=0..49 covers up to
	803	+ // 50 categories — far more than any real trip uses) and
	804	+ // uses JSON_EXTRACT to fetch each entry's effective price.
	805	+ $numbers = "(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19)";
	806	+ // Each category's effective price = first non-null of
	807	+ // discounted_price → sale_price → original_price → price.
	808	+ $categoryEff = "COALESCE("
	809	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].discounted_price'))) AS DECIMAL(10,2)), 0),"
	810	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].sale_price'))) AS DECIMAL(10,2)), 0),"
	811	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].original_price'))) AS DECIMAL(10,2)), 0),"
	812	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].price'))) AS DECIMAL(10,2)), 0)"
	813	+ . ")";
	814	+ $catCond = [];
	815	+ if ($priceMin !== null) { $catCond[] = "{$categoryEff} >= %f"; $params[] = $priceMin; }
	816	+ if ($priceMax !== null) { $catCond[] = "{$categoryEff} <= %f"; $params[] = $priceMax; }
	817	+ $catClause = implode(' AND ', $catCond);
	818	+
	819	+ // Guard the per-category branch against malformed JSON.
	820	+ // JSON_EXTRACT throws on invalid JSON, and `price_types`
	821	+ // can be NULL, '', '[]' or a stale string on legacy rows.
	822	+ // JSON_VALID() + IFNULL() lets us bail cleanly for those.
	823	+ $perCategoryExists = "("
	824	+ . "t.price_types IS NOT NULL"
	825	+ . " AND t.price_types <> ''"
	826	+ . " AND JSON_VALID(t.price_types) = 1"
	827	+ . " AND EXISTS ("
	828	+ . "SELECT 1 FROM {$numbers} _n"
	829	+ . " WHERE JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, ']')) IS NOT NULL"
	830	+ . " AND {$catClause}"
	831	+ . ")"
	832	+ . ")";
	833	+
	834	+ // Match if EITHER condition passes — a single legacy-priced
	835	+ // trip OR a per-category trip with any matching tier.
	836	+ $wheres[] = "(({$legacyClause}) OR {$perCategoryExists})";
648	837	}
649	838
650	839	// Duration filter
651	840	if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) {
		@@ -656,9 +845,29 @@
656	845	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
657	846	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
658	847	$params[] = $filters['duration_max'];
659	848	}
660		-
	849	+
	850	+ // Availability date filter ("available from this date onward"): keep only
	851	+ // trips that have a departure starting ON OR AFTER the selected date, so
	852	+ // a customer who picks a date sees every trip they could still travel on
	853	+ // from then. A departure matches when its effective start date (the
	854	+ // explicit start_date, else the single `date`) is >= the chosen day and
	855	+ // it has not been cancelled — the date bound alone excludes past dates
	856	+ // when the picker's min=today is respected, so no reliance on a possibly
	857	+ // stale 'past' status. Clause + param are appended together so the WHERE
	858	+ // ordering stays in sync with $params (see the count/main queries).
	859	+ if (!empty($filters['available_date'])) {
	860	+ $departures_table = DeparturesTable::getTableName();
	861	+ $wheres[] = "EXISTS (
	862	+ SELECT 1 FROM {$departures_table} yd
	863	+ WHERE yd.trip_id = t.id
	864	+ AND COALESCE(NULLIF(yd.start_date, '0000-00-00'), yd.date) >= %s
	865	+ AND yd.status <> 'cancelled'
	866	+ )";
	867	+ $params[] = $filters['available_date'];
	868	+ }
	869	+
661	870	// Rating filter
662	871	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
663	872	$having_clauses[] = "AVG(r.rating) >= %f";
664	873	$rating_params[] = $filters['rating_min'];
		@@ -765,8 +974,10 @@
765	974	case 'price_high':
766	975	return "ORDER BY {$effPrice} DESC";
767	976	case 'rating_high':
768	977	return "ORDER BY average_rating DESC";
	978	+ case 'date_asc':
	979	+ return "ORDER BY t.created_at ASC";
769	980	case 'duration_short':
770	981	return "ORDER BY CAST(t.duration_days AS UNSIGNED) ASC, CAST(t.duration_nights AS UNSIGNED) ASC";
771	982	case 'duration_long':
772	983	return "ORDER BY CAST(t.duration_days AS UNSIGNED) DESC, CAST(t.duration_nights AS UNSIGNED) DESC";
		@@ -1135,8 +1346,9 @@
1135	1346	'discounted_price' => isset($pt['discounted_price']) ? (float) $pt['discounted_price'] : null,
1136	1347	'sale_price' => isset($pt['sale_price']) ? (float) $pt['sale_price'] : null,
1137	1348	'label' => $pt['label'] ?? ($pt['title'] ?? null),
1138	1349	'pricing_mode' => $pt['pricing_mode'] ?? 'per_person',
	1350	+ 'is_default' => !empty($pt['is_default']),
1139	1351	];
1140	1352	if (isset($pt['category_label'])) {
1141	1353	$normalized['category_label'] = $pt['category_label'];
1142	1354	}
		@@ -1607,11 +1819,47 @@
1607	1819	*/
1608	1820	public function savePriceTypes(int $tripId, array $priceTypes): void
1609	1821	{
1610	1822	if (empty($priceTypes)) {
	1823	+ // Explicitly clear stored per-category pricing so that deleting all
	1824	+ // rows (even the last one) persists. Previously this early-returned,
	1825	+ // leaving the old price_types JSON in place — the deletion silently
	1826	+ // reverted on reload. Only the JSON is always cleared; the derived
	1827	+ // scalar columns (original/discounted/sale) are reset solely for
	1828	+ // traveler-based trips, because for regular pricing those columns
	1829	+ // hold the actual price and must not be wiped.
	1830	+ $table = TripsTable::getTableName();
	1831	+ $pricingType = $this->wpdb->get_var(
	1832	+ $this->wpdb->prepare("SELECT pricing_type FROM `{$table}` WHERE id = %d", $tripId)
	1833	+ );
	1834	+
	1835	+ $data = ['price_types' => null];
	1836	+ if ($pricingType === 'traveler_based') {
	1837	+ $data['original_price'] = null;
	1838	+ $data['discounted_price'] = null;
	1839	+ $data['sale_price'] = null;
	1840	+ }
	1841	+
	1842	+ $this->wpdb->update($table, $data, ['id' => $tripId]);
1611	1843	return;
1612	1844	}
1613	1845
	1846	+ // Ensure at most one default category is set (keep the first truthy one).
	1847	+ $defaultFound = false;
	1848	+ foreach ($priceTypes as &$pt) {
	1849	+ if (!is_array($pt)) {
	1850	+ continue;
	1851	+ }
	1852	+ $isDefault = !empty($pt['is_default']);
	1853	+ if ($isDefault && !$defaultFound) {
	1854	+ $defaultFound = true;
	1855	+ $pt['is_default'] = true;
	1856	+ } else {
	1857	+ $pt['is_default'] = false;
	1858	+ }
	1859	+ }
	1860	+ unset($pt);
	1861	+
1614	1862	// Compute minimal pricing values from provided price types
1615	1863	$minOriginal = PHP_FLOAT_MAX;
1616	1864	$minDiscounted = PHP_FLOAT_MAX;
1617	1865	$minSale = PHP_FLOAT_MAX;
		@@ -1970,45 +2218,165 @@
1970	2218	));
1971	2219	}
1972	2220
1973	2221	/**
1974		- * Save availability dates for a trip
	2222	+ * Save availability dates for a trip.
	2223	+ *
	2224	+ * Previously a flat DELETE-THEN-INSERT: every row for the trip was nuked
	2225	+ * and the incoming list rewritten. That made the trip-edit save lossy —
	2226	+ * when the Trip form only knew about a subset of fields on each date
	2227	+ * (e.g. it was loaded once, then the operator changed the seats on a
	2228	+ * single date through the Availability tab, then re-saved the Trip with
	2229	+ * the form's stale date payload), every field absent from the incoming
	2230	+ * row was reset to its hard-coded default. That's why operator edits
	2231	+ * to `seats_total` silently reverted to 20 after another trip-save:
	2232	+ *
	2233	+ * 1. Operator opens Trip → form loads `availability_dates` with
	2234	+ * `seats_total = 20`.
	2235	+ * 2. Operator switches to Availability tab → edits Sept 5 to
	2236	+ * `seats_total = 14` via the dedicated specific-date endpoint
	2237	+ * (writes directly to the row, so the row is now 14).
	2238	+ * 3. Operator returns to the Trip form, edits something unrelated
	2239	+ * (title / description), clicks Save. The form re-posts the date
	2240	+ * list it loaded in step 1 — `seats_total = 20`.
	2241	+ * 4. saveAvailabilityDates DELETEs everything, INSERTs the
	2242	+ * form-supplied list → Sept 5 is back to 20 (or the literal `20`
	2243	+ * default if the form didn't include seats_total at all).
	2244	+ *
	2245	+ * Fix: snapshot the existing rows before the DELETE and, when the
	2246	+ * incoming row omits an editable field, fall back to the snapshot's
	2247	+ * value instead of the schema default. The literal `20` default is
	2248	+ * removed in favour of the trip's `max_travelers` (or 1 as a last
	2249	+ * resort) so new dates inserted by truly-new entries don't pretend the
	2250	+ * trip seats 20 people unless the trip actually says so.
	2251	+ *
	2252	+ * Match key for the snapshot: `departure_date` + `departure_time` (both
	2253	+ * normalised), which is the same identity the Availability UI uses.
1975	2254	*/
1976	2255	public function saveAvailabilityDates(int $tripId, array $availabilityDates): void
1977	2256	{
1978	2257	global $wpdb;
1979	2258	$table = TripAvailabilityDatesTable::getTableName();
1980		-
	2259	+
	2260	+ // Snapshot existing rows by date + time so partial incoming payloads
	2261	+ // can be merged with persisted values instead of clobbering them.
	2262	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from schema helper.
	2263	+ $existingRows = $wpdb->get_results($wpdb->prepare(
	2264	+ "SELECT * FROM {$table} WHERE trip_id = %d",
	2265	+ $tripId
	2266	+ ));
	2267	+ $snapshot = [];
	2268	+ if (is_array($existingRows)) {
	2269	+ foreach ($existingRows as $row) {
	2270	+ $key = self::availabilityRowKey(
	2271	+ (string) ($row->departure_date ?? ''),
	2272	+ (string) ($row->departure_time ?? '')
	2273	+ );
	2274	+ if ($key !== '') {
	2275	+ $snapshot[$key] = $row;
	2276	+ }
	2277	+ }
	2278	+ }
	2279	+
	2280	+ // Fallback capacity when both the incoming row and the snapshot
	2281	+ // miss seats_total: read the trip's max_travelers once. The
	2282	+ // legacy literal `20` was a phantom default that punished sites
	2283	+ // whose actual trip capacity differs.
	2284	+ $tripFallbackSeats = 0;
	2285	+ $tripRow = $this->find($tripId);
	2286	+ if (is_object($tripRow) && isset($tripRow->max_travelers)) {
	2287	+ $tripFallbackSeats = (int) $tripRow->max_travelers;
	2288	+ }
	2289	+ if ($tripFallbackSeats <= 0) {
	2290	+ $tripFallbackSeats = 1;
	2291	+ }
	2292	+
1981	2293	// Delete existing
1982	2294	$wpdb->delete($table, ['trip_id' => $tripId], ['%d']);
1983		-
	2295	+
1984	2296	// Insert new
1985	2297	if (!empty($availabilityDates)) {
1986	2298	foreach ($availabilityDates as $date) {
1987	2299	if (is_array($date) && !empty($date['departure_date'])) {
1988		- $seatsTotal = isset($date['seats_total']) ? (int) $date['seats_total'] : 20;
1989		- $seatsAvailable = isset($date['seats_available']) ? (int) $date['seats_available'] : $seatsTotal;
1990		-
	2300	+ $key = self::availabilityRowKey(
	2301	+ (string) $date['departure_date'],
	2302	+ (string) ($date['departure_time'] ?? '')
	2303	+ );
	2304	+ $existing = $key !== '' && isset($snapshot[$key]) ? $snapshot[$key] : null;
	2305	+
	2306	+ $seatsTotal = isset($date['seats_total'])
	2307	+ ? (int) $date['seats_total']
	2308	+ : ($existing ? (int) ($existing->seats_total ?? $tripFallbackSeats) : $tripFallbackSeats);
	2309	+ $seatsAvailable = isset($date['seats_available'])
	2310	+ ? (int) $date['seats_available']
	2311	+ : ($existing ? (int) ($existing->seats_available ?? $seatsTotal) : $seatsTotal);
	2312	+
	2313	+ $arrivalDate = isset($date['arrival_date'])
	2314	+ ? sanitize_text_field($date['arrival_date'])
	2315	+ : (isset($date['return_date'])
	2316	+ ? sanitize_text_field($date['return_date'])
	2317	+ : ($existing ? $existing->arrival_date : null));
	2318	+ $returnDate = isset($date['return_date'])
	2319	+ ? sanitize_text_field($date['return_date'])
	2320	+ : ($existing ? $existing->return_date : null);
	2321	+ $arrivalTime = isset($date['arrival_time'])
	2322	+ ? sanitize_text_field($date['arrival_time'])
	2323	+ : ($existing ? $existing->arrival_time : null);
	2324	+
	2325	+ $originalPrice = isset($date['original_price'])
	2326	+ ? (float) $date['original_price']
	2327	+ : (isset($date['price_override'])
	2328	+ ? (float) $date['price_override']
	2329	+ : ($existing && $existing->original_price !== null ? (float) $existing->original_price : null));
	2330	+ $discountedPrice = isset($date['discounted_price'])
	2331	+ ? (float) $date['discounted_price']
	2332	+ : ($existing && $existing->discounted_price !== null ? (float) $existing->discounted_price : null);
	2333	+
	2334	+ $fromLocation = isset($date['from_location']) ? sanitize_text_field($date['from_location']) : ($existing ? $existing->from_location : null);
	2335	+ $toLocation = isset($date['to_location']) ? sanitize_text_field($date['to_location']) : ($existing ? $existing->to_location : null);
	2336	+ $fromLat = isset($date['from_latitude']) && is_numeric($date['from_latitude'])
	2337	+ ? (string) $date['from_latitude']
	2338	+ : ($existing ? $existing->from_latitude : null);
	2339	+ $fromLng = isset($date['from_longitude']) && is_numeric($date['from_longitude'])
	2340	+ ? (string) $date['from_longitude']
	2341	+ : ($existing ? $existing->from_longitude : null);
	2342	+ $toLat = isset($date['to_latitude']) && is_numeric($date['to_latitude'])
	2343	+ ? (string) $date['to_latitude']
	2344	+ : ($existing ? $existing->to_latitude : null);
	2345	+ $toLng = isset($date['to_longitude']) && is_numeric($date['to_longitude'])
	2346	+ ? (string) $date['to_longitude']
	2347	+ : ($existing ? $existing->to_longitude : null);
	2348	+
	2349	+ if (isset($date['is_blackout'])) {
	2350	+ $status = $date['is_blackout'] ? 'blocked' : 'available';
	2351	+ } elseif (isset($date['status'])) {
	2352	+ $status = sanitize_text_field($date['status']);
	2353	+ } elseif ($existing) {
	2354	+ $status = (string) ($existing->status ?? 'available');
	2355	+ } else {
	2356	+ $status = 'available';
	2357	+ }
	2358	+
1991	2359	$insertData = [
1992	2360	'trip_id' => $tripId,
1993	2361	'departure_date' => sanitize_text_field($date['departure_date']),
1994		- 'arrival_date' => isset($date['arrival_date']) ? sanitize_text_field($date['arrival_date']) : ($date['return_date'] ?? null),
1995		- 'return_date' => isset($date['return_date']) ? sanitize_text_field($date['return_date']) : null,
1996		- 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : null,
1997		- 'arrival_time' => isset($date['arrival_time']) ? sanitize_text_field($date['arrival_time']) : null,
	2362	+ 'arrival_date' => $arrivalDate,
	2363	+ 'return_date' => $returnDate,
	2364	+ 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : ($existing ? $existing->departure_time : null),
	2365	+ 'arrival_time' => $arrivalTime,
1998	2366	'seats_total' => $seatsTotal,
1999	2367	'seats_available' => $seatsAvailable,
2000		- 'original_price' => isset($date['original_price']) ? (float) $date['original_price'] : (isset($date['price_override']) ? (float) $date['price_override'] : null),
2001		- 'discounted_price' => isset($date['discounted_price']) ? (float) $date['discounted_price'] : null,
2002		- 'from_location' => isset($date['from_location']) ? sanitize_text_field($date['from_location']) : null,
2003		- 'to_location' => isset($date['to_location']) ? sanitize_text_field($date['to_location']) : null,
2004		- 'from_latitude' => isset($date['from_latitude']) && is_numeric($date['from_latitude']) ? (string) $date['from_latitude'] : null,
2005		- 'from_longitude' => isset($date['from_longitude']) && is_numeric($date['from_longitude']) ? (string) $date['from_longitude'] : null,
2006		- 'to_latitude' => isset($date['to_latitude']) && is_numeric($date['to_latitude']) ? (string) $date['to_latitude'] : null,
2007		- 'to_longitude' => isset($date['to_longitude']) && is_numeric($date['to_longitude']) ? (string) $date['to_longitude'] : null,
2008		- 'status' => isset($date['is_blackout']) && $date['is_blackout'] ? 'blocked' : (isset($date['status']) ? sanitize_text_field($date['status']) : 'available'),
	2368	+ 'original_price' => $originalPrice,
	2369	+ 'discounted_price' => $discountedPrice,
	2370	+ 'from_location' => $fromLocation,
	2371	+ 'to_location' => $toLocation,
	2372	+ 'from_latitude' => $fromLat,
	2373	+ 'from_longitude' => $fromLng,
	2374	+ 'to_latitude' => $toLat,
	2375	+ 'to_longitude' => $toLng,
	2376	+ 'status' => $status,
2009	2377	];
2010		-
	2378	+
2011	2379	$wpdb->insert(
2012	2380	$table,
2013	2381	$insertData,
2014	2382	['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
		@@ -2018,8 +2386,23 @@
2018	2386	}
2019	2387	}
2020	2388
2021	2389	/**
	2390	+ * Normalise a (date, time) pair to a single string key used to match
	2391	+ * incoming availability rows against the pre-delete snapshot. Time is
	2392	+ * left as-is for an exact comparison; empty time matches the "no
	2393	+ * specific time" row.
	2394	+ */
	2395	+ private static function availabilityRowKey(string $date, string $time): string
	2396	+ {
	2397	+ $date = trim($date);
	2398	+ if ($date === '') {
	2399	+ return '';
	2400	+ }
	2401	+ return $date . '|' . trim($time);
	2402	+ }
	2403	+
	2404	+ /**
2022	2405	* Save attributes for a trip
2023	2406	*/
2024	2407	public function saveAttributes(int $tripId, array $attributes): void
2025	2408	{
		@@ -2582,34 +2965,229 @@
2582	2965	));
2583	2966	}
2584	2967
2585	2968	/**
2586		- * Get price range statistics for published trips
2587		- *
	2969	+ * Get price range statistics for published trips.
	2970	+ *
	2971	+ * IMPORTANT: the previous SQL-only implementation only considered
	2972	+ * the legacy `original_price` / `discounted_price` / `sale_price`
	2973	+ * columns. For trips using traveler-based pricing (per-category
	2974	+ * prices stored in the `price_types` JSON column), those legacy
	2975	+ * columns are often empty or stale — so the computed max was lower
	2976	+ * than the actually-displayed price, and the price-range slider
	2977	+ * cut off above the real maximum (eg. trip cost €3755 but slider
	2978	+ * stopped at €3731). Worse, the trip then couldn't be filtered
	2979	+ * into the results because the effective-price WHERE excluded it.
	2980	+ *
	2981	+ * Fix: walk the published trips once in PHP and let
	2982	+ * `TripPricingService` decide each trip's effective price using
	2983	+ * the same logic the listing/single-trip pages use to DISPLAY the
	2984	+ * price. We also track the per-trip MIN/MAX across categories so
	2985	+ * the slider bounds cover every traveler tier (not only the
	2986	+ * default / cheapest).
	2987	+ *
2588	2988	* @return object Object with min_price and max_price properties
2589	2989	*/
2590	2990	public function getPriceRangeStats(): object
2591	2991	{
2592	2992	$table = $this->getTableName();
2593		- return $this->wpdb->get_row(
2594		- "SELECT
2595		- MIN(sub.eff_price) as min_price,
2596		- MAX(sub.eff_price) as max_price
2597		- FROM (
2598		- SELECT (CASE
2599		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2600		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2601		- ELSE CAST(original_price AS DECIMAL(10,2))
2602		- END) AS eff_price
2603		- FROM {$table}
2604		- WHERE status IN ('publish', 'published')
2605		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2606		- ) sub
2607		- WHERE sub.eff_price > 0"
2608		- );
	2993	+ $rows = $this->wpdb->get_results(
	2994	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	2995	+ FROM {$table}
	2996	+ WHERE status IN ('publish', 'published')
	2997	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	2998	+ ) ?: [];
	2999	+
	3000	+ $minPrice = null;
	3001	+ $maxPrice = null;
	3002	+
	3003	+ foreach ($rows as $row) {
	3004	+ $tripPrices = $this->collectTripDisplayPrices($row);
	3005	+ foreach ($tripPrices as $price) {
	3006	+ if ($price <= 0) {
	3007	+ continue;
	3008	+ }
	3009	+ if ($minPrice === null || $price < $minPrice) {
	3010	+ $minPrice = $price;
	3011	+ }
	3012	+ if ($maxPrice === null || $price > $maxPrice) {
	3013	+ $maxPrice = $price;
	3014	+ }
	3015	+ }
	3016	+ }
	3017	+
	3018	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3019	+ // slider bounds span every price a customer can actually be charged,
	3020	+ // not just the trips-table base. See collectPriceOverridePoints().
	3021	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3022	+ if ($price <= 0) {
	3023	+ continue;
	3024	+ }
	3025	+ if ($minPrice === null || $price < $minPrice) {
	3026	+ $minPrice = $price;
	3027	+ }
	3028	+ if ($maxPrice === null || $price > $maxPrice) {
	3029	+ $maxPrice = $price;
	3030	+ }
	3031	+ }
	3032	+
	3033	+ return (object) [
	3034	+ 'min_price' => $minPrice,
	3035	+ 'max_price' => $maxPrice,
	3036	+ ];
2609	3037	}
2610	3038
2611	3039	/**
	3040	+ * Collect every price a single trip might display to a user.
	3041	+ *
	3042	+ * Returns BOTH the legacy "regular" effective price AND every
	3043	+ * per-category effective price from `price_types`. Used by
	3044	+ * `getPriceRangeStats()` so the price-range slider's bounds cover
	3045	+ * the full range a customer could see — eg. for a traveler-based
	3046	+ * trip with Adult €3755 / Child €1500 / Infant €100, the array
	3047	+ * includes all three so the slider stretches from €100 to €3755.
	3048	+ *
	3049	+ * @param object $row Raw trip row (must include `price_types`,
	3050	+ * `original_price`, `discounted_price`,
	3051	+ * `sale_price`).
	3052	+ * @return float[]
	3053	+ */
	3054	+ protected function collectTripDisplayPrices(object $row): array
	3055	+ {
	3056	+ $prices = [];
	3057	+
	3058	+ // Legacy regular pricing.
	3059	+ $legacyPrice = \Yatra\Services\TripPricingService::resolveRegularCurrentPrice($row);
	3060	+ if ($legacyPrice > 0) {
	3061	+ $prices[] = $legacyPrice;
	3062	+ }
	3063	+
	3064	+ // Per-category prices (traveler-based pricing). Decode whether
	3065	+ // `price_types` is stored as a JSON string (raw DB row) or an
	3066	+ // already-decoded array (in-memory trip object).
	3067	+ $priceTypes = $row->price_types ?? null;
	3068	+ if (is_string($priceTypes) && $priceTypes !== '') {
	3069	+ $decoded = json_decode($priceTypes, true);
	3070	+ if (is_array($decoded)) {
	3071	+ $priceTypes = $decoded;
	3072	+ }
	3073	+ }
	3074	+ if (is_array($priceTypes)) {
	3075	+ foreach ($priceTypes as $pt) {
	3076	+ $price = \Yatra\Services\TripPricingService::resolveCategoryEffectivePrice((array) $pt);
	3077	+ if ($price > 0) {
	3078	+ $prices[] = $price;
	3079	+ }
	3080	+ }
	3081	+ }
	3082	+
	3083	+ return $prices;
	3084	+ }
	3085	+
	3086	+ /**
	3087	+ * Effective price points that OVERRIDE a trip's base price for specific
	3088	+ * dates, recurring rules, or individual departures.
	3089	+ *
	3090	+ * The price a customer actually sees, filters on, and pays is not always
	3091	+ * the trips-table base price: it can be overridden per
	3092	+ * - specific availability date (yatra_trip_availability_dates)
	3093	+ * - recurring availability rule (yatra_trip_availability_rules)
	3094	+ * - individual departure (yatra_trip_departures)
	3095	+ * each carrying its own original/discounted price and per-category
	3096	+ * (traveler-based) pricing JSON. The price-range slider bounds must span
	3097	+ * these too — otherwise a trip whose highest bookable price lives only on
	3098	+ * an override (e.g. a peak-season date priced €3,055 while the base is
	3099	+ * lower) is unreachable on the filter even though customers can book it.
	3100	+ *
	3101	+ * Effective-price semantics mirror the customer-facing display exactly by
	3102	+ * reusing collectTripDisplayPrices(): the discounted/sale price when set,
	3103	+ * otherwise the original — the same amount shown and charged. Non-bookable
	3104	+ * rows (blocked / cancelled / past dates, inactive rules) are excluded so
	3105	+ * they can't push the slider max beyond any price a customer can reach.
	3106	+ *
	3107	+ * @return array<int,float> Effective override prices (each > 0).
	3108	+ */
	3109	+ protected function collectPriceOverridePoints(): array
	3110	+ {
	3111	+ $prices = [];
	3112	+ $today = current_time('Y-m-d');
	3113	+
	3114	+ // Only overrides that belong to a genuinely available (published, not
	3115	+ // soft-deleted) trip may influence the bounds — otherwise a stray
	3116	+ // override on a draft/trashed trip leaks a phantom max that no
	3117	+ // customer-visible trip carries. Mirrors the trips-table filter the
	3118	+ // base bound uses.
	3119	+ $tripsTable = $this->getTableName();
	3120	+ $publishedJoin = "INNER JOIN {$tripsTable} t ON t.id = o.trip_id"
	3121	+ . " AND t.status IN ('publish', 'published')"
	3122	+ . " AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')";
	3123	+
	3124	+ // 1) Specific availability dates — same column shape as a trip row, so
	3125	+ // collectTripDisplayPrices() resolves them identically.
	3126	+ $datesTable = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName();
	3127	+ $dateRows = $this->wpdb->get_results($this->wpdb->prepare(
	3128	+ "SELECT o.original_price, o.discounted_price, NULL AS sale_price, o.price_types
	3129	+ FROM {$datesTable} o
	3130	+ {$publishedJoin}
	3131	+ WHERE COALESCE(o.is_blocked, 0) = 0
	3132	+ AND o.status NOT IN ('cancelled', 'blocked', 'closed')
	3133	+ AND (o.departure_date IS NULL OR o.departure_date >= %s)",
	3134	+ $today
	3135	+ )) ?: [];
	3136	+ foreach ($dateRows as $row) {
	3137	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3138	+ $prices[] = $p;
	3139	+ }
	3140	+ }
	3141	+
	3142	+ // 2) Recurring rules (active only — mirrors the `status = 'active'`
	3143	+ // filter RecurringAvailabilityRepository uses when generating
	3144	+ // availability). `traveler_pricing` holds the per-category JSON; a
	3145	+ // fixed `price_override` is an absolute per-booking price. A
	3146	+ // percentage override is a relative adjustment to the trip base —
	3147	+ // already spanned by the base bound — so it adds no new absolute max.
	3148	+ $rulesTable = \Yatra\Database\Tables\TripAvailabilityRulesTable::getTableName();
	3149	+ $ruleRows = $this->wpdb->get_results(
	3150	+ "SELECT o.original_price, o.sale_price, NULL AS discounted_price,
	3151	+ o.price_override, o.price_type, o.traveler_pricing AS price_types
	3152	+ FROM {$rulesTable} o
	3153	+ {$publishedJoin}
	3154	+ WHERE o.status = 'active'"
	3155	+ ) ?: [];
	3156	+ foreach ($ruleRows as $row) {
	3157	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3158	+ $prices[] = $p;
	3159	+ }
	3160	+ if ($row->price_override !== null && (string) $row->price_type !== 'percentage') {
	3161	+ $po = (float) $row->price_override;
	3162	+ if ($po > 0) {
	3163	+ $prices[] = $po;
	3164	+ }
	3165	+ }
	3166	+ }
	3167	+
	3168	+ // 3) Departures — `price_override` is an absolute price;
	3169	+ // `price_by_traveler_type` is the per-category JSON.
	3170	+ $depTable = \Yatra\Database\Tables\DeparturesTable::getTableName();
	3171	+ $depRows = $this->wpdb->get_results($this->wpdb->prepare(
	3172	+ "SELECT o.price_override AS original_price, NULL AS discounted_price,
	3173	+ NULL AS sale_price, o.price_by_traveler_type AS price_types
	3174	+ FROM {$depTable} o
	3175	+ {$publishedJoin}
	3176	+ WHERE o.status NOT IN ('cancelled')
	3177	+ AND (COALESCE(o.start_date, o.`date`) IS NULL OR COALESCE(o.start_date, o.`date`) >= %s)",
	3178	+ $today
	3179	+ )) ?: [];
	3180	+ foreach ($depRows as $row) {
	3181	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3182	+ $prices[] = $p;
	3183	+ }
	3184	+ }
	3185	+
	3186	+ return $prices;
	3187	+ }
	3188	+
	3189	+ /**
2612	3190	* Count trips by difficulty level
2613	3191	*
2614	3192	* @param int $difficultyLevelId Difficulty level ID
2615	3193	* @return int Number of trips with this difficulty level
		@@ -2771,35 +3349,78 @@
2771	3349	* Get price statistics for filter sidebar
2772	3350	*/
2773	3351	public function getPriceStats(): ?object
2774	3352	{
2775		- global $wpdb;
2776		-
2777	3353	$table = $this->getTableName();
2778		-
2779		- $result = $wpdb->get_row("
2780		- SELECT
2781		- MIN(sub.eff_price) as min_price,
2782		- MAX(sub.eff_price) as max_price,
2783		- AVG(sub.eff_price) as avg_price
2784		- FROM (
2785		- SELECT (CASE
2786		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2787		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2788		- ELSE CAST(original_price AS DECIMAL(10,2))
2789		- END) AS eff_price
2790		- FROM {$table}
2791		- WHERE status IN ('publish', 'published')
2792		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2793		- ) sub
2794		- WHERE sub.eff_price > 0
2795		- ");
2796		-
2797		- return $result ? (object) [
2798		- 'min_price' => (float) $result->min_price,
2799		- 'max_price' => (float) $result->max_price,
2800		- 'avg_price' => (float) $result->avg_price
2801		- ] : null;
	3354	+
	3355	+ // Bounds must cover EVERY price a customer can actually filter on.
	3356	+ // The search filter matches a trip if its legacy effective price OR
	3357	+ // ANY per-category price in `price_types` falls in range (see the
	3358	+ // price clause in the listing query), so the slider's min/max have to
	3359	+ // span the same set — otherwise a traveler-based trip whose highest
	3360	+ // tier (eg. Adult €3,055) lives only in `price_types` becomes
	3361	+ // unreachable when the column-only max stops short (eg. €3,045).
	3362	+ //
	3363	+ // Reuse `collectTripDisplayPrices()` — the single source of truth also
	3364	+ // used by `getPriceRangeStats()` and the listing/single-trip DISPLAY —
	3365	+ // so bound == display == filter for both regular and traveler-based
	3366	+ // pricing.
	3367	+ $rows = $this->wpdb->get_results(
	3368	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	3369	+ FROM {$table}
	3370	+ WHERE status IN ('publish', 'published')
	3371	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	3372	+ ) ?: [];
	3373	+
	3374	+ $minPrice = null;
	3375	+ $maxPrice = null;
	3376	+ $sum = 0.0;
	3377	+ $count = 0;
	3378	+
	3379	+ foreach ($rows as $row) {
	3380	+ foreach ($this->collectTripDisplayPrices($row) as $price) {
	3381	+ if ($price <= 0) {
	3382	+ continue;
	3383	+ }
	3384	+ if ($minPrice === null || $price < $minPrice) {
	3385	+ $minPrice = $price;
	3386	+ }
	3387	+ if ($maxPrice === null || $price > $maxPrice) {
	3388	+ $maxPrice = $price;
	3389	+ }
	3390	+ $sum += $price;
	3391	+ $count++;
	3392	+ }
	3393	+ }
	3394	+
	3395	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3396	+ // slider spans every price a customer can actually be charged (bounds
	3397	+ // only — the average stays a base-trip figure). See
	3398	+ // collectPriceOverridePoints().
	3399	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3400	+ if ($price <= 0) {
	3401	+ continue;
	3402	+ }
	3403	+ if ($minPrice === null || $price < $minPrice) {
	3404	+ $minPrice = $price;
	3405	+ }
	3406	+ if ($maxPrice === null || $price > $maxPrice) {
	3407	+ $maxPrice = $price;
	3408	+ }
	3409	+ }
	3410	+
	3411	+ if ($minPrice === null || $maxPrice === null) {
	3412	+ return null;
	3413	+ }
	3414	+
	3415	+ // Widen to whole units so the template's `(int)` cast on the bounds
	3416	+ // can never truncate a fractional boundary out of range (eg. a
	3417	+ // €3,055.50 max would otherwise int-cast to 3055 and exclude it).
	3418	+ return (object) [
	3419	+ 'min_price' => (float) floor($minPrice),
	3420	+ 'max_price' => (float) ceil($maxPrice),
	3421	+ 'avg_price' => $count > 0 ? (float) ($sum / $count) : 0.0,
	3422	+ ];
2802	3423	}
2803	3424
2804	3425	/**
2805	3426	* Distinct accommodation_type values on published trips with counts.