# Database

MySQL 8, InnoDB, `utf8mb4`. Conventions:
- Money = `unsigned bigint` **cents**. Weight = `decimal(6,2)` grams.
- Status/type columns store backed-enum values (varchar), constrained in app.
- Every table has `id` (bigint PK) and `created_at`/`updated_at` unless noted.
- FKs enforced; `onDelete` chosen per relation (restrict by default; cascade only for
  owned children like variations/images/items).
- Translatable text columns hold locale-keyed JSON (`{"ms":"…","en":"…"}`) via
  spatie/laravel-translatable — marked *(i18n)* below.
- Laravel standard tables (users, password_reset_tokens, sessions, jobs, cache,
  failed_jobs, personal_access_tokens) exist as usual and aren't re-listed.

---

## Pricing

### gold_prices  (append-only)
| Column | Type | Notes |
|---|---|---|
| purity | varchar | enum Purity (916/375) |
| sell_per_gram | bigint | cents; used for retail pricing |
| buy_per_gram | bigint | cents; used for trade-in |
| effective_from | datetime | current = latest ≤ now() per purity |
| created_by | bigint FK users | who set it |

Index: `(purity, effective_from)`. Never UPDATE/DELETE — new price = new row.

---

## Catalog

### categories
| Column | Type | Notes |
|---|---|---|
| parent_id | bigint FK categories nullable | adjacency list |
| name | json | *(i18n)* |
| slug | varchar unique | |
| position | int | ordering |
| is_active | bool | |

Index: `parent_id`, `slug`.

### products
| Column | Type | Notes |
|---|---|---|
| name | json | *(i18n)* |
| slug | varchar unique | |
| description | json nullable | *(i18n)* |
| purity | varchar | enum Purity |
| type | varchar | enum ProductType (simple/variable) |
| workmanship_type | varchar | enum WorkmanshipType (per_gram/flat) |
| workmanship_amount | bigint | cents (per gram, or flat) |
| price_override | bigint nullable | cents; product-level override (simple products) |
| is_active | bool | |
| published_at | datetime nullable | |

Index: `slug`, `is_active`, `purity`. `price_override`/`workmanship_*` are admin-only (guarded).

### product_variations
| Column | Type | Notes |
|---|---|---|
| product_id | bigint FK products (cascade) | |
| weight_grams | decimal(6,2) | variation key ("Spesifikasi") |
| sku | varchar unique nullable | |
| price_override | bigint nullable | cents; bypasses computed price |
| stock | int nullable | null = made-to-order |
| image_id | bigint FK product_images nullable | |
| position | int | |

Index: `product_id`, `sku`.

### product_images
| Column | Type | Notes |
|---|---|---|
| product_id | bigint FK products (cascade) | |
| disk | varchar | e.g. public |
| path | varchar | randomised filename |
| alt | json nullable | *(i18n)* |
| position | int | |
| is_primary | bool | |

Index: `product_id`.

### category_product  (pivot)
`category_id` FK, `product_id` FK, PK `(category_id, product_id)`.

---

## Cart & Orders

### carts
| Column | Type | Notes |
|---|---|---|
| customer_id | bigint FK users nullable | |
| session_token | varchar nullable | guest carts |

Index: `customer_id`, `session_token`.

### cart_items
| Column | Type | Notes |
|---|---|---|
| cart_id | bigint FK carts (cascade) | |
| product_variation_id | bigint FK product_variations | |
| quantity | int | |

Unique: `(cart_id, product_variation_id)`. Price computed live, never stored.

### orders
| Column | Type | Notes |
|---|---|---|
| number | varchar unique | e.g. AML-2026-000123 |
| customer_id | bigint FK users nullable | null = guest |
| status | varchar | enum OrderStatus |
| payment_status | varchar | enum PaymentStatus |
| subtotal | bigint | cents |
| shipping_total | bigint | cents |
| discount_total | bigint | cents (store credit applied) |
| grand_total | bigint | cents |
| shipping_zone | varchar | enum ShippingZone |
| ship_name / ship_phone / ship_line1 / ship_line2 / ship_city / ship_postcode / ship_state | varchar | address snapshot |
| email | varchar | contact (guest or customer) |
| courier | varchar nullable | |
| tracking_number | varchar nullable | |
| affiliate_id | bigint FK affiliates nullable | attribution |
| store_credit_id | bigint FK store_credits nullable | applied credit |
| payment_reference | varchar nullable | gateway ref |
| paid_at | datetime nullable | |

Index: `number`, `customer_id`, `status`, `payment_reference`.

### order_items  (immutable snapshot)
| Column | Type | Notes |
|---|---|---|
| order_id | bigint FK orders (cascade) | |
| product_id | bigint FK products nullable | |
| product_variation_id | bigint FK product_variations nullable | |
| name | varchar | snapshot |
| weight_grams | decimal(6,2) | snapshot |
| purity | varchar | snapshot |
| gold_price_per_gram | bigint | cents, snapshot |
| workmanship | bigint | cents, snapshot |
| unit_price | bigint | cents, snapshot |
| quantity | int | |
| line_total | bigint | cents |
| gold_price_id | bigint FK gold_prices | audit link |

Index: `order_id`.

---

## Store credit / e-voucher

### store_credits
| Column | Type | Notes |
|---|---|---|
| customer_id | bigint FK users | owner |
| code | varchar unique | random, non-sequential |
| type | varchar | enum StoreCreditType |
| initial_amount | bigint | cents |
| balance | bigint | cents |
| expires_at | datetime nullable | |
| status | varchar | enum StoreCreditStatus |

Index: `customer_id`, `code`, `status`.

### store_credit_transactions  (ledger)
| Column | Type | Notes |
|---|---|---|
| store_credit_id | bigint FK store_credits (cascade) | |
| order_id | bigint FK orders nullable | |
| amount | bigint | signed cents (+ issue / − redeem) |
| type | varchar | enum LedgerType |
| created_by | bigint FK users nullable | |

Index: `store_credit_id`.

---

## Trade-in

### trade_in_requests
| Column | Type | Notes |
|---|---|---|
| customer_id | bigint FK users | |
| status | varchar | enum TradeInStatus |
| total_estimated | bigint | cents |
| total_final | bigint nullable | cents |
| resulting_store_credit_id | bigint FK store_credits nullable | |
| admin_notes | text nullable | |
| reviewed_by | bigint FK users nullable | |

Index: `customer_id`, `status`.

### trade_in_items
| Column | Type | Notes |
|---|---|---|
| trade_in_request_id | bigint FK trade_in_requests (cascade) | |
| purity | varchar | enum Purity |
| weight_grams | decimal(6,2) | |
| estimated_value | bigint | cents |
| final_value | bigint nullable | cents |

Index: `trade_in_request_id`.

---

## Affiliate

### affiliates
| Column | Type | Notes |
|---|---|---|
| user_id | bigint FK users unique | |
| code | varchar unique | referral code |
| commission_rate | decimal(5,2) | percent |
| status | varchar | pending/active/suspended |
| payout_method | varchar nullable | |
| payout_details | json nullable | |

Index: `code`, `status`.

### affiliate_referrals
| Column | Type | Notes |
|---|---|---|
| affiliate_id | bigint FK affiliates | |
| order_id | bigint FK orders unique | one commission per order |
| commission_amount | bigint | cents |
| status | varchar | enum ReferralStatus |
| approved_at / paid_at | datetime nullable | |

Index: `affiliate_id`, `status`.

---

## Reviews

### reviews
| Column | Type | Notes |
|---|---|---|
| product_id | bigint FK products (cascade) | |
| customer_id | bigint FK users | |
| order_id | bigint FK orders nullable | non-null = verified purchase |
| rating | tinyint | 1–5 |
| body | text | escaped on output |
| status | varchar | enum ReviewStatus |

Unique: `(product_id, customer_id)`. Index: `product_id`, `status`.

---

## Content

### cms_pages
| Column | Type | Notes |
|---|---|---|
| slug | varchar unique | about, terms, refund_returns, … |
| title | json | *(i18n)* |
| body | json | *(i18n)* HTML (sanitised) |
| is_published | bool | |

Index: `slug`.

### audit_logs
| Column | Type | Notes |
|---|---|---|
| user_id | bigint FK users nullable | actor |
| action | varchar | e.g. order.status_changed |
| subject_type / subject_id | morphs | affected record |
| properties | json nullable | before/after, context |
| ip | varchar nullable | |

Index: `(subject_type, subject_id)`, `user_id`, `action`.

---

## Shipping
Free ≥ RM300; else per-zone flat rate (Peninsular vs East Malaysia). Held in
`config/shipping.php` (threshold + zone rates + MY state→zone map) rather than a
table, since values are few and rarely change.

## Relationships (summary)
- product 1─* product_variations, 1─* product_images, *─* categories, 1─* reviews.
- category self 1─* (parent/children).
- gold_prices 1─* order_items (audit).
- user 1─* orders, 1─* store_credits, 1─* trade_in_requests, 1─1 affiliate.
- order 1─* order_items, 1─0..1 store_credit (applied), 0..1─1 affiliate_referral.
- store_credit 1─* store_credit_transactions.
- trade_in_request 1─* trade_in_items, 0..1─ store_credit (resulting).

## Migration order
users/auth → gold_prices → categories → products → product_images →
product_variations → category_product → carts → cart_items → store_credits →
store_credit_transactions → affiliates → orders → order_items →
affiliate_referrals → trade_in_requests → trade_in_items → reviews → cms_pages →
audit_logs.
