# Targeted UUID Migration Guide

This guide explains how to migrate only specific key tables from numeric IDs to UUIDs, while keeping other tables with their original numeric auto-increment IDs.

## Overview

The targeted migration changes only specific key tables from numeric (BIGINT/SERIAL) types to UUIDs. This approach provides enhanced security for user-facing tables while maintaining performance for system tables that don't need UUIDs.

## Tables Affected by Migration

The following key tables will be migrated to use UUIDs:
- `adventures`
- `beach_places`
- `bookings`
- `companies`
- `markets`
- `market_items`
- `restaurants`
- `restaurant_items`
- `reviews`
- `users`

All other tables will keep their numeric auto-increment IDs.

## Files Created

1. `database/scripts/05_migrate_key_tables_to_uuid.sql` - Targeted SQL migration script
2. `database/scripts/run_targeted_uuid_migration.py` - Python migration runner
3. `database/scripts/run_targeted_uuid_migration.ps1` - PowerShell script to run migration on Windows

## Backend Changes

The backend models, schemas, and API endpoints have already been updated to use UUIDs for the key tables while maintaining numeric IDs for system tables:

### Models Updated
- `app/models/beach.py` - BeachPlace and related models use UUIDs
- `app/models/business.py` - Restaurant, Market, Adventure models use UUIDs
- `app/models/company.py` - Company model uses UUIDs
- `app/models/user.py` - User model uses UUIDs
- `app/models/booking.py` - Booking and Review models use UUIDs

### Schemas Updated
- `app/schemas/beach.py` - Beach schemas with UUID fields for key tables
- `app/schemas/business.py` - Business schemas with UUID fields for key tables
- `app/schemas/company.py` - Company schemas with UUID fields
- `app/schemas/user.py` - User schemas with UUID fields
- `app/schemas/booking.py` - Booking schemas with UUID fields

### API Endpoints Updated
- `app/api/v1/endpoints/beach.py` - Beach endpoints with UUID support for key tables
- `app/api/v1/endpoints/business.py` - Business endpoints with UUID support for key tables
- `app/api/v1/endpoints/company.py` - Company endpoints with UUID support
- `app/api/v1/endpoints/user.py` - User endpoints with UUID support
- `app/api/v1/endpoints/booking.py` - Booking endpoints with UUID support

## Migration Steps

1. **Backup your database** - This migration will result in data loss if you have existing data
2. **Stop the application** - Ensure no processes are accessing the database
3. **Run the targeted migration script**:
   ```bash
   # On Linux/Mac
   cd database/scripts
   python run_targeted_uuid_migration.py
   
   # On Windows
   cd database\scripts
   .\run_targeted_uuid_migration.ps1
   ```
4. **Update your application code** - The backend models and APIs have already been updated
5. **Restart the application**

## Important Notes

- This migration is destructive and will remove all existing data
- Only the specified key tables will have UUID primary keys
- System tables (countries, currencies, languages, etc.) will keep numeric IDs
- Foreign key relationships between key tables and system tables are maintained
- API endpoints for key tables now expect and return UUIDs
- API endpoints for system tables continue to use numeric IDs
- Frontend applications need to be updated to handle UUIDs for key tables

## Testing

After migration, verify that:

1. Key tables have UUID primary keys
2. System tables still have numeric primary keys
3. Foreign key relationships are intact between key tables and system tables
4. API endpoints for key tables accept and return UUIDs
5. API endpoints for system tables accept and return numeric IDs
6. The application functions correctly with the mixed ID approach

## Rollback

If you need to rollback the migration:

1. Restore your database from the backup
2. Revert the backend code changes (if you made a backup of the original files)

## Support

For issues with this migration, please contact the development team.