# LabaBersih v2 — Complete Execution Blueprint

> DOKUMEN INI DITULIS UNTUK AI EXECUTOR.
> Setiap section = 1 domain. Setiap domain punya: schema, function, rules, status.
> JANGAN mulai koding tanpa baca domain yang relevan.
> JANGAN asumsi — semua detail ada di sini. Kalau gak ada = belum diputuskan, TANYA Hafish.
> Cross-reference: master-plan.md (keputusan), business-rules.md (layer detail), journal-formulas.md (14 formula)

---

## DAFTAR ISI

0. [Status & Phase Overview](#0-status--phase-overview)
1. [Domain 1: Master Data](#1-domain-1-master-data)
2. [Domain 2: Accounting](#2-domain-2-accounting)
3. [Domain 3: Order Intake](#3-domain-3-order-intake)
4. [Domain 4: Fulfillment](#4-domain-4-fulfillment)
5. [Domain 5: Returns](#5-domain-5-returns)
6. Domain 6: Intelligence — **di `rules/v2-blueprint-future.md` section 2**
7. Cross-Domain ERD — **di `rules/v2-blueprint-future.md` section 3**
8. [Execution Order](#8-execution-order)

---

## 0. STATUS & PHASE OVERVIEW

### Apa yang SUDAH jadi (jangan bikin ulang)

```
✅ Backend Layer 0-9: 133 tests, 10 context modules
✅ UI Phase 1-5: semua list page + detail page + shared components
✅ Refactor Step 1-14: legal entity, COA auto-generate, per-fee rekon, tax, store FK
✅ Deploy: lababersih.id live di Fly.io
```

### Phase Map

| Phase | Scope | Kapan |
|---|---|---|
| **Phase 1** | Migrasi v1→v2. Nelly bisa kerja. | SEKARANG |
| **Phase 2** | Enhance fulfillment, GRN, advanced features | Setelah v2 live + stable |
| **Phase 3** | Intelligence, analytics, forecasting | Scale |

### Per Domain — Phase Assignment

| Domain | Phase 1 (sekarang) | Phase 2 (nanti) | Phase 3 (scale) |
|---|---|---|---|
| **Master Data** | Product bundle+unit conv, warehouse hierarchy, courier mapping, customer denormalized | Multi-warehouse stok enforcement | — |
| **Accounting** | Daily summary journal (Oban), recurring expenses | Aged receivables, financial health dashboard | — |
| **Order Intake** | XLSX import (auto, no staging), SKU importer | API sync staging pipeline (TikTok/Shopee/Mengantar) | Settlement XLSX parser |
| **Fulfillment** | Keep current packing+ship, add reserved_stock | FulfillmentOrder→PickList→Ship→Handover, GRN | PackingIncident QC |
| **Returns** | Already implemented ✅ | Lot restore improvement | — |
| **Intelligence** | — | — | Demand snapshot, ABC-XYZ, spike, reorder |

---

## 1. DOMAIN 1: MASTER DATA

> Definisi: Data referensi yang jarang berubah. Fondasi semua operasi lain.
> Context: `Lababersih.Inventory`, `Lababersih.Sales`, `Lababersih.Accounts`, `Lababersih.Purchasing`

### 1a. Product — Simple vs Bundle

**Status:** Schema `products` sudah ada. Field `type`, `harga_jual` BELUM ada. Table `bundle_items` BELUM ada.

**Prinsip:**
- Product = master data ONLY (nama, SKU, HPP, harga jual, unit)
- `stok` = readonly, HANYA berubah lewat dokumen (PO Receive / Adjustment / RTS Baik)
- SKU = milik seller, LabaBersih pakai SKU yang SAMA
- 2 tipe: `simple` (punya stok) dan `bundle` (virtual, dari komponen)

**Schema ALTER `products`:**
```sql
ALTER TABLE products ADD COLUMN type VARCHAR NOT NULL DEFAULT 'simple';
  -- "simple" = punya stok sendiri (dari PO)
  -- "bundle" = virtual, stok dihitung dari komponen

ALTER TABLE products ADD COLUMN harga_jual DECIMAL;
  -- Harga jual. Untuk margin report: harga_jual - HPP

ALTER TABLE products ADD COLUMN reserved_stock INTEGER NOT NULL DEFAULT 0;
  -- Stok yang sudah di-claim order tapi belum di-pick/ship
  -- available_stock = stok - reserved_stock
```

**Schema NEW `bundle_items` (BOM — Bill of Materials):**
```sql
CREATE TABLE bundle_items (
  id          BINARY_ID PRIMARY KEY,
  bundle_product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    -- FK ke product yang type=bundle
  component_product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    -- FK ke product yang type=simple
  quantity    INTEGER NOT NULL,
    -- Berapa komponen per 1 bundle
  timestamps
);
CREATE UNIQUE INDEX bundle_items_bundle_component ON bundle_items(bundle_product_id, component_product_id);
```

**Business Rules — Bundle:**
```
Stok bundle = floor(MIN(komponen.stok / bundle_item.quantity)) untuk semua komponen
HPP bundle  = SUM(komponen.hpp × bundle_item.quantity) untuk semua komponen

Contoh:
  BNDL02 [3Kg] Forbest = FRB01 × 3
  FRB01.stok = 100 → BNDL02 tersedia = floor(100/3) = 33
  FRB01.hpp = 45.000 → BNDL02.hpp = 45.000 × 3 = 135.000

Saat bundle dijual:
  Order: BNDL02 × 2
  → Lookup: BNDL02 = FRB01 × 3
  → Consume: FRB01 × 6 (FEFO)
  → HPP = sum lot costs dari 6 unit FRB01
```

**Context Functions — Inventory (tambah):**
```elixir
# Product CRUD update
create_product(attrs)
  # attrs.type = "simple" (default) atau "bundle"
  # Kalau bundle: attrs.bundle_items = [%{component_product_id, quantity}]

update_product(product, attrs)
  # TIDAK boleh ubah type setelah ada transaksi (stok > 0 atau ada order)

# Bundle
get_bundle_components(product_id)
  # Return: [%{component: product, quantity: N}]

calculate_bundle_stock(product_id)
  # Return: integer (available stock dari komponen terkecil)

calculate_bundle_hpp(product_id)
  # Return: Decimal (sum komponen HPP × qty)

# Stock Reservation (Phase 1)
reserve_stock(product_id, qty)
  # Check: (stok - reserved_stock) >= qty
  # Update: reserved_stock += qty
  # Return: {:ok, product} | {:error, :insufficient_available_stock}

release_reservation(product_id, qty)
  # Update: reserved_stock -= qty (floor 0)
  # Return: {:ok, product}

available_stock(product_id)
  # Return: product.stok - product.reserved_stock
```

**Test:**
```
- [ ] Create simple product → type = "simple"
- [ ] Create bundle product + components → bundle_items created
- [ ] Bundle stock = floor(min(component_stock / qty))
- [ ] Bundle HPP = sum(component HPP × qty)
- [ ] Ship bundle → consume komponen (FEFO), bukan bundle sendiri
- [ ] Reserve stock → available turun, stok tetap
- [ ] Reserve lebih dari available → error
- [ ] Release reservation → available naik
```

### 1b. Unit Conversion

**Status:** Table `unit_conversions` BELUM ada.

**Prinsip:** Database SELALU simpan base unit (pcs). Konversi di application layer.

**Schema NEW `unit_conversions`:**
```sql
CREATE TABLE unit_conversions (
  id          BINARY_ID PRIMARY KEY,
  product_id  UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  unit_name   VARCHAR NOT NULL,
    -- "kardus", "lusin", "pak", "box"
  factor      DECIMAL NOT NULL,
    -- 1 unit_name = factor × base unit
    -- Contoh: kardus = 18 (1 kardus = 18 pcs)
  timestamps
);
CREATE UNIQUE INDEX unit_conversions_product_unit ON unit_conversions(product_id, unit_name);
```

**Business Rules:**
```
DB selalu simpan base unit (pcs/botol/unit).
Konversi HANYA di:
  - PO input: user ketik "10 kardus @ Rp 810.000" → system simpan 180 pcs, lot cost Rp 45.000/pcs
  - Display: "180 pcs (10 kardus)"
  - Stock Audit: tampilkan kedua satuan + sisa

Contoh data:
  FRB01 → kardus → 18 (1 kardus = 18 pcs)
  RTN01 → kardus → 20 (1 kardus = 20 botol)

Stock Audit Report:
  SKU    Produk      Stok(pcs) Stok(kardus) Sisa
  FRB01  Forbest     180       10 kardus    0 pcs    ✓ PAS
  FRB02  Forbest B    55        3 kardus    1 pcs    ← sisa
```

**Context Functions — Inventory (tambah):**
```elixir
create_unit_conversion(product_id, %{unit_name, factor})
list_unit_conversions(product_id)
delete_unit_conversion(conversion_id)

convert_to_base(product_id, qty, unit_name)
  # 10 kardus → 180 pcs (qty × factor)

convert_from_base(product_id, qty_base)
  # 180 pcs → %{kardus: 10, sisa: 0}
  # 55 pcs → %{kardus: 3, sisa: 1}

stock_audit_report(org_id)
  # Per product: stok base, stok converted, sisa
```

**Test:**
```
- [ ] Create conversion: FRB01 kardus=18
- [ ] convert_to_base(FRB01, 10, "kardus") = 180
- [ ] convert_from_base(FRB01, 55) = %{kardus: 3, sisa: 1}
- [ ] PO input kardus → lot cost = price/factor
- [ ] Stock audit report shows both units
```

### 1c. Warehouse & Location Hierarchy

**Status:** `warehouses` dan `warehouse_locations` table sudah ada. `parent_id` dan `legal_entity_id` sudah di-alter (refactor step 1). `default_warehouse_id` di stores BELUM ada.

**Prinsip:**
- 1 org bisa punya banyak gudang
- 1 gudang bisa punya hierarchy lokasi: Zone → Rack → Bin (via `parent_id`)
- Stok di-track per gudang via `inventory_lots.warehouse_id`
- 1 toko punya 1 default warehouse (order masuk → stok dari gudang ini)

**Schema ALTER `stores`:**
```sql
ALTER TABLE stores ADD COLUMN default_warehouse_id UUID REFERENCES warehouses(id);
```

**Schema `warehouse_locations` (sudah ada, sudah di-alter):**
```
warehouse_locations:
  id, warehouse_id, name,
  parent_id (FK self, nullable) — Zone→Rack→Bin hierarchy
  legal_entity_id (FK, nullable) — kepemilikan per entity di shared gudang
```

**Hierarchy contoh:**
```
Gudang Kediri (warehouse)
├── Zona Frozen (location, parent=null)
│   ├── Rak A (parent=Zona Frozen, entity=PT Bestari)
│   └── Rak B (parent=Zona Frozen, entity=CV Maju)
└── Zona Packing (location, parent=null)
```

**Business Rules:**
```
- Store.default_warehouse_id → saat order masuk, stok dicek dari gudang ini
- Outbound (ship) → consume lot dari warehouse yang sama dengan store
- PO Receive → masuk ke warehouse yang dipilih saat receive
- Transfer antar gudang = outbound warehouse A + inbound warehouse B (Phase 2)
- Stok per produk = SUM(lot.quantity_remaining) WHERE warehouse_id = X
```

**Context Functions — Inventory (tambah):**
```elixir
# Warehouse Location
create_warehouse_location(warehouse_id, %{name, parent_id, legal_entity_id})
list_warehouse_locations(warehouse_id)  # tree structure
move_location(location_id, new_parent_id)

# Stock per Warehouse
stock_by_warehouse(product_id)
  # Return: [%{warehouse_id, warehouse_name, qty}]

# Transfer (Phase 2)
transfer_stock(product_id, from_warehouse_id, to_warehouse_id, qty)
```

### 1d. Courier Mapping

**Status:** Table `courier_mappings` BELUM ada.

**Prinsip:** Marketplace kirim nama ekspedisi berbeda-beda ("Shopee Cargo JNE", "JNE REG", "JNE ECO"). Semua = 1 induk kurir "JNE". Mapping ini untuk:
1. Sort paket per ekspedisi saat handover
2. Normalize data untuk report

**Schema NEW `courier_mappings`:**
```sql
CREATE TABLE courier_mappings (
  id        BINARY_ID PRIMARY KEY,
  org_id    UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  raw_name  VARCHAR NOT NULL,
    -- Dari marketplace, as-is: "Shopee Cargo JNE", "JNE REG"
  courier   VARCHAR NOT NULL,
    -- Induk kurir: "JNE", "J&T", "SiCepat"
  timestamps
);
CREATE UNIQUE INDEX courier_mappings_org_raw ON courier_mappings(org_id, raw_name);
```

**Seed data (default per org):**
```
"Shopee Hemat"       → "J&T"
"Shopee Cargo JNE"   → "JNE"
"Shopee Standard"    → "J&T"
"JNE REG"            → "JNE"
"JNE ECO"            → "JNE"
"J&T Express"        → "J&T"
"SiCepat REG"        → "SiCepat"
"SiCepat Halu"       → "SiCepat"
"AnterAja"           → "AnterAja"
"ID Express"         → "ID Express"
"SPX Express"        → "SPX"
```

**Context Functions — Sales atau Fulfillment:**
```elixir
resolve_courier(org_id, raw_name)
  # Lookup courier_mappings → found: {:ok, "JNE"} | not found: {:unknown, raw_name}

create_courier_mapping(org_id, %{raw_name, courier})
list_courier_mappings(org_id)
delete_courier_mapping(id)

list_unmapped_couriers(org_id)
  # Orders/shipments where courier_raw NOT IN courier_mappings
```

### 1e. Customer

**Status:** KEPUTUSAN: Denormalized di order. TIDAK ada tabel `customers` terpisah.

**Alasan:**
- Seller marketplace gak kenal customernya (data dari platform)
- Repeat buyer tracking bisa nanti dari aggregasi `customer_phone`
- Tabel terpisah = overhead tanpa benefit sekarang

**Data customer ada di:**
```
orders.customer_name   — "Budi"
orders.customer_phone  — "081234" (nullable)
```

**Future (kalau butuh):**
- Aggregate by phone: `SELECT customer_phone, COUNT(*), SUM(total_harga) FROM orders GROUP BY customer_phone`
- Baru bikin tabel `customers` kalau ada fitur spesifik (loyalty, notes, etc.)

### 1f. Store — Relasi Lengkap (sudah implemented, referensi)

```
Store:
├── org_id → Organization
├── legal_entity_id → Legal Entity (nullable)
├── default_warehouse_id → Warehouse (BELUM, Phase 1)
├── platform: "tiktok" / "shopee" / "mengantar" / "pos"
├── fee_mapping: jsonb [{label, accountCode, percent, flatAmount}]
├── 8 Account FKs (auto-generated saat create store):
│   ├── piutang_account_id
│   ├── revenue_account_id
│   ├── kas_account_id
│   ├── hpp_account_id
│   ├── persediaan_account_id
│   ├── retur_account_id
│   ├── sample_account_id (nullable)
│   └── transfer_kas_account_id (nullable, Mengantar only)
```

### 1g. Legal Entity (sudah implemented, referensi)

```
Legal Entity:
├── org_id → Organization
├── name: "PT Bestari Jaya"
├── entity_type: "pt" / "cv" / "personal"
├── tax_status: "non_pkp" / "pkp"
├── npwp: "01.234.567.8-901.000"
├── is_default: boolean (1 default per org)
├── Owns: stores, POs, lots, stock_transactions
└── Tax: tax_records per bulan
```

### EXECUTION STEPS — Domain 1 (Phase 1)

```
Step M1: Migration — ALTER products + NEW bundle_items + NEW unit_conversions + NEW courier_mappings + ALTER stores (default_warehouse_id)
Step M2: Schema — BundleItem, UnitConversion, CourierMapping. Update Product schema (type, harga_jual, reserved_stock)
Step M3: Context — Bundle functions (get_components, calculate_stock, calculate_hpp)
Step M4: Context — Unit conversion functions (convert_to_base, convert_from_base, stock_audit)
Step M5: Context — Stock reservation (reserve, release, available)
Step M6: Context — Courier mapping (resolve, create, list_unmapped)
Step M7: Test — Semua function di atas
Step M8: Seed — Update seed dengan bundle product + unit conversions + courier mappings
Step M9: UI — Update produk form (type selector, bundle components, unit conversions, harga_jual)
Step M10: UI — Update produk list (tab: Semua / SKU Utama / Bundle)
Step M11: UI — Courier mapping page (list + add/edit)
```

---

## 2. DOMAIN 2: ACCOUNTING

> Definisi: Semua yang berhubungan dengan uang, jurnal, laporan, pajak.
> Context: `Lababersih.Accounting`
> Cross-ref: journal-formulas.md (14 formula detail)

### 2a. COA — 3 Level Hierarchy (sudah implemented ✅)

```
Level 1: Type header    → 12 Piutang (is_header=true, source_type="template")
Level 2: Platform header → 12-2 Piutang Shopee (source_type="platform")
Level 3: Store leaf      → 12-201 Piutang Shopee Bestari (source_type="store")
```

Auto-generate: `Sales.AccountGenerator.generate_store_accounts/2` — advisory lock anti-race.

### 2b. Journal Architecture — Daily Summary + Per-Order Lines (BELUM implemented)

**Status:** Keputusan final = Opsi 4. Schema fields sudah di-alter (`batch_date`, `batch_type`, `order_id`). Logic BELUM.

**Prinsip:**
```
SAAT SHIP (real-time per order):
  → Stok potong (FEFO lot consumption) ✅ sudah ada
  → HPP tercatat per order ✅ sudah ada
  → Jurnal fee BELUM dicatat (batch nanti)

DAILY BATCH (Oban job akhir hari, per toko):
  → 1 jurnal header: "Penjualan Shopee Bestari — 30 Mar 2026"
  → Lines per order:
      Dr. Piutang (PS2603-00001)  Rp 184.000
      Dr. Admin   (PS2603-00001)  Rp   6.000  ← fee estimasi
      Dr. Komisi  (PS2603-00001)  Rp  10.000
      Cr. Revenue (PS2603-00001)  Rp 200.000
  → 1 jurnal HPP summary per toko per hari:
      Dr. HPP     (total hari ini)
      Cr. Persediaan (total hari ini)

SETTLEMENT (7-14 hari kemudian):
  → Per-fee matching (estimasi vs aktual) ✅ sudah ada
  → 1 jurnal adjustment per settlement
  → Known fee → auto-adjust ke akun yang benar
  → Unknown fee → suspense account (61-999)
```

**Scale comparison:**
```
300 order/hari:  per-order = 600 jurnal → daily summary = 6 jurnal + 300 line sets
6.300 order/hari: per-order = 12.600 jurnal → daily summary = ~30 jurnal + 6.300 line sets
```

**Context Functions — Accounting (tambah):**
```elixir
# Oban Worker: DailySalesJournalWorker
# Schedule: setiap hari jam 23:59 WIB (atau configurable)

generate_daily_sales_journal(org_id, date)
  # Per store yang punya shipped orders pada date tersebut:
  #   1. Query orders WHERE store_id = X AND shipped_at::date = date AND status IN (dikirim, selesai)
  #   2. Per order: calculate fees dari store.fee_mapping × order.total_harga
  #   3. Build journal lines: Dr. Piutang + Dr. Fees / Cr. Revenue (per order, order_id di line)
  #   4. Create 1 jurnal dengan batch_type = "daily_sales", batch_date = date
  #   5. Build HPP summary: Dr. HPP / Cr. Persediaan (1 pair, total per toko)
  #   6. Create 1 jurnal HPP dengan batch_type = "daily_hpp", batch_date = date
  # Idempotent: skip kalau sudah ada jurnal batch_type+batch_date+store

generate_daily_sales_journal_all(org_id, date)
  # Loop semua stores, call generate_daily_sales_journal per store

# Manual trigger (untuk backfill)
backfill_daily_journals(org_id, date_from, date_to)
```

**Test:**
```
- [ ] Ship 3 orders hari ini → generate_daily_sales_journal → 1 jurnal penjualan + 1 jurnal HPP
- [ ] Jurnal penjualan punya lines per order (order_id filled)
- [ ] Total debit = total credit (balanced)
- [ ] Idempotent: generate 2x → tetap 1 jurnal (bukan 2)
- [ ] Gak ada shipped orders → skip (no empty journal)
```

### 2c. Per-Fee Reconciliation + Suspense (sudah implemented ✅)

**Referensi:** `reconciliation.ex` sudah rewrite di refactor step 8.

```
Alur:
1. Validate order.store_id == attrs.store_id
2. Calculate estimated piutang dari fee mapping × gross
3. Per fee: match estimated vs actual
4. Known fee dengan selisih → adjustment ke akun fee
5. Unknown fee → suspense account 61-999
6. Jurnal: Dr. Kas / Cr. Piutang ± per-fee adjustments
```

### 2d. Period Closing (sudah implemented ✅)

```
Sequential: bulan sebelumnya HARUS sudah closed
3 jurnal penutup:
  1. Dr. Pendapatan / Cr. ILR (30-400)
  2. Dr. ILR / Cr. Beban
  3. Dr/Cr ILR ↔ Laba Tahun Berjalan (30-500)
```

### 2e. Tax — PPh Final (sudah implemented ✅)

```
Per legal entity per bulan:
  gross_revenue = SUM revenue jurnal entity bulan itu
  tax_payable = gross_revenue × 0.5%
  status: draft → filed → paid
PPN: structure ready (nullable fields), implement nanti kalau ada client PKP
```

### 2f. Aged Receivables (BELUM implemented — Phase 2)

**Definisi:** Laporan piutang per toko, grouped by umur.

**Data source:** Orders WHERE status = "dikirim" (belum "selesai" / belum rekonsil).
Umur = selisih hari dari `shipped_at` sampai hari ini.

**Output:**
```
Toko Shopee Bestari:
  0-7 hari:   Rp 15.000.000  (normal)
  8-14 hari:  Rp  3.000.000  (perhatian)
  15-30 hari: Rp    500.000  (WARNING)
  > 30 hari:  Rp    100.000  (BAHAYA)
```

**Context Function:**
```elixir
aged_receivables(org_id)
  # Return: [%{store_id, store_name, buckets: %{"0-7" => Decimal, "8-14" => Decimal, ...}, total: Decimal}]
```

### 2g. Recurring Expenses (BELUM implemented — Phase 2)

**Schema NEW `recurring_expenses`:**
```sql
CREATE TABLE recurring_expenses (
  id                BINARY_ID PRIMARY KEY,
  org_id            UUID NOT NULL REFERENCES organizations(id),
  legal_entity_id   UUID REFERENCES legal_entities(id),
  description       VARCHAR NOT NULL,  -- "Sewa Gudang Kediri"
  amount            DECIMAL NOT NULL,  -- 5.000.000
  debit_account_id  UUID NOT NULL REFERENCES accounts(id),  -- beban (62-xxx)
  credit_account_id UUID NOT NULL REFERENCES accounts(id),  -- kas (11-100)
  schedule_day      INTEGER NOT NULL,  -- tanggal generate (1-28)
  is_active         BOOLEAN NOT NULL DEFAULT true,
  last_generated    DATE,
  timestamps
);
```

**Oban Worker: RecurringExpenseWorker**
```
Schedule: daily check
Logic: WHERE is_active AND schedule_day = today AND (last_generated IS NULL OR last_generated < current_month)
  → Auto-create jurnal: Dr. debit_account / Cr. credit_account
  → Update last_generated = today
```

### 2h. Financial Health Dashboard (BELUM implemented — Phase 2)

**7 metrics (semua dari data existing):**
```
1. Piutang Ratio = piutang_belum_cair / omzet_bulan × 100%
2. Cash Gap = cash_masuk - cash_keluar bulan ini
3. Days to Cash = avg(settlement_date - shipped_at)
4. Burn Rate = total beban operasional per bulan
5. RTS Rate = jumlah_rts / total_order × 100%
6. Margin per Toko = (pendapatan - beban) / pendapatan × 100%
7. PKP Threshold = omzet_ytd / 4.800.000.000 × 100%
```

### EXECUTION STEPS — Domain 2 (Phase 1)

```
Step A1: Oban Worker — DailySalesJournalWorker (generate per toko per hari)
Step A2: Context — generate_daily_sales_journal/3
Step A3: Context — backfill_daily_journals/3
Step A4: Test — daily journal generation + idempotency
Step A5: Config — Oban schedule (23:59 WIB daily)
```

**Phase 2 steps (nanti):**
```
Step A6: Migration — NEW recurring_expenses
Step A7: Context + Worker — RecurringExpenseWorker
Step A8: Context — aged_receivables/1
Step A9: Context — financial_health_metrics/1
Step A10: UI — Recurring expenses form + list
Step A11: UI — Aged receivables report tab
Step A12: UI — Financial health dashboard widgets
```

---

## 3. DOMAIN 3: ORDER INTAKE

> Definisi: Bagaimana order MASUK ke sistem (dari luar → production).
> Context: `Lababersih.Orders`, `Lababersih.Integrations`
> Dependency: Master Data (product, store) HARUS sudah ada

### 3a. XLSX Import — Auto (Tanpa Staging/Preview)

**Status:** BELUM implemented. Keputusan: langsung masuk production, TANPA staging.

**Alasan gak pakai staging untuk XLSX:**
- Nelly upload 300+ order/hari
- Preview = 1 klik extra × 300 = buang waktu
- Duplikat auto-skip (by nomor_pesanan)
- SKU unknown → order tetap masuk, item flagged

**Flow:**
```
1. Klik "Upload XLSX" di header Pesanan
2. Modal: pilih file + pilih toko (dropdown)
3. System parse XLSX:
   a. Detect format (Shopee/TikTok/Mengantar) by column headers
   b. Extract orders + items
   c. Per item: match SKU ke products (exact match)
4. Auto-import:
   ├── Order baru → create (status: "dibuat")
   ├── Duplikat (nomor_pesanan exists) → skip
   ├── SKU unknown → order masuk, item.product_id = nil, flagged
   └── Invalid data → reject, log error
5. Toast: "285 pesanan masuk, 10 duplikat, 5 SKU unknown"
6. Redirect ke order list
```

**Schema ALTER `orders`:**
```sql
ALTER TABLE orders ADD COLUMN platform_order_date UTC_DATETIME;
  -- Tanggal dari platform (kapan customer order)
ALTER TABLE orders ADD COLUMN imported_at UTC_DATETIME;
  -- Tanggal masuk ke LabaBersih
ALTER TABLE orders ADD COLUMN fulfillment_status VARCHAR DEFAULT 'pending';
  -- "pending" / "fulfilled"
ALTER TABLE orders ADD COLUMN source VARCHAR DEFAULT 'manual';
  -- "xlsx" / "api" / "manual"
```

**Dependency baru:** XLSX parser library.
```elixir
# mix.exs
{:xlsxir, "~> 1.6"}  # atau {:elixlsx, "~> 0.6"}
```

**3 Parser (1 per platform):**

**TikTok XLSX Parser:**
```
- 63 kolom, 1 row = 1 line item (multi-row per order)
- Revenue formula: SKU Subtotal - Seller Discount
- Group by order ID → combine items
- JANGAN: sub_total - seller_discount (double subtraction!)
```

**Shopee XLSX Parser:**
```
- 49 kolom, 1 row = 1 line item
- Revenue formula: Total Pembeli + Diskon Shopee - Voucher - Paket Diskon
- Standard XLSX format
```

**Mengantar XLSX Parser:**
```
- HTML-disguised .xls files → DOMParser fallback
- 1 row = 1 order with embedded product list
- Revenue COD: productValue - estimatedPrice
- Revenue Non-COD: productValue
```

**Context Functions — Orders (tambah):**
```elixir
parse_order_xlsx(file_path, platform)
  # Detect format → call platform-specific parser
  # Return: {:ok, [%{nomor_pesanan, customer_name, items: [...], ...}]}
  #       | {:error, reason}

import_orders_from_xlsx(org_id, store_id, parsed_orders)
  # Per order:
  #   - Cek duplikat (nomor_pesanan exists) → skip
  #   - Match SKU ke products → link product_id
  #   - SKU gak ketemu → item.product_id = nil
  #   - Create order + items via Orders.create_order
  # Return: %{imported: N, skipped: N, sku_unknown: N, errors: [...]}
```

### 3b. API Sync — Staging Pipeline (Phase 2)

**Status:** Table `staging_orders` sudah ada (Layer 8). Logic BELUM.

**Flow (BEDA dari XLSX — pakai staging karena API = continuous):**
```
API Sync (TikTok/Shopee/Mengantar)
  → Parser → staging_orders (upsert)
  → User preview di /app/staging
  → Approve → production orders
```

**Context Functions — Integrations:**
```elixir
create_staging_orders(org_id, orders_data)
  # Bulk upsert ke staging_orders
  # Dedup by platform_order_id per org

list_staging_orders(org_id, opts)
  # Filter: status, platform, source, date range

approve_staging_orders(org_id, staging_ids)
  # Per staging order → Orders.create_order
  # Update staging.status = "approved"

reject_staging_orders(org_id, staging_ids)
  # Update staging.status = "rejected"
```

### 3c. SKU Importer (Phase 1 — onboarding produk cepat)

**Problem:** Seller punya 67 produk. Input 1-1 = capek.

**Flow:**
```
1. Upload XLSX pesanan 7 hari terakhir
2. System parse → extract SKU unik + nama produk
3. Tabel: SKU | Nama | HPP (user isi) | Harga Jual (user isi)
4. User bulk-fill
5. Confirm → create semua produk sekaligus (stok = 0)
```

**Context Function:**
```elixir
extract_unique_skus(parsed_orders)
  # Return: [%{sku, product_name, count}] — sorted by frequency

bulk_create_products(org_id, products_data)
  # Per product: create with stok=0, type="simple"
  # Return: %{created: N, skipped: N (already exists)}
```

### 3d. SKU Matching

**Prinsip:** Seller sudah punya SKU konsisten di semua marketplace. Exact match 90% kasus.

```
Match cascade:
1. Exact SKU match → link product_id
2. Not found → item.product_id = nil, flagged "SKU unknown"

TIDAK ADA fuzzy matching di Phase 1.
User resolve unknown SKU manual di halaman produk/order.
```

### EXECUTION STEPS — Domain 3 (Phase 1)

```
Step O1: Add dependency — xlsxir atau elixlsx di mix.exs
Step O2: Migration — ALTER orders (platform_order_date, imported_at, fulfillment_status, source)
Step O3: Schema — Update Order schema dengan field baru
Step O4: Parser — TikTok XLSX parser (63 kolom, multi-row)
Step O5: Parser — Shopee XLSX parser (49 kolom)
Step O6: Parser — Mengantar XLSX parser (HTML-disguised)
Step O7: Context — parse_order_xlsx/2, import_orders_from_xlsx/3
Step O8: Context — extract_unique_skus/1, bulk_create_products/2
Step O9: Test — Parse sample XLSX → correct order data
Step O10: Test — Import with dedup + SKU matching
Step O11: UI — Upload modal (file picker + toko dropdown)
Step O12: UI — SKU Importer page (extract → fill → bulk create)
Step O13: UI — Order list enrichment (2-level row, 12 fields in 7 kolom)
```

---

## 4. DOMAIN 4: FULFILLMENT

> Definisi: Bagaimana order DIPROSES dari terima sampai keluar gudang.
> Context: `Lababersih.Orders` (Phase 1), `Lababersih.Fulfillment` (Phase 2)

### 4a. Phase 1 — Keep Current + Enhance

**Sudah implemented:**
- Packing session: create → scan barcode → ship
- `ship_order/2`: atomic (status + stok FEFO + jurnal + audit)
- Advisory lock ID generation

**Tambahan Phase 1:**
- `reserved_stock` di products (lihat Domain 1)
- `courier_mappings` (lihat Domain 1)
- Saat order masuk → `reserve_stock(product_id, qty)`
- Saat ship → `release_reservation` + actual consume (sudah ada)
- Saat cancel → `release_reservation`

**Update `ship_order/2`:**
```elixir
# Tambah di step pertama:
# 1. Release reservation (karena stok akan di-consume)
# 2. ... existing logic (consume FEFO, jurnal, audit)
```

> **Phase 2 detail (12 tabel baru + context functions):** See `rules/v2-blueprint-future.md` section 1.

### EXECUTION STEPS — Domain 4

**Phase 1 (sekarang):**
```
Step F1: Update ship_order — add release_reservation before consume
Step F2: Update create_order — add reserve_stock after create
Step F3: Update cancel order — add release_reservation
Step F4: Test — reservation flow (create → reserve, ship → release + consume, cancel → release)
```

**Phase 2 (nanti):**
```
Step F5: Migration — 8 new tables (fulfillment_orders, fo_items, pick_lists, pl_items, shipments, courier_handovers, packing_incidents, grn, grn_items)
Step F6: Schema — 9 schema files
Step F7: Context — Lababersih.Fulfillment (full)
Step F8: Context — Update Purchasing (GRN replace receive_po)
Step F9: Test — Full fulfillment flow
Step F10: UI — FulfillmentOrder + PickList + Shipment + Handover pages
```

---

## 5. DOMAIN 5: RETURNS

> Definisi: Pengembalian barang (customer → seller, seller → supplier).
> Context: `Lababersih.Returns`, `Lababersih.Purchasing`
> Status: SUDAH IMPLEMENTED ✅ (Layer 5 + 6)

### 5a. Customer Return / RTS (implemented ✅)

```
Flow:
  Order status dikirim → detect RTS → create return_record
  ↓
  RTS Inbound Session:
  ├── Baik   → stok masuk + lot restore + reverse piutang (A3)
  ├── Rusak  → jurnal kerugian (A4: Dr. 64-700 / Cr. 13-100)
  └── Hilang → klaim kurir (A5: Dr. 12-400 / Cr. 13-100)
  ↓
  Klaim Kurir:
  ├── Diterima → A6: Dr. 11-100 / Cr. 12-400
  └── Ditolak  → A7: Dr. 64-600 / Cr. 12-400
```

**v2 improvement vs v1:** Lot restore saat RTS baik ✅ (v1 tidak restore lot)

**Platform difference:**
- Shopee/TikTok: piutang = NET (gross - fees)
- Mengantar: piutang = GROSS (tanpa fee), TIDAK ada ongkir tambahan

### 5b. Supplier Return (implemented ✅)

```
Flow:
  Barang cacat/expired → return ke supplier
  ├── Cash PO  → A11: Dr. 11-100 / Cr. 13-100
  └── Utang PO → A12: Dr. 20-100 / Cr. 13-100
```

### 5c. Customer Refund/Replacement (BELUM implemented — Phase 2)

```
Refund  → marketplace handle, LabaBersih track jurnal only
Replace → kirim baru tanpa payment: Dr. Beban Ganti / Cr. Persediaan
```

### EXECUTION STEPS — Domain 5

**Phase 1: Tidak ada — sudah implemented ✅**

**Phase 2:**
```
Step R1: Context — process_customer_refund (jurnal tracking only)
Step R2: Context — process_replacement_shipment (Dr. Beban Ganti / Cr. Persediaan)
```

---

## 6. DOMAIN 6: INTELLIGENCE

> Phase 3 — detail lengkap di `rules/v2-blueprint-future.md` section 2.
> Scope: Demand snapshots, ABC-XYZ classification, spike detection, campaign planning, reorder suggestions, new product tracking.
> Context: `Lababersih.Intelligence` (BARU, Phase 3). Dependency: Orders + Inventory + Accounting HARUS mature dulu.

---

## 7. CROSS-DOMAIN ERD

> ERD diagram + Key Relationships di `rules/v2-blueprint-future.md` section 3.

---

## 8. EXECUTION ORDER

### Phase 1 — Prioritized (migrasi v1→v2)

```
SESSION A: Master Data Migration
  Step M1:  Migration (ALTER products, NEW bundle_items, NEW unit_conversions,
            NEW courier_mappings, ALTER stores default_warehouse_id)
  Step M2:  Schema files (BundleItem, UnitConversion, CourierMapping, update Product)
  Step M3:  Context — bundle functions
  Step M4:  Context — unit conversion functions
  Step M5:  Context — stock reservation
  Step M6:  Context — courier mapping
  Step M7:  Tests (semua di atas)
  Step M8:  Seed update

SESSION B: Order Intake
  Step O1:  Add XLSX parser dependency
  Step O2:  Migration — ALTER orders (4 new fields)
  Step O3:  Schema update Order
  Step O4-O6: 3 XLSX parsers (TikTok, Shopee, Mengantar)
  Step O7:  Context — parse + import functions
  Step O8:  Context — SKU importer (extract + bulk create)
  Step O9-O10: Tests
  Step O11: UI — Upload modal
  Step O12: UI — SKU Importer page

SESSION C: Daily Journal + Fulfillment Enhancement
  Step A1-A2: Oban DailySalesJournalWorker + context function
  Step A3:    Backfill function
  Step A4:    Tests
  Step F1-F3: Update ship/create/cancel with reservation
  Step F4:    Tests

SESSION D: UI Polish
  Step M9-M10: Produk form update + tab bundle
  Step M11:    Courier mapping page
  Step O13:    Order list enrichment (2-level row)
  Step A5:     Oban schedule config

SESSION E: Style Refactor (TERAKHIR)
  → Apply styling-spec.md (Shopify Polaris + Stripe + Xero)
  → Semua halaman
  → Dark mode verify
  → Mobile verify
```

### Phase 2 — Setelah v2 Live + Stable

```
SESSION F: Fulfillment Full
  Step F5-F10: 8 new tables, context, UI

SESSION G: Accounting Enhance
  Step A6-A12: Recurring expenses, aged receivables, financial health

SESSION H: API Integration
  → TikTok Shop sync → colok ke staging pipeline
  → Shopee sync → colok ke staging pipeline
  → Mengantar sync → colok ke staging pipeline

SESSION I: Settlement XLSX
  → Shopee settlement parser
  → TikTok settlement parser
  → Flow: parse → staging → approve → process_reconciliation
```

### Phase 3 — Scale

```
SESSION J: Intelligence
  Step I1-I9: Demand, ABC-XYZ, spike, campaign, reorder
```

---

## RULES UNTUK AI EXECUTOR

1. **BACA domain yang relevan SEBELUM koding** — jangan asumsi
2. **Ikuti execution order** — jangan loncat session
3. **Per step: implement → compile → test → commit** — jangan batch
4. **Semua function return {:ok, result} atau {:error, reason}** — no exceptions
5. **org_id di setiap query** — multi-tenant enforced
6. **Repo.transaction untuk multi-step** — atomic
7. **Pattern matching untuk status** — gak ada if-else bertingkat
8. **Test WAJIB** — setiap function punya test
9. **Logic di context, BUKAN LiveView** — separation of concerns
10. **Angka JANGAN diasumsi** — tanya Hafish kalau ragu
11. **JANGAN rewrite dari ingatan** — BACA source file dulu
12. **WIB timezone** — semua datetime +07:00
13. **Advisory lock** — untuk ID generation (PS, PO, RTS, JE, PKG, FO, PL, SHP, HO, GRN)
14. **Cross-reference:** journal-formulas.md untuk detail 14 formula jurnal
15. **Cross-reference:** v1-integration-reference.md untuk API detail per platform
16. **Cross-reference:** business-rules.md untuk platform differences (Shopee ≠ TikTok ≠ Mengantar)