# Buy Local Lowveld — Database Schema (Phase 2a)

MySQL/MariaDB tables introduced in Phase 2a. See `db/schema.sql` for
the literal DDL.

## Table overview

```
           +-----------+
           |  members  |<---+
           +-----------+    |
             /   |   \      |
            /    |    \     |
    listings  invoices  transactions
        |        |            |
        |        +----<-------+
        |                     |
        +---- orders ---- order_items
                              |
                          branding_items
```

- **members** — single source of truth for a person: auth, profile, membership.
- **listings** — the public directory entries; each belongs to a member (or is unclaimed: `member_id IS NULL`).
- **orders / order_items** — a member's cart; once checked out, becomes an invoice.
- **invoices** — billable records (membership renewals + order checkouts).
- **transactions** — the ledger; every charge, payment, refund. Invoices reference transactions.
- **branding_items** — the shop catalogue.
- **password_resets** — short-lived tokens for "forgot password".
- **business_claims** — pending requests from people claiming unclaimed listings.
- **mailchimp_sync_log** — audit trail of every sync event (success / failure).

## Detailed tables

### members
The **one table that matters most.** Every other table points back here.
Acts as auth record (`email`, `password_hash`, `role`), profile (`first_name`,
`last_name`, `business_name`, `industry`, `tier`), and membership state
(`status`, `join_date`, `renewal_date`).

`role` distinguishes `member` from `admin` (Phase 2c uses this).
`status` controls whether the account can sign in and whether the listing
is public:
- `pending` — just signed up, awaiting verification
- `active` — full access
- `suspended` — blocked from signing in
- `cancelled` — kept for historical reporting

### listings
One directory entry per row. `member_id` is nullable so admins can
seed listings that members later claim via `claim-business.php`. The
`slug` is the URL-safe identifier; it must be unique across the whole
table.

`tier` and `featured` on the listing are kept in sync with the
member's `tier` when they upgrade. Storing it on both tables lets us
display the tier badge on a listing without a join.

### orders + order_items
An order is a cart while `status = 'cart'`. Each member has at most one
cart at a time (enforced in application code, not the schema — a unique
index would make recovery from crashes harder). Checkout flips the
status to `pending_payment` and generates an invoice. Phase 2b will
flip it to `paid` when PayFast confirms.

### invoices
Membership invoices are generated at signup (and annually on renewal);
order invoices are generated at cart checkout. `number` follows a
human-readable format: `INV-YYYY-NNNN` or `ORD-YYYY-NNNN`. Store
amounts in **cents** to avoid floating-point surprises.

### transactions
The ledger. Every invoice spawns at least one transaction (the
charge). A payment matching that charge is a second transaction with
negative `amount_cents`. Running balance = sum of `amount_cents` for
a given `member_id`.

### password_resets
Stores the **SHA-256 hash** of the raw token — we email the raw token
as a URL parameter but never store it directly. Once used, `used_at`
is set so the token can't be replayed. Tokens expire after 2 hours.

### mailchimp_sync_log
Every call to `mc_sync_member_from_db()` writes a row here. Useful for
answering "did X's profile change actually reach Mailchimp?" during QA.

## Amount storage: cents, not rands

All monetary fields (`amount_cents`, `price_cents`, `subtotal_cents`,
`total_cents`, `line_total_cents`, `unit_price_cents`) are INT
representing cents. Never store currency as floats — floating-point
arithmetic produces visible rounding errors in statements and
reconciliations. Use `cents / 100` only when rendering for display.

## How to rebuild from scratch

```bash
mysql -u root -p
mysql> CREATE DATABASE buylocal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> CREATE USER 'buylocal'@'localhost' IDENTIFIED BY 'your-password';
mysql> GRANT ALL ON buylocal.* TO 'buylocal'@'localhost';
mysql> \q

# Update includes/config.php with DB_USER, DB_PASS, DB_NAME
# then apply the schema:
mysql -u buylocal -p buylocal < db/schema.sql

# Seed the Phase 1 dummy data + demo accounts:
php db/seed.php
```

The seed script creates:
- An admin at `admin@buylocallowveld.co.za` / `admin1234`
- Ten demo members matching the Phase 1 listings, all with password `demo1234`
- A paid annual-membership invoice for each

**Change those passwords before going live.**

## Indexes + foreign keys

Every foreign key has ON DELETE behaviour chosen with care:

| FK | ON DELETE | Reason |
|----|-----------|--------|
| `listings.member_id → members.id` | SET NULL | Keep the listing; mark unclaimed |
| `orders.member_id → members.id` | CASCADE | Gone member, gone carts |
| `order_items.order_id → orders.id` | CASCADE | Line items follow their order |
| `order_items.branding_item_id → branding_items.id` | RESTRICT | Can't delete an item still referenced by past orders |
| `invoices.member_id → members.id` | CASCADE | |
| `invoices.order_id → orders.id` | SET NULL | Historical invoices survive an order purge |
| `transactions.member_id → members.id` | CASCADE | |
| `transactions.invoice_id → invoices.id` | SET NULL | Keep the ledger entry; detach the invoice |
| `password_resets.member_id → members.id` | CASCADE | |
| `business_claims.listing_id → listings.id` | CASCADE | |

## What's not in Phase 2a

Deferred to later phases:

- `payments` table + PayFast tokens (Phase 2b)
- Subscription schedules for annual auto-renewal (Phase 2b)
- Admin-side audit log (`admin_events` table) (Phase 2c)
- CMS tables for home-page content blocks (out of scope)