# SQL Compatibility Checklist (Doctor + Appointments APIs)

Use this checklist before deploying the updated APIs.

## 1) Required Tables

- [ ] `hospital_doctors` table exists
- [ ] `appointments` table exists
- [ ] `availability` table exists
- [ ] `user` table exists
- [ ] `notifications` table exists
- [ ] `doctor_answer` table exists (used by doctor profile/availability API)

## 2) `hospital_doctors` Must-Have Columns

- [ ] `doctor_id` (primary key)
- [ ] `hospital_id`
- [ ] `doctor_name`
- [ ] `doctor_email`
- [ ] `doctor_timezone` (nullable allowed)
- [ ] `doctor_country` (nullable allowed)
- [ ] `role`
- [ ] `specialization` (nullable allowed)
- [ ] `doctor_password`
- [ ] `google_2fa_secret` (nullable allowed)
- [ ] `is_2fa_enabled`
- [ ] `status`
- [ ] `created_at`

### Optional But Recommended for Existing Referral API

- [ ] `doctor_code` OR `referral_code` (at least one)

## 3) `appointments` Must-Have Columns

- [ ] `appointment_id` (primary key)
- [ ] `hospital_id`
- [ ] `patient_id`
- [ ] `doctor_id`
- [ ] `appointment_date`
- [ ] `appointment_start_time`
- [ ] `appointment_end_time`
- [ ] `appointment_time`
- [ ] `reason` (nullable allowed)
- [ ] `status` (default suggested: `scheduled`)
- [ ] `created_at`
- [ ] `agora_channel` (nullable allowed)
- [ ] `agora_token` (nullable allowed)

## 4) Existing API Compatibility Notes

- [ ] Old request key `user_id` is backward compatible in key places, but `patient_id` should be used going forward.
- [ ] Old request key `appointment_status` is backward compatible, but `status` should be used going forward.
- [ ] Status enum should support at least: `scheduled`, `confirmed`, `hold`, `cancelled`, `completed`, `waiting`.
- [ ] `availability` mapping is time-slot based (`doctor_id + appointment_date + appointment_start_time`).

## 5) Indexes (Recommended)

- [ ] Index on `appointments(patient_id, status)`
- [ ] Index on `appointments(doctor_id, appointment_date, appointment_start_time, status)`
- [ ] Index on `appointments(appointment_id)` if not already PK
- [ ] Index on `hospital_doctors(doctor_id)` if not already PK

## 6) Smoke Test Order

1. Create appointment (`doctor_appointment.php`)
2. Read patient appointments (`appointment_get.php`)
3. Update appointment status (`update_status.php` / `update_appointment.php`)
4. Generate Agora channel/token (`genrate_link.php`)
5. Check doctor availability (`doctor_availability.php`)