-- DentalCare 0.5.9.8.32: appointment-request integrity, booking scopes,
-- attributable confirmation, structured staff names, and service deferral/movement.

ALTER TABLE `{{prefix}}users`
  ADD COLUMN IF NOT EXISTS `first_name` VARCHAR(80) NULL AFTER `name`,
  ADD COLUMN IF NOT EXISTS `middle_name` VARCHAR(80) NULL AFTER `first_name`,
  ADD COLUMN IF NOT EXISTS `last_name` VARCHAR(80) NULL AFTER `middle_name`;

UPDATE `{{prefix}}users`
SET `first_name`=CASE
      WHEN COALESCE(NULLIF(TRIM(`first_name`),''),'')<>'' THEN `first_name`
      WHEN LOCATE(' ',TRIM(`name`))=0 THEN TRIM(`name`)
      ELSE SUBSTRING_INDEX(TRIM(`name`),' ',1)
    END,
    `last_name`=CASE
      WHEN COALESCE(NULLIF(TRIM(`last_name`),''),'')<>'' THEN `last_name`
      WHEN LOCATE(' ',TRIM(`name`))=0 THEN ''
      ELSE SUBSTRING_INDEX(TRIM(`name`),' ',-1)
    END,
    `middle_name`=CASE
      WHEN COALESCE(NULLIF(TRIM(`middle_name`),''),'')<>'' THEN `middle_name`
      WHEN (LENGTH(TRIM(`name`))-LENGTH(REPLACE(TRIM(`name`),' ','')))<2 THEN NULL
      ELSE NULLIF(TRIM(SUBSTRING(TRIM(`name`),LENGTH(SUBSTRING_INDEX(TRIM(`name`),' ',1))+2,LENGTH(TRIM(`name`))-LENGTH(SUBSTRING_INDEX(TRIM(`name`),' ',1))-LENGTH(SUBSTRING_INDEX(TRIM(`name`),' ',-1))-2)),'')
    END
WHERE COALESCE(NULLIF(TRIM(`first_name`),''),'')='' OR COALESCE(NULLIF(TRIM(`last_name`),''),'')='';

ALTER TABLE `{{prefix}}service_catalog`
  ADD COLUMN IF NOT EXISTS `booking_scope` VARCHAR(40) NOT NULL DEFAULT 'public' AFTER `is_public`;

UPDATE `{{prefix}}service_catalog`
SET `booking_scope`=CASE WHEN `is_public`=1 THEN 'public' ELSE 'reception' END
WHERE `booking_scope` IS NULL OR TRIM(`booking_scope`)='';

UPDATE `{{prefix}}service_catalog`
SET `is_public`=0,`booking_scope`='clinical_intake',`updated_at`=NOW()
WHERE LOWER(TRIM(`name`)) IN ('blood pressure check','blood pressure','vitals check','vital signs check','pre-procedure assessment');

ALTER TABLE `{{prefix}}appointments`
  ADD COLUMN IF NOT EXISTS `booking_source` VARCHAR(40) NOT NULL DEFAULT 'staff' AFTER `appointment_origin`,
  ADD COLUMN IF NOT EXISTS `confirmation_source` VARCHAR(60) NULL AFTER `confirmation_status`,
  ADD COLUMN IF NOT EXISTS `confirmation_note` VARCHAR(500) NULL AFTER `confirmation_source`,
  ADD COLUMN IF NOT EXISTS `patient_agreed_at` DATETIME NULL AFTER `confirmation_note`;

ALTER TABLE `{{prefix}}appointment_services`
  MODIFY `status` ENUM('planned','completed','deferred','moved','not_performed','cancelled') NOT NULL DEFAULT 'planned',
  ADD COLUMN IF NOT EXISTS `disposition_reason` VARCHAR(500) NULL AFTER `status`,
  ADD COLUMN IF NOT EXISTS `destination_appointment_id` BIGINT UNSIGNED NULL AFTER `disposition_reason`,
  ADD COLUMN IF NOT EXISTS `source_appointment_service_id` BIGINT UNSIGNED NULL AFTER `destination_appointment_id`,
  ADD COLUMN IF NOT EXISTS `disposition_by` BIGINT UNSIGNED NULL AFTER `source_appointment_service_id`,
  ADD COLUMN IF NOT EXISTS `disposition_at` DATETIME NULL AFTER `disposition_by`;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_service_dispositions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_service_id` BIGINT UNSIGNED NOT NULL,
  `source_appointment_id` BIGINT UNSIGNED NOT NULL,
  `destination_appointment_id` BIGINT UNSIGNED NULL,
  `action` ENUM('defer_next','move_existing','create_followup','not_performed') NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `performed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_service_disposition_source` (`source_appointment_id`,`created_at`),
  KEY `idx_service_disposition_destination` (`destination_appointment_id`,`created_at`),
  KEY `idx_service_disposition_segment` (`appointment_service_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
