-- ============================================================
-- 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`);
