docs/TRADE_RELATIONSHIPS_README.md

# Trade Relationships Module - Implementation Guide

## 🎯 Overview

The Trade Relationship module defines **who can transact with whom** in the supply chain. It serves as the **single source of truth** for:

- ✅ Supplier eligibility for buying
- 💰 Commercial rules (pricing level, payment terms, credit)
- 🌍 Regional & category constraints  
- 🔒 Transaction authorization (PO, Invoice, Returns, CN/DN)

**Critical Rule**: No Purchase Order, Invoice, Return, or Credit/Debit Note can be created unless an active trade relationship exists.

## 📁 Module Structure

```
app/trade_relationships/
├── __init__.py                     # Module initialization
├── constants.py                    # Enums, validation patterns, business rules
├── controllers/
│   ├── __init__.py
│   └── router.py                   # FastAPI endpoints
├── services/
│   ├── __init__.py
│   └── service.py                  # Business logic & CRUD operations
├── models/
│   ├── __init__.py
│   └── model.py                    # SQLAlchemy PostgreSQL model
└── schemas/
    ├── __init__.py
    └── schema.py                   # Pydantic request/response schemas
```

## 🗄️ Database Schema

### Table: `trans.scm_trade_relationship`

The relationship is **directional** and **explicit**:
```
Buyer Merchant → Supplier Merchant
```

#### Key Fields:
- **Parties**: `from_merchant_id` (buyer) → `to_merchant_id` (supplier)
- **Status**: `draft` | `active` | `suspended` | `expired` | `terminated`
- **Commercial**: `pricing_level`, `payment_terms`, `credit_allowed`, `credit_limit`
- **Constraints**: `allowed_regions[]`, `allowed_categories[]`
- **Validity**: `valid_from`, `valid_to` (NULL = indefinite)

#### Business Constraints:
- ✅ **Unique Relationship**: One relationship per merchant pair
- ✅ **Different Merchants**: Buyer ≠ Supplier  
- ✅ **Credit Logic**: If credit allowed, limit must be > 0
- ✅ **Date Logic**: `valid_to` ≥ `valid_from`

## 🏗️ Setup Instructions

### 1. Run Database Migration

```bash
# From the project root directory
python migrate_trade_relationships.py
```

This will:
- ✅ Create the `scm_trade_relationship` table in `trans` schema
- 🔍 Add 6 performance indexes
- ✅ Apply 4 business rule constraints  
- 📝 Add column documentation
- 🧪 Insert sample test data

### 2. Verify Installation

After running migration, restart your application and check:

```bash
# Check if endpoints are available
curl http://localhost:9101/docs

# Look for "trade-relationships" section in Swagger UI
```

### 3. Test Basic Functionality

```bash
# Health check
curl http://localhost:9101/trade-relationships/health

# List relationships (requires auth)
curl -H "Authorization: Bearer YOUR_TOKEN" \
     -X POST http://localhost:9101/trade-relationships/list \
     -H "Content-Type: application/json" \
     -d '{"skip": 0, "limit": 10}'
```

## 🔧 API Endpoints

### Core CRUD Operations

| Method | Endpoint | Description |
|--------|----------|-------------|
| `POST` | `/trade-relationships` | Create new relationship |
| `GET` | `/trade-relationships/{id}` | Get relationship details |
| `PUT` | `/trade-relationships/{id}` | Update relationship |
| `DELETE` | `/trade-relationships/{id}` | Delete relationship |

### Status Management

| Method | Endpoint | Description |
|--------|----------|-------------|
| `PUT` | `/trade-relationships/{id}/status` | Change status (draft→active→suspended, etc.) |

### Supplier Discovery (Key Feature)

| Method | Endpoint | Description |
|--------|----------|-------------|
| `POST` | `/trade-relationships/suppliers/discover` | Find suppliers for buyer (POST body) |
| `GET` | `/trade-relationships/suppliers?buyer_merchant_id=X` | Find suppliers (query params) |

### Relationship Validation

| Method | Endpoint | Description |
|--------|----------|-------------|
| `POST` | `/trade-relationships/validate` | Validate buyer→supplier relationship |
| `POST` | `/trade-relationships/list` | List with filters and pagination |

## 📋 Usage Examples

### 1. Create a Trade Relationship

```json
POST /trade-relationships
{
  "from_merchant_id": "mch_ncnf_mumbai_001",
  "to_merchant_id": "mch_company_hq_001", 
  "pricing_level": "NCNF",
  "credit": {
    "allowed": true,
    "limit": 10000000,
    "payment_terms": "NET_30"
  },
  "allowed_regions": ["IN-MH-MUM", "IN-GJ-AHM"],
  "allowed_categories": ["Haircare", "Skincare"],
  "remarks": "Primary procurement relationship"
}
```

### 2. Discover Suppliers for Purchase Order

```json
POST /trade-relationships/suppliers/discover
{
  "buyer_merchant_id": "mch_ncnf_mumbai_001",
  "region": "IN-MH-MUM",
  "category": "Haircare",
  "active_only": true
}
```

**Response:**
```json
{
  "buyer_merchant_id": "mch_ncnf_mumbai_001",
  "suppliers": [
    {
      "merchant_id": "mch_company_hq_001",
      "relationship_id": "550e8400-e29b-41d4-a716-446655440000",
      "pricing_level": "NCNF",
      "payment_terms": "NET_30",
      "credit_allowed": true,
      "credit_limit": 10000000
    }
  ],
  "total_count": 1
}
```

### 3. Validate Relationship Before Transaction

```json
POST /trade-relationships/validate
{
  "buyer_id": "mch_ncnf_mumbai_001",
  "supplier_id": "mch_company_hq_001",
  "region": "IN-MH-MUM",
  "category": "Haircare"
}
```

### 4. Change Relationship Status

```json
PUT /trade-relationships/{id}/status
{
  "status": "suspended",
  "remarks": "Temporary suspension for compliance review"
}
```

## 🔒 Authorization Logic

### The Authoritative Rule

**Every transaction** (PO, Invoice, Return, CN/DN) must validate:

```sql
-- This query MUST return a relationship for transaction to proceed
SELECT * FROM trans.scm_trade_relationship 
WHERE from_merchant_id = :buyer_id 
  AND to_merchant_id = :supplier_id
  AND status = 'active'
  AND valid_from <= CURRENT_DATE
  AND (valid_to IS NULL OR valid_to >= CURRENT_DATE)
  AND (allowed_regions IS NULL OR allowed_regions = ARRAY[] OR :region = ANY(allowed_regions))
  AND (allowed_categories IS NULL OR allowed_categories = ARRAY[] OR :category = ANY(allowed_categories));
```

### Integration with Other Modules

**Purchase Orders**: Must validate relationship before PO creation
```python
# In PO creation service
relationship = await TradeRelationshipService.validate_relationship(
    buyer_id=po_data.buyer_id,
    supplier_id=po_data.supplier_id,
    region=po_data.delivery_region,
    category=item.category
)
if not relationship:
    raise HTTPException(400, "No valid trade relationship")
```

**Credit Validation**: Check credit limits during invoice processing
```python
if relationship.credit_allowed:
    if outstanding_amount + invoice_total > relationship.credit_limit:
        raise HTTPException(400, "Credit limit exceeded")
else:
    # Must be prepaid
    if payment_terms != "PREPAID":
        raise HTTPException(400, "Prepaid payment required")
```

## 📊 Business Rules

### Status Lifecycle
```
Draft → Active → Suspended → Expired → Terminated
  ↓       ↓         ↓         ↓
  Active  Terminated Active   Terminated
  ↓       ↓         ↓
  Terminated      Terminated
```

### Commercial Rules
- **Credit Allowed = True**: Credit limit required, allows NET_X payment terms
- **Credit Allowed = False**: Only PREPAID allowed, no credit limit
- **Empty Regions/Categories**: Unrestricted (can trade anywhere/anything)
- **Populated Regions/Categories**: Restricted to specified values

### Operational Constraints
- **Regions**: Format `IN-MH-MUM` (Country-State-City)
- **Categories**: Format `Haircare`, `Skincare` (PascalCase)
- **Validity**: `valid_to` NULL means indefinite

## 🔍 Performance Features

### Optimized Indexes
- **Buyer Lookup**: Fast supplier discovery
- **Supplier Lookup**: Sales-side queries  
- **Status Filtering**: Active relationship queries
- **Date Filtering**: Validity checks
- **Array Lookups**: Region/category constraints (GIN indexes)

### Caching Strategy
```python
# Future enhancement: Redis caching for frequently accessed relationships
@cached(ttl=300)  # 5 minutes
async def get_cached_relationship(buyer_id: str, supplier_id: str):
    # Implementation for high-frequency validation
```

## 🧪 Testing

### Sample Data Included
The migration creates 3 test relationships:
1. **NCNF Mumbai** → **Company HQ** (NET_30, ₹1Cr credit)
2. **CNF Pune** → **NCNF Mumbai** (NET_15, ₹50L credit)  
3. **Distributor Nashik** → **CNF Pune** (PREPAID, no credit)

### Test Scenarios
```bash
# Test supplier discovery
curl -X POST http://localhost:9101/trade-relationships/suppliers/discover \
  -H "Content-Type: application/json" \
  -d '{"buyer_merchant_id": "mch_ncnf_mumbai_001"}'

# Test relationship validation  
curl -X POST http://localhost:9101/trade-relationships/validate \
  -H "Content-Type: application/json" \
  -d '{"buyer_id": "mch_ncnf_mumbai_001", "supplier_id": "mch_company_hq_001"}'
```

## 🚨 Important Notes

### Hard Guards (Non-Negotiable)
- ❌ **No PO without relationship**: All PO creation must validate first
- ❌ **No Invoice without relationship**: All invoicing must check
- ❌ **No Returns without relationship**: Return processing must verify
- ❌ **No CN/DN without relationship**: Credit/Debit notes must validate

### Status Check Pattern
```python
# Use this pattern in ALL transaction modules
if not relationship or not relationship.is_valid:
    raise HTTPException(
        status_code=400,
        detail="No valid trade relationship exists"
    )
```

### Regional/Category Enforcement
```python
# Validate constraints when specified
if relationship.allowed_regions and region not in relationship.allowed_regions:
    raise HTTPException(400, f"Region {region} not allowed")

if relationship.allowed_categories and category not in relationship.allowed_categories:
    raise HTTPException(400, f"Category {category} not allowed")
```

## 📈 Future Enhancements

### Planned Features
1. **Automatic Expiry**: Background job to expire relationships
2. **Credit Monitoring**: Real-time outstanding balance tracking  
3. **Approval Workflows**: Multi-step relationship approval
4. **Bulk Operations**: CSV import/export for relationship management
5. **Integration Hooks**: Webhooks for relationship status changes
6. **Analytics Dashboard**: Relationship performance metrics

### Integration Roadmap
1. **Phase 1**: PO module integration (validate before PO creation)
2. **Phase 2**: Invoice module integration (credit limit enforcement)  
3. **Phase 3**: Returns module integration (return authorization)
4. **Phase 4**: CN/DN module integration (credit/debit authorization)
5. **Phase 5**: Real-time credit monitoring and alerts

## 🆘 Troubleshooting

### Common Issues

**Issue**: "Trade relationship not found" error
```bash
# Check if relationship exists and is active
SELECT * FROM trans.scm_trade_relationship 
WHERE from_merchant_id = 'buyer_id' 
  AND to_merchant_id = 'supplier_id';
```

**Issue**: "Credit limit exceeded" error  
```bash
# Check current credit configuration
SELECT credit_allowed, credit_limit 
FROM trans.scm_trade_relationship 
WHERE relationship_id = 'relationship_id';
```

**Issue**: Foreign key errors during startup
```bash
# Ensure model is imported in sql.py
# Check that Base.metadata.create_all() includes the model
```

### Support Contacts
- **Database Issues**: Check migration logs and database connectivity
- **API Issues**: Verify authentication and request format
- **Business Logic**: Review relationship status and validity dates

---

## 📝 Summary

The Trade Relationships module is now **fully implemented** and ready for integration:

✅ **Complete CRUD API** with validation and error handling  
✅ **PostgreSQL model** with constraints and performance indexes  
✅ **Supplier discovery** for PO creation workflows  
✅ **Relationship validation** for transaction authorization  
✅ **Status management** with proper lifecycle controls  
✅ **Migration script** with sample data for testing  
✅ **Comprehensive documentation** and usage examples  

**Next Step**: Integrate relationship validation into PO, Invoice, Returns, and CN/DN modules to enforce the business rules.

The module follows all existing patterns and is ready for production use! 🎉