ALTER TABLE `{{prefix}}service_catalog`
  ADD COLUMN IF NOT EXISTS `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured' AFTER `standard_fee`,
  ADD COLUMN IF NOT EXISTS `no_charge_reason` VARCHAR(500) NULL AFTER `fee_status`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_by` BIGINT UNSIGNED NULL AFTER `no_charge_reason`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_at` DATETIME NULL AFTER `fee_authorized_by`;

UPDATE `{{prefix}}service_catalog`
SET `fee_status`='billable'
WHERE `standard_fee` > 0 AND (`fee_status`='unconfigured' OR `fee_status` IS NULL);

ALTER TABLE `{{prefix}}treatment_plan_items`
  ADD COLUMN IF NOT EXISTS `service_id` BIGINT UNSIGNED NULL AFTER `treatment_plan_id`,
  ADD COLUMN IF NOT EXISTS `fee_status` ENUM('unconfigured','billable','no_charge') NOT NULL DEFAULT 'unconfigured' AFTER `estimated_fee`,
  ADD COLUMN IF NOT EXISTS `no_charge_reason` VARCHAR(500) NULL AFTER `fee_status`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_by` BIGINT UNSIGNED NULL AFTER `no_charge_reason`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_at` DATETIME NULL AFTER `fee_authorized_by`;

UPDATE `{{prefix}}treatment_plan_items` i
JOIN `{{prefix}}treatment_plans` p ON p.id=i.treatment_plan_id
JOIN `{{prefix}}service_catalog` s ON LOWER(TRIM(s.name))=LOWER(TRIM(i.procedure_name))
  AND (s.branch_id=p.branch_id OR s.branch_id IS NULL)
SET i.service_id=s.id
WHERE i.service_id IS NULL;

UPDATE `{{prefix}}treatment_plan_items`
SET fee_status='billable'
WHERE estimated_fee > 0 AND fee_status='unconfigured';

UPDATE `{{prefix}}treatment_plan_items` i
JOIN `{{prefix}}service_catalog` s ON s.id=i.service_id
SET i.estimated_fee=CASE WHEN s.fee_status='billable' THEN s.standard_fee ELSE 0 END,
    i.fee_status=s.fee_status,
    i.no_charge_reason=s.no_charge_reason,
    i.fee_authorized_by=s.fee_authorized_by,
    i.fee_authorized_at=s.fee_authorized_at
WHERE i.fee_status='unconfigured' AND s.fee_status IN ('billable','no_charge');

ALTER TABLE `{{prefix}}procedures`
  ADD COLUMN IF NOT EXISTS `service_name_snapshot` VARCHAR(190) NULL AFTER `procedure_name`,
  ADD COLUMN IF NOT EXISTS `service_code_snapshot` VARCHAR(80) NULL AFTER `service_name_snapshot`,
  ADD COLUMN IF NOT EXISTS `approved_fee_snapshot` DECIMAL(12,2) NULL AFTER `service_code_snapshot`,
  ADD COLUMN IF NOT EXISTS `fee_source` ENUM('service_catalog','treatment_plan','authorized_no_charge') NULL AFTER `approved_fee_snapshot`,
  ADD COLUMN IF NOT EXISTS `fee_status` ENUM('blocked','billable','no_charge') NOT NULL DEFAULT 'blocked' AFTER `fee_source`,
  ADD COLUMN IF NOT EXISTS `no_charge_reason` VARCHAR(500) NULL AFTER `fee_status`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_by` BIGINT UNSIGNED NULL AFTER `no_charge_reason`,
  ADD COLUMN IF NOT EXISTS `fee_authorized_at` DATETIME NULL AFTER `fee_authorized_by`,
  ADD COLUMN IF NOT EXISTS `billing_status` ENUM('pending','ready','invoiced','no_charge','blocked') NOT NULL DEFAULT 'pending' AFTER `fee_authorized_at`;

UPDATE `{{prefix}}procedures` pr
JOIN `{{prefix}}clinical_encounters` ce ON ce.id=pr.encounter_id
JOIN `{{prefix}}service_catalog` s ON LOWER(TRIM(s.name))=LOWER(TRIM(pr.procedure_name))
  AND (s.branch_id=ce.branch_id OR s.branch_id IS NULL)
SET pr.service_id=COALESCE(pr.service_id,s.id)
WHERE pr.service_id IS NULL;

UPDATE `{{prefix}}procedures` pr
JOIN `{{prefix}}treatment_plan_items` i ON i.id=pr.treatment_plan_item_id
SET pr.service_id=COALESCE(pr.service_id,i.service_id),
    pr.service_name_snapshot=COALESCE(pr.service_name_snapshot,i.procedure_name),
    pr.service_code_snapshot=COALESCE(pr.service_code_snapshot,i.procedure_code),
    pr.approved_fee_snapshot=CASE WHEN i.fee_status='no_charge' THEN 0 ELSE NULLIF(i.estimated_fee,0) END,
    pr.fee_source='treatment_plan',
    pr.fee_status=CASE WHEN i.fee_status='no_charge' THEN 'no_charge' WHEN i.fee_status='billable' AND i.estimated_fee>0 THEN 'billable' ELSE 'blocked' END,
    pr.no_charge_reason=i.no_charge_reason,
    pr.fee_authorized_by=i.fee_authorized_by,
    pr.fee_authorized_at=i.fee_authorized_at,
    pr.billing_status=CASE WHEN i.fee_status='no_charge' THEN 'ready' WHEN i.fee_status='billable' AND i.estimated_fee>0 THEN 'ready' ELSE 'blocked' END
WHERE pr.approved_fee_snapshot IS NULL;

UPDATE `{{prefix}}procedures` pr
JOIN `{{prefix}}service_catalog` s ON s.id=pr.service_id
SET pr.service_name_snapshot=COALESCE(pr.service_name_snapshot,s.name),
    pr.approved_fee_snapshot=CASE WHEN s.fee_status='no_charge' THEN 0 ELSE NULLIF(s.standard_fee,0) END,
    pr.fee_source=CASE WHEN s.fee_status='no_charge' THEN 'authorized_no_charge' ELSE 'service_catalog' END,
    pr.fee_status=CASE WHEN s.fee_status='no_charge' THEN 'no_charge' WHEN s.fee_status='billable' AND s.standard_fee>0 THEN 'billable' ELSE 'blocked' END,
    pr.no_charge_reason=s.no_charge_reason,
    pr.fee_authorized_by=s.fee_authorized_by,
    pr.fee_authorized_at=s.fee_authorized_at,
    pr.billing_status=CASE WHEN s.fee_status='no_charge' THEN 'ready' WHEN s.fee_status='billable' AND s.standard_fee>0 THEN 'ready' ELSE 'blocked' END
WHERE pr.approved_fee_snapshot IS NULL;

UPDATE `{{prefix}}procedures`
SET billing_status='blocked',fee_status='blocked'
WHERE approved_fee_snapshot IS NULL AND status IN ('in_progress','completed');
