# Research: Customer Management (SP-04)

**Created**: 2026-06-04
**Feature**: [spec.md](./spec.md)

## Research Decisions

### 1. Customer List Sorting Query (idx_installments_contract_due_status)

**Decision**: Use SQL subquery to find MIN(due_date) for pending/overdue installments per customer

**Rationale**: The `idx_installments_contract_due_status` index is on `(contract_id, due_date ASC, status)`. To leverage this index efficiently while sorting customers by their nearest pending/overdue due date, we need a query that:

1. Joins customers → contracts → installments
2. Filters installments WHERE status IN ('pending', 'overdue')
3. Groups by customer
4. Orders by MIN(due_date) ASC

**SQL Pattern**:
```sql
SELECT u.*, MIN(i.due_date) as nearest_due_date
FROM users u
LEFT JOIN contracts c ON c.customer_id = u.id
LEFT JOIN installments i ON i.contract_id = c.id AND i.status IN ('pending', 'overdue')
GROUP BY u.id
ORDER BY COALESCE(MIN(i.due_date), '9999-12-31') ASC, u.id ASC
```

**Key Points**:
- Uses LEFT JOIN (not INNER JOIN) to include ALL users created as customers, even without contracts
- Customers with no contracts or no pending/overdue installments will have NULL for nearest_due_date
- COALESCE with far-future date ('9999-12-31') pushes NULLs to the end of the sorted list
- Secondary sort by customer_id ensures deterministic ordering
- This leverages `idx_installments_contract_due_status` index for the status-filtered join

**Alternatives considered**:
- Raw SQL with ORDER BY on subquery (rejected - Constitution prefers Eloquent/Query Builder)
- PHP sorting after fetching (rejected - performance issue with large datasets)
- Using LATERAL JOIN (more complex, not necessary with proper index)

### 2. Financial Summary SQL Aggregation

**Decision**: Use SUM(CASE WHEN ...) and COUNT(CASE WHEN ...) for financial metrics

**Rationale**: To calculate financial summary in a single efficient query without PHP iteration:

```sql
SELECT 
    u.id,
    SUM(i.amount) as total_installment_amount,
    SUM(CASE WHEN i.status = 'paid' THEN i.amount ELSE 0 END) as total_collected_amount,
    SUM(CASE WHEN i.status IN ('pending', 'overdue') THEN i.amount ELSE 0 END) as total_remaining_amount,
    COUNT(CASE WHEN i.status IN ('pending', 'overdue') THEN 1 END) as total_pending_installments,
    COUNT(CASE WHEN i.status = 'overdue' THEN 1 END) as total_overdue_installments
FROM users u
INNER JOIN contracts c ON c.customer_id = u.id
INNER JOIN installments i ON i.contract_id = c.id
WHERE u.id = :customer_id
GROUP BY u.id
```

**Alternatives considered**:
- Multiple queries (rejected - not efficient)
- PHP calculation (rejected - Constitution requirement for SQL aggregation)

### 3. Export File Generation

**Decision**: Use Maatwebsite/Excel for Excel, DomPDF for PDF

**Rationale**:
- Maatwebsite/Excel is already in Constitution as approved package
- DomPDF is already in Constitution as approved package
- Both integrate well with Laravel queues
- File naming: `customers_export_{YYYYMMDD}_{timestamp}.{format}`
- Storage location: `storage/app/exports/`

### 4. Time Filter Basis

**Decision**: Filter customer list by `contracts.created_at` (from_date/to_date)

**Rationale**: Contract creation date is the most business-relevant filter. When querying customers by time period, businesses typically want to know about contracts created in that period.

**Alternatives considered**:
- Customer creation date (users.created_at) - less relevant for business reporting
- First due date - too granular for customer list filtering

### 5. Queue Job Structure for Export

**Decision**: Export job receives filter parameters, queries data, generates file, stores to disk

**Rationale**: Following Constitution's async export pattern:
- Job dispatched with `from_date`, `to_date`, `format`
- Job queries customers matching filters
- Job generates Excel/PDF using Maatwebsite/DomPDF
- Job stores file and (future) could notify admin

**Job Payload**:
```php
[
    'from_date' => '2024-01-01',
    'to_date' => '2024-12-31',
    'format' => 'excel',
    'admin_id' => 1
]
```

---

## Technical References

- Constitution Section 3.3: Indexing Strategy (idx_installments_contract_due_status)
- Constitution Section 4.7: Domain Routing Architecture
- Constitution Section 6.4: Customer Rules (no deletion, modification allowed)
- API_Manifest.md Section 2: Customer endpoints specification