# 02 — Database Schema (master index)

Conventions confirmed from existing migrations (HR/Sales/Finance) and applied throughout:
`$table->foreignId('x_id')->constrained('table')->nullOnDelete()` (or `->restrictOnDelete()` for
FKs that must not dangle), `created_by`/`updated_by` → `foreignId(...)->nullable()->constrained('admins')->nullOnDelete()`,
**no `SoftDeletes` by default** (only master/reference tables get it, added the way the ERP already
does via a dedicated later migration — see `2026_05_28_000004_add_soft_deletes_to_master_tables.php`
for the precedent), module-prefixed table names (`clinic_...`) matching the `hr_`/`finance_` convention.

## Reused as-is (no migration needed)

| Table | Model | Note |
|---|---|---|
| `branches` | `Core\Branch` | A branch "is a clinic" once it has a `clinic_profiles` row. No schema change. |
| `users` | `User` | Existing site customers become Patients via a `clinic_patient_profiles` row. No schema change. |
| `admins` | `Admin` | See "Altered" below. |
| `notifications` | (Laravel native) | Reused via `NotificationService`. |

## Altered

| Migration (new file) | Table | Change |
|---|---|---|
| `..._add_branch_id_to_admins_table.php` | `admins` | `$table->foreignId('branch_id')->nullable()->after('id')->constrained('branches')->nullOnDelete();` — `null` = Super Admin (unrestricted), set = Clinic Admin/Receptionist scoped to that branch. |

## New tables (build order — respects FK dependencies)

| # | Migration filename (pattern) | Table | Purpose |
|---|---|---|---|
| 1 | `..._create_clinic_profiles_table.php` | `clinic_profiles` | 1:1 extension of `branches` with clinic-only data |
| 2 | `..._create_clinic_specialties_table.php` | `clinic_specialties` | Global specialty list |
| 3 | `..._create_clinic_doctors_table.php` | `clinic_doctors` | Doctor accounts (guard `doctor`) |
| 4 | `..._create_clinic_doctor_branch_table.php` | `clinic_doctor_branch` | Pivot: doctor × branch, price + slot duration |
| 5 | `..._create_clinic_doctor_schedules_table.php` | `clinic_doctor_schedules` | Weekly recurring slots per doctor-branch pair |
| 6 | `..._create_clinic_patient_profiles_table.php` | `clinic_patient_profiles` | 1:1 extension of `users` with medical history |
| 7 | `..._create_clinic_bookings_table.php` | `clinic_bookings` | The booking itself |
| 8 | `..._create_clinic_payments_table.php` | `clinic_payments` | 1:1 with a booking, payment status |
| 9 | `..._create_clinic_medical_records_table.php` | `clinic_medical_records` | 1:1 with a booking, diagnosis/prescription/report |
| 10 | `..._create_clinic_reviews_table.php` | `clinic_reviews` | 1:1 with a booking, two-part rating |
| 11 | `..._create_clinic_chat_messages_table.php` | `clinic_chat_messages` | Doctor↔Patient messages, independent of bookings |
| 12 | `..._create_clinic_offers_table.php` | `clinic_offers` | Platform-wide or branch-local promotions |

Exact column-by-column definitions for every table above are in the matching file under
`models/` (e.g. `clinic_bookings` columns are defined in `models/Booking.md`), so they're not
duplicated here — this file is the index and the dependency order.

## Why `clinic_profiles` instead of a `clinic_specialties`-style column on `branches` directly

`branches` is a shared, company-wide table also used outside this module (any org branch, not
just medical ones). Adding clinic-only columns (logo, working hours, commission %, avg rating)
directly onto `branches` would pollute a generic table other modules depend on. A 1:1 extension
table keeps `branches` untouched and makes "this branch is a clinic" an explicit, queryable fact
(`Branch::whereHas('clinicProfile')`).

## Why a separate `clinic_doctor_schedules` table instead of JSON on the pivot

`clinic_doctor_branch` holds the *commercial* facts of one doctor-branch pairing (price, slot
duration, active flag) — one row per pairing. `clinic_doctor_schedules` holds the *recurring
weekly availability* (day of week, start time, end time) — potentially multiple rows per pairing
(e.g. Sunday 10–2 and Tuesday 4–8 are two rows). Keeping them separate makes slot-generation
queries (`BookingService`) simple `WHERE` clauses instead of JSON parsing.