# Module: Patients & Registration (patients)

## Purpose
The hub of the entire OB/GYN system. `patients` holds the full demographic record of the couple (wife = the patient, plus husband data embedded in the same row). Almost every other table in the system references it via `patientid` (sometimes `pid`). The module covers: draft-based patient registration with auto file number, Egyptian national-ID parsing, duplicate checks, multi-criteria search, lookup-table management (titles, jobs, education, blood types, marital status), the shared patient-header strip rendered on every clinical screen, quick medical records + prescriptions, patient file uploads, end-of-care reports, and synchronous cURL sync of patients into an external ERP database as `client` rows.

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/patients.php` (2259 lines) — registration wizard (`index`, `add`, `addfinish`, `update`, `updateajax`), search (`search`), soft delete (`del`), full CRUD for all lookup tables (wifetypes, husbandtypes, education, wifejobs, husbandjobs, wifestatus, bloodtypes), previous marriages (`addprevmarr`), end-visit reports (`showendvisits`, `addendvisits`), file listing/download (`patientfiles`, `download`), ERP sync (`erpClient`, `erpClientDel`, `erpSellbill`, `addAllPatientsToERP`, `curlAddClient/curlUpdateClient/curlDelClient`, `erpGetClientTreeId`).
- `/home/amrtechogate/public_html/obgy/core/controllers/patientindex.php` — thin standalone wrapper that renders the patient add form plus the patient-data strip (used by sub-apps).
- `/home/amrtechogate/public_html/obgy/core/controllers/_patientdata.php` — class `patientdata::patientdatashow()`; shared header strip: resolves all lookups, computes ages, marriage duration, obstetric formula counters (P/CS/AB/Ectopic/VM/SVD) from `phobstetric` + baseline columns on `patients`, shows history summaries (`phpastmedical`, `phpastsurgical`, `phpastgynecological`, `phpastart`, `phfamily`, `phmenstrual`, `phcontraception`), previous marriages; **auto-inserts an empty `examination` row if none exists (write-on-read)**.
- `/home/amrtechogate/public_html/obgy/core/controllers/record.php` — quick medical record screen (`records` table) + prescriptions (`recorddrugs`/`drugs`) per date for wife (`forhusband=0`) or husband (`forhusband=1`), printable via `gyna/print.html`.
- `/home/amrtechogate/public_html/obgy/core/controllers/gtimage.php` — genital-tract image annotation (`gtimage`/`gtdetail` tables, patientid-linked); the "patient files" aspect: uploads go to `upload/patientfiles/{patientid}` via `_library/plugins/classupload/uploadImages.php` + `views/obgy/_multiupload/multiuplaod.html`; listing/download reads the directory directly (the `patientfiles` table is NOT used).
- Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/patients/` (`add.html`, `edit.html`, `show.html`, `patientdata.html`, `patientdatageneral.html`, `files.html`, `previous_marriage.html`, `showendvisits.html`, `siglemodeltodelete/`, `siglemodeltoupdate/`, `wifetypeafterdelete/`), `views/obgy/record/`, `views/obgy/searchresult.html`.

## Tables

### patients (utf8mb4, InnoDB) — core entity
Columns (from CREATE TABLE):
- `id` int(11) PK AI
- Wife: `wifename` text, `wiftypeid` int (→ wifetypes.id), `dateofbirth` date, `wifebl` int (→ bloodtypes.id), `wifeage` int (redundant with dateofbirth), `wifeeducation` varchar(255) (stores education.id as string), `wifejob` varchar(255) (stores wifejobs.id), `wifenationalid` varchar(255), `statuesid` int (→ wifestatus.id, note misspelling)
- Marriage: `mirragefrom` float ("Y.M" string-ish duration), `mirragefromdate` date (back-computed), `boyson` int, `girlson` int
- Husband: `husdandname` varchar(255) (misspelled), `husbandtypeid` int (→ husbandtypes.id), `husbandage` int, `husbanddateofbirth` date, `husbandbl` varchar(255) (→ bloodtypes.id as string), `husbandjob` varchar(255) (→ husbandjobs.id), `husbandeducation` varchar(255) (→ education.id), `husbandnationalid` varchar(255), `husband_notes` varchar(191), `husband_habbits` text
- Contact: `address`, `phone`, `mobile` varchar(255)
- Clinical flags: `importantnote` varchar(255), `risk` varchar(255), `risktype` int (→ risktype.id), `spacialnotes` varchar(255)
- File number: `statusno` varchar(255) (KEY statusno(191)); generated `MAX(statusno)+1` in `getstatusno()`
- Obstetric baseline counters (added to phobstetric-derived counts in _patientdata): `pno`, `ab`, `ectopic`, `vmodel`, `svd`, `cs` all varchar(191)
- Lifecycle: `done` tinyint(1) default 0 (draft flag), `deleted` int default 0 (soft delete), `entrydate` datetime, `createdate` datetime, `userid` int (→ awusers.user_id, creator), `doctorid` varchar(191) (→ awusers.user_id where role_id=4), `insert_from` int, `gender` tinyint(1)
- Portal: `patient_password_hash` varchar(191) (password_hash), `phone_qid` varchar(191), `patientid` int unsigned (self-reference column of unclear use — استنتاج: merge/import linkage)

### patients_updates (utf8mb4)
Exact clone of `patients` columns plus audit columns: `userid_edit` int default 0, `date_edit` datetime, `patientid_edit` int default 0 (→ patients.id). `statusno` is int here (vs varchar in patients). **No active controller writes/reads it** — legacy audit-trail table (inference). 

### patients_tmp (latin1)
`pit_id` int PK AI, `pit_name` varchar(55). Zero code references — dead scratch table.

### patientfiles (utf8mb4)
`id` int PK AI, `patientid` int NOT NULL (→ patients.id), `file` varchar(240). Table exists but file management is purely filesystem-based (`upload/patientfiles/{patientid}` scanned with `opendir` in `patients.php::patientfiles()`); effectively unused.

### registeration (utf8mb4) [sic — "registration"]
Delivery booking rows attached to an ANC sheet:
`id` int unsigned PK AI, `ancsheetid` int unsigned (→ ancsheet.id), `place2` varchar(191) (delivery place), `origin` varchar(191) (→ origin.id), `type` varchar(191), `awifep` varchar(191), `coast` varchar(191) (cost, misspelled), `rplace` varchar(191).
Created via the generic ajax row-append in `ancsheet.php` (`data-table="registeration"` in `views/obgy/ancsheet/append.html`); read in `ancsheet.php` (lines 224, 391, 554), `Deliveries.php`, `Completesreport.php`, `sh.php` (joins `origin` lookup).

### records (utf8mb4)
Quick medical record lines: `id` int PK AI, `patientid` int NOT NULL (→ patients.id), `date` date NOT NULL, `diagnosis` text, `ttt` text (treatment), `del` int default 0. Managed by `record.php` (auto-creates an empty row per patient if none; inline ajax updates; soft delete). Functionally paired with `recorddrugs` (patientid + date + forhusband) for prescriptions.

### origin (lookup, utf8mb4)
`id` int unsigned PK AI, `title` varchar(191), `del` varchar(191). No seed rows in dump. Referenced by `registeration.origin`; loaded in `Deliveries.php`/`sh.php`.

### education (lookup, latin1)
`id` int PK AI, `title` varchar(150), `del` int unsigned. No seed rows in dump. Used for both `patients.wifeeducation` and `patients.husbandeducation`. CRUD in patients.php.

### bloodtypes (lookup, latin1)
`id` int PK AI, `title` varchar(40), `del` int unsigned. No seed rows in dump. Used by `patients.wifebl` (int) and `patients.husbandbl` (varchar). CRUD in patients.php.

### wifejobs (lookup, latin1)
`id` int PK AI, `title` varchar(55), `del` int unsigned. No seed rows. → `patients.wifejob`; joined in search. CRUD in patients.php.

### husbandjobs (lookup, latin1)
`id` int PK AI, `title` varchar(55), `del` int unsigned. No seed rows. → `patients.husbandjob`; INNER JOIN in husband-job search. CRUD in patients.php.

### wifestatus (lookup, latin1)
`id` int PK AI, `title` varchar(55), `del` int unsigned default 0. **Seeded values** (UTF-8 stored in latin1 table, mojibake in dump): (1) انسة "single", (2) متزوجة "married", (3) مطلقة "divorced", (8) متزجه للمره الثانيه "married for the 2nd time", (9) ارمله "widow". → `patients.statuesid`. CRUD in patients.php.

### wifetypes (lookup, latin1)
`id` int PK AI, `title` varchar(55), `del` int unsigned. No seed rows. → `patients.wiftypeid`; printed as the honorific/title before the wife's name on prescriptions (`record.php::printpre`). CRUD in patients.php.

### husbandtypes (lookup, latin1)
`id` int PK AI, `title` varchar(50), `del` int unsigned. No seed rows. → `patients.husbandtypeid`; honorific on husband prescriptions. CRUD in patients.php.

### complaint (lookup, latin1)
`id` int PK AI, `name` varchar(255), `conditions` int (soft-delete flag, 0 = active). No seed rows. Chief-complaint vocabulary for gyna/infertility flows: read in `gyna.php`, `gynasheet00.php`, `infertilitysheet00.php`, `ancsheet00.php`, `completereport.php`. Deletable lookup per `setup.php` whitelist.

### complaintant (lookup, latin1)
`id` int PK AI, `name` varchar(255), `conditions` int. Structural twin of `complaint`, used for antenatal visit complaints: `antenalvisit.php` (read + generic add with `$table = "complaintant"`), `completereport.php`.

### locations (MyISAM, latin1/utf8mb4 name column)
`id` int PK AI, `name` varchar(255), `deleted` int default 0. **No controller references — dead table.**

## Relationships (explicit list; NO declared FKs anywhere — all inferred from code)
- patients.wiftypeid -> wifetypes.id
- patients.statuesid -> wifestatus.id
- patients.wifeeducation -> education.id (stored as varchar)
- patients.husbandeducation -> education.id (varchar)
- patients.wifejob -> wifejobs.id (varchar)
- patients.husbandjob -> husbandjobs.id (varchar)
- patients.wifebl -> bloodtypes.id
- patients.husbandbl -> bloodtypes.id (varchar)
- patients.husbandtypeid -> husbandtypes.id
- patients.risktype -> risktype.id (risk module)
- patients.userid -> awusers.user_id ; patients.doctorid -> awusers.user_id (role_id=4)
- patients_updates.patientid_edit -> patients.id ; patients_updates.userid_edit -> awusers.user_id (inferred)
- patientfiles.patientid -> patients.id (unused)
- records.patientid -> patients.id
- registeration.ancsheetid -> ancsheet.id ; registeration.origin -> origin.id
- Cross-module consumers of patients.id: visits.patientid, lastvisit.patientid, endvisitreports.patientid, examination.patientid, phmenstrual/phcontraception/phobstetric/phpastmedical/phpastsurgical/phpastgynecological/phpastart/phfamily.patientid, previous_marriage.patientid, hus_previous_marriage.patientid, recorddrugs.patientid (+ recorddrugs.drugid -> drugs.id), gtimage.patientid, ancsheet/gynasheet/infertilitysheet.patientid
- External ERP DB: client.obygyPatientId -> patients.id (cURL sync to `clientControllerAjax.php` do=addclient/updateFull/deleteFinaly; sellbillController.php do=addObgyVisit)

## Business Workflows (traced from code)
1. **Draft registration**: `patients.php?ac=index` finds an unfinished draft (`done=0 AND userid=current user`) or dispenses a new `patients` row immediately with auto `statusno = MAX(statusno)+1` (race-prone). Upload session dir is set per patient id.
2. **National ID**: `checknationalid()` validates the Egyptian 14-digit NID with a regex and derives the birth date (century digit 2/3, YYMMDD, governorate codes); ages displayed as "xY, xM, xD".
3. **Inline saving**: every field saves via ajax (`updateajax`, `saveage`, `saveagehus`, `caldatefromage`); live duplicate checks: `checkingwifename`, `checkingwifestatusno`, `checkingnationalwifename`, `checkingnationalhusband`, `checkmobile`.
4. **Finish (`addfinish`)**: sets `done=1`, persists full wife+husband payload, hashes patient password, marriage duration converted to a back-dated `mirragefromdate`; optionally inserts a `visits` row (detectionid, cash/visa amounts, discount, visit_period, urgent, enterordered queue number, payedflag) inside one RedBean transaction; redirects by role (role 3 → reception index, others → patienthistory).
5. **ERP sync**: if `programesetting.erpdb` is set, RedBean attaches a second DB; `client` matched by `obygyPatientId`; insert/update/delete done by synchronous cURL to the ERP controllers; `erpSellbill` posts the visit fee as a sell bill; `addAllPatientsToERP` bulk-migrates in batches of 30.
6. **Search**: `?ac=search` builds dynamic LIKE clauses (wifename/statusno, husband name, husband job join, wife job join, address, phone, mobile, wifeage, national id), `done=1 AND deleted=0`, LIMIT 60; per-result link target from `lastvisit.control`; hospital mode searches by department through `visits.detectionid`, excluding patients with a later `endvisitreports.enddate`.
7. **Patient header strip** (`_patientdata.php`, included by virtually every clinical controller): resolves all lookups, computes obstetric formula — P = SVD(phobstetric obstermination=1) + CS(…=2) + baselines (`pno`,`svd`,`cs`), AB (…=4 + `ab`), Ectopic (…=3 + `ectopic`), VM (…=5 + `vmodel`); shows top-5 summaries of past medical/surgical/gyn/ART/family history; auto-creates today's `examination` row if missing.
8. **Medical record** (`record.php`): per-date `records` rows (diagnosis/ttt) with inline ajax edit and soft delete; prescriptions in `recorddrugs` per date and per person (wife/husband), printed with the person's title from wifetypes/husbandtypes.
9. **Files**: multiupload plugin saves to `upload/patientfiles/{id}`; `patientfiles()` lists by directory scan; `download()` streams with force-download headers (no path sanitization — traversal risk).
10. **Delivery registration**: from the ANC sheet, generic ajax row-append creates `registeration` rows (place, origin, type, cost) keyed by `ancsheetid`; consumed by Deliveries/Completesreport/sh reports.
11. **End of care**: `addendvisits`/`showendvisits` create/list `endvisitreports` rows (status, enddate, reason, notes, doctorid, patientid).
12. **Delete**: soft delete `deleted=1` + ERP client final delete.

## ERP Migration Notes
- **Laravel models**: `Patient` (patients, demographics of the woman only), `Spouse` or `PatientHusband` (1:1, extracted husband columns), `ObstetricBaseline` (pno/ab/ectopic/vmodel/svd/cs as integers), `MedicalRecord` (records), `Prescription`/`PrescriptionItem` (recorddrugs, owned by pharmacy module), `DeliveryRegistration` (registeration, FK anc_sheet_id + origin_id, `coast`→`cost` decimal), `Origin`, `EducationLevel`, `JobTitle` (merge wifejobs+husbandjobs with no gender split), `PersonTitle` (merge wifetypes+husbandtypes), `MaritalStatus` (seed the 5 Arabic values), `BloodType` (or PHP enum), `Complaint` (merge complaint+complaintant with `context` enum: gyna|antenatal|infertility), `PreviousMarriage` (covers both previous_marriage and hus_previous_marriage with a `for_spouse` flag).
- **Drop**: patients_tmp, locations, patientfiles (replace with Spatie MediaLibrary / `patient_attachments`, importing files from `upload/patientfiles/{id}`), patients_updates (replace with owen-it/laravel-auditing on Patient).
- **Normalization/cleanup**: rename misspelled columns (`husdandname`→husband_name, `wiftypeid`, `statuesid`, `mirragefrom`, `spacialnotes`, `registeration`); convert varchar lookup-id columns to `foreignId`; `0000-00-00` → NULL dates; drop stored `wifeage`/`husbandage` (computed accessors); unique index on national id + file number; file number via DB sequence/transaction instead of MAX+1.
- **Auth**: move `patient_password_hash` to a `patient_users` table / separate Laravel guard (mobile app login).
- **Integration**: kill the cURL dual-DB sync — in the target ERP the patient IS the client (`patient_id` on the client/account record); visit billing becomes an event-driven invoice (queued job) instead of synchronous curl in the save path.
- **Search**: replace LIKE-on-unindexed-columns with indexed columns + Laravel Scout/fulltext; replicate the multi-criteria search UX in Angular.
- **Behavioral parity to preserve**: draft-style progressive registration (or replace with client-side form state), NID→birthdate auto-fill, duplicate checks (name/NID/phone/file no), obstetric formula computation, role-based post-registration redirect.