yatra / app / Database / Tables / CustomersTable.php

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

113 lines 4.5 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	/**
6	* Customers Table Class
7	*
8	* Represents the customers CRM table (wp_yatra_customers) containing comprehensive
9	* customer data including personal information, contact details, preferences,
10	* loyalty program data, and payment gateway customer IDs.
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	* CustomersTable::getTableName() // Returns 'wp_yatra_customers'
18	* CustomersTable::getSchema() // Returns complete SQL schema
19	*
20	* @package Yatra\Database\Tables
21	* @since 1.0.0
22	*/
23	class CustomersTable 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_customers';
31
32	/**
33	* Get the complete table schema as raw SQL CREATE TABLE statement
34	*
35	* Returns the full SQL schema for the customers 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	`user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'WordPress user ID if registered',
50	`first_name` varchar(100) NOT NULL,
51	`last_name` varchar(100) DEFAULT NULL,
52	`email` varchar(255) NOT NULL,
53	`phone` varchar(50) DEFAULT NULL,
54	`secondary_phone` varchar(50) DEFAULT NULL,
55	`address` varchar(500) DEFAULT NULL,
56	`city` varchar(100) DEFAULT NULL,
57	`state` varchar(100) DEFAULT NULL,
58	`country` varchar(100) DEFAULT NULL,
59	`postal_code` varchar(20) DEFAULT NULL,
60	`date_of_birth` date DEFAULT NULL,
61	`gender` enum('male','female','other','prefer_not_to_say') DEFAULT NULL,
62	`nationality` varchar(100) DEFAULT NULL,
63	`emergency_name` varchar(200) DEFAULT NULL,
64	`emergency_phone` varchar(50) DEFAULT NULL,
65	`emergency_relationship` varchar(100) DEFAULT NULL,
66	`dietary_requirements` varchar(255) DEFAULT NULL,
67	`medical_conditions` text,
68	`special_needs` text,
69	`preferred_language` varchar(10) DEFAULT 'en',
70	`preferred_currency` char(3) DEFAULT 'USD',
71	`communication_preferences` text COMMENT 'JSON: email, sms, whatsapp preferences',
72	`newsletter_optin` tinyint(1) DEFAULT 0,
73	`marketing_optin` tinyint(1) DEFAULT 0,
74	`source` varchar(100) DEFAULT NULL COMMENT 'How they found us',
75	`referral_code` varchar(50) DEFAULT NULL,
76	`referred_by` bigint(20) UNSIGNED DEFAULT NULL,
77	`total_bookings` int(11) UNSIGNED DEFAULT 0,
78	`total_spent` decimal(12,2) DEFAULT 0,
79	`total_travelers` int(11) UNSIGNED DEFAULT 0,
80	`last_booking_date` date DEFAULT NULL,
81	`last_travel_date` date DEFAULT NULL,
82	`loyalty_points` int(11) UNSIGNED DEFAULT 0,
83	`loyalty_tier` enum('bronze','silver','gold','platinum') DEFAULT 'bronze',
84	`loyalty_tier_expiry` date DEFAULT NULL,
85	`stripe_customer_id` varchar(255) DEFAULT NULL,
86	`paypal_customer_id` varchar(255) DEFAULT NULL,
87	`razorpay_customer_id` varchar(255) DEFAULT NULL,
88	`status` enum('active','inactive','blocked') DEFAULT 'active',
89	`notes` text COMMENT 'Internal notes about customer',
90	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
91	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
92	`created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who created this customer record',
93	`updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who last updated this customer record',
94	`last_login_at` datetime DEFAULT NULL,
95	`verified_at` datetime DEFAULT NULL,
96
97	PRIMARY KEY (`id`),
98	UNIQUE KEY `uk_email` (`email`),
99	KEY `idx_user_id` (`user_id`),
100	KEY `idx_phone` (`phone`),
101	KEY `idx_country` (`country`),
102	KEY `idx_status` (`status`),
103	KEY `idx_loyalty_tier` (`loyalty_tier`),
104	KEY `idx_created` (`created_at`),
105	KEY `idx_total_spent` (`total_spent`),
106	KEY `idx_stripe_customer` (`stripe_customer_id`),
107	KEY `idx_paypal_customer` (`paypal_customer_id`),
108	KEY `idx_razorpay_customer` (`razorpay_customer_id`)
109	) {$charsetCollate} COMMENT='Customer CRM data';
110	SQL;
111	}
112	}
113