database/schema.sql

CREATE DATABASE IF NOT EXISTS care_people
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE care_people;

CREATE TABLE IF NOT EXISTS doctors (
  id VARCHAR(10) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL,
  designation VARCHAR(100) NOT NULL,
  degrees VARCHAR(200) NOT NULL,
  profile_image_url LONGTEXT NULL,
  room_number VARCHAR(10) NOT NULL,
  consultation_fee DECIMAL(10, 2) NOT NULL,
  consultation_days JSON NOT NULL,
  consultation_times VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT 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;

CREATE TABLE IF NOT EXISTS patients (
  phone_number VARCHAR(15) PRIMARY KEY,
  email VARCHAR(255) NULL,
  name VARCHAR(100) NOT NULL,
  profile_image_url LONGTEXT NULL,
  date_of_birth DATE NOT NULL,
  address TEXT NOT NULL,
  gender ENUM('male', 'female', 'other') NOT NULL DEFAULT 'male',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL,
  UNIQUE KEY uniq_patients_email (email)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS administrators (
  id VARCHAR(10) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  profile_image_url LONGTEXT NULL,
  password_hash VARCHAR(255) NOT 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;

CREATE TABLE IF NOT EXISTS sessions (
  id CHAR(36) PRIMARY KEY,
  user_type ENUM('patient', 'doctor', 'administrator') NOT NULL,
  user_identifier VARCHAR(32) NOT NULL,
  refresh_token_hash CHAR(64) NOT NULL,
  login_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL DEFAULT NULL,
  metadata_json JSON NULL,
  INDEX idx_sessions_user (user_type, user_identifier),
  INDEX idx_sessions_expires (expires_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS appointments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  doctor_id VARCHAR(10) NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  appointment_date DATE NOT NULL,
  time_slot VARCHAR(20) NOT NULL,
  serial_number INT NOT NULL,
  status ENUM('confirmed', 'cancelled', 'completed') NOT NULL DEFAULT 'confirmed',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_appointment_slot (doctor_id, appointment_date, time_slot),
  INDEX idx_appointments_doctor_date (doctor_id, appointment_date),
  INDEX idx_appointments_patient_date (patient_phone, appointment_date),
  CONSTRAINT fk_appointments_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_appointments_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reschedule_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  appointment_id BIGINT UNSIGNED NOT NULL,
  requested_date DATE NOT NULL,
  requested_time_slot VARCHAR(20) NOT NULL,
  reason TEXT NULL,
  status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  responded_at DATETIME NULL DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_reschedule_requests_appt_status (appointment_id, status),
  CONSTRAINT fk_reschedule_requests_appointment
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prescriptions (
  id VARCHAR(40) PRIMARY KEY,
  doctor_id VARCHAR(10) NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  appointment_date DATE NOT NULL,
  issued_at DATETIME NOT NULL,
  additional_notes TEXT NULL,
  pdf_path VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_prescriptions_doctor_issue (doctor_id, issued_at),
  INDEX idx_prescriptions_patient_issue (patient_phone, issued_at),
  CONSTRAINT fk_prescriptions_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_prescriptions_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_prescriptions_appointment
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prescription_diagnoses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prescription_id VARCHAR(40) NOT NULL,
  diagnosis_text TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  INDEX idx_prescription_diagnoses_rx (prescription_id, sort_order),
  CONSTRAINT fk_prescription_diagnoses_prescription
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prescribed_medicines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prescription_id VARCHAR(40) NOT NULL,
  name VARCHAR(200) NOT NULL,
  dosage VARCHAR(100) NOT NULL,
  frequency VARCHAR(100) NOT NULL,
  duration VARCHAR(100) NOT NULL,
  times_per_day INT NULL,
  time_slots_json JSON NULL,
  duration_days INT NULL,
  meal_relation ENUM('before_meal', 'after_meal', 'any') NULL,
  notes TEXT NULL,
  INDEX idx_prescribed_medicines_rx (prescription_id),
  CONSTRAINT fk_prescribed_medicines_prescription
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS test_catalog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  department VARCHAR(120) NOT NULL,
  room_number VARCHAR(40) NOT NULL,
  required_specimen VARCHAR(180) NULL,
  preparation_instructions TEXT NULL,
  estimated_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  expected_turnaround_hours INT NOT NULL DEFAULT 24,
  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,
  INDEX idx_test_catalog_name (name),
  INDEX idx_test_catalog_department (department)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS doctor_prescription_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  doctor_id VARCHAR(10) NOT NULL,
  title VARCHAR(160) NOT NULL,
  diagnosis_json JSON NOT NULL,
  medicines_json JSON NOT NULL,
  tests_json JSON NULL,
  additional_notes TEXT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_templates_doctor (doctor_id, created_at),
  CONSTRAINT fk_templates_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS prescription_tests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prescription_id VARCHAR(40) NOT NULL,
  doctor_id VARCHAR(10) NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  catalog_test_id BIGINT UNSIGNED NULL,
  test_name VARCHAR(180) NOT NULL,
  department VARCHAR(120) NOT NULL,
  room_number VARCHAR(40) NOT NULL,
  required_specimen VARCHAR(180) NULL,
  preparation_instructions TEXT NULL,
  estimated_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  status ENUM('pending', 'ordered', 'cancelled', 'completed') NOT NULL DEFAULT 'pending',
  report_summary TEXT NULL,
  report_file_url VARCHAR(500) NULL,
  report_updated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_prescription_tests_rx (prescription_id),
  INDEX idx_prescription_tests_patient_status (patient_phone, status),
  INDEX idx_prescription_tests_doctor_status (doctor_id, status),
  CONSTRAINT fk_prescription_tests_prescription
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_prescription_tests_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_prescription_tests_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_prescription_tests_catalog
    FOREIGN KEY (catalog_test_id) REFERENCES test_catalog(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lab_test_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prescription_test_id BIGINT UNSIGNED NOT NULL,
  prescription_id VARCHAR(40) NOT NULL,
  doctor_id VARCHAR(10) NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  status ENUM('ordered', 'cancelled', 'sample_collected', 'processing', 'reported') NOT NULL DEFAULT 'ordered',
  instruction_text TEXT NULL,
  scheduled_at DATETIME NULL,
  specimen_room VARCHAR(40) NULL,
  report_summary TEXT NULL,
  report_file_url VARCHAR(500) NULL,
  ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_lab_orders_patient_status (patient_phone, status),
  INDEX idx_lab_orders_doctor_status (doctor_id, status),
  INDEX idx_lab_orders_test (prescription_test_id),
  CONSTRAINT fk_lab_orders_prescription_test
    FOREIGN KEY (prescription_test_id) REFERENCES prescription_tests(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_lab_orders_prescription
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_lab_orders_doctor
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_lab_orders_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS medication_reminders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prescription_id VARCHAR(40) NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  medicine_name VARCHAR(200) NOT NULL,
  dosage VARCHAR(120) NULL,
  intake_instruction VARCHAR(120) NULL,
  times_per_day INT NOT NULL,
  time_slots_json JSON NOT NULL,
  start_date DATE NOT NULL,
  duration_days INT NOT NULL,
  status ENUM('active', 'completed', 'cancelled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_medication_reminders_patient (patient_phone, start_date),
  INDEX idx_medication_reminders_rx (prescription_id),
  CONSTRAINT fk_medication_reminders_prescription
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_medication_reminders_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS medication_reminder_occurrences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reminder_id BIGINT UNSIGNED NOT NULL,
  patient_phone VARCHAR(15) NOT NULL,
  scheduled_for DATETIME NOT NULL,
  status ENUM('scheduled', 'notified', 'dismissed', 'taken', 'skipped') NOT NULL DEFAULT 'scheduled',
  notification_title VARCHAR(180) NULL,
  notification_body TEXT NULL,
  notified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_reminder_occurrences_patient_time (patient_phone, scheduled_for),
  INDEX idx_reminder_occurrences_status_time (status, scheduled_for),
  CONSTRAINT fk_reminder_occurrences_reminder
    FOREIGN KEY (reminder_id) REFERENCES medication_reminders(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_reminder_occurrences_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS in_app_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_type ENUM('patient', 'doctor', 'administrator') NOT NULL,
  user_identifier VARCHAR(32) NOT NULL,
  category VARCHAR(80) NOT NULL,
  title VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  metadata_json JSON NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_in_app_notifications_user (user_type, user_identifier, created_at),
  INDEX idx_in_app_notifications_unread (user_type, user_identifier, read_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS push_device_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_type ENUM('patient', 'doctor', 'administrator') NOT NULL,
  user_identifier VARCHAR(32) NOT NULL,
  platform ENUM('android', 'ios', 'web') NOT NULL DEFAULT 'android',
  device_id VARCHAR(128) NOT NULL,
  token VARCHAR(255) NOT NULL,
  app_version VARCHAR(30) NULL,
  device_model VARCHAR(120) NULL,
  locale VARCHAR(20) NULL,
  timezone VARCHAR(60) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_push_device_token (token),
  UNIQUE KEY uniq_push_user_device (user_type, user_identifier, platform, device_id),
  INDEX idx_push_tokens_user_active (user_type, user_identifier, is_active),
  INDEX idx_push_tokens_last_seen (last_seen_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_phone VARCHAR(15) NOT NULL,
  admitting_doctor_id VARCHAR(10) NOT NULL,
  ward VARCHAR(60) NOT NULL,
  bed_number VARCHAR(30) NULL,
  admission_reason TEXT NOT NULL,
  status ENUM('admitted', 'discharged') NOT NULL DEFAULT 'admitted',
  admitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expected_discharge_at DATETIME NULL,
  discharged_at DATETIME NULL,
  discharge_summary TEXT NULL,
  created_by_type ENUM('doctor', 'administrator', 'system') NOT NULL DEFAULT 'system',
  created_by_identifier VARCHAR(32) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_admissions_patient_status (patient_phone, status),
  INDEX idx_admissions_doctor_status (admitting_doctor_id, status),
  INDEX idx_admissions_admitted_at (admitted_at),
  CONSTRAINT fk_admissions_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_admissions_doctor
    FOREIGN KEY (admitting_doctor_id) REFERENCES doctors(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bills (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bill_number VARCHAR(40) NOT NULL UNIQUE,
  patient_phone VARCHAR(15) NOT NULL,
  admission_id BIGINT UNSIGNED NULL,
  status ENUM('issued', 'partially_paid', 'paid', 'cancelled') NOT NULL DEFAULT 'issued',
  subtotal DECIMAL(12, 2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  currency_code VARCHAR(8) NOT NULL DEFAULT 'BDT',
  notes TEXT NULL,
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_at DATETIME NULL,
  paid_at DATETIME NULL,
  created_by_type ENUM('doctor', 'administrator', 'system') NOT NULL DEFAULT 'system',
  created_by_identifier VARCHAR(32) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_bills_patient_status (patient_phone, status),
  INDEX idx_bills_issued_at (issued_at),
  CONSTRAINT fk_bills_patient
    FOREIGN KEY (patient_phone) REFERENCES patients(phone_number)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT fk_bills_admission
    FOREIGN KEY (admission_id) REFERENCES admissions(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bill_line_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bill_id BIGINT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  line_total DECIMAL(12, 2) NOT NULL,
  INDEX idx_bill_line_items_bill (bill_id),
  CONSTRAINT fk_bill_line_items_bill
    FOREIGN KEY (bill_id) REFERENCES bills(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  category VARCHAR(80) NULL,
  unit_of_measure VARCHAR(20) NOT NULL DEFAULT 'unit',
  quantity_on_hand DECIMAL(12, 2) NOT NULL DEFAULT 0,
  reorder_level DECIMAL(12, 2) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(12, 2) NOT NULL DEFAULT 0,
  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,
  INDEX idx_inventory_items_name (name),
  INDEX idx_inventory_items_reorder (quantity_on_hand, reorder_level)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT UNSIGNED NOT NULL,
  transaction_type ENUM('in', 'out', 'adjust') NOT NULL,
  quantity DECIMAL(12, 2) NOT NULL,
  quantity_before DECIMAL(12, 2) NOT NULL,
  quantity_after DECIMAL(12, 2) NOT NULL,
  unit_cost DECIMAL(12, 2) NULL,
  reference_type VARCHAR(50) NULL,
  reference_id VARCHAR(64) NULL,
  notes TEXT NULL,
  performed_by_type ENUM('doctor', 'administrator', 'system') NOT NULL DEFAULT 'system',
  performed_by_identifier VARCHAR(32) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_inventory_txn_item_date (item_id, created_at),
  INDEX idx_inventory_txn_type_date (transaction_type, created_at),
  CONSTRAINT fk_inventory_transactions_item
    FOREIGN KEY (item_id) REFERENCES inventory_items(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(80) NOT NULL,
  action VARCHAR(80) NOT NULL,
  actor_type ENUM('patient', 'doctor', 'administrator', 'system') NOT NULL,
  actor_identifier VARCHAR(32) NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id VARCHAR(64) NULL,
  metadata_json JSON NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_logs_created (created_at),
  INDEX idx_audit_logs_event (event_type, created_at),
  INDEX idx_audit_logs_actor (actor_type, actor_identifier, created_at),
  INDEX idx_audit_logs_entity (entity_type, entity_id, created_at)
) ENGINE=InnoDB;