← All changes | app/Repositories/TripRepository.php +626 -86 3.0.4 → 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
		@@ -270,8 +271,9 @@
270	271	* Create trip row; {@see afterWrite} handles cache; fires action `yatra_trip_created` once per insert.
271	272	*/
272	273	public function create(array $data): int
273	274	{
	275	+ $data = $this->dropUnsupportedDurationHours($data);
274	276	$id = parent::create($data);
275	277	do_action('yatra_trip_created', $id);
276	278
277	279	return $id;
		@@ -277,12 +279,32 @@
277	279	return $id;
278	280	}
279	281
280	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	+ /**
281	302	* Override update method to provide proper field formats
282	303	*/
283	304	public function update(int $id, array $data): bool
284	305	{
	306	+ $data = $this->dropUnsupportedDurationHours($data);
285	307	$data = $this->sanitizeData($data);
286	308	$data['updated_at'] = current_time('mysql');
287	309
288	310	// Build format array based on field types
		@@ -400,8 +422,21 @@
400	422	return self::$tripColumnExistsCache[$column];
401	423	}
402	424
403	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	+ /**
404	439	* SQL expression for trip "current" list price (matches TripPricingService::resolveRegularCurrentPrice).
405	440	*/
406	441	protected function sqlTripEffectiveListPrice(): string
407	442	{
		@@ -481,9 +516,9 @@
481	516	$params[] = $filters['trip_type'];
482	517	}
483	518	}
484	519
485		- // Featured Priority (column on yatra_new_trips, indexed by idx_featured_priority).
	520	+ // Featured Priority (column on yatra_trips, indexed by idx_featured_priority).
486	521	// Admin form's Featured Priority dropdown is the single source of truth.
487	522	// Legacy `is_featured` shortcode/block flag is normalised upstream into featured_priority='featured'.
488	523	if (
489	524	!empty($filters['featured_priority'])
		@@ -656,20 +691,21 @@
656	691	foreach (array_unique($filters['age_suitability']) as $age) {
657	692	if (!is_string($age)) {
658	693	continue;
659	694	}
	695	+ $ageLimits = self::ageSuitabilityThresholds();
660	696	switch ($age) {
661	697	case 'family-friendly':
662		- $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'] . ')';
663	699	break;
664	700	case 'kids-friendly':
665		- $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'] . ')';
666	702	break;
667	703	case 'senior-friendly':
668		- $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'] . ')';
669	705	break;
670	706	case 'adults-only':
671		- $ageParts[] = 't.age_min >= 18';
	707	+ $ageParts[] = 't.age_min >= ' . (int) $ageLimits['adults_min'];
672	708	break;
673	709	}
674	710	}
675	711	if ($ageParts !== []) {
		@@ -735,18 +771,71 @@
735	771	}
736	772	}
737	773	}
738	774
739		- // 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).
740	791	$effPrice = $this->sqlTripEffectiveListPrice();
741		- if (!empty($filters['price_min']) && $filters['price_min'] > 0) {
742		- $wheres[] = "{$effPrice} >= %f";
743		- $params[] = $filters['price_min'];
744		- }
	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;
745	794
746		- if (!empty($filters['price_max']) && $filters['price_max'] > 0) {
747		- $wheres[] = "{$effPrice} <= %f";
748		- $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})";
749	838	}
750	839
751	840	// Duration filter
752	841	if (!empty($filters['duration_min']) && $filters['duration_min'] > 0) {
		@@ -757,9 +846,29 @@
757	846	if (!empty($filters['duration_max']) && $filters['duration_max'] > 0) {
758	847	$wheres[] = "CAST(t.duration_days AS UNSIGNED) <= %d";
759	848	$params[] = $filters['duration_max'];
760	849	}
761		-
	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	+
762	871	// Rating filter
763	872	if (!empty($filters['rating_min']) && $filters['rating_min'] > 0) {
764	873	$having_clauses[] = "AVG(r.rating) >= %f";
765	874	$rating_params[] = $filters['rating_min'];
		@@ -1711,8 +1820,28 @@
1711	1820	*/
1712	1821	public function savePriceTypes(int $tripId, array $priceTypes): void
1713	1822	{
1714	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]);
1715	1844	return;
1716	1845	}
1717	1846
1718	1847	// Ensure at most one default category is set (keep the first truthy one).
		@@ -2090,45 +2219,165 @@
2090	2219	));
2091	2220	}
2092	2221
2093	2222	/**
2094		- * 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.
2095	2255	*/
2096	2256	public function saveAvailabilityDates(int $tripId, array $availabilityDates): void
2097	2257	{
2098	2258	global $wpdb;
2099	2259	$table = TripAvailabilityDatesTable::getTableName();
2100		-
	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	+
2101	2294	// Delete existing
2102	2295	$wpdb->delete($table, ['trip_id' => $tripId], ['%d']);
2103		-
	2296	+
2104	2297	// Insert new
2105	2298	if (!empty($availabilityDates)) {
2106	2299	foreach ($availabilityDates as $date) {
2107	2300	if (is_array($date) && !empty($date['departure_date'])) {
2108		- $seatsTotal = isset($date['seats_total']) ? (int) $date['seats_total'] : 20;
2109		- $seatsAvailable = isset($date['seats_available']) ? (int) $date['seats_available'] : $seatsTotal;
2110		-
	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	+
2111	2360	$insertData = [
2112	2361	'trip_id' => $tripId,
2113	2362	'departure_date' => sanitize_text_field($date['departure_date']),
2114		- 'arrival_date' => isset($date['arrival_date']) ? sanitize_text_field($date['arrival_date']) : ($date['return_date'] ?? null),
2115		- 'return_date' => isset($date['return_date']) ? sanitize_text_field($date['return_date']) : null,
2116		- 'departure_time' => isset($date['departure_time']) ? sanitize_text_field($date['departure_time']) : null,
2117		- '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,
2118	2367	'seats_total' => $seatsTotal,
2119	2368	'seats_available' => $seatsAvailable,
2120		- 'original_price' => isset($date['original_price']) ? (float) $date['original_price'] : (isset($date['price_override']) ? (float) $date['price_override'] : null),
2121		- 'discounted_price' => isset($date['discounted_price']) ? (float) $date['discounted_price'] : null,
2122		- 'from_location' => isset($date['from_location']) ? sanitize_text_field($date['from_location']) : null,
2123		- 'to_location' => isset($date['to_location']) ? sanitize_text_field($date['to_location']) : null,
2124		- 'from_latitude' => isset($date['from_latitude']) && is_numeric($date['from_latitude']) ? (string) $date['from_latitude'] : null,
2125		- 'from_longitude' => isset($date['from_longitude']) && is_numeric($date['from_longitude']) ? (string) $date['from_longitude'] : null,
2126		- 'to_latitude' => isset($date['to_latitude']) && is_numeric($date['to_latitude']) ? (string) $date['to_latitude'] : null,
2127		- 'to_longitude' => isset($date['to_longitude']) && is_numeric($date['to_longitude']) ? (string) $date['to_longitude'] : null,
2128		- '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,
2129	2378	];
2130		-
	2379	+
2131	2380	$wpdb->insert(
2132	2381	$table,
2133	2382	$insertData,
2134	2383	['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
		@@ -2138,8 +2387,23 @@
2138	2387	}
2139	2388	}
2140	2389
2141	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	+ /**
2142	2406	* Save attributes for a trip
2143	2407	*/
2144	2408	public function saveAttributes(int $tripId, array $attributes): void
2145	2409	{
		@@ -2702,34 +2966,229 @@
2702	2966	));
2703	2967	}
2704	2968
2705	2969	/**
2706		- * Get price range statistics for published trips
2707		- *
	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	+ *
2708	2989	* @return object Object with min_price and max_price properties
2709	2990	*/
2710	2991	public function getPriceRangeStats(): object
2711	2992	{
2712	2993	$table = $this->getTableName();
2713		- return $this->wpdb->get_row(
2714		- "SELECT
2715		- MIN(sub.eff_price) as min_price,
2716		- MAX(sub.eff_price) as max_price
2717		- FROM (
2718		- SELECT (CASE
2719		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2720		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2721		- ELSE CAST(original_price AS DECIMAL(10,2))
2722		- END) AS eff_price
2723		- FROM {$table}
2724		- WHERE status IN ('publish', 'published')
2725		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2726		- ) sub
2727		- WHERE sub.eff_price > 0"
2728		- );
	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	+ ];
2729	3038	}
2730	3039
2731	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	+ /**
2732	3191	* Count trips by difficulty level
2733	3192	*
2734	3193	* @param int $difficultyLevelId Difficulty level ID
2735	3194	* @return int Number of trips with this difficulty level
		@@ -2891,35 +3350,78 @@
2891	3350	* Get price statistics for filter sidebar
2892	3351	*/
2893	3352	public function getPriceStats(): ?object
2894	3353	{
2895		- global $wpdb;
2896		-
2897	3354	$table = $this->getTableName();
2898		-
2899		- $result = $wpdb->get_row("
2900		- SELECT
2901		- MIN(sub.eff_price) as min_price,
2902		- MAX(sub.eff_price) as max_price,
2903		- AVG(sub.eff_price) as avg_price
2904		- FROM (
2905		- SELECT (CASE
2906		- WHEN CAST(discounted_price AS DECIMAL(10,2)) > 0 THEN CAST(discounted_price AS DECIMAL(10,2))
2907		- WHEN CAST(sale_price AS DECIMAL(10,2)) > 0 THEN CAST(sale_price AS DECIMAL(10,2))
2908		- ELSE CAST(original_price AS DECIMAL(10,2))
2909		- END) AS eff_price
2910		- FROM {$table}
2911		- WHERE status IN ('publish', 'published')
2912		- AND (deleted_at IS NULL OR deleted_at = '0000-00-00 00:00:00')
2913		- ) sub
2914		- WHERE sub.eff_price > 0
2915		- ");
2916		-
2917		- return $result ? (object) [
2918		- 'min_price' => (float) $result->min_price,
2919		- 'max_price' => (float) $result->max_price,
2920		- 'avg_price' => (float) $result->avg_price
2921		- ] : 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	+ ];
2922	3424	}
2923	3425
2924	3426	/**
2925	3427	* Distinct accommodation_type values on published trips with counts.
		@@ -3229,16 +3731,48 @@
3229	3731	return 0;
3230	3732	}
3231	3733
3232	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	+ /**
3233	3765	* Count family friendly trips
3234	3766	*/
3235	3767	public function countByFamilyFriendly(): int
3236	3768	{
3237	3769	$table = $this->getTableName();
	3770	+ $limit = (int) self::ageSuitabilityThresholds()['family_max'];
	3771	+
3238	3772	return (int) $this->wpdb->get_var(
3239		- "SELECT COUNT(*) FROM {$table}
3240		- 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})"
3241	3775	);
3242	3776	}
3243	3777
3244	3778	/**
		@@ -3246,11 +3780,13 @@
3246	3780	*/
3247	3781	public function countByKidsFriendly(): int
3248	3782	{
3249	3783	$table = $this->getTableName();
	3784	+ $limit = (int) self::ageSuitabilityThresholds()['kids_max'];
	3785	+
3250	3786	return (int) $this->wpdb->get_var(
3251		- "SELECT COUNT(*) FROM {$table}
3252		- 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})"
3253	3789	);
3254	3790	}
3255	3791
3256	3792	/**
		@@ -3258,11 +3794,13 @@
3258	3794	*/
3259	3795	public function countBySeniorFriendly(): int
3260	3796	{
3261	3797	$table = $this->getTableName();
	3798	+ $limit = (int) self::ageSuitabilityThresholds()['senior_min'];
	3799	+
3262	3800	return (int) $this->wpdb->get_var(
3263		- "SELECT COUNT(*) FROM {$table}
3264		- 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})"
3265	3803	);
3266	3804	}
3267	3805
3268	3806	/**
		@@ -3270,11 +3808,13 @@
3270	3808	*/
3271	3809	public function countByAdultsOnly(): int
3272	3810	{
3273	3811	$table = $this->getTableName();
	3812	+ $limit = (int) self::ageSuitabilityThresholds()['adults_min'];
	3813	+
3274	3814	return (int) $this->wpdb->get_var(
3275		- "SELECT COUNT(*) FROM {$table}
3276		- WHERE status = 'publish' AND age_min >= 18"
	3815	+ "SELECT COUNT(*) FROM {$table}
	3816	+ WHERE status = 'publish' AND age_min >= {$limit}"
3277	3817	);
3278	3818	}
3279	3819
3280	3820	/**