# Module: Gynecology (gynecology)

## Purpose
Outpatient gynecology clinic of the legacy OB/GYN system (PHP + Smarty + RedBeanPHP "aw framework"). Covers:
- Per-patient gyna visit log (complaint, multi-diagnosis, notes, LMP).
- Prescriptions and investigation requests for wife OR husband from the gyna screen.
- Gyna ultrasound (cycle day, endometrium, ovaries, uterus, cervix) with per-follicle measurements.
- A one-page "Gyna Sheet" summary (critical note, menstrual history, local examination, follow-up visit rows).
- A compact infertility assessment (type, durations, hormones, semen, HSG) plus treatment-plan lines.
- A gyna-examination sub-record used inside the Infertility module.

## Controllers & Views (file paths)
Controllers (all in `/home/amrtechogate/public_html/obgy/core/controllers/`):
- `gyna.php` (1685 lines) — main gyna visit screen. Tables: `gyna`, `maingyna`, `gynadrugs`, `gynainvestigation`, `gynaus`, `gynausficils`, `gynainfertility`, `gynainfertilityplan`, `gynainfertilitytype`, plus cross-module `gynaph`, `lastvisit`, `diagnosis`, `complaint`, `drugs`, `invests`, `investcats`, `patients`, `programesetting`, `awusers`.
- `gynasheet.php` (775 lines) — Gyna Sheet screen. Tables: `gynasheet`, `newvisitg`, `gynasheetdrugs`, `gynasheetinvest`, plus `infertilitysheet` and generic lookup CRUD (`getselectajax`/`getselect`/`getdataselect`/`deldataselect`) over `menstrualreg`, `menstrualamount`, `menstrualdysm`, `localvulva`, `localvagina`, `localcx`, `newvisitgco`, `newvisitgdiag`.
- `gynasheet00.php` (466 lines) — legacy alternate sheet combining gyna visits + gynaus + gynasheet + infertilitysheet in one page (duplicated logic of gyna.php + gynasheet.php).
- `ultrasoundgyna.php` (316 lines) — sonographer workflow for the formal gyna US report. NOTE: operates on table `ultrasoundgyna` (ultrasound module, not in this module's table list); the gyna-visit US (`gynaus`) is managed inside `gyna.php`.
- `infertility.php` (cross-module) — creates/edits `infertilitygyna` rows (lines ~109, 144, 257).

Views (in `/home/amrtechogate/public_html/obgy/core/views/obgy/`):
- `gyna/` — add.html, newrow.html, newrowUS.html, infertility.html, newrowplan.html, addphmodel.html, showPrescriptions.html, showInvestigation.html, showph.html, print.html, printinv.html, printus.html, printph.html, newDrug.html, newrowdrugedit.html, newselectvalues.html, editmodel.html.
- `gynasheet/` — add.html, append.html, select.html, delselect.html, newrowUS.html, newrowdrugedit.html, showPrescriptions.html, showInvestigation.html.
- `Gynasheet/` (capitalized, used by gynasheet00.php) — add.html, select.html, delselect.html.
- `ultrasoundgyna/` — show.html, add.html, print.html.
- `infertility/newrow.html` — inline editing of `infertilitygyna` fields.

## Tables
No declared foreign keys anywhere; all FKs below are inferred from controller SQL/column names. All tables InnoDB utf8mb4, PK = `id`.

### gyna — gyna visit record (one row per visit)
Columns: `id` int unsigned AI PK; `patientid` int unsigned; `gynadate` date; `complaint` varchar(191); `diagnosisid` text (CSV of diagnosis ids); `notes` text; `conditions` int unsigned (soft delete: 0 active, 1 deleted); `doctorid` int unsigned; `diagnosistxt` varchar(191) (legacy free-text, merged into `complaint` on page load and blanked); `complaintid` varchar(191) (CSV of complaint ids); `inf_type`, `inf_since_y`, `inf_since_m`, `inf_stay_y`, `inf_stay_m`, `inf_stay_hus`, `inf_abortion`, `inf_semen`, `inf_fsh`, `inf_lh`, `inf_tsh`, `inf_prl`, `inf_amh`, `inf_hsg` all varchar(255); `inf_note` text; `gynalmp` date.
FKs: `patientid` -> patients.id; `doctorid` -> awusers.user_id; `diagnosisid` CSV -> diagnosis.id; `complaintid` CSV -> complaint.id.

### gynadrugs — prescription items on gyna screen
Columns: `id` int AI PK; `patientid` int; `date` date; `drugid` varchar(150); `drugtype` varchar(100); `drugdos` varchar(255); `deleted` int default 0; `doctorid` int; `drugname` varchar(191) (denormalized copy); `forhusband` int unsigned (0=wife, 1=husband); `recepittmpid` int default 0; `recepitdrugid` int default 0 (receipt-template linkage, inferred).
FKs: `patientid` -> patients.id; `drugid` -> drugs.id; `doctorid` -> awusers.user_id.

### gynainvestigation — investigation requests/results on gyna screen
Columns: `id` int AI PK; `patientid` int; `date` date; `investid` int; `investresult` varchar(250); `deleted` int default 0; `doctorid` int; `forhusband` int.
FKs: `patientid` -> patients.id; `investid` -> invests.id; `doctorid` -> awusers.user_id.

### gynasheet — one summary sheet header per patient
Columns: `id` int AI PK; `patientid` int unsigned; `usvsdtv` varchar(191); `importantnote` varchar(191) ("Critical Data" shown at top; `--`/`**` tokens rendered as `<br>`).
FKs: `patientid` -> patients.id. Parent of `newvisitg`.

### gynasheetdrugs — prescription items on Gyna Sheet screen
Identical structure to `gynadrugs` (same 12 columns). FKs: `patientid` -> patients.id; `drugid` -> drugs.id; `doctorid` -> awusers.user_id.

### gynasheetinvest — investigation requests on Gyna Sheet screen
Identical structure to `gynainvestigation` (same 8 columns). FKs: `patientid` -> patients.id; `investid` -> invests.id.

### gynaus — gyna-visit ultrasound (table comment: "ultrasound for gyna visits")
Columns: `id` int AI PK; `patientid` int; `date` date; `day` varchar(50) (cycle day); `endo` varchar(50) (endometrium); left ovary: `ovrayll`, `ovraylw`, `ovraylv`, `ovraylno`, `ovraylsize` varchar(50); right ovary: `ovrayrl`, `ovrayrw`, `ovrayrv`, `ovrayrno`, `ovrayrsize` varchar(50); uterus: `uterusl`, `uterusw`, `uterusv` varchar(50); cervix: `cervixl`, `cervixw`, `cervixv` varchar(50); `notes` text; `lfolliclesno` int default 0; `rfolliclesno` int default 0; `doctorid` int; `deleted` int default 0; `mainantenatalid` int unsigned.
FKs: `patientid` -> patients.id; `doctorid` -> awusers.user_id; `mainantenatalid` -> mainantenatal.id (inferred from name, set when US created from antenatal context).

### gynausficils — per-follicle measurements for a gynaus row
Columns: `id` int AI PK; `gynausid` int; `name` varchar(100); `length` varchar(100); `width` varchar(100); `volume` varchar(100); `type` int default 0 (0=left ovary, 1=right ovary — from `savefocil()`); `sysdate` date.
FK: `gynausid` -> gynaus.id. Saved in one transaction together with parent's `lfolliclesno`/`rfolliclesno`.

### gynainfertility — one infertility summary per patient (modal on gyna screen)
Columns: `id` int AI PK; `patientid` int; `note` text; `date` date; `type` varchar(255) (holds id of gynainfertilitytype — `addnewrecord()` stores the new type id here); `since_y`, `since_m`, `stay_y`, `stay_m`, `stay_hus`, `abortion`, `semen`, `fsh`, `lh`, `tsh`, `prl`, `amh`, `hsg` all varchar(255).
FKs: `patientid` -> patients.id; `type` -> gynainfertilitytype.id. Auto-created on first open (`getInfdata`). Duplicates `gyna.inf_*` columns.

### gynainfertilityplan — infertility treatment plan lines
Columns: `id` int AI PK; `patientid` int; `plan` text; `itr` int default 0; `deleted` int default 0.
FK: `patientid` -> patients.id. First row auto-created with empty plan.

### gynainfertilitytype — lookup: infertility types
Columns: `id` int AI PK; `name` text; `deleted` int default 0.
No seeded INSERTs in the dump (user-populated at runtime via `addnewrecord`). Referenced by `gynainfertility.type`.

### infertilitygyna — gyna examination rows inside the Infertility module
Columns: `id` int AI PK; `infertid` int; `date` date; `vagina` varchar(255); `exam` varchar(255); `sounding` varchar(255); `adenxae` varchar(255).
FK: `infertid` -> infertility.id. Created automatically when an `infertility` record is created (infertility.php ~line 109) and via `newgyna()`. WARNING: controller queries `del = 0` but the dump schema has NO `del` column — RedBeanPHP fluid mode presumably adds it at runtime (inference).

### maingyna — per-patient header notes/plan on the gyna screen
Columns: `id` int unsigned AI PK; `patientid` int unsigned; `create_date` datetime; `notes` varchar(191); `plan` varchar(191).
FK: `patientid` -> patients.id. Auto-created on first visit to gyna screen (gyna.php and gynasheet00.php index()).

### newvisitg — follow-up visit rows inside the Gyna Sheet
Columns: `id` int AI PK; `gynasheetid` int unsigned; `newvisitcycles` varchar(191); `newvisitgdiag` varchar(191) (CSV of newvisitgdiag ids); `newvisitgco` varchar(191) (CSV of newvisitgco ids); `date` varchar(191) (NOT a date type, stored "Y/m/d"); `lmp` varchar(191); `bw` varchar(191) (body weight); `ut` varchar(191) (uterus); `ov` varchar(191) (ovary); `noteg` varchar(191); `plan` text; `usvsdtv` varchar(191) (radio: 1=US, 2=TVS, 3=3D TVS — from gynasheet/add.html).
FKs: `gynasheetid` -> gynasheet.id; CSV columns -> newvisitgco.id / newvisitgdiag.id. Rows created by `gynasheet.php::append()` (tablename/tablep posted from client).

### newvisitgco — lookup: sheet-visit complaints (C/O)
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned (NULL=active, 1=deleted; queries use `del is null`).
No seeded data in dump; user-extensible via `getselectajax`. Referenced by `newvisitg.newvisitgco` (CSV, multi-select).

### newvisitgdiag — lookup: sheet-visit diagnoses
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned.
No seeded data. Referenced by `newvisitg.newvisitgdiag` (CSV, multi-select).

### menstrualamount — lookup: menstrual flow amount
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned.
No seeded data in this dump. Selected on Gyna Sheet "Menstrual History"; chosen value stored in `infertilitysheet.menstrualamount` (cross-module column).

### menstrualdysm — lookup: dysmenorrhea
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` tinyint(1) unsigned.
No seeded data. Value stored in `infertilitysheet.menstrualdysm`.

### menstrualreg — lookup: menstrual regularity
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` tinyint(1) unsigned.
No seeded data. Value stored in `infertilitysheet.menstrualreg`.

### localcx — lookup: local examination, cervix findings
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned.
No seeded data. Multi-select; CSV stored in `infertilitysheet.localcx`.

### localvagina — lookup: local examination, vagina findings
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned.
No seeded data. Multi-select; CSV stored in `infertilitysheet.localvagina`.

### localvulva — lookup: local examination, vulva findings
Columns: `id` int unsigned AI PK; `title` varchar(191); `del` int unsigned.
No seeded data. Multi-select; CSV stored in `infertilitysheet.localvulva`.

## Relationships (explicit list: table.column -> table.column)
- gyna.patientid -> patients.id
- gyna.doctorid -> awusers.user_id
- gyna.diagnosisid (CSV) -> diagnosis.id
- gyna.complaintid (CSV) -> complaint.id
- gynadrugs.patientid -> patients.id; gynadrugs.drugid -> drugs.id; gynadrugs.doctorid -> awusers.user_id
- gynainvestigation.patientid -> patients.id; gynainvestigation.investid -> invests.id; gynainvestigation.doctorid -> awusers.user_id
- gynasheet.patientid -> patients.id
- gynasheetdrugs.patientid -> patients.id; gynasheetdrugs.drugid -> drugs.id; gynasheetdrugs.doctorid -> awusers.user_id
- gynasheetinvest.patientid -> patients.id; gynasheetinvest.investid -> invests.id
- gynaus.patientid -> patients.id; gynaus.doctorid -> awusers.user_id; gynaus.mainantenatalid -> mainantenatal.id (inferred)
- gynausficils.gynausid -> gynaus.id
- gynainfertility.patientid -> patients.id; gynainfertility.type -> gynainfertilitytype.id
- gynainfertilityplan.patientid -> patients.id
- infertilitygyna.infertid -> infertility.id
- maingyna.patientid -> patients.id
- newvisitg.gynasheetid -> gynasheet.id
- newvisitg.newvisitgco (CSV) -> newvisitgco.id; newvisitg.newvisitgdiag (CSV) -> newvisitgdiag.id
- infertilitysheet.menstrualreg/menstrualamount/menstrualdysm -> menstrualreg.id / menstrualamount.id / menstrualdysm.id (cross-module storage column)
- infertilitysheet.localvulva/localvagina/localcx (CSV) -> localvulva.id / localvagina.id / localcx.id
- invests.investcatid -> investcats.id (catalog grouping used by the screens)

## Business Workflows (traced from code)
1. **Open gyna screen** (`gyna.php?patientid=N`): redirects to index if no patient; logs/updates `lastvisit`; auto-creates `maingyna` if missing; loads all non-deleted `gyna` rows DESC by date. Side effect during read: merges `diagnosistxt` into `complaint` and stores.
2. **New visit** (`newrow` AJAX): inserts empty `gyna` row dated today with current `doctorid`; every field then saved column-by-column via `update` (POST id/tableName/colName/value). Diagnosis/complaint multi-selects post JSON arrays stored as sorted CSV. New catalog terms added via `adddiagnosis` (appends new id to the CSV).
3. **Prescriptions**: `drawRow` (type=drug) inserts an empty `gynadrugs` (or `gynasheetdrugs`, or other modules' drug tables — shared endpoint) row for today's date and wife/husband; cascading drug selects (`search`: cat -> name -> type -> dose against `drugs`); `showprescription` lists by distinct date split wife/husband; `printpre` prints (optionally appending latest visit diagnosis when `programesetting.print_diag = 1`); `addprescription` soft-deletes an item.
4. **Investigations**: `addinvestigation` inserts one `gynainvestigation` row per checked invest (grouped UI by `investcats.displayorder`), prints the request; `showInvs` groups by date split wife/husband; results typed into `investresult` via `update`; `del` soft-deletes by date+subject or by id.
5. **Gyna ultrasound**: `loadusrow` (ussimple/uscomplete per `programesetting.ultrasound`) inserts `gynaus` row dated today; ovary follicle modal (`ltovary`/`rtovary`) lists `gynausficils`; `savefocil` stores N follicle rows + updates `lfolliclesno`/`rfolliclesno` inside an explicit transaction (R::begin/commit/rollback); `printus` prints selected `gynaus` rows; `delgynaus` soft-deletes.
6. **Infertility modal** (`getInfdata`): auto-creates one `gynainfertility` and one empty `gynainfertilityplan` per patient; `addnewplan` appends plan lines; `addnewrecord` creates a `gynainfertilitytype` and assigns its id to `gynainfertility.type`; `getalldata`/`deldata` manage the type lookup.
7. **Present history** (cross-module): `addph`/`showphs`/`printPh`/`delPh` write/read `gynaph` joined to `presenthistoryquestions`/`presenthistoryanswers`.
8. **Gyna Sheet** (`gynasheet.php`): auto-creates `gynasheet` AND `infertilitysheet` for the patient; renders menstrual history (menstrualreg/amount/dysm), sexual history, local exam (localvulva/localvagina/localcx) — all saved onto `infertilitysheet` columns; visit rows appended into `newvisitg` via `append` (client posts tablename + parent column name); generic `Add`/`update` save any posted table/column; sheet prescriptions/investigations go to `gynasheetdrugs`/`gynasheetinvest` with identical flows to step 3-4; lookups managed via `getselectajax` (insert title), `getdataselect`/`deldataselect` (list/soft-delete), `deleterow` (hard delete via R::trash).
9. **Legacy sheet** (`gynasheet00.php`): one page combining gyna visits, gynaus list, gynasheet + newvisitg, and the full infertilitysheet child-table set; duplicated code from gyna.php/gynasheet.php.
10. **Infertility module gyna exam**: creating an `infertility` file auto-creates an `infertilitygyna` row (with diagnosis/LMP/invest/notes siblings); `newgyna` appends more rows; fields (vagina/exam/sounding/adenxae) edited inline.
11. **Sonographer report** (`ultrasoundgyna.php`): on `add`, reuses latest unfinished `ultrasoundgyna` row or creates a draft (done=0, ~13 zeroed fields); sonographers fetched from `awusers where positionid = 4`; `showprint` prints and marks done=1; `delRow` soft-deletes (del=1). Table belongs to the ultrasound module.

## ERP Migration Notes (proposed Laravel model names, normalization advice, what to merge/drop)
- **GynaVisit** (`gyna_visits` from `gyna`): belongsTo Patient, Doctor. Replace CSV `diagnosisid`/`complaintid` with pivots `gyna_visit_diagnosis(visit_id, diagnosis_id)` and `gyna_visit_complaint(visit_id, complaint_id)`. Drop `diagnosistxt` (already migrated into complaint by the running code) and `conditions` (use `deleted_at`).
- **Prescriptions**: merge `gynadrugs` + `gynasheetdrugs` into `gyna_prescription_items` (or reuse a clinic-wide prescriptions table — the same row shape repeats in mainantenentaldrugs/followupdrugs/recorddrugs/etc. across modules) with `context` enum (visit|sheet) and `subject` enum (wife|husband) replacing `forhusband`. Real FK to `drugs`; drop denormalized `drugname` or keep as snapshot column intentionally.
- **Investigations**: merge `gynainvestigation` + `gynasheetinvest` into `gyna_investigation_requests` with the same `context`/`subject` pattern; FK to `invests`; structure result (value/unit/result_date) instead of free varchar `investresult`.
- **GynaUltrasound** (`gyna_ultrasounds` from `gynaus`): cast all measurements varchar(50) -> decimal; hasMany **GynaUltrasoundFollicle** (`gyna_ultrasound_follicles` from `gynausficils`) with `side` enum (left|right) replacing int `type`; drop `lfolliclesno`/`rfolliclesno` (derive via count). Keep nullable `antenatal_id` FK if the antenatal link is confirmed in data.
- **GynaSheet** (`gyna_sheets`) + **GynaSheetVisit** (`gyna_sheet_visits` from `newvisitg`): convert `date`/`lmp` varchar -> date; `usvsdtv` -> enum (US|TVS|TVS_3D); pivots for complaints/diagnoses instead of CSV.
- **Lookups**: `menstrualreg`, `menstrualamount`, `menstrualdysm`, `localcx`, `localvagina`, `localvulva`, `newvisitgco`, `newvisitgdiag`, `gynainfertilitytype` are all identical `(id, title, del)` shells, empty in this dump. Consolidate into one polymorphic `lookups` table (`type`, `title`, `is_active`) or seeded enums; keep runtime user-extensibility behind validated endpoints. Note these are stored against `infertilitysheet` columns today — the menstrual/local-exam answers belong logically to the gyna/infertility clinical sheet; decide owning model during data mapping.
- **InfertilityProfile** (`infertility_profiles`): merge `gynainfertility` with the duplicated `gyna.inf_*` columns (one canonical per-patient record; reconcile conflicts by latest date). **InfertilityPlan** from `gynainfertilityplan`. **InfertilityGynaExam** from `infertilitygyna` (FK `infertility_id`) — migrate alongside the Infertility module; remember the phantom `del` column (add it as `deleted_at`).
- **Drop/absorb**: `maingyna` -> two columns (`notes`, `plan`) on a per-patient GynaProfile/GynaSheet rather than its own table. Retire `gynasheet00.php` screen entirely (duplicate legacy).
- **Cross-cutting fixes**: add FKs + indexes on every `patient_id`/`doctor_id`/`*_id` and on (`patient_id`,`date`); unify soft delete (`deleted_at`) replacing `conditions`/`deleted`/`del`(int & NULL semantics differ!); add timestamps; replace the generic write-any-table endpoints (`update`, `Add`, `append`, `getselectajax`, `deleterow`) with per-model FormRequest-validated APIs (current design is a mass-assignment/table-injection risk; `search()` also concatenates SQL strings).