SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('patient','practitioner','operations','admin','partner') NOT NULL DEFAULT 'patient',
  status ENUM('active','pending','suspended','disabled') NOT NULL DEFAULT 'active',
  avatar VARCHAR(255) NULL,
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  INDEX idx_users_role_status(role,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL DEFAULT 'mobile',
  token_hash CHAR(64) NOT NULL UNIQUE,
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_api_token_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_api_token_user(user_id,revoked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_reset_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reset_expiry(expires_at,used_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  date_of_birth DATE NULL,
  gender VARCHAR(30) NULL,
  blood_group VARCHAR(10) NULL,
  national_id VARCHAR(80) NULL,
  emergency_contact_name VARCHAR(120) NULL,
  emergency_contact_phone VARCHAR(30) NULL,
  preferred_language VARCHAR(30) NULL DEFAULT 'English',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_patient_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS family_members (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  relationship VARCHAR(50) NOT NULL,
  date_of_birth DATE NULL,
  gender VARCHAR(30) NULL,
  notes TEXT NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_family_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS addresses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(50) NULL,
  address_line VARCHAR(255) NOT NULL,
  area VARCHAR(120) NULL,
  city VARCHAR(120) NULL,
  country VARCHAR(100) NOT NULL DEFAULT 'Kenya',
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_address_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_address_geo(latitude,longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS specialties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS services (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  description VARCHAR(500) NULL,
  base_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 45,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_services_active(is_active,sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  registration_number VARCHAR(100) NULL,
  professional_body VARCHAR(150) NULL,
  profession VARCHAR(120) NOT NULL,
  bio TEXT NULL,
  years_experience INT UNSIGNED NOT NULL DEFAULT 0,
  verification_status ENUM('pending','in_review','verified','rejected','suspended') NOT NULL DEFAULT 'pending',
  is_online TINYINT(1) NOT NULL DEFAULT 0,
  rating DECIMAL(3,2) NOT NULL DEFAULT 0,
  review_count INT UNSIGNED NOT NULL DEFAULT 0,
  service_radius_km DECIMAL(6,2) NOT NULL DEFAULT 20,
  verified_at DATETIME NULL,
  verified_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_practitioner_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_practitioner_verifier FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_practitioner_avail(verification_status,is_online)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_credentials (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  credential_type VARCHAR(100) NOT NULL,
  credential_number VARCHAR(120) NULL,
  issuer VARCHAR(150) NULL,
  document_path VARCHAR(255) NULL,
  expires_at DATE NULL,
  status ENUM('pending','verified','rejected','expired') NOT NULL DEFAULT 'pending',
  verified_at DATETIME NULL,
  verified_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_credential_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_credential_verifier FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_specialties (
  practitioner_id BIGINT UNSIGNED NOT NULL,
  specialty_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(practitioner_id,specialty_id),
  CONSTRAINT fk_ps_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_ps_specialty FOREIGN KEY(specialty_id) REFERENCES specialties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_services (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  price DECIMAL(12,2) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_practitioner_service(practitioner_id,service_id),
  CONSTRAINT fk_pservice_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_pservice_service FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_availability (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  day_of_week TINYINT UNSIGNED NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_availability_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  INDEX idx_availability_day(practitioner_id,day_of_week,is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practitioner_locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  accuracy_meters DECIMAL(8,2) NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 1,
  captured_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_location_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  INDEX idx_practitioner_geo(latitude,longitude,is_current)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS care_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  family_member_id BIGINT UNSIGNED NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  address_id BIGINT UNSIGNED NULL,
  scheduled_for DATETIME NULL,
  urgency ENUM('routine','priority') NOT NULL DEFAULT 'routine',
  status ENUM('requested','matching','offered','accepted','scheduled','en_route','arrived','in_progress','completed','cancelled','escalated') NOT NULL DEFAULT 'requested',
  chief_complaint TEXT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  address_text VARCHAR(255) NOT NULL,
  estimated_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  assigned_practitioner_id BIGINT UNSIGNED NULL,
  accepted_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_request_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_request_family FOREIGN KEY(family_member_id) REFERENCES family_members(id) ON DELETE SET NULL,
  CONSTRAINT fk_request_service FOREIGN KEY(service_id) REFERENCES services(id),
  CONSTRAINT fk_request_address FOREIGN KEY(address_id) REFERENCES addresses(id) ON DELETE SET NULL,
  CONSTRAINT fk_request_practitioner FOREIGN KEY(assigned_practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE SET NULL,
  INDEX idx_request_dispatch(status,service_id,scheduled_for),
  INDEX idx_request_patient(patient_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS care_request_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  care_request_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  status ENUM('offered','accepted','declined','expired','cancelled') NOT NULL DEFAULT 'offered',
  offered_at DATETIME NULL,
  expires_at DATETIME NULL,
  responded_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_request_assignment(care_request_id,practitioner_id),
  CONSTRAINT fk_assignment_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_assignment_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE CASCADE,
  INDEX idx_assignment_offers(practitioner_id,status,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  care_request_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  type ENUM('home','video','follow_up') NOT NULL DEFAULT 'home',
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL,
  status ENUM('scheduled','confirmed','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  meeting_url VARCHAR(500) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_appointment_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id) ON DELETE SET NULL,
  CONSTRAINT fk_appointment_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_appointment_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  care_request_id BIGINT UNSIGNED NOT NULL UNIQUE,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  status ENUM('scheduled','en_route','arrived','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  started_at DATETIME NULL,
  arrived_at DATETIME NULL,
  completed_at DATETIME NULL,
  summary TEXT NULL,
  follow_up_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_visit_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id),
  CONSTRAINT fk_visit_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_visit_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id),
  INDEX idx_visit_provider(practitioner_id,status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visit_vitals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visit_id BIGINT UNSIGNED NOT NULL,
  temperature_c DECIMAL(4,1) NULL,
  systolic INT NULL,
  diastolic INT NULL,
  pulse INT NULL,
  respiratory_rate INT NULL,
  oxygen_saturation DECIMAL(5,2) NULL,
  weight_kg DECIMAL(6,2) NULL,
  height_cm DECIMAL(6,2) NULL,
  notes TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_vitals_visit FOREIGN KEY(visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visit_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  subjective TEXT NULL,
  objective TEXT NULL,
  assessment TEXT NULL,
  plan TEXT NULL,
  is_locked TINYINT(1) NOT NULL DEFAULT 0,
  locked_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_note_visit FOREIGN KEY(visit_id) REFERENCES visits(id) ON DELETE SET NULL,
  CONSTRAINT fk_note_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_note_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_conditions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  diagnosed_at DATE NULL,
  notes TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_condition_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS allergies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  allergen VARCHAR(180) NOT NULL,
  reaction VARCHAR(255) NULL,
  severity VARCHAR(30) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_allergy_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(180) NOT NULL,
  dose VARCHAR(100) NULL,
  frequency VARCHAR(100) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  started_at DATE NULL,
  ended_at DATE NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_medication_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prescriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visit_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  status ENUM('draft','issued','dispensed','cancelled') NOT NULL DEFAULT 'issued',
  instructions TEXT NULL,
  issued_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_prescription_visit FOREIGN KEY(visit_id) REFERENCES visits(id) ON DELETE SET NULL,
  CONSTRAINT fk_prescription_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_prescription_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prescription_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prescription_id BIGINT UNSIGNED NOT NULL,
  medication_name VARCHAR(180) NOT NULL,
  dose VARCHAR(100) NULL,
  frequency VARCHAR(100) NULL,
  duration VARCHAR(100) NULL,
  instructions VARCHAR(500) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_prescription_item FOREIGN KEY(prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS partners (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  type ENUM('lab','pharmacy','hospital','insurer','corporate') NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(30) NULL,
  address VARCHAR(255) NULL,
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  integration_status ENUM('manual','sandbox','connected') NOT NULL DEFAULT 'manual',
  CONSTRAINT fk_partner_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lab_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  visit_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  partner_id BIGINT UNSIGNED NULL,
  tests TEXT NOT NULL,
  collection_type ENUM('home','facility') NOT NULL DEFAULT 'home',
  status ENUM('requested','scheduled','collected','processing','completed','cancelled') NOT NULL DEFAULT 'requested',
  requested_at DATETIME NULL,
  scheduled_for DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_lab_visit FOREIGN KEY(visit_id) REFERENCES visits(id) ON DELETE SET NULL,
  CONSTRAINT fk_lab_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_lab_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id),
  CONSTRAINT fk_lab_partner FOREIGN KEY(partner_id) REFERENCES partners(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lab_results (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lab_request_id BIGINT UNSIGNED NOT NULL,
  result_summary TEXT NULL,
  document_path VARCHAR(255) NULL,
  resulted_at DATETIME NULL,
  released_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_lab_result_request FOREIGN KEY(lab_request_id) REFERENCES lab_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pharmacy_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prescription_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  partner_id BIGINT UNSIGNED NULL,
  delivery_address VARCHAR(255) NULL,
  amount DECIMAL(12,2) NULL,
  status ENUM('pending','accepted','preparing','dispatched','delivered','cancelled') NOT NULL DEFAULT 'pending',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_pharmacy_prescription FOREIGN KEY(prescription_id) REFERENCES prescriptions(id),
  CONSTRAINT fk_pharmacy_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_pharmacy_partner FOREIGN KEY(partner_id) REFERENCES partners(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS partner_referrals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NULL,
  partner_id BIGINT UNSIGNED NOT NULL,
  reason TEXT NULL,
  status ENUM('created','accepted','completed','cancelled') NOT NULL DEFAULT 'created',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_referral_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_referral_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id) ON DELETE SET NULL,
  CONSTRAINT fk_referral_partner FOREIGN KEY(partner_id) REFERENCES partners(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  care_request_id BIGINT UNSIGNED NULL,
  reference VARCHAR(80) NOT NULL UNIQUE,
  amount DECIMAL(12,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'KES',
  method ENUM('pending','mpesa','card','insurance','cash','credit') NOT NULL DEFAULT 'pending',
  status ENUM('pending','processing','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  gateway_reference VARCHAR(150) NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_payment_user FOREIGN KEY(user_id) REFERENCES users(id),
  CONSTRAINT fk_payment_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id) ON DELETE SET NULL,
  INDEX idx_payment_status(status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payouts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  visit_id BIGINT UNSIGNED NULL,
  gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  platform_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('pending','processing','paid','failed') NOT NULL DEFAULT 'pending',
  reference VARCHAR(120) NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_payout_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id),
  CONSTRAINT fk_payout_visit FOREIGN KEY(visit_id) REFERENCES visits(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  care_request_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  practitioner_id BIGINT UNSIGNED NOT NULL,
  rating TINYINT UNSIGNED NOT NULL,
  comment TEXT NULL,
  status ENUM('pending','published','hidden') NOT NULL DEFAULT 'published',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_review_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id),
  CONSTRAINT fk_review_patient FOREIGN KEY(patient_id) REFERENCES users(id),
  CONSTRAINT fk_review_practitioner FOREIGN KEY(practitioner_id) REFERENCES practitioner_profiles(id),
  CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_key VARCHAR(120) NOT NULL,
  sender_user_id BIGINT UNSIGNED NOT NULL,
  recipient_user_id BIGINT UNSIGNED NOT NULL,
  care_request_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  read_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_message_sender FOREIGN KEY(sender_user_id) REFERENCES users(id),
  CONSTRAINT fk_message_recipient FOREIGN KEY(recipient_user_id) REFERENCES users(id),
  CONSTRAINT fk_message_request FOREIGN KEY(care_request_id) REFERENCES care_requests(id) ON DELETE SET NULL,
  INDEX idx_messages_conversation(conversation_key,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  message VARCHAR(800) NOT NULL,
  type VARCHAR(40) NOT NULL DEFAULT 'info',
  action_url VARCHAR(255) NULL,
  read_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_notification_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notification_user(user_id,read_at,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS telehealth_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  appointment_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(80) NOT NULL DEFAULT 'webrtc',
  room_key VARCHAR(180) NOT NULL,
  join_url VARCHAR(500) NULL,
  status ENUM('scheduled','open','ended','cancelled') NOT NULL DEFAULT 'scheduled',
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_telehealth_appointment FOREIGN KEY(appointment_id) REFERENCES appointments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS care_packages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(140) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  description TEXT NULL,
  price DECIMAL(12,2) NOT NULL,
  billing_period ENUM('one_time','monthly','quarterly') NOT NULL DEFAULT 'monthly',
  included_services TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  care_package_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active','paused','cancelled','expired') NOT NULL DEFAULT 'active',
  starts_at DATETIME NOT NULL,
  renews_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_subscription_patient FOREIGN KEY(patient_id) REFERENCES patient_profiles(id),
  CONSTRAINT fk_subscription_package FOREIGN KEY(care_package_id) REFERENCES care_packages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_codes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  discount_type ENUM('fixed','percent') NOT NULL,
  discount_value DECIMAL(12,2) NOT NULL,
  starts_at DATETIME NULL,
  expires_at DATETIME NULL,
  max_uses INT UNSIGNED NULL,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  consent_type VARCHAR(100) NOT NULL,
  version VARCHAR(40) NOT NULL,
  granted TINYINT(1) NOT NULL DEFAULT 1,
  ip_address VARCHAR(64) NULL,
  granted_at DATETIME NOT NULL,
  withdrawn_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_consent_user FOREIGN KEY(user_id) REFERENCES users(id),
  INDEX idx_consent_user(user_id,consent_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_entity(entity_type,entity_id,created_at),
  INDEX idx_audit_user(user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;
