# Estimates Pivot P1 - Technical Implementation Checklist

## Status update (2026-04-19)
- P1 core implementation: **done**.
- P2 core implementation (client/lead/aging/exception): **done in code**, menunggu UAT + reconciliation.
- Bugfix penting terbaru: `All time` sudah tidak mewarisi range tanggal lama.
- Fokus sesi berikutnya: validasi input hardening, UAT lintas role, SQL baseline reconciliation.

## 1) Scope P1 (locked)
- Global filter konsisten untuk semua blok pivot:
  - `owner_id`, `role_profile`, `status`, `time_frame`, `date_from`, `date_to`, `date_basis`, `source_id`, `sub_source_id`, `entity_type`.
- Role profile otomatis dari role user (default), admin boleh override.
- Tambahan laporan:
  - Per user (owner performance).
  - Per channel submit (source/sub source).

## 2) Definition of Done (P1)
- Semua endpoint pivot menerima filter global yang sama.
- Angka antar blok konsisten untuk filter yang sama.
- Role profile auto terpasang dan tidak bocor lintas scope akses.
- UI pivot menampilkan:
  - summary cards
  - owner performance
  - channel performance
- Export mengikuti filter global aktif.

## 3) Endpoint Contract (P1)

### Existing page endpoint
- `GET /estimates/pivot_report`

### Data endpoints (P1)
- `GET /estimates/pivot_summary_data`
- `GET /estimates/pivot_owner_performance_data`
- `GET /estimates/pivot_channel_performance_data`

### Required query params
- `owner_id` (optional)
- `role_profile` (optional; auto dari session jika kosong)
- `status` (optional)
- `time_frame` (optional; enum: `this_week|this_month|this_quarter|ytd|rolling_30|rolling_90|custom`)
- `date_from`, `date_to` (required jika `time_frame=custom`)
- `date_basis` (enum: `estimate_date|created_at|accepted_at`)
- `source_id` (optional)
- `sub_source_id` (optional)
- `entity_type` (enum: `all|lead|client`)

### Response standard
- JSON wajib berisi:
  - `success: true/false`
  - `filters_applied`
  - `captured_at`
  - `rows`
  - `totals`

## 4) File-by-file implementation checklist

## A. [Estimates.php](\\192.168.5.14\web\san_ibb_master\app\Controllers\Estimates.php)
- [x] Tambah parser filter global (single function), contoh: `buildPivotFilterInput()`.
- [x] Tambah resolver timeframe + custom range.
- [x] Tambah resolver `date_basis`.
- [x] Tambah role profile auto resolver dari session/user role.
- [ ] Terapkan access scope check sebelum query (row-level policy).
- [ ] Tambah endpoint:
  - [x] `pivot_summary_data()`
  - [x] `pivot_owner_performance_data()`
  - [x] `pivot_channel_performance_data()`
- [x] Standarkan payload response JSON.
- [ ] Tambah validasi input:
  - [x] tanggal invalid
  - [ ] range terlalu besar
  - [x] enum tidak dikenal

## B. [Reports.php](\\192.168.5.14\web\san_ibb_master\app\Controllers\Reports.php)
- [ ] (Jika dipakai) daftarkan route/menu report eksekutif terpisah atau redirect ke `/estimates/pivot_report`.
- [ ] Selaraskan permission check agar role tidak melihat data di luar scope.

## C. View Pivot Estimates (gunakan file yang dipakai saat ini)
- Kandidat utama:
  - [index.php](\\192.168.5.14\web\san_ibb_master\app\Views\estimates\index.php)
  - atau view pivot dedicated (jika sudah dipisah).
- [x] Tambah komponen filter global lengkap:
  - [x] time frame selector
  - [x] custom date range
  - [x] date basis
  - [x] source/sub source
  - [x] entity type
- [x] Role profile default auto (readonly untuk non-admin bila diperlukan).
- [x] Wiring AJAX ke 3 endpoint P1.
- [x] Tampilkan `captured_at` dari response.
- [ ] Tampilkan warning jika filter invalid/no data.

## D. Language files (jika label belum ada)
- [custom_lang.php EN](\\192.168.5.14\web\san_ibb_master\app\Language\english\custom_lang.php)
- [custom_lang.php ID](\\192.168.5.14\web\san_ibb_master\app\Language\Indonesian\custom_lang.php)
- [x] Tambah key label filter/kpi yang baru agar konsisten `app_lang`.

## 5) KPI formula lock (P1)
- [ ] `estimate_count = COUNT(estimates)`
- [ ] `estimate_value = SUM(estimate_total/value)`
- [ ] `accepted = COUNT(status='accepted')`
- [ ] `declined = COUNT(status='declined')`
- [ ] `accepted_rate = accepted / (accepted + declined) * 100`
- [ ] `avg_deal_size = estimate_value / estimate_count`
- [ ] `lead_count = COUNT(entity_type='lead')`
- [ ] `client_count = COUNT(entity_type='client')`

Rule:
- Jika denominator 0 => 0.00%.

## 6) Query mapping matrix (P1)
- Owner performance:
  - Group by: `owner`, `status`, bucket waktu (`week`/`month` sesuai filter).
  - Metrics: count, value, accepted, declined, accepted_rate, lead_count, client_count.
- Channel performance:
  - Group by: `source`, `sub_source`, `status`.
  - Metrics: count, value, accepted_rate, avg_deal_size.
- Summary:
  - Group by: none (grand aggregate) + optional trend by month.

## 7) Security checklist (P1)
- [ ] Non-admin tidak bisa override role_profile di luar haknya.
- [ ] Owner filter tidak bisa dipakai untuk akses data lintas scope.
- [ ] Export hanya untuk role yang diizinkan.
- [ ] Semua akses endpoint pivot tercatat (user + waktu + filter hash).

## 8) QA checklist (P1)
- [ ] Test timeframe preset vs custom range hasil konsisten.
- [ ] Test date_basis menghasilkan angka berbeda sesuai ekspektasi.
- [ ] Test accepted_rate dengan kasus denominator=0.
- [ ] Test owner/channel report terhadap SQL baseline.
- [ ] Test role visibility (Direksi vs Manager vs Ops).
- [ ] Test export = angka di UI.

## 9) UAT evidence template (P1)
- [ ] Screenshot filter aktif.
- [ ] Screenshot summary cards.
- [ ] Screenshot owner performance table.
- [ ] Screenshot channel table.
- [ ] Catat parameter request + timestamp.
- [ ] Catat angka verifikasi SQL baseline.

## 10) Rollout plan (P1)
- [ ] Dev implementasi endpoint + UI.
- [ ] Internal QA + reconciliation.
- [ ] UAT user bisnis (Direksi/Manager/Ops).
- [ ] Sign-off KPI contract.
- [ ] Production release (window + rollback note).

## 11) Catatan eksekusi
- P1 tidak menambah tabel baru.
- Fokus pada reuse data existing + konsistensi filter.
- Hardening enterprise lanjutan (monitoring/alerting/SLO) mengikuti roadmap P2/P3.