# 📊 Database Entity Relationship Diagram (ERD)

## Visual Schema Representation

```
┌─────────────────────────────────────────────────────────────────────────┐
│                         DATABASE: hotel_ordering_db                      │
└─────────────────────────────────────────────────────────────────────────┘

┌──────────────────────┐
│   super_admins       │
├──────────────────────┤
│ 🔑 id (INT, PK)      │
│ 📧 email (UNIQUE)    │
│ 🔒 password_hash     │
│ 📅 createdAt         │
│ 📅 updatedAt         │
└──────────────────────┘
         │
         │ (No direct relationship - manages system)
         │
         ▼
┌──────────────────────────────────────────────────────────────┐
│                         hotels                                │
├──────────────────────────────────────────────────────────────┤
│ 🔑 id (VARCHAR(36), PK, UUID)                                │
│ 📝 name                                                       │
│ 📧 email (UNIQUE)                                            │
│ 🔒 password_hash                                             │
│ ⏰ subscription_end                                          │
│ ✅ is_active                                                 │
│ 🖼️  logo_url                                                 │
│ 🎨 banner_url                                                │
│ 📱 whatsapp_number                                           │
│ ☎️  phone                                                     │
│ 📍 address                                                    │
│ 💬 tagline                                                    │
│ 📄 description                                                │
│ 📅 createdAt                                                  │
│ 📅 updatedAt                                                  │
└────────┬─────────────────────────────────┬──────────────────┘
         │                                 │
         │                                 │
         │ 1                               │ 1
         │                                 │
         │ *                               │ *
         ▼                                 ▼
┌─────────────────────┐          ┌─────────────────────┐
│    categories       │          │      offers         │
├─────────────────────┤          ├─────────────────────┤
│ 🔑 id (INT, PK)     │          │ 🔑 id (UUID, PK)    │
│ 🔗 hotel_id (FK)    │          │ 🔗 hotel_id (FK)    │
│ 📝 name             │          │ 📝 name             │
│ 🍽️  meal_times (JSON)│         │ 📄 description      │
│ 📅 createdAt        │          │ 💰 discount_type    │
│ 📅 updatedAt        │          │ 💵 discount_value   │
└────────┬────────────┘          │ 📅 start_date       │
         │                       │ 📅 end_date         │
         │ 1                     │ 🍽️  target_meal_time│
         │                       │ 📂 target_category  │
         │ *                     │ 📅 createdAt        │
         ▼                       │ 📅 updatedAt        │
┌─────────────────────────────┐ └─────────────────────┘
│       menu_items            │
├─────────────────────────────┤
│ 🔑 id (UUID, PK)            │
│ 🔗 hotel_id (FK)            │
│ 🔗 category_id (FK)         │
│ 📝 name                     │
│ 📄 description              │
│ 💰 price                    │
│ 💵 offer_price              │
│ 🖼️  image_url               │
│ 🍽️  meal_time (ENUM)        │
│ 🥗 dietary_type (ENUM)      │
│ ⭐ is_special               │
│ 👁️  is_visible              │
│ ⏰ available_from (TIME)    │
│ ⏰ available_to (TIME)      │
│ 📅 createdAt                │
│ 📅 updatedAt                │
└─────────────────────────────┘
```

## Relationships

### 1. hotels → categories (One-to-Many)
- One hotel can have many categories
- Each category belongs to one hotel
- **Foreign Key:** `categories.hotel_id` → `hotels.id`
- **Cascade:** Delete hotel → Delete all its categories

### 2. hotels → menu_items (One-to-Many)
- One hotel can have many menu items
- Each menu item belongs to one hotel
- **Foreign Key:** `menu_items.hotel_id` → `hotels.id`
- **Cascade:** Delete hotel → Delete all its menu items

### 3. categories → menu_items (One-to-Many)
- One category can have many menu items
- Each menu item belongs to one category
- **Foreign Key:** `menu_items.category_id` → `categories.id`
- **Cascade:** Delete category → Delete all its items

### 4. hotels → offers (One-to-Many)
- One hotel can have many offers
- Each offer belongs to one hotel
- **Foreign Key:** `offers.hotel_id` → `hotels.id`
- **Cascade:** Delete hotel → Delete all its offers

### 5. super_admins (Independent)
- No direct foreign key relationships
- Manages hotels through application logic
- Can create, update, delete hotels

## Data Flow Example

```
Super Admin
    │
    ├─ Creates → Hotel A
    │               │
    │               ├─ Has → Category: Starters
    │               │           │
    │               │           └─ Contains → Menu Item: Spring Rolls
    │               │
    │               ├─ Has → Category: Main Course
    │               │           │
    │               │           ├─ Contains → Menu Item: Pizza
    │               │           └─ Contains → Menu Item: Pasta
    │               │
    │               └─ Has → Offer: Lunch Special (20% off)
    │
    └─ Creates → Hotel B
                    │
                    ├─ Has → Category: Breakfast
                    │           │
                    │           └─ Contains → Menu Item: Pancakes
                    │
                    └─ Has → Offer: Weekend Deal
```

## Multi-Tenancy Isolation

```
┌─────────────────────────────────────────────────────────────┐
│                    Data Isolation                            │
└─────────────────────────────────────────────────────────────┘

Hotel A (id: abc-123)
├── Categories (WHERE hotel_id = 'abc-123')
│   ├── Starters
│   └── Main Course
├── Menu Items (WHERE hotel_id = 'abc-123')
│   ├── Spring Rolls (category: Starters)
│   └── Pizza (category: Main Course)
└── Offers (WHERE hotel_id = 'abc-123')
    └── Lunch Special

Hotel B (id: xyz-789)
├── Categories (WHERE hotel_id = 'xyz-789')
│   └── Breakfast
├── Menu Items (WHERE hotel_id = 'xyz-789')
│   └── Pancakes (category: Breakfast)
└── Offers (WHERE hotel_id = 'xyz-789')
    └── Weekend Deal

❌ Hotel A CANNOT access Hotel B's data
✅ All queries filtered by hotel_id from JWT token
```

## Indexes for Performance

```sql
-- hotels table
INDEX idx_email (email)                    -- Fast login lookup
INDEX idx_subscription (subscription_end, is_active)  -- Subscription checks

-- categories table
INDEX idx_hotel (hotel_id)                 -- Fast hotel lookup

-- menu_items table
INDEX idx_hotel (hotel_id)                 -- Fast hotel lookup
INDEX idx_category (category_id)           -- Fast category lookup
INDEX idx_visibility (is_visible)          -- Filter visible items
INDEX idx_meal_time (meal_time)            -- Filter by meal time

-- offers table
INDEX idx_hotel (hotel_id)                 -- Fast hotel lookup
INDEX idx_dates (start_date, end_date)     -- Active offers lookup
```

## ENUM Types

### meal_time (menu_items & offers)
- `'Breakfast'`
- `'Lunch'`
- `'Dinner'`

### dietary_type (menu_items)
- `'Veg'` - Vegetarian
- `'Non-Veg'` - Non-Vegetarian
- `'Vegan'` - Vegan
- `'None'` - Not specified

### discount_type (offers)
- `'Percentage'` - e.g., 20% off
- `'Fixed'` - e.g., $5 off
- `'Combo'` - Special combo deals

## JSON Fields

### categories.meal_times
```json
["Breakfast", "Lunch", "Dinner"]
```
Stores array of meal times when this category is available.

## Constraints

### UNIQUE Constraints
- `super_admins.email` - Each super admin has unique email
- `hotels.email` - Each hotel has unique email (used for login)

### NOT NULL Constraints
- All primary keys
- All foreign keys
- Essential fields (name, email, password_hash, prices, dates)

### DEFAULT Values
- `hotels.is_active` → `TRUE`
- `hotels.logo_url` → Placeholder image
- `hotels.banner_url` → Placeholder image
- `menu_items.dietary_type` → `'None'`
- `menu_items.is_special` → `FALSE`
- `menu_items.is_visible` → `TRUE`
- All `createdAt` → `CURRENT_TIMESTAMP`
- All `updatedAt` → `CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`

## Storage Estimates

### Small Hotel (100 items)
- Categories: ~10 rows × 1KB = 10KB
- Menu Items: 100 rows × 2KB = 200KB
- Offers: ~5 rows × 1KB = 5KB
- **Total:** ~215KB per hotel (excluding images)

### Large Hotel (1000 items)
- Categories: ~20 rows × 1KB = 20KB
- Menu Items: 1000 rows × 2KB = 2MB
- Offers: ~20 rows × 1KB = 20KB
- **Total:** ~2MB per hotel (excluding images)

### System with 100 Hotels
- Hotels: 100 rows × 2KB = 200KB
- Average data per hotel: 500KB
- **Total:** ~50MB (excluding images)

### Images (stored as files, URLs in DB)
- Average image: 200KB
- 100 items × 200KB = 20MB per hotel
- 100 hotels × 20MB = 2GB total

## Security Features

✅ **Password Hashing**
- All passwords stored as bcrypt hashes
- Never store plain text

✅ **Foreign Key Constraints**
- Prevent orphaned records
- Automatic cascade delete

✅ **Data Isolation**
- hotel_id in all tenant tables
- Application enforces filtering

✅ **Subscription Validation**
- subscription_end timestamp
- is_active boolean flag

## Backup Strategy

### Daily Backups
```bash
# Full backup
mysqldump -u root -p hotel_ordering_db > backup_daily.sql

# Compressed backup
mysqldump -u root -p hotel_ordering_db | gzip > backup_daily.sql.gz
```

### Table-Specific Backups
```bash
# Backup only hotels and menu_items
mysqldump -u root -p hotel_ordering_db hotels menu_items > backup_hotels.sql
```

### Point-in-Time Recovery
Enable binary logging in MySQL config:
```ini
[mysqld]
log-bin=mysql-bin
binlog_format=ROW
```

---

**Database Schema Version:** 1.0  
**Last Updated:** 2024-11-29  
**Character Set:** utf8mb4  
**Collation:** utf8mb4_unicode_ci  
**Engine:** InnoDB
