# Fix Database Migration - Aldudu Academy

## Problem

The database migration chain has become deprecated/fragmented with multiple heads that weren't properly merged. This causes issues when running `flask db upgrade`.

## Root Cause

The migration history shows multiple branching and merging:
- Multiple migrations branched from `a4bfedd613ff` (quiz settings)
- Some branches were merged, but not all were properly connected
- The latest migration `e1f2g3h4i5j7` tries to merge `d90fab5910ee` and `001`, but the chain is still fragmented

## Solution

### Option 1: Automatic Fix (Recommended for New Installations)

If you're setting up a new database or don't have important data:

```bash
# Drop and recreate the database
# Then run:
flask db upgrade
flask init-db
```

### Option 2: Manual Fix (For Existing Databases)

If you have an existing database with data:

#### Step 1: Backup Your Database

```bash
# For MySQL/MariaDB
mysqldump -u username -p database_name > backup_$(date +%Y%m%d).sql

# For PostgreSQL
pg_dump -U username database_name > backup_$(date +%Y%m%d).sql
```

#### Step 2: Fix the Alembic Version

Connect to your database and run:

```sql
-- Check current version
SELECT * FROM alembic_version;

-- Fix to point to the last known good head
UPDATE alembic_version SET version_num = 'e1f2g3h4i5j7';
```

#### Step 3: Run the Migration

```bash
# Using the virtualenv Python
/home/smpb5875/virtualenv/public_html/aldudu-academy.smpia22.com/3.11/bin/python -m flask db upgrade
```

Or if you have the venv activated:

```bash
source /home/smpb5875/virtualenv/public_html/aldudu-academy.smpia22.com/3.11/bin/activate
flask db upgrade
deactivate
```

#### Step 4: Verify

```bash
/home/smpb5875/virtualenv/public_html/aldudu-academy.smpia22.com/3.11/bin/python -m flask db current
```

Should show: `z999_fix_migration_chain (head)`

### Option 3: Direct SQL Fix (If Flask Commands Don't Work)

Run the provided SQL script:

```bash
# Connect to your database
mysql -u username -p database_name < fix_alembic_version.sql

# Or for PostgreSQL
psql -U username -d database_name -f fix_alembic_version.sql
```

Then run migrations:

```bash
flask db upgrade
```

## Migration Chain (Fixed)

```
0345434dad27 (initial)
  ↓
... (many migrations)
  ↓
a4bfedd613ff (quiz settings)
  ↓
├─→ c3d4e5f6g7h8 (content folders) ──→ 7400ff3bd702 (merge) ─┐
├─→ dd887f4e556b (quiz password) ────────────────────────────┤
└─→ 001 (preferred language) ────────────────────────────────┤
                                                              ↓
                                                a1b2c3d4e5f8 (is_archived)
                                                              ↓
                                                c9d8e7f6a5b4 (content folder v2)
                                                              ↓
                                                d90fab5910ee (merge heads)
                                                              ↓
                                                e1f2g3h4i5j7 (merge + whats_new)
                                                              ↓
                                                z999_fix_migration_chain (CURRENT HEAD)
```

## Files Changed

1. `migrations/alembic.ini` - Added `script_location = migrations`
2. `migrations/env.py` - Added fallback for script_location
3. `migrations/versions/z999_fix_migration_chain.py` - New consolidation migration
4. `fix_alembic_version.sql` - SQL script to fix alembic_version table

## Verification

After running the fix, verify with:

```bash
# Check current migration version
flask db current

# Check migration history
flask db history

# Verify no multiple heads
flask db heads
```

All commands should show a single head: `z999_fix_migration_chain`

## Troubleshooting

### Error: "Multiple heads are present"

Run the SQL fix script to set the correct version:

```sql
UPDATE alembic_version SET version_num = 'e1f2g3h4i5j7';
```

Then run:

```bash
flask db upgrade
```

### Error: "Script location not found"

Ensure `migrations/alembic.ini` has:

```ini
[alembic]
script_location = migrations
```

### Error: "Target database is not up-to-date"

Run:

```bash
flask db upgrade
```

## Prevention

To prevent this issue in the future:

1. Always run `flask db merge` when merging branches
2. Never manually edit migration files after they've been applied
3. Always test migrations on a staging database first
4. Keep backup of database before running migrations in production

## Contact

If you continue to experience issues, check the logs:

```bash
# Flask app logs
tail -f logs/error.log

# Database logs (MySQL)
tail -f /var/log/mysql/error.log
```