yatra / app / Repositories / ReviewRepository.php

ReviewRepository.php in Yatra – Travel Booking & Tour Operator Software 3.0.5, at app/Repositories/ReviewRepository.php

592 lines 16.3 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Repositories;
6
7	use Yatra\Database\Tables\ReviewsTable;
8
9	/**
10	* Review Repository
11	*
12	* Handles all database operations for trip reviews.
13	*
14	* @package Yatra\Repositories
15	*/
16	class ReviewRepository extends BaseRepository
17	{
18	/**
19	* Get full table name with prefix
20	*/
21	protected function getTableName(): string
22	{
23	return ReviewsTable::getTableName();
24	}
25
26	/**
27	* Get trips table name
28	*/
29	protected function getTripsTable(): string
30	{
31	// Use TripRepository for trips table
32	$tripRepository = new \Yatra\Repositories\TripRepository();
33	return $tripRepository->getTableName();
34	}
35
36	/**
37	* Get users table name
38	*/
39	protected function getUsersTable(): string
40	{
41	return $this->wpdb->users;
42	}
43
44	/**
45	* SQL predicate: review counts as approved on the frontend (ENUM-safe, trims whitespace).
46	*
47	* @param string $expr Column or qualified name, e.g. "status" or "r.status"
48	*/
49	public function sqlApprovedReviewsWhere(string $expr = 'status'): string
50	{
51	if (!preg_match('/^[a-zA-Z0-9_.]+$/', $expr)) {
52	$expr = 'status';
53	}
54
55	return "LOWER(TRIM(CAST({$expr} AS CHAR))) = 'approved'";
56	}
57
58	/**
59	* Get paginated reviews with filters
60	*
61	* @param array $filters Filter options
62	* @return array {data: array, total: int, page: int, per_page: int, total_pages: int}
63	*/
64	public function paginate(array $filters = []): array
65	{
66	$table = $this->getTableName();
67	$trips_table = $this->getTripsTable();
68	$users_table = $this->getUsersTable();
69
70	// Pagination
71	$page = max(1, (int) ($filters['page'] ?? 1));
72	$per_page = max(1, min(100, (int) ($filters['per_page'] ?? 20)));
73	$offset = ($page - 1) * $per_page;
74
75	// Build WHERE clause
76	$where_clauses = ['1=1'];
77	$where_values = [];
78
79	if (!empty($filters['status'])) {
80	$where_clauses[] = 'r.status = %s';
81	$where_values[] = sanitize_text_field($filters['status']);
82	}
83
84	if (!empty($filters['trip_id'])) {
85	$where_clauses[] = 'r.trip_id = %d';
86	$where_values[] = (int) $filters['trip_id'];
87	}
88
89	if (!empty($filters['rating'])) {
90	$where_clauses[] = 'r.rating = %d';
91	$where_values[] = (int) $filters['rating'];
92	}
93
94	if (!empty($filters['search'])) {
95	$search_like = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['search'])) . '%';
96	$where_clauses[] = '(r.title LIKE %s OR r.content LIKE %s OR r.author_name LIKE %s OR r.author_email LIKE %s)';
97	$where_values = array_merge($where_values, [$search_like, $search_like, $search_like, $search_like]);
98	}
99
100	$where_sql = implode(' AND ', $where_clauses);
101
102	// Get total count
103	$count_query = "SELECT COUNT(*) FROM {$table} r WHERE {$where_sql}";
104	if (!empty($where_values)) {
105	$count_query = $this->wpdb->prepare($count_query, ...$where_values);
106	}
107	$total = (int) $this->wpdb->get_var($count_query);
108
109	// Get reviews with trip and user info
110	$query = "SELECT r.*,
111	t.title as trip_title,
112	t.slug as trip_slug,
113	u.display_name as user_display_name
114	FROM {$table} r
115	LEFT JOIN {$trips_table} t ON r.trip_id = t.id
116	LEFT JOIN {$users_table} u ON r.user_id = u.ID
117	WHERE {$where_sql}
118	ORDER BY r.created_at DESC
119	LIMIT %d OFFSET %d";
120
121	$query_values = array_merge($where_values, [$per_page, $offset]);
122	$reviews = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values));
123
124
125	$return_data = [
126	'data' => $reviews ?: [],
127	'total' => $total,
128	'page' => $page,
129	'per_page' => $per_page,
130	'total_pages' => (int) ceil($total / $per_page),
131	];
132
133
134	return $return_data;
135	}
136
137	/**
138	* Find review by ID with trip info
139	*
140	* @param int $id Review ID
141	* @return object\|null
142	*/
143	public function findWithTrip(int $id): ?object
144	{
145	$table = $this->getTableName();
146	$trips_table = $this->getTripsTable();
147	$users_table = $this->getUsersTable();
148
149	$query = $this->wpdb->prepare(
150	"SELECT r.*,
151	t.title as trip_title,
152	t.slug as trip_slug,
153	u.display_name as user_display_name
154	FROM {$table} r
155	LEFT JOIN {$trips_table} t ON r.trip_id = t.id
156	LEFT JOIN {$users_table} u ON r.user_id = u.ID
157	WHERE r.id = %d",
158	$id
159	);
160
161	return $this->wpdb->get_row($query) ?: null;
162	}
163
164	/**
165	* Find approved reviews for a trip
166	*
167	* @param int $tripId Trip ID
168	* @param int $limit Limit results
169	* @return array
170	*/
171	public function findApprovedByTripId(int $tripId, int $limit = 10): array
172	{
173	$table = $this->getTableName();
174	$users_table = $this->getUsersTable();
175
176	$query = $this->wpdb->prepare(
177	"SELECT r.*, u.display_name as user_display_name
178	FROM {$table} r
179	LEFT JOIN {$users_table} u ON r.user_id = u.ID
180	WHERE r.trip_id = %d AND {$this->sqlApprovedReviewsWhere('r.status')}
181	ORDER BY r.created_at DESC
182	LIMIT %d",
183	$tripId,
184	$limit
185	);
186
187	return $this->wpdb->get_results($query) ?: [];
188	}
189
190	/**
191	* Find review by user and trip
192	*
193	* @param int $userId User ID
194	* @param int $tripId Trip ID
195	* @return object\|null
196	*/
197	public function findByUserAndTrip(int $userId, int $tripId): ?object
198	{
199	$table = $this->getTableName();
200
201	$query = $this->wpdb->prepare(
202	"SELECT * FROM {$table} WHERE user_id = %d AND trip_id = %d",
203	$userId,
204	$tripId
205	);
206
207	return $this->wpdb->get_row($query) ?: null;
208	}
209
210	/**
211	* Find reviews by user ID
212	*
213	* @param int $userId User ID
214	* @return array
215	*/
216	public function findByUserId(int $userId): array
217	{
218	$table = $this->getTableName();
219	$trips_table = $this->getTripsTable();
220
221	$query = $this->wpdb->prepare(
222	"SELECT r.*, t.title as trip_title
223	FROM {$table} r
224	LEFT JOIN {$trips_table} t ON r.trip_id = t.id
225	WHERE r.user_id = %d
226	ORDER BY r.created_at DESC",
227	$userId
228	);
229
230	return $this->wpdb->get_results($query) ?: [];
231	}
232
233	/**
234	* Create a new review
235	*
236	* @param array $data Review data
237	* @return int Review ID on success
238	* @throws \Exception on failure
239	*/
240	public function create(array $data): int
241	{
242	$table = $this->getTableName();
243
244	$insertData = $this->prepareReviewData($data);
245	$insertData['created_at'] = current_time('mysql');
246	$insertData['updated_at'] = current_time('mysql');
247
248	$result = $this->wpdb->insert($table, $insertData);
249
250	if ($result === false) {
251	throw new \Exception('Failed to create review: ' . $this->wpdb->last_error);
252	}
253
254	return $this->wpdb->insert_id;
255	}
256
257	/**
258	* Update a review
259	*
260	* @param int $id Review ID
261	* @param array $data Review data to update
262	* @return bool
263	*/
264	public function update(int $id, array $data): bool
265	{
266	$table = $this->getTableName();
267
268	$updateData = $this->prepareReviewData($data);
269	$updateData['updated_at'] = current_time('mysql');
270
271	$result = $this->wpdb->update(
272	$table,
273	$updateData,
274	['id' => $id],
275	null,
276	['%d']
277	);
278
279	return $result !== false;
280	}
281
282	/**
283	* Bulk update review status
284	*
285	* @param array $ids Review IDs
286	* @param string $status New status
287	* @return int Number of affected rows
288	*/
289	public function bulkUpdateStatus(array $ids, string $status): int
290	{
291	if (empty($ids)) {
292	return 0;
293	}
294
295	$table = $this->getTableName();
296	$ids = array_map('intval', $ids);
297	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
298
299	$query = $this->wpdb->prepare(
300	"UPDATE {$table} SET status = %s, updated_at = %s WHERE id IN ({$placeholders})",
301	array_merge([sanitize_text_field($status), current_time('mysql')], $ids)
302	);
303
304	$this->wpdb->query($query);
305
306	return (int) $this->wpdb->rows_affected;
307	}
308
309	/**
310	* Bulk delete reviews
311	*
312	* @param array $ids Review IDs
313	* @return int Number of deleted rows
314	*/
315	public function bulkDelete(array $ids): int
316	{
317	if (empty($ids)) {
318	return 0;
319	}
320
321	$table = $this->getTableName();
322	$ids = array_map('intval', $ids);
323	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
324
325	$this->wpdb->query($this->wpdb->prepare(
326	"DELETE FROM {$table} WHERE id IN ({$placeholders})",
327	$ids
328	));
329
330	return (int) $this->wpdb->rows_affected;
331	}
332
333	/**
334	* Update review status
335	*
336	* @param int $id Review ID
337	* @param string $status New status
338	* @return bool
339	*/
340	public function updateStatus(int $id, string $status): bool
341	{
342	$table = $this->getTableName();
343
344	$result = $this->wpdb->update(
345	$table,
346	[
347	'status' => sanitize_text_field($status),
348	'updated_at' => current_time('mysql'),
349	],
350	['id' => $id]
351	);
352
353	return $result !== false;
354	}
355
356	/**
357	* Delete a review
358	*
359	* @param int $id Review ID
360	* @return bool
361	*/
362	public function delete(int $id): bool
363	{
364	$table = $this->getTableName();
365
366	$result = $this->wpdb->delete($table, ['id' => $id], ['%d']);
367
368	return $result !== false;
369	}
370
371	/**
372	* Get average rating for a trip
373	*
374	* @param int $tripId Trip ID
375	* @return float
376	*/
377	public function getAverageRating(int $tripId): float
378	{
379	$table = $this->getTableName();
380
381	$approved = $this->sqlApprovedReviewsWhere('status');
382	$result = $this->wpdb->get_var($this->wpdb->prepare(
383	"SELECT AVG(rating) FROM {$table} WHERE trip_id = %d AND {$approved}",
384	$tripId
385	));
386
387	return round((float) ($result ?? 0), 1);
388	}
389
390	/**
391	* Get review count for a trip
392	*
393	* @param int $tripId Trip ID
394	* @return int
395	*/
396	public function getReviewCount(int $tripId): int
397	{
398	$table = $this->getTableName();
399
400	$approved = $this->sqlApprovedReviewsWhere('status');
401
402	return (int) $this->wpdb->get_var($this->wpdb->prepare(
403	"SELECT COUNT(*) FROM {$table} WHERE trip_id = %d AND {$approved}",
404	$tripId
405	));
406	}
407
408	/**
409	* Get rating distribution for a trip
410	*
411	* @param int $tripId Trip ID
412	* @return array
413	*/
414	public function getRatingDistribution(int $tripId): array
415	{
416	$table = $this->getTableName();
417
418	$approved = $this->sqlApprovedReviewsWhere('status');
419	$results = $this->wpdb->get_results($this->wpdb->prepare(
420	"SELECT rating, COUNT(*) as count
421	FROM {$table}
422	WHERE trip_id = %d AND {$approved}
423	GROUP BY rating
424	ORDER BY rating DESC",
425	$tripId
426	), OBJECT_K);
427
428	$distribution = [];
429	for ($i = 5; $i >= 1; $i--) {
430	$row = null;
431	if (is_array($results)) {
432	$row = $results[$i] ?? $results[(string) $i] ?? null;
433	}
434	$distribution[$i] = ($row && isset($row->count)) ? (int) $row->count : 0;
435	}
436
437	return $distribution;
438	}
439
440	/**
441	* Get review statistics
442	*
443	* @return array
444	*/
445	public function getStats(): array
446	{
447	$table = $this->getTableName();
448
449	// Total by status
450	$statusStats = $this->wpdb->get_results(
451	"SELECT status, COUNT(*) as count FROM {$table} GROUP BY status",
452	OBJECT_K
453	);
454
455	// Pending count
456	$pending = (int) ($statusStats['pending']->count ?? 0);
457
458	// Average rating (approved only)
459	$approved = $this->sqlApprovedReviewsWhere('status');
460	$avgRating = (float) $this->wpdb->get_var(
461	"SELECT AVG(rating) FROM {$table} WHERE {$approved}"
462	);
463
464	// This month
465	$thisMonth = (int) $this->wpdb->get_var($this->wpdb->prepare(
466	"SELECT COUNT(*) FROM {$table} WHERE created_at >= %s",
467	date('Y-m-01 00:00:00')
468	));
469
470	return [
471	'total' => array_sum(array_column((array) $statusStats, 'count')),
472	'by_status' => $statusStats,
473	'pending' => $pending,
474	'average_rating' => round($avgRating, 1),
475	'this_month' => $thisMonth,
476	];
477	}
478
479	/**
480	* Check if user can edit review (within 24 hours)
481	*
482	* @param int $reviewId Review ID
483	* @param int $userId User ID
484	* @return bool
485	*/
486	public function canUserEdit(int $reviewId, int $userId): bool
487	{
488	$review = $this->find($reviewId);
489
490	if (!$review) {
491	return false;
492	}
493
494	// Must be the review owner
495	if ((int) $review->user_id !== $userId) {
496	return false;
497	}
498
499	// Must be within 24 hours
500	$createdTime = strtotime($review->created_at);
501	$hoursSinceCreation = (time() - $createdTime) / 3600;
502
503	if ($hoursSinceCreation > 24) {
504	return false;
505	}
506
507	// Must not be approved
508	if ($review->status === 'approved') {
509	return false;
510	}
511
512	return true;
513	}
514
515	/**
516	* Prepare review data for insert/update
517	*
518	* @param array $data Raw data
519	* @return array Sanitized data
520	*/
521	private function prepareReviewData(array $data): array
522	{
523	$prepared = [];
524
525	if (array_key_exists('trip_id', $data)) {
526	$prepared['trip_id'] = (int) $data['trip_id'];
527	}
528
529	if (array_key_exists('user_id', $data)) {
530	$prepared['user_id'] = $data['user_id'] ? (int) $data['user_id'] : null;
531	}
532
533	if (array_key_exists('rating', $data)) {
534	$prepared['rating'] = max(1, min(5, (int) $data['rating']));
535	}
536
537	if (array_key_exists('title', $data)) {
538	$prepared['title'] = sanitize_text_field((string) $data['title']);
539	}
540
541	if (array_key_exists('content', $data)) {
542	$prepared['content'] = sanitize_textarea_field((string) $data['content']);
543	}
544
545	if (array_key_exists('author_name', $data)) {
546	$prepared['author_name'] = sanitize_text_field((string) $data['author_name']);
547	}
548
549	if (array_key_exists('author_email', $data)) {
550	$prepared['author_email'] = sanitize_email((string) $data['author_email']);
551	}
552
553	if (array_key_exists('author_location', $data)) {
554	$prepared['author_location'] = sanitize_text_field((string) $data['author_location']);
555	}
556
557	if (array_key_exists('status', $data)) {
558	$prepared['status'] = sanitize_text_field((string) $data['status']);
559	}
560
561	if (array_key_exists('helpful_count', $data)) {
562	$prepared['helpful_count'] = (int) $data['helpful_count'];
563	}
564
565	// Audit columns — kept optional so legacy callers don't need updating.
566	if (array_key_exists('created_by', $data)) {
567	$prepared['created_by'] = $data['created_by'] ? (int) $data['created_by'] : null;
568	}
569	if (array_key_exists('updated_by', $data)) {
570	$prepared['updated_by'] = $data['updated_by'] ? (int) $data['updated_by'] : null;
571	}
572
573	return $prepared;
574	}
575
576	/**
577	* Check if reviews table exists
578	*/
579	public function tableExists(): bool
580	{
581	global $wpdb;
582	$tableName = $this->getTableName();
583
584	return (bool) $wpdb->get_var(
585	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
586	WHERE TABLE_SCHEMA = DATABASE()
587	AND TABLE_NAME = '{$tableName}'"
588	);
589	}
590	}
591
592