-- DentalCare v0.5.7.6: daily tasks, calendar export, and continuity of care
-- Recommended installation method: Administration > System Setup > Updates & Database.
-- The PHP updater adds these columns and index idempotently and backfills from appointment history.

ALTER TABLE `{{prefix}}patients`
  ADD COLUMN IF NOT EXISTS `primary_dentist_user_id` BIGINT UNSIGNED NULL AFTER `branch_id`,
  ADD COLUMN IF NOT EXISTS `primary_dentist_assigned_at` DATETIME NULL AFTER `primary_dentist_user_id`,
  ADD COLUMN IF NOT EXISTS `primary_dentist_change_reason` VARCHAR(500) NULL AFTER `primary_dentist_assigned_at`,
  ADD COLUMN IF NOT EXISTS `primary_dentist_changed_by` BIGINT UNSIGNED NULL AFTER `primary_dentist_change_reason`;

UPDATE `{{prefix}}patients` p
SET p.primary_dentist_user_id=(
    SELECT a.dentist_user_id
    FROM `{{prefix}}appointments` a
    WHERE a.patient_id=p.id
      AND a.dentist_user_id IS NOT NULL
      AND a.status NOT IN ('cancelled','no_show')
    ORDER BY CASE WHEN a.status='completed' THEN 0 ELSE 1 END,a.start_at DESC,a.id DESC
    LIMIT 1
),
p.primary_dentist_assigned_at=NOW(),
p.primary_dentist_change_reason='Backfilled from appointment history'
WHERE p.primary_dentist_user_id IS NULL
  AND EXISTS (
      SELECT 1 FROM `{{prefix}}appointments` ax
      WHERE ax.patient_id=p.id
        AND ax.dentist_user_id IS NOT NULL
        AND ax.status NOT IN ('cancelled','no_show')
  );
