# Plan — Financial Invoice Report: Wizard + Buffer Table

## Status: MENUNGGU KONFIRMASI (4 pertanyaan di bawah)

---

## Masalah dengan Arsitektur Saat Ini

| Problem | Detail |
|---|---|
| `_auto = False` (SQL View) | Setiap buka menu = full scan semua tabel. Tidak ada cache, tidak ada filter |
| N+1 risk | `am.snk_payment_date` adalah stored compute yang query ke `snk.payment.request` per record |
| Tidak ada filter di awal | User tidak bisa filter by date range / partner / OU sebelum data ditarik — data semua dimuat |
| `NULL::varchar as snk_settlement_no` | Dulu kosong, sekarang sudah kita fix JOIN, tapi tetap jalan tiap kali view dibuka |

---

## Arsitektur Baru: Wizard + Buffer Table (Pattern Mesa)

```
┌─────────────────────────────────────┐
│         WIZARD (TransientModel)     │  ← User input filter:
│  snk.financial.report.wizard        │    date_start, date_end, partner, OU,
│                                     │    area, salesperson, company, dst
│  [action_generate]                  │
└────────────────┬────────────────────┘
                 │  DELETE old rows (user_id)
                 │  INSERT...SELECT (satu round-trip, CTE di PostgreSQL)
                 ▼
┌─────────────────────────────────────┐
│      BUFFER TABLE (Model biasa)     │  ← Bukan _auto=False view lagi
│  snk.financial.report               │    Tabel fisik dengan user_id
│                                     │    Domain filter: user_id = uid
└─────────────────────────────────────┘
```

---

## File yang Akan Dibuat / Diubah

```
mito/mito_base/
├── models/account/
│   ├── snk_financial_report.py              [UBAH] _auto=False → model biasa + tambah user_id
│   └── snk_financial_report_wizard.py       [BARU] TransientModel wizard
│
├── views/account/
│   ├── snk_financial_report_view.xml        [UBAH] tambah domain user_id
│   └── snk_financial_report_wizard_view.xml [BARU] form wizard
│
└── __manifest__.py                          [UBAH] daftarkan file baru
```

---

## Strategi Query — Anti N+1, CTE MATERIALIZED

```sql
WITH
-- ANCHOR: invoice lines yang masuk filter (sekali scan)
filtered_lines AS MATERIALIZED (
    SELECT aml.id, aml.move_id, ...
    FROM account_move_line aml
    JOIN account_move am ON ...
    WHERE am.invoice_date BETWEEN %(date_start)s AND %(date_end)s
      AND ... (filter dari wizard)
),

-- Payment request terbaru per invoice (DISTINCT ON, bukan subquery per baris)
latest_payment AS MATERIALIZED (
    SELECT DISTINCT ON (prl.snk_move_id)
        prl.snk_move_id,
        pr.snk_date,
        pr.snk_number
    FROM snk_payment_request_line prl
    JOIN snk_payment_request pr ON prl.request_id = pr.id
    WHERE pr.state = 'posted'
      AND prl.snk_move_id IN (SELECT DISTINCT move_id FROM filtered_lines)
    ORDER BY prl.snk_move_id, pr.snk_date DESC, pr.id DESC
),

-- Delivery cost per invoice (sekali aggregate)
delivery_cost AS MATERIALIZED (
    SELECT aml_d.move_id, SUM(aml_d.price_subtotal) AS cost
    FROM account_move_line aml_d
    JOIN product_product pp_d ON aml_d.product_id = pp_d.id
    JOIN product_template pt_d ON pp_d.product_tmpl_id = pt_d.id
    WHERE pt_d.snk_delivery_cost_ok = TRUE
      AND aml_d.move_id IN (SELECT DISTINCT move_id FROM filtered_lines)
    GROUP BY aml_d.move_id
),

-- COGS per invoice+product (sekali aggregate)
cogs_data AS MATERIALIZED (
    SELECT cogs.move_id, cogs.product_id, SUM(cogs.balance) AS amount
    FROM account_move_line cogs
    WHERE cogs.display_type = 'cogs'
      AND cogs.move_id IN (SELECT DISTINCT move_id FROM filtered_lines)
    GROUP BY cogs.move_id, cogs.product_id
)

-- MAIN SELECT: JOIN semua CTE, FROM filtered_lines sebagai anchor
SELECT ... FROM filtered_lines fl
JOIN account_move_line aml ON aml.id = fl.id
JOIN account_move am ON am.id = fl.move_id
...
LEFT JOIN latest_payment lp ON lp.snk_move_id = fl.move_id
LEFT JOIN delivery_cost dc ON dc.move_id = fl.move_id
LEFT JOIN cogs_data cd ON cd.move_id = fl.move_id AND cd.product_id = aml.product_id
```

---
---

## ❓ Pertanyaan yang Harus Dijawab Sebelum Implementasi

### 1. Kompatibilitas model lama
Model `snk.financial.report` saat ini `_auto = False` (SQL view). Kita akan **drop view lama dan ganti jadi tabel fisik**.
- Apakah ada report/wizard/menu lain yang depends ke model `snk.financial.report`?
- [ coba kamu cek yah pak sebelum kerjakan ] **JAWABAN:**

### 2. Filter tambahan
Filter yang sudah direncanakan: date, partner, OU, area, salesperson, company, payment_state.
- Apakah perlu tambah filter: grade, channel, product category, atau lainnya?
- [ 
Period (start)	"Field: snk_start_financial_inv
Type: date
Model: snk.wizard.financial.inv"	Akan mengambil customer invoice sesuai tanggal periode awal. Jika tidak diisi maka akan ambil semua periode.

Period (end)	"Field: snk_end_financial_inv
Type: date
Model: snk.wizard.financial.inv"	Akan mengambil customer invoice sesuai tanggal periode akhir. Jika tidak diisi maka akan ambil semua periode

Operating Unit	"Field: snk_ou_ids
Type: selelction
Model: snk.wizard.financial.inv"	"Ada 2 pilihan
- All: Semua operating unit yang ada maka akan muncul di report
- Select: > type many2many. Memilih operating unit yang akan muncul di report

Ambil dari snk.master.operating.unit"

Customer	"Field: snk_partner_ids
Type: many2many
Model: snk.wizard.financial.inv"	Jika operating unit diisi maka customer yg muncul sesuai dengan ou yg dipilih 

Apply	"Button: snk_apply
Model: snk.wizard.financial.inv"
Cancel	"Button: snk_cancel
Model: snk.wizard.financial.inv"
  
] **JAWABAN:**

### 3. Return action setelah generate
- **A)** `soft_reload` (seperti Mesa) — tetap di halaman yang sama, data auto refresh
- **B)** Buka list view langsung filtered by `user_id`
- [**B** — buka list view langsung dengan domain `user_id = uid`] **JAWABAN:** ✅ Done

### 4. Pivot & Graph view
- Saat ini view_mode: `list,pivot,graph` — apakah tetap dipertahankan?
- [ belum mintah pendapat kamu gak ] **JAWABAN:**

---

## Checklist Implementasi 

- [x] `snk_financial_report.py` — ubah dari `_auto=False` ke model biasa, tambah `user_id`
- [x] `snk_financial_invoice_report_wizard.py` — buat TransientModel `snk.wizard.financial.inv` dengan filter + `action_generate()`
- [x] Query CTE MATERIALIZED dengan `INSERT...SELECT` (zero N+1)
- [x] `snk_financial_report_view.xml` — update domain `user_id = uid`, menu arahkan ke wizard
- [x] `snk_financial_invoice_report_wizard_view.xml` — form wizard style Mesa: alert info, o_row date range, radio OU, many2many_tags customer
- [x] `snk_financial_report_view.xml` — tambah `<header>` tombol **Generate Report** di list view + `<help>` text saat list kosong
- [x] `__manifest__.py` — daftarkan file wizard baru
- [x] `security/ir.model.access.csv` — tambah akses `snk.wizard.financial.inv`
- [x] `wizards/account/__init__.py` — import wizard baru
- [x] Refactor wizard: ekstrak `_build_where(uid)` — WHERE clause tidak duplikat antara `action_generate` dan `action_explain_query`
- [x] Tambah `action_explain_query()` — `EXPLAIN (ANALYZE, BUFFERS)` output ke log dengan prefix `[FINANCIAL REPORT EXPLAIN]`
- [x] Tambah tombol **Explain Query (N+1 Check)** di footer wizard — di samping Generate Report
- [x] Tambah logging timer di `action_generate`: `[FINANCIAL REPORT] INSERT...SELECT selesai X.XXXs — N rows`
- [x] Tambah file log terpisah `D:\sinerka\project mito\conf\financial_report.log` — dual-log ke terminal + file, level DEBUG, catat filter/WHERE/timer/error
- [x] Drop view lama di DB: `DROP VIEW IF EXISTS snk_financial_report CASCADE` — selesai di DB `MITO_STAGING_SENIN`
- [x] Buat tabel fisik `snk_financial_report` dengan semua kolom + index `user_id` — selesai via psql langsung (bukan upgrade module, karena Odoo akan skip jika tabel sudah ada)
- [ ] Sync buffer payment: tambah `_snk_sync_financial_report_buffer()` di `snk_payment_request.py` → update `snk_payment_date` + `snk_settlement_no` saat payment request di-confirm
- [ ] Test fungsional: buka wizard, isi filter, klik Apply, verifikasi data muncul di list

### Adjustment List View (2026-06-23)
- [x] Hide kolom **Ratio GPM** + restrict `groups="account.group_account_manager"`
- [x] Hide kolom **Gross Profit Margin** + restrict `groups="account.group_account_manager"`
- [x] Hide kolom **HPP** + restrict `groups="account.group_account_manager"`
- [ ] Perbaikan kalkulasi HPP — ambil account dari `property_account_expense_categ_id` kategori produk, per product per line (detail di bawah)
- [x] Munculkan currency (`widget="monetary"`, `currency_field='company_currency_id'`) untuk semua field amount
- [x] Munculkan total (`sum=`) pada end lines untuk semua field amount + qty

---

## Perbaikan Kalkulasi HPP — Belum Dikerjakan (MENUNGGU KEPUTUSAN)

### Kondisi Saat Ini (yang akan diganti)
HPP sekarang diambil murni dari `display_type = 'cogs'` di
[snk_financial_invoice_report_wizard.py:247-253](mito/mito_base/wizards/account/snk_financial_invoice_report_wizard.py#L247-L253):

```sql
LEFT JOIN LATERAL (
    SELECT SUM(c.balance) AS amount
    FROM account_move_line c
    WHERE c.display_type = 'cogs'
      AND c.move_id = fl.move_id
      AND c.product_id = fl.product_id
) cogs ON true
```

### Yang Diminta User
> "Ambil account dari product category field `property_account_expense_categ_id`.
> Hitung per product per lines."

Maksudnya: HPP per line = SUM(balance) journal items yang **account-nya =
akun expense kategori produk** baris itu — bukan sekadar `display_type='cogs'`.

Mapping:
```
aml.product_id → product_template.categ_id (kategori produk)
                 → product.category.property_account_expense_categ_id (akun beban)
HPP line = SUM(balance) account_move_line di invoice ini
           WHERE account_id = <akun expense kategori> AND product_id = line ini
```

### ⚠️ Hal Teknis yang Harus Diingat
1. **`property_account_expense_categ_id` adalah company-dependent property**
   (disimpan di tabel `ir_property`, BUKAN kolom biasa di `product_category`).
   Di SQL tidak bisa `categ.property_account_expense_categ_id` langsung —
   harus resolve via `ir_property`:
   ```sql
   ir_property.name = 'property_account_expense_categ_id'
   AND ir_property.res_id = 'product.category,' || categ.id
   AND ir_property.value_reference = 'account.account,' || <acc_id>
   ```
   Plus tetap jaga anti-N+1: resolve sekali sebagai CTE MATERIALIZED
   (`category_expense_account`), bukan subquery per-line.

### ❓ Keputusan yang Masih Pending (belum dijawab user)
1. **Baris journal mana yang dihitung?**
   - (A) Hanya `display_type='cogs'` TAPI di-filter lagi `account_id = akun kategori`
     → paling aman, sesuai konsep HPP anglo-saxon Odoo.
   - (B) SEMUA `account_move_line` ke akun itu (tanpa peduli `display_type`)
     → risiko ikut baris lain yang kebetulan pakai akun sama.
   - **JAWABAN:** _belum diputuskan_

2. **Company mana untuk resolve `ir_property`?** (property company-dependent)
   - (A) Company dari invoice (`am.company_id`) → benar untuk multi-company.
   - (B) Abaikan company / pakai default global → simpel kalau single-company.
   - **JAWABAN:** _belum diputuskan ("Other" — perlu klarifikasi)_

---

## Optimasi Query — Riwayat EXPLAIN ANALYZE

### Round 1 — Baseline (sebelum optimasi)
**Execution Time: 146,117 ms (146 detik)**

Nested loop problem yang ditemukan:
| Node | Actual Time | Loops | Buffers | Problem |
|---|---|---|---|---|
| `tax_per_line` outer join | ~71,000 ms | 18,928 | 245,953 hits | CTE Scan → Nested Loop, **357 juta rows removed by join filter** |
| `cogs_data` outer join | ~335 ms | 18,928 | 12,763 hits | CTE Scan → Nested Loop, **335 juta rows removed by join filter** |
| `delivery_cost` outer join | ~348 ms | 18,928 | 353,173 hits | CTE Scan → Nested Loop, 56,735 loops `product_template` |
| `tax_per_line` CTE build | ~311 ms | — | 245,953 hits | JOIN balik ke `aml2 → am2 → rc` (4 level Nested Loop extra) |

Root cause: PostgreSQL treat CTE sebagai optimization fence — estimasi rows = 1 (aktual 18,928), planner pilih Nested Loop di semua outer join CTE Scan.

Fix Round 1: Ganti `cogs_data`, `delivery_cost`, `tax_per_line` dari CTE ke **LATERAL subquery** di outer query.

---

### Round 2 — Setelah LATERAL (partial fix)
**Execution Time: 18,733 ms (18.7 detik)**

Sisa bottleneck:
| Node | Actual Time | Loops | Buffers | Problem |
|---|---|---|---|---|
| `ongkir` LATERAL | 0.822 ms × 18,928 | 18,928 | **12,757,886 hits** | Hash Join `product_product` (1276 rows) per-invoice, lalu Nested Loop `product_template` → **4 juta loops**, filter `snk_delivery_cost_ok` |
| `latest_payment` CTE build | 246 ms | — | 130,424 hits | `IN (SELECT DISTINCT move_id FROM filtered_lines)` → re-traverse CTE Scan, estimate rows = 1 |
| `cogs` LATERAL | 0.098 ms × 18,928 | 18,928 | 562,899 hits | Index scan `move_id` lalu filter `product_id + display_type`, 215 rows removed per loop |

Fix Round 2:
- `delivery_cost`: Tambah CTE `delivery_product_ids` MATERIALIZED (scan `product_template` **sekali**), LATERAL tinggal `WHERE product_id IN (set kecil)` — eliminasi 4 juta loops
- `latest_payment`: Ganti `IN (SELECT DISTINCT ...)` ke `JOIN (SELECT DISTINCT ...)` — planner pilih Hash Join sekali, bukan re-traverse CTE
- `cogs`: Swap urutan filter → `display_type = 'cogs'` sebelum `product_id`

---

### Round 3 — Setelah `delivery_product_ids` + fix `latest_payment` Hash Join
**Execution Time: 5,306 ms (5.3 detik)**

Sisa bottleneck:
| Node | Actual Time | Loops | Buffers | Problem |
|---|---|---|---|---|
| `latest_payment` CTE build | 595 ms | — | 213,810 hits | `JOIN (SELECT DISTINCT move_id FROM filtered_lines)` → planner tetap re-traverse `filtered_lines` via CTE Scan karena inline subquery, estimate rows = 1 |
| `cogs` LATERAL | 0.103ms × 30,986 | 30,986 | 993,326 hits | Index scan `move_id` → filter `display_type + product_id`, 228 rows removed per loop (tidak ada composite index) |
| `delivery_cost` LATERAL | ~0ms | 30,986 | 37 hits | `IN (SELECT product_id FROM delivery_product_ids)` → HashAggregate CTE Scan di-evaluate per-loop |

Fix Round 4:
- `distinct_move_ids`: Tambah CTE MATERIALIZED baru yang simpan `DISTINCT move_id` — dipakai oleh `latest_payment` dan `ongkir`, tidak re-traverse `filtered_lines`
- `latest_payment`: Ganti inline `JOIN (SELECT DISTINCT ...)` ke `JOIN distinct_move_ids`
- `ongkir` LATERAL: Ganti `IN (SELECT ...)` ke `JOIN delivery_product_ids` eksplisit — hindari HashAggregate per-loop
- `cogs` LATERAL: Pastikan `display_type = 'cogs'` di posisi pertama filter

---

### Round 4 — Setelah `distinct_move_ids` + fix join ongkir
**Execution Time: (belum diukur — Explain Query lagi untuk konfirmasi)**

Improvement total sejauh ini: **146 detik → 5.3 detik (96% lebih cepat)**
