yatra / app / Database / Tables / TripAvailabilityDatesTable.php

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

128 lines 5.8 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	use Yatra\Database\Tables\BaseTable;
6	use Yatra\Database\Tables\TripsTable;
7
8	/**
9	* Trip Availability Dates Table Class
10	*
11	* @package Yatra\Database\Tables
12	* @since 2.0.0
13	*/
14	class TripAvailabilityDatesTable extends BaseTable
15	{
16	/**
17	* Table name without prefix
18	*
19	* @var string
20	*/
21	protected static string $table = 'yatra_trip_availability_dates';
22
23	/**
24	* Get the complete table schema as raw SQL CREATE TABLE statement
25	*
26	* @return string
27	*/
28	public static function getSchema(): string
29	{
30	$tableName = static::getTableName();
31	$charsetCollate = static::getCharsetCollate();
32	$tripsTable = TripsTable::getTableName();
33
34	return <<<SQL
35	CREATE TABLE IF NOT EXISTS `{$tableName}` (
36	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
37	`trip_id` bigint(20) unsigned NOT NULL,
38	`departure_date` date NOT NULL COMMENT 'Main departure date for the trip',
39	`arrival_date` date DEFAULT NULL COMMENT 'Arrival date (for multi-day trips)',
40	`return_date` date DEFAULT NULL COMMENT 'Return date (for round trips)',
41	`departure_time` time DEFAULT NULL COMMENT 'Departure time',
42	`arrival_time` time DEFAULT NULL COMMENT 'Arrival time',
43
44	-- Capacity Management
45	`seats_total` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Total seats available',
46	`seats_available` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Seats currently available',
47	`seats_reserved` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Seats reserved/booked',
48	`seats_waitlist` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Waitlist count',
49
50	-- Pricing Information
51	`pricing_type` enum('regular','discounted','special') NOT NULL DEFAULT 'regular',
52	`original_price` decimal(10,2) DEFAULT NULL COMMENT 'Original price before discounts',
53	`discounted_price` decimal(10,2) DEFAULT NULL COMMENT 'Discounted price',
54	`discount_percentage` decimal(5,2) DEFAULT NULL COMMENT 'Discount percentage',
55	`price_types` longtext DEFAULT NULL COMMENT 'JSON: Multiple price types (adult/child/senior)',
56
57	-- Location Information
58	`from_location` varchar(255) DEFAULT NULL COMMENT 'Departure location',
59	`to_location` varchar(255) DEFAULT NULL COMMENT 'Destination location',
60	`from_latitude` decimal(10,8) DEFAULT NULL COMMENT 'From location latitude',
61	`from_longitude` decimal(11,8) DEFAULT NULL COMMENT 'From location longitude',
62	`to_latitude` decimal(10,8) DEFAULT NULL COMMENT 'To location latitude',
63	`to_longitude` decimal(11,8) DEFAULT NULL COMMENT 'To location longitude',
64	`pickup_location` varchar(255) DEFAULT NULL COMMENT 'Specific pickup point',
65	`dropoff_location` varchar(255) DEFAULT NULL COMMENT 'Specific dropoff point',
66
67	-- Status and Availability
68	`status` enum('available','unavailable','limited','sold_out','cancelled','blocked','closed') NOT NULL DEFAULT 'available',
69	`is_blocked` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether this date is blocked',
70	`block_reason` varchar(255) DEFAULT NULL COMMENT 'Reason for blocking',
71
72	-- Booking Management
73	`cutoff_date` date DEFAULT NULL COMMENT 'Last date to book this departure',
74	`cutoff_hours` smallint(5) unsigned NOT NULL DEFAULT 24 COMMENT 'Hours before departure to stop bookings',
75	`alert_threshold` smallint(5) unsigned DEFAULT NULL COMMENT 'Alert when seats below this',
76
77	-- Special Information
78	`special_notes` text DEFAULT NULL COMMENT 'Special notes for this departure',
79	`guide_assigned` varchar(255) DEFAULT NULL COMMENT 'Assigned guide name',
80	`equipment_included` longtext DEFAULT NULL COMMENT 'JSON: Equipment included for this departure',
81
82	-- Synchronization and Tracking
83	`last_synced_at` datetime DEFAULT NULL COMMENT 'Last sync with external systems',
84	`sync_status` enum('pending','synced','failed') DEFAULT NULL COMMENT 'Sync status',
85
86	-- Metadata
87	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
88	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
89	`created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who created this record',
90	`updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who last updated this record',
91
92	PRIMARY KEY (`id`),
93	UNIQUE KEY `trip_departure_date_time` (`trip_id`, `departure_date`, `departure_time`),
94	KEY `idx_trip_status_departure` (`trip_id`, `status`, `departure_date`),
95	KEY `departure_date` (`departure_date`),
96	KEY `arrival_date` (`arrival_date`),
97	KEY `return_date` (`return_date`),
98	KEY `status` (`status`),
99	KEY `seats_available` (`seats_available`),
100	KEY `is_blocked` (`is_blocked`),
101	KEY `cutoff_date` (`cutoff_date`),
102	KEY `pricing_type` (`pricing_type`),
103	KEY `from_location` (`from_location`),
104	KEY `to_location` (`to_location`),
105	KEY `created_at` (`created_at`),
106	KEY `updated_at` (`updated_at`),
107	KEY `last_synced_at` (`last_synced_at`),
108
109	CONSTRAINT `fk_trip_availability_dates_trip`
110	FOREIGN KEY (`trip_id`)
111	REFERENCES `{$tripsTable}` (`id`)
112	ON DELETE CASCADE
113	ON UPDATE CASCADE,
114
115	-- Check constraints
116	CONSTRAINT `chk_seats_total_positive` CHECK (`seats_total` > 0),
117	CONSTRAINT `chk_seats_available_not_negative` CHECK (`seats_available` >= 0),
118	CONSTRAINT `chk_seats_reserved_not_negative` CHECK (`seats_reserved` >= 0),
119	CONSTRAINT `chk_seats_waitlist_not_negative` CHECK (`seats_waitlist` >= 0),
120	CONSTRAINT `chk_dates_cutoff_hours_positive` CHECK (`cutoff_hours` > 0),
121	CONSTRAINT `chk_seats_reserved_not_exceed_total` CHECK (`seats_reserved` <= `seats_total`),
122	CONSTRAINT `chk_seats_available_not_exceed_total` CHECK (`seats_available` <= `seats_total`)
123
124	) {$charsetCollate} COMMENT='Traditional Yatra trip availability dates with pricing and capacity';
125	SQL;
126	}
127	}
128