-- ============================================================ -- MediCare Hospital Management System -- MySQL Database Schema (for cPanel deployment) -- Version 1.0 -- ============================================================ CREATE DATABASE IF NOT EXISTS medicare_hms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE medicare_hms; -- ----------------------------------------------------------- -- TENANT (Multi-tenant SaaS) -- ----------------------------------------------------------- CREATE TABLE `tenants` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `slug` VARCHAR(100) NOT NULL UNIQUE, `domain` VARCHAR(255) NULL, `subscription_tier` ENUM('BASIC','PROFESSIONAL','ENTERPRISE') NOT NULL DEFAULT 'BASIC', `settings` JSON NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BRANCH -- ----------------------------------------------------------- CREATE TABLE `branches` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(50) NOT NULL, `address` TEXT NULL, `phone` VARCHAR(50) NULL, `email` VARCHAR(255) NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `uk_branch_code` (`code`, `tenant_id`), FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PERMISSION -- ----------------------------------------------------------- CREATE TABLE `permissions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `name` VARCHAR(100) NOT NULL UNIQUE, `description` TEXT NULL, `module` VARCHAR(50) NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- ROLE -- ----------------------------------------------------------- CREATE TABLE `roles` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(50) NOT NULL, `description` TEXT NULL, `is_system` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uk_role_name` (`name`, `tenant_id`), FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- ROLE_PERMISSION -- ----------------------------------------------------------- CREATE TABLE `role_permissions` ( `role_id` VARCHAR(36) NOT NULL, `permission_id` VARCHAR(36) NOT NULL, PRIMARY KEY (`role_id`, `permission_id`), FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE, FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- USER -- ----------------------------------------------------------- CREATE TABLE `users` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `name` VARCHAR(255) NOT NULL, `phone` VARCHAR(50) NULL, `avatar` VARCHAR(500) NULL, `role_id` VARCHAR(36) NOT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `email_verified` TINYINT(1) NOT NULL DEFAULT 0, `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0, `two_factor_secret` VARCHAR(255) NULL, `last_login` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- USER_SESSION -- ----------------------------------------------------------- CREATE TABLE `user_sessions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `user_id` VARCHAR(36) NOT NULL, `token` VARCHAR(500) NOT NULL, `ip_address` VARCHAR(50) NULL, `user_agent` TEXT NULL, `expires_at` DATETIME NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- AUDIT_LOG -- ----------------------------------------------------------- CREATE TABLE `audit_logs` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `user_id` VARCHAR(36) NULL, `action` VARCHAR(100) NOT NULL, `entity` VARCHAR(50) NOT NULL, `entity_id` VARCHAR(36) NULL, `details` JSON NULL, `ip_address` VARCHAR(50) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PATIENT -- ----------------------------------------------------------- CREATE TABLE `patients` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `mrn` VARCHAR(50) NOT NULL UNIQUE, `first_name` VARCHAR(100) NOT NULL, `last_name` VARCHAR(100) NOT NULL, `date_of_birth` DATE NULL, `gender` ENUM('MALE','FEMALE','OTHER') NULL, `blood_group` VARCHAR(5) NULL, `phone` VARCHAR(50) NOT NULL, `email` VARCHAR(255) NULL, `address` TEXT NULL, `city` VARCHAR(100) NULL, `state` VARCHAR(100) NULL, `country` VARCHAR(100) NULL, `postal_code` VARCHAR(20) NULL, `occupation` VARCHAR(100) NULL, `marital_status` VARCHAR(20) NULL, `nationality` VARCHAR(100) NULL, `religion` VARCHAR(50) NULL, `emergency_contact_name` VARCHAR(255) NULL, `emergency_contact_phone` VARCHAR(50) NULL, `emergency_contact_relation` VARCHAR(50) NULL, `insurance_provider` VARCHAR(255) NULL, `insurance_policy_no` VARCHAR(100) NULL, `insurance_expiry` DATE NULL, `allergies` TEXT NULL, `chronic_conditions` TEXT NULL, `notes` TEXT NULL, `avatar` VARCHAR(500) NULL, `qr_code` TEXT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PATIENT_DOCUMENT -- ----------------------------------------------------------- CREATE TABLE `patient_documents` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `patient_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `type` VARCHAR(50) NULL, `file_url` VARCHAR(500) NOT NULL, `uploaded_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PATIENT_TIMELINE -- ----------------------------------------------------------- CREATE TABLE `patient_timelines` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `patient_id` VARCHAR(36) NOT NULL, `event_type` VARCHAR(50) NOT NULL, `description` TEXT NULL, `reference_id` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- DOCTOR -- ----------------------------------------------------------- CREATE TABLE `doctors` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `user_id` VARCHAR(36) NOT NULL UNIQUE, `license_number` VARCHAR(100) NOT NULL UNIQUE, `specialization` VARCHAR(255) NOT NULL, `qualification` TEXT NULL, `experience` INT NULL, `consultation_fee` DECIMAL(10,2) NOT NULL DEFAULT 0, `follow_up_fee` DECIMAL(10,2) NULL DEFAULT 0, `online_fee` DECIMAL(10,2) NULL DEFAULT 0, `rating` DECIMAL(3,2) NULL DEFAULT 0, `is_available_for_telemedicine` TINYINT(1) NOT NULL DEFAULT 0, `bio` TEXT NULL, `education` JSON NULL, `experience_details` JSON NULL, `certifications` JSON NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- DOCTOR_SCHEDULE -- ----------------------------------------------------------- CREATE TABLE `doctor_schedules` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `doctor_id` VARCHAR(36) NOT NULL, `day_of_week` TINYINT NOT NULL, `start_time` TIME NOT NULL, `end_time` TIME NOT NULL, `slot_duration` INT NOT NULL DEFAULT 15, `is_available` TINYINT(1) NOT NULL DEFAULT 1, `branch_id` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- DOCTOR_LEAVE -- ----------------------------------------------------------- CREATE TABLE `doctor_leaves` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `doctor_id` VARCHAR(36) NOT NULL, `start_date` DATE NOT NULL, `end_date` DATE NOT NULL, `reason` TEXT NULL, `status` ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING', `approved_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- DOCTOR_PERFORMANCE -- ----------------------------------------------------------- CREATE TABLE `doctor_performances` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `doctor_id` VARCHAR(36) NOT NULL, `date` DATE NOT NULL, `patients_count` INT NOT NULL DEFAULT 0, `revenue` DECIMAL(10,2) NOT NULL DEFAULT 0, `rating` DECIMAL(3,2) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- APPOINTMENT -- ----------------------------------------------------------- CREATE TABLE `appointments` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `date` DATE NOT NULL, `start_time` TIME NOT NULL, `end_time` TIME NULL, `status` ENUM('SCHEDULED','CONFIRMED','CHECKED_IN','IN_PROGRESS','COMPLETED','CANCELLED','NO_SHOW') NOT NULL DEFAULT 'SCHEDULED', `type` ENUM('IN_PERSON','VIDEO','AUDIO','CHAT') NOT NULL DEFAULT 'IN_PERSON', `token_number` INT NULL, `queue_position` INT NULL, `reason` TEXT NULL, `notes` TEXT NULL, `cancellation_reason` TEXT NULL, `is_follow_up` TINYINT(1) NOT NULL DEFAULT 0, `follow_up_from_id` VARCHAR(36) NULL, `created_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`follow_up_from_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL, FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- WAITING_LIST -- ----------------------------------------------------------- CREATE TABLE `waiting_lists` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `date` DATE NOT NULL, `preferred_time` TIME NULL, `status` ENUM('WAITING','CALLED','CANCELLED') NOT NULL DEFAULT 'WAITING', `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- TELEMEDICINE_SESSION -- ----------------------------------------------------------- CREATE TABLE `telemedicine_sessions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `appointment_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `session_type` ENUM('VIDEO','AUDIO','CHAT') NOT NULL, `status` VARCHAR(50) NOT NULL DEFAULT 'SCHEDULED', `start_time` DATETIME NULL, `end_time` DATETIME NULL, `duration` INT NULL, `recording_url` VARCHAR(500) NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`appointment_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- TELEMEDICINE_FILE -- ----------------------------------------------------------- CREATE TABLE `telemedicine_files` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `session_id` VARCHAR(36) NOT NULL, `uploaded_by` VARCHAR(36) NULL, `file_name` VARCHAR(255) NOT NULL, `file_url` VARCHAR(500) NOT NULL, `file_type` VARCHAR(50) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions`(`id`) ON DELETE CASCADE, FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OPD PATIENT VISIT -- ----------------------------------------------------------- CREATE TABLE `opd_patient_visits` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `appointment_id` VARCHAR(36) NULL, `visit_date` DATETIME NOT NULL, `presenting_complaint` TEXT NULL, `history_of_presenting_illness` TEXT NULL, `past_medical_history` TEXT NULL, `family_history` TEXT NULL, `social_history` TEXT NULL, `review_of_systems` TEXT NULL, `diagnosis` TEXT NULL, `treatment_plan` TEXT NULL, `notes` TEXT NULL, `referral_doctor` VARCHAR(255) NULL, `referral_reason` TEXT NULL, `follow_up_date` DATE NULL, `created_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`appointment_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL, FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OPD PROCEDURE -- ----------------------------------------------------------- CREATE TABLE `opd_procedures` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `visit_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `description` TEXT NULL, `result` TEXT NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`visit_id`) REFERENCES `opd_patient_visits`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- WARD -- ----------------------------------------------------------- CREATE TABLE `wards` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(50) NOT NULL, `type` ENUM('GENERAL','SEMI_PRIVATE','PRIVATE','ICU','CCU','NICU','PICU') NOT NULL DEFAULT 'GENERAL', `total_beds` INT NOT NULL DEFAULT 0, `charges_per_day` DECIMAL(10,2) NOT NULL DEFAULT 0, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BED -- ----------------------------------------------------------- CREATE TABLE `beds` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `ward_id` VARCHAR(36) NOT NULL, `bed_number` VARCHAR(50) NOT NULL, `status` ENUM('AVAILABLE','OCCUPIED','RESERVED','MAINTENANCE') NOT NULL DEFAULT 'AVAILABLE', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`ward_id`) REFERENCES `wards`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- IPD ADMISSION -- ----------------------------------------------------------- CREATE TABLE `ipd_admissions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `ward_id` VARCHAR(36) NULL, `bed_id` VARCHAR(36) NULL, `admission_date` DATETIME NOT NULL, `admission_type` ENUM('EMERGENCY','ELECTIVE','URGENT') NOT NULL DEFAULT 'ELECTIVE', `diagnosis` TEXT NULL, `reason` TEXT NULL, `referred_by` VARCHAR(255) NULL, `expected_discharge_date` DATE NULL, `discharge_date` DATETIME NULL, `discharge_summary` TEXT NULL, `status` ENUM('ADMITTED','TRANSFERRED','DISCHARGED') NOT NULL DEFAULT 'ADMITTED', `created_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`ward_id`) REFERENCES `wards`(`id`) ON DELETE SET NULL, FOREIGN KEY (`bed_id`) REFERENCES `beds`(`id`) ON DELETE SET NULL, FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- IPD DAILY NOTE -- ----------------------------------------------------------- CREATE TABLE `ipd_daily_notes` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `admission_id` VARCHAR(36) NOT NULL, `nurse_id` VARCHAR(36) NULL, `date` DATE NOT NULL, `shift` VARCHAR(20) NULL, `subjective` TEXT NULL, `objective` TEXT NULL, `assessment` TEXT NULL, `plan` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE CASCADE, FOREIGN KEY (`nurse_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- IPD DIET PLAN -- ----------------------------------------------------------- CREATE TABLE `ipd_diet_plans` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `admission_id` VARCHAR(36) NOT NULL, `date` DATE NOT NULL, `meal_type` VARCHAR(50) NULL, `items` TEXT NULL, `instructions` TEXT NULL, `created_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE CASCADE, FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- IPD TREATMENT PLAN -- ----------------------------------------------------------- CREATE TABLE `ipd_treatment_plans` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `admission_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `diagnosis` TEXT NULL, `treatment` TEXT NULL, `medications` JSON NULL, `investigations` JSON NULL, `follow_up` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- EMERGENCY CASE -- ----------------------------------------------------------- CREATE TABLE `emergency_cases` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `triage_category` ENUM('RESUSCITATION','EMERGENT','URGENT','SEMI_URGENT','NON_URGENT') NOT NULL, `arrival_mode` VARCHAR(50) NULL, `complaint` TEXT NULL, `vital_signs` JSON NULL, `diagnosis` TEXT NULL, `treatment` TEXT NULL, `disposition` TEXT NULL, `admitted_to_ward_id` VARCHAR(36) NULL, `doctor_id` VARCHAR(36) NULL, `nurse_id` VARCHAR(36) NULL, `registered_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`admitted_to_ward_id`) REFERENCES `wards`(`id`) ON DELETE SET NULL, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE SET NULL, FOREIGN KEY (`nurse_id`) REFERENCES `users`(`id`) ON DELETE SET NULL, FOREIGN KEY (`registered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- AMBULANCE -- ----------------------------------------------------------- CREATE TABLE `ambulances` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `vehicle_number` VARCHAR(50) NOT NULL, `model` VARCHAR(255) NULL, `driver_name` VARCHAR(255) NULL, `driver_phone` VARCHAR(50) NULL, `driver_license` VARCHAR(100) NULL, `is_available` TINYINT(1) NOT NULL DEFAULT 1, `latitude` DECIMAL(10,8) NULL, `longitude` DECIMAL(11,8) NULL, `last_location_update` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- AMBULANCE BOOKING -- ----------------------------------------------------------- CREATE TABLE `ambulance_bookings` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `ambulance_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `pickup_location` TEXT NOT NULL, `dropoff_location` TEXT NOT NULL, `pickup_lat` DECIMAL(10,8) NULL, `pickup_lng` DECIMAL(11,8) NULL, `dropoff_lat` DECIMAL(10,8) NULL, `dropoff_lng` DECIMAL(11,8) NULL, `status` ENUM('PENDING','ASSIGNED','EN_ROUTE','AT_PICKUP','TRANSPORTING','ARRIVED','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING', `booked_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`ambulance_id`) REFERENCES `ambulances`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`booked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY MEDICINE -- ----------------------------------------------------------- CREATE TABLE `pharmacy_medicines` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `name` VARCHAR(255) NOT NULL, `generic_name` VARCHAR(255) NULL, `category` VARCHAR(100) NULL, `manufacturer` VARCHAR(255) NULL, `unit` VARCHAR(50) NOT NULL DEFAULT 'Piece', `price` DECIMAL(10,2) NOT NULL DEFAULT 0, `selling_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `reorder_level` INT NOT NULL DEFAULT 10, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY BATCH -- ----------------------------------------------------------- CREATE TABLE `pharmacy_batches` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `medicine_id` VARCHAR(36) NOT NULL, `batch_number` VARCHAR(100) NOT NULL, `expiry_date` DATE NOT NULL, `quantity` INT NOT NULL DEFAULT 0, `purchase_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `selling_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `received_date` DATE NULL, `supplier_id` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`medicine_id`) REFERENCES `pharmacy_medicines`(`id`) ON DELETE CASCADE, FOREIGN KEY (`supplier_id`) REFERENCES `pharmacy_suppliers`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY SUPPLIER -- ----------------------------------------------------------- CREATE TABLE `pharmacy_suppliers` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `contact_person` VARCHAR(255) NULL, `phone` VARCHAR(50) NULL, `email` VARCHAR(255) NULL, `address` TEXT NULL, `gst_no` VARCHAR(50) NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY PURCHASE ORDER -- ----------------------------------------------------------- CREATE TABLE `pharmacy_purchase_orders` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `supplier_id` VARCHAR(36) NULL, `order_number` VARCHAR(50) NOT NULL, `status` VARCHAR(50) NOT NULL DEFAULT 'PENDING', `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `notes` TEXT NULL, `ordered_by` VARCHAR(36) NULL, `received_by` VARCHAR(36) NULL, `received_date` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`supplier_id`) REFERENCES `pharmacy_suppliers`(`id`) ON DELETE SET NULL, FOREIGN KEY (`ordered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY PURCHASE ITEM -- ----------------------------------------------------------- CREATE TABLE `pharmacy_purchase_items` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `purchase_order_id` VARCHAR(36) NOT NULL, `medicine_id` VARCHAR(36) NOT NULL, `batch_id` VARCHAR(36) NULL, `quantity` INT NOT NULL DEFAULT 0, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`purchase_order_id`) REFERENCES `pharmacy_purchase_orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`medicine_id`) REFERENCES `pharmacy_medicines`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY SALE -- ----------------------------------------------------------- CREATE TABLE `pharmacy_sales` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NULL, `prescription_id` VARCHAR(36) NULL, `invoice_no` VARCHAR(50) NOT NULL UNIQUE, `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0, `discount` DECIMAL(10,2) NOT NULL DEFAULT 0, `tax` DECIMAL(10,2) NOT NULL DEFAULT 0, `total` DECIMAL(10,2) NOT NULL DEFAULT 0, `payment_method` VARCHAR(50) NULL, `payment_status` VARCHAR(50) NOT NULL DEFAULT 'PAID', `cashier_id` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE SET NULL, FOREIGN KEY (`cashier_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY SALE ITEM -- ----------------------------------------------------------- CREATE TABLE `pharmacy_sale_items` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `sale_id` VARCHAR(36) NOT NULL, `medicine_id` VARCHAR(36) NOT NULL, `batch_id` VARCHAR(36) NULL, `quantity` INT NOT NULL DEFAULT 0, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`sale_id`) REFERENCES `pharmacy_sales`(`id`) ON DELETE CASCADE, FOREIGN KEY (`medicine_id`) REFERENCES `pharmacy_medicines`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PHARMACY RETURN -- ----------------------------------------------------------- CREATE TABLE `pharmacy_returns` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `sale_id` VARCHAR(36) NOT NULL, `medicine_id` VARCHAR(36) NOT NULL, `batch_id` VARCHAR(36) NULL, `quantity` INT NOT NULL DEFAULT 0, `reason` TEXT NULL, `date` DATE NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`sale_id`) REFERENCES `pharmacy_sales`(`id`) ON DELETE CASCADE, FOREIGN KEY (`medicine_id`) REFERENCES `pharmacy_medicines`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- LAB TEST -- ----------------------------------------------------------- CREATE TABLE `lab_tests` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(50) NOT NULL, `category` VARCHAR(100) NULL, `sample_type` VARCHAR(50) NULL, `reference_ranges` JSON NULL, `price` DECIMAL(10,2) NOT NULL DEFAULT 0, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- LAB ORDER -- ----------------------------------------------------------- CREATE TABLE `lab_orders` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `test_id` VARCHAR(36) NOT NULL, `appointment_id` VARCHAR(36) NULL, `status` ENUM('ORDERED','COLLECTED','PROCESSING','COMPLETED','VERIFIED') NOT NULL DEFAULT 'ORDERED', `priority` VARCHAR(20) NULL DEFAULT 'ROUTINE', `sample_type` VARCHAR(50) NULL, `sample_id` VARCHAR(100) NULL, `sample_collected_by` VARCHAR(36) NULL, `sample_collected_at` DATETIME NULL, `result_value` TEXT NULL, `result_text` TEXT NULL, `result_unit` VARCHAR(50) NULL, `result_flag` VARCHAR(20) NULL, `verified_by` VARCHAR(36) NULL, `verified_at` DATETIME NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`test_id`) REFERENCES `lab_tests`(`id`) ON DELETE CASCADE, FOREIGN KEY (`appointment_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- RADIOLOGY ORDER -- ----------------------------------------------------------- CREATE TABLE `radiology_orders` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `modality` ENUM('XRAY','CT','MRI','ULTRASOUND','ECG') NOT NULL, `body_part` VARCHAR(100) NULL, `clinical_history` TEXT NULL, `findings` TEXT NULL, `impression` TEXT NULL, `report` TEXT NULL, `status` VARCHAR(50) NOT NULL DEFAULT 'ORDERED', `radiologist_id` VARCHAR(36) NULL, `report_url` VARCHAR(500) NULL, `image_urls` JSON NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`radiologist_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- NURSING TASK -- ----------------------------------------------------------- CREATE TABLE `nursing_tasks` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `admission_id` VARCHAR(36) NULL, `nurse_id` VARCHAR(36) NULL, `task_type` ENUM('VITALS','MEDICATION','WOUND_CARE','CATHETER','IV','DIET','HYGIENE','OTHER') NOT NULL, `description` TEXT NULL, `schedule_time` DATETIME NULL, `completed_at` DATETIME NULL, `notes` TEXT NULL, `status` ENUM('PENDING','IN_PROGRESS','COMPLETED','SKIPPED') NOT NULL DEFAULT 'PENDING', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE SET NULL, FOREIGN KEY (`nurse_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- VITAL SIGN -- ----------------------------------------------------------- CREATE TABLE `vital_signs` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `admission_id` VARCHAR(36) NULL, `recorded_by` VARCHAR(36) NULL, `temperature` DECIMAL(5,2) NULL, `heart_rate` INT NULL, `blood_pressure_systolic` INT NULL, `blood_pressure_diastolic` INT NULL, `respiratory_rate` INT NULL, `oxygen_saturation` DECIMAL(5,2) NULL, `pain_level` INT NULL, `blood_glucose` DECIMAL(6,2) NULL, `weight` DECIMAL(5,2) NULL, `height` DECIMAL(5,2) NULL, `bmi` DECIMAL(4,2) NULL, `notes` TEXT NULL, `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE SET NULL, FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PRESCRIPTION -- ----------------------------------------------------------- CREATE TABLE `prescriptions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `appointment_id` VARCHAR(36) NULL, `diagnosis` TEXT NULL, `notes` TEXT NULL, `follow_up_date` DATE NULL, `is_digital` TINYINT(1) NOT NULL DEFAULT 0, `digital_signature` TEXT NULL, `qr_code` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`appointment_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PRESCRIPTION MEDICATION -- ----------------------------------------------------------- CREATE TABLE `prescription_medications` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `prescription_id` VARCHAR(36) NOT NULL, `medicine_id` VARCHAR(36) NULL, `name` VARCHAR(255) NOT NULL, `dosage` VARCHAR(100) NOT NULL, `frequency` VARCHAR(100) NOT NULL, `duration` VARCHAR(100) NOT NULL, `route` VARCHAR(50) NULL, `instructions` TEXT NULL, `quantity` INT NULL, `refills` INT NOT NULL DEFAULT 0, `is_generic` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`prescription_id`) REFERENCES `prescriptions`(`id`) ON DELETE CASCADE, FOREIGN KEY (`medicine_id`) REFERENCES `pharmacy_medicines`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INVOICE -- ----------------------------------------------------------- CREATE TABLE `invoices` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `appointment_id` VARCHAR(36) NULL, `admission_id` VARCHAR(36) NULL, `invoice_no` VARCHAR(50) NOT NULL UNIQUE, `invoice_type` ENUM('CONSULTATION','ADMISSION','PHARMACY','LAB','RADIOLOGY','OT','AMBULANCE','EMERGENCY') NOT NULL, `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0, `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0, `discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `tax_percent` DECIMAL(5,2) NOT NULL DEFAULT 0, `tax_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `total` DECIMAL(10,2) NOT NULL DEFAULT 0, `paid_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `due_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `status` ENUM('PENDING','PARTIAL','PAID','REFUNDED','CANCELLED') NOT NULL DEFAULT 'PENDING', `payment_method` VARCHAR(50) NULL, `notes` TEXT NULL, `generated_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`appointment_id`) REFERENCES `appointments`(`id`) ON DELETE SET NULL, FOREIGN KEY (`admission_id`) REFERENCES `ipd_admissions`(`id`) ON DELETE SET NULL, FOREIGN KEY (`generated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INVOICE ITEM -- ----------------------------------------------------------- CREATE TABLE `invoice_items` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `invoice_id` VARCHAR(36) NOT NULL, `description` VARCHAR(500) NOT NULL, `quantity` INT NOT NULL DEFAULT 1, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `item_type` VARCHAR(50) NULL, `reference_id` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PAYMENT -- ----------------------------------------------------------- CREATE TABLE `payments` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `invoice_id` VARCHAR(36) NOT NULL, `amount` DECIMAL(10,2) NOT NULL, `payment_method` ENUM('CASH','CARD','BANK_TRANSFER','MOBILE_WALLET','INSURANCE','OTHER') NOT NULL, `transaction_id` VARCHAR(255) NULL, `reference_no` VARCHAR(100) NULL, `notes` TEXT NULL, `received_by` VARCHAR(36) NULL, `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE, FOREIGN KEY (`received_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INSURANCE COMPANY -- ----------------------------------------------------------- CREATE TABLE `insurance_companies` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(50) NULL, `contact_person` VARCHAR(255) NULL, `phone` VARCHAR(50) NULL, `email` VARCHAR(255) NULL, `address` TEXT NULL, `coverage_percent` DECIMAL(5,2) NOT NULL DEFAULT 0, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INSURANCE CLAIM -- ----------------------------------------------------------- CREATE TABLE `insurance_claims` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `invoice_id` VARCHAR(36) NULL, `insurance_company_id` VARCHAR(36) NOT NULL, `claim_no` VARCHAR(100) NOT NULL, `claim_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `approved_amount` DECIMAL(10,2) NULL, `status` ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED','PAID') NOT NULL DEFAULT 'DRAFT', `submission_date` DATE NULL, `approval_date` DATE NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL, FOREIGN KEY (`insurance_company_id`) REFERENCES `insurance_companies`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PRE_AUTHORIZATION -- ----------------------------------------------------------- CREATE TABLE `pre_authorizations` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NOT NULL, `insurance_company_id` VARCHAR(36) NOT NULL, `authorization_no` VARCHAR(100) NOT NULL, `requested_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `approved_amount` DECIMAL(10,2) NULL, `status` VARCHAR(50) NOT NULL DEFAULT 'PENDING', `expiry_date` DATE NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`insurance_company_id`) REFERENCES `insurance_companies`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INVENTORY ITEM -- ----------------------------------------------------------- CREATE TABLE `inventory_items` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `name` VARCHAR(255) NOT NULL, `category` VARCHAR(100) NULL, `unit` VARCHAR(50) NOT NULL DEFAULT 'Piece', `quantity` INT NOT NULL DEFAULT 0, `reorder_level` INT NOT NULL DEFAULT 10, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `supplier_id` VARCHAR(36) NULL, `location` VARCHAR(100) NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`supplier_id`) REFERENCES `inventory_suppliers`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INVENTORY TRANSACTION -- ----------------------------------------------------------- CREATE TABLE `inventory_transactions` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `item_id` VARCHAR(36) NOT NULL, `type` ENUM('PURCHASE','SALE','TRANSFER_IN','TRANSFER_OUT','ADJUSTMENT','RETURN') NOT NULL, `quantity` INT NOT NULL, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `reference_no` VARCHAR(100) NULL, `notes` TEXT NULL, `performed_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`item_id`) REFERENCES `inventory_items`(`id`) ON DELETE CASCADE, FOREIGN KEY (`performed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- INVENTORY SUPPLIER -- ----------------------------------------------------------- CREATE TABLE `inventory_suppliers` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `contact_person` VARCHAR(255) NULL, `phone` VARCHAR(50) NULL, `email` VARCHAR(255) NULL, `address` TEXT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PURCHASE ORDER -- ----------------------------------------------------------- CREATE TABLE `purchase_orders` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `supplier_id` VARCHAR(36) NULL, `order_number` VARCHAR(50) NOT NULL, `status` ENUM('DRAFT','PENDING','APPROVED','RECEIVED','CANCELLED') NOT NULL DEFAULT 'DRAFT', `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0, `notes` TEXT NULL, `ordered_by` VARCHAR(36) NULL, `approved_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`supplier_id`) REFERENCES `inventory_suppliers`(`id`) ON DELETE SET NULL, FOREIGN KEY (`ordered_by`) REFERENCES `users`(`id`) ON DELETE SET NULL, FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PURCHASE ORDER ITEM -- ----------------------------------------------------------- CREATE TABLE `purchase_order_items` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `purchase_order_id` VARCHAR(36) NOT NULL, `item_id` VARCHAR(36) NOT NULL, `quantity` INT NOT NULL DEFAULT 0, `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0, `received_quantity` INT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`item_id`) REFERENCES `inventory_items`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OT SCHEDULE -- ----------------------------------------------------------- CREATE TABLE `ot_schedules` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `doctor_id` VARCHAR(36) NOT NULL, `surgeon_id` VARCHAR(36) NULL, `anesthesiologist_id` VARCHAR(36) NULL, `ot_room_id` VARCHAR(36) NULL, `procedure_name` VARCHAR(255) NOT NULL, `date` DATE NOT NULL, `start_time` TIME NOT NULL, `end_time` TIME NULL, `status` ENUM('SCHEDULED','IN_PROGRESS','COMPLETED','CANCELLED') NOT NULL DEFAULT 'SCHEDULED', `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`ot_room_id`) REFERENCES `ot_rooms`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OT ROOM -- ----------------------------------------------------------- CREATE TABLE `ot_rooms` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `name` VARCHAR(255) NOT NULL, `room_number` VARCHAR(50) NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OT CHECKLIST -- ----------------------------------------------------------- CREATE TABLE `ot_checklists` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `ot_schedule_id` VARCHAR(36) NOT NULL, `item` VARCHAR(255) NOT NULL, `is_checked` TINYINT(1) NOT NULL DEFAULT 0, `checked_by` VARCHAR(36) NULL, `checked_at` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`ot_schedule_id`) REFERENCES `ot_schedules`(`id`) ON DELETE CASCADE, FOREIGN KEY (`checked_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- OT IMPLANT -- ----------------------------------------------------------- CREATE TABLE `ot_implants` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `ot_schedule_id` VARCHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `serial_number` VARCHAR(100) NULL, `manufacturer` VARCHAR(255) NULL, `quantity` INT NOT NULL DEFAULT 1, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`ot_schedule_id`) REFERENCES `ot_schedules`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BLOOD DONOR -- ----------------------------------------------------------- CREATE TABLE `blood_donors` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `patient_id` VARCHAR(36) NULL, `first_name` VARCHAR(100) NOT NULL, `last_name` VARCHAR(100) NOT NULL, `phone` VARCHAR(50) NOT NULL, `email` VARCHAR(255) NULL, `blood_group` VARCHAR(5) NOT NULL, `last_donation_date` DATE NULL, `total_donations` INT NOT NULL DEFAULT 0, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BLOOD COLLECTION -- ----------------------------------------------------------- CREATE TABLE `blood_collections` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `donor_id` VARCHAR(36) NOT NULL, `blood_group` VARCHAR(5) NOT NULL, `quantity` INT NOT NULL DEFAULT 0, `collection_date` DATE NOT NULL, `expiry_date` DATE NOT NULL, `status` ENUM('COLLECTED','TESTED','APPROVED','REJECTED','DISCARDED') NOT NULL DEFAULT 'COLLECTED', `tested_by` VARCHAR(36) NULL, `test_results` JSON NULL, `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`donor_id`) REFERENCES `blood_donors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`tested_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BLOOD STOCK -- ----------------------------------------------------------- CREATE TABLE `blood_stocks` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `blood_group` VARCHAR(5) NOT NULL, `quantity` INT NOT NULL DEFAULT 0, `min_level` INT NOT NULL DEFAULT 5, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- BLOOD REQUEST -- ----------------------------------------------------------- CREATE TABLE `blood_requests` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `patient_id` VARCHAR(36) NOT NULL, `blood_group` VARCHAR(5) NOT NULL, `quantity` INT NOT NULL DEFAULT 1, `urgency` VARCHAR(50) NULL DEFAULT 'NORMAL', `status` ENUM('PENDING','FULFILLED','CANCELLED') NOT NULL DEFAULT 'PENDING', `fulfilled_by` VARCHAR(36) NULL, `fulfilled_at` DATETIME NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`fulfilled_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- EMPLOYEE -- ----------------------------------------------------------- CREATE TABLE `employees` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `branch_id` VARCHAR(36) NULL, `user_id` VARCHAR(36) NOT NULL UNIQUE, `employee_code` VARCHAR(50) NOT NULL, `department` VARCHAR(100) NULL, `designation` VARCHAR(100) NULL, `joining_date` DATE NULL, `salary` DECIMAL(10,2) NULL, `employment_type` VARCHAR(50) NULL DEFAULT 'FULL_TIME', `emergency_contact` JSON NULL, `documents` JSON NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`) ON DELETE SET NULL, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- ATTENDANCE -- ----------------------------------------------------------- CREATE TABLE `attendances` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `employee_id` VARCHAR(36) NOT NULL, `date` DATE NOT NULL, `check_in` DATETIME NULL, `check_out` DATETIME NULL, `status` ENUM('PRESENT','ABSENT','LATE','HALF_DAY','LEAVE') NOT NULL DEFAULT 'PRESENT', `notes` TEXT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- LEAVE_REQUEST -- ----------------------------------------------------------- CREATE TABLE `leave_requests` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `employee_id` VARCHAR(36) NOT NULL, `leave_type` VARCHAR(50) NOT NULL, `start_date` DATE NOT NULL, `end_date` DATE NOT NULL, `reason` TEXT NULL, `status` ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING', `approved_by` VARCHAR(36) NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE, FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- PAYROLL -- ----------------------------------------------------------- CREATE TABLE `payrolls` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `employee_id` VARCHAR(36) NOT NULL, `month` INT NOT NULL, `year` INT NOT NULL, `basic_salary` DECIMAL(10,2) NOT NULL DEFAULT 0, `allowances` JSON NULL, `deductions` JSON NULL, `gross_salary` DECIMAL(10,2) NOT NULL DEFAULT 0, `net_salary` DECIMAL(10,2) NOT NULL DEFAULT 0, `status` ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING', `payment_date` DATE NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- NOTIFICATION -- ----------------------------------------------------------- CREATE TABLE `notifications` ( `id` VARCHAR(36) NOT NULL PRIMARY KEY, `tenant_id` VARCHAR(36) NOT NULL, `user_id` VARCHAR(36) NOT NULL, `type` VARCHAR(50) NOT NULL, `title` VARCHAR(255) NOT NULL, `message` TEXT NULL, `data` JSON NULL, `is_read` TINYINT(1) NOT NULL DEFAULT 0, `read_at` DATETIME NULL, `channel` ENUM('IN_APP','SMS','EMAIL','WHATSAPP','PUSH') NOT NULL DEFAULT 'IN_APP', `status` ENUM('PENDING','SENT','FAILED') NOT NULL DEFAULT 'PENDING', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- -- Indexes for performance -- ----------------------------------------------------------- CREATE INDEX `idx_patients_tenant` ON `patients`(`tenant_id`); CREATE INDEX `idx_patients_phone` ON `patients`(`phone`); CREATE INDEX `idx_patients_name` ON `patients`(`first_name`, `last_name`); CREATE INDEX `idx_appointments_date` ON `appointments`(`date`); CREATE INDEX `idx_appointments_doctor_date` ON `appointments`(`doctor_id`, `date`); CREATE INDEX `idx_appointments_patient` ON `appointments`(`patient_id`); CREATE INDEX `idx_appointments_status` ON `appointments`(`status`); CREATE INDEX `idx_lab_orders_status` ON `lab_orders`(`status`); CREATE INDEX `idx_pharmacy_medicines_name` ON `pharmacy_medicines`(`name`); CREATE INDEX `idx_invoices_patient` ON `invoices`(`patient_id`); CREATE INDEX `idx_invoices_status` ON `invoices`(`status`); CREATE INDEX `idx_notifications_user` ON `notifications`(`user_id`, `is_read`); CREATE INDEX `idx_audit_logs_tenant` ON `audit_logs`(`tenant_id`, `created_at`); CREATE INDEX `idx_ipd_admissions_status` ON `ipd_admissions`(`status`);