# Module: Medical Board & Sessions (board)

## Purpose
Standalone sub-app (`/board`) implementing a doctors' council / case-discussion board. Doctors submit consultation requests (free-text case title + details), the council secretary opens periodic sessions, marks member attendance, each attending member records an Accept/Refuse opinion per request, a final textual decision is recorded on the request, requests can be postponed to the next session, and closed sessions/requests are archived. A threaded comment system lets users discuss each request. A staff screen manages council composition (head / secretary "amin" / members).

Note: despite the clinical intent, there is NO structural link to patients — the case is described only in free text (`brequests.title`, `brequests.details`).

## Controllers & Views (file paths)
Controllers (board sub-app, awframework + RedBeanPHP `R::`, Smarty views):
- `/home/amrtechogate/public_html/obgy/board/controllers/requests.php` — request lifecycle + comments
  - actions: `index` (auto-create/load draft request), `update` (generic AJAX autosave), `sendrequest`, `showall` (status=1), `archiveshowall` (status=2), `requestDetails`, `requestcomments`, `addcomment`, `deletecomment`, `requestDetailsArchive`
- `/home/amrtechogate/public_html/obgy/board/controllers/sessions.php` — session lifecycle, attendance, opinions, decisions
  - actions: `index` (auto-create open session + bulk-insert members), `savesession` (close + archive requests, transactional), `archiveshowall`, `requestDetails`, `requestDetailsArchive`, `sessiondetails`, `addsessionmember`, `removesessionmember`, `delay`, `notdelay`, `saveopinion`, `savedecision`
- `/home/amrtechogate/public_html/obgy/board/controllers/stuff.php` — council staff management (head/amin/members in `councilstaff`) + ~200 lines of DEAD pharmacy code (`updategg`, `del`, `delup` operating on `importbill`, `importdetails`, `drugs`, `pharmacystore`) copy-pasted from the pharmacy module
- `/home/amrtechogate/public_html/obgy/board/controllers/test.php` — scratch/test file (not analyzed as assigned)

Views: `/home/amrtechogate/public_html/obgy/board/views/obgy/` — `addrequest.html`, `showrequests.html`, `requestdetails.html`, `request_model.html`, `comments.html`, `addsession.html`, `showsessions.html`, `sessiondetails.html`, `session_model.html`, `members.html`, `stuff.html`, `headinfoview/edit.html`, `amininfoview/edit.html`, `boardstuffview.html`, `stuffinfoedit.html`, `templates/*`.

## Tables
All from `/home/amrtechogate/public_html/obgy/_db/obgy_12-7-2024.sql`. No declared foreign keys anywhere. All six tables are EMPTY in the dump (`AUTO_INCREMENT=1`, no INSERT statements) — no seed/lookup data exists for this module.

### brequests (utf8mb4, dump line 4563)
Consultation request submitted to the board. Status lifecycle: 0 = draft (auto-created on screen open), 1 = submitted/pending discussion, 2 = discussed & archived.
| Column | Type | Notes |
|---|---|---|
| id | int(11) unsigned PK AI | |
| memberid | int(11) unsigned NULL | FK -> awusers.user_id (requesting doctor) |
| title | varchar(191) NULL | free-text case title |
| details | varchar(191) NULL | free-text case details — only 191 chars despite textarea UI |
| status | int(11) unsigned NULL | 0 draft / 1 submitted / 2 discussed (hardcoded) |
| date | date NULL | set to today on `sendrequest` |
| delay | int(11) unsigned DEFAULT 0 | 1 = postponed to next session; reset to 0 on session close |
| decision | varchar(191) NULL | council's final decision, free text (`savedecision`) |
| sessionid | int(11) unsigned NULL | FK -> bsession.id, set when session is closed (`savesession`) |

### bsession (latin1, dump line 4582)
Board session. Status: 0 = open (only one open at a time, auto-created), 1 = closed/archived.
| Column | Type | Notes |
|---|---|---|
| id | int(11) PK AI | |
| date | date NOT NULL | set to today on close (`savesession`) |
| status | int(11) NOT NULL | 0 open / 1 closed |
| memberid | int(11) unsigned NULL | FK -> awusers.user_id (user who opened the session) |

### bsessionmembers (utf8mb4, dump line 4596)
Session attendance join table (session x member). Row present = attended; deleted = absent. On session creation, ALL users with `awusers.positionid IN (1,2,3)` are bulk-inserted (transactional `R::storeAll`).
| Column | Type | Notes |
|---|---|---|
| id | int(11) unsigned PK AI | referenced by bmemberopenion.sessionmemberid |
| sessionid | int(11) unsigned NULL | FK -> bsession.id |
| memberid | int(11) unsigned NULL | FK -> awusers.user_id |

### bmemberopenion (utf8mb4, dump line 4535) — note misspelling "openion"
One opinion per (request x session-member); upserted by `saveopinion` (find-then-update or insert). No unique constraint enforced in schema.
| Column | Type | Notes |
|---|---|---|
| id | int(11) unsigned PK AI | |
| requestid | int(11) unsigned NULL | FK -> brequests.id |
| sessionmemberid | int(11) unsigned NULL | FK -> bsessionmembers.id (NOT a user id) |
| openion | int(11) unsigned NULL | 1 = Accept, 2 = Refuse (from views members.html / session_model.html) |

### bcomments (latin1, dump line 4507)
Threaded comments on requests. `relatedcommentid = 0` => top-level comment; otherwise points to parent comment id (one level of nesting used in code). Deleting a top-level comment deletes its replies.
| Column | Type | Notes |
|---|---|---|
| id | int(11) PK AI | |
| memberid | int(11) NOT NULL | FK -> awusers.user_id (author) |
| requestid | int(11) NOT NULL | FK -> brequests.id |
| comment | text NOT NULL | latin1 — Arabic text corruption risk |
| relatedcommentid | int(11) NOT NULL | self-FK -> bcomments.id, 0 = root |

### councilstaff (latin1, dump line 4778)
Council composition managed by stuff.php. Column comment in schema: `0 head, 1 amin, 2 member`.
| Column | Type | Notes |
|---|---|---|
| id | int(11) PK AI | |
| userid | int(11) NOT NULL | FK -> awusers.user_id |
| status | int(11) NOT NULL | 0 = head, 1 = amin (secretary), 2 = member |

FUNCTIONALLY ORPHANED: sessions.php line 83-84 has the `councilstaff` query commented out and replaced with a hardcoded `SELECT * FROM awusers WHERE positionid IN (1,2,3)`. So the council membership managed in the stuff screen does not actually drive session membership. No `positions` lookup table exists in the dump (values 1,2,3 are magic numbers).

## Relationships (explicit list)
- brequests.memberid -> awusers.user_id (requesting doctor)
- brequests.sessionid -> bsession.id (session in which it was discussed; set on close)
- bsession.memberid -> awusers.user_id (session creator)
- bsessionmembers.sessionid -> bsession.id
- bsessionmembers.memberid -> awusers.user_id
- bmemberopenion.requestid -> brequests.id
- bmemberopenion.sessionmemberid -> bsessionmembers.id
- bcomments.memberid -> awusers.user_id
- bcomments.requestid -> brequests.id
- bcomments.relatedcommentid -> bcomments.id (self, 0 = root)
- councilstaff.userid -> awusers.user_id
- (implicit) session member selection: awusers.positionid IN (1,2,3) — hardcoded, no positions table in dump
- (missing) NO patient/visit linkage anywhere in this module

## Business Workflows (traced from code)
1. **Submit request** (requests.php): `index` loads the doctor's last request; if none or last is completed, dispenses a new `brequests` row (status NULL/0, memberid = session user). Title/details auto-saved field-by-field via `update` (generic POST: id/tableName/colName/value). `sendrequest` sets status=1, date=today, redirects to pending list.
2. **Discuss via comments**: `requestcomments` renders root comments (relatedcommentid=0) plus one level of replies, joining author names from `awusers`. `addcomment` inserts; `deletecomment` cascades root -> replies manually.
3. **Open session** (sessions.php `index`): loads council members from `awusers.positionid IN (1,2,3)` and pending requests (status=1). If last `bsession` is closed or none exists: creates new `bsession` (memberid = current user) and bulk-inserts one `bsessionmembers` row per council member inside a transaction. If an open session exists, computes per-member `attend` flag from `bsessionmembers` presence.
4. **Attendance**: `addsessionmember` inserts, `removesessionmember` deletes (sessionid, memberid) rows.
5. **Per-request discussion**: `requestDetails` modal lists session members with their stored opinion (from `bmemberopenion` by sessionmemberid+requestid). `saveopinion` upserts opinion (1 Accept / 2 Refuse). `savedecision` writes free-text decision onto the request. `delay`/`notdelay` toggle `brequests.delay`.
6. **Close session** (`savesession`, transactional): session.status=1, session.date=today; all requests with status=1 AND delay=0 -> status=2, sessionid=closed session; all requests with status=1 AND delay=1 -> delay reset to 0 (stay pending for next session). Redirect to session archive.
7. **Archive review**: `archiveshowall` (sessions status=1; requests status=2), `sessiondetails` (members + requests of one session), `requestDetailsArchive` (request + comments + members' opinions read-only).
8. **Council staff management** (stuff.php): `index` shows head (councilstaff.status=0), amin (status=1), members (status=2) with names joined from awusers; `add`/`addboardstuff`/`update`/`updateamin`/`stuffupdate`/`delboard` CRUD councilstaff rows. NOTE: outputs not consumed by sessions.php (see above).

## Security/Quality Issues (must not be migrated)
- `requests.php::update()` — arbitrary table/column write: tableName, colName, id, value all from POST, passed straight to `R::load`/`R::store`. Critical vulnerability.
- `stuff.php` — all `autho::checkautho`/`checkauthoize` calls commented out (no auth), and string-concatenated SQL (`'... WHERE user_id = ' . $userid`, `NOT IN ($headuser->userid)`) — SQL injection.
- Empty `catch` blocks swallow all exceptions throughout.
- Mixed charsets (latin1 vs utf8mb4) across the six tables; Arabic text in `bcomments.comment` at risk.
- No timestamps on comments/opinions; varchar(191) for clinical details/decision; magic-number statuses; misspelled `openion`.
- ~200 lines of dead pharmacy code in stuff.php (`updategg`, `del`, `delup`).

## ERP Migration Notes
Proposed Laravel models/tables:
- `BoardRequest` (`board_requests`): requester_id FK users, **patient_id FK patients (NEW — fixes the biggest design gap)**, optional visit_id, title, details TEXT, status enum (draft/submitted/discussed), decision TEXT, submitted_at, session_id FK nullable, is_postponed bool, timestamps, soft deletes.
- `BoardSession` (`board_sessions`): opened_by FK users, status enum (open/closed), scheduled/closed dates, timestamps.
- `BoardSessionMember` (`board_session_members`): session_id, user_id, unique(session_id, user_id), timestamps. Replaces bsessionmembers.
- `BoardMemberOpinion` (`board_member_opinions`): request_id, session_member_id (or flatten to session_id+user_id), opinion enum (accept/refuse), unique(request_id, session_member_id), timestamps. Replaces bmemberopenion.
- `BoardComment` (`board_comments`): request_id, user_id, parent_id self-FK nullable, body, timestamps, soft deletes. Replaces bcomments (relatedcommentid=0 -> parent_id NULL).
- Council membership: replace BOTH `councilstaff` and the hardcoded `positionid IN (1,2,3)` with a single managed mechanism — either a `board_members` table (user_id, role enum head/secretary/member) or roles in the ERP's permission system (e.g. Spatie). Make session auto-population read from it.
- Enums: RequestStatus, SessionStatus, Opinion, CouncilRole — kill all magic numbers.
- Replace the generic autosave endpoint with `PATCH /api/board-requests/{id}` using FormRequest validation (whitelist title/details only).
- Drop entirely: dead pharmacy code in stuff.php; `test.php`.
- Data migration: all six tables are empty in this dump — likely greenfield rebuild with no historical data to migrate (verify against current production DB before finalizing).