-- DentalCare v0.5.9.7 revenue-cycle integrity.

CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `cashier_user_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('open','closed_pending_review','closed') NOT NULL DEFAULT 'open',
  `opening_cash` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at` DATETIME NULL,
  `expected_cash` DECIMAL(12,2) NULL,
  `counted_cash` DECIMAL(12,2) NULL,
  `variance` DECIMAL(12,2) NULL,
  `close_notes` TEXT NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cashier_session_open` (`branch_id`,`cashier_user_id`,`status`,`opened_at`),
  KEY `idx_cashier_session_review` (`status`,`closed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}discount_authorizations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `discount_type` ENUM('percent','fixed') NOT NULL,
  `discount_value` DECIMAL(12,2) NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected','used','expired') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `used_by` BIGINT UNSIGNED NULL,
  `used_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_discount_authorization_patient` (`branch_id`,`patient_id`,`status`,`requested_at`),
  KEY `idx_discount_authorization_queue` (`queue_entry_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}zero_charge_authorizations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `category` ENUM('no_charge_followup','warranty_rework','package_included','staff_benefit','charity','waived_balance','other') NOT NULL,
  `reason` VARCHAR(500) NOT NULL,
  `evidence_reference` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected','used','expired') NOT NULL DEFAULT 'pending',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `used_by` BIGINT UNSIGNED NULL,
  `used_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_zero_charge_queue` (`queue_entry_id`,`status`,`requested_at`),
  KEY `idx_zero_charge_patient` (`branch_id`,`patient_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}payment_adjustments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `adjustment_type` ENUM('refund','reversal') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `method` VARCHAR(40) NOT NULL,
  `reference_no` VARCHAR(100) NULL,
  `reason` VARCHAR(500) NOT NULL,
  `status` ENUM('requested','approved','rejected','processed') NOT NULL DEFAULT 'requested',
  `requested_by` BIGINT UNSIGNED NOT NULL,
  `requested_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_by` BIGINT UNSIGNED NULL,
  `approved_at` DATETIME NULL,
  `review_note` TEXT NULL,
  `processed_by` BIGINT UNSIGNED NULL,
  `processed_at` DATETIME NULL,
  `cashier_session_id` BIGINT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_payment_adjustment_payment` (`payment_id`,`status`,`requested_at`),
  KEY `idx_payment_adjustment_review` (`branch_id`,`status`,`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE `{{prefix}}invoice_items`
SET `source_type`=CASE
    WHEN `procedure_id` IS NOT NULL THEN 'clinical_procedure'
    WHEN `service_id` IS NOT NULL THEN 'service_catalog'
    ELSE 'legacy_manual'
  END,
  `source_authorized_at`=COALESCE(`source_authorized_at`,`created_at`),
  `source_authorized_by`=COALESCE(`source_authorized_by`,`created_by`)
WHERE `source_type`='legacy_manual' OR `source_type` IS NULL;

UPDATE `{{prefix}}invoices`
SET `charge_locked_at`=COALESCE(`charge_locked_at`,CASE WHEN `paid_amount`>0 OR `discount_amount`>0 THEN NOW() ELSE NULL END),
    `charge_locked_by`=COALESCE(`charge_locked_by`,`pos_finalized_by`)
WHERE `paid_amount`>0 OR `discount_amount`>0;

UPDATE `{{prefix}}payments`
SET `status`='posted',`source_type`=COALESCE(NULLIF(`source_type`,''),'patient')
WHERE `status` IS NULL OR `status`='';

CREATE TABLE IF NOT EXISTS `{{prefix}}insurance_claim_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `claim_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NOT NULL,
  `claimed_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `approved_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `denied_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `submission_reference` VARCHAR(120) NULL,
  `remittance_reference` VARCHAR(120) NULL,
  `notes` TEXT NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_claim_history_claim` (`claim_id`,`created_at`),
  KEY `idx_claim_history_status` (`new_status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}insurance_claim_history`
(`claim_id`,`previous_status`,`new_status`,`claimed_amount`,`approved_amount`,`denied_amount`,`submission_reference`,`remittance_reference`,`notes`,`changed_by`,`created_at`)
SELECT c.id,NULL,c.status,c.claimed_amount,c.approved_amount,c.denied_amount,c.submission_reference,c.remittance_reference,
       'Legacy claim state captured during Version 0.5.9.7 migration.',COALESCE(c.updated_by,c.created_by),COALESCE(c.created_at,NOW())
FROM `{{prefix}}insurance_claims` c
WHERE NOT EXISTS (
  SELECT 1 FROM `{{prefix}}insurance_claim_history` h WHERE h.claim_id=c.id
);
