# 📝 Manual Database Setup Guide

## Quick Steps

### 1. Open Supabase SQL Editor

1. Go to your Supabase project dashboard
2. Click on **SQL Editor** in the left sidebar
3. Click **New Query**

### 2. Run the Setup Script

**Option A: Full Setup** (recommended for first time)
- Open `scripts/manual-setup.sql`
- Copy ALL the content
- Paste into Supabase SQL Editor
- Click **Run** or press `Ctrl+Enter`

**Option B: Just Food Items** (if tables already exist)
- Open `scripts/insert-food-items.sql`
- Copy the content
- Paste into Supabase SQL Editor
- Click **Run**

### 3. Verify the Data

Run this query to check:
```sql
SELECT category, COUNT(*) as count 
FROM food_items 
GROUP BY category;
```

You should see:
```
category   | count
-----------|------
beverages  | 3
breakfast  | 3
lunch      | 3
snacks     | 3
```

## What's Included

### Full Setup Script (`manual-setup.sql`)
✅ Creates all 7 tables
✅ Creates indexes for performance
✅ Enables Row Level Security
✅ Inserts 12 food items
✅ Creates 3 test users (Student, Kitchen, Admin)
✅ Includes verification queries

### Quick Insert Script (`insert-food-items.sql`)
✅ Inserts 12 food items only
✅ Includes verification query
✅ Use this if tables already exist

## Food Items Included

### Breakfast (3 items)
- Masala Dosa - ₹40
- Idli Sambar - ₹30
- Poha - ₹25

### Lunch (3 items)
- Veg Thali - ₹60
- Dal Rice - ₹50
- Chole Bhature - ₹55

### Snacks (3 items)
- Samosa - ₹15
- Vada Pav - ₹20
- Pav Bhaji - ₹45

### Beverages (3 items)
- Chai - ₹10
- Coffee - ₹15
- Cold Coffee - ₹30

## Test Users Created

1. **Student Account**
   - Student ID: `TEST001`
   - Name: Test Student
   - Email: test@student.com
   - Role: student

2. **Kitchen Staff**
   - Student ID: `KITCHEN001`
   - Name: Kitchen Staff
   - Email: kitchen@canteen.com
   - Role: kitchen

3. **Admin User**
   - Student ID: `ADMIN001`
   - Name: Admin User
   - Email: admin@canteen.com
   - Role: admin

## Important Notes

⚠️ **This is PostgreSQL, not MySQL**
- Supabase uses PostgreSQL
- Syntax is similar but not identical to MySQL
- Use `gen_random_uuid()` instead of `UUID()`
- Use `TIMESTAMP WITH TIME ZONE` instead of `DATETIME`

⚠️ **Authentication Setup Required**
After running the SQL:
1. Go to Supabase → Authentication → Users
2. Create auth users for each test account
3. Use the same email addresses as in the database

## Troubleshooting

**Error: "relation already exists"**
- Tables already exist, use `insert-food-items.sql` instead

**Error: "duplicate key value"**
- Food items already inserted
- Delete existing items first: `DELETE FROM food_items;`

**Error: "permission denied"**
- Make sure you're using the service_role key
- Or run in Supabase SQL Editor (has admin access)

## Next Steps

After inserting data:

1. **Create Storage Bucket**
   - Go to Storage → Create bucket
   - Name: `food-images`
   - Make it Public

2. **Configure RLS Policies**
   - See `SETUP_GUIDE.md` for RLS policy examples

3. **Test the App**
   ```bash
   npm run dev
   ```

4. **Login as Test User**
   - Go to http://localhost:3000/login
   - Use Student ID: `TEST001`

## Useful Queries

**View all food items:**
```sql
SELECT * FROM food_items ORDER BY category, name;
```

**View all users:**
```sql
SELECT student_id, name, role FROM users;
```

**Count items by category:**
```sql
SELECT category, COUNT(*) FROM food_items GROUP BY category;
```

**Delete all food items:**
```sql
DELETE FROM food_items;
```

**Reset auto-increment (if needed):**
```sql
-- PostgreSQL doesn't use auto-increment for UUIDs
-- UUIDs are automatically generated
```

---

**Need Help?** Check the main `SETUP_GUIDE.md` for detailed instructions.
