# Fix Server Database - Migration Guide

## Problem Summary

Your server database is missing some columns and tables that exist in your local database. This causes errors when:
- Enabling/disabling roster features (missing `feature_flags.shop_id`)
- Using JWT token refresh (missing `refresh_tokens` table)
- Using branch PIN functionality (should already exist, but verified)

## What's Missing

Based on the server database export (`server_rootclockin_face.sql`), the following are missing:

1. **`feature_flags.shop_id` column** - Required for shop-specific feature flags
2. **`refresh_tokens` table** - Required for JWT token refresh functionality
3. **`branches.pin` column** - Should already exist, but script verifies it

## Solution

Run one of the migration scripts below to fix all issues at once.

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

SSH into your server and run:

```bash
cd /home/rootclockin/public_html
node scripts/fix_all_missing_columns.js
```

This script will:
- Check what's missing
- Add missing columns/tables
- Verify everything was created correctly
- Handle errors gracefully (won't fail if something already exists)

## Option 2: Using SQL Directly (phpMyAdmin or MySQL CLI)

If you prefer to run SQL directly:

1. **Via phpMyAdmin:**
   - Go to your database
   - Click "SQL" tab
   - Copy and paste the contents of `fix_all_missing_columns_simple.sql`
   - Click "Go"

2. **Via MySQL CLI:**
   ```bash
   mysql -u your_username -p your_database < scripts/fix_all_missing_columns_simple.sql
   ```

## Option 3: Using the Advanced SQL Script

For more control and better error handling, use `fix_all_missing_columns.sql` which uses prepared statements to check before adding.

## After Running Migration

1. **Restart your PM2 server:**
   ```bash
   pm2 restart server
   ```

2. **Verify everything works:**
   - Try enabling/disabling roster feature (should work now)
   - Check that login/token refresh works
   - Verify branch PIN functionality

## What Each Script Does

### `fix_all_missing_columns.js`
- Node.js script with error handling
- Checks database before making changes
- Provides detailed output
- Verifies changes after completion

### `fix_all_missing_columns.sql`
- Advanced SQL with conditional logic
- Uses prepared statements
- Checks before adding (safer)
- More verbose output

### `fix_all_missing_columns_simple.sql`
- Simple SQL statements
- Easy to run in phpMyAdmin
- May show errors if things already exist (this is OK)
- Fastest option

## Troubleshooting

### Error: "Duplicate column name"
- This means the column already exists - **this is OK!**
- The migration script handles this gracefully
- You can safely ignore this error

### Error: "Duplicate key name"
- This means the index already exists - **this is OK!**
- The migration script handles this gracefully

### Error: "Table already exists"
- This means the table already exists - **this is OK!**
- The migration script handles this gracefully

### If you get other errors:
1. Check your database credentials in `.env`
2. Make sure you have proper permissions
3. Check the PM2 logs: `pm2 logs server`

## Files Created

- `fix_all_missing_columns.js` - Node.js migration script (recommended)
- `fix_all_missing_columns.sql` - Advanced SQL with checks
- `fix_all_missing_columns_simple.sql` - Simple SQL (easiest)
- `FIX_SERVER_DATABASE.md` - This file

## Need Help?

If you encounter issues:
1. Check PM2 logs: `pm2 logs server --lines 50`
2. Verify database connection in `.env`
3. Check that all files were uploaded to server
4. Make sure Node.js and MySQL are running

