# Database

MySQL 8 in production; SQLite for local dev and tests (migrations are portable; the FULLTEXT index is MySQL-only and guarded).

## Conventions

- **Money**: `unsignedBigInteger` kobo. Never floats. Percentages are basis points (15% = `1500`).
- **Statuses/types**: `string` columns cast to PHP backed enums (`app/Enums`).
- **Soft deletes** on catalogue and user-facing records so order history keeps its references.
- **Append-only** tables have no `updated_at`: `inventory_movements`, `price_histories`, `audit_logs`.

## Phase 1 tables

| Table | Purpose |
|---|---|
| `users` | customers, creators, staff, admins (`role`, `status`) |
| `addresses` | Nigerian delivery/billing addresses (`state` validated against the 36 states + FCT) |
| `settings` | admin overrides for `config/beauthentic.php` (dotted keys, JSON values) |
| `audit_logs` | who/what/when/old/new/IP for sensitive admin actions |
| `colours` | hair colours + accent tokens (`hex`, `ink_hex`, `soft_hex`, `on_hex`) |
| `categories`, `collections`, `collection_product` | merchandising |
| `products` | type (`simple`/`bundle`/`limited`), status, SEO, denormalised `sales_count`, rating |
| `product_variants` | SKU, colour, `price`/`sale_price` (kobo), low-stock threshold, AI reference |
| `bundle_items` | bundle variant → component variants |
| `product_images` | disk + path, dimensions, order |
| `product_attributes` | `spec` rows and long-form `section` rows (how to apply, …) |
| `inventory` | one row per stock-keeping variant: `on_hand`, `reserved` |
| `inventory_movements` | stock ledger: type, signed quantity, `on_hand_after`, reference, user, notes |
| `price_rules` | scheduled sales / campaign prices targeting a variant, product or colour |
| `price_histories` | every change to `price` / `sale_price` |
| `coupons` | fixed / percent / free-shipping codes with limits and conditions |
| `pages` | Markdown content pages (about, shipping, returns…) |

## Phase 2 tables

| Table | Purpose |
|---|---|
| `carts`, `cart_items` | guest (cookie token) and account carts; optional coupon |
| `shipping_methods` | delivery options: fee (kobo), states served, ETA days, free-delivery eligibility |
| `order_sequences` | per-day counter behind `BA-YYYYMMDD-0001` (row-locked) |
| `orders` | totals in kobo, status + payment status, `checkout_token` (idempotency), stock flags, reservation expiry, tracking, timestamps |
| `order_items` | immutable snapshot: names, SKU, colour, unit/base price, price rule, discount share, bundle contents |
| `order_addresses` | delivery address snapshot |
| `order_status_histories` | append-only timeline: status, payment, system, notes (customer-visible flag) |
| `payments` | one row per Paystack attempt (unique reference), status, channel, refunded amount |
| `refunds` | refunds issued, Paystack id, restocked flag, who |
| `coupon_redemptions` | coupon use per order/email (per-customer limits) |
| `webhook_calls` | every inbound webhook, unique `(provider, idempotency_key)` |

## Phase 3 tables

| Table | Purpose |
|---|---|
| `point_accounts` | cached balance, lifetime earned/spent, tier — one per customer |
| `point_transactions` | append-only ledger: type, source, signed points, `balance_after`, `remaining` (FIFO/expiry), `idempotency_key`, expiry, reference |
| `rewards` | redeemable catalogue (type, cost, value, product, limits, tier, window) |
| `reward_redemptions` | issued codes: points spent, coupon, status (issued/used/expired), order |
| `referral_visits` | every `/ref/{code}` click (session, IP) |
| `referrals` | referrer → friend (unique email), status, qualifying order, friend coupon, points paid |
| `quizzes`, `quiz_questions`, `quiz_answers`, `quiz_answer_scores`, `quiz_outcomes`, `quiz_submissions` | data-driven quiz with per-colour weights and results |
| `personal_access_tokens` | Sanctum API tokens |

Also: `users.referral_code`, `orders.points_earned`, `coupons.reward_redemption_id`.

## Phase 4 tables

| Table | Purpose |
|---|---|
| `ai_generations` | one preview: uuid, board, owner (user or session), variant/colour, provider/model, status, private input/output paths, prompt, error, cost (kobo), latency, share token, consent/saved/expiry timestamps, IP |
| `ai_events` | funnel events per generation (and order for `ai_purchase`, with `value_kobo`); unique per event + generation + order |

Also: `orders.attribution.ai_generation_ids`.

## Phase 5 tables

| Table | Purpose |
|---|---|
| `challenges` | slug, name, brief, hashtag, prize, entry requirements, required product (+ purchase gate), colour theme, start/end, voting + close time, entries per customer, winner bonus, winner post, published |
| `challenge_participants` | who joined (unique per challenge + customer) |
| `ugc_posts` | a Wall post / challenge entry: owner, product/variant/colour, `challenge_id`, `creator_id` (Phase 6), private before/after paths, caption, handle, status, rejection reason, `votes_count`, consent/approval/feature/moderation timestamps |
| `ugc_votes` | one vote per customer per entry (unique), with `challenge_id` for counting |

A challenge entry is simply a `ugc_posts` row with `challenge_id`, so it shares moderation, privacy and points rules with every other post.

## Phase 6 tables

| Table | Purpose |
|---|---|
| `creator_applications` | handles, audience band, city, pitch, status, reviewer, rejection reason |
| `creator_profiles` | one per creator: code, slug, display name, bio, socials, commission override (bp), status, public flag, payout bank / **encrypted** account number / name |
| `creator_links` | trackable `/go/{token}` links to internal pages, click counter |
| `creator_clicks` | every counted click: creator, link, session, account, HMAC-hashed IP, user agent, referrer host |
| `creator_campaigns` | briefs with dates, optional product and boosted commission (bp) |
| `creator_conversions` | **unique per order**: creator, click, source (code/link), order total, commissionable amount |
| `creator_commissions` | ledger: `sale` and negative `clawback` lines; rate, base, original/current amount, status (pending → approved → paid / reversed), hold-until date, payout |
| `creator_payouts` | amount, status (pending/paid/cancelled), transfer reference, who created/paid |

Also: `coupons.creator_profile_id` (a creator code is a coupon), `orders.attribution.creator_click_id / creator_id / creator_source`.

## Phase 7 tables

| Table | Purpose |
|---|---|
| `campaigns` | name, trigger, conditions JSON `[{field, op, value}]` (money in kobo), actions JSON `[{type, params}]`, active, window, runs per customer, counters |
| `campaign_runs` | one per campaign × event × customer — **unique `idempotency_key`**; status, per-action results, error |
| `campaign_members` | campaign audiences (add_to_campaign action / member_of condition) |
| `customer_tags` | `(user_id, tag)` unique; source admin / campaign / crm |
| `customer_messages` | on-site messages from campaigns; expiry, dismissed |
| `outbox_messages` | CRM events and outbound webhooks: channel, destination, event, payload, **unique idempotency key**, status, attempts, next attempt, last error |
| `email_log` | recipient, type, sent/failed, error — never content |

Plus Laravel's `sessions`, `cache`, `jobs`, `failed_jobs`, `password_reset_tokens`.

## Order money maths

```
total = subtotal − discount + shipping (+ tax, currently 0)
revenue (dashboard) = Σ (total − refunded_total) of paid orders, by paid_at
```

## Stock maths

```
available(variant) = on_hand − reserved
available(bundle)  = min( available(component) ÷ qty_in_bundle )
```

## Seeding

`php artisan db:seed` creates the admin, five colours, five single-colour products, three bundles, the Pink Week scheduled sale (Oct 10–15, ₦12,000 → ₦10,500), a welcome coupon and content pages. The catalogue seeder never overwrites existing products.
