# YSP Payout File Matching and Storage Implementation

## Overview

This implementation provides the functionality to process YSP payout CSV files, match them against existing transactions, and store the matched records with their associated settlement IDs.

## Architecture

### Database Schema

#### New `payout` Table
```sql
CREATE TABLE payout (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  -- YSP Payout File Columns
  payment_mode VARCHAR(255),
  merchant_tag VARCHAR(255),
  merchant_id VARCHAR(255),
  batch_number VARCHAR(255),
  beneficiary_bank VARCHAR(255),
  beneficiary_name VARCHAR(255),
  payment_amount DECIMAL(15,2),
  payment_currency VARCHAR(255),
  beneficiary_account_number_or_iban VARCHAR(255),
  beneficiary_address TEXT,
  beneficiary_email_address VARCHAR(255),
  payment_details_for_beneficiary TEXT,
  payable_country VARCHAR(255),
  beneficiary_bank_bic_or_swift_code VARCHAR(255),
  route_code VARCHAR(255),
  uk_sort_code VARCHAR(255),
  intermediary_bank_bic_or_swift_code VARCHAR(255),
  charge_type VARCHAR(255),
  uae_purpose_of_payment VARCHAR(255),
  jordan_payment_code VARCHAR(255),
  payment_details_link_for_invoice_file VARCHAR(255),
  payment_reference VARCHAR(255),
  future_payment_date DATE,
  beneficiary_country VARCHAR(255),
  
  -- Linked Settlement ID from matching transaction
  settlement_id VARCHAR(255),
  
  inserted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY idx_merchant_id (merchant_id),
  KEY idx_batch_number (batch_number),
  KEY idx_settlement_id (settlement_id),
  KEY idx_merchant_batch (merchant_id, batch_number)
);
```

### Components

#### 1. Migration: `20250915000001_create_payout.exs`
- Creates the `payout` table with all YSP payout file columns
- Adds indexes for efficient querying
- Includes `settlement_id` field for linking to transactions

#### 2. Schema: `DaProductApp.Settlements.Payout`
- Ecto schema defining the payout table structure
- Changeset for validation and data casting
- Required fields: `merchant_id`, `batch_number`

#### 3. Service: `DaProductApp.Settlements.PayoutService`
- Core business logic for CSV processing
- Handles CSV parsing and field extraction
- Performs transaction matching by `merchant_id` and `batch_number`
- Creates payout records with settlement IDs
- Returns detailed processing results

#### 4. Controller: `DaProductAppWeb.PayoutController`
- HTTP endpoint handling for payout processing
- Supports both file upload and raw CSV content
- Returns JSON responses with processing results

#### 5. Context Integration: `DaProductApp.Settlements`
- Added payout-related functions to settlements context
- Functions for listing, creating, and querying payout records

## API Endpoint

### POST `/api/v1/settlements/payout`

Processes YSP payout CSV files and matches them with existing transactions.

#### Request Options

**Option 1: File Upload**
```bash
curl -X POST "http://localhost:4002/api/v1/settlements/payout" \
     -H "Content-Type: multipart/form-data" \
     -F "file=@payout.csv"
```

**Option 2: Raw CSV Content**
```bash
curl -X POST "http://localhost:4002/api/v1/settlements/payout" \
     -H "Content-Type: text/csv" \
     --data-binary "@payout.csv"
```

#### Response Format

**Success Response (200 OK):**
```json
{
  "status": "success",
  "message": "Payout file processed successfully",
  "data": {
    "total_rows": 10,
    "successful_matches": 8,
    "failed_matches": 2,
    "errors": [
      "No matching transaction found for MerchantID: MERCHANT999, BatchNumber: BATCH999"
    ]
  }
}
```

**Error Response (400 Bad Request):**
```json
{
  "status": "error",
  "message": "Failed to process payout file",
  "error": "Invalid CSV format"
}
```

## Processing Logic

### 1. CSV Parsing
- Parses CSV content line by line
- Extracts header row to map column names
- Handles quoted fields and basic CSV formatting
- Case-insensitive field name matching

### 2. Transaction Matching
For each CSV row:
1. Extract `MerchantID` and `BatchNumber` fields
2. Query `transactions` table for matching records:
   ```sql
   SELECT * FROM transactions 
   WHERE merchant_id = ? AND batch_number = ?
   LIMIT 1
   ```
3. If match found, proceed to payout creation
4. If no match, record as failed match with error message

### 3. Payout Record Creation
For matched transactions:
1. Map all CSV fields to payout table columns
2. Add `settlement_id` from matching transaction
3. Create payout record with proper data types
4. Handle data conversion (decimals, dates)

## CSV File Format

### Required Columns
- `MerchantID` - Used for transaction matching
- `BatchNumber` - Used for transaction matching

### Sample CSV Structure
```csv
Payment Mode,MerchantTag,MerchantID,BatchNumber,Beneficiary Bank,Beneficiary name,Payment Amount,Payment Currency,Beneficiary account number or IBAN,Beneficiary Address,Beneficiary Email Address,Payment Details for Beneficiary,Payable Country,Beneficiary Bank BIC or SWIFT Code,Route Code,UK Sort Code,Intermediary Bank BIC or SWIFT Code,Charge type,UAE Purpose of Payment,Jordan Payment Code,Payment Details Link For Invoice File,Payment Reference,Future Payment Date,Beneficiary Country
SWIFT,TAG001,MERCHANT123,BATCH001,Test Bank,John Doe,100.50,USD,1234567890,123 Main St,john@example.com,Payment for services,US,TESTBIC,RC001,123456,INTBIC,OUR,Personal,JP001,http://example.com,REF001,2024-12-01,US
```

## Testing

### Unit Tests
- `PayoutServiceTest` - Tests CSV processing and matching logic
- `PayoutControllerTest` - Tests HTTP endpoint functionality

### Test Coverage
- Valid CSV processing with matching transactions
- CSV processing with no matching transactions  
- Malformed CSV handling
- API endpoint request/response handling

## Usage Examples

### 1. Basic File Processing
```elixir
# Process CSV file
{:ok, results} = DaProductApp.Settlements.PayoutService.process_csv_file("payout.csv")

# Process CSV content
csv_content = File.read!("payout.csv")
{:ok, results} = DaProductApp.Settlements.PayoutService.process_csv_content(csv_content)
```

### 2. Query Payout Records
```elixir
# Get all payouts for a merchant
payouts = DaProductApp.Settlements.list_payouts(%{"merchant_id" => "MERCHANT123"})

# Get payouts for a specific settlement
payouts = DaProductApp.Settlements.get_payouts_by_settlement_id("SETTLE123")
```

## Error Handling

### Common Error Scenarios
1. **Invalid CSV Format** - Malformed CSV, incorrect column count
2. **No Matching Transactions** - MerchantID/BatchNumber not found in transactions table
3. **Data Validation Errors** - Invalid data types, missing required fields
4. **Database Errors** - Connection issues, constraint violations

### Error Response Format
Errors are collected and returned in the processing results:
```json
{
  "total_rows": 5,
  "successful_matches": 3,
  "failed_matches": 2,
  "errors": [
    "Row 2: No matching transaction found for MerchantID: INVALID, BatchNumber: BATCH999",
    "Row 4: Failed to create payout record: payment_amount is invalid"
  ]
}
```

## Security Considerations

1. **Input Validation** - All CSV fields are validated before database insertion
2. **SQL Injection Protection** - Uses parameterized queries via Ecto
3. **File Size Limits** - Consider implementing CSV file size limits
4. **Authentication** - Endpoint should be protected with proper authentication

## Performance Considerations

1. **Batch Processing** - Processes CSV rows individually but could be optimized for batch inserts
2. **Database Indexes** - Proper indexes on `merchant_id`, `batch_number` for fast lookups
3. **Memory Usage** - Loads entire CSV into memory; consider streaming for large files
4. **Transaction Optimization** - Could wrap processing in database transactions

## Future Enhancements

1. **Async Processing** - Use Oban for background processing of large CSV files
2. **File Validation** - Add CSV schema validation before processing
3. **Duplicate Detection** - Prevent duplicate payout records for same merchant/batch
4. **Audit Trail** - Track processing history and file metadata
5. **Export Functionality** - API endpoints to export payout data
6. **Dashboard Integration** - UI components for payout management

## Files Created/Modified

### New Files
- `priv/repo/migrations/20250915000001_create_payout.exs`
- `lib/da_product_app/settlements/payout.ex`
- `lib/da_product_app/settlements/payout_service.ex`
- `lib/da_product_app_web/controllers/payout_controller.ex`
- `test/da_product_app/settlements/payout_service_test.exs`
- `test/da_product_app_web/controllers/payout_controller_test.exs`

### Modified Files
- `lib/da_product_app_web/router.ex` - Added payout endpoint
- `lib/da_product_app/settlements.ex` - Added payout context functions

## Deployment Checklist

1. ✅ Run database migration: `mix ecto.migrate`
2. ✅ Verify payout table creation
3. ✅ Test API endpoint with sample CSV
4. ✅ Verify transaction matching works correctly
5. ✅ Check payout records are created with settlement_ids
6. ✅ Test error handling scenarios
7. ✅ Validate CSV parsing with various formats
8. ✅ Confirm endpoint authentication (if applicable)

This implementation provides a complete solution for YSP payout file processing, matching transactions by merchant ID and batch number, and storing the results with linked settlement IDs as specified in the requirements.