yatra / app / Database / Tables / TripsTable.php

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

193 lines 8.1 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	`duration_hours` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Duration in hours for hour-based single-day tours, NULL means day-based',
71	`available_from` date DEFAULT NULL COMMENT 'First available date',
72	`available_to` date DEFAULT NULL COMMENT 'Last available date',
73	`booking_window_days` smallint(5) UNSIGNED DEFAULT 30 COMMENT 'Days in advance to book',
74	`booking_deadline_hours` smallint(5) UNSIGNED DEFAULT 24 COMMENT 'Hours before trip start',
75	`has_default_time_slots` tinyint(1) DEFAULT 0 COMMENT 'Enable multiple time slots for day tours',
76	`default_time_slots` text COMMENT 'JSON array of time slot objects for day tours',
77	`departure_time` time DEFAULT NULL COMMENT 'Default departure time for trips',
78
79	-- SEASONAL & AVAILABILITY (Form Fields)
80	`seasonal_availability` varchar(100) DEFAULT NULL,
81	`best_season` varchar(100) DEFAULT NULL,
82	`peak_season` varchar(100) DEFAULT NULL,
83	`off_season` varchar(100) DEFAULT NULL,
84	`seasonal_auto_enable` tinyint(1) DEFAULT 0,
85	`seasonal_enable_date` date DEFAULT NULL,
86	`seasonal_disable_date` date DEFAULT NULL,
87
88	-- CATEGORIZATION (Form Fields)
89	`difficulty_level` bigint(20) UNSIGNED DEFAULT NULL,
90	`featured_priority` varchar(50) DEFAULT 'none',
91
92	-- PRICING (Form Fields)
93	`pricing_type` varchar(50) DEFAULT 'regular',
94	`original_price` decimal(10,2) DEFAULT 0.00,
95	`discounted_price` decimal(10,2) DEFAULT NULL,
96	`sale_price` decimal(10,2) DEFAULT NULL,
97	`deposit_amount` decimal(10,2) DEFAULT NULL,
98	`deposit_percentage` decimal(5,2) DEFAULT NULL,
99	`payment_terms` text,
100
101	-- BOOKING SETTINGS (Form Fields)
102	`min_travelers` smallint(5) UNSIGNED DEFAULT 1,
103	`max_travelers` smallint(5) UNSIGNED DEFAULT NULL,
104
105	-- REQUIREMENTS (Form Fields)
106	`age_min` tinyint(3) UNSIGNED DEFAULT NULL,
107	`age_max` tinyint(3) UNSIGNED DEFAULT NULL,
108	`physical_requirements` text,
109	`visa_requirements` text,
110	`vaccination_requirements` text,
111
112	-- POLICIES (Form Fields)
113	`cancellation_policy` text,
114
115	-- ACCOMMODATION (Form Fields)
116	`accommodation_type` varchar(100) DEFAULT NULL,
117	`meal_plan` varchar(50) DEFAULT NULL,
118	`accommodation_details` text,
119
120	-- TRANSPORTATION (Form Fields)
121	`transportation_included` tinyint(1) DEFAULT 0,
122	`pickup_location` varchar(255) DEFAULT NULL,
123	`dropoff_location` varchar(255) DEFAULT NULL,
124	`transportation_details` text,
125
126	-- MEDIA (Form Fields)
127	`featured_image` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'WordPress attachment ID',
128	`video_url` varchar(500) DEFAULT NULL,
129	`virtual_tour_url` varchar(500) DEFAULT NULL,
130	`testimonial_review_ids` text COMMENT 'JSON array of review IDs to display as testimonials',
131
132	-- SEO (Form Fields)
133	`meta_title` varchar(255) DEFAULT NULL,
134	`meta_description` text,
135	`meta_keywords` text,
136
137	-- STATUS & LIFECYCLE (Form Fields)
138	`status` varchar(50) DEFAULT 'draft',
139	`scheduled_publish_date` datetime DEFAULT NULL,
140	`scheduled_unpublish_date` datetime DEFAULT NULL,
141	`version` int(11) UNSIGNED DEFAULT 1,
142
143	-- SYSTEM FIELDS (Auto-managed)
144	`is_featured` tinyint(1) DEFAULT 0,
145	`views_count` int(11) UNSIGNED DEFAULT 0,
146	`bookings_count` int(11) UNSIGNED DEFAULT 0,
147	`revenue_total` decimal(12,2) DEFAULT 0.00,
148	`avg_rating` decimal(3,2) DEFAULT 0.00,
149	`reviews_count` int(11) UNSIGNED DEFAULT 0,
150	`last_viewed_at` datetime DEFAULT NULL,
151	`last_booked_at` datetime DEFAULT NULL,
152
153	-- JSON STORAGE (For simple array data from form)
154	`included_items` text COMMENT 'JSON array of objects with title and description',
155	`excluded_items` text COMMENT 'JSON array of objects with title and description',
156	`price_types` text COMMENT 'JSON array for traveler-based pricing',
157	`frontend_tabs` text COMMENT 'JSON array for frontend tab configuration',
158	`custom_fields` text COMMENT 'JSON object for custom metadata and future extensibility',
159
160	-- TIMESTAMPS & AUDIT
161	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
162	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
163	`created_by` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
164	`updated_by` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
165	`deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete',
166	`deleted_by` bigint(20) UNSIGNED DEFAULT NULL,
167
168	PRIMARY KEY (`id`),
169	UNIQUE KEY `idx_slug` (`slug`),
170	KEY `idx_status` (`status`),
171	KEY `idx_trip_type` (`trip_type`),
172	KEY `idx_featured_priority` (`featured_priority`),
173	KEY `idx_created_at` (`created_at`),
174	KEY `idx_updated_at` (`updated_at`),
175	KEY `idx_price` (`original_price`),
176	KEY `idx_duration_days` (`duration_days`),
177	KEY `idx_difficulty_level` (`difficulty_level`),
178	KEY `idx_is_featured` (`is_featured`),
179	KEY `idx_views_count` (`views_count`),
180	KEY `idx_bookings_count` (`bookings_count`),
181	KEY `idx_avg_rating` (`avg_rating`),
182	KEY `idx_deleted_at` (`deleted_at`),
183	KEY `idx_featured_image` (`featured_image`),
184	KEY `idx_starting_latitude` (`starting_latitude`),
185	KEY `idx_starting_longitude` (`starting_longitude`),
186	KEY `idx_ending_latitude` (`ending_latitude`),
187	KEY `idx_ending_longitude` (`ending_longitude`),
188	KEY `idx_has_default_time_slots` (`has_default_time_slots`)
189	) {$charsetCollate} COMMENT='Optimized trips table with only used fields';
190	SQL;
191	}
192	}
193