# hospital-management-Ghost

**Repository Path**: rayne-gao/ghost_database_finalproject

## Basic Information

- **Project Name**: hospital-management-Ghost
- **Description**: database_finial_project
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-10-15
- **Last Updated**: 2025-10-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# Hospital Management System

## Team Members
- Student 1 – Rayne Gao | 2023903116 高海天
- Student 2 – Danny Chen | 2023905333 陈子明
- Student 3 – Jacob Li | 2023905317 李定诺
- Student 4 – Beluga Ran | 2023905419 冉崧琦

## Project Description
Hospital Management System is a course project that combines a PostgreSQL 17 schema, a FastAPI backend, and a Streamlit dashboard. It covers patient registration, medical staff scheduling, appointment management, medical records, medication inventory, and analytical reports (department revenue, outstanding balances, busy doctors).

## Prerequisites
 PostgreSQL 17  
 Python 3.10 (or higher)  
 pip, Docker Desktop / Docker Engine 20+ (optional)  
 Required libraries: FastAPI, psycopg[binary,pool], Pydantic, Uvicorn, Streamlit, pytest, httpx

## Installation & Setup

### Database Setup
1. Create the database: `createdb hospital_management`
2. Run SQL scripts in order:
   - `psql -U postgres -d hospital_management -f sql/01_create_tables.sql`
   - `psql -U postgres -d hospital_management -f sql/03_create_constraints.sql`
   - `psql -U postgres -d hospital_management -f sql/04_create_triggers_functions.sql`
   - `psql -U postgres -d hospital_management -f sql/02_insert_sample_data.sql`
3. (Optional) Execute `sql/05_update_legacy_schema.sql` when upgrading an older schema.

### Application Setup
1. Create a virtual environment and install dependencies
   ```bash
   python -m venv .venv
   .venv\Scripts\activate        # Windows
   source .venv/bin/activate     # Linux/macOS
   pip install -r requirements.txt
   pip install -r requirements-dev.txt   # testing utilities
   ```
2. Configure environment variables (either `.env` or shell):
   ```env
   DATABASE_URL=postgresql://postgres:925567142@localhost:5432/hospital_management
   API_USERNAME=admin
   API_PASSWORD=admin123
   LOG_LEVEL=INFO
   ```
3. Run the FastAPI application: `uvicorn src.main:app --reload`
4. Launch the Streamlit dashboard (optional): `streamlit run src/streamlit_app.py`

## Usage
- REST API base URL: `http://127.0.0.1:8000`
- Swagger UI: `http://127.0.0.1:8000/docs`
- ReDoc: `http://127.0.0.1:8000/redoc`
- Streamlit dashboard: `http://127.0.0.1:8501` (configure API base URL and credentials in the sidebar)

## Project Structure
```text
project/
- README.md
- docs/
  - API_documentation.md
  - ER_diagram.png
  - architecture.png
  - docker/
  - postman/postman_testing_guide.md
  - templates/
- sql/
  - 01_create_tables.sql
  - 02_insert_sample_data.sql
  - ...
- src/
  - api/
  - database/
  - models/
  - services/
  - main.py
  - streamlit_app.py
- tests/
- requirements.txt
- requirements-dev.txt
- docker-compose.yml
- Dockerfile
- pytest.ini
```

## API Documentation
- In-repo guide: `docs/API_documentation.md`
- Live Swagger UI: `http://127.0.0.1:8000/docs`
- Postman guide: `docs/postman/postman_testing_guide.md`

## Testing
- Install development dependencies: `pip install -r requirements-dev.txt`
- Run the full test suite: `.venv\Scripts\python.exe -m pytest`
- Linux/macOS equivalent: `python -m pytest`

## Docker Compose Workflow
1. Ensure Docker Desktop / Docker Engine is running.
2. Build and start services:
   ```bash
   docker compose up --build
   ```
   - `hms-db`: PostgreSQL 17 container, seeded via `sql/`
   - `hms-api`: FastAPI service exposed on `http://localhost:8000`
3. Tail logs: `docker compose logs -f api` / `docker compose logs -f db`
4. Shut down:
   ```bash
   docker compose down          # stop containers
   docker compose down -v       # stop and remove volumes (data loss)
   ```

## Authentication
- All `/api/*` endpoints require HTTP Basic authentication.
- Default credentials: `admin` / `admin123`
- Override via environment variables `API_USERNAME` and `API_PASSWORD`.
- Include an `Authorization: Basic …` header in Postman/Streamlit/curl clients.

## Troubleshooting
1. Cannot connect to PostgreSQL – confirm the service is running, and that `.env` / `PG*` variables match your instance.
2. Dependency installation issues – upgrade pip (`python -m pip install --upgrade pip`) or configure proxy settings.
3. Trigger/constraint failures – ensure SQL scripts executed in order and dependent objects exist.
4. Streamlit cannot reach API – verify FastAPI is running and the Streamlit sidebar points to the correct base URL.
5. Missing logs – check the FastAPI terminal; set `LOG_LEVEL=DEBUG` for SQL traces.

## Evaluation Criteria Mapping
- **Database Design & Implementation (50%)**
  - *ER Model (10%)* – Conceptual design referenced in `docs/ER_diagram.png`, applied by `sql/01_create_tables.sql` and healthcare-specific tables (e.g., `patients`, `medical_staff`, `appointments`).
  - *Table Design (15%)* – Detailed in `sql/01_create_tables.sql`, using appropriate data types (`DATE`, `TIMESTAMP`, `DECIMAL`) and snake_case naming.
  - *Constraints (10%)* – Implemented in `sql/03_create_constraints.sql` with primary/foreign keys, `CHECK`, `UNIQUE`, and `NOT NULL` clauses.
  - *Advanced Features (10%)* – `sql/04_create_triggers_functions.sql` adds PL/pgSQL functions (`check_doctor_conflict`, `update_invoice_total`, `sync_room_status`) and associated triggers.
  - *Sample Data (5%)* – Realistic seed set in `sql/02_insert_sample_data.sql` (20+ departments, 25 patients, invoices, etc.), maintaining referential integrity.

- **Application Code (35%)**
  - *Code Quality (18%)* – Structured FastAPI modules (`src/api/`, `src/models/`, `src/services/sql_builder.py`) and Streamlit dashboard (`src/streamlit_app.py`) with concise helper functions and minimal, purposeful comments.
  - *CRUD Implementation (10%)* – Complete create/read/update/delete flows across routers:
    - Patients: `src/api/routers/patients.py`
    - Medical staff: `src/api/routers/medical_staff.py`
    - Appointments: `src/api/routers/appointments.py`
    - Medical records: `src/api/routers/medical_records.py`
    - Medications: `src/api/routers/medications.py`
  - *Complex Queries (5%)* – Analytic endpoints in `src/api/routers/reports.py` implement joins, CTEs, and aggregation (department revenue, outstanding invoices, busy doctors).
  - *Database Connection Management (2%)* – Connection pooling, scoped sessions, and transaction handling reside in `src/database/connection.py` (with session wrapper and `get_db_session` dependency).

- **REST API (10%)**
  - *Endpoints (5%)* – RESTful routers under `src/api/routers/` expose GET/POST/PUT/DELETE methods aligned with resource semantics.
  - *Error Handling (3%)* – Global exception handlers and consistent 4xx/5xx responses in `src/api/error_handlers.py`, plus per-route HTTP exceptions on conflicts or missing records.
  - *API Functionality (2%)* – Endpoints validated via automated tests (`tests/api/test_patients.py`, `tests/api/test_appointments.py`, `tests/api/test_reports.py`) ensuring payload formats and behavior.

- **Documentation (5%)**
  - *README (2%)* – You are here; setup, usage, structure, and workflow described in `README.md`.
  - *Code Comments (1%)* – Reserved for non-obvious logic (e.g., `src/database/connection.py` query logging helpers) while most code remains self-explanatory.
  - *API Documentation (1%)* – `docs/API_documentation.md` plus live Swagger UI (`src/main.py` → `/docs`) and Postman guide `docs/postman/postman_testing_guide.md`.
  - *Diagrams (1%)* – `docs/ER_diagram.png` and `docs/architecture.png` document schema and application flow.

- **Additional Requirements**
  - *Authentication – Basic API authentication* – Implemented in `src/api/auth.py` and enforced by router dependencies in `src/api/__init__.py`; credentials configurable via `.env`.
  - *Logging – Application and query logging* – HTTP middleware in `src/main.py` logs requests; database query logging occurs via `DatabaseSession._log_query` in `src/database/connection.py` when `LOG_LEVEL=DEBUG`.
  - *Docker – Containerized application/database* – Dockerfile and `docker-compose.yml` build FastAPI and PostgreSQL services; initialization scripts in `docs/docker/initdb/`.
  - *Testing – Unit tests for critical functions* – Pytest suite in `tests/` covers SQL builder (`tests/unit/test_sql_builder.py`), session handling (`tests/unit/test_database_session.py`), authentication (`tests/unit/test_auth.py`), and API behavior (`tests/api/*.py`).