← All changes | app/Repositories/TripRepository.php +761 -101 3.0.2.6 → 3.0.17 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'])) {
		@@ -555,20 +691,21 @@
555	691	foreach (array_unique($filters['age_suitability']) as $age) {
556	692	if (!is_string($age)) {
557	693	continue;
558	694	}
	695	+ $ageLimits = self::ageSuitabilityThresholds();
559	696	switch ($age) {
560	697	case 'family-friendly':
561		- $ageParts[] = '(t.age_min IS NULL OR t.age_min <= 5)';
	698	+ $ageParts[] = '(t.age_min IS NULL OR t.age_min <= ' . (int) $ageLimits['family_max'] . ')';
562	699	break;
563	700	case 'kids-friendly':
564		- $ageParts[] = '(t.age_min IS NULL OR t.age_min <= 12)';
	701	+ $ageParts[] = '(t.age_min IS NULL OR t.age_min <= ' . (int) $ageLimits['kids_max'] . ')';
565	702	break;
566	703	case 'senior-friendly':
567		- $ageParts[] = '(t.age_max IS NULL OR t.age_max >= 65)';
	704	+ $ageParts[] = '(t.age_max IS NULL OR t.age_max >= ' . (int) $ageLimits['senior_min'] . ')';
568	705	break;
569	706	case 'adults-only':
570		- $ageParts[] = 't.age_min >= 18';
	707	+ $ageParts[] = 't.age_min >= ' . (int) $ageLimits['adults_min'];
571	708	break;
572	709	}
573	710	}
574	711	if ($ageParts !== []) {
		@@ -634,18 +771,71 @@
634	771	}
635	772	}
636	773	}
637	774
638		- // Price range filter (effective list price, not original-only)
	775	+ // Price range filter.
	776	+ //
	777	+ // The previous version compared a single legacy "effective"
	778	+ // price (discounted → sale → original column) against the
	779	+ // range. That excluded traveler-based trips whose real
	780	+ // displayed price lives in the `price_types` JSON — so eg. a
	781	+ // trip costing €3755 (Adult category) never appeared in search
	782	+ // when the user set max=€3755, because its legacy
	783	+ // original_price was €0 / stale.
	784	+ //
	785	+ // New approach: match a trip if EITHER the legacy effective
	786	+ // price OR ANY per-category price in the JSON falls in the
	787	+ // requested range. Numeric values in the `price_types` JSON
	788	+ // are extracted with a regex on the raw text — works in all
	789	+ // MySQL 5.7+ builds without needing JSON_VALUE / JSON_TABLE
	790	+ // (which are inconsistent across MariaDB / older MySQL).
639	791	$effPrice = $this->sqlTripEffectiveListPrice();
640		- if (!empty($filters['price_min']) && $filters['price_min'] > 0) {
641		- $wheres[] = "{$effPrice} >= %f";
642		- $params[] = $filters['price_min'];
643		- }
	792	+ $priceMin = !empty($filters['price_min']) && $filters['price_min'] > 0 ? (float) $filters['price_min'] : null;
	793	+ $priceMax = !empty($filters['price_max']) && $filters['price_max'] > 0 ? (float) $filters['price_max'] : null;
644	794
645		- if (!empty($filters['price_max']) && $filters['price_max'] > 0) {
646		- $wheres[] = "{$effPrice} <= %f";
647		- $params[] = $filters['price_max'];
	795	+ if ($priceMin !== null || $priceMax !== null) {
	796	+ $legacyCond = [];
	797	+ if ($priceMin !== null) { $legacyCond[] = "{$effPrice} >= %f"; $params[] = $priceMin; }
	798	+ if ($priceMax !== null) { $legacyCond[] = "{$effPrice} <= %f"; $params[] = $priceMax; }
	799	+ $legacyClause = implode(' AND ', $legacyCond);
	800	+
	801	+ // For per-category pricing, find ANY price in `price_types`
	802	+ // JSON that falls in the requested range. This subquery
	803	+ // creates an ad-hoc number sequence (n=0..49 covers up to
	804	+ // 50 categories — far more than any real trip uses) and
	805	+ // uses JSON_EXTRACT to fetch each entry's effective price.
	806	+ $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)";
	807	+ // Each category's effective price = first non-null of
	808	+ // discounted_price → sale_price → original_price → price.
	809	+ $categoryEff = "COALESCE("
	810	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].discounted_price'))) AS DECIMAL(10,2)), 0),"
	811	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].sale_price'))) AS DECIMAL(10,2)), 0),"
	812	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].original_price'))) AS DECIMAL(10,2)), 0),"
	813	+ . "NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, '].price'))) AS DECIMAL(10,2)), 0)"
	814	+ . ")";
	815	+ $catCond = [];
	816	+ if ($priceMin !== null) { $catCond[] = "{$categoryEff} >= %f"; $params[] = $priceMin; }
	817	+ if ($priceMax !== null) { $catCond[] = "{$categoryEff} <= %f"; $params[] = $priceMax; }
	818	+ $catClause = implode(' AND ', $catCond);
	819	+
	820	+ // Guard the per-category branch against malformed JSON.
	821	+ // JSON_EXTRACT throws on invalid JSON, and `price_types`
	822	+ // can be NULL, '', '[]' or a stale string on legacy rows.
	823	+ // JSON_VALID() + IFNULL() lets us bail cleanly for those.
	824	+ $perCategoryExists = "("
	825	+ . "t.price_types IS NOT NULL"
	826	+ . " AND t.price_types <> ''"
	827	+ . " AND JSON_VALID(t.price_types) = 1"
	828	+ . " AND EXISTS ("
	829	+ . "SELECT 1 FROM {$numbers} _n"
	830	+ . " WHERE JSON_EXTRACT(t.price_types, CONCAT('$[', _n.n, ']')) IS NOT NULL"
	831	+ . " AND {$catClause}"
	832	+ . ")"
	833	+ . ")";
	834	+
	835	+ // Match if EITHER condition passes — a single legacy-priced
	836	+ // trip OR a per-category trip with any matching tier.
	837	+ $wheres[] = "(({$legacyClause}) OR {$perCategoryExists})";
648	838	}
649	839
650	840	// Duration filter
651	841	if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) {
		@@ -656,9 +846,29 @@
656	846	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
657	847	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
658	848	$params[] = $filters['duration_max'];
659	849	}
660		-
	850	+
	851	+ // Availability date filter ("available from this date onward"): keep only
	852	+ // trips that have a departure starting ON OR AFTER the selected date, so
	853	+ // a customer who picks a date sees every trip they could still travel on
	854	+ // from then. A departure matches when its effective start date (the
	855	+ // explicit start_date, else the single `date`) is >= the chosen day and
	856	+ // it has not been cancelled — the date bound alone excludes past dates
	857	+ // when the picker's min=today is respected, so no reliance on a possibly
	858	+ // stale 'past' status. Clause + param are appended together so the WHERE
	859	+ // ordering stays in sync with $params (see the count/main queries).
	860	+ if (!empty($filters['available_date'])) {
	861	+ $departures_table = DeparturesTable::getTableName();
	862	+ $wheres[] = "EXISTS (
	863	+ SELECT 1 FROM {$departures_table} yd
	864	+ WHERE yd.trip_id = t.id
	865	+ AND COALESCE(NULLIF(yd.start_date, '0000-00-00'), yd.date) >= %s
	866	+ AND yd.status <> 'cancelled'
	867	+ )";
	868	+ $params[] = $filters['available_date'];
	869	+ }
	870	+
661	871	// Rating filter
662	872	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
663	873	$having_clauses[] = "AVG(r.rating) >= %f";
664	874	$rating_params[] = $filters['rating_min'];
		@@ -765,8 +975,10 @@
765	975	case 'price_high':
766	976	return "ORDER BY {$effPrice} DESC";
767	977	case 'rating_high':
768	978	return "ORDER BY average_rating DESC";
	979	+ case 'date_asc':
	980	+ return "ORDER BY t.created_at ASC";
769	981	case 'duration_short':
770	982	return "ORDER BY CAST(t.duration_days AS UNSIGNED) ASC, CAST(t.duration_nights AS UNSIGNED) ASC";
771	983	case 'duration_long':
772	984	return "ORDER BY CAST(t.duration_days AS UNSIGNED) DESC, CAST(t.duration_nights AS UNSIGNED) DESC";
		@@ -1135,8 +1347,9 @@
1135	1347	'discounted_price' => isset($pt['discounted_price']) ? (float) $pt['discounted_price'] : null,
1136	1348	'sale_price' => isset($pt['sale_price']) ? (float) $pt['sale_price'] : null,
1137	1349	'label' => $pt['label'] ?? ($pt['title'] ?? null),
1138	1350	'pricing_mode' => $pt['pricing_mode'] ?? 'per_person',
	1351	+ 'is_default' => !empty($pt['is_default']),
1139	1352	];
1140	1353	if (isset($pt['category_label'])) {
1141	1354	$normalized['category_label'] = $pt['category_label'];
1142	1355	}
		@@ -1607,11 +1820,47 @@
1607	1820	*/
1608	1821	public function savePriceTypes(int $tripId, array $priceTypes): void
1609	1822	{
1610	1823	if (empty($priceTypes)) {
	1824	+ // Explicitly clear stored per-category pricing so that deleting all
	1825	+ // rows (even the last one) persists. Previously this early-returned,
	1826	+ // leaving the old price_types JSON in place — the deletion silently
	1827	+ // reverted on reload. Only the JSON is always cleared; the derived
	1828	+ // scalar columns (original/discounted/sale) are reset solely for
	1829	+ // traveler-based trips, because for regular pricing those columns
	1830	+ // hold the actual price and must not be wiped.
	1831	+ $table = TripsTable::getTableName();
	1832	+ $pricingType = $this->wpdb->get_var(
	1833	+ $this->wpdb->prepare("SELECT pricing_type FROM `{$table}` WHERE id = %d", $tripId)
	1834	+ );
	1835	+
	1836	+ $data = ['price_types' => null];
	1837	+ if ($pricingType === 'traveler_based') {
	1838	+ $data['original_price'] = null;
	1839	+ $data['discounted_price'] = null;
	1840	+ $data['sale_price'] = null;
	1841	+ }
	1842	+
	1843	+ $this->wpdb->update($table, $data, ['id' => $tripId]);
1611	1844	return;
1612	1845	}
1613	1846
	1847	+ // Ensure at most one default category is set (keep the first truthy one).
	1848	+ $defaultFound = false;
	1849	+ foreach ($priceTypes as &$pt) {
	1850	+ if (!is_array($pt)) {
	1851	+ continue;
	1852	+ }
	1853	+ $isDefault = !empty($pt['is_default']);
	1854	+ if ($isDefault && !$defaultFound) {
	1855	+ $defaultFound = true;
	1856	+ $pt['is_default'] = true;
	1857	+ } else {
	1858	+ $pt['is_default'] = false;
	1859	+ }
	1860	+ }
	1861	+ unset($pt);
	1862	+
1614	1863	// Compute minimal pricing values from provided price types
1615	1864	$minOriginal = PHP_FLOAT_MAX;
1616	1865	$minDiscounted = PHP_FLOAT_MAX;
1617	1866	$minSale = PHP_FLOAT_MAX;
		@@ -1970,45 +2219,165 @@
1970	2219	));
1971	2220	}
1972	2221
1973	2222	/**
1974		- * Save availability dates for a trip
	2223	+ * Save availability dates for a trip.
	2224	+ *
	2225	+ * Previously a flat DELETE-THEN-INSERT: every row for the trip was nuked
	2226	+ * and the incoming list rewritten. That made the trip-edit save lossy —
	2227	+ * when the Trip form only knew about a subset of fields on each date
	2228	+ * (e.g. it was loaded once, then the operator changed the seats on a
	2229	+ * single date through the Availability tab, then re-saved the Trip with
	2230	+ * the form's stale date payload), every field absent from the incoming
	2231	+ * row was reset to its hard-coded default. That's why operator edits
	2232	+ * to `seats_total` silently reverted to 20 after another trip-save:
	2233	+ *
	2234	+ * 1. Operator opens Trip → form loads `availability_dates` with
	2235	+ * `seats_total = 20`.
	2236	+ * 2. Operator switches to Availability tab → edits Sept 5 to
	2237	+ * `seats_total = 14` via the dedicated specific-date endpoint
	2238	+ * (writes directly to the row, so the row is now 14).
	2239	+ * 3. Operator returns to the Trip form, edits something unrelated
	2240	+ * (title / description), clicks Save. The form re-posts the date
	2241	+ * list it loaded in step 1 — `seats_total = 20`.
	2242	+ * 4. saveAvailabilityDates DELETEs everything, INSERTs the
	2243	+ * form-supplied list → Sept 5 is back to 20 (or the literal `20`
	2244	+ * default if the form didn't include seats_total at all).
	2245	+ *
	2246	+ * Fix: snapshot the existing rows before the DELETE and, when the
	2247	+ * incoming row omits an editable field, fall back to the snapshot's
	2248	+ * value instead of the schema default. The literal `20` default is
	2249	+ * removed in favour of the trip's `max_travelers` (or 1 as a last
	2250	+ * resort) so new dates inserted by truly-new entries don't pretend the
	2251	+ * trip seats 20 people unless the trip actually says so.
	2252	+ *
	2253	+ * Match key for the snapshot: `departure_date` + `departure_time` (both
	2254	+ * normalised), which is the same identity the Availability UI uses.
1975	2255	*/
1976	2256	public function saveAvailabilityDates(int $tripId, array $availabilityDates): void
1977	2257	{
1978	2258	global $wpdb;
1979	2259	$table = TripAvailabilityDatesTable::getTableName();
1980		-
	2260	+
	2261	+ // Snapshot existing rows by date + time so partial incoming payloads
	2262	+ // can be merged with persisted values instead of clobbering them.
	2263	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from schema helper.
	2264	+ $existingRows = $wpdb->get_results($wpdb->prepare(
	2265	+ "SELECT * FROM {$table} WHERE trip_id = %d",
	2266	+ $tripId
	2267	+ ));
	2268	+ $snapshot = [];
	2269	+ if (is_array($existingRows)) {
	2270	+ foreach ($existingRows as $row) {
	2271	+ $key = self::availabilityRowKey(
	2272	+ (string) ($row->departure_date ?? ''),
	2273	+ (string) ($row->departure_time ?? '')
	2274	+ );
	2275	+ if ($key !== '') {
	2276	+ $snapshot[$key] = $row;
	2277	+ }
	2278	+ }
	2279	+ }
	2280	+
	2281	+ // Fallback capacity when both the incoming row and the snapshot
	2282	+ // miss seats_total: read the trip's max_travelers once. The
	2283	+ // legacy literal `20` was a phantom default that punished sites
	2284	+ // whose actual trip capacity differs.
	2285	+ $tripFallbackSeats = 0;
	2286	+ $tripRow = $this->find($tripId);
	2287	+ if (is_object($tripRow) && isset($tripRow->max_travelers)) {
	2288	+ $tripFallbackSeats = (int) $tripRow->max_travelers;
	2289	+ }
	2290	+ if ($tripFallbackSeats <= 0) {
	2291	+ $tripFallbackSeats = 1;
	2292	+ }
	2293	+
1981	2294	// Delete existing
1982	2295	$wpdb->delete($table, ['trip_id' => $tripId], ['%d']);
1983		-
	2296	+
1984	2297	// Insert new
1985	2298	if (!empty($availabilityDates)) {
1986	2299	foreach ($availabilityDates as $date) {
1987	2300	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		-
	2301	+ $key = self::availabilityRowKey(
	2302	+ (string) $date['departure_date'],
	2303	+ (string) ($date['departure_time'] ?? '')
	2304	+ );
	2305	+ $existing = $key !== '' && isset($snapshot[$key]) ? $snapshot[$key] : null;
	2306	+
	2307	+ $seatsTotal = isset($date['seats_total'])
	2308	+ ? (int) $date['seats_total']
	2309	+ : ($existing ? (int) ($existing->seats_total ?? $tripFallbackSeats) : $tripFallbackSeats);
	2310	+ $seatsAvailable = isset($date['seats_available'])
	2311	+ ? (int) $date['seats_available']
	2312	+ : ($existing ? (int) ($existing->seats_available ?? $seatsTotal) : $seatsTotal);
	2313	+
	2314	+ $arrivalDate = isset($date['arrival_date'])
	2315	+ ? sanitize_text_field($date['arrival_date'])
	2316	+ : (isset($date['return_date'])
	2317	+ ? sanitize_text_field($date['return_date'])
	2318	+ : ($existing ? $existing->arrival_date : null));
	2319	+ $returnDate = isset($date['return_date'])
	2320	+ ? sanitize_text_field($date['return_date'])
	2321	+ : ($existing ? $existing->return_date : null);
	2322	+ $arrivalTime = isset($date['arrival_time'])
	2323	+ ? sanitize_text_field($date['arrival_time'])
	2324	+ : ($existing ? $existing->arrival_time : null);
	2325	+
	2326	+ $originalPrice = isset($date['original_price'])
	2327	+ ? (float) $date['original_price']
	2328	+ : (isset($date['price_override'])
	2329	+ ? (float) $date['price_override']
	2330	+ : ($existing && $existing->original_price !== null ? (float) $existing->original_price : null));
	2331	+ $discountedPrice = isset($date['discounted_price'])
	2332	+ ? (float) $date['discounted_price']
	2333	+ : ($existing && $existing->discounted_price !== null ? (float) $existing->discounted_price : null);
	2334	+
	2335	+ $fromLocation = isset($date['from_location']) ? sanitize_text_field($date['from_location']) : ($existing ? $existing->from_location : null);
	2336	+ $toLocation = isset($date['to_location']) ? sanitize_text_field($date['to_location']) : ($existing ? $existing->to_location : null);
	2337	+ $fromLat = isset($date['from_latitude']) && is_numeric($date['from_latitude'])
	2338	+ ? (string) $date['from_latitude']
	2339	+ : ($existing ? $existing->from_latitude : null);
	2340	+ $fromLng = isset($date['from_longitude']) && is_numeric($date['from_longitude'])
	2341	+ ? (string) $date['from_longitude']
	2342	+ : ($existing ? $existing->from_longitude : null);
	2343	+ $toLat = isset($date['to_latitude']) && is_numeric($date['to_latitude'])
	2344	+ ? (string) $date['to_latitude']
	2345	+ : ($existing ? $existing->to_latitude : null);
	2346	+ $toLng = isset($date['to_longitude']) && is_numeric($date['to_longitude'])
	2347	+ ? (string) $date['to_longitude']
	2348	+ : ($existing ? $existing->to_longitude : null);
	2349	+
	2350	+ if (isset($date['is_blackout'])) {
	2351	+ $status = $date['is_blackout'] ? 'blocked' : 'available';
	2352	+ } elseif (isset($date['status'])) {
	2353	+ $status = sanitize_text_field($date['status']);
	2354	+ } elseif ($existing) {
	2355	+ $status = (string) ($existing->status ?? 'available');
	2356	+ } else {
	2357	+ $status = 'available';
	2358	+ }
	2359	+
1991	2360	$insertData = [
1992	2361	'trip_id' => $tripId,
1993	2362	'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,
	2363	+ 'arrival_date' => $arrivalDate,
	2364	+ 'return_date' => $returnDate,
	2365	+ 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : ($existing ? $existing->departure_time : null),
	2366	+ 'arrival_time' => $arrivalTime,
1998	2367	'seats_total' => $seatsTotal,
1999	2368	'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'),
	2369	+ 'original_price' => $originalPrice,
	2370	+ 'discounted_price' => $discountedPrice,
	2371	+ 'from_location' => $fromLocation,
	2372	+ 'to_location' => $toLocation,
	2373	+ 'from_latitude' => $fromLat,
	2374	+ 'from_longitude' => $fromLng,
	2375	+ 'to_latitude' => $toLat,
	2376	+ 'to_longitude' => $toLng,
	2377	+ 'status' => $status,
2009	2378	];
2010		-
	2379	+
2011	2380	$wpdb->insert(
2012	2381	$table,
2013	2382	$insertData,
2014	2383	['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
		@@ -2018,8 +2387,23 @@
2018	2387	}
2019	2388	}
2020	2389
2021	2390	/**
	2391	+ * Normalise a (date, time) pair to a single string key used to match
	2392	+ * incoming availability rows against the pre-delete snapshot. Time is
	2393	+ * left as-is for an exact comparison; empty time matches the "no
	2394	+ * specific time" row.
	2395	+ */
	2396	+ private static function availabilityRowKey(string $date, string $time): string
	2397	+ {
	2398	+ $date = trim($date);
	2399	+ if ($date === '') {
	2400	+ return '';
	2401	+ }
	2402	+ return $date . '|' . trim($time);
	2403	+ }
	2404	+
	2405	+ /**
2022	2406	* Save attributes for a trip
2023	2407	*/
2024	2408	public function saveAttributes(int $tripId, array $attributes): void
2025	2409	{
		@@ -2582,34 +2966,229 @@
2582	2966	));
2583	2967	}
2584	2968
2585	2969	/**
2586		- * Get price range statistics for published trips
2587		- *
	2970	+ * Get price range statistics for published trips.
	2971	+ *
	2972	+ * IMPORTANT: the previous SQL-only implementation only considered
	2973	+ * the legacy `original_price` / `discounted_price` / `sale_price`
	2974	+ * columns. For trips using traveler-based pricing (per-category
	2975	+ * prices stored in the `price_types` JSON column), those legacy
	2976	+ * columns are often empty or stale — so the computed max was lower
	2977	+ * than the actually-displayed price, and the price-range slider
	2978	+ * cut off above the real maximum (eg. trip cost €3755 but slider
	2979	+ * stopped at €3731). Worse, the trip then couldn't be filtered
	2980	+ * into the results because the effective-price WHERE excluded it.
	2981	+ *
	2982	+ * Fix: walk the published trips once in PHP and let
	2983	+ * `TripPricingService` decide each trip's effective price using
	2984	+ * the same logic the listing/single-trip pages use to DISPLAY the
	2985	+ * price. We also track the per-trip MIN/MAX across categories so
	2986	+ * the slider bounds cover every traveler tier (not only the
	2987	+ * default / cheapest).
	2988	+ *
2588	2989	* @return object Object with min_price and max_price properties
2589	2990	*/
2590	2991	public function getPriceRangeStats(): object
2591	2992	{
2592	2993	$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		- );
	2994	+ $rows = $this->wpdb->get_results(
	2995	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	2996	+ FROM {$table}
	2997	+ WHERE status IN ('publish', 'published')
	2998	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	2999	+ ) ?: [];
	3000	+
	3001	+ $minPrice = null;
	3002	+ $maxPrice = null;
	3003	+
	3004	+ foreach ($rows as $row) {
	3005	+ $tripPrices = $this->collectTripDisplayPrices($row);
	3006	+ foreach ($tripPrices as $price) {
	3007	+ if ($price <= 0) {
	3008	+ continue;
	3009	+ }
	3010	+ if ($minPrice === null || $price < $minPrice) {
	3011	+ $minPrice = $price;
	3012	+ }
	3013	+ if ($maxPrice === null || $price > $maxPrice) {
	3014	+ $maxPrice = $price;
	3015	+ }
	3016	+ }
	3017	+ }
	3018	+
	3019	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3020	+ // slider bounds span every price a customer can actually be charged,
	3021	+ // not just the trips-table base. See collectPriceOverridePoints().
	3022	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3023	+ if ($price <= 0) {
	3024	+ continue;
	3025	+ }
	3026	+ if ($minPrice === null || $price < $minPrice) {
	3027	+ $minPrice = $price;
	3028	+ }
	3029	+ if ($maxPrice === null || $price > $maxPrice) {
	3030	+ $maxPrice = $price;
	3031	+ }
	3032	+ }
	3033	+
	3034	+ return (object) [
	3035	+ 'min_price' => $minPrice,
	3036	+ 'max_price' => $maxPrice,
	3037	+ ];
2609	3038	}
2610	3039
2611	3040	/**
	3041	+ * Collect every price a single trip might display to a user.
	3042	+ *
	3043	+ * Returns BOTH the legacy "regular" effective price AND every
	3044	+ * per-category effective price from `price_types`. Used by
	3045	+ * `getPriceRangeStats()` so the price-range slider's bounds cover
	3046	+ * the full range a customer could see — eg. for a traveler-based
	3047	+ * trip with Adult €3755 / Child €1500 / Infant €100, the array
	3048	+ * includes all three so the slider stretches from €100 to €3755.
	3049	+ *
	3050	+ * @param object $row Raw trip row (must include `price_types`,
	3051	+ * `original_price`, `discounted_price`,
	3052	+ * `sale_price`).
	3053	+ * @return float[]
	3054	+ */
	3055	+ protected function collectTripDisplayPrices(object $row): array
	3056	+ {
	3057	+ $prices = [];
	3058	+
	3059	+ // Legacy regular pricing.
	3060	+ $legacyPrice = \Yatra\Services\TripPricingService::resolveRegularCurrentPrice($row);
	3061	+ if ($legacyPrice > 0) {
	3062	+ $prices[] = $legacyPrice;
	3063	+ }
	3064	+
	3065	+ // Per-category prices (traveler-based pricing). Decode whether
	3066	+ // `price_types` is stored as a JSON string (raw DB row) or an
	3067	+ // already-decoded array (in-memory trip object).
	3068	+ $priceTypes = $row->price_types ?? null;
	3069	+ if (is_string($priceTypes) && $priceTypes !== '') {
	3070	+ $decoded = json_decode($priceTypes, true);
	3071	+ if (is_array($decoded)) {
	3072	+ $priceTypes = $decoded;
	3073	+ }
	3074	+ }
	3075	+ if (is_array($priceTypes)) {
	3076	+ foreach ($priceTypes as $pt) {
	3077	+ $price = \Yatra\Services\TripPricingService::resolveCategoryEffectivePrice((array) $pt);
	3078	+ if ($price > 0) {
	3079	+ $prices[] = $price;
	3080	+ }
	3081	+ }
	3082	+ }
	3083	+
	3084	+ return $prices;
	3085	+ }
	3086	+
	3087	+ /**
	3088	+ * Effective price points that OVERRIDE a trip's base price for specific
	3089	+ * dates, recurring rules, or individual departures.
	3090	+ *
	3091	+ * The price a customer actually sees, filters on, and pays is not always
	3092	+ * the trips-table base price: it can be overridden per
	3093	+ * - specific availability date (yatra_trip_availability_dates)
	3094	+ * - recurring availability rule (yatra_trip_availability_rules)
	3095	+ * - individual departure (yatra_trip_departures)
	3096	+ * each carrying its own original/discounted price and per-category
	3097	+ * (traveler-based) pricing JSON. The price-range slider bounds must span
	3098	+ * these too — otherwise a trip whose highest bookable price lives only on
	3099	+ * an override (e.g. a peak-season date priced €3,055 while the base is
	3100	+ * lower) is unreachable on the filter even though customers can book it.
	3101	+ *
	3102	+ * Effective-price semantics mirror the customer-facing display exactly by
	3103	+ * reusing collectTripDisplayPrices(): the discounted/sale price when set,
	3104	+ * otherwise the original — the same amount shown and charged. Non-bookable
	3105	+ * rows (blocked / cancelled / past dates, inactive rules) are excluded so
	3106	+ * they can't push the slider max beyond any price a customer can reach.
	3107	+ *
	3108	+ * @return array<int,float> Effective override prices (each > 0).
	3109	+ */
	3110	+ protected function collectPriceOverridePoints(): array
	3111	+ {
	3112	+ $prices = [];
	3113	+ $today = current_time('Y-m-d');
	3114	+
	3115	+ // Only overrides that belong to a genuinely available (published, not
	3116	+ // soft-deleted) trip may influence the bounds — otherwise a stray
	3117	+ // override on a draft/trashed trip leaks a phantom max that no
	3118	+ // customer-visible trip carries. Mirrors the trips-table filter the
	3119	+ // base bound uses.
	3120	+ $tripsTable = $this->getTableName();
	3121	+ $publishedJoin = "INNER JOIN {$tripsTable} t ON t.id = o.trip_id"
	3122	+ . " AND t.status IN ('publish', 'published')"
	3123	+ . " AND (t.deleted_at IS NULL OR t.deleted_at = '0000-00-00 00:00:00')";
	3124	+
	3125	+ // 1) Specific availability dates — same column shape as a trip row, so
	3126	+ // collectTripDisplayPrices() resolves them identically.
	3127	+ $datesTable = \Yatra\Database\Tables\TripAvailabilityDatesTable::getTableName();
	3128	+ $dateRows = $this->wpdb->get_results($this->wpdb->prepare(
	3129	+ "SELECT o.original_price, o.discounted_price, NULL AS sale_price, o.price_types
	3130	+ FROM {$datesTable} o
	3131	+ {$publishedJoin}
	3132	+ WHERE COALESCE(o.is_blocked, 0) = 0
	3133	+ AND o.status NOT IN ('cancelled', 'blocked', 'closed')
	3134	+ AND (o.departure_date IS NULL OR o.departure_date >= %s)",
	3135	+ $today
	3136	+ )) ?: [];
	3137	+ foreach ($dateRows as $row) {
	3138	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3139	+ $prices[] = $p;
	3140	+ }
	3141	+ }
	3142	+
	3143	+ // 2) Recurring rules (active only — mirrors the `status = 'active'`
	3144	+ // filter RecurringAvailabilityRepository uses when generating
	3145	+ // availability). `traveler_pricing` holds the per-category JSON; a
	3146	+ // fixed `price_override` is an absolute per-booking price. A
	3147	+ // percentage override is a relative adjustment to the trip base —
	3148	+ // already spanned by the base bound — so it adds no new absolute max.
	3149	+ $rulesTable = \Yatra\Database\Tables\TripAvailabilityRulesTable::getTableName();
	3150	+ $ruleRows = $this->wpdb->get_results(
	3151	+ "SELECT o.original_price, o.sale_price, NULL AS discounted_price,
	3152	+ o.price_override, o.price_type, o.traveler_pricing AS price_types
	3153	+ FROM {$rulesTable} o
	3154	+ {$publishedJoin}
	3155	+ WHERE o.status = 'active'"
	3156	+ ) ?: [];
	3157	+ foreach ($ruleRows as $row) {
	3158	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3159	+ $prices[] = $p;
	3160	+ }
	3161	+ if ($row->price_override !== null && (string) $row->price_type !== 'percentage') {
	3162	+ $po = (float) $row->price_override;
	3163	+ if ($po > 0) {
	3164	+ $prices[] = $po;
	3165	+ }
	3166	+ }
	3167	+ }
	3168	+
	3169	+ // 3) Departures — `price_override` is an absolute price;
	3170	+ // `price_by_traveler_type` is the per-category JSON.
	3171	+ $depTable = \Yatra\Database\Tables\DeparturesTable::getTableName();
	3172	+ $depRows = $this->wpdb->get_results($this->wpdb->prepare(
	3173	+ "SELECT o.price_override AS original_price, NULL AS discounted_price,
	3174	+ NULL AS sale_price, o.price_by_traveler_type AS price_types
	3175	+ FROM {$depTable} o
	3176	+ {$publishedJoin}
	3177	+ WHERE o.status NOT IN ('cancelled')
	3178	+ AND (COALESCE(o.start_date, o.`date`) IS NULL OR COALESCE(o.start_date, o.`date`) >= %s)",
	3179	+ $today
	3180	+ )) ?: [];
	3181	+ foreach ($depRows as $row) {
	3182	+ foreach ($this->collectTripDisplayPrices($row) as $p) {
	3183	+ $prices[] = $p;
	3184	+ }
	3185	+ }
	3186	+
	3187	+ return $prices;
	3188	+ }
	3189	+
	3190	+ /**
2612	3191	* Count trips by difficulty level
2613	3192	*
2614	3193	* @param int $difficultyLevelId Difficulty level ID
2615	3194	* @return int Number of trips with this difficulty level
		@@ -2771,35 +3350,78 @@
2771	3350	* Get price statistics for filter sidebar
2772	3351	*/
2773	3352	public function getPriceStats(): ?object
2774	3353	{
2775		- global $wpdb;
2776		-
2777	3354	$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;
	3355	+
	3356	+ // Bounds must cover EVERY price a customer can actually filter on.
	3357	+ // The search filter matches a trip if its legacy effective price OR
	3358	+ // ANY per-category price in `price_types` falls in range (see the
	3359	+ // price clause in the listing query), so the slider's min/max have to
	3360	+ // span the same set — otherwise a traveler-based trip whose highest
	3361	+ // tier (eg. Adult €3,055) lives only in `price_types` becomes
	3362	+ // unreachable when the column-only max stops short (eg. €3,045).
	3363	+ //
	3364	+ // Reuse `collectTripDisplayPrices()` — the single source of truth also
	3365	+ // used by `getPriceRangeStats()` and the listing/single-trip DISPLAY —
	3366	+ // so bound == display == filter for both regular and traveler-based
	3367	+ // pricing.
	3368	+ $rows = $this->wpdb->get_results(
	3369	+ "SELECT id, original_price, discounted_price, sale_price, price_types
	3370	+ FROM {$table}
	3371	+ WHERE status IN ('publish', 'published')
	3372	+ AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')"
	3373	+ ) ?: [];
	3374	+
	3375	+ $minPrice = null;
	3376	+ $maxPrice = null;
	3377	+ $sum = 0.0;
	3378	+ $count = 0;
	3379	+
	3380	+ foreach ($rows as $row) {
	3381	+ foreach ($this->collectTripDisplayPrices($row) as $price) {
	3382	+ if ($price <= 0) {
	3383	+ continue;
	3384	+ }
	3385	+ if ($minPrice === null || $price < $minPrice) {
	3386	+ $minPrice = $price;
	3387	+ }
	3388	+ if ($maxPrice === null || $price > $maxPrice) {
	3389	+ $maxPrice = $price;
	3390	+ }
	3391	+ $sum += $price;
	3392	+ $count++;
	3393	+ }
	3394	+ }
	3395	+
	3396	+ // Fold in per-date / per-rule / per-departure price overrides so the
	3397	+ // slider spans every price a customer can actually be charged (bounds
	3398	+ // only — the average stays a base-trip figure). See
	3399	+ // collectPriceOverridePoints().
	3400	+ foreach ($this->collectPriceOverridePoints() as $price) {
	3401	+ if ($price <= 0) {
	3402	+ continue;
	3403	+ }
	3404	+ if ($minPrice === null || $price < $minPrice) {
	3405	+ $minPrice = $price;
	3406	+ }
	3407	+ if ($maxPrice === null || $price > $maxPrice) {
	3408	+ $maxPrice = $price;
	3409	+ }
	3410	+ }
	3411	+
	3412	+ if ($minPrice === null || $maxPrice === null) {
	3413	+ return null;
	3414	+ }
	3415	+
	3416	+ // Widen to whole units so the template's `(int)` cast on the bounds
	3417	+ // can never truncate a fractional boundary out of range (eg. a
	3418	+ // €3,055.50 max would otherwise int-cast to 3055 and exclude it).
	3419	+ return (object) [
	3420	+ 'min_price' => (float) floor($minPrice),
	3421	+ 'max_price' => (float) ceil($maxPrice),
	3422	+ 'avg_price' => $count > 0 ? (float) ($sum / $count) : 0.0,
	3423	+ ];
2802	3424	}
2803	3425
2804	3426	/**
2805	3427	* Distinct accommodation_type values on published trips with counts.
		@@ -3109,16 +3731,48 @@
3109	3731	return 0;
3110	3732	}
3111	3733
3112	3734	/**
	3735	+ * Age thresholds behind the suitability filters.
	3736	+ *
	3737	+ * These were repeated as literals in six places — the filter query and the
	3738	+ * count query for each band — so an operator whose "kids" means under 18
	3739	+ * rather than under 12 had no way to say so, and changing it meant editing
	3740	+ * the same number in six spots and hoping none were missed.
	3741	+ *
	3742	+ * @return array{family_max:int, kids_max:int, senior_min:int, adults_min:int}
	3743	+ */
	3744	+ public static function ageSuitabilityThresholds(): array
	3745	+ {
	3746	+ $defaults = [
	3747	+ 'family_max' => 5,
	3748	+ 'kids_max' => 12,
	3749	+ 'senior_min' => 65,
	3750	+ 'adults_min' => 18,
	3751	+ ];
	3752	+
	3753	+ $filtered = (array) apply_filters('yatra_age_suitability_thresholds', $defaults);
	3754	+
	3755	+ foreach ($defaults as $key => $fallback) {
	3756	+ $filtered[$key] = isset($filtered[$key]) && is_numeric($filtered[$key])
	3757	+ ? (int) $filtered[$key]
	3758	+ : $fallback;
	3759	+ }
	3760	+
	3761	+ return $filtered;
	3762	+ }
	3763	+
	3764	+ /**
3113	3765	* Count family friendly trips
3114	3766	*/
3115	3767	public function countByFamilyFriendly(): int
3116	3768	{
3117	3769	$table = $this->getTableName();
	3770	+ $limit = (int) self::ageSuitabilityThresholds()['family_max'];
	3771	+
3118	3772	return (int) $this->wpdb->get_var(
3119		- "SELECT COUNT(*) FROM {$table}
3120		- WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 5)"
	3773	+ "SELECT COUNT(*) FROM {$table}
	3774	+ WHERE status = 'publish' AND (age_min IS NULL OR age_min <= {$limit})"
3121	3775	);
3122	3776	}
3123	3777
3124	3778	/**
		@@ -3126,11 +3780,13 @@
3126	3780	*/
3127	3781	public function countByKidsFriendly(): int
3128	3782	{
3129	3783	$table = $this->getTableName();
	3784	+ $limit = (int) self::ageSuitabilityThresholds()['kids_max'];
	3785	+
3130	3786	return (int) $this->wpdb->get_var(
3131		- "SELECT COUNT(*) FROM {$table}
3132		- WHERE status = 'publish' AND (age_min IS NULL OR age_min <= 12)"
	3787	+ "SELECT COUNT(*) FROM {$table}
	3788	+ WHERE status = 'publish' AND (age_min IS NULL OR age_min <= {$limit})"
3133	3789	);
3134	3790	}
3135	3791
3136	3792	/**
		@@ -3138,11 +3794,13 @@
3138	3794	*/
3139	3795	public function countBySeniorFriendly(): int
3140	3796	{
3141	3797	$table = $this->getTableName();
	3798	+ $limit = (int) self::ageSuitabilityThresholds()['senior_min'];
	3799	+
3142	3800	return (int) $this->wpdb->get_var(
3143		- "SELECT COUNT(*) FROM {$table}
3144		- WHERE status = 'publish' AND (age_max IS NULL OR age_max >= 65)"
	3801	+ "SELECT COUNT(*) FROM {$table}
	3802	+ WHERE status = 'publish' AND (age_max IS NULL OR age_max >= {$limit})"
3145	3803	);
3146	3804	}
3147	3805
3148	3806	/**
		@@ -3150,11 +3808,13 @@
3150	3808	*/
3151	3809	public function countByAdultsOnly(): int
3152	3810	{
3153	3811	$table = $this->getTableName();
	3812	+ $limit = (int) self::ageSuitabilityThresholds()['adults_min'];
	3813	+
3154	3814	return (int) $this->wpdb->get_var(
3155		- "SELECT COUNT(*) FROM {$table}
3156		- WHERE status = 'publish' AND age_min >= 18"
	3815	+ "SELECT COUNT(*) FROM {$table}
	3816	+ WHERE status = 'publish' AND age_min >= {$limit}"
3157	3817	);
3158	3818	}
3159	3819
3160	3820	/**