-- DentalCare 0.5.9.8.26: encounter Dental Assistant assignment and delegated treatment-entry attribution.
ALTER TABLE `{{prefix}}clinical_encounters`
  ADD COLUMN IF NOT EXISTS `assistant_user_id` BIGINT UNSIGNED NULL AFTER `dentist_name`,
  ADD COLUMN IF NOT EXISTS `assistant_name` VARCHAR(150) NULL AFTER `assistant_user_id`,
  ADD COLUMN IF NOT EXISTS `assistant_assigned_by` BIGINT UNSIGNED NULL AFTER `assistant_name`,
  ADD COLUMN IF NOT EXISTS `assistant_assigned_at` DATETIME NULL AFTER `assistant_assigned_by`,
  ADD INDEX IF NOT EXISTS `idx_encounter_assistant` (`assistant_user_id`,`status`,`created_at`);

ALTER TABLE `{{prefix}}procedures`
  ADD COLUMN IF NOT EXISTS `entered_by_user_id` BIGINT UNSIGNED NULL AFTER `assistant_user_id`,
  ADD COLUMN IF NOT EXISTS `entered_by_role` VARCHAR(30) NULL AFTER `entered_by_user_id`,
  ADD COLUMN IF NOT EXISTS `verified_by_user_id` BIGINT UNSIGNED NULL AFTER `entered_by_role`,
  ADD COLUMN IF NOT EXISTS `verified_at` DATETIME NULL AFTER `verified_by_user_id`;

-- Existing procedure rows predate delegated entry. Preserve them as Dentist-entered records.
UPDATE `{{prefix}}procedures`
SET `entered_by_user_id`=COALESCE(`entered_by_user_id`,`dentist_user_id`),
    `entered_by_role`=COALESCE(NULLIF(`entered_by_role`,''),'dentist');
