# Feature Specification: Customer Management (SP-04)

**Feature Branch**: `004-customer-management`

**Created**: 2026-06-04

**Status**: Draft

**Input**: User description: "Build complete Customer Domain with CRUD, Search, Financial Summary, and Async Export"

## User Scenarios & Testing *(mandatory)*

### User Story 1 - Customer Creation (Priority: P1)

Admin can create a new customer by entering the customer's name, phone number, optional description, and optional avatar image.

**Why this priority**: Customer creation is the fundamental entry point for all customer-related operations. Without this, no customer management exists.

**Independent Test**: Can be fully tested by sending a POST request with customer data and verifying the response returns 201 with customer details.

**Acceptance Scenarios**:

1. **Given** valid customer data (name, phone), **When** admin submits POST request to `/api/v1/customers`, **Then** system creates a new user record and returns 201 with customer data including auto-generated ID and timestamps
2. **Given** phone number already exists, **When** admin submits POST request, **Then** system returns 422 validation error indicating phone uniqueness violation
3. **Given** required fields are missing (name or phone), **When** admin submits POST request, **Then** system returns 422 validation error with field-specific errors

---

### User Story 2 - Customer Data Modification (Priority: P1)

Admin can update existing customer information including name, phone, description, and avatar. Updates are allowed even when the customer has active contracts.

**Why this priority**: Customer information may need corrections or updates over time. Unlike contracts, customer data modification is a constitutional right.

**Independent Test**: Can be fully tested by updating customer fields and verifying changes persist in subsequent GET requests.

**Acceptance Scenarios**:

1. **Given** an existing customer, **When** admin submits PUT request with updated name, **Then** system updates the name and returns 200 with updated data
2. **Given** a customer with active contracts, **When** admin submits PUT request to update phone, **Then** system successfully updates phone (constitution allows this)
3. **Given** a non-existent customer ID, **When** admin submits PUT request, **Then** system returns 404 not found

---

### User Story 3 - Customer Listing with Intelligent Sorting (Priority: P1)

Admin can view a paginated list of all customers, sorted by their closest pending or overdue installment due date. The system utilizes the database index `idx_installments_contract_due_status` for efficient sorting.

**Why this priority**: This is the primary view for admin to see which customers need attention first. Sorting by nearest due date is a business requirement for prioritizing collection efforts.

**Independent Test**: Can be fully tested by creating customers with contracts and installments, then verifying the list is correctly ordered without fetching all data to sort in PHP.

**Acceptance Scenarios**:

1. **Given** customers with active installments, **When** admin requests GET `/api/v1/customers`, **Then** results are sorted by minimum due_date of pending/overdue installments per customer using database-level sorting with LEFT JOIN (customers without contracts included, sorted to end with NULL nearest_due_date)
2. **Given** search parameter "Ahmed", **When** admin requests GET with `search=Ahmed`, **Then** system returns only customers whose name or phone matches using case-insensitive partial matching (ILIKE)
3. **Given** time period filter, **When** admin requests GET with `from_date` and `to_date`, **Then** system filters customers by contract creation date within the period
4. **Given** large dataset, **When** admin requests paginated list, **Then** system uses cursor pagination (not offset) for consistent performance
5. **Given** customers with NULL nearest_due_date (no pending installments), **When** sorting, **Then** these customers are pushed to the end using COALESCE(MIN(due_date), '9999-12-31') ORDER BY, with customer_id as secondary sort

---

### User Story 4 - Customer Details with Financial Summary (Priority: P1)

Admin can view comprehensive customer details including computed financial metrics calculated entirely via SQL aggregation (not PHP loops).

**Why this priority**: Provides instant financial overview needed for collection negotiations and status reporting.

**Independent Test**: Can be fully tested by retrieving a customer with contracts and verifying financial aggregates match expected calculated values.

**Acceptance Scenarios**:

1. **Given** a customer with contracts and installments, **When** admin requests GET `/api/v1/customers/{id}`, **Then** system returns customer data plus computed: total_installment_amount, total_collected_amount, total_remaining_amount, total_pending_installments, total_overdue_installments
2. **Given** financial calculations must use SQL SUM/COUNT with CASE, **When** computing metrics, **Then** system performs aggregation in database query, not PHP iteration
3. **Given** non-existent customer, **When** admin requests details, **Then** system returns 404

---

### User Story 5 - Asynchronous Customer Export (Priority: P2)

Admin can request an export of customer data for a specified time period. The export processing happens asynchronously via a queue job to prevent API timeouts.

**Why this priority**: Heavy export operations would cause API timeouts if run synchronously. Queue-based processing ensures admin receives immediate acknowledgment while file generation happens in background.

**Independent Test**: Can be fully tested by dispatching export request and verifying immediate 200/202 response with job visibility in jobs table.

**Acceptance Scenarios**:

1. **Given** valid export parameters (from_date, to_date, format), **When** admin submits POST to `/api/v1/customers/export`, **Then** system immediately returns success response in under 1 second
2. **Given** export job is dispatched, **When** job processes, **Then** it queries filtered customer data, generates file (Excel/PDF), and stores to disk
3. **Given** database queue driver is configured, **When** export is requested, **Then** a job record appears in the jobs table for tracking

---

### Edge Cases

- What happens when a customer has no contracts? They appear in customer list but with no `nearest_due_date` (NULL) - these customers are sorted to the END of the list
- What happens when a customer has only paid installments? They should appear in list with no pending/overdue installments, so their `nearest_due_date` will be NULL and they are pushed to the end
- What happens when export format is neither excel nor pdf? Return 422 validation error
- What happens when from_date is after to_date? Return 422 validation error
- What happens when cursor parameter is invalid? Return 400 bad request

## Requirements *(mandatory)*

### Functional Requirements

- **FR-001**: System MUST allow admins to create new customers via POST `/api/v1/customers` with name, phone, optional description, and optional avatar
- **FR-002**: System MUST allow admins to update customer data via PUT `/api/v1/customers/{id}` with any combination of name, phone, description, avatar
- **FR-003**: System MUST NOT provide any delete endpoint for customers (Constitution rule: Users cannot be deleted)
- **FR-004**: System MUST return paginated customer list via GET `/api/v1/customers` using cursor pagination
- **FR-005**: System MUST support search filtering by customer name or phone number using PostgreSQL `ILIKE '%term%'` for partial, case-insensitive matching
- **FR-006**: System MUST support time period filtering via from_date and to_date query parameters
- **FR-007**: System MUST sort customer list by closest pending/overdue installment due date using database-level query utilizing `idx_installments_contract_due_status` index
- **FR-008**: System MUST return customer details with computed financial summary via GET `/api/v1/customers/{id}`
- **FR-009**: System MUST calculate financial metrics (total_remaining_amount, total_collected_amount, total_pending_installments, total_overdue_installments) using SQL aggregation queries, not PHP collection iteration
- **FR-010**: System MUST dispatch async export job via POST `/api/v1/customers/export` and return immediate success response
- **FR-011**: System MUST use domain-specific routing: all customer routes in `app/Domains/Customer/Routes/v1/api.php`
- **FR-012**: System MUST use CustomerServiceProvider to load domain routes
- **FR-013**: System MUST use ApiResponseHelper for all API responses following unified response format

### Key Entities *(include if feature involves data)*

- **Customer**: A human entity (User) created in the customer context. A customer does NOT need to have contracts - a user becomes a customer simply by being created via the customer creation endpoint. Key attributes: name, phone, description, avatar (stored as relative path string after file upload via Laravel Storage), created_at, updated_at.
- **Contract**: Agreement between customer and company for installment payments. Contains product details, amounts, dates, and status.
- **Installment**: State table representing scheduled payment due dates. Key for sorting: due_date, status (pending/paid/overdue).

## Success Criteria *(mandatory)*

### Measurable Outcomes

- **SC-001**: Admin can create a new customer and receive 201 response within 500ms
- **SC-002**: Admin can update customer data and receive 200 response with updated data
- **SC-003**: Customer list returns results sorted by nearest pending/overdue due date using database-level sorting in under 1 second for 10,000 customers
- **SC-004**: Customer list supports cursor pagination with consistent performance regardless of page number
- **SC-005**: Search by name or phone returns filtered results in under 500ms
- **SC-006**: Customer financial summary accurately reflects SQL-calculated aggregates matching manual calculation
- **SC-007**: Export endpoint returns success response in under 1 second regardless of data volume
- **SC-008**: Export job is visible in jobs table immediately after dispatch
- **SC-009**: Delete operation on User model throws RuntimeException (Constitution enforcement)
- **SC-010**: All API responses follow unified format via ApiResponseHelper

## Assumptions

- Customers are ANY users created via the customer creation endpoint - no contract required (customer = user created in customer context)
- Customers with no contracts or no pending installments appear in list with NULL nearest_due_date and are sorted to the end
- The `idx_installments_contract_due_status` index exists and is properly configured for the sorting query
- Database queue driver is configured and operational from SP-03
- Maatwebsite/Excel and DomPDF packages are available for file generation
- Admin authentication via Sanctum is already implemented (SP-01)
- Permission system (Spatie) is available for authorization (customers.view, customers.create, customers.update, customers.export)
- Timezone is Asia/Damascus for all date operations
- Financial calculations use DECIMAL(10,2) precision (no float/double)
- Avatar is uploaded via Laravel Storage and only the relative path string is stored in the VARCHAR(255) column