yatra / app / Database / Tables / TripClassificationsTable.php

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

78 lines 2.8 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	use Yatra\Database\Tables\BaseTable;
6
7	/**
8	* Optimized TripClassifications Table Class
9	*
10	* Consolidates multiple relationship tables into a single flexible system:
11	* - yatra_trip_trip_categories
12	* - yatra_trip_activities
13	* - yatra_trip_destinations
14	* - yatra_trip_attributes
15	*
16	* This polymorphic relationship table handles all trip-to-classification associations
17	* with additional metadata for context-specific information.
18	*
19	* Key improvements:
20	* - Single table for all trip relationships
21	* - Polymorphic design for flexibility
22	* - Context metadata for each relationship
23	* - Sorting and priority support
24	* - Future-proof for new relationship types
25	*
26	* @package Yatra\Database\Tables\Optimized
27	* @since 2.0.0
28	*/
29	class TripClassificationsTable extends BaseTable
30	{
31	/**
32	* Table name without prefix
33	*
34	* @var string The base table name without WordPress prefix
35	*/
36	protected static string $table = 'yatra_trip_classifications';
37
38	/**
39	* Get the complete table schema as raw SQL CREATE TABLE statement
40	*
41	* Returns the optimized schema that unifies all trip-to-classification
42	* relationships into a single polymorphic table.
43	*
44	* @return string Complete CREATE TABLE SQL statement
45	*/
46	public static function getSchema(): string
47	{
48	$tableName = static::getTableName();
49	$charsetCollate = static::getCharsetCollate();
50
51	return <<<SQL
52	CREATE TABLE IF NOT EXISTS `{$tableName}` (
53	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
54	`trip_id` bigint(20) unsigned NOT NULL,
55	`classification_id` bigint(20) unsigned NOT NULL,
56	`classification_type` enum('category','activity','destination','difficulty','traveler_type','attribute','item_type','item') NOT NULL,
57	`relationship_type` enum('primary','secondary','optional','required') DEFAULT 'primary',
58	`metadata` longtext COMMENT 'JSON: Relationship-specific data (emphasis, custom notes, etc.)',
59	`sort_order` smallint(5) unsigned DEFAULT 0,
60	`is_featured` tinyint(1) DEFAULT 0,
61	`is_active` tinyint(1) NOT NULL DEFAULT 1,
62	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
63	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
64
65	PRIMARY KEY (`id`),
66	UNIQUE KEY `trip_classification_unique` (`trip_id`, `classification_id`, `classification_type`),
67	KEY `idx_trip_classification_type` (`trip_id`, `classification_type`),
68	KEY `classification_id` (`classification_id`),
69	KEY `classification_type` (`classification_type`),
70	KEY `relationship_type` (`relationship_type`),
71	KEY `is_active` (`is_active`),
72	KEY `is_featured` (`is_featured`),
73	KEY `sort_order` (`sort_order`)
74	) {$charsetCollate} COMMENT='Polymorphic trip-to-classification relationships';
75	SQL;
76	}
77	}
78