# Addendum 78 — MED: New Tables Extracted from Code

**Scope:** Second deployment discovered at `/home/medgreennatureco/public_html/med` (live: med.greennature.com.sa). Newer/extended edition of the same codebase family, configured as a **center/hospital** rather than a single OB/GYN clinic.

**Method:** Read-only, code-only schema extraction. No live DB access. Sources:
- RedBeanPHP calls: `R::dispense|find|findAll|findOne|load|getAll|exec|count(...)` across `med/{core,board,pharmacy,vote}/controllers` and `med/core/views`.
- Raw SQL `FROM/JOIN/INSERT INTO/UPDATE` table names.
- Cross-checked every normalized name against the original 312-table list:
  `grep -oE 'CREATE TABLE IF NOT EXISTS \`[a-z0-9_]+\`' /home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql` → 312 unique tables.

## Quantification

| Metric | Count |
|---|---|
| Distinct tables referenced in MED code (RedBean, noise-filtered) | **413** |
| Known (present in original 312-table dump) | **137** |
| **NEW (not in original dump)** | **276** |
| Modules scanned | core, board, pharmacy, vote |

Noise removed as confirmed non-tables (DB selectors / debug / ext, not entities): `default, new, old, obgy, obor, true, xdispense, select, insert, update, delete, from, into, index`. Verified via grep, e.g. `R::addDatabase("new")`, `R::selectDatabase('obgy')`, `R::debug('true')`, `R::ext('xdispense')`.

## Architectural finding — Multi-DB federation + dedicated ERP DB

`med/core/controllers/*.php` call `R::addDatabase()` / `R::selectDatabase()` against multiple databases:
- `erpDB` — `R::addDatabase('erpDB', ... dbname=' . $programesetting->erpdb ...)` → a separate ERP database whose name is read from `programesetting.erpdb`. Visit bean carries `iserppayment`, `enterordered` flags → financial bridge to ERP already exists in code.
- `old`, `new`, `obor`, `royalDb`, `default` — cross-deployment/branch data migration scenarios.

This proves MED is a federation layer over several databases, not a single-DB app. HIS plan must address a Master Patient Index across these, not assume one schema.

## board / pharmacy modules use KNOWN tables only

`board/controllers/{sessions,requests,stuff}.php` reference `bsession, bsessionmembers, bmemberopenion, brequests, bcomments, councilstaff` — all KNOWN.
`pharmacy/controllers/*` reference `drugs, importbill, importdetails, pharmacystore, receiptdrugs, recepittmp, storedrugs` — all KNOWN.
The true expansion is concentrated in **`core`** (the center/hospital controllers).

## New entities by domain (with field inference and file citations)

### Kiosk Voting / patient-satisfaction (module `vote`)
Files: `vote/controllers/index.php`, `core/controllers/vote.php`
- `votes` (NEW) — survey definition.
- `vote_questions` (NEW) — `vote_id, question_text`.
- `vote_answers` (NEW) — `question_id, answer_text, answer_face`.
- `vote_devices` (NEW) — kiosk devices.
- `clients_votes` / `client_votes` (NEW) — `vote_id, vote_device_id, user_id, voter_name, voter_phone, ip, local_ip, remote_ip, user_agent, created_at`.
- `clients_votes_answers` (NEW) — `client_vote_id, question_id, answer_id, answer_text, answer_face, user_id, created_at`.

### Organizations / Geo (center contracts + geography)
Files: `core/controllers/regions.php`, `core/controllers/visits.php`, `visits_common.php`
- `organizations` (NEW) — `id, name, deleted, patient_no`; contracted insurers/companies.
- `organization_discount`, `organizations_patient_no` (NEW).
- `governorate`, `governorate_centers`, `regions`, `sub_regions` (NEW) — `id, name, region_id, deleted`.
- `nationality, hnationality, religion, hreligion, husstatus` (NEW) — reference lists (h-prefix = husband).

### Visit / Reception / Reservation (central encounter)
Files: `core/controllers/visits.php`, `visits_common.php`, `reserve_clinic.php`
- `visit` (NEW) — central encounter. Fields observed: `patientid, branch_id, branch_name, for_department, for_doctor, for_husband, visitdate, visit_time, original_price, contract_price, discount, organization_id, organization_discount, center_discount, center_discount_value, detectionvalue_cash, detectionvalue_visa, dr_salary, is_redirect, enc_id, iserppayment, enterordered, user_id, printserial, subregion`.
- `visit_services, visit_hours, day_times` (NEW).
- `clinic_reserves, doctors_reserves` (NEW) — `visit_id, doctor_id, room_id, reserve_day_no, reserve_hour, start_date, is_active, cancel_date, user_id`.
- `clinic_rooms, operation_room` (NEW); `advance_payment` (NEW).

### Residence / Admission
Files: `core/controllers/residence_rooms.php`, `residence_rooms`, `exit_summary`
- `residence_rooms` / `residence_room` (NEW) — `name, floor_no, start_time, end_time, create_date, deleted`.
- `residence_reserves` (NEW); `exit_summary` (NEW, discharge summary).

### IVF / Embryology Lab (largest new domain)
Files: `core/controllers/embryoslab.php`, `embryology.php`, `embryofreezing.php`, `ovumpickupembryotrans.php`, `tanks.php`
- `embryoslab` (NEW) — `date, visit_id, patient_id, clinician_id, embryologist_id_tb, referred_dr_id, create_date`.
- `embryo` (NEW) — `embryoslab_id, embryologist_id_tb, icsi_by_tb, freeze_embryologist_id_tb, cancelled_user`.
- `embryofreezing, embryothawing, embryofreezingreport, embryotransfer, embryotransferovum, ovumpickup` (NEW).
- `tanks, tanks_general, tankcells, tankcellhistory, embryoslab_tank_cells` (NEW) — `embryoslab_id, tank_cell, cell_no, row_no, col_no, btn_no, color_name, location, date_add, user_id, deleted` (LN2 tank cell-mapping + movement history).
- Reference lists: `embryologist, embryologyreport, embryoscoring, embryotype, embryodifficulty, embryojetplace, oocytequality, oocytecytoplasm, oocytezona, oocytepvs, oocyteother, growthmedia, growthincubator, growthco2, growthoil, freezingmedia, freezingprotocol`.
- `ivf_records, ivf_report` (NEW).

### Andrology / Semen (`and*` cluster)
Files: `core/controllers/and_visits.php`, `and_examination.php`, `and_history.php`, `spermfreezing.php`, `semen_*` views
- `andvisits` (NEW) — `patientid, doctorid, doctorname, date, complaintid, diagnosisid`.
- `andvisitssemen, andvisitsus, andvisitsrays, andvisitsinvestigation, andvisitsficils, andvisitsgenetictesting, andvisitsdrugs` (NEW) — `andvisitsusid, azf, chromosome, length, width, volume, name, type, sysdate`.
- `andexamination, andhistory, andejaculation, anderection, andcontraception, andcontratype, andmedicalproblems, anddiagnosis, andcomplaint, erectiondisease, ejaculationdisease` (NEW, exam + lookups).
- `semen_analysis, semen_process, semen_processing, semen_cryopreservation, sperm_extract, spermfreezingreport, sperum_source` (NEW).

### Endoscopy / Colonoscopy
Files: `core/controllers/endoscopy.php`, `colonoscopy.php`, `endoscopy_template.php`, `colonoscopy_template.php`, `oscopic.php`
- `endoscopy`, `colonoscopy` (NEW) — `patient_id, exam_date, indication, findings, conclusion, plan, esophagus, stomach, duodenum, pylorus, cardio_esophageal_junction, dre, anesthesia, instrument, preparation, preparation_2, consent, signature, created_at, updated_at`.
- `endoscopy_images, colonoscopy_images` (NEW) — `endoscopy_id|colonoscopy_id, image`.
- `endoscopy_template, colonoscopy_template` (NEW).
- `oscopic_report, oscopic_operations, oscopic_specimens, oscopic_required_examinations` (NEW).

### Operations / OR / Anaesthesia
Files: `core/controllers/operationreport.php`, `operations_rooms.php`, `operations_calendar.php`, `operativedetails.php`
- `operations_main, operation_data, operation_form, operationgyna` (NEW).
- `operations_rooms, operations_rooms_cal, operation_room` (NEW).
- `operationinstructions, operationotherth, followup_operation, manual_operation, manual_operation_updates` (NEW).
- `pre_anaesthetic, intra_anaesthetic` (NEW).

### Lab Investigation Results (detailed)
Files: `core/controllers/investigation.php`, `financiallab.php`, `addinvestigation.php`, `labdevices.php`
- `investigationresults` (NEW) header + sample-type detail tables:
  `investigationresults_blood, _urine, _stool, _semen, _culture, _cross, _cross_donners, _lipid, _egfr, _esr, _pt, _aborh, _custom, _times` (NEW).
- Microscopy option lists: `stool_rbc, stool_rbc2, stool_wbc, stool_wbc2, urine_pus, urine_pus2, urine_pbcs, urine_pbcs2` (NEW).
- `invest_elements, inv_ranges, sampletype, esr_` (NEW).
- `lab_devices, lab_devices_ranges, saveresultslog, external_labs` (NEW).

### Radiology
Files: `core/controllers/rays.php`, `radiation.php`, `raysdept.php`
- `rays, rayscats, raysresults, raysresults_img, gynarays, mainantenentalrays` (NEW).

### Pharmacy / Prescriptions / Store (core-side)
Files: `core/controllers/pres_common.php`, `pharmacy.php`, `_phmain.php`, `drugsex.php`
- `prescription, prescription_details` (NEW) — `patient_id, drugname, drugtype, drugdos`.
- `prepared_prescriptions, prepared_prescriptions_drugs` (NEW).
- `stores, storedrugs, storedrugsvalidation` (NEW).
- `drugmovements, recorddrugs, importdrugs, followupdrugs` (NEW) — `drug_id, amountbefore, amountafter, amountvary, optype, opdate`.
- `drug_sensitivity, drug_therapy, antibiotic_sensitivity` (NEW).
- `supplies, suppliescats, invests_supplies, invoffer, invofferdetails` (NEW).

### Patient History / Forms
Files: `core/controllers/patienthistory.php`, `infertility.php`, `gyna.php`, `obstetric*`, `mensturalhistory` views
- `pasthistorymedical, pasthistorysurgical, pasthistoryart, pasthistorygynencological` (NEW).
- `phpersonal, phsexual, phsexualtypes, phpasthx, phpasthxtypes, phpastartresult, phdrugs` (NEW, infertility profile).
- `obstetric_case, obstetric_case_ex, obstetrichistorydetails, summary_obstetric, summary_obstetric_abortion, previous_pregnancies, previous_puerperium` (NEW).
- `mensturalhistory, mensamount, mensdysmo, mensregularity` (NEW).
- `patientinformation, patientdecleration, patients_childs, patients_complaints, patients_notify, relative` (NEW).
- `patient_hystroscopic, patient_laparoscopic, hystroscopic, laparoscopic` (NEW).
- `vital_sign, fetal_pulse, blood_transfusion, bltype, dna_results, complaint_analysis, decleration` (NEW).

### Follow-up / Archive / Settings / Infra
Files: `core/controllers/followup.php`, `followupcard.php`, `archive.php`, `programesetting.php`, `sms_control.php`, `api_web.php`
- `follow_drugs, follow_drain, follow_post, follow_pre, follow_rout, follow_semen, follow_uop, follow_vpack, follow_instruction, visits_follows` (NEW).
- `archive_request, archive_tracking` (NEW) — `patient_id, in_date, out_date, in_time, out_time, deleted` (paper-file checkout tracking).
- `programesetting2, programesettingex, programesettinglab` (NEW) — includes `erpdb, tanks_view, visit_phones`.
- `sms_control, sms_control_setting, login_tacking` (NEW).
- `short_urls, index_redirect` (NEW) — URL shortener / redirect (for SMS links).
- Reference/UI lists: `awusermenu, device, visibility, colors, fastoptions, custom_select, doc_instruction, recommend, refer, knownfrom, symptoms, disease, diseasefamily, medicaldisease` (NEW).
- `beauty_visits` (NEW) — aesthetic/beauty unit.

## Inference caveat
Fields marked "(inference)" in the HTML are derived from SQL column names or RedBean bean-property assignments in the controllers, not from actual table DDL — no live DB was queried. Reference-list tables share a uniform `id, name, deleted` shape across the system.

## ERP/HIS plan impacts
1. Original 312-table analysis covers only the OB/GYN clinic; MED adds **276 new tables** turning it into a multi-specialty center/hospital — migration blueprint must expand.
2. Whole missing modules to add to the HIS plan: IVF embryology lab (with LN2 tank cell tracking), Andrology, Endoscopy/Colonoscopy, OR + scheduling, Residence/Admission, detailed lab results by sample type, Radiology.
3. An ERP integration already exists in code (`erpDB`, `iserppayment`, `enterordered`) — absorb it, don't rebuild.
4. Multi-DB federation (`old/new/obor/royalDb`) implies Master Patient Index / consolidation work.
5. `visit` is the central financial+clinical encounter entity (pricing, org discounts, inter-department routing) — model it as the core Encounter.
6. `organizations + organization_discount + organizations_patient_no` add the insurance/contracted-party dimension needed for Billing/Claims.
7. `drugmovements/storedrugs/storedrugsvalidation` provide a movement ledger for inventory — bind to ERP inventory module.
8. Before migration, confirm real DDL via `SHOW CREATE TABLE` on the live MED DB (with permission; outside this code-only analysis).