# Module: Visits & Appointments (visits)

## Purpose
Operational core of the OB/GYN clinic system. Handles appointment booking (reception + mobile app), daily doctor queue, day-period slotting with capacity limits, and — inside the *same* `visits` table — the financial side of each visit (cash/visa split, discount, outstanding debt, rest payments, refunds, installments). Also routes the doctor from the queue to the patient's last-used clinical sheet, records infertility follow-up visits, and closes treatment episodes via end-of-visit reports. Syncs paid visits to an external ERP DB as sell bills via cURL.

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/visits.php` (1946 lines) — booking, queue lists, financial transactions, detections CRUD, visit_periods admin, ERP sync.
- `/home/amrtechogate/public_html/obgy/core/controllers/index.php` — home page = today's queue (hospital vs clinic modes), drag-sort of `visitorder`, reads `lastvisit` for sheet routing.
- `/home/amrtechogate/public_html/obgy/core/controllers/mobileservices.php` — mobile booking API (`addVisit`, `updateVisit`, `delVisit`, `visitType`, `queryAllVisitsByDate`, `queryAllVisitsByPatientAndDate`). NO auth checks (commented out).
- `/home/amrtechogate/public_html/obgy/core/controllers/patients.php` — `showendvisits`, `addendvisits`, `endvisit` (writes `endvisitreports`).
- `lastvisit` upserts in: `antenalvisit.php` (~line 307), `ancsheet.php`, `ancsheet00.php`, `gynasheet.php`, `gynasheet00.php`, `iui.php`, `infertilitysheet.php` — each stores its own `controllname` as `lastvisit.control`.
- `newvisit` read/written in: `infertilitysheet.php` (line 252, generic AJAX cell-save), reports in `sh.php`, `Completesreport.php`.
- Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/visits/` — `add.html`, `visitdiv.html`, `waiting_list.html`, `lastvisits.html`, `rest.html`, `periodeditmodel.html`, `detectionmodel.html`, `showdepts.html`, etc. Queue view: `/core/views/obgy/index.html`.

## Tables

### visits (core, transactional)
Appointment + encounter + payment ledger in one table.

| Column | Type | Notes |
|---|---|---|
| id | int(11) PK AI | |
| patientid | int(11) NOT NULL | FK -> patients.id |
| visitdate | date NOT NULL | stored 'Y/m/d' |
| visittime | time NULL | set on payment rows |
| detectionid | int(11) NOT NULL | FK -> detections.id; MAGIC: -99=installment, 999=pay-rest, 9999=refund |
| totaldetectionvalue | decimal(10,0) | total fee |
| discount | decimal(10,0) | |
| detectionvalue_cash | decimal(10,0) | cash paid |
| detectionvalue_visa | decimal(10,0) | visa paid |
| restdetectionvalue | decimal(10,0) | outstanding debt at creation |
| visitorder | int(11) NOT NULL | queue position (drag-sortable) |
| enterordered | int(11) NOT NULL | entry sequence (MAX+1 on insert) |
| view | tinyint(1) def 0 | 0=waiting, 1=entered (hospital mode) |
| deleted | int(11) def 0 | soft delete |
| end | int(11) def 0 | legacy end flag |
| enddate | date | legacy |
| reason / notes | text | |
| enddoctorid | int(11) | FK -> awusers.user_id (inferred) |
| mobileappvisit | int(11) uns | 1 = booked via mobile app |
| payedflag | int(11) uns | 1 = paid (triggers ERP sell bill) |
| visit_period | int(11) def 0 | FK -> visit_periods.id |
| user_id | int(11) def 0 | FK -> awusers.user_id (creator) |
| urgent | int(11) def 0 | 1 = waiting-list/urgent future visit |
| createdate | datetime | |
| visitid | int(11) def 0 | SELF-FK -> visits.id (original visit for 999/9999 payment rows) |
| patient_edit | int(11) def 0 | |
| approx_time | time | approximate appointment time |
| card_add / printserial / sms_send | int(11) def 0 | flags |
| branch_id | varchar(11) def '0' | multi-branch (string type!) |
| visit_order | int(11) uns | duplicate of visitorder (redundant) |

### visits_updates (dead/reserved)
Same columns as `visits` plus: `detectionvalue` int uns, `clinic_id` int uns, `visit_order` int, `userid_edit` int, `date_edit` datetime, `patientid_edit` int. **Not referenced by any PHP code found** in obgy or www apps — looks like an intended edit-audit shadow table that was never wired up (inferred).

### visit_periods (lookup)
| Column | Type |
|---|---|
| id | int(11) PK AI |
| name | varchar(255) |
| timing | varchar(255) |
| max_no | int(11) def 0 (capacity per day-period) |
| deleted | int(11) def 0 |

No seeded rows in the dump. Managed via `visits.php::visitPeriodEdit/addNewRow/updateAjax` (generic AJAX). Active only when `programesetting.visit_period = 1`. Capacity check: `visits.php::patPerPeriod` counts visits per period+date vs `max_no` (enforced client-side).

### newvisit (infertility sheet follow-up visits)
| Column | Type |
|---|---|
| id | int(11) PK AI |
| infertilitysheetid | int(11) uns — FK -> infertilitysheet.id |
| newvisitcycles | varchar(191) — FK -> newvisitcycles.id (stored as string) |
| date | varchar(191) — visit date as text |
| lmp | varchar(191) — last menstrual period |
| bw | varchar(191) — body weight (inferred) |
| ut | varchar(191) — uterus finding |
| ov | varchar(191) — ovaries finding |
| ttt | varchar(191) — treatment |

All clinical fields are varchar — no typing. Rows created/edited via generic AJAX cell-save from `infertilitysheet/add.html`.

### newvisitcycles (lookup)
`id` int uns PK AI, `title` varchar(191), `del` int uns. No seeded rows in dump. Shared by `newvisit` (infertility) and `newvisitg` (gyna sheet follow-ups, other module).

### lastvisit (routing pointer)
`id` int uns PK AI, `patientid` int uns (FK -> patients.id), `control` varchar(191) = controller name of last clinical sheet opened (`antenalvisit`, `ancsheet`, `gynasheet`, `iui`, `infertilitysheet`). Upserted whenever a sheet is opened; read by `index.php` to render the "go" link on the queue.

### old_visits (legacy archive)
Pre-refactor copy of visits with single `detectionvalue` decimal(10,0) instead of cash/visa split. Columns: id, patientid, visitdate, detectionid, detectionvalue, visitorder, enterordered, view, deleted, end, enddate, reason, notes, enddoctorid, mobileappvisit, payedflag. Only used by one-off migration `visits.php::fixOldVisits()` (copies detectionvalue -> totaldetectionvalue + detectionvalue_cash by matching `visits.id = old_visits.id`, hardcoded id ranges up to 9092). The migration function is still web-callable.

### endvisitreports (treatment closure)
| Column | Type |
|---|---|
| id | int(11) PK AI |
| patientid | int(11) NOT NULL — FK -> patients.id |
| reason | text |
| enddate | date |
| notes | text |
| doctorid | int(11) NOT NULL — FK -> awusers.user_id |
| status | int(11) uns |

Written by `patients.php::showendvisits/addendvisits` and bulk-closed per department by `visits.php::emptydeptvisit`.

### vacations (dead/reserved)
`id` int uns PK AI, `vacation_date` date, `vacation_reason` varchar(191), `created_at`/`updated_at` datetime, `created_by`/`updated_by` int uns (FK -> awusers, inferred). Laravel-style timestamps. **Not referenced by any controller/view found** — unused or for newer external code (inferred).

## Relationships (inferred — NO declared FKs anywhere)
- visits.patientid -> patients.id
- visits.detectionid -> detections.id (service type + price `detectionval`; magic values -99/999/9999 are NOT real detections)
- visits.visit_period -> visit_periods.id
- visits.user_id -> awusers.user_id
- visits.enddoctorid -> awusers.user_id (inferred)
- visits.visitid -> visits.id (self-ref: payment/refund row -> original visit)
- visits_updates.patientid -> patients.id; visits_updates.userid_edit -> awusers.user_id (inferred)
- old_visits.id -> visits.id (1:1 by id, per fixOldVisits); old_visits.patientid -> patients.id
- newvisit.infertilitysheetid -> infertilitysheet.id
- newvisit.newvisitcycles -> newvisitcycles.id
- lastvisit.patientid -> patients.id; lastvisit.control -> controller name (antenalvisit/ancsheet/gynasheet/iui/infertilitysheet)
- endvisitreports.patientid -> patients.id; endvisitreports.doctorid -> awusers.user_id
- Installments: visits rows with detectionid=-99 settle balances in totalbalance.patientid
- External ERP DB: sellbill.obygyVisitId -> visits.id; product.obygyDetectionId -> detections.id; client.obygyPatientId -> patients.id (cURL to `sellbillController.php?do=addObgyVisit|updateObgyVisit|delObgyVisit`, `buyBillControllerAjax.php`, `productControllerAjax.php`)
- Config dependencies: programesetting.visitsform (1=hospital queue / else clinic), .visit_period (slots on/off), .addshours (after-midnight extra hours show yesterday's visits), .erpdb / .erpdbsave (ERP target)

## Business Workflows (traced from code)
1. **Reception booking** (`visits.php::index/add`): find patient by statusno (`searchpatient`) or name autocomplete (`getnames`); pick detection (price auto-filled via `getdtprice`); enter cash/visa/discount/rest, period, urgent, approx_time, notes. Insert with `payedflag=1`, `enterordered = MAX+1`, `createdate=now`, `user_id=session`. Paid visit fires `erpSellbill()` (cURL sell bill).
2. **Mobile booking** (`mobileservices.php::addVisit`): rejects if patient already has a visit that day (flag 2) or if 10 mobile visits already exist that day (flag 3, hardcoded). Inserts with `mobileappvisit=1`, `payedflag=0`. On arrival, reception `payvisit()` sets payedflag=1 and creates the ERP bill. `delVisit` refuses to delete paid app visits.
3. **Daily queue** (`index.php::index`): hospital mode — waiting list (`view=0` ORDER BY visitorder) vs entered list (`view=1` ORDER BY enterordered); `visitviewed()` marks entry. Clinic mode — all today's visits ORDER BY visitorder, approx_time; if current hour <= `addshours`, yesterday's visits are included (late-night clinics). `sortable()` persists drag-reorder into `visitorder`.
4. **Sheet routing**: queue row shows a "go" link from `lastvisit.control`; opening any clinical sheet (ANC/gyna/infertility/IUI/antenatal visit) upserts `lastvisit` with that controller name.
5. **Financial follow-ups** (all rows in `visits`): `payval()` inserts detectionid=999 row linked via `visitid`; `refund()` inserts detectionid=9999 after validating refund <= net paid (SUM of 999 rows + original − SUM of 9999 rows); installments `addbalance/gettotalbalance` use detectionid=-99 vs `totalbalance`. `del()` blocks deleting a visit that has refunds. Debt per visit computed on the fly in `visitCommon()`/`getrestvalues()`.
6. **Period view** (`loadVisits/visitsWithPeriod`): when slots enabled, visit list is grouped per day per visit_period with Arabic day names.
7. **Waiting list** (`waitingList/updateWaiting`): future `urgent=1` visits; move to today (`visitdate=today`) or cancel (`deleted=1`).
8. **Treatment closure**: `patients.php::addendvisits/showendvisits` insert `endvisitreports` (status 0, today, doctor=session user); `visits.php::emptydeptvisit` bulk-closes every patient of a detection/department whose latest visit has no later end report.

## ERP Migration Notes
**Proposed Laravel models:**
- `Appointment` (from visits booking fields): patient_id, service_id, scheduled_date, approx_time, visit_period_id, source enum(reception, mobile), urgent bool, status enum(booked, arrived, in_consultation, done, cancelled) — replaces view/deleted/urgent flags.
- `Encounter` or queue fields on Appointment: queue_position (replaces visitorder + visit_order + enterordered).
- `PaymentTransaction`: appointment_id, type enum(fee, rest_payment, refund, installment), cash, visa, discount — **eliminates magic detectionid -99/999/9999 and self-FK visitid**; keep running balance instead of recomputed SUMs.
- `Service` (from detections — owned by services/billing module): title, price.
- `VisitPeriod` / `AppointmentSlot`: name, timing, capacity (max_no); enforce capacity in a DB transaction (current check is client-side only / race-prone).
- `ClinicClosure` (from vacations): wire it into booking validation — table exists but was never used.
- `TreatmentClosure` (from endvisitreports): patient_id, doctor_id, closed_at, reason, notes; or model as status on an EpisodeOfCare.
- `InfertilityFollowUp` + `CycleType` (from newvisit + newvisitcycles): belongs to clinical infertility module; convert varchar date/lmp/bw to date/decimal types; unify CycleType with gyna sheet's `newvisitg` usage.

**Drop/merge:**
- Drop `old_visits` (archive only; fixOldVisits already applied) and `visits_updates` (never wired); replace audit with laravel-auditing/activity-log.
- Drop `lastvisit`; derive "last sheet type" from latest clinical sheet record or a `patients.last_sheet_type` column.
- Normalize branch_id (varchar '0') to a proper branches FK.

**Risks/cleanup during migration:**
- Magic detectionid rows must be ETL-split into PaymentTransaction rows; rows with detectionid in (-99,999,9999) are NOT appointments.
- SQL injection throughout (`search`, `getnames`, `emptydeptvisit`, string-concat queries) and generic AJAX endpoints accepting arbitrary table/column names (`updateAjax`, `addNewRow`, `editselect`) — do not port; replace with validated REST endpoints.
- `mobileservices.php` has all auth commented out — public unauthenticated API; replace with Sanctum-protected mobile API; make the 10-visits/day cap a setting.
- Dates stored as 'Y/m/d' strings in `date` columns and as varchar in newvisit — normalize during ETL.
- Replace cURL-based ERP sell-bill sync with internal invoicing in the unified Laravel ERP (events/jobs), preserving the obygyVisitId mapping for historical bills.