docs/database/FOREIGN_KEY_FIXES_SUMMARY.md

# Foreign Key Fixes Summary

## Issue Resolved

**Error**: `Foreign key associated with column 'scm_trade_shipment.order_id' could not find table 'scm_po' with which to generate a foreign key to target column 'po_id'`

**Root Cause**: 
1. Multiple `Base` instances across different model files
2. Foreign key references not including schema prefixes
3. Tables in different schemas causing reference issues

## Solutions Applied

### 1. Created Shared Database Base

**File**: `app/core/database.py`
```python
from sqlalchemy.orm import declarative_base

# Shared Base for all SQLAlchemy models
Base = declarative_base()
```

### 2. Updated All Models to Use Shared Base

#### Before:
```python
# Each model file had its own Base
from sqlalchemy.orm import declarative_base
Base = declarative_base()
```

#### After:
```python
# All models now use shared Base
from app.core.database import Base
```

**Files Updated**:
- `app/catalogues/models/model.py`
- `app/inventory/stock/models/model.py`
- `app/purchases/orders/models/model.py`
- `app/purchases/receipts/models/model.py`
- `app/trade_sales/models/model.py`

### 3. Fixed Foreign Key Schema References

#### Before:
```python
ForeignKey('scm_po.po_id')
ForeignKey('scm_grn.grn_id')
```

#### After:
```python
ForeignKey('trans.scm_po.po_id')
ForeignKey('trans.scm_grn.grn_id')
```

**Foreign Keys Fixed**:

**`app/trade_sales/models/model.py`**:
- `order_id`: `'scm_po.po_id'` → `'trans.scm_po.po_id'`
- `shipment_id`: `'scm_trade_shipment.shipment_id'` → `'trans.scm_trade_shipment.shipment_id'`
- `po_item_id`: `'scm_po_item.po_item_id'` → `'trans.scm_po_item.po_item_id'`

**`app/inventory/stock/models/model.py`**:
- `ledger_id`: `'scm_stock_ledger.ledger_id'` → `'trans.scm_stock_ledger.ledger_id'`
- `adjustment_id`: `'scm_stock_adjustment.adjustment_id'` → `'trans.scm_stock_adjustment.adjustment_id'`

**`app/purchases/orders/models/model.py`**:
- `po_id`: `'scm_po.po_id'` → `'trans.scm_po.po_id'`

**`app/purchases/receipts/models/model.py`**:
- `po_id`: `'scm_po.po_id'` → `'trans.scm_po.po_id'`
- `grn_id`: `'scm_grn.grn_id'` → `'trans.scm_grn.grn_id'`
- `po_item_id`: `'scm_po_item.po_item_id'` → `'trans.scm_po_item.po_item_id'`
- `grn_item_id`: `'scm_grn_item.grn_item_id'` → `'trans.scm_grn_item.grn_item_id'`

### 4. Updated Table Creation Logic

**File**: `app/sql.py`

#### Before:
```python
from app.purchases.orders.models.model import Base
```

#### After:
```python
from app.core.database import Base

# Import all models to ensure they're registered with Base
from app.catalogues.models.model import CatalogueRef
from app.inventory.stock.models.model import ScmStock, ScmStockLedger, ScmStockAdjustment, ScmStockTake
from app.purchases.orders.models.model import ScmPo, ScmPoItem, ScmPoStatusLog
from app.purchases.receipts.models.model import ScmGrn, ScmGrnItem, ScmGrnIssue
from app.trade_sales.models.model import ScmTradeShipment, ScmTradeShipmentItem
```

## Database Configuration

### Current Setup:
- **Database**: `cuatrolabs`
- **Schema**: `trans` (for all SCM tables)
- **Shared Base**: All models use `app.core.database.Base`

### Tables in `trans` Schema:
1. `catalogue_ref` - Catalogue reference with pricing_levels
2. `scm_stock` - Stock snapshots
3. `scm_stock_ledger` - Stock movement ledger
4. `scm_stock_adjustment` - Stock adjustments
5. `scm_stock_take` - Physical stock audits
6. `scm_po` - Purchase orders
7. `scm_po_item` - Purchase order items
8. `scm_po_status_log` - PO status changes
9. `scm_grn` - Goods receipt notes
10. `scm_grn_item` - GRN items
11. `scm_grn_issue` - GRN issues/discrepancies
12. `scm_trade_shipment` - Trade shipments
13. `scm_trade_shipment_item` - Trade shipment items

## Verification Results

### ✅ **All Tests Passed**:
1. **Model Import**: All models import successfully with shared Base
2. **Foreign Keys**: All foreign key relationships resolve correctly
3. **Table Creation**: `create_tables()` executes without errors
4. **Application Startup**: FastAPI app starts without foreign key errors
5. **Schema Consistency**: All tables use `trans` schema consistently

### ✅ **Foreign Key Relationships Working**:
- Trade shipments → Purchase orders
- Trade shipment items → PO items
- GRN items → Purchase orders
- Stock adjustments → Stock ledger
- All cross-table relationships functional

## Benefits Achieved

1. **Eliminated Foreign Key Errors**: All table relationships work correctly
2. **Schema Consistency**: All SCM tables in `trans` schema
3. **Maintainable Code**: Single shared Base for all models
4. **Database Integrity**: Proper foreign key constraints enforced
5. **Application Stability**: No startup errors related to table relationships

## Future Maintenance

### When Adding New Models:
1. Always import from `app.core.database import Base`
2. Include schema in `__table_args__ = {'schema': 'trans'}`
3. Use full schema.table format in foreign keys: `'trans.table_name.column'`
4. Add model imports to `app/sql.py` create_tables function

### Schema Migration Best Practices:
1. Use consistent schema across all related tables
2. Always specify schema explicitly in models
3. Include schema in foreign key references
4. Test foreign key relationships after schema changes