# UUID Migration Guide

This guide explains how to migrate the BookBeach database from numeric IDs to UUIDs.

## Overview

The migration changes primary keys and foreign keys in specific key business tables from numeric (BIGINT/SERIAL) types to UUIDs. This improves security by making it harder to guess IDs and provides better scalability.

**Important**: This targeted migration only affects the following key business tables:
- public.adventures
- public.beach_places
- public.bookings
- public.companies
- public.markets
- public.market_items
- public.restaurants
- public.restaurant_items
- public.reviews
- public.users

All other system tables (countries, currencies, languages, etc.) will keep their numeric auto-increment IDs.

## Files Created

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

## Backend Changes

All models, schemas, and API endpoints have been updated to use UUIDs for key tables while keeping numeric IDs for system tables:

### Models Updated
- `app/models/beach.py`
- `app/models/business.py`
- `app/models/company.py`
- `app/models/user.py`
- `app/models/booking.py`
- `app/models/system.py`

### Schemas Created
- `app/schemas/beach.py`
- `app/schemas/business.py`
- `app/schemas/company.py`
- `app/schemas/user.py`
- `app/schemas/booking.py`
- `app/schemas/system.py`

### API Endpoints Created
- `app/api/v1/endpoints/beach.py`
- `app/api/v1/endpoints/business.py`
- `app/api/v1/endpoints/company.py`
- `app/api/v1/endpoints/user.py`
- `app/api/v1/endpoints/booking.py`
- `app/api/v1/endpoints/system.py`

## 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 migration script**:
   ```bash
   # On Linux/Mac
   cd database/scripts
   python run_uuid_migration.py
   
   # On Windows
   cd database\scripts
   .\run_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 key business tables will have UUID primary keys
- System tables will keep their numeric IDs
- Foreign key relationships between key tables have been updated to use UUIDs
- Foreign key relationships to system tables remain as numeric IDs
- API endpoints for key tables now expect and return UUIDs instead of numeric IDs
- API endpoints for system tables still use numeric IDs
- Frontend applications will 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
4. API endpoints for key tables accept and return UUIDs
5. API endpoints for system tables still accept and return numeric IDs
6. The application functions correctly with the mixed ID system

## 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.