# Module: Antenatal Care (antenatal)

## Purpose
Full pregnancy follow-up for an OB/GYN clinic: EDD calculation from LMP (Naegele's rule, hardcoded `LMP + 9 months + 7 days`), ANC sheets, per-visit vitals, per-pregnancy drugs/investigations, antenatal ultrasound records, first-trimester (T-scan) and anomaly (TT-scan) scan tracking with auto-booking into the 4D scan waiting list, past pregnancy-loss history (EPC), and reports (expected deliveries, EPC by date, risk type, termination type).

IMPORTANT ARCHITECTURAL FACT: the system contains **two parallel, duplicated ANC implementations**:
1. "ANC Sheet" path: `ancsheet` + `ancnewvisit` + `ancsheetdrugs` + `ancsheetinvest` (controller `ancsheet.php`, legacy copy `ancsheet00.php`).
2. "Antenatal Visit" path: `mainantenental` + `antenalvisit` + `mainantenentaldrugs` + `mainantenentalinvest` + `mainantenentalus` (controller `antenalvisit.php`).
Both can hold data for the same pregnancy.

## Controllers & Views (file paths)
- `/home/amrtechogate/public_html/obgy/core/controllers/ancsheet.php` — main ANC sheet (auto-create sheet, eedlmp EDD calc, ancnewvisit append, drugs/invest CRUD + print, end-pregnancy, archive, history, dynamic lookup CRUD via `getselectajax`/`getdataselect`).
- `/home/amrtechogate/public_html/obgy/core/controllers/ancsheet00.php` — older duplicate of ancsheet.php (uses `Ancsheet/` view dir, no 4D integration). Dead/legacy.
- `/home/amrtechogate/public_html/obgy/core/controllers/antenalvisit.php` — parallel antenatal module on `mainantenental` (auto gravida count from `phobstetric` + `patients` counters, visits, US rows, drugs/invest, end-preg `done=1`, archive by `g`, pregdetail).
- `/home/amrtechogate/public_html/obgy/core/controllers/edd.php` — report: `ancsheet` rows with `sheetedd` = today / in range (+ per-patient filter); also gestational week calc.
- `/home/amrtechogate/public_html/obgy/core/controllers/expected.php` — report: `mainantenental` rows with `eed` = today / in range, `done=0`.
- `/home/amrtechogate/public_html/obgy/core/controllers/risktype.php` — report: patients by `patients.risktype` (raw SQL concat — SQLi).
- `/home/amrtechogate/public_html/obgy/core/controllers/epc.php` — report: `wifeepc` by `wifeepcdate`, joins `infertilitysheet` -> `patients`, resolves wifeepctype/ttt/obst lookups.
- `/home/amrtechogate/public_html/obgy/core/controllers/termination.php` — report: `phobstetric` joined to `patients` by `obstermination` type (raw SQL concat — SQLi).

Views: `/home/amrtechogate/public_html/obgy/core/views/obgy/ancsheet/` (add, anchistory, append, archive, select, delselect, showInvestigation, showPrescriptions, newrowdrugedit, newrowUS), `/home/amrtechogate/public_html/obgy/core/views/obgy/antenalvisit/` (add, archive, pregdetail, newrow, newrowUS, editmodel, showInvestigation, showPrescriptions, newrowdrugedit), `/home/amrtechogate/public_html/obgy/core/views/obgy/reports/` (expected*, risktype*, termination*), `/home/amrtechogate/public_html/obgy/core/views/obgy/reports2/` (edd*, epc*). Print templates reused from `gyna/print.html`, `gyna/printinv.html`.

Schema source: `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql` (lookup seed data only present in older dumps `obgy_app_20-04-2024.sql` / `obgy_app_22-12-2021.sql`).

## Tables

### ancsheet — pregnancy master file (ANC Sheet path)
Columns: `id` int PK AI; `patientid` int unsigned; `endpreg` int unsigned (0=active, 1=ended/archived); `sheetlmp` varchar(191); `sheetedd` varchar(191); `sheetlast` varchar(191); `placenta` varchar(191); `sheetnte` varchar(191); `tscandate/tscanw/tscanresult/tscanreport` varchar(191) (first-trimester scan); `ttscandate/ttscanw/ttscanresult/ttscanreport` varchar(191) (anomaly scan); `historytoday` datetime (set at end-pregnancy); `obstgn/obstpn/obstepc` int unsigned (G/P/EPC snapshot copied from `infertilitysheet` at end-pregnancy); `importantnote` varchar(191); `4d_list_id` int default 0.
Inferred FKs: `patientid -> patients.id`; `4d_list_id -> op_4d_list.id`; `placenta` holds title text chosen from `placenta` lookup (stored as text, not id — by `getselectajax` select).

### ancnewvisit — ANC follow-up visit row
Columns: `id` int PK AI; `ancsheetid` int unsigned; `newvisitdate` varchar(191); `newvisitw` varchar(191) (gestational age "12W 3D", recomputed from `ancsheet.sheetlmp` on every page load and stored back); `usn` varchar (US number, lookup `usn`); `usplace` varchar (lookup `usplace`); `usaf` varchar (amniotic fluid); `newvisitbw` varchar (body weight); `newvisitbp` varchar (blood pressure); `newvisitnote`, `newvisitnote2` varchar; `usefw` varchar (estimated fetal weight); `usbiom` varchar (biometry); `plan` varchar.
Inferred FK: `ancsheetid -> ancsheet.id`.

### ancsheetdrugs — prescriptions on ANC sheet
Columns: `id` int PK AI; `patientid` int; `ancsheetid` int; `date` date; `drugid` varchar(150); `drugtype` varchar(100); `drugdos` varchar(255); `deleted` int default 0; `doctorid` int; `drugname` varchar(191) (denormalized copy from drugs); `forhusband` int unsigned (0=wife, 1=husband); `recepittmpid` int default 0; `recepitdrugid` int default 0.
Inferred FKs: `ancsheetid -> ancsheet.id`; `drugid -> drugs.id`; `doctorid -> awusers.user_id`; `patientid -> patients.id`; `recepittmpid/recepitdrugid` -> receipt module.

### ancsheetinvest — investigation requests on ANC sheet
Columns: `id` int PK AI; `patientid` int; `ancsheetid` int; `date` date; `investid` int; `investresult` varchar(250); `deleted` int default 0; `doctorid` int; `forhusband` int.
Inferred FKs: `ancsheetid -> ancsheet.id`; `investid -> invests.id`; `doctorid -> awusers.user_id`.

### mainantenental — pregnancy master (Antenatal Visit path; "antenatal" misspelled)
Columns: `id` int unsigned PK AI; `patientid` varchar(191) (TYPE MISMATCH — varchar vs int elsewhere); `doctorid` int unsigned; `lmp` date; `eed` date (EDD = lmp+9m+7d); `notes` varchar(191); `done` int unsigned (0=current, 1=ended); `ivf` date (= lmp+9m-7d, auto-set); `g` int unsigned (gravida, auto-computed: count(`phobstetric` by obstermination) + `patients.pno/cs/svd/ab/ectopic/vmodel` counters + 1); `sysdate` date; `hb` double; `sex` varchar(255).
Inferred FKs: `patientid -> patients.id`; `doctorid -> awusers.user_id`.

### antenalvisit — antenatal visit row (parallel path)
Columns: `id` int PK AI; `patientid` int unsigned; `doctorid` int unsigned; `antenaldate` date; `complaint` text; `wt` varchar(191); `bp` varchar(191); `pulse` varchar(255); `notes` varchar(191); `conditions` int unsigned (soft-delete flag: 0=active, 1=deleted); `mainantenentalid` int unsigned; `diagnosisid` varchar(191) (CSV of `diagnosisant` ids); `complaintid` varchar(191) (CSV of `complaintant` ids).
Inferred FKs: `mainantenentalid -> mainantenental.id`; `patientid -> patients.id`; `doctorid -> awusers.user_id`.

### mainantenentaldrugs — prescriptions (parallel path; structure identical to ancsheetdrugs)
Columns: `id` int PK AI; `patientid` int; `mainantenatalid` int (NOTE different spelling from table name); `date` date; `drugid` varchar(150); `drugtype` varchar(100); `drugdos` varchar(255); `deleted` int; `doctorid` int; `drugname` varchar(191); `forhusband` int unsigned; `recepittmpid` int; `recepitdrugid` int.
Inferred FK: `mainantenatalid -> mainantenental.id`; `drugid -> drugs.id`.

### mainantenentalinvest — investigations (parallel path; identical to ancsheetinvest)
Columns: `id` int PK AI; `patientid` int; `mainantenatalid` int; `date` date; `investid` int; `investresult` varchar(250); `deleted` int; `doctorid` int; `forhusband` int.
Inferred FK: `mainantenatalid -> mainantenental.id`; `investid -> invests.id`.

### mainantenentalus — antenatal ultrasound record
Columns: `id` int PK AI; `mainantenatalid` int; `doctorid` int; `patientid` int; `date` date; `nga` varchar(100) (GA by US); `gs` (gestational sac); `crl`; `fhr`; `bpd`; `fl`; `placenta`; `ai` (amniotic index); `fwt` (fetal weight) — all varchar(100) NOT NULL; `notes` text; `deleted` int.
Inferred FK: `mainantenatalid -> mainantenental.id`; `doctorid -> awusers.user_id`. Last row's `placenta` is shown as "current placenta" on the visit screen; `instruction.php` and `completereport.php` also read it.

### placenta — lookup: placental site (used by `ancsheet.placenta` select)
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` int unsigned.
Seeded values (older dump): 'ant', 'post high', 'post high rt lat', 'ant high', 'fundal ant to lt', 'fundal', 'Ant fundal', 'post placenta', 'ant low lying', 'pl pr compl cent to rt', 'post', 'post low to lt', '1st->ant / 2nd-> post', 'ant low to lt' + several NULL rows and deleted Arabic test rows. Dirty data.

### pla2cen — lookup: ICSI place (BELONGS TO IVF MODULE)
Columns: `id` int unsigned PK AI; `title` int(11) unsigned (TYPE BUG — should be varchar); `del` tinyint.
No seed data in any dump. Used only by `ivfsheet.php` (`ivfsheet.pla2cen -> pla2cen.id`, rendered as `icsiplace`) and `sh.php`.

### anprotocol — lookup: stimulation protocol (LEGACY/ORPHANED, IVF domain)
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` tinyint.
Seeded values: 'long ag', 'antag', 'short ag'. The column `ivfsheet.anprotocol` is resolved in code against table `icsiprotocol`, NOT `anprotocol` — this table appears abandoned (استنتاج).

### antype — lookup: infertility type (IVF domain)
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` int unsigned.
Seeded values: '1ry', '2ry', 'sex selection', '2ry inf + sex selec' (+1 deleted NULL). Used by `ivfsheet.antype` (sh.php, Ivfstatistics.php).

### antypes — lookup: cycle type (IVF domain)
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` tinyint.
Seeded values: 'fresh', 'frozen'. Used by `ivfsheet.antypes` (sh.php).

### wifeepc — past pregnancy-loss / early pregnancy complication record (EPC)
Columns: `id` int PK AI; `infertilitysheetid` int unsigned; `wifeepcttt` varchar(191) (treatment lookup id); `wifeepcobst` varchar(191) (provider lookup id); `wifeepctype` varchar(191) (loss type lookup id); `wifeepcw` varchar(191) (gestational weeks at loss); `histopath` varchar(191); `wifeepcp` varchar(191); `wifeepcepc` varchar(191); `coast` varchar(191) (cost, misspelled); `wifeepcdate` varchar(191).
Inferred FKs: `infertilitysheetid -> infertilitysheet.id`; `wifeepctype -> wifeepctype.id`; `wifeepcttt -> wifeepcttt.id`; `wifeepcobst -> wifeepcobst.id`. Edited inline from ancsheet/gynasheet/infertilitysheet screens; reported by epc.php.

### wifeepctype — lookup: pregnancy-loss type
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` int unsigned.
Seeded values: 'EPL' (early pregnancy loss), 'A T1' (abortion 1st trimester), 'A T2', 'C VM' (complete vesicular mole), 'P VM' (partial), 'EP' (ectopic pregnancy).

### wifeepcttt — lookup: pregnancy-loss treatment
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` int unsigned.
Seeded values: 'conservative', 'medical (miso)', 'Medical (MTX)', 'D&C', 'laparoscopy otomy', 'Laparoscopy ectomy', 'Laparotomy Otomy', 'Laparotomy ectomy', 'medical+ d&c'.

### wifeepcobst — lookup: obstetrician/place that managed the loss
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` tinyint.
Seeded values: ~30+ free-text rows mixing Arabic doctor names (د.علي المصري, د.هاله هدايه...), hospitals (م. التخصصي), and countries ('s.arabia', السعودية) plus junk ('rtyrt').

### wifeobst — lookup: obstetrician/place of prior deliveries (children)
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` int unsigned.
Seeded values: ~68 free-text rows, same mix of doctor names/hospitals/countries, with duplicated soft-deleted rows. Consumed by `awifep.wifeobst` as CSV multi-select (`R::findAll('wifeobst', "id in (0$data->wifeobst)")` in Deliveries.php / Completesreport.php).

### wifemodeofd — lookup: mode of delivery
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` tinyint.
Seeded values: 'CS', 'sVD', 'nVD'. Consumed by `awifep.wifemodeofd`.

### wifetypeofd — lookup: delivery term
Columns: `id` int unsigned PK AI; `title` varchar(191); `del` tinyint.
Seeded values: 'FT' (full term), 'PT' (preterm). Consumed by `awifep.wifetypeofd`; infertilitysheet.php counts FT/PT children by title match (`R::findOne('wifetypeofd', 'title = "FT"')`).

Related (NOT assigned but central): `awifep` — children/prior deliveries table under `infertilitysheet` with columns `wifesex, wifemodeofd, wifeobst, wifetypeofd, name, date, wifew, wifel, duration, stopped, comment, awifepmethod, wifeage`.

## Relationships (explicit list)
- ancsheet.patientid -> patients.id
- ancsheet.4d_list_id -> op_4d_list.id (and op_4d_list.ancsheetid -> ancsheet.id, bidirectional)
- ancnewvisit.ancsheetid -> ancsheet.id
- ancsheetdrugs.ancsheetid -> ancsheet.id; ancsheetdrugs.drugid -> drugs.id; ancsheetdrugs.doctorid -> awusers.user_id; ancsheetdrugs.patientid -> patients.id
- ancsheetinvest.ancsheetid -> ancsheet.id; ancsheetinvest.investid -> invests.id (invests.investcatid -> investcats.id)
- mainantenental.patientid -> patients.id (varchar column); mainantenental.doctorid -> awusers.user_id
- antenalvisit.mainantenentalid -> mainantenental.id; antenalvisit.diagnosisid -> diagnosisant.id (CSV); antenalvisit.complaintid -> complaintant.id (CSV)
- mainantenentaldrugs.mainantenatalid -> mainantenental.id; .drugid -> drugs.id
- mainantenentalinvest.mainantenatalid -> mainantenental.id; .investid -> invests.id
- mainantenentalus.mainantenatalid -> mainantenental.id; .doctorid -> awusers.user_id
- wifeepc.infertilitysheetid -> infertilitysheet.id; wifeepc.wifeepctype -> wifeepctype.id; wifeepc.wifeepcttt -> wifeepcttt.id; wifeepc.wifeepcobst -> wifeepcobst.id
- awifep.wifemodeofd -> wifemodeofd.id; awifep.wifetypeofd -> wifetypeofd.id; awifep.wifeobst -> wifeobst.id (CSV multi)
- ancsheet.placenta <- placenta.title (value copied as text via select)
- registeration.ancsheetid -> ancsheet.id; investigations.ancsheetid / ovst.ancsheetid -> ancsheet.id (legacy attachments shown in sheet)
- IVF module: ivfsheet.antype -> antype.id; ivfsheet.antypes -> antypes.id; ivfsheet.pla2cen -> pla2cen.id; ivfsheet.anprotocol -> icsiprotocol.id (anprotocol table orphaned)
- Gravida computation reads phobstetric (obstermination in 1..5) + patients.pno/cs/svd/ab/ectopic/vmodel; reports read phobstericterminate / phobstericterplace lookups; patients.risktype for risk report

## Business Workflows (traced from code)
1. **Open ANC sheet** (`ancsheet.php::index`): loads patient; ensures one active `ancsheet` (`endpreg=0`) exists else creates it; ensures `infertilitysheet` exists; records screen entry in `lastvisit`; loads invest catalog grouped by `displayorder`; loads visits, legacy `investigations`/`ovst` attachments, `registeration` bookings, EPC and children (`awifep`) from infertility sheet.
2. **EDD calc** (`eedlmp()`): entering `sheetlmp` sets `sheetedd = lmp + 9 months + 7 days`; entering `sheetedd` back-computes lmp. First EDD set auto-creates `op_4d_list` row and stores `4d_list_id`. Field-by-field AJAX persistence (`Add()` generic table/column writer). Setting `tscandate`/`ttscandate` flips `op_4d_list.t11/t21 = 1`.
3. **New visit** (`append()`): inserts `ancnewvisit` with today's date and GA = weeks+days from lmp. GA strings are recomputed and re-stored on every page render (`loadAnc`).
4. **Prescriptions**: `getprescription()` loads today's `ancsheetdrugs` (wife/husband split via `forhusband`); rows added/updated inline; `showprescription` groups by date; `printpre` renders `gyna/print.html`. Soft delete via `deleted=1`.
5. **Investigations**: `addinvestigation()` bulk-inserts checked catalog items into `ancsheetinvest` and prints request; `showInvs` groups by date; results typed into `investresult`.
6. **End pregnancy** (`endpreg()`): sets `endpreg=1`, copies `obstg/obstp/obstepc` from infertilitysheet into the sheet, stamps `historytoday`. `archive()`/`historys()` browse ended sheets.
7. **Parallel path** (`antenalvisit.php::index`): ensures `mainantenental` (`done=0`) exists, recomputes `g` from phobstetric+patients counters each load; `newrow()` adds `antenalvisit`; `update()` handles lmp/eed bidirectional calc (also sets `ivf` date), CSV diagnosis/complaint lists, and "placentaOut" shortcut which upserts today's `mainantenentalus` row; `loadusrow()` adds US rows; own drugs/invest functions mirror ancsheet's; `endpreg()` sets `done=1`; `archive()`/`pregdetail()` browse by `g`.
8. **Reports**: `edd.php` (ancsheet.sheetedd = today / between dates / per patient, prints); `expected.php` (mainantenental.eed, done=0); `epc.php` (wifeepc by wifeepcdate, joins infertilitysheet for patient, resolves 3 lookups); `risktype.php` (patients by risktype — raw SQL); `termination.php` (phobstetric join patients by obstermination — raw SQL).
9. **Lookup management**: `getselectajax` inserts a new row (title) into any client-named lookup table; `getdataselect`/`deldataselect` list/soft-delete lookup values; `deleterow` hard-deletes child rows (R::trash).

## ERP Migration Notes
Proposed Laravel models (module `Antenatal`):
- `Pregnancy` (table `pregnancies`): merge `ancsheet` + `mainantenental`. Fields: patient_id FK, lmp DATE, edd DATE, ivf_reference_date, gravida, status enum(active,ended), ended_at, hb, fetus_sex, placenta_site_id FK, important_note, g_snapshot/p_snapshot/epc_snapshot. Migration must match the two legacy rows per pregnancy by patient + lmp/done flags; treat varchar dates and `0000-00-00` carefully.
- `AntenatalVisit` (merge `ancnewvisit` + `antenalvisit`): pregnancy_id, visit_date DATE, weight, bp, pulse, complaint, notes, plan, doctor_id. GA computed accessor — do NOT store it. Pivots `antenatal_visit_diagnoses`, `antenatal_visit_complaints` replacing CSV columns.
- `AntenatalUltrasound` (from `mainantenentalus` + ancsheet T/TT-scan columns): pregnancy_id, scan_type enum(routine,first_trimester,anomaly,four_d), date, ga, gs, crl, fhr, bpd, fl, placenta, afi, efw, result, report, doctor_id. Link 4D bookings (`op_4d_list`) here.
- Unified `Prescription`/`PrescriptionItem` and `InvestigationRequest` (+ result) with morphable or pregnancy_id context replacing the 4 duplicated tables (`ancsheetdrugs`, `mainantenentaldrugs`, `ancsheetinvest`, `mainantenentalinvest`); keep for_husband flag as subject enum(patient,husband); FK to drugs/invests catalogs and users.
- `PregnancyLoss` (from `wifeepc`): re-parent to patient_id directly (resolve via infertilitysheet.patientid during migration); loss_type_id, treatment_id, provider_id, gestational_weeks, histopathology, cost DECIMAL, occurred_on DATE.
- Lookups: `PregnancyLossType` (seed EPL/AT1/AT2/CVM/PVM/EP), `PregnancyLossTreatment`, `DeliveryMode` enum (CS/SVD/NVD), `DeliveryTerm` enum (full_term/preterm), `PlacentaSite` (clean `placenta` seed: drop NULL/free-text dups), `ExternalProvider` merging `wifeobst` + `wifeepcobst` (type: doctor/hospital/abroad; heavy dedup needed).
- Move `antype`, `antypes`, `pla2cen` to the IVF module migration; DROP `anprotocol` after verifying no live FK data (code uses `icsiprotocol`).
- Drop dead code path `ancsheet00.php`; keep only one workflow.
- Fix at migration time: varchar->DATE conversions ('Y/m/d' strings), `mainantenental.patientid` varchar->int, unify soft deletes (`del`/`deleted`/`conditions`/`endpreg`/`done`) to `deleted_at` + status, add real FKs/indexes (none exist), centralize EDD in an `EddCalculator` service, and replace the generic table/column AJAX writers and string-concatenated report SQL (risktype.php, termination.php) with validated endpoints to eliminate SQL injection and mass-assignment-by-table-name vulnerabilities.