yatra / app / Database / Tables / TripAvailabilityRulesTable.php

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

168 lines 8.7 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 Rules Table Class
10	*
11	* @package Yatra\Database\Tables
12	* @since 2.0.0
13	*/
14	class TripAvailabilityRulesTable extends BaseTable
15	{
16	/**
17	* Table name without prefix
18	*
19	* @var string
20	*/
21	protected static string $table = 'yatra_trip_availability_rules';
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	`name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Rule name for identification',
39	`status` enum('active','inactive','paused') NOT NULL DEFAULT 'active',
40
41	-- Recurrence Pattern Configuration
42	`recurrence_type` enum('daily','weekly','monthly','yearly','custom') NOT NULL DEFAULT 'weekly',
43	`recurrence_pattern` json DEFAULT NULL COMMENT 'Recurrence pattern configuration',
44	`start_date` date NOT NULL COMMENT 'Rule start date',
45	`end_date` date DEFAULT NULL COMMENT 'Rule end date (null for no end)',
46
47	-- Time-based Recurrence Fields
48	`days_of_week` json DEFAULT NULL COMMENT 'Applicable days of week for weekly patterns',
49	`day_of_month` int(11) DEFAULT NULL COMMENT 'Day of month for monthly patterns',
50	`month_of_year` int(11) DEFAULT NULL COMMENT 'Month of year for yearly patterns',
51	`interval` int(11) NOT NULL DEFAULT 1 COMMENT 'Interval for recurrence (every X days/weeks/etc)',
52
53	-- Availability Settings
54	`availability_status` enum('available','unavailable','limited') NOT NULL DEFAULT 'available',
55	`max_bookings` int(11) DEFAULT NULL COMMENT 'Maximum bookings per recurrence',
56	`price_override` decimal(10,2) DEFAULT NULL COMMENT 'Override price for this rule',
57	`price_type` enum('fixed','percentage') DEFAULT 'fixed' COMMENT 'Price override type',
58
59	-- Time-based Settings
60	`departure_time` time DEFAULT NULL COMMENT 'Default departure time for generated dates',
61	`arrival_time` time DEFAULT NULL COMMENT 'Default arrival time for generated dates',
62	`duration_hours` decimal(5,2) DEFAULT NULL COMMENT 'Trip duration in hours',
63
64	-- Location Settings
65	`from_location` varchar(255) DEFAULT NULL COMMENT 'Default pickup location',
66	`to_location` varchar(255) DEFAULT NULL COMMENT 'Default destination location',
67	`from_latitude` decimal(10,8) DEFAULT NULL COMMENT 'From location latitude',
68	`from_longitude` decimal(11,8) DEFAULT NULL COMMENT 'From location longitude',
69	`to_latitude` decimal(10,8) DEFAULT NULL COMMENT 'To location latitude',
70	`to_longitude` decimal(11,8) DEFAULT NULL COMMENT 'To location longitude',
71	`pickup_location` varchar(255) DEFAULT NULL COMMENT 'Specific pickup point',
72	`dropoff_location` varchar(255) DEFAULT NULL COMMENT 'Specific dropoff point',
73
74	-- Exception Handling
75	`exceptions` json DEFAULT NULL COMMENT 'Date exceptions to the rule',
76	`exception_type` enum('exclude','include_only') DEFAULT 'exclude' COMMENT 'How to handle exceptions',
77
78	-- Capacity and Pricing Rules
79	`capacity_type` enum('fixed','percentage') DEFAULT 'fixed' COMMENT 'Capacity calculation type',
80	`capacity_value` int(11) DEFAULT NULL COMMENT 'Capacity value (seats or percentage)',
81	`pricing_adjustment` decimal(10,2) DEFAULT 0.00 COMMENT 'Price adjustment amount',
82	`pricing_adjustment_type` enum('amount','percentage') DEFAULT 'amount' COMMENT 'Price adjustment type',
83
84	-- Booking Management
85	`cutoff_hours` smallint(5) unsigned DEFAULT NULL COMMENT 'Hours before departure to stop bookings',
86	`cutoff_days` smallint(5) unsigned DEFAULT NULL COMMENT 'Days before departure to stop bookings',
87	`advance_booking_days` smallint(5) unsigned DEFAULT NULL COMMENT 'Days in advance booking opens',
88
89	-- Conditions and Restrictions
90	`minimum_participants` smallint(5) unsigned DEFAULT NULL COMMENT 'Minimum participants required',
91	`maximum_participants` smallint(5) unsigned DEFAULT NULL COMMENT 'Maximum participants allowed',
92	`age_restrictions` json DEFAULT NULL COMMENT 'Age restrictions (min/max ages)',
93	`skill_level` varchar(100) DEFAULT NULL COMMENT 'Required skill level',
94
95	-- Seasonal and Date-based Rules
96	`season_type` enum('all','high','low','shoulder') DEFAULT 'all' COMMENT 'Season applicability',
97	`holiday_only` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Apply only on holidays',
98	`weekend_only` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Apply only on weekends',
99	`weekday_only` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Apply only on weekdays',
100
101	-- Recurring Availability UI (admin React) — aligns with RecurringAvailabilityRepository
102	`rule_type` varchar(50) NOT NULL DEFAULT 'weekly' COMMENT 'weekly\|daily\|monthly\|custom\|date_range\|single_date',
103	`seats_total` smallint(5) unsigned DEFAULT NULL COMMENT 'Seat cap for capacity service / generated dates',
104	`week_of_month` smallint(5) unsigned DEFAULT NULL,
105	`day_of_week` tinyint(3) unsigned DEFAULT NULL COMMENT '0–6 when rule uses a single weekday',
106	`interval_days` int(11) DEFAULT NULL,
107	`interval_start_date` date DEFAULT NULL,
108	`excluded_dates` longtext DEFAULT NULL COMMENT 'JSON: excluded dates list',
109	`time_slots` longtext DEFAULT NULL COMMENT 'JSON: time slot definitions',
110	`original_price` decimal(10,2) DEFAULT NULL,
111	`sale_price` decimal(10,2) DEFAULT NULL,
112	`traveler_pricing` longtext DEFAULT NULL COMMENT 'JSON: per-category pricing',
113	`pricing_by_traveler_type` longtext DEFAULT NULL COMMENT 'JSON: alternate key for traveler pricing',
114	`alert_threshold` smallint(5) unsigned DEFAULT NULL,
115	`day_overrides` longtext DEFAULT NULL COMMENT 'JSON: per-day capacity/price overrides',
116	`months` longtext DEFAULT NULL COMMENT 'JSON: month filters',
117
118	-- Metadata and Status
119	`notes` text DEFAULT NULL COMMENT 'Internal notes for this rule',
120	`priority` smallint(5) unsigned DEFAULT 10 COMMENT 'Rule priority (lower = higher priority)',
121	`auto_generate` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Automatically generate dates',
122	`last_generated` datetime DEFAULT NULL COMMENT 'Last time dates were generated',
123
124	-- Audit Trail
125	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
126	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
127	`created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who created this record',
128	`updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who last updated this record',
129
130	PRIMARY KEY (`id`),
131	KEY `trip_id` (`trip_id`),
132	KEY `status` (`status`),
133	KEY `recurrence_type` (`recurrence_type`),
134	KEY `start_date` (`start_date`),
135	KEY `end_date` (`end_date`),
136	KEY `availability_status` (`availability_status`),
137	KEY `price_type` (`price_type`),
138	KEY `season_type` (`season_type`),
139	KEY `priority` (`priority`),
140	KEY `auto_generate` (`auto_generate`),
141	KEY `last_generated` (`last_generated`),
142	KEY `created_at` (`created_at`),
143	KEY `updated_at` (`updated_at`),
144
145	CONSTRAINT `fk_trip_availability_rules_trip`
146	FOREIGN KEY (`trip_id`)
147	REFERENCES `{$tripsTable}` (`id`)
148	ON DELETE CASCADE
149	ON UPDATE CASCADE,
150
151	-- Check constraints
152	CONSTRAINT `chk_interval_positive` CHECK (`interval` > 0),
153	CONSTRAINT `chk_priority_positive` CHECK (`priority` >= 0),
154	CONSTRAINT `chk_minimum_participants_positive` CHECK (`minimum_participants` IS NULL OR `minimum_participants` > 0),
155	CONSTRAINT `chk_maximum_participants_positive` CHECK (`maximum_participants` IS NULL OR `maximum_participants` > 0),
156	CONSTRAINT `chk_rules_cutoff_hours_positive` CHECK (`cutoff_hours` IS NULL OR `cutoff_hours` > 0),
157	CONSTRAINT `chk_cutoff_days_positive` CHECK (`cutoff_days` IS NULL OR `cutoff_days` > 0),
158	CONSTRAINT `chk_advance_booking_days_positive` CHECK (`advance_booking_days` IS NULL OR `advance_booking_days` >= 0),
159	CONSTRAINT `chk_capacity_value_positive` CHECK (`capacity_value` IS NULL OR `capacity_value` > 0),
160	CONSTRAINT `chk_max_bookings_positive` CHECK (`max_bookings` IS NULL OR `max_bookings` > 0),
161	CONSTRAINT `chk_duration_hours_positive` CHECK (`duration_hours` IS NULL OR `duration_hours` > 0),
162	CONSTRAINT `chk_participants_order` CHECK (`minimum_participants` IS NULL OR `maximum_participants` IS NULL OR `minimum_participants` <= `maximum_participants`)
163
164	) {$charsetCollate} COMMENT='Recurring availability rules for trip scheduling';
165	SQL;
166	}
167	}
168