# Production Database Restoration Guide
## Laravel 5.2 → Current Version Migration

### 🎯 **Overview**
This guide helps restore your production database (Laravel 5.2 + MySQL 5.7) to your development environment (Current Laravel + MySQL 8/9).

---

## 📋 **Pre-Restoration Checklist**

### 1. **Production Database Backup**
```bash
# On production server (Laravel 5.2):
mysqldump -u root -p seraph > seraph_production_backup_$(date +%Y%m%d).sql

# Include routines, triggers, and events:
mysqldump -u root -p --routines --triggers --events seraph > seraph_full_backup_$(date +%Y%m%d).sql
```

### 2. **Check Production Schema Compatibility**
```sql
-- Check for MySQL 5.7 specific features that might need adjustment:
SHOW CREATE TABLE your_problematic_tables;
SELECT @@sql_mode;
```

---

## 🔄 **Restoration Process**

### **PHASE 1: Environment Preparation**

#### 1.1. Clean Current Development Database
```bash
# Stop containers
docker-compose down

# Remove current MySQL data
docker volume rm seraph3_mysql_data

# Clean migrations (run our cleanup script)
chmod +x scripts/cleanup-migrations.sh
./scripts/cleanup-migrations.sh
```

#### 1.2. Start with MySQL 8.0 (Transition Step)
```bash
# Use MySQL 8.0 first for compatibility testing
docker-compose -f docker-compose.mysql8.yml up -d
```

### **PHASE 2: Database Restoration**

#### 2.1. Import Production Data
```bash
# Copy your production backup to the project root
# Then import it:
docker-compose -f docker-compose.mysql8.yml exec mysql mysql -u root -p laravel < seraph_production_backup_20250109.sql
```

#### 2.2. Handle Laravel 5.2 → Current Version Schema Differences
```bash
# Check current migration status after import
docker-compose -f docker-compose.mysql8.yml exec app php artisan migrate:status

# Mark existing tables as migrated (they came from production)
docker-compose -f docker-compose.mysql8.yml exec app php artisan migrate:install

# Mark production migrations as run (so they won't run again)
docker-compose -f docker-compose.mysql8.yml exec app php artisan migrate --pretend
```

#### 2.3. Apply Only New Migrations (Like Quotes)
```bash
# Run only your new quotes migration
docker-compose -f docker-compose.mysql8.yml exec app php artisan migrate --path=database/migrations/2025_01_20_000000_create_quotes_table.php
```

### **PHASE 3: Laravel Version Compatibility Fixes**

#### 3.1. Update Laravel 5.2 Schema to Current Laravel
```sql
-- Connect to database and run these updates if needed:

-- Fix charset/collation for Laravel 8+ compatibility
ALTER DATABASE laravel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Update tables that might have charset issues
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tasks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Repeat for other text-heavy tables

-- Add missing columns that Laravel added after 5.2
-- (Check your current Laravel version requirements)
```

#### 3.2. Handle MySQL 5.7 → 8.0 Compatibility
```sql
-- Fix potential SQL mode issues
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- Update any GROUP BY queries that might break in MySQL 8+
-- (Check your application logs after restoration)
```

---

## 🧪 **Testing & Validation**

### **Step 1: Basic Functionality Test**
```bash
# Clear caches
docker-compose -f docker-compose.mysql8.yml exec app php artisan cache:clear
docker-compose -f docker-compose.mysql8.yml exec app php artisan config:clear
docker-compose -f docker-compose.mysql8.yml exec app php artisan view:clear

# Test database connection
docker-compose -f docker-compose.mysql8.yml exec app php artisan tinker
# In tinker: DB::connection()->getPdo();
```

### **Step 2: Application Feature Testing**
```bash
# Test your main application features:
# - User authentication
# - Task management
# - Client management
# - NEW: Quotes feature (the one you just added)

# Check for any missing imports
docker-compose -f docker-compose.mysql8.yml exec app php artisan validate:imports
```

### **Step 3: MySQL 8.0 Compatibility Verification**
```bash
# Check MySQL logs for any warnings
docker-compose -f docker-compose.mysql8.yml logs mysql

# Performance test with MySQL 8.0
# Monitor query performance for any significant changes
```

---

## 🚀 **MySQL 9.0 Upgrade (After 8.0 Success)**

### **Option A: Direct Upgrade (Recommended after 8.0 testing)**
```yaml
# Update docker-compose.mysql8.yml:
mysql:
  image: mysql:9.0
  # ... rest of configuration
```

### **Option B: Gradual Upgrade**
```bash
# Test with MySQL 8.4 first (LTS version)
# Then move to 9.0

# Update your docker-compose file:
image: mysql:8.4  # Test this first
# Then: image: mysql:9.0
```

---

## ⚠️ **Common Issues & Solutions**

### **Issue 1: Laravel 5.2 Migration Conflicts**
```bash
# Solution: Mark old migrations as run without executing
INSERT INTO migrations (migration, batch) VALUES 
('2013_04_19_124512_create_users_table', 1),
('2013_04_19_134821_create_clients_table', 1);
# ... etc for all production migrations
```

### **Issue 2: MySQL Authentication Plugin Issues**
```bash
# Solution: Already handled in our MySQL 8.0 config with:
--default-authentication-plugin=mysql_native_password
```

### **Issue 3: Character Set Problems**
```sql
-- Solution: Convert tables to utf8mb4
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

### **Issue 4: SQL Mode Strictness**
```sql
-- Our configuration already handles this with appropriate sql-mode settings
```

---

## 📊 **Production Deployment Strategy**

### **Phase 1: Development Testing** ✅
- Test with MySQL 8.0 in development
- Validate all features work
- Performance test critical queries

### **Phase 2: Staging Environment**
- Set up staging with production data copy
- Test MySQL 8.0 upgrade on staging
- Run full application test suite

### **Phase 3: Production Upgrade**
- Schedule maintenance window
- Backup current production database
- Upgrade MySQL 5.7 → 8.0 → 9.0
- Deploy new Laravel version

---

## 🔧 **Quick Commands Reference**

```bash
# Start with MySQL 8.0
docker-compose -f docker-compose.mysql8.yml up -d

# Cleanup migrations
./scripts/cleanup-migrations.sh

# Import production backup
docker-compose -f docker-compose.mysql8.yml exec mysql mysql -u root -p laravel < backup.sql

# Run new migrations only
docker-compose -f docker-compose.mysql8.yml exec app php artisan migrate --path=database/migrations/2025_01_20_000000_create_quotes_table.php

# Test for missing imports
docker-compose -f docker-compose.mysql8.yml exec app php artisan validate:imports

# Clear all caches
docker-compose -f docker-compose.mysql8.yml exec app php artisan optimize:clear
```

---

## 📞 **Support Checklist**

- [ ] Production database backup created
- [ ] Development environment cleaned
- [ ] MySQL 8.0 compatibility tested
- [ ] Laravel version differences handled
- [ ] New features (quotes) working
- [ ] Performance benchmarked
- [ ] Ready for staging deployment 