# Module: Laparoscopy & Hysteroscopy (endoscopy)

## Purpose
Documents gynecological endoscopy procedures in two parallel, **unlinked** representations:

1. **Free-text per-patient reports** (`laparoscopy`, `hysteroscopy`) edited inside the Investigations screen (`investigation.php`), one row per procedure date, free-text findings per anatomical region.
2. **Structured per-infertility-sheet records** (`laparoscopyinfertility`, `hysteroscopyinfertility`) inside the Infertility Sheet, where findings AND management/intervention per organ are picked from 26 user-extensible lookup tables (`lapar*`, `copy*`), multi-select values stored as CSV id strings. Includes intervention text and cost.

Additionally owns the surgical incision lookups `jncision` / `midlinejncision` (parent/child cascading dropdowns) consumed by the Operative Details module.

Naming decode:
- `lapar` + `ul`=uterus, `rt`=right tube, `lt`=left tube, `ro`=right ovary, `lo`=left ovary, `pelvis`, `place`=facility. `laparm*` = **m**anagement (intervention) for the same organ.
- `copy` = suffix of hystero-s-**copy**. `copydil`=cervical dilatation, `copyintrod`=scope introduction, `copycx`=cervix/cervical canal, `copycavity`=cavity & fundus, `copyrostium`/`copylostium`=right/left tubal ostium, `copyplace`=facility. `copym*` = management for cervix/cavity/ostia.
- `jncision` = misspelling of "incision"; `midlinejncision` = incision **subtype** child lookup (despite the name it holds children for ALL incision types via `jncisionid`).

## Controllers & Views (file paths)
Controllers (all under `/home/amrtechogate/public_html/obgy/core/controllers/`):
- `investigation.php` (lines ~152-195): auto-creates empty `laparoscopy`/`hysteroscopy` row per patient on first open (sdate=today, patientid, doctorid, del=0); lists rows; joins `awusers` for doctor name. Tabs LAPAROSCOPY / HYSTEROSCOPY in view.
- `infertilitysheet.php` (+ legacy `infertilitysheet00.php`): `loadOne()` loads `hysteroscopyinfertility`/`laparoscopyinfertility` by `infertilitysheetid` (lines 234-238); `append()` creates new child row via AJAX (POST tablename/tablep/id); `update()` generic single-cell save (POST tableName/colName/id/value — no whitelist); `getselectajax()` inserts a new lookup term (`title`, `del=null`); `getselect()`/`getdataselect()` load lookups with `del is null`; `deldataselect()` soft-deletes a term (`del=1`); `deleterow()` deletes sheet child rows.
- `gynasheet.php` / `gynasheet00.php` (lines ~225-228 / 269-271): display same infertility endoscopy rows inside the gyna sheet.
- `sh.php` (lines 200-231, 666-672): print/report rendering; resolves lookup ids — single ids via `R::load`, CSV multi ids via `R::findAll(t, "id in (0$csv)")`.
- `Completesreport.php` (lines 411-440): same resolution for the complete printable report.
- `completereport.php` (lines 426-437): per-patient report; selects non-empty `laparoscopy`/`hysteroscopy` rows joined to `awusers` (doctorid = user_id).
- `fullreport.php` (lines 206-208): checks existence of endoscopy rows in date range for report sectioning.
- `operations.php` (index + search actions, lines 60-250): operations-day report; lists `hysteroscopyinfertility`/`laparoscopyinfertility` LEFT JOIN `infertilitysheet` with string-concatenated search query; resolves `copyplace`/`laparplace`; writes `lastvisit`. NOTE: also queries `hysteroscopy`/`laparoscopy` by nonexistent columns `date`/`infertilitysheetid` — dead/broken code path (inference).
- `operativedetails.php` (lines 282-485, 812): loads `jncision` (del=0) and dependent `midlinejncision` (by `jncisionid`); AJAX `loadjncision()`, `addjncision()`, `addjncisionchild()` manage the cascading lookups.
- `setup.php` (line 195): `jncision`/`midlinejncision` in tables-not-allowed-to-delete list.
- `merge.php`, `excel.php`, `Completesreport.php`: patient-merge and export touchpoints (read-only references).

Views (under `/home/amrtechogate/public_html/obgy/core/views/obgy/`):
- `investigation/investigation.html` (tabs at lines 356-510: laparoscopy fields sdate/uterus/radnexa/ladnexa/pelvis/notes; hysteroscopy fields sdate/cervix/uterine/rostium/lostium/notes; `updateElement` AJAX save, `axedelete2` soft delete).
- `infertilitysheet/add.html` (hysteroscopy block lines 929-1005, headers: Date/Place/Dil/Introd/CX/Cavity-Fundus/R-Ostium/L-Ostium + management row; laparoscopy block lines 1010-1095, headers: Date/Place/UT/RT/LT/RO/LO/Pelvis + Intervention/management row + Cost).
- `infertilitysheet/append.html` (lines 355-470): AJAX-appended row templates for both procedures.
- `operativedetails/jncision.html`, `operativedetails/showdata.html`, `operativedetails/editmodel.html`, `_assets/custom/operativedetails.js`, `_assets/custom/investigation.js`.
- Reports: `reports/complete.html`, `reports/completeprint.html`, `reports/full/fullprint.html`, `reports2/operations.html`, `reports2/operationsprint.html`, `reports2/completesreport(s).html`.

## Tables
No declared FKs anywhere; all relations inferred from controller code. All from `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`.

### laparoscopy (line 8738) — free-text laparoscopy report per patient
| column | type |
|---|---|
| id | int(11) unsigned PK AI |
| sdate | date |
| patientid | varchar(191) |
| del | int(11) unsigned |
| uterus | varchar(191) |
| radnexa | varchar(191) (right adnexa) |
| ladnexa | varchar(191) (left adnexa) |
| pelvis | varchar(191) |
| notes | varchar(191) |
| doctorid | int(11) unsigned |

FKs (inferred): `patientid -> patients.id` (stored as varchar!), `doctorid -> awusers.user_id`. No seed data.

### hysteroscopy (line 7769) — free-text hysteroscopy report per patient
| column | type |
|---|---|
| id | int(11) unsigned PK AI |
| sdate | date |
| patientid | varchar(191) |
| del | int(11) unsigned |
| cervix | varchar(191) |
| uterine | varchar(191) (uterine cavity) |
| rostium | varchar(191) (right ostium) |
| lostium | varchar(191) (left ostium) |
| notes | varchar(191) |
| doctorid | int(11) unsigned |

FKs (inferred): `patientid -> patients.id`, `doctorid -> awusers.user_id`. No seed data.

### laparoscopyinfertility (line 8758) — structured laparoscopy in infertility sheet
| column | type | meaning |
|---|---|---|
| id | int(11) PK AI | |
| infertilitysheetid | int(11) unsigned | -> infertilitysheet.id |
| laparplace | varchar(191) | single id -> laparplace.id |
| laparul | varchar(191) | CSV ids -> laparul (uterus findings) |
| laparrt | varchar(191) | CSV ids -> laparrt (right tube) |
| laparlt | varchar(191) | CSV ids -> laparlt (left tube) |
| laparro | varchar(191) | CSV ids -> laparro (right ovary) |
| laparlo | varchar(191) | CSV ids -> laparlo (left ovary) |
| laparpelvis | varchar(191) | CSV ids -> laparpelvis |
| laparmut | varchar(191) | CSV ids -> laparmut (management uterus) |
| laparmrt | varchar(191) | CSV ids -> laparmrt |
| laparmlt | varchar(191) | CSV ids -> laparmlt |
| laparmro | varchar(191) | CSV ids -> laparmro |
| laparmlo | varchar(191) | CSV ids -> laparmlo |
| laparmpelvis | varchar(191) | CSV ids -> laparmpelvis |
| lapintervention | varchar(191) | free-text intervention |
| date | varchar(191) | procedure date as text |
| coast | varchar(191) | cost (misspelled, text) |

### hysteroscopyinfertility (line 7789) — structured hysteroscopy in infertility sheet
| column | type | meaning |
|---|---|---|
| id | int(11) PK AI | |
| infertilitysheetid | int(11) unsigned | -> infertilitysheet.id |
| copyplace | varchar(191) | single id -> copyplace.id |
| copydil | varchar(191) | single id -> copydil (dilatation) |
| copyintrod | varchar(191) | single id -> copyintrod (introduction) |
| copycx | varchar(191) | CSV ids -> copycx (cervix) |
| copycavity | varchar(191) | CSV ids -> copycavity (cavity-fundus) |
| copyrostium | varchar(191) | single id -> copyrostium |
| copylostium | varchar(191) | single id -> copylostium |
| copymcx | varchar(191) | CSV ids -> copymcx (management cervix) |
| copymcavity | varchar(191) | CSV ids -> copymcavity |
| copymrostium | varchar(191) | CSV ids -> copymrostium |
| copymlostium | varchar(191) | CSV ids -> copymlostium |
| hysintervention | varchar(191) | free-text intervention |
| date | varchar(191) | procedure date as text |
| coast | varchar(191) | cost |

### Lookup tables — identical schema `(id int unsigned PK AI, title varchar(191), del tinyint(1)/int unsigned)`
All EMPTY in this dump (AUTO_INCREMENT=1, no INSERT statements); vocabulary is built at runtime by doctors via `getselectajax` (new terms saved with `del=null`; lists filter `del is null`; delete sets `del=1`).

Laparoscopy lookups (dump lines 8634-8849):
- `laparplace` — procedure facility
- `laparul` — uterus findings
- `laparrt` — right tube findings
- `laparlt` — left tube findings
- `laparro` — right ovary findings
- `laparlo` — left ovary findings
- `laparpelvis` — pelvis findings
- `laparmut` — management/intervention uterus
- `laparmrt` — management right tube
- `laparmlt` — management left tube
- `laparmro` — management right ovary
- `laparmlo` — management left ovary
- `laparmpelvis` — management pelvis

Hysteroscopy lookups (dump lines 4635-4776):
- `copyplace` — procedure facility
- `copydil` — cervical dilatation findings
- `copyintrod` — scope introduction findings
- `copycx` — cervix/cervical canal findings
- `copycavity` — uterine cavity & fundus findings
- `copyrostium` — right tubal ostium findings
- `copylostium` — left tubal ostium findings
- `copymcx` — management cervix
- `copymcavity` — management cavity
- `copymrostium` — management right ostium
- `copymlostium` — management left ostium

Minor type drift among the clones: `del` is `tinyint(1) unsigned` in some, `int(11) unsigned` in others (e.g. `copycx`, `copymcx`, `laparlt`, `laparrt`, `laparmlt`, `laparmpelvis`, `laparplace`).

### jncision (line 8591) — surgical incision type lookup
`(id int PK AI, name varchar(255) NOT NULL, del int NOT NULL)` — SEEDED (AUTO_INCREMENT=25):
Active (del=0): `midline`, `transverse`, `paramedian`, `Pfannistel` [sic], plus junk rows `6666`, `iiiii`, `aaa`, `cccc`, `bbb`. Soft-deleted test junk (del=1): sss, wwwaa, aaaaaqq, eeee, www, ee21, rr1q111, 444, ww, 444444, ffff, bbb, "لل", ddd, aaaaa.

### midlinejncision (line 9103) — incision subtype child lookup
`(id int PK AI, name varchar(255) NOT NULL, del int NOT NULL, jncisionid int NOT NULL)` — SEEDED (AUTO_INCREMENT=23):
Meaningful rows: `upper`(->midline), `lower`(->midline), `pfnnestiel` [sic](->transverse), `joel cohen`(->transverse), `RT`(->paramedian), `1-T`(->paramedian); remainder is test junk (ccc, rr, ee11, aaa, ww, 22 ...).

## Relationships (inferred, no declared FKs)
- `laparoscopy.patientid` -> `patients.id` (varchar vs int type mismatch)
- `laparoscopy.doctorid` -> `awusers.user_id`
- `hysteroscopy.patientid` -> `patients.id`
- `hysteroscopy.doctorid` -> `awusers.user_id`
- `laparoscopyinfertility.infertilitysheetid` -> `infertilitysheet.id`
- `hysteroscopyinfertility.infertilitysheetid` -> `infertilitysheet.id`
- `laparoscopyinfertility.laparplace` -> `laparplace.id` (single)
- `laparoscopyinfertility.{laparul,laparrt,laparlt,laparro,laparlo,laparpelvis,laparmut,laparmrt,laparmlt,laparmro,laparmlo,laparmpelvis}` -> matching lookup `.id` (CSV multi-valued)
- `hysteroscopyinfertility.{copyplace,copydil,copyintrod,copyrostium,copylostium}` -> matching lookup `.id` (single)
- `hysteroscopyinfertility.{copycx,copycavity,copymcx,copymcavity,copymrostium,copymlostium}` -> matching lookup `.id` (CSV multi-valued)
- `midlinejncision.jncisionid` -> `jncision.id`
- `operativedetails.jncisionid` -> `jncision.id` (cross-module: operations)
- `operativedetails.midlineid` -> `midlinejncision.id` (cross-module: operations)
- `infertilitysheet.patientid` -> `patients.id` (transitive path from structured endoscopy rows to patient)
- `lastvisit.patientid` -> `patients.id` (written by operations.php when opening patient ops view)

## Business Workflows (traced from code)
1. **Free-text path (investigation.php index)**: on opening a patient's Investigations screen, if no non-deleted `laparoscopy` row exists, an empty one is auto-inserted (sdate=today, patientid, doctorid from session, del=0); same for `hysteroscopy`. Tabs render all rows newest-first with doctor name from `awusers`. Each input is an `updateElement` → POST to generic `update` action (load bean, set column, store). Delete buttons set `del=1`. Reports (`completereport.php`) later filter out rows where ALL clinical fields are NULL — compensating for the auto-created empties.
2. **Structured path (infertilitysheet.php / gynasheet.php)**: the infertility sheet's Investigations area has "Hysteroscopy" / "Laparoscopy" add links → AJAX `append` (dispense bean of `hysteroscopyinfertility`/`laparoscopyinfertility`, set `infertilitysheetid`, date=today) → returns row HTML from `infertilitysheet/append.html`. Each row = 2 visual rows: findings (place + per-organ selects) and management (intervention text + per-organ management selects + cost). Selects are populated via `getselect` (terms where `del is null`); new clinical terms added inline via `getselectajax`; terms removed via `deldataselect` (del=1); whole record removed via `deleterow`.
3. **Operations report (operations.php)**: index lists today's procedures; `search`/`searchresult` build `$searchQuery` by string concatenation (date range, patientid) and run `hysteroscopyinfertility`/`laparoscopyinfertility` LEFT JOIN `infertilitysheet`, then hydrate patient via `infertilitysheet.patientid` and place names. Writes/updates `lastvisit`. The parallel queries against `hysteroscopy`/`laparoscopy` use columns `date` and `infertilitysheetid` that don't exist in those tables → broken/dead branch (inference).
4. **Reporting/printing**: `sh.php` and `Completesreport.php` re-read both structured tables per infertility sheet and resolve every lookup id (CSV expansion via `id in (0$csv)`); `fullreport.php` gates report sections on existence of endoscopy rows in a date span; `completereport.php` prints free-text rows with doctor names.
5. **Incision lookups (operativedetails.php)**: operative details form shows `jncision` dropdown (del=0); selecting one AJAX-loads its `midlinejncision` children (`loadjncision`); `addjncision`/`addjncisionchild` insert new types inline; `setup.php` protects both tables from deletion in the generic table manager.

## ERP Migration Notes
Proposed Laravel models:
- **`EndoscopyProcedure`** (`endoscopy_procedures`): unify all 4 record tables. Columns: `patient_id` FK, `infertility_sheet_id` nullable FK, `type` enum(laparoscopy, hysteroscopy), `performed_at` date, `place_id` FK, `intervention` text, `notes` text, `cost` decimal(10,2), `doctor_id` FK, timestamps, softDeletes.
- **`EndoscopyFinding`** (`endoscopy_findings`): replaces the 26 CSV columns. Columns: `procedure_id` FK, `site` enum(uterus, right_tube, left_tube, right_ovary, left_ovary, pelvis, cervix, cavity_fundus, right_ostium, left_ostium, dilatation, introduction, right_adnexa, left_adnexa), `category` enum(finding, management), `term_id` nullable FK, `free_text` nullable. Multi-select becomes multiple rows (or a pivot `endoscopy_finding_term`).
- **`EndoscopyTerm`** (`endoscopy_terms`): merge all 26 identical lookup tables. Columns: `scope` enum(laparoscopy, hysteroscopy), `site`, `category` (finding/management/place), `title`, `is_active`. Source table name maps deterministically to (scope, site, category).
- **`IncisionType`** (`incision_types`, self-referencing `parent_id`): merge `jncision` + `midlinejncision`; belongs to the Operations module (`operative_details.incision_type_id`, `incision_subtype_id`). Cleanse junk seed rows (keep midline/transverse/paramedian/Pfannenstiel + real children; fix spellings) before migration.

Migration/normalization advice:
- Import free-text `laparoscopy`/`hysteroscopy` rows only where at least one clinical field is non-null (replicate completereport.php filter) to drop auto-created empties; map free-text region fields into `EndoscopyFinding.free_text` rows.
- Explode CSV id columns into finding rows; cast `patientid` varchar → int; parse `date` varchar and `coast` varchar with cleanup pass; normalize `del` (0/1/null) → `deleted_at`.
- Replace the generic `update` endpoint (arbitrary table/column write — mass-assignment/SQLi risk) with dedicated REST resources + FormRequest validation; replace `id in (0$csv)` and concatenated search queries (operations.php SQLi) with Eloquent whereIn/parameter binding.
- Drop legacy duplicates after migration: `gynasheet00.php`/`infertilitysheet00.php` read paths, the broken operations.php branch, and all 26 lookup tables + 4 record tables.
- Keep inline vocabulary growth as a feature (doctors add terms on the fly) but gate it with permissions and dedupe (case-insensitive unique index on scope+site+category+title).