-- DentalCare 0.5.8.0
-- Doctor workflow integrity, administration coverage alerts, and in-house services.

ALTER TABLE `{{prefix}}service_catalog`
  ADD COLUMN IF NOT EXISTS `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist' AFTER `cleanup_minutes`,
  ADD COLUMN IF NOT EXISTS `provider_role_code` VARCHAR(40) NULL AFTER `provider_type`,
  ADD COLUMN IF NOT EXISTS `slot_capacity` SMALLINT UNSIGNED NOT NULL DEFAULT 1 AFTER `provider_role_code`,
  ADD COLUMN IF NOT EXISTS `requires_chair` TINYINT(1) NOT NULL DEFAULT 1 AFTER `slot_capacity`;

ALTER TABLE `{{prefix}}appointments`
  ADD COLUMN IF NOT EXISTS `service_id` BIGINT UNSIGNED NULL AFTER `patient_id`,
  ADD COLUMN IF NOT EXISTS `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist' AFTER `service_name`,
  ADD COLUMN IF NOT EXISTS `inhouse_provider_user_id` BIGINT UNSIGNED NULL AFTER `dentist_name`,
  ADD COLUMN IF NOT EXISTS `inhouse_provider_name` VARCHAR(150) NULL AFTER `inhouse_provider_user_id`;

ALTER TABLE `{{prefix}}queue_entries`
  ADD COLUMN IF NOT EXISTS `service_id` BIGINT UNSIGNED NULL AFTER `priority`,
  ADD COLUMN IF NOT EXISTS `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist' AFTER `service_name`,
  ADD COLUMN IF NOT EXISTS `inhouse_provider_user_id` BIGINT UNSIGNED NULL AFTER `dentist_name`;

ALTER TABLE `{{prefix}}clinical_encounters`
  ADD COLUMN IF NOT EXISTS `signed_by_name` VARCHAR(190) NULL AFTER `finalized_at`,
  ADD COLUMN IF NOT EXISTS `signed_license_number` VARCHAR(100) NULL AFTER `signed_by_name`,
  ADD COLUMN IF NOT EXISTS `signed_signature_path` VARCHAR(255) NULL AFTER `signed_license_number`,
  ADD COLUMN IF NOT EXISTS `finalized_hash` CHAR(64) NULL AFTER `signed_signature_path`;

ALTER TABLE `{{prefix}}clinical_note_addenda`
  ADD COLUMN IF NOT EXISTS `author_name_snapshot` VARCHAR(190) NULL AFTER `author_user_id`,
  ADD COLUMN IF NOT EXISTS `author_license_snapshot` VARCHAR(100) NULL AFTER `author_name_snapshot`,
  ADD COLUMN IF NOT EXISTS `signature_path_snapshot` VARCHAR(255) NULL AFTER `author_license_snapshot`,
  ADD COLUMN IF NOT EXISTS `signed_at` DATETIME NULL AFTER `signature_path_snapshot`,
  ADD COLUMN IF NOT EXISTS `addendum_hash` CHAR(64) NULL AFTER `signed_at`;

UPDATE `{{prefix}}appointments` a
LEFT JOIN `{{prefix}}service_catalog` s
  ON s.name=a.service_name AND (s.branch_id=a.branch_id OR s.branch_id IS NULL)
SET a.service_id=COALESCE(a.service_id,s.id),
    a.provider_type=COALESCE(s.provider_type,'dentist')
WHERE a.service_id IS NULL OR a.provider_type IS NULL;

UPDATE `{{prefix}}queue_entries` q
LEFT JOIN `{{prefix}}appointments` a ON a.id=q.appointment_id
LEFT JOIN `{{prefix}}service_catalog` s
  ON s.name=q.service_name AND (s.branch_id=q.branch_id OR s.branch_id IS NULL)
SET q.service_id=COALESCE(q.service_id,a.service_id,s.id),
    q.provider_type=COALESCE(a.provider_type,s.provider_type,'dentist')
WHERE q.service_id IS NULL OR q.provider_type IS NULL;

-- Keep the earliest encounter attached to each queue entry and preserve any
-- legacy duplicates as historical records without a queue link.
UPDATE `{{prefix}}clinical_encounters` c
JOIN (
  SELECT queue_entry_id,MIN(id) keep_id
  FROM `{{prefix}}clinical_encounters`
  WHERE queue_entry_id IS NOT NULL
  GROUP BY queue_entry_id
  HAVING COUNT(*)>1
) d ON d.queue_entry_id=c.queue_entry_id
SET c.queue_entry_id=NULL
WHERE c.id<>d.keep_id;

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;
