Database Structure (Paper-ready)

Database: hospital_system

1) users
- id INT AUTO_INCREMENT PRIMARY KEY
- full_name VARCHAR(150) NOT NULL
- email VARCHAR(200) UNIQUE NOT NULL
- password_hash VARCHAR(255) NOT NULL
- role ENUM('receptionist','doctor') NOT NULL
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2) patients
- id INT AUTO_INCREMENT PRIMARY KEY
- full_name VARCHAR(150) NOT NULL
- gender ENUM('Male','Female','Other') NOT NULL
- date_of_birth DATE NOT NULL
- phone_number VARCHAR(30) NOT NULL
- address VARCHAR(255) NOT NULL
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

3) appointments
- id INT AUTO_INCREMENT PRIMARY KEY
- patient_id INT NOT NULL
- doctor_id INT NOT NULL
- appointment_date DATE NOT NULL
- appointment_time TIME NOT NULL
- status ENUM('scheduled','completed','cancelled') NOT NULL DEFAULT 'scheduled'
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Foreign keys:
- appointments.patient_id -> patients.id (ON DELETE RESTRICT)
- appointments.doctor_id -> users.id (ON DELETE RESTRICT)

4) medical_reports
- id INT AUTO_INCREMENT PRIMARY KEY
- appointment_id INT NOT NULL UNIQUE
- doctor_id INT NOT NULL
- diagnosis TEXT NOT NULL
- prescription TEXT NOT NULL
- report_date DATE NOT NULL
- created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Foreign keys:
- medical_reports.appointment_id -> appointments.id (ON DELETE RESTRICT)
- medical_reports.doctor_id -> users.id (ON DELETE RESTRICT)

