# DATABASE_DESIGN — BANDUNGCRAFT.COM Phase 3

> Status: **Rancangan (belum dibuat).** Tidak ada database production di Phase ini.
> Engine: **InnoDB**, charset **utf8mb4 / utf8mb4_unicode_ci**, uang memakai **DECIMAL(15,2)**, tidak pernah FLOAT.

## 1. Konvensi

- PK: `id BIGINT UNSIGNED AUTO_INCREMENT` kecuali tabel yang terbukti perlu UUID (activity untuk skala besar).
- Semua FK memakai `ON UPDATE CASCADE ON DELETE RESTRICT` kecuali dinyatakan SOFT.
- Uang: `DECIMAL(15,2)`, stok/berat: `DECIMAL(12,3)` (kg) dan `INT` untuk pcs.
- Semua tabel mempunyai `created_at TIMESTAMP NULL`, `updated_at TIMESTAMP NULL`; tabel transaksional ditambah `deleted_at` (soft delete) kecuali `payments`, `transactions`, `journal_entry_lines`.
- Audit: kolom `created_by`, `updated_by` pada tabel yang bisa diubah admin.
- Unique business code: `orders.order_number`, `invoices.invoice_number`, `quotations.quotation_number`, `transactions.transaction_number` (immutable, tidak di-reuse walau row di-soft-delete).
- Idempotency: `payments` mempunyai `idempotency_key CHAR(36) UNIQUE` untuk cegah double payment.

## 2. Tabel

### User & Akses
| Tabel | Kunci | Catatan |
|---|---|---|
| `users` | PK id; UQ email | `password_hash VARCHAR(255)`, `phone`, `is_active`, soft delete |
| `roles` | PK id; UQ name | Super Admin, Admin, Operator Produksi, Kasir |
| `permissions` | PK id; UQ name | slug seperti `products.manage` |
| `role_permissions` | PK (role, permission) | pivot |
| `user_roles` | PK (user, role) | pivot, satu user bisa multi-role |

### Pelanggan
| Tabel | Kunci |
|---|---|
| `customers` | PK id; UQ email |
| `customer_addresses` | PK id; FK customer_id; `is_primary`, `is_shipping` |

### Produk
| Tabel | Kunci |
|---|---|
| `categories` | PK id; UQ slug |
| `products` | PK id; **UQ SKU**, UQ slug; `cost_price`, `base_price`, `discount_percent`, `stock_qty`, `weight_gram` |
| `product_images` | PK id; FK product_id |
| `product_variants` | PK id; FK product_id; warna/ukuran; UQ (product_id, color, size) |
| `inventory` | PK product_id; `qty_on_hand`, `qty_reserved` |
| `stock_movements` | PK id; FK product_id; `kindENUM(tambah,kurang,penyesuaian)`, `before_qty`, `after_qty` |

### Custom Order & Quotation
| Tabel | Kunci |
|---|---|
| `custom_orders` | PK id; UQ custom_number; FK customer_id |
| `custom_order_images` | FK custom_order_id |
| `custom_order_files` | FK custom_order_id (STL/3MF/OBJ) |
| `quotations` | PK id; **UQ quotation_number**; FK custom_order_id nullable |
| `quotation_items` | FK quotation_id; biaya desain/material/printing/finishing/tambahan; `discount` |
| `quotation_revisions` | FK quotation_id; snapshot JSON + catatan |

### Pesanan & Pembayaran (multiple payment per order)
| Tabel | Kunci |
|---|---|
| `orders` | PK id; **UQ order_number**; FK customer_id; `subtotal`, `discount`, `shipping_cost`, `additional_cost`, `total`
  - `paid` dan `due` adalah **generated column** dari SUM(payments.amount WHERE status=success) dan tidak diisi manual |
| `order_items` | FK order_id; FK product_id nullable (custom) |
| `payments` | PK id; FK order_id; `amount DECIMAL(15,2)`, `kindENUM(dp,pelunasan,pembayaran)`, `statusENUM(pending,success,failed)`, **UQ idempotency_key** |
| `payment_methods` | PK id; UQ code (transfer, qris, tunai, va) |
| `invoices` | PK id; **UQ invoice_number**; FK order_id |
| `shipments` | FK order_id nullable (1 order = 1 shipment utama tapi schema memungkinkan multi) |

### Produksi
| Tabel | Kunci |
|---|---|
| `printers` | PK id; UQ code |
| `production_jobs` | PK id; UQ job_number; FK order_id nullable, FK printer_id, FK material_id; `weight_kg`, `estimated_minutes` |
| `production_files` | FK production_job_id |
| `production_updates` | FK production_job_id; stage, operator, catatan |
| `materials` | PK id; UQ code; `categoryENUM(PLA,PETG,TPU,ABS,Resin,Lainnya)`, `weight_remaining_kg`, `min_stock_kg`, `price_per_kg` |
| `material_movements` | FK material_id; pembelian/pemakaian |

### POS
| Tabel | Kunci |
|---|---|
| `cash_sessions` | PK id; FK user_id; buka/tutup, saldo awal, saldo akhir, total tunai/non-tunai, pengeluaran kas |
| `transactions` | PK id; **UQ transaction_number**; FK cash_session_id nullable |
| `transaction_items` | FK transaction_id |
| `transaction_payments` | FK transaction_id; method, amount, change |
| `cash_movements` | FK cash_session_id (in/out manual) |

### Keuangan
| Tabel | Kunci |
|---|---|
| `expense_categories` | PK; UQ name (Pembelian Material, Listrik, Operasional, Gaji, Packaging, Pengiriman, Marketing, Maintenance, Lainnya) |
| `expenses` | FK category_id; `amount`, `expense_date` |
| `journal_entries` | UQ journal_number; debit/credit totals |
| `journal_entry_lines` | FK journal_entry_id; account, debit, credit — **tidak bisa dihapus permanen** |

### Website
| Tabel | Kunci |
|---|---|
| `website_settings` | key-value + kolom terstruktur (`site_name`, `primary_color`, `whatsapp`, `seo_*`, `uploads_*`) |
| `banners` | halaman/kampanye |
| `pages` | UQ slug |
| `menus` | lokasi + urutan |
| `coupons` | UQ code |

### Sistem
| Tabel | Kunci |
|---|---|
| `activity_logs` | FK user_id; ip, action, entity, detail |
| `notifications` | FK user_id nullable |

## 3. Index penting

- `orders(customer_id, status, created_at)`, `payments(order_id, status)`, `transactions(date, settlement)`, `expenses(category_id, expense_date)`, `stock_movements(product_id, at)`, `activity_logs(user_id, at)`, `inventory(qty_on_hand)` untuk low-stock.

## 4. Migration plan (Laravel)

1. `001_create_users_roles_permissions_*`
2. `002_create_customers_addresses`
3. `003_create_categories_products_variants_inventory_stock`
4. `004_create_materials_printers`
5. `005_create_custom_orders_files_quotations_items_revisions`
6. `006_create_orders_items_payments_invoices_shipments`
7. `007_create_production_jobs_files_updates`
8. `008_create_cash_sessions_transactions_*`
9. `009_create_expense_categories_expenses_journals`
10. `010_create_website_tables`
11. `011_create_activity_logs_notifications`
12. `012_add_unique_business_numbers + idempotency`
13. Seeder: roles/permissions, expense categories, payment methods, 20 produk demo, admin user (password di-hash).

Perubahan breaking: jangan edit migration lama; selalu migration baru dengan `Schema::table` + backfill.

## 5. Data dictionary singkat

| Kolom | Tipe | Contoh | Ringkasan |
|---|---|---|---|
| `products.sku` | VARCHAR(50) UQ | BC-01-R1 | untuk validasi duplikat di CUD |
| `products.cost_price` / `base_price` | DECIMAL(15,2) | 120000 | HPP vs harga jual; laba = base − cost |
| `orders.total` | DECIMAL(15,2) | 512000 | subtotal − discount + shipping + additional |
| `orders.due` | GENERATED | 200000 | total − SUM(success payments) |
| `payments.kind` | ENUM | dp | dp vs pelunasan vs pembayaran penuh — DP tidak boleh dihitung lunas |
| `cash_sessions.closing_balance` | DECIMAL(15,2) | 1050000 | opening + tunai masuk − tunai keluar |

## 6. Aturan immutability

- `payments`, `transactions`, `journal_entry_lines` **tidak boleh dihapus** lewat CRUD biasa. Koreksi hanya via entri kompensasi (reversal) yang mengacu ke transaksi asli.