# Database Migrations

## For Fresh Installation

Use the complete installation script:

```bash
mysql -u your_db_user -p your_database < app/migrations/complete_installation.sql
```

This single file includes:
- All table schemas with proper indexes
- Domain ownership support
- Default images table
- All application settings
- Initial admin user
- Default data

## For Existing Installations

If you're upgrading from an older version, run migrations in sequence:

### Available Migrations:

| File | Purpose | When to Use |
|------|---------|-------------|
| `001_complete_schema.sql` | Original schema (DEPRECATED) | Don't use - missing ownership |
| `002_complete_schema_with_ownership.sql` | Schema with ownership (OLD) | Don't use - replaced by complete_installation.sql |
| `003_sync_production_settings.sql` | Sync settings | Only if upgrading from v1.x |
| `004_force_reset_settings.sql` | Reset all settings | Only if settings corrupted |
| `005_default_images_table.sql` | Add images table | Only if upgrading from v1.x |
| `006_add_image_ownership.sql` | Add image ownership | Only if upgrading images table |
| `007_reset_admin_password.sql` | Reset admin password | Emergency password reset |
| `008_ensure_og_dimensions.sql` | Add OG dimensions | Only if missing dimensions columns |
| **`complete_installation.sql`** | **Complete installation** | **USE THIS FOR NEW INSTALLS** |

## Important Notes

1. **Always backup your database before running migrations**
2. **For new installations, only use `complete_installation.sql`**
3. **Old migration files are kept for reference only**
4. **Test migrations on a staging environment first**

## Quick Commands

### Check Current Schema
```sql
-- Check if you need migrations
SHOW TABLES;
DESCRIBE domains;
DESCRIBE shortlinks;
DESCRIBE default_images;
```

### Backup Before Migration
```bash
# Create backup
mysqldump -u user -p database > backup_$(date +%Y%m%d_%H%M%S).sql
```

### Run Migration
```bash
# Fresh installation
mysql -u user -p database < complete_installation.sql

# Or via phpMyAdmin:
# Import -> Choose file -> complete_installation.sql -> Go
```

## Troubleshooting

### Foreign Key Errors
If you get foreign key constraint errors:
```sql
SET FOREIGN_KEY_CHECKS = 0;
-- Run your migration
SET FOREIGN_KEY_CHECKS = 1;
```

### Duplicate Key Errors
If tables already exist:
```sql
-- Check existing structure first
SHOW CREATE TABLE your_table;
-- Then decide whether to DROP or ALTER
```

### Permission Errors
Ensure your database user has these privileges:
- CREATE
- DROP
- ALTER
- INSERT
- UPDATE
- DELETE
- SELECT
- INDEX
- REFERENCES