# Data Model: Customer Management (SP-04)

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

## Entity Overview

Customer is a **User** created via the customer creation endpoint. A customer does NOT need to have contracts. This is not a separate table - customer is inferred from the context in which the user was created.

### Entities Used (Existing Tables)

| Entity | Table | Role in Customer Domain |
|--------|-------|------------------------|
| User | `users` | Customer - user created via customer endpoint (contract optional) |
| Contract | `contracts` | Links customer to installment schedule (optional for customer) |
| Installment | `installments` | State table for due dates and status |

### Entity Relationships

```
User (1) ──────< Contract (M) >────── (1) Customer
  │                    │
  │                    │
  │                    └──────────< Installment (M)
  │                                 │
  │                    due_date, status (pending/paid/overdue)
  │
  └────── Agent relationship (separate)
```

**Note**: Customers are ALL users created via the customer endpoint, regardless of whether they have contracts. Users without contracts appear in the list with NULL nearest_due_date and are sorted to the end.

## Customer Identification

A **Customer** is identified by:
- `users.id` as primary key
- `users.phone` as unique business identifier

A user becomes a customer when:
- They are created via POST `/api/v1/customers` (no contract required)

## Data Attributes

### User (Customer) Attributes

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| id | BIGSERIAL | PRIMARY KEY | Unique identifier |
| name | VARCHAR(150) | NOT NULL | Customer full name |
| phone | VARCHAR(20) | NOT NULL, UNIQUE | Phone number for search/contact |
| description | TEXT | NULLABLE | Administrative notes |
| avatar | VARCHAR(255) | NULLABLE | Relative path after file upload via Laravel Storage (e.g., `avatars/uuid.jpg`) |
| created_at | TIMESTAMPTZ | NOT NULL | Registration timestamp |
| updated_at | TIMESTAMPTZ | NOT NULL | Last update timestamp |

**Note on avatar**: When uploaded, the file is stored via Laravel's Storage facade to `storage/app/public/avatars/`. Only the relative path string (e.g., `avatars/uuid.jpg`) is stored in this column.

### Contract Attributes (for customer context)

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| id | BIGSERIAL | PRIMARY KEY | Unique identifier |
| customer_id | BIGINT | FK(users.id) | Reference to customer |
| product_name | VARCHAR(255) | NOT NULL | Product purchased |
| total_after_profit | DECIMAL(10,2) | NOT NULL | Total amount financed |
| monthly_installment_amount | DECIMAL(10,2) | NOT NULL | Monthly payment amount |
| start_date | DATE | NOT NULL | First installment date |
| is_signed | BOOLEAN | DEFAULT FALSE | Contract status |

### Installment Attributes (for sorting/summary)

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| id | BIGSERIAL | PRIMARY KEY | Unique identifier |
| contract_id | BIGINT | FK(contracts.id) | Reference to contract |
| due_date | DATE | NOT NULL | Payment due date |
| amount | DECIMAL(10,2) | NOT NULL | Scheduled amount |
| status | VARCHAR(20) | NOT NULL | pending/paid/overdue |
| paid_at | TIMESTAMPTZ | NULLABLE | Actual payment timestamp |

### Search Implementation

**Decision**: Use PostgreSQL `ILIKE` for case-insensitive partial matching

**Rationale**: The spec requires search by name or phone with partial matching. PostgreSQL's `ILIKE` operator provides:
- Case-insensitive matching
- Partial matching with wildcards (e.g., `%Ahmed%`)
- Better performance than application-level filtering

**SQL Pattern**:
```sql
WHERE (u.name ILIKE '%' || :search || '%' OR u.phone ILIKE '%' || :search || '%')
```

**Alternatives considered**:
- Laravel's `where()` with LIKE (works but ILIKE is explicit for Postgres)
- Application-level filtering (rejected - must filter at database level for performance)

### Installment Status Flow

```
pending ──[payment received]──> paid
   │
   └─[due_date passed]──> overdue
```

Note: An installment can transition from overdue back to pending if a payment is deleted (LIFO).

## Validation Rules

### Create Customer (CreateCustomerRequest)

| Field | Rules |
|-------|-------|
| name | required, string, max:150 |
| phone | required, string, unique:users, regex for phone format |
| description | nullable, string |
| avatar | nullable, image file, max:2048KB |

### Update Customer (UpdateCustomerRequest)

| Field | Rules |
|-------|-------|
| name | sometimes, string, max:150 |
| phone | sometimes, unique:users, ignore:{id} |
| description | nullable, string |
| avatar | nullable, image file, max:2048KB |

### Export Customer (ExportCustomerRequest)

| Field | Rules |
|-------|-------|
| from_date | required, date, before:to_date |
| to_date | required, date, after:from_date |
| format | required, enum:excel,pdf |

## Computed Financial Summary (SQL Aggregation)

### Metrics Calculated per Customer

| Metric | SQL Calculation |
|--------|-----------------|
| total_installment_amount | SUM(amount) from all installments |
| total_collected_amount | SUM(amount) WHERE status = 'paid' |
| total_remaining_amount | SUM(amount) WHERE status IN ('pending', 'overdue') |
| total_pending_installments | COUNT(*) WHERE status = 'pending' |
| total_overdue_installments | COUNT(*) WHERE status = 'overdue' |

### Sorting Query (Database-Level)

```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) - ALL users created as customers are included, even without contracts
- Customers with no pending/overdue installments have NULL nearest_due_date
- COALESCE with '9999-12-31' pushes NULLs to the end (sorted last)
- Secondary sort by u.id ensures deterministic results

**Index Used**: `idx_installments_contract_due_status ON installments(contract_id, due_date ASC, status)`

## Permissions Required

| Action | Permission |
|--------|------------|
| List customers | customers.view |
| Create customer | customers.create |
| Update customer | customers.update |
| View customer details | customers.view |
| Export customers | customers.export |