erDiagram %% Core Location Management destinations { uuid id PK string name "Sikkim" string slug "sikkim" text description string featured_image_url boolean is_active int display_order timestamp created_at timestamp updated_at } sub_destinations { uuid id PK uuid destination_id FK string name "North Sikkim" string slug "north-sikkim" text description boolean is_active int display_order timestamp created_at timestamp updated_at } %% Package Management duration_templates { uuid id PK string name "3 Nights 4 Days" int nights "3" int days "4" boolean is_active timestamp created_at } packages { uuid id PK string title "Sikkim Darjeeling Tour" string slug "sikkim-darjeeling-tour" text description text content string featured_image_url uuid duration_template_id FK decimal price_starting_from "15999.00" string status "published" boolean is_featured int view_count timestamp published_at timestamp created_at timestamp updated_at uuid created_by FK } package_destinations { uuid id PK uuid package_id FK uuid destination_id FK uuid sub_destination_id FK boolean is_primary timestamp created_at } package_itinerary { uuid id PK uuid package_id FK int day_number "1" string title "Arrival in Gangtok" text description jsonb activities "['Check-in', 'Local sightseeing']" jsonb meals_included "{'breakfast': false, 'lunch': true, 'dinner': true}" string accommodation_type "3-star hotel" timestamp created_at timestamp updated_at } %% Content Management faqs { uuid id PK text question "How to book packages?" text answer "Fill inquiry form or WhatsApp" string category "booking" int display_order boolean is_active timestamp created_at uuid created_by FK } testimonials { uuid id PK string source_type "google_places" string google_place_id "ChIJ1234567890" string customer_name "John Doe" string customer_location "Delhi" decimal rating "4.5" text review_text "Amazing experience in Sikkim" date review_date "2024-12-15" boolean is_featured timestamp last_synced_at string sync_status "synced" timestamp created_at } google_places_config { uuid id PK string place_id "ChIJ1234567890" string place_name "Sikkim Tourism Office" boolean auto_sync_enabled int sync_frequency_hours "24" decimal min_rating "4.0" int max_reviews_per_sync "10" timestamp last_sync_at timestamp next_sync_at boolean is_active } google_places_sync_jobs { uuid id PK uuid place_config_id FK string status "completed" timestamp started_at timestamp completed_at int reviews_fetched "15" int reviews_imported "12" int api_requests_made "3" text error_message } %% Inquiry Management inquiries { uuid id PK string customer_name "Priya Sharma" string customer_mobile "+91-9876543210" string customer_email "priya@email.com" int adults_count "2" int children_count "1" uuid package_id FK uuid destination_id FK string status "new" boolean whatsapp_message_sent string whatsapp_message_id "msg_123456" timestamp whatsapp_sent_at string source_page "/packages/sikkim-tour" string utm_source "google" timestamp created_at } %% Media Management media_assets { uuid id PK string file_name "sikkim-landscape-001.jpg" string original_name "beautiful-sikkim.jpg" string file_url "https://cdn.example.com/images/sikkim-landscape-001.jpg" string file_type "image" int width "1920" int height "1080" string alt_text "Beautiful Sikkim landscape" string usage_context "package" uuid reference_id FK string storage_provider "cloudinary" string cloud_public_id "tourism/sikkim/landscape_001" boolean is_active timestamp created_at uuid created_by FK } %% Admin Management admin_users { uuid id PK string username "admin" string email "admin@tourism.com" string password_hash "$2b$12$..." string first_name "Admin" string last_name "User" string role "super_admin" jsonb permissions "{'packages': ['create', 'read', 'update', 'delete']}" boolean is_active timestamp last_login_at timestamp created_at } %% Analytics page_views { uuid id PK string page_type "package" uuid page_id FK string page_url "/packages/sikkim-tour" uuid visitor_id string session_id "sess_abc123" inet ip_address "203.0.113.1" string country "IN" string region "West Bengal" string city "Kolkata" string referrer_url "https://google.com" string utm_source "google" string utm_campaign "sikkim_winter_2024" string device_type "mobile" string browser "Chrome" timestamp viewed_at int time_spent "45" } search_queries { uuid id PK string query_text "sikkim tour packages" int results_count "12" uuid clicked_result_id FK uuid visitor_id timestamp searched_at } %% Relationships destinations ||--o{ sub_destinations : "has" destinations ||--o{ package_destinations : "linked_to" sub_destinations ||--o{ package_destinations : "linked_to" packages ||--o{ package_destinations : "belongs_to" packages ||--o{ package_itinerary : "has" packages ||--o{ inquiries : "generates" packages ||--o{ page_views : "tracked_in" duration_templates ||--o{ packages : "used_in" testimonials ||--o{ google_places_config : "synced_from" google_places_config ||--o{ google_places_sync_jobs : "has" admin_users ||--o{ packages : "creates" admin_users ||--o{ destinations : "manages" admin_users ||--o{ sub_destinations : "manages" admin_users ||--o{ faqs : "manages" admin_users ||--o{ testimonials : "manages" admin_users ||--o{ media_assets : "uploads" packages ||--o{ media_assets : "uses" destinations ||--o{ media_assets : "uses" sub_destinations ||--o{ media_assets : "uses" packages ||--o{ search_queries : "found_in" destinations ||--o{ inquiries : "receives" sub_destinations ||--o{ inquiries : "receives"