# Database Files

This directory contains SQL schema and sample data files for the Hotel Ordering System.

## Files

### 📄 schema.sql
Complete database schema with all table definitions, indexes, and foreign keys.

**Usage:**
```bash
# Create database and tables
mysql -u root -p < schema.sql
```

**What it creates:**
- Database: `hotel_ordering_db`
- 5 tables: super_admins, hotels, categories, menu_items, offers
- All indexes and foreign key constraints
- Sample queries for reference

### 📄 sample_data.sql
Sample menu items and offers for testing (optional).

**Usage:**
```bash
# After running initDatabase.js, add sample data
mysql -u root -p hotel_ordering_db < sample_data.sql
```

**Note:** You need to replace `YOUR_HOTEL_ID` with the actual hotel ID from your database.

## Recommended Setup Order

### Option 1: Using Node.js Script (Recommended)
```bash
# This is the easiest way - it handles everything
cd Backend
npm run init-db
```

This will:
- Create all tables
- Hash passwords properly
- Create super admin
- Create demo hotel
- Create default categories

### Option 2: Manual SQL Setup
```bash
# 1. Create database and tables
mysql -u root -p < database/schema.sql

# 2. You'll need to manually insert super admin and hotel
# with properly hashed passwords (use bcrypt)
# OR just run: npm run init-db
```

## Database Schema Overview

```
super_admins
├── id (INT, PK)
├── email (VARCHAR, UNIQUE)
├── password_hash (VARCHAR)
└── timestamps

hotels
├── id (VARCHAR(36), PK, UUID)
├── name, email (UNIQUE), password_hash
├── subscription_end (DATETIME)
├── is_active (BOOLEAN)
├── logo_url, banner_url
├── whatsapp_number, phone, address
├── tagline, description
└── timestamps

categories
├── id (INT, PK)
├── hotel_id (FK → hotels.id)
├── name (VARCHAR)
├── meal_times (JSON)
└── timestamps

menu_items
├── id (VARCHAR(36), PK, UUID)
├── hotel_id (FK → hotels.id)
├── category_id (FK → categories.id)
├── name, description
├── price, offer_price (DECIMAL)
├── image_url (VARCHAR)
├── meal_time (ENUM: Breakfast, Lunch, Dinner)
├── dietary_type (ENUM: Veg, Non-Veg, Vegan, None)
├── is_special, is_visible (BOOLEAN)
├── available_from, available_to (TIME)
└── timestamps

offers
├── id (VARCHAR(36), PK, UUID)
├── hotel_id (FK → hotels.id)
├── name, description
├── discount_type (ENUM: Percentage, Fixed, Combo)
├── discount_value (DECIMAL)
├── start_date, end_date (DATETIME)
├── target_meal_time (ENUM)
├── target_category (VARCHAR)
└── timestamps
```

## Useful Queries

### Get all hotels with stats
```sql
SELECT h.id, h.name, h.email, h.subscription_end, h.is_active,
       COUNT(m.id) as item_count
FROM hotels h
LEFT JOIN menu_items m ON h.id = m.hotel_id
GROUP BY h.id;
```

### Get menu for a hotel
```sql
SELECT m.*, c.name as category_name
FROM menu_items m
JOIN categories c ON m.category_id = c.id
WHERE m.hotel_id = 'YOUR_HOTEL_ID'
AND m.is_visible = TRUE;
```

### Get active offers
```sql
SELECT * FROM offers
WHERE hotel_id = 'YOUR_HOTEL_ID'
AND start_date <= NOW()
AND end_date >= NOW();
```

### Check subscription status
```sql
SELECT id, name, email,
       DATEDIFF(subscription_end, NOW()) as days_remaining,
       CASE 
           WHEN is_active = FALSE THEN 'Suspended'
           WHEN subscription_end < NOW() THEN 'Expired'
           ELSE 'Active'
       END as status
FROM hotels;
```

## Backup & Restore

### Backup
```bash
mysqldump -u root -p hotel_ordering_db > backup_$(date +%Y%m%d).sql
```

### Restore
```bash
mysql -u root -p hotel_ordering_db < backup_20241129.sql
```

## Notes

- All tables use `utf8mb4` character set for emoji support
- UUIDs are used for hotels, menu_items, and offers
- Foreign keys have `ON DELETE CASCADE` for automatic cleanup
- Indexes are added for frequently queried columns
- JSON type is used for `meal_times` array in categories

## Troubleshooting

### "Unknown database 'hotel_ordering_db'"
Run the schema.sql file first, or create the database manually:
```sql
CREATE DATABASE hotel_ordering_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

### "Cannot add foreign key constraint"
Make sure parent tables exist before child tables. The schema.sql file has the correct order.

### "Duplicate entry for key 'email'"
Email addresses must be unique. Use a different email or delete the existing entry.
