# 06 DATA MODEL

WooCommerce remains the source of truth for products, orders and customers. Nothing here duplicates it.

Prefix: `_bc_` for meta keys, `bc_` for table name segments and status slugs.

---

## 1. Order statuses

Registered in `BC_Order_Statuses`. Reasoning for the reduction is in decision D-04.

| Slug | Label | Custom | Notes |
|---|---|---|---|
| `wc-pending` | Pending Payment | no | online gateway only, unused while COD-only |
| `wc-bc-verify` | Verification Required | yes | stock not yet reduced |
| `wc-processing` | Confirmed | no, relabelled | stock reduces here |
| `wc-bc-shipped` | Shipped | yes | triggers the shipped email |
| `wc-completed` | Delivered | no, relabelled | the only status counted as revenue |
| `wc-cancelled` | Cancelled | no | requires a reason code |
| `wc-bc-rto` | RTO / Returned | yes | records the courier RTO charge |
| `wc-refunded` | Refunded | no | |

Register the three custom slugs on `init` and label them on `wc_order_statuses`.

**Revenue and paid status, carefully.** On a COD order no money exists until the rider collects it, so:

- Add **only** `wc-completed` to `woocommerce_order_is_paid_statuses`. Do **not** add `wc-bc-shipped`. Treating dispatch as paid contradicts the whole `_bc_cod_status` model and changes `is_paid()` for reporting and gateways.
- `woocommerce_reports_order_statuses` affects only the deprecated legacy reports screens. **WooCommerce Analytics, which the dashboard is cross-checked against, does not read it.** Analytics is governed by the `woocommerce_excluded_report_order_statuses` and `woocommerce_actionable_order_statuses` options. Set those on activation: exclude `wc-cancelled`, `wc-bc-rto`, `wc-failed` and `wc-pending` from reports; make `wc-bc-verify`, `wc-processing` and `wc-bc-shipped` actionable. Without this the custom statuses are silently bucketed wrong and the dashboard will never match Analytics.

Reusing `wc-processing` and `wc-completed` rather than creating parallel statuses keeps WooCommerce's stock handling, paid-status logic and Analytics working without rewiring.

## 2. Order meta

All read and written through the CRUD API. Never `get_post_meta`.

### Verification

| Key | Values |
|---|---|
| `_bc_verification_status` | `not_required` \| `pending` \| `attempted` \| `verified` \| `failed` |
| `_bc_verification_reason` | `high_value` \| `prior_rto` \| `unknown_city` \| `large_qty` \| `short_address` \| `manual` |
| `_bc_verification_channel` | `whatsapp` \| `call` \| `sms` |
| `_bc_verification_at` | datetime |
| `_bc_verification_by` | user ID |
| `_bc_verification_notes` | text |
| `_bc_verification_attempts` | int |

### Fulfilment

| Key | Values |
|---|---|
| `_bc_fulfilment_status` | `pending` \| `sent_to_factory` \| `packed` \| `ready_for_courier` \| `dispatched` |
| `_bc_fulfilment_at` | datetime |

### Courier and delivery

| Key | Values |
|---|---|
| `_bc_courier_slug` | string |
| `_bc_courier_name` | string |
| `_bc_tracking_number` | string |
| `_bc_tracking_url` | url, built from a per-courier template in settings |
| `_bc_shipped_at` | datetime |
| `_bc_delivery_status` | `in_transit` \| `out_for_delivery` \| `delivered` \| `failed_attempt` \| `returning` |
| `_bc_delivered_at` | datetime |
| `_bc_courier_charge` | decimal |
| `_bc_rto_charge` | decimal |
| `_bc_rto_reason` | string |

### COD and settlement

| Key | Values |
|---|---|
| `_bc_cod_amount` | decimal |
| `_bc_cod_status` | `pending` \| `collected` \| `settled` \| `written_off` |
| `_bc_cod_collected_at` | datetime |
| `_bc_settlement_id` | int, foreign key to `bc_settlements` |
| `_bc_settlement_ref` | string, denormalised for display |

### Address and identity

| Key | Values |
|---|---|
| `_bc_customer_phone` | canonical `03XXXXXXXXX`, the customer identity key |
| `_bc_customer_phone_raw` | as typed, kept for audit |
| `_bc_whatsapp_phone` | canonical form, may equal `_bc_customer_phone` |
| `_bc_province` | string |
| `_bc_city` | string |
| `_bc_city_recognised` | bool, false triggers verification |
| `_bc_area` | string |
| `_bc_landmark` | string |

### Payment (dormant until a gateway is added)

| Key | Values |
|---|---|
| `_bc_payment_gateway` | slug |
| `_bc_payment_txn_id` | string, the idempotency key |
| `_bc_payment_status` | `pending` \| `paid` \| `failed` \| `refunded` |
| `_bc_payment_at` | datetime |
| `_bc_payment_amount` | decimal |

### Housekeeping

| Key | Values |
|---|---|
| `_bc_cancel_reason` | `unreachable` \| `customer_cancelled` \| `out_of_stock` \| `fraud_suspected` \| `duplicate` \| `out_of_area` |
| `_bc_order_source` | captured UTM parameters, JSON |
| `_bc_ga_tracked` | bool, guards the purchase event against a thank-you page refresh |
| `_bc_capi_sent` | bool, guards the Conversions API event |
| `_bc_duplicate_flag` | bool, same phone with an open order in the last 24 hours |

## 3. Product meta

| Key | Type |
|---|---|
| `_bc_spec_dimensions` | string, "240 x 260 cm" |
| `_bc_spec_pieces` | int |
| `_bc_spec_includes` | short text, "1 bedsheet, 2 pillow covers" |
| `_bc_spec_care` | text |
| `_bc_spec_thread_count` | string, "180 TC" |
| `_bc_cod_blocked` | bool, blocks COD on this product |
| `_bc_video_url` | url, YouTube or Vimeo only, rendered as a facade embed |

Fabric, Size, Pattern, Season and Set type are global attributes, not meta. See decision D-10.

## 4. Custom tables

Two, both justified in decision D-09.

### `{$wpdb->prefix}bc_order_stats`

A denormalised cache, one row per order, written on every status transition. Rebuildable from orders with `wp bc rebuild-stats`. It is not a source of truth.

| Column | Type | Index |
|---|---|---|
| `order_id` | bigint unsigned | primary |
| `order_date` | datetime | index |
| `status` | varchar(32) | index |
| `phone` | varchar(16) | index |
| `city` | varchar(64) | index |
| `province` | varchar(64) | |
| `gross` | decimal(12,2) | |
| `discount` | decimal(12,2) | |
| `shipping` | decimal(12,2) | |
| `net` | decimal(12,2) | |
| `items` | smallint | |
| `units` | smallint | total quantity across all lines |
| `fulfilment_status` | varchar(24) | index |
| `is_cod` | tinyint | |
| `is_rto` | tinyint | index |
| `cod_status` | varchar(16) | index |
| `settlement_id` | bigint unsigned | index |

Why it exists: the ops dashboard needs daily aggregates, and running those over `wc_orders_meta` on shared MySQL with no object cache is the query that falls over first. It also gives the per-phone order history and RTO count that the verification panel and the COD blocklist need, in one indexed lookup.

### `{$wpdb->prefix}bc_settlements`

| Column | Type |
|---|---|
| `id` | bigint unsigned, primary, auto |
| `courier_slug` | varchar(32) |
| `settlement_date` | date |
| `reference` | varchar(64) |
| `gross_amount` | decimal(12,2) |
| `courier_charges` | decimal(12,2) |
| `net_received` | decimal(12,2) |
| `orders_count` | int |
| `notes` | text |
| `created_by` | bigint unsigned |
| `created_at` | datetime |

Why it exists: a settlement is one courier remittance covering many orders. There is no WooCommerce equivalent, and modelling it as order meta makes reconciliation impossible. Orders link back via `_bc_settlement_id`.

### `{$wpdb->prefix}bc_order_stat_items`

One row per order line, written alongside the parent stats row.

| Column | Type | Index |
|---|---|---|
| `order_id` | bigint unsigned | index |
| `product_id` | bigint unsigned | index |
| `variation_id` | bigint unsigned | |
| `category_id` | bigint unsigned | index |
| `qty` | smallint | |
| `line_net` | decimal(12,2) | |

Why it exists: the dashboard needs top products by units and sales by category, and the brief asks for category sales in section 31. Neither is answerable from the parent table, and computing them live from order line items on shared MySQL is exactly the query that will time out. Rebuilt by the same command as the parent table, so it is equally a cache and equally disposable.

### Rejected tables

- Verification log. Order notes carry the audit trail via `BC_Order_Timeline`.
- Courier event log. Meta plus notes suffice at manual scale.
- Custom customer table. WooCommerce's lookup tables plus the phone key in `bc_order_stats` cover it. The customer view in phase 10 aggregates from `bc_order_stats` grouped by `phone`.

### Rebuilding without WP-CLI

Namecheap shared plans frequently have no SSH and therefore no WP-CLI, so a CLI-only rebuild path is not a rebuild path on the launch host. Every `wp bc` command must also be reachable as a button on the plugin's Tools screen, running the same code through Action Scheduler in batches of 200. The CLI command is the developer convenience; the admin button is the one that has to work in production.

## 5. Options

Stored under one autoloaded option, `bc_settings`, kept small. Never add a large autoloaded option.

```
bc_settings = [
  cod_low_threshold          => 5000,
  cod_high_threshold         => 20000,
  cod_max_value              => 50000,   // above this, COD is unavailable and checkout shows advance payment only
  city_tiers                 => [ 'tier1' => [ cities, shipping_fee, lead_days_min, lead_days_max ], ... ],
  flat_shipping_fee          => 250,
  low_stock_threshold        => 5,
  verify_qty_trigger         => 5,
  verify_min_address_length  => 25,
  rto_block_count            => 2,
  free_shipping_threshold    => 3000,
  delivery_lead_days_min     => 2,
  delivery_lead_days_max     => 4,
  whatsapp_number            => '',
  couriers                   => [ slug => [ name, tracking_url_template, cod_charge, rto_charge ] ],
  ga4_id                     => '',
  meta_pixel_id              => '',
]
```

Schema version lives in a separate non-autoloaded option, `bc_db_version`.

## 6. Roles and capabilities

| Role | Capabilities |
|---|---|
| Administrator | everything |
| Shop Manager | WooCommerce default, plus `bc_verify_orders`, `bc_manage_fulfilment`, `bc_manage_settlements` |
| Fulfilment Staff (future) | `edit_shop_orders` read plus `bc_manage_fulfilment` only, no pricing, no settings, no plugins |

Every custom admin action checks its own capability, never `is_admin()`.