# Module: Clinical Examination & Diagnosis (examination)

## Purpose
Per-patient general clinical examination records (vitals + per-body-system findings), configurable lookup catalogs for examination findings (head/neck, chest/heart, abdomen, pelvis, extremities, local gynecological exam, breast, hirsutism, thyroid), a configurable Present History Q&A engine (questions + predefined answers, answered per visit), gyna/antenatal diagnosis catalogs, and a general-anesthesia subtype catalog consumed by the operations module.

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/examination.php` — examination CRUD (index, addNewRow, update, delData, addexitem)
- `/home/amrtechogate/public_html/obgy/core/controllers/addpresenthistory.php` — present-history Q&A catalog admin (index, addnew, update, del, getbyquesid, addtofavorite)
- Views:
  - `/home/amrtechogate/public_html/obgy/core/views/obgy/examination/add.html` (main grid; inline AJAX cell editing via `updateElement` class with `data-tableName`/`data-colName`)
  - `/home/amrtechogate/public_html/obgy/core/views/obgy/examination/newExamRow.html` (AJAX row fragment)
  - `/home/amrtechogate/public_html/obgy/core/views/obgy/addpresenthistory/show.html`, `answers.html`
- Consumers (other modules using this module's tables):
  - `core/controllers/gyna.php` — present-history rendering/saving (writes `gynaph`), `diagnosis` catalog usage, `adddiagnosis`, `deldiagnosis`
  - `core/controllers/antenalvisit.php` — `diagnosisant` catalog usage
  - `core/controllers/operativedetails.php` — `generalanasthesa`
  - `core/controllers/sh.php`, `Completesreport.php`, `combinedreport.php` — resolve lookup IDs for printed reports
  - `core/views/obgy/infertilitysheet/add.html`, `core/views/obgy/gynasheet/add.html` — selects for `generalbreast`, `generalhirsutism`, `generalthyroid`, `examinationlocalse` writing into table `infertilitysheet`
  - `core/controllers/setup.php` — DB maintenance lists (`diagnosis`/`diagnosisant` allowed to truncate; `generalanasthesa` protected)

## Tables
Schema source: `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`. No declared foreign keys anywhere; all FKs below are inferred. ALL lookup tables in this dump are EMPTY (`AUTO_INCREMENT=1`, no INSERT statements) — actual clinical vocabulary lives only in the production DB.

### examination
Per-patient general examination record (one row per exam session).
| Column | Type |
|---|---|
| id | int(11) unsigned PK AI |
| patientid | varchar(191) NULL |
| doctorid | int(11) unsigned NULL |
| sysdate | varchar(191) NULL (entry date) |
| examinationdate | varchar(191) NULL |
| weight | varchar(191) NULL |
| height | varchar(191) NULL |
| bmi | varchar(191) NULL (JS-computed, readonly field) |
| b_p | varchar(191) NULL (blood pressure, masked input) |
| pulse | varchar(191) NULL |
| rgb | varchar(191) NULL (random blood glucose, inferred from UI position) |
| headandneck | varchar(191) NULL (free text OR examinationhead id — mixed) |
| chestandheart | varchar(191) NULL (free text OR examinationchest id) |
| abdomen | varchar(191) NULL (free text OR examinationabdomen id) |
| pelvis | varchar(191) NULL (free text OR examinationpelvis id) |
| extremitis | varchar(191) NULL (free text OR examinationextremitis id) |
| del | int(11) unsigned NULL (soft delete) |
| bp2 | int(11) NULL (second BP value) |

Inferred FKs: `patientid -> patients.id` (type mismatch: varchar vs int), `doctorid -> awusers.user_id`.

### examinationhead / examinationchest / examinationabdomen / examinationpelvis / examinationextremitis
Five structurally identical finding lookup catalogs, one per body system:
`id int(11) unsigned PK AI, name varchar(191) NULL, del int(11) unsigned NULL`.
Loaded only when the corresponding `programesetting` flag (`head`, `chest`, `abdomen`, `pelvis`, `extremitis`) != 1; otherwise the UI shows free-text input. `addexitem()` inserts a new row and stores its numeric id into the matching `examination` column (same column that otherwise holds free text). Seed data: none in dump.

### examinationlocalse
Local (gynecological) examination findings lookup: `id int(11) unsigned PK AI, title varchar(191) NULL, del tinyint(1) unsigned NULL`. Multi-select; selected ids stored as CSV in `infertilitysheet.examinationlocalse` (used by infertilitysheet view; resolved in sh.php/Completesreport.php via `R::findAll('examinationlocalse', "id in (0$csv)")`). Seed data: none in dump.

### generalbreast
Breast exam findings lookup: `id int(11) unsigned PK AI, title varchar(191) NULL, del tinyint(1) unsigned NULL`. Multi-select; CSV ids in `infertilitysheet.generalbreast` (selects present in both gynasheet/add.html and infertilitysheet/add.html, both with `data-table="infertilitysheet"`). Seed data: none in dump.

### generalhirsutism
Hirsutism grading lookup: `id int(11) unsigned PK AI, title varchar(191) NULL, del tinyint(1) unsigned NULL`. Single select; id stored in `infertilitysheet.generalhirsutism` (resolved with `R::load`). Seed data: none in dump.

### generalthyroid
Thyroid exam findings lookup: `id int(11) unsigned PK AI, title varchar(191) NULL, del tinyint(1) unsigned NULL`. Single select; id stored in `infertilitysheet.generalthyroid`. Seed data: none in dump.

### generalanasthesa
General-anesthesia subtype/detail catalog (child of `anasthesa`): `id int(11) PK AI, name varchar(255) NOT NULL, del int(11) NOT NULL, anasthesaid int(11) NOT NULL`.
Inferred FK: `anasthesaid -> anasthesa.id`. Consumed by `operativedetails.php` (`findall('generalanasthesa','anasthesaid = ? and del = 0', [$operativedetails->anasthesaid])`); rows are creatable/editable from the operative-details screen. Listed in setup.php as NOT allowed to truncate. Seed data: none in dump.

### diagnosis
Gyna-visit diagnosis catalog: `id int(11) unsigned PK AI, name varchar(191) NULL, conditions int(11) unsigned NULL, title varchar(191) NULL, del tinyint(1) unsigned NULL`.
`conditions` is the de-facto soft-delete flag: lists filter `conditions = 0`; `gyna.php::deldiagnosis()` sets `conditions = 1`; `adddiagnosis()` inserts with `conditions = 0`. The `del` and `title` columns appear unused by the traced code. Selected diagnoses stored as sorted CSV of ids in `gyna.diagnosisid` (text column; JSON array from UI decoded, sorted, joined with commas). Seed data: none in dump.

### diagnosisant
Antenatal diagnosis catalog: `id int(11) unsigned PK AI, name varchar(191) NULL, conditions int(11) unsigned NULL`. Same `conditions`-as-soft-delete pattern. CSV ids stored in `antenalvisit.diagnosisid` (varchar(191)); resolved via `explode(',')` + `R::load('diagnosisant', $id)` for printing. Inline add via `antenalvisit.php::adddiagnosis()`, which also appends the new id to the visit's CSV. Seed data: none in dump.

### presenthistoryquestions
Present-history question (category) catalog: `id int(11) PK AI, name varchar(150) NULL, deleted int(11) NOT NULL DEFAULT 0, displayorder int(11) NOT NULL DEFAULT 0`. Table comment: "cats for invests appear in gyna and antenatal". `displayorder` doubles as UI column-grouping key hardcoded in `gyna.php` (groups: 1-3, 4-6, 7-9, 10-12, >12, =0 "others"; admin list excludes `displayorder = -1`). New question gets `max(displayorder)+1`. Seed data: none in dump.

### presenthistoryanswers
Predefined answers per question: `id int(11) PK AI, presenthistorycatid int(11) NOT NULL, name varchar(150) NULL, favorite int(11) NOT NULL DEFAULT 0, deleted int(11) NOT NULL DEFAULT 0`. Table comment: "invests appear in gyna and antenatal".
Inferred FK: `presenthistorycatid -> presenthistoryquestions.id`. `favorite` toggled via `addtofavorite()`. Seed data: none in dump.

### Runtime companion table (NOT in dump): gynaph
Patient's actual present-history answers per visit date, written by `gyna.php`: columns observed in code: `patientid, date, catid, answerid, doctorid, deleted`. `catid -> presenthistoryquestions.id`, `answerid -> presenthistoryanswers.id`. Absent from the SQL dump — likely created at runtime by RedBeanPHP fluid mode (inference). Must be captured from production DB during migration.

## Relationships (explicit list)
- examination.patientid -> patients.id (varchar->int mismatch)
- examination.doctorid -> awusers.user_id
- examination.headandneck -> examinationhead.id (only when lookup mode; otherwise free text)
- examination.chestandheart -> examinationchest.id (same caveat)
- examination.abdomen -> examinationabdomen.id (same caveat)
- examination.pelvis -> examinationpelvis.id (same caveat)
- examination.extremitis -> examinationextremitis.id (same caveat)
- programesetting.head/chest/abdomen/pelvis/extremitis -> feature toggles controlling lookup vs free text
- infertilitysheet.examinationlocalse -> examinationlocalse.id (CSV multi-value)
- infertilitysheet.generalbreast -> generalbreast.id (CSV multi-value)
- infertilitysheet.generalhirsutism -> generalhirsutism.id
- infertilitysheet.generalthyroid -> generalthyroid.id
- gyna.diagnosisid -> diagnosis.id (CSV multi-value)
- antenalvisit.diagnosisid -> diagnosisant.id (CSV multi-value)
- presenthistoryanswers.presenthistorycatid -> presenthistoryquestions.id
- gynaph.catid -> presenthistoryquestions.id (gynaph not in dump)
- gynaph.answerid -> presenthistoryanswers.id
- gynaph.patientid -> patients.id; gynaph.doctorid -> awusers.user_id
- generalanasthesa.anasthesaid -> anasthesa.id
- operativedetails.anasthesaid -> anasthesa.id (joins generalanasthesa on same key)

## Business Workflows (traced from code)
1. **Examination grid**: `examination.php::index()` requires a patient in session (else redirect), loads `programesetting`, conditionally loads the five system-finding lookups (`del = 0`), and lists all `examination` rows for the patient (`del = 0`, ordered `examinationdate desc, id desc`), resolving each row's doctor name from `awusers`.
2. **Row creation**: "Add" triggers `addNewRow()` which immediately persists an empty `examination` bean (today's date, session user as doctor, all clinical fields '') and returns the `newExamRow.html` fragment — record exists before any data entry.
3. **Cell-by-cell autosave**: every input/select has `updateElement` + `data-tableName`/`data-colName`/`data-id`; blur posts to `update()` which loads the row (by id+patientid for `examination`, by id alone for lookup tables) and stores the single column. BMI is computed client-side (`calcbm` class) and saved readonly. BP uses a mask and two columns (`b_p`, `bp2`).
4. **Inline vocabulary growth**: `addexitem()` inserts a new lookup row (name, del=0) into the POSTed table name and then writes the new numeric id into the specified `examination` column for the current row.
5. **Soft delete**: `delData()` sets `del = 1` on any POSTed table/id.
6. **Present-history catalog admin** (`addpresenthistory.php`): list non-deleted questions (`displayorder != -1`); `addnew()` creates either an answer (when `questionId > 0`, linked via `presenthistorycatid`) or a question (next `displayorder`); `getbyquesid()` returns the answers fragment; `addtofavorite()` flips `favorite`; `del()` sets `deleted = 1`.
7. **Present-history capture** (`gyna.php`): questions fetched in five hardcoded `displayorder` buckets + "others" (`displayorder = 0`); checked answers per question are stored one row each in `gynaph` (patientid, date, catid, answerid, doctorid, deleted=0); review screen groups by distinct date with joins to question/answer names; date-level delete via `UPDATE gynaph SET deleted = 1 WHERE patientid = ? AND date = ?`; printable via `gyna/printph.html`.
8. **Diagnosis capture**: gyna visit multiselect posts a JSON array; controller decodes, sorts, joins to CSV into `gyna.diagnosisid`. Antenatal identical with `diagnosisant` -> `antenalvisit.diagnosisid`. Both allow inline `adddiagnosis` (insert with `conditions = 0`, append id to visit CSV). Catalog "delete" = `conditions = 1`.
9. **Sheet-level general/local exam**: gynasheet and infertilitysheet screens write `generalbreast` (multi, CSV), `generalhirsutism`, `generalthyroid`, `examinationlocalse` (multi, CSV) selections into `infertilitysheet` columns; print controllers (`sh.php`, `Completesreport.php`) resolve them with `findAll(..., "id in (0$csv)")` / `R::load`.
10. **Anesthesia subtypes**: `operativedetails.php` lists `generalanasthesa` filtered by the operation's `anasthesaid`, supports inline add/edit/soft-delete of subtypes.

## Security / Data-Quality Findings
- `examination.php::update()` has NO auth/authorization check and accepts arbitrary `tableName`/`colName` from POST -> arbitrary table/column write (mass assignment). `delData()` and `addexitem()` also take table names from the client (auth-checked but still table-injection prone).
- String-interpolated SQL in report controllers: `"id in (0$infertility->examinationlocalse)"` (sh.php:116, Completesreport.php:327) — SQL injection surface.
- Mixed-content columns: `examination.headandneck` etc. hold free text or lookup ids depending on config history — migration must disambiguate (numeric-only value matching an existing lookup id => FK; else free text).
- CSV multi-value columns (`gyna.diagnosisid`, `antenalvisit.diagnosisid`, `infertilitysheet.generalbreast`, `.examinationlocalse`) violate 1NF.
- Misleading semantics: `diagnosis.conditions` is a soft-delete flag; `diagnosis.del`/`title` unused; three different soft-delete column names across the module (`del`, `deleted`, `conditions`).
- All numerics/dates stored as varchar; `examination.patientid` varchar vs `patients.id` int.
- `gynaph` missing from dump (RedBean fluid-mode runtime table — inference); export from production before migration.

## ERP Migration Notes (Laravel + Angular)
Proposed models/tables:
- `ClinicalExamination` (`clinical_examinations`): `patient_id` FK, `doctor_id` FK (users), `examined_at` date, `weight`/`height`/`bmi` decimal, `bp_systolic`/`bp_diastolic` smallint (map `b_p`+`bp2`), `pulse` smallint, `random_blood_glucose` decimal, SoftDeletes. Keep cell-level autosave UX via PATCH endpoint with a whitelisted field set (replaces the unsafe generic `update()`).
- `ExaminationFindingOption` (`examination_finding_options`): merge the 9 identical lookups (`examinationhead`, `examinationchest`, `examinationabdomen`, `examinationpelvis`, `examinationextremitis`, `examinationlocalse`, `generalbreast`, `generalhirsutism`, `generalthyroid`) into one table with `body_system` enum (head_neck, chest_heart, abdomen, pelvis, extremities, local_gyn, breast, hirsutism, thyroid) + `name` + SoftDeletes.
- `ExaminationFinding` pivot (`examination_findings`): `examination_id` (or polymorphic to sheet), `body_system`, nullable `finding_option_id`, nullable `free_text` — cleanly separates the current mixed text/id columns and the CSV columns on `infertilitysheet`.
- `Diagnosis` (`diagnoses`): merge `diagnosis` + `diagnosisant` with `specialty` enum (gyna, antenatal); migrate `conditions=1` rows to `deleted_at`. Add `visit_diagnosis` pivot (polymorphic `diagnosable` for gyna visit / antenatal visit) replacing CSV `diagnosisid` columns. Consider ICD-10 code column for future coding.
- `HistoryQuestion` (`history_questions`): name, `display_group` (explicit, replacing the hardcoded displayorder bucketing), `sort_order`, SoftDeletes.
- `HistoryAnswerOption` (`history_answer_options`): `history_question_id` FK, name, `is_favorite`, SoftDeletes.
- `PatientHistoryAnswer` (`patient_history_answers`): replaces runtime `gynaph` — `patient_id`, `visit_date`, `history_question_id`, `history_answer_option_id`, `doctor_id`, SoftDeletes.
- `AnesthesiaType` / `AnesthesiaSubtype`: replaces `anasthesa` / `generalanasthesa` (`anesthesia_type_id` FK); consumed by the operations module.
Migration script requirements: type-cast varchar vitals/dates; resolve patientid varchar->int; disambiguate mixed text/id finding columns; explode CSV columns into pivots; export lookup seed data and `gynaph` from production (this dump's lookups are empty). Drop: `diagnosis.del`, `diagnosis.title` (unused), the `conditions` flag pattern. Enforce FormRequest validation + Policies on all endpoints; eliminate client-supplied table/column names entirely.