-- DentalCare v0.5.9.8.3
-- Provider choice, clinical task routing, dashboard integrity, credential correction history, and privilege UI support.

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;

UPDATE `{{prefix}}appointment_requests`
SET `provider_preference_mode`='clinic_decides'
WHERE `patient_type`='new'
  AND `preferred_dentist_user_id` IS NULL
  AND LOWER(TRIM(COALESCE(`preferred_dentist`,''))) IN ('let the dental clinic decide','clinic to assign');

UPDATE `{{prefix}}service_catalog`
SET `required_privilege_code` = CASE
  WHEN LOWER(`name`) REGEXP 'surgical extraction|impacted|odontectomy' THEN 'surgical_extraction'
  WHEN LOWER(`name`) REGEXP 'extraction|tooth removal' THEN 'simple_extraction'
  WHEN LOWER(`name`) REGEXP 'root canal|endodont' THEN 'root_canal'
  WHEN LOWER(`name`) REGEXP 'orthodont|braces|archwire' THEN 'orthodontics'
  WHEN LOWER(`name`) REGEXP 'implant' THEN 'implant_placement'
  WHEN LOWER(`name`) REGEXP 'crown|bridge|denture|prosthodont' THEN 'prosthodontics'
  WHEN LOWER(`name`) REGEXP 'periodontal surgery|flap surgery|gingivectomy' THEN 'periodontal_surgery'
  WHEN LOWER(`name`) REGEXP 'prophylaxis|cleaning|scaling' THEN 'oral_prophylaxis'
  WHEN LOWER(`name`) REGEXP 'restoration|filling|composite|glass ionomer' THEN 'restoration'
  WHEN LOWER(`name`) REGEXP 'pediatric|pulpotomy' THEN 'pediatric_treatment'
  WHEN LOWER(`name`) REGEXP 'sedation' THEN 'sedation'
  ELSE COALESCE(`required_privilege_code`,'consultation')
END
WHERE `provider_type`='dentist' AND (`required_privilege_code` IS NULL OR `required_privilege_code`='');
