# SQL upgrade scripts (phpMyAdmin / cPanel)

## Quick path — one file (recommended)

Use **[`99-full-schema-sync.sql`](./99-full-schema-sync.sql)** to sync production with the current app schema in a single import:

1. Full database backup
2. phpMyAdmin → SQL → set **Delimiter** to `$$`
3. Paste entire `99-full-schema-sync.sql` → Go
4. Set Delimiter back to `;`
5. Owner login once (seeds roles), re-run **Section 6** backfill if needed

Safe to re-run. **Does not delete** existing rows or drop tables/columns.

---

## Step-by-step path

Run in numeric order. Full instructions: [`../PRODUCTION_UPGRADE_RUNBOOK.md`](../PRODUCTION_UPGRADE_RUNBOOK.md).

## cPanel setup (required once)

1. Open phpMyAdmin → your database → **SQL** tab.
2. Set the **Delimiter** field (below the SQL box) to: `$$`
3. Paste and run [`00b-cpanel-helpers.sql`](./00b-cpanel-helpers.sql)
4. Set Delimiter back to `;` (semicolon)

This installs `cp_safe_exec` so foreign keys and duplicate alters do **not** stop the script.

All scripts avoid `information_schema` and do not use `PREPARE` in the main files.

| File | When |
|------|------|
| `00-preflight-checks.sql` | Before anything — save results |
| `00b-cpanel-helpers.sql` | **Once** — install safe helper procedure |
| `01-baseline-migrations.sql` | Before schema changes |
| `02-access-control-schema.sql` | Creates access control tables |
| `03-member-access-role.sql` | Adds `members.access_role_id` |
| *(seed roles via app login or `npm run seed:access-roles`)* | Required before backfill |
| `04-backfill-member-roles.sql` | After roles exist |
| `05-permission-scope-updates.sql` | After roles exist |
| `06-post-migration-verify.sql` | Verify access-control upgrade |
| `07-work-schedule-timezone.sql` | Adds `working_schedule.timezone` |
| `08-notice-members.sql` | Creates `_noticemembers` join table |
| `09-leave-document.sql` | Adds `leaves.document` JSON column |
| `10-attendance-checkout-note.sql` | Adds `attendances.checkout_note` |
| `11-attendance-requests.sql` | Creates `attendance_requests` table + FKs |
| `12-attendance-request-permissions.sql` | After roles exist — request permissions |
| `13-work-schedule-correction-limit.sql` | Adds `monthly_correction_limit` |
| `15-leave-type-day-off.sql` | Adds `day_off` to `leaves.leave_type` enum |
| `14-post-schema-verify.sql` | Final checks |

All scripts are **additive** and **safe to re-run**.

## Already on access-control schema?

Run **00b** (if not done), then **07 → 15** (or **14** verify only if **15** already applied).

## Fresh legacy upgrade

Run **00 → 15** in order (with role seeding between 03 and 04).

## phpMyAdmin tips

- Paste **one file at a time** in the SQL tab (delimiter `;` except for `00b`).
- If `cp_safe_exec` is missing, scripts 02/03/08/11 will error — run `00b` first.
- `ADD COLUMN IF NOT EXISTS` requires MariaDB — **not supported on MySQL (cPanel)**. Column scripts use plain `ALTER TABLE ... ADD`.
- If you get **#1060 Duplicate column name**, the column already exists — run only the `INSERT INTO migrations` lines from that file.
- Foreign-key scripts (02, 03, 08, 11) use `cp_safe_exec` — run `00b` first.
