# Supabase → cPanel MySQL Migration Guide

This is **Phase 1** (backend only). The frontend still uses the Supabase client and will be migrated in Phase 2 once this backend runs cleanly on cPanel.

## What was generated for you

```
backend/
├── .env.example                ← copy to .env, replace values for prod
├── .env                        ← contains your cPanel DB URL (NOT for git)
├── package.json
├── prisma/
│   ├── schema.prisma           ← Prisma models (MySQL)
│   └── seed.js                 ← creates first admin user
├── sql/
│   └── pryojon_mysql_schema.sql  ← raw MySQL DDL (run via phpMyAdmin if you prefer)
└── src/
    ├── server.js               ← Express app, mounts all routes
    ├── lib/                    ← prisma client, auth, mailer, codes
    └── routes/                 ← REST endpoints (auth, services, sellers, …)
```

## Two ways to create the database tables

### Option A — Prisma (recommended)

From your local machine, with the backend `.env` pointing at your cPanel MySQL:

```bash
cd backend
npm install
npx prisma generate
npx prisma migrate deploy     # or:  npx prisma db push
node prisma/seed.js
```

Prisma will create every table in `produc16_test_proyojon` automatically.

### Option B — Raw SQL (phpMyAdmin)

If you don't want to expose your DB port from your laptop, import the SQL directly:

1. cPanel → **phpMyAdmin** → select `produc16_test_proyojon`.
2. Tab **Import** → choose `backend/sql/pryojon_mysql_schema.sql` → **Go**.
3. After import, still run `npx prisma generate` locally so Prisma Client is built.

## Step-by-step deployment to cPanel

1. **Create the DB user** (already done — credentials in `.env`).
2. **Open the port to your dev machine** (skip if using Option B):
   cPanel → **Remote MySQL** → add your laptop's IP.
3. **Create database tables** with Option A or B above.
4. **Set up the Node.js app**:
   - cPanel → **Setup Node.js App** → Create application.
   - Node version: 18 or higher.
   - Application mode: **Production**.
   - Application root: `api` (or your subdir; upload `backend/` contents there).
   - Application URL: e.g. `api.your-domain.com`.
   - Application startup file: `src/server.js`.
5. **Upload files**: zip the `backend/` folder → cPanel **File Manager** → upload → extract into the application root. Do **not** include `node_modules`.
6. **Set environment variables** in the Node.js App UI (mirror `.env.example`). Critical ones:
   - `DATABASE_URL` — same as `.env` (URL-encoded password).
   - `JWT_SECRET` — generate with `openssl rand -base64 48`.
   - `CORS_ORIGIN` — your frontend domain(s), comma-separated.
   - `PUBLIC_BASE_URL` — e.g. `https://api.your-domain.com`.
   - SMTP_* — leave dummy for now (mailer logs to console when host = `smtp.example.com`).
7. **Install dependencies**:
   - In the Node.js App UI click **Run NPM Install**, OR via terminal:
     ```bash
     source /home/<cpanel-user>/nodevenv/api/18/bin/activate
     cd ~/api
     npm install --production
     npx prisma generate
     ```
8. **Restart the app** from the Node.js App UI.
9. **Verify**: visit `https://api.your-domain.com/api/health` — expect `{"ok":true,...}`.
10. **Create the first admin**: `node prisma/seed.js` (or hit `POST /api/auth/signup` then `POST /api/user-roles/claim-first-admin`).

## Migrating existing Supabase data (optional)

If you have live Supabase data to bring over:

1. In Supabase Studio → **Database → Backups** (or `pg_dump`) export your tables as CSV: `profiles`, `user_roles`, `sellers`, `seller_documents`, `categories`, `districts`, `thanas`, `packages`, `category_packages`, `category_pricing`, `services`, `service_images`, `payments`, `reviews`, `banners`, `site_pages`.
2. For `auth.users`, export `id`, `email`, `encrypted_password`, `created_at`. Supabase stores bcrypt hashes that are compatible with `bcryptjs` — you can insert them straight into `auth_users.password_hash` so existing users keep their passwords.
3. Open phpMyAdmin → select a table → **Import** → CSV with column mapping. Order matters because of foreign keys: `auth_users → profiles → user_roles → packages → sellers → categories → districts → thanas → category_pricing/category_packages → services → service_images → payments → reviews → banners → site_pages`.
4. Storage files (`seller-docs`, `service-images`, `category-images`, `banners` buckets): use the helper at `scripts/download-storage.mjs` (already in the repo) to pull them locally, then upload to `backend/uploads/<bucket>/` on cPanel. The `file_path` columns already store the relative path, so the frontend just needs `VITE_API_BASE_URL` pointed at the new host once Phase 2 is done.

## Differences vs Supabase to be aware of

| Supabase                                    | New backend                                                    |
| ------------------------------------------- | -------------------------------------------------------------- |
| Row Level Security (RLS)                    | Express middleware (`requireAuth`, `requireRole`)              |
| `auth.users` + magic links                  | `auth_users` table + JWT login/signup                          |
| Storage buckets                             | Local disk under `backend/uploads/`, served at `/uploads/...`  |
| Postgres enums                              | `VARCHAR` + `CHECK` constraints (values unchanged)             |
| Triggers (codes, ratings, expiry, profiles) | App-level code in `src/lib/codes.js`, routes, `auth/signup`    |
| `gen_random_uuid()`                         | Prisma `@default(uuid())`                                      |
| `timestamptz`                               | MySQL `DATETIME(3)` (UTC). Convert on read if you need TZ data |

## Phase 2 preview (frontend migration — not done yet)

When you're ready, the frontend changes will be:

1. Add `VITE_API_BASE_URL` to `.env`.
2. Replace `src/integrations/supabase/client.ts` with a thin `fetch` wrapper that:
   - Reads JWT from `localStorage`.
   - Maps Supabase calls like `from('services').select(...)` → `GET /api/services?...`.
   - Maps `auth.signInWithPassword` → `POST /api/auth/login`.
3. Keep `types.ts` field names unchanged — every endpoint returns `snake_case` matching the current schema.
4. Delete the `supabase/` folder + `@supabase/supabase-js` dependency.

The UI stays **completely** unchanged.