CREATE TABLE IF NOT EXISTS `{{prefix}}roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}branches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `code` VARCHAR(30) NOT NULL,
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(50) NULL,
  `address` TEXT NULL,
  `timezone` VARCHAR(64) NOT NULL DEFAULT 'Asia/Manila',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_branch_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `employee_no` VARCHAR(30) NULL,
  `name` VARCHAR(150) NOT NULL,
  `first_name` VARCHAR(80) NULL,
  `middle_name` VARCHAR(80) NULL,
  `last_name` VARCHAR(80) NULL,
  `email` VARCHAR(190) NOT NULL,
  `contact_number` VARCHAR(50) NULL,
  `job_title` VARCHAR(120) NULL,
  `address` TEXT NULL,
  `emergency_contact_name` VARCHAR(150) NULL,
  `emergency_contact_phone` VARCHAR(50) NULL,
  `profile_completed_at` DATETIME NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `status` ENUM('active','inactive','locked') NOT NULL DEFAULT 'active',
  `email_verified_at` DATETIME NULL,
  `last_login_at` DATETIME NULL,
  `must_change_password` TINYINT(1) NOT NULL DEFAULT 0,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `role_context_version` INT UNSIGNED NOT NULL DEFAULT 1,
  `last_role_switch_at` DATETIME NULL,
  `last_role_switch_role_id` BIGINT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_employee_no` (`employee_no`),
  KEY `idx_users_role` (`role_id`),
  CONSTRAINT `{{prefix}}fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `{{prefix}}roles` (`id`),
  CONSTRAINT `{{prefix}}fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}user_role_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `assigned_by` BIGINT UNSIGNED NULL,
  `assignment_type` ENUM('primary','secondary') NOT NULL DEFAULT 'secondary',
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `note` VARCHAR(255) NULL,
  `expires_at` DATETIME NULL,
  `last_switched_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_role_assignment` (`user_id`,`role_id`),
  KEY `idx_user_role_assignments_user_status` (`user_id`,`status`),
  KEY `idx_user_role_assignments_role_status` (`role_id`,`status`),
  KEY `idx_user_role_assignments_assigned_by` (`assigned_by`),
  KEY `idx_user_role_assignment_expiry` (`user_id`,`status`,`expires_at`),
  CONSTRAINT `{{prefix}}fk_user_role_assignments_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_user_role_assignments_role` FOREIGN KEY (`role_id`) REFERENCES `{{prefix}}roles` (`id`),
  CONSTRAINT `{{prefix}}fk_user_role_assignments_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}system_migration_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` VARCHAR(40) NOT NULL,
  `package_version` VARCHAR(40) NULL,
  `migration_key` VARCHAR(120) NOT NULL,
  `checksum_sha256` CHAR(64) NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `attempt_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `applied_by` BIGINT UNSIGNED NULL,
  `started_at` DATETIME NULL,
  `applied_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `execution_ms` INT UNSIGNED NULL,
  `error_reference` VARCHAR(40) NULL,
  `error_message` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_system_migration_key` (`migration_key`),
  KEY `idx_system_migration_version` (`version`,`status`,`completed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}system_hardening_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `release_version` VARCHAR(40) NOT NULL,
  `status` VARCHAR(20) NOT NULL,
  `blocked_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `review_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `fingerprint_sha256` CHAR(64) NOT NULL,
  `result_json` LONGTEXT NOT NULL,
  `run_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_hardening_release_status` (`release_version`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_name` VARCHAR(80) NOT NULL,
  `setting_key` VARCHAR(120) NOT NULL,
  `setting_value` LONGTEXT NULL,
  `is_secret` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_setting` (`group_name`, `setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}email_outbox` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `recipient_email` VARCHAR(190) NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `html_body` LONGTEXT NOT NULL,
  `text_body` LONGTEXT NULL,
  `metadata_json` JSON NULL,
  `status` ENUM('pending','processing','retry','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `available_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_attempt_at` DATETIME NULL,
  `sent_at` DATETIME NULL,
  `last_error` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email_outbox_work` (`status`, `available_at`),
  KEY `idx_email_outbox_status_created` (`status`, `created_at`),
  KEY `idx_email_outbox_processing` (`status`, `last_attempt_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}email_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `recipient_email` VARCHAR(190) NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `status` ENUM('sent','failed') NOT NULL,
  `error_message` TEXT NULL,
  `metadata_json` JSON NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_email_log_recipient` (`recipient_email`),
  KEY `idx_email_log_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}email_queue_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `trigger_type` VARCHAR(40) NOT NULL,
  `status` ENUM('success','warning','failed','busy') NOT NULL DEFAULT 'success',
  `selected_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `retry_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `skipped_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `recovered_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `message` TEXT NULL,
  `started_by` BIGINT UNSIGNED NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email_queue_run_time` (`started_at`),
  KEY `idx_email_queue_run_status` (`status`,`started_at`),
  CONSTRAINT `{{prefix}}fk_email_queue_runs_user` FOREIGN KEY (`started_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}email_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_key` VARCHAR(100) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(80) NOT NULL DEFAULT 'general',
  `subject_template` VARCHAR(255) NOT NULL,
  `html_template` LONGTEXT NOT NULL,
  `text_template` LONGTEXT NULL,
  `allowed_variables_json` JSON NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_system` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email_template_key` (`template_key`),
  KEY `idx_email_template_category` (`category`,`is_active`),
  CONSTRAINT `{{prefix}}fk_email_template_user` FOREIGN KEY (`updated_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}staff_account_invites` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `contact_number` VARCHAR(50) NULL,
  `job_title` VARCHAR(120) NULL,
  `address` TEXT NULL,
  `emergency_contact_name` VARCHAR(150) NULL,
  `emergency_contact_phone` VARCHAR(50) NULL,
  `profile_completed_at` DATETIME NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME NULL,
  `email_outbox_id` BIGINT UNSIGNED NULL,
  `last_sent_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_staff_invite_token` (`token_hash`),
  KEY `idx_staff_invite_user` (`user_id`,`used_at`,`expires_at`),
  KEY `idx_staff_invite_outbox` (`email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_staff_invite_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_staff_invite_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_staff_invite_outbox` FOREIGN KEY (`email_outbox_id`) REFERENCES `{{prefix}}email_outbox` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `chain_key` VARCHAR(100) NULL,
  `chain_sequence` BIGINT UNSIGNED NULL,
  `previous_hash` CHAR(64) NULL,
  `integrity_version` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `action` VARCHAR(120) NOT NULL,
  `entity_type` VARCHAR(100) NULL,
  `entity_id` VARCHAR(100) NULL,
  `old_values_json` JSON NULL,
  `new_values_json` JSON NULL,
  `record_hash` CHAR(64) NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_user` (`user_id`),
  KEY `idx_audit_branch` (`branch_id`,`created_at`),
  KEY `idx_audit_entity` (`entity_type`, `entity_id`),
  KEY `idx_audit_record_hash` (`record_hash`),
  UNIQUE KEY `uq_audit_chain_sequence` (`chain_key`,`chain_sequence`),
  KEY `idx_audit_chain_id` (`chain_key`,`id`),
  CONSTRAINT `{{prefix}}fk_audit_user` FOREIGN KEY (`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}}install_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version` VARCHAR(40) NOT NULL,
  `database_action` VARCHAR(190) NOT NULL,
  `installed_by_email` VARCHAR(190) NOT NULL,
  `installed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Core clinic application modules (v0.2.1)
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,
  `preferred_name` VARCHAR(100) NULL,
  `birth_date` DATE NULL,
  `sex` VARCHAR(20) NULL,
  `civil_status` VARCHAR(40) NULL,
  `nationality` VARCHAR(80) NULL,
  `occupation` VARCHAR(120) NULL,
  `employer` VARCHAR(190) NULL,
  `preferred_language` VARCHAR(60) NULL,
  `communication_preference` VARCHAR(40) NULL,
  `profile_photo` VARCHAR(255) 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',
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `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_directory` (`deleted_at`,`status`,`last_name`,`first_name`,`id`),
  KEY `idx_patient_mobile` (`mobile`),
  KEY `idx_patient_branch` (`branch_id`),
  KEY `idx_patient_primary_dentist` (`primary_dentist_user_id`),
  KEY `idx_patients_demo` (`is_demo`,`created_at`),
  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_id` BIGINT UNSIGNED 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,
  `follow_up_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,
  `schedule_bypass` TINYINT(1) NOT NULL DEFAULT 0,
  `schedule_bypass_reason` VARCHAR(500) NULL,
  `schedule_bypass_by` BIGINT UNSIGNED NULL,
  `schedule_bypass_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,
  `series_id` BIGINT UNSIGNED NULL,
  `series_sequence` SMALLINT UNSIGNED NULL,
  `planned_visit_label` VARCHAR(190) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_appointment_visit` (`visit_id`),
  KEY `idx_appt_series` (`series_id`,`series_sequence`),
  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_dentist_user` (`dentist_user_id`,`start_at`,`end_at`),
  KEY `idx_appt_chair` (`chair_name`,`start_at`,`end_at`),
  KEY `idx_appt_chair_resource` (`chair_id`,`start_at`,`end_at`),
  KEY `idx_appt_inhouse_provider` (`inhouse_provider_user_id`,`start_at`,`end_at`),
  KEY `idx_appt_origin` (`appointment_origin`,`start_at`),
  KEY `idx_appt_followup` (`follow_up_id`),
  KEY `idx_appt_service_provider` (`service_id`,`provider_type`,`start_at`),
  KEY `idx_appointment_branch_start_status` (`branch_id`,`start_at`,`status`),
  KEY `idx_appointment_dentist_start_status` (`dentist_user_id`,`start_at`,`status`),
  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_series` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `series_name` VARCHAR(190) NOT NULL,
  `status` ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_appointment_series_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_appointment_series_branch` (`branch_id`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_services` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `sequence_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `service_name` VARCHAR(150) NOT NULL,
  `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist',
  `required_privilege_code` VARCHAR(80) NULL,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `preparation_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `cleanup_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `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_id` BIGINT UNSIGNED NULL,
  `chair_name` VARCHAR(100) NULL,
  `start_at` DATETIME NOT NULL,
  `end_at` DATETIME NOT NULL,
  `status` ENUM('planned','completed','deferred','moved','not_performed','cancelled') NOT NULL DEFAULT 'planned',
  `disposition_reason` VARCHAR(500) NULL,
  `destination_appointment_id` BIGINT UNSIGNED NULL,
  `source_appointment_service_id` BIGINT UNSIGNED NULL,
  `disposition_by` BIGINT UNSIGNED NULL,
  `disposition_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appointment_service_sequence` (`appointment_id`,`sequence_no`),
  KEY `idx_appointment_service_time` (`appointment_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_provider` (`dentist_user_id`,`inhouse_provider_user_id`,`start_at`),
  KEY `idx_appointment_service_dentist` (`dentist_user_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_inhouse` (`inhouse_provider_user_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_chair` (`chair_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_catalog` (`service_id`,`status`),
  CONSTRAINT `{{prefix}}fk_appointment_service_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_service_dispositions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_service_id` BIGINT UNSIGNED NOT NULL,
  `source_appointment_id` BIGINT UNSIGNED NOT NULL,
  `destination_appointment_id` BIGINT UNSIGNED NULL,
  `action` ENUM('defer_next','move_existing','create_followup','not_performed') NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `performed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_service_disposition_source` (`source_appointment_id`,`created_at`),
  KEY `idx_service_disposition_destination` (`destination_appointment_id`,`created_at`),
  KEY `idx_service_disposition_segment` (`appointment_service_id`,`created_at`)
) 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_id` BIGINT UNSIGNED 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,
  `intake_completed_at` DATETIME NULL,
  `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`),
  KEY `idx_queue_inhouse_provider` (`inhouse_provider_user_id`,`checked_in_at`,`status`),
  KEY `idx_queue_chair_resource` (`chair_id`,`checked_in_at`,`status`),
  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,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `dental_safety_assessment_id` BIGINT UNSIGNED NULL,
  `consent_form_id` BIGINT UNSIGNED NULL,
  `consent_exception_reason` TEXT 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,
  `clinical_disposition` VARCHAR(60) NULL,
  `disposition_reason` TEXT NULL,
  `patient_advice` TEXT NULL,
  `procedure_done` TEXT NULL,
  `soap_note` TEXT NULL,
  `prescription` TEXT NULL,
  `follow_up_date` DATE NULL,
  `follow_up_purpose` VARCHAR(190) NULL,
  `follow_up_urgency` VARCHAR(30) NULL,
  `disposition` VARCHAR(60) NULL,
  `status` ENUM('draft','pending_review','finalized','addendum') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `autosaved_at` DATETIME NULL,
  `completion_check_json` JSON NULL,
  `finalized_snapshot_json` LONGTEXT NULL,
  `finalized_at` DATETIME NULL,
  `paused_at` DATETIME NULL,
  `pause_reason` VARCHAR(500) NULL,
  `paused_by` BIGINT UNSIGNED NULL,
  `pause_releases_chair` TINYINT(1) NOT NULL DEFAULT 1,
  `responsibility_transferred_at` DATETIME NULL,
  `responsibility_transfer_id` BIGINT UNSIGNED NULL,
  `signed_by_name` VARCHAR(190) NULL,
  `signed_license_number` VARCHAR(100) NULL,
  `signed_signature_path` VARCHAR(255) NULL,
  `finalized_hash` CHAR(64) NULL,
  `completion_destination` VARCHAR(40) NULL,
  `no_payment_reason` VARCHAR(500) NULL,
  `discharge_summary_json` LONGTEXT NULL,
  `billing_handoff_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `billing_handoff_at` DATETIME NULL,
  `discharged_at` DATETIME NULL,
  `chair_released_at` DATETIME 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`),
  UNIQUE KEY `uq_clinical_queue_entry` (`queue_entry_id`),
  KEY `idx_encounter_consent` (`consent_form_id`),
  KEY `idx_encounter_paused` (`dentist_user_id`,`paused_at`,`status`),
  KEY `idx_encounter_assistant` (`assistant_user_id`,`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,
  CONSTRAINT `{{prefix}}fk_enc_assistant` FOREIGN KEY (`assistant_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_enc_assigned_by` FOREIGN KEY (`assistant_assigned_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_enc_assistant_decided_by` FOREIGN KEY (`assistant_decided_by`) 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,
  `service_id` BIGINT UNSIGNED 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,
  `cashier_window_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_window` (`cashier_window_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,
  `clinical_usage_item_id` BIGINT UNSIGNED NULL,
  `inhouse_checkup_result_id` BIGINT UNSIGNED NULL,
  `source_type` ENUM('clinical_procedure','service_catalog','retail_product','manual_charge','legacy_manual','clinical_supply','dispensed_medicine','equipment_service','diagnostic_service') 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`),
  KEY `idx_invoice_item_usage` (`clinical_usage_item_id`),
  KEY `idx_invoice_item_inhouse_result` (`inhouse_checkup_result_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_windows` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `window_code` VARCHAR(40) NOT NULL,
  `window_name` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cashier_window_code` (`branch_id`,`window_code`),
  KEY `idx_cashier_window_active` (`branch_id`,`is_active`,`window_name`)
) 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,
  `window_id` BIGINT UNSIGNED NULL,
  `window_name` VARCHAR(100) NULL,
  `locked_patient_id` BIGINT UNSIGNED NULL,
  `locked_queue_entry_id` BIGINT UNSIGNED NULL,
  `transaction_locked_at` DATETIME NULL,
  `transaction_lock_note` VARCHAR(500) 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_window` (`branch_id`,`window_id`,`status`,`opened_at`),
  KEY `idx_cashier_session_lock` (`locked_patient_id`,`locked_queue_entry_id`,`status`),
  KEY `idx_cashier_session_review` (`status`,`closed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_checkouts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `cashier_session_id` BIGINT UNSIGNED NOT NULL,
  `cashier_window_id` BIGINT UNSIGNED NOT NULL,
  `cashier_user_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `checkout_type` ENUM('paid','zero_balance','insurance','settled') NOT NULL,
  `amount_collected` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reason` VARCHAR(500) NULL,
  `authorization_id` BIGINT UNSIGNED NULL,
  `completed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_checkout_session` (`cashier_session_id`,`completed_at`),
  KEY `idx_checkout_window` (`cashier_window_id`,`completed_at`),
  KEY `idx_checkout_cashier` (`cashier_user_id`,`completed_at`),
  KEY `idx_checkout_patient` (`patient_id`,`completed_at`),
  KEY `idx_checkout_queue` (`queue_entry_id`)
) 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}}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,
  `provider_preference_mode` ENUM('specific','clinic_decides') NOT NULL DEFAULT 'specific',
  `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,
  `is_demo` TINYINT(1) NOT NULL DEFAULT 0,
  `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_provider_preference` (`provider_preference_mode`,`status`,`preferred_date`),
  KEY `idx_request_identity` (`last_name`,`first_name`,`birth_date`),
  KEY `idx_requests_demo` (`is_demo`,`created_at`),
  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,
  `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured',
  `no_charge_reason` VARCHAR(500) NULL,
  `fee_authorized_by` BIGINT UNSIGNED NULL,
  `fee_authorized_at` DATETIME NULL,
  `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,
  `required_privilege_code` VARCHAR(100) 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}}inhouse_service_staff` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inhouse_service_staff` (`branch_id`,`service_id`,`user_id`),
  KEY `idx_inhouse_staff_lookup` (`branch_id`,`service_id`,`is_active`),
  KEY `idx_inhouse_staff_user` (`user_id`,`is_active`),
  CONSTRAINT `{{prefix}}fk_inhouse_staff_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_staff_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}}inhouse_checkup_results` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `service_name` VARCHAR(150) NOT NULL,
  `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured',
  `approved_fee_snapshot` DECIMAL(12,2) NULL,
  `no_charge_reason` VARCHAR(500) NULL,
  `fee_authorized_by` BIGINT UNSIGNED NULL,
  `fee_authorized_at` DATETIME NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `billing_status` ENUM('pending','ready','invoiced','no_charge','blocked') NOT NULL DEFAULT 'pending',
  `provider_user_id` BIGINT UNSIGNED NULL,
  `status` ENUM('in_progress','completed','referred') NOT NULL DEFAULT 'in_progress',
  `blood_pressure_systolic` SMALLINT UNSIGNED NULL,
  `blood_pressure_diastolic` SMALLINT UNSIGNED NULL,
  `pulse_rate` SMALLINT UNSIGNED NULL,
  `respiratory_rate` SMALLINT UNSIGNED NULL,
  `temperature_c` DECIMAL(4,1) NULL,
  `oxygen_saturation` DECIMAL(5,2) NULL,
  `weight_kg` DECIMAL(7,2) NULL,
  `height_cm` DECIMAL(7,2) NULL,
  `bmi` DECIMAL(5,2) NULL,
  `blood_glucose_mg_dl` DECIMAL(7,2) NULL,
  `clearance_status` ENUM('not_applicable','received','pending','incomplete','rejected') NOT NULL DEFAULT 'not_applicable',
  `findings` TEXT NULL,
  `notes` TEXT NULL,
  `abnormal_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `referral_dentist_user_id` BIGINT UNSIGNED NULL,
  `referral_reason` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NOT NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inhouse_checkup_queue` (`queue_entry_id`),
  KEY `idx_inhouse_checkup_patient` (`patient_id`,`created_at`),
  KEY `idx_inhouse_checkup_branch_status` (`branch_id`,`status`,`created_at`),
  KEY `idx_inhouse_checkup_provider` (`provider_user_id`,`status`,`created_at`),
  KEY `idx_inhouse_result_billing` (`billing_status`,`invoice_id`,`completed_at`),
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_provider` FOREIGN KEY (`provider_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_referral_dentist` FOREIGN KEY (`referral_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_inhouse_checkup_recorder` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT
) 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}}patient_guardians` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `relationship` VARCHAR(80) NULL,
  `mobile` VARCHAR(40) NULL,
  `email` VARCHAR(190) NULL,
  `address` TEXT NULL,
  `authorized_to_consent` TINYINT(1) NOT NULL DEFAULT 0,
  `authorized_to_receive_records` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_status` ENUM('pending','verified','rejected','revoked') NOT NULL DEFAULT 'pending',
  `verification_method` VARCHAR(120) NULL,
  `verification_note` TEXT NULL,
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `revoked_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_guardian_patient` (`patient_id`),
  KEY `idx_guardian_verification` (`patient_id`,`verification_status`),
  CONSTRAINT `{{prefix}}fk_guard_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}medical_alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `alert_type` VARCHAR(100) NOT NULL,
  `severity` ENUM('information','caution','critical') NOT NULL DEFAULT 'caution',
  `description` TEXT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_alert_patient` (`patient_id`,`is_active`),
  CONSTRAINT `{{prefix}}fk_alert_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_alert_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}}vital_signs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `dental_safety_assessment_id` BIGINT UNSIGNED NULL,
  `blood_pressure_systolic` SMALLINT UNSIGNED NULL,
  `blood_pressure_diastolic` SMALLINT UNSIGNED NULL,
  `bp_arm` ENUM('left','right','other') NULL,
  `bp_position` ENUM('seated','standing','supine','other') NULL,
  `bp_cuff_size` VARCHAR(30) NULL,
  `bp_device_type` ENUM('automatic','manual','other') NULL,
  `bp_measurement_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `bp_repeat_systolic` SMALLINT UNSIGNED NULL,
  `bp_repeat_diastolic` SMALLINT UNSIGNED NULL,
  `bp_repeat_after_minutes` SMALLINT UNSIGNED NULL,
  `bp_symptoms_reviewed` TINYINT(1) NOT NULL DEFAULT 0,
  `bp_symptoms` VARCHAR(500) NULL,
  `pulse_rate` SMALLINT UNSIGNED NULL,
  `temperature_c` DECIMAL(4,1) NULL,
  `respiratory_rate` SMALLINT UNSIGNED NULL,
  `oxygen_saturation` DECIMAL(5,2) NULL,
  `weight_kg` DECIMAL(6,2) NULL,
  `height_cm` DECIMAL(7,2) NULL,
  `bmi` DECIMAL(6,2) NULL,
  `pain_score` TINYINT UNSIGNED NULL,
  `blood_glucose` DECIMAL(7,2) NULL,
  `pregnancy_status` VARCHAR(60) NULL,
  `medical_changes` TEXT NULL,
  `measurement_notes` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vitals_patient` (`patient_id`,`recorded_at`),
  KEY `idx_vitals_encounter` (`encounter_id`),
  KEY `idx_vitals_queue` (`queue_entry_id`),
  KEY `idx_vitals_safety_assessment` (`dental_safety_assessment_id`),
  CONSTRAINT `{{prefix}}fk_vitals_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_vitals_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_vitals_user` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}odontogram_entries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentition` ENUM('permanent','primary','mixed') NOT NULL DEFAULT 'permanent',
  `tooth_number` VARCHAR(12) NOT NULL,
  `surface` VARCHAR(40) NULL,
  `condition_code` VARCHAR(80) NOT NULL,
  `finding_type` VARCHAR(40) NOT NULL DEFAULT 'condition',
  `severity` VARCHAR(30) NULL,
  `chart_layer` ENUM('existing','proposed','in_progress','completed','historical') NOT NULL DEFAULT 'existing',
  `status` ENUM('active','resolved','superseded') NOT NULL DEFAULT 'active',
  `diagnosis_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `supersedes_entry_id` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_odontogram_patient` (`patient_id`,`tooth_number`,`recorded_at`),
  KEY `idx_odontogram_active` (`patient_id`,`status`,`dentition`,`tooth_number`),
  KEY `idx_odontogram_diagnosis` (`diagnosis_id`),
  KEY `idx_odontogram_plan_item` (`treatment_plan_item_id`),
  CONSTRAINT `{{prefix}}fk_odon_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_odon_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_odon_user` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_odon_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_odon_supersedes` FOREIGN KEY (`supersedes_entry_id`) REFERENCES `{{prefix}}odontogram_entries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dental_condition_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'condition',
  `default_severity` VARCHAR(30) NULL,
  `surface_specific` TINYINT(1) NOT NULL DEFAULT 1,
  `description` TEXT NULL,
  `recommended_procedure` VARCHAR(190) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dental_condition_code` (`code`),
  KEY `idx_dental_condition_category` (`category`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dental_chart_reviews` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentition` ENUM('permanent','primary','mixed') NOT NULL DEFAULT 'permanent',
  `review_date` DATE NOT NULL,
  `caries_risk` VARCHAR(30) NULL,
  `periodontal_risk` VARCHAR(30) NULL,
  `oral_hygiene_status` VARCHAR(80) NULL,
  `occlusion_summary` VARCHAR(255) NULL,
  `clinical_summary` TEXT NULL,
  `status` ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
  `finalized_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_chart_review_patient` (`patient_id`,`review_date`),
  KEY `idx_chart_review_encounter` (`encounter_id`),
  CONSTRAINT `{{prefix}}fk_chart_review_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_chart_review_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_chart_review_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}}periodontal_charts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `chart_date` DATE NOT NULL,
  `risk_classification` VARCHAR(80) NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_perio_patient` (`patient_id`,`chart_date`),
  CONSTRAINT `{{prefix}}fk_perio_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_perio_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_perio_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}}periodontal_measurements` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chart_id` BIGINT UNSIGNED NOT NULL,
  `tooth_number` VARCHAR(12) NOT NULL,
  `site` ENUM('MB','B','DB','ML','L','DL') NOT NULL,
  `probing_depth_mm` TINYINT UNSIGNED NULL,
  `recession_mm` TINYINT UNSIGNED NULL,
  `bleeding` TINYINT(1) NOT NULL DEFAULT 0,
  `suppuration` TINYINT(1) NOT NULL DEFAULT 0,
  `plaque` TINYINT(1) NOT NULL DEFAULT 0,
  `mobility` TINYINT UNSIGNED NULL,
  `furcation` VARCHAR(20) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_perio_measure` (`chart_id`,`tooth_number`,`site`),
  CONSTRAINT `{{prefix}}fk_perio_measure_chart` FOREIGN KEY (`chart_id`) REFERENCES `{{prefix}}periodontal_charts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}treatment_plans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `title` VARCHAR(190) NOT NULL,
  `phase` VARCHAR(80) NULL,
  `status` ENUM('draft','presented','partially_accepted','accepted','scheduled','in_progress','completed','deferred','declined','cancelled','expired','revised') NOT NULL DEFAULT 'draft',
  `version_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `estimate_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `accepted_at` DATETIME NULL,
  `expires_at` DATE NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_plan_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_plan_branch` (`branch_id`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_plan_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_plan_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_plan_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}}treatment_plan_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `treatment_plan_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `diagnosis` VARCHAR(255) NULL,
  `diagnosis_id` BIGINT UNSIGNED NULL,
  `odontogram_entry_id` BIGINT UNSIGNED NULL,
  `tooth_region` VARCHAR(80) NULL,
  `tooth_number` VARCHAR(12) NULL,
  `surfaces` VARCHAR(80) NULL,
  `procedure_code` VARCHAR(80) NULL,
  `procedure_name` VARCHAR(190) NOT NULL,
  `priority` ENUM('routine','important','urgent','emergency') NOT NULL DEFAULT 'routine',
  `sequence_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `sessions` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `estimated_fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured',
  `no_charge_reason` VARCHAR(500) NULL,
  `fee_authorized_by` BIGINT UNSIGNED NULL,
  `fee_authorized_at` DATETIME NULL,
  `status` ENUM('proposed','accepted','scheduled','in_progress','completed','deferred','declined','cancelled') NOT NULL DEFAULT 'proposed',
  `clinical_rationale` TEXT NULL,
  `prognosis` VARCHAR(190) NULL,
  `consent_status` VARCHAR(30) NOT NULL DEFAULT 'not_requested',
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_plan_item_plan` (`treatment_plan_id`,`sequence_no`),
  KEY `idx_plan_item_service` (`service_id`),
  KEY `idx_plan_item_finding` (`odontogram_entry_id`),
  KEY `idx_plan_item_diagnosis` (`diagnosis_id`),
  CONSTRAINT `{{prefix}}fk_plan_item_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_plan_item_finding` FOREIGN KEY (`odontogram_entry_id`) REFERENCES `{{prefix}}odontogram_entries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}treatment_plan_reassignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `treatment_plan_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `request_type` ENUM('consultation','transfer') NOT NULL DEFAULT 'consultation',
  `from_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `to_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `status` ENUM('pending','accepted','declined','cancelled') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` BIGINT UNSIGNED NULL,
  `decided_at` DATETIME NULL,
  `decision_note` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_treatment_handoff_plan` (`treatment_plan_id`,`status`,`requested_at`),
  KEY `idx_treatment_handoff_target` (`to_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_treatment_handoff_patient` (`patient_id`,`status`,`requested_at`),
  CONSTRAINT `{{prefix}}fk_treatment_handoff_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_from` FOREIGN KEY (`from_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_to` FOREIGN KEY (`to_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_requester` FOREIGN KEY (`requested_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_treatment_handoff_decider` FOREIGN KEY (`decided_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_task_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `treatment_plan_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `task_code` VARCHAR(100) NOT NULL,
  `task_title` VARCHAR(190) NOT NULL,
  `required_privilege_code` VARCHAR(100) NULL,
  `requesting_dentist_user_id` BIGINT UNSIGNED NULL,
  `assigned_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `reason` TEXT NOT NULL,
  `status` ENUM('pending','accepted','declined','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` BIGINT UNSIGNED NULL,
  `decided_at` DATETIME NULL,
  `decision_note` TEXT NULL,
  `completed_by` BIGINT UNSIGNED NULL,
  `completed_at` DATETIME NULL,
  `completion_encounter_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_task_assignee` (`assigned_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_clinical_task_requester` (`requesting_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_clinical_task_patient` (`patient_id`,`status`,`requested_at`),
  KEY `idx_clinical_task_item` (`treatment_plan_item_id`,`status`),
  CONSTRAINT `{{prefix}}fk_clinical_task_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_clinical_task_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_clinical_task_item` FOREIGN KEY (`treatment_plan_item_id`) REFERENCES `{{prefix}}treatment_plan_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_clinical_task_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_requesting_dentist` FOREIGN KEY (`requesting_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_assigned_dentist` FOREIGN KEY (`assigned_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_clinical_task_requester` FOREIGN KEY (`requested_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_clinical_task_decider` FOREIGN KEY (`decided_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_completer` FOREIGN KEY (`completed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_clinical_task_completion_encounter` FOREIGN KEY (`completion_encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_credential_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `old_license_number` VARCHAR(120) NULL,
  `new_license_number` VARCHAR(120) NULL,
  `old_license_expiry` DATE NULL,
  `new_license_expiry` DATE NULL,
  `old_credential_status` VARCHAR(30) NULL,
  `new_credential_status` VARCHAR(30) NULL,
  `change_reason` TEXT NOT NULL,
  `changed_by` BIGINT UNSIGNED NOT NULL,
  `changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dentist_credential_history` (`dentist_user_id`,`changed_at`),
  CONSTRAINT `{{prefix}}fk_dentist_credential_history_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_credential_history_actor` FOREIGN KEY (`changed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}procedures` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `consent_form_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `assistant_user_id` BIGINT UNSIGNED NULL,
  `entered_by_user_id` BIGINT UNSIGNED NULL,
  `entered_by_role` VARCHAR(30) NULL,
  `verified_by_user_id` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `procedure_name` VARCHAR(190) NOT NULL,
  `service_name_snapshot` VARCHAR(190) NULL,
  `service_code_snapshot` VARCHAR(80) NULL,
  `approved_fee_snapshot` DECIMAL(12,2) NULL,
  `fee_source` ENUM('service_catalog','treatment_plan','authorized_no_charge') NULL,
  `fee_status` ENUM('blocked','billable','no_charge') NOT NULL DEFAULT 'blocked',
  `no_charge_reason` VARCHAR(500) NULL,
  `fee_authorized_by` BIGINT UNSIGNED NULL,
  `fee_authorized_at` DATETIME NULL,
  `billing_status` ENUM('pending','ready','invoiced','no_charge','blocked') NOT NULL DEFAULT 'pending',
  `tooth_region` VARCHAR(80) NULL,
  `started_at` DATETIME NULL,
  `ended_at` DATETIME NULL,
  `anesthetic` TEXT NULL,
  `materials` TEXT NULL,
  `complications` TEXT NULL,
  `outcome` TEXT NULL,
  `status` ENUM('planned','in_progress','completed','interrupted','cancelled') NOT NULL DEFAULT 'completed',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_proc_patient` (`patient_id`,`created_at`),
  KEY `idx_procedure_billing_status` (`encounter_id`,`billing_status`,`status`),
  KEY `idx_proc_branch` (`branch_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_proc_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_proc_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_item` FOREIGN KEY (`treatment_plan_item_id`) REFERENCES `{{prefix}}treatment_plan_items` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_assistant` FOREIGN KEY (`assistant_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_entered_by` FOREIGN KEY (`entered_by_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_proc_verified_by` FOREIGN KEY (`verified_by_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}}billing_correction_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `assigned_dentist_user_id` BIGINT UNSIGNED NULL,
  `reason` VARCHAR(1000) NOT NULL,
  `status` ENUM('open','in_review','corrected','rejected','cancelled') NOT NULL DEFAULT 'open',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `resolution_note` VARCHAR(1000) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_billing_correction_queue` (`queue_entry_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_encounter` (`encounter_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_dentist` (`assigned_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_billing_correction_patient` (`patient_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}financial_correction_addenda` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `correction_type` ENUM('service_mapping','fee_policy','insurance','discount','no_charge','other') NOT NULL DEFAULT 'other',
  `reason` VARCHAR(1000) NOT NULL,
  `old_value_json` JSON NULL,
  `new_value_json` JSON NULL,
  `author_user_id` BIGINT UNSIGNED NOT NULL,
  `author_name_snapshot` VARCHAR(190) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `addendum_hash` CHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_financial_addendum_encounter` (`encounter_id`,`created_at`),
  KEY `idx_financial_addendum_invoice` (`invoice_id`,`created_at`),
  KEY `idx_financial_addendum_patient` (`patient_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_encounter_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `request_type` ENUM('consultation','transfer','emergency_takeover') NOT NULL DEFAULT 'consultation',
  `from_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `to_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `required_privilege_code` VARCHAR(100) NULL,
  `reason` VARCHAR(1000) NOT NULL,
  `status` ENUM('pending','accepted','declined','cancelled','completed') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` BIGINT UNSIGNED NULL,
  `decided_at` DATETIME NULL,
  `decision_note` VARCHAR(1000) NULL,
  `consultation_note` TEXT NULL,
  `consultation_signed_name` VARCHAR(190) NULL,
  `consultation_signed_license` VARCHAR(100) NULL,
  `consultation_signature_path` VARCHAR(500) NULL,
  `consultation_signed_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_encounter_transfer_encounter` (`encounter_id`,`status`,`requested_at`),
  KEY `idx_encounter_transfer_target` (`to_dentist_user_id`,`status`,`requested_at`),
  KEY `idx_encounter_transfer_patient` (`patient_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_note_addenda` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `author_user_id` BIGINT UNSIGNED NULL,
  `author_name_snapshot` VARCHAR(190) NULL,
  `author_license_snapshot` VARCHAR(100) NULL,
  `signature_path_snapshot` VARCHAR(255) NULL,
  `signed_at` DATETIME NULL,
  `addendum_hash` CHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_addendum_encounter` (`encounter_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_addendum_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_addendum_user` FOREIGN KEY (`author_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}}prescriptions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `prescription_no` VARCHAR(40) NOT NULL,
  `issued_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('draft','issued','cancelled','expired','replaced') NOT NULL DEFAULT 'issued',
  `clinical_indication` TEXT NULL,
  `review_snapshot_json` LONGTEXT NULL,
  `signed_by_name` VARCHAR(190) NULL,
  `signed_license_number` VARCHAR(100) NULL,
  `signed_signature_path` VARCHAR(255) NULL,
  `prescription_hash` CHAR(64) NULL,
  `cancelled_at` DATETIME NULL,
  `cancelled_by` BIGINT UNSIGNED NULL,
  `cancellation_reason` TEXT NULL,
  `replaces_prescription_id` BIGINT UNSIGNED NULL,
  `safety_assessment_id` BIGINT UNSIGNED NULL,
  `safety_status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `safety_override_reason` TEXT NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rx_no` (`prescription_no`),
  KEY `idx_rx_patient` (`patient_id`,`issued_at`),
  KEY `idx_rx_safety_status` (`safety_status`,`issued_at`),
  KEY `idx_rx_safety_assessment` (`safety_assessment_id`),
  CONSTRAINT `{{prefix}}fk_rx_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_rx_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_rx_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}}medicine_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `catalog_key` CHAR(40) NOT NULL,
  `display_name` VARCHAR(190) NOT NULL,
  `generic_name` VARCHAR(190) NULL,
  `brand_name` VARCHAR(190) NULL,
  `form` VARCHAR(100) NULL,
  `strength` VARCHAR(80) NULL,
  `route` VARCHAR(80) NULL,
  `notes` TEXT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_medicine_catalog_key` (`catalog_key`),
  KEY `idx_medicine_catalog_search` (`is_active`,`display_name`),
  KEY `idx_medicine_catalog_generic` (`generic_name`,`is_active`),
  CONSTRAINT `{{prefix}}fk_medicine_catalog_user` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `{{prefix}}medicine_catalog`
(`catalog_key`,`display_name`,`generic_name`,`brand_name`,`form`,`strength`,`route`,`is_active`,`created_at`,`updated_at`) VALUES
('d3b64d00e561feb503a6f721445f7a85fcca62c4','Amoxicillin','Amoxicillin',NULL,'Capsule','500 mg','Oral',1,NOW(),NOW()),
('dd588d9b850df4c6d666f31f4ce769c579efb9ed','Amoxicillin + Clavulanic Acid','Amoxicillin / Clavulanate',NULL,'Tablet','625 mg','Oral',1,NOW(),NOW()),
('d88a900e5cec0e0891b103026a1062e9f7dcf7ac','Azithromycin','Azithromycin',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('9fc6f83a939d17444c334cfb04e2dabc27769274','Clindamycin','Clindamycin',NULL,'Capsule','300 mg','Oral',1,NOW(),NOW()),
('af354bfa344a50ecd2d84662acd9cc797b967046','Metronidazole','Metronidazole',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('b40d876d98cc0e9dd122b3e2d7346de89faf699e','Paracetamol','Paracetamol / Acetaminophen',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('bbb01451e89c62bb54ad6af80e9150f62926f385','Ibuprofen','Ibuprofen',NULL,'Tablet','400 mg','Oral',1,NOW(),NOW()),
('a5965b923954a6f2f3947968446375e071c74444','Naproxen Sodium','Naproxen Sodium',NULL,'Tablet','550 mg','Oral',1,NOW(),NOW()),
('d937e96559a7d99a178e6bed2057ed30cdb75086','Mefenamic Acid','Mefenamic Acid',NULL,'Capsule','500 mg','Oral',1,NOW(),NOW()),
('5358a273c95603f346c4e082cdaf4c4fbf8a9c09','Celecoxib','Celecoxib',NULL,'Capsule','200 mg','Oral',1,NOW(),NOW()),
('d8f9af4a2b06feb64b0449f905892281a6cc4c7d','Chlorhexidine Gluconate','Chlorhexidine Gluconate',NULL,'Mouth rinse','0.12%','Oral rinse',1,NOW(),NOW()),
('227e28846b12515eb18ab5c6347db0299b38f09b','Benzydamine Hydrochloride','Benzydamine Hydrochloride',NULL,'Mouth rinse','0.15%','Oral rinse',1,NOW(),NOW());

CREATE TABLE IF NOT EXISTS `{{prefix}}prescription_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `prescription_id` BIGINT UNSIGNED NOT NULL,
  `medicine_catalog_id` BIGINT UNSIGNED NULL,
  `medication_name` VARCHAR(190) NOT NULL,
  `generic_name` VARCHAR(190) NULL,
  `indication` VARCHAR(255) NULL,
  `strength` VARCHAR(80) NULL,
  `dosage` VARCHAR(100) NULL,
  `frequency` VARCHAR(100) NULL,
  `route` VARCHAR(80) NULL,
  `duration_days` SMALLINT UNSIGNED NULL,
  `quantity` VARCHAR(80) NULL,
  `instructions` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rx_item_rx` (`prescription_id`),
  KEY `idx_rx_item_catalog` (`medicine_catalog_id`),
  CONSTRAINT `{{prefix}}fk_rx_item_rx` FOREIGN KEY (`prescription_id`) REFERENCES `{{prefix}}prescriptions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_rx_item_catalog` FOREIGN KEY (`medicine_catalog_id`) REFERENCES `{{prefix}}medicine_catalog` (`id`) ON DELETE SET NULL
) 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;

CREATE TABLE IF NOT EXISTS `{{prefix}}documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `category` VARCHAR(100) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `tooth_region` VARCHAR(80) NULL,
  `description` TEXT NULL,
  `access_level` ENUM('clinical','staff','patient') NOT NULL DEFAULT 'clinical',
  `uploaded_by` 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_doc_patient` (`patient_id`,`category`,`created_at`),
  CONSTRAINT `{{prefix}}fk_doc_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doc_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_doc_upload` FOREIGN KEY (`uploaded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_doc_review` FOREIGN KEY (`reviewed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}consent_forms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `treatment_plan_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `consent_type` VARCHAR(150) NOT NULL,
  `version_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `content_snapshot` LONGTEXT NULL,
  `risks_snapshot` LONGTEXT NULL,
  `alternatives_snapshot` LONGTEXT NULL,
  `refusal_consequences_snapshot` LONGTEXT NULL,
  `questions_answered` TINYINT(1) NOT NULL DEFAULT 0,
  `signer_name` VARCHAR(190) NULL,
  `signer_relationship` VARCHAR(100) NULL,
  `signer_type` ENUM('patient','guardian','authorized_representative') NOT NULL DEFAULT 'patient',
  `guardian_id` BIGINT UNSIGNED NULL,
  `identity_verification_method` VARCHAR(150) NULL,
  `signature_method` ENUM('in_person_typed','in_person_electronic','uploaded_signed_form') NULL,
  `signature_attestation_snapshot` LONGTEXT NULL,
  `status` ENUM('draft','requested','signed','declined','withdrawn','expired') NOT NULL DEFAULT 'draft',
  `signed_at` DATETIME NULL,
  `expires_at` DATE NULL,
  `withdrawn_at` DATETIME NULL,
  `declined_at` DATETIME NULL,
  `decline_reason` TEXT NULL,
  `form_hash` CHAR(64) NULL,
  `signature_hash` CHAR(64) NULL,
  `witness_user_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `signed_by_user_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_consent_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_consent_branch` (`branch_id`,`created_at`),
  KEY `idx_consent_plan_item` (`treatment_plan_item_id`,`status`,`signed_at`),
  KEY `idx_consent_encounter` (`encounter_id`,`status`),
  CONSTRAINT `{{prefix}}fk_consent_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_consent_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_consent_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_consent_witness` FOREIGN KEY (`witness_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_consent_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}}referrals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `referring_dentist_id` BIGINT UNSIGNED NULL,
  `provider_name` VARCHAR(190) NOT NULL,
  `provider_type` VARCHAR(100) NULL,
  `reason` TEXT NOT NULL,
  `urgency` ENUM('routine','urgent','emergency') NOT NULL DEFAULT 'routine',
  `status` ENUM('draft','sent','received','appointment_scheduled','report_returned','completed','cancelled') NOT NULL DEFAULT 'draft',
  `sent_at` DATETIME NULL,
  `returned_at` DATETIME NULL,
  `outcome` TEXT NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_referral_patient` (`patient_id`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_ref_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_ref_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_ref_dentist` FOREIGN KEY (`referring_dentist_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}laboratory_cases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `treatment_plan_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `laboratory_name` VARCHAR(190) NOT NULL,
  `case_type` VARCHAR(120) NOT NULL,
  `tooth_arch` VARCHAR(80) NULL,
  `shade` VARCHAR(60) NULL,
  `material` VARCHAR(120) NULL,
  `due_date` DATE NULL,
  `status` ENUM('draft','sent','received_by_lab','in_production','quality_check','ready','received_by_clinic','patient_fitting','adjustment_required','completed','cancelled') NOT NULL DEFAULT 'draft',
  `cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `instructions` TEXT NULL,
  `received_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_lab_patient` (`patient_id`,`status`,`due_date`),
  CONSTRAINT `{{prefix}}fk_lab_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_lab_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_lab_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}}follow_ups` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `purpose` VARCHAR(190) NOT NULL,
  `recommended_date` DATE NOT NULL,
  `preferred_time` TIME NULL,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `maximum_date` DATE NULL,
  `urgency` ENUM('routine','important','urgent') NOT NULL DEFAULT 'routine',
  `status` ENUM('pending','scheduled','completed','deferred','cancelled') NOT NULL DEFAULT 'pending',
  `instructions` TEXT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `scheduled_at` DATETIME NULL,
  `scheduled_by` BIGINT UNSIGNED NULL,
  `completion_notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_followup_visit` (`visit_id`,`status`),
  KEY `idx_followup_due` (`status`,`recommended_date`),
  KEY `idx_followup_branch` (`branch_id`,`recommended_date`),
  KEY `idx_followup_booking_work` (`status`,`appointment_id`,`recommended_date`),
  CONSTRAINT `{{prefix}}fk_follow_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_follow_enc` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_follow_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_follow_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}diagnostic_review_tasks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `assigned_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `reason` TEXT NOT NULL,
  `priority` ENUM('routine','important','urgent') NOT NULL DEFAULT 'routine',
  `status` ENUM('pending','in_review','validated','closed','cancelled') NOT NULL DEFAULT 'pending',
  `source_role` VARCHAR(40) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dentist_notes` TEXT NULL,
  `validated_by` BIGINT UNSIGNED NULL,
  `validated_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_diag_review_dentist` (`assigned_dentist_user_id`,`status`,`priority`,`created_at`),
  KEY `idx_diag_review_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_diag_review_branch` (`branch_id`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_diag_review_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diag_review_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_dentist` FOREIGN KEY (`assigned_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_diag_review_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_validator` FOREIGN KEY (`validated_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}recalls` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `recall_type` VARCHAR(150) NOT NULL,
  `due_date` DATE NOT NULL,
  `status` ENUM('future','due_soon','due','overdue','contacted','scheduled','completed','deferred','declined','unable_to_contact') NOT NULL DEFAULT 'future',
  `contacted_at` DATETIME NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_recall_due` (`status`,`due_date`),
  CONSTRAINT `{{prefix}}fk_recall_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_recall_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}communications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `email_outbox_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `channel` ENUM('email','sms','phone','portal','in_person','other') NOT NULL DEFAULT 'phone',
  `direction` ENUM('outbound','inbound') NOT NULL DEFAULT 'outbound',
  `recipient` VARCHAR(190) NULL,
  `subject` VARCHAR(255) NULL,
  `summary` TEXT NOT NULL,
  `delivery_status` VARCHAR(60) NULL,
  `follow_up_needed` TINYINT(1) NOT NULL DEFAULT 0,
  `communicated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_comm_patient` (`patient_id`,`communicated_at`),
  KEY `idx_comm_email_outbox` (`email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_comm_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_comm_appt` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_comm_user` FOREIGN KEY (`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}}patient_insurance` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `provider` VARCHAR(190) NOT NULL,
  `member_no` VARCHAR(100) NULL,
  `plan_name` VARCHAR(150) NULL,
  `policy_holder` VARCHAR(190) NULL,
  `relationship_to_holder` VARCHAR(80) NULL,
  `coverage_start` DATE NULL,
  `coverage_end` DATE NULL,
  `authorization_required` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_insurance_patient` (`patient_id`,`is_active`),
  CONSTRAINT `{{prefix}}fk_ins_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}insurance_claims` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `insurance_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `claim_no` VARCHAR(100) NULL,
  `authorization_no` VARCHAR(120) NULL,
  `submission_reference` VARCHAR(120) NULL,
  `status` ENUM('draft','ready','submitted','pending','information_required','approved','partially_approved','denied','paid','appealed','closed') NOT NULL DEFAULT 'draft',
  `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,
  `submitted_at` DATETIME NULL,
  `paid_at` DATETIME NULL,
  `remittance_reference` VARCHAR(120) NULL,
  `payment_id` BIGINT UNSIGNED NULL,
  `settled_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `settled_by` BIGINT UNSIGNED NULL,
  `settled_at` DATETIME NULL,
  `reason` TEXT NULL,
  `notes` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_claim_status` (`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_claim_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_claim_ins` FOREIGN KEY (`insurance_id`) REFERENCES `{{prefix}}patient_insurance` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_claim_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE SET NULL
) 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`),
  CONSTRAINT `{{prefix}}fk_claim_history_claim` FOREIGN KEY (`claim_id`) REFERENCES `{{prefix}}insurance_claims` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_claim_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}}inventory_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `sku` VARCHAR(80) NOT NULL,
  `barcode` VARCHAR(120) NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(120) NULL,
  `unit` VARCHAR(40) NOT NULL DEFAULT 'unit',
  `supplier_name` VARCHAR(190) NULL,
  `default_supplier_id` BIGINT UNSIGNED NULL,
  `track_lots` TINYINT(1) NOT NULL DEFAULT 1,
  `is_clinical_supply` TINYINT(1) NOT NULL DEFAULT 1,
  `is_retail_product` TINYINT(1) NOT NULL DEFAULT 0,
  `purchase_cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `selling_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `usage_cost` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reorder_level` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `current_stock` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('active','inactive','discontinued') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_sku` (`sku`),
  KEY `idx_inventory_barcode` (`barcode`),
  KEY `idx_inventory_stock` (`status`,`current_stock`,`reorder_level`),
  CONSTRAINT `{{prefix}}fk_inventory_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}}inventory_items` (`sku`,`name`,`category`,`unit`,`track_lots`,`is_clinical_supply`,`purchase_cost`,`usage_cost`,`reorder_level`,`current_stock`,`status`,`created_at`) VALUES
('SUP-PPE-001','Nitrile Examination Gloves - Small','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-002','Nitrile Examination Gloves - Medium','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-003','Nitrile Examination Gloves - Large','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-004','Surgical Face Masks','PPE','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-005','Disposable Isolation Gowns','PPE','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-006','Disposable Head Caps','PPE','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PPE-007','Face Shields','PPE','piece',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-001','Patient Bibs','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-002','Cotton Rolls','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-003','Cotton Pellets','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-004','Sterile Gauze Pads 2 x 2','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-005','Sterile Gauze Pads 4 x 4','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-006','Saliva Ejectors','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-007','High-Volume Evacuation Tips','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-008','Disposable Air-Water Syringe Tips','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-009','Disposable Prophy Cups','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-DSB-010','Disposable Prophy Brushes','Disposable supplies','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-001','Lidocaine 2% with Epinephrine Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-002','Articaine 4% with Epinephrine Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-003','Mepivacaine 3% Cartridges','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-004','Topical Anesthetic Gel','Anesthetics','tube',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-005','Dental Needles - Short','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ANE-006','Dental Needles - Long','Anesthetics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-001','Phosphoric Acid Etchant Gel 37%','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-002','Universal Dental Bonding Agent','Restorative','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-003','Universal Composite Resin','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-004','Flowable Composite Resin','Restorative','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-005','Glass Ionomer Restorative Cement','Restorative','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-006','Temporary Filling Material','Restorative','jar',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-007','Calcium Hydroxide Liner','Restorative','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-008','Matrix Bands','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-009','Interdental Wedges','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-RES-010','Articulating Paper','Restorative','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-001','Prophylaxis Paste','Preventive','jar',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-002','Fluoride Varnish','Preventive','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-PRE-003','Pit and Fissure Sealant','Preventive','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-001','Gutta-Percha Points','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-002','Absorbent Paper Points','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-003','Sodium Hypochlorite Irrigant','Endodontics','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-004','EDTA Root Canal Lubricant','Endodontics','syringe',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-005','Endodontic Irrigation Syringes','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-END-006','Rubber Dam Sheets','Endodontics','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-001','Alginate Impression Material','Impression','bag',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-002','PVS Impression Material','Impression','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IMP-003','Bite Registration Material','Impression','cartridge',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-LAB-001','Dental Stone','Laboratory','bag',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-LAB-002','Cold-Cure Acrylic Resin','Laboratory','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-001','Sterilization Pouches - Small','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-002','Sterilization Pouches - Medium','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-003','Sterilization Pouches - Large','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-004','Chemical Indicator Tape','Infection control','roll',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-005','Biological Indicator Tests','Infection control','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-006','Enzymatic Instrument Cleaner','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-007','Surface Disinfectant','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-IPC-008','Ultrasonic Cleaning Solution','Infection control','bottle',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-001','Digital Sensor Sleeves','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-002','Phosphor Plate Barrier Envelopes','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-XRY-003','Intraoral X-Ray Film','Radiography','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-001','Orthodontic Elastics','Orthodontics','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-002','Orthodontic Archwires','Orthodontics','pack',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-ORT-003','Orthodontic Brackets','Orthodontics','kit',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-001','Sutures','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-002','Hemostatic Sponges','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW()),
('SUP-SUR-003','Surgical Blades','Surgery','box',1,1,0.00,0.00,0.00,0.00,'active',NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`category`=VALUES(`category`),`unit`=VALUES(`unit`),`status`='active';

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_lots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `received_document_id` BIGINT UNSIGNED NULL,
  `lot_number` VARCHAR(100) NULL,
  `expiry_date` DATE NULL,
  `quantity_received` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `quantity_remaining` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `unit_cost` DECIMAL(12,2) NULL,
  `received_at` DATE NULL,
  `status` ENUM('active','depleted','expired','disposed','quarantined') NOT NULL DEFAULT 'active',
  `quarantined_at` DATETIME NULL,
  `quarantine_reason` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_lot_expiry` (`expiry_date`,`status`),
  KEY `idx_lot_branch_item` (`branch_id`,`item_id`,`status`),
  KEY `idx_lot_lookup` (`item_id`,`branch_id`,`lot_number`),
  CONSTRAINT `{{prefix}}fk_lot_item` FOREIGN KEY (`item_id`) REFERENCES `{{prefix}}inventory_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `lot_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `document_id` BIGINT UNSIGNED NULL,
  `document_line_id` BIGINT UNSIGNED NULL,
  `movement_no` VARCHAR(40) NULL,
  `counterpart_transaction_id` BIGINT UNSIGNED NULL,
  `transaction_type` ENUM('purchase','transfer_in','transfer_out','procedure_consumption','issue','return','adjustment','damage','expiration','disposal','stock_count') NOT NULL,
  `quantity` DECIMAL(12,2) NOT NULL,
  `unit_cost` DECIMAL(12,2) NULL,
  `balance_before` DECIMAL(12,2) NULL,
  `balance_after` DECIMAL(12,2) NULL,
  `status` ENUM('posted','voided') NOT NULL DEFAULT 'posted',
  `reference_type` VARCHAR(80) NULL,
  `reference_id` VARCHAR(80) NULL,
  `notes` TEXT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `posted_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_tx_item` (`item_id`,`created_at`),
  UNIQUE KEY `uq_inventory_movement_no` (`movement_no`),
  KEY `idx_inventory_tx_document` (`document_id`,`document_line_id`),
  CONSTRAINT `{{prefix}}fk_invtx_item` FOREIGN KEY (`item_id`) REFERENCES `{{prefix}}inventory_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_invtx_lot` FOREIGN KEY (`lot_id`) REFERENCES `{{prefix}}inventory_lots` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invtx_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_invtx_user` FOREIGN KEY (`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}}sterilization_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `equipment_id` BIGINT UNSIGNED NULL,
  `sterilizer_name` VARCHAR(150) NOT NULL,
  `cycle_no` VARCHAR(80) NULL,
  `load_no` VARCHAR(80) NULL,
  `cycle_started_at` DATETIME NOT NULL,
  `cycle_completed_at` DATETIME NULL,
  `operator_user_id` BIGINT UNSIGNED NULL,
  `cycle_status` ENUM('loaded','running','completed','quarantined','failed','released','cancelled') NOT NULL DEFAULT 'loaded',
  `temperature_c` DECIMAL(6,2) NULL,
  `pressure_kpa` DECIMAL(8,2) NULL,
  `exposure_minutes` INT UNSIGNED NULL,
  `chemical_indicator_result` ENUM('pending','pass','fail') NOT NULL DEFAULT 'pending',
  `biological_indicator_required` TINYINT(1) NOT NULL DEFAULT 0,
  `indicator_result` VARCHAR(100) NULL,
  `biological_result` VARCHAR(100) NULL,
  `biological_indicator_result` ENUM('not_required','pending','pass','fail') NOT NULL DEFAULT 'not_required',
  `completed_by` BIGINT UNSIGNED NULL,
  `released_by` BIGINT UNSIGNED NULL,
  `released_at` DATETIME NULL,
  `release_note` TEXT NULL,
  `failure_reason` TEXT NULL,
  `batch_hash` CHAR(64) NULL,
  `release_status` ENUM('pending','released','failed','quarantined') NOT NULL DEFAULT 'pending',
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sterile_status` (`release_status`,`cycle_started_at`),
  KEY `idx_sterile_cycle_status` (`branch_id`,`cycle_status`,`cycle_started_at`),
  KEY `idx_sterile_cycle_load` (`branch_id`,`cycle_no`,`load_no`),
  CONSTRAINT `{{prefix}}fk_sterile_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_sterile_user` FOREIGN KEY (`operator_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DentalCare v0.5.9.8 inventory, procurement, and sterilization integrity.

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_suppliers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(190) NOT NULL,
  `contact_person` VARCHAR(150) NULL,
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(60) NULL,
  `address` TEXT NULL,
  `tax_id` VARCHAR(80) NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_supplier_name` (`name`),
  KEY `idx_inventory_supplier_status` (`status`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_branch_stocks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `current_stock` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reorder_level` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `last_reconciled_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_branch_stock` (`item_id`,`branch_id`),
  KEY `idx_inventory_branch_alert` (`branch_id`,`current_stock`,`reorder_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_no` VARCHAR(40) NOT NULL,
  `document_type` ENUM('purchase_order','goods_receipt','issue','return','transfer','stock_count','adjustment','damage','expiry','disposal') NOT NULL,
  `source_document_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `source_branch_id` BIGINT UNSIGNED NULL,
  `destination_branch_id` BIGINT UNSIGNED NULL,
  `supplier_id` BIGINT UNSIGNED NULL,
  `requested_for_user_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `usage_destination` VARCHAR(40) NULL,
  `chair_id` BIGINT UNSIGNED NULL,
  `evidence_file_path` VARCHAR(255) NULL,
  `evidence_original_name` VARCHAR(255) NULL,
  `external_reference` VARCHAR(120) NULL,
  `reason` VARCHAR(500) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('draft','submitted','approved','rejected','posted','dispatched','received','cancelled') NOT NULL DEFAULT 'draft',
  `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,
  `posted_by` BIGINT UNSIGNED NULL,
  `posted_at` DATETIME NULL,
  `received_by` BIGINT UNSIGNED NULL,
  `received_at` DATETIME NULL,
  `document_hash` CHAR(64) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_document_no` (`document_no`),
  KEY `idx_inventory_document_work` (`document_type`,`status`,`branch_id`,`requested_at`),
  KEY `idx_inventory_document_transfer` (`source_branch_id`,`destination_branch_id`,`status`),
  KEY `idx_inventory_document_source` (`source_document_id`),
  KEY `idx_inventory_document_encounter` (`encounter_id`,`status`),
  KEY `idx_inventory_document_destination` (`usage_destination`,`chair_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_document_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` BIGINT UNSIGNED NOT NULL,
  `source_line_id` BIGINT UNSIGNED NULL,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `lot_id` BIGINT UNSIGNED NULL,
  `lot_number` VARCHAR(100) NULL,
  `expiry_date` DATE NULL,
  `quantity` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `unit_cost` DECIMAL(12,2) NULL,
  `recorded_quantity` DECIMAL(12,2) NULL,
  `counted_quantity` DECIMAL(12,2) NULL,
  `variance_quantity` DECIMAL(12,2) NULL,
  `notes` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_document_line` (`document_id`,`item_id`),
  KEY `idx_inventory_source_line` (`source_line_id`),
  KEY `idx_inventory_document_lot` (`lot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_document_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(30) NULL,
  `new_status` VARCHAR(30) NOT NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_document_history` (`document_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_equipment` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `serial_number` VARCHAR(120) NULL,
  `manufacturer` VARCHAR(150) NULL,
  `model` VARCHAR(120) NULL,
  `last_service_date` DATE NULL,
  `next_service_date` DATE NULL,
  `status` ENUM('active','maintenance','out_of_service','retired') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sterilization_equipment` (`branch_id`,`name`),
  KEY `idx_sterilization_equipment_status` (`branch_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}instrument_sets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `set_code` VARCHAR(80) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `contents` TEXT NOT NULL,
  `status` ENUM('dirty','prepared','in_cycle','sterile','in_use','quarantined','retired') NOT NULL DEFAULT 'dirty',
  `last_batch_id` BIGINT UNSIGNED NULL,
  `prepared_by` BIGINT UNSIGNED NULL,
  `prepared_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_instrument_set_code` (`branch_id`,`set_code`),
  KEY `idx_instrument_set_work` (`branch_id`,`status`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_batch_sets` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `instrument_set_id` BIGINT UNSIGNED NOT NULL,
  `loaded_by` BIGINT UNSIGNED NULL,
  `loaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sterilization_batch_set` (`batch_id`,`instrument_set_id`),
  KEY `idx_sterilization_set_batch` (`instrument_set_id`,`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sterilization_batch_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(30) NULL,
  `new_status` VARCHAR(30) NOT NULL,
  `event_data_json` JSON NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sterilization_batch_event` (`batch_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}instrument_usage` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `instrument_set_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `procedure_id` BIGINT UNSIGNED NULL,
  `assigned_by` BIGINT UNSIGNED NOT NULL,
  `assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `returned_by` BIGINT UNSIGNED NULL,
  `returned_at` DATETIME NULL,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_instrument_usage_patient` (`patient_id`,`assigned_at`),
  KEY `idx_instrument_usage_set` (`instrument_set_id`,`returned_at`),
  KEY `idx_instrument_usage_batch` (`batch_id`,`assigned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(100) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `module_group` VARCHAR(80) NOT NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permission_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}role_permissions` (
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  `allowed` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`,`permission_id`),
  CONSTRAINT `{{prefix}}fk_roleperm_role` FOREIGN KEY (`role_id`) REFERENCES `{{prefix}}roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_roleperm_perm` FOREIGN KEY (`permission_id`) REFERENCES `{{prefix}}permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_portal_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NULL,
  `email` VARCHAR(190) NOT NULL,
  `contact_number` VARCHAR(50) NULL,
  `job_title` VARCHAR(120) NULL,
  `address` TEXT NULL,
  `emergency_contact_name` VARCHAR(150) NULL,
  `emergency_contact_phone` VARCHAR(50) NULL,
  `profile_completed_at` DATETIME NULL,
  `password_hash` VARCHAR(255) NULL,
  `status` ENUM('invited','active','inactive','locked') NOT NULL DEFAULT 'invited',
  `last_login_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_portal_primary_patient` (`patient_id`),
  UNIQUE KEY `uq_portal_email` (`email`),
  CONSTRAINT `{{prefix}}fk_portal_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_portal_invites` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `guardian_id` BIGINT UNSIGNED NULL,
  `email` VARCHAR(190) NOT NULL,
  `contact_number` VARCHAR(50) NULL,
  `job_title` VARCHAR(120) NULL,
  `address` TEXT NULL,
  `emergency_contact_name` VARCHAR(150) NULL,
  `emergency_contact_phone` VARCHAR(50) NULL,
  `profile_completed_at` DATETIME NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME NULL,
  `email_outbox_id` BIGINT UNSIGNED NULL,
  `last_sent_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_portal_invite_patient` (`patient_id`,`expires_at`),
  KEY `idx_portal_invite_guardian` (`guardian_id`,`expires_at`),
  KEY `idx_portal_invite_email` (`email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_invite_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_invite_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}}patient_portal_account_patients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `guardian_id` BIGINT UNSIGNED NULL,
  `is_self` TINYINT(1) NOT NULL DEFAULT 0,
  `can_view_records` TINYINT(1) NOT NULL DEFAULT 1,
  `can_manage_appointments` TINYINT(1) NOT NULL DEFAULT 0,
  `can_submit_health` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','revoked') NOT NULL DEFAULT 'active',
  `authorized_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `authorized_by` BIGINT UNSIGNED NULL,
  `revoked_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_portal_account_patient` (`account_id`,`patient_id`),
  KEY `idx_portal_patient_accounts` (`patient_id`,`status`),
  KEY `idx_portal_guardian_accounts` (`guardian_id`,`status`),
  CONSTRAINT `{{prefix}}fk_portal_map_account` FOREIGN KEY (`account_id`) REFERENCES `{{prefix}}patient_portal_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_map_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_map_guardian` FOREIGN KEY (`guardian_id`) REFERENCES `{{prefix}}patient_guardians` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_map_authorizer` FOREIGN KEY (`authorized_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_portal_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `request_type` VARCHAR(60) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `requested_start_at` DATETIME NULL,
  `requested_end_at` DATETIME NULL,
  `preferred_date` DATE NULL,
  `preferred_time_from` TIME NULL,
  `preferred_time_to` TIME NULL,
  `reason` VARCHAR(500) NULL,
  `payload_json` LONGTEXT NULL,
  `staff_note` TEXT NULL,
  `resolved_by` BIGINT UNSIGNED NULL,
  `resolved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_portal_request_patient` (`patient_id`,`created_at`),
  KEY `idx_portal_request_status` (`status`,`request_type`,`created_at`),
  KEY `idx_portal_request_appointment` (`appointment_id`,`status`),
  KEY `idx_portal_request_followup` (`follow_up_id`,`status`),
  CONSTRAINT `{{prefix}}fk_portal_request_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_request_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_request_followup` FOREIGN KEY (`follow_up_id`) REFERENCES `{{prefix}}follow_ups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_request_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}system_uploads` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `category` VARCHAR(100) NOT NULL,
  `title` VARCHAR(190) NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `description` TEXT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_system_upload_category` (`category`,`created_at`),
  KEY `idx_system_upload_branch` (`branch_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_system_upload_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_system_upload_user` FOREIGN KEY (`uploaded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}backup_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NOT NULL DEFAULT 'created',
  `backup_type` VARCHAR(30) NOT NULL DEFAULT 'database',
  `checksum_sha256` CHAR(64) NULL,
  `file_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `verified_at` DATETIME NULL,
  `verification_evidence_json` LONGTEXT NULL,
  `verification_file_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `verification_format_version` INT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_backup_created` (`created_at`),
  CONSTRAINT `{{prefix}}fk_backup_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}}system_update_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `run_id` CHAR(32) NOT NULL,
  `target_version` VARCHAR(40) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `plan_json` LONGTEXT NOT NULL,
  `completed_json` LONGTEXT NULL,
  `current_version` VARCHAR(40) NULL,
  `current_label` VARCHAR(190) NULL,
  `step_index` INT UNSIGNED NOT NULL DEFAULT 0,
  `step_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_error` TEXT NULL,
  `error_reference` VARCHAR(40) NULL,
  `started_by` BIGINT UNSIGNED NULL,
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_system_update_run_id` (`run_id`),
  KEY `idx_system_update_status` (`status`,`target_version`,`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('patients.view','View patient records','Patient Care','View patient records'),
('patients.manage','Create and edit patients','Patient Care','Create and edit patients'),
('appointments.manage','Manage appointments and online requests','Patient Care','Manage appointments and online requests'),
('appointments.schedule_bypass','Bypass schedule controls','Schedule','Override published availability or conflicts with a mandatory audited reason'),
('queue.manage','Manage appointment and walk-in queues','Patient Care','Manage appointment and walk-in queues'),
('clinical.view','View clinical records','Clinical','View clinical records'),
('clinical.manage','Create clinical records','Clinical','Create clinical records'),
('clinical.finalize','Finalize clinical notes and prescriptions','Clinical','Finalize clinical notes and prescriptions'),
('billing.manage','Manage invoices and payments','Finance','Manage invoices and payments'),
('insurance.manage','Manage insurance and HMO claims','Finance','Manage insurance and HMO claims'),
('inventory.manage','Manage inventory and stock','Operations','Manage inventory and stock'),
('inventory.catalog','Add retail products and supply catalog items','Operations','Create product and supply catalog records without receiving, issuing, transferring, adjusting, approving, or disposing stock'),
('sterilization.manage','Manage sterilization records','Operations','Manage sterilization records'),
('communications.manage','Manage patient communications','Operations','Manage patient communications'),
('reports.view','View operational and financial reports','Reports','View operational and financial reports'),
('staff.manage','Manage staff and dentists','Administration','Manage staff and dentists'),
('configuration.manage','Manage clinic configuration','Administration','Manage clinic configuration'),
('system.manage','Manage system updates, backups, and permissions','Administration','Manage system updates, backups, and permissions'),
('uploads.manage','Manage clinic files and uploads','Administration','Upload, view, and download protected clinic files')
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'
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='receptionist' AND p.code IN ('patients.view','patients.manage','appointments.manage','queue.manage','communications.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='dentist' AND p.code IN ('patients.view','appointments.manage','queue.manage','clinical.view','clinical.manage','clinical.finalize','reports.view')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='dental_assistant' AND p.code IN ('patients.view','queue.manage','clinical.view','clinical.manage','sterilization.manage','inventory.catalog')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='cashier' AND p.code IN ('patients.view','queue.manage','billing.manage','reports.view')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='inventory_officer' AND p.code IN ('inventory.manage','sterilization.manage','reports.view')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);


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='staff' AND p.code IN ('patients.view','patients.manage','appointments.manage','queue.manage','communications.manage','reports.view','billing.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);



INSERT INTO `{{prefix}}dental_condition_catalog` (`code`,`name`,`category`,`default_severity`,`surface_specific`,`description`,`recommended_procedure`) VALUES
('sound','Sound tooth','condition','information',0,'No clinically significant finding.','Preventive care'),
('incipient_caries','Incipient caries','caries','mild',1,'Early enamel demineralization without cavitation.','Preventive or minimally invasive management'),
('enamel_caries','Enamel caries','caries','mild',1,'Caries clinically limited to enamel.','Preventive or restorative management'),
('dentinal_caries','Dentinal caries','caries','moderate',1,'Caries extending into dentin.','Caries removal and restoration'),
('deep_caries','Deep caries','caries','severe',1,'Deep lesion approaching the pulp.','Pulp assessment and definitive treatment'),
('recurrent_caries','Recurrent caries','caries','moderate',1,'Caries adjacent to an existing restoration.','Replace or repair restoration'),
('fractured','Fractured tooth','trauma','severe',1,'Loss of tooth structure due to fracture.','Restore, endodontic assessment, or extract as indicated'),
('wear_attrition','Attrition','wear','mild',1,'Tooth wear caused by tooth-to-tooth contact.','Occlusal assessment and protective management'),
('wear_abrasion','Abrasion','wear','mild',1,'Mechanical cervical tooth wear.','Risk-factor control and restoration if indicated'),
('wear_erosion','Erosion','wear','moderate',1,'Chemical loss of dental hard tissue.','Risk-factor control and restorative management'),
('mobility','Tooth mobility','periodontal','moderate',0,'Abnormal tooth mobility.','Periodontal assessment'),
('periapical_lesion','Periapical lesion','endodontic','severe',0,'Clinical or radiographic periapical pathology.','Endodontic evaluation'),
('pulpitis_suspected','Suspected pulpitis','endodontic','severe',0,'Symptoms or tests suggest pulpal inflammation.','Pulpal diagnosis and treatment'),
('root_fragment','Retained root fragment','surgical','severe',0,'Residual root structure without a maintainable crown.','Surgical assessment'),
('impacted','Impacted tooth','developmental','moderate',0,'Tooth prevented from normal eruption.','Surgical or orthodontic assessment'),
('unerupted','Unerupted tooth','developmental','information',0,'Tooth has not erupted.','Observe or investigate based on age and imaging'),
('missing','Missing tooth','missing','moderate',0,'Tooth absent from the arch.','Replacement-options assessment'),
('for_extraction','Indicated for extraction','surgical','severe',0,'Tooth judged non-restorable or otherwise indicated for removal.','Extraction'),
('under_observation','Under observation','monitoring','information',0,'Finding requires surveillance before definitive action.','Review appointment'),
('amalgam_restoration','Amalgam restoration','restoration','information',1,'Existing amalgam restoration.','Monitor, repair, or replace as indicated'),
('composite_restoration','Composite restoration','restoration','information',1,'Existing resin-composite restoration.','Monitor, repair, or replace as indicated'),
('temporary_restoration','Temporary restoration','restoration','moderate',1,'Interim restorative material.','Definitive restoration'),
('crown','Crown','prosthesis','information',0,'Full-coverage indirect restoration.','Monitor or replace as indicated'),
('pontic','Pontic','prosthesis','information',0,'Artificial tooth component of a fixed bridge.','Monitor prosthesis'),
('implant','Dental implant','prosthesis','information',0,'Implant-supported site.','Implant maintenance'),
('root_canal_treated','Root-canal treated','endodontic','information',0,'Previously completed endodontic treatment.','Monitor and restore as indicated'),
('completed_treatment','Completed treatment','completed','information',1,'Documented completion of a treatment-plan item.','Maintenance and review')
ON DUPLICATE KEY UPDATE name=VALUES(name),category=VALUES(category),default_severity=VALUES(default_severity),surface_specific=VALUES(surface_specific),description=VALUES(description),recommended_procedure=VALUES(recommended_procedure),is_active=1;


-- Doctor workflow and SMTP diagnostics (v0.5.0)
CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_specialties` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(60) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dentist_specialty_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_profiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `professional_title` VARCHAR(80) NULL,
  `display_name` VARCHAR(190) NULL,
  `photo_path` VARCHAR(255) NULL,
  `photo_original_path` VARCHAR(255) NULL,
  `photo_square_path` VARCHAR(255) NULL,
  `photo_portrait_path` VARCHAR(255) NULL,
  `photo_thumbnail_path` VARCHAR(255) NULL,
  `specialty_id` BIGINT UNSIGNED NULL,
  `subspecialty` VARCHAR(150) NULL,
  `license_number` VARCHAR(100) NULL,
  `license_expiry` DATE NULL,
  `accreditation_numbers` TEXT NULL,
  `years_in_practice` SMALLINT UNSIGNED NULL,
  `education` TEXT NULL,
  `qualifications` TEXT NULL,
  `certifications` TEXT NULL,
  `affiliations` TEXT NULL,
  `languages_json` JSON NULL,
  `biography` TEXT NULL,
  `public_biography` TEXT NULL,
  `signature_path` VARCHAR(255) NULL,
  `prescription_signature_path` VARCHAR(255) NULL,
  `preferred_tooth_numbering` ENUM('FDI','Universal','Palmer') NOT NULL DEFAULT 'FDI',
  `default_note_format` ENUM('SOAP','Narrative','Structured') NOT NULL DEFAULT 'SOAP',
  `employment_type` ENUM('full_time','part_time','visiting','contractual') NOT NULL DEFAULT 'full_time',
  `primary_branch_id` BIGINT UNSIGNED NULL,
  `default_chair_name` VARCHAR(100) NULL,
  `default_duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `maximum_daily_appointments` SMALLINT UNSIGNED NULL,
  `accepting_new_patients` TINYINT(1) NOT NULL DEFAULT 1,
  `public_profile_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `profile_status` ENUM('draft','active','suspended') NOT NULL DEFAULT 'draft',
  `credential_status` ENUM('pending','verified','rejected','suspended') NOT NULL DEFAULT 'pending',
  `credential_verified_by` BIGINT UNSIGNED NULL,
  `credential_verified_at` DATETIME NULL,
  `credential_note` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dentist_profile_user` (`user_id`),
  KEY `idx_dentist_profile_specialty` (`specialty_id`),
  KEY `idx_dentist_profile_branch` (`primary_branch_id`),
  KEY `idx_dentist_credential` (`credential_status`,`license_expiry`),
  CONSTRAINT `{{prefix}}fk_dentist_profile_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_profile_specialty` FOREIGN KEY (`specialty_id`) REFERENCES `{{prefix}}dentist_specialties` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dentist_profile_branch` FOREIGN KEY (`primary_branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinic_chairs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `chair_code` VARCHAR(40) NOT NULL,
  `chair_name` VARCHAR(100) NOT NULL,
  `room_name` VARCHAR(100) NULL,
  `usage_mode` ENUM('dentist_bound','shared_inhouse') NOT NULL DEFAULT 'dentist_bound',
  `status` ENUM('available','maintenance','inactive') NOT NULL DEFAULT 'available',
  `notes` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_clinic_chair_branch_code` (`branch_id`,`chair_code`),
  UNIQUE KEY `uq_clinic_chair_branch_name` (`branch_id`,`chair_name`),
  KEY `idx_clinic_chair_status` (`branch_id`,`status`,`usage_mode`),
  CONSTRAINT `{{prefix}}fk_clinic_chair_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_clinic_chair_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}}doctor_chair_assignments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `chair_id` BIGINT UNSIGNED NULL,
  `chair_name` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_doctor_chair_branch_doctor` (`dentist_user_id`,`branch_id`),
  UNIQUE KEY `uq_doctor_chair_branch_name` (`branch_id`,`chair_name`),
  KEY `idx_doctor_chair_active` (`branch_id`,`is_active`,`chair_name`),
  KEY `idx_doctor_chair_resource` (`chair_id`),
  CONSTRAINT `{{prefix}}fk_doctor_chair_resource` FOREIGN KEY (`chair_id`) REFERENCES `{{prefix}}clinic_chairs` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_doctor_chair_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doctor_chair_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_doctor_chair_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}}clinical_privileges` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(100) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `risk_level` ENUM('routine','moderate','high') NOT NULL DEFAULT 'routine',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_clinical_privilege_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_clinical_privileges` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `privilege_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('granted','pending','suspended','expired') NOT NULL DEFAULT 'granted',
  `effective_from` DATE NULL,
  `expires_at` DATE NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`dentist_user_id`,`privilege_id`),
  CONSTRAINT `{{prefix}}fk_dentist_priv_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_priv_privilege` FOREIGN KEY (`privilege_id`) REFERENCES `{{prefix}}clinical_privileges` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_priv_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}}dentist_branch_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`branch_id`),
  CONSTRAINT `{{prefix}}fk_dentist_branch_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_branch_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}}dentist_service_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `custom_duration_minutes` SMALLINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`service_id`),
  CONSTRAINT `{{prefix}}fk_dentist_service_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_service_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `document_type` ENUM('license','certification','training','accreditation','employment','other') NOT NULL DEFAULT 'other',
  `title` VARCHAR(190) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `issued_at` DATE NULL,
  `expires_at` DATE NULL,
  `verification_status` ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `verified_by` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dentist_document_user` (`dentist_user_id`,`document_type`),
  KEY `idx_dentist_document_expiry` (`expires_at`,`verification_status`),
  CONSTRAINT `{{prefix}}fk_dentist_document_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_document_verifier` FOREIGN KEY (`verified_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `template_type` ENUM('examination','procedure','soap','prescription','instruction') NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dentist_template_user` (`dentist_user_id`,`template_type`),
  CONSTRAINT `{{prefix}}fk_dentist_template_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_favorite_procedures` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`service_id`),
  CONSTRAINT `{{prefix}}fk_dentist_favorite_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_favorite_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_assistant_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `assistant_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `effective_from` DATE NULL,
  `effective_until` DATE NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`assistant_user_id`),
  CONSTRAINT `{{prefix}}fk_dentist_assistant_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_assistant_assistant` FOREIGN KEY (`assistant_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_assistant_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}smtp_diagnostics` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `test_type` ENUM('configuration','connection','authentication','delivery','queue') NOT NULL,
  `status` ENUM('success','warning','failed') NOT NULL,
  `host` VARCHAR(190) NULL,
  `port` INT UNSIGNED NULL,
  `encryption` VARCHAR(30) NULL,
  `message` TEXT NULL,
  `transcript` MEDIUMTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_smtp_diagnostic_created` (`created_at`),
  CONSTRAINT `{{prefix}}fk_smtp_diagnostic_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}dentist_specialties` (`code`,`name`,`description`) VALUES
('general_dentistry','General Dentistry','General examination, diagnosis, prevention, and routine dental treatment.'),
('orthodontics','Orthodontics','Diagnosis and treatment of tooth and jaw alignment.'),
('endodontics','Endodontics','Pulpal diagnosis and root canal treatment.'),
('periodontics','Periodontics','Gum, supporting tissue, and periodontal treatment.'),
('prosthodontics','Prosthodontics','Crowns, bridges, dentures, and implant prosthetics.'),
('pediatric_dentistry','Pediatric Dentistry','Dental care for children and adolescents.'),
('oral_surgery','Oral and Maxillofacial Surgery','Surgical dental and maxillofacial procedures.'),
('implant_dentistry','Implant Dentistry','Dental implant planning, placement, and restoration.'),
('oral_medicine','Oral Medicine','Oral diagnosis and nonsurgical management of oral disease.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}clinical_privileges` (`code`,`name`,`category`,`description`,`risk_level`) VALUES
('consultation','Consultation','General','Conduct dental consultations and patient assessments.','routine'),
('diagnosis','Diagnosis','General','Record and finalize dental diagnoses.','routine'),
('restoration','Restorative Treatment','Restorative','Perform and document dental restorations.','routine'),
('oral_prophylaxis','Oral Prophylaxis','Preventive','Perform preventive cleaning and prophylaxis.','routine'),
('simple_extraction','Simple Extraction','Surgery','Perform routine non-surgical extractions.','moderate'),
('surgical_extraction','Surgical Extraction','Surgery','Perform surgical extractions and impacted-tooth procedures.','high'),
('root_canal','Root Canal Treatment','Endodontics','Perform endodontic treatment episodes.','moderate'),
('orthodontics','Orthodontic Treatment','Orthodontics','Plan and perform orthodontic treatment.','moderate'),
('periodontal_surgery','Periodontal Surgery','Periodontics','Perform periodontal surgical treatment.','high'),
('implant_placement','Implant Placement','Implants','Plan and place dental implants.','high'),
('prosthodontics','Prosthodontic Treatment','Prosthodontics','Plan and deliver crowns, bridges, and dentures.','moderate'),
('pediatric_treatment','Pediatric Treatment','Pediatric','Provide pediatric dental treatment.','moderate'),
('sedation','Sedation','Anesthesia','Provide authorized dental sedation.','high'),
('prescription','Prescription Authority','Medication','Issue and finalize dental prescriptions.','moderate'),
('clinical_note_finalization','Clinical Note Finalization','Documentation','Finalize clinical notes under the dentist identity.','routine')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`category`=VALUES(`category`),`description`=VALUES(`description`),`risk_level`=VALUES(`risk_level`);

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('dentist_profile.view','View dentist professional profiles','Clinical','View dentist credentials, assignment, and public profile information.'),
('dentist_profile.manage_own','Manage own dentist profile','Clinical','Edit own profile, templates, favorites, and availability-related preferences.'),
('dentist_profile.manage_all','Manage all dentist profiles','Administration','Manage dentist assignments, specialties, public listing, and privileges.'),
('smtp.manage','Manage SMTP and email delivery','Administration','Edit SMTP configuration, run diagnostics, and process email queue.')
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 ('dentist_profile.view','dentist_profile.manage_all','smtp.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

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='dentist' AND p.code IN ('dentist_profile.view','dentist_profile.manage_own')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);


-- Data import and reset tools (v0.5.2)
CREATE TABLE IF NOT EXISTS `{{prefix}}data_import_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(20) NOT NULL,
  `status` ENUM('uploaded','previewed','importing','completed','partial','failed','cancelled') NOT NULL DEFAULT 'uploaded',
  `source_count_json` JSON NULL,
  `imported_count_json` JSON NULL,
  `skipped_count_json` JSON NULL,
  `report_json` LONGTEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_import_batch_status` (`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_import_batch_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}}demo_record_registry` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` BIGINT UNSIGNED NULL,
  `entity_type` VARCHAR(80) NOT NULL,
  `entity_id` BIGINT UNSIGNED NOT NULL,
  `external_key` VARCHAR(190) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_demo_entity` (`entity_type`,`entity_id`),
  KEY `idx_demo_batch` (`batch_id`,`entity_type`),
  CONSTRAINT `{{prefix}}fk_demo_batch` FOREIGN KEY (`batch_id`) REFERENCES `{{prefix}}data_import_batches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}data_reset_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reset_type` ENUM('demo_only','clinic_activity','factory_defaults') NOT NULL,
  `backup_file` VARCHAR(255) NOT NULL,
  `summary_json` JSON NULL,
  `performed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reset_log_date` (`created_at`),
  CONSTRAINT `{{prefix}}fk_reset_user` FOREIGN KEY (`performed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `{{prefix}}print_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_type` VARCHAR(80) NOT NULL,
  `record_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `format` VARCHAR(20) NOT NULL DEFAULT 'html',
  `reason` VARCHAR(255) NULL,
  `printed_by` BIGINT UNSIGNED NULL,
  `printed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_print_patient` (`patient_id`,`printed_at`),
  KEY `idx_print_type` (`document_type`,`printed_at`),
  KEY `idx_print_user_time` (`printed_by`,`printed_at`),
  KEY `idx_print_branch_time` (`branch_id`,`printed_at`),
  CONSTRAINT `{{prefix}}fk_print_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_print_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_print_user` FOREIGN KEY (`printed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('records.print','Print and export authorized records','Reports','Print patient, clinical, finance, schedule, inventory, and audit records within the user’s assigned scope')
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 ('clinic_admin','dentist','receptionist','staff','dental_assistant','cashier','inventory_officer') AND p.code='records.print'
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);


CREATE TABLE IF NOT EXISTS `{{prefix}}patient_intakes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `intake_type` ENUM('new_patient','returning_patient','walk_in','follow_up','recall') NOT NULL DEFAULT 'returning_patient',
  `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,
  `allergies_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `allergy_changes` TEXT NULL,
  `medications_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `medication_changes` TEXT NULL,
  `medical_changes` TEXT NULL,
  `dental_history_changes` TEXT NULL,
  `pain_score` TINYINT UNSIGNED NULL,
  `consent_acknowledged` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('draft','complete','reviewed') NOT NULL DEFAULT 'draft',
  `completed_by` BIGINT UNSIGNED NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_intake_queue` (`queue_entry_id`),
  KEY `idx_intake_patient` (`patient_id`,`created_at`),
  KEY `idx_intake_appointment` (`appointment_id`),
  CONSTRAINT `{{prefix}}fk_intake_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_intake_request` FOREIGN KEY (`appointment_request_id`) REFERENCES `{{prefix}}appointment_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_intake_user` FOREIGN KEY (`completed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `{{prefix}}diagnosis_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(120) NULL,
  `default_status` ENUM('provisional','confirmed','chronic','under_observation','referred') NOT NULL DEFAULT 'provisional',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_diagnosis_catalog_name` (`name`),
  KEY `idx_diagnosis_catalog_search` (`is_active`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `{{prefix}}diagnosis_catalog` (`code`,`name`,`category`,`default_status`,`created_at`) VALUES
('K02.9','Dental caries, unspecified','Caries','confirmed',NOW()),
('K04.0','Pulpitis','Endodontic','provisional',NOW()),
('K04.1','Necrosis of pulp','Endodontic','confirmed',NOW()),
('K04.4','Acute apical periodontitis of pulpal origin','Endodontic','provisional',NOW()),
('K04.5','Chronic apical periodontitis','Endodontic','chronic',NOW()),
('K05.1','Chronic gingivitis','Periodontal','chronic',NOW()),
('K05.3','Chronic periodontitis','Periodontal','chronic',NOW()),
('K08.8','Other specified disorders of teeth and supporting structures','General','provisional',NOW()),
('K12.1','Other forms of stomatitis','Soft tissue','provisional',NOW()),
('K10.2','Inflammatory conditions of jaws','Oral surgery','provisional',NOW()),
('S02.5','Fracture of tooth','Trauma','confirmed',NOW()),
(NULL,'Reversible pulpitis','Endodontic','provisional',NOW()),
(NULL,'Irreversible pulpitis','Endodontic','provisional',NOW()),
(NULL,'Periapical abscess','Endodontic','confirmed',NOW()),
(NULL,'Tooth sensitivity','General','under_observation',NOW()),
(NULL,'Impacted tooth','Oral surgery','confirmed',NOW()),
(NULL,'Temporomandibular joint disorder','TMJ','under_observation',NOW()),
(NULL,'Oral soft-tissue lesion','Soft tissue','provisional',NOW());

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_usage_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `inventory_item_id` BIGINT UNSIGNED NULL,
  `usage_type` ENUM('medication_administered','medicine_dispense','consumable','equipment','diagnostic_service') NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit` VARCHAR(40) NULL,
  `billing_mode` ENUM('included','bill_separately','track_only') NOT NULL DEFAULT 'track_only',
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `fulfillment_status` ENUM('draft','requested','confirmed','cancelled') NOT NULL DEFAULT 'draft',
  `notes` VARCHAR(500) NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `confirmed_by` BIGINT UNSIGNED NULL,
  `confirmed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_usage_encounter` (`encounter_id`,`fulfillment_status`),
  KEY `idx_clinical_usage_patient` (`patient_id`,`created_at`),
  KEY `idx_clinical_usage_inventory` (`inventory_item_id`,`fulfillment_status`),
  CONSTRAINT `{{prefix}}fk_usage_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_usage_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_usage_inventory` FOREIGN KEY (`inventory_item_id`) REFERENCES `{{prefix}}inventory_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_assessments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `extraoral_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `extraoral_findings` TEXT NULL,
  `intraoral_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `intraoral_findings` TEXT NULL,
  `soft_tissue_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `soft_tissue_findings` TEXT NULL,
  `tmj_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `tmj_findings` TEXT NULL,
  `occlusion_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `occlusion_findings` TEXT NULL,
  `oral_hygiene_status` VARCHAR(80) NULL,
  `radiographic_status` ENUM('normal','abnormal','not_examined','not_applicable') NOT NULL DEFAULT 'not_examined',
  `radiographic_findings` TEXT NULL,
  `risk_assessment` TEXT NULL,
  `prognosis` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_assessment_encounter` (`encounter_id`),
  KEY `idx_assessment_patient` (`patient_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_assessment_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_assessment_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_assessment_user` FOREIGN KEY (`recorded_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}diagnoses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `tooth_region` VARCHAR(80) NULL,
  `diagnosis_code` VARCHAR(80) NULL,
  `diagnosis_name` VARCHAR(255) NOT NULL,
  `severity` ENUM('mild','moderate','severe','critical') NULL,
  `diagnosis_status` ENUM('provisional','confirmed','resolved','chronic','under_observation','referred') NOT NULL DEFAULT 'provisional',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `diagnosed_by` BIGINT UNSIGNED NULL,
  `diagnosed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_diagnosis_patient` (`patient_id`,`diagnosed_at`),
  KEY `idx_diagnosis_encounter` (`encounter_id`,`is_primary`),
  CONSTRAINT `{{prefix}}fk_diagnosis_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diagnosis_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diagnosis_user` FOREIGN KEY (`diagnosed_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Version 0.5.8.6 clinical linkage constraints are added after all related
-- tables exist, avoiding fresh-install creation-order failures.
ALTER TABLE `{{prefix}}odontogram_entries`
  ADD CONSTRAINT `{{prefix}}fk_odon_diagnosis` FOREIGN KEY (`diagnosis_id`) REFERENCES `{{prefix}}diagnoses` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `{{prefix}}fk_odon_plan_item` FOREIGN KEY (`treatment_plan_item_id`) REFERENCES `{{prefix}}treatment_plan_items` (`id`) ON DELETE SET NULL;
ALTER TABLE `{{prefix}}treatment_plan_items`
  ADD CONSTRAINT `{{prefix}}fk_plan_item_diagnosis` FOREIGN KEY (`diagnosis_id`) REFERENCES `{{prefix}}diagnoses` (`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `{{prefix}}visit_workflow_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visit_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `appointment_request_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `follow_up_id` BIGINT UNSIGNED NULL,
  `stage_code` VARCHAR(60) NOT NULL,
  `stage_label` VARCHAR(150) NOT NULL,
  `details` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_workflow_visit` (`visit_id`,`created_at`),
  KEY `idx_workflow_patient` (`patient_id`,`created_at`),
  KEY `idx_workflow_appointment` (`appointment_id`,`created_at`),
  KEY `idx_workflow_queue` (`queue_entry_id`,`created_at`),
  CONSTRAINT `{{prefix}}fk_workflow_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_workflow_request` FOREIGN KEY (`appointment_request_id`) REFERENCES `{{prefix}}appointment_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_queue` FOREIGN KEY (`queue_entry_id`) REFERENCES `{{prefix}}queue_entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_followup` FOREIGN KEY (`follow_up_id`) REFERENCES `{{prefix}}follow_ups` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_workflow_user` FOREIGN KEY (`recorded_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.8.3: editable email templates for fresh installations.
INSERT IGNORE INTO `{{prefix}}email_templates`
(`template_key`,`name`,`category`,`subject_template`,`html_template`,`text_template`,`allowed_variables_json`,`is_active`,`is_system`,`created_at`) VALUES
('staff_account_invite','Staff account invitation','Accounts','Your {{clinic_name}} account is ready','<h2>Welcome to {{clinic_name}}</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{role_name}}</strong> account has been created.</p><p><a href="{{activation_url}}">Create your password and activate your account</a></p><p>This secure link expires in {{expires_hours}} hours.</p><p>If you were not expecting this account, contact {{support_email}}.</p>','Welcome to {{clinic_name}}. Create your password: {{activation_url}}. This link expires in {{expires_hours}} hours.','["clinic_name","recipient_name","role_name","activation_url","expires_hours","support_email","login_url"]',1,1,NOW()),
('staff_password_setup','Staff password setup or reset','Accounts','Set your {{clinic_name}} account password','<h2>Set your account password</h2><p>Hello {{recipient_name}},</p><p>An administrator requested a secure password setup for your {{clinic_name}} account.</p><p><a href="{{activation_url}}">Set a new password</a></p><p>This link expires in {{expires_hours}} hours.</p>','Set your {{clinic_name}} password: {{activation_url}}. The link expires in {{expires_hours}} hours.','["clinic_name","recipient_name","role_name","activation_url","expires_hours","support_email","login_url"]',1,1,NOW()),
('patient_portal_invite','Patient portal invitation','Accounts','Activate your {{clinic_name}} patient portal','<h2>Patient portal invitation</h2><p>Hello {{recipient_name}},</p><p>{{clinic_name}} invited you to securely review approved appointments and records.</p><p><a href="{{activation_url}}">Activate patient portal</a></p><p>This invitation expires in {{expires_hours}} hours.</p>','Activate your {{clinic_name}} patient portal: {{activation_url}}. This invitation expires in {{expires_hours}} hours.','["clinic_name","recipient_name","activation_url","expires_hours","support_email"]',1,1,NOW()),
('appointment_request_received','Appointment request received','Appointments','Dental appointment request {{reference}}','<h2>We received your appointment request</h2><p>Hello {{recipient_name}},</p><p>Your request has been submitted to {{clinic_name}}.</p><p><strong>Reference:</strong> {{reference}}<br><strong>Service:</strong> {{service_name}}<br><strong>Requested schedule:</strong> {{appointment_datetime}}</p><p>This is a request, not a confirmed appointment. The clinic will contact you after reviewing availability.</p>','We received your appointment request {{reference}} for {{service_name}} on {{appointment_datetime}}. The clinic will confirm availability.','["clinic_name","recipient_name","reference","service_name","appointment_datetime","support_email"]',1,1,NOW()),
('appointment_confirmed','Appointment confirmed','Appointments','Your dental appointment is confirmed','<h2>Your appointment is confirmed</h2><p>Hello {{recipient_name}},</p><p>Your appointment is scheduled for <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add this appointment to your calendar</a></p>','Your appointment is confirmed for {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('appointment_rescheduled','Appointment rescheduled','Appointments','Your dental appointment was rescheduled','<h2>Your appointment was rescheduled</h2><p>Hello {{recipient_name}},</p><p>Your new appointment schedule is <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Update your calendar</a></p>','Your appointment was rescheduled to {{appointment_datetime}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('appointment_cancelled','Appointment cancelled','Appointments','Your dental appointment was cancelled','<h2>Appointment cancelled</h2><p>Hello {{recipient_name}},</p><p>Your appointment for <strong>{{appointment_datetime}}</strong> has been cancelled.</p><p>To request another schedule, contact {{support_email}} or use the clinic booking page.</p>','Your appointment for {{appointment_datetime}} was cancelled. Contact {{support_email}} to book another schedule.','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","support_email","booking_url"]',1,1,NOW()),
('appointment_reminder','Appointment reminder','Reminders','Reminder: dental appointment in {{lead_time}}','<h2>Dental appointment reminder</h2><p>Hello {{recipient_name}},</p><p>This is a reminder for your appointment on <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add or update this appointment in your calendar</a></p><p>Please contact the clinic as soon as possible if you need to reschedule or cancel.</p>','Reminder: your dental appointment is on {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","lead_time","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('followup_reminder','Follow-up reminder','Reminders','Your dental follow-up is due','<h2>Your dental follow-up is due</h2><p>Hello {{recipient_name}},</p><p>Your clinic care team recommended a follow-up for <strong>{{due_date}}</strong>.</p><p><strong>Purpose:</strong> {{purpose}}<br><strong>{{provider_label}}:</strong> {{provider_name}}</p><p>{{instructions}}</p><p><a href="{{booking_url}}">Request your follow-up appointment</a></p>','Your dental follow-up is due on {{due_date}}. Request an appointment: {{booking_url}}','["clinic_name","recipient_name","due_date","purpose","provider_label","provider_name","instructions","booking_url","support_email"]',1,1,NOW()),
('recall_reminder','Recall reminder','Reminders','Your dental recall is due','<h2>Dental recall reminder</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{recall_type}}</strong> recall is due on <strong>{{due_date}}</strong>.</p><p><a href="{{booking_url}}">Request an appointment</a></p>','Your {{recall_type}} dental recall is due. Request an appointment: {{booking_url}}','["clinic_name","recipient_name","recall_type","due_date","booking_url","support_email"]',1,1,NOW()),
('payment_receipt','Payment receipt','Finance','Receipt from {{clinic_name}}','<h2>Payment received</h2><p>Hello {{recipient_name}},</p><p>We received your payment of <strong>{{amount}}</strong>.</p><p><strong>Receipt:</strong> {{receipt_number}}<br><strong>Date:</strong> {{payment_date}}</p><p>Thank you.</p>','Payment received: {{amount}}. Receipt {{receipt_number}} dated {{payment_date}}.','["clinic_name","recipient_name","amount","receipt_number","payment_date","support_email"]',1,1,NOW());

-- 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;

-- v0.8.0.3 General Staff Receptionist/Cashier combination
INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('reports.frontdesk','View front-desk operational reports','Reports','View front-desk appointment, queue, and service reports'),
('appointment.service_disposition','Defer or move appointment services','Patient Care','Defer, move, or preserve unfinished appointment services with an audited reason'),
('cashier.statement.print','Print cashier invoices, receipts, and statements','Finance','Print authorized invoices, receipts, statements, and patient ledgers'),
('cashier.session.manage','Open and close own cashier session','Finance','Open, operate, reconcile, and close only the signed-in user cashier session'),
('records.print','Print and export authorized records','Reports','Print patient, finance, schedule, and other records within the user assigned scope')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

UPDATE `{{prefix}}roles`
SET `name`='General Staff',
    `description`='Combined Receptionist and Cashier role for registration, appointments, queue, communications, cashier sessions, payments, receipts, and checkout without clinical, inventory, approval, or system authority'
WHERE `code`='staff';

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='staff' AND p.code IN ('patients.view','patients.manage','appointments.manage','queue.manage','communications.manage','reports.view','reports.frontdesk','records.print','appointment.service_disposition','billing.manage','cashier.statement.print','cashier.session.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

-- 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;


-- 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.2 patient portal and controlled record release.
ALTER TABLE `{{prefix}}patient_portal_accounts`
  ADD COLUMN IF NOT EXISTS `preferred_channel` VARCHAR(30) NOT NULL DEFAULT 'email' AFTER `profile_completed_at`,
  ADD COLUMN IF NOT EXISTS `appointment_notifications` TINYINT(1) NOT NULL DEFAULT 1 AFTER `preferred_channel`,
  ADD COLUMN IF NOT EXISTS `record_notifications` TINYINT(1) NOT NULL DEFAULT 1 AFTER `appointment_notifications`;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_record_releases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `release_scope` VARCHAR(30) NOT NULL DEFAULT 'summary',
  `date_from` DATE NULL,
  `date_to` DATE NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending_identity',
  `identity_verified_by` BIGINT UNSIGNED NULL,
  `identity_verified_at` DATETIME NULL,
  `clinical_approved_by` BIGINT UNSIGNED NULL,
  `clinical_approved_at` DATETIME NULL,
  `released_by` BIGINT UNSIGNED NULL,
  `released_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `revoked_by` BIGINT UNSIGNED NULL,
  `revoked_at` DATETIME NULL,
  `revocation_reason` VARCHAR(500) NULL,
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_viewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_patient_record_release_request` (`request_id`),
  KEY `idx_patient_record_release_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_patient_record_release_branch` (`branch_id`,`status`,`created_at`),
  KEY `idx_patient_record_release_expiry` (`status`,`expires_at`),
  CONSTRAINT `{{prefix}}fk_record_release_request` FOREIGN KEY (`request_id`) REFERENCES `{{prefix}}patient_portal_requests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_record_release_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_record_release_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_identity_user` FOREIGN KEY (`identity_verified_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_clinical_user` FOREIGN KEY (`clinical_approved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_released_user` FOREIGN KEY (`released_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_revoked_user` FOREIGN KEY (`revoked_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('records.release.receive','Receive patient record-copy requests','Records','Verify identity and route a patient dental-record request for approval.'),
('records.release.clinical_approve','Approve clinical record release','Records','Dentist review of finalized clinical content before patient release.'),
('records.release.manage','Release patient dental records','Records','Release an approved patient-safe dental-record copy through the secure portal.'),
('records.release.financial','Release patient financial records','Records','Release invoices, payments, receipts, and statements without clinical content.')
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','receptionist','staff') AND p.code='records.release.receive')
   OR (r.code='dentist' AND p.code='records.release.clinical_approve')
   OR (r.code IN ('super_admin','clinic_admin') AND p.code='records.release.manage')
   OR (r.code IN ('super_admin','clinic_admin','cashier','staff') AND p.code='records.release.financial')
ON DUPLICATE KEY UPDATE allowed=1;


-- DentalCare v0.9.1.3 navigation and route integrity.
-- No new persistent clinical or operational table is required; runtime readiness is recorded in system settings.

-- 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;
