yatra / app / Database / Tables / BookingsTable.php

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

124 lines 5.4 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	/**
6	* Bookings Table Class
7	*
8	* Represents the main bookings table (wp_yatra_bookings) containing comprehensive
9	* booking data including customer details, payment information, trip associations,
10	* and booking lifecycle management.
11	*
12	* This table follows the new simplified pattern with only two static methods:
13	* - getTableName(): Returns the prefixed table name
14	* - getSchema(): Returns the complete CREATE TABLE SQL statement
15	*
16	* Usage:
17	* BookingsTable::getTableName() // Returns 'wp_yatra_bookings'
18	* BookingsTable::getSchema() // Returns complete SQL schema
19	*
20	* @package Yatra\Database\Tables
21	* @since 1.0.0
22	*/
23	class BookingsTable extends BaseTable
24	{
25	/**
26	* Table name without prefix
27	*
28	* @var string The base table name without WordPress prefix
29	*/
30	protected static string $table = 'yatra_bookings';
31
32	/**
33	* Get the complete table schema as raw SQL CREATE TABLE statement
34	*
35	* Returns the full SQL schema for the bookings table using heredoc syntax
36	* for proper IDE syntax highlighting. Includes all columns, indexes,
37	* and constraints from the original Database.php schema.
38	*
39	* @return string Complete CREATE TABLE SQL statement
40	*/
41	public static function getSchema(): string
42	{
43	$tableName = static::getTableName();
44	$charsetCollate = static::getCharsetCollate();
45
46	return <<<SQL
47	CREATE TABLE IF NOT EXISTS `{$tableName}` (
48	`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
49	`reference` varchar(50) NOT NULL COMMENT 'Booking reference code',
50	`trip_id` bigint(20) UNSIGNED NOT NULL,
51	`customer_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Reference to yatra_customers',
52	`user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'WordPress user ID if logged in',
53	`contact_first_name` varchar(100) DEFAULT NULL,
54	`contact_last_name` varchar(100) DEFAULT NULL,
55	`contact_email` varchar(255) NOT NULL,
56	`contact_phone` varchar(50) NOT NULL,
57	`contact_country` varchar(100) DEFAULT NULL,
58	`contact_data` text COMMENT 'JSON with full contact details',
59	`emergency_contact` text COMMENT 'JSON with emergency contact details',
60	`travel_date` date NOT NULL,
61	`start_date` date DEFAULT NULL COMMENT 'Trip start (often same as travel_date)',
62	`end_date` date DEFAULT NULL COMMENT 'Trip end for multi-day itineraries',
63	`availability_id` bigint(20) UNSIGNED DEFAULT NULL,
64	`travelers_count` smallint(5) UNSIGNED NOT NULL DEFAULT 1,
65	`total_amount` decimal(12,2) NOT NULL,
66	`amount_paid` decimal(12,2) DEFAULT 0,
67	`amount_due` decimal(12,2) NOT NULL,
68	`currency` char(3) DEFAULT 'USD',
69	`discount_amount` decimal(12,2) DEFAULT 0,
70	`discount_code` varchar(100) DEFAULT NULL,
71	`subtotal` decimal(12,2) DEFAULT NULL COMMENT 'Base amount before tax',
72	`tax_amount` decimal(12,2) DEFAULT 0 COMMENT 'Total tax amount',
73	`tax_rate` decimal(5,2) DEFAULT 0 COMMENT 'Total tax rate percentage',
74	`tax_inclusive` tinyint(1) DEFAULT 0 COMMENT 'Whether tax is included in price',
75	`tax_details` text COMMENT 'JSON with detailed tax breakdown',
76	`itinerary_costs` text COMMENT 'JSON with itinerary activity costs',
77	`itinerary_costs_total` decimal(12,2) DEFAULT 0 COMMENT 'Total itinerary costs',
78	`payment_method` enum('full','deposit','partial') DEFAULT 'full',
79	`payment_gateway` varchar(80) DEFAULT 'pay_later',
80	`payment_status` enum('pending','partial','paid','refunded','failed') DEFAULT 'pending',
81	`payment_session_id` varchar(255) DEFAULT NULL,
82	`payment_transaction_id` varchar(255) DEFAULT NULL,
83	`payment_date` datetime DEFAULT NULL,
84	`payment_notes` text,
85	`status` enum('pending','pending_verification','confirmed','processing','completed','cancelled','refunded','failed','on_hold','waitlist') DEFAULT 'pending',
86	`cancellation_reason` text,
87	`cancelled_at` datetime DEFAULT NULL,
88	`cancelled_by` bigint(20) UNSIGNED DEFAULT NULL,
89	`special_requests` text,
90	`internal_notes` text COMMENT 'Admin-only notes',
91	`newsletter_optin` tinyint(1) DEFAULT 0,
92	`terms_accepted` tinyint(1) DEFAULT 1,
93	`ip_address` varchar(45) DEFAULT NULL,
94	`user_agent` text,
95	`referral_source` varchar(255) DEFAULT NULL,
96	`reminder_sent` tinyint(1) DEFAULT 0,
97	`reminder_sent_at` datetime DEFAULT NULL,
98	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
99	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
100	`created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who created this booking',
101	`updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who last updated this booking',
102	`confirmed_at` datetime DEFAULT NULL,
103	`completed_at` datetime DEFAULT NULL,
104	`meta` longtext DEFAULT NULL COMMENT 'Optional JSON for integrations — use typed columns for core fields',
105
106	PRIMARY KEY (`id`),
107	UNIQUE KEY `reference` (`reference`),
108	KEY `idx_trip_status` (`trip_id`, `status`),
109	KEY `idx_trip_travel` (`trip_id`, `travel_date`),
110	KEY `idx_customer_id` (`customer_id`),
111	KEY `idx_user_id` (`user_id`),
112	KEY `idx_email` (`contact_email`),
113	KEY `idx_status` (`status`),
114	KEY `idx_payment_status` (`payment_status`),
115	KEY `idx_travel_date` (`travel_date`),
116	KEY `idx_start_date` (`start_date`),
117	KEY `idx_end_date` (`end_date`),
118	KEY `idx_availability_id` (`availability_id`),
119	KEY `idx_created` (`created_at`)
120	) {$charsetCollate} COMMENT='Trip bookings';
121	SQL;
122	}
123	}
124