# Module: Pharmacy & Drugs (pharmacy)

## Purpose
Standalone sub-app (`/pharmacy/`) of the legacy OB/GYN system (PHP + Smarty + RedBeanPHP `R::` ORM, "aw framework"). Manages:
1. The unified drug catalog (~2,000 seeded rows) with category, brand name, dosage form, and Arabic dosage instruction text.
2. Pharmacy inventory as a ledger (`pharmacystore`) of before/after balances per operation.
3. Purchase invoices from suppliers (`importbill` / `importdetails`).
4. Dispensing of prescriptions ("receipts") that are auto-generated by clinical sheet controllers in the core app (`recepittmp` / `receiptdrugs`).

## Controllers & Views (file paths)
Pharmacy sub-app controllers (`/home/amrtechogate/public_html/obgy/pharmacy/controllers/`):
- `drugs.php` — drug catalog CRUD; DataTables server-side listing (`show`), per-field autocomplete (`getthisserach`), `adddrug`, `updatedrug`, `deletedrug`. View: `medicineview/drugs.html`.
- `buys.php` — purchase invoices ("Import"): `index` (add form), `addnew` (create bill + details + stock movements, transactional), `show` (list bills, joins `awusers` for receiver name), `invoice` (printable bill), `edit`/`update` (reverse-and-reapply stock logic), `del` (delete bill + correcting movements), `delup` (delete single bill line). Views: `buys/add.html`, `buys/addselect.html`, `buys/show.html`, `buys/invoice.html`, `buys/edit.html`.
- `receipt.php` — pending prescription queue: `index`/`show` (identical: list `recepittmp` with doctor name from `awusers` and patient name from `patients.wifename`), `showdetails` (line items + low-stock flag `datachecked`), `updatereceipt` (dispense: write `pharmacystore` optype=1, decrement `drugs.currentbalance`, set `recepittmp.status`), `update` (DANGEROUS generic write: loads arbitrary table/column/value from POST). Views: `receipt/show.html`, `receipt/showdetails.html`.
- `store.php` — `show`: full stock-movement report from `pharmacystore` joined (in PHP loop) to `drugs.drugname`. View: `store/show.html`.
- `stuff.php` — manages `councilstaff` (head-of-council assignment, not a pharmacy table) AND contains verbatim copies of `buys.php`'s `update` (named `updategg`), `del`, `delup`. Views: `stuff.html`, `newdatashow*.html`.

Core controllers that feed this module (`/home/amrtechogate/public_html/obgy/core/controllers/`):
- `followupcard.php` — on saving a follow-up card prescription: dispenses `recepittmp` (doctor_id = session user, patient_id, rect_date = today, drugstablename = 'followupcarddrugs', status = 0), then per drug writes `followupcarddrugs` (with `recepittmpid` back-ref) and `receiptdrugs` (drugs_id, amount = 1, receipt_id).
- `operativedetails.php` — helpers `addrecepittmp($patientid)` (drugstablename = 'operativedetailsdrugs') and `addrecepitdrug($recepittmpId, $drugid)` (amount = 1); inline-edit of `drugid` creates/updates receipt rows, reusing an open (status = 0) receipt if present.
- `gyna.php` — prescription row creation for many sheet tables (`mainantenentaldrugs`, `followupdrugs`, `ancsheetdrugs`, `recorddrugs`, `infertilitydrugs`, `mointoringsheetdrugs`, `infertilitysheetdrugs`, `gynasheetdrugs`); initializes `recepittmpid = 0`, `recepitdrugid = 0` and pulls drug pick-lists via `SELECT DISTINCT drugcat/drugtype/drugdos FROM drugs`.

## Tables

### drugs
Master drug catalog + running stock balance. 6 INSERT batches in dump; IDs range ~3..2047 (AUTO_INCREMENT=2048), i.e. ~2,000 seeded rows.
```sql
id             int(11) PK AUTO_INCREMENT
drugcat        varchar(255) NULL      -- therapeutic category, free text (e.g. 'Paracetamol', 'Ovulation induction', 'Menopause', 'Anti-inflammatory', 'Antacid', 'Anti-viruis\r\n' [sic])
drugname       varchar(230) NOT NULL  -- brand name (e.g. 'Abimol', 'Acyclovir', 'Activelle')
drugtype       varchar(100) NOT NULL  -- dosage form, free text: Tab, Syrup, Sachets, Susp, Lotion, Amp, Cream...
drugdos        varchar(255) NULL      -- Arabic dosage instruction text (e.g. "قرص بعد الأكل عند اللزوم")
initialbalance int(11) DEFAULT 0
currentbalance int(11) DEFAULT 0      -- denormalized running stock, updated by controllers
```
Inferred FKs pointing here: `importdetails.drug_id`, `importtdetails.drug_id`, `pharmacystore.drug_id`, `receiptdrugs.drugs_id`, clinical `*drugs.drugid` (note: those are varchar(150)).

### drugdos
DEAD lookup table. Zero rows (AUTO_INCREMENT=1), zero code references. `name` is mistyped as int. Real dosage text lives in `drugs.drugdos`.
```sql
id      int(11) unsigned PK AUTO_INCREMENT
name    int(11) unsigned NULL   -- mistyped; should have been varchar
deleted int(11) unsigned NULL
```

### pharmacystore
Stock-movement ledger. One row per balance change.
```sql
id           int(11) PK AUTO_INCREMENT
drug_id      int(11) NOT NULL        -- -> drugs.id
amountbefore int(11) DEFAULT 0
amountafter  int(11) DEFAULT 0
amountvary   int(11) NOT NULL        -- delta
opdate       datetime NOT NULL
optype       tinyint(1) NOT NULL     -- 0 = purchase/import, 1 = dispense/export, 2 = bill edit correction, 3 = bill/line delete correction (code comments are wrong/misleading)
```
No seeded data. Note: `buys.php update()` queries `pharmacystore` by `bill_id` (`'drug_id = ? AND bill_id = ?'`) but the table has NO `bill_id` column — latent bug (RedBean would error or the row lookup silently fails).

### receiptdrugs
Prescription (receipt) line items.
```sql
id         int(11) PK AUTO_INCREMENT
receipt_id int(11) NOT NULL   -- -> recepittmp.id
drugs_id   int(11) NOT NULL   -- -> drugs.id
amount     int(11) NOT NULL   -- always 1 when auto-generated from clinical sheets
```

### recepittmp
Prescription/receipt header awaiting dispensing ("tmp" is a misnomer; it is the permanent receipt header). Name itself is a typo of "receipttmp".
```sql
id             int(11) PK AUTO_INCREMENT
doctor_id      int(11) NOT NULL       -- -> awusers.user_id
patient_id     int(11) NOT NULL       -- -> patients.id
rect_date      date NOT NULL
drugstablename varchar(250) NULL      -- polymorphic source: 'followupcarddrugs', 'operativedetailsdrugs', ...
status         int(11) unsigned NULL  -- 0 = pending, 1 = dispensed
```

### importbill
Purchase invoice header.
```sql
id          int(11) PK AUTO_INCREMENT
invoicenum  varchar(50) NOT NULL
invoicedate date NOT NULL
suppliers   varchar(100) NOT NULL  -- free-text supplier name; no suppliers table exists
receiveuser int(11) NOT NULL       -- -> awusers.user_id (receiving employee, from $_SESSION['user_id'])
```

### importdetails
Purchase invoice line items. Only table in the module with an FK-style index (`index_foreignkey_importdetails_importbill` on `bill_id`).
```sql
id      int(11) PK AUTO_INCREMENT
bill_id int(11) NOT NULL   -- -> importbill.id
drug_id int(11) NOT NULL   -- -> drugs.id
amount  int(10) NOT NULL
```

### importtdetails
DEAD duplicate of `importdetails` (utf8mb4, unsigned variant — looks like an aborted recreate). Zero rows, zero code references anywhere in the PHP codebase.
```sql
id      int(11) unsigned PK AUTO_INCREMENT
bill_id int(11) unsigned NULL
drug_id int(11) unsigned NULL
amount  int(11) unsigned NULL
```

## Relationships (no declared FKs anywhere; all inferred from controller SQL)
- `importdetails.bill_id` -> `importbill.id`
- `importdetails.drug_id` -> `drugs.id`
- `importtdetails.bill_id` -> `importbill.id` (dead table, by analogy)
- `importtdetails.drug_id` -> `drugs.id` (dead table, by analogy)
- `pharmacystore.drug_id` -> `drugs.id`
- `receiptdrugs.receipt_id` -> `recepittmp.id`
- `receiptdrugs.drugs_id` -> `drugs.id`
- `recepittmp.doctor_id` -> `awusers.user_id`
- `recepittmp.patient_id` -> `patients.id`
- `importbill.receiveuser` -> `awusers.user_id`
- `recepittmp.drugstablename` -> table NAME string (polymorphic): `followupcarddrugs`, `operativedetailsdrugs`, ...
- Clinical prescription tables (`ancsheetdrugs`, `gynadrugs`, `gynasheetdrugs`, `followupcarddrugs`, `followupdrugs`, `mainantenentaldrugs`, `infertilitydrugs`, `infertilitysheetdrugs`, `mointoringsheetdrugs`, `operativedetailsdrugs`, `recorddrugs`):
  - `.drugid` (varchar!) -> `drugs.id`
  - `.recepittmpid` -> `recepittmp.id` (0 = not yet linked)
  - `.recepitdrugid` -> `receiptdrugs.id` (0 = not yet linked)
  - plus `.patientid` -> `patients.id`, `.doctorid` -> `awusers.user_id`, and free-text copies of `drugtype`/`drugdos`/`drugname`

## Business Workflows (traced from code)
1. **Catalog maintenance** (`drugs.php`): add drug sets `currentbalance = initialbalance`. Listing via DataTables server-side protocol; autocomplete per field via `LIKE 'term%'` queries.
2. **Purchasing** (`buys.php addnew`, transactional `R::begin/commit/rollback`): create `importbill`; per line create `importdetails`, insert `pharmacystore` row (optype=0, before = current, after = before + amount), set `drugs.currentbalance = amountafter`.
3. **Prescription generation** (core app): saving a follow-up card (status 0 -> 1) or editing an operative-details drug creates `recepittmp` (status=0, drugstablename = source table) + one `receiptdrugs` row per drug (amount hardcoded to 1); the clinical row stores `recepittmpid`/`recepitdrugid` back-references. `operativedetails.php` reuses an existing open receipt (status=0) for the same operation instead of creating a new one.
4. **Dispensing** (`receipt.php`): pharmacist sees pending receipts (doctor + patient names resolved in PHP loops); `showdetails` flags `datachecked = true` if any line's `amount > drugs.currentbalance`; `updatereceipt` writes optype=1 movements and decrements balances ONLY for lines with sufficient stock (insufficient lines silently skipped), then marks the whole receipt with the posted `status` — no partial-dispense state.
5. **Bill edit/delete** (`buys.php update/del/delup`, duplicated in `stuff.php`): reverse previous quantities, write optype=2 (edit) / optype=3 (delete) correction movements, adjust `drugs.currentbalance` (can go negative — no guard).
6. **Stock report** (`store.php show`): dump of all `pharmacystore` rows with drug names (N+1 query per row).

## ERP Migration Notes
Proposed Laravel models/tables:
- `Drug` (`drugs`): keep id, name; normalize `drugcat` -> `drug_categories` and `drugtype` -> `dosage_forms` lookup tables; keep `default_dosage_text` (Arabic). Data cleanup required: trim `\r\n`, dedupe categories ('Anti-viruis' vs 'Antiviral'), unique constraint on (name, dosage_form).
- `StockMovement` (`pharmacystore`): drug_id FK, qty_before/qty_after/qty_delta, `movement_type` enum {purchase, dispense, adjustment_edit, adjustment_delete}, reference morph (purchase invoice / prescription), user_id, timestamp. Make `drugs.current_balance` derived (SUM of movements or event-maintained inside DB transactions) with a non-negative check.
- `PurchaseInvoice` (`importbill`) + `PurchaseInvoiceItem` (`importdetails`): add `supplier_id` FK to a NEW `suppliers` table (currently free text); consider unit cost, batch number, expiry date columns (absent today — no pricing/expiry anywhere in legacy schema).
- `Prescription` (`recepittmp`) + `PrescriptionItem` (`receiptdrugs`): doctor_id, patient_id, date; replace `drugstablename` string with a standard Laravel polymorphic relation (`prescribable_type`/`prescribable_id`) — or better, collapse the 10+ clinical `*drugs` tables into one `prescription_items` table tagged by sheet type. Status enum {pending, partially_dispensed, dispensed, cancelled}; store dispensed qty per item instead of silently skipping out-of-stock lines. Real quantities instead of hardcoded amount=1.
- DROP: `drugdos` (empty, mistyped, unreferenced) and `importtdetails` (empty duplicate, unreferenced). Do not migrate.
- `stuff.php`'s `councilstaff` logic belongs to an admin/staff module, not pharmacy; its duplicated invoice functions are dead weight.
- Security fixes inherent to rewrite: parameterized queries (legacy `drugs.php show()` concatenates `sSearch` -> SQL injection), remove the generic table/column/value write endpoint (`receipt.php update()`), enforce authorization via Policies (several legacy actions have `checkauthoize` commented out).
- Known latent bug not to replicate: `buys.php update()`/`del()` query `pharmacystore` by a nonexistent `bill_id` column and can drive `currentbalance` negative; edit flow compares form drug id to a bill-row id (`$billIDn != $drug_id`) — rewrite the reversal logic from the ledger, not from last-row heuristics.