# 95 — ERP Migration Blueprint (Legacy OB/GYN → Moon ERP Module)

Target: absorb the legacy PHP/Smarty OB/GYN system (312 tables, MySQL, zero FKs) as a new
**nwidart module `Modules/Obgy`** (working name; alt: `Modules/Clinics`) in the existing
Laravel + Angular Moon ERP, reusing the ERP patient master, LIS, inventory/pharmacy,
accounting and auth instead of the legacy cross-database cURL sync.

Net result: **312 legacy tables → ~85 new tables** (≈55 domain models + shared `lookups`,
pivots, and media), with ~40 dead/derived/dev tables dropped outright.

---

## 1. Proposed Module Structure

```
Modules/Obgy
├── Entities/            (grouped by domain below)
│   ├── Patients/        ├── Encounters/      ├── Clinical/
│   ├── History/         ├── Antenatal/       ├── Gynecology/
│   ├── Infertility/     ├── Ivf/             ├── Andrology/
│   ├── Imaging/         ├── Endoscopy/       ├── Operations/
│   └── Board/
├── Http/Controllers/Api (Sanctum-guarded REST, Angular consumer)
├── Services/            (EddCalculator, GravidaFormula, QueueService, CycleDayService…)
├── Events|Listeners/    (PatientRegistered, VisitInvoiced, PrescriptionDispensed,
│                         LabOrderPlaced, LabResultReceived…)
├── Jobs/                (ETL backfills, Excel exports via queues)
└── Database/Migrations|Seeders (lookup seeds exported from PRODUCTION db)
```

### Cross-cutting consolidation patterns

| Pattern | Legacy | New |
|---|---|---|
| Tiny lookup tables (~190 tables, shape `id+title+del`) | one table per dropdown, incl. clone quadruplet `ssemen/sseemen/sseemmen/sseemmeen` | single **`obgy_lookups`** table `(id, domain, parent_id, title_ar, title_en, sort, is_active, deleted_at)` + PHP Enums for clinically fixed sets (blood type, delivery mode CS/SVD/NVD, delivery term FT/PT, loss types) |
| Sheet + `*drugs` clone (10 tables) | ancsheetdrugs, mainantenentaldrugs, gynadrugs, gynasheetdrugs, infertilitydrugs, infertilitysheetdrugs, mointoringsheetdrugs, followupcarddrugs, operativedetailsdrugs, recorddrugs | **`visit_prescriptions`** (`Prescription` header, morphs `prescribable` → sheet/visit/cycle) + **`prescription_items`** (drug_id FK, dose, form, subject enum patient/husband, dispensed_qty) |
| Sheet + `*invest` clone (8 tables) | ancsheetinvest, mainantenentalinvest, gynainvestigation, gynasheetinvest, infertilityinvest, infertilitysheetinvest, mointoringsheetinvestigation, followupinvest | **`visit_investigations`** (`LabOrder` header, morphs `orderable`) + **`lab_order_items`** (lab_test_id FK → LIS catalog, result, subject, status) |
| CSV id columns (~40 columns) | e.g. `gyna.diagnosisid`, `tvs.tvsut`, `operations.operation`, `semen2resultscrotal` | real pivot tables per relation |
| 3 soft-delete dialects (`del`/`deleted`/`conditions`) | mixed | Laravel `SoftDeletes` (`deleted_at`) everywhere |
| Copy-table audit (`patients_updates`, `visits_updates`) | structural clones | `owen-it/laravel-auditing` |
| Generic AJAX writers (`Add/update/del` with client-supplied table/column) | mass-write + SQLi primitives | resource APIs + FormRequests + Policies (do **not** port) |

### 1.1 Patients & Registration domain

| New model | Table | Legacy tables | Key fields / notes |
|---|---|---|---|
| `Patient` | *(ERP patient master)* | `patients` (wife half) | The legacy patient **becomes** the ERP patient/client. `obgy_patient_profiles` holds OB/GYN-only fields: file_no (transactional sequence, not MAX+1), national_id (unique, DOB derived), risk, risk_type_id, marriage_date |
| `Spouse` | `obgy_spouses` | `patients` (husband\* columns) | 1-1 with patient: name, title_id, dob, national_id, education_id, job_id, blood_type enum, habits |
| `PreviousMarriage` | `obgy_previous_marriages` | `previous_marriage`, `hus_previous_marriage` | merged, `subject` enum(patient,spouse); fix latin1 |
| `ObstetricBaseline` | `obgy_obstetric_baselines` | `patients.pno/cs/ab/ectopic/vmodel/svd` | integer opening balances for G/P formula |
| `MedicalRecord` | `obgy_medical_records` | `records` (+`recorddrugs` → Prescription) | quick diagnosis/treatment lines |
| `DeliveryRegistration` | `obgy_delivery_registrations` | `registeration` | FK pregnancy_id, origin lookup, `coast`→`cost` decimal |
| `MediaAttachment` | (spatie medialibrary) | `patientfiles` + `upload/patientfiles/*` | real media table, signed URLs |
| lookups/enums | `obgy_lookups` | `wifejobs`+`husbandjobs` → JobTitle; `wifetypes`+`husbandtypes` → PersonTitle; `complaint`+`complaintant` → Complaint(context); `education`, `wifestatus`, `risktype`, `origin`; `bloodtypes` → Enum | merged dual-coded lists |
| **Dropped** | — | `patients_tmp`, `locations`, `patients_updates` (→ auditing), `table2/3`, `tablename`, `testtbl1/2` | dead |

### 1.2 Encounters, Scheduling & Billing bridge

| New model | Legacy | Notes |
|---|---|---|
| `Appointment` | `visits` (booking half) | patient_id, service_id, date, period_id, source enum(reception,mobile), status |
| `Encounter` | `visits` (queue half) + `followupcard` | queue_no, department/service, doctor, started/ended; clinical records FK to encounter_id (fixes "no visit linkage" flaw) |
| `Service` | `detections` | price list, synced to ERP item/service catalog |
| `VisitPeriod` | `visit_periods` | capacity enforced in DB transaction |
| `ClinicClosure` | `vacations` | actually enforced in booking |
| `TreatmentClosure` | `endvisitreports` | per patient/department |
| `PatientLedgerEntry` | `totalbalance` + magic codes in `visits` (`detectionid` -99 installment / 999 settle / 9999 refund) | explicit `entry_type` enum: charge, payment, refund, package_credit, installment; balance computed |
| `Invoice` / `Payment` | derived from `visits` money columns (cash/visa/discount/remaining) | posted to ERP accounting via events |
| **Dropped** | `old_visits`, `visits_updates`, `lastvisit` (derive last sheet by query), `totalbalancepaids` | dead/derived |

### 1.3 Clinical Core (shared services)

| New model | Legacy | Notes |
|---|---|---|
| `ClinicalExamination` | `examination` | vitals typed decimal; no auto-create-on-render |
| `ExaminationFinding` + options in `obgy_lookups(domain=exam.*)` | `examinationhead/chest/abdomen/pelvis/extremitis`, `examinationlocalse`, `generalbreast/hirsutism/thyroid` (9 tables) | finding rows split free-text vs catalog id |
| `Diagnosis` + pivot `encounter_diagnoses` | `diagnosis` + `diagnosisant` | merged with `specialty` column; replaces CSV columns |
| `HistoryQuestion` / `HistoryAnswerOption` / `PatientHistoryAnswer` | `presenthistoryquestions`, `presenthistoryanswers`, runtime `gynaph` | **gynaph exists only in production — export before ETL** |
| `Prescription` / `PrescriptionItem` | the 10 `*drugs` clones | see consolidation table; feeds pharmacy via `PrescriptionDispensed` event |
| `LabOrder` / `LabOrderItem` | the 8 `*invest` clones + EAV trio (`otherinvestigations`, `otherinvestigationsrows`, `otherinvestigationsvalues`) | EAV custom tests folded into catalog as ad-hoc tests |
| `InstructionTemplate`, `Referral` | `instruction`, hospital referral letters | templates + standalone referral entity |
| **Dropped** | `investigations` (legacy fixed-column, empty), `excelinfo` (license), `excelinfopatients` (→ queued export job) | |

### 1.4 Patient History

`MenstrualHistory` (phmenstrual), `ObstetricHistory` + child `ObstetricBaby`
(phobstetric triple-baby columns normalized; **also absorbs `wifep` + `awifep`** so there is
one obstetric history source; G/P formula becomes an accessor service),
`ContraceptionHistory` (phcontraception), `PastMedicalHistory` (phpastmedical, `for_spouse`),
`PastSurgicalHistory`, `PastGynecologicalHistory`, `PastArtCycle` + `PastArtCycleIcsiDetail`
(phpastart + phpasticsi via partentid), `FamilyHistory` (phfamily).
Catalogs `medicalhistory/surgicalhistory/familyhistory` + `*dm` husband clones + `phpast*` lists
merge into `obgy_lookups` with `for_spouse` flag. Termination mode → fixed Enum (G/P logic
depends on ids 1..5).

### 1.5 Antenatal

| New model | Legacy | Notes |
|---|---|---|
| `Pregnancy` | **`ancsheet` + `mainantenental` + `followup`** (3 parallel generations) | one file per pregnancy: lmp/edd DATE, gravida snapshot, status enum(active,ended); ETL matches generations by patient+lmp/done/endpreg |
| `AntenatalVisit` | `ancnewvisit` + `antenalvisit` + `followupvisit` | GA computed accessor (never stored); pivots for diagnosis/complaint CSV |
| `AntenatalUltrasound` | `mainantenentalus` + ancsheet T/TT-scan columns | scan_type enum incl. anomaly & 4D |
| `FourDScanBooking` | `op_4d_list` | explicit status instead of bit flags t11/t21 |
| `PregnancyLoss` (+ type/treatment lookups) | `wifeepc`, `wifeepctype`, `wifeepcttt` | re-parented to patient_id (resolved via infertilitysheet) |
| `ExternalProvider` | `wifeobst` + `wifeepcobst` | type doctor/hospital/abroad, heavy dedup |
| `ClinicalNote` | `followupdiagnosis` + `followupexam` + `followupinvest` | one table, `note_type` enum |
| Lookups/Enums | `placenta` (cleaned), `wifemodeofd`→Enum, `wifetypeofd`→Enum | `antype/antypes/pla2cen` move to IVF; **drop `anprotocol`** (orphaned) |
| **Production-only** | `followupdrugs` runtime table | export schema+data from production |

### 1.6 Gynecology

`GynaVisit` (gyna; CSV diagnosis/complaint → pivots), `GynaSheet` (gynasheet + maingyna),
`GynaSheetVisit` (newvisitg), `GynaUltrasound` (gynaus) + `GynaUltrasoundFollicle`
(gynausficils, side enum), `InfertilityProfile` (gynainfertility + `gyna.inf_*` columns),
`InfertilityPlan` (gynainfertilityplan). Nine empty structural-clone dictionaries
(menstrual*, local*, sheet complaint/diagnosis, infertility type) → `obgy_lookups`,
seeded from production. Drop legacy `gynasheet00` path.

### 1.7 Infertility

`InfertilityFile` — merges gen-1 `infertility` (+ dated children `infertilitydiagnosis`,
`infertilitylmp`, `infertilityinvest`, `infertilitynotes`) with gen-2 `infertilitysheet`
(64 flat columns) into one file per patient with structured child tables; the 15 `ttt*`,
`typeinf`, `sheetlocation`, `husbndiagnosis1-3`, `sheethusband/wife` lists → `obgy_lookups`
+ pivots (replacing comma-separated varchar ids). Obstetric history rows (`wifep`/`awifep`)
move to History domain (§1.4).

### 1.8 IVF / ICSI / IUI

| New model | Legacy | Notes |
|---|---|---|
| `IvfCycle` | **`ivfsheet` + `mointoringsheet`** | attempt_no, cycle_type enum(fresh,frozen,iui,monitoring), protocol/procedure lookups, status; partial unique index = one active cycle per patient |
| `IvfCycleVisit` + `FollicleMeasurement` | `ovst` + `mointoringsheetvisits` + `folliculom` | follicle grid (side, diameter_mm 10–20, count) replaces 26 cryptic columns |
| `OocyteRetrieval`, `SemenSample`, `EmbryoTransfer`, `Cryopreservation`, `EndometrialPrep` (+`epreplps`), `CycleOutcome` | stage columns of ivfsheet + `eprep` + runtime `sefo` | stage sub-tables |
| **Dropped** | `icsi` (derived archive → query/View; ETL orphan rows into IvfCycle), 15 lookups (`icsiprotocol`, `icsisemen`, `icsiss`, `icsiresult`, `icsiplace`, 9× `mointoringsheet*`, `epreplps`) → `obgy_lookups`, `ssemen` quadruplet | seed from production (empty in dump) |

### 1.9 Andrology

`SemenAnalysis` (semen + semeninfertility, numeric columns, WHO flags),
`AndrologyInvestigation` (semen2 wide → long rows: test_type, date, place_id, result_id,
pivot for multi-value scrotal US), `HormoneResult` (hormon + hormonalprofile +
hormonalprofile2 → long rows) + `HormoneDefinition` (unit, reference range, links to LIS).
9 `semen2result*/place*` + `semenplace/sementype` lookups → `obgy_lookups`.
**Drop orphan `sseemmeen`/`sseemmen`** (real list is `icsisemen`).

### 1.10 Imaging

`ImagingStudy` (unified header for `ultrasound`, `ultrasoundobst`, `ultrasoundgyna`,
`tvs`, `dtvs`, `sis`, `hsg`, `hsginfertility`, `mrict`, `sonar` headers; study_type +
approval status), `FetalScanFinding` (ultrasounddetail + ultrasoundobstdetail, per fetus),
`GynaScanFinding` (ultrasoundgyna detail), `AnnotatedDiagram` + `DiagramMarker`
(gtimage/gtdetail, relative coordinates), `MediaAttachment` (sonar images/videos from
`upload/sonar`, `upload/sonarvedio`). 16 vocabulary tables (tvs*, hsg*, usn/usaf/usplace,
dtvs*, four_d) → `obgy_lookups(domain)`; CSV result columns → pivots.

### 1.11 Endoscopy

`EndoscopyProcedure` (laparoscopy + hysteroscopy + laparoscopyinfertility +
hysteroscopyinfertility; `kind` enum, optional infertility_file_id, cost decimal),
`EndoscopyFinding` (site + category finding/management), `EndoscopyTerm` → `obgy_lookups`
(replaces 24 `lapar*`/`copy*` tables) + pivot for multi-select. `jncision`/`midlinejncision`
→ self-referencing `incision_types` in Operations domain.

### 1.12 Operations & Deliveries

`OperativeNote` (operativedetails; team CSV → `operative_note_staff` pivot with role enum),
`SurgicalBooking` (op_wait_list + procedures pivot), `InfertilityOperation` (operations;
operation/histopath CSV → pivots), `Procedure` catalog (operation, title/name merged),
`AnesthesiaType` (anasthesa + generalanasthesa chain), `Hospital` (hospitalnames),
`HistopathologyFinding` (histopath), `PathologyResult` (pathology), `Location`
(place + place2 merged with type column). Drop `operationids`. OR drug usage
(operativedetailsdrugs) → `Prescription` with context = operative note.

### 1.13 Investigations catalog → LIS

`LabTestCategory` (investcats, 22 seeded) and `LabTest` (invests, 276 items, add code/unit/
reference range) become a **mapping layer onto the existing LIS catalog** — each legacy
investid maps to a LIS test id; unmapped items are created in LIS. Custom EAV definitions
(otherinvestigations) become ad-hoc LIS tests.

### 1.14 Pharmacy → ERP inventory

`Drug` (drugs ~2000 items, dedup + normalize), `DrugCategory`/`DosageForm` lookups,
`Supplier` (from importbill.suppliers free text), `PurchaseInvoice`/`PurchaseInvoiceItem`
(importbill/importdetails; add prices, expiry, batch), `StockMovement` (pharmacystore →
movement ledger, non-negative computed balance), `Prescription` dispensing replaces
`recepittmp` (+ table-name polymorphism) and `receiptdrugs`. **Long-term:** drug catalog and
stock delegate to ERP inventory item master; the module keeps only the prescription layer.
Drop `drugdos`, `importtdetails`.

### 1.15 Medical Board

Rebuild from scratch (tables empty in dump; verify production): `BoardRequest`
(**adds mandatory patient_id — biggest legacy gap**), `BoardSession`, `BoardSessionMember`,
`BoardMemberOpinion`, `BoardComment` (nested), council roles via ERP permissions
(drop `councilstaff` + hardcoded positionid 1–3). Enums for request/session/opinion states.

### 1.16 Platform → ERP core (replaced, not migrated)

- `awusers` → ERP users (bcrypt hashes are portable); `awrole` + 5 permission tables
  (~590 actions / 3000 grants) → `spatie/laravel-permission`; menu (`awmenu`) → Angular
  route guards driven by permissions.
- `programesetting` (75-column singleton) → key/value settings; credentials → `.env`;
  receipt counters → transactional sequences.
- `branches` becomes real FK + `branch_user` pivot (kills the `-1 = all` varchar).
- Mobile API (`mobileservices.php`, unauthenticated, SQLi) → rewritten as documented
  Sanctum REST; patient password moves to a dedicated guard.
- Waiting-screen (`screen_slider`) → small `ScreenSlide` model + queue websocket feed.
- Drop: dev tables, `devices/floors/device_tracking` (incomplete feature — keep schema design
  note for HMS), `messages`, JWT lib, DB-dump tool with hardcoded password.

---

## 2. Integration Contracts with Moon ERP

| # | Legacy mechanism | New contract |
|---|---|---|
| 2.1 **Patient master** | cURL sync to external ERP DB (`client.obygyPatientId`), per-save, synchronous | **Eliminated.** The OB/GYN patient *is* the ERP patient/client (`patient_id`). One-time ETL reconciles `obygyPatientId` mappings; `PatientRegistered/Updated` domain events for any downstream consumers. |
| 2.2 **LIS** | none (paper requests; free-text results in `*invest.investresult`) | `LabOrderPlaced` event → LIS order with mapped test ids; LIS publishes `LabResultReceived` → result lands on `lab_order_items` and renders inside the clinical sheet. Catalog governed by mapping table `obgy_lis_test_map`. |
| 2.3 **Pharmacy / inventory** | direct writes to `recepittmp`/`pharmacystore` from clinical code | `PrescriptionDispensed` event → ERP inventory issue + sale line; drug catalog mastered in ERP item master, OB/GYN keeps prescribing metadata (dose, subject). Purchases handled entirely by ERP procurement. |
| 2.4 **Accounting** | `visits` doubles as journal with magic codes; sellbill pushed by cURL (`obygyVisitId`) | `VisitInvoiced` / `PaymentReceived` / `RefundIssued` events post Invoice/Payment/ledger entries to ERP accounting; `Service` price list mastered in ERP; daily-close report reads the ledger, not visits. |
| 2.5 **Auth / RBAC** | `login_system` lib + 5 `aw*` tables + open mobile endpoint | ERP single user store, Sanctum tokens, spatie permissions (one permission per legacy controller action + named button rights), branch scoping via pivot. |
| 2.6 **HMS (planned)** | — | `Encounter`, `OperativeNote`, `SurgicalBooking`, `Hospital`, `incision_types`, and the waiting-queue service are designed module-agnostic so the HMS module can consume them. |

---

## 3. Migration Phases

| Phase | Scope | Size |
|---|---|---|
| **P0 — Foundation** | Module skeleton, `obgy_lookups` framework + Enums, auditing, ETL toolkit (date/encoding/orphan cleaners), **production exports**: lookup values, runtime tables (`sefo`, `gynaph`, `followupdrugs`), media inventory. Auth/RBAC mapping to spatie. | **M** |
| **P1 — Patients & Front desk** | Patient↔ERP master merge, Spouse split, baselines, appointments/queue/periods/closures, Service catalog, ledger + accounting events. First go-live for reception. | **L** |
| **P2 — Clinical core & catalogs** | Examination + findings, diagnosis merge, history engine, shared `visit_prescriptions`/`visit_investigations`, LIS catalog mapping, pharmacy/inventory integration, patient history (ph\*) domain. | **L** |
| **P3 — Antenatal & Gynecology** | Pregnancy (3-generation merge), antenatal visits/US, 4D bookings, pregnancy loss, gyna visits/sheets/US. Highest data volume + trickiest dedup. | **L** |
| **P4 — Infertility, IVF, Andrology** | InfertilityFile merge, IvfCycle unification + stage sub-tables + follicle grid ETL, semen/hormone normalization. Clinically richest, needs clinician validation of lookup semantics (`ssemen` quadruplet, F-column order). | **L** |
| **P5 — Imaging, Endoscopy, Operations** | ImagingStudy unification, findings pivots, endoscopy term dictionary, operative notes/bookings, pathology. | **M** |
| **P6 — Board, Follow-up cards, Reports, Mobile** | Board rebuild, instruction/referral templates, statistical reports as query layer + Angular dashboards, authenticated mobile REST. | **M** |
| **P7 — Media migration & Cutover** | 4.8 GB upload tree → media library (checksummed, staged rsync, signed URLs), parallel-run reconciliation, final delta ETL, legacy read-only freeze, decommission cURL sync + old DB. | **M** |

Sequencing rule: each phase ships behind module permissions; legacy stays read-only
reference until P7 sign-off. ETL is re-runnable (idempotent upserts keyed on legacy ids
kept in `legacy_id`/`legacy_table` columns).

---

## 4. Data Migration Risk Register

| # | Risk | Impact | Mitigation |
|---|---|---|---|
| R1 | **Zero declared FKs** across 312 tables; all joins implicit | orphan rows surface as ETL failures | Pre-flight orphan audit per relation (90-erd list is the contract); quarantine orphans to `etl_orphans` with reason; clinicians review samples |
| R2 | **Clinical rows never reference visits** (patientid+date only) | cannot attribute records to encounters | Heuristic matcher (same patient + same date + department) creates Encounter links; unmatched rows get synthetic "historical encounter" |
| R3 | **Duplicate generations** (3 antenatal paths, 2 infertility, ivfsheet vs mointoringsheet, semen×4, hormone×3) | double counting / record collisions | Deterministic merge keys (patient+lmp / patient+date); generation precedence rules signed off by doctors; merge report per patient |
| R4 | **Varchar dates** (`Y/m/d`, `0000-00-00`, empty, free text) | silent data corruption | Defensive parser with reject-queue; NULL for invalid; ETL stats per column |
| R5 | **CSV multi-value id columns** (~40) with dangling ids | broken pivots | Split + validate against lookups; dangling ids logged and mapped to "unknown (legacy)" term |
| R6 | **Arabic encoding**: mixed latin1/utf8mb4 tables, mojibake seeds | unreadable patient/lookup data | Convert via latin1→binary→utf8mb4 round-trip per table; visual QA sample of Arabic names; full target DB utf8mb4_unicode_ci |
| R7 | **Dump ≠ production**: lookup tables empty in the 2024 dump; RedBean runtime tables (`sefo`, `gynaph`, `followupdrugs`) and runtime columns absent | seeds and ETL built on wrong schema | Mandatory fresh production export before each ETL rehearsal; schema diff gate in pipeline |
| R8 | **4.8 GB media** referenced by folder-naming convention only (patient files, sonar images/videos) | broken links, partial copies | Checksum inventory → staged rsync → DB-backed media records → verify counts/bytes; old tree kept read-only 6 months |
| R9 | **Magic financial codes** (`detectionid` -99/999/9999, self-FK visitid) | wrong balances in accounting | Transactional re-derivation into ledger entry types; reconcile totals per patient vs legacy screens before accounting hand-off |
| R10 | **MAX+1 file numbers, duplicate national IDs / drafts (`done=0`)** | identity collisions in patient master merge | Dedup pass (national id, phone, name+DOB) with human review queue; drafts not migrated unless they carry clinical data |
| R11 | **Misspelled/inconsistent columns** (`husdandname`, `coast`, `registeration`, varchar ids next to int ids) | mapping errors | Central column-mapping dictionary in repo, used by all ETL jobs and reviewed once |
| R12 | **Security debt must not travel**: generic table/column AJAX writers, unauthenticated mobile API, SQL-concatenated reports, hardcoded dump password | reintroducing vulnerabilities | Hard rule: no legacy endpoint ported; all new APIs FormRequest+Policy; secrets to `.env`; pen-test gate at P6 |

---

*Companion Arabic HTML fragment: `sections/95-erp-mapping.html`.*