CREATE TABLE IF NOT EXISTS `{{prefix}}patients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `primary_dentist_user_id` BIGINT UNSIGNED NULL,
  `primary_dentist_assigned_at` DATETIME NULL,
  `primary_dentist_change_reason` VARCHAR(500) NULL,
  `primary_dentist_changed_by` BIGINT UNSIGNED NULL,
  `patient_no` VARCHAR(30) NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `middle_name` VARCHAR(100) NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `birth_date` DATE NULL,
  `sex` VARCHAR(20) NULL,
  `mobile` VARCHAR(40) NULL,
  `email` VARCHAR(190) NULL,
  `address` TEXT NULL,
  `emergency_contact` VARCHAR(190) NULL,
  `medical_alerts` TEXT NULL,
  `status` ENUM('pending','active','inactive','archived','deceased','merged','restricted') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_patient_no` (`patient_no`),
  KEY `idx_patient_name` (`last_name`,`first_name`),
  KEY `idx_patient_mobile` (`mobile`),
  KEY `idx_patient_branch` (`branch_id`),
  KEY `idx_patient_primary_dentist` (`primary_dentist_user_id`),
  CONSTRAINT `{{prefix}}fk_patient_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_patient_primary_dentist` FOREIGN KEY (`primary_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_patient_continuity_changed_by` FOREIGN KEY (`primary_dentist_changed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_patient_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}medical_histories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `allergies` TEXT NULL,
  `medications` TEXT NULL,
  `conditions` TEXT NULL,
  `dental_history` TEXT NULL,
  `confirmed_at` DATETIME NULL,
  `source_type` ENUM('patient_reported','staff_recorded','clinician_verified','legacy_record') NOT NULL DEFAULT 'staff_recorded',
  `verification_status` ENUM('pending_review','verified','rejected') NOT NULL DEFAULT 'pending_review',
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `verification_note` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_medical_patient` (`patient_id`,`created_at`),
  KEY `idx_medical_verification` (`patient_id`,`verification_status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_medical_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_medical_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentist_name` VARCHAR(150) NULL,
  `inhouse_provider_user_id` BIGINT UNSIGNED NULL,
  `inhouse_provider_name` VARCHAR(150) NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist',
  `start_at` DATETIME NOT NULL,
  `end_at` DATETIME NOT NULL,
  `chair_name` VARCHAR(100) NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'confirmed',
  `appointment_origin` VARCHAR(40) NOT NULL DEFAULT 'manual',
  `booking_source` VARCHAR(40) NOT NULL DEFAULT 'staff',
  `source_request_id` BIGINT UNSIGNED NULL,
  `reason_for_visit` TEXT NULL,
  `confirmation_status` VARCHAR(40) NOT NULL DEFAULT 'confirmed',
  `confirmation_source` VARCHAR(60) NULL,
  `confirmation_note` VARCHAR(500) NULL,
  `patient_agreed_at` DATETIME NULL,
  `notes` TEXT NULL,
  `last_email_outbox_id` BIGINT UNSIGNED NULL,
  `last_email_type` VARCHAR(60) NULL,
  `last_email_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_appointment_visit` (`visit_id`),
  KEY `idx_appt_start` (`start_at`),
  KEY `idx_appt_patient` (`patient_id`,`start_at`),
  KEY `idx_appt_dentist` (`dentist_name`,`start_at`,`end_at`),
  KEY `idx_appt_chair` (`chair_name`,`start_at`,`end_at`),
  KEY `idx_appointment_last_email` (`last_email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_appt_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_appt_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_appt_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_appt_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_status_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NOT NULL,
  `reason` VARCHAR(255) NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_appt_history` (`appointment_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_appt_history_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_appt_history_user` FOREIGN KEY (`changed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}queue_entries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `source_type` ENUM('appointment','walk_in') NOT NULL DEFAULT 'walk_in',
  `queue_no` VARCHAR(20) NOT NULL,
  `priority` VARCHAR(20) NOT NULL DEFAULT 'regular',
  `service_id` BIGINT UNSIGNED NULL,
  `service_name` VARCHAR(150) NULL,
  `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist',
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentist_name` VARCHAR(150) NULL,
  `inhouse_provider_user_id` BIGINT UNSIGNED NULL,
  `inhouse_provider_name` VARCHAR(150) NULL,
  `chair_name` VARCHAR(100) NULL,
  `reason` TEXT NULL,
  `notes` TEXT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'waiting',
  `checked_in_by` BIGINT UNSIGNED NULL,
  `checked_in_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `called_at` DATETIME NULL,
  `seated_at` DATETIME NULL,
  `treatment_started_at` DATETIME NULL,
  `billing_at` DATETIME NULL,
  `cashier_called_at` DATETIME NULL,
  `paid_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `cancelled_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_queue_visit` (`visit_id`),
  KEY `idx_queue_day` (`checked_in_at`,`status`),
  KEY `idx_queue_source` (`source_type`,`checked_in_at`),
  KEY `idx_queue_patient_day` (`patient_id`,`checked_in_at`),
  KEY `idx_queue_dentist` (`dentist_user_id`,`checked_in_at`),
  KEY `idx_queue_service_provider` (`service_id`,`provider_type`,`checked_in_at`),
  CONSTRAINT `{{prefix}}fk_queue_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_queue_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_queue_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_queue_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_queue_checkin_user` FOREIGN KEY (`checked_in_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}queue_status_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NOT NULL,
  `notes` VARCHAR(255) NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_queue_history_entry` (`queue_entry_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_queue_history_entry` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_queue_history_user` FOREIGN KEY (`changed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_encounters` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentist_name` VARCHAR(150) NOT NULL,
  `assistant_user_id` BIGINT UNSIGNED NULL,
  `assistant_name` VARCHAR(150) NULL,
  `assistant_assigned_by` BIGINT UNSIGNED NULL,
  `assistant_assigned_at` DATETIME NULL,
  `assistant_decision` ENUM('assigned','none') NULL,
  `assistant_decided_by` BIGINT UNSIGNED NULL,
  `assistant_decided_at` DATETIME NULL,
  `visit_type` VARCHAR(40) NOT NULL DEFAULT 'scheduled',
  `care_path` VARCHAR(40) NOT NULL DEFAULT 'comprehensive',
  `chief_complaint` TEXT NULL,
  `history_present_condition` TEXT NULL,
  `vitals_skip_reason` VARCHAR(500) NULL,
  `care_path_decided_by` BIGINT UNSIGNED NULL,
  `care_path_decided_at` DATETIME NULL,
  `examination` TEXT NULL,
  `diagnosis` TEXT NULL,
  `treatment_plan` TEXT NULL,
  `procedure_done` TEXT NULL,
  `soap_note` TEXT NULL,
  `prescription` TEXT NULL,
  `follow_up_date` DATE NULL,
  `status` ENUM('draft','pending_review','finalized','addendum') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `finalized_at` DATETIME NULL,
  `signed_by_name` VARCHAR(190) NULL,
  `signed_license_number` VARCHAR(100) NULL,
  `signed_signature_path` VARCHAR(255) NULL,
  `finalized_hash` CHAR(64) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_encounter_visit` (`visit_id`),
  KEY `idx_encounter_patient` (`patient_id`,`created_at`),
  KEY `idx_encounter_care_path` (`care_path`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_enc_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_enc_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_enc_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_enc_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}invoices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `invoice_no` VARCHAR(30) NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `description` VARCHAR(255) NOT NULL,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_type` VARCHAR(20) NOT NULL DEFAULT 'none',
  `discount_value` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_reason` VARCHAR(255) NULL,
  `discount_authorization_id` BIGINT UNSIGNED NULL,
  `zero_charge_authorization_id` BIGINT UNSIGNED NULL,
  `charge_source_hash` CHAR(64) NULL,
  `charge_locked_at` DATETIME NULL,
  `charge_locked_by` BIGINT UNSIGNED NULL,
  `legacy_charge_review_reason` VARCHAR(500) NULL,
  `legacy_charge_review_evidence` VARCHAR(255) NULL,
  `legacy_charge_reviewed_by` BIGINT UNSIGNED NULL,
  `legacy_charge_reviewed_at` DATETIME NULL,
  `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('draft','unpaid','partial','paid','voided','refunded','written_off') NOT NULL DEFAULT 'unpaid',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `pos_finalized_at` DATETIME NULL,
  `pos_finalized_by` BIGINT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_visit` (`visit_id`,`status`),
  UNIQUE KEY `uq_invoice_no` (`invoice_no`),
  KEY `idx_invoice_patient` (`patient_id`,`created_at`),
  KEY `idx_invoice_queue` (`queue_entry_id`),
  CONSTRAINT `{{prefix}}fk_invoice_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_invoice_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invoice_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `cashier_session_id` BIGINT UNSIGNED NULL,
  `source_type` ENUM('patient','insurance') NOT NULL DEFAULT 'patient',
  `status` ENUM('posted','partially_adjusted','adjusted') NOT NULL DEFAULT 'posted',
  `amount` DECIMAL(12,2) NOT NULL,
  `adjustment_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tendered_amount` DECIMAL(12,2) NULL,
  `change_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `method` VARCHAR(40) NOT NULL,
  `reference_no` VARCHAR(100) NULL,
  `request_token` CHAR(64) NULL,
  `payer_name` VARCHAR(190) NULL,
  `payer_relationship` VARCHAR(40) NULL,
  `notes` VARCHAR(500) NULL,
  `received_by` BIGINT UNSIGNED NULL,
  `receipt_email_outbox_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payment_invoice` (`invoice_id`,`created_at`),
  KEY `idx_payment_receipt_email` (`receipt_email_outbox_id`),
  UNIQUE KEY `uq_payments_request_token` (`request_token`),
  CONSTRAINT `{{prefix}}fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_payment_user` FOREIGN KEY (`received_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}invoice_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `source_type` ENUM('clinical_procedure','service_catalog','manual_charge','legacy_manual') NOT NULL DEFAULT 'legacy_manual',
  `item_code` VARCHAR(80) NULL,
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `gross_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `notes` VARCHAR(500) NULL,
  `source_authorized_by` BIGINT UNSIGNED NULL,
  `source_authorized_at` DATETIME NULL,
  `source_hash` CHAR(64) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_item_invoice` (`invoice_id`,`id`),
  KEY `idx_invoice_item_source` (`source_type`,`procedure_id`,`service_id`),
  CONSTRAINT `{{prefix}}fk_invoice_item_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS `{{prefix}}payment_item_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `invoice_item_id` BIGINT UNSIGNED NULL,
  `sequence_no` INT UNSIGNED NOT NULL DEFAULT 1,
  `item_code_snapshot` VARCHAR(80) NULL,
  `description_snapshot` VARCHAR(255) NOT NULL,
  `quantity_snapshot` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit_price_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_net_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payable_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `previous_paid_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount_applied` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `remaining_after_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_item_allocation_sequence` (`payment_id`,`sequence_no`),
  KEY `idx_payment_item_allocation_invoice` (`invoice_id`,`invoice_item_id`),
  KEY `idx_payment_item_allocation_payment` (`payment_id`,`id`),
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_payment` FOREIGN KEY (`payment_id`) REFERENCES `{{prefix}}payments` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_item` FOREIGN KEY (`invoice_item_id`) REFERENCES `{{prefix}}invoice_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `cashier_user_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('open','closed_pending_review','closed') NOT NULL DEFAULT 'open',
  `opening_cash` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at` DATETIME NULL,
  `expected_cash` DECIMAL(12,2) NULL,
  `counted_cash` DECIMAL(12,2) NULL,
  `variance` DECIMAL(12,2) NULL,
  `close_notes` TEXT NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cashier_session_open` (`branch_id`,`cashier_user_id`,`status`,`opened_at`),
  KEY `idx_cashier_session_review` (`status`,`closed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}discount_authorizations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `discount_type` ENUM('percent','fixed') NOT NULL,
  `discount_value` DECIMAL(12,2) NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected','used','expired') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `used_by` BIGINT UNSIGNED NULL,
  `used_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_discount_authorization_patient` (`branch_id`,`patient_id`,`status`,`requested_at`),
  KEY `idx_discount_authorization_queue` (`queue_entry_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}zero_charge_authorizations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `category` ENUM('no_charge_followup','warranty_rework','package_included','staff_benefit','charity','waived_balance','other') NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected','used','expired') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `used_by` BIGINT UNSIGNED NULL,
  `used_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_zero_charge_queue` (`queue_entry_id`,`status`,`requested_at`),
  KEY `idx_zero_charge_patient` (`branch_id`,`patient_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}payment_adjustments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `adjustment_type` ENUM('refund','reversal') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `method` VARCHAR(40) NOT NULL,
  `reference_no` VARCHAR(100) NULL,
  `reason` VARCHAR(500) NOT NULL,
  `status` ENUM('requested','approved','rejected','processed') NOT NULL DEFAULT 'requested',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_by` BIGINT UNSIGNED NULL,
  `approved_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `processed_by` BIGINT UNSIGNED NULL,
  `processed_at` DATETIME NULL,
  `cashier_session_id` BIGINT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_payment_adjustment_payment` (`payment_id`,`status`,`requested_at`),
  KEY `idx_payment_adjustment_review` (`branch_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}insurance_claim_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `claim_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NOT NULL,
  `claimed_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `approved_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `denied_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `submission_reference` VARCHAR(120) NULL,
  `remittance_reference` VARCHAR(120) NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_claim_history_claim` (`claim_id`,`created_at`),
  KEY `idx_claim_history_status` (`new_status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `public_reference` VARCHAR(32) NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_type` ENUM('new','returning') NOT NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `receipt_email_outbox_id` BIGINT UNSIGNED NULL,
  `patient_no_entered` VARCHAR(30) NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `middle_name` VARCHAR(100) NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `birth_date` DATE NOT NULL,
  `sex` VARCHAR(20) NULL,
  `mobile` VARCHAR(40) NOT NULL,
  `email` VARCHAR(190) NULL,
  `address` TEXT NULL,
  `emergency_contact` VARCHAR(190) NULL,
  `guardian_name` VARCHAR(190) NULL,
  `guardian_mobile` VARCHAR(40) NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `preferred_dentist` VARCHAR(150) NULL,
  `preferred_dentist_user_id` BIGINT UNSIGNED NULL,
  `preferred_date` DATE NOT NULL,
  `preferred_time` TIME NOT NULL,
  `alternate_date` DATE NULL,
  `alternate_time` TIME NULL,
  `urgency` ENUM('routine','soon','urgent') NOT NULL DEFAULT 'routine',
  `reason` TEXT NOT NULL,
  `allergies` TEXT NULL,
  `medications` TEXT NULL,
  `conditions` TEXT NULL,
  `medical_changes` TEXT NULL,
  `status` ENUM('submitted','reviewing','needs_verification','approved','scheduled','declined','cancelled') NOT NULL DEFAULT 'submitted',
  `staff_notes` TEXT NULL,
  `reschedule_required` TINYINT(1) NOT NULL DEFAULT 0,
  `reschedule_note` TEXT NULL,
  `consent_at` DATETIME NOT NULL,
  `submitted_ip` VARCHAR(45) NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_public_reference` (`public_reference`),
  KEY `idx_request_status_created` (`status`,`created_at`),
  KEY `idx_request_patient` (`patient_id`,`created_at`),
  KEY `idx_request_preferred` (`preferred_date`,`preferred_time`),
  KEY `idx_request_identity` (`last_name`,`first_name`,`birth_date`),
  KEY `idx_request_receipt_email` (`receipt_email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_request_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_request_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- Doctor availability, operation hours, services, branding and licensing support (v0.3.0)
CREATE TABLE IF NOT EXISTS `{{prefix}}branch_operating_hours` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `day_of_week` TINYINT UNSIGNED NOT NULL,
  `is_open` TINYINT(1) NOT NULL DEFAULT 1,
  `open_time` TIME NULL,
  `close_time` TIME NULL,
  `slot_interval_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_branch_day` (`branch_id`,`day_of_week`),
  CONSTRAINT `{{prefix}}fk_hours_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}branch_operating_exceptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `exception_date` DATE NOT NULL,
  `is_open` TINYINT(1) NOT NULL DEFAULT 0,
  `open_time` TIME NULL,
  `close_time` TIME NULL,
  `reason` VARCHAR(255) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_branch_exception_date` (`branch_id`,`exception_date`),
  CONSTRAINT `{{prefix}}fk_branch_exception` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_branch_exception_user` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}doctor_availability_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `doctor_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `day_of_week` TINYINT UNSIGNED NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `effective_from` DATE NOT NULL,
  `effective_until` DATE NULL,
  `status` ENUM('draft','submitted','approved','active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_rule_lookup` (`doctor_user_id`,`branch_id`,`day_of_week`,`effective_from`,`effective_until`,`status`),
  CONSTRAINT `{{prefix}}fk_doc_rule_user` FOREIGN KEY (`doctor_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doc_rule_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doc_rule_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_doc_rule_approver` FOREIGN KEY (`approved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}doctor_availability_exceptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `doctor_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `exception_date` DATE NOT NULL,
  `exception_type` ENUM('unavailable','special_availability','leave','break','training','closure') NOT NULL,
  `start_time` TIME NULL,
  `end_time` TIME NULL,
  `all_day` TINYINT(1) NOT NULL DEFAULT 0,
  `reason` VARCHAR(255) NULL,
  `status` ENUM('draft','submitted','approved','active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_exception_lookup` (`doctor_user_id`,`branch_id`,`exception_date`,`status`),
  CONSTRAINT `{{prefix}}fk_doc_exception_user` FOREIGN KEY (`doctor_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doc_exception_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doc_exception_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_doc_exception_approver` FOREIGN KEY (`approved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}service_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `standard_fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `preparation_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `cleanup_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist',
  `provider_role_code` VARCHAR(40) NULL,
  `slot_capacity` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `requires_chair` TINYINT(1) NOT NULL DEFAULT 1,
  `is_public` TINYINT(1) NOT NULL DEFAULT 1,
  `booking_scope` VARCHAR(40) NOT NULL DEFAULT 'public',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_service_public` (`branch_id`,`is_public`,`is_active`),
  CONSTRAINT `{{prefix}}fk_service_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}issued_licenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `license_id` VARCHAR(64) NOT NULL,
  `customer_name` VARCHAR(190) NOT NULL,
  `domains_json` JSON NOT NULL,
  `features_json` JSON NULL,
  `issued_at` DATETIME NOT NULL,
  `expires_at` DATE NULL,
  `issued_by` BIGINT UNSIGNED NULL,
  `status` ENUM('active','revoked','expired') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_issued_license_id` (`license_id`),
  CONSTRAINT `{{prefix}}fk_issued_license_user` FOREIGN KEY (`issued_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_reminders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` ENUM('appointment','follow_up','recall') NOT NULL DEFAULT 'appointment',
  `entity_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `reminder_key` VARCHAR(120) NOT NULL,
  `channel` ENUM('email','sms','portal') NOT NULL DEFAULT 'email',
  `scheduled_for` DATETIME NOT NULL,
  `status` ENUM('queued','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  `outbox_id` BIGINT UNSIGNED NULL,
  `last_error` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reminder_delivery` (`entity_type`,`entity_id`,`reminder_key`,`channel`),
  KEY `idx_reminder_schedule` (`status`,`scheduled_for`),
  KEY `idx_reminder_patient` (`patient_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare 0.5.8.2: receptionist appointment waitlist.
CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_waitlist` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `earliest_date` DATE NOT NULL,
  `latest_date` DATE NOT NULL,
  `preferred_time_from` TIME NULL,
  `preferred_time_to` TIME NULL,
  `priority` ENUM('routine','soon','urgent') NOT NULL DEFAULT 'routine',
  `status` ENUM('waiting','offered','booked','declined','cancelled','expired') NOT NULL DEFAULT 'waiting',
  `notes` TEXT NULL,
  `offer_expires_at` DATETIME NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_waitlist_branch_status_dates` (`branch_id`,`status`,`earliest_date`,`latest_date`),
  KEY `idx_waitlist_service_doctor` (`service_id`,`dentist_user_id`,`status`),
  KEY `idx_waitlist_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_waitlist_appointment` (`appointment_id`),
  CONSTRAINT `{{prefix}}fk_waitlist_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_waitlist_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_waitlist_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_waitlist_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare 0.5.9.8.22: canonical visit state and operations reconciliation.
CREATE TABLE IF NOT EXISTS `{{prefix}}visits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_code` VARCHAR(40) NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `primary_invoice_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `series_id` BIGINT UNSIGNED NULL,
  `source_type` ENUM('appointment','walk_in') NOT NULL DEFAULT 'walk_in',
  `state_code` VARCHAR(40) NOT NULL DEFAULT 'scheduled',
  `outcome_code` VARCHAR(40) NULL,
  `current_owner_role` VARCHAR(40) NULL,
  `current_owner_user_id` BIGINT UNSIGNED NULL,
  `state_reason` VARCHAR(500) NULL,
  `integrity_status` ENUM('ok','review') NOT NULL DEFAULT 'ok',
  `started_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `last_transition_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_visit_code` (`visit_code`),
  UNIQUE KEY `uq_visit_appointment` (`appointment_id`),
  UNIQUE KEY `uq_visit_queue` (`queue_entry_id`),
  KEY `idx_visit_patient_state` (`patient_id`,`state_code`,`created_at`),
  KEY `idx_visit_branch_state` (`branch_id`,`state_code`,`updated_at`),
  UNIQUE KEY `uq_visit_encounter` (`encounter_id`),
  KEY `idx_visit_invoice` (`primary_invoice_id`),
  KEY `idx_visit_followup` (`follow_up_id`),
  KEY `idx_visit_series` (`series_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_state_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NOT NULL,
  `previous_state` VARCHAR(40) NULL,
  `new_state` VARCHAR(40) NOT NULL,
  `action_code` VARCHAR(80) NULL,
  `details` VARCHAR(1000) NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_visit_state_history` (`visit_id`,`created_at`),
  KEY `idx_visit_state_action` (`action_code`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_integrity_issues` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `issue_key` CHAR(64) NOT NULL,
  `visit_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `issue_code` VARCHAR(80) NOT NULL,
  `issue_group` VARCHAR(40) NOT NULL DEFAULT 'workflow',
  `source_module` VARCHAR(40) NOT NULL DEFAULT 'operations',
  `severity` ENUM('warning','urgent','critical','approval') NOT NULL DEFAULT 'warning',
  `responsible_role` VARCHAR(40) NULL,
  `assigned_to_user_id` BIGINT UNSIGNED NULL,
  `assigned_by_user_id` BIGINT UNSIGNED NULL,
  `assigned_at` DATETIME NULL,
  `due_at` DATETIME NULL,
  `entity_type` VARCHAR(60) NULL,
  `entity_id` BIGINT UNSIGNED NULL,
  `summary` VARCHAR(255) NOT NULL,
  `detail` VARCHAR(1000) NULL,
  `metadata_json` JSON NULL,
  `status` ENUM('open','in_review','resolved','ignored') NOT NULL DEFAULT 'open',
  `occurrence_count` INT UNSIGNED NOT NULL DEFAULT 1,
  `detected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_detected_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `resolution_action` VARCHAR(80) NULL,
  `resolution_reason` VARCHAR(1000) NULL,
  `old_values_json` JSON NULL,
  `new_values_json` JSON NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_visit_integrity_issue_key` (`issue_key`),
  KEY `idx_visit_integrity_open` (`status`,`severity`,`last_detected_at`),
  KEY `idx_visit_integrity_branch` (`branch_id`,`status`,`severity`),
  KEY `idx_visit_integrity_visit` (`visit_id`,`status`),
  KEY `idx_visit_integrity_role` (`responsible_role`,`status`,`severity`),
  KEY `idx_visit_integrity_group` (`issue_group`,`status`,`severity`,`last_detected_at`),
  KEY `idx_visit_integrity_assignment` (`assigned_to_user_id`,`status`,`due_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare 0.5.9.8.30: staff Help & Support requests.
CREATE TABLE IF NOT EXISTS `{{prefix}}support_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `category` VARCHAR(40) NOT NULL DEFAULT 'workflow',
  `priority` ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `subject` VARCHAR(180) NOT NULL,
  `message` TEXT NOT NULL,
  `page_route` VARCHAR(80) NULL,
  `page_url` VARCHAR(500) NULL,
  `error_reference` VARCHAR(100) NULL,
  `context_json` JSON NULL,
  `status` ENUM('open','in_review','waiting_user','resolved','closed') NOT NULL DEFAULT 'open',
  `assigned_to_user_id` BIGINT UNSIGNED NULL,
  `resolution_note` TEXT NULL,
  `resolved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_support_request_user` (`user_id`,`status`,`updated_at`),
  KEY `idx_support_request_branch` (`branch_id`,`status`,`priority`,`updated_at`),
  KEY `idx_support_request_assignment` (`assigned_to_user_id`,`status`,`updated_at`),
  KEY `idx_support_request_error_reference` (`error_reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare v0.9.0.4: granular patient-data access and denied-access decisions.
CREATE TABLE IF NOT EXISTS `{{prefix}}access_decision_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `actual_role` VARCHAR(40) NOT NULL DEFAULT '',
  `active_role` VARCHAR(40) NOT NULL DEFAULT '',
  `decision` VARCHAR(30) NOT NULL,
  `permission_code` VARCHAR(120) NULL,
  `route_key` VARCHAR(120) NULL,
  `entity_type` VARCHAR(80) NULL,
  `entity_id` VARCHAR(80) NULL,
  `reason` VARCHAR(500) NULL,
  `ip_address` VARCHAR(64) NULL,
  `user_agent` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_access_user_created` (`user_id`,`created_at`),
  KEY `idx_access_decision_created` (`decision`,`created_at`),
  KEY `idx_access_permission_created` (`permission_code`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('patients.identity.view','View patient identity','Patient Care','View patient number, name, date of birth, sex, and photograph.'),
('patients.contact.view','View patient contact details','Patient Care','View telephone, email, address, and emergency-contact details.'),
('patients.safety_alert.view','View patient dental-safety alert flag','Patient Care','See that a dental treatment safety alert exists without exposing detailed health information.'),
('patients.medical.intake','Record patient-reported dental-safety history','Patient Care','Record an unverified patient-reported health update that may affect dental care for dentist review.'),
('patients.medical.view','View health information for dental care','Clinical','View allergies, medicines, conditions, alerts, and history versions relevant to dental care.'),
('patients.medical.verify','Verify dental-safety history and alerts','Clinical','Verify health information relevant to dental care and activate structured dental safety alerts.'),
('patients.financial.view','View patient financial records','Finance','View invoices, payments, balances, adjustments, and insurance details.'),
('patients.portal.manage','Manage patient portal access','Patient Care','Invite, link, review, or revoke patient and guardian portal access.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE (r.code='clinic_admin' AND p.code IN ('patients.identity.view','patients.contact.view','patients.safety_alert.view','patients.medical.view','patients.financial.view','patients.portal.manage'))
   OR (r.code='receptionist' AND p.code IN ('patients.identity.view','patients.contact.view','patients.safety_alert.view','patients.medical.intake','patients.portal.manage'))
   OR (r.code='staff' AND p.code IN ('patients.identity.view','patients.contact.view','patients.safety_alert.view','patients.medical.intake','patients.financial.view','patients.portal.manage'))
   OR (r.code='dentist' AND p.code IN ('patients.identity.view','patients.safety_alert.view','patients.medical.view','patients.medical.verify'))
   OR (r.code='dental_assistant' AND p.code IN ('patients.identity.view','patients.safety_alert.view','patients.medical.intake','patients.medical.view'))
   OR (r.code='cashier' AND p.code IN ('patients.identity.view','patients.financial.view'))
ON DUPLICATE KEY UPDATE allowed=1;

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,0 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE (r.code='clinic_admin' AND p.code IN ('patients.medical.intake','patients.medical.verify'))
   OR (r.code='receptionist' AND p.code IN ('patients.medical.view','patients.medical.verify','patients.financial.view'))
   OR (r.code='staff' AND p.code IN ('patients.medical.view','patients.medical.verify'))
   OR (r.code='dentist' AND p.code IN ('patients.contact.view','patients.medical.intake','patients.financial.view','patients.portal.manage'))
   OR (r.code='dental_assistant' AND p.code IN ('patients.contact.view','patients.medical.verify','patients.financial.view','patients.portal.manage'))
   OR (r.code='cashier' AND p.code IN ('patients.contact.view','patients.safety_alert.view','patients.medical.intake','patients.medical.view','patients.medical.verify','patients.portal.manage'))
   OR (r.code='inventory_officer' AND p.code IN ('patients.identity.view','patients.contact.view','patients.safety_alert.view','patients.medical.intake','patients.medical.view','patients.medical.verify','patients.financial.view','patients.portal.manage'))
ON DUPLICATE KEY UPDATE allowed=0;

-- DentalCare v0.9.0.5 controlled emergency administrative access.
CREATE TABLE IF NOT EXISTS `{{prefix}}administrative_break_glass_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `branch_id` bigint unsigned DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'active',
  `reason_code` varchar(80) NOT NULL,
  `reason_text` text NOT NULL,
  `scope_permissions_json` text NOT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` varchar(80) DEFAULT NULL,
  `token_hash` char(64) NOT NULL,
  `user_agent_hash` char(64) DEFAULT NULL,
  `mfa_verified_at` datetime NOT NULL,
  `started_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  `ended_at` datetime DEFAULT NULL,
  `ended_reason` varchar(190) DEFAULT NULL,
  `review_due_at` datetime NOT NULL,
  `reviewer_user_id` bigint unsigned DEFAULT NULL,
  `review_outcome` varchar(30) DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_ip` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_break_glass_token` (`token_hash`),
  KEY `idx_break_glass_user_status` (`user_id`,`status`,`expires_at`),
  KEY `idx_break_glass_review` (`status`,`review_due_at`),
  KEY `idx_break_glass_branch` (`branch_id`,`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}administrative_break_glass_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `session_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `event_type` varchar(80) NOT NULL,
  `permission_code` varchar(120) DEFAULT NULL,
  `route_key` varchar(120) DEFAULT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` varchar(80) DEFAULT NULL,
  `details_json` longtext DEFAULT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_break_glass_event_session` (`session_id`,`created_at`),
  KEY `idx_break_glass_event_permission` (`permission_code`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}administrative_break_glass_notifications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `session_id` bigint unsigned NOT NULL,
  `reviewer_user_id` bigint unsigned NOT NULL,
  `channel` varchar(40) NOT NULL DEFAULT 'in_app',
  `status` varchar(30) NOT NULL DEFAULT 'pending',
  `queued_email_id` bigint unsigned DEFAULT NULL,
  `last_error` varchar(500) DEFAULT NULL,
  `acknowledged_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_break_glass_reviewer` (`session_id`,`reviewer_user_id`),
  KEY `idx_break_glass_notification_review` (`reviewer_user_id`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('security.break_glass.manage','Use controlled emergency administrative access','Security','Start, end, and view a time-limited MFA re-authenticated emergency-access session.'),
('security.break_glass.review','Review controlled emergency access','Security','Independently review or revoke an assigned emergency-access session.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE r.code IN ('super_admin','clinic_admin') AND p.code IN ('security.break_glass.manage','security.break_glass.review')
ON DUPLICATE KEY UPDATE allowed=1;


-- DentalCare v0.9.0.6 audit integrity and privacy governance
CREATE TABLE IF NOT EXISTS `{{prefix}}audit_chain_heads` (
  `chain_key` VARCHAR(100) NOT NULL,
  `last_sequence` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `last_audit_id` BIGINT UNSIGNED NULL,
  `last_hash` CHAR(64) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`chain_key`),
  KEY `idx_audit_chain_head_id` (`last_audit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}audit_integrity_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(30) NOT NULL,
  `checked_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `valid_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `invalid_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `unhashed_rows` INT UNSIGNED NOT NULL DEFAULT 0,
  `broken_links` INT UNSIGNED NOT NULL DEFAULT 0,
  `head_mismatches` INT UNSIGNED NOT NULL DEFAULT 0,
  `latest_audit_id` BIGINT UNSIGNED NULL,
  `tip_hash` CHAR(64) NULL,
  `summary_json` LONGTEXT NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NOT NULL,
  `anchor_reference` VARCHAR(255) NULL,
  `anchored_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_audit_snapshot_status` (`status`,`verified_at`),
  KEY `idx_audit_snapshot_tip` (`tip_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}privacy_processing_activities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `title` VARCHAR(190) NOT NULL,
  `purpose` TEXT NOT NULL,
  `data_categories` TEXT NULL,
  `data_subjects` TEXT NULL,
  `legal_basis` VARCHAR(500) NOT NULL,
  `recipients` TEXT NULL,
  `international_transfer` TEXT NULL,
  `safeguards` TEXT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `owner_user_id` BIGINT UNSIGNED NULL,
  `review_due_at` DATE NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_privacy_processing_branch` (`branch_id`,`status`),
  KEY `idx_privacy_processing_review` (`review_due_at`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}privacy_retention_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `record_category` VARCHAR(190) NOT NULL,
  `retention_months` INT UNSIGNED NOT NULL,
  `trigger_event` VARCHAR(255) NOT NULL,
  `legal_basis` VARCHAR(500) NULL,
  `disposition_action` VARCHAR(255) NOT NULL,
  `legal_hold_allowed` TINYINT(1) NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `review_due_at` DATE NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_privacy_retention_branch` (`branch_id`,`is_active`),
  KEY `idx_privacy_retention_review` (`review_due_at`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}privacy_rights_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `request_reference` VARCHAR(80) NOT NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `requester_name` VARCHAR(190) NOT NULL,
  `requester_contact` VARCHAR(255) NULL,
  `request_type` VARCHAR(60) NOT NULL,
  `received_at` DATE NOT NULL,
  `due_at` DATE NOT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'received',
  `identity_verification_status` VARCHAR(40) NOT NULL DEFAULT 'pending',
  `identity_verified_at` DATETIME NULL,
  `assigned_user_id` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `outcome_summary` TEXT NULL,
  `completed_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_privacy_request_reference` (`request_reference`),
  KEY `idx_privacy_request_branch_status` (`branch_id`,`status`,`due_at`),
  KEY `idx_privacy_request_patient` (`patient_id`,`received_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}privacy_incidents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `incident_reference` VARCHAR(80) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `severity` VARCHAR(30) NOT NULL,
  `status` VARCHAR(40) NOT NULL DEFAULT 'open',
  `occurred_at` DATETIME NULL,
  `discovered_at` DATETIME NOT NULL,
  `affected_subjects_estimate` INT UNSIGNED NOT NULL DEFAULT 0,
  `data_categories` TEXT NULL,
  `containment_actions` TEXT NULL,
  `risk_assessment` TEXT NULL,
  `notification_required` VARCHAR(20) NOT NULL DEFAULT 'unknown',
  `authority_notification_due_at` DATETIME NULL,
  `patient_notification_due_at` DATETIME NULL,
  `assigned_user_id` BIGINT UNSIGNED NULL,
  `closed_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_privacy_incident_reference` (`incident_reference`),
  KEY `idx_privacy_incident_branch_status` (`branch_id`,`status`,`severity`),
  KEY `idx_privacy_incident_deadline` (`notification_required`,`authority_notification_due_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `{{prefix}}patient_allergies` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `medical_history_id` BIGINT UNSIGNED NULL,
  `substance_name` VARCHAR(190) NOT NULL,
  `normalized_substance` VARCHAR(190) NOT NULL,
  `canonical_key` CHAR(64) NULL,
  `reaction` VARCHAR(255) NULL,
  `severity` VARCHAR(30) NOT NULL DEFAULT 'unknown',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `is_current` TINYINT(1) NOT NULL DEFAULT 1,
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'verified',
  `source_type` VARCHAR(40) NOT NULL DEFAULT 'clinician_verified',
  `recorded_by` BIGINT UNSIGNED NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patient_allergy_current` (`patient_id`,`is_current`,`status`),
  KEY `idx_patient_allergy_normalized` (`normalized_substance`,`status`),
  KEY `idx_patient_allergy_history` (`medical_history_id`),
  UNIQUE KEY `uq_patient_allergy_current_key` (`canonical_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_medications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `medical_history_id` BIGINT UNSIGNED NULL,
  `medication_name` VARCHAR(190) NOT NULL,
  `normalized_medication` VARCHAR(190) NOT NULL,
  `canonical_key` CHAR(64) NULL,
  `generic_name` VARCHAR(190) NULL,
  `dose_text` VARCHAR(190) NULL,
  `frequency` VARCHAR(100) NULL,
  `route` VARCHAR(80) NULL,
  `reason` VARCHAR(255) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `is_current` TINYINT(1) NOT NULL DEFAULT 1,
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'verified',
  `source_type` VARCHAR(40) NOT NULL DEFAULT 'clinician_verified',
  `recorded_by` BIGINT UNSIGNED NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patient_med_current` (`patient_id`,`is_current`,`status`),
  KEY `idx_patient_med_normalized` (`normalized_medication`,`status`),
  KEY `idx_patient_med_history` (`medical_history_id`),
  UNIQUE KEY `uq_patient_med_current_key` (`canonical_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_conditions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `medical_history_id` BIGINT UNSIGNED NULL,
  `condition_name` VARCHAR(190) NOT NULL,
  `normalized_condition` VARCHAR(190) NOT NULL,
  `canonical_key` CHAR(64) NULL,
  `severity` VARCHAR(30) NOT NULL DEFAULT 'unknown',
  `status` VARCHAR(30) NOT NULL DEFAULT 'active',
  `is_current` TINYINT(1) NOT NULL DEFAULT 1,
  `verification_status` VARCHAR(30) NOT NULL DEFAULT 'verified',
  `source_type` VARCHAR(40) NOT NULL DEFAULT 'clinician_verified',
  `recorded_by` BIGINT UNSIGNED NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_patient_condition_current` (`patient_id`,`is_current`,`status`),
  KEY `idx_patient_condition_normalized` (`normalized_condition`,`status`),
  KEY `idx_patient_condition_history` (`medical_history_id`),
  UNIQUE KEY `uq_patient_condition_current_key` (`canonical_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}medicine_safety_profiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `medicine_catalog_id` BIGINT UNSIGNED NOT NULL,
  `normalized_ingredient` VARCHAR(190) NOT NULL,
  `medication_class` VARCHAR(100) NOT NULL DEFAULT 'unknown',
  `allergy_groups_json` LONGTEXT NULL,
  `interaction_tags_json` LONGTEXT NULL,
  `contraindication_tags_json` LONGTEXT NULL,
  `pregnancy_note` TEXT NULL,
  `renal_note` TEXT NULL,
  `hepatic_note` TEXT NULL,
  `minimum_age_years` DECIMAL(5,2) NULL,
  `evidence_status` VARCHAR(40) NOT NULL DEFAULT 'clinic_curated',
  `source_label` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_medicine_safety_catalog` (`medicine_catalog_id`),
  KEY `idx_medicine_safety_ingredient` (`normalized_ingredient`,`is_active`),
  KEY `idx_medicine_safety_class` (`medication_class`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}prescription_safety_assessments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `prescription_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `hard_stop_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `serious_warning_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `advisory_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `data_gap_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `context_hash` CHAR(64) NOT NULL,
  `summary_json` LONGTEXT NULL,
  `override_reason` TEXT NULL,
  `acknowledged_by` BIGINT UNSIGNED NULL,
  `acknowledged_at` DATETIME NULL,
  `assessed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rx_safety_prescription` (`prescription_id`),
  KEY `idx_rx_safety_patient` (`patient_id`,`assessed_at`),
  KEY `idx_rx_safety_status` (`status`,`assessed_at`),
  KEY `idx_rx_safety_context` (`context_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}prescription_safety_findings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_id` BIGINT UNSIGNED NOT NULL,
  `severity` VARCHAR(30) NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `medicine_index` INT UNSIGNED NOT NULL DEFAULT 0,
  `medicine_name` VARCHAR(190) NULL,
  `title` VARCHAR(255) NOT NULL,
  `detail` TEXT NOT NULL,
  `override_allowed` TINYINT(1) NOT NULL DEFAULT 0,
  `resolution_status` VARCHAR(40) NOT NULL DEFAULT 'not_required',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rx_safety_finding_assessment` (`assessment_id`,`severity`),
  KEY `idx_rx_safety_finding_code` (`code`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_safety_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `prescription_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `severity` VARCHAR(30) NOT NULL,
  `code` VARCHAR(100) NOT NULL,
  `details_json` LONGTEXT NULL,
  `action_taken` VARCHAR(100) NOT NULL,
  `actor_user_id` BIGINT UNSIGNED NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_safety_patient` (`patient_id`,`created_at`),
  KEY `idx_clinical_safety_event` (`event_type`,`severity`,`created_at`),
  KEY `idx_clinical_safety_review` (`reviewed_at`,`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- DentalCare v0.9.1.0 first-seating dental treatment safety
CREATE TABLE IF NOT EXISTS `{{prefix}}dental_safety_assessments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `intake_id` BIGINT UNSIGNED NULL,
  `assessment_type` ENUM('first_visit','returning_review','urgent_review') NOT NULL DEFAULT 'returning_review',
  `identity_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `allergies_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `medicines_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `conditions_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `anesthesia_history_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `bleeding_risk_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `pregnancy_relevance_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `change_status` ENUM('none','reported','unknown') NOT NULL DEFAULT 'unknown',
  `changes_summary` TEXT NULL,
  `allergies_value` TEXT NULL,
  `medicines_value` TEXT NULL,
  `conditions_value` TEXT NULL,
  `dental_history_value` TEXT NULL,
  `bp_indication` VARCHAR(60) NOT NULL DEFAULT 'not_required',
  `blood_pressure_systolic` SMALLINT UNSIGNED NULL,
  `blood_pressure_diastolic` SMALLINT UNSIGNED NULL,
  `pulse_rate` SMALLINT UNSIGNED NULL,
  `measurement_notes` TEXT NULL,
  `status` ENUM('draft','ready','needs_dentist_review','hold','verified') NOT NULL DEFAULT 'draft',
  `outcome_note` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `recorded_at` DATETIME NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dental_safety_queue` (`queue_entry_id`),
  KEY `idx_dental_safety_patient` (`patient_id`,`recorded_at`),
  KEY `idx_dental_safety_status` (`status`,`recorded_at`),
  KEY `idx_dental_safety_branch` (`branch_id`,`status`),
  CONSTRAINT `{{prefix}}fk_dental_safety_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dental_safety_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dental_safety_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dental_safety_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dental_safety_intake` FOREIGN KEY (`intake_id`) REFERENCES `{{prefix}}patient_intakes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dental_safety_recorded_by` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dental_safety_verified_by` FOREIGN KEY (`verified_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare v0.9.1.4 transaction-integrity request ledger.
CREATE TABLE IF NOT EXISTS `{{prefix}}operation_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dedupe_key` CHAR(64) NOT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `active_role` VARCHAR(40) NOT NULL DEFAULT '',
  `route_name` VARCHAR(100) NOT NULL,
  `action_name` VARCHAR(80) NOT NULL,
  `entity_type` VARCHAR(80) NULL,
  `entity_id` BIGINT UNSIGNED NULL,
  `payload_hash` CHAR(64) NOT NULL,
  `status` ENUM('processing','completed','failed') NOT NULL DEFAULT 'processing',
  `attempt_count` INT UNSIGNED NOT NULL DEFAULT 1,
  `duplicate_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  `expires_at` DATETIME NOT NULL,
  `last_error_code` VARCHAR(64) NULL,
  `last_error_message` VARCHAR(500) NULL,
  `response_reference` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_operation_dedupe_key` (`dedupe_key`),
  KEY `idx_operation_status_expiry` (`status`,`expires_at`),
  KEY `idx_operation_user_time` (`user_id`,`created_at`),
  KEY `idx_operation_branch_time` (`branch_id`,`created_at`),
  KEY `idx_operation_route_action` (`route_name`,`action_name`,`created_at`),
  CONSTRAINT `{{prefix}}fk_operation_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_operation_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('system.transaction_integrity.view','View transaction-integrity monitoring','System','Review duplicate-submission protection, concurrent processing, failed attempts, and workflow request integrity metadata.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p ON p.code='system.transaction_integrity.view'
WHERE r.code IN ('super_admin','clinic_admin')
ON DUPLICATE KEY UPDATE `allowed`=1;
