# NUCLEAR DEPLOYMENT EXECUTION GUIDE
## Step-by-Step Instructions for Complete Database Reset

Generated: 2025-08-21 07:00:00 AEST

---

## ⚠️ CRITICAL WARNING

**THIS WILL DELETE ALL EXISTING DATA IN YOUR DATABASE**

Before proceeding:
1. ✅ Ensure you have a complete backup
2. ✅ Confirm this is the intended action
3. ✅ Have a rollback plan ready

---

## 📋 PRE-DEPLOYMENT CHECKLIST

- [ ] Database URL configured in .env or environment
- [ ] Python environment activated with psycopg2 installed
- [ ] SQL files present:
  - [ ] NUCLEAR_DEPLOYMENT_COMPLETE_SCHEMA.sql
  - [ ] NUCLEAR_DEPLOYMENT_COMPLETE_SCHEMA_PART2.sql
  - [ ] execute_nuclear_deployment.py
- [ ] Backup created (automatic or manual)
- [ ] Team notified of maintenance window

---

## 🚀 EXECUTION STEPS

### STEP 1: Install Dependencies
```bash
pip install psycopg2-binary
```

### STEP 2: Create Manual Backup (Optional but Recommended)
```bash
# For Fly.io production database
fly postgres connect -a rateright-db
pg_dump -Fc -v -d rateright > backup_$(date +%Y%m%d_%H%M%S).dump
exit

# For local database
pg_dump -h localhost -U postgres -d rateright > backup_local.sql
```

### STEP 3: Execute Nuclear Deployment
```bash
# Run the deployment script
python execute_nuclear_deployment.py
```

You will see:
1. Connection confirmation
2. Backup creation (automatic)
3. **FINAL WARNING prompt** - Type `DEPLOY` to proceed
4. Schema drop and recreation
5. Table creation progress
6. Verification results

### STEP 4: Monitor Execution

Expected output:
```
============================================================
NUCLEAR DATABASE DEPLOYMENT
Complete Reset and Rebuild
============================================================

🔗 Connecting to database...
   ✅ Connected successfully

📦 Creating backup: backup_before_nuclear_20250821_070000.sql
   Found 12 existing tables to backup

============================================================
⚠️  FINAL WARNING ⚠️
This will DELETE ALL EXISTING DATA and recreate the database!
============================================================

Type 'DEPLOY' to proceed or anything else to cancel: DEPLOY

☢️  EXECUTING NUCLEAR DEPLOYMENT...
   ⚠️  WARNING: This will DROP all existing tables!

📖 Reading SQL files...
   - SQL files loaded successfully
   - Total SQL size: 150000 characters

🚀 Executing database reset...
   - Dropping existing schema...
   - Creating new schema...
   - Creating enum types...
   - Creating tables...
   - Creating indexes...
   - Inserting initial data...

✅ Nuclear deployment completed in 8.34 seconds!

🔍 VERIFYING DEPLOYMENT...

📊 VERIFICATION RESULTS:
   ✓ Tables created: 47 (expected: 47)
   ✓ Enum types created: 20 (expected: 20+)
   ✓ Indexes created: 65 (expected: 60+)
   ✓ Critical tables: 6/6

   Critical tables verified:
     ✅ alembic_version
     ✅ contracts
     ✅ jobs
     ✅ payments
     ✅ user_sessions
     ✅ users

🎉 DEPLOYMENT VERIFICATION: SUCCESS!
   All expected database objects have been created.

🧪 Testing basic query...
   - Users table accessible: 0 rows
   - Job categories loaded: 10 categories

============================================================
✅ NUCLEAR DEPLOYMENT SUCCESSFUL!
============================================================
```

---

## 🔍 POST-DEPLOYMENT VERIFICATION

### 1. Test Database Connection
```bash
python -c "
import psycopg2
import os
conn = psycopg2.connect(os.environ['DATABASE_URL'])
cursor = conn.cursor()
cursor.execute('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = \'public\'')
print(f'Total tables: {cursor.fetchone()[0]}')
conn.close()
"
```

### 2. Test Authentication System
```bash
python test_auth_endpoints.py
```

### 3. Run Application
```bash
flask run
```

### 4. Check Critical Endpoints
- `/` - Homepage should load
- `/auth/login` - Login page should load
- `/auth/register` - Registration should work
- `/dashboard` - Should redirect to login

---

## 🔧 TROUBLESHOOTING

### If Deployment Fails

1. **Check Error Messages**
   ```bash
   # Look for specific SQL errors
   # Common issues: enum types already exist, foreign key violations
   ```

2. **Restore from Backup**
   ```bash
   psql -h $DATABASE_HOST -U postgres -d rateright < backup_file.sql
   ```

3. **Try Partial Deployment**
   - Run NUCLEAR_DEPLOYMENT_COMPLETE_SCHEMA.sql first
   - Then run NUCLEAR_DEPLOYMENT_COMPLETE_SCHEMA_PART2.sql

### Common Issues

| Issue | Solution |
|-------|----------|
| `psycopg2` not found | `pip install psycopg2-binary` |
| DATABASE_URL not found | Check .env file or set environment variable |
| Permission denied | Ensure database user has CREATE/DROP permissions |
| Enum type already exists | Database not fully cleaned, run DROP SCHEMA first |
| Timeout during execution | Increase connection timeout, run in smaller batches |

---

## 📊 VERIFICATION QUERIES

After deployment, verify everything:

```sql
-- Check table count
SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema = 'public';
-- Expected: 47

-- Check enum types
SELECT COUNT(*) FROM pg_type 
WHERE typtype = 'e';
-- Expected: 20+

-- Check indexes
SELECT COUNT(*) FROM pg_indexes 
WHERE schemaname = 'public';
-- Expected: 60+

-- Check critical tables
SELECT table_name FROM information_schema.tables 
WHERE table_name IN ('users', 'user_sessions', 'jobs', 'contracts')
ORDER BY table_name;
-- Expected: All 4 tables
```

---

## 🔄 ROLLBACK PROCEDURE

If you need to rollback:

1. **Immediate Rollback (within same session)**
   ```sql
   ROLLBACK;
   ```

2. **Restore from Backup**
   ```bash
   # Drop corrupted schema
   psql -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
   
   # Restore backup
   pg_restore -d rateright backup_file.dump
   ```

3. **Use Previous Migration**
   ```bash
   # If you have a previous working migration
   alembic downgrade -1
   alembic upgrade head
   ```

---

## ✅ SUCCESS CRITERIA

The deployment is successful when:

1. ✅ All 47 tables are created
2. ✅ All 20+ enum types exist
3. ✅ All 60+ indexes are created
4. ✅ user_sessions table exists (fixes auth)
5. ✅ alembic_version table exists (fixes migrations)
6. ✅ Application starts without errors
7. ✅ Authentication works
8. ✅ No 500 errors on main pages

---

## 📝 NOTES

- Execution time: 5-15 seconds typically
- Database will be empty except for seed data (categories, templates)
- All users will need to re-register
- All data relationships are properly configured
- Stripe integration tables are ready
- Session management is fixed

---

*End of Nuclear Deployment Execution Guide*