-- DentalCare v0.5.9.8.36 treatment-to-billing final flow.
-- Idempotent clinical handoff metadata only. No patient, billing, or payment data is deleted.

ALTER TABLE `{{prefix}}procedures`
  MODIFY `status` ENUM('planned','in_progress','completed','interrupted','deferred','moved_follow_up','not_performed','cancelled') NOT NULL DEFAULT 'completed';

ALTER TABLE `{{prefix}}procedures`
  MODIFY `billing_status` ENUM('pending','ready','invoiced','no_charge','blocked','not_billable') NOT NULL DEFAULT 'pending';

ALTER TABLE `{{prefix}}procedures`
  ADD COLUMN IF NOT EXISTS `disposition_reason` VARCHAR(500) NULL AFTER `outcome`,
  ADD COLUMN IF NOT EXISTS `patient_agreement_recorded_at` DATETIME NULL AFTER `disposition_reason`,
  ADD COLUMN IF NOT EXISTS `patient_agreement_recorded_by` BIGINT UNSIGNED NULL AFTER `patient_agreement_recorded_at`,
  ADD COLUMN IF NOT EXISTS `cashier_handoff_status` VARCHAR(40) NOT NULL DEFAULT 'pending_review' AFTER `billing_status`,
  ADD COLUMN IF NOT EXISTS `cashier_handoff_at` DATETIME NULL AFTER `cashier_handoff_status`;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_billing_handoff_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `event_label` VARCHAR(190) NOT NULL,
  `payload_json` LONGTEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_handoff_encounter` (`encounter_id`,`created_at`),
  KEY `idx_clinical_handoff_invoice` (`invoice_id`,`created_at`),
  KEY `idx_clinical_handoff_patient` (`patient_id`,`created_at`),
  KEY `idx_clinical_handoff_event` (`event_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `{{prefix}}procedures`
SET `billing_status`='not_billable', `cashier_handoff_status`='excluded'
WHERE `status` IN ('deferred','moved_follow_up','not_performed','cancelled')
  AND `billing_status` NOT IN ('invoiced','no_charge');

UPDATE `{{prefix}}procedures`
SET `cashier_handoff_status`=CASE
    WHEN `status`='completed' AND `billing_status` IN ('invoiced','no_charge') THEN 'sent_to_cashier'
    WHEN `status`='completed' AND `billing_status`='ready' THEN 'ready_for_cashier'
    WHEN `status` IN ('deferred','moved_follow_up','not_performed','cancelled') THEN 'excluded'
    ELSE COALESCE(NULLIF(`cashier_handoff_status`,''),'pending_review')
  END
WHERE `cashier_handoff_status` IS NULL OR `cashier_handoff_status`='pending_review';
