# Production Migration: Workshops and Workshop Inquiries Tables

This guide will help you create the `workshops` and `workshop_inquiries` tables in your production database.

## ⚠️ Important Notes

- **Backup your database first!** Always backup before running migrations.
- The `workshops` table must be created **before** `workshop_inquiries` (due to foreign key dependency).
- This migration is **safe to run multiple times** - it uses `CREATE TABLE IF NOT EXISTS`.

## Method 1: Using Node.js Script (Recommended)

This is the easiest method if you have Node.js access to your production server.

### Steps:

1. **SSH into your production server** (or navigate to your project directory)

2. **Ensure your `.env` file has correct database credentials:**
   ```env
   DB_HOST=your-database-host
   DB_USER=your-database-user
   DB_PASSWORD=your-database-password
   DB_NAME=your-database-name
   ```

3. **Run the migration script:**
   ```bash
   node backend/scripts/createWorkshopsAndInquiriesProduction.js
   ```

4. **Verify the tables were created:**
   ```bash
   # You can check in MySQL or run:
   mysql -u your_user -p your_database -e "SHOW TABLES LIKE 'workshop%';"
   ```

## Method 2: Using MySQL Command Line

### Steps:

1. **Connect to your MySQL database:**
   ```bash
   mysql -u your_username -p your_database_name
   ```

2. **Run the SQL file:**
   ```bash
   source backend/migrations/CREATE_WORKSHOPS_AND_INQUIRIES_PRODUCTION.sql
   ```
   
   Or from outside MySQL:
   ```bash
   mysql -u your_username -p your_database_name < backend/migrations/CREATE_WORKSHOPS_AND_INQUIRIES_PRODUCTION.sql
   ```

3. **Verify tables:**
   ```sql
   SHOW TABLES LIKE 'workshop%';
   DESCRIBE workshops;
   DESCRIBE workshop_inquiries;
   ```

## Method 3: Using MySQL Workbench / phpMyAdmin

### Steps:

1. **Open MySQL Workbench** or **phpMyAdmin** and connect to your production database

2. **Open the SQL file:**
   - File: `backend/migrations/CREATE_WORKSHOPS_AND_INQUIRIES_PRODUCTION.sql`

3. **Execute the SQL script** (usually a "Run" or "Execute" button)

4. **Verify:**
   - Check that both `workshops` and `workshop_inquiries` tables appear in your database
   - Verify the table structures match the expected schema

## Method 4: Manual SQL Execution

If you prefer to run SQL manually, here's what needs to be created:

### 1. First, update users table (if needed):
```sql
ALTER TABLE `users` 
MODIFY COLUMN `role` ENUM('customer', 'admin', 'manager', 'supplier', 'workshop') 
NOT NULL DEFAULT 'customer';
```

### 2. Create workshops table:
See `backend/migrations/ADD_WORKSHOP_SUPPORT.sql` for the full SQL.

### 3. Create workshop_inquiries table:
See `backend/migrations/CREATE_WORKSHOP_INQUIRIES.sql` for the full SQL.

## Verification Queries

After running the migration, verify with these queries:

```sql
-- Check if tables exist
SHOW TABLES LIKE 'workshop%';

-- Check workshops table structure
DESCRIBE workshops;

-- Check workshop_inquiries table structure
DESCRIBE workshop_inquiries;

-- Count records (should be 0 for new tables)
SELECT COUNT(*) FROM workshops;
SELECT COUNT(*) FROM workshop_inquiries;

-- Check foreign keys
SELECT 
  TABLE_NAME,
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('workshops', 'workshop_inquiries')
  AND REFERENCED_TABLE_NAME IS NOT NULL;
```

## Troubleshooting

### Error: "Table 'workshops' doesn't exist" when creating workshop_inquiries
- **Solution:** Make sure you run the migrations in order. The `workshops` table must be created first.

### Error: "Unknown column 'role' in 'users'"
- **Solution:** Your users table structure might be different. Check your existing users table structure first.

### Error: "Duplicate column name" or "Table already exists"
- **Solution:** This is normal if tables already exist. The migration uses `IF NOT EXISTS` so it's safe to run multiple times.

### Error: Foreign key constraint fails
- **Solution:** Ensure the `users` table exists and has the correct structure before creating `workshops`.

## What Gets Created

### `workshops` Table
- Stores workshop registration information
- Links to `users` table via `userId`
- Includes workshop details: name, address, contact, services, etc.

### `workshop_inquiries` Table
- Stores customer vehicle repair inquiries
- Links to `users` table (for registered customers) and `workshops` table (for assigned workshops)
- Includes inquiry details: vehicle info, issue description, photos, status, etc.

## After Migration

Once the tables are created:

1. **Restart your backend server** (if needed)
2. **Test workshop registration** through the admin panel
3. **Test workshop inquiry submission** from the customer portal
4. **Verify admin can view inquiries** in the admin dashboard

## Need Help?

If you encounter issues:
1. Check the error message carefully
2. Verify your database credentials in `.env`
3. Ensure you have proper database permissions
4. Check that your MySQL version supports JSON columns (MySQL 5.7.8+)