# Settlement EOD API Implementation

This document describes the implementation of the YSP Settlement EOD API options with CSV & JSON format support.

## Overview

The implementation provides transaction-level settlement file generation in both CSV and JSON formats, following the exact specification requirements. It includes proper SHA256 checksum validation, filename formatting, and comprehensive error handling.

## Features Implemented

### 📄 File Formats

#### CSV Format
- **Header Section**: Metadata with all required fields (`#FileType`, `#Version`, `#SettlementDate`, etc.)
- **Transaction Section**: Transaction-level data with columns: `QRTransactionID,QRID,TID,TransactionAmount,Currency,TransactionStatus,TransactionTime,MDRCharge,TaxOnMDR,NetReceived,BatchNumber`
- **Footer Section**: Row count, SHA256 checksum, generated by info, and end marker
- **Checksum**: SHA256 hash excluding checksum and end-of-file lines, uppercase hexadecimal format

#### JSON Format
- **Structured Mirror**: Same data as CSV but in JSON format with nested objects
- **Amounts**: Value/currency pairs for all monetary fields
- **Transactions**: Array of transaction objects with proper field mapping
- **Footer**: Metadata including checksum and row count

### 🌐 API Endpoints

| Endpoint | Method | Description |
|----------|--------|-------------|
| `/api/v1/settlements/settlement/{merchantId}/{date}` | GET | Returns both CSV and JSON files or format based on Accept header |
| `/api/v1/settlements/settlement/{merchantId}/{date}/csv` | GET | Returns CSV file only |
| `/api/v1/settlements/settlement/{merchantId}/{date}/json` | GET | Returns JSON file only |

#### Parameters
- `merchantId`: Merchant identifier (e.g., `900111222333444`)
- `date`: Settlement date in YYYY-MM-DD format (e.g., `2025-07-05`)
- `sequence`: Optional query parameter for file sequence number (defaults to 1)

#### Headers
- **Accept**: `text/csv` for CSV format, `application/json` for JSON format
- **Content-Type**: Set appropriately in response (`text/csv` or `application/json`)
- **Content-Disposition**: Attachment with proper filename

### 📁 File Naming Convention

Files follow the pattern: `mercury_pay_{MMDD}_{HHMMSS}_{seq}.{ext}`

- `MMDD`: Month and date (e.g., `0705` for July 5th)
- `HHMMSS`: Time in 24-hour format (e.g., `221046` for 22:10:46)
- `seq`: Zero-padded sequence number (e.g., `01`, `02`)
- `ext`: File extension (`csv` or `json`)

**Example**: `mercury_pay_0705_221046_01.csv`

## Implementation Details

### Core Modules

#### `DaProductApp.Settlements.TransactionEodGenerator`
Main module for generating transaction-level EOD files.

**Key Functions**:
- `generate_settlement_files/1`: Main entry point for file generation
- `generate_csv_content/3`: Creates CSV content with proper format
- `generate_json_content/3`: Creates JSON content with structured data
- `calculate_checksum/1`: SHA256 checksum calculation

#### Controller Integration
**`DaProductAppWeb.SettlementController`** includes new actions:
- `generate_merchant_settlement_files/2`: Handles main endpoint
- `generate_merchant_settlement_csv/2`: Handles CSV-specific endpoint  
- `generate_merchant_settlement_json/2`: Handles JSON-specific endpoint

### Database Integration

Uses existing schemas:
- **`Settlement`**: Main settlement record with metadata
- **`SettlementTransaction`**: Individual transaction details

Queries filter by `merchant_id` and `date` to retrieve relevant data.

### Error Handling

- **Invalid Date**: Returns 400 with clear error message
- **Missing Settlement**: Returns 400 when no settlement found for merchant/date
- **File Generation Errors**: Returns 500 with error details
- **JSON Encoding Errors**: Proper error handling for malformed data

## API Usage Examples

### 1. Get Both Formats
```bash
curl -X GET 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05'
```

### 2. Get CSV Only
```bash
curl -X GET 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05/csv'
```

### 3. Get JSON Only
```bash
curl -X GET 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05/json'
```

### 4. Use Accept Header for Format Selection
```bash
# Request CSV format
curl -H 'Accept: text/csv' 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05'

# Request JSON format  
curl -H 'Accept: application/json' 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05'
```

### 5. Specify Sequence Number
```bash
curl -X GET 'http://localhost:4000/api/v1/settlements/settlement/900111222333444/2025-07-05/csv?sequence=2'
```

## Test Coverage

The implementation includes comprehensive tests covering all 8 specification test cases:

1. **Happy-Path Settlement (Small Batch)**: Basic functionality with multiple transactions
2. **Mismatch-Detected Scenario**: Handling of data inconsistencies  
3. **Checksum Integrity Failure**: Validation of checksum calculation
4. **Multiple Batches Same Day**: Sequence number handling
5. **Zero-Transaction Settlement**: Edge case with no transactions
6. **High-Value / Large Batch Stress Test**: Performance validation
7. **Invalid Currency Code**: Input validation
8. **Header Field Missing**: Required field validation

### Running Tests

```bash
# Run specific transaction EOD generator tests
mix test test/da_product_app/settlements/transaction_eod_generator_test.exs

# Run settlement controller tests
mix test test/da_product_app_web/controllers/settlement_controller_test.exs

# Run all tests
mix test
```

## Sample Output

### CSV Example
```csv
#FileType,QR_Payment_Settlement
#Version,1.1
#SettlementDate,2025-07-05
#MerchantTag,TAG123
#BankUserID,MERCURY_AANI123
#MID,900111222333444
#TotalTransactionCount,3
#GrossSettlementAmount,225.00,AED
#MDRCharges,4.50,AED
#TaxOnMDR,0.23,AED
#NetSettlementAmount,220.27,AED
#MismatchDetected,NO
#GeneratedTimestamp,2025-07-05T23:59:59+05:30
QRTransactionID,QRID,TID,TransactionAmount,Currency,TransactionStatus,TransactionTime,MDRCharge,TaxOnMDR,NetReceived,BatchNumber
aani_pay_2030560038715669_2047757450700001,AANI9876543210000000,90080001,100.00,AED,SUCCESS,2025-07-05T12:35:00+05:30,2.00,0.10,97.90,000001
aani_pay_2030560038715669_2047757450700002,AANI9876543210000000,90080001,50.00,AED,SUCCESS,2025-07-05T12:40:00+05:30,1.00,0.05,48.95,000001
aani_pay_2030560038715669_2047757450700003,AANI9876543210000000,90080001,75.00,AED,FAILED,2025-07-05T12:50:00+05:30,0.00,0.00,0.00,000001
#TotalRowCount,3
#Checksum,AB1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF
#FileGeneratedBy,AaniQR System
#EndOfFile
```

### JSON Example
```json
{
  "fileType": "QR_Payment_Settlement",
  "version": "1.1",
  "settlementDate": "2025-07-05",
  "merchantTag": "TAG123",
  "bankUserId": "MERCURY_AANI123",
  "merchantId": "900111222333444",
  "batchNumber": "000001",
  "totalTransactionCount": 3,
  "grossSettlementAmount": {"value": "225.00", "currency": "AED"},
  "mdrCharges": {"value": "4.50", "currency": "AED"},
  "taxOnMdr": {"value": "0.23", "currency": "AED"},
  "netSettlementAmount": {"value": "220.27", "currency": "AED"},
  "mismatchDetected": false,
  "generatedTimestamp": "2025-07-05T23:59:59+05:30",
  "transactions": [
    {
      "transactionId": "aani_pay_2030560038715669_2047757450700001",
      "qrId": "AANI9876543210000000", 
      "terminalId": "90080001",
      "transactionAmount": {"value": "100.00", "currency": "AED"},
      "transactionStatus": "SUCCESS",
      "transactionTime": "2025-07-05T12:35:00+05:30",
      "mdrCharge": {"value": "2.00", "currency": "AED"},
      "taxOnMdr": {"value": "0.10", "currency": "AED"},
      "netReceivedAmount": {"value": "97.90", "currency": "AED"}
    }
  ],
  "footer": {
    "totalRowCount": 3,
    "checksum": "AB1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF",
    "fileGeneratedBy": "AaniQR System",
    "endOfFile": true
  }
}
```

## Security Considerations

- **Checksum Validation**: SHA256 ensures file integrity
- **Input Validation**: Date format and merchant ID validation
- **Error Handling**: No sensitive data exposed in error messages
- **File Storage**: Files stored in controlled directory structure
- **Access Control**: API endpoints follow existing authentication patterns

## Performance Considerations

- **Efficient Queries**: Optimized database queries with proper indexing
- **Memory Management**: Streaming processing for large transaction volumes
- **File Generation**: Efficient string building and file writing
- **Caching**: Consider caching for frequently requested files

## Deployment Notes

1. **Database**: Ensure settlement and settlement_transaction tables exist with proper data
2. **File Storage**: Verify write permissions for `priv/static/settlements/` directory
3. **Dependencies**: Jason library required for JSON encoding
4. **Environment**: Configure appropriate timezone settings for IST conversion

## Future Enhancements

- **Background Processing**: For very large datasets (>50k transactions)
- **File Caching**: Cache generated files to avoid regeneration
- **Compression**: Support for gzipped file downloads
- **Webhooks**: Notification system for file generation completion
- **Audit Trail**: Logging of file generation requests and results

---

**Status**: ✅ Implementation Complete and Ready for Production

**Compliance**: ✅ Fully compliant with YSP Settlement EOD API specification

**Test Coverage**: ✅ 100% of specification test cases covered