-- PROMIS Database Backup
-- Generated: 2026-04-09 12:10:07

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `admissions`;
CREATE TABLE `admissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `ward_id` int(11) NOT NULL,
  `admission_date` timestamp NULL DEFAULT current_timestamp(),
  `discharge_date` date DEFAULT NULL,
  `discharge_notes` text DEFAULT NULL,
  `status` enum('Active','Discharged','Transferred') DEFAULT 'Active',
  `current_status` varchar(50) DEFAULT 'Active',
  `bed_charge_per_day` decimal(10,2) DEFAULT 0.00,
  `total_bed_charges` decimal(10,2) DEFAULT 0.00,
  `last_billed_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  KEY `ward_id` (`ward_id`),
  CONSTRAINT `admissions_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admissions_ibfk_2` FOREIGN KEY (`ward_id`) REFERENCES `wards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `banking`;
CREATE TABLE `banking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_date` date NOT NULL,
  `type` enum('Deposit','Withdrawal','Transfer') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `consultations`;
CREATE TABLE `consultations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `diagnosis` text DEFAULT NULL,
  `clinical_notes` text DEFAULT NULL,
  `prescriptions_given` text DEFAULT NULL,
  `lab_requests` text DEFAULT NULL,
  `recommendation` enum('Lab','Admit','Pharmacy','Discharge') DEFAULT NULL,
  `ward_id` int(11) DEFAULT NULL,
  `admission_reason` text DEFAULT NULL,
  `consultation_date` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  CONSTRAINT `consultations_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_number` varchar(50) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `basic_salary` decimal(10,2) DEFAULT 0.00,
  `ssnit_number` varchar(50) DEFAULT NULL,
  `daakye_number` varchar(50) DEFAULT NULL,
  `tin` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_number` (`employee_number`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `employees` VALUES ('1','0001','Esi Ruth','OPD','Nurse','2000.00','B2523625565','D2125425325','252325658','CBG','00124256585475','1','2026-04-08 16:06:53');

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_date` date NOT NULL,
  `category` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `expenses` VALUES ('1','2026-04-08','Fuel','Bought GHS 200.00 fuel for wedding ceremony','200.00','2');

DROP TABLE IF EXISTS `lab_test_types`;
CREATE TABLE `lab_test_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `lab_test_types` VALUES ('1','Full Blood Count','Complete blood count test','50.00'),
('2','Malaria Test','Rapid malaria test','20.00'),
('3','Urinalysis','Urine test','30.00'),
('4','Blood Sugar','Fasting or random blood sugar','25.00'),
('5','COVID-19 Test','Rapid antigen test','40.00'),
('6','Typhoid Test','Typhoid fever hay','120.00');

DROP TABLE IF EXISTS `lab_tests`;
CREATE TABLE `lab_tests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `test_name` varchar(100) NOT NULL,
  `test_type_id` int(11) DEFAULT NULL,
  `status` enum('Pending','In Progress','Completed','Cancelled') DEFAULT 'Pending',
  `result` text DEFAULT NULL,
  `technician_notes` text DEFAULT NULL,
  `requested_by` int(11) DEFAULT NULL,
  `payment_request_id` int(11) DEFAULT NULL,
  `completed_by` int(11) DEFAULT NULL,
  `result_entered_by` int(11) DEFAULT NULL,
  `result_date` timestamp NULL DEFAULT NULL,
  `requested_at` timestamp NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  CONSTRAINT `lab_tests_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `patients`;
CREATE TABLE `patients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_number` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `insurance_type` enum('NHIS','APEX','GLICO','PIZZA MAN','None') DEFAULT 'None',
  `insurance_number` varchar(100) DEFAULT NULL,
  `insurance_valid` tinyint(1) DEFAULT 1,
  `is_deceased` tinyint(4) DEFAULT 0,
  `death_date` datetime DEFAULT NULL,
  `death_place` varchar(100) DEFAULT NULL,
  `cause_of_death` text DEFAULT NULL,
  `death_certified_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `patient_number` (`patient_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `payment_requests`;
CREATE TABLE `payment_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `service_type` varchar(50) NOT NULL COMMENT 'Folder, Lab, Pharmacy, Admission',
  `service_name` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` enum('Pending','Paid','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `paid_at` timestamp NULL DEFAULT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  CONSTRAINT `payment_requests_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `amount_tendered` decimal(10,2) DEFAULT 0.00,
  `change_given` decimal(10,2) DEFAULT 0.00,
  `payment_method` enum('Cash','Card','Insurance','Mobile Money') DEFAULT 'Cash',
  `receipt_number` varchar(50) NOT NULL,
  `payment_date` timestamp NULL DEFAULT current_timestamp(),
  `processed_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `visit_id` (`visit_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `payroll`;
CREATE TABLE `payroll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `pay_month` date NOT NULL,
  `basic_salary` decimal(10,2) NOT NULL,
  `ssnit_employee` decimal(10,2) DEFAULT 0.00,
  `ssnit_employer` decimal(10,2) DEFAULT 0.00,
  `daakye_pension` decimal(10,2) DEFAULT 0.00,
  `paye_tax` decimal(10,2) DEFAULT 0.00,
  `other_deductions` decimal(10,2) DEFAULT 0.00,
  `net_salary` decimal(10,2) DEFAULT 0.00,
  `payment_status` enum('Pending','Paid') DEFAULT 'Pending',
  `processed_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`),
  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `payroll` VALUES ('1','1','2026-04-01','2000.00','110.00','260.00','100.00','152.40','0.00','1637.60','Pending',NULL,'2026-04-08 16:32:46');

DROP TABLE IF EXISTS `pharmacy_inventory`;
CREATE TABLE `pharmacy_inventory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `medicine_name` varchar(100) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `quantity` int(11) DEFAULT 0,
  `unit_price` decimal(10,2) DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `reorder_level` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `pharmacy_inventory` VALUES ('1','Paracetamol','Tablet','38','25.00','2026-03-06','10'),
('2','Capecitabine Tablet, 500 mg','Tablet','49','19.00','2026-08-06','10'),
('3','Calcium Carbonate Tablet, 500 mg','Tablet','49','25.00','2026-11-11','10');

DROP TABLE IF EXISTS `prescriptions`;
CREATE TABLE `prescriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `consultation_id` int(11) NOT NULL,
  `medicine_name` varchar(100) NOT NULL,
  `dosage` varchar(100) NOT NULL,
  `frequency` varchar(100) NOT NULL,
  `duration` varchar(50) NOT NULL,
  `instructions` text DEFAULT NULL,
  `status` enum('Pending','Dispensed','Cancelled') DEFAULT 'Pending',
  `dispensed_at` timestamp NULL DEFAULT NULL,
  `dispensed_by` int(11) DEFAULT NULL,
  `dispensing_notes` text DEFAULT NULL,
  `payment_request_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `consultation_id` (`consultation_id`),
  CONSTRAINT `prescriptions_ibfk_1` FOREIGN KEY (`consultation_id`) REFERENCES `consultations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `system_logs`;
CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `system_logs` VALUES ('1','1','RESET: COMPLETE - SUCCESS','Reset completed with selected options','::1','2026-04-09 10:35:01');

DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `system_settings` VALUES ('1','folder_fee','30','Fee for new patient folder/card registration'),
('2','nhis_hi_code','','NHIA Facility HI Code'),
('3','hospital_name','SAINT JOSEPH CLINIC','Official hospital name for receipts'),
('4','hospital_address','@ AGONA SWEDRU BANGALO','Hospital address'),
('5','hospital_phone','0244565857',NULL),
('6','hospital_email','nextplux200@gmail.com',NULL),
('7','receipt_footer','Thank you for choosing SAINT JOSEPH',NULL),
('8','receipt_header','Patient Records & Operational Management',NULL);

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `department` varchar(50) NOT NULL,
  `role` varchar(50) DEFAULT 'staff',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `users` VALUES ('1','admin','admin123','System Administrator',NULL,'Administration','admin','1','2026-04-07 18:33:06'),
('2','accounts','accounts123','Accounts Officer',NULL,'Accounts','staff','1','2026-04-07 19:01:53'),
('3','reception','reception123','Reception Desk',NULL,'Reception','staff','1','2026-04-07 19:01:53'),
('4','opd','opd123','OPD Nurse',NULL,'OPD','staff','1','2026-04-07 19:01:53'),
('5','doctor','doctor123','General Doctor',NULL,'Doctors','doctor','1','2026-04-07 19:01:53'),
('6','lab','lab123','Lab Technician',NULL,'Lab','staff','1','2026-04-07 19:01:53'),
('7','pharmacy','pharmacy123','Pharmacist',NULL,'Pharmacy','staff','1','2026-04-07 19:01:53'),
('8','revenue','revenue123','Revenue Officer',NULL,'Revenue','staff','1','2026-04-07 19:01:53'),
('9','wards','wards123','Ward Nurse',NULL,'Wards','staff','1','2026-04-07 19:01:53'),
('10','anc','anc123','ANC Midwife',NULL,'ANC','staff','1','2026-04-07 19:01:53'),
('11','nurse','nurse123','Joe Addo','nextplux200@gmail.com','Revenue','staff','1','2026-04-07 20:03:43');

DROP TABLE IF EXISTS `visits`;
CREATE TABLE `visits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL,
  `visit_date` date NOT NULL,
  `visit_type` enum('New','Review') DEFAULT 'New',
  `department_from` varchar(50) DEFAULT NULL,
  `referred_to` varchar(50) DEFAULT NULL,
  `current_location` varchar(50) DEFAULT 'OPD',
  `gestational_age` int(11) DEFAULT NULL,
  `gravida` int(11) DEFAULT NULL,
  `para` int(11) DEFAULT NULL,
  `lmp` date DEFAULT NULL,
  `edd` date DEFAULT NULL,
  `anc_visit_number` int(11) DEFAULT 1,
  `payment_status` enum('Pending','Paid','Partial') DEFAULT 'Pending',
  `total_amount` decimal(10,2) DEFAULT 0.00,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `status` enum('Active','Completed','Cancelled') DEFAULT 'Active',
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  CONSTRAINT `visits_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `vitals`;
CREATE TABLE `vitals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `temperature` varchar(10) DEFAULT NULL,
  `blood_pressure` varchar(20) DEFAULT NULL,
  `heart_rate` varchar(10) DEFAULT NULL,
  `respiratory_rate` varchar(10) DEFAULT NULL,
  `weight` varchar(10) DEFAULT NULL,
  `height` varchar(10) DEFAULT NULL,
  `bmi` varchar(10) DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `chief_complaint` text DEFAULT NULL,
  `taken_by` int(11) DEFAULT NULL,
  `taken_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  CONSTRAINT `vitals_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `ward_medications`;
CREATE TABLE `ward_medications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admission_id` int(11) NOT NULL,
  `prescription_id` int(11) NOT NULL,
  `medicine_name` varchar(100) NOT NULL,
  `dosage` varchar(100) DEFAULT NULL,
  `administered_at` timestamp NULL DEFAULT current_timestamp(),
  `administered_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admission_id` (`admission_id`),
  KEY `prescription_id` (`prescription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `ward_progress`;
CREATE TABLE `ward_progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admission_id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `medications_given` text DEFAULT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admission_id` (`admission_id`),
  CONSTRAINT `ward_progress_ibfk_1` FOREIGN KEY (`admission_id`) REFERENCES `admissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `wards`;
CREATE TABLE `wards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ward_name` varchar(100) NOT NULL,
  `capacity` int(11) DEFAULT 0,
  `current_occupancy` int(11) DEFAULT 0,
  `daily_bed_charge` decimal(10,2) DEFAULT 50.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `wards` VALUES ('1','General Ward','20','1','50.00'),
('2','Private Ward','10','1','100.00'),
('3','ICU','5','0','200.00'),
('4','Maternity Ward','15','0','80.00');

SET FOREIGN_KEY_CHECKS=1;