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 1,
  `authorized_to_receive_records` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_guardian_patient` (`patient_id`),
  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,
  `encounter_id` BIGINT UNSIGNED NULL,
  `blood_pressure_systolic` SMALLINT UNSIGNED NULL,
  `blood_pressure_diastolic` SMALLINT UNSIGNED NULL,
  `pulse_rate` SMALLINT UNSIGNED NULL,
  `temperature_c` DECIMAL(4,1) NULL,
  `respiratory_rate` SMALLINT UNSIGNED NULL,
  `weight_kg` DECIMAL(6,2) NULL,
  `pain_score` TINYINT UNSIGNED NULL,
  `blood_glucose` DECIMAL(7,2) NULL,
  `pregnancy_status` VARCHAR(60) NULL,
  `medical_changes` 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`),
  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,
  `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,
  `chart_layer` ENUM('existing','proposed','in_progress','completed','historical') NOT NULL DEFAULT 'existing',
  `status` ENUM('active','resolved','superseded') NOT NULL DEFAULT 'active',
  `notes` TEXT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_odontogram_patient` (`patient_id`,`tooth_number`,`recorded_at`),
  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
) 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,
  `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,
  `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`),
  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,
  `diagnosis` VARCHAR(255) NULL,
  `tooth_region` 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,
  `status` ENUM('proposed','accepted','scheduled','in_progress','completed','deferred','declined','cancelled') NOT NULL DEFAULT 'proposed',
  `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`),
  CONSTRAINT `{{prefix}}fk_plan_item_plan` FOREIGN KEY (`treatment_plan_id`) REFERENCES `{{prefix}}treatment_plans` (`id`) ON DELETE CASCADE
) 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,
  `encounter_id` BIGINT UNSIGNED NULL,
  `treatment_plan_item_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `assistant_user_id` BIGINT UNSIGNED NULL,
  `procedure_name` VARCHAR(190) NOT NULL,
  `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`),
  CONSTRAINT `{{prefix}}fk_proc_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  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
) 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,
  `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,
  `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',
  `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`),
  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}}prescription_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `prescription_id` BIGINT UNSIGNED NOT NULL,
  `medication_name` VARCHAR(190) NOT NULL,
  `generic_name` VARCHAR(190) 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`),
  CONSTRAINT `{{prefix}}fk_rx_item_rx` FOREIGN KEY (`prescription_id`) REFERENCES `{{prefix}}prescriptions` (`id`) ON DELETE CASCADE
) 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,
  `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,
  `encounter_id` BIGINT UNSIGNED NULL,
  `treatment_plan_id` BIGINT UNSIGNED NULL,
  `consent_type` VARCHAR(150) NOT NULL,
  `version_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `content_snapshot` LONGTEXT NULL,
  `signer_name` VARCHAR(190) NULL,
  `signer_relationship` VARCHAR(100) NULL,
  `status` ENUM('draft','requested','signed','withdrawn','expired') NOT NULL DEFAULT 'draft',
  `signed_at` DATETIME NULL,
  `withdrawn_at` DATETIME NULL,
  `witness_user_id` BIGINT UNSIGNED NULL,
  `dentist_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`),
  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,
  `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,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `purpose` VARCHAR(190) NOT NULL,
  `recommended_date` DATE NOT NULL,
  `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,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_followup_due` (`status`,`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}}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,
  `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`),
  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,
  `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,
  `reason` TEXT NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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}}inventory_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `sku` VARCHAR(80) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(120) NULL,
  `unit` VARCHAR(40) NOT NULL DEFAULT 'unit',
  `supplier_name` VARCHAR(190) NULL,
  `purchase_cost` 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_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;

CREATE TABLE IF NOT EXISTS `{{prefix}}inventory_lots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` BIGINT UNSIGNED NOT 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,
  `received_at` DATE NULL,
  `status` ENUM('active','depleted','expired','disposed') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_lot_expiry` (`expiry_date`,`status`),
  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,
  `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,
  `reference_type` VARCHAR(80) NULL,
  `reference_id` VARCHAR(80) NULL,
  `notes` TEXT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_tx_item` (`item_id`,`created_at`),
  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,
  `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,
  `indicator_result` VARCHAR(100) NULL,
  `biological_result` VARCHAR(100) 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`),
  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;

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 NOT NULL,
  `email` VARCHAR(190) NOT 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`),
  UNIQUE KEY `uq_portal_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,
  `email` VARCHAR(190) NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_portal_invite_patient` (`patient_id`,`expires_at`),
  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}}backup_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('created','failed','deleted','restored') NOT NULL DEFAULT 'created',
  `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;

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'),
('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'),
('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')
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')
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);
