# hospital-management-whatever

**Repository Path**: verena1/whatever

## Basic Information

- **Project Name**: hospital-management-whatever
- **Description**: No description available
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-10-21
- **Last Updated**: 2025-10-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# Hospital Management System

A comprehensive database-driven hospital management system with a REST API built using Python, PostgreSQL, and FastAPI.

## Team Members

- Student 1: Verena | 陈溢苹 | 2023905495
- Student 2: Fannie | 曾羽凡 | 2023905939
- Student 3: Hongrui Bian | 边红瑞 | 2023905334
- Student 4: Jacinda | 张馨月 | 2023905322

## Project Description

This Hospital Management System is a full-stack application designed to manage various aspects of hospital operations including:

- **Patient Management**: Register and manage patient information, medical history, and appointments
- **Staff Management**: Manage doctors, nurses, and administrative staff
- **Appointment System**: Schedule and track patient appointments with doctors
- **Medical Records**: Maintain comprehensive medical records with diagnoses and treatment plans
- **Prescription Management**: Track medications prescribed to patients
- **Department Management**: Organize hospital departments and their staff
- **Billing System**: Generate and track patient bills and payments
- **Analytics**: Complex queries for hospital performance metrics and statistics

### Key Features

- RESTful API with comprehensive CRUD operations
- Advanced SQL queries with JOINs, aggregates, and CTEs
- Database triggers and stored functions
- Connection pooling for optimal performance
- Input validation and error handling
- Comprehensive API documentation with Swagger UI

## Prerequisites

Before running this application, ensure you have the following installed:

- **PostgreSQL 17+** (or PostgreSQL 12+)
- **Python 3.10+**
- **pip** (Python package manager)

## Installation & Setup

### 1. Database Setup

#### Step 1: Create Database

Open PostgreSQL command line or pgAdmin and create a new database:

```sql
CREATE DATABASE hospital_db;
```

#### Step 2: Run SQL Scripts

Execute the SQL scripts in order to set up the database schema, constraints, and sample data:

```bash
# Connect to the database
psql -U postgres -d hospital_db

# Run the scripts in order
\i sql/01_create_tables.sql
\i sql/02_insert_sample_data.sql
\i sql/03_create_constraints.sql
\i sql/04_create_triggers_functions.sql
```

Alternatively, you can run them using psql from the command line:

```bash
psql -U postgres -d hospital_db -f sql/01_create_tables.sql
psql -U postgres -d hospital_db -f sql/02_insert_sample_data.sql
psql -U postgres -d hospital_db -f sql/03_create_constraints.sql
psql -U postgres -d hospital_db -f sql/04_create_triggers_functions.sql
```

### 2. Application Setup

#### Step 1: Clone the Repository

```bash
git clone <your-gitee-repository-url>
cd hospital-management-system
```

#### Step 2: Create Virtual Environment

```bash
# Create virtual environment
python -m venv venv

# Activate virtual environment
# On Windows:
venv\Scripts\activate
# On macOS/Linux:
source venv/bin/activate
```

#### Step 3: Install Dependencies

```bash
pip install -r requirements.txt
```

#### Step 4: Configure Database Connection

Create a `.env` file in the project root directory:

```bash
# Copy the example file
cp .env.example .env
```

Edit the `.env` file with your database credentials:

```env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=hospital_db
DB_USER=postgres
DB_PASSWORD=your_password_here

APP_HOST=0.0.0.0
APP_PORT=8000
```

### 3. Run the Application

#### Start the API Server

```bash
# Make sure you're in the project root directory
python -m src.main
```

Or using uvicorn directly:

```bash
uvicorn src.main:app --reload --host 0.0.0.0 --port 8000
```

The API will be available at:
- **API Base URL**: http://localhost:8000
- **Interactive API Documentation (Swagger UI)**: http://localhost:8000/docs
- **Alternative API Documentation (ReDoc)**: http://localhost:8000/redoc

## Usage

### API Endpoints

The API provides the following main endpoint groups:

#### Patients (`/api/patients`)
- `GET /api/patients` - List all patients (with pagination)
- `GET /api/patients/{id}` - Get specific patient
- `POST /api/patients` - Create new patient
- `PUT /api/patients/{id}` - Update patient
- `DELETE /api/patients/{id}` - Delete patient
- `GET /api/patients/search/by-name?name={name}` - Search patients by name
- `GET /api/patients/{id}/history` - Get patient medical history

#### Staff (`/api/staff`)
- `GET /api/staff` - List all staff (with pagination)
- `GET /api/staff/{id}` - Get specific staff member
- `POST /api/staff` - Create new staff member
- `PUT /api/staff/{id}` - Update staff member
- `DELETE /api/staff/{id}` - Delete staff member
- `GET /api/staff/doctors` - Get all doctors
- `GET /api/staff/department/{id}` - Get staff by department

#### Registrations/Appointments (`/api/registrations`)
- `GET /api/registrations` - List all registrations
- `GET /api/registrations/{id}` - Get specific registration
- `POST /api/registrations` - Create new registration
- `PUT /api/registrations/{id}` - Update registration
- `DELETE /api/registrations/{id}` - Delete registration
- `GET /api/registrations/patient/{id}` - Get registrations by patient
- `GET /api/registrations/doctor/{id}` - Get registrations by doctor
- `GET /api/registrations/date/{date}` - Get registrations by date

#### Medical Records (`/api/medical-records`)
- `GET /api/medical-records` - List all medical records
- `GET /api/medical-records/{id}` - Get specific medical record
- `POST /api/medical-records` - Create new medical record
- `PUT /api/medical-records/{id}` - Update medical record
- `DELETE /api/medical-records/{id}` - Delete medical record
- `GET /api/medical-records/patient/{id}` - Get records by patient
- `GET /api/medical-records/registration/{id}` - Get records by registration

#### Departments (`/api/departments`)
- `GET /api/departments` - List all departments
- `GET /api/departments/{id}` - Get specific department
- `POST /api/departments` - Create new department
- `PUT /api/departments/{id}` - Update department
- `DELETE /api/departments/{id}` - Delete department

#### Medicines (`/api/medicines`)
- `GET /api/medicines` - List all medicines
- `GET /api/medicines/{id}` - Get specific medicine
- `POST /api/medicines` - Create new medicine
- `PUT /api/medicines/{id}` - Update medicine
- `DELETE /api/medicines/{id}` - Delete medicine
- `GET /api/medicines/search/by-name?name={name}` - Search medicines
- `GET /api/medicines/low-stock?threshold={n}` - Get low stock medicines

#### Complex Queries (`/api/queries`)
- `GET /api/queries/appointments-details` - Appointments with full details
- `GET /api/queries/revenue-by-department` - Revenue statistics by department
- `GET /api/queries/doctors-high-appointments` - Doctors with high appointment counts
- `GET /api/queries/patients-outstanding-bills` - Patients with unpaid bills
- `GET /api/queries/medicine-usage-statistics` - Medicine usage statistics
- `GET /api/queries/patient-visit-frequency` - Patient visit frequency
- `GET /api/queries/department-performance` - Department performance metrics
- `GET /api/queries/prescription-details/{id}` - Prescription details by patient
- `GET /api/queries/monthly-registration-trends` - Monthly registration trends
- `GET /api/queries/staff-workload-analysis` - Staff workload analysis

### Example API Requests

#### Create a New Patient

```bash
curl -X POST "http://localhost:8000/api/patients" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "John Doe",
    "gender": "Male",
    "age": 35,
    "phone": "1234567890",
    "address": "123 Main St",
    "blood_type": "A+"
  }'
```

#### Get All Patients

```bash
curl "http://localhost:8000/api/patients?limit=10&offset=0"
```

#### Search Patients by Name

```bash
curl "http://localhost:8000/api/patients/search/by-name?name=John"
```

## Project Structure

```
hospital-management-system/
├── README.md                          # Project documentation
├── requirements.txt                   # Python dependencies
├── .env.example                       # Environment variables template
├── .gitignore                        # Git ignore file
├── docs/                             # Documentation
│   ├── ER_diagram.pdf                # Entity-Relationship diagram
│   ├── architecture.png              # System architecture diagram
│   └── API_documentation.md          # Detailed API documentation
├── sql/                              # SQL scripts
│   ├── 01_create_tables.sql          # Table creation scripts
│   ├── 02_insert_sample_data.sql     # Sample data insertion
│   ├── 03_create_constraints.sql     # Constraints and indexes
│   └── 04_create_triggers_functions.sql # Triggers and functions
└── src/                              # Source code
    ├── main.py                       # Main application entry point
    ├── database/                     # Database connection module
    │   ├── __init__.py
    │   └── connection.py             # Database connection manager
    ├── models/                       # Data models and CRUD operations
    │   ├── __init__.py
    │   ├── patient.py                # Patient model
    │   ├── staff.py                  # Staff model
    │   ├── registration.py           # Registration model
    │   ├── medical_record.py         # Medical record model
    │   ├── department.py             # Department model
    │   ├── medicine.py               # Medicine model
    │   └── queries.py                # Complex queries
    └── api/                          # API endpoints
        ├── __init__.py
        ├── patients.py               # Patient endpoints
        ├── staff.py                  # Staff endpoints
        ├── registrations.py          # Registration endpoints
        ├── medical_records.py        # Medical record endpoints
        ├── departments.py            # Department endpoints
        ├── medicines.py              # Medicine endpoints
        └── queries.py                # Complex query endpoints
```

## Database Schema

The system uses 10 main tables:

1. **departments** - Hospital departments
2. **staff** - Medical and administrative staff
3. **patients** - Patient information
4. **wards** - Hospital wards/rooms
5. **registrations** - Patient appointments
6. **medical_records** - Medical diagnoses and treatments
7. **medicines** - Medicine inventory
8. **prescriptions** - Prescribed medications
9. **treatments** - Available treatments
10. **bills** - Patient billing information

For detailed schema information, see the ER diagram in `docs/ER_diagram.pdf`.

## Advanced Features

### Database Triggers

1. **update_patients_updated_at** - Automatically updates the `updated_at` timestamp when patient records are modified
2. **update_stock_after_prescription** - Automatically reduces medicine stock when prescriptions are created
3. **prevent_double_booking** - Prevents scheduling conflicts for doctor appointments

### Stored Functions

1. **update_updated_at_column()** - Updates timestamp columns
2. **check_registration_conflict()** - Validates appointment scheduling
3. **update_medicine_stock()** - Manages medicine inventory
4. **calculate_prescription_total()** - Calculates prescription costs
5. **get_patient_registration_count()** - Counts patient visits

### Complex Queries

The system includes 10 complex queries demonstrating:
- Multiple table JOINs (INNER, LEFT, RIGHT)
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
- GROUP BY and HAVING clauses
- Subqueries
- Common Table Expressions (CTEs)
- Window functions

## API Documentation

For detailed API documentation including request/response schemas and examples, visit:
- **Swagger UI**: http://localhost:8000/docs
- **ReDoc**: http://localhost:8000/redoc
- **Detailed Documentation**: See `docs/API_documentation.md`

## Testing

To test the database connection:

```bash
python -c "from src.database import test_connection; test_connection()"
```

To test the API:

```bash
# Health check
curl http://localhost:8000/health

# Root endpoint
curl http://localhost:8000/
```

## Troubleshooting

### Database Connection Issues

If you encounter database connection errors:

1. Verify PostgreSQL is running
2. Check database credentials in `.env` file
3. Ensure the database `hospital_db` exists
4. Verify network connectivity to database server

### Import Errors

If you get import errors:

1. Ensure virtual environment is activated
2. Verify all dependencies are installed: `pip install -r requirements.txt`
3. Check Python version: `python --version` (should be 3.10+)

### Port Already in Use

If port 8000 is already in use:

```bash
# Use a different port
uvicorn src.main:app --reload --port 8001
```

## Contributing

This project was developed as part of a database course final project. For any questions or issues, please contact the team members.