# File Path Migration Guide

## Overview

This guide covers the database migration scripts created to convert absolute file paths to relative paths in the VFX Project Management System. This migration addresses the issue where absolute paths stored in the database become invalid when deploying to different environments, particularly Linux.

## Problem Statement

The system previously stored absolute file paths like:
- `D:\Repo\LinkDesk\backend\uploads\submissions\98\v001_render.jpg`
- `/home/user/vfx-system/backend/uploads/attachments/123/reference.pdf`

These paths become invalid when:
- Deploying to different operating systems
- Moving the backend directory
- Running in containerized environments

## Solution

Convert all absolute paths to relative paths:
- `uploads/submissions/98/v001_render.jpg`
- `uploads/attachments/123/reference.pdf`
- `uploads/project_thumbnails/project_1_thumbnail.jpg`

## Migration Scripts

### 1. `migrate_file_paths_to_relative.py`

**Purpose**: Basic migration script for development use.

**Features**:
- Converts absolute paths to relative paths in all three tables
- Basic validation and error handling
- Interactive confirmation prompt
- Detailed logging

**Usage**:
```bash
cd backend
python migrate_file_paths_to_relative.py
```

### 2. `migrate_file_paths_production.py`

**Purpose**: Production-ready migration script with comprehensive validation.

**Features**:
- Automatic database backup creation
- Pre-migration analysis of problematic paths
- Enhanced error handling and recovery
- Comprehensive validation of results
- Detailed logging with timestamps
- Support for multiple absolute path patterns

**Usage**:
```bash
cd backend
python migrate_file_paths_production.py
```

### 3. `validate_migration_results.py`

**Purpose**: Validation script to verify migration success.

**Features**:
- Validates all paths are now relative
- Checks file accessibility
- Verifies path format consistency
- Comprehensive reporting

**Usage**:
```bash
cd backend
python validate_migration_results.py
```

## Tables Affected

### 1. `submissions` table
- **Column**: `file_path`
- **Purpose**: Stores paths to user-submitted work files
- **Example conversion**: 
  - Before: `D:\Repo\LinkDesk\backend\uploads\submissions\98\v001_render.jpg`
  - After: `uploads/submissions/98/v001_render.jpg`

### 2. `task_attachments` table
- **Column**: `file_path`
- **Purpose**: Stores paths to task attachment files
- **Example conversion**:
  - Before: `D:\Repo\LinkDesk\backend\uploads\attachments\98\reference.pdf`
  - After: `uploads/attachments/98/reference.pdf`

### 3. `projects` table
- **Column**: `thumbnail_path`
- **Purpose**: Stores paths to project thumbnail images
- **Example conversion**:
  - Before: `D:\Repo\LinkDesk\backend\uploads\project_thumbnails\project_1_thumb.jpg`
  - After: `uploads/project_thumbnails/project_1_thumb.jpg`

## Migration Process

### Pre-Migration Steps

1. **Backup Database**:
   ```bash
   cp database.db database_backup_$(date +%Y%m%d_%H%M%S).db
   ```

2. **Verify Current State**:
   ```bash
   python validate_migration_results.py
   ```

### Running Migration

1. **Development Environment**:
   ```bash
   python migrate_file_paths_to_relative.py
   ```

2. **Production Environment**:
   ```bash
   python migrate_file_paths_production.py
   ```

### Post-Migration Validation

1. **Validate Results**:
   ```bash
   python validate_migration_results.py
   ```

2. **Test File Serving**:
   - Start the backend server
   - Test thumbnail URLs in the frontend
   - Verify file downloads work correctly

## Error Handling

### Common Issues and Solutions

1. **File Not Found**:
   - **Issue**: Original file doesn't exist at expected location
   - **Solution**: Migration logs the issue but continues processing
   - **Action**: Review log files and manually fix missing files

2. **Path Conversion Failure**:
   - **Issue**: Cannot determine relative path from absolute path
   - **Solution**: Migration attempts multiple conversion strategies
   - **Action**: Review problematic paths in log files

3. **Database Transaction Failure**:
   - **Issue**: Database error during migration
   - **Solution**: Automatic rollback preserves data integrity
   - **Action**: Fix underlying issue and re-run migration

### Recovery Procedures

1. **Restore from Backup**:
   ```bash
   cp database_backup_YYYYMMDD_HHMMSS.db database.db
   ```

2. **Partial Migration Recovery**:
   - Migration is transactional per table
   - Individual table failures don't affect other tables
   - Re-run migration to complete partial migrations

## Validation Criteria

### Success Criteria

1. **All paths are relative**: No absolute paths remain in database
2. **Files are accessible**: All relative paths resolve to existing files
3. **Format consistency**: All paths follow expected format patterns
4. **No data loss**: All original file references are preserved

### Validation Checks

1. **Path Format Validation**:
   - Submissions: `uploads/submissions/[task_id]/[filename]`
   - Attachments: `uploads/attachments/[task_id]/[filename]`
   - Projects: `uploads/project_thumbnails/[filename]`

2. **File Accessibility**:
   - All relative paths resolve to existing files
   - File permissions allow read access

3. **Database Integrity**:
   - No NULL values introduced
   - All foreign key relationships preserved
   - No duplicate entries created

## Logging and Monitoring

### Log Files

- **Development**: `migration_file_paths.log`
- **Production**: `migration_file_paths_YYYYMMDD_HHMMSS.log`

### Log Levels

- **INFO**: Normal migration progress
- **WARNING**: Non-critical issues (missing files, etc.)
- **ERROR**: Critical issues that prevent migration

### Monitoring Points

1. **Conversion Statistics**:
   - Total files processed
   - Files successfully converted
   - Files skipped (already relative)
   - Errors encountered

2. **Validation Results**:
   - Path format compliance
   - File accessibility
   - Database integrity

## Integration with FileHandler

### Updated FileHandler Behavior

After migration, the FileHandler class should be updated to:

1. **Store only relative paths** in database
2. **Resolve relative paths** to absolute paths for file operations
3. **Handle both formats** during transition period

### Code Changes Required

```python
# Before migration
file_path = "/absolute/path/to/file.jpg"

# After migration
file_path = "uploads/submissions/123/file.jpg"
absolute_path = file_handler.resolve_absolute_path(file_path)
```

## Testing

### Test Scenarios

1. **File Upload**: Verify new uploads store relative paths
2. **File Serving**: Verify existing files serve correctly
3. **Thumbnail Generation**: Verify thumbnails work with relative paths
4. **Cross-Platform**: Test on different operating systems

### Test Commands

```bash
# Test file serving endpoints
curl http://localhost:8000/files/submissions/98/v001_render.jpg

# Test thumbnail URLs
curl http://localhost:8000/files/thumbnails/v001_render_thumb.jpg

# Test project thumbnails
curl http://localhost:8000/files/project_thumbnails/project_1_thumb.jpg
```

## Deployment Checklist

### Pre-Deployment

- [ ] Database backup created
- [ ] Migration scripts tested in staging
- [ ] Validation scripts ready
- [ ] Rollback procedure documented

### During Deployment

- [ ] Stop application services
- [ ] Run migration script
- [ ] Validate migration results
- [ ] Update FileHandler code
- [ ] Start application services

### Post-Deployment

- [ ] Test file serving endpoints
- [ ] Verify thumbnail URLs work
- [ ] Monitor application logs
- [ ] Confirm no absolute paths in new uploads

## Troubleshooting

### Common Problems

1. **Migration appears to do nothing**:
   - Check if paths are already relative
   - Review log files for details

2. **Files not accessible after migration**:
   - Verify file permissions
   - Check if files were moved or deleted
   - Validate relative path resolution

3. **Thumbnails not displaying**:
   - Check thumbnail file existence
   - Verify thumbnail path format
   - Test thumbnail serving endpoints

### Debug Commands

```bash
# Check current path formats
python -c "
from models.task import Submission
from database import SessionLocal
db = SessionLocal()
for s in db.query(Submission).limit(5):
    print(f'ID: {s.id}, Path: {s.file_path}')
"

# Test path resolution
python -c "
from utils.file_handler import file_handler
from pathlib import Path
backend_dir = Path(__file__).parent
test_path = 'uploads/submissions/98/test.jpg'
resolved = backend_dir / test_path
print(f'Resolved: {resolved}')
print(f'Exists: {resolved.exists()}')
"
```

## Requirements Addressed

This migration addresses the following requirements from the specification:

- **1.1**: Convert absolute paths to relative paths in submissions table
- **1.2**: Convert absolute paths to relative paths in task_attachments table
- **1.3**: Convert absolute paths to relative paths in projects table (thumbnail_path)
- **1.4**: Add validation and error handling for problematic paths
- **1.5**: Ensure all file paths in database are relative to backend directory

## Conclusion

The file path migration scripts provide a comprehensive solution for converting absolute file paths to relative paths, ensuring cross-platform compatibility and system portability. The migration includes robust error handling, validation, and recovery procedures suitable for both development and production environments.