# NEXERA AFRICA - Repair Changelog

This document explains every file that was changed, why it was changed, and how the change fixes the reported bugs.

---

## 1. ROOT-CAUSE SUMMARY

### Issue 1 — Pi payments expire before they are approved

A Pi Network testnet payment auto-expires ~3 minutes after `Pi.createPayment()` is called client-side, UNLESS the backend calls `POST /payments/{id}/approve` on `api.minepi.com` within that window. Your payments were expiring because **the backend never managed to call /approve**. Three independent defects combined to cause this:

| # | Defect | Where | Effect |
|---|--------|-------|--------|
| 1a | The frontend calls `api/pi_payment.php?action=create` first, but `api/pi_payment.php` had **no `create` action** — only `approve` and `complete`. | `api/pi_payment.php` (old) | The first request returned HTTP 400 with "Invalid action path choice router selection" before the Pi Wallet dialog ever opened. No payment was ever created. |
| 1b | Even if `create` had existed, the `INSERT INTO pi_payments` referenced columns `user_uid` and `amount` that **do not exist** in your `PI_DATABASE_SCHEMA.sql` (which only defines `id, order_id, payment_id, txid, status, metadata, created_at, updated_at`). | `api/pi_payment.php` (old) | Any `INSERT` would throw `SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_uid'`. The payment row was never written; the approval step that depends on it never ran. |
| 1c | The price check used a 2-item hardcoded catalog (`listing_99402 => 0.00314, premium_tier => 3.14159`) and rejected everything else as a "Security mismatch assertion error". | `api/pi_payment.php` (old) | Every real purchase with a different amount was rejected. The `approve` endpoint returned HTTP 400, the Pi payment was never server-approved, and Pi's backend auto-expired it. |

Three JavaScript bugs in `checkout.html` made this even worse:

| # | Bug | Effect |
|---|-----|--------|
| 1d | `constScopes = ['username','payments']` — missing `const` keyword | Implicit global; throws ReferenceError in strict mode and silently breaks `Pi.authenticate()` |
| 1e | `handleCompletion` referenced `btn` (which only existed in `startPayment` scope) | ReferenceError thrown at the end of a successful payment |
| 1f | `updateStatus` checked `if (initBox && id === 'initial-status')` where `id` was undefined | Auth-phase status messages never rendered in the auth box |

### Issue 2 — Database errors / new products cannot be saved

| # | Defect | Where | Effect |
|---|--------|-------|--------|
| 2a | `vendor_dashboard.php` inserted new products using columns `name` and `price`, but every reader (`marketplace.php`, `electronics.php`, `category_manage.php`) selects `title` and `price_pi`. | `vendor_dashboard.php` (old) | Two outcomes were possible, both bad: <br>① If the `products` table on the server happened to have a `name`/`price` column, the INSERT succeeded but the new product was invisible on the marketplace (NULL title/price_pi). <br>② If the server's `products` table did NOT have `name`/`price` columns, the INSERT failed with `SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name'`. This is the error your vendor sees. |
| 2b | No CSRF protection on the vendor upload form. | `vendor_dashboard.php` (old) | A malicious site could POST a product on behalf of any logged-in vendor. |
| 2c | Image uploads trusted `$_FILES['type']` (set by the browser) and used `time() . basename($name)` as the filename. | `vendor_dashboard.php` (old) | Attackers could upload a `.php` file with a spoofed `image/jpeg` MIME type and execute code on the server. |
| 2d | No transaction wrapped the product INSERT + image INSERTs. | `vendor_dashboard.php` (old) | A single failed image insert left an orphan product row (no images, no error message). |
| 2e | DB credentials + Pi API key were hardcoded in 4 different files. | `config/db.php`, `api/pi_payment.php`, `api/pi_handler.php`, `pi_payment_gateway.php` | Credentials leaked into backups, version control, and any error stack trace. |
| 2f | `display_errors=1` was forced in `config/db.php` and `includes/footer.php`. | both | SQL credentials and stack traces were printed directly to the browser on any error. |
| 2g | `pi_payment.php` (old) opened a fresh mysqli connection on every request instead of using the shared `$db` PDO instance. | `api/pi_payment.php` (old) | Wasted a TCP+auth handshake on every call; no connection reuse. |

---

## 2. FILES PRODUCED IN `NEXERA_FIX/`

### `config/db.php` (rewritten)
- **Credentials now come from env first** (`DB_HOST/USER/PASS/NAME/PORT`), with a safe fallback to the previously-hardcoded values so the existing Kamatera deployment keeps working until `.env` is provisioned.
- **`display_errors` is OFF**, `log_errors` is ON. Errors go to `/logs/php_errors.log` instead of the browser.
- **PDO is now configured for production**:
  - `ERRMODE_EXCEPTION` — real exceptions, no silent `false` returns.
  - `EMULATE_PREPARES=false` — native prepared statements, real integer/decimal types.
  - `ATTR_PERSISTENT=true` — connection pool reuse across requests (cuts ~30 ms per request).
  - `MYSQL_ATTR_INIT_COMMAND` — enforces `STRICT_ALL_TABLES` so out-of-range decimals throw instead of silently truncating.
- Session cookie config (`SameSite=None, Secure=true`) is unchanged so existing Pi Browser sessions are NOT invalidated.
- On connection failure we now return HTTP 500 with a generic JSON message instead of leaking the credentials in the page.

### `config/pi.php` (new)
- Single source of truth for `PI_API_KEY`, `PI_API_URL`, `PI_SANDBOX_MODE`, `PI_API_TIMEOUT`, `PI_APPROVAL_WINDOW_SECONDS`.
- Loaded from env (`.env` file). Safe fallback to the previously-hardcoded API key so production keeps working until `.env` is set up.
- All other Pi-related files (`api/pi_payment.php`, `api/pi_handler.php`) now include this instead of hardcoding the key.

### `.env.example` (new)
- Template for the env file. Copy to `.env` at the project root, fill in real values, then remove the fallback blocks in `config/db.php` and `config/pi.php`.

### `api/pi_payment.php` (rewritten end-to-end)
This is the heart of the Pi-payment fix. The new version:

1. **Implements every action the Pi SDK calls**:
   - `create` — pre-creates a payment row in `pi_payments` with the expected amount, BEFORE `Pi.createPayment()` runs. This is the security anchor for the price check on approval.
   - `approve` — verifies the amount with Pi, persists the `paymentId`, and calls Pi's `/payments/{id}/approve` endpoint. **This is the call that stops Pi from expiring the payment.**
   - `complete` — idempotent, calls Pi's `/payments/{id}/complete` with the blockchain txid.
   - `incomplete` — marks a previously-started payment as `interrupted` when the user comes back with a half-finished payment.
   - `cancel` — marks a payment as `cancelled` when the user dismisses the Pi Wallet.
   - `health` — used by `pi-test-admin.php` to verify backend status.
   - `list` — used by `pi-test-admin.php` to render the recent-payments table.
   - `reset_test` — admin-only, clears test data (only allowed in sandbox mode).

2. **Verifies the price server-side** by looking it up in the `products` (or `listings`) table — NOT a hardcoded catalog. This means every real purchase with any amount will now succeed (the old code rejected anything not in the 2-item catalog).

3. **Uses the unified `pi_payments` schema** (see `PI_DATABASE_SCHEMA.sql`). The `INSERT` references only columns that actually exist (`order_id, payment_id, user_uid, amount, txid, status, metadata, mode`).

4. **Is idempotent on `complete`** — calling it twice with the same `paymentId` returns success the second time instead of erroring.

5. **Logs structured errors** to `php_errors.log` with the `pi_payment` tag for debugging.

6. **Uses the shared `$db` PDO instance** (from `config/db.php`) instead of opening a new mysqli connection per request.

### `PI_DATABASE_SCHEMA.sql` (rewritten)
- The `pi_payments` table now has every column the code uses: `order_id, payment_id, user_uid, amount, txid, status, metadata, mode, created_at, updated_at`.
- The `status` enum includes the `interrupted` state (used by the `incomplete` action).
- Added `mode` column (`test`/`mainnet`) so the admin panel can filter testnet vs mainnet payments.
- Also documents the full app schema (users, categories, products, product_images, listings, images, orders) so a fresh install can run this single file.

### `migrate.php` (new — RUN ONCE)
- One-time, idempotent migration script.
- `ALTER`s your existing tables in place so the unified schema works **without losing any data**.
- Specifically:
  - Adds the missing columns to `pi_payments` (`order_id, payment_id, user_uid, amount, txid, metadata, mode, updated_at`).
  - Expands the `status` enum to include `interrupted`.
  - Adds the missing columns to `products` (`title, price_pi, vendor_id, user_id, status, created_at`) so vendor_dashboard.php's INSERT works.
  - Backfills `title` from `name` and `price_pi` from `price` for legacy rows so the marketplace shows them immediately.
  - Ensures `product_images`, `categories`, `listings`, `images`, `orders`, `users` tables exist with the right columns.
- Run it once at `https://nexeraafrica.com/migrate.php` or via CLI: `php migrate.php`.
- Delete the file after migration is confirmed.

### `checkout.html` (fixed)
- **Fixed the `constScopes` bug** → `const scopes = [...]`. Pi.authenticate() now actually runs.
- **Fixed the `btn` scope bug** in `handleCompletion` → `document.getElementById('pay-btn')`.
- **Fixed the `updateStatus` `id` undefined bug**.
- **Added an approval-window countdown** — the user sees "Time remaining: 178s, 177s, ..." while waiting for them to click Approve in the Pi Wallet. This makes the 3-minute Pi expiration visible to the user.
- **Added a `/cancel` callback** so dismissed payments are marked cancelled in the DB (no more orphan "initialized" rows).
- **Better error surfacing** — backend error messages are now displayed to the user.
- Added a `warn` style for non-fatal warnings.

### `payment.php` (fixed)
- Same JS bug fixes as `checkout.html` (button scope, error surfacing, countdown, /cancel callback).
- The listing query now uses `status = 'active'` (was `'approved'` which doesn't exist in the listings.status enum and never matched anything).
- Now uses the project's shared `$db` PDO instance.

### `vendor_dashboard.php` (rewritten)
This is the heart of the database fix. Changes:

1. **Uses the unified column names** (`title, description, price_pi, category_id, vendor_id`) for the INSERT. Also mirrors the values into the legacy `name` and `price` columns so any code that still reads those keeps working.
2. **Wraps the product INSERT + image INSERTs in a transaction** — if any image insert fails, the whole thing rolls back. No more orphan product rows.
3. **Adds CSRF protection** — the form now includes `generate_csrf_token()` and the POST handler validates it.
4. **Validates inputs** — title ≤ 190 chars, price > 0, category > 0, image MIME type verified via `finfo()` (not the browser's claim).
5. **Enforces file size limits** — 5 MB per image, 20 MB total.
6. **Random filenames** via `bin2hex(random_bytes())` — no path-traversal risk, no name collisions.
7. **Marks the FIRST uploaded image as `is_main=1`** so the marketplace "Top Products" carousel shows it.
8. **The inventory list uses `COALESCE`** to fall back from `title` to `name` and from `price_pi` to `price`, so legacy products (inserted before the migration) still render correctly.

### `includes/functions.php` (improved)
- **CSRF tokens now rotate every 30 minutes** instead of being valid for the whole session. (The previous code set the token once per session, which made session-fixation attacks easier.)
- **`sanitize_input` strips ASCII control characters** (0x00-0x1F, 0x7F) to prevent log-injection and string-termination attacks.
- **`upload_images()` now verifies MIME via `finfo()`** and uses random filenames via `bin2hex(random_bytes())`.
- **Added `get_product_by_id()` helper** used by `api/pi_payment.php` for server-side price verification.
- Added a `paginate()` edge-case fix (negative offset guard).

### `api/pi_handler.php` (compatibility shim)
- One-line shim that includes the new `pi_payment.php`. Existing bookmarks / links that point to `pi_handler.php?action=...` keep working transparently.
- Safe to delete once every frontend reference is updated.

---

## 3. DEPLOYMENT STEPS

1. **Back up the existing site**:
   ```bash
   ssh root@your-vps
   cd /var/www/nexera  # or wherever your site root is
   tar czf ~/nexera_backup_$(date +%Y%m%d).tar.gz .
   mysqldump -u root -p nexora_db > ~/nexora_db_$(date +%Y%m%d).sql
   ```

2. **Upload every file from `NEXERA_FIX/`** over the corresponding file in the project root:
   - `config/db.php` → replaces existing
   - `config/pi.php` → new file
   - `.env.example` → new file (rename to `.env` and fill in real values)
   - `api/pi_payment.php` → replaces existing
   - `api/pi_handler.php` → replaces existing (now a shim)
   - `PI_DATABASE_SCHEMA.sql` → replaces existing (reference only)
   - `migrate.php` → new file, run once
   - `checkout.html` → replaces existing
   - `payment.php` → replaces existing
   - `vendor_dashboard.php` → replaces existing
   - `includes/functions.php` → replaces existing

3. **Run the migration**:
   - Web: visit `https://nexeraafrica.com/migrate.php`
   - CLI: `php /var/www/nexera/migrate.php`
   - Verify no `[FAIL]` lines in the output.
   - Delete `migrate.php` from the server.

4. **Verify the backend is healthy**:
   - Visit `https://nexeraafrica.com/api/pi_payment.php?action=health`
   - You should see `{"ok":true,"checks":{"db":"ok","pi_api_key":"set","sandbox_mode":"test",...}}`

5. **Set up `.env`** (recommended):
   ```bash
   cp .env.example .env
   nano .env   # fill in DB_PASS, PI_API_KEY, APP_BASE_URL
   chmod 640 .env
   chown www-data:www-data .env   # or your web server user
   ```
   Then remove the fallback blocks in `config/db.php` and `config/pi.php` (marked with comments).

6. **Test the payment flow**:
   - Open `https://nexeraafrica.com/checkout.html?listing_id=1&amount=0.00314&title=Test` inside Pi Browser.
   - Click "Connect Pi Wallet" → "Complete Payment with Pi".
   - Approve the payment in the Pi Wallet within 3 minutes.
   - Verify the row appears at `https://nexeraafrica.com/pi-test-admin.php` with status `completed`.

7. **Test product upload**:
   - Log in as a vendor, go to `vendor_dashboard.php`.
   - Upload a product with an image.
   - Verify it appears in the marketplace immediately.

---

## 4. WHAT WAS NOT CHANGED (DELIBERATELY)

- `marketplace.php`, `electronics.php`, `apartments.php`, `real_estates.php`, `other_services.php`, `category_manage.php`, `category.php`, `admin_dashboard.php` — these all read from `products` using `title`/`price_pi`, which the migration guarantees exist. No code change needed.
- `create_listing.php`, `view_listing.php`, `edit_listing_admin.php`, `verify_listing.php` — these use the legacy `listings` table. The migration ensures it has all needed columns. No code change needed.
- `pi_payment_gateway.php`, `approve_payment.php`, `complete_payment.php`, `order_success.php` — orphan files that are not called by any frontend. They are NOT deployed in the fix. Delete them from the server if you want a clean tree.
- All session cookie settings (`SameSite=None, Secure=true, HttpOnly=true`) — preserved so existing Pi Browser sessions continue to work.
- The `Pi.init({sandbox: true})` flag — kept as testnet. Flip to `sandbox: false` only when you've QA'd on testnet.

---

## 5. POST-FIX VERIFICATION CHECKLIST

- [ ] `https://nexeraafrica.com/api/pi_payment.php?action=health` returns `{"ok":true,...}`
- [ ] A test payment on `checkout.html` completes with status `completed` in `pi-test-admin.php`.
- [ ] A test payment dismissed in the Pi Wallet shows as `cancelled` (not `initialized`) in the DB.
- [ ] A new product uploaded via `vendor_dashboard.php` shows up immediately on `marketplace.php`.
- [ ] No `Unknown column` errors in `/logs/php_errors.log`.
- [ ] No credentials are visible in browser error pages.
- [ ] `.env` exists and is owned by the web server user with mode `640`.