# SPECIFICATIONS/04_DATABASE/02_MATERIALIZED_VIEWS.md

**PURPOSE:** Defines the SQL for every Materialized View in the system and describes its output columns. This file answers "what data does each MV physically contain?" — it contains zero application-level access logic.

> **Refresh strategy and scheduling rationale:** `../03_ARCHITECTURE/03_PERFORMANCE_STRATEGY.md`
> **Index definitions for these views:** `03_INDEXES.md`
> **Application-level Eloquent models and scopes:** `../../CONSTITUTIONS/04_DATABASE_COMMUNICATION.md`

---

## customer_listing_mv

**Purpose:** Pre-computed customer list data enabling fast sorting by nearest due installment, status filtering, and time-range filtering.

### SQL Definition

```sql
CREATE MATERIALIZED VIEW customer_listing_mv AS
WITH nearest_unpaid AS (
    SELECT 
        c.id AS client_id,
        ct.product_name AS next_due_product_name,
        i.due_date AS next_due_date,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY i.due_date ASC) AS rn
    FROM clients c
    JOIN contracts ct ON ct.customer_id = c.id 
        AND ct.status IN ('active', 'completed')
    JOIN installments i ON i.contract_id = ct.id 
        AND i.status IN ('pending', 'overdue')
    WHERE 'customer' = ANY(c.client_type_flags)
),
installment_status AS (
    SELECT 
        ct.customer_id,
        BOOL_OR(i.status = 'overdue') AS has_overdue,
        BOOL_OR(
            i.status = 'pending' 
            AND DATE_TRUNC('month', i.due_date) = DATE_TRUNC('month', CURRENT_DATE)
        ) AS has_due_this_month,
        BOOL_OR(
            i.status = 'paid' 
            AND i.paid_at IS NOT NULL 
            AND DATE_TRUNC('month', i.paid_at) = DATE_TRUNC('month', CURRENT_DATE)
        ) AS paid_this_month
    FROM contracts ct
    JOIN installments i ON i.contract_id = ct.id
    WHERE ct.status IN ('active', 'completed')
    GROUP BY ct.customer_id
)
SELECT 
    c.id AS client_id,
    c.name,
    c.phone,
    c.avatar,
    c.reference_number,
    c.created_at AS customer_created_at,
    MIN(ct.start_date) AS first_contract_date,
    n.next_due_date,
    n.next_due_product_name,
    COALESCE(SUM(i.amount), 0) AS total_installment_amount,
    COALESCE(SUM(CASE WHEN i.status = 'paid' THEN i.amount ELSE 0 END), 0) AS total_collected_amount,
    COALESCE(SUM(CASE WHEN i.status IN ('pending', 'overdue') THEN i.amount ELSE 0 END), 0) AS total_remaining_amount,
    COALESCE(COUNT(CASE WHEN i.status = 'pending' THEN 1 END), 0) AS total_pending_installments,
    COALESCE(COUNT(CASE WHEN i.status = 'overdue' THEN 1 END), 0) AS total_overdue_installments,
    COALESCE(s.has_overdue, false) AS has_overdue,
    COALESCE(s.has_due_this_month, false) AS has_due_this_month,
    COALESCE(s.paid_this_month, false) AS paid_this_month
FROM clients c
LEFT JOIN contracts ct ON ct.customer_id = c.id 
    AND ct.status IN ('active', 'completed')
LEFT JOIN installments i ON i.contract_id = ct.id 
    AND i.status IN ('pending', 'overdue', 'paid')
LEFT JOIN nearest_unpaid n ON n.client_id = c.id AND n.rn = 1
LEFT JOIN installment_status s ON s.customer_id = c.id
WHERE 'customer' = ANY(c.client_type_flags)
GROUP BY c.id, c.name, c.phone, c.avatar, c.reference_number, c.created_at, 
         n.next_due_date, n.next_due_product_name,
         s.has_overdue, s.has_due_this_month, s.paid_this_month
WITH DATA;
```

### Output Columns

| Column | Type | Description |
|:---|:---|:---|
| `client_id` | BIGINT | Primary key (matches `clients.id`) |
| `name` | VARCHAR | Customer name |
| `phone` | VARCHAR | Phone number |
| `avatar` | VARCHAR | Relative path (convert to URL at response time) |
| `reference_number` | VARCHAR | Document reference |
| `customer_created_at` | TIMESTAMPTZ | Account creation date (sort option) |
| `first_contract_date` | DATE | Earliest contract start date (time filter) |
| `next_due_date` | DATE | Nearest unpaid installment due date (default sort) |
| `next_due_product_name` | VARCHAR | Product for nearest due |
| `total_installment_amount` | DECIMAL | Total from active/completed contracts |
| `total_collected_amount` | DECIMAL | Sum of paid installments |
| `total_remaining_amount` | DECIMAL | Sum of pending/overdue installments |
| `total_pending_installments` | INT | Count of pending installments |
| `total_overdue_installments` | INT | Count of overdue installments |
| `has_overdue` | BOOLEAN | Has any overdue installment |
| `has_due_this_month` | BOOLEAN | Has installment due this month |
| `paid_this_month` | BOOLEAN | Paid any installment this month |

### Refresh

| Aspect | Configuration |
|:---|:---|
| Frequency | Every 5 minutes |
| Method | `REFRESH MATERIALIZED VIEW CONCURRENTLY` |
| Command | `analytics:refresh-customer-listing` |
| Data freshness | Up to 5 minutes stale |

> **Scheduler implementation and performance rationale:** `../03_ARCHITECTURE/03_PERFORMANCE_STRATEGY.md`