-- ============================================================================
--  2026_sprint30j_phase4_acceptance.sql — فاز ۴ (بستنِ پذیرش/Acceptance Closure)
--  ADDITIVE فقط / idempotent / بدونِ DELIMITER / بدونِ stored procedure /
--  سازگار با cPanel و MariaDB/MySQL / غیرِمخرب (هیچ داده‌ای حذف/بازنویسی نمی‌شود).
--  پیش‌نیاز: پس از ...30h و ...30i اجرا شود. اجرای چندباره بی‌خطر است.
--  این مهاجرت هیچ UNIQUE جدیدی اضافه نمی‌کند؛ گزارشِ تشخیصیِ تکراری‌ها در انتها آمده.
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ─────────────── cw_consult_plan_items: D1 — پراپاگیشنِ قالبِ رضایتِ آیتمِ پلن ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_consult_plan_items' AND COLUMN_NAME='consent_template_key');
SET @s:=IF(@c=0,"ALTER TABLE `cw_consult_plan_items` ADD COLUMN `consent_template_key` VARCHAR(40) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── cw_performed_services: D1/D4/D7 — snapshotِ قالب، شمارندهٔ وقفه، و فیلدهای ابطالِ پزشکی ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='consent_template_key');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `consent_template_key` VARCHAR(40) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='interrupt_count');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `interrupt_count` INT NOT NULL DEFAULT 0",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='invalidated_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `invalidated_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='invalidated_by');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `invalidated_by` INT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='invalidated_reason');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `invalidated_reason` VARCHAR(255) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND COLUMN_NAME='correction_ref');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD COLUMN `correction_ref` VARCHAR(60) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── cw_aftercare_deliveries: D3 — زمان‌بندی، شناسهٔ ارائه‌دهنده، و آخرین تلاش ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND COLUMN_NAME='provider_message_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `provider_message_id` VARCHAR(80) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND COLUMN_NAME='scheduled_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `scheduled_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND COLUMN_NAME='acknowledged_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `acknowledged_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND COLUMN_NAME='last_attempt_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD COLUMN `last_attempt_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── cw_services: D3 — مراحلِ چندگانهٔ مراقبت (JSON) ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_services' AND COLUMN_NAME='aftercare_stages');
SET @s:=IF(@c=0,"ALTER TABLE `cw_services` ADD COLUMN `aftercare_stages` TEXT DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── cw_appointments: D4 — پیوندهای ساختاریِ جلسهٔ بعد ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='cw_visit_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `cw_visit_id` INT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='performed_service_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `performed_service_id` BIGINT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='payment_item_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `payment_item_id` BIGINT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='plan_item_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `plan_item_id` BIGINT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='service_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `service_id` INT UNSIGNED DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='device_id');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `device_id` INT DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND COLUMN_NAME='source_dept');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD COLUMN `source_dept` VARCHAR(20) DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── cw_satisfaction_eligibility: D7 — ستونِ updated_at برای ابطال ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_satisfaction_eligibility' AND COLUMN_NAME='updated_at');
SET @s:=IF(@c=0,"ALTER TABLE `cw_satisfaction_eligibility` ADD COLUMN `updated_at` DATETIME DEFAULT NULL",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── جدولِ جدید: تاریخچهٔ append-only تلاش‌های مراقبتِ بعد (D3) ───────────────
CREATE TABLE IF NOT EXISTS `cw_aftercare_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `delivery_id` BIGINT UNSIGNED NOT NULL,
  `attempt_no` INT NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL,
  `error` VARCHAR(255) DEFAULT NULL,
  `provider_message_id` VARCHAR(80) DEFAULT NULL,
  `by_user` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_delivery` (`delivery_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ─────────────── ایندکس‌های کمکی (idempotent، غیرِیکتا) ───────────────
SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND INDEX_NAME='idx_ps_invalidated');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD INDEX `idx_ps_invalidated` (`invalidated_at`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_performed_services' AND INDEX_NAME='idx_ps_correction_ref');
SET @s:=IF(@c=0,"ALTER TABLE `cw_performed_services` ADD INDEX `idx_ps_correction_ref` (`correction_ref`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND INDEX_NAME='idx_appt_ps');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD INDEX `idx_appt_ps` (`performed_service_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_appointments' AND INDEX_NAME='idx_appt_visit');
SET @s:=IF(@c=0,"ALTER TABLE `cw_appointments` ADD INDEX `idx_appt_visit` (`cw_visit_id`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @c:=(SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='cw_aftercare_deliveries' AND INDEX_NAME='idx_after_sched');
SET @s:=IF(@c=0,"ALTER TABLE `cw_aftercare_deliveries` ADD INDEX `idx_after_sched` (`status`,`scheduled_at`)",'SELECT 1'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- ─────────────── گزارشِ تشخیصیِ تکراری (اطلاعاتی؛ پیش از هر UNIQUE احتمالی در آینده) ───────────────
-- این مهاجرت UNIQUE جدیدی اضافه نمی‌کند. اگر بعداً UNIQUE(performed_service_id,stage) لازم شد،
-- ابتدا خروجیِ زیر باید خالی باشد:
SELECT 'aftercare_dupe_check' AS report, performed_service_id, stage, COUNT(*) AS n
  FROM cw_aftercare_deliveries
  GROUP BY performed_service_id, stage HAVING COUNT(*) > 1;

SET FOREIGN_KEY_CHECKS = 1;
-- پایانِ مهاجرتِ 30j