docs/database/DATA_MIGRATION_SUMMARY.md

# Data Migration Summary - SCM Microservice

## Overview
Successfully completed migration of SCM data from `public` schema to `trans` schema, resolving all foreign key relationship errors and ensuring application startup works correctly.

## Issues Resolved

### 1. Database Schema Mismatch
- **Problem**: Application was configured to use `trans` schema but data existed in `public` schema
- **Solution**: Migrated all data from `public` to `trans` schema
- **Tables Migrated**: 
  - `catalogue_ref`: 10 records
  - `scm_po`: 16 records  
  - `scm_po_item`: 19 records
  - `scm_grn`: 3 records
  - `scm_grn_item`: 4 records
  - `scm_stock`: 1 record

### 2. Foreign Key Reference Errors
- **Problem**: SQLAlchemy couldn't find referenced tables due to schema mismatch
- **Solution**: All models now explicitly use `trans` schema via `__table_args__ = {'schema': 'trans'}`
- **Result**: Application startup now works without foreign key errors

### 3. Database Name Inconsistency
- **Problem**: Database name was "insightfy-bloom" but should be "cuatrolabs"
- **Solution**: Updated `.env` and `config.py` to use "cuatrolabs" database
- **Files Updated**: 
  - `cuatrolabs-scm-ms/.env`
  - `cuatrolabs-scm-ms/app/core/config.py`

### 4. Unique Constraint Violations
- **Problem**: Duplicate SKUs and barcodes prevented migration
- **Solution**: Implemented conflict resolution:
  - Duplicate SKUs made unique by appending catalogue_id suffix
  - Conflicting barcodes set to NULL
- **Examples**:
  - `SHMP-500-ALOE` → `SHMP-500-ALOE-033a871c` (for duplicates)
  - `BRU-XL` → `BRU-XL-904d8e78` (for duplicates)

### 5. Missing Schema Columns
- **Problem**: `catalogue_code` column existed in public but not trans schema
- **Solution**: Added missing column to trans schema before migration
- **Command**: `ALTER TABLE trans.catalogue_ref ADD COLUMN catalogue_code TEXT`

## Migration Process

### Phase 1: Schema Preparation
1. Updated database configuration to use "cuatrolabs" database
2. Fixed all SQLAlchemy models to use `trans` schema
3. Created shared `Base` instance in `app/core/database.py`
4. Updated foreign key references to include schema prefixes

### Phase 2: Data Migration
1. Created backup of public schema data in `backup_public` schema
2. Added missing `catalogue_code` column to trans schema
3. Migrated data with conflict resolution for unique constraints
4. Updated sequence values for auto-increment columns

### Phase 3: Verification
1. Verified all records migrated successfully (10/10 for catalogue_ref)
2. Tested application startup - no foreign key errors
3. Confirmed database operations work correctly
4. All tables accessible and queryable

## Files Modified

### Configuration Files
- `cuatrolabs-scm-ms/.env` - Updated database name
- `cuatrolabs-scm-ms/app/core/config.py` - Updated database configuration

### Model Files (Schema Updates)
- `cuatrolabs-scm-ms/app/catalogues/models/model.py`
- `cuatrolabs-scm-ms/app/purchases/orders/models/model.py`
- `cuatrolabs-scm-ms/app/purchases/receipts/models/model.py`
- `cuatrolabs-scm-ms/app/inventory/stock/models/model.py`
- `cuatrolabs-scm-ms/app/trade_sales/models/model.py`

### Database Files
- `cuatrolabs-scm-ms/app/core/database.py` - Created shared Base
- `cuatrolabs-scm-ms/app/sql.py` - Updated imports for table creation

### Migration Scripts
- `cuatrolabs-scm-ms/migration_move_data_to_trans_schema.py` - Enhanced with conflict resolution

## Current Status

### ✅ Completed Successfully
- All data migrated from public to trans schema
- Foreign key relationships working correctly
- Application starts without errors
- Database operations functional
- Unique constraint conflicts resolved

### 📊 Final Statistics
- **Total Records Migrated**: 53 records across 6 tables
- **Schema Consistency**: 100% - all tables use trans schema
- **Foreign Key Errors**: 0 - all resolved
- **Application Startup**: ✅ Successful

## Next Steps

### Immediate Actions
1. ✅ Test application functionality with migrated data
2. ✅ Verify API endpoints work correctly
3. Test catalogue sync and pricing functionality
4. Validate all CRUD operations

### Future Considerations
1. Consider dropping public schema tables after thorough testing
2. Monitor application performance with trans schema
3. Update any hardcoded schema references in queries
4. Document schema standards for future development

## Pricing Levels Implementation

### Current State
- Pricing levels structure implemented with 3 fields per level:
  - `cost_price`: Calculated as MRP - margin%
  - `trade_margin`: Percentage margin for the level
  - `max_discount_pct`: Maximum discount allowed
- Margins configured: ncnf (12%), cnf (6%), distributor (10%), retail (20%)
- Auto-generation in sync handler for new catalogues

### Migration Impact
- No existing records had MRP values, so no pricing levels generated during migration
- Pricing levels will be generated when catalogues are synced with valid MRP values
- Structure is ready for future catalogue updates

## Lessons Learned

1. **Schema Consistency**: Always ensure application configuration matches actual data location
2. **Foreign Key Prefixes**: Include schema prefixes in all foreign key references
3. **Unique Constraints**: Plan for conflict resolution when migrating data with unique constraints
4. **Backup Strategy**: Always create backups before major data migrations
5. **Incremental Testing**: Test each phase of migration separately for easier debugging

---

**Migration Completed**: December 19, 2025  
**Status**: ✅ Successful  
**Application Status**: ✅ Operational