# Estimates Enterprise KPI/Pivot UAT Checklist (Technical)

## 1. Scope Endpoint

| Endpoint | Method | Tujuan |
|---|---|---|
| `estimates/kpi_core_data` | POST | KPI core terstruktur (`count`, `value`, `accepted_rate`, aging draft, lead priority) |
| `estimates/pivot_owner_month_status_data` | POST | Pivot multi dimensi `Owner x Month x Status` + totals |
| `estimates/prioritas_data_summary_data` | POST | Ringkasan prioritas lead (API compatibility endpoint) |
| `estimates/prioritas_data_list_data` | POST | Daftar detail prioritas lead (aging + missing fields) |

## 2. Payload Standar UAT

```json
{
  "start_date": "2026-04-01",
  "end_date": "2026-04-30",
  "status": "",
  "owner_id": "",
  "month": "",
  "priority_state": "incomplete",
  "priority_owner_id": "",
  "priority_draft_age": "",
  "priority_missing_field": "",
  "priority_province_id": ""
}
```

## 3. Checklist UAT per Endpoint

| ID | Endpoint | Validasi Request | Validasi Response | Validasi SQL/Rekonsiliasi | PASS/FAIL |
|---|---|---|---|---|---|
| TC-KPI-01 | `estimates/kpi_core_data` | Filter tanggal diterapkan (`start_date`,`end_date`) | `success=true`, ada `data.kpi.total_count`, `data.kpi.total_value`, `data.kpi.accepted_rate` | `total_count` = `COUNT(*)` dari scope filter yang sama | |
| TC-KPI-02 | `estimates/kpi_core_data` | Filter `owner_id` valid | `data.kpi.status_breakdown` terisi sesuai status | Sum `status_breakdown.count` = `total_count` | |
| TC-KPI-03 | `estimates/kpi_core_data` | Status accepted/declined tersedia | `accepted_rate` = `accepted/(accepted+declined)*100` | Hitung manual dari distribusi status DB | |
| TC-KPI-04 | `estimates/kpi_core_data` | Lead priority filter jalan | `data.lead_priority` memiliki 4 metrik | Samakan dengan hasil `prioritas_data_summary_data` untuk filter yang sama | |
| TC-PVT-01 | `estimates/pivot_owner_month_status_data` | Filter tanggal aktif | `data[]` memiliki `owner_id,owner_name,month,status,estimate_count,estimate_value` | Sum `estimate_count` di pivot = count KPI filter sama | |
| TC-PVT-02 | `estimates/pivot_owner_month_status_data` | Filter `month=YYYY-MM` aktif | Semua baris hanya untuk month tersebut | Compare dengan query DB group month sama | |
| TC-PVT-03 | `estimates/pivot_owner_month_status_data` | Filter `owner_id` aktif | Semua baris owner sesuai | Compare dengan query DB owner sama | |
| TC-PVT-04 | `estimates/pivot_owner_month_status_data` | Server-side paging (`server_side=1`) | `recordsTotal` dan `recordsFiltered` benar | Jumlah baris page <= `limit` | |
| TC-PRIO-01 | `estimates/kpi_core_data` | Payload valid untuk summary card | `data.lead_priority` berisi 4 metrik summary | Boundary aging day-2/day-3/day-7 benar | |
| TC-PRIO-02 | `estimates/prioritas_data_list_data` | Filter `priority_missing_field` aktif | Kolom missing fields sesuai filter | Cocokkan sample baris UI vs DB | |

## 4. Query Rekonsiliasi Referensi

### 4.1 KPI Count/Value (mirror filter scope)
```sql
SELECT COUNT(*) AS total_count,
       SUM(estimate_value) AS total_value
FROM (
  -- gunakan formula estimate_value dari Estimates_model->get_details()
  -- dan filter scope yang sama (tanggal/status/akses owner)
) t;
```

### 4.2 Status Distribution
```sql
SELECT status, COUNT(*) AS total_count
FROM estimates
WHERE deleted = 0
  AND estimate_date BETWEEN :start_date AND :end_date
GROUP BY status;
```

### 4.3 Pivot Owner x Month x Status
```sql
SELECT c.owner_id,
       DATE_FORMAT(e.estimate_date, '%Y-%m') AS month_key,
       e.status,
       COUNT(*) AS total_count
FROM estimates e
JOIN clients c ON c.id = e.client_id
WHERE e.deleted = 0
  AND c.deleted = 0
  AND e.estimate_date BETWEEN :start_date AND :end_date
GROUP BY c.owner_id, month_key, e.status;
```

## 5. Evidence Minimum

1. Request/response JSON capture tiap endpoint.
2. Screenshot UI card/pivot yang dibandingkan.
3. Output query DB pembanding.
4. Sheet rekonsiliasi `API vs UI vs DB`.
5. Defect ticket jika mismatch.