yatra / app / Database / Tables / TripsTable.php

TripsTable.php in Yatra – Travel Booking & Tour Operator Software 3.0.7, at app/Database/Tables/TripsTable.php

192 lines 7.9 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	/**
6	* Trips Table Class
7	*
8	* Represents the main trips table (wp_yatra_trips) containing comprehensive trip data
9	* including pricing, scheduling, location details, policies, and analytics.
10	*
11	* This table follows the new simplified pattern with only two static methods:
12	* - getTableName(): Returns the prefixed table name
13	* - getSchema(): Returns the complete CREATE TABLE SQL statement
14	*
15	* Usage:
16	* TripsTable::getTableName() // Returns 'wp_yatra_trips'
17	* TripsTable::getSchema() // Returns complete SQL schema
18	*
19	* @package Yatra\Database\Tables
20	* @since 1.0.0
21	*/
22	class TripsTable extends BaseTable
23	{
24	/**
25	* Table name without prefix
26	*
27	* @var string The base table name without WordPress prefix
28	*/
29	protected static string $table = 'yatra_trips';
30
31	/**
32	* Get the complete table schema as raw SQL CREATE TABLE statement
33	*
34	* Returns the full SQL schema for the trips table using heredoc syntax
35	* for proper IDE syntax highlighting. Includes all columns, indexes,
36	* and constraints from the original Database.php schema.
37	*
38	* @return string Complete CREATE TABLE SQL statement
39	*/
40	public static function getSchema(): string
41	{
42	$tableName = static::getTableName();
43	$charsetCollate = static::getCharsetCollate();
44
45	return <<<SQL
46	CREATE TABLE IF NOT EXISTS `{$tableName}` (
47	`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
48
49	-- IDENTIFICATION & BASIC INFO (Form Fields)
50	`title` varchar(255) NOT NULL COMMENT 'Trip title',
51	`slug` varchar(255) NOT NULL COMMENT 'URL-friendly identifier',
52	`description` text COMMENT 'Full trip description',
53	`short_description` varchar(500) DEFAULT NULL COMMENT 'Brief summary',
54	`trip_details` longtext COMMENT 'Detailed itinerary overview',
55	`what_makes_special` text COMMENT 'Unique selling points',
56	`trip_story` longtext COMMENT 'Narrative/story format',
57
58	-- LOCATION & GEOGRAPHY (Form Fields)
59	`starting_location` varchar(255) DEFAULT NULL COMMENT 'Pickup/start point',
60	`ending_location` varchar(255) DEFAULT NULL COMMENT 'Drop-off/end point',
61	`starting_latitude` decimal(10,8) DEFAULT NULL COMMENT 'Starting location latitude',
62	`starting_longitude` decimal(11,8) DEFAULT NULL COMMENT 'Starting location longitude',
63	`ending_latitude` decimal(10,8) DEFAULT NULL COMMENT 'Ending location latitude',
64	`ending_longitude` decimal(11,8) DEFAULT NULL COMMENT 'Ending location longitude',
65
66	-- DURATION & SCHEDULE (Form Fields)
67	`trip_type` varchar(50) DEFAULT 'multi_day',
68	`duration_days` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Total days',
69	`duration_nights` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Total nights',
70	`available_from` date DEFAULT NULL COMMENT 'First available date',
71	`available_to` date DEFAULT NULL COMMENT 'Last available date',
72	`booking_window_days` smallint(5) UNSIGNED DEFAULT 30 COMMENT 'Days in advance to book',
73	`booking_deadline_hours` smallint(5) UNSIGNED DEFAULT 24 COMMENT 'Hours before trip start',
74	`has_default_time_slots` tinyint(1) DEFAULT 0 COMMENT 'Enable multiple time slots for day tours',
75	`default_time_slots` text COMMENT 'JSON array of time slot objects for day tours',
76	`departure_time` time DEFAULT NULL COMMENT 'Default departure time for trips',
77
78	-- SEASONAL & AVAILABILITY (Form Fields)
79	`seasonal_availability` varchar(100) DEFAULT NULL,
80	`best_season` varchar(100) DEFAULT NULL,
81	`peak_season` varchar(100) DEFAULT NULL,
82	`off_season` varchar(100) DEFAULT NULL,
83	`seasonal_auto_enable` tinyint(1) DEFAULT 0,
84	`seasonal_enable_date` date DEFAULT NULL,
85	`seasonal_disable_date` date DEFAULT NULL,
86
87	-- CATEGORIZATION (Form Fields)
88	`difficulty_level` bigint(20) UNSIGNED DEFAULT NULL,
89	`featured_priority` varchar(50) DEFAULT 'none',
90
91	-- PRICING (Form Fields)
92	`pricing_type` varchar(50) DEFAULT 'regular',
93	`original_price` decimal(10,2) DEFAULT 0.00,
94	`discounted_price` decimal(10,2) DEFAULT NULL,
95	`sale_price` decimal(10,2) DEFAULT NULL,
96	`deposit_amount` decimal(10,2) DEFAULT NULL,
97	`deposit_percentage` decimal(5,2) DEFAULT NULL,
98	`payment_terms` text,
99
100	-- BOOKING SETTINGS (Form Fields)
101	`min_travelers` smallint(5) UNSIGNED DEFAULT 1,
102	`max_travelers` smallint(5) UNSIGNED DEFAULT NULL,
103
104	-- REQUIREMENTS (Form Fields)
105	`age_min` tinyint(3) UNSIGNED DEFAULT NULL,
106	`age_max` tinyint(3) UNSIGNED DEFAULT NULL,
107	`physical_requirements` text,
108	`visa_requirements` text,
109	`vaccination_requirements` text,
110
111	-- POLICIES (Form Fields)
112	`cancellation_policy` text,
113
114	-- ACCOMMODATION (Form Fields)
115	`accommodation_type` varchar(100) DEFAULT NULL,
116	`meal_plan` varchar(50) DEFAULT NULL,
117	`accommodation_details` text,
118
119	-- TRANSPORTATION (Form Fields)
120	`transportation_included` tinyint(1) DEFAULT 0,
121	`pickup_location` varchar(255) DEFAULT NULL,
122	`dropoff_location` varchar(255) DEFAULT NULL,
123	`transportation_details` text,
124
125	-- MEDIA (Form Fields)
126	`featured_image` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'WordPress attachment ID',
127	`video_url` varchar(500) DEFAULT NULL,
128	`virtual_tour_url` varchar(500) DEFAULT NULL,
129	`testimonial_review_ids` text COMMENT 'JSON array of review IDs to display as testimonials',
130
131	-- SEO (Form Fields)
132	`meta_title` varchar(255) DEFAULT NULL,
133	`meta_description` text,
134	`meta_keywords` text,
135
136	-- STATUS & LIFECYCLE (Form Fields)
137	`status` varchar(50) DEFAULT 'draft',
138	`scheduled_publish_date` datetime DEFAULT NULL,
139	`scheduled_unpublish_date` datetime DEFAULT NULL,
140	`version` int(11) UNSIGNED DEFAULT 1,
141
142	-- SYSTEM FIELDS (Auto-managed)
143	`is_featured` tinyint(1) DEFAULT 0,
144	`views_count` int(11) UNSIGNED DEFAULT 0,
145	`bookings_count` int(11) UNSIGNED DEFAULT 0,
146	`revenue_total` decimal(12,2) DEFAULT 0.00,
147	`avg_rating` decimal(3,2) DEFAULT 0.00,
148	`reviews_count` int(11) UNSIGNED DEFAULT 0,
149	`last_viewed_at` datetime DEFAULT NULL,
150	`last_booked_at` datetime DEFAULT NULL,
151
152	-- JSON STORAGE (For simple array data from form)
153	`included_items` text COMMENT 'JSON array of objects with title and description',
154	`excluded_items` text COMMENT 'JSON array of objects with title and description',
155	`price_types` text COMMENT 'JSON array for traveler-based pricing',
156	`frontend_tabs` text COMMENT 'JSON array for frontend tab configuration',
157	`custom_fields` text COMMENT 'JSON object for custom metadata and future extensibility',
158
159	-- TIMESTAMPS & AUDIT
160	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
161	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
162	`created_by` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
163	`updated_by` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
164	`deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete',
165	`deleted_by` bigint(20) UNSIGNED DEFAULT NULL,
166
167	PRIMARY KEY (`id`),
168	UNIQUE KEY `idx_slug` (`slug`),
169	KEY `idx_status` (`status`),
170	KEY `idx_trip_type` (`trip_type`),
171	KEY `idx_featured_priority` (`featured_priority`),
172	KEY `idx_created_at` (`created_at`),
173	KEY `idx_updated_at` (`updated_at`),
174	KEY `idx_price` (`original_price`),
175	KEY `idx_duration_days` (`duration_days`),
176	KEY `idx_difficulty_level` (`difficulty_level`),
177	KEY `idx_is_featured` (`is_featured`),
178	KEY `idx_views_count` (`views_count`),
179	KEY `idx_bookings_count` (`bookings_count`),
180	KEY `idx_avg_rating` (`avg_rating`),
181	KEY `idx_deleted_at` (`deleted_at`),
182	KEY `idx_featured_image` (`featured_image`),
183	KEY `idx_starting_latitude` (`starting_latitude`),
184	KEY `idx_starting_longitude` (`starting_longitude`),
185	KEY `idx_ending_latitude` (`ending_latitude`),
186	KEY `idx_ending_longitude` (`ending_longitude`),
187	KEY `idx_has_default_time_slots` (`has_default_time_slots`)
188	) {$charsetCollate} COMMENT='Optimized trips table with only used fields';
189	SQL;
190	}
191	}
192