🛠️Installation
Database
CREATE TABLE IF NOT EXISTS `medical_appointments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`doctor_identifier` varchar(50) NOT NULL,
`doctor_name` varchar(200) NOT NULL,
`patient_identifier` varchar(50) NOT NULL,
`patient_name` varchar(200) NOT NULL,
`appointment_date` varchar(20) NOT NULL COMMENT 'Date in YYYY-MM-DD format',
`appointment_time` varchar(10) NOT NULL COMMENT 'Time in HH:MM format',
`reason` text DEFAULT NULL,
`status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, accepted, rejected, completed',
`created_at` int(11) NOT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_doctor` (`doctor_identifier`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_status` (`status`),
KEY `idx_date` (`appointment_date`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_doctor_schedules` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`doctor_identifier` varchar(50) NOT NULL,
`doctor_name` varchar(200) NOT NULL,
`availability` longtext NOT NULL COMMENT 'JSON: {Monday: [times], Tuesday: [times], ...}',
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `doctor_identifier` (`doctor_identifier`),
KEY `idx_doctor` (`doctor_identifier`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`role` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL COMMENT 'Hashed password for employee login',
`created_at` int(11) NOT NULL,
`created_by` varchar(50) NOT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_exams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient_identifier` varchar(50) NOT NULL,
`patient_name` varchar(200) NOT NULL,
`doctor_identifier` varchar(50) NOT NULL,
`doctor_name` varchar(200) NOT NULL,
`exam_type` varchar(255) NOT NULL COMMENT 'Blood Test, X-Ray, MRI, CT Scan, Ultrasound, etc',
`reason` text DEFAULT NULL COMMENT 'Reason for exam request',
`status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, completed, cancelled',
`results` text DEFAULT NULL COMMENT 'Exam results/findings',
`results_notes` text DEFAULT NULL COMMENT 'Additional notes about results',
`completed_by` varchar(200) DEFAULT NULL COMMENT 'Who completed/processed the exam',
`created_at` int(11) NOT NULL,
`completed_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_doctor` (`doctor_identifier`),
KEY `idx_status` (`status`),
KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_interventions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`creator_identifier` varchar(50) NOT NULL,
`creator_name` varchar(200) NOT NULL,
`patient_identifier` varchar(50) DEFAULT NULL,
`patient_name` varchar(200) NOT NULL,
`type` varchar(100) NOT NULL COMMENT 'Accident, Medical Emergency, Fire, Rescue, Other',
`severity` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1-5 scale',
`location` varchar(255) NOT NULL,
`description` text NOT NULL,
`vitals` longtext DEFAULT NULL COMMENT 'JSON: vital signs at intervention',
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_timestamp` (`timestamp`),
KEY `idx_creator` (`creator_identifier`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`identifier` varchar(50) NOT NULL,
`staff_name` varchar(200) NOT NULL COMMENT 'Name of staff member who performed action',
`action` varchar(100) NOT NULL COMMENT 'Type of action performed',
`details` text NOT NULL COMMENT 'Details about the action',
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_identifier` (`identifier`),
KEY `idx_action` (`action`),
KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from_identifier` varchar(50) NOT NULL,
`from_name` varchar(200) NOT NULL,
`to_identifier` varchar(50) NOT NULL,
`to_name` varchar(200) NOT NULL,
`message` text NOT NULL,
`is_read` tinyint(1) NOT NULL DEFAULT 0,
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_from` (`from_identifier`),
KEY `idx_to` (`to_identifier`),
KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_notes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient_identifier` varchar(50) NOT NULL,
`author_identifier` varchar(50) NOT NULL,
`author_name` varchar(200) NOT NULL,
`author_job` varchar(100) NOT NULL,
`note` text NOT NULL,
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_patient_images` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient_identifier` varchar(50) NOT NULL,
`patient_name` varchar(200) NOT NULL,
`uploaded_by_identifier` varchar(50) NOT NULL,
`uploaded_by_name` varchar(200) NOT NULL,
`image_type` varchar(100) NOT NULL COMMENT 'X-Ray, CT Scan, MRI, Wound Photo, Surgery Photo, etc',
`image_url` text NOT NULL COMMENT 'URL or base64 encoded image data',
`description` text DEFAULT NULL COMMENT 'Description of the image',
`body_part` varchar(100) DEFAULT NULL COMMENT 'Body part shown in the image',
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_uploaded_by` (`uploaded_by_identifier`),
KEY `idx_timestamp` (`timestamp`),
KEY `idx_type` (`image_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_patients` (
`identifier` varchar(50) NOT NULL,
`firstname` varchar(100) NOT NULL,
`lastname` varchar(100) NOT NULL,
`birthdate` int(11) DEFAULT NULL,
`bloodtype` varchar(5) DEFAULT NULL,
`marital_status` varchar(50) DEFAULT NULL,
`emergency_contact_name` varchar(200) DEFAULT NULL COMMENT 'Emergency contact person name (for coma patients)',
`emergency_contact_phone` varchar(50) DEFAULT NULL COMMENT 'Emergency contact phone number',
`general_practitioner_id` varchar(50) DEFAULT NULL COMMENT 'Identifier of the patient''s general practitioner',
`general_practitioner_name` varchar(200) DEFAULT NULL COMMENT 'Name of the general practitioner',
`lifestyle_habits` longtext DEFAULT NULL COMMENT 'JSON: {smoking, alcohol}',
`medications` longtext DEFAULT NULL COMMENT 'JSON array of medications',
`medical_history` longtext DEFAULT NULL COMMENT 'JSON array of medical history',
`surgical_history` longtext DEFAULT NULL COMMENT 'JSON array of surgical history',
`last_vitals` longtext DEFAULT NULL COMMENT 'JSON: {pulse, oxygen, bloodPressure, bloodSugar, temperature}',
`last_vitals_timestamp` int(11) DEFAULT NULL,
`created_at` int(11) NOT NULL,
`updated_at` int(11) DEFAULT NULL,
`photo` varchar(500) DEFAULT NULL COMMENT 'Patient profile photo URL',
`phone` varchar(50) DEFAULT NULL COMMENT 'Phone number from lb-phone',
`email` varchar(100) DEFAULT NULL COMMENT 'Email address from lb-phone',
PRIMARY KEY (`identifier`),
KEY `idx_lastname` (`lastname`),
KEY `idx_firstname` (`firstname`),
KEY `idx_gp` (`general_practitioner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_prescriptions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient_identifier` varchar(50) NOT NULL,
`patient_name` varchar(200) NOT NULL,
`doctor_identifier` varchar(50) NOT NULL,
`doctor_name` varchar(200) NOT NULL,
`medication_name` varchar(255) NOT NULL,
`dosage` varchar(100) NOT NULL,
`frequency` varchar(100) NOT NULL,
`duration` varchar(100) NOT NULL COMMENT 'Duration of treatment (e.g. 7 days, 2 weeks)',
`instructions` text DEFAULT NULL COMMENT 'Special instructions for taking the medication',
`quantity` int(11) NOT NULL COMMENT 'Number of units prescribed',
`refills` int(11) NOT NULL DEFAULT 0 COMMENT 'Number of refills allowed',
`diagnosis` text DEFAULT NULL COMMENT 'Medical diagnosis/reason for prescription',
`status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active, filled, expired, cancelled',
`created_at` int(11) NOT NULL,
`expires_at` int(11) DEFAULT NULL COMMENT 'Prescription expiration date',
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_doctor` (`doctor_identifier`),
KEY `idx_status` (`status`),
KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_staff_accounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`identifier` varchar(50) NOT NULL COMMENT 'Player identifier (citizenid/identifier)',
`username` varchar(100) NOT NULL COMMENT 'Login username',
`password` varchar(255) NOT NULL COMMENT 'Hashed password (bcrypt)',
`name` varchar(200) NOT NULL COMMENT 'Full name',
`job` varchar(100) NOT NULL COMMENT 'Job name (paramedic, ambulance, etc)',
`active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Account active status',
`auto_login` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Auto-login enabled (1 = yes, 0 = no)',
`created_at` int(11) NOT NULL,
`last_login` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `identifier` (`identifier`),
UNIQUE KEY `username` (`username`),
KEY `idx_identifier` (`identifier`),
KEY `idx_username` (`username`),
KEY `idx_job` (`job`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `medical_visit_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`patient_identifier` varchar(50) NOT NULL,
`patient_name` varchar(200) NOT NULL,
`doctor_identifier` varchar(50) NOT NULL,
`doctor_name` varchar(200) NOT NULL,
`visit_type` varchar(100) NOT NULL COMMENT 'Emergency, Consultation, Follow-up, Surgery, etc',
`chief_complaint` text NOT NULL COMMENT 'Main reason for visit',
`diagnosis` text DEFAULT NULL COMMENT 'Medical diagnosis',
`treatment` text DEFAULT NULL COMMENT 'Treatment provided',
`vitals` longtext DEFAULT NULL COMMENT 'JSON: vital signs during visit',
`prescriptions_given` text DEFAULT NULL COMMENT 'List of prescriptions given during visit',
`exams_ordered` text DEFAULT NULL COMMENT 'List of exams ordered during visit',
`follow_up_needed` tinyint(1) DEFAULT 0 COMMENT 'Whether follow-up is required',
`follow_up_date` varchar(20) DEFAULT NULL COMMENT 'Recommended follow-up date',
`notes` text DEFAULT NULL COMMENT 'Additional visit notes',
`timestamp` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_patient` (`patient_identifier`),
KEY `idx_doctor` (`doctor_identifier`),
KEY `idx_timestamp` (`timestamp`),
KEY `idx_visit_type` (`visit_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Last updated