yatra / app / Database / Tables / ClassificationsTable.php

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

86 lines 3.1 KB

Raw Download Zip

1	<?php
2
3	namespace Yatra\Database\Tables;
4
5	use Yatra\Database\Tables\BaseTable;
6
7	/**
8	* Optimized Classifications Table Class
9	*
10	* Consolidates multiple classification tables (Categories, Activities, Destinations)
11	* into a single flexible system using a type-based approach. This eliminates redundancy
12	* and provides a unified interface for all classification needs.
13	*
14	* Consolidated from:
15	* - yatra_trip_categories
16	* - yatra_activities
17	* - yatra_destinations
18	* - yatra_difficulty_levels
19	* - yatra_traveler_categories
20	*
21	* Key improvements:
22	* - Single table for all classification types
23	* - Type-based differentiation
24	* - Hierarchical support via parent_id
25	* - JSON metadata for flexible extensions
26	* - Unified search and filtering
27	*
28	* @package Yatra\Database\Tables\Optimized
29	* @since 2.0.0
30	*/
31	class ClassificationsTable extends BaseTable
32	{
33	/**
34	* Table name without prefix
35	*
36	* @var string The base table name without WordPress prefix
37	*/
38	protected static string $table = 'yatra_classifications';
39
40	/**
41	* Get the complete table schema as raw SQL CREATE TABLE statement
42	*
43	* Returns the optimized schema that unifies all classification types
44	* into a single flexible table with type-based differentiation.
45	*
46	* @return string Complete CREATE TABLE SQL statement
47	*/
48	public static function getSchema(): string
49	{
50	$tableName = static::getTableName();
51	$charsetCollate = static::getCharsetCollate();
52
53	return <<<SQL
54	CREATE TABLE IF NOT EXISTS `{$tableName}` (
55	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
56	`type` enum('category','activity','destination','difficulty','traveler_type','attribute','item_type','item') NOT NULL,
57	`name` varchar(255) NOT NULL,
58	`slug` varchar(255) NOT NULL,
59	`description` text,
60	`parent_id` bigint(20) unsigned DEFAULT NULL,
61	`level` tinyint(3) unsigned DEFAULT 0,
62	`icon` text DEFAULT NULL,
63	`color` varchar(7) DEFAULT NULL COMMENT 'Hex color code',
64	`metadata` longtext COMMENT 'JSON: Type-specific metadata (coordinates for destinations, requirements for activities, field config for attributes, item_type_id for items, etc.)',
65	`sorting` int(11) DEFAULT 0,
66	`is_featured` tinyint(1) DEFAULT 0,
67	`status` varchar(20) NOT NULL DEFAULT 'draft',
68	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
69	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
70	`created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who created this record',
71	`updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User ID who last updated this record',
72
73	PRIMARY KEY (`id`),
74	UNIQUE KEY `type_slug` (`type`, `slug`),
75	KEY `type` (`type`),
76	KEY `parent_id` (`parent_id`),
77	KEY `level` (`level`),
78	KEY `status` (`status`),
79	KEY `is_featured` (`is_featured`),
80	KEY `sorting` (`sorting`),
81	FULLTEXT KEY `search_content` (`name`, `description`)
82	) {$charsetCollate} COMMENT='Unified classification system for categories, activities, destinations, attributes, items, and item types';
83	SQL;
84	}
85	}
86