# Module: Patient Medical History (history)

## Purpose
Patient-level (not visit-level) comprehensive medical history for an OB/GYN + infertility clinic. Seven sections rendered on one screen: menstrual history, contraception history, obstetric (past pregnancies/deliveries), past medical, past surgical, past gynecological, past ART attempts (IUI/IVF/ICSI with lab details), family history. Summary data (LMP, obstetric formula G/P counts) is consumed by virtually every other clinical screen. A separate small controller manages the "present history" (chief complaint) question/answer catalog used by exam sheets.

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/patienthistory.php` (1270 lines) — main module controller.
- `/home/amrtechogate/public_html/obgy/core/controllers/addpresenthistory.php` (198 lines) — admin CRUD for `presenthistoryquestions` / `presenthistoryanswers` catalog (not in assigned tables, but owned by this area).
- Shared consumers: `core/controllers/_patientdata.php` (patient header: latest LMP + obstetric formula), `core/controllers/_phmain.php` (history summary panel in sheets), `core/controllers/excel.php` (Excel export), `core/controllers/merge.php` (patient merge migrates all ph* rows), `core/controllers/termination.php` (report by termination type), `core/controllers/sh.php` + `Completesreport.php` (infertility-sheet reports resolving the *history catalogs), `fullreport.php`, `completereport.php`, `combinedreport.php`.
- Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/patienthistory/` — `add.html` (full form), `addsimple.html` (simple-view mode), `editmodel.html`, `newMenstrualRow.html`, `newContractRow.html`, `newObstetricRow.html`, `newPastMedicalRow.html`, `newPastSurgicalRow.html`, `newPastgynecologicalRow.html`, `newPastArtRow.html`, `newFamilyRow.html`, `test.html`. Also `core/views/obgy/addpresenthistory/{show,answers}.html` and `core/views/obgy/infertilitysheet/add.html` (multi-selects bound to the *history catalogs).
- ORM: RedBeanPHP (`R::`) in fluid mode; Smarty templates; inline AJAX cell-save pattern.

## Tables
All tables: InnoDB, utf8mb4_unicode_ci, no declared foreign keys, soft delete via `del` flag. In the 12-7-2024 dump these tables are present schema-only (AUTO_INCREMENT=1, no INSERTs); seeded values below come from `_db/obgy_app_22-12-2021.sql`.

### Patient-record tables (one row per patient event)

#### phmenstrual — menstrual history records
- `id` int(11) unsigned PK AI
- `patientid` varchar(191) -> patients.id (stored as string)
- `sysdate` varchar(191) — entry date
- `menarche` varchar(191), `duration` varchar(191), `length` varchar(191)
- `phregularity` varchar(191) -> phregularity.id
- `phamount` varchar(191) -> phamount.id
- `phdysmenorrhoea` varchar(191) -> phdysmenorrhoea.id
- `phl_mp` date — LMP, used in pregnancy dating elsewhere
- `del` int(11) unsigned, `doctorid` int(11) unsigned -> awusers.user_id

#### phcontraception — past contraception records
- `id` PK AI, `patientid` varchar(191) -> patients.id, `sysdate` varchar(191)
- `contype` varchar(191) -> phcontracttype.id
- `contduration`, `contstopped`, `contranote` varchar(191)
- `del` int unsigned, `doctorid` int unsigned -> awusers.user_id

#### phobstetric — past pregnancies/deliveries (1 row per pregnancy, up to 3 babies inline)
- `id` PK AI, `patientid` varchar(191) -> patients.id, `sysdate` varchar(191)
- `obstrduration` varchar(191) — gestation duration
- `obstermination` varchar(191) -> phobstericterminate.id (hardcoded semantics: 1=SVD, 2=CS, 3=Ectopic, 4=Abortion, 5=V.mole in `_patientdata.php`/`_phmain.php`)
- `obsplace` varchar(191) -> phobstericterplace.id
- `obsdate` varchar(191), `obsnote` text
- `del` int unsigned, `doctorid` int unsigned -> awusers.user_id
- `outcomeid` int(191) — outcome flag; controller `update()` forces `babygendernew=1` when outcomeid==1 else 0
- Baby group x3 (denormalized twins/triplets): `babytype`, `babygendernew` (default '0'), `babyweight`, `babyname`, then `babytype1/babygendernew1/babyweight1/babyname1`, `babytype2/babygendernew2/babyweight2/babyname2` (all varchar(191))

#### phpastmedical — past medical diseases
- `id` PK AI, `patientid` varchar(191) -> patients.id, `sysdate` varchar(191)
- `medicaldisease` varchar(191) -> phpastmedicaldisease.id
- `medicalnote` varchar(191), `del` int unsigned, `doctorid` int unsigned
- `for_husband` int(11) default 0 — flags husband's condition (written by sheet controllers, not patienthistory.php)

#### phpastsurgical — past general surgical operations
- `id` PK AI, `patientid` int(11) unsigned -> patients.id (note: int here, varchar elsewhere)
- `doctorid` int unsigned, `sysdate` varchar(191)
- `surgicaloperation` varchar(191) -> phpastsurgicaloperation.id
- `phsurgicalyear` varchar(191) (free text year), `phsurgicalsurgeon` varchar(191), `surgicalnote` varchar(191)
- `del` int unsigned, `for_husband` int(11) default 0

#### phpastgynecological — past gynecological operations
- `id` PK AI, `patientid` varchar(191) -> patients.id, `doctorid` int unsigned
- `sysdate` date (note: real date here, varchar elsewhere)
- `gynecologicaloperation` varchar(191) -> phpastgynecologicaloperation.id
- `gynecologicalyear`, `gynecologicalsurgeon`, `gynecologicalnote` varchar(191)
- `del` int unsigned

#### phpastart — past ART attempts (external centers)
- `id` PK AI, `patientid` varchar(191) -> patients.id, `sysdate` varchar(191)
- `arttype` varchar(191) -> phpastarttype.id (code treats 2/3 = IVF/ICSI specially)
- `artcenter` varchar(191) -> phpastartcenter.id
- `artyear` varchar(191), `artnote` varchar(191)
- `del` int unsigned, `doctorid` int unsigned
- `emdate` date — embryo (freeze) date, `emembryos` int unsigned, `straw1` int unsigned — frozen straws
- NOTE: controller also reads `ovdate`, `freezdate` which are NOT in the 2024 schema (RedBeanPHP fluid-mode drift).

#### phpasticsi — lab details of a past IVF/ICSI attempt (child of phpastart)
- `id` PK AI
- `opu` varchar(191) — oocytes picked up
- `patientid` int unsigned -> patients.id
- `partentid` int unsigned -> phpastart.id (sic, misspelled "parent")
- `gi`, `gii`, `gv` varchar(191) — oocyte maturity grades (GI/GII/GV=MII presumed)
- `et` varchar(191) — embryos transferred, `etf` varchar(191)
- `frozen` varchar(191), `bhcg` varchar(191), `fhr` varchar(191)
- NOTE: controller reads `notes` column not present in schema (fluid drift). Upserted via generic `update()` keyed on `partentid`.

#### phfamily — family history records
- `id` PK AI, `patientid` varchar(191) -> patients.id, `doctorid` int unsigned, `sysdate` varchar(191)
- `family_disease` varchar(191) -> phfamilydisease.id
- `family_relative` varchar(191) -> phfamilyrelative.id
- `familynote` varchar(191), `del` int unsigned

### Lookup tables for patienthistory screen (all: `id` PK AI, `name` varchar(191), `del`)
- **phregularity** (`del` int(11) NOT NULL) — menstrual regularity. Seed (2021): regular, irrigular [sic], menpause [sic], premenpause, iry amenorrhea.
- **phamount** — menstrual blood AMOUNT (not money; verified: only referenced by `phmenstrual.phamount`). Seed: excessive.
- **phdysmenorrhoea** — Seed: dysmenorrhea.
- **phcontracttype** — contraception types. Seed: DMPA, Implants, POP, Mirena, IUD, OC, Condom, tuba, tubal ligation, coc, progesterone only, mesocept.
- **phobstericterminate** — pregnancy termination modes. Seed: SVD(1), CS(2), Ectopic(3), Abortion(4), V.mole(5) + soft-deleted junk rows ('aaaa','777','4444', Arabic test strings). IDs 1-5 are hardcoded in obstetric-formula logic.
- **phobstericterplace** — delivery place. Has an anomalous extra `patientid` int unsigned column (unused in read code). No seed data found.
- **phpastmedicaldisease** — disease names for phpastmedical. No seed in dumps.
- **phpastsurgicaloperation** — operation names for phpastsurgical. No seed in dumps.
- **phpastgynecologicaloperation** — gyn operation names. No seed in dumps.
- **phpastarttype** — ART attempt types. Seed: IUI(1), IVF(2), ICSI(3), cryo(4), faild [sic](5).
- **phpastartcenter** — external ART center names. No seed in dumps.
- **phfamilydisease** — family disease names. No seed in dumps.
- **phfamilyrelative** — relative/kinship names. No seed in dumps.

### Lookup catalogs for the INFERTILITY SHEET (parallel duplicate vocabularies; all: `id` PK AI, `title` varchar(191), `del`)
Referenced as comma-separated ID lists stored in `infertilitysheet` columns of the same name; resolved in `sh.php` / `Completesreport.php` via `R::findAll('X', "id in (0$list)")` (string-interpolated SQL).
- **medicalhistory** — wife's medical diseases. Seed: DM, HTN, hypothyroid, hyperthyroid, MS, Rh Arthritis, SLE, Renal stone, cervical lumbar disc, sciatica, tachcardia, DKA, recurrent ICU admission, anemic, icu admission (covid 19), gastritis, V.V, lumbar disc, hypercholesterolemia, favism, etc. (contains blanks and '??' junk).
- **medicalhistorydm** — husband's ("dm" = male partner, inferred from values) medical history. Seed: DM, varicocele, STD, Smoking, addiction, HTN, bilat, rt, lt, BA, hematuria, hematospermia, Smoking(shisha), paraplegia, obese, ex smoker, ex addiction, etc.
- **surgicalhistory** — wife's surgical history. Seed: appendicectomy, myomectomy, ov cystectomy, lap bil tubal disconn, EP variants (conservative/MTX/Laparoscopy/Laparotomy -otomy/-ectomy), CS, total thyroidectomy, hysterectomy, cholecystectomy, etc. (+7 blank soft-deleted rows).
- **surgicalhistorydm** — husband's surgical history. Seed: herniorrhaphy, varicocelectomy, cystoscope, orchiectomy, unilat, lt, rt, splenectomy, appendicectomy, piles/anal fissure, hard palate tumor excision, etc.
- **familyhistory** — family history for infertility sheet. Seed: DM, HTN, cardiac, hepatic, renal, hypothyroid, cancer, breast cancer, twins, brain cancer, B.A, rh arthritis, down s, HCV, '1st marriage +ve consanguinity', thyrotoxicosis, etc. (DM duplicated 4x, 3 soft-deleted).

## Relationships (explicit list, all inferred — no declared FKs)
- phmenstrual.patientid -> patients.id; phmenstrual.doctorid -> awusers.user_id
- phmenstrual.phregularity -> phregularity.id; phmenstrual.phamount -> phamount.id; phmenstrual.phdysmenorrhoea -> phdysmenorrhoea.id
- phcontraception.patientid -> patients.id; phcontraception.doctorid -> awusers.user_id; phcontraception.contype -> phcontracttype.id
- phobstetric.patientid -> patients.id; phobstetric.doctorid -> awusers.user_id; phobstetric.obstermination -> phobstericterminate.id; phobstetric.obsplace -> phobstericterplace.id
- phpastmedical.patientid -> patients.id; phpastmedical.medicaldisease -> phpastmedicaldisease.id; phpastmedical.doctorid -> awusers.user_id
- phpastsurgical.patientid -> patients.id; phpastsurgical.surgicaloperation -> phpastsurgicaloperation.id; phpastsurgical.doctorid -> awusers.user_id
- phpastgynecological.patientid -> patients.id; phpastgynecological.gynecologicaloperation -> phpastgynecologicaloperation.id; phpastgynecological.doctorid -> awusers.user_id
- phpastart.patientid -> patients.id; phpastart.arttype -> phpastarttype.id; phpastart.artcenter -> phpastartcenter.id; phpastart.doctorid -> awusers.user_id
- phpasticsi.partentid -> phpastart.id; phpasticsi.patientid -> patients.id
- phfamily.patientid -> patients.id; phfamily.family_disease -> phfamilydisease.id; phfamily.family_relative -> phfamilyrelative.id; phfamily.doctorid -> awusers.user_id
- infertilitysheet.{medicalhistory,surgicalhistory,familyhistory,medicalhistorydm,surgicalhistorydm} -> CSV id lists into medicalhistory.id / surgicalhistory.id / familyhistory.id / medicalhistorydm.id / surgicalhistorydm.id
- Cross-module reads: mointoringsheet.patientid (internal ART history shown on history page), mointoringsheetprocedure.id / mointoringsheetprotocol.id (names), excelinfopatients.patientid (export queue), programesetting (section visibility flags: menstrual, contraception, obstetric, past, family, medical, surgical, gynecological, art; simpleview; phppath), presenthistoryanswers.presenthistorycatid -> presenthistoryquestions.id, simplediagnosis (simple-view mode).

## Business Workflows (traced from code)
1. **Open history page** (`patienthistory.php::index`): requires login + role authorization + selected patient (redirect otherwise). Loads `programesetting`; if `simpleview==1` renders `addsimple.html` with `simplediagnosis` list, else renders `add.html` with all 7 sections: each section loads its lookup table(s) (`del=0`) plus the patient's records (`del=0 and patientid=?`), resolving `doctorid` to user_name per row (N+1 query per row). Past ART rows additionally join their `phpasticsi` child by `partentid` and `internalArt()` loads completed `mointoringsheet` rows (status=1) with procedure/protocol names. On every page open: patient registered in `excelinfopatients` if absent and `exec("{phppath}\php excel.php ...")` spawns a background Excel export.
2. **Add row (insert-then-edit)**: `addnewmenstural`, `addnewconstract`, `addnewobstetric`, `addnewpasthmedical`, `addnewpasthsurgical`, `addnewpastgynecological`, `addnewpastart`, `addnewFamily` — each immediately INSERTs an empty record (patientid, doctorid from session, sysdate=today, blank fields, del=0) and returns a rendered Smarty partial row (`new*Row.html`) for the screen.
3. **Inline cell save**: generic `update()` receives `tableName`, `colName`, `id`, `value`, `patientId` from POST and writes directly via RedBean (no whitelist, no auth check on this method). Special cases: `phpasticsi` upserted by `partentid`; `colName=='outcomeid'` also sets `babygendernew`. Date fields normalized when `dated==1`.
4. **Inline lookup creation**: `addRegualrity`, `addPhamount`, `addphdysmenorrhoea`, `addphcontype`, `addphobstericterminate`, `addphobstericterplace`, `addphpastmedical`, `addphpastsurgical`, `addphgynecologicaloperation`, `addphpastarttype`, `addphpastartcenter`, `addphFamilyDisease`, `addphFamilyRelative` — insert new lookup value, echo new id, and immediately update the patient's record row to point at it. `editselect`/`updateselect` rename existing lookup values from a modal (table name passed from client).
5. **Soft delete**: `delRows` (auth) and `delRowsommedel` (NO auth checks) set `del=1` on any client-supplied table/id.
6. **Header summary** (`_patientdata.php`, `_phmain.php`): latest `phmenstrual` by `phl_mp` gives LMP; obstetric formula computed by counting `phobstetric` rows per hardcoded `obstermination` ids (1 SVD, 2 CS, 3 Ectopic, 4 Abortion, 5 V.mole); top-5 distinct past medical / ART / family entries shown via joins to their lookup tables.
7. **Present history catalog** (`addpresenthistory.php`): CRUD on `presenthistoryquestions` (with displayorder) and `presenthistoryanswers` (per question, with `favorite` flag, `deleted` flag) — admin screen feeding the chief-complaint pickers in exam sheets.
8. **Reports/consumers**: `termination.php` joins `phobstetric` to `patients` for termination-type reports; `excel.php` exports all sections per patient; `merge.php` re-points all ph* rows when merging duplicate patients; `sh.php`/`Completesreport.php` resolve infertility-sheet CSV catalog ids for printable reports.

## ERP Migration Notes (Laravel + Angular)
Proposed models/tables (all with real FKs `patient_id`, `recorded_by` (user), timestamps, `softDeletes`):
- `MenstrualHistory` (menstrual_histories): menarche_age int, cycle_duration_days, cycle_length_days, regularity enum/lookup, flow_amount lookup, dysmenorrhea lookup, lmp date.
- `ContraceptionHistory`: method_id FK, duration, stopped_at, note.
- `ObstetricHistory` + child `ObstetricBaby` (hasMany, replaces babytype/1/2 column triplication): gestation, termination_mode ENUM (SVD/CS/Ectopic/Abortion/VesicularMole — make this a code-level enum, NOT a user-editable lookup, because G/P formula logic depends on fixed ids today), place_id, occurred_on date, outcome, note.
- `PastMedicalHistory`, `PastSurgicalHistory`, `PastGynecologicalHistory`: disease/operation_id FK, year (smallint), surgeon, note, `for_husband` boolean (keep — already in legacy phpastmedical/phpastsurgical).
- `PastArtCycle` + `PastArtIcsiDetail` (hasOne; or inline the 9 ICSI columns into PastArtCycle since it's strictly 1:1 via partentid): type enum (IUI/IVF/ICSI/Cryo), center_id FK, year, opu_count, grade_gi/gii/gv, embryos_transferred, frozen_count, bhcg_result, fhr, freeze_date, frozen_straws.
- `FamilyHistory`: disease_id, relative_id, note.
- Lookups: either one polymorphic `lookup_values` table (type, name, is_active) or small dedicated tables. **Merge the duplicate vocabularies**: `medicalhistory` + `phpastmedicaldisease` -> one diseases catalog; `surgicalhistory`/`surgicalhistorydm` + `phpastsurgicaloperation` -> one operations catalog; `familyhistory` + `phfamilydisease` -> one family-disease catalog. Replace `*dm` (husband) catalogs with a `for_husband`/`subject` discriminator on the pivot rather than separate tables.
- Infertility-sheet CSV columns (`infertilitysheet.medicalhistory` etc.) -> proper many-to-many pivots (e.g. `infertility_sheet_disease`, with `subject` = wife|husband). Migration script must split comma lists and map ids.
- `PresentHistoryQuestion` hasMany `PresentHistoryAnswer` (displayorder, favorite).
- Drop/ignore: `phobstericterplace.patientid` stray column; soft-deleted junk lookup rows ('aaaa','777', blanks); dedupe repeated catalog titles (DM x4 in familyhistory). Handle fluid-schema drift columns (`phpastart.ovdate`, `freezdate`, `phpasticsi.notes`) — check production DB for these columns before writing the migration; they exist in code but not in the 2024 dump.
- Data fixes during ETL: cast varchar patientid/lookup-id columns to ints; parse varchar dates (`sysdate`, `obsdate`) to DATE; skip fully-empty rows created by the insert-then-edit pattern; note the 2024 dump ships these lookups EMPTY (live data resembles the 2021 dump — extract vocabularies from production, not from the 2024 dump).
- Security replacements: kill generic client-driven `update/delRows/delRowsommedel` endpoints (arbitrary table/column write, one with zero auth); use per-resource REST endpoints + FormRequest validation + policies; replace `exec()` Excel export with queued Laravel Excel export; eliminate string-interpolated `id IN (0$list)` queries.
- Performance: replace per-row `awusers` lookups with eager loading (`with('recordedBy')`).