# Module: Infertility (infertility)

## Purpose
Couple-centric infertility / delayed-conception management. Two generations coexist:

1. **Legacy quick file** (`infertility.php` + `infertility` table family, keyed by `infertid`): one record per patient with dated child rows for diagnosis (wife+husband), LMP, gyna exam, investigations, notes, plus its own prescription table.
2. **Modern "Infertility Sheet"** (`infertilitysheet.php` + `infertilitysheet`, one row per patient, ~64 columns inline-edited via AJAX): full couple workup — infertility type/duration, menstrual/sexual/medical/surgical/family history, wife examination, 3-level husband diagnosis, previous treatment (ttt*) and response, computed obstetric formula (G/P/FT/PT/EPC/Living), per-spouse prescriptions and lab orders, structured obstetric history rows (`awifep`).

All lookup tables in this module are EMPTY in the dump (`obgy_12-7-2024.sql` has INSERTs only for drugs/invests/aw* framework tables); the clinical vocabulary is built at runtime by doctors via `getselectajax` (inline add-new-term).

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/infertility.php` (515 lines) — legacy quick file. Actions: `index` (auto-creates `infertility` + first child rows), `newdiagnosis`, `newlmp`, `newgyna`, `newinvest`, `newnote`, `update` (generic table/col/value), `del` (soft delete `del=1`), `addprescription` (soft-delete drug), `showprescription`, `printpre`.
- `/home/amrtechogate/public_html/obgy/core/controllers/infertilitysheet.php` (838 lines) — active sheet. Actions: `index`, `loadOne` (loads ~18 child collections by `infertilitysheetid`), `Add`/`update` (generic writes), `obstetric` (recompute G/P), `append` (insert child row into POSTed table), `folliculom`, `getselectajax` (add lookup term), `getselect` (render select of lookup), `getdataselect`/`deldataselect`/`addnewselect`/`deleterow` (lookup CRUD), prescriptions (`showprescription`, `printpre`, `addprescription` against `infertilitysheetdrugs`), investigations (`addinvestigation`, `showInvs`, `printinv` against `infertilitysheetinvest`).
- `/home/amrtechogate/public_html/obgy/core/controllers/infertilitysheet00.php` (513 lines) — OLDER duplicate of infertilitysheet.php (dead code; prescriptions still point to `gynadrugs`, loads `gyna`, `maingyna`).
- Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/infertility/` (`add.html`, `newrow.html`, `newrowdrugedit.html`, `showPrescriptions.html`) and `/home/amrtechogate/public_html/obgy/core/views/obgy/infertilitysheet/` (`add.html` 1988 lines, `append.html`, `select.html`, `delselect.html`, `semen.html`, `showInvestigation.html`, `showPrescriptions.html`, `newrowdrugedit.html`). Print templates are shared from `gyna/print.html` and `gyna/printinv.html`.
- Previous-marriage tables are managed in the **patients module**: `core/controllers/patients.php` (lines 189-193, 1016-1020, 2235-2250 → `patients/previous_marriage.html`) and rendered in header via `core/controllers/_patientdata.php` (lines 297-305) / `patients/patientdata.html` (feature-flagged by `programesetting.previous_marriage`).

## Tables
All InnoDB; NO declared foreign keys anywhere — FKs below are inferred from controller code/views.

### infertility (legacy master, 1:1 patient)
- `id` int PK, `patientid` int NOT NULL, `marriage` varchar(255), `menstr` varchar(255), `operations` varchar(255), `sexualhistory` varchar(255), `breast` varchar(255), `hirsuitism` varchar(255), `sexch` varchar(255), `obesity` varchar(255)
- FK: `patientid` → patients.id. Auto-created on first visit (infertility.php:90-129).

### infertilitydiagnosis
- `id` PK, `infertid` int NOT NULL, `date` date, `diagnosiswife` text, `diagnosishusband` text, `del` int default 0
- FK: `infertid` → infertility.id. Soft delete `del`.

### infertilitydrugs (legacy prescriptions, one drug per row)
- `id` PK, `patientid` int, `date` date, `drugid` varchar(150), `drugtype` varchar(100), `drugdos` varchar(255), `deleted` int default 0, `doctorid` int, `drugname` varchar(191), `forhusband` int unsigned (0=wife, 1=husband), `recepittmpid` int default 0, `recepitdrugid` int default 0
- FK: `patientid` → patients.id; `drugid` → drugs.id (despite varchar type); `doctorid` → awusers.user_id.

### infertilityinvest (legacy investigation results, fixed columns)
- `id` PK, `infertid` int, `date` date, `semen`, `peg`, `hsg`, `pct`, `us`, `prolactin`, `thyr`, `fsh`, `lh`, `laparoscopy` (all varchar(255)), `del` int default 0
- FK: `infertid` → infertility.id. View also writes an `others` column (schema drift — RedBeanPHP auto-adds columns).

### infertilitylmp
- `id` PK, `infertid` int, `date` date. FK: `infertid` → infertility.id.

### infertilitynotes
- `id` PK, `infertid` int, `date` date, `note` text, `del` int default 0. FK: `infertid` → infertility.id.

### infertilitysheet (modern master, 1:1 patient, ~64 cols)
- `id` PK, `patientid` int unsigned
- Obstetric formula (computed by `obstetric()`): `obstg`, `obstp`, `obstft`, `obstpt`, `obstepc`, `obstliving` (int unsigned)
- Sheet header: `sheettype` (→ typeinf), `sheetyear`, `sheetmonth`, `sheetlocation` (→ sheetlocation), `historyduration`, `historyin`, `historyout`
- Husband: `husbndiagnosis1`, `husbndiagnosis2`, `husbndiagnosis3` (CSV ids → husbndiagnosis1/2/3 lookups, multi-select), `sheethusband` (CSV → sheethusband)
- Wife factors: `sheetwife` (CSV → sheetwife), `sheetother`
- Previous treatment: `sheetttt` (free text), `tttda` (→ tttda), `tttmetformin` (→ tttmetformin), `ttttype` (CSV → ttttype), `tttresponse` (→ tttresponse), `sheettrials`, `sheetdate`, `sheetresult`, `diagnosis`
- General exam: `generalbw`, `generalbmi`, `generalbreast`, `generalhirsutism`, `generalthyroid`
- Local exam: `localvulva`, `localvagina`, `localcx`, `localother`, `localother1`, `examinationgeneral`, `examinationlocalse`, `examinationlocalnote`
- Menstrual: `menstrualreg` (→ menstrualreg lookup, other module), `wmenstrualcd`, `menstrualamount`, `menstrualdysm`, `wmenstruallmp`, `menstrualh`
- History: `wsexual`, `medicalhistory`, `w2medical`, `surgicalhistory`, `wsurgical`, `familyhistory`, `wfamily`, `medicalhistorydm`, `medicalhistorynote`, `surgicalhistorydm`, `surgicalhistorynote`
- Misc: `usvsdtv`, `importantnote` (rendered with `--`/`**` → `<br>`)
- All varchar(191); dates stored as "Y/m/d" strings. FK: `patientid` → patients.id. Hub record: ~18 other-module tables key on `infertilitysheetid` (semeninfertility, semen2, hormonalprofile(2), wifeepc, tvs, dtvs, sis, hsginfertility, hysteroscopyinfertility, laparoscopyinfertility, infertilityother, icsi, folliculom, operations, newvisit).

### infertilitysheetdrugs (sheet prescriptions)
- Identical structure to infertilitydrugs: `id`, `patientid`, `date` date, `drugid` varchar(150), `drugtype`, `drugdos`, `deleted`, `doctorid`, `drugname`, `forhusband`, `recepittmpid`, `recepitdrugid`
- FK: `patientid` → patients.id; `drugid` → drugs.id; `doctorid` → awusers.user_id (set from `$_SESSION['user_id']`).

### infertilitysheetinvest (sheet lab orders + results)
- `id` PK, `patientid` int, `date` date, `investid` int, `investresult` varchar(250), `deleted` int default 0, `doctorid` int, `forhusband` int
- FK: `patientid` → patients.id; `investid` → invests.id (catalog grouped by investcats.displayorder buckets in controller); `doctorid` → awusers.user_id.

### Lookup tables — ALL share shape `id` PK, `title` varchar(191), `del` (active = `del IS NULL`); ALL EMPTY in dump; populated at runtime via getselectajax:
- **typeinf** — infertility type for `infertilitysheet.sheettype` (primary/secondary — inference; bound in infertilitysheet/add.html:115).
- **type** — NOT infertility-specific: bound to `registeration.type` in ANC sheet views (ancsheet/add.html:648, append.html:200). Generic lookup; misassigned to this module historically.
- **sheethusband** — husband-attributed infertility factors, multi-select CSV into `infertilitysheet.sheethusband` (add.html:1400).
- **sheetwife** — wife-attributed factors, multi-select CSV into `infertilitysheet.sheetwife` (add.html:1407).
- **sheetlocation** — exam/follow-up location for `infertilitysheet.sheetlocation` (add.html:164).
- **husbndiagnosis1 / husbndiagnosis2 / husbndiagnosis3** — 3-level husband (male-factor) diagnosis multi-selects (add.html:137-149).
- **wifesex** — baby sex lookup for obstetric history rows (`awifep.wifesex`, also legacy `wifep.wifesex`).
- **wifepmethod** — post-delivery contraception method, LEGACY, paired with `wifep`.
- **awifepmethod** — post-delivery contraception method, ACTIVE version, multi-select CSV in `awifep.awifepmethod` (append.html:167, add.html:576); note `wifep` also has an `awifepmethod` column (migration artifact).
- **tttda** — previous treatment: dopamine agonist / ovulation-induction agent ("D.A." — inference) for `infertilitysheet.tttda` (add.html:1188).
- **tttmetformin** — metformin use in previous treatment (add.html:1192).
- **ttttype** — previous treatment/stimulation type, multi-select CSV (add.html:1199).
- **tttresponse** — response to previous treatment (add.html:1203).

### wifep (LEGACY obstetric-history rows)
- `id` PK, `infertilitysheetid` varchar(191), `wifemodeofd`, `wifeobst`, `wifesex`, `wifepmethod`, `wifetypeofd`, `name`, `date`, `comment`, `duration` (varchar(191)), `wifel` tinyint, `awifepmethod` varchar(191)
- FK: `infertilitysheetid` → infertilitysheet.id (varchar! type mismatch); `wifesex` → wifesex.id; `wifepmethod` → wifepmethod.id; `wifemodeofd`/`wifetypeofd`/`wifeobst` → same-named lookups (other module scope). Superseded by `awifep`; still READ in `ivfsheet.php:378` (`ivfhistory` action).

### awifep (ACTIVE obstetric-history rows; "a" = newer revision — inference)
- `id` PK, `infertilitysheetid` int unsigned, `wifesex`, `wifemodeofd`, `wifeobst`, `wifetypeofd`, `name`, `date`, `wifew` (birth weight), `wifel` (living flag; counted as `wifel=1`), `duration`, `stopped`, `comment`, `awifepmethod` (CSV), `wifeage` (all varchar(191))
- FK: `infertilitysheetid` → infertilitysheet.id; `wifesex` → wifesex.id; `awifepmethod` → awifepmethod.id (CSV); `wifetypeofd` → wifetypeofd.id (values "FT"/"PT" used in `obstetric()` to count full-term/pre-term). Rows created by `append` action; `wifeage` computed at render (DateTime diff). Read by infertilitysheet.php, infertilitysheet00.php, ivfsheet.php(:114).

### previous_marriage (wife's previous marriages — patients module screen)
- `id` PK, `period` varchar(255), `males` int default 0, `females` int default 0, `last_age` varchar(255), `deleted` int default 0, `patientid` int default 0
- CHARSET **latin1** (Arabic-content risk). FK: `patientid` → patients.id. Feature flag `programesetting.previous_marriage`.

### hus_previous_marriage (husband's previous marriages)
- Identical columns/charset to previous_marriage. FK: `patientid` → patients.id.

## Relationships (explicit list)
- infertility.patientid → patients.id
- infertilitydiagnosis.infertid → infertility.id
- infertilitylmp.infertid → infertility.id
- infertilityinvest.infertid → infertility.id
- infertilitynotes.infertid → infertility.id
- infertilitydrugs.patientid → patients.id; infertilitydrugs.drugid → drugs.id; infertilitydrugs.doctorid → awusers.user_id
- infertilitysheet.patientid → patients.id
- infertilitysheet.sheettype → typeinf.id
- infertilitysheet.sheetlocation → sheetlocation.id
- infertilitysheet.husbndiagnosis1/2/3 → husbndiagnosis1/2/3.id (CSV)
- infertilitysheet.tttda → tttda.id; .tttmetformin → tttmetformin.id; .ttttype → ttttype.id (CSV); .tttresponse → tttresponse.id
- infertilitysheet.sheethusband → sheethusband.id (CSV); .sheetwife → sheetwife.id (CSV)
- infertilitysheetdrugs.patientid → patients.id; .drugid → drugs.id; .doctorid → awusers.user_id
- infertilitysheetinvest.patientid → patients.id; .investid → invests.id; .doctorid → awusers.user_id
- awifep.infertilitysheetid → infertilitysheet.id; awifep.wifesex → wifesex.id; awifep.awifepmethod → awifepmethod.id (CSV); awifep.wifemodeofd → wifemodeofd.id; awifep.wifetypeofd → wifetypeofd.id
- wifep.infertilitysheetid → infertilitysheet.id (legacy); wifep.wifepmethod → wifepmethod.id; wifep.wifesex → wifesex.id
- previous_marriage.patientid → patients.id; hus_previous_marriage.patientid → patients.id
- registeration.type → type.id (ANC module usage of assigned lookup `type`)
- Cross-module reads: obstetric() reads ancsheet (patientid, endpreg=0, sheetlmp) and wifeepc (infertilitysheetid); ivfsheet reads infertilitysheet/awifep/wifep/wifeepc by infertilitysheetid; gynasheet auto-created from sheet screen; lastvisit upserted per patient; invests/investcats catalog; programesetting; wifetypes/husbandtypes for print headers.

## Business Workflows (traced from code)
1. **Open sheet** (`infertilitysheet.php?patientid=N&ac=index`): loads patient, auto-creates `infertilitysheet` (and `gynasheet`) if absent, upserts `lastvisit`, loads drug catalog, invest catalog (5 displayorder buckets + favorites), and via `loadOne()` all 18 child collections keyed by `infertilitysheetid`. Renders `infertilitysheet/add.html`.
2. **Field-level autosave**: every input/select POSTs `Add`/`update` with `table`,`id`,`name`/`colName`,`value` → RedBeanPHP load/store. `update` special-cases `infertilitysheetdrugs.drugid` (copies drugname) and date flags (`Y/m/d` reformat).
3. **Lookup-driven selects**: `getselect` renders a lookup's options (active = `del IS NULL`) with current value(s) (`explode(',')`); `getselectajax` inserts a new term typed by the doctor; `getdataselect`/`deldataselect` manage (soft-delete) terms.
4. **Obstetric history**: `append` (POST tablename=awifep, tablep=infertilitysheetid) inserts a dated row, rendered by `append.html`; row fields autosaved; `deleterow` hard-deletes (R::trash). `obstetric()` recounts: FT/PT from awifep.wifetypeofd matching wifetypeofd titles "FT"/"PT", abortions from wifeepc (wifeepctype>0), living from awifep.wifel=1, +1 gravida if active ancsheet (endpreg=0) has sheetlmp; stores obst* on sheet and echoes CSV for UI.
5. **Prescriptions** (per spouse, per date): rows in `infertilitysheetdrugs` with forhusband 0/1; `showprescription` (type=show) groups by distinct date with wife/husband drug-name summaries; (type=edit) reloads a date for editing; `printpre` prints via shared `gyna/print.html` using patient wife/husband names + wifetypes/husbandtypes titles.
6. **Investigations**: `addinvestigation` bulk-inserts checked catalog items into `infertilitysheetinvest` (doctorid from session, forhusband flag) and immediately prints (`gyna/printinv.html`); `showInvs` lists per-date husband/wife orders with results; results entered via generic update into `investresult`.
7. **Legacy quick file** (`infertility.php?patientid=N`): auto-creates `infertility` + one initial row each in infertilitydiagnosis/infertilitylmp/infertilitygyna/infertilityinvest/infertilitynotes; per-section "new row" AJAX actions; generic `update`/`del` (del=1); own prescription set in `infertilitydrugs` with identical show/edit/print flow.
8. **Previous marriages** (patients module): added/edited from patient screen (patients.php case at :2235 chooses `previous_marriage` vs `hus_previous_marriage`), displayed in the shared patient-data header on all module screens when `programesetting.previous_marriage = 1`.

## ERP Migration Notes
**Proposed Laravel models:**
- `InfertilityFile` (merge `infertility` + `infertilitysheet`; one per patient, FK patients) with structured satellites: `InfertilityHistory`, `InfertilityExamination`, `InfertilityMenstrualHistory`, `InfertilityTreatmentHistory` (tttda/tttmetformin/ttttype/tttresponse/trials/result), `InfertilityDiagnosisEntry` (dated wife/husband diagnosis ← infertilitydiagnosis), `InfertilityNote` (← infertilitynotes), `LmpRecord` (← infertilitylmp).
- `ObstetricHistory` — single model replacing `wifep` + `awifep` (migrate both, dedupe; columns: sheet FK, baby_sex_id, delivery_mode_id, delivery_type_id, birth_date date, weight, living bool, contraception via pivot, duration, stopped, comment). Drop stored `wifeage`; compute.
- `Prescription` + `PrescriptionItem` — unify `infertilitydrugs` + `infertilitysheetdrugs` (and gynadrugs etc. from other modules) with `context` enum and `for_spouse` enum(wife,husband); FK drugs, users. Fix drugid varchar→FK int.
- `LabOrder` + `LabOrderItem` (← infertilitysheetinvest; result on item) FK invests, users, for_spouse.
- `PreviousMarriage` — single table with `spouse` enum replacing previous_marriage/hus_previous_marriage; convert latin1→utf8mb4; belongs to Patient (patients module).
- `Lookup`/per-domain enum tables replacing the 15 identical lookup tables (typeinf, sheethusband, sheetwife, sheetlocation, husbndiagnosis1-3, ttt*×4, wifesex, wifepmethod+awifepmethod merged, type). Multi-select fields (husbndiagnosis1-3, ttttype, sheethusband, sheetwife, awifepmethod) become pivot tables; write a CSV-explode migration script. Note: lookups are empty in dump but production data will contain runtime-entered terms — migrate distinct titles, trim/dedupe.
- Move lookup `type` to the ANC module scope (used by `registeration.type` only).

**Normalize/fix during migration:**
- All varchar dates ("Y/m/d", "d-m-Y") → DATE columns with tolerant parser; `wifep.infertilitysheetid` varchar → int.
- Replace stored obst* counters with computed accessors from ObstetricHistory + abortions + active pregnancy.
- Standardize soft delete (`del` int / `deleted` int / `del IS NULL` semantics) → Laravel SoftDeletes.
- RedBeanPHP schema drift: production tables may contain extra auto-created columns (e.g. infertilityinvest.others, infertilitygyna) — diff live schema vs dump before migration.

**Drop/security:**
- Drop `infertilitysheet00.php` (dead duplicate). Decommission generic AJAX endpoints (`Add`, `update`, `append`, `getselect*`, `deleterow`) that accept arbitrary table/column names from POST — replace with per-model FormRequest-validated endpoints + Policies (current code allows arbitrary DB writes/deletes; only login is checked, several actions skip even role authorization).